2.2 MySQL高级用法

心已赠人 2023-06-25 02:08 1阅读 0赞

本文是课上资料的总结非原创没有转载地址

### 目录 ###

*  MySQL高级用法
 *   *  回顾
     *  一、数据完整性
     *   *  1.1 实体完整性
         *   *  1.1.1 主键约束（PRIMARY KEY）
             *  1.1.2 唯一约束（UNIQUE）
             *  1.1.3 自动增长列（AUTO\_INCREMENT）
         *  1.2 域完整性
         *   *  1.2.1 数据类型
             *  1.2.2 非空约束 NOT NULL
             *  1.2.3 默认值约束 DEFAULT
         *  1.3 引用完整性
     *  二、多表查询
     *   *  2.1 多表的关系
         *   *  2.1.1 一对多关系
             *  2.1.2 多对多关系
             *  2.1.3 一对一关系
         *  2.2 多表查询
         *   *  2.2.1 合并结果集 UNION、UNION ALL
             *  2.2.2 连接查询
             *   *  1. 内连接 INNER JOIN
                 *  2. 外连接
                 *   *  2.1 左外连接：以左表为主表，右表是从表
                     *  2.2 右外连接：以右表为主表，左表是从表
                 *  连接查询总结
             *  2.2.3 子查询
             *  2.2.4 导出导入数据
             *   *  1.使用命令方式
                 *  2 使用SQLyog工具完成导出导入(演示操作)
             *  2.2.5 创建用户和授权
     *  三、综合练习
     *   *  被操作表
         *  3.1 综合练习1-【多表查询】
         *  3.2 综合练习2-【子查询】
         *  3.3 综合练习3-【分页查询】
     *  四、函数
     *   *  4.1 字符串函数
         *  4.2 数学函数
         *  4.3 日期类函数
     *  五、事务 transaction
     *   *  5.1 什么是事务？
         *  5.2 事务四个特性【重点面试】
         *  5.3 MySQL中使用事务
 *  总结
 *  作业题
 *  面试题

# 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

## 一、数据完整性 ##

*  数据完整性是指数据的精确性（Accuracy）和可靠性（Reliability）。
 *  确保数据的完整性 = 在创建表时给表中添加约束，保证用户输入的数据保存到数据库中是准确可靠的。
 *  **完整性的分类**：

1.  实体完整性
2.  域完整性
3.  引用完整性

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

*  实体：即表中的一行(一条记录)代表一个实体（Entity）
 *  实体完整性的作用：标识每一行数据不重复。
 *  **约束类型**：

1.  **主键约束（PRIMARY KEY）**
2.  **唯一约束（UNIQUE）**
3.  **自动增长列（AUTO\_INCREMENT）**

#### 1.1.1 主键约束（PRIMARY KEY） ####

*  *注：每个表中要有一个主键*
 *  **特点**：数据唯一切不能为null
 *  示例：

1.  第一种添加方式：

CREATE TABLE student(
    	id INT PRIMARY KEY,
    	name VARCHAR(50)
    );

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

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

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

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=100;
    INSERT INTO student(name) values(‘tom’);

### 1.2 域完整性 ###

*  **域完整性约束的作用**：限制此单元格的数据正确，不对其它单元格起作用，域代表当前单元格
 *  **域完整性约束**：数据类型、非空约束（not null）、默认值约束(default)

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

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>(-32768，32767)</td> 
   <td>(0，65535)</td> 
   <td>大整数值</td> 
  </tr> 
  <tr> 
   <td>MEDIUMINT</td> 
   <td>3字节</td> 
   <td>(-8388608，8388607)</td> 
   <td>(0，16777215)</td> 
   <td>大整数值</td> 
  </tr> 
  <tr> 
   <td>INT或INTEGER</td> 
   <td>4字节</td> 
   <td>(-2147483648，2147483647)</td> 
   <td>(0，4294967295)</td> 
   <td>大整数值</td> 
  </tr> 
  <tr> 
   <td>BIGINT</td> 
   <td>8字节</td> 
   <td>(-9233372036854775808，9223372036854775807)</td> 
   <td>(0，18446744073709551615)</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>DOUBLE(M,D)</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>DECIMAL(M,D)</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>

