ToB企服应用市场:ToB评测及商务社交产业平台

标题: MySQL数据库 [打印本页]

---

作者: 祗疼妳一个    时间: 2023-5-13 07:53
标题: MySQL数据库

基本概念

概念：用于存储和管理数据的仓库

数据库的特点：

• 持久化存储数据
  
• 方便存储和管理数据
  
• 使用了统一的方式操作数据库
  

安装，卸载

安装

略，百度有，基本就两种方式：

• 绿色版压缩包
  
• 安装包
  

卸载

• 在MySQL安装目录下找到my.ini文件(MySQL的配置文件)
  复制datadir地址（数据存放位置）
  
• 卸载MySQL
  
• 删除MySQL安装目录文件夹
  

启动和退出

打开windows服务命令：services.msc

启动和关闭MySQL的服务：命令提示符(管理员)

• 启动：net start mysql
  
• 关闭：net stop mysql
  

MySQL的登录和退出（本地数据库）

• 先启动MySQL服务：net start mysql
  
• root是管理员账号，一般管理员账号root的密码也设置为root
  
• 连接数据库方式1：mysql -uroot -proot
  
• 连接数据库方式2：mysql -uroot -p，然后输入密码
  
• 退出数据库方式1：exit
  
• 退出数据库方式2：quit
  

MySQL的登录和退出（远程数据库）

• 连接数据库方式1：mysql -h"ip地址" -uroot -p，然后输入连接目标的密码
  如：mysql -h127.0.0.1 -uroot -p
  
• 连接数据库方式2：mysql --host="ip地址" --user="用户名" --password="密码"
  如：mysql --host=127.0.0.1 --user=root --password=root
  
• 退出数据库方式1：exit
  
• 退出数据库方式2：quit
  

目录结构

安装目录

• MySQL的安装目录下有个配置文件：my.ini
  
• MySQL的安装位置：my.ini文件中的basedir
  

数据目录

• MySQL的数据存放位置：my.ini文件中的datadir
  
• 数据库：文件夹（相当于）
  
• 表：文件
  
• 数据：文件的内容
  

SQL

SQL：结构化查询语言

• 其实就是定义了操作所有关系型数据库的规则
  
• 每种数据库操作的方式存在不同的地方，称为“方言”
  

SQL的通用语法

• SQL语句可以单行或多行书写，以分号结尾
  
• 可以使用空格和缩进，来增强语句的可读性
  
• MySQL数据库的SQL语句不分大小写，关键字建议使用大写
  
• 注释
  单行注释
    -- 注释（--之后有空格）
    # 注释（mysql特有）
  多行注释
    /*  注释 */
  

SQL的分类

• Data Definition Language (DDL 数据定义语言) 如：建库，建表
  
• Data Manipulation Language(DML 数据操作语言)，如：对表中的记录操作增删改
  
• Data Query Language(DQL 数据查询语言)，如：对表中的查询操作
  
• Data Control Language(DCL 数据控制语言)，如：对用户权限的设置
  

DDL：操作数据库和表

操作数据库

CRUD

• C: 创建
    创建数据库：create database 数据库名称；
    创建数据库前判断是否存在，如果存在就不创建，不存在再创建：create database if not exists 数据库名称;
    创建数据库时设置字符集：create database 数据库名称 character set gbk;
    创建数据库前判断是否存在并设置字符集：create database if not EXISTS db1 CHARACTER set gbk;
  
• R: 查询
    查询所有数据库的名称：show databases;
    查询数据库的创建语句和字符集：show create database 数据库名称；
  
• U: 修改
    修改数据库的字符集：ALTER DATABASE 数据库名称 CHARACTER set utf8;
  
• D: 删除
    删除数据库：DROP DATABASE 数据库名称;
    删除数据库之前判断是否存在，如果存在就删除，如果不存在就不删除：DROP DATABASE if EXISTS 数据库名称;
  

使用数据库：

• 查询当前正在使用的数据库：SELECT DATABASE();
  
• 使用数据库：USE 数据库名称;
  

操作表

• 创建
    创建表：create table 表名(
        列名1  数据类型1，
        列名2  数据类型2，
        ……，
        列名n  数据类型n
      );
    如：create table student(
        id int,
        NAME VARCHAR(32),
        age int,
        score double(4,1),
        birthday DATE,
        insert_time TIMESTAMP
      );
  
• 查询
    查询数据库中所有表的名称：show tables;
    查询表结构：desc 表名
  
• 修改
    修改表名：alter table 表名 rename to 新的表名;
    如：alter table stu rename to stu1;
    修改表的字符集：ALTER TABLE 表名 CHARACTER set gbk;
    添加一列：ALTER TABLE 表名 add 列名 数据类型；
    修改列名称和类型：ALTER TABLE 表名 change 列名 新的列名 新的类型;
    修改列类型：ALTER TABLE 表名 modify 列名 新的类型;
    删除列：ALTER TABLE 表名 DROP 列名;
  
• 删除
    删除表：drop table 表名;
    删除表之前判断表是否存在：drop table if exists 表名;
  
