您的位置: 首页  >  文章  >  Mysql数据库学习笔记

Mysql数据库学习笔记

分类: 文章 • 2023-12-14 20:51:58

目录
数据库中的相关基本知识 1
数据库的设计 2
一、 三范式 2
二、 ER模型 2
数据库的操作 2
数据表的操作 3
一、查看当前数据库中所有的表 3
二、 创建表：NAME是数据表的名字 3
三、 查看表的结构：NAME是数据表的名字 3
四、 向数据表里面插入数据：NAME是数据表的名字 3
五、 删除表：NAME是数据表的名字 4
六、 数据表的增删查改：NAME是数据表的名字 4
七、 零碎知识 7
使用python操作数据库 7
一、 基本流程 7
二、 增、删、改 8
Mysql高级 8
一、 视图 8
二、 事务----所用于事务的增、删、改 8
三、 索引 9
四、 账户管理 9
五、 MySQL主从 9

数据库中的相关基本知识

1.ENUM：枚举类型中的类型，可以用数字来选择；
2.Char相对于varchar增加了数据内存的使用；
3.链接数据库：
mysql -uroot -p
mysql -uroot -p123456(跟密码)
4.退出数据库：
Exit
Quit
CTRL+D
5.列叫字段，行叫记录；
6.WHERE对原表进行判断，HAVING对查询结果的条件判断
7.

数据库的设计

一、三范式

1．第一范式：列必须是原子，不能再拆分；
2．第二范式：在第一范式的基础上，必须有主键，除了主键之外的非主键字段必须完全依赖于主键，而不能依赖于主键的一部分（主键可能由多个字段组成）；
3．第三范式：在第二范式的基础上，除了主键之外的非主键字段必须直接依赖于主键；

二、ER模型

1．多对一模型在多的表里面添加字段；
2．多对多模型，新建一个表格，在新表格中添加两个表格的对应字段；

数据库的操作

