MySQL约束和多表查询

骑猪看日落 2023-06-22 06:23 22阅读 0赞

### MySQL 数据库 ###

#### 回顾 ####

1 数据库（database）：存储数据的仓库。
    
    2 数据库管理系统(DBMS  DataBase Manager System):  MySQL  Oracle  SQL Server DB2
    
    3 MySQL安装、卸载
    
    4 SQL语句：
    
    	DDL 数据定义语言   创建库，创建表  create database create table
    
    	DML 数据操纵语言   添加  删除   更新   insert  delete  udpate
    
    	DQL 数据查询语言   select 
    
    	DCL 数据控制语言， 用户的创建 权限分配。
    
    5 DQL	
    
    	简单查询  select * from emp;
    
    	条件查询   where    运算符    =    !=   <>    >   >=    <   <=      in    between   and   and   or  not     +  -  *  /  %		
    
        模糊查询  like  通配符   _表示一个字符     %表示任意多个字符
    
    	字段控制    distinct  去重     合并     别名
    
    	排序   order by
    
    	聚合函数   count  sum   max  min   avg
    
    	分组查询  group by   having
    
    	limit 限制查询
    	
    	书写顺序select 列表  from  表 【where--->group by--->having--->order by--->limit】
    	执行顺序 from 表 where--->group by--->having-->select--->order by--->limit

#### 今日内容 ####

1、数据库完整性(约束)
    2、多表查询
    3、综合练习

#### 教学目标 ####

1、掌握SQL语言数据的完整性
    2、掌握内连接
    3、掌握外连接
    4、掌握子查询

#### 第一节 数据的完整性 ####

作用：保证用户输入的数据保存到数据库中是正确的。

确保数据的完整性 = 在创建表时给表中添加约束

完整性的分类：

*  实体完整性:
 *  域完整性:
 *  引用完整性: 学生表(学号 ,姓名) 成绩表( 学号,科目,成绩)

##### 1.1 实体完整性 #####

实体：即表中的一行(一条记录)代表一个实体（entity）

实体完整性的作用：标识每一行数据不重复。

约束类型：

**主键约束（primary key）**

**唯一约束(unique)**

**自动增长列(auto\_increment)**

###### 1.1.1 主键约束（primary key） ######

注：每个表中要有一个主键。

特点：数据唯一，且不能为null

示例：

第一种添加方式：

CREATE TABLE student(
    	id int primary key,
    	name varchar(50)
    );

第二种添加方式：此种方式优势在于，可以创建联合主键

CREATE TABLE student(
    	id int,
    	name varchar(50),
    	primary key(id)
    );

CREATE TABLE student(
    	classid int,
    	stuid int,
    	name varchar(50),
    	primary key(classid，stuid)
    );

第三种添加方式：（不推荐）

CREATE TABLE student(
    	id int,
    	name varchar(50)
    );
    ALTER TABLE student  ADD  PRIMARY KEY (id);

###### 1.1.2 唯一约束(unique) ######

特点：数据不能重复。可以为null

CREATE TABLE student(
    	Id int primary key,
    	Name varchar(50) unique
    );

###### 1.1.3 自动增长列(auto\_increment) ######

sqlserver数据库 (identity) oracle数据库( sequence)

给主键添加自动增长的数值，列只能是整数类型

CREATE TABLE student(
    	Id int primary key auto_increment,
    	Name varchar(50)
    ) auto_increment=1;
    INSERT INTO student(name) values(‘tom’);

##### 1.2 域完整性 #####

域完整性的作用：限制此单元格的数据正确，不对照此列的其它单元格比较

域代表当前单元格

域完整性约束：数据类型、非空约束（not null）、默认值约束(default)

