MySql入门教程

MySql

学习来源黑马视频教程

文章目录

1.数据库的基本概念

什么是数据库（DataBase）？

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

数据库的特点

持久化存储数据的。其实数据库就是一个文件系统
方便存储和管理数据
使用了统一的方式操作数据库

2.SQL

什么是SQL？

Structured Query Language:结构化查询语言

其实就是定义了操作所有关系型数据库的规则。

SQL的分类

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ffSVnze7-1659168214131)(C:\Users\30666\AppData\Roaming\Typora\typora-user-images\image-20220728173538949.png)]$

3.DDL

DESC people;-- 查询表结构createtable student(
    id INT(11),`name`VARCHAR(20),
    age int(3),
    score DOUBLE(4,1),
    birthday date,
    createTime TIMESTAMP);DESC student;-- 查询表结构-- 删除表 drop table if exists 表的名称-- 复制表  create table 表名 like 被复制的表名-- 修改表名 alter table 表名 rename to 新的表名altertable student renameto stu;-- 查看表的字符集showcreatetable stu;/* CREATE TABLE `stu` (
  `id` int DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `score` double(4,1) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `createTime` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
*/-- 修改表的字符集 alter table 表名 character set utf8;-- 添加一列 alter table 表名 add 列名 数据类型;-- 修改列名 类型 -- 1.修改列名   alter table 表名 change 列名 修改后的新列名 新的数据类型-- 2.修改类型   alter table 表名 modify 列名 新数据类型-- 删除列 alter table 表名 drop 列名

4.DML

-- 添加数据INSERTINTO stu(id,`name`,age)VALUES(1,'张三',18);INSERTINTO stu VALUES(2,'李四',19,99.9,NULL,NULL);-- 查询语句SELECT*FROM stu;-- 删除数据DELETEFROM stu WHERE id =2;-- 若要删除表的全部记录，推荐使用以下方式TRUNCATEtable stu;-- 先删除表，然后在创建一张一样的表-- 修改数据UPDATE stu SET age =20WHERE id =1;UPDATE stu SET age =20,score =100WHERE id =2;

5.DQL

查询语句

基础查询

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

-- 查询 姓名和年龄select`name`,age from student;select*from student;-- 去除重复的结果selectdistinct address from student;selectdistinct name,address from student;-- 计算math和chinese的分数之后select`name`,chinese,math,math+chinese as 总分 from student;-- 如果有null 参与的运算，计算机结果都为nullselect`name`,chinese,math,IFNULL(math,0)+IFNULL(chinese,0)from student;select`name`as 姓名,chinese as 语文成绩,math as 数学成绩,IFNULL(math,0)+IFNULL(chinese,0)as
总分 from student;

条件查询

where子句后跟条件

运算符

< 、 > 、 <= 、>= 、 = 、 <>

BETWEEN…AND

IN(集合)

LIKE 模糊查询

_ :单个任意字符

% :多个任意字符

IS NULL

&& 或 and

or 或 ||

not 或 !

-- 条件查询 -- 查询年龄大于等于20岁select*from student where age>=20;-- 查询年龄不等于20岁select*from student where age!=20;select*from student where age<>20;-- 查询年龄大于等于20 小于等于30的年龄select*from student where age>=20&& age<=30;select*from student where age>=20and age<=30;select*from student where age BETWEEN20AND30;-- 查询年龄21岁 19岁 18岁select*from student where age =22or age =19or age =18;select*from student where age =22|| age =19|| age =18;select*from student where age in(22,19,18);-- 查询数据缺考的学生select*from student where math =NULL;-- NULL值不能使用 = 运算符判断select*from student where math ISNULL;-- 查询数学成绩不为空的成绩select*from student where math ISNOTNULL;

模糊查询

-- 模糊查询-- 查询姓张的有哪些select*from student where`name`LIKE'张%';-- 查询第二个字是小的人select*from student where`name`LIKE'_小%';-- 查询姓名是三个字的人select*from student where`name`LIKE'___';-- 查询姓名中包含张的人select*from student where`name`LIKE'%张%';

排序查询

oder by 子句

order by 排序字段排序方式

order by 排序字段1 排序方式1，排序字段2 排序方式2…

排序方式

asc：升序，默认的

desc：降序

注意：如果有多个排序条件，则当前的条件值一样时，才会判断第二条件

-- 排序查询    select*from student orderby math asc;select*from student orderby math desc;-- 按照数据成绩排名，如果数据成绩一样，则按照语文成绩排名select*from student orderby math asc,chinese asc;

聚合函数

将一列数据作为一个整体，进行纵向的计算。

count：计算个数

一般选择非空的列：主键

COUNT(*)

max：计算最大值

min：计算最小值

sum：计算和

avg：计算平均值

注意：聚合函数的计算，排序null值

解决方案：

选择非空的列进行计算

IFNULL函数