• 复制
    复制表：create table 目标表名 like 原始表名;
    如：create table stu like student;
  

常用的数据类型：

• int：整数
    如：age int
  
• double：小数类型
    如：score double(5，2)
    共5位小数，小数点后保留2位
    最大就是999.99
  
• date：日期，只包含年月日，yyyy-MM-dd
  
• datetime：日期，包含年月日时分秒，yyyy-MM-dd HH:mm:ss
  
• timestamp：时间戳类型，包含年月日时分秒，yyyy-MM-dd HH:mm:ss
    如果不给这个字段赋值 或 赋值为null，则默认使用当前系统时间来自动赋值
  
• varchar：字符串类型
    如：name varchar(20)
    姓名最大20个字符
  

DML：增删改表中的数据

添加数据

1. #语法：insert into 表名(列名1,列名2……列名n) values(值1,值2……值n);       
2. insert into stu1(id,name,age) VALUES(3,"马保国",70);
3. #注意：
4. #1.列名 和 值 要一一对应
5. #2.如果表名后，不写列名，则默认要给所有列都添加值
6. insert into stu1 VALUES(4,"张左手",28,99.5,null,null);
7. #3.除了数字类型，其他类型赋值时要带上引号（单引号双引号都可以）
8. insert into stu1 VALUES(5,"李大头",30,95.5,'2023-3-22',null);

复制代码

删除数据

1. #按条件删除表中数据：delete from 表名 [where 条件]
2. delete from stu where id = 1;
3. #注意：
4. #如果不加where条件，则删除表中所有记录（有几条记录，就执行几次删除；效率低，不推荐使用）

复制代码

1. #删除表：TRUNCATE TABLE 表名;
2. TRUNCATE TABLE stu1;
3. #注意：此操作为删除表后，再创建一个一模一样的空表（效率高，推荐使用）

复制代码

修改数据

1. #语法：update 表名 set 列名1 = 值1,列名2 = 值2,……[where 条件];
2. UPDATE stu1 set name = "马保国666" where id = 3;
3. #注意
4. #如果不加条件，则会将表中所有记录全部修改

复制代码

DQL：查询表中的数据

1. #语法
2. select
3.         字段列表
4. from
5.         表名列表
6. where
7.         条件列表
8. group by
9.         分组字段
10. having
11.         分组之后的条件
12. order by
13.         排序
14. limit
15.         分页限定

复制代码

基础查询

• 多个字段的查询
  
• 去除重复
  
• 计算列
  
• 起别名
  

• 多个字段的查询
  

1. # 查询所有字段 语法：select * from 表名
2. select * from student;
4. #多个字段查询，中间用逗号隔开
5. select
6.         id,                        -- 字段1
7.         name,                -- 字段2
8.         address         -- 字段3
9. from
10.         student;        -- 表名

复制代码

• 去除重复
  

1. #查询student表中的地址
2. select DISTINCT -- 去除重复：DISTINCT
3.         address
4. from
5.         student;

复制代码

• 计算列
  

1. #计算math列和english列的和：math + english
2. select
3.         name,
4.         math,
5.         english,
6.         math + english         -- 计算
7. from
8.         student;
9.        
10. #注意：如果有null参与计算,结果为null
11. #如果需要在计算时将null替换为0，可以使用IFNULL(原始值,替换值)
12. select
13.         name,
14.         math,
15.         english,
16.         math + IFNULL(english,0)        -- 计算时，如果english的值为null，则替换为0
17. from
18.         student;

复制代码

• 起别名
  

1. #计算math列和english列的和，并用sum代替
2. select
3.         name,
4.         math,
5.         english,
6.         math + english as sum        -- 使用as起别名
7.         # math + english sum        -- 不写as也可以
8. from
9.         student;

复制代码

条件查询

• where子句 + 条件
  
• 运算符：
    > 、< 、= 、= 、
    BETWEEN...AND
    IN( 集合)
    LIKE ' 张%'
    IS NULL
    and  或 &&
    or  或 ||
    not  或 !
  

1. #查询年龄大于20岁
2. SELECT * from student WHERE age > 20;
4. #查询年龄大于等于20岁
5. SELECT * from student WHERE age >= 20;
7. #查询年龄等于20岁
8. #等于用=，而不是==
9. SELECT * from student WHERE age = 20;
11. #查询年龄不等于20岁
12. #<>在SQL中表示不等于,在mysql中也可以使用!=
13. SELECT * from student WHERE age <> 20;
14. SELECT * from student WHERE age != 20;

复制代码

1. #查询年龄大于等于20岁，小于等于30岁
2. SELECT * from student WHERE age >= 20 && age <= 30; -- 不推荐
3. SELECT * from student WHERE age >= 20 AND age <= 30;
4. SELECT * from student WHERE age BETWbeEEN 20 AND 30;
5. # BETWbeEEN 20 AND 30，包括 20 和 30

复制代码

分页查询

语法：limit 开始的索引，每页的查询条数；

• 开始的索引：0代表第一条数据
  
• 开始的索引 = （当前页码 - 1） * 每页显示的条数
  

注意：limit是一个方言，只在MySQL中使用

