MySQL常用语法

常用命令行

登录MySQL

mysql -u root -p[password];

显示数据库

SHOW DATABASES;

添加语句

INSERT INTO student (`name`,`gender`,`birthday`,`adress`,`email`) VALUES
('zhangsan','男','1999-08-13','广东省深圳市福田区','zhangsan@outlook.com'),
('lisi','女','2000-08-23','湖北省武汉市洪山区','lisi@163.com')

举个栗子：

INSERT INTO student (`name`,`gender`,`birthday`,`adress`,`email`) VALUES
('zhangsan','男','1999-08-13','广东省深圳市福田区','zhangsan@outlook.com'),
('lisi','女','2000-08-23','湖北省武汉市洪山区','lisi@163.com')

修改语句

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

举个栗子：

UPDATE `student` SET `name`='wangwu' WHERE `id`=1 AND `gender`='男'

删除语句

DELETE FROM `table_name` WHERE `colnum`='value';

举个栗子：

DELETE FROM `test` WHERE `id`=1

实际开发中一般使用伪删除，即给一个标记字段，使用UPDATE修改标记达到"删除"的目的。另外即使真的需要删除，那也一定要带上WHERE条件子句，不然整个表数据就没了。如果需要清空表数据建议使用TRUNCATE

清空整个表数据：

TRUNCATE TABLE `test`

查询语句

MySQL完整语法

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...中匹配的学生

SELECT * FROM student WHERE `studentno` IN ('1000','1001','1002');

联表查询

SELECT <select_list> FROM TableA A INNER JOIN TableB B ON A.Key = B.Key

举个栗子：

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表查询）：

查询参加考试的同学(学号、学生姓名、科目名、分数)

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

自联接

把一张表看成是两张一摸一样的表

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表示显示条数

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子句中嵌套一个查询

注意：子查询中的查询语句中返回只能是一行数据

SELECT studentno,subjectno,studentresult
FROM result
			WHERE subjectno = (
			SELECT subjectno 
			FROM `subject` 
			WHERE subjectname = '高等数学-1')
ORDER BY studentresult DESC

常用函数

文档链接：https://dev.mysql.com/doc/refman/8.0/en/functions.html

-- 获取当前时间
SELECT CURRENT_DATE()
-- 获取当前时间时分秒
SELECT NOW()
-- 获取本地时间
SELECT LOCALTIME()
-- 获取日期几号
SELECT DAY(NOW())

更多函数访问官网查看...

聚合函数

函数名称

描述

COUNT()

计数

MAX()

最大值

MIN()

最小值

AVG()

平均值

SUM()

求和

...

...

3种COUNT()用法：

-- 指定列的计数，会判断指定字段是否为NULL，为NULL则不累加，反之则累加
SELECT COUNT(studentno) FROM student

-- 计数
SELECT COUNT(*) FROM student
SELECT COUNT(1) FROM student

一般情况下推荐使用COUNT(*)，性能略高一些

分组和条件过滤

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

事务

事务其实是一个最小的不可分割的工作单元。事务能够保证一个业务的完整性。

查询事务的自动提交状态（默认就是开启的）

SELECT @@AUTOCOMMIT;
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，序列化事务级别既不允许脏读，也不允许不可重复读，并且还不允许幻读。

事务隔离级别越严格，越消耗计算机性能，效率也越低，通常情况下，设置为允许不可重复读就可以解决大多数的问题了。

索引

创建索引

-- 创建索引
CREATE INDEX id_app_user_name
ON app_user(`name`)

创建百万行模拟数据

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;

索引原则

  • 索引不是越多越好

  • 不要对经常变动的数据加索引

  • 小数据量的表没有太大必要加索引

  • 索引一般加载常用来查询的字段上

更多内容参考文章：https://blog.codinglabs.org/articles/theory-of-mysql-index.html

数据库三范式

第一范式（1NF）

原子性，保证每一列不可再分。

第二范式（2NF）

满足1NF的基础上，唯一性，非主键字段要完全依赖主键字段（每张表只描述一件事情）

第三范式（3NF）

满足1NF和2NF的基础上，冗余性，表中不能产生传递依赖。

反范式

没有冗余的数据库未必是最好的数据库，有时为了提高运行效率，就必须降低范式标准，适当保留冗余数据。具体做法是： 在概念数据模型设计时遵守第三范式，降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段，减少了查询时的关联，提高查询效率。

上一页串口CRC校验下一页Windows下MySQL安装流程

最后更新于

这有帮助吗？