MySQL基础篇

数据库基本操作

启动与停止

1.第一种方式：

1>以管理员身份运行cmd

2>在命令行窗口中输入:

启动:net start mysql80

停止:net stop mysql80

2.第二种方式:

1>Win+R快捷方式打开如下：

​ 输入：services.msc

2>找到MySQL80

3>双击：

4>这里我选择的是开机自启动

客户端连接

1.第一种方式：通过MySQL提供的客户端命令行工具

2.第二种方式：通过命令行工具执行命令

mysql [-h 127.0.0.1] [-P 3306] -u 用户 -p

注意：

1.[]中可省略

2.使用这种方式时，需要配置PATH环境变量

SQL

1.DDL(数据定义语言)

数据库操作

查询所有数据库:

show databases;

查询当前数据库:

select database();

创建数据库:

create database [if not exists] 数据库名 [default charset 字符集] [collate 排序规则];

删除数据库:

drop database [if exists] 数据库名;

使用数据库:

use 数据库名;

表操作

查询：

查询当前数据库所有表：

show tables;

查询表结构:

desc 表名;

查询指定表的建表语句:

show create table 表名;

创建：

create table 表名(

        字段1 字段1类型[comment '注释'],

        字段2 字段2类型[comment '注释'],

        字段3 字段3类型[comment '注释'],

        .......

        字段n 字段n类型[comment '注释']

) [comment '注释'];       

修改：

添加字段：

alter table 表名 add 字段名 类型(长度) [comment '注释'] [约束];

修改数据类型：

alter table 表名 modify 字段名 新数据类型(长度);

修改表名：

alter table 表名 rename to 新表名;

删除：

删除表：

drop table [if exists] 表名;

删除指定表并重新创建该表：

truncate table 表名;

---

2.DML(数据操作语言)

添加数据（insert）

给指定字段添加数据：

insert into 表名 (字段1，字段2......) values(值1， 值2......);

给全部字段添加数据：

insert into 表名 values(值1， 值2......);

批量添加数据：

insert into 表名 (字段1，字段2......) values(值1， 值2......),(值1， 值2......),(值1， 值2......);

insert into 表名 values(值1， 值2......),(值1， 值2......),(值1， 值2......);

注意：

​ 1.插入数据时要按注意顺序

​ 2.字符串和日期型数据应该包含在引号中

​ 3.插入的数据大小要合法

修改数据（update）

update 表名 set 字段1=值1，字段2=值2......[where 条件];

注意：

​ 如果没有条件，则会修改整张表

删除数据（delete）

delete from 表名 [where 条件]

注意：

​ 1.如果没有条件，则会删除整张表的数据

​ 2.delete不能删除某一字段的值

---

3.DQL(数据查询语言)

编写顺序：

select 字段列表

from 表名列表

where 条件列表

group by 分组字段列表

having 分组后条件列表

order by 排序字段列表

limit 分页参数;

基本查询

查询多个字段：

select 字段1，字段2，字段3...from 表名;

select * from 表名;

设置别名：

select 字段1[as 别名1]，字段2 [as 别名2]......from 表名;

去除重复记录：

select distinct 字段列表 from 表名;

条件查询

语法：

select 字段列表 from 表名 where 条件列表;

条件：

比较运算符	功能
>
>=
<
<=
=
<> 或 !=	不等于
between…and…	在某个范围之内
in(…)	在in之后的括号中，多选一
like 占位符	模糊匹配（_匹配单个字符，%匹配任意个字符）
is null
and 或 &&	并且
or 或 ||	或
not 或 !	非

eg：

#二、条件查询
#select 字段列表 from 表名 where 条件列表;
#1.查询年龄等于25的员工
select * from emp where age = 25;

#2.查询年龄小于20的员工
select  * from emp where age < 20;

#3.查询没有身份证信息的员工
select * from emp where idcard is null;

#4.查询有身份证信息的员工
select * from emp where idcard is not null;

#5.查询年龄不等于18的员工
select * from emp where age != 18;
select * from emp where age <> 18;

#6.查询年龄在20岁到25岁(包含25)之间的员工信息
select * from emp where age > 20 && age <= 25;
select * from emp where age > 20 AND age <= 25;

#两端都包含
select * from emp where age between 15 and 25;

#7.查询性别为女且年龄小于25的员工信息
select * from emp where gender = '男' && age < 25;

