MYSQL数据库基础

MYSQL数据库

  • 关系型数据库
    • 通过表结构存储数据的数据库就是关系型数据库

SQL

  • 以分号结尾
  • SQL语句不区分大小写，关键字建议用大写
  • 注释：
    • 单行注释：使用 -- 或 #
    • 多行注释：/ * * /
  1. DDL：数据定义语句，用来定义数据库对象（数据库，表，字段）
  2. DML：数据操作语言，用来对数据库表中的数据进行增删改
  3. DQL：数据查询语言，用来查询数据库中表的记录
  4. DCL：数据控制语言，用来创建数据库用户，控制数据库的访问权限

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 字段1注释],
	字段2 字段2类型[comment 字段2注释],
	字段3 字段3类型[comment 字段3注释]
	)[comment 表注释];
	
#添加字段
alter table 表名 add 字段名 类型 [comment 注释][约束];

#修改数据类型
alter table 表名 modify 字段名 新数据类型;
#修改字段名和字段类型
alter table 表名 change 旧字段名 新字段名 类型[comment 注释][约束]; 

#删除表
drop table[if exists] 表名;
#删除指定表，并且重新创建该表,相当于清空
truncate table 表名;
#删除字段
alter table 表名 drop 字段名;

#修改表名
alter table 表名 rename to 新表名;
  • 数据类型：
    • 无符号：unsigned

DML(数据操作语言)

  • insert
  • update
  • delete
#添加数据时，指定的字段顺序需要与值的顺序是一一对应的
#给指定字段添加数据
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...)...;

#修改数据
update 表名 set 字段名1 = 值1,字段名2 = 值2,...[where 条件];
 
#删除数据
delete from 表名 [where 条件];

DQL(数据查询语言)

  • 编写顺序

select +字段列表

from +表名列表

where +条件列表

group by +分组字段列表

having +分组后的条件列表

order by +排序字段列表

limit +分页参数

  • 查询所有字段数据时，*尽量少用，尽量给出所有的字段名

  • 基础查询

#查询多个字段
select 字段1,字段2...from 表名;
select * from 表名;
#设置别名
select 字段1[as 别名1],字段2[as 别名2]...from 表名;#其中as可以省略
#去除重复记录
select distinct 字段列表 from 表名;
  • 条件查询（where）
select 字段列表 from 表名 where 条件列表;

#模糊匹配(_匹配单个字符，%匹配任意个字符)
#查询姓名为两个字的员工信息
select * from 表名 where 字段名 like '__';
#查询身份证号码最后一位为X的信息
select * from 表名 where 字段名 like '%X';
  • 聚合函数(将一列数据作为一个整体，进行纵向计算)，null值不参与聚合函数的计算
select 聚合函数(字段列表) from 表名;
#统计数量(count)
select count(*) from 表名;
#统计平均值(avg)
select avg(字段名) from 表名;
#统计最大值(max)
select max(字段名) from 表名;
#统计最小值(min)
select min(字段名) from 表名;
#统计值的和(sum)
select sum(字段名) from 表名;
  • 分组查询（group by）
  • where与having的区别：
    • 执行时机不同，where是分组之前进行过滤，不满足where条件，不参与分组。having是分组之后对结果进行过滤。
    • 判断条件不同：where不能对聚合函数进行判断，二having可以。
  • 执行顺序：where > 聚合函数 > having
  • 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无意义。
select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后的过滤条件];
#例：
#根据性别进行分组，统计男性员工和女性员工的数量
select sex,count(*) from 表名 group by sex;
#根据性别进行分组，统计男性员工和女性员工的平均年龄
select sex,avg(age) from 表名 group by sex;
#查询年龄小于45的员工，并根据工作地址进行分组，获取员工数量大于等于3的工作地址
select 工作地址,count(*) from 表名 where age<45 group by 工作地址 having count(*)>=3;
  • 排序查询（order by）
  • 升序：asc(默认值)
  • 降序：desc
select 字段列表 from 表名 order by 字段1 排序方式1,字段2 排序方式2;
  • 分页查询（limit）
select 字段列表 from 表名 limit 起始索引,查询记录数;
#查询第1页数据，每页展示10条记录
select * from 表名 limit 0,10;#其中0可以省略
#查询第2页数据，每页展示10条数据
select * from 表名 limit 10,10;
  • 举例总结DQL：