check约束（mysql不支持）check(sex='男’or sex=‘女’)

###### 1.2.1 数据类型 ######

数值类型

<table> 
 <thead> 
  <tr> 
   <th>类型</th> 
   <th>大小</th> 
   <th>范围（有符号）</th> 
   <th>范围（无符号）</th> 
   <th>用途</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>TINYINT</td> 
   <td>1 字节</td> 
   <td>(-128，127)</td> 
   <td>(0，255)</td> 
   <td>小整数值</td> 
  </tr> 
  <tr> 
   <td>SMALLINT</td> 
   <td>2 字节</td> 
   <td>(-32 768，32 767)</td> 
   <td>(0，65 535)</td> 
   <td>大整数值</td> 
  </tr> 
  <tr> 
   <td>MEDIUMINT</td> 
   <td>3 字节</td> 
   <td>(-8 388 608，8 388 607)</td> 
   <td>(0，16 777 215)</td> 
   <td>大整数值</td> 
  </tr> 
  <tr> 
   <td><strong>INT或INTEGER</strong></td> 
   <td>4 字节</td> 
   <td>(-2 147 483 648，2 147 483 647)</td> 
   <td>(0，4 294 967 295)</td> 
   <td>大整数值</td> 
  </tr> 
  <tr> 
   <td>BIGINT</td> 
   <td>8 字节</td> 
   <td>(-9 233 372 036 854 775 808，9 223 372 036 854 775 807)</td> 
   <td>(0，18 446 744 073 709 551 615)</td> 
   <td>极大整数值</td> 
  </tr> 
  <tr> 
   <td>FLOAT</td> 
   <td>4 字节</td> 
   <td>查看帮助文档</td> 
   <td>查看帮助文档</td> 
   <td>单精度浮点数值</td> 
  </tr> 
  <tr> 
   <td>DOUBLE</td> 
   <td>8 字节</td> 
   <td>查看帮助文档</td> 
   <td>查看帮助文档</td> 
   <td>双精度浮点数值</td> 
  </tr> 
  <tr> 
   <td><strong>DOUBLE(M,D)</strong></td> 
   <td>8个字节，M表示长度，D表示小数位数</td> 
   <td>同上，受M和D的约束 DUBLE(5,2) -999.99-999.99</td> 
   <td>同上，受M和D的约束</td> 
   <td>双精度浮点数值</td> 
  </tr> 
  <tr> 
   <td><strong>DECIMAL(M,D)</strong></td> 
   <td>对DECIMAL(M,D) ，如果M&gt;D，为M+2否则为D+2</td> 
   <td>依赖于M和D的值，M最大值为65</td> 
   <td>依赖于M和D的值，M最大值为65</td> 
   <td>小数值</td> 
  </tr> 
 </tbody> 
</table>

日期类型:

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。

TIMESTAMP类型有专有的自动更新特性

<table> 
 <thead> 
  <tr> 
   <th>类型</th> 
   <th>大小(字节)</th> 
   <th>范围</th> 
   <th>格式</th> 
   <th>用途</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><strong>DATE</strong></td> 
   <td>3</td> 
   <td>1000-01-01/9999-12-31</td> 
   <td>YYYY-MM-DD</td> 
   <td>日期值</td> 
  </tr> 
  <tr> 
   <td>TIME</td> 
   <td>3</td> 
   <td>‘-838:59:59’/‘838:59:59’</td> 
   <td>HH:MM:SS</td> 
   <td>时间值或持续时间</td> 
  </tr> 
  <tr> 
   <td>YEAR</td> 
   <td>1</td> 
   <td>1901/2155</td> 
   <td>YYYY</td> 
   <td>年份值</td> 
  </tr> 
  <tr> 
   <td><strong>DATETIME</strong></td> 
   <td>8</td> 
   <td>1000-01-01 00:00:00/9999-12-31 23:59:59</td> 
   <td>YYYY-MM-DD HH:MM:SS</td> 
   <td>混合日期和时间值</td> 
  </tr> 
  <tr> 
   <td>TIMESTAMP</td> 
   <td>4</td> 
   <td>1970-01-01 00:00:00/2038 结束时间是第 <strong>2147483647</strong> 秒，北京时间 <strong>2038-1-19 11:14:07</strong>，格林尼治时间 2038年1月19日 凌晨 03:14:07</td> 
   <td>YYYYMMDD HHMMSS</td> 
   <td>混合日期和时间值，时间戳</td> 
  </tr> 
 </tbody> 
</table>

字符串类型:

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET

<table> 
 <thead> 
  <tr> 
   <th>类型</th> 
   <th>大小</th> 
   <th>用途</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><strong>CHAR</strong></td> 
   <td>0-255字符</td> 
   <td>定长字符串 char(10) 10个字符</td> 
  </tr> 
  <tr> 
   <td><strong>VARCHAR</strong></td> 
   <td>0-65535 字节</td> 
   <td>变长字符串 varchar(10) 10个字符</td> 
  </tr> 
  <tr> 
   <td>TINYBLOB</td> 
   <td>0-255字节</td> 
   <td>不超过 255 个字符的二进制字符串</td> 
  </tr> 
  <tr> 
   <td>TINYTEXT</td> 
   <td>0-255字节</td> 
   <td>短文本字符串</td> 
  </tr> 
  <tr> 
   <td><strong>BLOB</strong>（binary large object）</td> 
   <td>0-65 535字节</td> 
   <td>二进制形式的长文本数据</td> 
  </tr> 
  <tr> 
   <td><strong>TEXT</strong></td> 
   <td>0-65 535字节</td> 
   <td>长文本数据</td> 
  </tr> 
  <tr> 
   <td>MEDIUMBLOB</td> 
   <td>0-16 777 215字节</td> 
   <td>二进制形式的中等长度文本数据</td> 
  </tr> 
  <tr> 
   <td>MEDIUMTEXT</td> 
   <td>0-16 777 215字节</td> 
   <td>中等长度文本数据</td> 
  </tr> 
  <tr> 
   <td>LONGBLOB</td> 
   <td>0-4 294 967 295字节</td> 
   <td>二进制形式的极大文本数据</td> 
  </tr> 
  <tr> 
   <td>LONGTEXT</td> 
   <td>0-4 294 967 295字节</td> 
   <td>极大文本数据</td> 
  </tr> 
 </tbody> 
</table>

CHAR和VARCHAR类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

BINARY和VARBINARY类类似于CHAR和VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

BLOB是一个二进制大对象，可以容纳可变数量的数据。有4种BLOB类型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。

有4种TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。

###### 1.2.2 非空约束 ######

not null

CREATE TABLE student(
    	Id int primary key,
    	Name varchar(50) not null,
    	Sex varchar(10)
    );
    INSERT INTO student values(1,’tom’,null);

###### 1.2.3 默认值约束 ######

default

CREATE TABLE student(
    	Id int pirmary key,
    	Name varchar(50) not null,
    	Sex varchar(10) default '男'
    );
    insert intostudent1 values(1,'tom','女');
    insert intostudent1 values(2,'jerry',default);

##### 1.3 引用完整性 #####

（参照完整性）

外键约束：FOREIGN KEY

示例：

第一种添加外键方式。推荐

#学生表(主表)
    CREATE TABLE student(
    	sid int primary key,
    	name varchar(50) not null,
    	sex varchar(10) default '男'
    );

#成绩表（从表）
    create table score(
            id int,
            score int,
            sid int , 
            CONSTRAINT fk_score_sid foreign key(sid) references student(sid)
    );
    -- 外键列的数据类型一定要与主键的类型一致

第二种添加外键方式。

ALTER TABLE score ADD CONSTRAINT fk_stu_score FOREIGN KEY(sid) REFERENCES student(sid);

#### 第二节 多表查询 ####

多个表之间是有关系的，那么关系靠谁来维护?

多表约束：外键约束。

##### 2.1 多表的关系 #####

###### 2.1.1 一对多关系 ######

客户和订单，分类和商品，部门和员工.

一对多建表原则：在多的一方创建一个字段，字段作为外键指向一的一方的主键.

###### 2.1.2 多对多关系 ######

学生和课程:

多对多关系建表原则：需要创建第三张表,中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键.

###### 2.1.3 一对一关系 ######

在实际的开发中应用不多.因为一对一可以创建成一张表.

两种建表原则：

唯一外键对应：在多的一方创建一个外键指向一的一方的主键，将外键设置为unique和非空.

主键对应：让一对一的双方的主键进行建立关系.

##### 2.2 多表查询 #####

多表查询有如下几种：

1 **合并结果集**；UNION 、

UNION ALL

2 **连接查询**

2.1内连接 \[INNER\] JOIN ON

2.2外连接 OUTER JOIN ON

*  左外连接 LEFT \[OUTER\] JOIN
 *  右外连接 RIGHT \[OUTER\] JOIN

**3 子查询**

###### 2.2.1 合并结果集 ######

作用：合并结果集就是把两个select语句的查询结果合并到一起！

合并结果集有两种方式：

l UNION：去除重复记录，例如：SELECT\* FROM t1 UNION SELECT \* FROM t2；

l UNION ALL：不去除重复记录，例如：SELECT \* FROM t1 UNION ALL SELECT \* FROM t2。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NTEzNDMw_size_16_color_FFFFFF_t_70]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NTEzNDMw_size_16_color_FFFFFF_t_70 1]

