mysql笔记3 - 高级SQL查询（主键，外键，嵌套）

2017/10/31 逻辑模型到->用SQL实现数据库
1:1两个人表中的主键都可以做外键
1：n在多的实体表中新增一个字段，该字段是“一”实体表的主键
m：n拆成两个1：n的关系
pk primary 主键
fk foreign 外键
---------------------------------------------------------------------
在student表中，增添两个限制：
1.not null 非空限制
2.primary key 主键限制
==================================================================+++
实例-创建主键和外键：
sql> create database yang_test;
mysql> use yang_test;


创建学生表：
mysql> create table student(s_id int not null primary key,name varchar(20),gender varchar(20),age int,address varchar(50));


创建教师表：
create table teachers(name varchar(20) not null primary key,
gender varchar(20),rank varchar(20));


创建教材表：
create table textbook(ISBN varchar(20) not null primary key,name varchar(20),author varchar(20),publisher varchar(20));
注：textbook 教材


创建课程表：
create table courses(
c_id int not null primary key,
name varchar(20),
credit int,
instructor varchar(20),
ISBN varchar(20),
foreign key(instructor) references teachers(name),
foreign key(ISBN) references textbook(ISBN));
注：instructor授课教师名、credit学分、references关联
外键属性的内容a，一定要先出现在 teachers 表中 中


创建注册表：
create table registration(
s_id int not null,
c_id int not null,
term varchar(20),
grade decimal(4,1),
primary key(s_id,c_id),
foreign key(s_id) references student(s_id),
foreign key(c_id) references courses(c_id));


mysql> show tables;
+---------------------+
| Tables_in_yang_test |
+---------------------+
| courses             |
| registration        |
| student             |
| teachers            |
| textbook            |
==================================================================+++
向表中插入信息：
insert into student values (1,'Ann','F',21,'Beijing');
insert into textbook values('00000','Intro to SQL','Yang','renmin press');
insert into teachers values('Yang','F','Instructor');
insert into courses values(1,'Databases',3,'Yang','00000');
insert into registration values(1,1,201711,93.0);
=====================================================================
错误演示与总结：
（1）
关于 学生表students 中的 not null
mysql> insert into student values (null,'bob','F',22,'shanghai');
ERROR 1048 (23000): Column 's_id' cannot be null
总结1：sql中空是 null ，不能省略不写，也不能写成' '
（2）
总结2：查询关于空值信息的语句（用is，is not不用==，！=）：
select * from student where address is null;
select * from student where address is not null;
（3）
学生表的学号已经设置为主键，如果出现两个学号2，那么第二个学生的信息输入将提示错误
（4）
在courses表中，有两个外键teachers 和 textbook：
foreign key(instructor) references teachers(name),
foreign key(ISBN) references textbook(ISBN));
和其他的表进行关联，当向courses中插入数据时，直接输入教师名和教材名，会提示错误。教师名和教材名在教师表和教材表中分别为主键，必须在教师表和教材表中出现相应的信息记录，才能在课程表courses中添加对应的信息。
总结为，必须先有对应的老师、教材，才能添加到课程表中的老师记录或教材记录，插入数据时，先插入主键记录，后插入外键记录；
删除数据时，先删除外键记录，后删除主键记录。
insert into textbook values('10000','intro to python','gdio','beijing press');
insert into teachers values('wei','M','Instructor');
insert into courses values(2,'python',4,'wei','10000');
=====================================================================
思考题：
插入以下信息，查询student表中地址为空 和 不为空的学生的信息
insert into student values (2,'bob','F',23,null);
mysql> select * from student where address is null;
+------+------+--------+------+---------+
| s_id | name | gender | age  | address |
+------+------+--------+------+---------+
|    2 | bob  | F      |   23 | NULL    |
+------+------+--------+------+---------+
mysql> select * from student where address is not null;
+------+------+--------+------+---------+
| s_id | name | gender | age  | address |
+------+------+--------+------+---------+
|    1 | Ann  | F      |   21 | Beijing |
+------+------+--------+------+---------+


====================================================================
高级SQL：
create table orders(o_id int not null,o_name varchar(50),data datetime,c_id int,amount decimal(10,2));