1. #查询年龄为18,20,22岁的记录
2. SELECT * from student WHERE age = 18 || age = 20 || age = 22;
3. SELECT * from student WHERE age = 18 OR age = 20 OR age = 22;
4. SELECT * from student WHERE age IN(18,20,22);

复制代码

DCL：管理用户权限

查询用户

1. #查询英语成绩为null的记录
2. select * from student where english = null;                -- 这是错误的语法；null值不能用运算符判断
3. select * from student where english is null;        -- 这是正确的语法；null值要用is判断
5. #查询英语成绩不为null的记录
6. select * from student where english is NOT null;        -- 使用 is not判断不为null

复制代码

添加用户

1. #模糊查询：查询姓马的人
2. #查询姓马的人
3. select * from student WHERE name LIKE '马%';
4. #查询姓马的，姓名为两个字的人
5. select * from student WHERE name LIKE '马_';
6. #查询姓名第二个字是“化”的人
7. select * from student WHERE name LIKE '_化%';
8. #查询姓名最后一个字是“涛”的人
9. select * from student WHERE name LIKE '%涛';
10. #查询姓名为3个字的人
11. select * from student WHERE name LIKE '___';         -- 三个下划线
12. #查询姓名中包含“德”的人
13. select * from student WHERE name LIKE '%德%';

复制代码

修改密码

注意：

• 旧版需要 password()函数给密码加密
  
• 新版取消了password关键字，改为了authentication_string关键字
  
• 新版取消了password()函数
  

1. #按照升序的方式查询数学成绩
2. SELECT * FROM student ORDER BY math ASC;
3. SELECT * FROM student ORDER BY math;                -- 不写排序方式，默认为升序
5. #按照降序的方式查询数学成绩
6. SELECT * FROM student ORDER BY math DESC;
8. #按照升序的方式查询数学成绩;
9. #如果有几条记录的数学成绩相同，则这几条记录按照英语成绩升序排序
10. SELECT *
11. FROM
12.         student
13. ORDER BY
14.         math ASC,                         -- 第一条件
15.         english ASC;                -- 第二条件（只有当第一条件相同时，才会使用第二条件）

复制代码

如果忘记root用户的密码，该怎么办？

• 在命令提示符管理员的权限下，停止mysql服务：net stop mysql;
  
• 使用无验证方式，启动MySQL服务：mysqld --skip-grant-tables，回车；
  
• 新打开一个命令提示符窗口输入：MySQL，回车；
  
• 登录之后，使用命令更改root账户的密码；
  
• 使用任务管理器，手动结束mysqld.exe进程；
  
• 在命令提示符管理员的权限下，打开mysql服务：net start mysql;
  
• 通过更改之后的 root账户的密码，登录MySQL数据库；
  

删除用户

1. #计算student表中有多少个math
2. SELECT COUNT(math) FROM student;
4. #如果要计算包含null值的列数，使用IFNULL
5. select COUNT(IFNULL(math,0)) FROM student;

复制代码

管理用户权限

1. #计算student表中math列的最大值
2. select MAX(math) FROM student;

复制代码

授予用户的权限（权限列表）

• 如 CREATE、ALTER、SELECT、INSERT、UPDATE 等；
  
• 如果要授予所有的权限则使用 ALL；
  

1. #计算student表中math列的最小值
2. select MIN(math) FROM student;

复制代码

1. #计算student表中math列的和
2. select SUM(math) FROM student;

复制代码

约束

概念：对表中的数据进行限定，保证数据的正确性、有效性和完整性。

约束的分类：

• 主键约束：primary key
  
• 非空约束：not null
  
• 唯一约束：unique
  
• 外键约束：foreign key
  

非空约束

• 非空约束：某一列的值不能为null
  
• 添加非空约束的两种方式
  

• 创建表时，添加非空约束
  
• 创建完表之后，添加非空约束
  

1. #计算student表中math列的平均值
2. select AVG(math) FROM student;

复制代码

唯一约束

• 某一列的值不能重复
  
• 唯一约束可以有null值，但只能有一条记录为null
  
• 删除唯一约束
  

• ALTER TABLE 表名 DROP INDEX 列名;
  

• 添加唯一约束的两种方式
  

• 创建表时，添加唯一约束
  
• 创建完表之后，添加唯一约束
  