-- 聚合查询selectCOUNT(`name`)from student;-- 计算该表中有多少条记录selectCOUNT(IFNULL(math,0))from student;selectCOUNT(*)from student;-- 查询数学成绩的最大值selectMAX(math)from student;-- 查询数学成绩的最小值selectMIN(math)from student;-- 查询数学成绩的和selectSUM(math)from student;-- 查询数学成绩的平均值selectAVG(math)from student;

分组查询

语法：group by 分组字段

注意：

分组之后查询的字段：分组字段、聚合函数

where和having的区别？

1.where 在分组之前进行限定，如果不满足这个限定的条件，则不参与分组，having 在分组之后进行限定，如果不满足这个限定的条件，则不会被查询出来

2.where 后不可以跟聚合函数，having 可以进行聚合函数的判断，

-- 分组查询-- 按照性别分组，分别查询男、女同学的math的平均分 select sex,AVG(math)from student groupby sex;-- 按照性别分组，分别查询男、女同学的math的平均分 ,人数select sex,AVG(math),count(id)from student groupby sex;-- 按照性别分组，分别查询男、女同学的math的平均分 ,人数。要求：分数低于70分的人不参与分组    select sex,AVG(math),count(id)from student where math >70groupby sex;-- 按照性别分组，分别查询男、女同学的math的平均分 ,人数。要求：分数低于70分的人不参与分组    ,分组之后的人数要大于2个人select sex,AVG(math),count(id) 人数
        from student 
            where math >70groupby sex 
havingcount(id)>=2;

分页查询

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

公式：开始的索引 = （当前的页码-1）*每页显示的条数

-- 分页查询-- 每页显示三条记录select*from student limit0,3;-- 第一页select*from student limit3,3;-- 第二页-- 公式：开始的索引 = （当前的页码-1）*每页显示的条数

6.约束

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

分类：

主键约束：primary key

非空约束：not null

唯一约束：unique

外键约束：foreign key

主键约束：primary key

含义：非空且唯一

一张表只能有一个字段为主键

主键就是表中记录的唯一标识

-- 在创建表时添加主键约束createtable stu(
    id int(11)primarykey,-- 给id添加主键约束`name`VARCHAR(20));-- 删除主键altertable stu dropprimarykey;-- 在创建表后添加主键约束createtable stu(
    id int(11),-- 给id添加主键约束`name`VARCHAR(20));-- 在创建表后添加主键约束altertable stu modify id int(11)primarykey;

主键约束_自动增长

概念：如果某一列是数值类型的，使用 auto_increment 可以完成值的自动增长

-- 主键约束_自动增长createtable stu(
    id int(11)primarykeyauto_increment,-- 给id添加主键约束,并且自动增长`name`VARCHAR(20));-- 删除自动增长    altertable stu modify id int(11);-- 添加自动增长altertable stu modify id int(11)auto_increment;

非空约束：not null

-- 创建表时添加约束createtable stu(
    id int(11),`name`VARCHAR(20)NOTNULL-- name为非空);-- 删除name的非空约束altertable stu modify`name`VARCHAR(20);-- 创建表结束后添加非空约束createtable stu(
    id int(11),`name`VARCHAR(20)-- name为非空);-- 创建表结束后添加非空约束altertable stu modify`name`VARCHAR(20)NOT NUL;

唯一约束：unique

某一列的值不能重复

注意：

唯一约束可以有null值，但是只能有一条记录为null

-- 创建表添加唯一约束createtable stu(
    id int(11),
     phone VARCHAR(20)UNIQUE-- phone为唯一约束);-- 删除phone的唯一约束altertable stu dropindex phone;-- 创建表结束后添加唯一约束createtable stu(
    id int(11),
     phone VARCHAR(20));-- 创建表结束后添加唯一约束altertable stu modify phone VARCHAR(20)UNIQUE;

外键约束：foreign key

让表与表产生关系，从而保证数据的正确性。

-- 外键约束：foreign key-- 创建部门表(主表)createtable department(
    id int(11)primarykeyauto_increment, 
    dep_name VARCHAR(20),
    dep_location VARCHAR(20));-- 创建员工表(从表)createtable employee(
    id int(11)primarykeyauto_increment,`name`VARCHAR(20),
    age int(11),
    dep_id int(11)-- 外键对应部门表的主键 );insertinto department(dep_name,dep_location)VALUES('研发部','广州');insertinto department(dep_name,dep_location)VALUES('销售部','深圳');insertinto employee(`name`,age,dep_id)VALUES('张三',21,1);insertinto employee(`name`,age,dep_id)VALUES('李四',23,1);insertinto employee(`name`,age,dep_id)VALUES('王五',25,1);insertinto employee(`name`,age,dep_id)VALUES('小二',21,2);insertinto employee(`name`,age,dep_id)VALUES('赵六',24,2);insertinto employee(`name`,age,dep_id)VALUES('大一',18,2);

在创建表时，添加外键

语法：

