MySQL

萌芽阶段

放在内存里——瞬时：程序结束，上次的数据就没啦
放在文件里——持久：进步了！能一直保存了（比如放在.txt文件里），但还是存在问题
- 不安全
- 不方便在中间插入、删除数据
- 但也不是一无是处，可以应用于配置文件：.xml文件、.ini文件等等

数据库的产生

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

层次模型
- 优点：层次清晰
- 缺点：（假如一张“专业”表有你，“学校”表也有你）
  - 查询效率低下（不知道查哪张）
  - 数据不完整（这张表改了那张表没改）
- 致命：重复就GG了
网状模型
- 优点：解决复杂问题，数据也完整了，重复的话也只操作一个文件
- 缺点：重复文件没法解决特殊性问题（假设计算机一班的人和上mysql课的有一部分人都是）
  - 计算机一班的每人收100元
  - 上mysql课的每人收233元
- 致命：并未解决导航问题
关系型
- 解决了导航问题：比如每张表都有个学号（公共的编号）
- 分批管理，各管各的
- 数据完整，层次清晰
- 说白了，关系就是共性，为了让每张表都能找到爹
- **和谁都有关系，但是又互不影响

数据库基本操作

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 + 数据库名)：

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

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;

列属性完整性

auto_increment自增属性一定是主键
auto_increment的属性中，删除id=3的数据后，就不能再插入一个id为3的数据了
删除主键：
```
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;
// 会显示怎么创建的，包括注释都可以看到

数据库完整性：
要有一个主键，自动增长；
有些要有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;

置空：删除数据（只更删除几个）
级联：更新数据（操作的是所有的数据）

 // 创建主表
 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 update cascade 
       // 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)

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)

// 跟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

事务

只要commit就不能再rollback了

MySQL

标签：有关 err XML 电话号码别名多对多第三范式 from 注意