MySQL简介

1 数据库介绍

1.1 作用

数据库是用来存放数据的仓库

数据库中存放的是表，表中存放的是数据。

1.2 发展史

萌芽阶段：文件系统

最初始的数据库是用磁盘来存储数据的。文件就是最早的数据库。

第一代数据库：层次模型

优点：这是导航结构

优点：结构清晰，分类查询方便

缺点：有可能造成数据无效

第一代数据库：网状模型

网状模型解决了层次模型的数据不一致的问题，但没有解决导航问题。导航结构在查询中有时候效率低下，比如查询整个公司的四月的营业额。

第二阶段：关系模型

特点：

1、每个表都是独立的

2、通过关系字段将两个表连接起来

3、关系：两个表的公共字段

4、关系型数据库中多表联合查询效率低下。

为了解决关系型数据库多表查询效率的问题，项目中使用了NoSQL(非关系型数据库，Redis、mongodb等等)，在数据库中按照键值对来存储，它是关系型数据库的补充。

1.3 SQL

Structured Query Language（结构化查询语言），是用来操作关系型数据库的一门语言。这是一个关系型数据库的通用操作语言，也成为标准SQL，也叫SQL-92。

数据库的生产厂商为了占有市场份额，都会在标准SQL的基础上扩展一些自己的东西以吸引用户。

关系型数据库	开发公司	使用语言
access	微软公司	SQL
SQL Server	微软公司	T-SQL
Oracle	甲骨文公司	PL/SQL
MySQL	被甲骨文公司收购	MySQL

2 连接服务器

数据库是CS模式的软件，所以要连接数据库必须要有客户端软件。

MySQL数据库默认端口号是3306

2.1 window界面连接服务器

1、Navicat

2、MySQL-Front

2.2 通过web窗体连接

主要有浏览器就可以访问数据库

2.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: ****

2.4 退出登录

 
mysql> exit     -- 方法一
mysql> quit     -- 方法二mysql> \q       -- 方法三

3 数据库基本概念

3.1 数据库、表相关

数据库：数据库中存放的是表，一个数据库中可以存放多个表
表：表是用来存放数据的。
关系：两个表的公共字段
行：也称记录，也称实体
列：也称字段，也称属性

就表结构而言，表分为行和列；就表数据而言，表分为记录和字段；就面向对象而言，一个记录就是一个实体，一个字段就是一个属性。

3.2 数据相关

1、数据冗余：相同的数据存储在不同的地方

冗余只能减少，不能杜绝。

减少冗余的方法是分表

2、数据完整性：正确性+准确性=数据完整性

正确性：数据类型正确准确性：数据范围要准确

3.3 数据库执行过程

4 数据库的操作

4.1 创建数据库

 
create database [if not exists] 数据名 [选项]

 
-- 创建数据库
mysql> create database stu;​-- 创建数据库时，如果数据库已经存在就要报错mysql> create database stu;​-- 在创建数据库时候，判断数据库是否存在，不存在就创建mysql> create database if not exists stu;​-- 特殊字符、关键字做数据库名，使用反引号将数据库名括起来mysql> create database `create`;​mysql> create database `%$`;​-- 创建数据库时指定存储的字符编码mysql> create database emp charset=gbk;# 如果不指定编码，数据库默认使用安装数据库时指定的编码

MySQL数据库的目录

数据库保存的路径在安装MySQL的时候就配置好。也可以在my.ini配置文件中更改数据库的保存地址。(datadir="F:/wamp/PHPTutorial/MySQL/data/")一个数据库就对应一个文件夹，在文件夹中有一个db.opt文件。在此文件中设置数据库的字符集和校对集.

1、如果创建的数据库已存在，就会报错。

2、如果数据库名是关键字和特殊字符要报错。

3、创建数据库的时候可以指定字符编码

4.2 显示所有数据库

show databases

数据库安装后，只带下面四个数据库

 
mysql> show databases;
+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || test               |+--------------------+4 rows in set (0.00 sec)

4.3 删除数据库

 
drop database [if exists] 数据库名

 
mysql> drop database `create`;
mysql> drop database `%$`;​-- 判断数据库是否存在，如果存在就删除mysql> drop database if exists stu;

如果删除的数据库不存在，会报错,

删除之前判断一下，如果存在就删除

4.4 显示创建数据库的语句

 
show create database 数据库名

 
mysql> show create database emp;

4.5 修改数据库

只能修改数据库选项，数据库的选项只有字符编码

 
alter database 数据库名 charset=字符编码
​mysql> alter database emp charset=utf8;

1、修改数据库只能修改数据库的字符编码

2、在MySQL中utf字符编码之间没有横杆 utf8

4.6 选择数据库

 
use 数据库名
    mysql> use emp;

5 表的操作

*表示所有字段

 
mysql> create database data;
Query OK, 1 row affected (0.00 sec)​mysql> use data;Database changed