注意：被合并的两个结果：列数、列类型必须相同。

###### 2.2.2 连接查询 ######

连接查询就是求出多个表的乘积，例如t1连接t2，那么查询出的结果就是t1\*t2。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NTEzNDMw_size_16_color_FFFFFF_t_70 2]

连接查询会产生笛卡尔积，假设集合A=\{a,b\}，集合B=\{0,1,2\}，则两个集合的笛卡尔积为\{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)\}。可以扩展到多个集合的情况。

那么多表查询产生这样的结果并不是我们想要的，那么怎么去除重复的，不想要的记录呢，当然是通过条件过滤。通常要查询的多个表之间都存在关联关系，那么就通过关联关系去除笛卡尔积。

示例 1：现有两张表

emp表

CREATE TABLE emp(
    	empno	int,
    	ename	varchar(50),
    	job		varchar(50),
    	mgr		int,
    	hiredate	date,
    	sal		decimal(7,2),
    	comm	decimal(7,2),
    	deptno	int
    );
    #添加数据SQL语句省略

dept表

CREATE TABLE dept(
    	deptno		int,
    	dname		varchar(14),
    	loc		varchar(13)
    );
    #添加数据SQL语句省略

执行如下SQL语句

select * from emp,dept;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NTEzNDMw_size_16_color_FFFFFF_t_70 3]  
**使用主外键关系做为条件来去除无用信息**

SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NTEzNDMw_size_16_color_FFFFFF_t_70 4]

上面查询结果会把两张表的所有列都查询出来，也许你不需要那么多列，这时就可以指定要查询的列了。

SELECT emp.ename,emp.sal,emp.comm,dept.dname 
    FROM emp,dept 
    WHERE emp.deptno=dept.deptno;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NTEzNDMw_size_16_color_FFFFFF_t_70 5]

**一：内连接**

上面的连接语句就是内连接，但它不是SQL标准中的查询方式，可以理解为方言！

SQL标准的内连接为：

SELECT * 
    FROM emp e 
    INNER JOIN dept d 
    ON e.deptno=d.deptno;
    注意：on后面 主外键关系

内连接的特点：查询结果必须满足条件。

**二：外连接**

包括左外连接和右外连接，外连接的特点：查询出的结果存在不满足条件的可能。

a.左外连接

SELECT * FROM emp e 
    LEFT OUTER JOIN dept d 
    ON e.deptno=d.deptno;

左连接是先查询出左表（即以左表为主），然后查询右表，左表中满足条件和不满足条件都显示出来，右边不满足条件的显示NULL。

我们还是用上面的例子来说明。其中emp表中“张三”这条记录中，部门编号为50，而dept表中不存在部门编号为50的记录，所以“张三”这条记录，不能满足e.deptno=d.deptno这条件。但在左连接中，因为emp表是左表，所以左表中的记录都会查询出来，即“张三”这条记录也会查出，但相应的右表部分显示NULL。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NTEzNDMw_size_16_color_FFFFFF_t_70 6]

b.右外连接

右连接就是先把右表中所有记录都查询出来，然后左表满足条件的显示，不满足显示NULL。例如在dept表中的40部门并不存在员工，但在右连接中，如果dept表为右表，那么还是会查出40部门，但相应的员工信息为NULL。