1. #按照性别分组，分别查询男同学和女同学的数学平均分
2. select
3.         sex,                                -- 性别
4.         AVG(math)                        -- 数学平均分
5. FROM
6.         student                                -- 表名
7. GROUP BY
8.         sex;                                -- 按照性别分组
11. #按照性别分组，分别查询 男同学和女同学的 数学平均分和人数
12. select
13.         sex,                                -- 性别
14.         AVG(math),                         -- 数学平均分
15.         COUNT(id)                        -- 人数
16. FROM
17.         student                                -- 表名
18. GROUP BY
19.         sex;                                -- 按照性别分组
22. #按照性别分组，分别查询 男同学和女同学 的 数学平均分和人数
23. #要求数学成绩低于70分的人，不参与分组
24. select
25.         sex,                                -- 性别
26.         AVG(math),                         -- 数学平均分
27.         COUNT(id)                        -- 人数
28. FROM
29.         student                                -- 表名
30. WHERE
31.         math > 70                        -- 数学成绩大于70分的才参与分组
32. GROUP BY
33.         sex;                                -- 按照性别分组
36. #按照性别分组，分别查询 男同学和女同学 的 数学平均分和人数
37. #要求：数学成绩低于70分的人，不参与分组
38. #要求：显示人数大于2的记录
39. select
40.         sex,                                -- 性别
41.         AVG(math),                         -- 数学平均分
42.         COUNT(id)                        -- 人数
43. FROM
44.         student                                -- 表名
45. WHERE
46.         math > 70                        -- 数学成绩大于70分的才参与分组
47. GROUP BY
48.         sex                                        -- 按照性别分组
49. HAVING
50.         COUNT(id) > 2;                -- 聚合函数，显示人数大于2的记录
53. #按照性别分组，分别查询 男同学和女同学 的 数学平均分和人数
54. #要求：数学成绩低于70分的人，不参与分组
55. #要求：显示人数大于2的记录
56. select
57.         sex,                                -- 性别
58.         AVG(math),                         -- 数学平均分
59.         COUNT(id)        人数        -- 别名:人数
60. FROM
61.         student
62. WHERE
63.         math > 70                        -- 数学成绩大于70分的才参与分组
64. GROUP BY
65.         sex                                        -- 按照性别分组
66. HAVING
67.         人数 > 2;                        -- 显示别名人数大于2的记录

复制代码

主键约束

• 主键：非空且唯一
  

• 一张表只能有一个字段为主键
  
• 主键就是表中记录的唯一标识
  

• 添加主键约束的两种方式
  

• 创建表时，添加主键约束
  
• 创建完表之后，添加主键约束
  

• 删除主键：ALTER TABLE 表名 DROP PRIMARY KEY;
  4.主键自动增长：auto_increment
  

• 自动跟随上一条记录的最大值进行+1
  

1. #查询student表中第一页，每页3条数据
2. #也就是前3条数据（第1到3条）
3. select * from  student LIMIT 0,3;        #第一页
5. #查询student表中第二页数据（第4到6条）
6. #开始的索引：（2 - 1） * 3 = 3
7. select * from  student LIMIT 3,3;        #第二页
9. #查询第三页数据，（3-1）*3=6
10. select * from  student LIMIT 6,3;        #第三页

复制代码

主键约束-自动增长

如果某列是数值类型，可以使用auto_increment实现自动增长

• 自动增长一般配合主键id使用
  
• 主键id自动增长，会自动跟随上一条记录的id最大值进行+1
  

1. # 查询用户
2. #1.切换数据库到mysql
3. USE mysql;
4. #2.查询user表
5. SELECT * FROM USER;
7. # host的值
8. # localhost 表示当前主机可登录
9. # % 表示任意主机可登录,可用于远程操作

复制代码

运行结果：id自动增长成功

1. # 添加用户
2. # 语法：create user '用户名'@'主机名' identified by '密码';
3. CREATE USER 'JHF'@'localhost' IDENTIFIED BY '123';
4. CREATE USER 'JHF'@'%' IDENTIFIED BY '123';

复制代码

外键约束

• 外键约束：让表与表产生关系，从而保证数据的正确性
  

• 外键可以为null（没有关联），但是不可以为主表中不存在的值
  

• 添加外键约束的方式
  

• 在创建表时，添加外键约束
  create table 表名(
    ……
    外键列，
    constraint        外键名称 foreign key （外键列名称） references        主表名称(主表列名称)
  );
  
• 在创建表之后，添加外键约束
  ALTER TABLE 表名 ADD constraint        外键名称 foreign key （外键列名称） references        主表名称(主表列名称);
  

• 删除外键
  

• ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
  

案例
创建emp表，并添加数据

1. # 修改用户密码
2. # 需求：修改用户JHF的密码为abc
4. # 方式一
5. # 旧版语法：update user set password = password('新密码') where user = '用户名';
6. # 新版语法：update user set authentication_string = '新密码' where user = '用户名';
7. UPDATE USER SET authentication_string = 'abc' WHERE USER = 'JHF';
9. # 方式二
10. # 旧版语法：set password for '用户名'@'主机名' = password('新密码');
11. # 新版语法：set PASSWORD for '用户名'@'主机名' = 'abc';
12. SET PASSWORD FOR 'JHF'@'localhost' = 'abc123';

复制代码

• 发现emp表中有多条重复的内容
  

• dep_name：部门名称
  
• dep_location：部门位置
  

• 使用外键约束进行优化
  

1.创建部门表department，并添加数据

1. # 删除用户
2. # 语法：drop user '用户名'@'主机名';
3. DROP USER 'JHF'@'localhost';
4. DROP USER 'JHF'@'%';

复制代码

这样就没有了重复内容

2.创建员工表employee

1. # 查询用户权限
2. # 语法：SHOW GRANTS FOR '用户名'@'主机名';
3. SHOW GRANTS FOR 'JHF'@'localhost';