5.1 创建表

 
create table [if not exists] `表名`(
    `字段名` 数据类型 [null|not null] [default] [auto_increment] [primary key] [comment],    `字段名 数据类型 …)[engine=存储引擎] [charset=字符编码]​null|not null     是否为空default:          默认值auto_increment    自动增长,默认从1开始，每次递增1primary key       主键，主键的值不能重复，不能为空,每个表必须只能有一个主键comment:          备注engine            引擎决定了数据的存储和查找   myisam、innodb​表名和字段名如果用了关键字，要用反引号引起来。

 
 -- 设置客户端和服务器通讯的编码
mysql> set names gbk;  ​-- 创建简单的表mysql> create table stu1(    -> id int auto_increment primary key,    -> name varchar(20) not null    -> )engine=innodb charset=gbk;​-- 创建复杂的表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;

1、如果不指定引擎，默认是innodb

2、如果不指定字符编码，默认和数据库编码一致

3、varchar(20) 表示长度是20个字符

数据表的文件

一个数据库对应一个文件夹一个表对应一个或多个文件引擎是myisam，一个表对应三个文件

 
.frm    ：存储的是表结构
.myd    :存储的是表数据.myi    :存储的表数据的索引

引擎是innodb,一个表对应一个表结构文件，innodb的都有表的数据都保存在ibdata1文件中，如果数据量很大，会自动的创建ibdata2，ibdata3...

innodb和myisam的区别

推荐使用innodb。

myisam

1、查询速度快

2、容易产生碎片

3、不能约束数据

innodb

1、以前没有myisam查询速度快，现在已经提速了

2、不产生碎片

3、可以约束数据

5.2 显示创建表的语句

 
show create table;   -- 结果横着排列
show create table \G  -- 将结果竖着排列

5.3 查看表结构

desc[ribe] 表名

5.4 复制表

语法一：create table 新表 select 字段 from 旧表

特点：不能复制父表的键，能够复制父表的数据

语法二：create table 新表 like 旧表

特点：只能复制表结构，不能复制表数据

5.5 删除表

 
drop table [if exists] 表1，表2,…

 
-- 删除表
mysql> drop table stu4;​-- 如果表存在就删除mysql> drop table if exists stu4;​-- 一次删除多个表mysql> drop table stu2,stu3;

5.6 修改表

语法：alter table 表名

创建初始表

 
mysql> create table stu(
    -> id int,    -> name varchar(20)    -> );

1、添加字段：alter table 表名add [column] 字段名数据类型 [位置]

 
mysql> alter table stu add `add` varchar(20);
-- 默认添加字段放在最后​mysql> alter table stu add sex char(1) after name; -- 在name之后添加sex字段​mysql> alter table stu add age int first;  -- age放在最前面​

2、删除字段：alter table 表 drop [column] 字段名

 
mysql> alter table stu drop age;   -- 删除age字段

3、修改字段(改名)：alter table 表 change [column] 原字段名新字段名数据类型 …

 
-- 将name字段更改为stuname varchar(10)
mysql> alter table stu change name stuname varchar(10);

4、修改字段（不改名）:alter table 表 modify 字段名字段属性…

 
-- 将sex数据类型更改为varchar(20)
mysql> alter table stu  modify sex varchar(20);​-- 将add字段更改为varchar(20) 默认值是‘地址不详’mysql> alter table stu modify `add` varchar(20) default '地址不详';

5、修改引擎：alter table 表名 engine=引擎名

 
mysql> alter table stu engine=myisam;

6、修改表名:alter table 表名 rename to 新表名

 
-- 将stu表名改成student
mysql> alter table stu rename to student;

7、将表移动到其他数据库

 
-- 将当前数据库中的student表移动到php74数据库中改名为stu
mysql> alter table student rename to php74.stu;

6 数据操作

6.1 插入数据

语法：insert into 表名 (字段名, 字段名,…) values (值1, 值1,…)

1、插入所有字段

 
-- 插入所有字段
mysql> insert into stu (id,stuname,sex,`add`) values (1,'tom','男','北京');​-- 插入部分字段mysql> insert into stu(id,stuname) values (2,'berry');​-- 插入的字段和表的字段可以顺序不一致。但是插入字段名和插入的值一定要一一对应mysql> insert into stu(sex,`add`,id,stuname) values ('女','上海',3,'ketty');​-- 插入字段名可以省略mysql> insert into stu values(4,'rose','女','重庆');

1、插入字段名的顺序和数据表中字段名的顺序可以不一致 2、插入值的个数、顺序必须和插入字段名的个数、顺序要一致。 3、如果插入的值的顺序和个数与表字段的顺序个数一致，插入字段可以省略。

2、插入默认值和空值

 
mysql> insert into stu values (5,'jake',null,default);

default关键字用来插入默认值，null用来插入空值.

3、插入多条数据

 
mysql> insert into stu values (6,'李白','男','四川'),(7,'杜甫','男','湖北');

6.2 更新数据

 
update 表名 set 字段=值 [where 条件]

 
-- 将berry性别改为女
mysql> update stu set sex='女' where stuname='berry';​-- 将编号是1号的学生性别改成女，地址改为上海。mysql> update stu set sex='女',`add`='上海' where id=1;

6.3 删除数据

 
delete from 表名 [where 条件]

 
-- 删除1号学生
mysql> delete from stu where id=1;​-- 删除名字是berry的学生mysql> delete from stu where stuname='berry';​-- 删除所有数据mysql> delete from stu;

elete from 表和truncate table 表区别？

1、delete from 表：遍历表记录，一条一条的删除

2、truncate table：将原表销毁，再创建一个同结构的新表。就清空表而言，这种方法效率高。

6.4 查询数据

select 列名 from 表名

 
-- 查询id字段的值
mysql> select id from stu;​-- 查询id,stuname字段的值mysql> select id,stuname from stu;、​-- 查询所有字段的值mysql> select * from stu;

6.5 数据传输时使用字符集

发现：在插入数据的时候，如果有中文会报错（或者中文无法插入）

1、查看客户端发送的编码

2、查看服务器接受，返回的编码

更改接受客户端指令的编码

mysql> set character_set_client=gbk;

原因：返回编码是utf8，客户端是gbk;

测试：成功

可以通过set names一次性设置

7 校对集

1、概念：在某种字符集下，字符之间的比较关系，比如a和B的大小关系，如果区分大小写a>B，如果不区分大小写则a<B。比如赵钱孙李大小关系，不同的标准关系不一样

2、校对集依赖与字符集，不同的字符集的的比较规则不一样，如果字符集更改，校对集也重新定义。

3、不同的校对集对同一字符序列比较的结果是不一致的。

4、可以在定义字符集的同时定义校对集、语法：

 collate = 校对集

定义两个表，相同字符集不同校对集

 
mysql> create table stu1(
    -> name char(1)    -> )charset=utf8 collate=utf8_general_ci;​mysql> create table stu2(    -> name char(1)    -> )charset=utf8 collate=utf8_bin;​mysql> insert into stu1 values ('a'),('B');​mysql> insert into stu2 values ('a'),('B');

两个表的数据都是有小到大排序

 
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：不区分大小写

8 数据类型

MySQL中的数据类型是强类型

8.1 数值型

8.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)

8.1.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)   -- 双精度数    -> );​mysql> insert into stu5 values (3.1415,12.96);​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位    -> );​mysql> insert into stu6 values (5E2),(6E-2); # 插入科学计数法​mysql> select * from stu6;+------+| num  |+------+|  500 || 0.06 |+------+2 rows in set (0.00 sec)

浮点数精度会丢失

 
mysql> insert into stu6 values(99.999999999);
​mysql> select * from stu6;+------+| num  |+------+|  100 |

8.1.3 小数(定点数)

将整数部分和小数部分分开存储

decimal(M,D)

 
mysql> create table stu8(
    -> num decimal(20,9)   # 存放定点数    -> );​mysql> insert into stu8 values(12.999999999);​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填充。

8.2 字符型

在数据库中没有字符串概念，只有字符，所以数据库中只能用单引号

数据类型	描述
char	定长字符，最大可以到255
varchar	可变长度字符，最大可以到65535
tinytext	2^8^–1 =255
text	2^16^–1 =65535
mediumtext	2^24^–1
longtext	2^32^–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

8.3 枚举(enum)

从集合中选择一个值作为数据（单选）

 
mysql> create table stu12(
    -> name varchar(20),    -> sex enum('男','女','保密') # 枚举    -> );​-- 插入的枚举值只能是枚举中提供的选项mysql> insert into stu12 values ('tom','男');-- 报错，只能插入男、女、保密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);   -- 插入数字
​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）、运行速度快（整形比字符串运行速度快）

8.4 集合(set)

从集合中选择一些值作为数据（多选）

 
mysql> create table stu13(
    -> name varchar(20),    -> hobby set('爬山','读书','游泳','烫头')  -- 集合    -> );​mysql> insert into stu13 values ('tom','爬山');​mysql> insert into stu13 values ('Berry','爬山,游泳');​mysql> insert into stu13 values ('Berry','游泳,爬山');   -- 插入的顺序不一样，但显示的顺序一样​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.5 日期时间型

数据类型	描述
datetime	日期时间，占用8个字节
date	日期占用3个字节
time	时间占用3个字节
year	年份，占用1个字节
timestamp	时间戳，占用4个字节

8.5.1 datetime和date

datetime格式：年-月-日小时:分钟:秒。支持的范围是'1000-01-01 00:00:00'到'9999-12-31 23:59:59'。

 
mysql> create table stu14(
    -> t1 datetime,    -- 日期时间    -> t2 date              -- 日期    -> );    -- 插入测试数据mysql> insert into stu14 values ('2019-01-15 12:12:12','2019-01-15 12:12:12');​-- 查询mysql> select * from stu14;+---------------------+------------+| t1                  | t2         |+---------------------+------------+| 2019-01-15 12:12:12 | 2019-01-15 |+---------------------+------------+1 row in set (0.00 sec)

8.5.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    -> );​mysql> insert into stu15 values ('2038-01-19 11:14:07');

8.5.3 year

只能表示1901~2155之间的年份，因为只占用1个字节，只能表示255个数

 
mysql> create table stu16(    
    -> y1 year        -> );   mysql> insert into stu16 values (2155);

8.5.4 time

可以表示时间，也可以表示时间间隔。范围是：-838:59:59~838:59:59

 
mysql> create table stu17(
    -> t1 time    -> );​mysql> insert into stu17 values ('12:12:12');​mysql> insert into stu17 values ('212:12:12');​mysql> insert into stu17 values ('-212:12:12');​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秒​mysql> select * from stu17;+------------+| t1         |+------------+| 12:12:12   || 212:12:12  || -212:12:12 || 250:25:25  |+------------+4 rows in set (0.00 sec)

8.6 Boolean

MySQL不支持布尔型，true和false在数据库中对应的是1和0.

boolean型在MySQL中对应的是tinyint。

 
mysql> create table stu18(
    -> flag boolean    -> );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);mysql> select * from stu18;+------+| flag |+------+|    1 ||    0 |+------+2 rows in set (0.00 sec

9 列属性

9.1 是否为空（null|not null）

null表示字段值可以为nullnot null字段值不能为空

9.2 默认值（default）

如果一个字段没有插入值，可以默认插入一个指定的值

default关键字用来插入默认值

 
mysql> create table stu19(
    -> name varchar(20) not null default '姓名不详',    -> addr varchar(50) not null default '地址不详'    -> );​mysql> insert into stu19(name) values ('tom');​mysql> insert into stu19 values (default,default);​mysql> select * from stu19;+----------+----------+| name     | addr     |+----------+----------+| tom      | 地址不详        || 姓名不详         | 地址不详        |+----------+----------+2 rows in set (0.00 sec)

9.3 自动增长（auto_increment）

字段值从1开始，每次递增1，自动增长的值就不会有重复，适合用来生成唯一的id。在MySQL中只要是自动增长列必须是主键

9.4 主键（primary key）

主键概念：唯一标识表中的记录的一个或一组列称为主键。

特点

1、不能重复、不能为空 2、一个表只能有一个主键。

作用

1、保证数据完整性 2、加快查询速度

选择主键的原则

最少性：尽量选择单个键作为主键稳定性：尽量选择数值更新少的列作为主键

 
-- 创建主键方法一
  mysql> create table stu20(          -> id int auto_increment primary key,          -> name varchar(20)          -> )-- 创建主键方法二  mysql> create table stu21(          -> id int auto_increment,          -> name varchar(20),          -> primary key(id)         -> )

组合键

 
mysql> create table stu22(
    -> classname varchar(20),    -> stuname varchar(20),    -> primary key(classname,stuname)  -- 创建组合键    -> );

通过更改表添加主键

 
mysql> create table stu23(
    -> id int,    -> name varchar(20)    -> );​-- 添加主键mysql> alter table stu23 add primary key(id);

删除主键

 
mysql> alter table stu23 drop primary key;

插入数据

 
mysql> create table stu25(
    -> id tinyint unsigned auto_increment primary key,    -> name varchar(20)    -> );​-- 插入数据mysql> insert into stu25 values (3,'tom');   -- 可以直接插入数字​-- 自动增长列可以插入null,让列的值自动递增mysql> insert into stu25 values (null,'berry');

1、只要是auto_increment必须是主键，但是主键不一定是auto_increment

2、主键特点是不能重复不能为空

3、一个表只能有一个主键，但是一个主键可以有多个字段组成

4、自动增长列通过插入null值让其递增

5、自动增长列的数据被删除，默认不再重复使用。truncate table删除数据后，再次插入从1开始

9.5 唯一键（unique）

键
主键	1、不能重复，不能为空<br />2、一个表只能有一个主键
唯一键	1、不能重刻，可以为空<br />2、一个表可以有多个唯一键

 
-- 创建表的时候创建唯一键 
mysql> create table stu26(    -> id int auto_increment primary key,    -> name varchar(20) unique    -- 唯一键    -> );​-- 方法二 mysql> create table stu27(    -> id int primary key,    -> name varchar(20),    -> unique(name)    -> );​unique 或 unique key 是一样的

通过修改表添加唯一键

 
-- 将name设为唯一键
mysql> alter table stu28 add unique(name);  ​-- 将name,addr设为唯一键mysql> alter table stu28 add unique(name),add unique(addr);​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;

9.6 备注（comment）

说明性文本

备注属于SQL代码的一部分

 
mysql> create table stu29(
    -> id int primary key comment '学号',    -> name varchar(20) not null comment '姓名'    -> );

9.7 SQL注释

-- 单行注释

# 单行注释

/* */ 多行注释

10 数据完整性

10.1 数据完整性包括

1、实体完整性

1、主键约束
2、唯一约束
3、标识列

2、域完整性

1、数据类型约束
2、非空约束
3、默认值约束

3、引用完整性

外键约束

4、自定义完整性

1、存储过程
2、触发器

10.2 主表和从表

主表中没有的记录，从表不允许插入
从表中有的记录，主表中不允许删除
删除主表前，先删子表

10.3 外键（foreign key）

外键：从表中的公共字段

1、只有innodb才能支持外键2、公共字段的名字可以不一样，但是数据类型要一样

 
-- 创建表的时候添加外键
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`;

10.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;

置空、级联操作中外键不能是从表的主键

11 实体之间的关系

11.1 一对多（1：N)

