表的增删查改有CRUD四类操作 : Create(创建),Retrieve(读取),Update(更新),Delete(删除)
1. Create(创建)
语法:
INSERT [INTO] table_name [(column,[column]...)] VALUES (value_list) [(value_list)]...;
先创建一张学生表作为示例:
CREATE TABLE students(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
sn INT NOT NULL UNIQUE COMMENT '学号',
name VARCHAR(20) NOT NULL,
qq VARCHAR(20)
);
1.1 单行数据的全列插入
INSERT INTO students VALUES (100,10000,'孙悟空',111111);
1.2 多行数据的指定列插入
插入多条记录,value_list数量必须和指定列数量及顺序一致。
INSERT INTO students (id,sn,name) VALUES (102,20001,'曹孟德'), (103,20002,'孙仲谋');
1.3 插入(存在则更新)
由于主键或者唯一键对应的值已经存在而导致插入失败。
INSERT INTO students (id,sn,name) VALUES (100,10010,'唐大师');
可以选择性的进行同步更新操作语法:
INSERT ... ON DUPLICATE KEY UPDATE column=value [,column=value] ...;
INSERT INTO students (id,sn,name) VALUES (100,10010,'唐大师') ON DUPLICATE KEY UPDATE sn=10010,name='唐大师';
1.4 替换
主键或者唯一键没有冲突,则直接插入; 主键或者唯一键如果冲突,则删除后再插入。
语法:
REPLACE [INTO] table_name [(column,[column]...)] VALUES (value_list) [(value_list)]...;
REPLACE INTO students (id,sn,name) VALUES (100,20030,'曹阿瞒');
REPLACE INTO students (id,sn,name) VALUES (110,20040,'阿瑞斯');
2. Retrieve
语法:
SELECT [DISTINCT] { * | {column [,column] ...} [FROM table_name] [WHERE...]
[ORDER BY column [ASC | DESC], ...] LIMIT ...
示例:
创建一张表:
CREATE TABLE exam_result(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL COMMENT '同学姓名',
chinese float DEFAULT 0.0 COMMENT '语文成绩',
math float DEFAULT 0.0 COMMENT '数学成绩',
english float DEFAULT 0.0 COMMENT'英语成绩'
);
插入测试数据:
INSERT INTO exam_result (name,chinese,math,english) VALUES
('唐三藏',67,98,56),('孙悟空',87,78,77),('猪悟能',88,98,90),
('曹孟德',82,84,67),('刘玄德',55,85,45),('孙权',70,73,78),('宋公明',75,65,30);
2.1 SELECT列
2.1.1 全列查询
通常情况下不建议使用*进行全列查询:
1.查询的列越多,意味着需要传输的数据量越大; 2.可能会影响到索引的使用。
SELECT * FROM exam_result;
2.1.2 指定列查询
指定列的顺序不需要按定义表的顺序来。
SELECT id,name english FROM exam_result;
2.1.3 查询字段为表达式
表达式会在每条记录后进行拼接。
1.表达式不包含字段
SELECT id,name,10 FROM exam_result;
2.表达式包含一个字段
SELECT id,name,english+10 FROM exam_result;
3.表达式包含多个字段
SELECT id,name,chinese+math+english FROM exam_result;
2.1.4 为查询结果指定别名
语法:
SELECT column [AS] alias_name [...] FROM table_name;
SELECT id,name,chinese+math+english as 总分 FROM exam_result;
2.1.5 结果去重
SELECT math FROM exam_result; SELECT DISTINCT math FROM exam_result;
2.2 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) |
示例:
1. >, >=, <, <=,=,<=>,!=,<>
2. BETWEEN
3. IN
4. AND,OR
2.3 结果排序
ASC为升序(从小到大);DESC为降序(从大到小)--默认为ASC
语法:
SELECT ... FROM table_name [WHERE...] ORDERBY column [ASC | DESC],[...]; 注意:没有ORDER BY子句的查询,返回的顺序是未定义的,永远不要依赖这个顺序.
示例:
2.4 筛选分页结果
语法:
起始下标为0
从0开始,筛选n条结果SELECT ... FROM table_name [WHERE...] [ORDERBY...] LIMIT n;
从s开始,筛选n条结果
SELECT ... FROM table_name [WHERE...] [ORDERBY...] LIMIT s,n;从s开始,筛选n条结果,比第二种用法更明确,建议使用
SELECT ... FROM table_name [WHERE...] [ORDERBY...] LIMIT n OFFSET s;
建议:对未知表进行查询时,最好加一条LIMIT 1,避免因为表中数据过大,查询全表数据导致数据库卡死。
示例:
3. Update
语法:
UPDATE table_name SET column=expr [,column=expr [WHERE...] [ORDERBY...] [LIMIT...]
示例:
将孙悟空同学的数学成绩变更为80分
将所有同学的语文成绩更新为原来的2倍
4. Delete
语法:
DELETE FROM table_name [WHERE...] [ORDERBY...] [LIMIT...];
4.1 删除数据
示例:
删除孙悟空同学的考试成绩
删除整张表数据
注意:删除整表操作要慎用!
4.2 截断表
语法:
TRUNCATE [TABLE] table_name;
注意:这个操作慎用
- 只能对整表操作,不能像DELETE一样针对部分数据操作;
- 实际上MySQL不对数据操作,所以比DELETE更快,但是TRUNCATE在删除数据的时候,并不经过真正的事物,所以无法回滚
- 会重置AUTO_INCREMENT项
示例:
准备测试表:
CREATE TABLE for_truncate(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
);
插入数据:
INSERT INTO for_truncate (name) VALUES ('A'),('B'),('C');
查看表结构:
SHOW CREATE TABLE for_truncate\G
截断整表数据
TRUNCATE for_truncate;
在插入一条数据
由此可见TRUNCATE截断表后AUTO_INCREMENT会重置,而Delete不会重置AUTO_INCREMENT。
5. 插入查询结果
语法:
INSERT INTO table_name [(column[,column...])] SELECT...;
示例:删除表中的的重复复记录,重复的数据只能有一份
创建一张表:
CREATE TABLE duplicate_table (id int,name varchar(20));
插入数据:
INSERT INTO duplicate_table VALUES
(100,'aaa'),(100,'aaa'),
(200,'bbb'),(200,'bbb'),
(200,'bbb'),(300,'ccc');
创建一张结构和duplicate_table一样的空表:
CREATE TABLE no_duplicate_table (id int,name varchar(20));
将duplicate_table的去重数据插入到no_duplicate_table:
INSERT INTO no_duplicate_table SELECT DISTINCT * FROM duplicate_table;
6. 聚合函数
函数 | 说明 |
COUNT([DISTINCT] expr) | 返回查询到的数据的数量 |
SUM([DISTINCT] expr) | 返回查询到的数据的总和,不是数字没有意义 |
AVG([DISTINCT] expr) | 返回查询到的数据的平均值,不是数字没有意义 |
MAX([DISTINCT] expr) | 返回查询到的数据的最大值,不是数字没有意义 |
MIN([DISTINCT] expr) | 返回查询到的数据的最小值,不是数字没有意义 |
6.1 COUNT
6.2 SUM
6.3 AVG
6.4 MAX
6.5 MIN
7. group by
在select中使用group by子句可以对指定列进行分组查询
语法:
select column1,column2, ... from table group by column;
示例:
创建一张表:
CREATE TABLE stu_class (id int,name varchar(20),
chinese int,math int,class int);
插入数据:
INSERT INTO stu_class (id,name,chinese,math,class) VALUES
(1,'唐三藏',67,98,10),(2,'孙悟空',87,78,10),
(3,'猪悟能',88,98,20),(4,'曹孟德',82,84,20),
(5,'刘玄德',55,85,30),(6,'孙权',70,73,30),(7,'宋公明',75,65,40);
having和group by配合使用,对group by结果进行过滤
having经常和groupby搭配使用,作用是对分组进行筛选,作用有些像where。