#查询年龄为 20,21,22,23 岁的女性员工信息。
select * from 表名 where sex='女' and age in(20,21,22,23);
#查询性别为男，并且年龄在 20-40 岁 (含) 以内的姓名为三个字的员工。
select * from 表名 where sex='男' and age between20 and 40 and name like '___';
#统计员工表中，年龄小于 60 岁的，男性员工和女性员工的人数。
select sex,count(*) from 表名 where age<60 group by sex;
#查询所有年龄小于等于 35 岁员工的姓名和年龄，并对查询结果按年龄升序排序，如果年龄相同按入职时间降序排序。
select name,age from 表名 where age<=35 order by age asc ,time desc;
#查询性别为男，且年龄在 20-40 岁 (含) 以内的前 5 个员工信息，对查询的结果按年龄升序排序，年龄相同按入职时间升序排序
select * from 表名 where sex='男' and age>=20 and age <=40 order by age asc,time asc limit 5;

  • DQL语句执行顺序

    from +表名列表

    where +条件列表

    group by +分组字段列表

    having +分组后的条件列表

​ select +字段列表

​ order by +排序字段列表

​ limit +分页参数

DCL（数据控制语言）

  • 管理用户
use mysql;
#查询用户
select * from user;

#创建用户
create user '用户名'@ '主机名' identified by '密码';

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

#删除用户
drop user '用户名'@ '主机名';
  • 权限控制
#查询权限
show grants for '用户名'@ '主机名';
#授予权限
grant 权限列表 on 数据库名.表名 to '用户名'@ '主机名';
#撤销权限
revoke 权限列表 on 数据库名.表名 from '用户名'@ '主机名';

函数

字符串函数

  • concat (s1,s2...) 将这些字符串拼接成一个字符串
  • lower(str) 将字符串str全部转为小写
  • upper(str) 将字符串str全部转为大写
  • lpad(str,n,pad) 左填充，用字符串pad对str的左边进行填充，达到n个字符串长度
  • rpad(str,n,pad) 右填充，用字符串pad对str的右边进行填充，达到n个字符串长度
  • trim(str) 去掉字符串头部和尾部的空格
  • substring(str,start,len) 返回字符串str 从start 位置起的len个长度的字符串

数值函数

  • cell(x) 向上取整
  • floor(x) 向下取整
  • mod(x,y) 返回x/y的模
  • rand() 返回0~1内的随机数
  • round(x,y) 求参数x的四舍五入的值，保留y位小数

格式：select cell(....)

日期函数

  • curdate() 返回当前日期（年月日）
  • curtime() 返回当前时间（时分秒）
  • now() 返回当前日期和时间
  • year(date) 获取指定date的年份
  • month(date) 获取指定date的月份
  • date_add(date,interval expr type) 返回上一个日期/时间值加上一个时间间隔expr后的时间值。//其中type是时间类型（年月日），expr是时间数值【
  • datediff(date1,date2) 返回起始时间date1和结束时间date2 之间的天数

格式：select curdate();

流程控制函数

  • if(value,t,f) 如果value为true 则返回t ,否则返回f
  • ifnull(value1,value2) 如果value1不为空 返回value1 ,否则返回value2
  • 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 默认值
#示例
#查询表中的员工姓名和工作地址，工作地址为北京，上海就返回“一线城市”，否则返回“二线城市”
select name,
		case address when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end
from 表名;

约束

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

  • 非空约束 not null

  • 唯一约束 unique

  • 主键约束 primary key

  • 默认约束 default

  • 检查约束 check()

    • 例：年龄0~120 ------> age int check(age>0 && age<=120)

  • 自动增长：auto_increment

  • 外键约束 foreign key

    • 让两张表的数据之间建立连接，从而保证数据的一致性和完整性
    • 添加外键
    create table 表名(
	字段名 数据类型,
    ....
    [constraint] [外键名称] foreign key(外键字段名) references 主表(主表列名)
);

alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表(主表列名);
    • 删除外键
    alter table 表名 drop foreign key 外键名称;

  • cascade 级联，删除或更新主表当中的数据，从表中对应的数据也会发生改变

alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表(主表列名) on update cascade on delete cascade;
  • set null ：在主表（父表）中删除对应记录时，检查是否有对应外键，如果有则设子表中外键值为null（这时需要求外键允许取null）
alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表(主表列名) on update set null on delete set null;

多表查询

  • 多表关系：

    • 一对一：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的（unique）
    • 一对多：在多的一方建立外键，指向一的一方的主键
    • 多对多：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

  • 笛卡尔积：A集合和B集合的所有组合情况

#为了消除访问两个表出现的笛卡尔积情况
select * from A,B where A.id=B.id;#将关联的两个字段（实际上数值是一样的）设置为相等，作用是排除因为笛卡尔积导致的多余的无用的数据

内连接

  • 查询的是两张表交集的部分
#隐式内连接
select 字段列表 from 表1,表2 where 条件...;

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

外连接

#左外连接,相当于查询左表的所有数据，包含表1和表2的交集部分的数据
select 字段列表 from 表1 left [outer]join 表2 on 条件...;

#右外连接,相当于查询右表的所有数据，包含表1和表2的交集部分的数据
select 字段列表 from 表1 right [outer]join 表2 on 条件...;

自连接

  • 自连接查询可以是内连接，也可以是外连接