主表中的一条记录对应从表中的多条记录

实现一对多的方式：主键和非主键建关系

多对一（N：1）

多对一就是一对多

11.2 一对一（1:1）

主键和主键建关系

一对一两个表完全可以用一个表实现，为什么还要分成两个表？

在字段数量很多情况下，数据量也就很大，每次查询都需要检索大量数据，这样效率低下。我们可以将所有字段分成两个部分，“常用字段”和“不常用字段”，这样对大部分查询者来说效率提高了。【表的垂直分割】

11.3 多对多（N：M）

主表中的一条记录对应从表中的多条记录，从表中的一条记录，对应主表中的多条记录

如何实现多对多：利用第三张关系表

12 数据库设计

12.1 步骤

收集信息：与该系统有关人员进行交流、坐谈，充分理解数据库需要完成的任务
标识对象（实体－Entity）：标识数据库要管理的关键对象或实体
标识每个实体的属性（Attribute）
标识对象之间的关系（Relationship）
将模型转换成数据库
规范化

12.2 绘制E-R图

E-R（Entity－Relationship）实体关系图）

完整的E-R图

12.3 将E-R图转成表

实体转成表，属性转成字段
如果没有合适的字段做主键，给表添加一个自动增长列做主键。

13 数据规范化

13.1 第一范式