SELECT * FROM emp e 
    RIGHT OUTER JOIN dept d 
    ON e.deptno=d.deptno;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NTEzNDMw_size_16_color_FFFFFF_t_70 7]

**连接查询心得**：

连接不限于两张表，连接查询也可以是三张、四张，甚至N张表的连接查询。通常连接查询不可能需要整个笛卡尔积，而只是需要其中一部分，那么这时就需要使用条件来去除不需要的记录。这个条件大多数情况下都是使用主外键关系去除。

###### 2.2.3 子查询 ######

一个select语句中包含另一个完整的select语句。

子查询就是嵌套查询，即SELECT中包含SELECT，如果一条语句中存在两个，或两个以上SELECT，那么就是子查询语句了。里面的查询叫做子查询，外层的查询叫父查询，一般情况都是先执行子查询，再执行父查询。

l 子查询出现的位置：

a. where后，作为被查询的条件的一部分；

b. from后，作临时表；

l 当子查询出现在where后作为条件时，还可以使用如下关键字：

a. any

b. all

l 子查询结果集的形式：

a. 单行单列（用于条件）

b. 多行单列（用于条件）

c. 多行多列（用于表）

示例：

**1. 工资高于JONES的员工。**

分析：

查询条件：工资>JONES工资，其中JONES工资需要一条子查询。

第一步：查询JONES的工资

SELECT sal FROM emp WHERE ename='JONES';

第二步：查询高于JONES工资的员工

SELECT * FROM emp WHERE sal > (第一步结果);

结果：

SELECT * FROM emp WHERE sa
    l > (SELECT sal FROM emp WHERE ename='JONES');

**2. 查询与SCOTT同一个部门的员工。**

l 子查询作为条件

l 子查询形式为单行单列

分析：

查询条件：部门=SCOTT的部门编号，其中SCOTT 的部门编号需要一条子查询。

第一步：查询SCOTT的部门编号

SELECT deptno FROM emp WHERE ename='SCOTT';

第二步：查询部门编号等于SCOTT的部门编号的员工

SELECT * FROM emp WHERE deptno = (SELECT deptno FROM emp WHERE ename='SCOTT');

**3. 工资高于30号部门所有人的员工信息**

分析：

SELECT * FROMemp WHERE sal>(SELECT MAX(sal)FROM emp WHERE deptno=30);

查询条件：工资高于30部门所有人工资，其中30部门所有人工资是子查询。高于所有需要使用all关键字。

第一步：查询30部门所有人工资

SELECT sal FROM emp WHERE deptno=30;

第二步：查询高于30部门所有人工资的员工信息

SELECT * FROM emp WHERE sal > ALL (第一步)

结果：

SELECT * FROM emp WHERE sal > ALL (SELECT sal FROM emp WHERE deptno=30)

l 子查询作为条件

l 子查询形式为多行单列（当子查询结果集形式为多行单列时可以使用ALL或ANY关键字）

form后面子查询

SELECT ename,job,hiredate FROM (SELECT ename,job,hiredate FROM emp WHERE hiredate>'1987-1-1') AS temp;

###### 2.2.4 导出导入数据库 ######

1 使用命令方式

导出数据库表

mysqldump -uroot -p 数据库名 > school.sql

导入数据库表

mysql -u root -p
    mysql>use 数据库
    然后使用source命令，后面参数为脚本文件(如这里用到的.sql)
    mysql>source d:/dbname.sql

2 使用SQLyog工具完成导出导入(演示操作)

1 先备份,右击数据库选择备份---》备份数据库，转储到sql
    2 导入，右击选择 "执行SQL脚本"

###### 2.2.5 创建用户和授权 ######

要使用命令行登录。

创建用户

CREATE USER `zhangsan` IDENTIFIED BY '123';
    CREATE USER `zhangsan`@`localhost` IDENTIFIED BY '123';

授权

GRANT ALL ON school.* TO `zhangsan`;

撤销权限

REVOKE ALL ON school.* FROM `zhangsan`;

