1. -- 1.创建表格
  2. create table `student`(
  3.     `id` int,
  4.     `name` varchar(10),
  5.     `sex` char(1),
  6.     `age` int,
  7.     `deptid` int,
  8.     `height` int,
  9.     `login_date` date,
  10.     `course_id` int
  11. );
  12. -- 2.插入
  13. insert into student values(5,'Jim','m',21,4,159,'2021-03-02',4);
  14. insert into student values(6,'John','m',22,3,170,'2021-03-11',1);
  15. insert into student values(7,'Lily','f',20,2,180,'2021-03-11',1);
  16. insert into student values(8,'Susan','f',25,2,178,'2021-03-11',1);
  17. insert into student values(9,'Thpmas','m',24,1,168,'2021-03-11',2);
  18. insert into student values(10,'Tom','m',23,3,168,'2021-03-09',3);
  19. insert into student values(11,'若曦','f',23,1,173,'2021-03-07',3);
  21. insert into student values(12,'浩然','m',21,3,176,'2021-03-07',3),
  22.                                                     (13,'若熙','f',23,1,175,'2021-03-06',3),
  23.                                                     (14,'若溪','f',21,2,159,'2021-03-05',4),
  24.                                                     (15,'昊然','m',29,2,179,'2021-03-01',4),
  25.                                                     (16,'皓然','m',29,3,176,'2021-03-02',5),
  26.                                                     (17,'子轩','f',28,3,171,'2021-03-04',4),
  27.                                                     (18,'紫萱','f',27,4,172,'2021-03-03',5);
  28. -- 解释 *,所有数据查询
  29. select * from student;
  30. # 部分数据查询
  31. select name,height from student;
  33. -- 3.去重查询
  34. select distinct age from student;
  35. -- 3.1 组合去重
  36. select distinct age,name from student;
  38. -- 3.2 通配符去重
  39. select distinct * from student;
  41. -- 3.3 去重计数
  42. select count(distinct name,age)    as num from student;    
  43. -- 3.3.1 不去重计数
  44. select count(age)    as num from student;    
  45. -- 3.3.2 通配符不去重计数
  46. select count(*)    as num from student;    
  48. -- 记数count
  49. select count(1) from student group by sex;
  51. -- 4.设置别名
  52. select s.name as 名字,s.age as 年龄 from student as s;
  54. -- 5.限制查询结果条数
  55. select * from student limit 3,5;
  56. select * from student limit 3;
  58. -- 6.查询结果排序
  59. select * from student order by height;        
  60. select * from student order by height desc;    
  61. -- 6.1查询结果组合排序
  62. select * from student order by age,height desc; 
  64. -- 7.条件查询
  65. # 单一指向
  66. select * from student where height=170;
  67. select name,height from student where height=170;
  68. # 比较运算符
  69. select name,height from student where height>=170;
  70. # -多个查询条件-逻辑运算符
  71. select * from student where height > 170 and age < 25;
  73. # 使用like '%'模糊查询
  74. select * from student where name like 'T%';
  75. # 不区分大小写，区分大小写加binary
  76. select * from student where name like binary 'T%';
  77. # 使用like '_',占位符
  78. select * from student where name like 'L___';
  80. # 区间
  81. select * from student where age between 20 and 21;
  82. # 为空和不为空，is null和is not null
  83. select * from student where login_date is null;

通常group by和聚合函数使用
1.如果没有使用聚合函数，只显示每个分组第一条记录