insert into orders values 
(100,'computer','2011-11-1 12:34:56',1,8800);
insert into orders values 
(101,'iphone','2011-10-10 15:20:56',3,6600),
(102,'microphone','2017-09-10 09:34:56',4,450),
(102,'ipad','2014-02-27 18:24:17',4,9200),
(103,'iwatch','2017-09-13 10:34:22',2,5700),
(105,'mouse','2012-06-23 09:44:33',5,200),
(106,'pen','2013-03-31 21:34:23',6,300),
(107,'iphone','2016-04-23 07:19:22',7,7800),
(108,'ipad','2016-02-12 20:20:20',5,12000);


mysql> select * from orders;
+------+------------+---------------------+------+----------+
| o_id | o_name     | data                | c_id | amount   |
+------+------------+---------------------+------+----------+
|  100 | computer   | 2011-11-01 12:34:56 |    1 |  8800.00 |
|  101 | iphone     | 2011-10-10 15:20:56 |    3 |  6600.00 |
|  102 | microphone | 2017-09-10 09:34:56 |    4 |   450.00 |
|  102 | ipad       | 2014-02-27 18:24:17 |    4 |  9200.00 |
|  103 | iwatch     | 2017-09-13 10:34:22 |    2 |  5700.00 |
|  105 | mouse      | 2012-06-23 09:44:33 |    5 |   200.00 |
|  106 | pen        | 2013-03-31 21:34:23 |    6 |   300.00 |
|  107 | iphone     | 2016-04-23 07:19:22 |    7 |  7800.00 |
|  108 | ipad       | 2016-02-12 20:20:20 |    5 | 12000.00 |
+------+------------+---------------------+------+----------+
mysql> select * from customers;
+------+----------+------+-----------+----------+
| ID   | NAME     | AGE  | ADDRESS   | SALARY   |
+------+----------+------+-----------+----------+
|    2 | li       |   30 | beijing   |  2645.00 |
|    3 | jeremy   |   20 | shanghai  |  2300.00 |
|    4 | Bob      |   25 | beijing   |  8596.25 |
|    5 | aaa      |   25 | beijing   |  8596.25 |
|    6 | bbb      |   25 | beijing   |  8596.25 |
|    7 | ccc      |   27 | beijing   |  8596.25 |
|    8 | xioayu   |   24 | shenzhen  | 60000.00 |
|    9 | xiaohu   |   25 | guangzhou | 60000.00 |
|    1 | Ann      |   22 | zhuhai    | 30000.00 |
|   10 | xiaoming |   25 | beijing   |  2000.00 |
+------+----------+------+-----------+----------+


1.joins-链接查询:
利用两个表中共同含有的列的相同值来链接
select列名 from 第一个表，第二个表 where 第一个表.列名 = 第二个表.列名；


实例-夺标链接查询-实质joins：
mysql> select id,name,salary,o_name,amount from customers,orders where customers.id = orders.c_id;
+------+--------+----------+------------+----------+
| id   | name   | salary   | o_name     | amount   |
+------+--------+----------+------------+----------+
|    1 | Ann    | 30000.00 | computer   |  8800.00 |
|    3 | jeremy |  2300.00 | iphone     |  6600.00 |
|    4 | Bob    |  8596.25 | microphone |   450.00 |
|    4 | Bob    |  8596.25 | ipad       |  9200.00 |
|    2 | li     |  2645.00 | iwatch     |  5700.00 |
|    5 | aaa    |  8596.25 | mouse      |   200.00 |
|    6 | bbb    |  8596.25 | pen        |   300.00 |
|    7 | ccc    |  8596.25 | iphone     |  7800.00 |
|    5 | aaa    |  8596.25 | ipad       | 12000.00 |
+------+--------+----------+------------+----------+


练习：
打印顾客消费额度超过其工资的人员名单和工资信息以及消费额度
提示：select  from  where  ()  and ()
mysql> select name,salary,amount from customers,orders where (orders.c_id = customers.id) and (orders.amount > customers.salary); 
+--------+---------+----------+
| name   | salary  | amount   |
+--------+---------+----------+
| jeremy | 2300.00 |  6600.00 |
| Bob    | 8596.25 |  9200.00 |
| li     | 2645.00 |  5700.00 |
| aaa    | 8596.25 | 12000.00 |
+--------+---------+----------+


====================================================================
2.子查询-嵌入到另一个select语句中的select语句中：
select 列名或者表达式 from 表名 where 表达式 比较操作符 {all|any}(子查询语句)；
(1)返回单值的子查询（=）
select * from customers where customers.id =
 (select c_id from orders where o_id = 100);