create table 表名(

…

constraint 外键名称 foreign key （外键列的名称） references 主表的名称(主表列名称)

);

-- 添加外键    -- 创建部门表(主表)createtable department(
    id int(11)primarykeyauto_increment, 
    dep_name VARCHAR(20),
    dep_location VARCHAR(20));-- 创建员工表(从表)createtable employee(
    id int(11)primarykeyauto_increment,`name`VARCHAR(20),
    age int(11),
    dep_id int(11),-- 外键对应部门表的主键 constraint emp_dep_id foreignkey(dep_id)references department(id));insertinto department(dep_name,dep_location)VALUES('研发部','广州');insertinto department(dep_name,dep_location)VALUES('销售部','深圳');insertinto employee(`name`,age,dep_id)VALUES('张三',21,1);insertinto employee(`name`,age,dep_id)VALUES('李四',23,1);insertinto employee(`name`,age,dep_id)VALUES('王五',25,1);insertinto employee(`name`,age,dep_id)VALUES('小二',21,2);insertinto employee(`name`,age,dep_id)VALUES('赵六',24,2);insertinto employee(`name`,age,dep_id)VALUES('大一',18,2);SELECT*FROM employee;SELECT*FROM department;-- 删除外键altertable employee dropforeignkey emp_dep_id;-- 创建表之后添加外键altertable employee addconstraint emp_dep_id foreignkey(dep_id)references department(id);

外键约束_级联操作

-- 外键约束_级联操作-- 删除外键altertable employee dropforeignkey emp_dep_id;-- 添加外键，设置级联更新（当修改dep表中的id时会同时更新emp中的dep_id）altertable employee addconstraint emp_dep_id foreignkey(dep_id)references department(id)onupdatecascade;-- 添加外键，设置级联更新、级联删除（当删除dep表中对应的id数据时会同时删除emp表中对应的dep_id数据）altertable employee addconstraint emp_dep_id foreignkey(dep_id)references department(id)onupdatecascadeondeletecascade;

7.多表之间的关系

分类

一对一

如：人和身份证

分析：一个人只有一个身份证，一个身份证只有一个人

一对多（多对一）

如：部门和员工

分析：一个部门有多个员工，一个员工只能对应一个部门

多对多

如：学生和课程

分析：一个学生可以选择多门课程，一门课程也可以被多个学生选择

实现关系

一对多（多对一）

如：部门和员工

分析：一个部门有多个员工，一个员工只能对应一个部门

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

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-TCpstufB-1659168214132)(C:\Users\30666\AppData\Roaming\Typora\typora-user-images\image-20220729164659418.png)]$

多对多

如：学生和课程

分析：一个学生可以选择多门课程，一门课程也可以被多个学生选择

实现方式：多对多关系实现需要借助第三张中间表。中间表至少包含两个字段，这两个字段作为第三表的外键，分别指向两张表的主键。

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dX51vaYl-1659168214133)(C:\Users\30666\AppData\Roaming\Typora\typora-user-images\image-20220729165155622.png)]$

一对一

如：人和身份证

分析：一个人只有一个身份证，一个身份证只有一个人

实现方式：一对一关系实现，可以在任意一方添加唯一外键指向另一方的主键

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-pnt3pEp8-1659168214133)(C:\Users\30666\AppData\Roaming\Typora\typora-user-images\image-20220729165312435.png)]$

8.范式

概念：设计数据库时，需要遵循的一些规范

设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、第四范式（4NF）、第五范式（5NF）和Boyce-Codd范式（BCNF）。

分类：

第一范式（1NF）：每一列都是不可分割原子数据项

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zgxWbisv-1659168214133)(C:\Users\30666\AppData\Roaming\Typora\typora-user-images\image-20220729173324384.png)]$

第二范式（2NF）：在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分依赖函数）

1.函数依赖：A—>B，如果通过A属性（属性组）的值，可以确定唯一B属性的值，则称B依赖于A。

如：学号—>姓名。（学号、课程名称）—>分数

2.完全函数依赖：A—>B，如果A是一个属性组，则B属性值的确定需要依赖于A属性组中所有的属性值。

如：（学号、课程名称）—>分数

3.部分函数依赖：A—>B，如果A是一个属性组，则B属性值的确定只需要依赖于A属性组中的某一些值即可确定。

如：（学号、课程名称）—>姓名

4.传递函数依赖：A—>B,B—>C, 如果通过A属性（属性组）的值，可以确定唯一B属性的值，再通过B属性（属性组）的值可以确定唯一C属性的值，则称C传递依赖于A。

如：学号—>系名，系名—>系主任

5.码：如果在一张表中，一个属性或属性组，被其他所有属性所完全依赖，则称这个属性（属性组）为该表的码。

如：该表中码为（学号、课程名称）

主属性：码属性组中的所有属性

非主属性：除码属性组中的属性

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DV6EVQyW-1659168214134)(C:\Users\30666\AppData\Roaming\Typora\typora-user-images\image-20220729202842706.png)]$

