--- 創(chuàng)建學(xué)生成績(jī)表
mysql> create table exam(
    -> id int primary key comment '學(xué)號(hào)',
    -> name varchar(20) comment '學(xué)生姓名',
    -> email varchar(50) not null comment '電子郵箱',
    -> chinese decimal(4,1) comment '語(yǔ)文成績(jī)',
    -> english decimal(4,1) comment '英語(yǔ)成績(jī)',
    -> math decimal(4,1)  comment'數(shù)學(xué)成績(jī)'
    -> );
Query OK, 0 rows affected (0.02 sec)
--- 插入學(xué)生成績(jī)數(shù)據(jù)
mysql> insert into exam values(202301,'張華','123452@163.com',69,112,110),
    -> (202302,'李三','1452563@163.com',115.5,120,89),
    -> (202303,'宋七','36215465@qq.com',110,113,66),
    -> (202304,'王五','15547522@163.com',89,65,78),
    -> (202305,'趙四','15623355@163.com',90,112,130),
    -> (202306,'李八','18625222@163.com',null,null,null);
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0
---學(xué)生成績(jī)表結(jié)構(gòu)
mysql> desc exam;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | NO   | PRI | NULL    |       |
| name    | varchar(20)  | YES  |     | NULL    |       |
| email   | varchar(50)  | NO   |     | NULL    |       |
| chinese | decimal(4,1) | YES  |     | NULL    |       |
| english | decimal(4,1) | YES  |     | NULL    |       |
| math    | decimal(4,1) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
--- 數(shù)據(jù)詳表
mysql> select * from exam;
+--------+------+------------------+---------+---------+-------+
| id     | name | email            | chinese | english | math  |
+--------+------+------------------+---------+---------+-------+
| 202301 | 張華 | 123452@163.com   |    69.0 |   112.0 | 110.0 |
| 202302 | 李三 | 1452563@163.com  |   115.5 |   120.0 |  89.0 |
| 202303 | 宋七 | 36215465@qq.com  |   110.0 |   113.0 |  66.0 |
| 202304 | 王五 | 15547522@163.com |    89.0 |    65.0 |  78.0 |
| 202305 | 趙四 | 15623355@163.com |    90.0 |   112.0 | 130.0 |
| 202306 | 李八 | 18625222@163.com |    NULL |    NULL |  NULL |
+--------+------+------------------+---------+---------+-------+
6 rows in set (0.01 sec)