复制代码

dep_id关联department表中的部门信息

• 但是这样并没有真正的起作用
  
• 让我们使用外键约束来重新设计表吧
  

3.在创建表时，添加外键约束

使用外键约束，重新设计数据库表

1. # 授予用户权限
2. # 语法：grant  权限列表 on 数据库名.表名 to '用户名'@'主机名';
4. # 给Tom设置只能查看demo1数据库中的account表，并进行（权限列表）操作。
5. GRANT  SELECT ON demo1.`account` TO 'Tom'@'localhost';
6. GRANT  ALL ON demo1.`account` TO 'Tom'@'localhost';
8. # 给Jerry设置所有数据库所有表的所有权限
9. GRANT  ALL ON *.* TO 'Jerry'@'%';       
10. # 一般来讲，要先在mysql数据库中的user表下，使用CREATE USER语法创建用户，再进行权限授权。

复制代码

select * from department;

select * from employee;

此时employee表已经和主表department通过外键约束关联起来了

1. # 撤销权限
2. # 语法：REVOKE 权限列表 on 数据库名.表名 from '用户名'@'主机名';
3. REVOKE ALL ON demo1.`account` FROM 'Tom'@'localhost';
4. REVOKE UPDATE ON *.* FROM 'Jerry'@'%';

复制代码

删除外键

1. #1.创建stu表时,对name列添加非空约束
2. CREATE table stu(
3.         id INT,
4.         NAME VARCHAR(20) NOT NULL         #NAME非空
5. );
6. #2.测试：给name列插入null数据
7. INSERT INTO stu(id) VALUES(3);
8. #3.结果报错
9. #报错原因：添加非空约束的字段不能为null
10. Field 'NAME' doesn't have a default value
12. #但如果name列没有设置非空约束，则可以添加null值
13. #1.更改name列的类型，删除非空约束
14. ALTER TABLE stu MODIFY NAME VARCHAR(20);
15. #2.执行成功，name为null时可以插入数据
16. INSERT INTO stu(id) VALUES(3);
18. #创建完stu表之后，对name列添加非空约束
19. #1.删除name为null的数据
20. DELETE FROM stu where id = 3;
21. #2.更改name列的类型，设置非空约束
22. ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;
23. #3.测试：给name列插入null数据
24. #结果报错，添加非空约束的字段不能为null
25. INSERT INTO stu(id) VALUES(3);

复制代码

在创建表之后，添加外键约束

1. #1.创建stu表时，对phoneNumber列添加唯一约束
2. CREATE table stu(
3.         id INT,       
4.         phoneNumber VARCHAR(20) UNIQUE #电话号码不能重复
5. );
6. #2.添加数据
7. INSERT INTO stu(id,phoneNumber) VALUES(1,15291011111);
8. #3.执行报错
9. INSERT INTO stu(id,phoneNumber) VALUES(2,15291011111);
10. #报错原因：设置唯一约束的列不能重复
11. Duplicate entry '15291011111' for key 'phoneNumber'
12. #4.执行成功，phoneNumber列的值不重复
13. INSERT INTO stu(id,phoneNumber) VALUES(2,15291011112);
15. #1.删除唯一约束，更改phoneNumber列的类型
16. ALTER TABLE stu MODIFY phoneNumber VARCHAR(20);
17. #2.添加数据
18. #执行失败，以更改类型的方式删除唯一约束，是不行的
19. INSERT INTO stu(id,phoneNumber) VALUES(1,15291011111);
20. #3.DROP INDEX：以删除索引的方式，删除唯一约束
21. ALTER TABLE stu DROP INDEX phoneNumber;
22. #4.添加数据成功
23. #没有添加唯一约束时，数据可以重复
24. INSERT INTO stu(id,phoneNumber) VALUES(3,15291011111);
26. #创建完表之后，添加唯一约束
27. #1.删除phoneNumber列有重复的数据
28. DELETE FROM stu WHERE id=3;
29. #2.更改phoneNumber列的类型，添加唯一约束
30. ALTER TABLE stu MODIFY phoneNumber VARCHAR(20) UNIQUE;
31. #3.添加数据失败
32. #设置唯一约束的列不能重复
33. INSERT INTO stu(id,phoneNumber) VALUES(3,15291011111);

复制代码

外键约束-级联操作

• 主表中的列，被外键约束之后，就不能随意更改了；如果想要更改，就要设置级联操作
  

• 添加外键时，设置级联操作：
  ALTER TABLE 表名 ADD constraint        外键名称 foreign key （外键列名称） references        主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE;
  

• 级联操作的分类
  

• 级联更新：ON UPDATE CASCADE
  表的外键列，随着关联的主表列的改变，而自动改变
  
• 级联删除：ON DELETE CASCADE
  表中的记录，随着主表数据的删除，而自动删除
  