第一范式：确保每列原则性

目标是确保每列的原子性，一个字段表示一个含义

13.2 第二范式

第二范式：非键字段必须依赖于键字段

在满足第一范式的前提下，要求每个表只描述一件事情

13.3 第三范式

第三范式：消除传递依赖

在满足第二范式的前提下，除了主键以外的其他列消除传递依赖。

13.4 反3NF

范式越高，数据冗余越少，但是效率有时就越地下，为了提高运行效率，可以适当让数据冗余。

规范化和性能的关系：性能比规范化更重要

14 查询语句

 
语法：select [选项] 列名 [from 表名] [where 条件]  [group by 分组] [order by 排序][having 条件] [limit 限制]

14.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 |

14.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)

14.3 dual表

dual表是一个伪表。在有些特定情况下，没有具体的表的参与，但是为了保证select语句的完整又必须要一个表名，这时候就使用伪表。

 
mysql> select 10*10 as 结果 from dual;
+------+| 结果     |+------+|  100 |+------+1 row in set (0.00 sec)

14.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 or math<60;-- 查询不是北京和上海的学生mysql> select * from stu where stuaddress not in ('北京','上海');-- 年龄不在20~25之间mysql> select * from stu where stuage not between 20 and 25;-- 查找需要补考的学生mysql> select * from stu where ch<60 or math<60 or ch is null or math is null;​