第三范式（3NF）：在2NF的基础上，任何非主属性不依赖于其它非主属性

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-yvx9iyFZ-1659168214134)(C:\Users\30666\AppData\Roaming\Typora\typora-user-images\image-20220729203057968.png)]$

9.数据库的备份和还原

命令行

语法：

备份：mysqldump -u用户名 -p密码要备份的数据库名称 > 保存的路径

还原：

登录数据库

创建数据库

使用数据库

执行文件 source 文件路径

图形化工具

10.多表查询

创建表

CREATETABLE`department`(`id`intNOTNULLAUTO_INCREMENT,`dep_name`varchar(20)DEFAULTNULL,`dep_location`varchar(20)DEFAULTNULL,PRIMARYKEY(`id`))ENGINE=InnoDBAUTO_INCREMENT=6DEFAULTCHARSET=utf8mb3;INSERTINTO`department`VALUES(1,'研发部','广州');INSERTINTO`department`VALUES(2,'销售部','广州');INSERTINTO`department`VALUES(3,'财务部','广州');CREATETABLE`employee`(`id`intNOTNULLAUTO_INCREMENT,`name`varchar(20)DEFAULTNULL,`age`intDEFAULTNULL,`gender`varchar(255)DEFAULTNULL,`salary`varchar(255)DEFAULTNULL,`join_date`timestamp(6)NULLDEFAULTNULLONUPDATECURRENT_TIMESTAMP(6),`dep_id`intDEFAULTNULL,PRIMARYKEY(`id`),KEY`emp_dep_id`(`dep_id`),CONSTRAINT`emp_dep_id`FOREIGNKEY(`dep_id`)REFERENCES`department`(`id`)ONUPDATECASCADE)ENGINE=InnoDBAUTO_INCREMENT=7DEFAULTCHARSET=utf8mb3;INSERTINTO`employee`VALUES(1,'张三',21,'男','10000','2022-07-29 20:41:49.280651',1);INSERTINTO`employee`VALUES(2,'李四',23,'男','9000','2022-07-29 20:41:52.279826',1);INSERTINTO`employee`VALUES(3,'王五',25,'男','12300','2022-07-29 20:42:11.728213',1);INSERTINTO`employee`VALUES(4,'欧阳菲',21,'女','8500','2022-07-29 20:42:05.337770',2);INSERTINTO`employee`VALUES(5,'赵六',24,'男','9500','2022-07-29 20:42:23.664280',2);INSERTINTO`employee`VALUES(6,'张小小',18,'女','10000','2022-07-29 20:43:58.530818',3);

笛卡尔积

有两个集合A、B，取这两个集合的所有组成情况。

要完成多表查询，需要消除无用的数据。

SELECT*FROM employee,department

-- 笛卡尔积 A，B   3*6=18-- 消除无用的数据

内连接查询

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

-- 内连接查询-- 查询所有员工信息和对应的部门信息SELECT*FROM 
        employee,department 
WHERE 
        employee.dep_id = department.id;-- 查询员工表的名称，性别。部门表的名称SELECT 
        employee.`name`,employee.gender,department.`dep_name`FROM 
        employee,department
WHERE 
        employee.dep_id = department.id;SELECT 
        t1.`name`,t1.gender,t2.dep_name
FROM
        employee t1,department t2
WHERE 
        t1.dep_id = t2.id

显式内连接

语法：select 字段列表 from 表名1 [inner可省略] join 表名2 on 条件

-- 显式内连接SELECT*from 
            employee 
INNERJOIN
            department
ON
        employee.dep_id = department.id

-- --------------------------------------------------------------SELECT*from 
            employee 
JOIN
            department
ON
        employee.dep_id = department.id

外连接查询

左外连接

语法：select 字段列表 from 表1 left [outer可省略] join 表2 on 条件；

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

-- 左外连接-- 查询所有员工信息，如果员工有部门，则查询部门名称，没有部门，则不显示部门名称SELECT 
            t1.*,t2.dep_name     
FROM
          employee t1
LEFTJOIN 
                    department t2
ON
        t1.dep_id = t2.id

右外连接

语法：select 字段列表 from 表1 right [outer可省略] join 表2 on 条件；

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

-- 右外连接-- 查询所有员工信息，如果员工有部门，则查询部门名称，没有部门，则不显示SELECT 
            t1.*,t2.dep_name     
FROM
          employee t1
RIGHTJOIN 
                    department t2
ON
        t1.dep_id = t2.id

子查询

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

-- 子查询-- 查询工资最高的员工信息-- 1.查询最高的工资是多少 12300SELECTMAX(salary)FROM employee;-- 2.查询员工信息，并且工资等于12300的SELECT*FROM employee WHERE employee.salary =12300;-- 一条sql就完成这个操作SELECT*FROM employee WHERE employee.salary =(SELECTMAX(salary)FROM employee)