1. #1.创建表时，添加主键约束
2. create table stu(
3.         id int PRIMARY KEY,        #给id添加主键约束
4.         name VARCHAR(30)
5. );
6. #2.添加记录
7. INSERT INTO stu(id,name) VALUES(1,"马保国");
8. #添加失败
9. INSERT INTO stu(id,name) VALUES(1,"马保国");
10. #报错原因：主键重复
11. Duplicate entry '1' for key 'PRIMARY'
12. #3.主键不重复，则添加记录成功
13. INSERT INTO stu(id,name) VALUES(2,"马保国");
15. #1.删除主键
16. ALTER TABLE stu MODIFY id INT;                #错误方式
17. #2.添加记录失败
18. INSERT INTO stu(id,name) VALUES(1,"马保国");
19. #3.删除主键
20. ALTER TABLE stu DROP PRIMARY KEY;        #正确方式
21. #4.id重复时，没有设置主键约束，可以添加记录成功
22. INSERT INTO stu(id,name) VALUES(1,"马保国");
24. #创建完表之后，添加主键约束
25. #1.删除表中所有记录
26. delete FROM stu;
27. #2.更改id类型，添加主键约束
28. ALTER TABLE stu MODIFY id INT PRIMARY KEY;
29. #3.添加记录
30. INSERT INTO stu(id,name) VALUES(1,"马保国");
31. #4.添加记录失败
32. INSERT INTO stu(id,name) VALUES(1,"马保国");
33. #报错原因：主键重复
34. Duplicate entry '1' for key 'PRIMARY'

复制代码

查看更改后的主表department数据
SELECT * FROM department;

主表数据更改之后，employee表中的外键也随之更新了
SELECT * FROM employee;

1. #1.删除旧表
2. drop table stu;
3. #2.创建新表
4. create table stu(
5.         id int PRIMARY KEY auto_increment,        #设置主键自动增长
6.         name VARCHAR(30)
7. );
8. #3.插入数据
9. INSERT INTO stu VALUES(null,"aaa");
10. INSERT INTO stu VALUES(null,"bbb");
11. #4.查询表中所有数据
12. select * from stu;

复制代码

查看删除数据之后的主表department数据
SELECT * FROM department;

可以看到删除主表department中的数据之后，employee表中与之相关联的数据也被删除了
SELECT * FROM employee;

数据库的设计

• 多表之间的关系
  

• 一对一
  
• 一对多（多对一）
  
• 多对多
  

• 数据库设计的范式
  

1. 多表之间的关系

• 一对一： 一个人只有一个身份证，一个身份证只能对应一个人
  
• 一对多（多对一）： 一个部门有多个员工，一个员工只对应一个部门
  
• 多对多： 一个学生可以选择多门课程，每门课程也可以被多个学生选择
  

多表关系：一对多关系实现

如：部门和员工

• 实现方式：在多的一方建立外键，指向一的一方的主键
  

多表关系：多对多关系实现

如：学生和课程

• 实现方式：多对多关系实现需要借助第三张中间表。中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键。
  
• 联合主键：PRIMARY KEY(a,b);
  

多表关系：一对一关系实现（了解）

如：人和身份证

• 实现方式：任意一方添加唯一外键，指向另一方的主键
  
• 注意：一对一的关系，一般设计成一张表
  

2. 范式

概念

设计数据库时，需要遵循的一些规范；首先要遵循三大范式。

• 好的数据库设计对数据的存储性能和后期的程序开发，都会产生重要的影响。建立科学的，规范的数据库就需要满足一些规则来优化数据的设计和存储，这些规则就称为范式。
  

分类

• 目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。
  
• 满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），其余范式以次类推。
  