14.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 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()将同一组的数据连接起来

14.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;

14.7 having条件

having：是在结果集上进行条件筛选

 
-- 查询女生
mysql> select * from stu where stusex='女';​-- 查询女生mysql> select * from stu having stusex='女';​-- 查询女生姓名mysql> select stuname from stu where stusex='女';​-- 使用having报错，因为结果集中没有stusex字段mysql> select stuname from stu having stusex='女';ERROR 1054 (42S22): Unknown column 'stusex' in 'having clause'

having和where的区别：

where是对原始数据进行筛选，having是对记录集进行筛选。

14.8 limit

limit [起始位置]，显示长度

 
-- 从第0个位置开始取，取3条记录
mysql> select * from stu limit 0,3;​-- 从第2个位置开始取，取3条记录mysql> select * from stu limit 2,3;

起始位置可以省略，默认是从0开始

 
mysql> select * from stu limit 3;

limit在update和delete语句中也是可以使用的。

 
--  前3名语文成绩加1分
mysql> update stu set ch=ch+1 order by ch+math desc limit 3;​-- 前3名删除mysql> delete from stu order by ch+math desc limit 3;

14.9 查询语句中的选项

查询语句中的选项有两个：

1、all：显示所有数据【默认】

2、distinct：去除结果集中重复的数据

 
mysql> select all stuaddress from stu;
​-- 去除重复的项mysql> select distinct stuaddress from stu;

