MySQL数据库SQL小结

1. 创建数据库

   create database 数据库名 [其他选项];

2. 创建数据库表

   create table 表名称(列声明);
   
   ## 以创建 students 表为例, 表中将存放 学号(id)、姓名(name)、性别(sex)、年龄(age)、联系电话(tel) 这些内容:
       create table students
       （
           id int unsigned not null auto_increment primary key,
           name char(8) not null,
           sex char(4) not null,
           age tinyint unsigned not null,
           tel char(13) null default "-"
       );

3. 插入数据

   insert [into] 表名 [(列名1, 列名2, 列名3, …)] values (值1, 值2, 值3, …);
   
   ##其中 [] 内的内容是可选的, 例如, 要给 samp_db 数据库中的 students 表插入一条记录, 执行语句:
   insert into students values(NULL, "王刚", "男", 20, "13811371377");
   
   ##有时我们只需要插入部分数据, 或者不按照列的顺序进行插入, 可以使用这样的形式进行插入:
   insert into students (name, sex, age) values("孙丽华", "女", 21);

4. 单表查询

   select 列名称 from 表名称 [查询条件];    
   select 列名称 from 表名称 where 条件;
   
   ## 例如要查询 students 表中所有学生的名字和年龄, 输入语句 select name, age from students; 执行结果如下:
       mysql> select name as 姓名, age as 年龄 from students;
       +--------+-----+
       | 姓名   | 年龄 |
       +--------+-----+
       | 王刚   |  20 |
       | 孙丽华 |  21 |
       | 王永恒 |  23 |
       | 郑俊杰 |  19 |
       | 陈芳   |  22 |
       | 张伟朋 |  21 |
       +--------+-----+
       6 rows in set (0.00 sec)
   
   
   ##查询年龄在21岁以上的所有人信息: 
   select * from students where age > 21;
   
   ##查询名字中带有 "王" 字的所有人信息: 
   select * from students where name like "%王%";
   
   ##查询id小于5且年龄大于20的所有人信息: 
   select * from students where id<5 and age>20;
   
   ##消除取值重复的行
   select distinct Sno as 选修了课程的学生学号 from SC;
   
   ##确定范围，查询IS系和CS系的全体学生姓名和性别
   select Sname as 姓名,Sdept as 系别,Sage as 年龄 from student where Sage between 20 and 23;
   
   ##查询IS系和CS系的全体学生姓名和性别
   select Sname as 姓名,Ssex as 性别 from student where Sdept='IS' or Sdept='CS';
   select Sname as 姓名,Ssex as 性别 from student where Sdept in ('IS','CS');
   
   ##查询既不属于IS系，也不属于MA系的学生姓名和年龄
   select Sname as 姓名,Sage as 年龄 from student where Sdept !='IS'and Sdept!='CS';
   select Sname as 姓名,Sage as 年龄 from student where Sdept not in('IS','MA');
   
   ##涉及空值的查询（is null）
   ##查询没有先修课的课程号和课程名
   select Cno as 课程号,Cname as 课程名,Cpno from course where Cpno is null;
   
   ##查询结果排序（order by )
   ##查询选修了3号课程的学生学号和成绩，结果按成绩降序/升序排列。
   ##降序 Decending Order
   select Sno as 学号,Grade as 成绩 from SC where Cno=3 order by Grade desc;
   ##升序 Ascending order
   select Sno as 学号,Grade as 成绩 from SC where Cno=3 order by Grade asc;
   
   ##聚集函数 count、sum、avg、max、min
   ##查询学生总数
   select count(*) as 学生总数 from student;
   ##查询所有课程的总学分
   select sum(Ccredit) as 所有课程总学分 from course;
   ##查询全体学生平均年龄
   select avg(Sage) as 平均年龄 from student;
   ##查询1号课程的最高分
   select max(Grade) as 1号课程的最高分 from SC where Cno=1;
   
   ##分组统计（group by）
   ##查询男女学生各有多少人
   select Ssex as 性别,count(*) as 人数 from student group by Ssex;
   ##查询每个课程的课程号和平均分。
   select Cno as 课程号,avg(Grade) as 平均分 from SC group by Cno;

   having 关键字后面直接跟聚集函数
   在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与合计函数一起使用。

   SELECT column_name, aggregate_function(column_name)
   FROM table_name
   WHERE column_name operator value
   GROUP BY column_name
   HAVING aggregate_function(column_name) operator value

   单表复杂实例：

   ##查询选修了2门课程以上（含2门，但不含1号课程）,学生学号和选修课程数。
   select Sno as 学号, count(course.Cno) as 选修课程数
   From SC where course.Cno = SC.Cno and course.Cno != 1
   Group by Sno
   Having Count(course.Cno)>=2;
   
   ##查询不及格门数2门以上的学生学号。
   select Sno as 学号
   From SC
   Where sc.Grade < 60
   Group by Sno
   Having count(Cno) > 2
   
   ##查询有2名以上（含2名）学生选修了的课程号和选修人数。
   select Cno as 课程号, count(Sno)
   From SC
   Group by Cno
   Having count(Sno) >= 2