聚合函数

  1. • COUNT() 统计记录的条数；
  2. • SUM() 计算字段值的总和；
  3. • AVG() 计算字段值的平均值；
  4. • MAX() 查询字段的最大值；
  5. • MIN() 查询字段的最小值。
  6. • group_concat() 字符拼接函数 ```sql — 8.group by

    单独使用group by(没有聚合函数),只显示每个分组第一条记录

    select name,sex from student group by sex;

sql标准，字段分组需要显示

select sex from student group by sex;

聚合函数-计数： ()知只是表示拿什么字段计数

select sex,count(1) from student group by sex; select age,sex,count(1) from student group by sex,age;

去重

select age,sex,count(distinct 1) from student group by sex,age;

聚合函数-拼接，把女的分为一组显示，男的分为一组显示

— 通常用法：分组字段显示出来，然后组内实现某个函数 select sex,group_concat(name) from student group by sex;

  1. ```sql
  2. -- 9.having:过滤分组
  3. -- having对select字段过滤，where对from字段过滤
  4. select name,sex,height from student having height > 150;
  5. select name,sex,height from student where height > 50;
  7. -- having可以使用别名，where字段不可以使用别名
  8. select name,sex,height from student having height > 172;
  9. select name,sex,height as h from student having h > 172;
  12. # 一般与聚合函数一起使用
  13. # 执行顺序：group对from字段进行分组，把名字穿起来，求平均身高，筛选平均身高不能大于170
  14. select group_concat(name),sex,avg(height) from student group by sex having avg(height) > 171;
  1. create table course(course_id int,course_name varchar(10));
  2. insert into course values(1,'Java'),
  3.                                                  (2,'C语言'),
  4.                          (3,'MySQL'),
  5.                          (4,'Spark'),
  6.                          (5,'Hadoop');
  7. alter table student add column course_id int;
  9. -- 笛卡尔积
  10. select * from student,course;
  11. # 无意义的笛卡尔积
  12. select id,name,sex,s.course_id,course_name from student s,course c;
  13. # 有意义的笛卡尔积,重复的字段来源指定从表，否则会报错
  14. # 交叉链接cross join
  15. select id,name,sex,s.course_id,course_name from student s,course c where s.course_id = c.course_id;
  17. # 内链接inner join
  18. select id,name,sex,s.course_id,course_name from student s inner join course c on s.course_id = c.course_id;
  20. # 外连接left join
  21. select s.name,c.course_name from student s left join course c on s.course_id=c.course_id;
  23. update student set course_id=6 where id=18;
  25. select s.name,c.course_name from student s left join course c on 1=1;
  26. select s.name,c.course_name from student s left join course c on 1=1 where s.course_id=c.course_id;
  28. # left join on and
  29. select s.name,c.course_name from student s left join course c on s.course_id=c.course_id and c.course_name= 'MySQL';
  31. # left join on where
  32. select s.name,c.course_name from student s left join course c on s.course_id=c.course_id where c.course_name= 'MySQL';

子查询

  1. -- 子查询
  2. -- 双重where筛选:学生where id筛选，课程表where id筛选
  3. select name from student where course_id in(select course_id from course where course_name = 'Java');

插入

  1. -- 插入insert into,两种插入数据方式
  2. # insert into tbn values()和insert into tbnNew select coln from tbn;
  4. create table tb_courses(
  5.   course_id int auto_increment,
  6.   course_name char(40),
  7.   course_grade float,
  8.   course_info char(100),
  9.   primary key(course_id)
  10. );
  12. insert into tb_courses(course_name,course_grade,course_info) values('Network',3,'Computer Network');
  13. insert into tb_courses(course_name,course_grade,course_info) values('Daabase',3,'MySQL');
  14. insert into tb_courses(course_name,course_grade,course_info) values('System',3,'Operation');
  16. create table tb_course_new(
  17.   course_id int auto_increment,
  18.   course_name char(40),
  19.   course_grade float,
  20.   course_info char(100),
  21.   primary key(course_id)
  22. );
  24. ## 第二种：insert into tbnNew select coln from tbn;
  25. insert into tb_course_new select course_id,course_name,course_grade,course_info from tb_courses;
  27. ### 第三种插入:建表插入(缺少约束)：用于备份create table as
  28. --表格查询出数据直接
  29. create table tb_course2 as select * from tb_courses;

修改

  1. -- update
  2. # 列值全部修改
  3. update tb_course_new set course_grade = 4;
  4. # 指定列值修改
  5. update tb_course_new set course_grade = 5 where course_info =  'MySQL';

删除

  1. --  delete
  2. # 删除表数据
  3. delete from tb_course_new;
  4. # 删除指定行数据
  5. delete from tb_course_new where course_info = 'MySQL';