15 聚合函数 & 模糊查询 & union（联合）

15.1 聚合函数

sum() 求和
avg() 求平均值
max() 求最大值
min() 求最小值
count() 求记录数

15.2 模糊查询

通配符

_ [下划线] 表示任意一个字符
% 表示任意字符

模糊查询（like）

模糊查询的条件不能用'='，要使用like。

 
mysql> select * from stu where stuname like 'T_m';
-- 查询姓张的学生mysql> select * from stu where stuname like '张%';

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

union的使用

作用：将多个select语句结果集纵向联合起来

union可以将一个复杂的条件转成两个简单的条件

 
语法：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='女');-- 方法二：unionmysql> select * from stu where stuaddress='上海' and stusex='男' union select * from stu where stuaddress='北京' and stusex='女';

union的选项有两个

1、all：显示所有数据

2、distinct：去除重复的数据【默认】

 
mysql> select stuname from stu union all select name from emp;

union的注意事项

1、 union两边的select语句的字段个数必须一致

2、 union两边的select语句的字段名可以不一致，最终按第一个select语句的字段名。

3、 union两边的select语句中的数据类型可以不一致。

16多表查询

16.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;​-- 相同的字段只显示一次mysql> select s.stuno,stuname,stusex,writtenexam,labexam from stuinfo s inner join stumarks m on s.stuno=m.stuno;​-- 使用wheremysql> select * from stuinfo,stumarks where stuinfo.stuno=stumarks.stuno;

 
-- 1、内连接中inner可以省略
select * from 表1  join 表2 on 表1.公共字段=表2.公共字段​mysql> select * from stuinfo join stumarks on stuinfo.stuno=stumarks.stuno;

 
-- 如何实现三表查询
select * from 表1 inner join 表2 on 表1.公共字段=表2.公共字段 inner join 表3 on 表2.公共字段=表3.公共字段-- 表连接越多，效率越低

16.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;

 
select * from 表1 left join 表2 on 表1.公共字段=表2.公共字段和select * from 表2 left join 表1 on 表1.公共字段=表2.公共字段一样吗？
​答：不一样，第一个SQL以表1为准，第二个SQL以表2为准。

右外连接

以右边的表为准，左边如果没有对应的记录用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;

 
select * from 表1 left join 表2 on 表1.公共字段=表2.公共字段
和select * from 表2 right join 表1 on 表1.公共字段=表2.公共字段   一样吗？​答：一样

16.3 交叉连接

返回笛卡尔积

 
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;

16.4 自然连接

自动判断条件连接，判断的条件是依据同名字段

1、自然内连接（natural join）

 
mysql> select * from stuinfo natural join stumarks;

2、自然左外连接（natural left join）

 
mysql> select * from stuinfo natural left join stumarks;

3、自然右外连接（natural right join）

 
mysql> select * from stuinfo natural right join stumarks;

表连接是通过同名字段来连接的

如果没有同名字段就返回笛卡尔积

同名的连接字段只显示一个，并且将该字段放在最前面

16.5 using

using用来指定连接字段

 
mysql> select * from stuinfo inner join stumarks using(stuno);

using的结果也会对公共字段进行优化，优化的规则和自然连接是一样的；

17 子查询

外面的查询称为父查询

子查询为父查询提供查询条件

 
select * from 表1 where （子查询）

17.1 标量子查询

子查询返回的值是一个

 
-- 查找笔试成绩是80的学生
mysql> select * from stuinfo where stuno=(select stuno from stumarks where writtenexam=80);-- 查找最高分的学生-- 方法一mysql> select * from stuinfo where stuno=(select stuno from stumarks order by writtenexam desc limit 1);-- 方法二：mysql> select * from stuinfo where stuno=(select stuno from stumarks where writtenexam=(select max(writtenexam) from stumarks))

17.2 列子查询

子查询返回的结果是一列

如果子查询的结果返回多条记录，不能使用等于，用in或not in

 
-- 查找及格的同学
mysql> select * from stuinfo where stuno in (select stuno from stumarks where writtenexam>=60);-- 查询不及格的同学mysql> select * from stuinfo where stuno in (select stuno from stumarks where writtenexam<60);-- 查询需要补考的学生mysql> select * from stuinfo where stuno not in (select stuno from stumarks where writtenexam>=60);

17.3 行子查询

子查询返回的结果是多个字段组成

 
-- 查找语文成绩最高的男生和女生
mysql> select * from stu where(stusex,ch) in (select stusex,max(ch) from stu group by stusex);

17.4 表子查询

将子查询的结果作为表

 
-- 查找语文成绩最高的男生和女生
mysql> select * from (select * from stu order by ch desc) t group by stusex;

from后面跟的是数据源，如果将子查询当成表来看，必须给结果集取别名。

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