子查询的不同情况

1.子查询的结果是单行单列的

子查询可以作为条件，使用运算符去判断 > >= < <= =

-- 查询员工工资小于平均工资的人SELECT*FROM employee WHERE employee.salary <(SELECTAVG(salary)FROM employee);

2.子查询的结果是多行单列的,使用运算符in来判断

子查询可以作为条件

-- 查询财务部和研发部所有的员工信息SELECT id FROM department WHERE dep_name ='财务部'or dep_name ='研发部';SELECT*FROM employee WHERE dep_id  =3or dep_id =1;-- 一条sql就完成这个操作SELECT*FROM employee WHERE dep_id in(3,1);SELECT*FROM employee WHERE dep_id in(SELECT id FROM department WHERE dep_name ='财务部'or dep_name ='研发部');

3.子查询的结果是多行多列的

子查询可以作为一张虚拟表。

-- 查询员工入职日期是2021-7-28日之后的员工信息和部门信息SELECT*FROM 
        department t1,(SELECT*FROM employee WHERE employee.join_date >'2022-07-28') t2
WHERE 
        t1.id = t2.dep_id;-- 普通连接SELECT*FROM 
        employee t1,department t2 
WHERE 
        t1.dep_id = t2.id 
and 
        t1.join_date >'2022-07-28';

多表查询练习

/*
 Navicat Premium Data Transfer

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 80027
 Source Host           : localhost:3306
 Source Schema         : db1

 Target Server Type    : MySQL
 Target Server Version : 80027
 File Encoding         : 65001

 Date: 30/07/2022 14:41:37
*/SET NAMES utf8mb4;SET FOREIGN_KEY_CHECKS =0;-- ------------------------------ Table structure for dept-- ----------------------------DROPTABLEIFEXISTS`dept`;CREATETABLE`dept`(`id`intNOTNULL,`dname`varchar(50)CHARACTERSET utf8 COLLATE utf8_general_ci NULLDEFAULTNULL,`loc`varchar(50)CHARACTERSET utf8 COLLATE utf8_general_ci NULLDEFAULTNULL,PRIMARYKEY(`id`)USINGBTREE)ENGINE=InnoDBCHARACTERSET= utf8 COLLATE= utf8_general_ci ROW_FORMAT = Dynamic;-- ------------------------------ Records of dept-- ----------------------------INSERTINTO`dept`VALUES(10,'研发部','北京');INSERTINTO`dept`VALUES(20,'学工部','上海');INSERTINTO`dept`VALUES(30,'销售部','广州');INSERTINTO`dept`VALUES(40,'财务部','深圳');-- ------------------------------ Table structure for emp-- ----------------------------DROPTABLEIFEXISTS`emp`;CREATETABLE`emp`(`id`intNOTNULL,`ename`varchar(50)CHARACTERSET utf8 COLLATE utf8_general_ci NULLDEFAULTNULL,`job_id`intNULLDEFAULTNULL,`mgr`intNULLDEFAULTNULL,`joindate`timestampNULLDEFAULTNULL,`salary`decimal(7,2)NULLDEFAULTNULL,`bonus`decimal(7,2)NULLDEFAULTNULL,`dept_id`intNULLDEFAULTNULL,PRIMARYKEY(`id`)USINGBTREE,INDEX`job_id`(`job_id`)USINGBTREE,INDEX`dept_id`(`dept_id`)USINGBTREE,CONSTRAINT`emp_ibfk_1`FOREIGNKEY(`job_id`)REFERENCES`job`(`id`)ONDELETERESTRICTONUPDATERESTRICT,CONSTRAINT`emp_ibfk_2`FOREIGNKEY(`dept_id`)REFERENCES`dept`(`id`)ONDELETERESTRICTONUPDATERESTRICT)ENGINE=InnoDBCHARACTERSET= utf8 COLLATE= utf8_general_ci ROW_FORMAT = Dynamic;-- ------------------------------ Records of emp-- ----------------------------INSERTINTO`emp`VALUES(1001,'孙悟空',4,1004,'2000-12-17 00:00:00',8000.00,NULL,20);INSERTINTO`emp`VALUES(1002,'卢俊义',3,1006,'2001-02-20 00:00:00',16000.00,3000.00,30);INSERTINTO`emp`VALUES(1003,'林冲',3,1006,'2001-02-22 00:00:00',12500.00,5000.00,30);INSERTINTO`emp`VALUES(1004,'唐僧',2,1009,'2001-04-02 00:00:00',29750.00,NULL,20);INSERTINTO`emp`VALUES(1005,'李逵',4,1006,'2001-09-28 00:00:00',12500.00,14000.00,30);INSERTINTO`emp`VALUES(1006,'宋江',2,1009,'2001-05-01 00:00:00',28500.00,NULL,30);INSERTINTO`emp`VALUES(1007,'刘备',2,1009,'2001-09-01 00:00:00',24500.00,NULL,10);INSERTINTO`emp`VALUES(1008,'猪八戒',4,1004,'2007-04-19 00:00:00',30000.00,NULL,20);INSERTINTO`emp`VALUES(1009,'罗贯中',1,NULL,'2001-11-17 00:00:00',50000.00,NULL,10);INSERTINTO`emp`VALUES(1010,'吴用',3,1006,'2001-09-08 00:00:00',15000.00,0.00,30);INSERTINTO`emp`VALUES(1011,'沙僧',4,1004,'2007-05-23 00:00:00',11000.00,NULL,20);INSERTINTO`emp`VALUES(1012,'李逵',4,1006,'2001-12-03 00:00:00',9500.00,NULL,30);INSERTINTO`emp`VALUES(1013,'小白龙',4,1004,'2001-12-03 00:00:00',30000.00,NULL,20);INSERTINTO`emp`VALUES(1014,'关羽',4,1007,'2002-01-23 00:00:00',13000.00,NULL,10);-- ------------------------------ Table structure for job-- ----------------------------DROPTABLEIFEXISTS`job`;CREATETABLE`job`(`id`intNOTNULL,`jname`varchar(20)CHARACTERSET utf8 COLLATE utf8_general_ci NULLDEFAULTNULL,`description`varchar(50)CHARACTERSET utf8 COLLATE utf8_general_ci NULLDEFAULTNULL,PRIMARYKEY(`id`)USINGBTREE)ENGINE=InnoDBCHARACTERSET= utf8 COLLATE= utf8_general_ci ROW_FORMAT = Dynamic;-- ------------------------------ Records of job-- ----------------------------INSERTINTO`job`VALUES(1,'董事长','管理整个公司,接单');INSERTINTO`job`VALUES(2,'经理','管理部门员工');INSERTINTO`job`VALUES(3,'销售员','向客人推销产品');INSERTINTO`job`VALUES(4,'文员','使用办公软件');-- ------------------------------ Table structure for salarygrade-- ----------------------------DROPTABLEIFEXISTS`salarygrade`;CREATETABLE`salarygrade`(`grade`intNOTNULL,`losalary`intNULLDEFAULTNULL,`hisalary`intNULLDEFAULTNULL,PRIMARYKEY(`grade`)USINGBTREE)ENGINE=InnoDBCHARACTERSET= utf8 COLLATE= utf8_general_ci ROW_FORMAT = Dynamic;-- ------------------------------ Records of salarygrade-- ----------------------------INSERTINTO`salarygrade`VALUES(1,7000,12000);INSERTINTO`salarygrade`VALUES(2,12000,14000);INSERTINTO`salarygrade`VALUES(3,14000,20010);INSERTINTO`salarygrade`VALUES(4,20010,30010);INSERTINTO`salarygrade`VALUES(5,30010,99990);SET FOREIGN_KEY_CHECKS =1;-- 1.查询所有员工信息。查询员工编号、员工姓名、工资、职位名称、职务描述/*
    分析：
            1.员工编号、员工姓名、工资、需要查询emp表    |     职位名称、职务描述 需要查询job表
            2.查询的条件 emp.jod_id = job.id
*/SELECT
            e.id,e.ename,e.salary,j.jname,j.description  
