文章目录
CRUD : Create(创建), Retrieve(读取),Update(更新),Delete(删除)
一、表的创建
-- 创建一张学生表
CREATE TABLE students (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
sn INT NOT NULL UNIQUE COMMENT '学号',
name VARCHAR(20) NOT NULL,
qq VARCHAR(20)
);
插入数据的语法:
INSERT [INTO] table_name[(column [, column] ...)]
VALUES (value_list) [, (value_list)] ...
value_list: value, [, value] ...
1.1 全列插入
全列插入是指,一次插入一行,每列都赋值。
mysql> insert into students values(10,1234,'zhangsan',22222);
Query OK, 1 row affected (0.00 sec)
1.2 指定列插入
给出指定插入的列名和插入的数据。
insert into students(sn, name, qq) values(12345,'lisi',33333);
Query OK, 1 row affected (0.01 sec)
mysql> select * from students ;
+----+-------+----------+-------+
| id | sn | name | qq |
+----+-------+----------+-------+
| 10 | 1234 | zhangsan | 22222 |
| 11 | 12345 | lisi | 33333 |
+----+-------+----------+-------+
2 rows in set (0.00 sec)
id是自增的。
1.3 多行插入
多行插入在values后面每行用逗号分隔。
mysql> insert into students values(12, 123456, 'aaa', 1213), (13, 1234567, 'bbb', 123321);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from students;
+----+---------+----------+--------+
| id | sn | name | qq |
+----+---------+----------+--------+
| 10 | 1234 | zhangsan | 22222 |
| 11 | 12345 | lisi | 33333 |
| 12 | 123456 | aaa | 1213 |
| 13 | 1234567 | bbb | 123321 |
+----+---------+----------+--------+
4 rows in set (0.00 sec)
1.4 更新含key行的其他数据
由于 主键 或者 唯一键 对应的值已经存在而导致插入失败。
可以选择性的进行同步更新操作。
语法:
INSERT ... ON DUPLICATE KEY UPDATE
column = value [, column = value] ...
如果冲突,就把要替换的列的值写在后面。
mysql> select * from students;
+----+---------+----------+--------+
| id | sn | name | qq |
+----+---------+----------+--------+
| 10 | 1234 | zhangsan | 22222 |
| 11 | 12345 | lisi | 33333 |
| 12 | 123456 | aaa | 1213 |
| 13 | 1234567 | bbb | 123321 |
+----+---------+----------+--------+
4 rows in set (0.00 sec)
mysql> insert into students(sn, name, qq) values(123456, 'ccc', 44444) on duplicate key update sn=123456, name = 'ccc';
Query OK, 2 rows affected (0.01 sec)
mysql> select * from students;
+----+---------+----------+--------+
| id | sn | name | qq |
+----+---------+----------+--------+
| 10 | 1234 | zhangsan | 22222 |
| 11 | 12345 | lisi | 33333 |
| 12 | 123456 | ccc | 1213 |
| 13 | 1234567 | bbb | 123321 |
+----+---------+----------+--------+
4 rows in set (0.00 sec)
上面只替换了名字,没有替换qq,因为后面没有写。
对表执行操作后,都会提示多少行被影响。例如上面这个例子:
-- 0 row affected: 表中有冲突数据,但冲突数据的值和 update 的值相等
-- 1 row affected: 表中没有冲突数据,数据被插入
-- 2 row affected: 表中有冲突数据,并且数据已经被更新
1.5 替换
替换使用关键字replace
,其他和插入的用法相同。
-- 主键 或者 唯一键 没有冲突,则直接插入;
-- 主键 或者 唯一键 如果冲突,则删除后再插入
REPLACE INTO students (sn, name) VALUES (123456, 'ddd');
Query OK, 2 rows affected (0.00 sec)
-- 1 row affected: 表中没有冲突数据,数据被插入
-- 2 row affected: 表中有冲突数据,删除后重新插入
二、表的读取
查询是使用最为频繁的,规则也是最多的。
语法:
--- 估计的学完后面的具体使用才能看懂这个语法
SELECT [DISTINCT] {* | {column [, column] ...} [FROM table_name] [WHERE ...]
[ORDER BY column [ASC | DESC], ...] LIMIT ...
2.1 全列查询
select *from 表名
;‘ * '表示当前表的所有列。一般情况不这样查询,用后面的各种查询方法。
对于字段比较少,并且插入数据比较少的表,可以使用全列查询。如果一个表中字段比较多,或者数据量较大,则全列查询会很慢。
2.2 指定列查询
指定列的顺序不需要按定义表的顺序来。
mysql> select id, name, chinese from exam_result;
+----+-----------+---------+
| id | name | chinese |
+----+-----------+---------+
| 1 | 唐三藏 | 67 |
| 2 | 孙悟空 | 87 |
| 3 | 猪悟能 | 88 |
| 4 | 曹孟德 | 82 |
| 5 | 刘玄德 | 55 |
| 6 | 孙权 | 70 |
| 7 | 宋公明 | 75 |
+----+-----------+---------+
7 rows in set (0.00 sec)
2.3 查询字段为表达式
表达式是任意可以计算,有含义的式子。
例如:查询每个人的总分。
mysql> select name, chinese + math + english from exam_result;
+-----------+--------------------------+
| name | chinese + math + english |
+-----------+--------------------------+
| 唐三藏 | 221 |
| 孙悟空 | 242 |
| 猪悟能 | 276 |
| 曹孟德 | 233 |
| 刘玄德 | 185 |
| 孙权 | 221 |
| 宋公明 | 170 |
+-----------+--------------------------+
7 rows in set (0.00 sec)
当表达式太常,后续查询还有使用的时候,可以为某个表达式去别名。在查询的时候,可以为任意字段或者表名取别名,别名只在本查询语句有效。
语法:表达式 [as] 别名
as 可以省略
mysql> select name, chinese + math + english as 总分 from exam_result;
+-----------+--------+
| name | 总分 |
+-----------+--------+
| 唐三藏 | 221 |
| 孙悟空 | 242 |
| 猪悟能 | 276 |
| 曹孟德 | 233 |
| 刘玄德 | 185 |
| 孙权 | 221 |
| 宋公明 | 170 |
+-----------+--------+
7 rows in set (0.00 sec)
2.4 结果去重
使用关键字distinct
对结果去重。
mysql> select name, chinese + math + english as 总分 from exam_result;
+-----------+--------+
| name | 总分 |
+-----------+--------+
| 唐三藏 | 221 |
| 孙悟空 | 242 |
| 猪悟能 | 276 |
| 曹孟德 | 233 |
| 刘玄德 | 185 |
| 孙权 | 221 |
| 宋公明 | 170 |
+-----------+--------+
7 rows in set (0.00 sec)
mysql> select distinct chinese + math + english as 总分 from exam_result;
+--------+
| 总分 |
+--------+
| 221 |
| 242 |
| 276 |
| 233 |
| 185 |
| 170 |
+--------+
6 rows in set (0.00 sec)
2.5 where条件查询
可以使用下列运算符筛选。
比较运算符:
运算符 | 说明 |
---|---|
>, >=, <, <= | 大于,大于等于,小于,小于等于 |
= | 等于,NULL 不安全,例如 NULL = NULL 的结果是 NULL |
<=> | 等于,NULL 安全,例如 NULL <=> NULL 的结果是 TRUE(1) |
!=, <> | 不等于 |
BETWEEN a0 AND a1 | 范围匹配,[a0, a1],如果 a0 <= value <= a1,返回 TRUE(1) |
IN (option, …) | 如果是 option 中的任意一个,返回 TRUE(1) |
IS NULL | 是 NULL |
IS NOT NULL | 不是 NULL |
LIKE | 模糊匹配。% 表示任意多个(包括 0 个)任意字符;_ 表示任意一个字符 |
逻辑运算符:
运算符 | 说明 |
---|---|
AND | 多个条件必须都为 TRUE(1),结果才是 TRUE(1) |
OR | 任意一个条件为 TRUE(1), 结果为 TRUE(1) |
NOT | 条件为 TRUE(1),结果为 FALSE(0) |
例如:
mysql> select name, chinese from exam_result where chinese < 60;
+-----------+---------+
| name | chinese |
+-----------+---------+
| 刘玄德 | 55 |
+-----------+---------+
1 row in set (0.00 sec)
mysql> select name, math, english from exam_result where math > 80 and english > 70;
+-----------+------+---------+
| name | math | english |
+-----------+------+---------+
| 猪悟能 | 98 | 90 |
+-----------+------+---------+
1 row in set (0.00 sec)
- 从下面这个例子可以看到,在显示字段的地方定义的别名,在where阶段是不知道。说明where条件语句是在先执行。这也符合正常现象,需要显示的内容是筛选之后的,所以先从表里面拿出数据筛选,然后再显示。
mysql> select name, math + english + chinese as 总分 from exam_result where 总分 > 200;
ERROR 1054 (42S22): Unknown column '总分' in 'where clause'
mysql> select name, math + english + chinese as 总分 from exam_result where math + english + chinese > 200;
+-----------+--------+
| name | 总分 |
+-----------+--------+
| 唐三藏 | 221 |
| 孙悟空 | 242 |
| 猪悟能 | 276 |
| 曹孟德 | 233 |
| 孙权 | 221 |
+-----------+--------+
5 rows in set (0.00 sec)
判断空尽量使用is null
和is not null
。
--- 显示姓孙的人
mysql> select name, math from exam_result where name like '孙%';
+-----------+------+
| name | math |
+-----------+------+
| 孙悟空 | 78 |
| 孙权 | 73 |
+-----------+------+
2 rows in set (0.00 sec)
2.6 结果排序
将查询到的结果排序。
注意:没有 ORDER BY 子句的查询,返回的顺序是未定义的,永远不要依赖这个顺序
语法:
-- ASC 为升序(从小到大)
-- DESC 为降序(从大到小)
-- 默认为 ASC
SELECT ... FROM table_name [WHERE ...]
ORDER BY column [ASC|DESC], [...];
例如:
mysql> select name, math from exam_result order by math; --- 默认按照升序
+-----------+------+
| name | math |
+-----------+------+
| 宋公明 | 65 |
| 孙权 | 73 |
| 孙悟空 | 78 |
| 曹孟德 | 84 |
| 刘玄德 | 85 |
| 唐三藏 | 98 |
| 猪悟能 | 98 |
+-----------+------+
7 rows in set (0.00 sec)
NULL 视为比任何值都小,升序出现在最上面,降序在最下面。
- 可以看到,在排序的时候可以使用别名,说明数据已经出来了,然后再排序。
mysql> select name, math + english + chinese as 总分 from exam_result order by 总分;
+-----------+--------+
| name | 总分 |
+-----------+--------+
| 宋公明 | 170 |
| 刘玄德 | 185 |
| 唐三藏 | 221 |
| 孙权 | 221 |
| 曹孟德 | 233 |
| 孙悟空 | 242 |
| 猪悟能 | 276 |
+-----------+--------+
7 rows in set (0.00 sec)
2.7 分页查询
语法:
-- 起始下标为 0
-- 从 0 开始,筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;
-- 从 s 开始,筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n;
-- 从 s 开始,筛选 n 条结果,比第二种用法更明确,建议使用
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;
注意第三种使用方法的n和s的位置。
mysql> select name, math + english + chinese as 总分 from exam_result order by 总分 limit 4 offset 2 ;
+-----------+--------+
| name | 总分 |
+-----------+--------+
| 唐三藏 | 221 |
| 孙权 | 221 |
| 曹孟德 | 233 |
| 孙悟空 | 242 |
+-----------+--------+
4 rows in set (0.00 sec)
建议:对未知表进行查询时,最好加一条 LIMIT 1,避免因为表中数据过大,查询全表数据导致数据库卡死
三、表的更新
对查询到的结果进行列值更新。
语法:
UPDATE table_name SET column = expr [, column = expr ...]
[WHERE ...] [ORDER BY ...] [LIMIT ...]
例如:将孙悟空的数学成绩改为87
mysql> select name, math from exam_result where name = '孙悟空';
+-----------+------+
| name | math |
+-----------+------+
| 孙悟空 | 78 |
+-----------+------+
1 row in set (0.00 sec)
mysql> update exam_result set math = 87 where name = '孙悟空';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select name, math from exam_result where name = '孙悟空';
+-----------+------+
| name | math |
+-----------+------+
| 孙悟空 | 87 |
+-----------+------+
1 row in set (0.00 sec)
同样可以更新查询结果的多列的值。
四、表的删除
4.1 删除数据
语法:
DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]
-- 删除查询结果的数据,按行删除
DELETE FROM exam_result WHERE name = '孙悟空';
-- 删除整表数据
DELETE FROM table_name;
- 这种删除,不会将自增值归零。
4.2 截断表
注意:这个操作慎用
语法:
TRUNCATE [TABLE] table_name
- 只能对整表操作,不能像 DELETE 一样针对部分数据操作;
- 实际上 MySQL 不对数据操作,所以比 DELETE 更快,但是TRUNCATE在删除数据的时候,并不经过真正的事物,所以无法回滚。
- 会重置 AUTO_INCREMENT 项。
-- 截断整表数据,注意影响行数是 0,所以实际上没有对数据真正操作
TRUNCATE for_truncate;
Query OK, 0 rows affected (0.10 sec)
五、插入查询结果
其实就是把查询结果作为插入的值。不需要写values。
语法:
INSERT INTO table_name [(column [, column ...])] SELECT ...
案例:删除表中的的重复复记录,重复的数据只能有一份
-- 创建原数据表
mysql> create table duplicate_table (id int, name varchar(10));
Query OK, 0 rows affected (0.03 sec)
-- 插入测试数据
mysql> insert into duplicate_table values
-> (100, 'aaa'),
-> (100, 'aaa'),
-> (200, 'bbb'),
-> (200, 'bbb'),
-> (200, 'bbb'),
-> (300, 'ccc');
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0
-- 创建一张空表 no_duplicate_table,结构和 duplicate_table 一样
-- mysql> create table no_duplicate_table (id int, name varchar(10));
mysql> create table no_duplicate_table like duplicate_table; -- 效果和上面的一样
Query OK, 0 rows affected (0.03 sec)
-- 将查询到的不重复数据,插入新表
mysql> insert into no_duplicate_table select distinct id, name from duplicate_table;
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
-- 对两张表重命名即可得到不重复数据的表
mysql> rename table duplicate_table to old_duplicate_table,
-> no_duplicate_table to duplicate_table;
Query OK, 0 rows affected (0.05 sec)
mysql> select * from duplicate_table;
+------+------+
| id | name |
+------+------+
| 100 | aaa |
| 200 | bbb |
| 300 | ccc |
+------+------+
3 rows in set (0.00 sec)
六、聚合函数
函数 | 说明 |
---|---|
COUNT([DISTINCT] expr) | 返回查询到的数据的 数量 |
SUM([DISTINCT] expr) | 返回查询到的数据的 总和,不是数字没有意义 |
AVG([DISTINCT] expr) | 返回查询到的数据的 平均值,不是数字没有意义 |
MAX([DISTINCT] expr) | 返回查询到的数据的 最大值,不是数字没有意义 |
MIN([DISTINCT] expr) | 返回查询到的数据的 最小值,不是数字没有意义 |
聚合函数括号里面的内容应该是查询结果里面有的。
count 可以统计null行。
例如:
统计表的总行数
mysql> select count(*) from exam_result;
+----------+
| count(*) |
+----------+
| 7 |
+----------+
1 row in set (0.00 sec)
mysql> select max(math) from exam_result;
+-----------+
| max(math) |
+-----------+
| 98 |
+-----------+
1 row in set (0.00 sec)
七、group by子句
在select中使用group by 子句可以对指定列进行分组查询
语法:
select column1, column2, .. from table group by column;
- having和group by配合使用,对group by结果进行过滤
having经常和group by搭配使用,作用是对分组进行筛选,作用有些像where。
现在有这样一张表:
mysql> select * from emp limit 1;
+--------+-------+-------+------+---------------------+--------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+--------+-------+-------+------+---------------------+--------+------+--------+
| 007369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 |
+--------+-------+-------+------+---------------------+--------+------+--------+
1 row in set (0.00 sec)
- 显示每个部门的每种岗位的平均工资和最低工资
先根据部门号分组,再根据岗位分组,然后计算出每个部门每个岗位聚合值。
mysql> select deptno, job, avg(sal), min(sal) from emp group by deptno, job;
+--------+-----------+-------------+----------+
| deptno | job | avg(sal) | min(sal) |
+--------+-----------+-------------+----------+
| 10 | CLERK | 1300.000000 | 1300.00 |
| 10 | MANAGER | 2450.000000 | 2450.00 |
| 10 | PRESIDENT | 5000.000000 | 5000.00 |
| 20 | ANALYST | 3000.000000 | 3000.00 |
| 20 | CLERK | 950.000000 | 800.00 |
| 20 | MANAGER | 2975.000000 | 2975.00 |
| 30 | CLERK | 950.000000 | 950.00 |
| 30 | MANAGER | 2850.000000 | 2850.00 |
| 30 | SALESMAN | 1400.000000 | 1250.00 |
+--------+-----------+-------------+----------+
9 rows in set (0.00 sec)
- 显示平均工资低于2000的部门和它的平均工资
统计各个部门的平均工资
select avg(sal) from EMP group by deptno
使用having对分组结果过滤(条件筛选)
mysql> select deptno,avg(sal) from emp group by deptno having avg(sal) < 2000;
+--------+-------------+
| deptno | avg(sal) |
+--------+-------------+
| 30 | 1566.666667 |
+--------+-------------+
1 row in set (0.00 sec)
总结下来,语句的执行顺序
SQL查询中各个关键字的执行先后顺序 from > on > join > where > group by > with > having > select > distinct > order by > limit
不用刻意去背,可以联想实际筛查的顺序。