18 视图

18.1 概述

1、视图是一张虚拟表，它表示一张表的部分数据或多张表的综合数据，其结构和数据是建立在对表的查询基础上

2、视图中并不存放数据，而是存放在视图所引用的原始表（基表）中

3、同一张原始表，根据不同用户的不同需求，可以创建不同的视图

18.2 作用

1、筛选表中的行

2、防止未经许可的用户访问敏感数据

3、隐藏数据表的结构

4、降低数据表的复杂程度

18.3 创建视图

 
-- 创建视图
create view 视图名as     select 语句；-- 查询视图select 列名 from 视图

 
-- 创建视图
mysql> create view view1    -> as    -> select * from stu where ch>=60 and math>=60;​-- 查询视图mysql> select * from view1;​-- 视图可以使得降低SQL语句的复杂度mysql> create view view2    -> as    -> select stuno,stusex,writtenexam,labexam from stuinfo natural join stumarks;

18.4 修改视图

 
alter view 视图名
as    select 语句

 
mysql> alter view view2    
    -> as        -> select stuname from stuinfo;

18.5 删除视图

 
drop view [if exists ] 视图1，视图,...

mysql> drop view view2;

18.6 查看视图信息

 
-- 方法一；
mysql> show tables;  -- 显示所有的表和视图-- 方法二：精确查找视图（视图信息存储在information_schema下的views表中）mysql> select table_name from information_schema.views;-- 方法三：通过表的comment属性查询视图mysql> show table status\G;     -- 查询所有表和视图的详细状态信息mysql> show table status where comment='view'\G  -- 只查找视图信息

查询视图结构

mysql> desc view1;

查询创建视图的语法

 
mysql> show create view view1\G

18.7 视图算法

找出语文成绩最高的男生和女生

 
方法一:
mysql> select * from (select * from stu order by ch desc) t group by stusex;方法二：mysql> create view view3    -> as    -> select * from stu order by ch desc;    方法一和方法二的结果不一样，这是因为视图的算法造成的。

视图的算法有：

1、merge：合并算法（将视图语句和外层语句合并后再执行）

2、temptable:临时表算法（将视图作为一个临时表来执行）

3、undefined：未定义算法（用哪种算法有MySQL决定，这是默认算法，视图一般会选merge算法）

 
-- 创建视图，指定算法为临时表算法  (和子查询结果一致。)
mysql> create or replace algorithm=temptable view view3    -> as    -> select * from stu order by ch desc;

19 事务

19.1 概述

事务(TRANSACTION)是一个整体，要么一起执行，要么一起不执行

19.2 事务特性

事务必须具备以下四个属性，简称ACID 属性：

原子性（Atomicity）：事务是一个完整的操作。事务的各步操作是不可分的（原子的）；要么都执行，要么都不执行

一致性（Consistency）：当事务完成时，数据必须处于一致状态

隔离性（Isolation）：对数据进行修改的所有并发事务是彼此隔离的。

永久性（Durability）：事务完成后，它对数据库的修改被永久保持。

19.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;​mysql> insert into bank values ('1001',1000),('1002',1);​-- 开启事务mysql> begin;​mysql> delimiter //   -- 更改定界符mysql> update bank set money=money-100 where card='1001';    -> update bank set money=money+100 where card='1002' //​-- 回滚事务mysql> rollback //-------------------------------------------------------------------- 开启事务mysql> start transaction  //​mysql> update bank set money=money-100 where card='1001';    -> update bank set money=money+100 where card='1002' //​-- 提交事务mysql> commit //

设置事务的回滚点

 
-- 开启事务
mysql> begin  //mysql> insert into bank values ('1003',500) //​-- 记录事务的回滚点mysql> savepoint a1  //mysql> insert into bank values ('1004',200) //​-- 回滚到回滚点mysql> rollback  to a1  //

每一个SQL语句都是一个独立的事务

1、事务是事务开启的时候开始

2、提交事务、回滚事务后事务都结束

3、只有innodb支持事务

4、一个SQL语句就是一个独立的事务，开启事务是将多个SQL语句放到一个事务中执行

20 索引

20.1 概述

优点

加快查询速度

缺点：

带索引的表在数据库中需要更多的存储空间
增、删、改命令需要更长的处理时间，因为它们需要对索引进行更新

20.2 创建索引的指导原则

适合创建索引的列

1、该列用于频繁搜索
2、该列用于对数据进行排序
3、在WHERE子句中出现的列，在join子句中出现的列。

请不要使用下面的列创建索引：

1、列中仅包含几个不同的值。
2、表中仅包含几行。为小型表创建索引可能不太划算，因为MySQL在索引中搜索数据所花的时间比在表中逐行搜索所花的时间更长

20.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)    -> );​mysql> create unique index ix_name on t5(name);​-- 通过更改表的方式创建唯一索引mysql> alter table t5 add unique ix_addr (addr);

