数据库介绍

What’s 数据库

1.数据库（Database，DB）：将大量数据保存起来，通过计算机加工而成的可以进行高效访问的数据集合。如：大型-银行存储的信息，小型-电话簿。
2.数据库管理系统（Batabase Management System，DBMS）：用来管理数据库的计算机系统。
3.关系型数据库（Relational Database，RDB）：目前应用最广泛的数据库。
4.结构化查询语言（Structured Query Language，SQL）：专门用于操作 RDB。
5.常见的 5 种关系型数据库管理系统（Relational Database Management System，RDBMS）：
①Oracle Database：甲骨文公司
②SQL Server：微软公司
③DB2：IBM 公司
④PostgreSQL：开源
⑤MySQL：开源
6.常见的非关系型数据库（Not noly sql）
①MongoDB：MongoDB Inc公司
②Redis：Vmware公司

sql语句注意事项

字段名：表的一列名（垂直方向）。
记录 ：表的一行为一条记录信息（水平方向）。【注意】关系数据库必须以行为单位进行数据读写。
以分号 ；结尾；
字符串、日期：用单引号括起来（’ ‘），如’Hello’，’weqwewq123123’,’2016-09-24’
数字：直接书写，不用加单引号，如 5

连接数据库

连接数据库的工具有：

连接数据库的基本信息

  1. ip：1.116.112.231
  2. 用户名：fanmao78
  3. 密码：Aa111111

image.png
image.png
输入好ip，用户名，密码之后可以点击连接测试，看是否能正常连接
image.png
连接成功后，点击确定即可

新建 sql查询

image.png

怎么运行sql语句

选中自己写的sql，然后右键选择 运行以选择
image.png

基本语法

select查询

  • select 字段名 from 表名 ； #查询表中的某个字段
  • select 字段1,字段2 from 表名； #查询表中的字段1，字段2的信息（多个字段之间用 ， 号隔开）
  • select * from 表名； #查询表中所有字段的信息 ```sql select name from lianxi ; 查询lianxi表中所有的name 的信息

select name,sex from lianxi; 查询lianxi表中所有的name，sex的信息

select * from lianxi; 查询lianxi表中所有的信息

  1. #星号 *  代表匹配所有的字段
  3. <a name="lW9vR"></a>
  4. #### 练习
  6. 1. 查询lianxi表中所有name的信息；
  7. 2. 查询lianxi表中所有name名字，科目course，分数score的信息
  8. 3. 查询lianxi表中所有的信息
  10. <a name="pGctO"></a>
  11. ### 注释
  13. - #开头的                                           注释相当于备注，不会影响sql语句的运行
  14. ```sql
  15. #井号开头的是注释

where 条件查询

  • select 字段名 from 表名 where 条件； #条件查询
符号 定义 例子
> 大于 score>60
>= 大于等于 score>=60
< 小于 score<60
<= 小于等于 score<=60
= 等于 name = ‘小王’
!= 或 <> 不等于 != ‘小王’
in 在什么什么内 score in （60，70，80）
not in 不在什么什么内 score not in （60，70，80）
is null 是null（空） name is null
is not null 不是null （不为空） name is not null 
  1. select * from lianxi where score > 60;                                                    查询所有score大于60的信息
  3. select * from lianxi where score >= 60;                                                    查询所有score大于等于60的信息
  5. select * from lianxi where score < 60;                                                    查询所有score小于60的信息
  7. select * from lianxi where score <= 60;                                                    查询所有score小于等于60的信息
  9. select * from lianxi where name = '小红';                                                查询所有name叫做小红的信息
  11. select * from lianxi where name != '小张';                                            查询所有name不为小张的信息
  13. select * from lianxi where score in (60,70,80);                                    查询所有score为60或70或80的信息
  15. select * from lianxi where name not in ('小王','小黄','小红');   查询所有name不为小王，小黄，小红的信息
  17. select * from lianxi where name is null ;                                                查询所有名字为空（null）的学员信息
  19. select * from lianxi where score is not null;                                  查询所有分数不为空的学员的信息

练习

  1. 查询小红所有的信息;
  2. 查询黄贤礼所有的科目course和成绩score

  3. 查询所有人数学科目course的成绩

  4. 查询所有大于80分的信息

  5. 查询所有及格的信息
  6. 查询小于90分的学生名字name和科目course

  7. 查询所有男生的姓名name和科目course，成绩score

  8. 查询id不为 3 ，6 ，9 ，12 的所有信息

  9. 查询名字叫小黄或小红的所有信息
  10. 查询考67分或89分的同学的信息

  11. 查询黄贤礼和小克的所有信息

  12. 查询所有学员中名字为空的学员信息

  13. 查询所有学员中没有分数的学院信息
  14. 查询所有学院中性别为女且分数为空的学员信息（思考） 条件1 and 条件2

and 并且 满足两个条件

  • select * from 表名 where 条件1 and 条件2； ```sql

    查询性别为女且分数为空的学员信息

    select * from lianxi where sex=’女’ and score is NULL;

