MySQL基础知识--SQL语句、函数、约束、多表查询以及事务-CSDN博客

本文链接：https://blog.csdn.net/qq_46694178/article/details/140286675

MySQL基础

SQL

数据操作语言DDL

数据库操作

显示所有数据库
show databases;
显示当前被选中的数据库
select database();
创建名为learn_database的数据库
create database learn_database;

show databases;

创建数据库之前先查询是否存在同名数据库
create database if not exists learn_database;
删除数据库，在删除之前查询是否存在，不存在时不会报错
drop database if exists learn_database ;
创建数据库并指定默认字符集为utf8mb4（支持4字节的utf8）
create database learn_database default charset utf8mb4;
使用名为learn_database的数据库，先进入数据库才能进行后续的表操作
use learn_database;

表操作

显示当前所在数据库所有表名
show tables;
创建名为userInfo的表

create table userInfo(
						id int comment '编号',
						ame varchar(50) comment '姓名',
						age int comment '年龄',
						gender varchar(1) comment '性别')comment '用户信息';

详细展示表的结构
desc userInfo;
删除名为userInfo的表
drop table if exists userInfo;
删除并重新创建表，即清空表内所有数据但是保存表结构
truncate table userInfo;
查看表创建时的细节（创建语句）
show create table userInfo;

create table staffInfo (id int comment '员工编号',
						jobNum varchar(10) comment '工号',
						jobName varchar(10) comment '姓名',
						gender char(1) comment '性别',
						age tinyint unsigned comment '年龄',
						idcardNum char(18) comment '身份证号',
						entryTime date comment '入职时间') comment '员工信息表';

desc staffInfo;

修改表格->增加字段
alter table staffInfo add nickName varchar(10) comment '昵称';
修改表格->修改字段的类型
alter table staffInfo modify jobNum varchar(16);
修改表格->修改字段名和类型
alter table staffInfo change jobNum workerNum varchar(10) comment '工人工号';
修改表格->删除字段
alter table staffInfo drop workernum;
修改表格->对表格进行重命名
alter table staffInfo rename to staffInformation;

desc staffInformation;

数据操作语言DML

添加数据 INSERT

指定字段添加数据
insert into 表名 (字段1，字段2，字段3) values (值1，值2，值3);
全部字段添加数据
insert into 表名 values (值1，值2，...);

修改数据 UPDATE
UPDATE 表名 SET 字段名1 = 值1，字段名2 = 值2 WHERE 条件;
删除数据 DELETE
DELETE FROM 表名 WHERE 条件;

数据查询语言DQL

基本查询
desc lg_machineinfo;

选择单个字段
select id from lg_machineinfo;
选择多个字段
select id, UNIT_CODE from lg_machineinfo;
选择全部内容
select * from lg_machineinfo;
为字段别名，as可以省略
select id as id_machine from lg_machineinfo;
去重查询
select distinct UNIT_TYPE from lg_machineinfo;

条件查询

普通查询
select * from lg_machineinfo where UNIT_CODE = 'BA';
使用and条件查询
select * from lg_machineinfo where UNIT_CODE = 'BA' and UNIT_TYPE = 'B';
使用or条件查询
select * from lg_machineinfo where UNIT_CODE = 'BA' or UNIT_CODE = 'BB';
同一个字段的or查询可以替代为in，用列表框起字段值
select * from lg_machineinfo where UNIT_CODE in ('BA','BB');
占位符like，进行模糊匹配，"%“是不管长度如何，”_"是一个字符的位置

select * from lg_machineinfo where UNIT_CODE like '%A';
select * from lg_machineinfo where UNIT_CODE like '_A';

聚合函数->作用于一列

count->统计数量（null值不参与统计）
select count(*) from lg_machineinfo;
avg ->统计平均值
select avg(PRE_DAY) from lg_machineinfo;
min ->统计最小
select min(PRE_DAY) from lg_machineinfo;
max ->统计最大
select max(PRE_DAY) from lg_machineinfo;
sum ->统计求和
select sum(PRE_DAY) from lg_machineinfo;

分组查询

select 字段 from 表名 [where 条件] group by 字段 [having 分组后过滤条件] ，执行顺序 where > 聚合 > having
select count(*) from lg_machineinfo where UNIT_TYPE = 'B' group by UNIT_CODE having count(*) = 1;

排序查询

select 字段名 from 表名 order by 字段1 排序方式1，字段2 排序方式2；排序方式：升序-> asc,降序-> desc;
select * from lg_machineinfo order by OMEGA asc, PRE_DAY desc; 先按照第一个排序，若相同则按照第二个排序

分页查询

elect 字段名 from 表名 LIMIT 起始索引，查询记录数; 起始索引从0 开始，起始索引 = （查询页码 - 1）* 每页显示记录数；
select * from lg_machineinfo limit 10,10;

顺序

编写顺序
SELECT 字段列表 FROM 表名列表 WHERE 条件列表 GROUP BY 分组字段列表 HAVING 分组后条件列表 ORDER BY 排序字段列表 LIMIT 分页参数
执行顺序
FROM 表名列表 WHERE 条件列表 GROUP BY 分组字段列表 HAVING 分组后条件列表 SELECT 字段列表 ORDER BY 排序字段列表 LIMIT 分页参数