删除用户

DROP USER `zhangsan`;

#### 第三节 综合练习 ####

某网上商城数据库如下图所示

表结构如下：

# 创建用户表
    create table user(
    	 userId int primary key auto_increment,
      	 username varchar(20) not null,
      	 password varchar(18) not null,
         address varchar(100),
         phone varchar(11)
    );
    
    #一对多的实现
    #创建分类表
    create table category(
      cid varchar(32) PRIMARY KEY ,
      cname varchar(100)		#分类名称
    );
    
    # 商品表
    CREATE TABLE `products` (
      `pid` varchar(32) PRIMARY KEY,
      `name` VARCHAR(40) ,
      `price` DOUBLE(7,2),
       category_id varchar(32),
       foreign key(category_id) references category(cid)
    );
    
    #多对多的实现
    #订单表
    create table `orders`(
      `oid` varchar(32) PRIMARY KEY ,
      `totalprice` double(12,2), #总计
      `userId` int,
       foreign key(userId) references user(userId) #外键
    );
    
    # 订单项表
    create table orderitem(
      oid varchar(32),	#订单id
      pid varchar(32),	#商品id
      num int ,         #购买商品数量
      primary key(oid,pid), #主键
      foreign key(oid) references orders(oid),
      foreign key(pid) references products(pid)
    );
    
    
    #初始化数据
    
    #用户表添加数据
    INSERT INTO USER(username,PASSWORD,address,phone) VALUES('张三','123','北京昌平沙河','13812345678');
    INSERT INTO USER(username,PASSWORD,address,phone) VALUES('王五','5678','北京海淀','13812345141');
    INSERT INTO USER(username,PASSWORD,address,phone) VALUES('赵六','123','北京朝阳','13812340987');
    INSERT INTO USER(username,PASSWORD,address,phone) VALUES('田七','123','北京大兴','13812345687');
    
    #给商品表初始化数据
    insert into products(pid,name,price,category_id) values('p001','联想',5000,'c001');
    insert into products(pid,name,price,category_id) values('p002','海尔',3000,'c001');
    insert into products(pid,name,price,category_id) values('p003','雷神',5000,'c001');
    insert into products(pid,name,price,category_id) values('p004','JACK JONES',800,'c002');
    insert into products(pid,name,price,category_id) values('p005','真维斯',200,'c002');
    insert into products(pid,name,price,category_id) values('p006','花花公子',440,'c002');
    insert into products(pid,name,price,category_id) values('p007','劲霸',2000,'c002');
    insert into products(pid,name,price,category_id) values('p008','香奈儿',800,'c003');
    insert into products(pid,name,price,category_id) values('p009','相宜本草',200,'c003');
    insert into products(pid,name,price,category_id) values('p010','梅明子',200,null);
    
    
    #给分类表初始化数据
    insert into category values('c001','电器');
    insert into category values('c002','服饰');
    insert into category values('c003','化妆品');
    insert into category values('c004','书籍');
    
    #添加订单
    insert into orders values('o6100',18000.50,1);
    insert into orders values('o6101',7200.35,1);
    insert into orders values('o6102',600.00,2);
    insert into orders values('o6103',1300.26,4);
    
    #订单详情表
    insert into orderitem values('o6100','p001',1),('o6100','p002',1),('o6101','p003',1)

##### 3.1 综合练习1-【多表查询】 #####

1>查询所有用户的订单

SELECT o.oid,o.totalprice, u.userId,u.username,u.phone 
    FROM orders o INNER JOIN USER u ON o.userId=u.userId;

2>查询用户id为 1 的所有订单详情

SELECT o.oid,o.totalprice, u.userId,u.username,u.phone ,oi.pid
    FROM  orders o INNER JOIN USER u ON o.userId=u.userId
    INNER JOIN orderitem oi ON o.oid=oi.oid
    where u.userid=1;

##### 3.2 综合练习2-【子查询】 #####

1>查看用户为张三的订单

SELECT * FROM orders WHERE userId=(SELECT userid FROM USER WHERE username='张三');

2>查询出订单的价格大于800的所有用户信息。

