MySQL的一些基本命令笔记(3)

 指明外键：
1 ：1
两个表中的主键都可以当成外键
1 ：N
在 "多" 的实体表中新增加一个字段，该字段是 "一" 实体表的主键
M : N
拆成两个1 ：N的关系
在 "多" 的实体表中新增加一个字段，该字段是 "一" 实体表的主键、

逻辑模型---->用SQL实现数据库
create database yr;

use yr;
在students表中增加两个限制；
1.not null非空限制
2.primary key 主键限制
create table students(
    -> 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)
    -> );
rank    级别
gender  性别

create  table textbooks(
    -> ISBN varchar(20) not null primary key,
    -> name varchar(20),
    -> auther varchar(20),
    -> publisher varchar(20)
    -> );
author       作者
publisher    出版社
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 textbooks(ISBN)
    -> );
instructor    授课教师
credit        学分
references    关联 
foreign key   外键

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 students(s_id),
    -> foreign key(c_id) references courses(c_id) );

insert into students values(1,"Ann","F",21,"beijing");

insert into textbooks 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);

insert into students values(2,"Bob","M",23,NULL);
错误的 s_id是主键不能重复
insert into students values(2,"ob","M",23,NULL);
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'  提示2重复

注意：SQL中，空值是null

思考题：************
1.查询students表中，地址为空的学生信息
 select * from students where address is null;[当为null的时候不能用=必须使用is]
2.查询students表中，地址不为空的学生信息 
 select * from students where address is not null;

演示foreign key限制错误：
insert into courses values("2","python",3,"wei","10000");[老师的值一定是teacher表中应该有的老师，如果没有报错，这个外键是teachers中的主键]

insert into teachers values("Wei","M","Instructor");

insert into textbooks values("10000","Intro to python","Guido","Beijing Press");

规范化：
第一范式：
  1.消除重复的行和列
  2.定义主键，唯一标识所有记录
  3.所有其他字段必须直接或间接的依赖于主键
  4.所有字段必须只包含一个值
  5.每个字段中的所有值必须是相同的数据类型
第二范式：
   1.表必须处于1NF中
   2.所有非键值必须完全函数依赖于主键
   3.当主键为复合键时，必须删除部分依赖

高级SQL:
create table orde(
    -> o_id int not null,
    -> o_name varchar(50),
    -> date datetime,
    -> c_id int,
    -> amount decimal(10,2)
    -> );

    
insert into orde values(100,"computer","2011-11-1 12:34:56",1,8800)； 
insert into orde values
(101,"iphone","2011-10-10 15:20:56",3,6600),
(103,"ipad","2017-9-13 09:34:45",4,450),
(104,"pen","2016-10-18 15:20:56",3,6600),
(105,"iphone","2015-11-10 16:20:56",5,6000),
(106,"ipad","2014-09-12 14:20:56",2,10000);

joins
利用两个表中共同含有的列的相同值来连接

select 列名 from 第一个表，第二个表
where 第一个表.列名 = 第二个表.列名

select id,name,salary,o_name,amount from customers,orde where customers.id =orde.c_id;

select id,name,salary,o_name,amount from customers,orde;[就变成笛卡尔积了就是两个表格的所有组合]

练习;
打印出顾客消费额度超过其工资的人员名单和工资信息以及消费额度
select id,name,salary,amount from customers,orde where (customers.salary < orde.amount)and(customers.id =orde.c_id); 

子查询;嵌入到另一个select语句中的select语句

select 列名或者表达式 from 表名   where 表达式 比较操作符{all|any}(子查询语句)；[子查询语句一定是select语句]
1.返回单值的子查询
select * from customers where customers.id =(select c_id from orde where o_id=100);
等同于
select c.id,c.name,c.age,c.address,c.salary from customers as c,orde as o where c.id = o.c_id and o.o_id=100;

select c.id,c.name,c.age,c.address,c.salary from customers as c[这个是重命名相当于customers=c],orde as o where c.id = o.c_id and o.o_id=100;

2.返回多值的子查询
select * from customers where customers.id in (1,2,3);

select * from customers where customers.id in (select c_id from orde where amount>5000);
[顾客的身份的id在所有订单消费大于5000的c_id中 ]
练习：
选出地址在北京或者上海的顾客，该顾客消费7000元
select * from customers where customers.id in (select c_id from orde where amount>7000 ) and(address="beijing" or "shanghai" );

子查询与聚合函数配套练习：************
选择消费最少的顾客信息
select * from customers where customers.id =(select c_id from orde where amount=(select min(amount) from orde));
select * from customers where customers.id =(select c_id from orde order by amount asc limit 1);
打印出orde表中amount的最小值
select min(amount) from orde；
select c_id from orde where amount=450；

选择工资salary最少的顾客的信息
select * from customers where salary= (select min(salary) from customers);

all/any和子查询的用法
  select * from customers where age > any(24,25,27);
  select * from customers where age > all(24,25,27);

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)
    -> );

insert into customers_1 select * from customers where id in (select c_id from orde where amount>8000);
insert into customers_1 select * from customers where id[customers的客户id号] in (select c_id from orde where amount>8000);

update[更改]语句的嵌套查询

update 表名 set 列名=新的值 where 列名 操作符（select 列名 from 表名 [where 条件]);
示例：
 update customers set salary =salary*1.5 where age in (select age from customers_1 where age>=20);

练习：
1.把customers表中工资小于所有顾客的平均消费的人的工资提高一倍
select * from customers where id  in( select c_id from orde where amount= (select avg(amount) from orde) <= customers.salary);
先找出数据然后在如下进行修改
update customers set salary =salary*1.5 where id  in( select c_id from orde where amount= (select avg(amount) from orde) <= customers.salary);
update customers set salary =salary*1.5 where salary>=(select avg(amount) from orde);
2.把customers表中的新数据，按照工资由大到小排序[不用where，where是加条件用的]
select * from customers  order by salary desc;
 
3.求出customers表中各个城市的人的平均工资
select address,avg(salary) from customers group by address;

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

select * from customers where customers.id in (select c_id from orde where amount<(select avg(salary) from orde));
5.列出工资高于平均消费额的客户的总个数
select count(*) from customers where salary>(select avg(amount) from orde);