数据库：放数据的“仓库”

萌芽阶段

• 放在内存里——瞬时：程序结束，上次的数据就没啦

• 放在文件里——持久：进步了！能一直保存了（比如放在.txt文件里），但还是存在问题

  • 不安全
  • 不方便在中间插入、删除数据
  • 但也不是一无是处，可以应用于配置文件：.xml文件、.ini文件等等

数据库的产生

核心：CRUD（最难的是“查找”）

• 层次模型

  • 优点：层次清晰

  • 缺点：（假如一张“专业”表有你，“学校”表也有你）

    • 查询效率低下（不知道查哪张）
    • 数据不完整（这张表改了那张表没改）
  • 致命：重复就GG了

• 网状模型

  • 优点：解决复杂问题，数据也完整了，重复的话也只操作一个文件

  • 缺点：重复文件没法解决特殊性问题（假设计算机一班的人和上mysql课的有一部分人都是）

    • 计算机一班的每人收100元
    • 上mysql课的每人收233元
  • 致命：并未解决导航问题

• 关系型

  • 解决了导航问题：比如每张表都有个学号（公共的编号）
  • 分批管理，各管各的
  • 数据完整，层次清晰
  • 说白了，关系就是共性，为了让每张表都能找到爹
  • **和谁都有关系，但是又互不影响

创建data文件夹

C:\Users\19837>cd C:\Program Files\MySQL\MySQL Server 5.7    

C:\Program Files\MySQL\MySQL Server 5.7>mysqld --initialize-insecure --user=root

C:\Program Files\MySQL\MySQL Server 5.7>

数据库基本操作

CRUD：增删改查（查找是最复杂的）

创建数据库

create database if not exists `student`;

删除数据库

drop database if exists student;

查看创建的数据库

show create database `teacher`;

乱码->字符编码：GBK、UTF-8(在学习中用GBK，企业中UTF-8)

create database if not exists `student` charset=gbk;

修改、更新数据库字符编码

alter database teacher charset=gbk;

表

使用数据库

use frank_school;

查看表

show tables;

创建表

create table student(
    id int,
    name varchar(30),
    age int
    );

创建表（B格）

create table teacher(
    id int auto_increment primary key comment '主键id',
    
    #auto_increment 自动增长
    #primary key 主键，最主要的，用来区分
    #comment 注释
    
    name varchar(30) not null,
    #not null 不能为空
    
    phone varchar(20) comment '电话号码',
    address varchar(100) default '暂时未知' comment '住址'
    #default 默认值，此处为'暂时未知'
    
    #engine:数据库引擎
    )engine=innodb;

查看表的结构

desc student;
desc teacher;

删除表

drop table if exists oooo, stu,jjj;

添加字段

#默认在最后面
alter table student add phone varchar(20);
​
#指定插入在哪，在name后面
alter table student add phone varchar(20) after name;
​
#在最前面
alter table student add phone varchar(20) first;

删除字段

drop table s;
alter table student drop address;in

修改字段

#既可以修改名字，也可以修改类型
alter table student change phone tel_phone int (11);
​
#只改变类型
alter table student modify tel_phone int (13);

数据操作

插入数据

insert into teacher (id, name, phone, address) values(1, 'Frank', '1888888', 'ShangHai');
​
# 这样就必须按照创建表的时候的顺序填写
insert into teacher values(1, 'Frank', '1888888', 'ShangHai');

查看数据

select * from teacher;
# *:全部
# from + 表

删除数据

# 删除id为6的数据
delete from teacher where id=6;
​
# 删除大于三十岁的数据
delete from student where age>30;
​
# 清除表的所有数据（不建议:慢）
# 坏处：再创建数据的时候，id等自增数据，会接着被清空之前的id自动增长
delete from teacher;
​
# 建议这种方法清除数据
# 好处：id等自增数据，不会接着被清空之前的id
truncate table student;

更新数据

# 将id=1的name，改为frank
# 注意where后面的，注意不要有重复的id等！！！！
update teacher set name='frank' where id=1;
​
# 也可以改多个数据
update teacher set name='frank',phone='1111' where id=1;
​
# 如果不写where，那么将改变所有的数据
update teacher set name='frank';
​
# SQL注入攻击就是改变where
​
# 多个条件的情况
# 将phone为1111111或者2222的数据修改address的值
update teacher set address='shanghai' where phone='1111111' or phone='2222';