orders表o_id = 100 -> orders表o_id c_id -> customers表id ->customers表信息
实例：
mysql> select * from customers where customers.id =
    ->  (select c_id from orders where o_id = 100);
+------+------+------+---------+----------+
| ID   | NAME | AGE  | ADDRESS | SALARY   |
+------+------+------+---------+----------+
|    1 | Ann  |   22 | zhuhai  | 30000.00 |
+------+------+------+---------+----------+
1 row in set (0.00 sec)


单值子查询等效语句（重命名customers as c,）：
select c.id,c.name,c.age,c.address,c.salary from customers as c,orders as o where c.id = orders.c_id and orders.o_id = 100; #@@@@@@@@@@@@@@@@@@@


(2)返回多值的子查询（in）
实例：
select * from customers where customers.id = 1;
select * from customers where customers.id in (1,2,3);
select * from customers where customers.id in (select c_id from orders where amount > 5000);


练习：
选出顾客的消费大于2000元，同时地址是北京或者上海？？？
select * from customers where customers.id in (select c_id from orders where amount > 2000) and address in ('beijng','shanghai');


选出消费最少的顾客信息？？？
提示：单值的子查询，min(...)
错解：
select * from customers where customers.id = (select c_id from orders where) and (select min(amount) from orders);
正解：
select * from customers where customers.id = (select c_id from orders where amount = (select min(amount) from orders));


选出工资最少的顾客信息(注意这里是顾客的所有信息，不仅仅是选出薪资信息)？
select * from customers where salary = (select min(salary)from customers);


(3)all，any和子查询：#@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
all:所有的
any：其中之一的，任选其一
select * from customers where age > any(24,25,27);
select * from customers where age > all(24,25,27);


(4)insert语句嵌套子查询：
insert into 表名 [列名1,列名2,...,列名n] values (value1,value2,...,valueN);


insert into 表名 [列名1,列名2,...,列名n] select [列名1,列名2,...,列名n] from 表名 [where 条件];


create table customers_1(
id int,
name varchar(20),
age int,
address char(25),
salary decimal(18,2));


使用嵌套查询将消费额度大于8000元的客户的全部信息 存放在新生成的customers_1表中：
insert into customers_1 select * from customers where id in (select c_id from orders where amount > 8000)
注意：这里的 in 相当于python中的for i in range（1,10）,是迭代的，会将后面所有的数据传递给前面，而不是只做in的判断。


(5)update语句嵌套子查询：
update 表名 set 列名 = 新的值 where 列名 操作符 (select 列名 from 表名 [where 条件]);
update customers set salary = salary * 1.5 where age in (select age from customers_1 where age >= 25);
 注意: where a in b, b是一个数据集合，其中的所有数据都将传给a做匹配


练习：
1.将customers表中工资小于所有顾客的平均消费的人的工资提高一倍
提示：update嵌套select子查询，聚合函数avg
2.将customers表中的新数据，按照工资由大到小排列
3.求出customers表中各个城市的人的平均工资
4.求出customers表中 消费额度大于 全体顾客的平均工资 这样的顾客的全部信息
提示：select嵌套select子查询
5.列出工资高于平均消费额的客户额的客户的总个数
提示：select嵌套select子查询，聚合函数count（*）


1.将customers表中工资小于所有顾客的平均消费的人的工资提高一倍
我自己的解法-错误：
update customers set salary = salary * 2 where (customers.id in orders.c_id) and (salary < select avg(amount) from orders);
正解：
update customers set salary = salary * 2 where customers.id in (select c_id from orders group by c_id having customers.salary < avg(amount));


2.将customers表中的新数据，按照工资由大到小排列
select * from customers order by salary desc;


3.求出customers表中各个城市的人的平均工资
select avg(salary),address from customers group by address;


4.求出customers表中 消费额度大于 全体顾客的平均工资 这样的顾客的全部信息
select * from customers where customers.id in (select c_id from orders where amount > (select avg(salary) from customers));


5.列出工资高于平均消费额的客户额的客户的总个数
select * from customers;
select count(*) from customers;
mysql> select count(*) from customers;
+----------+
| count(*) |
+----------+
|       10 |
+----------+
select count(*) from customers where customers.id in (select c_id from orders group by c_id having customers.salary > avg(amount));
select * from customers where customers.id in (select c_id from orders group by c_id having customers.salary > avg(amount));