#計(jì)算表中的總行數(shù)
mysql> select count(*) from exam;
+----------+
| count(*) |
+----------+
|        6 |
+----------+
1 row in set (0.01 sec
#計(jì)算表中chinese中的總行數(shù)
mysql> select count(math) from exam;
+-------------+
| count(math) |
+-------------+
|           5 |
+-------------+
1 row in set (0.00 sec)
#計(jì)算表中english列的總行數(shù),并去重
mysql> select count(distinct english) from exam;
+-------------------------+
| count(distinct english) |
+-------------------------+
|                       4 |
+-------------------------+
1 row in set (0.01 sec)

#查詢(xún)exam表當(dāng)中, 英語(yǔ)成績(jī)?yōu)?12的人數(shù)
mysql> select count(english) from exam where english = 112;
+----------------+
| count(english) |
+----------------+
|              2 |
+----------------+
1 row in set (0.00 sec)

#計(jì)算數(shù)學(xué)成績(jī)的總和, 并命名為emath
mysql> select sum(math) as emath from exam;
+-------+
| emath |
+-------+
| 473.0 |
+-------+
1 row in set (0.00 sec)
#計(jì)算英語(yǔ)成績(jī)低于120分的成績(jī)總和
mysql> select sum(english) from exam where english < 120;
+--------------+
| sum(english) |
+--------------+
|        402.0 |
+--------------+
1 row in set (0.00 sec)

# 計(jì)算所有同學(xué)的語(yǔ)文成績(jī)的平均值
mysql> select avg(chinese) from exam;
+--------------+
| avg(chinese) |
+--------------+
|     94.70000 |
+--------------+
1 row in set (0.03 sec)
#計(jì)算總分的平均分
mysql> select avg(math+chinese+english) as 總均分 from exam;
+-----------+
| 總均分    |
+-----------+
| 293.70000 |
+-----------+
1 row in set (0.00 sec)

# 查詢(xún)總分最高的同學(xué)總分
mysql> select max(math+english+chinese) as 總成績(jī) from exam;
+--------+
| 總成績(jī) |
+--------+
|  332.0 |
+--------+
1 row in set (0.00 sec)
#查詢(xún)單科英語(yǔ)成績(jī)的最高分的姓名
mysql>  select max(english) from exam;
+--------------+
| max(english) |
+--------------+
|        120.0 |
+--------------+
1 row in set (0.00 sec)

mysql> select (chinese+math+english) as total  from exam order by total desc limit 1;
+-------+
| total |
+-------+
| 332.0 |
+-------+
1 row in set (0.00 sec)

# 查詢(xún)總成績(jī)最高的同學(xué)信息
mysql> select *
    -> from exam
    -> where chinese+math+english = (select max(chinese+math+english)
    ->                               from exam
    ->                              );
+--------+------+------------------+---------+---------+-------+
| id     | name | email            | chinese | english | math  |
+--------+------+------------------+---------+---------+-------+
| 202305 | 趙四 | 15623355@163.com |    90.0 |   112.0 | 130.0 |
+--------+------+------------------+---------+---------+-------+
1 row in set (0.00 sec)

# 查詢(xún)最小的總分
mysql> select min(math + english + chinese) as total from exam;
+-------+
| total |
+-------+
| 232.0 |
+-------+
1 row in set (0.00 sec)
#查詢(xún)最小總分的全部信息
mysql> select * from exam 
       where chinese+english+math = (select min(math + english + chinese) as                                      total from exam);
+--------+------+------------------+---------+---------+------+
| id     | name | email            | chinese | english | math |
+--------+------+------------------+---------+---------+------+
| 202304 | 王五 | 15547522@163.com |    89.0 |    65.0 | 78.0 |
+--------+------+------------------+---------+---------+------+
1 row in set (0.00 sec)

IFNULL(expression_1,expression_2);

#查找某位同學(xué)的聯(lián)系方式 （如果電話為空，使用郵箱）
mysql> select name,ifnull(phone,email) as 聯(lián)系方式 from exam;
+------+------------------+
| name | 聯(lián)系方式         |
+------+------------------+
| 張華 | 13225631456      |
| 李三 | 15698475235      |
| 宋七 | 15236486952      |
| 王五 | 15547522@163.com |
| 趙四 | 13562698745      |
| 李八 | 18625222@163.com |
+------+------------------+
6 rows in set (0.00 sec)
#其中，王五和李八的電話為空，則使用其電子郵箱作為其聯(lián)系方式。

mysql> select year('2018-01-01');
+--------------------+
| year('2018-01-01') |
+--------------------+
|               2018 |
+--------------------+
1 row in set
mysql> select dayname('2018-01-01') dayname;
+---------+
| dayname |
+---------+
| Monday  |
+---------+
1 row in set

#示例職工表
mysql> create table emp(
    ->  id int primary key auto_increment,
    ->  name varchar(20) not null,
    ->  role varchar(20) not null,
    ->  salary numeric(11,2)
    -> );
Query OK, 0 rows affected (0.05 sec)
mysql> insert into emp(name, role, salary) values
    -> ('張三','科員', 2000.54),
    -> ('宋七','副廳', 8996.99),
    -> ('趙四','科員', 1800.11),
    -> ('李八','科長(zhǎng)', 4540.5),
    -> ('宋九','科員', 2356.33);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0
mysql> select * from emp;
+----+------+------+---------+
| id | name | role | salary  |
+----+------+------+---------+
|  1 | 張三 | 科員 | 2000.54 |
|  2 | 宋七 | 副廳 | 8996.99 |
|  3 | 趙四 | 科員 | 1800.11 |
|  4 | 李八 | 科長(zhǎng) | 4540.50 |
|  5 | 宋九 | 科員 | 2356.33 |
+----+------+------+---------+
5 rows in set (0.00 sec)

mysql> select role,max(salary),min(salary) from emp group by role;
+------+-------------+-------------+
| role | max(salary) | min(salary) |
+------+-------------+-------------+
| 副廳 |     8996.99 |     8996.99 |
| 科員 |     2356.33 |     1800.11 |
| 科長(zhǎng) |     4540.50 |     4540.50 |
+------+-------------+-------------+
3 rows in set (0.00 sec)

mysql> select role,max(salary) from emp group by role having max(salary) > 4000;
+------+-------------+
| role | max(salary) |
+------+-------------+
| 副廳 |     8996.99 |
| 科長(zhǎng) |     4540.50 |
+------+-------------+
2 rows in set (0.00 sec)

mysql> select role,round(avg(salary)) from emp where salary < 5000 group by role;
+------+--------------------+
| role | round(avg(salary)) |
+------+--------------------+
| 科員 |               2052 |
| 科長(zhǎng) |               4541 |
+------+--------------------+
2 rows in set (0.01 sec)

mysql> select * from class;
+----------+------------+
|    id    |   name     |
+----------+------------+
|        1 | 計(jì)科1班    |
|        2 | 計(jì)科2班    |
+----------+------------+
2 rows in set (0.00 sec)
mysql> select * from student;
+------+--------+----------+
| id   | name   | class_id |
+------+--------+----------+
|    1 | 張三   |        1 |
|    2 | 李四   |        1 |
|    3 | 王五   |        2 |
|    4 | 趙六   |        2 |
+------+--------+----------+
#兩個(gè)表進(jìn)行笛卡爾集之后可以得到結(jié)果
mysql> select * from student, class;
+------+--------+----------+----------+------------+
| id   | name   | class_id |   id     | name       |
+------+--------+----------+----------+------------+
|    1 | 張三   |        1 |        1 | 計(jì)科1班    |
|    1 | 張三   |        1 |        2 | 計(jì)科2班    |
|    2 | 李四   |        1 |        1 | 計(jì)科1班    |
|    2 | 李四   |        1 |        2 | 計(jì)科2班    |
|    3 | 王五   |        2 |        1 | 計(jì)科1班    |
|    3 | 王五   |        2 |        2 | 計(jì)科2班    |
|    4 | 趙六   |        2 |        1 | 計(jì)科1班    |
|    4 | 趙六   |        2 |        2 | 計(jì)科2班    |
+------+--------+----------+----------+------------+
8 rows in set (0.00 sec)

select 字段列表 from 表1 ，表2 where ... = .. .;

select  字段列表 from 表1 inner join 表2 on 連接條件

#隱式內(nèi)連接
mysql> select student.name, class.name from class,student
    ->       where student.class_id = class.id;
+------+---------+
| name | name    |
+------+---------+
| 張三 | 計(jì)科1班 |
| 李四 | 計(jì)科1班 |
| 王五 | 計(jì)科2班 |
| 趙六 | 計(jì)科2班 |
+------+---------+
4 rows in set (0.01 sec)
# 也可以對(duì)表名起一個(gè)別名
mysql> select s.name, c.name from class c,student s
    ->   where s.class_id = c.id;
+------+---------+
| name | name    |
+------+---------+
| 張三 | 計(jì)科1班 |
| 李四 | 計(jì)科1班 |
| 王五 | 計(jì)科2班 |
| 趙六 | 計(jì)科2班 |
+------+---------+
4 rows in set (0.00 sec)
#顯式內(nèi)連接
mysql>  select student.name, class.name from student inner join class on student.class_id = class.id;
+------+---------+
| name | name    |
+------+---------+
| 張三 | 計(jì)科1班 |
| 李四 | 計(jì)科1班 |
| 王五 | 計(jì)科2班 |
| 趙六 | 計(jì)科2班 |
+------+---------+
4 rows in set (0.03 sec)

select 字段列表 from 表1 left join 表2 on 條件 ... ;

select  字段列表 from 表1 right join 表2 on 條件 ....;

#表結(jié)構(gòu)如下所示
mysql> select * from class;
+----+---------+
| id | name    |
+----+---------+
|  1 | 計(jì)科1班 |
|  2 | 計(jì)科2班 |
|  3 | NULL    |
+----+---------+
3 rows in set (0.00 sec)
mysql> select * from student;
+----+------+----------+
| id | name | class_id |
+----+------+----------+
|  1 | 張三 |        1 |
|  2 | 李四 |        1 |
|  3 | 王五 |        2 |
|  4 | 趙六 |        2 |
|  5 | 宋六 |     NULL |
+----+------+----------+
5 rows in set (0.01 sec)
mysql> select * from student left join class on student.class_id = class.id;
+----+------+----------+------+---------+
| id | name | class_id | id   | name    |
+----+------+----------+------+---------+
|  1 | 張三 |        1 |    1 | 計(jì)科1班 |
|  2 | 李四 |        1 |    1 | 計(jì)科1班 |
|  3 | 王五 |        2 |    2 | 計(jì)科2班 |
|  4 | 趙六 |        2 |    2 | 計(jì)科2班 |
|  5 | 宋六 |     NULL | NULL | NULL    |
+----+------+----------+------+---------+
5 rows in set (0.00 sec)
mysql> select * from class left join student on class.id = student.class_id;
+----+---------+------+------+----------+
| id | name    | id   | name | class_id |
+----+---------+------+------+----------+
|  1 | 計(jì)科1班 |    1 | 張三 |        1 |
|  1 | 計(jì)科1班 |    2 | 李四 |        1 |
|  2 | 計(jì)科2班 |    3 | 王五 |        2 |
|  2 | 計(jì)科2班 |    4 | 趙六 |        2 |
|  3 | NULL    | NULL | NULL |     NULL |
+----+---------+------+------+----------+
5 rows in set (0.00 sec)
#使用左外連接或者右外連接的前提一般是 , 需求的列中部分元素為空值,直接使用內(nèi)連接無(wú)法顯示出來(lái),所以采用左外連接或者 右外連接

select 字段列表 from 表A 別名A join 表A 別名B on 條件

# 查詢(xún)員工以及所屬領(lǐng)導(dǎo)的名稱(chēng) (內(nèi))
select a.name,b.name  from emp a,emp b  where a.managerid = b.id;
(查詢(xún)的結(jié)果不包含領(lǐng)導(dǎo)為null的字段)
# 查詢(xún)所有員工及其領(lǐng)導(dǎo)的名字 , 如果員工沒(méi)有領(lǐng)導(dǎo), 也需要查詢(xún)出來(lái)
select a.name '員工',b.name '領(lǐng)導(dǎo)' from emp a left join emp b on a.managerid = b.id;
#使用左外連接會(huì)將所有員工的姓名全部顯示出來(lái), 包括沒(méi)有領(lǐng)導(dǎo)的

select * from course where id<3
union all
select * from course where name= '數(shù)據(jù)結(jié)構(gòu)';
# 當(dāng)查詢(xún)字段不一致時(shí)會(huì)報(bào)錯(cuò)

select * from course where id<3
union 
select * from course where name= '數(shù)據(jù)結(jié)構(gòu)';

select * from course where id < 3 or name = '數(shù)據(jù)結(jié)構(gòu)';

select * from table1 where column1 = (select column1 fropm table2);

#查詢(xún)張三同學(xué)的同班同學(xué)
select * from student where class_id =(select class_id from student where name = '張三');

#  查詢(xún)選修課程名稱(chēng)為語(yǔ)文或者英語(yǔ)的分?jǐn)?shù)信息 (in)
select * from score where course_id in 
(select id from course where name='語(yǔ)文' or name='英文');
#查詢(xún)語(yǔ)文課程分?jǐn)?shù)大于其他任意一名學(xué)生的信息(any)
select * from score where grade>any(select grade from score where course_id = (select class_id from student where name = '語(yǔ)文'));

-- 獲取“計(jì)算機(jī)1班”的平均分，將其看作臨時(shí)表
select avg( sco.score ) score from 
score sco
join student stu ON sco.student_id = stu.id
join classes cls ON stu.classes_id = cls.id
where cls.NAME = '計(jì)算機(jī)1班';