5. 多表连接查询

   等值与非等值连接查询：

   ##查询每个学生及其的选修课程情况
   select student.Sno as 学号,course.Cno as 选修课号,SC.Grade as 成绩 
   from student,course,SC 
   where student.Sno=SC.Sno and course.Cno=SC.Cno ;

   自身连接：

   ##查询每个学生的间接选修课
   select SC.Sno as 学号,
   FIRST.Cname as 直接选修课,
   SECOND.Cname as 间接选修课
   from SC,
   course as FIRST,
   course as SECOND
   where FIRST.Cno=SC.Cno
   and FIRST.Cpno=SECOND.Cno;

   外连接

   ##查询所有学生选修课程情况（含没选修课程的学生）
   select student.Sno as 学号,
   Sname as 姓名,
   sc.Cno as 选修课程号
   from student 
   LEFT OUTER JOIN SC ON student.Sno=SC.Sno;

   JOIN 用于根据两个或多个表中的列之间的关系，从这些表中查询数据:

   JOIN: 如果表中有至少一个匹配，则返回行
   LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行
   RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行
   FULL JOIN: 只要其中一个表中存在匹配，就返回行

   注意：

   UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
   请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。

6. 嵌套查询
   带有IN谓词的子查询（ 属性 in （子查询的查询结果） ）

   ##查询与王敏同学在同一个系的学生信息。
   select *
   from student
   where Sdept in (
    select Sdept
    from student
    where Sname='王敏'
   );
   
   ##查询不与王敏同学不在同一个系的学生信息。
   select *
   from student
   where Sdept not in (
    select Sdept
    from student
     whereSname='王敏'
   );
   
   ##查询选修了课程名是“信息系统”的学生学号和姓名。
   select student.Sno as 学号, Sname as 姓名
   from student,SC
   where student.Sno=SC.Sno and Cno in (
    select Cno
    from course
    where Cname='信息系统'
   )
   
   ##查询曾与刘晨一同上课的学生学号和姓名。（假设：一个课程只有一个上课班）
   select distinct student.Sno as 学号, Sname as 姓名
   from student,SC
   where student.Sno=SC.Sno and Cno in (
    select Cno
    from SC,student
    where SC.Sno=student.Sno and student.Sno in (
      select Sno
      from student
      where student.Sname='刘晨'
     )
   )

   带有比较运算符的子查询（=，>=，<=，<>或!=）

   ##查询与王敏同学在同一个系的所有学生信息 （=判断）
   select *
   from student
   where Sdept=(
      select Sdept
      from student
      where Sname='王敏'
   )
   
   ##查询每个学生超过该课程最低分的课程号。（同类课程不是最低分的），子查询的结果返回一个数的时候，这个子查询就可以当一个数用？可以使用in符号，或者大于小于符号。
   select Cno
   from SC a
   where Grade> (
    select min(Grade)
    from SC b
    where a.Cno=b.Cno
   )
   
   ##查询每个学生超过他选修课程平均成绩的课程号。
   select Cno
   from SC a
   where Grade> (
    select avg(Grade)
    from SC b
    where a.Sno=b.Sno
   )

   带有ANY或ALL谓词的子查询：
   ANY表示任何一个，ALL表示所有，可以用在子查询的括号前面

   ##查询其他系中比计算机系某一学生年龄小的学生姓名，性别、年龄和所在系。
   select Sname as 姓名,Ssex as 性别, Sage as 年龄, Sdept as 所在系
   from student
   where Sage <(
    select Sage
    from student
    where Sdept='CS'
   );
   
   ##查询其他系中比计算机系所有年龄都小的学生姓名和年龄。
   select Sname as 姓名, Sage as 年龄
   from student
   where Sdept<>'CS' and  Sage <ALL (
    select Sage
    from student
    where Sdept='CS'
   );

   带有Exists谓词的子查询：

   ##查询所有选修了1号课程的学生姓名。
   select Sname as 姓名
   from student
   where Exists (
    select *
    from SC
    where Cno=1 and Sno=Student.Sno
   );