• 一般说来，数据库只需满足第三范式(3NF）就行了。
  

详解

第一范式

每一列不可再拆分，称为原子性

第二范式

在 1NF 的基础上所有列完全依赖于主键列
特点：

• 一张表只描述一件事情。
  
• 表中的每一列都完全依赖于主键
  

• 函数依赖：A-->B，如果通过A(属性或属性组)的值，可以确定唯一 B属性的值。则称B依赖于A
  
• 完全函数依赖：A-->B，A是一个属性组，如果B属性的值需要依赖于A中所有的属性值，则称B完全依赖于A
  
• 部分函数依赖：A-->B，A是一个属性组，如果A中的某些属性值就能确定B属性的值，则称B部分依赖于A
  
• 传递函数依赖：A-->B-->C，如果通过A(属性或属性组)的值，可以确定唯一 B属性的值；通过B(属性或属性组)的值，可以确定唯一 C属性的值。则称C传递依赖于A
  
• 码：如果在一张表中，一个属性或者属性组被其他所有属性完全依赖，则称这个属性或属性组为该表的码。
  主属性：码属性组中的所有属性
  非主属性：除过码属性组中的属性
  

第三范式

在满足 2NF 的基础上，任何非主列不得传递依赖于主键

三大范式小结

数据库的备份和还原

命令行方式

1. #删除自动增长
2. #1.更改id类型，删除自动增长
3. ALTER TABLE stu MODIFY id INT;
4. #2.添加数据失败
5. INSERT INTO stu VALUES(null,"aaa");
6. #报错原因：删除自动增长之后，主键不能为空
7. Column 'id' cannot be null
9. #添加自动增长
10. #1.更改id类型，添加自动增长
11. ALTER TABLE stu MODIFY id INT auto_increment;
12. #2.添加数据成功
13. #设置自动增长后，id虽然为null，但可以根据上一条记录的值，实现自动增长
14. INSERT INTO stu VALUES(null,"aaa");

复制代码

多表查询

多表查询的分类

• 内连接：隐式内连接、显式内连接
  
• 外连接：左外连接、右外连接
  
• 子查询
  

1. #1.创建emp表
2. CREATE TABLE emp (
3. id INT PRIMARY KEY AUTO_INCREMENT,
4. NAME VARCHAR(30),
5. age INT,
6. dep_name VARCHAR(30),
7. dep_location VARCHAR(30)
8. );
9. #2.添加数据
10. INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('张三', 20, '研发部', '广州');
11. INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('李四', 21, '研发部', '广州');
12. INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('王五', 20, '研发部', '广州');
13. INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('老王', 20, '销售部', '深圳');
14. INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('大王', 22, '销售部', '深圳');
15. INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('小王', 18, '销售部', '深圳');
17. #3.查询表的所有记录
18. select * from emp;

复制代码

创建部门表和员工表，并添加信息

1. #1.创建部门表
2. create table department(                                #部门表
3.         id int primary key auto_increment,         #主键id自增
4.         dep_name varchar(20),                                #部门名称
5.         dep_location varchar(20)                        #部门地址
6. );
7. #2.添加部门数据
8. insert into department values(null, '研发部','广州'),(null, '销售部', '深圳');
9. #3.查询部门表的所有内容
10. select * from department;

复制代码

部门表dept

员工表emp

查询部门表和员工表中的笛卡尔积

笛卡尔积：A集合与B集合的所有的组合结果

1. #1.创建员工表
2. create table employee(                                        #员工表
3.         id int primary key auto_increment,        #主键id自增
4.         name varchar(20),                                        #员工名称
5.         age int,                                                        #员工年龄
6.         dep_id int                                                         #部门表的id(外键对应主表的主键)
7. )
8. #2.添加员工数据
9. #添加员工,dep_id 表示员工所在的部门
10. INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1);
11. INSERT INTO employee (NAME, age, dep_id) VALUES ('李四', 21, 1);
12. INSERT INTO employee (NAME, age, dep_id) VALUES ('王五', 20, 1);
13. INSERT INTO employee (NAME, age, dep_id) VALUES ('老王', 20, 2);
14. INSERT INTO employee (NAME, age, dep_id) VALUES ('大王', 22, 2);
15. INSERT INTO employee (NAME, age, dep_id) VALUES ('小王', 18, 2);
16. #3.查询员工表所有内容
17. select * from employee;

复制代码

查询结果：15行，但是有无用数据

使用多表查询可以消除无用信息

1.内连接

• 从哪些表中查询数据
  
• 条件是什么
  
• 查询哪些字段
  

隐式内连接：使用where条件消除无用数据

1. #1.删除emp表、department表和employee表
2. drop table emp;
3. drop table department;
4. drop table employee;
6. #1.重新创建部门表department
7. create table department(
8.         id int primary key auto_increment,
9.         dep_name varchar(20),
10.         dep_location varchar(20)
11. );
13. #2.重新创建employee表，加入外键约束
14. create table employee(
15.         id int primary key auto_increment,
16.         name varchar(20),
17.         age int,
18.         dep_id int,                                        #外键列
19.         CONSTRAINT                                         #约束
20.         emp_dep_fk                                         #自定义的外键名称
21.         FOREIGN KEY (dep_id)                 #外键：此表的外键列
22.         REFERENCES department(id)        #关联：主表的主键列
23. );
25. #3.添加数据
26. #添加两个部门
27. insert into department values(null, '研发部','广州'),(null, '销售部', '深圳');
28. #添加员工信息
29. INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1);
30. INSERT INTO employee (NAME, age, dep_id) VALUES ('李四', 21, 1);
31. INSERT INTO employee (NAME, age, dep_id) VALUES ('王五', 20, 1);
32. INSERT INTO employee (NAME, age, dep_id) VALUES ('老王', 20, 2);
33. INSERT INTO employee (NAME, age, dep_id) VALUES ('大王', 22, 2);
34. INSERT INTO employee (NAME, age, dep_id) VALUES ('小王', 18, 2);

复制代码

查询结果：

显式内连接

语法：select 字段列表 from 表名1 inner join 表名2 on 条件

1. #1.删除主表department中的一条记录
2. DELETE FROM department WHERE id = 1;
3. #报错：主表的数据正在通过外键被其他表关联着，所以删不掉
4. Cannot delete or update a parent row: a foreign key constraint fails (`demo`.`employee`, CONSTRAINT `emp_dep_fk` FOREIGN KEY (`dep_id`) REFERENCES `department` (`id`))
5. #2.向employee表中添加一条记录，关联department中不存在的部门
6. INSERT INTO employee (NAME, age, dep_id) VALUES ('马保国', 70, 3);
7. #报错：使用了外键约束的表，只能关联主表中存在的记录
8. Cannot add or update a child row: a foreign key constraint fails (`demo`.`employee`, CONSTRAINT `emp_dep_fk` FOREIGN KEY (`dep_id`) REFERENCES `department` (`id`))