#8.查询年龄等于15或者20或者25的员工
select * from emp where age = 15 || age = 20 || age = 25;
select * from emp where age = 15 or age = 20 or age = 25;
select * from emp where age in(15, 20, 25);

#9.查询姓名为两个字的员工 模糊匹配
select * from emp where name like '___';

#10.查询身份证号最后一位为X的员工
select * from emp where idcard like '%X';

聚合函数

select 聚合函数(字段列表) from 表名;

注意：

​ 对一列进行计算 所有null值不参与聚合函数的计算

函数	功能
count	统计数量
max	最大值
min	最小值
avg	平均值
sum	求和

eg：

#三、聚合函数
#对一列进行计算 所有null值不参与聚合函数的计算
#select 聚合函数(字段列表) from 表名;

#1.统计数量
select count(*) from emp;

#2.统计企业员工的平均年龄
select avg(age) from emp;

#3.最大年龄
select max(age) from emp;

#4.最小年龄
select min(age) from emp;

#5.统计北京地区员工年龄之和
select sum(age) from emp where workaddress = '北京';

分组查询

select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件];

where	**分组之前执行,不满足where条件的不参与分组,where不能对聚合函数进行判断**
having	**分组之后对结果进行过滤,having可以对聚合函数进行判断**

eg：

#1.根据性别分组， 统计男性与女性的数量
select gender, count(*) from emp group by gender;

#2.根据性别分组，统计男性和女性的平均年龄
select  gender, avg(age) from emp group by gender;

#3.查询年龄小于25的员工, 并根据工作地址分组，获取员工数量大于等于3的工作地址
select workaddress, count(*) address_count from emp where age <= 25 group by workaddress having count(*) > 1;

排序查询

select 字段列表 from 表名 order by 字段1 排序方式1, 字段2 排序方式2;

asc	升序（默认）
desc	降序

eg：

#1.根据年龄对公司员工进行升序排序
select * from emp order by age asc;

#2.根据年龄对公司员工进行降序排序
select * from emp order by age desc

#3.根据年龄升序 根据id降序
select * from emp order by age asc, id desc;

分页查询

select 字段列表 from 表名 limit 起始索引, 查询记录数;

eg：

#1.查询第1页员工数据，每页展示2条记录
select * from emp limit 0, 2;

#2.查询第2页员工数据，每页展示10条数据 ------>(页码 - 1)*页展示记录数
select * from emp limit 2, 2;

---

4.DCL(数据控制语言)

管理用户

注意：

​ 主机名可以使用%通配

查询用户：

use mysql;
select * from user;

创建用户：

create user '用户名'@‘主机名’ identified by '密码';

修改用户密码：

alter user '用户名'@'主机名' identified with mysql_native_password by '新密码';

删除用户：

drop user '用户名'@'主机名';

权限控制

权限	说明
all, all privileges	所有权限
select	查询数据
insert	插入数据
update	修改数据
delete	删除数据
alter	修改表
drop	删除数据库/表/视图
create	创建数据库/表

查询权限:

show grants for '用户名'@'主机名';

授予权限:

grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';

注意：

​ 授权时数据库名和表名可以用*进行通配，代表所有

撤销权限:

revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';

---

---

函数

查看函数返回结果

select + 函数;

字符串函数

函数	功能
concat(s1, s2, s3...sn)	拼接
lower(s)	转小写
upper(s)	转大写
lpad(s, n, pad)	左填充，用pad对s进行左填充以达到n个字符
rpad(s, n, pad)	右填充，用pad对s进行右填充以达到n个字符
trim(s)	去掉s头部和尾部的空格
substring(s, st, len)	截取从st开始的len个字符 st从1开始

eg：

#concat
select concat('Karry',' Wang');

#lower
select lower('HELLO');

#upper
select upper('hello');

#lpad
select lpad('01', 9, '-');

#rpad
select rpad('01', 9, '-');

#trim
select trim('  010  101   ');

#substring
select substring('Hello World', 1, 5);

#1
update emp set working=lpad(working, 5, '0');

数值函数

函数	功能
ceil(x)	向上取整
floor(x)	向下取整
mod(x, y)	返回x % y
rand()	返回0~1内的随机数
round(x, y)	求x四舍五入的值，保留y位小数

eg：