7. 集合查询
   并 UNION

   ##查询计算机系的学生及年龄不大于19岁的学生详细信息。
   select *
   from student
   where student.Sdept='CS'
   union
   select *
   from student
   where student.Sage<=19;

   交 INTERSECT

   ##查询选修了1号课程的与年龄不大于19岁的 学生 详细信息 的交集。
   Select *
   from student,SC
   where student.Sno=SC.Sno and SC.Cno=1
   INTERSECT
   Select *
   from student
   where student.Sage<=19;

   差 EXCEPT

   ##查询计算机科学系的学生与年龄不大于19岁的学生详细信息的差集。
   select *
   from student
   where student.Sdept='SC'
   EXCEPT
   select *
   from student
   where student.Sage<=19;

8. 更新数据

   update 表名称 set 列名称=新值 where 更新条件;
   
   ##将id为5的手机号改为默认的"-": 
   update students set tel=default where id=5;
   
   ##将所有人的年龄增加1: 
   update students set age=age+1;
   
   ##将手机号为 13288097888 的姓名改为 "张伟鹏", 年龄改为 19: 
   update students set name="张伟鹏", age=19 where tel="13288097888";
   

9. 删除数据

   delete from 表名称 where 删除条件;
   
   ##删除id为2的行: 
   delete from students where id=2;
   
   ##删除所有年龄小于21岁的数据: 
   delete from students where age<20;
   
   ##删除表中的所有数据: 
   delete from students;
   

10. 修改创建后的表

    alter table 语句用于创建后对表的修改, 基础用法如下:
    添加列:

    ##基本形式: 
    alter table 表名 add 列名 列数据类型 [after 插入位置];
    
    ##在表的最后追加列 address: 
    alter table students add address char(60);
    
    ##在名为 age 的列后插入列 birthday: 
    alter table students add birthday date after age;

    修改列

    ##基本形式: 
    alter table 表名 change 列名称 列新名称 新数据类型;
    
    ##将表 tel 列改名为 telphone: 
    alter table students change tel telphone char(13) default "-";
    
    ##将 name 列的数据类型改为 char(16): 
    alter table students change name name char(16) not null;

    删除列

    ##基本形式: 
    alter table 表名 drop 列名称;
    
    ##删除 birthday 列: 
    alter table students drop birthday;

    重命名表

    ##基本形式: 
    alter table 表名 rename 新表名;
    
    ##重命名 students 表为 workmates: 
    alter table students rename workmates;

11. 删除整张表

    ##基本形式: 
    drop table 表名;
    
    ##删除 workmates 表: 
    drop table workmates;

12. 删除整个数据库

    ##基本形式: 
    drop database 数据库名;
    
    ##删除 samp_db 数据库: 
    drop database samp_db;

---

推荐阅读：
2019年前端面试题-01
2019年前端面试题-02
2019年前端面试题-03
2019年前端笔试题

---

我是Cloudy，年轻的前端攻城狮一枚，爱专研，爱技术，爱分享。
个人笔记，整理不易，感谢阅读、点赞和收藏。
文章有任何问题欢迎大家指出，也欢迎大家一起交流前端各种问题！