union 和union all 并集
前者：对两个结果集进行并集操作，不包括重复数据，按照默认规则排序
后者：对两个结果集进行并集操作，包括重复数据，不进行排序
合并多个select语句的结果集，需要满足如下条件：
相同的列
列必须是相似的数据类型
select语句的列顺序必须一致

  1. -- 功夫课程表
  2. create table kongfu(name varchar(10),course varchar(20),tel char(11),teacher varchar(12));
  3. insert into kongfu values('令狐冲','紫霞秘籍','13245687985','岳不群'),
  4.                                                  ('张无忌','九阳真经','13556151544','白猿'),
  5.                          ('周芷若','九阴白骨爪','16845543516','倚天屠龙记');
  6. -- 音乐课程表                        
  7. create table music(name varchar(10),course varchar(20),tel char(11),teacher varchar(12));
  8. insert into music values('令狐冲','笑傲江湖曲','13245687985','任盈盈'),
  9.                                                  ('乐之扬','杏花天影','19838745464','乐韶凤'),
  10.                          ('黄蓉','碧海潮生曲','16578943541','黄药师');
  13. # 并集--纵向并集        
  14. select name 学生名字,tel 联系电话,course 课程名字,teacher 老师名字 from kongfu
  15. union
  16. select name 学生名字,tel 联系电话,course 课程名字,teacher 老师名字 from music;
  17. +----------+-------------+------------+------------+
  18. | 学生名字 | 联系电话    | 课程名字   | 老师名字   |
  19. +----------+-------------+------------+------------+
  20. | 令狐冲   | 13245687985 | 紫霞秘籍   | 岳不群     |
  21. | 张无忌   | 13556151544 | 九阳真经   | 白猿       |
  22. | 周芷若   | 16845543516 | 九阴白骨爪 | 倚天屠龙记 |
  23. | 令狐冲   | 13245687985 | 笑傲江湖曲 | 任盈盈     |
  24. | 乐之扬   | 19838745464 | 杏花天影   | 乐韶凤     |
  25. | 黄蓉     | 16578943541 | 碧海潮生曲 | 黄药师     |
  26. +----------+-------------+------------+------------+
  28. select name 学生名字,tel 联系电话 from kongfu
  29. union
  30. select name 学生名字,tel 联系电话 from music;
  31. +----------+-------------+
  32. | 学生名字 | 联系电话    |
  33. +----------+-------------+
  34. | 令狐冲   | 13245687985 |
  35. | 张无忌   | 13556151544 |
  36. | 周芷若   | 16845543516 |
  37. | 乐之扬   | 19838745464 |
  38. | 黄蓉     | 16578943541 |
  39. +----------+-------------+
  41. # 交集--横向交集
  42. select k.name 学生名字,k.tel 联系电话 from kongfu k 
  43. left join music m on k.name = m.name 
  44. and k.tel = m.tel;
  45. +----------+-------------+
  46. | 学生名字 | 联系电话    |
  47. +----------+-------------+
  48. | 令狐冲   | 13245687985 |
  49. | 张无忌   | 13556151544 |
  50. | 周芷若   | 16845543516 |
  51. +----------+-------------+
  53. # 交集--横向交集
  54. select k.name 学生名字,k.tel 联系电话 from kongfu k 
  55. left join music m on k.name = m.name 
  56. and k.tel = m.tel;
  57. +----------+-------------+
  58. | 学生名字 | 联系电话    |
  59. +----------+-------------+
  60. | 令狐冲   | 13245687985 |
  61. | 张无忌   | NULL        |
  62. | 周芷若   | NULL        |
  63. +----------+-------------+
  65. select k.name 学生名字,k.tel 联系电话 from kongfu k 
  66. left join music m on k.name = m.name 
  67. where k.tel = m.tel;
  68. +----------+-------------+
  69. | 学生名字 | 联系电话    |
  70. +----------+-------------+
  71. | 令狐冲   | 13245687985 |
  72. +----------+-------------+