FROM
        emp e,job j
WHERE
        e.job_id = j.id;-- 2.查询员工编号，员工姓名、工资、职务名称、职务描述、部门名称、部门位置/*
    分析：
            1.员工编号，员工姓名、工资 emp  | 职务名称、职务描述 job  |  部门名称、部门位置 dept
            2.查询的条件 emp.job_id = job.id and emp.dept_id = dept.id
*/SELECT
            e.id,e.ename,e.salary,j.jname,j.description,d.dname,d.loc
FROM
        emp e,job j,dept d
WHERE
        e.job_id = j.id and e.dept_id = d.id
        
        
-- 3.查询员工姓名、工资、工资等级/*
    分析：
            1.员工姓名、工资 emp |  工资等级 salarygrade
            2.条件 emp.salary >= salarygrade.losalary and emp.salary <=salarygrade.hisalary
                            emp.salary between salarygrade.losalary and salarygrade.hisalary
*/SELECT
            e.ename,e.salary,s.grade
FROM 
            emp e,salarygrade s
WHERE 
            e.salary BETWEEN s.losalary AND s.hisalary

-- 4.查询员工姓名、工资、职务名称、职务描述、部门名称、部门位置、工资等级/*
    分析：
            1.员工姓名、工资 emp  | 职务名称、职务描述 job| 部门名称、部门位置 dept | 工资等级 salarygrade
            
            2.条件 emp.job_id = job.id and emp.dept_id = dept.id and
                         emp.salary between salarygrade.losalary and salarygrade.hisalary
*/SELECT
            e.ename,e.salary,j.jname,j.description,d.dname,d.loc,s.grade
FROM
            emp e,job j,dept d,salarygrade s