查询表数据

# 查询teacher表中的id跟phone的数据
select id,phone from teacher;
​
# 查询teacher表中所有的数据
select * from teacher;

SQL语句区分

• DDL：data definition language 数据库定义语言（create、alter、drop、show...)
• DML：data manipulation language 数据操纵语言（insert、update、select...)
• DCL： data control language

查找所有字符集设置

show variables like 'character_set_%';

设置客户端字符集

set character_set_client=utf8;

列属性完整性

主键：

• 一个表中只有一个主键，哪怕是复合主键(id,name)也是一个主键
  主键的作用：1.确保属性的完整性
• 2.加快查找数据的速度
• 主键一般要用整数，因为字符串不好处理
• auto_increment自增属性一定是主键
• auto_increment的属性中，删除id=3的数据后，就不能再插入一个id为3的数据了
• 添加主键：

alter table t_8 add primary key (id);

删除主键：

alter table t_8 drop primary key;

唯一键

• 可以多个唯一键
• 作用：保证数据不重复
  unique唯一键添加：

create table t_9(id int primary key,phone varchar(20) unique);

两个unique唯一键：

create table t_11(id int,name varchar(20) unique,phone varchar(20) unique);

• 删除唯一键：

alter table t_11 drop index phone;
// 执行后，phone不是唯一键了

主键与唯一键的区别

• 主键不能重复，不能空
• 唯一键也不可以重复，可以为空
• 主键可以在很多个表中使用，唯一键只能在一张表中使用（格局小了）
• sql内注释和代码注释：

create table t_12(
id int(20),
name varchar(20) comment '姓名'
);
// comment注释
show create table t_12;
// 会显示怎么创建的，包括注释都可以看到

数据库完整性

• 要有一个主键，自动增长；
• 选择合适的数据类型
• 是否为null
• 有些要有default，暂无，例子：有同学考试缺考，就要用default

外键

• 用于连接两个表

create table eatery(
id int primary key,
money decimal(10,4),
stuId int(4),
foreign key (stuId) references stu(stuId)
);
// 从stu表中的stuId创建外键

• 注：外键很少能用到，尤其是并发
• 显示表怎么创建的：