数据控制语言DCL

管理用户

查询用户
use mysql;
select * from user;
创建用户
create user '用户名'@'主机名' identified by '密码';
修改用户密码
alter user '用户名'@'主机名' identified with nysql_native_password by '新密码';
删除用户
drop user '用户名'@'主机名';
管理访问权限
在当前主机访问数据库：‘用户名’@‘localhost’；
在任意主机访问数据库：‘用户名’@‘%’

权限控制
…

函数

字符串函数

concat(s1,s2,s3)：将字符串s1,s2,s3进行拼接；
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个长度的字符串

数值函数

CEIL(x)：向上取整；
FLOOR(x)：向下取整；
MOD(x,y)：返回x/y的模，即取余；
RAND()：返回0~1内的随机数；
ROUND(x,y)：求参数x的四舍五入的值，保留y位小数；
生成六位随机数：select lpad(round(rand() * 1000000 , 0), 6, 0);

日期函数

CURDATE()：返回当前日期；
CURTIME()：返回当前时间；
NOW()：返回当前日期和时间；
YEAR(date)：获取指定date的年份；
MONTH(date)：获取指定date的月份；
DAY(date)：获取指定date的日期；
DATE_ADD(date, INTERVAL expr type)：返回一个日期/时间值加上一个时间间隔expr后的时间值；
select date_add(curdate(),interval 70 day);
DATEDIFF(date1,date2)：返回起始时间date1和结束时间date2之间的天数；
select datediff(date_add(curdate(),interval 70 day),curdate());

流程函数

IF(value ,t, f)如果value为true，则返回t，否则返回f
IFNULL(value1 , value2)如果value1不为空，返回value1，否则返回value2
CASE WHEN [vall ] THEN [res] … ELSE[ default ] END如果vall为true，返回res1，…否则返回default默认值
CASE [expr ] WHEN [vall ] THEN [res1] … ELSE[ default] END如果expr的值等于vall，返回resl，…否则返回default默认值

约束

NOT NULL:
非空约束限制该字段的数据不能为null
UNIQUE:
唯一约束保证该字段的所有数据都是唯一、不重复的
PRIMARY KEY:
主键约束主键是一行数据的唯一标识，要求非空且唯一
DEFAULT:
默认约速保存数据时，如果未指定该字段的值，则采用默认值
CHECK:
检查约束(8.0.16版本之后)保证字段值满足某一个条件

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

FOREIGN KEY:
外键约束用来让两张表的数据之间建立连接，保证数据的一致性和完整性(删除具有外键关联的表中数据时会报错).
添加外键：
alter table tableName add constrain fk_tableName_id foreign key (tableName_id) references table2(id);
删除外键：
alter table tableName drop foreign key fk_tableName_id;
指定更新、删除规则(更新或者删除记录时，同时更新或删除外键子表中的记录CASCADE):
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGNKEY（外键字段）REFERENCES 主表名（主表字段名）ON UPDATE CASCADE ON DELETE CASCADE;
指定更新、删除规则(删除记录时，将外键子表中的记录的该外键值置null,SET NULL):
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGNKEY（外键字段）REFERENCES 主表名（主表字段名） ON DELETE SET NULL;\

多表查询

多表关系
一对一、一对多、多对多
连接查询

内连接
查询两张表交集的部分。
select 字段名 FROM 表1, 表2 where 条件...;
外连接
左外连接：表1的全部数据（包含表1与右表交集部分的数据）.
select 字段名 from 表1 left [outer] join 表2 on 条件...;
select emp.*,dept.name from emp left outer join dept on emp.dept_id = dept.id
右外连接：表2的全部数据（包含左表与表2交集部分的数据）.
select 字段名 from 表1 right [outer] join 表2 on 条件...;
自连接
把一张表看作两张表
使用内连接：(必须对表起别名)
select 字段名 FROM 表1, 表2 where 条件...;
select t1.name, t2.name from table1 t1, table2 t2 where t1.id = t2.manager.id;
使用外连接：
select t1.name, t2.name from table1 t1 left outer join table2 t2 on t1.id = t2.manager.id;

联合查询
将多次查询到的结果合并：
select 字段名 from 表1 ... union [all] select 字段名 from 表2...;
子查询
SQL语句中嵌套select语句，称为嵌套查询又称子查询
select * from table1 where column1 = (select column1 from table2);

事务

一组操作的集合，事务会把所有操作作为一个整体向系统提交或者撤销，要么全部成功，要么全部失败。

两种手动提交的方式

修改事务提交方式为手动
查询MySQL事务提交方式，若1则自动提交，若0则手动提交：
select @@autocommit;
设置MySQL事务提交方式：
set @@autocommit = 0;
手动提交事务方式：commit; 回滚操作：rollback;
自动提交方式下，手动开启事务
开启事务：start transaction;/begin;
提交事务：commit;
回滚操作：rollback;

事务的四大特性

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

并发事务问题

脏读：一个事务读到另外一个事务还没有提交的数据。
不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同（该事务两次读取过程中被另一个并发事务修改，例如先查询后插入，查询时无该数据，插入有该数据而失败），称之为不可重复读。
幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了”幻影"。