#生成一个六位验证码
select rpad(round(rand()*1000000, 0), 6, round(rand()*10, 0));

日期函数

函数	功能
curdate()	返回当前日期
curtime()	返回当前时间
now()	返回当前日期和时间
year(date)	获取date的年份
month(date)	获取date的月份
day(date)	获取date的日期
date_add(date, interval expr type)	返回一个日期加上一个时间间隔expr后的时间值
datediff(date1, date2)	返回起始时间date1和结束时间date2之间的天数(date1 - date2)

eg:

select date_add(now(), interval 70 day);
select date_add(now(), interval 70 month);

select datediff(now(), '2022-9-21');

#根据入职时长倒序排序
select * from emp;
select name, datediff(now(), entrydate) from emp order by datediff(now(), entrydate) desc;

流程函数

函数	功能
if(value, t, f)	如果val为true，返回t，否则返回f
ifnull(value1, value2)	如果val1不为空，返回val1，否则返回val2
case when [val1] then [res1]... else [default] end	如果val1为true，返回res1，否则返回default默认值
case [expr] when [val1] then [res1]...else [default] end	如果expr=val1，返回res1，否则返回default默认值

eg:

select if(true, 'ok', 'no');
select  ifnull('1', '2');
select ifnull(null, 'default');
select case when true then 'ok' else 'no' end;
select case '2' when '2' then 'yes' else 'no' end;

eg:

select name,
       workaddress,
       case workaddress when '南京' then '一线城市'
                        when '重庆' then '一线城市'
                        else '二线城市' end
       as citydiffer
from emp;

eg:

select
    id,
    name,
    (case when math >= 85 then '优秀' when math >= 60 then '及格' else '不及格' end) as '数学',
    (case when English >= 85 then '优秀' when English >= 60 then '及格' else '不及格' end) as '英语',
    (case when Chinese >= 85 then '优秀' when Chinese >= 60 then '及格' else '不及格' end) as '语文'
from score;

---

约束

概述

概念：

​ 约束是作用于表中字段上的规则，用于限制存储在表中的数据

​ 约束可以在创建表/修改表的时候添加

目的：

​ 保证数据库中数据的正确性、有效性和完整性

分类：

约束	描述	关键字
非空约束	限制该字段的数据不能为null	not null
唯一约束	保证该字段的所有数据都是唯一的、不重复的	unique
主键约束	主键是一行数据的唯一标识，要求非空且唯一	primary key (自增:auto_increment)
默认约束	保存数据时，如果未指定该字段的值，则采用默认值	default
检查约束	保证字段值满足某一条件	check
外键约束	用来让两张表的数据之间建立连接，保证数据的一致性和完整性	foreign key

约束演示

create table user(
    id int primary key auto_increment comment '主键',
    name varchar(10) not null unique comment '姓名',
    age int check ( age > 0 && age <= 120 ) comment '年龄',
    status char(1) default '1' comment '状态',
    gender char(1) comment '性别'
) comment '用户表';

外键约束

概念

​ 子表（从表）：具有外键的表

​ 父表（主表）：外键所关联的表

语法

添加外键

sql语句添加：

alter table (从表) add constraint 外键名称 foreign key (外键字段名) references 主表(主键);

创建表时添加：

create table emp1(
    id int primary key auto_increment comment '主键',
    name varchar(10) not null unique comment '姓名',
    age int check ( age > 0 && age <= 120 ) comment '年龄',
    job varchar(20) comment '职位',
    salary int comment '薪资',
    entrydate date comment '入职时间',
    managerid int comment '直属领导id',
    dept_id int comment '部门id',
    constraint dept_id_fk foreign key (dept_id) references dept(id)
)comment '员工表';

eg：

#添加外键
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);

删除外键

alter table 从表 drop foreign key 外键名称；

eg：

#删除外键
alter table emp drop foreign key fk_emp_dept_id;

指定外键删除/更新行为

行为	说明
not action / restrict	默认行为，当父表删除或更新记录时，如果当前记录对应的父表与子表有关联，则不予删除或更新
cascade	当父表删除或更新记录时，如果有外键关联父表与子表，则也删除或者更新外键在子表中的记录
set null	当父表删除或更新记录时，如果有外键关联父表与子表，则设置子表中该外键为null
set default	当父表删除或更新记录时，如果有外键关联父表与子表，则设置子表中该外键为默认值（innodb不支持）

