# MySQL常用语法 ## 常用命令行 ### 登录MySQL ``` mysql -u root -p[password]; ``` ### 显示数据库 ```sql SHOW DATABASES; ``` ## 添加语句 ```sql INSERT INTO student (`name`,`gender`,`birthday`,`adress`,`email`) VALUES ('zhangsan','男','1999-08-13','广东省深圳市福田区','zhangsan@outlook.com'), ('lisi','女','2000-08-23','湖北省武汉市洪山区','lisi@163.com') ``` **举个栗子：** ```sql INSERT INTO student (`name`,`gender`,`birthday`,`adress`,`email`) VALUES ('zhangsan','男','1999-08-13','广东省深圳市福田区','zhangsan@outlook.com'), ('lisi','女','2000-08-23','湖北省武汉市洪山区','lisi@163.com') ``` ## 修改语句 ```sql UPDATE `table_name` SET `colnum`='value' WHERE `counum`='value' [AND `counum`='value' ...] ``` **WHERE条件子句：** | **运算符** | 含义 | 范围 | 结果 | | ------- | ------- | --------------- | ----- | | = | 等于 | 1=2 | FALSE | | <> 或 != | 不等于 | 1<>2 | TRUE | | > | 大于 | 1>2 | FALSE | | < | 小于 | 1<2 | TRUE | | >= | 大于等于 | 1>=2 | FALSE | | <= | 小于等于 | 1<=2 | FALSE | | BETWEEN | 在某个范围之间 | BETWEEN 1 AND 2 | | | AND | 与 | 1<2 AND 5>4 | TRUE | | OR | 或 | 1>2 AND 5>4 | TRUE | **举个栗子：** ```sql UPDATE `student` SET `name`='wangwu' WHERE `id`=1 AND `gender`='男' ``` ## 删除语句 ```sql DELETE FROM `table_name` WHERE `colnum`='value'; ``` **举个栗子：** ```sql DELETE FROM `test` WHERE `id`=1 ``` {% hint style="info" %} 实际开发中一般使用伪删除，即给一个标记字段，使用`UPDATE`修改标记达到"删除"的目的。另外即使真的需要删除，那也一定要带上`WHERE`条件子句，不然整个表数据就没了。如果需要清空表数据建议使用`TRUNCATE` {% endhint %} **清空整个表数据：** ```sql TRUNCATE TABLE `test` ``` ## 查询语句 ### MySQL完整语法 ```sql SELECT [ALl | DISTINCT] {* | colnum} [字段别名] FROM table_name [表格别名] [INNER JOIN table_name] -- 联接查询 [WHERE ...] -- 指定结果需要满足的条件（结果为布尔） [GROUP BY ...] -- 指定结果按照哪几个字段来分组 [HAVING ...] -- 过滤分组的记录必须满足次要的条件 [ORDER BY ...] -- 指定查询记录按一个或多个条件排序 [LIMIT {[offset,] rows | rows OFFSET offset}] -- 指定查询的记录从哪条到哪条 ``` ### 模糊查询 查询学号在1000,1001...中匹配的学生 ```sql SELECT * FROM student WHERE `studentno` IN ('1000','1001','1002'); ``` ### 联表查询 ![](https://937995404-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MMTM8NYe0uekudlCMKb%2F-MPJ1b3OvN-rO3yCaiEZ%2F-MPJ1mCR43cKM-qkshcM%2Fimage.png?alt=media\&token=2f955c09-e281-48f3-86d5-3d8f13e367d1) ```sql SELECT FROM TableA A INNER JOIN TableB B ON A.Key = B.Key ``` **举个栗子：** ```sql SELECT stu.studentno,stu.studentname,res.subjectno,res.studentresult FROM `student` stu INNER JOIN `result` res ON stu.studentno=res.studentno ``` | 操作 | 描述 | | ---------- | --------------------- | | INNER JOIN | 如果表中至少有一个匹配，就返回行 | | LEFT JOIN | 会从左表中返回所有的值，即使右表中没有匹配 | | RIGHT JOIN | 会从右表中返回所有的值，即使左表中没有匹配 | 实战一下（3表查询）： > 查询参加考试的同学(学号、学生姓名、科目名、分数) ```sql SELECT stu.studentno,studentname,subjectname,studentresult FROM student stu RIGHT JOIN `result` res ON stu.studentno=res.studentno INNER JOIN `subject` sub ON res.subjectno=sub.subjectno ``` ### 自联接 > 把一张表看成是两张一摸一样的表 ```sql SELECT a.categoryname '父级',b.categoryname '子级' FROM category a,category b WHERE a.categoryid=b.pid ``` ### 分页和排序 排序：ORDER BY colnum \[DESC | ASC] 分页：LIMIT 起始下标，pageSize 计算分页：(n-1)\*pageSize --->n表示当前页，pageSize表示显示条数 ```sql SELECT stu.studentno,stu.studentname,sub.subjectname,res.studentresult FROM student stu INNER JOIN result res ON stu.studentno=res.studentno INNER JOIN `subject` sub ON sub.subjectno=res.subjectno WHERE res.studentresult>60 AND sub.subjectname LIKE '%高等数学%' ORDER BY res.studentresult DESC LIMIT 0,2 ``` ### 子查询 本质：在`WHERE`子句中嵌套一个查询 注意：子查询中的查询语句中返回只能是一行数据 ```sql SELECT studentno,subjectno,studentresult FROM result WHERE subjectno = ( SELECT subjectno FROM `subject` WHERE subjectname = '高等数学-1') ORDER BY studentresult DESC ``` ## 常用函数 文档链接： ```sql -- 获取当前时间 SELECT CURRENT_DATE() -- 获取当前时间时分秒 SELECT NOW() -- 获取本地时间 SELECT LOCALTIME() -- 获取日期几号 SELECT DAY(NOW()) ``` 更多函数访问官网查看... ## 聚合函数 | 函数名称 | 描述 | | ------- | --- | | COUNT() | 计数 | | MAX() | 最大值 | | MIN() | 最小值 | | AVG() | 平均值 | | SUM() | 求和 | | ... | ... | **3种COUNT()用法：** ```sql -- 指定列的计数，会判断指定字段是否为NULL，为NULL则不累加，反之则累加 SELECT COUNT(studentno) FROM student -- 计数 SELECT COUNT(*) FROM student SELECT COUNT(1) FROM student ``` 一般情况下推荐使用`COUNT(*)`，性能略高一些 ## 分组和条件过滤 ```sql SELECT subjectname,AVG(studentresult) as avgres,MAX(studentresult),MIN(studentresult) FROM result r INNER JOIN `subject` s ON r.subjectno = s.subjectno GROUP BY s.subjectname HAVING avgres>60 ``` ## 事务 事务其实是一个最小的不可分割的工作单元。事务能够**保证一个业务的完整性**。 查询事务的自动提交状态（默认就是开启的） ```sql SELECT @@AUTOCOMMIT; ``` ```sql SET AUTOCOMMIT = 0 -- 关闭自动提交 BEGIN -- 开启事务 UPDATE `user` set money = money - 100 WHERE name = 'A'; UPDATE `user` set money = money + 100 WHERE name = 'B'; COMMIT -- 提交事务 ROLLBACK -- 回滚事务 END SET AUTOCOMMIT = 1 -- 开启自动提交 ``` #### 脏读 指事务A读取到事务B修改但未提交事务的数据。 #### **不可重复读** 事务A读取到事务B已提交事务的数据，导致两次读取数据信息不一致。 #### **幻读** 通俗点说就是select 某记录是否存在，不存在，准备插入此记录，但执行 insert 时发现此记录已存在，无法插入，此时就发生了幻读。 #### **串行化** 序列化方式SERIALIZABLE，序列化事务级别既不允许脏读，也不允许不可重复读，并且还不允许幻读。 事务隔离级别越严格，越消耗计算机性能，效率也越低，通常情况下，设置为`允许不可重复读`就可以解决大多数的问题了。 ## 索引 创建索引 ```sql -- 创建索引 CREATE INDEX id_app_user_name ON app_user(`name`) ``` 创建百万行模拟数据 ```sql DELIMITER $$ CREATE FUNCTION mock_data() RETURNS INT -- 注意returns，否则报错。 DETERMINISTIC -- 8.0版本需要多这么一行 BEGIN DECLARE num INT DEFAULT 1000000; -- num 作为截止数字，定义为百万， DECLARE i INT DEFAULT 0; WHILE i < num DO INSERT INTO app_user(`name`, `email`, `phone`, `gender`, `password`, `age`)VALUES(CONCAT('用户', i),'965499224@qq.com', CONCAT('13', FLOOR(RAND()*(999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(), FLOOR(RAND()*100)); SET i = i + 1; END WHILE; RETURN i; END; ``` #### 索引原则 * 索引不是越多越好 * 不要对经常变动的数据加索引 * 小数据量的表没有太大必要加索引 * 索引一般加载常用来查询的字段上 更多内容参考文章： ## 数据库三范式 ### 第一范式（1NF） **原子性**，保证每一列不可再分。 ### 第二范式（2NF） 满足1NF的基础上，**唯一性**，非主键字段要完全依赖主键字段（每张表只描述一件事情） ### 第三范式（3NF） 满足1NF和2NF的基础上，**冗余性**，表中不能产生传递依赖。 ### 反范式 没有冗余的数据库未必是最好的数据库，有时为了提高运行效率，就必须降**低范式标准**，**适当保留冗余数据**。具体做法是： 在概念数据模型设计时遵守第三范式，降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段，减少了查询时的关联，提高查询效率。