【数据库】MySQL基本查询

1.单行插入

insert into students values (100, 10000, '小红', NULL);

2.多行插入

insert into students (id, sn, name) VALUES
(102, 20001, '小文'),
(103, 20002, '小红');

3.替换

• 主键或者唯一键没有冲突，则直接插入
• 主键或者唯一键如果冲突，则删除后再插入

replace into students (sn,name) values (1000,'小明');

4.全列查询

select * from exam_result;

5.指定列查询

select id,name from exam_result;

6.查询字段为表达式

SELECT id, name, math+10 FROM exam_result;

7.结果去重

select distinct math from exam_result;

8.模糊查询

• % 匹配任意多个（包括 0 个）任意字符
•  _ 匹配严格的一个任意字符

SELECT name FROM exam_result WHERE name LIKE '孙%';//姓孙的同学
SELECT name FROM exam_result WHERE name LIKE '孙_';//孙某同学

9.结果排序

SELECT name, qq FROM students ORDER BY qq DESC;

10.一次更新一个列

UPDATE exam_result SET math = 80 WHERE name = '孙悟空';//将孙悟空同学的数学成绩变更为 80 分

11.一次更新多个列

UPDATE exam_result SET math = 60, English = 70 WHERE name = '小红';//将小红同学的数学成绩变更为 60 分，英语成绩变更为 70 分

12.筛选结果分页

//起始下标为 0

SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;//从 0 开始，筛选 n 条结果

SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n;//从 s 开始，筛选 n 条结果

SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET n;//从 s 开始，筛选 n 条结果，比第二种用法更明确，建议使用

如果某一页不足n个，有几个，显示几个：

SELECT id, name, shuxue, yingyu, yuwen FROM exam_result ORDER BY id LIMIT 3 OFFSET 6;

13.删除数据

DELETE FROM exam_result WHERE name = '孙文';

14.分组查询

select avg(sal),min(sal),job, deptno from EMP group by deptno, job;//显示每个部门的每种岗位的平均工资和最低工资

15.删除表中的重复项

DELETE FROM table_a 
WHERE id IN 
(SELECT id FROM (SELECT id FROM table_a GROUP BY c_1,c_2 HAVING count(*) > 1)e);