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)

• 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

事务

• 只要commit就不能再rollback了

•  

MySQL

原文地址：https://www.cnblogs.com/Angela-Joker/p/14602400.html