SELECT * FROM USER WHERE userId IN (SELECT DISTINCT userId FROM orders WHERE totalprice>800)

##### 3.3 综合练习3-【分页查询】 #####

1>查询所有订单信息，每页显示5条数据

#查询第一页
    SELECT * FROM orders LIMIT 0,5

#### 总结 ####

1 数据完整性 :数据的正确性。

实体完整性:

主键约束：不能重复，不能为为null

唯一约束: 不能重复 ，可以为null

自动增长: auto\_increment, 默认从1 开始每次增长1

域完整性:

数据类型：

int

double

double(5,2)

decimal(5,2);

char

varchar

text

blob

date

datetime

timestamp

非空约束 not null

默认约束 default

引用完整性

外键约束

2 多表查询

2.1 表关系

一对多

一对一

多对多

2.2 查询

合并结果集

union all

连接查询

内连接

外连接

子查询

2.3 导出导入

2.4 创建用户

create user zhangsan identified by ‘123’;

授权

grant all on school.\* to zhangsan

撤销权限

revoke all on scholl.\* from zhangsan

3 综合练习

#### 作业题 ####

数据库结构
    创建四张表 分别存储 学生信息 课程信息 分数 讲师信息表 存储相应数据
    学生信息表 Student
    字段名	  字段类型		字段约束 	含义
    Sno		varchar(3)	Not null  	学员编号
    Sname	varchar(4)	Not null  	学员姓名 
    Ssex	varchar(2)	Not null  	性别
    Sbirthday	Datetime			生日
    Classnum	varchar(5)			班级号
    
    CREATE TABLE student
    (
    	SNO varchar(3) NOT NULL,
    	SNAME varchar(4) NOT NULL,
    	SSEX varchar(2) NOT NULL,
    	SBIRTHDAY DATETIME,
    	CLASS varchar(5)
    );
    课程信息表 course
    字段名	字段类型	字段约束     含义
    Cno	    varchar(5)	 Not null  课程编号
    Cname	varchar(10)	 Not null  课程名称
    Tno	    varchar(10)  Not null  授课老师编号
    
    CREATE TABLE course
    (
    	CNO VARCHAR(5) NOT NULL,
    	CNAME VARCHAR(10) NOT NULL,
    	TNO VARCHAR(10) NOT NULL
    );
    成绩表score
    字段名	字段类型	字段约束     含义
    Sno	Varchar(3)	Not null 	学员编号
    Cno	Varchar(5)	Not null  	课程编号
    Degree	Double(3,1)	Not null  分数
    
    CREATE TABLE score
    (
    	SNO  varchar(3) NOT NULL,
    	CNO  varchar(5) NOT NULL,
    	DEGREE Double(3,1) NOT NULL
    );
    讲师表teacher
    字段名		字段类型	字段约束 	 含义
    Tno		varchar(3)	Not null  	  讲师编号
    Tname	varchar(4)	Not null  讲师姓名
    Tsex	varchar(2)	Not null  讲师性别
    Tbirthday Datetime Not null 出生日期
    Prof	varchar(6)	等级
    Depart	varchar(10)	所属院系
    
    CREATE TABLE teacher
    (
    	TNO   varchar(3) NOT NULL,
    	TNAME varchar(4) NOT NULL, 
    	TSEX  varchar(2) NOT NULL,
    	TBIRTHDAY DATETIME NOT NULL, 
    	PROF 	varchar(6),
    	DEPART  varchar(10) NOT NULL
    );
    
    向表中存储数据
    INSERT INTO student (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (108 ,'曾华' ,'男' ,1977-09-01,95033);
    INSERT INTO student (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (105 ,'匡明' ,'男' ,1975-10-02,95031);
    INSERT INTO student (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (107 ,'王丽' ,'女' ,1976-01-23,95033);
    INSERT INTO student (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (101 ,'李军' ,'男' ,1976-02-20,95033);
    INSERT INTO student (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (109 ,'王芳' ,'女' ,1975-02-10,95031);
    INSERT INTO student (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (103 ,'陆君' ,'男' ,1974-06-03,95031);
    
    
    INSERT INTO course(CNO,CNAME,TNO)VALUES ('3-105' ,'计算机导论',825)
    INSERT INTO course(CNO,CNAME,TNO)VALUES ('3-245' ,'操作系统' ,804);
    INSERT INTO course(CNO,CNAME,TNO)VALUES ('6-166' ,'数据电路' ,856);
    INSERT INTO course(CNO,CNAME,TNO)VALUES ('9-888' ,'高等数学' ,100);
    
    
    INSERT INTO score(SNO,CNO,DEGREE)VALUES (103,'3-245',86);
    INSERT INTO score(SNO,CNO,DEGREE)VALUES (105,'3-245',75);
    INSERT INTO score(SNO,CNO,DEGREE)VALUES (109,'3-245',68);
    INSERT INTO score(SNO,CNO,DEGREE)VALUES (103,'3-105',92);
    INSERT INTO score(SNO,CNO,DEGREE)VALUES (105,'3-105',88);
    INSERT INTO score(SNO,CNO,DEGREE)VALUES (109,'3-105',76);
    INSERT INTO score(SNO,CNO,DEGREE)VALUES (101,'3-105',64);
    INSERT INTO score(SNO,CNO,DEGREE)VALUES (107,'3-105',91);
    INSERT INTO score(SNO,CNO,DEGREE)VALUES (108,'3-105',78);
    INSERT INTO score(SNO,CNO,DEGREE)VALUES (101,'6-166',85);
    INSERT INTO score(SNO,CNO,DEGREE)VALUES (107,'6-106',79);
    INSERT INTO score(SNO,CNO,DEGREE)VALUES (108,'6-166',81);
    
    
    INSERT INTO teacher(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) 
    VALUES (804,'李诚','男','1958-12-02','副教授','计算机系');
    INSERT INTO teacher(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) 
    VALUES (856,'张旭','男','1969-03-12','讲师','电子工程系');
    INSERT INTO teacher(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)
    VALUES (825,'王萍','女','1972-05-05','助教','计算机系');
    INSERT INTO teacher(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) 
    VALUES (831,'刘冰','女','1977-08-14','助教','电子工程系');
    
    
    1、 查询Student表中的所有记录的Sname、Ssex和Class列。
    2、 查询教师所有的单位即不重复的Depart列。
    3、 查询Student表的所有记录。
    4、 查询Score表中成绩在60到80之间的所有记录。
    5、 查询Score表中成绩为85，86或88的记录。
    6、 查询Student表中“95031”班或性别为“女”的同学记录。
    7、 以Class降序查询Student表的所有记录。
    8、 以Cno升序、Degree降序查询Score表的所有记录。
    9、 查询“95031”班的学生人数。
    10、查询Score表中的最高分的学生学号和课程号。
    11、查询‘3-105’号课程的平均分。
    12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
    13、查询最低分大于70，最高分小于90的Sno列。
    14、查询所有学生的Sname、Cno和Degree列。
    15、查询所有学生的Sno、Cname和Degree列。

#### 面试题 ####

有三张表,学生表S,课程C,学生课程表SC,学生可以选修多门课程,一门课程可以被多个学生选修,通过SC 表关联。
    1）写出建表语句；
    2）写出SQL 语句,查询选修了所有选修课程的学生；
    3）写出SQL 语句,查询选修了至少5 门以上的课程的学生。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NTEzNDMw_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20191214174216746.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NTEzNDMw,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NTEzNDMw_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20191214174250891.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NTEzNDMw,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NTEzNDMw_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/2019121417433351.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NTEzNDMw,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NTEzNDMw_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20191214174501486.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NTEzNDMw,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NTEzNDMw_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20191214174440831.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NTEzNDMw,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NTEzNDMw_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20191214174522263.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NTEzNDMw,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NTEzNDMw_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/20191214174530111.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NTEzNDMw,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NTEzNDMw_size_16_color_FFFFFF_t_70 7]: https://img-blog.csdnimg.cn/20191214174547570.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NTEzNDMw,size_16,color_FFFFFF,t_70