1.1 今日目标
- 掌握数据库的作用；
- 能够通俗的解释什么是关系型数据库；
- 能够至少说出三种关系型数据库；
- 掌握MySQL客户端登录和登出MySQL服务器；
- 理解数据库具体数据的存储逻辑；
- 掌握创建、查看和删除数据库；
- 了解MySQL数据库创建与删除指令对应的文件效果
- 掌握数据表的增删改查操作；
- 掌握数据的增删改查操作；
1.2 数据库介绍
1.2.1 作用
数据库是用来存放数据的仓库
数据库中存放的是表，表中存放的是数据。
1.2.2 数据库的发展史
萌芽阶段：文件系统
最初始的数据库是用磁盘来存储数据的。文件就是最早的数据库。
第一代数据库：层次模型
优点：这是导航结构
优点：结构清晰，分类查询方便
缺点：有可能造成数据无效
第一代数据库：网状模型
网状模型解决了层次模型的数据不一致的问题，但没有解决导航问题。导航结构在查询中有时候效率低下，比如查询整个公司的四月的营业额。
第二阶段：关系模型
特点：
1、每个表都是独立的
2、通过关系字段将两个表连接起来
3、关系：两个表的公共字段
4、关系型数据库中多表联合查询效率低下。
多学一招：为了解决关系型数据库多表查询效率的问题，项目中使用了NoSQL(非关系型数据库，Redis、mongodb等等)，在数据库中按照键值对来存储，它是关系型数据库的补充。
1.2.3 SQL
Structured Query Language（结构化查询语言），是用来操作关系型数据库的一门语言。这是一个关系型数据库的通用操作语言，也成为标准SQL，也叫SQL-92。
脚下留心：数据库的生产厂商为了占有市场份额，都会在标准SQL的基础上扩展一些自己的东西以吸引用户。
1.2.4 常用的关系型数据库
| 关系型数据库 | 开发公司 | 使用语言 |
|---|---|---|
| access | 微软公司 | SQL |
| SQL Server | 微软公司 | T-SQL |
| Oracle | 甲骨文公司 | PL/SQL |
| MySQL | 被甲骨文公司收购 | MySQL |
思考：已知标准SQL可以在所有的关系型数据库上运行，在Oracle上编写的PL/SQL能否在MySQL上运行？
答：不可以，只能运行标准SQL
1.3 连接服务器
数据库是CS模式的软件，所以要连接数据库必须要有客户端软件。
MySQL数据库默认端口号是3306
1.3.1 window界面连接服务器
1、Navicat
2、MySQL-Front
1.3.2 通过web窗体连接
主要有浏览器就可以访问数据库
1.3.3 命令行连接
host -h 主机
port -P 端口号 （大写）
user -u 用户名
password -p 密码 （小写）
例题
-- 连接数据库
F:\wamp\PHPTutorial\MySQL\bin>mysql -h127.0.0.1 -P3306 -uroot -proot -- 明文
-- 如果连接本地数据库 -h可以省略 如果服务器端口是3306，-P端口号也可以省略
F:\wamp\PHPTutorial\MySQL\bin>mysql -uroot -proot -- 明文
-- 密文
F:\wamp\PHPTutorial\MySQL\bin>mysql -uroot -p
Enter password: ****
1.3.4 退出登录
mysql> exit -- 方法一
mysql> quit -- 方法二
mysql> \q -- 方法三
1.4 数据库基本概念
1.4.1 数据库、表相关
-
数据库：数据库中存放的是表，一个数据库中可以存放多个表
-
表：表是用来存放数据的。
-
关系：两个表的公共字段
-
行：也称记录，也称实体
-
列：也称字段，也称属性
脚下留心：就表结构而言，表分为行和列；就表数据而言，表分为记录和字段；就面向对象而言，一个记录就是一个实体，一个字段就是一个属性。
1.4.2 数据相关
1、数据冗余：相同的数据存储在不同的地方
冗余只能减少，不能杜绝。
减少冗余的方法是分表
2、数据完整性：正确性+准确性=数据完整性
正确性：数据类型正确
准确性：数据范围要准确
思考：学生的年龄是整型，输入1000岁，正确性和准确性如何？
答：正确的，但不准确。失去了数据完整性。
1.4.3 数据库执行过程
1.5 数据库的操作
1.5.1 创建数据库
语法：
create database [if not exists] 数据名 [选项]
例题
-- 创建数据库
mysql> create database stu;
Query OK, 1 row affected (0.06 sec)
-- 创建数据库时，如果数据库已经存在就要报错
mysql> create database stu;
# ERROR 1007 (HY000): Can't create database 'stu'; database exists
-- 在创建数据库时候，判断数据库是否存在，不存在就创建
mysql> create database if not exists stu;
Query OK, 1 row affected, 1 warning (0.00 sec)
-- 特殊字符、关键字做数据库名，使用反引号将数据库名括起来
mysql> create database `create`;
Query OK, 1 row affected (0.04 sec)
mysql> create database `%$`;
Query OK, 1 row affected (0.05 sec)
-- 创建数据库时指定存储的字符编码
mysql> create database emp charset=gbk;
Query OK, 1 row affected (0.00 sec)
# 如果不指定编码，数据库默认使用安装数据库时指定的编码
MySQL数据库的目录
数据库保存的路径在安装MySQL的时候就配置好。
也可以在my.ini配置文件中更改数据库的保存地址。(datadir="F:/wamp/PHPTutorial/MySQL/data/")
一个数据库就对应一个文件夹，在文件夹中有一个db.opt文件。在此文件中设置数据库的字符集和校对集
****
小结：
1、如果创建的数据库已存在，就会报错。
解决方法：创建数据库的时候判断一下数据库是否存在，如果不存在再创建
2、如果数据库名是关键字和特殊字符要报错。
解决：在特殊字符、关键字行加上反引号
3、创建数据库的时候可以指定字符编码
脚下留心：创建数据库如果不指定字符编码，默认和MySQL服务器的字符编码是一致的。
1.5.2 显示所有数据库
语法：
show databases
例题
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
注意：数据库安装后，只带上面四个数据库
1.5.3 删除数据库
语法：
drop database [if exists] 数据库名
例题
mysql> drop database `create`;
Query OK, 0 rows affected (0.00 sec)
mysql> drop database `%$`;
Query OK, 0 rows affected (0.00 sec)
-- 判断数据库是否存在，如果存在就删除
mysql> drop database if exists stu;
Query OK, 0 rows affected (0.00 sec)
小结：
1、如果删除的数据库不存在，会报错
解决：删除之前判断一下，如果存在就删除
1.5.4 显示创建数据库的语句
语法：
show create database 数据库名
例题：
mysql> show create database emp;
+----------+-------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------+
| emp | CREATE DATABASE `emp` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+-------------------------------------------------------------+
1 row in set (0.00 sec)
1.5.5 修改数据库
只能修改数据库选项，数据库的选项只有字符编码
语法：
alter database 数据库名 charset=字符编码
例题：
mysql> alter database emp charset=utf8;
Query OK, 1 row affected (0.00 sec)
小结：
1、修改数据库只能修改数据库的字符编码
2、在MySQL中utf字符编码之间没有横杆 utf8
1.5.6 选择数据库
语法：
use 数据库名
例题
mysql> use emp;
Database changed
1.6 表的操作
mysql> create database data;
Query OK, 1 row affected (0.00 sec)
mysql> use data;
Database changed
1.6.1 创建表
语法：
create table [if not exists] `表名`(
`字段名` 数据类型 [null|not null] [default] [auto_increment] [primary key] [comment],
`字段名 数据类型 …
)[engine=存储引擎] [charset=字符编码]
null|not null 是否为空
default: 默认值
auto_increment 自动增长,默认从1开始，每次递增1
primary key 主键，主键的值不能重复，不能为空,每个表必须只能有一个主键
comment: 备注
engine 引擎决定了数据的存储和查找 myisam、innodb
脚下留心：表名和字段名如果用了关键字，要用反引号引起来。
例题：
-- 设置客户端和服务器通讯的编码
mysql> set names gbk;
Query OK, 0 rows affected (0.00 sec)
-- 创建简单的表
mysql> create table stu1(
-> id int auto_increment primary key,
-> name varchar(20) not null
-> )engine=innodb charset=gbk;
Query OK, 0 rows affected (0.11 sec)
-- 创建复杂的表
mysql> create table stu2(
-> id int auto_increment primary key comment '主键',
-> name varchar(20) not null comment '姓名',
-> `add` varchar(50) not null default '地址不详' comment '地址',
-> score int comment '成绩,可以为空'
-> )engine=myisam;
Query OK, 0 rows affected (0.06 sec)
小结：
1、如果不指定引擎，默认是innodb
2、如果不指定字符编码，默认和数据库编码一致
3、varchar(20) 表示长度是20个字符
数据表的文件
一个数据库对应一个文件夹
一个表对应一个或多个文件
引擎是myisam，一个表对应三个文件
.frm ：存储的是表结构
.myd :存储的是表数据
.myi :存储的表数据的索引
引擎是innodb,一个表对应一个表结构文件，innodb的都有表的数据都保存在ibdata1文件中，如果数据量很大，会自动的创建ibdata2，ibdata3...
innodb和myisam的区别
| 引擎 | |
|---|---|
| myisam | 1、查询速度快 2、容易产生碎片 3、不能约束数据 |
| innodb | 1、以前没有myisam查询速度快，现在已经提速了 2、不产生碎片 3、可以约束数据 |
脚下留心：推荐使用innodb。
1.6.2 显示所有表
语法
show tables;
例题：
mysql> show tables;
Empty set (0.00 sec)
1.6.3 显示创建表的语句
语法
show create table; -- 结果横着排列
show create table \G -- 将结果竖着排列
例题
1.6.4 查看表结构
语法
desc[ribe] 表名
例题
-- 方法一
mysql> describe stu2;
+-------+-------------+------+-----+----------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+----------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| add | varchar(50) | NO | | 地址不详 | |
| score | int(11) | YES | | NULL | |
+-------+-------------+------+-----+----------+----------------+
4 rows in set (0.05 sec)
-- 方法二
mysql> desc stu2;
+-------+-------------+------+-----+----------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+----------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| add | varchar(50) | NO | | 地址不详 | |
| score | int(11) | YES | | NULL | |
+-------+-------------+------+-----+----------+----------------+
4 rows in set (0.00 sec)
1.6.5 复制表
语法一：create table 新表 select 字段 from 旧表
特点：不能复制父表的键，能够复制父表的数据
语法二：create table 新表 like 旧表
特点：只能复制表结构，不能复制表数据
小结：
*表示所有字段
1.6.6 删除表
语法：
drop table [if exists] 表1，表2,…
例题：
-- 删除表
mysql> drop table stu4;
Query OK, 0 rows affected (0.06 sec)
-- 如果表存在就删除
mysql> drop table if exists stu4;
Query OK, 0 rows affected, 1 warning (0.00 sec)
-- 一次删除多个表
mysql> drop table stu2,stu3;
Query OK, 0 rows affected (0.03 sec)
1.6.7 修改表
语法：alter table 表名
创建初始表
mysql> create table stu(
-> id int,
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.00 sec)
1、添加字段：alter table 表名add [column] 字段名 数据类型 [位置]
mysql> alter table stu add `add` varchar(20); -- 默认添加字段放在最后
Query OK, 0 rows affected (0.05 sec)
mysql> alter table stu add sex char(1) after name; -- 在name之后添加sex字段
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table stu add age int first; -- age放在最前面
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc stu;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| age | int(11) | YES | | NULL | |
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| add | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
2、删除字段：alter table 表 drop [column] 字段名
mysql> alter table stu drop age; -- 删除age字段
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
3、修改字段(改名)：alter table 表 change [column] 原字段名 新字段名 数据类型 …
-- 将name字段更改为stuname varchar(10)
mysql> alter table stu change name stuname varchar(10);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc stu;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| stuname | varchar(10) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| add | varchar(20) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
4、修改字段（不改名）:alter table 表 modify 字段名 字段属性…
-- 将sex数据类型更改为varchar(20)
mysql> alter table stu modify sex varchar(20);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
-- 将add字段更改为varchar(20) 默认值是‘地址不详’
mysql> alter table stu modify `add` varchar(20) default '地址不详';
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
5、修改引擎：alter table 表名 engine=引擎名
mysql> alter table stu engine=myisam;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
6、修改表名:alter table 表名 rename to 新表名
-- 将stu表名改成student
mysql> alter table stu rename to student;
Query OK, 0 rows affected (0.00 sec)
7、将表移动到其他数据库
-- 将当前数据库中的student表移动到php74数据库中改名为stu
mysql> alter table student rename to php74.stu;
Query OK, 0 rows affected (0.00 sec)
1.7 数据操作
1.7.1 插入数据
语法：insert into 表名 (字段名, 字段名,…) values (值1, 值1,…)
1、插入所有字段
-- 插入所有字段
mysql> insert into stu (id,stuname,sex,`add`) values (1,'tom','男','北京');
Query OK, 1 row affected (0.00 sec)
-- 插入部分字段
mysql> insert into stu(id,stuname) values (2,'berry');
-- 插入的字段和表的字段可以顺序不一致。但是插入字段名和插入的值一定要一一对应
mysql> insert into stu(sex,`add`,id,stuname) values ('女','上海',3,'ketty');
Query OK, 1 row affected (0.00 sec)
-- 插入字段名可以省略
mysql> insert into stu values(4,'rose','女','重庆');
Query OK, 1 row affected (0.00 sec)
小结：
1、插入字段名的顺序和数据表中字段名的顺序可以不一致
2、插入值的个数、顺序必须和插入字段名的个数、顺序要一致。
3、如果插入的值的顺序和个数与表字段的顺序个数一致，插入字段可以省略。
2、插入默认值和空值
mysql> insert into stu values (5,'jake',null,default);
Query OK, 1 row affected (0.05 sec)
小结：
default关键字用来插入默认值，null用来插入空值.
3、插入多条数据
mysql> insert into stu values (6,'李白','男','四川'),(7,'杜甫','男','湖北');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
1.7.2 更新数据
语法：update 表名 set 字段=值 [where 条件]
-- 将berry性别改为女
mysql> update stu set sex='女' where stuname='berry';
Query OK, 1 row affected (0.06 sec)
-- 将编号是1号的学生性别改成女，地址改为上海。
mysql> update stu set sex='女',`add`='上海' where id=1;
Query OK, 1 row affected (0.00 sec)
1.7.3 删除数据
语法：delete from 表名 [where 条件]
-- 删除1号学生
mysql> delete from stu where id=1;
-- 删除名字是berry的学生
mysql> delete from stu where stuname='berry';
Query OK, 1 row affected (0.00 sec)
-- 删除所有数据
mysql> delete from stu;
Query OK, 5 rows affected (0.00 sec)
多学一招：delete from 表和truncate table 表区别？
1、delete from 表：遍历表记录，一条一条的删除
2、truncate table：将原表销毁，再创建一个同结构的新表。就清空表而言，这种方法效率高。
1.7.4 查询数据
语法：
select 列名 from 表名
例题
-- 查询id字段的值
mysql> select id from stu;
-- 查询id,stuname字段的值
mysql> select id,stuname from stu;、
-- 查询所有字段的值
mysql> select * from stu;
1.7.5 数据传输时使用字符集
发现：在插入数据的时候，如果有中文会报错（或者中文无法插入）
分析：
1、查看客户端发送的编码
2、查看服务器接受，返回的编码
更改接受客户端指令的编码
mysql> set character_set_client=gbk;
Query OK, 0 rows affected (0.05 sec)
原因：返回编码是utf8，客户端是gbk;
测试：成功
可以通过set names一次性设置
小结：
1、设置什么编码取决于客户端的编码
2、通过set names 设置编码
1.8 补充知识
每次执行指令要进入相应的目录中，麻烦，可以通过环境变量简化操作。
1.8.1 环境变量配置
我的电脑右键——属性——高级
将mysql指令目录地址添加到环境变量的Path值中
这时候就可以在任意目录下使用mysql指令
原理：
1、输入指令后，首先在当前目录下查找，如果当前目录下找不到，就到环境变量的Path中查找
2、Path中有很多目录，从前往后查找
1.8.2 校对集
1、概念：在某种字符集下，字符之间的比较关系，比如a和B的大小关系，如果区分大小写a>B，如果不区分大小写则a<B。比如赵钱孙李大小关系，不同的标准关系不一样
2、校对集依赖与字符集，不同的字符集的的比较规则不一样，如果字符集更改，校对集也重新定义。
3、不同的校对集对同一字符序列比较的结果是不一致的。
4、 可以在定义字符集的同时定义校对集、 语法：
collate = 校对集
例题：
定义两个表，相同字符集不同校对集
mysql> create table stu1(
-> name char(1)
-> )charset=utf8 collate=utf8_general_ci;
Query OK, 0 rows affected (0.05 sec)
mysql> create table stu2(
-> name char(1)
-> )charset=utf8 collate=utf8_bin;
Query OK, 0 rows affected (0.05 sec)
mysql> insert into stu1 values ('a'),('B');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into stu2 values ('a'),('B');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
测试：两个表的数据都是有小到大排序
mysql> select * from stu1 order by name; -- 不区分大小写
+------+
| name |
+------+
| a |
| B |
+------+
2 rows in set (0.08 sec)
mysql> select * from stu2 order by name; -- 区分大小写
+------+
| name |
+------+
| B |
| a |
+------+
2 rows in set (0.00 sec)
小结校对集规则：
_bin：按二进制编码比较，区别大小写
_ci：不区分大小写
2.1 今日目标
- 掌握char和varchar的应用；
- 了解text类型是用来存储长文本数据；
- 了解字段属性的作用；
- 掌握主键primary key的应用以及效果；
- 掌握逻辑主键的自增长auto_increment应用；
- 掌握唯一键与主键的区别；
- 了解外键的约束作用；
- 掌握主键冲突的两种解决方案；
2.2 数据类型
MySQL中的数据类型是强类型
2.2.1 数值型
1、 整型
| 整形 | 占用字节数 | 范围 |
|---|---|---|
| tinyint | 1 | -128~127 |
| smallint | 2 | -32768~32767 |
| mediumint | 3 | -8388608~8388607 |
| int | 4 | -2147483648~2147483647 |
| bigint | 8 | -9223372036854775808~9223372036854775807 |
选择的范围尽可能小，范围越小占用资源越少
mysql> create table stu1(
-> id tinyint, # 范围要尽可能小，范围越小，占用空间越少
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.02 sec)
-- 超出范围会报错
mysql> insert into stu1 values (128,'tom');
ERROR 1264 (22003): Out of range value for column 'id' at row 1
无符号整形（unsigned） 无符号整形就是没有负数，无符号整数是整数的两倍
mysql> create table stu2(
-> id tinyint unsigned # 无符号整数
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> insert into stu2 values (128);
Query OK, 1 row affected (0.00 sec)
整形支持显示宽度，显示宽带是最小的显示位数，如int(11)表示整形最少用11位表示，如果不够位数用0填充。显示宽度默认不起作用，必须结合zerofill才起作用。
mysql> create table stu4(
-> id int(5),
-> num int(5) zerofill # 添加前导0,int(5)显示宽带是5
-> );
Query OK, 0 rows affected (0.05 sec)
mysql> insert into stu4 values (12,12);
Query OK, 1 row affected (0.00 sec)
mysql> select * from stu4;
+------+-------+
| id | num |
+------+-------+
| 12 | 00012 |
+------+-------+
1 row in set (0.00 sec)
小结：
1、范围要尽可能小，范围越小，占用空间越少
2、无符号整数是整数的两倍
3、整形支持显示宽度，显示宽带是最小的显示位数，必须结合zerofill才起作用
2、浮点型
| 浮点型 | 占用字节数 | 范围 |
|---|---|---|
| float（单精度型） | 4 | -3.4E+38~3.4E+38 |
| double（双精度型） | 8 | -1.8E+308~1.8E+308 |
浮点型的声明：float(M,D) double(M,D)
M：总位数 D：小数位数
例题
mysql> create table stu5(
-> num1 float(5,2), -- 浮点数
-> num2 double(6,1) -- 双精度数
-> );
Query OK, 0 rows affected (0.05 sec)
mysql> insert into stu5 values (3.1415,12.96);
Query OK, 1 row affected (0.00 sec)
mysql> select * from stu5;
+------+------+
| num1 | num2 |
+------+------+
| 3.14 | 13.0 |
+------+------+
1 row in set (0.00 sec)
MySQL浮点数支持科学计数法
mysql> create table stu6(
-> num float # 不指定位数，默认是小数点后面6位 double默认是17位
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> insert into stu6 values (5E2),(6E-2); # 插入科学计数法
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from stu6;
+------+
| num |
+------+
| 500 |
| 0.06 |
+------+
2 rows in set (0.00 sec)
浮点数精度会丢失
mysql> insert into stu6 values(99.999999999);
Query OK, 1 row affected (0.00 sec)
mysql> select * from stu6;
+------+
| num |
+------+
| 100 |
+------+
小结：
1、浮点数有单精度和双精度
2、浮点数支持科学计数法
3、浮点数精度会丢失
3、小数（定点数）
原理：将整数部分和小数部分分开存储
语法：
decimal(M,D)
例题：
mysql> create table stu8(
-> num decimal(20,9) # 存放定点数
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> insert into stu8 values(12.999999999);
Query OK, 1 row affected (0.00 sec)
mysql> select * from stu8;
+--------------+
| num |
+--------------+
| 12.999999999 |
+--------------+
1 row in set (0.00 sec)
小结：
1、decimal是变长的，大致是每9个数字用4个字节存储，整数和小数分开计算。M最大是65,D最大是30，默认是（10,2）。
2、定点和浮点都支持无符号、显示宽度0填充。
2.2.2 字符型
在数据库中没有字符串概念，只有字符，所以数据库中只能用单引号
| 数据类型 | 描述 |
|---|---|
| char | 定长字符，最大可以到255 |
| varchar | 可变长度字符，最大可以到65535 |
| tinytext | 28–1 =255 |
| text | 216–1 =65535 |
| mediumtext | 224–1 |
| longtext | 232–1 |
char(4)：存放4个字符，中英文一样。
varchar(L)实现变长机制，需要额外的空间来记录数据真实的长度。
L的理论长度是65535，但事实上达不到，因为有的字符是多字节字符，所以L达不到65535。
text系列的类型在表中存储的是地址，占用大小大约10个字节
一个记录的所有字段的总长度也不能超过65535个字节。
小结：
1、char是定长，var是变长
2、char最大值是255，varchar最大值是65535，具体要看字符编码
3、text系列在表中存储的是地址
4、一条记录的总长度不能超过65535
2.2.3 枚举(enum)
从集合中选择一个值作为数据（单选）
mysql> create table stu12(
-> name varchar(20),
-> sex enum('男','女','保密') # 枚举
-> );
Query OK, 0 rows affected (0.06 sec)
-- 插入的枚举值只能是枚举中提供的选项
mysql> insert into stu12 values ('tom','男');
Query OK, 1 row affected (0.00 sec)
-- 报错，只能插入男、女、保密
mysql> insert into stu12 values ('tom','不告诉你');
ERROR 1265 (01000): Data truncated for column 'sex' at row 1
枚举值是通过整形数字来管理的，第一个值是1，第二个值是2，以此类推，枚举值在数据库存储的是整形数字。
mysql> insert into stu12 values ('berry',2); -- 插入数字
Query OK, 1 row affected (0.00 sec)
mysql> select * from stu12;
+-------+------+
| name | sex |
+-------+------+
| tom | 男 |
| berry | 女 |
+-------+------+
mysql> select * from stu12 where sex=2; -- 2表示第二个枚举值
+-------+------+
| name | sex |
+-------+------+
| berry | 女 |
+-------+------+
1 row in set (0.00 sec)
枚举优点：
（1）、限制值
（2）、节省空间
（3）、运行速度快（整形比字符串运行速度快）
思考：已知枚举占用两个字节，所以最多可以有多少个枚举值？
答：2字节=16位，216=65536，范围是（0-65535），由于枚举从1开始，所以枚举值最多有65535个
2.2.4 集合(set)
从集合中选择一些值作为数据（多选）
mysql> create table stu13(
-> name varchar(20),
-> hobby set('爬山','读书','游泳','烫头') -- 集合
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> insert into stu13 values ('tom','爬山');
mysql> insert into stu13 values ('Berry','爬山,游泳');
Query OK, 1 row affected (0.00 sec)
mysql> insert into stu13 values ('Berry','游泳,爬山'); -- 插入的顺序不一样，但显示的顺序一样
Query OK, 1 row affected (0.00 sec)
mysql> select * from stu13;
+-------+-----------+
| name | hobby |
+-------+-----------+
| tom | 爬山 |
| Berry | 爬山,游泳 |
| Berry | 爬山,游泳 |
+-------+-----------+
3 rows in set (0.00 sec)
集合和枚举一样，也为每个集合元素分配一个固定值，分配方式是从前往后按2的0、1、2、…次方，转换成二进制后只有一位是1，其他都是0。
'爬山','读书','游泳','烫头'
1 2 4 8
mysql> select hobby+0 from stu13;
+---------+
| hobby+0 |
+---------+
| 1 |
| 5 |
| 5 |
+---------+
mysql> insert into stu13 values ('rose',15);
Query OK, 1 row affected (0.00 sec)
已知集合类型占8个字节，那么集合中最多有多少选项
答：有64个选项。
2.2.5 日期时间型
| 数据类型 | 描述 |
|---|---|
| datetime | 日期时间，占用8个字节 |
| date | 日期 占用3个字节 |
| time | 时间 占用3个字节 |
| year | 年份，占用1个字节 |
| timestamp | 时间戳，占用4个字节 |
1、datetime和date
datetime格式：年-月-日 小时:分钟:秒。支持的范围是'1000-01-01 00:00:00'到'9999-12-31
23:59:59'。
mysql> create table stu14(
-> t1 datetime, -- 日期时间
-> t2 date -- 日期
-> );
Query OK, 0 rows affected (0.05 sec)
-- 插入测试数据
mysql> insert into stu14 values ('2019-01-15 12:12:12','2019-01-15 12:12:12');
Query OK, 1 row affected, 1 warning (0.00 sec)
-- 查询
mysql> select * from stu14;
+---------------------+------------+
| t1 | t2 |
+---------------------+------------+
| 2019-01-15 12:12:12 | 2019-01-15 |
+---------------------+------------+
1 row in set (0.00 sec)
2、timestamp（时间戳）
datetime类型和timestamp类型表现上是一样的，他们的区别在于：
datetime从1000到9999，而timestamp从1970年~2038年（原因在于timestamp占用4个字节，和整形的范围一样，2038年01月19日11:14:07以后的秒数就超过了4个字节的长度）
mysql> create table stu15(
-> t1 timestamp
-> );
Query OK, 0 rows affected (0.06 sec)
mysql> insert into stu15 values ('2038-01-19 11:14:07');
Query OK, 1 row affected (0.00 sec)
3、year
只能表示1901~2155之间的年份，因为只占用1个字节，只能表示255个数
mysql> create table stu16(
-> y1 year
-> );
Query OK, 0 rows affected (0.08 sec)
mysql> insert into stu16 values (2155);
Query OK, 1 row affected (0.00 sec)
4、time
可以表示时间，也可以表示时间间隔。范围是：-838:59:59~838:59:59
mysql> create table stu17(
-> t1 time
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> insert into stu17 values ('12:12:12');
Query OK, 1 row affected (0.00 sec)
mysql> insert into stu17 values ('212:12:12');
Query OK, 1 row affected (0.00 sec)
mysql> insert into stu17 values ('-212:12:12');
Query OK, 1 row affected (0.00 sec)
mysql> insert into stu17 values ('839:00:00'); -- 报错
ERROR 1292 (22007): Incorrect time value: '839:00:00' for column 't1' at row 1
-- time支持以天的方式来表示时间间隔
mysql> insert into stu17 values ('10 10:25:25'); -- 10天10小时25分25秒
Query OK, 1 row affected (0.00 sec)
mysql> select * from stu17;
+------------+
| t1 |
+------------+
| 12:12:12 |
| 212:12:12 |
| -212:12:12 |
| 250:25:25 |
+------------+
4 rows in set (0.00 sec)
2.2.6 Boolean
MySQL不支持布尔型，true和false在数据库中对应的是1和0
mysql> create table stu18(
-> flag boolean
-> );
Query OK, 0 rows affected (0.05 sec)
mysql> desc stu18;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| flag | tinyint(1) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> insert into stu18 values (true),(false);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from stu18;
+------+
| flag |
+------+
| 1 |
| 0 |
+------+
2 rows in set (0.00 sec
小结：
boolean型在MySQL中对应的是tinyint。
2.2.6 练习题
电话号码一般使用什么数据类型存储? varchar
手机号码用什么数据类型 char
性别一般使用什么数据类型存储? char tinyint enum
年龄信息一般使用什么数据类型存储? tinyint
照片信息一般使用什么数据类型存储? binary
薪水一般使用什么数据类型存储? decimal
2.3 列属性
2.3.1 是否为空（null|not null）
null表示字段值可以为null
not null字段值不能为空
练习
学员姓名允许为空吗? not null
家庭地址允许为空吗? not null
电子邮件信息允许为空吗? null
考试成绩允许为空吗? null
2.3.2 默认值（default）
如果一个字段没有插入值，可以默认插入一个指定的值
mysql> create table stu19(
-> name varchar(20) not null default '姓名不详',
-> addr varchar(50) not null default '地址不详'
-> );
Query OK, 0 rows affected (0.05 sec)
mysql> insert into stu19(name) values ('tom');
Query OK, 1 row affected (0.00 sec)
mysql> insert into stu19 values (default,default);
Query OK, 1 row affected (0.00 sec)
mysql> select * from stu19;
+----------+----------+
| name | addr |
+----------+----------+
| tom | 地址不详 |
| 姓名不详 | 地址不详 |
+----------+----------+
2 rows in set (0.00 sec)
小结：
default关键字用来插入默认值
2.3.3 自动增长（auto_increment）
字段值从1开始，每次递增1，自动增长的值就不会有重复，适合用来生成唯一的id。在MySQL中只要是自动增长列必须是主键
2.3.4 主键（primary key）
主键概念：唯一标识表中的记录的一个或一组列称为主键。
特点：
1、不能重复、不能为空
2、一个表只能有一个主键。
作用：
1、保证数据完整性
2、加快查询速度
选择主键的原则
最少性：尽量选择单个键作为主键
稳定性：尽量选择数值更新少的列作为主键
比如：学号，姓名、地址 这三个字段都不重复，选哪个做主键
选学号，因为学号最稳定
练习
-- 创建主键方法一
mysql> create table stu20(
-> id int auto_increment primary key,
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.04 sec)
-- 创建主键方法二
mysql> create table stu21(
-> id int auto_increment,
-> name varchar(20),
-> primary key(id)
-> );
Query OK, 0 rows affected (0.02 sec)
组合键
mysql> create table stu22(
-> classname varchar(20),
-> stuname varchar(20),
-> primary key(classname,stuname) -- 创建组合键
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> desc stu22;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| classname | varchar(20) | NO | PRI | | |
| stuname | varchar(20) | NO | PRI | | |
+-----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
通过更改表添加主键
mysql> create table stu23(
-> id int,
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.05 sec)
-- 添加主键
mysql> alter table stu23 add primary key(id);
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
删除主键
mysql> alter table stu23 drop primary key;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
插入数据
mysql> create table stu25(
-> id tinyint unsigned auto_increment primary key,
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.05 sec)
-- 插入数据
mysql> insert into stu25 values (3,'tom'); -- 可以直接插入数字
Query OK, 1 row affected (0.06 sec)
-- 自动增长列可以插入null,让列的值自动递增
mysql> insert into stu25 values (null,'berry');
Query OK, 1 row affected (0.00 sec)
小结：
1、只要是auto_increment必须是主键，但是主键不一定是auto_increment
2、主键特点是不能重复不能为空
3、一个表只能有一个主键，但是一个主键可以有多个字段组成
4、自动增长列通过插入null值让其递增
5、自动增长列的数据被删除，默认不再重复使用。truncate table删除数据后，再次插入从1开始
练习
在主键列输入的数值，允许为空吗? 不可以
一个表可以有多个主键吗? 不可以
在一个学校数据库中，如果一个学校内允许重名的学员，但是一个班级内不允许学员重名，可以组合班级和姓名两个字段一起来作为主键吗？ 对
标识列（自动增长列）允许为字符数据类型吗？ 不允许
一个自动增长列中，插入3行，删除2行，插入3行，删除2行，插入3行，删除2行，再次插入是多少？ 10
2.3.5 唯一键（unique）
| 键 | 区别 |
|---|---|
| 主键 | 1、不能重复，不能为空 2、一个表只能有一个主键 |
| 唯一键 | 1、不能重刻，可以为空 2、一个表可以有多个唯一键 |
例题
-- 创建表的时候创建唯一键
mysql> create table stu26(
-> id int auto_increment primary key,
-> name varchar(20) unique -- 唯一键
-> );
Query OK, 0 rows affected (0.05 sec)
-- 方法二
mysql> create table stu27(
-> id int primary key,
-> name varchar(20),
-> unique(name)
-> );
Query OK, 0 rows affected (0.05 sec)
多学一招：
unique 或 unique key 是一样的
通过修改表添加唯一键
-- 将name设为唯一键
mysql> alter table stu28 add unique(name);
-- 将name,addr设为唯一键
mysql> alter table stu28 add unique(name),add unique(addr);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc stu28;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | UNI | NULL | |
| addr | varchar(20) | YES | UNI | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.02 sec)
通过show create table 查看唯一键的名字
通过唯一键的名字删除唯一键
mysql> alter table stu28 drop index name;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
2.3.6 备注（comment）
说明性文本
mysql> create table stu29(
-> id int primary key comment '学号',
-> name varchar(20) not null comment '姓名'
-> );
Query OK, 0 rows affected (0.03 sec)
注意：备注属于SQL代码的一部分
2.4 SQL注释
单行注释
-- 单行注释
# 单行注释
多行注释 /* */
2.5 数据完整性
2.5.1 数据完整性包括
1、实体完整性
1、主键约束
2、唯一约束
3、标识列
2、 域完整性
1、数据类型约束
2、非空约束
3、默认值约束
3、 引用完整性
外键约束
4、 自定义完整性
1、存储过程
2、触发器
2.5.2 主表和从表
- 主表中没有的记录，从表不允许插入
- 从表中有的记录，主表中不允许删除
- 删除主表前，先删子表
2.5.3 外键（foreign key）
外键：从表中的公共字段
-- 创建表的时候添加外键
drop table if exists stuinfo;
create table stuinfo(
id tinyint primary key,
name varchar(20)
)engine=innodb;
drop table if exists stuscore;
create table stuscore(
sid tinyint primary key,
score tinyint unsigned,
foreign key(sid) references stuinfo(id) -- 创建外键
)engine=innodb;
-- 通过修改表的时候添加外键
语法：alter table 从表 add foreign key(公共字段) references 主表(公共字段)
drop table if exists stuinfo;
create table stuinfo(
id tinyint primary key,
name varchar(20)
)engine=innodb;
drop table if exists stuscore;
create table stuscore(
sid tinyint primary key,
score tinyint unsigned
)engine=innodb;
alter table stuscore add foreign key (sid) references stuinfo(id)
删除外键
通过外键的名字删除外键
-- 删除外键
mysql> alter table stuscore drop foreign key `stuscore_ibfk_1`;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
小结：
1、只有innodb才能支持外键
2、公共字段的名字可以不一样，但是数据类型要一样
2.5.4 三种外键操作
1、 严格限制（参见主表和从表）
2、 置空操作（set null）：如果主表记录删除，或关联字段更新，则从表外键字段被设置为null。
3、 级联操作（cascade）：如果主表记录删除，则从表记录也被删除。主表更新，从表外键字段也更新。
语法：foreign key (外键字段) references 主表名 (关联字段) [主表记录删除时的动作] [主表记录更新时的动作]。
一般说删除时置空，更新时级联。
drop table if exists stuinfo;
create table stuinfo(
id tinyint primary key comment '学号，主键',
name varchar(20) comment '姓名'
)engine=innodb;
drop table if exists stuscore;
create table stuscore(
id int auto_increment primary key comment '主键',
sid tinyint comment '学号，外键',
score tinyint unsigned comment '成绩',
foreign key(sid) references stuinfo(id) on delete set null on update cascade
)engine=innodb;
小结：
置空、级联操作中外键不能是从表的主键
2.6 补充
phpstudy中MySQL默认不是严格模式，将MySQL设置成严格模式
打开my.ini，在sql-mode的值中，添加STRICT_TRANS_TABLES
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES"
测试
单词
medium：中等的
small:小
tiny:微小
big:大
3.1 今日目标
- 掌握一对多关系的数据表设计方案和原理；
- 掌握多对多关系的数据表设计方案和原理；
- 掌握where子句进行数据筛选；
- 掌握group by子句进行数据分类统计；
- 掌握order by子句进行数据排序；
- 了解mysql数据库的设计规范；
3.2 实体之间的关系
3.2.1 一对多（1：N）
主表中的一条记录对应从表中的多条记录
实现一对多的方式：主键和非主键建关系
问题：说出几个一对多的关系？
班主任表——学生表
品牌表——商品表
3.2.2 多对一（N：1）
多对一就是一对多
3.2.3 一对一（1:1）
如何实现一对一：主键和主键建关系
思考：一对一两个表完全可以用一个表实现，为什么还要分成两个表？
答：在字段数量很多情况下，数据量也就很大，每次查询都需要检索大量数据，这样效率低下。我们可以将所有字段分成两个部分，“常用字段”和“不常用字段”，这样对大部分查询者来说效率提高了。【表的垂直分割】
3.2.3 多对多（N：M）
主表中的一条记录对应从表中的多条记录，从表中的一条记录，对应主表中的多条记录
如何实现多对多：利用第三张关系表
问题：说出几个多对多的关系？
讲师表——学生表
课程表——学生表
商品表——订单表
小结：
如何实现一对一：主键和主键建关系
如果实现一对多：主键和非主键建关系
如何实现多对多：引入第三张关系表
3.3 数据库设计
3.3.1 数据库设计的步骤
-
收集信息：与该系统有关人员进行交流、坐谈，充分理解数据库需要完成的任务
-
标识对象（实体－Entity）：标识数据库要管理的关键对象或实体
-
标识每个实体的属性（Attribute）
-
标识对象之间的关系（Relationship）
-
将模型转换成数据库
-
规范化
3.3.2 例题
第一步：收集信息
BBS论坛的基本功能：
用户注册和登录，后台数据库需要存放用户的注册信息和在线状态信息；
用户发贴，后台数据库需要存放贴子相关信息，如贴子内容、标题等；
用户可以对发帖进行回复；
论坛版块管理：后台数据库需要存放各个版块信息，如版主、版块名称、贴子数等；
第二步：标识对象
实体一般是名词：
1、用户对象
2、板块对象
3、帖子对象
4、跟帖对象
第三步：标识每个实体的属性
第四步：标识对象之间的关系
3.3.3 绘制E-R图
E-R（Entity－Relationship）实体关系图）
完整的E-R图
3.3.4 将E-R图转成表
- 实体转成表，属性转成字段
- 如果没有合适的字段做主键，给表添加一个自动增长列做主键。
3.4 数据规范化
3.4.1 第一范式：确保每列原则性
第一范式：的目标是确保每列的原子性，一个字段表示一个含义
思考如下表是否满足第一范式
思考：地址包含省、市、县、地区是否需要拆分？
答：如果仅仅起地址的作用，不需要统计，可以不拆分；如果有按地区统计的功能需要拆分。
在实际项目中，建议拆分。
3.4.2 第二范式：非键字段必须依赖于键字段
第二范式：在满足第一范式的前提下，要求每个表只描述一件事情
思考：如下表设计是否合理
3.4.3 第三范式：消除传递依赖
第三范式：在满足第二范式的前提下，除了主键以外的其他列消除传递依赖。
思考：如下表设计是否合理？
结论：不满足第三范式，因为语文和数学确定了，总分就确定了
3.4.4 反3NF
范式越高，数据冗余越少，但是效率有时就越地下，为了提高运行效率，可以适当让数据冗余。
| 学号 | 姓名 | 语文 | 数学 | 总分 |
|---|---|---|---|---|
| 1 | 李白 | 77 | 88 | 165 |
上面的设计不满足第三范式，但是高考分数表就是这样设计的，为什么？
答：高考分数峰值访问量非常大，这时候就是性能更重要。当性能和规范化冲突的时候，我们首选性能。这就是“反三范式”。
小结
1、第一范式约束的所有字段
2、第二范式约束的主键和非主键的关系
3、第三范式约束的非主键之间的关系
4、范式越高，冗余越少，但表业越多。
5、规范化和性能的关系 ：性能比规范化更重要
3.4.5 例题
需求
假设某建筑公司要设计一个数据库。公司的业务规
则概括说明如下：
公司承担多个工程项目，每一项工程有：工程号、工程名称、施工人员等
公司有多名职工，每一名职工有：职工号、姓名、性别、职务（工程师、技术员）等
公司按照工时和小时工资率支付工资，小时工资率由职工的职务决定（例如，技术员的小时工资率与工程师不同）
标识实体
1、工程
2、职工
3、工时
4、小时工资率
3.5 查询语句
语法：select [选项] 列名 [from 表名] [where 条件] [group by 分组] [order by 排序][having 条件] [limit 限制]
3.5.1 字段表达式
-- 可以直接输出内容
mysql> select '锄禾日当午';
+------------+
| 锄禾日当午 |
+------------+
| 锄禾日当午 |
+------------+
1 row in set (0.00 sec)
-- 输出表达式
mysql> select 10*10;
+-------+
| 10*10 |
+-------+
| 100 |
+-------+
1 row in set (0.00 sec)
mysql> select ch,math,ch+math from stu;
+------+------+---------+
| ch | math | ch+math |
+------+------+---------+
| 80 | NULL | NULL |
| 77 | 76 | 153 |
| 55 | 82 | 137 |
| NULL | 74 | NULL |
-- 表达式部分可以用函数
mysql> select rand();
+--------------------+
| rand() |
+--------------------+
| 0.6669325378415478 |
+--------------------+
1 row in set (0.00 sec)
通过as给字段取别名
mysql> select '锄禾日当午' as '标题'; -- 取别名
+------------+
| 标题 |
+------------+
| 锄禾日当午 |
+------------+
1 row in set (0.00 sec)
mysql> select ch,math,ch+math as '总分' from stu;
+------+------+------+
| ch | math | 总分 |
+------+------+------+
| 80 | NULL | NULL |
| 77 | 76 | 153 |
| 55 | 82 | 137 |
| NULL | 74 | NULL |
-- 多学一招：as可以省略
mysql> select ch,math,ch+math '总分' from stu;
+------+------+------+
| ch | math | 总分 |
+------+------+------+
| 80 | NULL | NULL |
| 77 | 76 | 153 |
| 55 | 82 | 137 |
| NULL | 74 | NULL |
3.5.2 from子句
from：来自，from后面跟的是数据源。数据源可以有多个。返回笛卡尔积。
插入测试表
create table t1(
str char(2)
);
insert into t1 values ('aa'),('bb');
create table t2(
num int
);
insert into t2 values (10),(20);
测试
-- from子句
mysql> select * from t1;
+------+
| str |
+------+
| aa |
| bb |
+------+
2 rows in set (0.00 sec)
-- 多个数据源,返回笛卡尔积
mysql> select * from t1,t2;
+------+------+
| str | num |
+------+------+
| aa | 10 |
| bb | 10 |
| aa | 20 |
| bb | 20 |
+------+------+
4 rows in set (0.00 sec)
3.5.3 dual表
dual表是一个伪表。在有些特定情况下，没有具体的表的参与，但是为了保证select语句的完整又必须要一个表名，这时候就使用伪表。
mysql> select 10*10 as 结果 from dual;
+------+
| 结果 |
+------+
| 100 |
+------+
1 row in set (0.00 sec)
3.5.4 where子句
where后面跟的是条件，在数据源中进行筛选。返回条件为真记录
MySQL支持的运算符
-- 比较运算符
> 大于
< 小于
>= 大于等于
<= 小于等于
= 等于
!= 不等于
-- 逻辑运算符
and 与
or 或
not 非
-- 其他
in | not in 字段的值在枚举范围内
between…and|not between…and 字段的值在数字范围内
is null | is not null 字段的值不为空
例题：
-- 查找语文成绩及格的学生
mysql> select * from stu where ch>=60;
-- 查询语文和数学都及格的学生
mysql> select * from stu where ch>=60 and math>=60;
-- 查询语文或数学不及格的学生
mysql> select * from stu where ch<60 or math<60;
思考：如下语句输出什么？
mysql> select * from stu where 1; -- 输出所有数据
mysql> select * from stu where 0; -- 不输出数据
思考：如何查找北京和上海的学生
-- 通过or实现
mysql> select * from stu where stuaddress='北京' or stuaddress='上海';
-- 通过in语句实现
mysql> select * from stu where stuaddress in ('北京','上海');
-- 查询不是北京和上海的学生
mysql> select * from stu where stuaddress not in ('北京','上海');
思考：查找年龄在20~25之间
-- 方法一：
mysql> select * from stu where stuage>=20 and stuage<=25;
-- 方法二：
mysql> select * from stu where not(stuage<20 or stuage>25);
-- 方法三：between...and...
mysql> select * from stu where stuage between 20 and 25;
-- 年龄不在20~25之间
mysql> select * from stu where stuage not between 20 and 25;
思考：
-- 查找缺考的学生
mysql> select * from stu where ch is null or math is null;
+--------+----------+--------+--------+---------+------------+------+------+
| stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | ch | math |
+--------+----------+--------+--------+---------+------------+------+------+
| s25301 | 张秋丽 | 男 | 18 | 1 | 北京 | 80 | NULL |
| s25304 | 欧阳俊雄 | 男 | 28 | 4 | 天津 | NULL | 74 |
+--------+----------+--------+--------+---------+------------+------+------+
-- 查找没有缺考的学生
mysql> select * from stu where ch is not null and math is not null;
+--------+----------+--------+--------+---------+------------+------+------+
| stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | ch | math |
+--------+----------+--------+--------+---------+------------+------+------+
| s25302 | 李文才 | 男 | 31 | 3 | 上海 | 77 | 76 |
| s25303 | 李斯文 | 女 | 22 | 2 | 北京 | 55 | 82 |
| s25305 | 诸葛丽丽 | 女 | 23 | 7 | 河南 | 72 | 56 |
| s25318 | 争青小子 | 男 | 26 | 6 | 天津 | 86 | 92 |
| s25319 | 梅超风 | 女 | 23 | 5 | 河北 | 74 | 67 |
| s25320 | Tom | 男 | 24 | 8 | 北京 | 65 | 67 |
| s25321 | Tabm | 女 | 23 | 9 | 河北 | 88 | 77 |
+--------+----------+--------+--------+---------+------------+------+------+
7 rows in set (0.00 sec)
-- 查找需要补考的学生
mysql> select * from stu where ch<60 or math<60 or ch is null or math is null;
+--------+----------+--------+--------+---------+------------+------+------+
| stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | ch | math |
+--------+----------+--------+--------+---------+------------+------+------+
| s25301 | 张秋丽 | 男 | 18 | 1 | 北京 | 80 | NULL |
| s25303 | 李斯文 | 女 | 22 | 2 | 北京 | 55 | 82 |
| s25304 | 欧阳俊雄 | 男 | 28 | 4 | 天津 | NULL | 74 |
| s25305 | 诸葛丽丽 | 女 | 23 | 7 | 河南 | 72 | 56 |
+--------+----------+--------+--------+---------+------------+------+------+
4 rows in set (0.00 sec)
练习：
-- 1、查找学号是s25301,s25302,s25303的学生
mysql> select * from stu where stuno in ('s25301','s25302','s25303');
+--------+---------+--------+--------+---------+------------+------+------+
| stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | ch | math |
+--------+---------+--------+--------+---------+------------+------+------+
| s25301 | 张秋丽 | 男 | 18 | 1 | 北京 | 80 | NULL |
| s25302 | 李文才 | 男 | 31 | 3 | 上海 | 77 | 76 |
| s25303 | 李斯文 | 女 | 22 | 2 | 北京 | 55 | 82 |
+--------+---------+--------+--------+---------+------------+------+------+
3 rows in set (0.00 sec)
-- 2、查找年龄是18~20的学生
mysql> select * from stu where stuage between 18 and 20;
+--------+---------+--------+--------+---------+------------+------+------+
| stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | ch | math |
+--------+---------+--------+--------+---------+------------+------+------+
| s25301 | 张秋丽 | 男 | 18 | 1 | 北京 | 80 | NULL |
+--------+---------+--------+--------+---------+------------+------+------+
1 row in set (0.00 sec)
3.5.5 group by 【分组查询】
将查询的结果分组，分组查询目的在于统计数据。
-- 查询男生和女生的各自语文平均分
mysql> select stusex,avg(ch) '平均分' from stu group by stusex;
+--------+---------+
| stusex | 平均分 |
+--------+---------+
| 女 | 72.2500 |
| 男 | 77.0000 |
+--------+---------+
2 rows in set (0.00 sec)
-- 查询男生和女生各自多少人
mysql> select stusex,count(*) 人数 from stu group by stusex;
+--------+------+
| stusex | 人数 |
+--------+------+
| 女 | 4 |
| 男 | 5 |
+--------+------+
2 rows in set (0.00 sec)
-- 查询每个地区多少人
mysql> select stuaddress,count(*) from stu group by stuaddress;
+------------+----------+
| stuaddress | count(*) |
+------------+----------+
| 上海 | 1 |
| 北京 | 3 |
| 天津 | 2 |
| 河北 | 2 |
| 河南 | 1 |
+------------+----------+
5 rows in set (0.00 sec)
-- 每个地区的数学平均分
mysql> select stuaddress,avg(math) from stu group by stuaddress;
+------------+-----------+
| stuaddress | avg(math) |
+------------+-----------+
| 上海 | 76.0000 |
| 北京 | 74.5000 |
| 天津 | 83.0000 |
| 河北 | 72.0000 |
| 河南 | 56.0000 |
+------------+-----------+
5 rows in set (0.00 sec)
查询字段是普通字段，只取第一个值
通过group_concat()函数将同一组的值连接起来显示
mysql> select group_concat(stuname),stusex,avg(math) from stu group by stusex;
+-------------------------------------+--------+-----------+
| group_concat(stuname) | stusex | avg(math) |
+-------------------------------------+--------+-----------+
| 李斯文,诸葛丽丽,梅超风,Tabm | 女 | 70.5000 |
| 张秋丽,李文才,欧阳俊雄,争青小子,Tom | 男 | 77.2500 |
+-------------------------------------+--------+-----------+
2 rows in set (0.00 sec)
多列分组
mysql> select stuaddress,stusex,avg(math) from stu group by stuaddress,stusex;
+------------+--------+-----------+
| stuaddress | stusex | avg(math) |
+------------+--------+-----------+
| 上海 | 男 | 76.0000 |
| 北京 | 女 | 82.0000 |
| 北京 | 男 | 67.0000 |
| 天津 | 男 | 83.0000 |
| 河北 | 女 | 72.0000 |
| 河南 | 女 | 56.0000 |
+------------+--------+-----------+
6 rows in set (0.00 sec)
小结：
1、如果是分组查询，查询字段是分组字段和聚合函数。
2、查询字段是普通字段，只取第一个值
3、group_concat()将同一组的数据连接起来
3.5.6 order by排序
asc：升序【默认】
desc：降序
-- 按年龄的升序排列
mysql> select * from stu order by stuage asc;
mysql> select * from stu order by stuage; -- 默认是升序
-- 按总分降序
mysql> select *,ch+math '总分' from stu order by ch+math desc;
多列排序
-- 年龄升序，如果年龄一样，按ch降序排列
mysql> select * from stu order by stuage asc,ch desc;
思考如下代码表示什么含义
select * from stu order by stuage desc,ch desc; #年龄降序，语文降序
select * from stu order by stuage desc,ch asc; #年龄降序，语文升序
select * from stu order by stuage,ch desc; #年龄升序、语文降序
select * from stu order by stuage,ch; #年龄升序、语文升序
3.5.7 having条件
having：是在结果集上进行条件筛选
例题
-- 查询女生
mysql> select * from stu where stusex='女';
+--------+----------+--------+--------+---------+------------+------+------+
| stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | ch | math |
+--------+----------+--------+--------+---------+------------+------+------+
| s25303 | 李斯文 | 女 | 22 | 2 | 北京 | 55 | 82 |
| s25305 | 诸葛丽丽 | 女 | 23 | 7 | 河南 | 72 | 56 |
| s25319 | 梅超风 | 女 | 23 | 5 | 河北 | 74 | 67 |
| s25321 | Tabm | 女 | 23 | 9 | 河北 | 88 | 77 |
+--------+----------+--------+--------+---------+------------+------+------+
4 rows in set (0.00 sec)
-- 查询女生
mysql> select * from stu having stusex='女';
+--------+----------+--------+--------+---------+------------+------+------+
| stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | ch | math |
+--------+----------+--------+--------+---------+------------+------+------+
| s25303 | 李斯文 | 女 | 22 | 2 | 北京 | 55 | 82 |
| s25305 | 诸葛丽丽 | 女 | 23 | 7 | 河南 | 72 | 56 |
| s25319 | 梅超风 | 女 | 23 | 5 | 河北 | 74 | 67 |
| s25321 | Tabm | 女 | 23 | 9 | 河北 | 88 | 77 |
+--------+----------+--------+--------+---------+------------+------+------+
4 rows in set (0.00 sec)
-- 查询女生姓名
mysql> select stuname from stu where stusex='女';
+----------+
| stuname |
+----------+
| 李斯文 |
| 诸葛丽丽 |
| 梅超风 |
| Tabm |
+----------+
4 rows in set (0.00 sec)
-- 使用having报错，因为结果集中没有stusex字段
mysql> select stuname from stu having stusex='女';
ERROR 1054 (42S22): Unknown column 'stusex' in 'having clause'
小结：having和where的区别：
where是对原始数据进行筛选，having是对记录集进行筛选。
3.5.8 limit
语法：limit [起始位置]，显示长度
-- 从第0个位置开始取，取3条记录
mysql> select * from stu limit 0,3;
-- 从第2个位置开始取，取3条记录
mysql> select * from stu limit 2,3;
+--------+----------+--------+--------+---------+------------+------+------+
| stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | ch | math |
+--------+----------+--------+--------+---------+------------+------+------+
| s25303 | 李斯文 | 女 | 22 | 2 | 北京 | 55 | 82 |
| s25304 | 欧阳俊雄 | 男 | 28 | 4 | 天津 | NULL | 74 |
| s25305 | 诸葛丽丽 | 女 | 23 | 7 | 河南 | 72 | 56 |
+--------+----------+--------+--------+---------+------------+------+------+
3 rows in set (0.00 sec)
起始位置可以省略，默认是从0开始
mysql> select * from stu limit 3;
+--------+---------+--------+--------+---------+------------+------+------+
| stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | ch | math |
+--------+---------+--------+--------+---------+------------+------+------+
| s25301 | 张秋丽 | 男 | 18 | 1 | 北京 | 80 | NULL |
| s25302 | 李文才 | 男 | 31 | 3 | 上海 | 77 | 76 |
| s25303 | 李斯文 | 女 | 22 | 2 | 北京 | 55 | 82 |
+--------+---------+--------+--------+---------+------------+------+------+
3 rows in set (0.00 sec)
例题：找出班级总分前三名
mysql> select *,ch+math total from stu order by (ch+math) desc limit 0,3;
+--------+----------+--------+--------+---------+------------+------+------+-------+
| stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | ch | math | total |
+--------+----------+--------+--------+---------+------------+------+------+-------+
| s25318 | 争青小子 | 男 | 26 | 6 | 天津 | 86 | 92 | 178 |
| s25321 | Tabm | 女 | 23 | 9 | 河北 | 88 | 77 | 165 |
| s25302 | 李文才 | 男 | 31 | 3 | 上海 | 77 | 76 | 153 |
+--------+----------+--------+--------+---------+------------+------+------+-------+
3 rows in set (0.00 sec)
多学一招：limit在update和delete语句中也是可以使用的。
-- 前3名语文成绩加1分
mysql> update stu set ch=ch+1 order by ch+math desc limit 3;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
-- 前3名删除
mysql> delete from stu order by ch+math desc limit 3;
Query OK, 3 rows affected (0.00 sec)
3.5.9 查询语句中的选项
查询语句中的选项有两个：
1、 all：显示所有数据 【默认】
2、 distinct：去除结果集中重复的数据
mysql> select all stuaddress from stu;
+------------+
| stuaddress |
+------------+
| 北京 |
| 北京 |
| 天津 |
| 河南 |
| 河北 |
| 北京 |
+------------+
6 rows in set (0.00 sec)
-- 去除重复的项
mysql> select distinct stuaddress from stu;
+------------+
| stuaddress |
+------------+
| 北京 |
| 天津 |
| 河南 |
| 河北 |
+------------+
4 rows in set (0.00 sec)
3.6 聚合函数
- sum() 求和
- avg() 求平均值
- max() 求最大值
- min() 求最小值
- count() 求记录数
# 语文最高分
mysql> select max(ch) '语文最大值' from stu;
+------------+
| 语文最大值 |
+------------+
| 88 |
+------------+
1 row in set (0.00 sec)
#求语文总分、语文平均分、语文最低分、总人数
mysql> select max(ch) 语文最高分,min(ch) 语文最低分,sum(ch) 语文总分,avg(ch) 语文平均分,count(*) 总人数 from stu;
+------------+------------+----------+------------+--------+
| 语文最高分 | 语文最低分 | 语文总分 | 语文平均分 | 总人数 |
+------------+------------+----------+------------+--------+
| 88 | 55 | 597 | 74.6250 | 9 |
+------------+------------+----------+------------+--------+
1 row in set (0.00 sec)
3.7 模糊查询
3.7.1 通配符
- _ [下划线] 表示任意一个字符
- % 表示任意字符
练习
1、满足“T_m”的有（A、C）
A：Tom B：Toom C：Tam D：Tm E：Tmo
2、满足“T_m_”的有（ B C）
A:Tmom B:Tmmm C:T1m2 D:Tmm E:Tm
3、满足“张%”的是（ABCD）
A:张三 B：张三丰 C：张牙舞爪 D：张 E：小张
4、满足“%诺基亚%”的是（ABCD）
A：诺基亚2100 B：2100诺基亚 C：把我的诺基亚拿过来 D：诺基亚
3.7.2 模糊查询（like）
模糊查询的条件不能用'='，要使用like。
mysql> select * from stu where stuname like 'T_m';
+--------+---------+--------+--------+---------+------------+------+------+
| stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | ch | math |
+--------+---------+--------+--------+---------+------------+------+------+
| s25320 | Tom | 男 | 24 | 8 | 北京 | 65 | 67 |
+--------+---------+--------+--------+---------+------------+------+------+
1 row in set (0.00 sec)
-- 查询姓张的学生
mysql> select * from stu where stuname like '张%';
+--------+---------+--------+--------+---------+------------+------+------+
| stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | ch | math |
+--------+---------+--------+--------+---------+------------+------+------+
| s25301 | 张秋丽 | 男 | 18 | 1 | 北京 | 80 | NULL |
+--------+---------+--------+--------+---------+------------+------+------+
1 row in set (0.00 sec)
3.8 union（联合）
插入测试数据
create table emp(
id tinyint unsigned auto_increment primary key,
name varchar(20) not null,
skill set('PHP','mysql','java')
);
insert into emp values (null,'李白',1),(null,'杜甫',2),(null,'白居易',4)
insert into emp values (null,'争青小子',3)
3.8.1 union的使用
作用：将多个select语句结果集纵向联合起来
语法：select 语句 union [选项] select 语句 union [选项] select 语句
-- 查询stu表中的姓名和emp表中姓名 结果自动合并的重复的记录
mysql> select stuname from stu union select name from emp;
例题：查询上海的男生和北京的女生
-- 方法一：
mysql> select * from stu where (stuaddress='上海' and stusex='男') or (stuaddress='北京' and stusex='女');
+--------+---------+--------+--------+---------+------------+------+------+
| stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | ch | math |
+--------+---------+--------+--------+---------+------------+------+------+
| s25302 | 李文才 | 男 | 31 | 3 | 上海 | 77 | 76 |
| s25303 | 李斯文 | 女 | 22 | 2 | 北京 | 55 | 82 |
+--------+---------+--------+--------+---------+------------+------+------+
2 rows in set (0.00 sec)
-- 方法二：union
mysql> select * from stu where stuaddress='上海' and stusex='男' union select * from stu where stuaddress='北京' and stusex='女';
+--------+---------+--------+--------+---------+------------+------+------+
| stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | ch | math |
+--------+---------+--------+--------+---------+------------+------+------+
| s25302 | 李文才 | 男 | 31 | 3 | 上海 | 77 | 76 |
| s25303 | 李斯文 | 女 | 22 | 2 | 北京 | 55 | 82 |
+--------+---------+--------+--------+---------+------------+------+------+
2 rows in set (0.00 sec)
结论：union可以将一个复杂的条件转成两个简单的条件
3.8.2 union的选项
union的选项有两个
1、 all：显示所有数据
2、 distinct：去除重复的数据【默认】
mysql> select stuname from stu union all select name from emp;
3.8.3 union的注意事项
1、 union两边的select语句的字段个数必须一致
2、 union两边的select语句的字段名可以不一致，最终按第一个select语句的字段名。
3、 union两边的select语句中的数据类型可以不一致。
3.9 补充
3.9.1 插入数据时主键冲突
-- 插入测试表
mysql> create table stu(
-> id char(4) primary key,
-> name varchar(20)
-> )engine=innodb;
Query OK, 0 rows affected (0.06 sec)
-- 插入测试数据
mysql> insert into stu values ('s001','tom');
Query OK, 1 row affected (0.00 sec)
如果插入的主键重复会报错
解决方法：如果插入的主键重复就执行替换
语法一：
mysql> replace into stu values('s002','ketty');
Query OK, 2 rows affected (0.00 sec)
# 原理：如果插入的主键不重复就直接插入，如果主键重复就替换（删除原来的记录，插入新记录）
语法二（推荐）：
on duplicate key update # 当插入的值与主键或唯一键有冲突执行update操作
-- 例题
mysql> insert into stu values ('s002','李白') on duplicate key update name='李白';
Query OK, 2 rows affected (0.00 sec)
# 插入的数据和主键或唯一键起冲突，将s002的name字段改为‘李白’
4.1 今日目标
- 理解查询五子句的顺序关系；
- 掌握两张表的联合查询方法；
- 理解连接查询的原理；
- 掌握子查询的使用方式；
- 掌握预处理的实现步骤；
- 理解事务的基本工作原理；
- 掌握事务的四个特点；
- 理解视图的概念和作用；
4.2 多表查询
4.2.1 内连接
规则：返回两个表的公共记录
语法：
-- 语法一
select * from 表1 inner join 表2 on 表1.公共字段=表2.公共字段
-- 语法二
select * from 表1，表2 where 表1.公共字段=表2.公共字段
例题
-- inner join
mysql> select * from stuinfo inner join stumarks on stuinfo.stuno=stumarks.stuno;
+--------+----------+--------+--------+---------+------------+---------+--------+-------------+---------+
| stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | examNo | stuNo | writtenExam | labExam |
+--------+----------+--------+--------+---------+------------+---------+--------+-------------+---------+
| s25303 | 李斯文 | 女 | 22 | 2 | 北京 | s271811 | s25303 | 80 | 58 |
| s25302 | 李文才 | 男 | 31 | 3 | 上海 | s271813 | s25302 | 50 | 90 |
| s25304 | 欧阳俊雄 | 男 | 28 | 4 | 天津 | s271815 | s25304 | 65 | 50 |
| s25301 | 张秋丽 | 男 | 18 | 1 | 北京 | s271816 | s25301 | 77 | 82 |
| s25318 | 争青小子 | 男 | 26 | 6 | 天津 | s271819 | s25318 | 56 | 48 |
+--------+----------+--------+--------+---------+------------+---------+--------+-------------+---------+
5 rows in set (0.00 sec)
-- 相同的字段只显示一次
mysql> select s.stuno,stuname,stusex,writtenexam,labexam from stuinfo s inner join stumarks m on s.stuno=m.stuno;
+--------+----------+--------+-------------+---------+
| stuno | stuname | stusex | writtenexam | labexam |
+--------+----------+--------+-------------+---------+
| s25303 | 李斯文 | 女 | 80 | 58 |
| s25302 | 李文才 | 男 | 50 | 90 |
| s25304 | 欧阳俊雄 | 男 | 65 | 50 |
| s25301 | 张秋丽 | 男 | 77 | 82 |
| s25318 | 争青小子 | 男 | 56 | 48 |
+--------+----------+--------+-------------+---------+
5 rows in set (0.00 sec)
-- 使用where
mysql> select * from stuinfo,stumarks where stuinfo.stuno=stumarks.stuno;
+--------+----------+--------+--------+---------+------------+---------+--------+-------------+---------+
| stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | examNo | stuNo | writtenExam | labExam |
+--------+----------+--------+--------+---------+------------+---------+--------+-------------+---------+
| s25303 | 李斯文 | 女 | 22 | 2 | 北京 | s271811 | s25303 | 80 | 58 |
| s25302 | 李文才 | 男 | 31 | 3 | 上海 | s271813 | s25302 | 50 | 90 |
| s25304 | 欧阳俊雄 | 男 | 28 | 4 | 天津 | s271815 | s25304 | 65 | 50 |
| s25301 | 张秋丽 | 男 | 18 | 1 | 北京 | s271816 | s25301 | 77 | 82 |
| s25318 | 争青小子 | 男 | 26 | 6 | 天津 | s271819 | s25318 | 56 | 48 |
+--------+----------+--------+--------+---------+------------+---------+--------+-------------+---------+
5 rows in set (0.00 sec)
多学一招：
-- 1、内连接中inner可以省略
select * from 表1 join 表2 on 表1.公共字段=表2.公共字段
mysql> select * from stuinfo join stumarks on stuinfo.stuno=stumarks.stuno;
+--------+----------+--------+--------+---------+------------+---------+--------+-------------+---------+
| stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | examNo | stuNo | writtenExam | labExam |
+--------+----------+--------+--------+---------+------------+---------+--------+-------------+---------+
| s25303 | 李斯文 | 女 | 22 | 2 | 北京 | s271811 | s25303 | 80 | 58 |
| s25302 | 李文才 | 男 | 31 | 3 | 上海 | s271813 | s25302 | 50 | 90 |
| s25304 | 欧阳俊雄 | 男 | 28 | 4 | 天津 | s271815 | s25304 | 65 | 50 |
| s25301 | 张秋丽 | 男 | 18 | 1 | 北京 | s271816 | s25301 | 77 | 82 |
| s25318 | 争青小子 | 男 | 26 | 6 | 天津 | s271819 | s25318 | 56 | 48 |
+--------+----------+--------+--------+---------+------------+---------+--------+-------------+---------+
5 rows in set (0.00 sec)
-- 如何实现三表查询
select * from 表1 inner join 表2 on 表1.公共字段=表2.公共字段 inner join 表3 on 表2.公共字段=表3.公共字段
-- 表连接越多，效率越低
思考：
select * from 表1 inner join 表2 on 表1.公共字段=表2.公共字段
和
select * from 表2 inner join 表1 on 表1.公共字段=表2.公共字段 一样吗？
答：一样的
4.2.2 左外连接
规则：以左边的表为准，右边如果没有对应的记录用null显示
语法：
select * from 表1 left join 表2 on 表1.公共字段=表2.公共字段
例题：
mysql> select stuname,writtenexam,labexam from stuinfo left join stumarks on stuinfo.stuno=stumarks.stuno;
+----------+-------------+---------+
| stuname | writtenexam | labexam |
+----------+-------------+---------+
| 张秋丽 | 77 | 82 |
| 李文才 | 50 | 90 |
| 李斯文 | 80 | 58 |
| 欧阳俊雄 | 65 | 50 |
| 诸葛丽丽 | NULL | NULL |
| 争青小子 | 56 | 48 |
| 梅超风 | NULL | NULL |
+----------+-------------+---------+
7 rows in set (0.01 sec)
思考：
select * from 表1 left join 表2 on 表1.公共字段=表2.公共字段
和
select * from 表2 left join 表1 on 表1.公共字段=表2.公共字段 一样吗？
答：不一样，第一个SQL以表1为准，第二个SQL以表2为准。
4.2.3 右外连接
规则：以右边的表为准，左边如果没有对应的记录用null显示
语法：
select * from 表1 right join 表2 on 表1.公共字段=表2.公共字段
例题：
mysql> select stuname,writtenexam,labexam from stuinfo right join stumarks on stuinfo.stuno=stumarks.stuno;
+----------+-------------+---------+
| stuname | writtenexam | labexam |
+----------+-------------+---------+
| 李斯文 | 80 | 58 |
| 李文才 | 50 | 90 |
| 欧阳俊雄 | 65 | 50 |
| 张秋丽 | 77 | 82 |
| 争青小子 | 56 | 48 |
| NULL | 66 | 77 |
+----------+-------------+---------+
6 rows in set (0.00 sec)
思考
select * from 表1 left join 表2 on 表1.公共字段=表2.公共字段
和
select * from 表2 right join 表1 on 表1.公共字段=表2.公共字段 一样吗？
答：一样
4.2.4 交叉连接
语法，返回笛卡尔积
select * from 表1 cross join 表2
例题
-- 交叉连接
mysql> select * from stuinfo cross join stumarks;
-- 交叉连接有连接表达式与内连接是一样的
mysql> select * from stuinfo cross join stumarks on stuinfo.stuno=stumarks.stuno;
+--------+----------+--------+--------+---------+------------+---------+--------+-------------+---------+
| stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | examNo | stuNo | writtenExam | labExam |
+--------+----------+--------+--------+---------+------------+---------+--------+-------------+---------+
| s25303 | 李斯文 | 女 | 22 | 2 | 北京 | s271811 | s25303 | 80 | 58 |
| s25302 | 李文才 | 男 | 31 | 3 | 上海 | s271813 | s25302 | 50 | 90 |
| s25304 | 欧阳俊雄 | 男 | 28 | 4 | 天津 | s271815 | s25304 | 65 | 50 |
| s25301 | 张秋丽 | 男 | 18 | 1 | 北京 | s271816 | s25301 | 77 | 82 |
| s25318 | 争青小子 | 男 | 26 | 6 | 天津 | s271819 | s25318 | 56 | 48 |
+--------+----------+--------+--------+---------+------------+---------+--------+-------------+---------+
5 rows in set (0.00 sec)
小结
1、交叉连接如果没有连接条件返回笛卡尔积
2、如果有连接条件和内连接是一样的。
4.2.5 自然连接
自动判断条件连接，判断的条件是依据同名字段
1、自然内连接（natural join）
mysql> select * from stuinfo natural join stumarks;
+--------+----------+--------+--------+---------+------------+---------+-------------+---------+
| stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | examNo | writtenExam | labExam |
+--------+----------+--------+--------+---------+------------+---------+-------------+---------+
| s25303 | 李斯文 | 女 | 22 | 2 | 北京 | s271811 | 80 | 58 |
| s25302 | 李文才 | 男 | 31 | 3 | 上海 | s271813 | 50 | 90 |
| s25304 | 欧阳俊雄 | 男 | 28 | 4 | 天津 | s271815 | 65 | 50 |
| s25301 | 张秋丽 | 男 | 18 | 1 | 北京 | s271816 | 77 | 82 |
| s25318 | 争青小子 | 男 | 26 | 6 | 天津 | s271819 | 56 | 48 |
+--------+----------+--------+--------+---------+------------+---------+-------------+---------+
5 rows in set (0.00 sec)
2、自然左外连接（natural left join）
mysql> select * from stuinfo natural left join stumarks;
+--------+----------+--------+--------+---------+------------+---------+-------------+---------+
| stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | examNo | writtenExam | labExam |
+--------+----------+--------+--------+---------+------------+---------+-------------+---------+
| s25301 | 张秋丽 | 男 | 18 | 1 | 北京 | s271816 | 77 | 82 |
| s25302 | 李文才 | 男 | 31 | 3 | 上海 | s271813 | 50 | 90 |
| s25303 | 李斯文 | 女 | 22 | 2 | 北京 | s271811 | 80 | 58 |
| s25304 | 欧阳俊雄 | 男 | 28 | 4 | 天津 | s271815 | 65 | 50 |
| s25305 | 诸葛丽丽 | 女 | 23 | 7 | 河南 | NULL | NULL | NULL |
| s25318 | 争青小子 | 男 | 26 | 6 | 天津 | s271819 | 56 | 48 |
| s25319 | 梅超风 | 女 | 23 | 5 | 河北 | NULL | NULL | NULL |
+--------+----------+--------+--------+---------+------------+---------+-------------+---------+
7 rows in set (0.00 sec)
3、自然右外连接（natural right join）
mysql> select * from stuinfo natural right join stumarks;
+--------+---------+-------------+---------+----------+--------+--------+---------+------------+
| stuNo | examNo | writtenExam | labExam | stuName | stuSex | stuAge | stuSeat | stuAddress |
+--------+---------+-------------+---------+----------+--------+--------+---------+------------+
| s25303 | s271811 | 80 | 58 | 李斯文 | 女 | 22 | 2 | 北京 |
| s25302 | s271813 | 50 | 90 | 李文才 | 男 | 31 | 3 | 上海 |
| s25304 | s271815 | 65 | 50 | 欧阳俊雄 | 男 | 28 | 4 | 天津 |
| s25301 | s271816 | 77 | 82 | 张秋丽 | 男 | 18 | 1 | 北京 |
| s25318 | s271819 | 56 | 48 | 争青小子 | 男 | 26 | 6 | 天津 |
| s25320 | s271820 | 66 | 77 | NULL | NULL | NULL | NULL | NULL |
+--------+---------+-------------+---------+----------+--------+--------+---------+------------+
6 rows in set (0.00 sec)
小结：
1、表连接是通过同名字段来连接的
2、如果没有同名字段就返回笛卡尔积
3、同名的连接字段只显示一个，并且将该字段放在最前面
4.2.6 using
using用来指定连接字段
mysql> select * from stuinfo inner join stumarks using(stuno);
+--------+----------+--------+--------+---------+------------+---------+-------------+---------+
| stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | examNo | writtenExam | labExam |
+--------+----------+--------+--------+---------+------------+---------+-------------+---------+
| s25303 | 李斯文 | 女 | 22 | 2 | 北京 | s271811 | 80 | 58 |
| s25302 | 李文才 | 男 | 31 | 3 | 上海 | s271813 | 50 | 90 |
| s25304 | 欧阳俊雄 | 男 | 28 | 4 | 天津 | s271815 | 65 | 50 |
| s25301 | 张秋丽 | 男 | 18 | 1 | 北京 | s271816 | 77 | 82 |
| s25318 | 争青小子 | 男 | 26 | 6 | 天津 | s271819 | 56 | 48 |
+--------+----------+--------+--------+---------+------------+---------+-------------+---------+
5 rows in set (0.00 sec)
using的结果也会对公共字段进行优化，优化的规则和自然连接是一样的；
4.2.7 练习
1、显示地区及每个地区参加笔试的人数，并按人数降序排列
-- 第一步： 显示地区及每个地区参加笔试的人数
mysql> select stuaddress,count(writtenexam) from stuinfo left join stumarks using(stuno) group by stuaddress;
+------------+--------------------+
| stuaddress | count(writtenexam) |
+------------+--------------------+
| 上海 | 1 |
| 北京 | 2 |
| 天津 | 2 |
| 河北 | 0 |
| 河南 | 0 |
+------------+--------------------+
5 rows in set (0.00 sec)
-- 第二步：将结果降序排列
mysql> select stuaddress,count(writtenexam) c from stuinfo left join stumarks using(stuno) group by stuaddress order by c desc;
+------------+---+
| stuaddress | c |
+------------+---+
| 北京 | 2 |
| 天津 | 2 |
| 上海 | 1 |
| 河北 | 0 |
| 河南 | 0 |
+------------+---+
5 rows in set (0.00 sec)
2、显示有学生参加考试的地区
-- having筛选
mysql> select stuaddress,count(writtenexam) c from stuinfo left join stumarks using(stuno) group by stuaddress having c>0;
+------------+---+
| stuaddress | c |
+------------+---+
| 上海 | 1 |
| 北京 | 2 |
| 天津 | 2 |
+------------+---+
3 rows in set (0.00 sec)
-- 表连接实现
-- 第一步：右连接获取有成绩的地区
mysql> select stuaddress from stuinfo right join stumarks using(stuno);
+------------+
| stuaddress |
+------------+
| 北京 |
| 上海 |
| 天津 |
| 北京 |
| 天津 |
| NULL |
+------------+
6 rows in set (0.00 sec)
-- 第二步：去重复
mysql> select distinct stuaddress from stuinfo right join stumarks using(stuno);
+------------+
| stuaddress |
+------------+
| 北京 |
| 上海 |
| 天津 |
| NULL |
+------------+
4 rows in set (0.00 sec)
-- 去除null
mysql> select distinct stuaddress from stuinfo right join stumarks using(stuno) having stuaddress is not null;
+------------+
| stuaddress |
+------------+
| 北京 |
| 上海 |
| 天津 |
+------------+
3 rows in set (0.00 sec)
3、显示男生和女生的人数
-- 方法一： 分组查询
mysql> select stusex,count(*) from stuinfo group by stusex;
+--------+----------+
| stusex | count(*) |
+--------+----------+
| 女 | 3 |
| 男 | 4 |
+--------+----------+
2 rows in set (0.00 sec)
-- 方法二： union
mysql> select stusex,count(*) from stuinfo where stusex='男' union select stusex,count(*) from stuinfo where stusex='女';
+--------+----------+
| stusex | count(*) |
+--------+----------+
| 男 | 4 |
| 女 | 3 |
+--------+----------+
2 rows in set (0.00 sec)
-- 方法三：直接写条件
mysql> select sum(stusex='男') 男,sum(stusex='女') 女 from stuinfo;
+------+------+
| 男 | 女 |
+------+------+
| 4 | 3 |
+------+------+
1 row in set (0.00 sec)
4、显示每个地区男生、女生、总人数
mysql> select stuaddress,count(*) 总人数,sum(stusex='男') 男,sum(stusex='女') 女 from stuinfo group by stuaddress;
+------------+--------+------+------+
| stuaddress | 总人数 | 男 | 女 |
+------------+--------+------+------+
| 上海 | 1 | 1 | 0 |
| 北京 | 2 | 1 | 1 |
| 天津 | 2 | 2 | 0 |
| 河北 | 1 | 0 | 1 |
| 河南 | 1 | 0 | 1 |
+------------+--------+------+------+
5 rows in set (0.00 sec)
4.3 子查询
语法：select * from 表1 where （子查询）
外面的查询称为父查询
子查询为父查询提供查询条件
4.3.1 标量子查询
特点：子查询返回的值是一个
-- 查找笔试成绩是80的学生
mysql> select * from stuinfo where stuno=(select stuno from stumarks where writtenexam=80);
+--------+---------+--------+--------+---------+------------+
| stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress |
+--------+---------+--------+--------+---------+------------+
| s25303 | 李斯文 | 女 | 22 | 2 | 北京 |
+--------+---------+--------+--------+---------+------------+
1 row in set (0.00 sec)
-- 查找最高分的学生
-- 方法一
mysql> select * from stuinfo where stuno=(select stuno from stumarks order by writtenexam desc limit 1);
+--------+---------+--------+--------+---------+------------+
| stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress |
+--------+---------+--------+--------+---------+------------+
| s25303 | 李斯文 | 女 | 22 | 2 | 北京 |
+--------+---------+--------+--------+---------+------------+
1 row in set (0.00 sec)
-- 方法二：
mysql> select * from stuinfo where stuno=(select stuno from stumarks where writtenexam=(select max(writtenexam) from stumarks))
+--------+---------+--------+--------+---------+------------+
| stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress |
+--------+---------+--------+--------+---------+------------+
| s25303 | 李斯文 | 女 | 22 | 2 | 北京 |
+--------+---------+--------+--------+---------+------------+
1 row in set (0.00 sec)
4.3.2 列子查询
特点： 子查询返回的结果是一列
如果子查询的结果返回多条记录，不能使用等于，用in或not in
-- 查找及格的同学
mysql> select * from stuinfo where stuno in (select stuno from stumarks where writtenexam>=60);
+--------+----------+--------+--------+---------+------------+
| stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress |
+--------+----------+--------+--------+---------+------------+
| s25301 | 张秋丽 | 男 | 18 | 1 | 北京 |
| s25303 | 李斯文 | 女 | 22 | 2 | 北京 |
| s25304 | 欧阳俊雄 | 男 | 28 | 4 | 天津 |
+--------+----------+--------+--------+---------+------------+
3 rows in set (0.00 sec)
-- 查询不及格的同学
mysql> select * from stuinfo where stuno in (select stuno from stumarks where writtenexam<60);
+--------+----------+--------+--------+---------+------------+
| stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress |
+--------+----------+--------+--------+---------+------------+
| s25302 | 李文才 | 男 | 31 | 3 | 上海 |
| s25318 | 争青小子 | 男 | 26 | 6 | 天津 |
+--------+----------+--------+--------+---------+------------+
2 rows in set (0.00 sec)
-- 查询需要补考的学生
mysql> select * from stuinfo where stuno not in (select stuno from stumarks where writtenexam>=60);
+--------+----------+--------+--------+---------+------------+
| stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress |
+--------+----------+--------+--------+---------+------------+
| s25302 | 李文才 | 男 | 31 | 3 | 上海 |
| s25305 | 诸葛丽丽 | 女 | 23 | 7 | 河南 |
| s25318 | 争青小子 | 男 | 26 | 6 | 天津 |
| s25319 | 梅超风 | 女 | 23 | 5 | 河北 |
+--------+----------+--------+--------+---------+------------+
4 rows in set (0.00 sec)
4.3.3 行子查询
特点：子查询返回的结果是多个字段组成
-- 查找语文成绩最高的男生和女生
mysql> select * from stu where(stusex,ch) in (select stusex,max(ch) from stu group by stusex);
+--------+----------+--------+--------+---------+------------+------+------+
| stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | ch | math |
+--------+----------+--------+--------+---------+------------+------+------+
| s25318 | 争青小子 | 男 | 26 | 6 | 天津 | 86 | 92 |
| s25321 | Tabm | 女 | 23 | 9 | 河北 | 88 | 77 |
+--------+----------+--------+--------+---------+------------+------+------+
2 rows in set (0.00 sec)
4.3.4 表子查询
特点：将子查询的结果作为表
-- 查找语文成绩最高的男生和女生
mysql> select * from (select * from stu order by ch desc) t group by stusex;
+--------+----------+--------+--------+---------+------------+------+------+
| stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | ch | math |
+--------+----------+--------+--------+---------+------------+------+------+
| s25321 | Tabm | 女 | 23 | 9 | 河北 | 88 | 77 |
| s25318 | 争青小子 | 男 | 26 | 6 | 天津 | 86 | 92 |
+--------+----------+--------+--------+---------+------------+------+------+
2 rows in set (0.00 sec)
注意：from后面跟的是数据源，如果将子查询当成表来看， 必须给结果集取别名。
4.3.5 exists子查询
-- 如果笔试成绩有人超过80人，就显示所有学生信息
mysql> select * from stuinfo where exists (select * from stumarks where writtenexam>=80);
-- 没有超过80的学生，就显示所有学生信息
mysql> select * from stuinfo where not exists (select * from stumarks where writtenexam>=80);
Empty set (0.00 sec)
作用：提高查询效率
4.4 视图
4.4.1 概述
1、视图是一张虚拟表，它表示一张表的部分数据或多张表的综合数据，其结构和数据是建立在对表的查询基础上
2、视图中并不存放数据，而是存放在视图所引用的原始表（基表）中
3、同一张原始表，根据不同用户的不同需求，可以创建不同的视图
4.4.2 作用
1、筛选表中的行
2、防止未经许可的用户访问敏感数据
3、隐藏数据表的结构
4、降低数据表的复杂程度
4.4.3 创建视图
语法：
-- 创建视图
create view 视图名
as
select 语句；
-- 查询视图
select 列名 from 视图
例题
-- 创建视图
mysql> create view view1
-> as
-> select * from stu where ch>=60 and math>=60;
Query OK, 0 rows affected (0.00 sec)
-- 查询视图
mysql> select * from view1;
+--------+----------+--------+--------+---------+------------+------+------+
| stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | ch | math |
+--------+----------+--------+--------+---------+------------+------+------+
| s25302 | 李文才 | 男 | 31 | 3 | 上海 | 77 | 76 |
| s25318 | 争青小子 | 男 | 26 | 6 | 天津 | 86 | 92 |
| s25319 | 梅超风 | 女 | 23 | 5 | 河北 | 74 | 67 |
| s25320 | Tom | 男 | 24 | 8 | 北京 | 65 | 67 |
| s25321 | Tabm | 女 | 23 | 9 | 河北 | 88 | 77 |
+--------+----------+--------+--------+---------+------------+------+------+
5 rows in set (0.02 sec)
-- 视图可以使得降低SQL语句的复杂度
mysql> create view view2
-> as
-> select stuno,stusex,writtenexam,labexam from stuinfo natural join stumarks;
Query OK, 0 rows affected (0.01 sec)
4.4.4 修改视图
语法
alter view 视图名
as
select 语句
例题：
mysql> alter view view2
-> as
-> select stuname from stuinfo;
Query OK, 0 rows affected (0.00 sec)
4.4.5 删除视图
语法
drop view [if exists ] 视图1，视图,...
例题
mysql> drop view view2;
Query OK, 0 rows affected (0.00 sec)
4.4.6 查看视图信息
-- 方法一；
mysql> show tables; -- 显示所有的表和视图
-- 方法二：精确查找视图（视图信息存储在information_schema下的views表中）
mysql> select table_name from information_schema.views;
+------------+
| table_name |
+------------+
| view1 |
+------------+
1 row in set (0.05 sec)
-- 方法三：通过表的comment属性查询视图
mysql> show table status\G; -- 查询所有表和视图的详细状态信息
mysql> show table status where comment='view'\G -- 只查找视图信息
查询视图的结构
mysql> desc view1;
查询创建视图的语法
mysql> show create view view1\G
4.4.7 视图算法
场景：找出语文成绩最高的男生和女生
方法一：
mysql> select * from (select * from stu order by ch desc) t group by stusex;
+--------+----------+--------+--------+---------+------------+------+------+
| stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | ch | math |
+--------+----------+--------+--------+---------+------------+------+------+
| s25321 | Tabm | 女 | 23 | 9 | 河北 | 88 | 77 |
| s25318 | 争青小子 | 男 | 26 | 6 | 天津 | 86 | 92 |
+--------+----------+--------+--------+---------+------------+------+------+
2 rows in set (0.00 sec)
方法二：
mysql> create view view3
-> as
-> select * from stu order by ch desc;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from view3 group by stusex;
+--------+---------+--------+--------+---------+------------+------+------+
| stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | ch | math |
+--------+---------+--------+--------+---------+------------+------+------+
| s25301 | 张秋丽 | 男 | 18 | 1 | 北京 | 80 | NULL |
| s25303 | 李斯文 | 女 | 22 | 2 | 北京 | 55 | 82 |
+--------+---------+--------+--------+---------+------------+------+------+
2 rows in set (0.00 sec)
结论：方法一和方法二的结果不一样，这是因为视图的算法造成的。
视图的算法有：
1、merge：合并算法（将视图语句和外层语句合并后再执行）
2、temptable:临时表算法（将视图作为一个临时表来执行）
3、undefined：未定义算法（用哪种算法有MySQL决定，这是默认算法，视图一般会选merge算法）
重新通过视图实现
-- 创建视图，指定算法为临时表算法
mysql> create or replace algorithm=temptable view view3
-> as
-> select * from stu order by ch desc;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from view3 group by stusex;
+--------+----------+--------+--------+---------+------------+------+------+
| stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | ch | math |
+--------+----------+--------+--------+---------+------------+------+------+
| s25321 | Tabm | 女 | 23 | 9 | 河北 | 88 | 77 |
| s25318 | 争青小子 | 男 | 26 | 6 | 天津 | 86 | 92 |
+--------+----------+--------+--------+---------+------------+------+------+
2 rows in set (0.00 sec)
结论：和子查询结果一致。
4.5 事务
4.5.1 概述
事务(TRANSACTION)是一个整体，要么一起执行，要么一起不执行
4.5.2 事务特性
事务必须具备以下四个属性，简称ACID 属性：
原子性（Atomicity）：事务是一个完整的操作。事务的各步操作是不可分的（原子的）；要么都执行，要么都不执行
一致性（Consistency）：当事务完成时，数据必须处于一致状态
隔离性（Isolation）：对数据进行修改的所有并发事务是彼此隔离的。
永久性（Durability）：事务完成后，它对数据库的修改被永久保持。
4.5.3 事务处理
开启事务
start transaction 或 begin [work]
提交事务
commit
回滚事务
rollback
例题：
-- 插入测试数据
mysql> create table bank(
-> card char(4) primary key comment '卡号',
-> money decimal(10,2) not null
-> )engine=innodb charset=utf8;
Query OK, 0 rows affected (0.05 sec)
mysql> insert into bank values ('1001',1000),('1002',1);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
-- 开启事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter // -- 更改定界符
mysql> update bank set money=money-100 where card='1001';
-> update bank set money=money+100 where card='1002' //
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
-- 回滚事务
mysql> rollback //
Query OK, 0 rows affected (0.00 sec)
mysql> select * from bank //
+------+---------+
| card | money |
+------+---------+
| 1001 | 1000.00 |
| 1002 | 1.00 |
+------+---------+
2 rows in set (0.00 sec)
------------------------------------------------------------------
-- 开启事务
mysql> start transaction //
Query OK, 0 rows affected (0.00 sec)
mysql> update bank set money=money-100 where card='1001';
-> update bank set money=money+100 where card='1002' //
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
-- 提交事务
mysql> commit //
Query OK, 0 rows affected (0.00 sec)
mysql> select * from bank //
+------+--------+
| card | money |
+------+--------+
| 1001 | 900.00 |
| 1002 | 101.00 |
+------+--------+
设置事务的回滚点
-- 开启事务
mysql> begin //
Query OK, 0 rows affected (0.00 sec)
mysql> insert into bank values ('1003',500) //
Query OK, 1 row affected (0.00 sec)
-- 记录事务的回滚点
mysql> savepoint a1 //
Query OK, 0 rows affected (0.00 sec)
mysql> insert into bank values ('1004',200) //
Query OK, 1 row affected (0.00 sec)
-- 回滚到回滚点
mysql> rollback to a1 //
Query OK, 0 rows affected (0.00 sec)
-- 查询
mysql> select * from bank //
+------+--------+
| card | money |
+------+--------+
| 1001 | 900.00 |
| 1002 | 101.00 |
| 1003 | 500.00 |
+------+--------+
3 rows in set (0.00 sec)
自动提交事务
每一个SQL语句都是一个独立的事务
小结：
1、事务是事务开启的时候开始
2、提交事务、回滚事务后事务都结束
3、只有innodb支持事务
4、一个SQL语句就是一个独立的事务，开启事务是将多个SQL语句放到一个事务中执行
4.6 索引
4.6.1 概述
优点
加快查询速度
缺点：
带索引的表在数据库中需要更多的存储空间
增、删、改命令需要更长的处理时间，因为它们需要对索引进行更新
4.6.2 创建索引的指导原则
适合创建索引的列
1、该列用于频繁搜索
2、该列用于对数据进行排序
3、在WHERE子句中出现的列，在join子句中出现的列。
请不要使用下面的列创建索引：
1、列中仅包含几个不同的值。
2、表中仅包含几行。为小型表创建索引可能不太划算，因为MySQL在索引中搜索数据所花的时间比在表中逐行搜索所花的时间更长
4.6.3 创建索引
1、主键索引：主要创建了主键就会自动的创建主键索引
2、唯一索引：创建唯一键就创建了唯一索引
-- 创建表的时候添加唯一索引
create table t5(
id int primary key,
name varchar(20),
unique ix_name(name) -- 添加唯一索引
);
-- 给表添加唯一索引
mysql> create table t5(
-> name varchar(20),
-> addr varchar(50)
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> create unique index ix_name on t5(name);
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
-- 通过更改表的方式创建唯一索引
mysql> alter table t5 add unique ix_addr (addr);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
普通索引
-- 创建表的时候添加普通索引
mysql> create table t6(
-> id int primary key,
-> name varchar(20),
-> index ix_name(name)
-> );
Query OK, 0 rows affected (0.02 sec)
-- 给表添加普通索引
mysql> create table t7(
-> name varchar(20),
-> addr varchar(50)
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> create index ix_name on t7(name) ;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
-- 通过更改表的方式创建索引
mysql> alter table t7 add index ix_addr(addr);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
小结：
1、创建主键就会创建主键索引
2、创建唯一键就会创建唯一索引
3、创建唯一键的语法
--语法一
create unique [index] 索引名 on 表名（字段名）
-- 方法二
alter table 表名 add uniqe [index] 索引名(字段名)
4、创建普通索引
-- 语法一
create index 索引名 on 表名（字段名）
-- 语法二
alter table 表名 add index 索引名(字段名)
5、索引创建后，数据库根据查询语句自动选择索引
4.6.4 删除索引
语法：drop index 索引名 on 表名
mysql> drop index ix_name on t7;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
4.7 函数
4.7.1 数字类
-- 获取随机数
mysql> select rand();
+------------------+
| rand() |
+------------------+
| 0.25443412666622 |
+------------------+
1 row in set (0.00 sec)
-- 随机排序
mysql> select * from stuinfo order by rand();
-- 随机获取一条记录
mysql> select * from stuinfo order by rand() limit 1;
-- 四舍五入，向上取整，向下取整
mysql> select round(3.1415926,3) '四舍五入',truncate(3.14159,3) '截取数据',ceil(3.1) '向上取整',floor(3.9) '向下取整';
+----------+----------+----------+----------+
| 四舍五入 | 截取数据 | 向上取整 | 向下取整 |
+----------+----------+----------+----------+
| 3.142 | 3.141 | 4 | 3 |
+----------+----------+----------+----------+
1 row in set (0.04 sec)
注意： 截取数据直接截取，不四舍五入
4.7.2 字符串类
-- 大小写转换
mysql> select ucase('i name is tom') '转成大写',lcase('My Name IS TOM') '转成小写';
+---------------+----------------+
| 转成大写 | 转成小写 |
+---------------+----------------+
| I NAME IS TOM | my name is tom |
+---------------+----------------+
1 row in set (0.00 sec)
-- 截取字符串
mysql> select left('abcdef',3) '从左边截取',right('abcdef',3) '从右边截取',substring('abcdef',2,3) '字符串';
+------------+------------+--------+
| 从左边截取 | 从右边截取 | 字符串 |
+------------+------------+--------+
| abc | def | bcd |
+------------+------------+--------+
1 row in set (0.00 sec)
-- 字符串相连
mysql> select concat('中国','北京','顺义') '地址';
+--------------+
| 地址 |
+--------------+
| 中国北京顺义 |
+--------------+
1 row in set (0.00 sec)
mysql> select concat(stuname,'-',stusex) 信息 from stuinfo;
+-------------+
| 信息 |
+-------------+
| 张秋丽-男 |
| 李文才-男 |
| 李斯文-女 |
| 欧阳俊雄-男 |
| 诸葛丽丽-女 |
| 争青小子-男 |
| 梅超风-女 |
+-------------+
7 rows in set (0.00 sec)
-- coalesce(str1,str2) :str1有值显示str1,如果str1为空就显示str2
-- 将成绩为空的显示为缺考
mysql> select stuname,coalesce(writtenexam,'缺考'),coalesce(labexam,'缺考') from stuinfo natural left join stumarks;
+----------+------------------------------+--------------------------+
| stuname | coalesce(writtenexam,'缺考') | coalesce(labexam,'缺考') |
+----------+------------------------------+--------------------------+
| 张秋丽 | 77 | 82 |
| 李文才 | 50 | 90 |
| 李斯文 | 80 | 58 |
| 欧阳俊雄 | 65 | 50 |
| 诸葛丽丽 | 缺考 | 缺考 |
| 争青小子 | 56 | 48 |
| 梅超风 | 缺考 | 缺考 |
+----------+------------------------------+--------------------------+
7 rows in set (0.02 sec)
-- length（）:字节长度，char_length()：字符长度
mysql> select length('锄禾日当午') 字节,char_length('锄禾日当午') 字符;
+------+------+
| 字节 | 字符 |
+------+------+
| 10 | 5 |
+------+------+
1 row in set (0.00 sec)
4.7.3 时间类
-- 时间戳
mysql> select unix_timestamp();
+------------------+
| unix_timestamp() |
+------------------+
| 1560330458 |
+------------------+
1 row in set (0.00 sec)
-- 格式化时间戳
mysql> select from_unixtime(unix_timestamp());
+---------------------------------+
| from_unixtime(unix_timestamp()) |
+---------------------------------+
| 2019-06-12 17:08:18 |
+---------------------------------+
1 row in set (0.05 sec)
-- 获取当前格式化时间
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2019-06-12 17:08:50 |
+---------------------+
1 row in set (0.00 sec)
-- 获取年，月，日，小时，分钟，秒
mysql> select year(now()) 年,month(now()) 月,day(now()) 日,hour(now()) 小时,minute(now()) 分钟,second(now())秒;
+------+------+------+------+------+------+
| 年 | 月 | 日 | 小时 | 分钟 | 秒 |
+------+------+------+------+------+------+
| 2019 | 6 | 12 | 17 | 10 | 48 |
+------+------+------+------+------+------+
1 row in set (0.00 sec)
-- 星期，本年第几天;
mysql> select dayname(now()) 星期,dayofyear(now()) 本年第几天;
+-----------+------------+
| 星期 | 本年第几天 |
+-----------+------------+
| Wednesday | 163 |
+-----------+------------+
1 row in set (0.00 sec)
-- 日期相减
mysql> select datediff(now(),'2010-08-08') 相距天数;
+----------+
| 相距天数 |
+----------+
| 3230 |
+----------+
1 row in set (0.00 sec)
4.7.4 加密函数
1、md5()
2、sha()
mysql> select md5('aa');
+----------------------------------+
| md5('aa') |
+----------------------------------+
| 4124bc0a9335c27f086f24ba207a4912 |
+----------------------------------+
1 row in set (0.00 sec)
mysql> select sha('aa');
+------------------------------------------+
| sha('aa') |
+------------------------------------------+
| e0c9035898dd52fc65c41454cec9c4d2611bfb37 |
+------------------------------------------+
1 row in set (0.00 sec)
4.8 预处理
每个代码的段的执行都要经历：词法分析——语法分析——编译——执行
预编译一次，可以多次执行。用来解决一条SQL语句频繁执行的问题。
预处理语句：prepare 预处理名字 from ‘sql语句’
执行预处理：execute 预处理名字 [using 变量]
例题：不带参数的预处理
-- 创建预处理
mysql> prepare stmt from 'select * from stuinfo';
Query OK, 0 rows affected (0.06 sec)
Statement prepared
-- 执行预处理
mysql> execute stmt;
+--------+----------+--------+--------+---------+------------+
| stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress |
+--------+----------+--------+--------+---------+------------+
| s25301 | 张秋丽 | 男 | 18 | 1 | 北京 |
| s25302 | 李文才 | 男 | 31 | 3 | 上海 |
| s25303 | 李斯文 | 女 | 22 | 2 | 北京 |
| s25304 | 欧阳俊雄 | 男 | 28 | 4 | 天津 |
| s25305 | 诸葛丽丽 | 女 | 23 | 7 | 河南 |
| s25318 | 争青小子 | 男 | 26 | 6 | 天津 |
| s25319 | 梅超风 | 女 | 23 | 5 | 河北 |
+--------+----------+--------+--------+---------+------------+
7 rows in set (0.00 sec)
例题：带一个参数的预处理
-- 创建带有位置占位符的预处理语句
mysql> prepare stmt from 'select * from stuinfo where stuno=?' ;
Query OK, 0 rows affected (0.00 sec)
Statement prepared
-- 调用预处理，并传参数
mysql> delimiter //
mysql> set @id='s25301';
-> execute stmt using @id //
Query OK, 0 rows affected (0.00 sec)
+--------+---------+--------+--------+---------+------------+
| stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress |
+--------+---------+--------+--------+---------+------------+
| s25301 | 张秋丽 | 男 | 18 | 1 | 北京 |
+--------+---------+--------+--------+---------+------------+
1 row in set (0.00 sec)
例题：传递多个参数
mysql> prepare stmt from 'select * from stuinfo where stuage>? and stusex=?' //
Query OK, 0 rows affected (0.00 sec)
Statement prepared
mysql> set @age=20;
-> set @sex='男';
-> execute stmt using @age,@sex //
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
+--------+----------+--------+--------+---------+------------+
| stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress |
+--------+----------+--------+--------+---------+------------+
| s25302 | 李文才 | 男 | 31 | 3 | 上海 |
| s25304 | 欧阳俊雄 | 男 | 28 | 4 | 天津 |
| s25318 | 争青小子 | 男 | 26 | 6 | 天津 |
+--------+----------+--------+--------+---------+------------+
3 rows in set (0.00 sec)
小结：
1、MySQL中变量以@开头
2、通过set给变量赋值
3、？是位置占位符
5.1 今日目标
- 理解数据备份与还原的重要性；
- 掌握mysqldump.exe备份方式；
- 掌握SQL备份后的还原方式；
- 掌握Mysqli操作MySQL服务器的过程；
- 掌握PHP利用Mysqli实现数据表的增删改查操作
- 掌握PHP操作数据库的封装含义及意义
- 能够实现新增新闻入库；
- 能够实现更新新闻信息
- 能够实现删除新闻信息
- 能够实现显示新闻列表
5.2 连接数据库
通过PHP做MySQL的客户端
5.2.1 开启mysqli扩展
在php.ini中开启mysqli扩展
extension=php_mysqli.dll
开启扩展后重启服务器，就可以使用mysqli_函数了，
5.2.2 连接数据库
创建news数据库
-- 创建表
drop table if exists news;
create table news(
id int unsigned auto_increment primary key comment '主键',
title varchar(20) not null comment '标题',
content text not null comment '内容',
createtime int not null comment '添加时间'
)engine=innodb charset=utf8 comment '新闻表';
-- 插入测试数据
insert into news values (null,'锄禾','锄禾日当午',unix_timestamp());
insert into news values (null,'草','离离原上草',unix_timestamp());
思考：时间字段可以用datetime类型，也可以使用int类型。一般用int，因为datetime占用8个字节，int占用4个字节。
连接数据库
mysqli_connect(主机IP，用户名，密码，数据库名，端口号) //如果端口号是3306可以省略
mysqli_connect_error()：获取连接数据库的错误信息
mysqli_connect_errno()：获取连接数据库的错误编码
mysqli_set_charset(连接对象,字符编码)
代码如下：
<?php
//连接数据库，连接成功返回连接对象
$link=@mysqli_connect('localhost','root','root','data','3306');
//var_dump($link); //object(mysqli)
if(mysqli_connect_error()){
echo '错误号：'.mysqli_connect_errno(),'<br>'; //显示错误编码
echo '错误信息：'.mysqli_connect_error(); //显示错误信息
exit;
}
//设置字符编码
mysqli_set_charset($link,'utf8');
脚下留心：与数据库相关用utf8，与页面显示相关用utf-8
5.3 操作数据
5.3.1 数据操作语句
通过mysqli_query()执行SQL语句
增、删、改语句执行成功返回true，失败返回false
<?php
//1、连接数据库
$link=mysqli_connect('localhost','root','root','data');
//2、设置支付编码
mysqli_set_charset($link,'utf8');
//3、执行SQL语句
//3.1 执行insert语句
/*
$rs=mysqli_query($link,"insert into news values (null,'静夜思','床前明月光',unix_timestamp())");
if($rs)
echo '自动增长的编号是：'.mysqli_insert_id($link);
*/
//3.2 执行update语句
/*
$rs=mysqli_query($link,"update news set content='疑是地上霜' where id=4");
if($rs)
echo '受影响的记录数是：'.mysqli_affected_rows($link);
else{
echo '错误码：'.mysqli_errno($link),'<br>';
echo '错误信息：'.mysqli_error($link);
}
*/
//3.3 执行delete语句
mysqli_query($link,"delete from news where id=5");
用到的函数
mysqli_query()：执行SQL语句
mysqli_insert_id()：获取插入记录自动增长的ID
mysqli_affected_rows()：获取受影响的记录数
mysqli_error()：获取执行SQL语句的错误信息
mysqli_errno()：获取执行SQL语句的错误码
5.3.2 数据查询语句
数据查询用select、desc、show，成功会返回结果集，失败返回false
<?php
//1、连接数据库
$link=@mysqli_connect('localhost','root','root','data') or die('错误信息：'.mysqli_connect_error());
//2、设置字符编码
mysqli_query($link,'set names utf8');
//3、执行查询语句
$rs=mysqli_query($link,'select * from news');
//var_dump($rs); //object(mysqli_result)
//4、获取对象中的数据
//4.1 将对象中的一条数据匹配成索引数组,指针下移一条
//$rows=mysqli_fetch_row($rs);
//4.2 将对象中的一条数据匹配成关联数组,指针下移一条
//$rows=mysqli_fetch_assoc($rs);
//4.3 将对象中的一条数据匹配成索引，关联数组,指针下移一条
//$rows=mysqli_fetch_array($rs);
//4.4 总列数、总行数
//echo '总行数'.mysqli_num_rows($rs),'<br>';
//echo '总列数'.mysqli_num_fields($rs),'<br>';
//4.5 获取所有数据
//$list=mysqli_fetch_all($rs); //默认是索引数组
//$list=mysqli_fetch_all($rs,MYSQLI_NUM); //匹配成索引数组
//$list=mysqli_fetch_all($rs,MYSQLI_ASSOC); //匹配成关联数组
$list=mysqli_fetch_all($rs,MYSQLI_BOTH); //匹配成关联、索引数组
echo '<pre>';
print_r($list);
//5、销毁结果集
mysqli_free_result($rs);
//6、关闭连接
mysqli_close($link);
使用的函数
mysqli_fetch_assoc()：将一条数组匹配关联数组
mysqli_fetch_row()：将一条记录匹配成索引数组
mysqli_fetch_array()：将一条记录匹配成既有关联数组又有索引数组
mysqli_fetch_all()：匹配所有记录
mysqli_num_rows()：总行数
mysqli_num_fields()：总记录数
mysqli_free_result()：销毁结果集
mysqli_close()：关闭连接
5.4 新闻模块
5.4.1 包含文件
由于所有的操作都要连接数据库，将连接数据库的代码存放到包含文件中
步骤
1、在站点下创建inc文件夹
2、在inc下创建conn.php文件，用来连接数据库，代码就是上面连接数据库的代码
代码实现
<?php
//连接数据库
$link=@mysqli_connect('localhost','root','root','data') or die('错误：'.mysqli_connect_error());
mysqli_set_charset($link,'utf8');
5.4.2 显示新闻
步骤：
1、连接数据库
2、获取数据
3、遍历循环数据
代码
<style type="text/css">
table{
width:780px;
border:solid 1px #000;
margin:auto;
}
th,td{
border:solid 1px #000;
}
</style>
<body>
<?php
//1、连接数据库
require './inc/conn.php';
//2、获取数据
$rs=mysqli_query($link,'select * from news order by id desc'); //返回结果集对象
$list=mysqli_fetch_all($rs,MYSQLI_ASSOC); //将结果匹配成关联数组
?>
<table>
<tr>
<th>编号</th> <th>标题</th> <th>内容</th> <th>时间</th> <th>修改</th> <th>删除</th>
<!--3、循环显示数据-->
<?php foreach($list as $rows):?>
<tr>
<td><?php echo $rows['id']?></td>
<td><?php echo $rows['title']?></td>
<td><?php echo $rows['content']?></td>
<td><?php echo date('Y-m-d H:i:s',$rows['createtime'])?></td>
<td><input type="button" value="修改" onclick=""></td>
<td><input type="button" value="删除" onclick=""></td>
</tr>
<?php endforeach;?>
</tr>
</table>
</body>
运行结果
5.4.3 添加新闻
步骤：
1、创建表单
2、连接数据库
3、将新闻数据写入到数据库中
入口(list.php）
<a href="./add.php">添加新闻</a>
代码实现
<body>
<?php
if(!empty($_POST)) {
//2、连接数据库
require './inc/conn.php';
//3、插入数据
$time=time(); //获取时间戳
$sql="insert into news values (null,'{$_POST['title']}','{$_POST['content']}',$time)"; //拼接SQL语句
if(mysqli_query($link,$sql)) //执行SQL语句
header('location:./list.php'); //插入成功就跳转到list.php页面
else{
echo 'SQL语句插入失败<br>';
echo '错误码：'.mysqli_errno($link),'<br>';
echo '错误信息：'.mysqli_error($link);
}
}
?>
<!--1、创建表单-->
<form method="post" action="">
标题： <input type="text" name="title"> <br /> <br />
内容: <textarea name="content" rows="5" cols="30"></textarea> <br /> <br />
<input type="submit" name="button" value="提交">
</form>
</body>
运行结果
5.4.4 删除新闻
步骤：
1、在list.php页面点击删除按钮，跳转到del.php页面，传递删除的id
2、在del.php页面连接数据库
3、通过id删除数据
4、删除成功后，跳转到list.php
入口（list.php）
<input type="button" value="删除" onclick="if(confirm('确定要删除吗'))location.href='./del.php?id=<?php echo $rows['id']?>'">
del.php
<?php
//1、连接数据库
require './inc/conn.php';
//2、拼接SQL语句
$sql="delete from news where id={$_GET['id']}";
//3、执行SQL语句
if(mysqli_query($link,$sql))
header('location:./list.php');
else{
echo '删除失败';
}
小结：
1、一个页面是否写HTML架构，取决于是否有显示功能。
2、如果一个页面只是做业务逻辑，没有显示功能，就不需要写HTML架构，比如del.php页面
5.4.5 修改新闻
入口（list.php）
<input type="button" value="修改" onclick="location.href='edit.php?id=<?php echo $rows['id']?>'">
edit.php页面
步骤
第一步：显示修改界面
1、连接数据库
2、获取修改的数据
3、将数据显示到表单中
第二步：执行修改逻辑
1、获取新数据
2、拼接修改的SQL语句，执行修改逻辑
代码如下
<?php
//连接数据库
require './inc/conn.php';
//1、获取修改的数据库
$sql="select * from news where id={$_GET['id']}"; //拼接SQL语句
$rs=mysqli_query($link,$sql); //获取修改的数据
$rows=mysqli_fetch_assoc($rs); //将修改的数据匹配成一维关联数组
//2、执行修改的逻辑
if(!empty($_POST)) {
$id=$_GET['id']; //获取修改的id
$title=$_POST['title']; //修改的标题
$content=$_POST['content']; //修改的内容
$sql="update news set title='$title',content='$content' where id=$id"; //拼接SQL语句
if(mysqli_query($link,$sql))
header('location:list.php'); //修改成功跳转到list.php页面
else
echo '错误：'.mysqli_error($link);
exit;
}
?>
<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>无标题文档</title>
</head>
<body>
<form method="post" action="">
标题： <input type="text" name="title" value='<?php echo $rows['title']?>'> <br /> <br />
内容: <textarea name="content" rows="5" cols="30"><?php echo $rows['content']?></textarea> <br /> <br />
<input type="submit" name="button" value="提交">
<input type="button" value="返回" onclick="location.href='list.php'">
</form>
</body>
</html>
运行结果
5.5 数据备份与还原
数据库中的数据需要定期备份，数据量小的可以一周备份一次，数据量的可以一天备份一次。
5.5.1 数据备份
利用mysqldump工具，语法：
mysqldump 数据库连接 数据库 > SQL文件备份地址
例题：
-- 将data数据库中所有的表导出到data.sql中
F:\wamp\PHPTutorial\MySQL\bin>mysqldump -uroot -proot data>c:\data.sql
-- 将data数据库中的stuinfo、stumarks表
F:\wamp\PHPTutorial\MySQL\bin>mysqldump -uroot -proot data stuinfo stumarks>c:\data.sql
-- 导出data数据库，导出的语句中带有创建数据库的语法
F:\wamp\PHPTutorial\MySQL\bin>mysqldump -uroot -proot -B data>c:\data1.sql
5.5.2 数据还原
方法一：MySQL的source指令（需要登录MySQL才能使用）
mysql> source c:/data.sql;
注意：地址分隔符用斜线，不能用反斜线
方法二：通过mysql指令数据还原（不需要登录MySQL）
语法：
mysql 连接数据库 导入的数据库名 < 导入的SQL文件
例题：
F:\wamp\PHPTutorial\MySQL\bin>mysql -uroot -proot data1 < c:\data.sql
5.6 作业：
1、通过循环的方式获取表中的所有记录
2、通过异步实现增、删、改
异步添加
add.html
<body>
<script src='./js/jquery-3.3.1.min.js'></script>
<script>
$(document).ready(function() {
$(':button:first').click(function(){
var title=$('#title').val();
var content=$('#content').val();
$.post('./add.php',{'title':title,'content':content},function(data){
if(data){
alert('添加成功');
location.href='list.php';
}else{
alert('添加失败');
}
})
})
});
</script>
<!--1、创建表单-->
<form method="post" action="">
标题： <input type="text" id="title"> <br /> <br />
内容: <textarea id="content" rows="5" cols="30"></textarea> <br /> <br />
<input type="button" name="button" value="提交">
</form>
</body>
add.php
<?php
require './inc/conn.php';
$title=$_POST['title'];
$content=$_POST['content'];
$time=time();
$sql="insert into news values (null,'$title','$content',$time)";
echo mysqli_query($link,$sql)?1:0;
异步删除
list.php
-- button按钮
<td><input type="button" value="删除" op='del' newsid=<?php echo $rows['id']?>></td>
<script src='./js/jquery-3.3.1.min.js'></script>
<script>
$(document).ready(function() {
$(':button').click(function(){
var tr=$(this).parents('tr'); //按钮所在的行
if($(this).attr('op')=='del'){
var id=$(this).attr('newsid'); //获取新闻的id
$.post('./del.php',{'id':id},function(data){
if(data==1)
tr.remove();
else
alert('删除失败');
})
}
})
});
</script>
del.php
<?php
//1、连接数据库
require './inc/conn.php';
//2、拼接SQL语句
$sql="delete from news where id={$_POST['id']}";
//3、执行SQL语句
echo mysqli_query($link,$sql)?1:0;