show create table eatery;
​
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table
                                                                                                      |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| eatery | CREATE TABLE `eatery` (
  `id` int(11) NOT NULL,
  `money` decimal(10,4) DEFAULT NULL,
  `stuId` int(4) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `stuId` (`stuId`),
  CONSTRAINT `eatery_ibfk_1` FOREIGN KEY (`stuId`) REFERENCES `stu` (`stuId`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)

• 删除外键

alter table eatery drop foreign key eatery_ibfk_1;

置空&级联

• 置空：删除数据（将数据改值为null，但没彻底删除）
• 级联：更新数据（将数据全部删除，是彻底的删除）

 // 创建主表
 create table stu(
    -> stuId int(4) primary key,
    -> name varchar(20)
    -> );
 // 创建从表
 create table eatery(
    -> id int (4) primary key,
    -> money decimal(10,4),
    -> stuId int(4),
       // 在删除的时候置空，在更新的时候级联操作
    -> foreign key(stuId) references stu(stuId) on delete set null 
       // on delete set null：当删除时，置空
       // on update cascade：当更新时，级联
    -> );
    

数据库设计

概念

• 关系：关系型数据库，有两张表的共有字段去确定数据的完整性
• 行：一条数据，一条数据记录，实体
• 列：一个字段，属性

实体之间的关系

• 一对一，一对多，多对一，多对多

三大范式

• 第一范式：确保每列原子性
• 第二范式：非键字段必须依赖键字段（一个表只能描述一件事情，不能扯淡，学生表中不能有食堂的数据）
• 第三范式：消除传递依赖（可能考虑要干掉多余的字段，例：语数外成绩表没有必要存在总分字段，直接相加就可以了）
• 数据库设计要根据需求而设计，而不是根据行业来设计

单表查询

select

mysql> select 3*4;
+-----+
| 3*4 |
+-----+
|  12 |
+-----+
1 row in set (0.00 sec)
​
// 起“别名”
mysql> select 2*6 as res;
+-----+
| res |
+-----+
|  12 |
+-----+
1 row in set (0.00 sec)

from

mysql> select * from t1;
+------+-------+
| id   | name  |
+------+-------+
|    1 | frank |
|    2 | jerry |
+------+-------+
2 rows in set (0.00 sec)
 
mysql> select * from t2; 
+--------+--------+
| score1 | score2 |
+--------+--------+
|     98 |     99 |
|     90 |     77 |
+--------+--------+
2 rows in set (0.00 sec)
​
mysql> select * from t1,t2;
// 返回笛卡尔积
+------+-------+--------+--------+
| id   | name  | score1 | score2 |
+------+-------+--------+--------+
|    1 | frank |     98 |     99 |
|    2 | jerry |     98 |     99 |
|    1 | frank |     90 |     77 |
|    2 | jerry |     90 |     77 |
+------+-------+--------+--------+
4 rows in set (0.00 sec)

dual

// 其实有个尾表，可以省略
// 了解就行
mysql> select 2*7 as res from dual;
+-----+
| res |
+-----+
|  14 |
+-----+
1 row in set (0.00 sec)

where

mysql> select * from t4;
+------+----------+
| id   | address  |
+------+----------+
|    1 | shanghai |
|    2 | shanghai |
|    3 | beijing  |
|    4 | shenzhen |
+------+----------+
4 rows in set (0.00 sec)
​
// where限定查找某一个范围
mysql> select * from t4 where address = 'shanghai';
+------+----------+
| id   | address  |
+------+----------+
|    1 | shanghai |
|    2 | shanghai |
+------+----------+
2 rows in set (0.00 sec)
​
mysql> select * from t4 where address = 'shenzhen';  
+------+----------+
| id   | address  |
+------+----------+
|    4 | shenzhen |
+------+----------+
1 row in set (0.00 sec)
​
mysql> select * from t4 where address = 'shenzhen ';
+------+----------+
| id   | address  |
+------+----------+
|    4 | shenzhen |
+------+----------+
1 row in set (0.00 sec)

in

// 跟or一样
mysql> select * from t4 where address in('beijing','shanghai');
+------+----------+
| id   | address  |
+------+----------+
|    1 | shanghai |
|    2 | shanghai |
|    3 | beijing  |
+------+----------+
3 rows in set (0.00 sec)

between...and...

mysql> select * from t3 where age between 15 and 19; 
+------+------+
| id   | age  |
+------+------+
|    1 |   18 |
|    2 |   19 |
|    3 |   19 |
|    4 |   16 |
|    5 |   17 |
+------+------+
5 rows in set (0.00 sec)

is null

// 查找为空的
// is not null
mysql> select * from t3 where age is null;
+------+------+
| id   | age  |
+------+------+
|    7 | NULL |
+------+------+
1 row in set (0.00 sec)

聚合函数

mysql> select * from score;            
+------+---------+---------+------+
| id   | chinese | english | math |
+------+---------+---------+------+
|    1 |      98 |      99 |  100 |
|    2 |      90 |      95 |   85 |
+------+---------+---------+------+
2 rows in set (0.00 sec)

// 求和
mysql> select sum(chinese) from score;
+--------------+
| sum(chinese) |
+--------------+
|          188 |
+--------------+
1 row in set (0.00 sec)

// 平均值
mysql> select avg(chinese) from score; 
+--------------+
| avg(chinese) |
+--------------+
|      94.0000 |
+--------------+
1 row in set (0.00 sec)

// 最大值
mysql> select max(chinese) from score; 
+--------------+
| max(chinese) |
+--------------+
|           98 |
+--------------+
1 row in set (0.00 sec)

// 最小值
mysql> select min(chinese) from score; 
+--------------+
| min(chinese) |
+--------------+
|           90 |
+--------------+
1 row in set (0.00 sec)

// 求数量（很坑，不建议用）
mysql> select count(*) from score;     
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

like模糊查询

mysql> select * from student;
+------+--------+--------+------+-----------+
| id   | name   | gender | age  | tel_phone |
+------+--------+--------+------+-----------+
|    1 | 张三   | 0      |   18 | 155555    |
|    2 | 张四   | 1      |   20 | 16541654  |
|    3 | 李四   | 1      |   22 | 1231      |
|    4 | 张某某 | 0      |   15 | 15646     |
+------+--------+--------+------+-----------+
4 rows in set (0.00 sec)

// %代表多个字符
mysql> select * from student where name like '张%';
+------+--------+--------+------+-----------+
| id   | name   | gender | age  | tel_phone |
+------+--------+--------+------+-----------+
|    1 | 张三   | 0      |   18 | 155555    |
|    2 | 张四   | 1      |   20 | 16541654  |
|    4 | 张某某 | 0      |   15 | 15646     |
+------+--------+--------+------+-----------+
3 rows in set (0.00 sec)

// _代表一个字符
mysql> select * from student where name like '张_';
+------+------+--------+------+-----------+
| id   | name | gender | age  | tel_phone |
+------+------+--------+------+-----------+
|    1 | 张三 | 0      |   18 | 155555    |
|    2 | 张四 | 1      |   20 | 16541654  |
+------+------+--------+------+-----------+
2 rows in set (0.00 sec)

order by 排序查询

mysql> select * from score;
+------+---------+---------+------+
| id   | chinese | english | math |
+------+---------+---------+------+
|    1 |      98 |      99 |  100 |
|    2 |      90 |      95 |   85 |
|    3 |      87 |      79 |   99 |
+------+---------+---------+------+
3 rows in set (0.00 sec)

// asc升序
mysql> select * from score order by english asc;
+------+---------+---------+------+
| id   | chinese | english | math |
+------+---------+---------+------+
|    3 |      87 |      79 |   99 |
|    2 |      90 |      95 |   85 |
|    1 |      98 |      99 |  100 |
+------+---------+---------+------+
3 rows in set (0.00 sec)

// desc降序
mysql> select * from score order by english desc; 
+------+---------+---------+------+
| id   | chinese | english | math |
+------+---------+---------+------+
|    1 |      98 |      99 |  100 |
|    2 |      90 |      95 |   85 |
|    3 |      87 |      79 |   99 |
+------+---------+---------+------+
3 rows in set (0.00 sec)

group by 分组查询

mysql> select * from info;
+----+------+--------+---------+
| id | age  | gender | address |
+----+------+--------+---------+
|  1 |   25 | 男     | 上海    |
|  2 |   23 | 女     | 上海    |
|  3 |   24 | 女     | 北京    |
|  4 |   26 | 男     | 北京    |
|  5 |   22 | 男     | 上海    |
+----+------+--------+---------+
5 rows in set (0.00 sec)

// 按照性别分组统计平均年龄
// 1.可以分组2.聚合函数
mysql> select avg(age) as '年龄',gender as '性别' from info group by gender;
+---------+------+
| 年龄    | 性别  |
+---------+------+
| 24.3333 | 男   |
| 23.5000 | 女   |
+---------+------+
2 rows in set (0.00 sec)

group_concat

mysql> select * from student;
+----+------+-------+--------+---------+
| id | age  | name  | gender | phone   |
+----+------+-------+--------+---------+
|  1 |   18 | ????  | 0      | 115555  |
|  2 |   20 | ??    | 1      | 5621654 |
|  3 |   31 | ????? | 1      | 15646   |
|  4 |   32 | ??    | 1      | 4856748 |
+----+------+-------+--------+---------+
4 rows in set (0.00 sec)

// 根据gender将id分组，进行聚合显示
mysql> select group_concat(id),gender from student group by gender;
+------------------+--------+
| group_concat(id) | gender |
+------------------+--------+
| 1                | 0      |
| 2,3,4            | 1      |
+------------------+--------+
2 rows in set (0.01 sec)

having

mysql> desc info;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | NO   | PRI | NULL    |       |
| age     | int(11)      | YES  |     | NULL    |       |
| gender  | varchar(5)   | YES  |     | NULL    |       |
| address | varchar(255) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

// having是在查询之后的数据进行筛选（已经查询之后的）
mysql> SELECT avg(age) AS 'age',address AS 'address' FROM info GROUP BY address HAVING age > 24;
+---------+---------+
| age     | address |
+---------+---------+
| 24.5000 | beijing |
+---------+---------+
1 row in set (0.00 sec)

limit

mysql> select * from info
    -> ;
+----+------+--------+----------+
| id | age  | gender | address  |
+----+------+--------+----------+
|  1 |   25 | 0      | shanghai |
|  2 |   23 | 1      | shanghai |
|  3 |   26 | 1      | beijing  |
|  4 |   23 | 0      | beijing  |
|  5 |   21 | 0      | shanghai |
|  6 |   24 | 1      | shenzhen |
+----+------+--------+----------+
6 rows in set (0.00 sec)

// 从下标为0开始，查两个
mysql> select * from info limit 0,2;
+----+------+--------+----------+
| id | age  | gender | address  |
+----+------+--------+----------+
|  1 |   25 | 0      | shanghai |
|  2 |   23 | 1      | shanghai |
+----+------+--------+----------+
2 rows in set (0.00 sec)

// 降序前三个
mysql> select * from info order by age desc limit 3;
+----+------+--------+----------+
| id | age  | gender | address  |
+----+------+--------+----------+
|  3 |   26 | 1      | beijing  |
|  1 |   25 | 0      | shanghai |
|  6 |   24 | 1      | shenzhen |
+----+------+--------+----------+
3 rows in set (0.00 sec)

distinct

mysql> select distinct address from info;
+----------+
| address  |
+----------+
| shanghai |
| beijing  |
| shenzhen |
+----------+
3 rows in set (0.00 sec)

// 去除重复的，计算个数
mysql> select count(distinct address) from info;
+-------------------------+
| count(distinct address) |
+-------------------------+
|                       3 |
+-------------------------+
1 row in set (0.00 sec)

多表查询

union联合查询

mysql> select * from teacher;
+------+------+--------+----------+
| id   | age  | gender | address  |
+------+------+--------+----------+
|    1 |   25 | 0      | shanghai |
|    2 |   23 | 1      | shanghai |
|    3 |   26 | 1      | beijing  |
|    4 |   23 | 0      | beijing  |
|    5 |   21 | 0      | shanghai |
|    6 |   24 | 1      | shenzhen |
+------+------+--------+----------+
6 rows in set (0.00 sec)

mysql> select * from info;    
+----+------+--------+----------+
| id | age  | gender | address  |
+----+------+--------+----------+
|  1 |   25 | 0      | shanghai |
|  2 |   23 | 1      | shanghai |
|  3 |   26 | 1      | beijing  |
|  4 |   23 | 0      | beijing  |
|  5 |   21 | 0      | shanghai |
|  6 |   24 | 1      | shenzhen |
+----+------+--------+----------+
6 rows in set (0.00 sec)

mysql> select age,gender from info union select `name`,phone from student;
+-------+---------+
| age   | gender  |
+-------+---------+
| 25    | 0       |
| 23    | 1       |
| 26    | 1       |
| 23    | 0       |
| 21    | 0       |
| 24    | 1       |
| ????  | 115555  |
| ??    | 5621654 |
| ????? | 15646   |
| ??    | 4856748 |
+-------+---------+
10 rows in set (0.00 sec)

inner join（内连接）

// 查name和score
mysql> select 
               name,
               score 
       from 
               student 
               inner join score on student.id=score.stuid;
+----------+-------+
| name     | score |
+----------+-------+
| zhangsan | 89.00 |
| ren      | 80.00 |
| lalala   | 70.00 |
+----------+-------+
3 rows in set (0.00 sec)

left join（左连接同理right join）

// 以左表为基准，该方式student为左表
mysql> select
    -> name,
    -> score
    -> from
    -> student left join score
    -> on student.id = score.stuid;
+----------+-------+
| name     | score |
+----------+-------+
| zhangsan | 89.00 |
| ren      | 80.00 |
| lalala   | 70.00 |
| moumou   |  NULL |
+----------+-------+
4 rows in set (0.00 sec)

其他

• cross join（两个表的笛卡尔积）
  natural join：公共的字段，条件是有同名的字段，然后相当于将两个表内、左、右连接
• 若没有公共字段即同名的字段，则返回笛卡尔积
• using：当两个表的字段完全相同，若内连接，则返回空，这就要用using指定字段
• 不推荐using和natural join，一般使用inner join

子查询

基本语句

mysql> select * from score;
+----+-------+-------+
| id | stuid | score |
+----+-------+-------+
|  1 |     1 | 89.00 |
|  2 |     3 | 80.00 |
|  3 |     4 | 70.00 |
+----+-------+-------+
3 rows in set (0.02 sec)

// 多个结果要用in不用=，还有not in
mysql> select * from student where id in (select stuid from score where score >= 75);
+----+------+----------+--------+--------+
| id | age  | name     | gender | phone  |
+----+------+----------+--------+--------+
|  1 |   18 | zhangsan | 0      | 115555 |
|  3 |   31 | ren      | 1      | 15646  |
+----+------+----------+--------+--------+
2 rows in set (0.01 sec)

exists和not exists

mysql> select * from score;
+----+-------+-------+
| id | stuid | score |
+----+-------+-------+
|  1 |     1 | 89.00 |
|  2 |     3 | 80.00 |
|  3 |     4 | 70.00 |
+----+-------+-------+
3 rows in set (0.00 sec)

mysql> select * from student where exists (select stuid from score where score >=85);
+----+------+----------+--------+---------+
| id | age  | name     | gender | phone   |
+----+------+----------+--------+---------+
|  1 |   18 | zhangsan | 0      | 115555  |
|  2 |   20 | moumou   | 1      | 5621654 |
|  3 |   31 | ren      | 1      | 15646   |
|  4 |   32 | lalala   | 1      | 4856748 |
+----+------+----------+--------+---------+
4 rows in set (0.00 sec)

mysql> select * from student where exists (select stuid from score where score >=100);
Empty set (0.00 sec)

视图

作用

• 隐藏敏感的数据
• 降低sql语句的复杂度

创建视图

// 这样就可以只看到student的name和phone，而不用看到其他敏感的数据
create view vw_stu as
select name,phone from student;

// 保存视图之后，就可以直接查视图，而不用再把查询语句写一遍
select * from vw_stu;

显示视图

// 显示视图，所以必须要用前缀区分
mysql> show tables;
+------------------------+
| Tables_in_frank_school |
+------------------------+
| info                   |
| score                  |
| student                |
| teacher                |
| vw_stu                 |
+------------------------+
5 rows in set (0.00 sec)

mysql> desc vw_stu;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name  | varchar(255) | YES  |     | NULL    |       |
| phone | varchar(20)  | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> show create view vw_stu; 
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View   | Create View

                               | character_set_client | collation_connection |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| vw_stu | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vw_stu` AS select `student`.`name` AS `name`,`student`.`phone` AS `phone` from `student` | utf8mb4              | utf8mb4_general_ci 
  |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)

mysql> show table status where comment = 'view' \G 
*************************** 1. row ***************************
           Name: vw_stu
         Engine: NULL
        Version: NULL
     Row_format: NULL
           Rows: NULL
 Avg_row_length: NULL
    Data_length: NULL
Max_data_length: NULL
   Index_length: NULL
      Data_free: NULL
 Auto_increment: NULL
    Create_time: NULL
    Update_time: NULL
     Check_time: NULL
      Collation: NULL
       Checksum: NULL
 Create_options: NULL
        Comment: VIEW
1 row in set (0.00 sec)

更新和删除视图

// 修改视图要查的东西
mysql> alter view vw_stu as select age,name,phone from student;
Query OK, 0 rows affected (0.01 sec)

// 删除
drop view vw_stu;

视图算法temptable和merge

• 将子查询放到视图里的时候要特别注意，要将算法改为temptable

事务

基本术语

• 事务（transaction）指一组 SQL 语句；
• 回退（rollback）指撤销指定 SQL 语句的过程；
• 提交（commit）指将未存储的 SQL 语句结果写入数据库表；
• 保留点（savepoint）指事务处理中设置的临时占位符（placeholder），你可以对它发布
• 回退（与回退整个事务处理不同）。

原理

• MySQL的事务提交默认是隐式提交，每执行一条语句就把这条语句当成一个事务，然后进行提交。当出现start transaction语句时，会关闭隐式提交；当commit或rooback语句执行后，事务会自动关闭，重新回复隐式提交

要么一起执行，要么回滚

transaction

mysql> select * from wallet;
+----+-----------+
| id | balance   |
+----+-----------+
|  1 |    500.87 |
|  2 |    636.57 |
|  3 | 888888.00 |
+----+-----------+
3 rows in set (0.02 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

// 将1转账给2五十块钱
mysql> update wallet set balance=balance-50 where id=1;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update wallet set balance=balance+50 where id=2; 
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;                                         
Query OK, 0 rows affected (0.01 sec)

mysql> select * from wallet;
+----+-----------+
| id | balance   |
+----+-----------+
|  1 |    450.87 |
|  2 |    686.57 |
|  3 | 888888.00 |
+----+-----------+
3 rows in set (0.00 sec)

• 只要commit就不能再rollback了

设置回滚点(rollback to)

• 类似git和虚拟机

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into wallet values(4,1000);
Query OK, 1 row affected (0.00 sec)

mysql> savepoint four;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into wallet values(5,199999); 
Query OK, 1 row affected (0.00 sec)

mysql> savepoint five; 
Query OK, 0 rows affected (0.00 sec)

mysql> select * from wallet;
+----+-----------+
| id | balance   |
+----+-----------+
|  1 |    450.87 |
|  2 |    686.57 |
|  3 | 888888.00 |
|  4 |   1000.00 |
|  5 | 199999.00 |
+----+-----------+
5 rows in set (0.00 sec)

mysql> rollback to four; 
Query OK, 0 rows affected (0.00 sec)

mysql> select * from wallet;
+----+-----------+
| id | balance   |
+----+-----------+
|  1 |    450.87 |
|  2 |    686.57 |
|  3 | 888888.00 |
|  4 |   1000.00 |
+----+-----------+
4 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from wallet;
+----+-----------+
| id | balance   |
+----+-----------+
|  1 |    450.87 |
|  2 |    686.57 |
|  3 | 888888.00 |
|  4 |   1000.00 |
+----+-----------+
4 rows in set (0.00 sec)

ACID：事务的特性

• atomicity：原子性（要么一起执行，要么就不执行）
• consistency：一致性（完成操作的时候所有的数据都应该正常）
• isolation：隔离性
• durability：持久性（一旦commit就不能再改了）

并发一致性问题

在并发环境下，事务的隔离性很难保证，因此会出现很多并发一致性的问题

• 脏读数据

当B修改了数据但没有提交时，A在B回滚之前查询了这个数据，如果B在之后回滚了，那么A读取的数据就是脏数据

• 不可重复读

  • 读取前后数据的内容不一致（某一行）

在一个事务内多次读取同一个数据集合，在这一个事务还没有结束前，另一个事务也访问到了该同一数据集合，并做出了修改，由于第二个事务的修改，第一个事务的两次读取的数据可能不一致

• 幻读

  • 读取前后，数据量不一致（整张表）

解决：并发控制来保证隔离性，并发控制可以通过封锁来实现，但是封锁操作需要用户自己控制，相当复杂，数据库管理系统提供了事务的隔离级别，让用户以一种更轻松的方式处理并发一致性问题

隔离级别

• 未提交（READ UNCOMMITTED）
• 提交读（READ COMMITTED）
• 可重复读（REPEATABLE READ）用的最多
• 可串行化（SERIALIZABLE）
• 隔离级别能解决的并发一致性问题

一些函数

number

• ceil(3.1)：向上取整
• round(3.1)，floor()向下取整
• truncate(3.1415926, 2)：3.14：截取小数位数
• order by rand()：随机排序

string

• lcase(‘FUCK!’)：fuck!：小写
• ucase('fuck!')：FUCK!：大写
• left('FUCK!', 2)：FU
• right('FUCK!', 2)：K!
• substring('FUCK!', 2, 3)：UCK
• concat('FUCK', 'YOU')：FUCKYOU

other

• now()：当前时间
• unix_timestamp()：时间戳
• sha('dasdhdl')：加密

企业规范约束*

表示是否（一定这样写）

• 格式：is_...，例如：is_vip
• 类型：tinyint unsigned
• 长度：1，表示是否
• 不要在数值类型上耗费性能

表明/字段名

• 必须小写字母开头，不能使用数字开头
• 不能出现复数
• 不能出现关键字
• 索引名：pk_xxx
• 唯一键：uk_xxx
• 唯一键索引：idx_xxx
• 小数：decimal和Number

• 字符串很小：char，不要用varchar

  • varchar不要定义5000以上
  • 如果非要5000以上，可以用text

必须要有的字段

• id：主键， bigint unsigned，单表必须自增1
• create_time和update_time：必须为datatime

字段允许适当的冗余

• 需要满足

  • 不是频繁修改的字段
  • 不是唯一索引的字段
  • 不是varchar的超长字段，更不能是text字段

不要使用count(xxx)取代count(*)

• count(*)会统计null的行，而count(xxx)
• 不要用=判断null，要用isnull()函数判断

禁止使用外键和级联

• 大项目中，性能消耗过大

禁止使用存储过程

更新数据的时候

• 先查询出来，在修改

不要使用in