alter table (从表) add constraint 外键名称 foreign key (外键字段名) references 主表(主键) on update 行为 on delete 行为;

eg：

#指定外键更新或删除行为
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on update cascade on DELETE cascade;

example：

#主表
create table dept(
    id int auto_increment primary key comment 'id',
    name varchar(50) not null comment '部门名称'
)comment '部门表';

#从表
create table emp(
    id int primary key auto_increment comment '主键',
    name varchar(10) not null unique comment '姓名',
    age int check ( age > 0 && age <= 120 ) comment '年龄',
    job varchar(20) comment '职位',
    salary int comment '薪资',
    entrydate date comment '入职时间',
    managerid int comment '直属领导id',
    dept_id int comment '部门id'
)comment '员工表';

#插入数据
insert into dept(name) values ('研发部'), ('市场部'), ('销售部'), ('外交部');
insert into emp(id, name, age, job, salary, entrydate, managerid, dept_id) values
                                                                               (1, '成朗', 24, '外交官', 5000, '1-1-1', 1, 4),
                                                                               (2, '庄文杰', 18, '学生', 3000, '1-1-1', 2, 2);
#外键约束
#添加外键
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);
#删除外键
alter table emp drop foreign key fk_emp_dept_id;
#指定外键更新或删除行为
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on update cascade on DELETE cascade;

---

多表查询

多表关系

一对多（多对一）

在多的一方建立外键，指向一的一方的主键

多对多（中间表）

建立第三张中间表，中间表至少包含两个外键，分别关联多方主键

一对一（单表拆分）

在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的（unique）

多表查询概述

笛卡尔积

两个集合所有组合的情况

#多表查询
select * from emp, dept;

消除无效笛卡尔积

#多表查询
select * from emp, dept where emp.dept_id = dept.id;

多表查询分类

连接查询

外连接

左外连接：

​ 查询左表所有数据，以及两张表交集部分数据

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

右外连接：

​ 查询右表所有数据，以及两张表交集部分数据

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

eg:

#左外连接
select e.*, d.name from emp e left outer join dept d on e.dept_id = d.id;
#右外连接
select d.*, e.* from emp e right outer join dept d on e.dept_id = d.id;

内连接

​ 相当于查询两张表交集部分数据

隐式内连接

select 字段列表 from 表1, 表2 where 条件...;

显式内连接

select 字段列表 from 表1 [inner] join 表2 on 连接条件...;

eg:

#显式内连接
select emp.name, dept.name from emp, dept where emp.dept_id = dept.id;
#隐式内连接
select emp.name, dept.name from emp inner join dept where emp.dept_id = dept.id;

自连接

​ 当前表与自身的连接查询，自连接必须使用表别名

select 字段列表 from 表1 别名1 join 表1 别名2 on 条件...;

eg:

#自连接
select e1.name, e2.name from emp e1 join emp e2 on e1.managerid = e2.managerid;
select e1.*, e2.name from emp e1 left outer join emp e2 on e1.managerid = e2.managerid;

联合查询

union查询

把多次查询的结果合并起来，形成一个新的查询结果集

union:

​ 对查询结果进行去重操作

union all:

​ 将查询结果直接输出

注意：

​ 联合查询的多张表字段列表个数及类型必须保持一致，查询结果的字段名称为第一次查询字段名

select 字段列表 from 表1 ... union [all] select 字段列表 from 表2 ...;

eg:

#联合查询
select emp.name, emp.age, emp.salary from emp where salary <= 5000 union
                                                          select emp.name, emp.age, emp.salary from emp where age >= 10;

子查询（嵌套查询）

概念

​ SQL语句中嵌套select语句，成为嵌套查询（子查询）

语法

insert/update/delete/select * from 表1 where column1 = (select column1 from 表2);

分类

按子查询结果：

标量子查询，列子查询， 行子查询，表子查询

按子查询位置

where之后、from之后、select之后

标量子查询（单个值）

​ 子查询返回结果为单个值（数字，字符串，日期……）

​ 常用操作符：= 等于

​ <> 不等于

​ > 大于

​ >= 大于等于

​ < 小于

​ <= 小于等于

eg：

select * from emp where dept_id = (select id from dept where name = '外交部');
select * from emp where entrydate >= (select entrydate from emp where name = '成朗');