sex=’女’ and score is NULL 用and连接 必须满足两个条件

  2. <a name="pTwc9"></a>
  3. ### or        或者                    满足一个条件
  5. - select  * from 表名  where   条件1   or  条件2；
  6. ```sql
  7. #查询姓名叫小谢或者分数大于70的的学院信息
  8. select * from lianxi where name = '小谢' or score > 70;
  10. name = '小谢' or score > 70                用or连接            满足其中一个条件就可以了

sql语句会先执行and再执行or

练习

  1. 查询id>6且分数大于60的所有信息
  2. 查询id<10或者分数大于80的所有信息
  3. 查询id为9，10，11 且姓名为小兰 或者 性别为男 且 分数大于90 的学员信息

like 模糊查询

  • select * from 表名 where 字段名 like ‘文本%’ ; #模糊查询 ```sql

    查询所有2015年出生的学员的信息

    select * from lianxi where birthday like ‘2015%’;

查询所有姓黄的学员的信息

select * from lianxi where name like ‘黄_’;

  1. #%(百分号)匹配多个字符以及0个字符<br />#_(下划线)匹配1个字符<br />                    
  2. <a name="mjC6n"></a>
  3. #### 练习
  5. 1. 查询所有7月出生的学员信息
  6. 2. 查询所有名字以礼结尾的学员信息
  7. 3. 查询所有分数为七十多分且性别为女的学员的信息
  9. <a name="buN6R"></a>
  10. ### between  ... and ...            介于什么之间
  12. - select  *  from  表名  where  字段  between  值1 and  值2;
  13. ```sql
  14. #查询分数在40-60之间的学员的信息
  15. SELECT * from lianxi where score BETWEEN 40 and 60 ;
  17. #查询出生时间在2020年-2021年之间的学员的信息
  18. select * from lianxi where birthday between '2020-01-01 00:00:00' and '2021-12-31 23:59:59';

between的区间会包含两个边界值。

作业

heros表

  1. 查询 name（姓名）中包含刘的所有英雄信息；
  2. 查询 hp_max(最大血量) 在 6000-8000 之间的所有英雄信息；
  3. 查询 birthdate（上架日期）不为 null 的所有英雄信息；
  4. 查询 birthdate（上架日期）在 2015年并且 hp_max(最大血量)在 8000以上的所有英雄信息；
  5. 查询 role_main （主要定位） 为 辅助或者坦克或者法师 的所有英雄信息；
  6. 查询 name（姓名）为两个字 并且 role_main（主要定位）为 法师 或 辅助 的英雄信息；
  7. 查询 同时符合 birthdate(上架时间)不为null，attack_range 为 远程 的英雄信息；
  8. 查询所有hp_max（最大血量）大于7000的英雄的信息
  9. 查询所有hp_max（最大血量）大于8000且role_main(主要职业)为坦克的英雄的信息
  10. 查询所有id大于10011并且mp_max（最大蓝量）大于1700 或者 名字以吕开头的英雄的信息
  11. 查询所有attack_range（攻击范围）为近战且role_main(主要职业）为坦克 且 role_assist (副职业)为辅助的英雄的信息
  12. 查询所有role_assist (副职业)为空，birthdate（上线时间）不为空的英雄的信息

以下在 student表中进行

  1. 查询表中所有名字的信息；
  2. 查询表中所有名字，班级class的信息
  3. 查询表中所有的信息
  4. 查询赵洁所有的信息;
  5. 查询王玉珍的名字和性别和出生日期
  6. 查询所有人出生日期和班级class
  7. 查询所有 学号sno 大于120的信息
  8. 查询学号sno小于180分的学生名字name和班级
  9. 查询所有男生的姓名sname和出生日期，班级
  10. 查询学号sno为 103 ，106 ，119 ，112 的所有信息
  11. 查询名字叫王东或王英的所有信息
  12. 查询班级为20033或20032的同学的信息
  13. 查询性别为女和班级为20033的所有信息
  14. 查询所有学员中姓许的学员信息
  15. 查询所有学院中性别为女且班级为20031的学员信息
  16. 查询性别为女且学号大于150且 名字姓张的学员信息
  17. 查询姓名叫张晨或者班级为20032的学院信息
  18. 查询所有7月出生的学员信息
  19. 查询所有名字以红结尾的学员信息
  20. 查询所有姓李的女的学员的信息
  21. 查询出生时间在1990-01-01 00:00:00 到1999-12-31 23:59:59之间的学员的信息

排序，限制

order by 排序

  • order by 字段名 排序方式
  • 升序 asc 数字从小到大（默认升序）

  • 降序 desc 数字从大到小


    使用order by 可以对数据进行排序，可以升序（asc），降序 （desc） ```sql

    查询学生信息，并按照分数降序排列