WHERE
        e.job_id = j.id AND e.dept_id = d.id AND
            e.salary BETWEEN s.losalary AND s.hisalary
            
            
-- 5.查询部门编号、部门名称、部门位置、部门人数/*
    分析：
            1.部门编号、部门名称、部门位置 dept  |  部门人数 emp
            2.使用分组查询，按照emp.dept_id完成分组，查询count(id)
            3.使用子查询将第二步的查询结果和dept表进行关联查询
*/SELECT    
            t1.id,t1.dname,t1.loc,t2.total
FROM 
            dept t1,(SELECT    
                            dept_id,count(id) total
                FROM
                            emp
                GROUPBY dept_id
            ) t2
WHERE t1.id = t2.dept_id;-- 6.查询所有员工姓名及其直接上级的姓名，没有领导的员工也需要查询/*
    分析：
            1.姓名 emp | 直接上级的姓名 emp    
                            * emp的id和mgr是自关联
            2.条件 emp.id = emp.mgr
            3.查询左表的所有数据，交集数据 （使用左连接）
            
*//*
SELECT    
            t1.ename,t1.mgr,t2.id,t2.ename
FROM 
         emp t1,emp t2
 WHERE 
            t1.mgr = t2.id
*/SELECT    
            t1.ename,t1.mgr,t2.id,t2.ename
FROM 
         emp t1
LEFTJOIN
        emp t2
ON 
            t1.mgr = t2.id

11.事务

事务的基本介绍

概念：

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

操作：

1.开启事务：start transaction；

2.回滚：rollback；

3.提交：commit；

注意：MySql数据库中事务默认自动提交

事务提交的两个方式：

1.自动提交

mysql就是自动提交的

一条MDL（增删改查）语句会自动提交一次事务

2.手动提交

Oracle 数据库默认是手动提交

需要先开启事务，再提交

修改事务的默认提交方式

查看事务的默认提交方式 SELECT @@autocommit; – 1 ：代表自动提交 | 0 ：代表手动提交

修改默认提交方式：set @@autocommit = 0;

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-nNJoOjsn-1659168214135)(C:\Users\30666\AppData\Roaming\Typora\typora-user-images\image-20220730144519426.png)]$

CREATETABLE account(
    id INTPRIMARYKEYauto_increment,`name`VARCHAR(20),
    balance DOUBLE);INSERTINTO account(`name`,balance)VALUES('zhangsan',1000),('lisi',1000);SELECT*FROM account;UPDATE account SET balance =1000;-- 张三给李四转账500元-- 0.开启事务STARTTRANSACTION;-- 1.张三账户 -500UPDATE account SET balance = balance -500WHERE`name`='zhangsan';-- 2.李四账户 +500UPDATE account SET balance = balance +500WHERE`name`='lisi';-- 发现执行没有问题，提交事务COMMIT;-- 发现出问题了，回滚事务ROLLBACK;

事务的四大特征

1.原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败。

2.持久性：如果事务一旦提交或回滚，数据库会持久化的保存数据。

3.隔离性：多个事务之间，相互独立。

4.一致性：事务操作前后，数据总量不变。

事务的隔离级别

概念

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

1.脏读：一个事务读取到另一个事务中没有提交的数据。

2.不可重复读（虚读）：在同一个事务中，两次读取到的数据不一样。

3.幻读：一个事务操作（DML操作）数据表中所有记录，另一个事务添加了一条事务，则一个事务查询不到自己的修改。

隔离级别：

1.read uncommitted：读未提交

产生问题：脏读、不可重复读（虚读）、幻读

2.read committed：读已提交(Oracle默认)

产生问题：不可重复读（虚读）、幻读

3.repeatable read：可重复读(MySql默认)

产生问题：幻读

4.serializable：串行化

可以解决所有的问题

注意：隔离级别从小到大安全性越来越高，但是效率越来越低。

数据库查询隔离级别：

SELECT @@SESSION.transaction_isolation

数据库设置隔离级别：

set global transaction isolation level 级别字符串;

SELECT @@SESSION.transaction_isolationsetglobaltransactionisolationlevelserializable;setglobaltransactionisolationlevelrepeatableread;

1.脏读：一个事务读取到另一个事务中没有提交的数据。

setglobaltransactionisolationlevelreadcommitted;-- 0.开启事务STARTTRANSACTION;-- 1.张三账户 -500UPDATE account SET balance = balance -500WHERE`name`='zhangsan';-- 2.李四账户 +500UPDATE account SET balance = balance +500WHERE`name`='lisi';-- 注意：这里并未提交，但是在另一个打开的图形化界面进行下面查询语句的时候，可以读取到未提交的数据select*from account;-- 当使用了rollback进行回滚之后，数据又回到各自的账务中，这时候在使用查询语句查询各自的账户时均为1000，这便是不可重复读，因为第一次查询时各自的账户分别是500和1500，第二次使用了rollback之后在进行查询时各自的账户分别是1000和1000.