列子查询（一列）

​ 子查询返回结果为一列（可以是多行）

​ 常见操作符：

​ in 在指定的集合范围之内，多选一

​ not in 不在指定集合范围之内

​ any 子查询返回列表中，有任何一个满足即可

​ some 与any相同

​ all 子查询返回列表的所有值都必须满足

eg：

select * from emp where dept_id in (select id from dept where name = '外交部' or name = '销售部');
select * from emp where salary > all(select salary from emp where dept_id = (select id from dept where name = '外交部'));
select * from emp where salary > some(select salary from emp where dept_id = (select id from dept where name = '销售部'));

行子查询（一行）

​ 子查询返回结果为一行

​ 常见操作符：

​ = 等于

​ <> 不等于

​ in 在集合范围之内

​ not in 不在集合范围之内

eg：

select * from emp where (salary, managerid) = (select salary, emp.managerid from emp where name = '成朗');

表子查询（多行多列）

​ 子查询返回结果为多行多列

​ 常见操作符：

​ in 在集合范围之内

​

eg：

select  * from emp where (salary, dept_id) in (select salary, dept_id from emp where name = '成朗' or name = '庄文杰');
select e.*, dept.name from (select * from emp where entrydate >= '1-1-1') e, dept where e.dept_id = dept.id;
select e.*, d.* from (select * from emp where entrydate >= '1-1-1') e left join dept d on e.dept_id = d.id;

---

事务

简介（来自百度百科）

概念：数据库事务( transaction)是访问并可能操作各种数据项的一个数据库操作序列，这些操作要么全部执行,要么全部不执行，是一个不可分割的工作单位。事务由事务开始与事务结束之间执行的全部数据库操作组成

注意：默认MySQL的事务是自动提交的，也就是说，当执行一条DML语句，MySQL会立即隐式的提交事务

操作（控制事务）

查看/设置事务提交方式

#为1是自动提交 为0是手动提交
select @@autocommit; #查看事务提交方式
set @@autocommit = 0; #设置事务提交方式

为1表示当前事务提交方式为自动提交

为0表示当前事务提交方式为手动提交

提交事务

#提交事务 : 当设置了手动提交方式后，必须有commit指令
commit;

回滚事务

#回滚事务 : 当出现异常，可以回滚事务
rollback;

开始事务

#开启事务
start transaction;

#开启事务
begin;

四大特性（ACID）

原子性

事务是不可分割的最小操作单位元，要么全部成功，要么全部失败

一致性

事务完成时，必须使所有数据都保持一致状态

隔离性

数据库系统提供的隔离机制，保证事务在不受外部并发操作影响到独立环境下运行

持久性

事务一旦提交或回滚，它对数据库的改变就是永久的

并发事务问题

问题	描述
脏读	一个事务读到另外一个事务还没有提交的数据
不可重复读	一个事务先后读取同一条记录，但两次读取到的数据不同
幻读	一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了“幻影”

事务隔离级别

事务的隔离级别越高，数据越安全，但是性能越低

分类(隔离级别从低到高)：

隔离级别	脏读	不可重复读	幻读
read uncommitted	Y	Y	Y
read committed (Oracle默认)	N	Y	Y
repeatable read (MySQL默认)	N	N	Y
serialzable	N	N

注意： Y表示在当前隔离级别下，当前某种并发事务问题会出现，反之，N表示不会出现

查看事务隔离级别：

#查看事务隔离级别
select @@transaction_isolation;

设置事务隔离级别：

#设置事务隔离级别
set [session|global] transaction isolation level {read uncommitted | read committed | repeatable read| serializable};

eg:

set session transaction isolation level serializable;
commit;
select @@transaction_isolation;

注意：

session: 只对当前客户端窗口有效
global:对所有客户端的绘画窗口都有效

演示

打开cmd连接mysql，打开两个窗口进行演示

脏读

一个事务读取到了另外一个事务未提交的数据

不可重复读

同样的SQL在同一个事务中查询出来的数据不一样

可重复读

同样的SQL在同一个事务中查询出来的数据一致

幻读

插入数据的时候提示重复，查询却提示为空

规避幻读(serializable)

右侧光标闪烁表示堵塞，在等待左侧事务提交了才能继续执行

---

如有错误，欢迎指正！！！

所有笔记总结目录-CSDN博客