select * from lianxi order by score desc ;

查询学生信息，按照年龄从大到小排列;

出生时间越小，年龄越大，1999年出生的要比2000年出生的年龄大

select * from lianxi ORDER by birthday ASC;

  3. <a name="lV5Ps"></a>
  4. ### 根据多个字段进行排列
  6. - order by  字段1  排列方式1，字段2 排列方式2；
  8. 当字段1的值相同时候，再根据字段2来进行排列；
  9. ```sql
  10. #按照出生日期升序排列，出生日期相同时，按照分数降序排列
  12. select * from lianxi ORDER by birthday ASC,score desc;

where order by 组合使用

  • select 字段 form 表名 where 条件1 order by 字段1 排序方式；

在排序的时候，如果有where条件过滤，首先先进行where条件过滤，再进行order by排序。

  1. #查询分数在60-90之间的学员信息，按照分数降序排列
  3. SELECT * from lianxi where score BETWEEN 60 and 90 order by score desc ;

field( )

  • field (字段1,”值1”,”值2””值3” ) 对字段1的值1,值2,值3优先排列

image.png

练习

  1. 查询所有姓黄学员的信息，并且按照分数升序排列；
  2. 查询所有女生的信息，并且按照年龄，从大到小排列；

    limit 限制

  • limit n； 取结果的前 n 条数据；

limit可以限制返回数据的数量。

  1. #查询表中的第一条数据
  3. select * from lianxi limit 1;
  5. #查询表中分数最高的三个人的信息（跟order by 结合使用）
  7. select * from lianxi order by score desc limit 3;

limit m,n

  • limit m,n 取第（m+1）条往后的 n 条数据

  • m计数是从0开始计数的，0是第1行，1是第2行，2是第3行，，，，99是第100行，，，，，，m是第m+1行

  • n表示取多少条数据

  • limit 5,6

  • 从第（5+1）行开始
  • 取 6 条数据 ```sql

查询heros表中血量排名 第11-20名的英雄信息

select * from heros order by hp_max asc limit 10,10;

查询heros表中攻击距离(attack_range) 为近战且上架时间(birthdate)不为空的英雄中血量(hp_max)第二大的英雄的名字