2.不可重复读（虚读）：在同一个事务中，两次读取到的数据不一样。

setglobaltransactionisolationlevelreaduncommitted;-- 0.开启事务STARTTRANSACTION;-- 1.张三账户 -500UPDATE account SET balance = balance -500WHERE`name`='zhangsan';-- 2.李四账户 +500UPDATE account SET balance = balance +500WHERE`name`='lisi';-- 这时只有张三使用了rollback之后，金额才会进入李四的账户中，但是两次查询结果不一致，产生不可重复读

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hDq4RNv1-1659168214136)(C:\Users\30666\AppData\Roaming\Typora\typora-user-images\image-20220730153024677.png)]$

12.DCL

SQL分类：

1.DDL：操作数据库和表

2.DML：操作增删改表中的数据

3.DQL：查询表中的数据

4.DCL：管理用户、授权

DCL

1.管理用户

1.添加用户

2.删除用户

3.修改用户密码

4.查询用户

-- 1.切换到mysql数据库USE mysql;-- 2.查询user表SELECT*FROMuser;-- 创建用户-- CREATE `user` '用户名'@'主机名' IDENTIFIED     BY '密码';CREATEuser'cmk'@'localhost' IDENTIFIED BY'123456';CREATEuser'qx'@'%' IDENTIFIED BY'123456';-- 删除用户-- DROP user '用户名'@'主机名';DROPuser'qx'@'%';

注意：通配符 % 表述可以在任意主机使用用户登录数据库

mysql密码忘了怎么办?

1.cmd --> net stop mysql 需要管理员运行cmd
2.使用无验证方式启动mysql服务：mysqld --skip-grant-tables

启动另外一个窗口直接输入mysql 回车即可登录
登录成功后即可修改密码
use mysql；
update user set password = password('123456') where user='root';

重新启动cmd
net start mysql

mysql -uroot -p123456

2.权限管理

1.查询授权

2.授予权限

3.撤销授权

` = ‘lisi’;

– 注意：这里并未提交，但是在另一个打开的图形化界面进行下面查询语句的时候，可以读取到未提交的数据
select * from account;

– 当使用了rollback进行回滚之后，数据又回到各自的账务中，这时候在使用查询语句查询各自的账户时均为1000，这便是不可重复读，因为第一次查询时各自的账户分别是500和1500，第二次使用了rollback之后在进行查询时各自的账户分别是1000和1000.


> 2.不可重复读（虚读）：在同一个事务中，两次读取到的数据不一样。

```sql
set global transaction isolation level read uncommitted;

-- 0.开启事务
START TRANSACTION;
-- 1.张三账户 -500
UPDATE account SET balance = balance - 500 WHERE `name` = 'zhangsan';
-- 2.李四账户 +500
UPDATE account SET balance = balance + 500 WHERE `name` = 'lisi';

-- 这时只有张三使用了rollback之后，金额才会进入李四的账户中，但是两次查询结果不一致，产生不可重复读

[外链图片转存中…(img-hDq4RNv1-1659168214136)]

12.DCL

SQL分类：

1.DDL：操作数据库和表

2.DML：操作增删改表中的数据

3.DQL：查询表中的数据

4.DCL：管理用户、授权

DCL

1.管理用户

1.添加用户

2.删除用户

3.修改用户密码

4.查询用户

-- 1.切换到mysql数据库USE mysql;-- 2.查询user表SELECT*FROMuser;-- 创建用户-- CREATE `user` '用户名'@'主机名' IDENTIFIED     BY '密码';CREATEuser'cmk'@'localhost' IDENTIFIED BY'123456';CREATEuser'qx'@'%' IDENTIFIED BY'123456';-- 删除用户-- DROP user '用户名'@'主机名';DROPuser'qx'@'%';

注意：通配符 % 表述可以在任意主机使用用户登录数据库

mysql密码忘了怎么办?

1.cmd --> net stop mysql 需要管理员运行cmd
2.使用无验证方式启动mysql服务：mysqld --skip-grant-tables

启动另外一个窗口直接输入mysql 回车即可登录
登录成功后即可修改密码
use mysql；
update user set password = password('123456') where user='root';

重新启动cmd
net start mysql

mysql -uroot -p123456

2.权限管理

1.查询授权

2.授予权限

3.撤销授权

标签： mysql 数据库 sql

本文转载自: https://blog.csdn.net/weixin_50569789/article/details/126073587
版权归原作者 七寻北里 所有，如有侵权，请联系我们删除。

MySql

文章目录

1.数据库的基本概念

2.SQL

3.DDL

4.DML

5.DQL

6.约束

7.多表之间的关系

8.范式

9.数据库的备份和还原

10.多表查询

11.事务

12.DCL

12.DCL

发表评论

“MySql入门教程”的评论:

关于作者

overfit同步小助手

相关阅读

文章导航