复制代码

2.外连接

左外连接

语法：select 字段列表 from 表1 left [outer] join 表2 on 条件;

• 查询的是左表所有数据以及与右表的交集部分
  

1. #1.删除外键
2. ALTER TABLE employee DROP FOREIGN KEY emp_dep_fk;
3. #2.向employee表中添加一条记录，关联department中不存在的部门
4. INSERT INTO employee (NAME, age, dep_id) VALUES ('马保国', 70, 3);
5. #执行成功，删除了外键之后，可以向表中添加记录关联主表中不存在的记录

复制代码

右外连接

语法：select 字段列表 from 表1 right [outer] join 表2 on 条件;

• 查询的是右表所有数据以及与左表的交集部分
  

1. #1.删除之前向employee表中添加的，关联主表中不存在记录的那条数据
2. DELETE FROM employee WHERE id = 8;
3. #2.添加外键
4. ALTER TABLE employee ADD CONSTRAINT emp_dep_fk FOREIGN KEY (dep_id) REFERENCES department(id);
5. #3.向employee表中添加一条记录，关联department中不存在的部门
6. INSERT INTO employee (NAME, age, dep_id) VALUES ('马保国', 70, 3);
7. #报错：使用了外键约束的表，只能关联主表中存在的记录
8. Cannot add or update a child row: a foreign key constraint fails (`demo`.`employee`, CONSTRAINT `emp_dep_fk` FOREIGN KEY (`dep_id`) REFERENCES `department` (`id`))

复制代码

3.子查询

概念：查询中嵌套查询，称嵌套查询为子查询

1. #添加外键时，设置级联更新
2. #1.删除外键
3. ALTER TABLE employee DROP FOREIGN KEY emp_dep_fk;
4. #2.添加外键,设置级联更新
5. ALTER TABLE employee ADD CONSTRAINT emp_dep_fk FOREIGN KEY (dep_id) REFERENCES department(id) ON UPDATE CASCADE;
6. #3.设置级联更新之后，就可以将主表中的关联列数据进行更改了
7. UPDATE department SET id = 5 WHERE id = 1;

复制代码

情况1
<blockquote>子查询的结果是单行单列的
<ul>子查询可以作为条件，使用<strong>运算符(>, =,  "2011-11-11") t2WHERE        t1.`id` = t2.dept_id;[/code]事务

基本介绍

概念：如果一个包含多个步骤的业务操作，被事务管理，那么这些操作，要么同时成功，要么同时失败。

• 开启事务：START TRANSACTION;
  
• 回滚：ROLLBACK;
  
• 提交事务：COMMIT;
  

事务提交的两种方式

手动提交：

• 需要先开启事务，再提交；
  

自动提交：

• MySQL数据库中事务默认自动提交，一条DML(增删改)语句会自动提交一次事务；
  
• Oracle数据库中事务默认手动提交；
  

事物的默认提交方式

1. #添加外键,设置级联更新，设置级联删除
2. #1.删除外键
3. ALTER TABLE employee DROP FOREIGN KEY emp_dep_fk;
4. #2.添加外键,设置级联更新，设置级联删除
5. ALTER TABLE employee ADD CONSTRAINT emp_dep_fk FOREIGN KEY (dep_id) REFERENCES department(id) ON UPDATE CASCADE ON DELETE CASCADE;
6. #3.设置级联删除后，删除主表department中的记录
7. DELETE FROM department WHERE id = 5;

复制代码

事务四大特征

• 原子性：不可分割的最小操作单位，要么同时成功，要么同时失败；
  
• 持久性：事务一旦提交或者回滚，数据库将持久化访问数据；
  
• 隔离性：多个事物之间，相互独立；
  
• 一致性：事务操作前后，数据总量不变；
  

事务隔离级别

概念

多个事物之间相互独立，如果多个事务操作同一批数据，则会引发一些问题；设置不同的隔离级别，就可以解决这些问题；

存在问题

• 脏读：一个事务读取到另一个事务中，没有提交的数据；
  
• 不可重复读（虚读）：在同一个事物中，两次读取到的数据不一样；
  
• 幻读：一个事务操作（DML）数据表中所有的记录，另一个事务添加了一条数据，导致第一个事务查询不到自己的修改；
  

隔离级别

read uncommited：读未提交

• 产生的问题：脏读、不可重复读、幻读；
  

read commited：读已提交（Oracle默认）

• 产生的问题：不可重复读、幻读；
  

repeatable read：可重复读（MySQL默认）

• 产生的问题：幻读；
  

serializable：串行化

• 可以解决所有的问题；
  

注意：隔离级别从小到大，安全性递增，效率递减；

数据库查询 / 设置隔离级别

• 查询隔离级别：select @@tx_isolation；
  
• 设置隔离级别：set global transaction isolation level  隔离级别；
  

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！

---

欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/)

Powered by Discuz! X3.4