select * from heros where attack_range = “近战” and birthdate is not null order by hp_max desc limit 1,1;

  2. <a name="RgA4F"></a>
  3. #### 练习
  5. 1. 查询出生日期最小的学员信息
  6. 2. 查询id最大的学员信息
  7. 3. 查询姓黄的学员中分数最大的学员信息
  8. 4. 查询考试分数60-89中，分数最大的前三条学员信息
  11. 1. 查询lianxi表中分数排名6-10名同学的名字
  12. 2. 查询lianxi表中语文科目考试成绩2-8名同学的所有信息
  13. 3. 查询lianxi表中数学科目考试成绩倒数4-7名的学员的所有信息
  14. <a name="vbUK7"></a>
  15. # 子查询
  16. 将一个sql语句的查询结果  作为一个查询的条件，也可以作为一个临时表
  18. <a name="sCSsO"></a>
  19. ## as
  20. as 不仅可以给表起别名,也可以给字段起别名<br />![image.png](https://cdn.nlark.com/yuque/0/2021/png/22358770/1639970551811-7e0b33a4-ed59-4f88-b16f-fbd17a2ed395.png#clientId=ue3191229-9e70-4&crop=0&crop=0&crop=1&crop=1&from=paste&height=174&id=ubb593578&margin=%5Bobject%20Object%5D&name=image.png&originHeight=231&originWidth=371&originalType=binary&ratio=1&rotation=0&showTitle=false&size=6619&status=done&style=none&taskId=u0220f3fc-d5b4-4a73-bc54-b2fa5528a3a&title=&width=280)<br />as 可以用空格代替,省略不写
  21. <a name="RNn25"></a>
  22. ## 作为临时表
  23. 作为一张临时表的时候需要给sql语句起别名
  25. - select * from (sql语句) as  别名；                         给sql语句的执行结果起别名，别名随意取
  27. ```sql
  28. #查询lianxi表中的前10条数据，把这10条数据作为一张临时表
  29. SELECT * from lianxi limit 10;              
  31. #把上面sql语句的执行结果作为一张临时表，作为临时表的时候要起别名 (sql语句)  as   临时表名
  32. select * from (SELECT * from lianxi limit 10) as aa;

as 可以用 空格 代替

  1. 查询前10条数据中考试分数最高的同学的所有信息； ```sql

    首先要找到前10条数据

SELECT * from lianxi limit 1;

把上一条sql语句的结果作为临时表，然后对临时表进行排序找到分数最高的一条数据

select from (SELECT from lianxi limit 10) as biao order by score desc limit 1;

  2. <a name="zN6hT"></a>
  3. ## 作为查询条件
  4. 把sql语句的执行结果,作为了where后面的条件
  7. -  select * from 表名 where 字段1=（sql语句（的结果））
  9. - 作为条件的时候     where     某个字段=(sql语句)
  11. 这个sql语句要括起来,这个时候会把 字段 = sql语句执行结果的数据筛选出来
  12. ```sql
  13. select id from lianxi where limit 1;            执行结果为1
  15. select * from lianxi where id = (select id from lianxi where limit 1);
  16. 等同于
  17. select id from lianxi where id = 1;
  21. 这是sql语句执行结果为单个值的,单个值用 = 号,当作为条件的sql语句为多个值的时候需要用 in
  23. select id from lianxi where limit 3;            执行结果为1,2,3
  25. select * from lianxi where id in (select id from lianxi where limit 3);
  26. 等同于
  27. select * from lianxi where id in (1,2,3)

这个是2表嵌套同理,可以3表,4表嵌套,把之前sql语句的执行结果作为查询后面的判断条件的值

练习

  1. 查询lianxi表前十条数据中语文科目成绩最高的同学的所有信息
  2. 查询lianxi表第5-15条数据中考试成绩最低的同学的所有信息
  1. 查询李成老师所教授的课程名字
  2. 查询皮想的各科成绩
  3. 查询数字电路科目的所有成绩
  4. 查询教授操作系统科目的老师是谁
  5. 查询考试成绩在80分以上的学员的名字

image.png

作业

查询学习李成教授科目的所有学员的名字
#查询柳冰所教授科目的所有成绩
#查询陈婷未学习的科目的名字
#查询陈婷未学习的科目的老师的名字
#查询学习过数字电路的所有同学的信息
#查询所有汪萍教授科目的考试成绩
#(此题可不做)终极: 查询性别为男的老师教授的科目中成绩大于60的所有学员的信息,并筛选出1990年后出生的所有女学员,按照学号sno降序排列,显示其第11-20条数据 (只显示年龄最大的学员的信息,此条件可不加)

常用函数

时间函数

now() 当前时间

  1. 获取当前时间
  3. select  now();

image.png

获取日期对应的时间

函数 解释
year() 年份
month() 月份
day() 天
hour() 小时
minute() 分钟
second() 秒

可以用函数获取日期中所对应的年份，月份，天，小时，分钟。

  1. 获取当前时间的年份
  2. select year(now());
  4. 获取当前时间的月份
  5. select month(now());
  7. 获取当前时间的天
  8. select day(now());
  10. 获取当前时间的小时
  11. select hour(now());
  13. 获取当前时间的分钟
  14. select minute(now());
  16. 获取当前时间的秒
  17. select second(now());
  1. 查询一下在第15分钟出生的学员信息
    1. select * from lianxi where minute(birthday) = 15;

date_format() 时间格式转换

格式 描述
%a 缩写星期名
%b 缩写月名
%c 月，数值
%D 带有英文前缀的月中的天
%d 月的天，数值(00-31)
%e 月的天，数值(0-31)
%f 微秒
%H 小时 (00-23)
%h 小时 (01-12)
%I 小时 (01-12)
%i 分钟，数值(00-59)
%j 年的天 (001-366)
%k 小时 (0-23)
%l 小时 (1-12)
%M 月名
%m 月，数值(00-12)
%p AM 或 PM
%r 时间，12-小时（hh:mm:ss AM 或 PM）
%S 秒(00-59)
%s 秒(00-59)
%T 时间, 24-小时 (hh:mm:ss)
%U 周 (00-53) 星期日是一周的第一天
%u 周 (00-53) 星期一是一周的第一天
%V 周 (01-53) 星期日是一周的第一天，与 %X 使用
%v 周 (01-53) 星期一是一周的第一天，与 %x 使用
%W 星期名
%w 周的天 （0=星期日, 6=星期六）
%X 年，其中的星期日是周的第一天，4 位，与 %V 使用
%x 年，其中的星期一是周的第一天，4 位，与 %v 使用
%Y 年，4 位
%y 年，2 位 
  1. select date_format(now(),"%c--%H")
  2. 输出为  12--11
  4. select date_format(now(),"%Y--%c--%d")
  5. 输出为  2021--12--20

  1. 查询lianxi表中的姓名和出生日期,出生日期按照 1999-10-10 的格式输出

    1. select name ,date_format(birthday,"%Y-%m-%d" )from lianxi;

    练习

  2. 查询student表中学生名字和出生日期，出生日期只显示年份

  3. 查询student表中 女学生名字 和出生日期，出生日期只显示月份
  4. 查询student表中 名字 和出生日期，并筛选出90后的男学员， 出生日期以 1999-10-10 格式展示
  1. select sname ,date_format(sbirthday,"%Y") from sthdent;
  2. select sname ,date_format(sbirthday,"%M") from sthdent where ssex="女";
  3. select sname ,date_format(sbirthday,"%Y-%M-%d") from sthdent where ssex="男" and sbirthday like "199%";

datediff() 计算时间差

  • datediff（时间1，时间2 ） 会计算出两个时间之间差多少天
  • 其中datediff计算出 时间1减去时间2 的天数 ```sql 计算现在的时间离2021-05-01有多少天 select datediff(now(),”2021-05-01”);

查询练习表中现在的时间减去出生日期，小于100天的学员的信息 select * from lianxi where DATEDIFF(now(),birthday)< 100;

  2. <a name="xeZei"></a>
  3. ### date_add() 灵活操作时间
  4. | Type 值 | 说明 |
  5. | --- | --- |
  6. | MICROSECOND | 微秒 |
  7. | SECOND | 秒 |
  8. | MINUTE | 分钟 |
  9. | HOUR | 小时 |
  10. | DAY | 天 |
  11. | WEEK | 周 |
  12. | MONTH | 月 |
  13. | QUARTER | 季度 |
  14. | YEAR | 年 |
  17. - date_add(时间,interval     数字    时间单位  )
  19. ```sql
  20. 两年前的时间
  21. select date_add(now(),interval -2 year);

image.png

  1. 25个月前的时间
  2. 最近25个月出生的学员的信息 ```sql select from lianxi where date_add(now(),interval -25 month) <birthday ; 等同于 select from lianxi where “2019-11-20 14:16:35” < birthday ;

“2019-11-20 14:16:35”是函数 date_add(now(),interval -25 month) 计算的结果。

  1. <a name="BdMyR"></a>
  2. #### 练习
  4. 1. 输出2天后的时间
  5. 2. 输出1个季度后的时间
  6. 3. 输出1年前的时间
  7. 4. 查询最近一年出生的学员的信息
  9. <a name="mKChL"></a>
  10. ## 聚合函数
  11. | 函数名 | 说明 |
  12. | --- | --- |
  13. | sum( ) | 总和 |
  14. | avg( ) | 平均值 |
  15. | max( ) | 最大值 |
  16. | min( ) | 最小值 |
  17. | count( ) | 计数 |
  19. - sum(字段1)      会对字段1里面的值进行求和
  20. - avg(字段1)    会对字段1里面的值进行求平均值    
  21. - max(字段1)    会找出字段1的值里面最大的值
  22. - min(字段1)    会找出字段1的值里面最小的值
  23. - count(字段1)    会对字段1里面的数据进行计数，计算一共有多少条
  25. ![image.png](https://cdn.nlark.com/yuque/0/2021/png/22358770/1639984083703-eda62ab6-8933-42c5-a452-1a0fcb4f8324.png#clientId=uec1474de-bce9-4&crop=0&crop=0&crop=1&crop=1&from=paste&height=129&id=u6f896017&margin=%5Bobject%20Object%5D&name=image.png&originHeight=129&originWidth=851&originalType=binary&ratio=1&rotation=0&showTitle=false&size=9472&status=done&style=none&taskId=u46d34460-4e4f-45b9-b36d-3cde382aa43&title=&width=851)
  29. 1. 查询出最大的分数
  30. 2. 查询出最小的分数
  31. 3. 分数大于60的同学有多少人
  32. ```sql
  33. select max(score) from lianxi;
  35. select min(score) from lianxi;
  37. select count(*) from lianxi where score > 60 ;

distinct 去除重复的值

  • distinct 字段1 会对字段的值进行去重
    1. 去除重复的名字
    2. select DISTINCT name from lianxi;
  1. 查询成绩表score ，查询所有学号sno，去除重复的值
  2. 查询lianxi表中所有人的出生日期，去除重复的日期
  3. 查询lianxi表中所有人的名字和出生日期，去除名字和出生日期相同的人
  1. select distinct sno from score;
  3. select distinct birthday from lianxi ;
  5. select distinct name , birthday from lianxi;

分组查询

  • group by 字段 对字段进行分组
    1. select cno from score  GROUP BY cno;
  1. 首先是score表里的内容

image.png

  1. 按照cno分组会把表中cno字段值一样的分为一组，并且去除重复的cno值，分组结果如下

image.png
1个科目的值对应着多个学号和成绩，这个时候就不可以select *，因为mysql不支持1个值对应多个值进行输出， 只能输出被分组的字段，和聚合函数计算出来的值。

练习

  1. 查询score分数表中，每个（sno）学生的平均成绩（degree）
  2. 查询student学生表中，男生女生的人数
  3. 查询student表中，各个班级（class）有多少人 ```sql select sno 学号,avg(degree) 平均成绩 from score group by sno;

SELECT ssex,count(*) from student GROUP BY ssex;

SELECT class ,count(*) from student GROUP BY class;

  2. <a name="bngeo"></a>
  3. ## 分组过滤
  5. - having  条件
  6. - having  条件1  and  条件2
  8. 跟where的用法一样，但是having后面跟的都是聚合函数
  9. ```sql
  10. 1.查询出score表中平均成绩大于60的同学信息
  12. 题目中求的是同学信息，条件是平均成绩大于60
  14. select sno as 学号,avg(degree) as 平均成绩 from score group by sno having avg(degree) >60;
  15. #having后面跟条件avg(degree)>60
  18. 2.查询每个人的最低成绩大于60分的学员的信息
  20. 求得是学生信息，  条件是最低成绩大于60
  22. select sno from score group by sno having min(degree) >=60;
  23. #having后面跟条件  min(degree)>=60

Mysql - 图15

练习

查询出所有90后男学员的信息
#查询出所有90后男生和女生的人数
#查询出20033班级的男生和女生的人数
#查询出20033班级中学员的平均成绩

  1. SELECT * from student where sbirthday like "199%" and ssex="男";
  3. SELECT ssex,count(*) from student where sbirthday LIKE "199%" GROUP BY ssex;
  5. SELECT ssex,count(*) from student where class=20033 GROUP BY ssex;
  7. select  sno,avg(degree) from score where sno in (SELECT sno from student where class = 20033) group by sno;

sql 语句执行顺序

Mysql - 图16

sql 语句书写的顺序

  1. select  > from  > where > group by >  having > order by  > limit

作业

  1. 查询陈婷同学的平均成绩
  2. 查询每个同学（sno）的平均成绩
  3. 查询每个科目的平均成绩
  4. 查询学习每个科目的人数
  5. 查询李成所教授的科目的平均成绩
  6. 查询20032班级学员的各科成绩
  7. 查询成绩表中各科都及格的学员的sno
  8. 查询女学员的所有成绩，按照分数降序排列
  9. 查询学过李成课程的所有学员的信息

  10. 终极（可不做）：查询学过柳冰教授课程的成绩大于60,并且是90后的，男生和女生的人数

c407423130a3e3fdbff53d08528553b.png

多表连接

就是将多张表连接在一起，连成一张表，根据表之间共同的字段连接

内连接

  • inner join … on …
  • join … on …

inner join 等同于 join ，inner可以省略不写

  • select * from 表1 inner join 表2 on 表1.id=表2.id； #两表连接

  • select * from 表1 inner join 表2 on 表1.id=表2.id inner join 表3 on 表2.id=表3.id #三表连接

  • select * from 表1 inner join 表2 on 表1.id=表2.id inner join 表3 on 表2.id=表3.id inner join 表4 on 表3.id = 表4.id #四表连接

  1. 内连接两张表
  2. select * from xingming INNER JOIN zhuzhi on xingming.id=zhuzhi.id;

首先连表的时候，连接的字段会去一行的一行的匹配，匹配成功就连接成一条数据
image.png
image.png

当连接的字段匹配到两行时，会与这两行分别匹配
image.png

image.png

  • 当连表的时候有相同的字段名的时候，select的时候就需要声明一下这个是哪张表的

练习

  1. 连接分数表和课程表
  2. 连接课程表和老师表
  3. 连接 学生表和分数表和课程表
  4. 连接4个表
  1. 查询所有老师的名字和老师教授的科目的名字，以及老师的编号
  2. 查询成绩大于80的学生名字，分数，科目名字，教师名字
  3. 查询成绩大于60的学生名字，分数，科目名字，教师名字按照成绩降序排列
  4. 查询每个学生的名字和平均分数，按照平均分数降序排列
  5. 查询 个人平均分数大于60的学生的名字和平均分数，按照平均分数降序排列

左连接

  • left join … on …

  • select * from 表1 left join 表2 on 表1.id = 表2.id

以表1为主，表2为辅
image.png

右连接

  • right join … on …

  • select * from 表1 right join 表2 on 表1.id = 表2.id

以表2为主，表1为辅
image.png

  1. 左连接与内连接的区别，
  2. 内连接是取两表共同的部分，
  3. 左连接是以左表为主，右表为辅，右表没有对应值的地方，显示为null
  4. 右连接是以右表为主，左表为辅，左表没有对应值的地方，显示为null

外连接

mysql中没有外连接，Oracle中有外连接full join
image.png
full join 会匹配两张表中的所有内容，匹配不到的会显示为null;

union 连接

  • union可以把两条sql语句的运行结果连接在一起
  1. SELECT sum(id) from zhuzhi 
  2. union
  3. SELECT max(id) from xingming;

image.png
#连接的时候两张表的字段数量必须一致,第一个sql显示3个字段的值,第二个也必须显示3个字段的值,不然会报错
#union连接的子句里面有limit时，需要把子句（ ）起来

练习

  1. 查询姓张同学的学生名，考试成绩，科目名字，教师名字
  2. 查询至少有三门成绩及格的学员的名字
  3. 查询计算机导论这门科目的平均成绩
  4. 查询计算机系里面考试成绩最高的学生的信息
  5. 查询李成老师的教师编号，并查询许兵同学的学生编号，结果连接起来
  6. 查询女学员每个人的平均成绩，总成绩，成绩中的最高分数，成绩中的最低分数，考试的科目数；
  1. 1. 查询姓张同学的学生名，考试成绩，科目名字，教师名字
  3. select sname,degree,cname,tname from student join score on student.sno=score.sno join course on score.cno=course.cno join teacher on course.tno=teacher.tno where sname like "张%";
  7. 2. 查询至少有三门成绩及格的学员的名字
  10. select sname from student join score on student.sno=score.sno where degree >= 60 GROUP BY sname having COUNT(*)>=3;
  13. 3. 查询计算机导论这门科目的平均成绩
  14. SELECT avg(degree) from course join score on course.cno=score.cno where cname="计算机导论";
  17. 4. 查询计算机系里面考试成绩最高的学生的信息
  19. #求最高分是多少
  20. select degree from student join score on student.sno=score.sno join course on score.cno=course.cno join teacher on course.tno=teacher.tno where depart ="计算机系" ORDER BY degree desc LIMIT 1;
  22. #连接在一起
  23. SELECT * from student join score on student.sno=score.sno join course on score.cno=course.cno join teacher on course.tno=teacher.tno where depart ="计算机系" and degree = (select degree from student join score on student.sno=score.sno join course on score.cno=course.cno join teacher on course.tno=teacher.tno where depart ="计算机系" ORDER BY degree desc LIMIT 1)
  25. 5. 查询李成老师的教师编号，并查询许兵同学的学生编号，结果连接起来
  27. select tno from teacher where tname="李成"
  28. union
  29. select sno from student where sname="许兵"
  31. 6. 查询女学员每个人的平均成绩，总成绩，成绩中的最高分数，成绩中的最低分数，考试的科目数；
  33. SELECT student.sno,avg(degree),sum(degree),max(degree),min(degree),count(degree) from score join student on score.sno=student.sno where ssex="女" GROUP BY student.sno;

作业

  1. 查询每个学员的的学号,姓名,科目名字,考试成绩
  2. 查询每门科目的学习人数,显示科目名字,人数
  3. 查询平均分最高的科目名字
  4. 统计每个学生的总分
  5. 查询总分最高的学生的名字
  6. 查询计算机系的女学员中个人总分最高的学员名字
  1. 查询教师表Depart(系别) 列去重显示；
  2. 查询student表中班级为20031班或性别为女的同学记录；
  3. 查询20031班学生人数；
  4. 以Cno(课程编号) 升序Degree(分数) 降序查询Score表的学号，学生姓名，学科名称，成绩；
  5. 查询Score表中单科最高分的学生学号和课程号（子查询或排序）；
  6. 查询20033班得分在80分以上或者等于 60，61，62的学生；
  7. 查询所有班级的名称，以及每个班级中的人数 和 平均分；
  8. (可不做)查询总分最高的10个男学员姓名, 总分 按照总分升序排列； 
  3. #查询总分最高的10个男学员姓名, 总分,班级  按照总分升序排列
  4. SELECT sname,sum(degree),class from student join score on student.sno=score.sno 
  5. where student.sname in (SELECT sname from (select sname from student join score on student.sno=score.sno
  6. where ssex = "男" GROUP BY sname ORDER BY sum(degree) desc limit 10) a) GROUP BY sname,class ORDER BY sum(degree);

数据库增删改

搭建数据库
https://www.yuque.com/docs/share/b4a2224f-3e53-417b-96d7-04672c90973b

命令行的方式操作mysql数据库

  1. mysql -u root -p 登录数据库
  2. 然后输入密码，秘密是不可见的

image.png

  1. show databases; 查看数据库
  2. use 数据库名; 切换进入数据库
  3. show tables; 查看当前数据库的表

创建数据库

image.png
字符集：utf-8支持中文选择
排序规则：不区分大小写 

  1. #创建数据库
  2. create datebase 数据名
  3. CHARACTER SET    "字符集"
  4. COLLATE "排序规则";
  7. #例子
  8. create database lianxi
  9. CHARACTER SET "utf8"
  10. COLLATE "utf8_general_ci";

image.png

创建表

字段类型

字段类型 说明 例子
int 整数 123,2344,233
float 小数 1.01 ， 12.23
varchar（255） 字符串 “123hello”,”张三”
char（255） 字符串 “123hello”,”张三”
text 保存长文本的数据 数据量大的文本
date 日期 “2019-01-01”

1个汉字占用1个字节

char和varchar的区别，varcahr占用空间小速度慢，char占用空间大速度快

  • char占中的空间为规定的空间，内容不足的地方用空白填充，占用的空间更大，但是存取速度比较快
  • varchar占用的空间可以灵活变动，根据文本内容来调整，占用的空间比较小，但是存取速度比较慢

建表约束

约束名 说明
not null 不为空
default “默认值” 给字段设置一个默认值
unique key 唯一索引
primary key 设置为主键
auto_increment 自增长，每张表中能有一个自增长

约束可以不加

创建表

  1. 创建表
  2. create table  表名(
  3. 字段1  字段类型  约束,
  4. 字段2     字段类型  约束,
  5. 字段3     字段类型  约束,
  6. ......
  7. 字段n     字段类型  约束
  8. )
  10. 例子：
  12. create table lianxi(                                            --表名
  13. id     int       not null,                                --字段
  14. name   varchar(255)   not null,                        --字段
  15. sex    varchar(255)   not null,                      --字段
  16. birthday  date   not null                                    --字段
  17. )

添加数据

  1. #添加数据公式
  2. #如果添加所有字段，则表名后面不需要写添加的字段
  3. insert into  表名(字段1,字段2,字段3) 
  4. values(值1,值2,值3),(值1,值2,值3),(值1,值2,值3);
  7. 例子：
  9. insert into  lianxi(id,name,sex) values (1,"张三","男")

image.png

修改数据

  1. #修改数据
  3. update 表名 set 字段=新值 where  条件;
  7. #例子
  8. update lianxi set name="李四" where id=4;

image.png

修改表名
ALTER TABLE 旧表名 RENAME to 新表名;

删除数据

  1. #删除数据
  3. delete from 表名 where 条件;
  6. #例子
  7. #删除id<5的数据
  8. delete  from lianxi  where id<5;

清空表

清空表中的数据但是表还存在

#清空表
delete from 表名;

truncate 表名;

image.png
truncate与delete的区别
truncate不记录日志，delete记录日志，所以truncate要快于delete，但是用truncate删除之后就不能恢复，delete每删除一次都会记录在日志中，以便于之后的回滚。

删除表

不仅会删除表中的数据，还会把表给删除

#删除表
drop table 表名;


drop table lianxi; 

#删库，跑路
drop database 数据库名;

练习

  1. 创建xinxi表 | 字段名 | 字段类型 | 约束 | | —- | —- | —- | | id | int | 不为空 | | name | varchar(255) | | | score | int | | | course | varchar(255) | |
id name score course
1 张三 89 语文
1 张三 99 数学
2 王五 59 语文
2 王五 60 数学
3 李四 22 语文
3 李四 13 数学
4 张飞 97 语文
4 张飞 89 数学
  1. 查询每个人的平均分
  2. 寻找到总分最高的学员的名字
  3. 查询语文成绩最高的学员的名字
  4. 修改把成绩小于60的分数改为60
  5. 删除语文成绩等于60的数据

来自于gy_stu表
— 查询出1994年到1996年之间（不包含96年的）(birthday)出生的所有学生
— 姓张（gy_stu）的学生
— 查询出所有学生姓名(stu_name)以 明 字结尾的学生
— 查询出所有2月份生日(birthday)的学生
— 查询出马什么梅(stu_name)的学生
— 查询出名字(stu_name)为3个字的学生
— 查出学历(education)为大专或本科的学生
— 查询出（age）22岁的女同学，25岁的男同学
— 查询出（省份province，地区 city ）河南省且开封市，安徽省且巢湖地区，江苏省且启东市的学生
— 查询出class_id为空的学生
— 查询出班级名称（class_name）为null的数据
— 查询出(stu_name)姓王的学生
— 查询出(birthday)不是2号生日的学生
— 查询出是所有(sex)性别不为女生的学生

作业

  1. 查询教授的女学生最多的老师名字，和人数
  2. 查询教授课程平均分最低的老师的名字
  3. 查询20033班级各科成绩最低的学员的名字，（用union连接）
  4. 查询男学员个人平均分大于30的学员的名字
  5. 查询计算机系个人平均分最低的10个学员的名字和平均分，按照平均分降序排列