select 字段列表 from 表1 别名A join 表1 别名B on 条件...;

联合查询(union,union all)

  • 联合查询多张表的语句的返回列数要一致，字段类型也要一致
select 字段列表 from 表1...
union[all]
select 字段列表 from 表2...;

#union all直接将两种条件查询的结果合并
#union会将两种条件查询的结果去重

子查询

  • SQL语句(可以是select，update，insert，delete）中嵌套select语句，称为嵌套查询，即子查询
select * from 表名1 where 字段名1=(select 字段名1 from 表名2);

#标量子查询，返回结果为单个值
#例：查询“芒果”入职之后的员工信息
select * from 表名 where date > (select date from 表名 where name='芒果');#子查询语句查询到的是‘芒果’的入职日期

#列子查询，返回结果为一列
#例：查询“销售部”和“市场部”的所有员工信息
select * from 员工表 where 部门id in (select id from 部门表 where name='销售部' or name='市场部');

#行子查询，返回结果为一行
#例：查询与“芒果”职位和薪资相同的员工信息
select * from 员工表 where (职位，薪资) = (select 职位,薪资 from 员工表 where name='芒果');

#表子查询，返回结果为多行多列，即一张表
#例：查询与“芒果”,和“阿雨”职位和薪资相同的员工信息
select * from 员工表 where (职位，薪资) in (select 职位,薪资 from 员工表 where name='芒果' or name='阿雨');
  • 案例：
#员工表为A，部门表为B。信息为：id,name,age,job,salary,entrydate,managerid(直属领导),dept_id(部门id)

#查询员工的姓名、年龄、职位、部门信息。(隐式内连接)
select A.name,A.age,A.job,B.name from A,B where A.dept_id=B.id;

#查询年龄小于 30 岁的员工姓名、年龄、职位、部门信息。(显式内连接)
select A.name,A.age,A.job,B.name from A inner join B on A.dept_id=B.id where A.age<30;

#查询拥有员工的部门ID、部门名称。(去重)
select distinct B.id,B.name from A,B where A.dept_id=B.id;

#查询所有年龄大于 40 岁的员工，及其归属的部门名称；如果员工没有分配部门，也需要展示出来。(外连接)
select A.*,B.name from A left join B on A.dept_id=B.id where A.age>40;


#等级表C，信息为：grade,losal(最低工资),hisal(最高工资)。
#查询所有员工的工资等级。
select A.name , C.grade from A ,C where A.salary between B.losal and B.hisal;

#查询 "研发部" 所有员工的信息及工资等级。
select A.* , C.grade from A,C,B where (A.salary between B.losal and B.hisal) and A.dept_id=B.id and B.name='研发部';

#查询 "研发部" 员工的平均工资。
select avg(A.salary) from A,B where A.dept_id=B.id and B.name='研发部';

#查询工资比 "灭绝" 高的员工信息。
select * from A where salary>(select salary from A where name='灭绝');

#查询比平均薪资高的员工信息。
select * from A where salary>(select avg(salary) from A);

#查询低于本部门平均工资的员工信息。
select * from A2  where A2.salary<(select avg(A1.salary) from A1 where A1.dept_id=A2.dept_id);

#查询所有的部门信息，并统计部门的员工人数。
select id,name,(select count(*) from A where dept_id=id) from B ;

事务

  • 事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功要么同时失败。
#查看/设置事务提交方式
select @@autocommit;#当前事务自动提交
set @@autocommit=0;#当前事务手动提交

#提交事务
commit;

#回滚事务
rollback;

#开启事务
start transaction;
#例：(手动提交情况)
	set @@autocommit=0;#当前事务手动提交
	#一系列操作
	....
	....
	....
	commit;#提交事务
	#如果程序报错，那么手动执行回滚事务，不报错就手动执行提交事务
	rollback;
#例：(自动提交情况)
	select @@autocommit;#当前事务自动提交
	#开启事务
	start transaction;
	#一系列操作
	....
	....
	....
	commit;#提交事务
	#如果程序报错，那么手动执行回滚事务，不报错就手动执行提交事务
	rollback;

  • 四大特性：

    • 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败
    • 一致性（Consistency）：事务完成时，必须使所有数据全部保持一致状态
    • 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行
    • 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的

  • 并发事务问题：

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

  • 事务隔离级别

屏幕截图 2025-12-19 123616

  • 其中打勾的事务并发问题会出现
  • Serializable隔离级别高，但性能最差
  • Read uncommitted 性能最高，但安全性最差
  • 事务隔离级别越高，数据越安全，但是性能越低
#查看事务隔离级别
select @@transaction_isolation;

#设置事务隔离级别
set [session|global] transaction isolation level {read umcommitted | read committed | repeatable read | Serializable}#session会话级别，global全局级别
posted @ 2025-12-19 13:30  芒果学代码  阅读(5)  评论(0)    收藏  举报