Mysql-CRUD（增删查改）

1.Create

INSERT [ INTO ] table_name

[( column [, column ] ...)]

VALUES (value_list) [, (value_list)] ...

 1.1 全列插入+单行数据

insert into book values('Java核心技术','Cay S.Horstman',56.43,'计算机技术');

 1.2 多行数据+指定列

insert into product(name,price,storage) values('学生书包',18.91,101)，('铅笔',20.91,155);

以上插入了两行，指定name，price，storage三列插入数据。 

注意：当数据里有主键冲突或唯一键冲突时，可以使用on duplicate key来进行更新操作。

INSERT INTO product (name,price,storage ) VALUES ('学生书包',18.91,101 )

ON DUPLICATE KEY UPDATE price = 18.91 , name = '学生书包 ' ;

-- ON DUPLICATE KEY 当发生重复 key 的时候

1.3 替换

REPLACE INTO product (name,price,storage )  VALUES ('学生书包',18.91,101 );

•  表中没有数据，数据被插入。
• 表中有冲突数据，删除数据后重新插入。

1.4 插入查询结果 

语法： 

INSERT INTO table_name [(column [, column ...])] SELECT ...  

-- 将 duplicate_table 的去重数据插入到 no_duplicate_table

INSERT INTO no_duplicate_table SELECT DISTINCT * FROM duplicate_table;

2.Retrieve 

语法：  

SELECT

[DISTINCT] {* | {column [, column] ...}

[FROM table_name]

[WHERE ...]

[ORDER BY column [ASC | DESC], ...]

LIMIT ...

2.1 全列查询 

-- 通常情况下不建议使用 * 进行全列查询

-- 1. 查询的列越多，意味着需要传输的数据量越大；

-- 2. 可能会影响到索引的使用。

SELECT * FROM table_name;  

2.2 指定列查询 

-- 指定列的顺序不需要按定义表的顺序来

SELECT id, name, english FROM exam_result; 

2.3 为指定列起别名 

语法： 

SELECT column [AS] alias_name [...] FROM table_name;  

2.4 结果去重 

 distinct：对查询结果具有去重作用。

SELECT DISTINCT math FROM exam_result; 

 2.5 where条件

比较运算符：

逻辑运算符：

2.6 结果排序 

语法：  

-- ASC 为升序（从小到大）

-- DESC 为降序（从大到小）

-- 默认为 ASC

SELECT ... FROM table_name [WHERE ...]

ORDER BY column [ASC|DESC], [...];

编写一个SQL查询，获取Employee表中第二高的薪水（Salary） 

select Salary as SecondHighestSalary
from Employee
order by Salary
desc
limit 1 
offset 1

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;  

建议：对未知表进行查询时，最好加一条 LIMIT 1，避免因为表中数据过大，查询全表数据导致数据库卡死 

 3.Update

UPDATE table_name SET column = expr [, column = expr ...]

[WHERE ...] [ORDER BY ...] [LIMIT ...]

对查询到的结果进行列值更新 

update book set price=61 where name='Java核心技术'; 

4.Delete 

语法： 

DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...] 

delete from product where price>60 or storage<200; 

 4.1 删除整表数据

DELETE FROM table_name 

 注意：

• 假如数据中有自增类型的数据，那么删除了整表的数据后，自增不会变为0，而是插入后继续增加。

4.2 截断表

TRUNCATE [TABLE] table_name  

 

5 聚合函数 

SELECT COUNT(*) FROM students;  --统计全班有多少学生

SELECT SUM(math) FROM exam_result;  --统计数学成绩总分

SELECT AVG(chinese + math + english) -- 平均总分 FROM exam_result;  --统计平均总分

SELECT MAX(english) FROM exam_result;  --返回英语最高分

SELECT MIN(math) FROM exam_result WHERE math > 70;  --返回数学大于70分的最低分

6.group by子句 

在select中使用group by 子句可以对指定列进行分组查询 

select column1, column2, .. from table group by column;  

select deptno,avg(sal),max(sal) from EMP group by deptno; --显示每个部门的平均工资和最高工资 

 7.各个关键词执行顺序

SQL查询中各个关键字的执行先后顺序 from > on> join > where > group by > with > having > select > distinct > order by > limit