1.  **日期类型**:

*  表示时间值的日期和时间类型为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>DATE</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>DATETIME</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-0100: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>

1.  **字符串类型**:

*  字符串类型指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 primary 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`
 *  示例：

1.  第一种添加外键方式。（推荐）  
    学生表（主表）

# 学生表（主表）
    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)
    )
    -- 外键列的数据类型一定要与主键的类型一致

> 注意：fk\_score\_sid是外键名；  
> 外键名：为定义的外键约束的名称，一个表中不能有相同名称的外键；字段名表示子表需要添加外健约束的字段列；主表名即被子表外键所依赖的表的名称；主键列表示主表中定义的主键列或者列组合。

1.  第二种添加外键方式。

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

> 注意：其中fk\_stu\_score是索引名

## 二、多表查询 ##

*  多个表之间是有关系的，那么关系靠谁来维护?
 *  **多表约束：外键约束。**

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

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

*  客户和订单，分类和商品，部门和员工。
 *  **一对多建表原则**：在多的一方创建一个字段，字段作为外键指向一方的主键。

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

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

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

*  一个丈夫对应一个妻子
 *  一个员工对应一个简历
 *  在实际的开发中应用不多.因为一对一可以创建成一张表。
 *  **两种建表原则**：

1.  **唯一外键对应**：在多的一方创建一个外键指向一的一方的主键，将外键设置为unique和非空。
2.  **主键对应**：让一对一的双方的主键进行建立关系。

### 2.2 多表查询 ###

多表查询有如下几种：

1.  **合并结果集**：UNION、UNION ALL
2.  **连接查询**：
    
     *  **内连接** \[INNER\] JOIN ON
     *  **外连接** OUTER JOIN ON
        
         *  **左外连接** LEFT \[OUTER\] JOIN
         *  **右外连接** RIGHT \[OUTER\] JOIN
3.  **子查询**

#### 2.2.1 合并结果集 UNION、UNION ALL ####

*  **作用**：合并结果集就是把两个SELECT语句的查询结果合并到一起！
 *  合并结果集有两种方式：

<table> 
 <thead> 
  <tr> 
   <th>关键字</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>UNION</td> 
   <td>去除重复记录<br>例如：<code>SELECT * FROM table1 UNION SELECT * FROM table2;</code></td> 
  </tr> 
  <tr> 
   <td>UNION ALL</td> 
   <td>不去除重复记录<br>例如：<code>SELECT * FROM table1 UNION ALL SELECT * FROM table2;</code></td> 
  </tr> 
 </tbody> 
</table>

*  UNION示例：

SELECT * FROM t1 UNION SELECT * FROM t2;

![UNION][]

*  UNION ALL示例

SELECT * FROM t1 UNION ALL SELECT * FROM t2;

![UNION ALL][]

*  注意：别合并的两个结果：列数必须相同，列类型可以不同。

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

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

SELECT * FROM t1,t2;

![连接查询][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNjQ2Njcy_size_16_color_FFFFFF_t_70_pic_center]

*  连接查询会产生**笛卡尔积**1，假设集合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语句省略

*  练习：

select * from emp,dept;

![select \* from emp,dept;][select _ from emp_dept]  
**使用主外键关系做为条件来去除无用信息**

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

![SELECT \* FROM emp,dept WHERE emp.deptno=dept.deptno;][SELECT _ FROM emp_dept WHERE emp.deptno_dept.deptno]  
上面查询结果会把两张表的所有列都查询出来，也许你不需要那么多列，这时就可以指定要查询的列了。

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

![WHERE emp.deptno=dept.deptno;][WHERE emp.deptno_dept.deptno]

##### 1. 内连接 INNER JOIN #####

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

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

> 注意：\# 注意：on后面 主外键关系

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

##### 2. 外连接 #####

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

###### 2.1 左外连接：以左表为主表，右表是从表 ######

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_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNjQ2Njcy_size_16_color_FFFFFF_t_70_pic_center 1]

###### 2.2 右外连接：以右表为主表，左表是从表 ######

*  右连接就是先把右表中所有记录都查询出来，然后左表满足条件的显示，不满足显示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_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNjQ2Njcy_size_16_color_FFFFFF_t_70_pic_center 2]

##### 连接查询总结 #####

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

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

*  一个select语句中包含另一个完整的select语句。
 *  子查询就是嵌套查询，即SELECT中包含SELECT，如果一条语句中存在两个，或两个以上SELECT，那么就是子查询语句了。里面的查询叫做子查询，外层的查询叫父查询，一般情况都是先执行子查询，再执行父查询。
 *  **子查询出现的位置**：
    
    1.  WHERE后，作为被查询的条件；
    2.  FROM后，作临时表。
 *  **当子查询出现在WHERE后作为条件时，还可以使用如下关键字**：
    
    1.  ANY
    2.  ALL
 *  **子查询结果集的常见形式**：
    
    1.  单行单列（用于条件）
    2.  多行单列（用于条件）
    3.  多行多列（用于表）
 *  示例：

1.  工资高于JONES的员工。  
    分析：查询条件：工资>JONES工资，其中JONES工资需要一条子查询。

# 第一步：查询JONES的工资
    SELECT sal FROM emp WHERE ename='JONES';

# 第二步：查询高于JONES工资的员工
    SELECT * FROM emp WHERE sal > (第一步结果);

# 结果：
    SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename='JONES');

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

*  子查询作为条件
 *  子查询形式为单行单列
 *  分析：查询条件：部门=SCOTT的部门编号，其中SCOTT 的部门编号需要一条子查询。

# 第一步：查询SCOTT的部门编号
    SELECT deptno FROM emp WHERE ename='SCOTT';

# 第二步：查询部门编号等于SCOTT的部门编号的员工
    SELECT * FROM emp WHERE deptno = (SELECT deptno FROM emp WHERE ename='SCOTT');

1.  工资高于30号部门所有人的员工信息  
    分析：查询条件：工资高于30部门所有人工资，其中30部门所有人工资是子查询。高于所有需要使用all关键字。

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

# 第一步：查询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)

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

1.  from后面子查询

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 创建用户和授权 ####

*  要使用命令行登录。

1.  创建用户

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

1.  授权

GRANT ALL ON school.* TO `zhangsan`;

1.  撤销权限

REVOKE ALL ON school.* FROM `zhangsan`;

1.  删除用户

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) not null		#分类名称
    );
    
    # 商品表
    CREATE TABLE `products` (
      `pid` varchar(32) PRIMARY KEY,
      `name` VARCHAR(40) ,
      `price` DOUBLE(7,2),
       category_id varchar(32),
       constraint foreign key(category_id) references category(cid)
    );
    
    #多对多的实现
    #订单表
    create table `orders`(
      `oid` varchar(32) PRIMARY KEY ,
      `totalprice` double(12,2), #总计
      `userId` int,
       constraint 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 category values('c001','电器');
    insert into category values('c002','服饰');
    insert into category values('c003','化妆品');
    insert into category values('c004','书籍');
    
    #给商品表初始化数据
    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 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;

1.  查询用户id为1的所有订单详情。

# 查询用户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='张三');

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

# 查询出订单的价格大于800的所有用户信息。
    SELECT * FROM user WHERE userId IN (
    	SELECT DISTINCT userId FROM orders WHERE totalprice>800
    );

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

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

# 查询所有订单信息，每页显示5条数据。
    SELECT * FROM orders LIMIT 0,5;

## 四、函数 ##

*  MySQL中的函数类似于Java中的带返回值的方法，主要包括字符串函数、数学函数、日期类函数

### 4.1 字符串函数 ###

<table> 
 <thead> 
  <tr> 
   <th>函数声明</th> 
   <th>功能描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>LENGTH (string )</td> 
   <td>string长度，字节的长度</td> 
  </tr> 
  <tr> 
   <td>CONCAT (string2 [,… ])</td> 
   <td>连接字串</td> 
  </tr> 
  <tr> 
   <td>INSTR (string ,substring )</td> 
   <td>返回substring首次在string中出现的位置,不存在返回0</td> 
  </tr> 
  <tr> 
   <td>LCASE (string2 )</td> 
   <td>转换成小写</td> 
  </tr> 
  <tr> 
   <td>UCASE (string2 )</td> 
   <td>转换成大写</td> 
  </tr> 
  <tr> 
   <td>LPAD (string ,length ,pad )</td> 
   <td>重复用pad加在string开头,直到字串长度为length</td> 
  </tr> 
  <tr> 
   <td>RPAD (string ,length ,pad)</td> 
   <td>在str后用pad补充,直到长度为length</td> 
  </tr> 
  <tr> 
   <td>LTRIM (string )</td> 
   <td>去除前端空格</td> 
  </tr> 
  <tr> 
   <td>RTRIM (string )</td> 
   <td>去除后端空格</td> 
  </tr> 
  <tr> 
   <td>STRCMP (string1 ,string2)</td> 
   <td>逐字符比较两字串大小,</td> 
  </tr> 
  <tr> 
   <td>SUBSTRING (str , position [,length ])</td> 
   <td>从str的position开始,取length个字符</td> 
  </tr> 
 </tbody> 
</table>

### 4.2 数学函数 ###

<table> 
 <thead> 
  <tr> 
   <th>函数声明</th> 
   <th>功能描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>ABS (number2 )</td> 
   <td>绝对值</td> 
  </tr> 
  <tr> 
   <td>BIN (decimal_number)</td> 
   <td>十进制转二进制</td> 
  </tr> 
  <tr> 
   <td>CEILING (number2 )</td> 
   <td>向上取整</td> 
  </tr> 
  <tr> 
   <td>FLOOR (number2 )</td> 
   <td>向下取整</td> 
  </tr> 
  <tr> 
   <td>FORMAT (number,decimal_places )</td> 
   <td>保留小数位数</td> 
  </tr> 
  <tr> 
   <td>HEX (DecimalNumber)</td> 
   <td>转十六进制</td> 
  </tr> 
  <tr> 
   <td>POWER (number ,power )</td> 
   <td>求指数</td> 
  </tr> 
  <tr> 
   <td>RAND([seed])</td> 
   <td>随机数</td> 
  </tr> 
  <tr> 
   <td>ROUND (number [,decimals ])</td> 
   <td>四舍五入,decimals为小数位数] 注：返回类型并非均为整数</td> 
  </tr> 
 </tbody> 
</table>

### 4.3 日期类函数 ###

<table> 
 <thead> 
  <tr> 
   <th>函数声明</th> 
   <th>功能描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><code>NOW()</code></td> 
   <td>当前时间</td> 
  </tr> 
  <tr> 
   <td>CURRENT_DATE ( )</td> 
   <td>当前日期</td> 
  </tr> 
  <tr> 
   <td>CURRENT_TIME ( )</td> 
   <td>当前时间</td> 
  </tr> 
  <tr> 
   <td>CURRENT_TIMESTAMP ( )</td> 
   <td>当前时间戳和now()语义相同</td> 
  </tr> 
  <tr> 
   <td>ADDDATE(‘2019-04-15’,2)</td> 
   <td>加2天</td> 
  </tr> 
  <tr> 
   <td><code>DATEDIFF (date1 ,date2 )</code></td> 
   <td>两个日期差天数</td> 
  </tr> 
  <tr> 
   <td><code>TIMEDIFF (datetime1 ,datetime2 )</code></td> 
   <td>两个时间差</td> 
  </tr> 
  <tr> 
   <td>TIME_TO_SEC(time)</td> 
   <td>时间转秒数</td> 
  </tr> 
  <tr> 
   <td>YEAR (datetime )</td> 
   <td>年份</td> 
  </tr> 
  <tr> 
   <td>DAY (date )</td> 
   <td>返回日期的天</td> 
  </tr> 
  <tr> 
   <td>HOUR(datetime)</td> 
   <td>小时</td> 
  </tr> 
  <tr> 
   <td>DAYOFWEEK (date )</td> 
   <td>星期(1-7) ,1为星期天</td> 
  </tr> 
 </tbody> 
</table>

## 五、事务 transaction ##

### 5.1 什么是事务？ ###

*  事务是由完成任务的一个或多个操作组成，这些操作作为一个整体不能分割，要么全部执行成功，要么全部失败。
 *  比如：银行系统转账：涉及到两个操作，一个账号扣钱，一个账户存钱，这两步不可分割，组成一个事务。
 *  人员管理系统：要删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务。

### 5.2 事务四个特性【重点面试】 ###

*  事务是必须满足4个条件（ACID）原子性（**A**tomicity，或称不可分割性）、一致性（**C**onsistency）、隔离性（**I**solation，又称独立性）、持久性（**D**urability）。

1.  **原子性**：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
2.  **一致性**：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
3.  **隔离性**：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
4.  **持久性**：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

### 5.3 MySQL中使用事务 ###

*  MySQL中使用事务控制语句完成事务操作
 *  默认情况下单独insert、update、delete等语句就是一个完成的事务操作。
 *  常用事务控制语句:

1.  **BEGIN** 或 **START TRANSACTION** 开始一个事务
2.  **ROLLBACK** 事务回滚
3.  **COMMIT** 事务提交

*  事务操作演示

#创建表 account
    create table account(id int,name varchar(20),money double(10,2));
    #事务操作
    begin;
    insert into account(id,name,money) values(1,'李晨',20000);
    insert into account(id,name,money) values(1,'李晨',20000);
    #提交事务
    commit;
    #查询数据
    select * from account;
    begin;
    insert into account(id,name,money) values(1,'大郎',50000);
    insert into account(id,name,money) values(1,'金莲',50000);
    #提交事务
    rollback;
    #再次查询数据
    select * from account;

# 总结 #

# 作业题 #

数据库结构
    创建四张表 分别存储 学生信息 课程信息 分数 讲师信息表 存储相应数据
    学生信息表 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 门以上的课程的学生。

--------------------

1.  注脚的解释 ↩︎

[UNION]: https://img-blog.csdnimg.cn/20191224115943794.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNjQ2Njcy,size_16,color_FFFFFF,t_70#pic_center
[UNION ALL]: https://img-blog.csdnimg.cn/20191224120037254.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNjQ2Njcy,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNjQ2Njcy_size_16_color_FFFFFF_t_70_pic_center]: https://img-blog.csdnimg.cn/20191224135349444.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNjQ2Njcy,size_16,color_FFFFFF,t_70#pic_center
[select _ from emp_dept]: https://img-blog.csdnimg.cn/20191224142721325.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNjQ2Njcy,size_16,color_FFFFFF,t_70#pic_center
[SELECT _ FROM emp_dept WHERE emp.deptno_dept.deptno]: https://img-blog.csdnimg.cn/20191224142837937.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNjQ2Njcy,size_16,color_FFFFFF,t_70#pic_center
[WHERE emp.deptno_dept.deptno]: https://img-blog.csdnimg.cn/20191224142902614.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNjQ2Njcy,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNjQ2Njcy_size_16_color_FFFFFF_t_70_pic_center 1]: https://img-blog.csdnimg.cn/2019122414324913.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNjQ2Njcy,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNjQ2Njcy_size_16_color_FFFFFF_t_70_pic_center 2]: https://img-blog.csdnimg.cn/20191224143729728.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNjQ2Njcy,size_16,color_FFFFFF,t_70#pic_center