数据库之基本查询

1 Create

       先创建一张学生表

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 students values(100,10000,'唐僧',null);
insert into students values(101,10001,'孙悟空','545454');

• 多行数据+指定列插入

insert into students(id,sn,name) values
(102,20001,'诸葛亮'),
(103,20002,'周瑜');
select * from students;

• 插入是否更新
         由于主键或者唯一键对应的值已经存在而导致插入失败，可以选择性的进行同步更新操作：

insert into students(id,sn,name) values(100,10010,'唐三藏') on duplicate key update sn = 10010, name = '唐三藏';

• 替换
         主键或唯一键没有冲突，直接插入
         主键或唯一键冲突，则删除后重新插入

replace into students(sn,name) values(10001,'孙行者');

id变成104，id为101的数据已经不在了（被删除了）。

2. Retrieve

       先创建表结构

CREATE TABLE exam_result (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL COMMENT '同学姓名',
yuwen float DEFAULT 0.0 COMMENT '语文成绩',
shuxue float DEFAULT 0.0 COMMENT '数学成绩',
yingyu float DEFAULT 0.0 COMMENT '英语成绩' 
);

       插入测试数据

INSERT INTO exam_result (name, yuwen, shuxue, yingyu) VALUES
('唐三藏', 67, 98, 56),
('孙悟空', 87, 78, 77),
('猪悟能', 88, 98, 90),
('曹孟德', 82, 84, 67),
('刘玄德', 55, 85, 45),
('孙权', 70, 73, 78),
('宋公明', 75, 65, 30);

• 全列查询

select * from exam_result;

• 指定列查询

select id,name,yingyu from exam_result;

• 查询字段为表达式
         表达式不包含字段

select id,name,10 from exam_result;

              表达式包含一个字段

select id,name,yingyu+10 from exam_result;

              表达式包含多个字段

select id,name,yuwen+shuxue+yingyu from exam_result;

• 为查询结果指定别名

select id,name,yuwen+shuxue+yingyu 总分 from exam_result;

• 结果去重

select distinct shuxue from exam_result;

• while条件
        比较运算符
  
        逻辑运算符
  
        数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩

select name shuxue from exam_result
where shuxue = 58
or shuxue = 59
or shuxue = 98
or shuxue = 99;

select name,shuxue from exam_result where shuxue in(58,59,98,99);

      姓孙的同学及孙某同学

---  %  匹配任意多个（包括0个）任意字符
select name from exam_result where name like '孙%';
---  _  匹配严格的一个任意字符
select name from exam_result where name like '孙__';

      总分在200分以下的同学，where条件中使用表达式，别名不能用于where条件中。

select name,yuwen+shuxue+yingyu 总分 from exam_result where yuwen+shuxue+yingyu < 200;

      语文成绩>80并且不姓孙的同学

select name,yuwen from exam_result where yuwen>80 and name not like'孙%';

• NULL的查询
  查询qq号已知的同学姓名

select name,qq from students where qq is not null;

• 结果排序
  按数学成绩升序显示

select name,shuxue from exam_result order by shuxue;

null视为比任何值都小，升序出现在最上面
     查询同学各门成绩，依次按数学降序，英语升序，语文升序

select shuxue,yingyu,yuwen from exam_result order by shuxue desc, yingyu, yuwen;

     查询总分，由高到低，order by子句中可以使用别名，表达式

select yuwen+shuxue+yingyu 总分 from exam_result order by yuwen+shuxue+yingyu desc;

     查询姓孙的同学或者姓曹的同学，按数学成绩降序显示。

select name,shuxue from exam_result where name like'孙%' or name like'曹%' order by shuxue desc;

• 筛选分页结果
  – 起始下标为 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;

select * from exam_result order by id limit 3 offset 0;

    如果不足三个也没有影响。

3. Update

        更新值为具体值

update exam_result set shuxue = 80 where name = '孙悟空';   -----------------------------------------悟空的语文改成80
update exam_result set shuxue = 60, yuwen = 70 where name = '曹孟德';--------------------------------一次修改多个数据
update exam_result set shuxue = shuxue + 30 order by yuwen+shuxue+yingyu limit 3;-------------------将总成绩倒数前三的3位同学的数学加上30分
update exam_result set yuwen = yuwen*2;-------------------------------------------------------------将所有语文成绩*2，

4. Delete

• 删除数据

delete from table_name [where....] [order by ...] [limit ...]

       删除孙悟空的考试成绩

delete from exam_result where name='孙悟空';

       删除整张表的数据，慎用

delete from students;

如果表中有auto_incement，这个值不会从头开始再来。

show crate table exam_result;------可以看到AUTO_INCEMENT=n项
- 截断表
**慎用**
只对整表操作，不能像delete一样针对部分数据操作
实际上 MySQL 不对数据操作，所以比 DELETE 更快 
会重置 AUTO_INCREMENT 项

CREATE TABLE for_truncate (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
);

INSERT INTO for_truncate (name) VALUES (‘A’), (‘B’), (‘C’);
TRUNCATE for_truncate;-----此时显示影响行数是0，所以实际上没有对数据真正操作

5. 插入查询结果

      创建原数据表

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'); 

      创建一张空表，结构和原数据表一样
CREATE TABLE no_duplicate_table LIKE duplicate_table;
      将 duplicate_table 的去重数据插入到 no_duplicate_table
INSERT INTO no_duplicate_table SELECT DISTINCT * FROM duplicate_table;
      通过重命名表，实现原子的去重操作
RENAME TABLE duplicate_table TO old_duplicate_table, no_duplicate_table TO duplicate_table;
       查看最终结果
SELECT * FROM duplicate_table;

6. 聚合函数

• 统计班级共用多少个学生（使用*统计，不受null影响）
  select count(*) from students;
• 统计班级收集的qq号有多少（null不会计入结果）
  select count(qq) from students;
• 统计本次考试的数学成绩分数个数
  select count(shuxue) from exam_result;
• 去重之后的数学成绩个数
  select count(distinct shuxue) from exam_result;
• 统计数学分数<60的总分，没有结果返回null
  select sum(shuxue) from exam_result where shuxue<60;

7. group by子句的使用

        在select中使用group by子句可以对指定列进行分组查询
        having和group by配合使用，对group by结果进行过滤