Sql语句都有分号
1.显示数据库版本：
SELECT VERSION();
2.显示当前数据库的时间：
SELECT NOW();
3.显示所有数据库：
SHOW DATABASES;
4.创建数据库：NAME是数据库的名字
CREAT DATABASE NAME;
5.查看创建数据库的类型的语句：NAME是数据库的名字
SHOW CREATE DATABASE NAME
6.更改创建后的数据库的语句：NAME是数据库的名字
SHOW CREATE DATABASE NAMENEW CHARSET=’UTF8;
7.查看新创建的数据库的类型的语句：
SHOW CREATE DATABASE NAMENEW;
8.删除数据库：当数据库的名字出现无法识别的时候可以尝试加上`（这个是Tab上边的撇的符号）的符号；NAME是数据库的名字
DROP DATABASE NAME;
9.查看当前使用的数据库：
SELECT DATABASE();
10.使用数据库：NAME是数据库的名字
USE NAME;

数据表的操作

一、查看当前数据库中所有的表

1.SHOW TABLES;

二、创建表：NAME是数据表的名字

—AUTO_INCREMENT 表示自动增长
—NOT NULL 表示不能为空
—PRIMARY KEY 表示主键
—DEFAULT 默认值
1.CREATE TABLE 数据表名字(字段 类型 约束,字段 类型 约束)；
—CREATE TABLE NAME (ID INT PRIMARY KEY , NAME VARCHAR (30));
2.创建一个表，其中新建表的数据来自另一个表
—CREATE TABLE 新建表 AS SELECT 字段名1，字段名2…… FROM 已建表;

三、查看表的结构：NAME是数据表的名字

1.DESC ‘NAME’;
Mysql数据库学习笔记
图 1 插入数据并查询

四、向数据表里面插入数据：NAME是数据表的名字

批量插入：
1．INSERT INTO NAME VALUES(根据数据表字段名的顺序填入相应的数据)，VALUES(根据数据表字段名的顺序填入相应的数据)
2．INSERT INTO NAME(字段名1，字段名2……) VALUES (对应字段名1的值，……)，VALUES (对应字段名1的值，……)
3．INSERT OR IGNORE INTO NAME VALUES(根据数据表字段名的顺序填入相应的数据)，VALUES(根据数据表字段名的顺序填入相应的数据)

五、删除表：NAME是数据表的名字

1．DROP TABLE NAME;

六、数据表的增删查改：NAME是数据表的名字

1.增

(1)ALTER TABLE NAME ADD 字段名 类型以及约束;
(2)对于已经存在的数据表，更新外键约束
①ALTER TABLE NAMEa ADD FOREIGN KEY (字段名) REFERENCES NAMEb(字段名)
(3)

2.删

(1)物理删除：真正意义上的删除
DELETE FROM NAME;
DELETE FROM NAME WHERE 字段名 = xxxx;
(2)逻辑删除：增加字段，标记是否删除了
ALTER TABLE NAME ADD IS_DELETE BIT DEFAULT 0;
ALTER TABLE NAME DROP 字段名;

3.查NAME是数据表的名字

SHOW CREATE TABLE NAME;
获取当前数据库中所有表的名字：
----SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE = ‘NAME’;

  • (1)查询数据表里面的数据：
    Mysql数据库学习笔记
    图 2 STUDENTS表里面的数据
    ①无条件全部查询：SELECT * FROM NAME;
    ②有条件全部查询：SELECT * FROM NAME WHERE 字段名1 =or xxxxx;
    ③查询指定字段：SELECT 字段名1,字段名2,…… FROM NAME;
    ④查询指定字段且显示名称更改：
    SELECT 字段名1 AS xxx,字段名2 AS xxx,…… FROM NAME;
    SELECT NAME.字段名1,NAME.字段名2 FROM NAME;
    —数据表一旦改名，原来的名不可索引字段名
    SELECT N.字段名1,N.字段名2 FROM NAME AS N;
    —DISTINCT用于去重
    可以直接使用 DISTINCT(字段名) 放在SELECT后面相当于一个被筛选的字段
    SELECT DISTINCT 字段名 FROM NAME;
  • (2)条件查询
    ①比较运算符：’>’ OR ‘<’ OR ‘=’ OR ‘!=’
    单个否定：SELECT * FROM NAME WHERE (NOT) 条件1，(NOT)条件2，……
    全部否定：SELECT * FROM NAME WHERE (NOT)( 条件1，条件2，……)
  • (3)模糊查询
    ①like：
    —%替换一个或者多个或没有；_替换一个
    —在查询条件前加就是前面有内容，后面加就是后面有内容
    1)查询以“小”字开头的
    SELECT 字段名 FROM NAME WHERE 字段名 LIKE “小%”
    2)查询有“小”字的
    SELECT 字段名 FROM NAME WHERE 字段名 LIKE “%小%”
    3)查询有两个字的
    SELECT 字段名 FROM NAME WHERE 字段名 LIKE “”
    4)查询至少有两个字的
    SELECT 字段名 FROM NAME WHERE 字段名 LIKE “    %”
    ②rlike:使用正则表达式
    1)查询以“周”开头的
    SELECT 字段名 FROM NAME WHERE 字段名 RLIKE “^周.*”
    2)查询以“周”开头，以“伦”结尾的
    SELECT 字段名 FROM NAME WHERE 字段名 RLIKE “^周.*伦$”
    ③范围查询
    1)IN(1,3,8)表示在一个非连续的范围内
    —SELECT 字段名1 = XXXX,…… FROM NAME WHERE 字段名 = XX OR 字段名 = XX OR ……
    在一个范围内：
    SELECT 字段名1 = XXXX,…… FROM NAME WHERE 字段名 IN (x,x,x)
    不在一个范围内：
    SELECT 字段名1 = XXXX,…… FROM NAME WHERE 字段名 NOT IN (x,x,x)
    2)BETWEEN … AND … 表示在一个连续的范围内
    在一个范围内：
    SELECT 字段名1 = XXXX,…… FROM NAME WHERE 字段名 BETWEEN … AND …
    不在一个范围内：
    SELECT 字段名1 = XXXX,…… FROM NAME WHERE 字段名 NOT BETWEEN … AND …
    3)判断空：
    SELECT 字段名1 = XXXX,…… FROM NAME WHERE 字段名 IS (NOT) NULL;
  • (4)排序
    ①ORDER BY ：
    —若后面只有一个字段，按照字段排序后，若字段相同，默认按照主键降序排列
    —若后面多个字段，前一个字段相同，则按照后一个字段排序，以此类推,若前面都相同，则默认按照主键排列
    SELECT * FROM NAME WHERE 条件1 AND 条件2 …… ORDER BY 字段名1 ASC(升序)/DESC(降序),字段名2 ASC(升序)/DESC(降序)
  • (5)聚合函数
    ①总数：COUNT
    1)SELECT COUNT(*) FROM NAME WHERE 条件;
    ②最大值：MAX
    1)SELECT MAX(字段名) FROM NAME WHERE 条件;
    ③最小值：MIN
    1)SELECT MIN(字段名) FROM NAME WHERE 条件;
    ④求和：SUM
    ⑤求平均值：AVG
    ⑥四舍五入：ROUND(参数1，参数2) 参数1是用来处理的数据，参数2是保留的小数的位数
  • (6)分组：重点是要和聚合函数合起来使用，才能展现分组的好处，注意顺序
    ①GROUP BY：按照字段名来分组
    1)SELECT 字段名,聚合函数 FROM NAME GROUP BY 字段名
    ②GROUP_CONCAT：将多个字段的值连接起来
    1)SELECT GENDER,GROUP_CONCAT(NAME," “,AGE,” ",ID) FROM STUDENTS WHERE GENDER = 1 GROUP BY GENDER;
    2)group_concat( [distinct] <要连接的字段> [order by 排序字段 asc/desc ] [separator ‘分隔符’] )
    ③HAVING：不看分组
    1)SELECT GENDER,GROUP_CONCAT(NAME) FROM STUDENTS GROUP BY GENDER HAVING AVG(AGE) > 30 ;
  • (7)分页：将数据分为多组，可以查询任意一组
    ①LIMIT：START/NUMBER + 1的值就是页数
    1)限制查询出来的数据最大个数：LIMIT 10
    SELECT * FROM NAME LIMIT NUMBER;
    2)LIMIT START,NUMBER
    SELECT * FROM NAME LIMIT START, NUMBER;
    ②LIMIT用法详解：
    https://blog.csdn.net/a934079371/article/details/103760101
    注：START的位置不能用算术式；LIMIT的位置在语句最后；
  • (8)链接查询：多个表的关联查询
    ①内连接：取两个表的交集
    1)SELECT … FROM 表A INNER JOIN 表B ON 条件;
    2)—按要求显示内容
    SELECT NAME1.字段名,NAME2.字段名 FROM 表A INNER JOIN 表B ON 条件 WHERE 条件A AND 条件B;
    3)SELECT A.字段名,C.字段名 FROM 表A AS S INNER JOIN 表B AS C ON 条件;
    ②左连接：以LEFT JOIN左边的表格为基准
    1)SELECT A.字段名,C.字段名 FROM 表A AS S LEFT JOIN 表B AS C ON 条件;
    ③右链接：以RIGHT JOIN右边的表格为基准
    1)SELECT A.字段名,C.字段名 FROM 表A AS S RIGHT JOIN 表B AS C ON 条件;
  • (9)自关联：表内部的连个字段相关联
    ①SELECT … FROM NAME WHERE 条件1;
    ②将一个表当作两个表用，将一张表用AS取两个名字即可；
  • (10)子查询
    ①在SELECT语句里面嵌套SELECT语句；
  • (11)窗口函数
    ①窗口函数：<窗口函数> OVER ([PARITITION BY <列清单>] ORDER BY <排序用列清单>) [PARITITION BY <列清单>]可以省略。
    ②能够作为窗口函数的聚合函数（sum,avg,count,max,min）
    1)专用窗口函数（RANK,DENSE_RANK,ROW_NUMBER)
    a.RANK() OVER (ORDER BY <列名>)：计算排序，如果存在相同位次的记录，则会跳过之后的位次。如1,1,1,4
    b.DENSE_RANK () OVER (ORDER BY <列名>)：计算排序，即使存在相同位次的记录，也不会跳过之后的位次。如1,1,1,2
    c.ROW_NUMBER () OVER (ORDER BY <列名>)：计算排序，赋予唯一的连续位次。如：1,2,3,4
  • (12)Exists语句：
    ①Exists谓词返回的是一个布尔值，在嵌套的子查询中，查询的字段存在，则exists返回True，否则返回false
    1)例如：select e.* from employees as e
    where not exists (select dee.dept_no from dept_emp as dee where dee.emp_no = e.emp_no);
    这里就指的是，子查询中，如果满足了where条件的dee.dept_no存在，就返回True，并且主查询返回令子查询中where条件成立的e.emp_no所在的那条记录。
    https://www.cnblogs.com/netserver/archive/2008/12/25/1362615.html
  • (13)并(UNION)、交(INTERSECT)、差(minus)、除去(EXCEPT)
    ①详解：https://www.cnblogs.com/elves/p/3653771.html
  • (14)case when 字段名 then
    ①ase when <列名> then …的搜索case表达式，最后要记得使用end结束case；

4.改

ALTER TABLE NAME CHANGE 字段名 类型以及约束;
ALTER TABLE NAME MODIFY 字段名 类型以及约束;

  • (1)修改数据：NAME是数据库的名字
    ①全部修改：UPDATE NAME SET 字段名1 = xxxxx，字段名2 = xxxxx;
    ②部分修改：UPDATE NAME SET 字段名1 = xxxxx，字段名2 = xxxxx WHERE 主键 = xxxxx;
  • (2)同步表数据
    ①UPDATE NAMEa INNER JOIN NAMEb ON 条件 SET 需要同步的字段；
  • (3)Repace：
    ①网址：
    https://blog.csdn.net/zhangjg_blog/article/details/23267761
    ② MySQL replace into 有三种形式：
    1.replace into <表名>(<列名>) values(…)
    2.Replace into <表名>(<列名>) select …
    3.replace into <表名> set <列名>=value, …
  • (4)更改数据表的名字：
    ①rename table <变更前的名称> to <变更后的名称>

七、零碎知识 NAME是数据表名

1．IF NOT EXISTS判断是否存在，不存在就执行，存在则不执行；
2．Select选出来的是数值，而且是原原本本的数值，并非有指的是这个数有一个、两个；
3．Left join 后面的条件接在on还是where后面输出的结果不同，要注意；
4．Not in表示筛选的条件不在这个范围里面，而!=则表示符号前后两个值不相同；
5．多个数据表之间关联的时候不一定要用join将其关联成新表，只要能在多个表之间建立筛选关系即可；
八、数据库函数
1．Extract()函数，用于提取时间，可以提取年月日时分秒，还可以组合提取，用法：https://www.runoob.com/sql/func-extract.html
2．Length()函数，用于计算字符串的长度；
3．subtring(对象字符串,截取的起始位置,截取的字符数)；
4．

使用python操作数据库

一、基本流程

from pymysql import *
def main():
#链接数据库
conn = connect(host = ‘localhost’,
port = 3306,
user = ‘root’,
password = ‘123465’,
database = ‘jing_dong’,
charset = ‘utf8’)
#获取游标对象
cursor = conn.cursor()
#通过execute执行sql语句
cursor.execute(“select * from goods;”)
#通过游标对象获取数据
cursor.fetchall() #获取全部数据
#关闭游标对象
cursor.close()
#关闭数据库
conn.close()

if name == ‘main’:
main()

二、增、删、改

1．在执行完sql语句之后一定要Conn.commit()，数据库里才会更新数据；
2．在commit之前可以使用conn.rollback语句撤销上一个commit之后的所有操作；
3．防止SQL注入，使用execute自行获取字符串

Mysql高级

一、视图 NAME是新建的数据表名

1．一张虚拟的表：CREATE VIEW NAME AS + SQL语句；
2．视图无法更新，仅仅用来方便查询数据；
3．删除视图：DROP VIEW NAME;
4．作用：
（1）提高了重用性；
（2）对数据库重构，却不影响程序的运行；
（3）提高了安全性能，可以对不同的用户；
（4）让数据更加清晰；
（5）在原表更新了数据以后，视图也会更新数据；

二、事务----所用于事务的增、删、改

1．事务的四大特性（简称ACID）

（1）原子性A：一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性。
（2）一致性C：数据库总是从一个致性的状态转换到另一个一致性的状态。(在前面的例子中，一致性确保了，即使在执行第三、四条语句之间时系统崩溃，支票账户中也不会损失200美元。因为事务最终没有提交，所以事务中所做的修改也不会保存到数据库中。)
（3）隔离性I：通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的。(在前面的例子中 ，当执行完第三条语句、第四条语句还未开始时，此时有另外的一个账户汇总程序开始运行，则其看到支票帐户的余额并没有被减去200美元。)
（4）持久性D：一旦事务提交，则其所做的修改会永久保存到数据库。(此时即使系统崩溃，修改的数据也不会丢失。)

2．开启事务

（1）START TRANSACTION;
（2）BEGIN;

3．提交事务

（1）COMMIT

4．回滚事务;

（1）ROLLBACK;

三、MYSQL中常用的强制性操作

1．网址：https://www.jb51.net/article/49807.htm

四、触发器

1．在指定数据库事件发生时自动执行
2．网址：https://www.runoob.com/sqlite/sqlite-trigger.html

五、索引

1.一种包含着对数据表所有记录的引用指针的特殊文件；
2.目的：提高查询效率；
3.建立过多的索引会影响更新和插入的速度、占用过多的存储空间，而且没必要所有字段都建立索引；
4.开启运行时间监测NAME是新建的数据表名
(1)SET PROFILING = 1;
5.查看执行的时间
(1)SHOW PROFILES;
6.为表建立索引NAME是新建的数据表名
(1)建立普通索引：CREATE INDEX 索引名 ON NAME(字段名(字段名的长度));
(2)建立唯一索引：CREATE UNIQUE INDEX 索引名 ON NAME(字段名(字段名的长度));
注：字段为字符串的时候要写字段长度，非字符串可以不写；
7.查看索引NAME是新建的数据表名
(1)SHOW INDEX FROM NAME;
8.删除索引NAME是新建的数据表名
(1)DROP INDEX 索引名称 ON NAME;

六、账户管理

1.创建账户&授权
(1)授予权限：GRANT 权限列表 ON 数据库 TO ‘用户名’@’访问主机’ IDENTIFIED BY ‘密码’;
①ALL PRIVILEGES/SELECT/UPDATE…
②LOCALHOST(本地)/%(任意机器都可以登录)
(2)修改权限：GRANT 权限名称 ON 数据库 TO ‘用户名’@’访问主机’ WITH GRANT OPTION;
(3)刷新权限：FLUSH PRICILEGES;
(4)修改密码：UPDATE USER SET AUTHENTICATION_STRING = PASSWORD(‘xxxxxx’) WHERE USER = ‘数据库名’;
2.远程连接（危险慎用）
(1)mysql -uXXX -pXXXXX -h域名
3.删除账户
(1)DROP USER ‘用户名’@’主机’;

七、MySQL主从数据库

1.作用
(1)读写分离
(2)数据备份
(3)负载均衡
2.备份
(1)mysqldump -uroot -p 数据库名 > python.sql
3.恢复
(1)mysql -uroot -p 数据库名 < python.sql

相关推荐