普通索引

 
-- 创建表的时候添加普通索引
mysql> create table t6(    ->        id int primary key,    ->        name varchar(20),    ->        index ix_name(name)    -> );​-- 给表添加普通索引mysql> create table t7(    -> name varchar(20),    -> addr varchar(50)    -> );​mysql> create index ix_name on t7(name) ;​-- 通过更改表的方式创建索引mysql> alter table t7 add index ix_addr(addr);

1、创建主键就会创建主键索引

2、创建唯一键就会创建唯一索引

3、创建唯一键的语法

 
--语法一
create unique [index] 索引名 on 表名（字段名）-- 方法二alter table 表名 add uniqe [index] 索引名(字段名)

4、创建普通索引

 
-- 语法一
create index 索引名 on 表名（字段名）-- 语法二alter table 表名 add index 索引名(字段名)

5、索引创建后，数据库根据查询语句自动选择索引

20.4 删除索引

 
drop index 索引名 on 表名
​mysql> drop index ix_name on t7;

21 函数

21.1 数字类

 
-- 获取随机数
mysql> select rand();-- 随机排序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) '向下取整';​

21.2 字符串类

 
-- 大小写转换
mysql> select ucase('i name is tom') '转成大写',lcase('My Name IS TOM') '转成小写';-- 截取字符串mysql> select left('abcdef',3) '从左边截取',right('abcdef',3) '从右边截取',substring('abcdef',2,3) '字符串';-- 字符串相连mysql> select concat('中国','北京','顺义') '地址';-- coalesce(str1,str2)  :str1有值显示str1,如果str1为空就显示str2-- 将成绩为空的显示为缺考mysql> select stuname,coalesce(writtenexam,'缺考'),coalesce(labexam,'缺考') from stuinfo natural left join stumarks;-- length（）:字节长度，char_length()：字符长度mysql> select length('锄禾日当午') 字节,char_length('锄禾日当午') 字符;

22.3 时间类

 
-- 时间戳
mysql> select unix_timestamp();​-- 格式化时间戳mysql> select from_unixtime(unix_timestamp());​-- 获取当前格式化时间mysql> select now();​-- 获取年，月，日，小时，分钟，秒mysql> select year(now()) 年,month(now()) 月,day(now()) 日,hour(now()) 小时,minute(now()) 分钟,second(now())秒;​-- 星期，本年第几天;mysql> select dayname(now()) 星期,dayofyear(now()) 本年第几天;​-- 日期相减mysql> select datediff(now(),'2010-08-08') 相距天数;

22.4 加密函数

1、md5()

2、sha()

 
mysql> select md5('aa');
​mysql> select sha('aa');

22 预处理

每个代码的段的执行都要经历：词法分析——语法分析——编译——执行

预编译一次，可以多次执行。用来解决一条SQL语句频繁执行的问题。

预处理语句：prepare 预处理名字 from ‘sql语句’
执行预处理：execute 预处理名字 [using 变量]

不带参数的预处理

 
-- 创建预处理
mysql> prepare stmt from 'select * from stuinfo';​-- 执行预处理mysql> execute stmt;

带一个参数的预处理

 
-- 创建带有位置占位符的预处理语句
mysql> prepare stmt from 'select * from stuinfo where stuno=?' ​-- 调用预处理，并传参数mysql> delimiter //mysql> set @id='s25301';    -> execute stmt using @id //

传递多个参数

 
mysql> prepare stmt from 'select * from stuinfo where stuage>? and stusex=?' //
​mysql> set @age=20;    -> set @sex='男';    -> execute stmt using @age,@sex //

1、MySQL中变量以@开头

2、通过set给变量赋值

3、？是位置占位符

23 连接数据库

通过PHP做MySQL的客户端

23.1 开启mysqli扩展

在php.ini中开启mysqli扩展

extension=php_mysqli.dll

开启扩展后重启服务器，就可以使用mysqli_函数了，

23.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

24 操作数据

24.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()：获取插入记录自动增长的IDmysqli_affected_rows()：获取受影响的记录数mysqli_error()：获取执行SQL语句的错误信息mysqli_errno()：获取执行SQL语句的错误码

24.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()：关闭连接

25 新闻模块

25.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');

25.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>

运行结果

![1560494286358](images/1560494286358.png)

25.3 添加新闻

步骤：

1、创建表单

2、连接数据库

3、将新闻数据写入到数据库中

入口(list.php）

 
<a href="./add.php">添加新闻</a>

代码实现

 
<body>
<?phpif(!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>

运行结果

![1560495319869](images/1560495319869.png)

25.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页面

![1560496299270](images/1560496299270.png)

25.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>

26 数据备份与还原

数据库中的数据需要定期备份，数据量小的可以一周备份一次，数据量的可以一天备份一次。

26.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

26.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

发表于 2019-06-23 08:26 花凝冰阅读(403) 评论(1) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部