Mysql知识点复习

最新推荐文章于 2023-03-14 18:50:53 发布

liuhan_424

最新推荐文章于 2023-03-14 18:50:53 发布

阅读量287

点赞数 1

分类专栏： mysql

本文链接：https://blog.csdn.net/qq_43226113/article/details/100089991

版权

mysql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

服务器登录退出

mysql -u root -paaa
exit #或者 ctrl+c

mysql的常见命令

show databases;  # 查看当前所有数据库
use 库名;  # 使用指定数据库
show tables;  # 查看当前库的所有表
show tables from 库名;   # 查看其他库的所有表
desc 表名;  # 查看表结构
select version();   # 查看版本（登录服务器后）
mysql --version   # 查看版本（没有登录服务器）
mysel --V

基础

一、DQL(data query language)

1.基础查询

 select 查询字段 from 表名;`

①起别名：使用as 或者空格
②去重：使用distinct关键字
③+号的作用：运算符，不可以做连接操作
（如果字符转成数字失败，则转成0，如果有一方是null，则结果为null）
④将两个字段拼接在一起用concat函数
2.条件查询

 select 查询列表
 from 表名
 where 筛选条件;

（1）分类：
①按条件表达式筛选： > < = != <> >= <=
②按逻辑表达式筛选：and or not
③模糊查询：like、between and、in、is null

（2）安全等于：<=>

is null pk <=>:
①is null：仅仅可以判断NULL，可读性较高
②<=>：既可以判断NULL，又可以判断普通的数值，可读性较差
3.排序查询

select 查询列表 
from 表
[where 筛选条件] 
order by 排序列表 [asc|desc]

①asc:升序 desc:降序（不写默认升序）
②order by支持单个字段，多个字段，表达式，函数，别名
③order by子句一般放在查询语句的最后面,limit子句除外
4.常见函数
单行函数
（1）字符函数：
length()：获取参数值的字节个数

 length('aaaaa')

concat()：拼接字符串

concat('aaa','vvv','bbb')

upper()、lower()：将字符变成大写、小写

 upper('user')
 lower('USER')

substr()或者叫substring()：获取从指定索引处后面所有字符（可以指定长度）

 substr('你好world',3)
 substr('你好world',3,2)

instr()：返回子串第一次出现的索引，如果找不到返回0
substr('你好world','你好')
trim()：默认截断前后空格处，也可以自己指定截断的格式

 trim('   你好   ')
 trim('a' from 'aaaaaaa你好aaaaaa'）

lpad()：用指定的字符实现左填充指定长度

  lpad('你好',12,'hi')

Rpad()：用指定的字符实现右填充指定长度

rpad('你好',12,'hi')

replace()：替换

replace('hello，scala','scala','spark')

（2）数学函数：
round()：四舍五入
ceil()：向上取整
floor()：向下取整
truncate()：截断
mod()：取余（a-a/b*b）
（3）日期函数：
now()：返回当前时间
curdate()：返回当前日期，不包含时间
curtime()：返回当前时间，不包含日期
year()：返回年
str_to_date()：将日期格式的字符转换成指定格式的日期
date_format()：将日期转换成字符
datadiff()：返回两个日期相差天数
（4）其他函数：
version()
database()
user()

分组函数
sum求和、avg平均值、max最大值、min最小值、count计算个数
sum、avg一般用于处理数值型
max、min、count可以处理任何类型
可以和distinct搭配使用，eg:

select sum(distinct s) from sums;

关于count() 和count(1)*
效率：①MYISM存储引擎下，count()的效率高
②INNODB存储引擎下，count()和count(1)的效率差不多，比count(字段)要高一些
5.分组查询

select 分组函数,列（要求出现在group by的后面）
from 表
[where 筛选条件]
group by 分组的列表
[order by 子句]

（1）分组的筛选条件
-------------------------数据源-----------------位置-------------------------关键字-------------
分组前筛选----------原始表 ---------group by子句的前面------------where-------------
分组后筛选—分组后的结果集------group by子句的后面-----------having------------
（2）注意：
①分组函数做条件肯定是放在Having子句中
②能用分组前筛选的，就优先考虑用分组前筛选
6. 连接查询

select 查询列表
from 表1 别名 【连接类型】
join 表2 别名
on 连接条件
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】

（1）
①笛卡尔乘积错误
用一个表中的每一项去乘另一个表中的每一项。即表1 m 行 * 表2 n行 = m*n行
原因：没有有效的连接条件
②连接类型分类
sql92标准：仅仅支持内连接
sql99标准【推荐】：支持左外、右外、内连、交叉连
内连接（inner）：等值连接、非等值连接、自连接
外连接：左外连接（left outer）、右外连接（right outer）、全外连接（full outer）
交叉连接（cross）
③为表其别名
区分多个重名的字段，如果为表起了别名就不能使用原来的表名查询字段
（2）等值连接
①多表等值连接的结果为多表的交集部分
②n表连接，至少需要n-1个连接条件
③可以搭配前面的所有子句使用
④两个标准中的使用效果是一样的
sql92标准：

select 查询列表
from 表1 别名,表2 别名
where 等值连接条件;

sql99标准

select 查询列表
from 表1 别名 
inner join 表2 别名
on 等值连接条件;

（3）外连接
特点：
①查询结果为主表中的所有记录，从表中有与它匹配的，则显示匹配的值，如果没有，则显示null
即查询结果 = 内连结果 + 主表有而从表没有的记录
②左外：left join 左边为主表
右外：right join 右边为主表
左外和右外交换两个表的顺序可以得到相同的结果
（4）全外连接
查询结果 = 内连结果 + 表1有但是表2没有的 + 表2有但是表1没有的
7.子查询
（1）含义
出现在其他语句中的select语句被称为子查询或内查询，外部的查询语句称为主查询或外查询。
（2）分类
按出现的位置
select后面：仅仅支持标量子查询
from后面：支持表子查询
where或者having后面：标量子查询，列子查询，行子查询
exists后面（相关子查询）：表子查询

按结果集的行列数不同：
标量子查询（结果集只有一行一列）
列子查询（结果集只有一列多行）
行子查询（可能为一行多列或多行多列）
表字查询（结果集一般为多行多列）

多行子查询比较操作符
in | not in：等于列表中的任意一个
any | some：和子查询返回的某一个值比较
all：和子查询返回的所有值比较
8.分页查询

select 查询列表
from 表
【join type join 表2
on 连接条件
where 筛选条件
group by 分组条件
having 分组后的筛选
order by排序的字段】
limit offset,size;   # offset要显示的条目的起始索引  size：显示条目个数
# limit放最后
# 公式：limit (page-1)*size,size;

9.union联合查询

查询语句1
union
查询语句2
union
......;

（1）含义
将多条查询语句的结果集合并成一个结果
（2）应用场景
要查询的结果来自多个表，且多个表没有直接的连接关系，但查询的信息是一致时
（3）union 和 union all的区别
Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；
Union All：对两个结果集进行并集操作，包括重复行，不进行排序。

二、DML(data manager language)

1、插入
（1）方式一：

insert into 表名(列名...) values(值...);

①列的顺序可以调换
②列数和值的个数必须一致
③可以省略列名，默认所有列，且列的顺序和表中的顺序是一致的
（2）方式二：

insert into 表名
set 列名=值，列名=值.....

（3）两种方式PK:
①方式一支持插入多行，方式二不支持
②方式一支持子查询，方式二不支持

2、修改
（1）修改单表

update 表名
set 列=新值,列=新值...
where 筛选条件;

（2）多表查询
sql92语法

update 表1 别名,表2 别名
set 列=值，列=值....
where 连接条件
and 筛选条件；

sql99语法

update 表1 别名
inner | left | right join 表2 别名
on 连接条件
set 列 = 值
where 连接条件；

3、删除

# 方式一
delete  from 表名 where 筛选条件;
# 方式二
truncate table 表名;

delete PK truncate：
①truncate删除，效率高一点
②delete可以加where条件，truncate不可以加
③删除的表中如果有自增长列，truncate后再加入数据从1 开始，delete后插入数据从断点开始
④truncate删除不能回滚，delete可以回滚
⑤truncate没有返回值，delete有返回值

三、DDL(data define language)

(一)库的管理

1.创建

create database 【if not exists】 库名;

2.修改

alter database 库名 character set gbk;# 更改字符集

3.删除

drop database 【if exists】 库名;

(二)表的管理

1.创建（create）

create table 表名(
 列名 列类型【（长度）约束】,
 列名 列类型【（长度）约束】,
 ...
 列名 列类型【（长度）约束】
);

②字符型：较短的文本：char、varchar
较长的文本:text、blob(较长的二进制数据类型)
注意：
char PK varchar
char：最大字符数可以省略，默认为1，固定长度的字符，比较耗费空间，效率高；
varchar：最大字符数不可以省略，可变长度的字符，比较节省空间，效率低。

③日期型：date | datetime | timestamp | time | year
注意：date只保存日期；time只保存时间；year只保存日期+时间

2.修改（alter）
①修改列名

alter table 表名 change column 列名 列类型;

②修改列的类型或约束

alter table 表名 modify column 列名 列类型;

③添加新列

alter table 表名 add column 列名 列类型;

④删除列

alter table 表名 drop column 列名;

⑤修改表名

alter table 表名 rename to 表名;

3.删除（drop）

drop table if exists  表名;

4.表的复制

create table 表名A like 表名B;  # 仅仅复制表的结构
create table 表名A select  * from 表名B；  # 复制表的结构+数据
create table 表名A select  * from 表名B where 筛选条件；  # 复制表的结构+部分数据

(三)约束

1.六大约束
（1）NOT NULL：非空，用于保证字段不能为空
（2）DEFAULT：默认
（3）PRIMARY KEY：逐渐，具有唯一性，且非空
（4）UNIQUE：唯一，用于保证该字段的值具有唯一性，可以为空
（5）CHECK：检查约束【mysql中不支持】
（6）FORIGN KEY：外键，用于限制两个表的关系
外键特点：
*要求在从表设置外键关系；
*从表的外键类型和主表的类型要求一致或兼容，名称无要求；
*主表的关联列必须是一个key（一般为主键或唯一）；
*插入数据时，先插入主表，在插入从表；
删除数据时，先删除从表再删除主表
2.创建约束
（1）创建表时添加列级约束

create table stu(
	id int primary key,# 主键
	name varchar(10) not null， # 非空
	gendar char(1) check {gender='男' or gender = '女' }, # 检查
	seat int unique,# 唯一
	age int default 18, # 默认约束
	majorId int 
);
create table major(
	id int primary,
	mname varchar(10) 
);

注意：只支持：默认、非空、主键、唯一
（2）添加标记约束
语法：
【constraint 约束名】约束类型（字段名）
除了非空、默认其他都支持

create table stu(
	id int，
	name varchar(10) ，
	gendar char(1)，
	seat int，
	age int，
	majorId int ，
	constraint pk primary key(id),# 主键
	constraint uq unique(seat),# 非空
	constraint ck  {gender='男' or gender = '女' }, # 检查
	constraint fk_stu_major foreign key(marjorid) references major(id) # 外键
);

（3）修改表时添加约束
语法：
列级约束
alter table 表名 modify column 字段名字段类型新约束；
表级约束
alter table 表名 add 【constraint 约束名】约束类型（字段名）；

# 列级约束
alter tabel stu modify column id int primary key;
# 表级约束
alter table stu add primary key(id);
# 外键
alter table st add constraint fk_stu_major foreign key(id) references major(id);

3.主键和唯一的区别
主键：保证唯一性，不允许为空，一个表至多一个，允许组合（不推荐）
唯一：保证唯一性，允许为空，一个表可以有多个，允许组合（不推荐）

4.标识列
（1）含义：可以不用手动的插入值，系统提供默认的序列值s
（2）使用：id int primary key auto increment
（3）特点：
*标识列不一定和主键搭配，但必须是一个key
*一个表至多一个标识列，且只能是数值型
*通过 set auto_increment=数字；设置步长

四、TCL(transaction control language

(一)事务

一个或一组sql语句组成一个执行单元，这个单元要么全做，要么全不做
1.ACID属性
（1）原子性 atomicity
一个不可分割的状态，事务中的操作要么全做要么全部不做。
（2）一致性 consistency
使数据库从一个一致性状态变换到另一个一致性状态。
（3）隔离性 isolation
值一个事务的执行不能被其他事务干扰。
（4）持久性 durability
一个事务一旦被提交，它对数据库中数据的改变就是永久性的。
2.创建事务
（1）隐式事务：事务没有明显的开始结束标记。
（2）显示事务：事务有明显的开始结束标记。
（3）使用显示事务
步骤一（前提）：必须先设置自动提交功能为禁用

set autocommit = 0;

步骤二：编写事务中SQL语句
步骤三：提交或回滚
commute； | rollback;
3.事务并发问题
（1）脏读
一个事务读取了另一个事务还没有提交的字段后，另一个事务回滚了，则此字段就是临时且无效的。
（2）不可重复读
一个事务读取字段后，另一个事务更新了该字段，该事务再次读取事务时字段值不同了。
（3）幻读
事务1读取字段后另一个事务插入了一些新的行，事务一再次读同一个表时数量不一样。
4.数据库的隔离级别
（1）读未提交数据 read uncommited
允许事务读未被其他事务提交的数据，会出现脏读、不可重复读、幻读。
（2）读已提交数据 read commited
只允许事务读已被提交的数据，可以避免脏读，会出现不可重复读、幻读。
（3）可重复读 repeatable read
确保事务从一个字段多次读到相同的值，避免脏读、不可重复读，会出现幻读
（4）序列化 serializable
确保可以从一个表中读到相同的行，在该事务期间，进制其他事务对该表插入、更新、删除，避免脏读、不可重复读、幻读

注意：
oracle只支持两种隔离级别：read commited 、seriallzable,oracle中默认隔离级别为：read commited;
mysql支持4中事务隔离级别，mysql默认事务隔离级别是:repeatable read。
5.回滚点

set autocommit=0;
start transaction;
sql语句；
savepoint  节点名;# 设置保存点
sql语句;
rollback to 节点名;回滚到保存点

(二)视图

1.创建视图
create view 视图名
as
查询语句;
2.修改视图
create or replace view 视图名 as 查询语句;
或者
alter view 视图名 as 查询语句;
3.删除视图
drop view 视图名，视图名2…；
4.查询视图
desc 视图名;
5.以下特点的视图不允许更新
视图的增删改查和表的语法一样
（1）包含分组函数、distinct、group by、having、union、union all
（2）常量视图
（3）select中包含子查询
（4）join
（5）from一个不能更新的视图
（6）where子句的子查询引用了from子句中的表
6.视图和表的对比
-------------------创建表的关键字-------------是否占用物理空间---------------使用---------------------------
–视图------------create view-----------只是保存了SQL逻辑--------增删改查，一般不能增删改--------
—表-------------create table------------保存了数据--------------------增删改查-------------------------------

(三)存储过程和函数

1.变量
（1）系统变量
如果是全局级别，则需要加global，如果是会话级别，则需要加session，如果不写，则默认session

show global | session variables;#查看所有系统变量
show global | 【session】 varianles like '%char%'; # 查看满足条件的部分系统变量
select @@global | 【session】.系统变量名;# 查看指定的某个系统变量的值;
set global | 【session】 系统变量名 = 值; # 为某个系统变量赋值
set @@gloabal | 【session】.系统变量名 = 值；

①全局变量
服务器每次启动将所有的全局变量赋初始值，针对所有的会话（连接）有效，但不能跨重启
②会话变量
仅仅针对当前会话（连接）有效
（2）自定义变量
①用户变量
针对当前会话（连接）有效，等同会话变量的作用域

#声明并初始化
set @用户变量名=值；
set @用户变量名:=值；
select @用户变量名:=值;
#通过select into赋值
select 字段 into @变量名
form 表；
#使用
select @用户变量名;

②局部变量
仅仅在它的begin end中有效且只能在begin后的第一句话，声明初始化赋值和上面一样

#声明并初始化
set 局部变量名=值；
set 局部变量名:=值；
select @局部变量名:=值;
#通过select into赋值
select 字段 into 局部变量名
form 表；
#使用
select @用户变量名;

③对比
用户变量，作用域为当前会话，定义和使用在会话中任何地方，必须加@符号，不用限定类型；
局部变量，begin end 中，只能在begin的第一句话，一般不用加@，需要限定类型。
2.存储过程
（1）含义：一组预先编译好的SQL语句的集合，理解成处理语句。
（2）创建：
create procedure 存储过程名(参数列表)
begin
存储过程体
end
（3）参数列表
参数模式参数名参数类型
（4）参数类型：
IN：输入
OUT：输出
INOUT：即可输入又可输出
（5）注意
如果存储过程体只有一句话，begin end可以省略
存储过程体里面的每条SQL都必须加分号
存储过程的结果为了和里面SQL的结束区分，要用delimiter 结束符重新设置

delimiter $  # 不一定是$，也可以是其他符号

（6）调用
call 存储过程名（实参列表）结束符
（7）例子

delimiter $
create procedure mypro(INOUT a int,INOUT b int )
begin
	SET a = a*2;
	SET b = b*2;
end $
# 调用
set @m=10$
set @n=10$
call mypro(@m,@n)$

（8）删除
drop procedure 存储过程名;
drop procedure pro1,pro2; # 错误，不可以接着写
（9）查看
desc 存储过程名; # X
show create procedure 存储过程名;
3.函数
（1）存储过程：可以有0个返回，也可以有多个返回，适合做批量插入、批量更新
函数：有且仅有1个返回，适合做处理数据后返回一个结果
（2）创建语法
create function 函数名(参数列表) returns 返回类型
begin
函数体
end
注意：
参数类别包含：参数名参数类型
函数体：没有return语句会报错，且return建议放函数体最后
使用delimiter语句设置结束标记
（3）调用语法
select 函数名（参数列表）
（4）案例

delimiter $
create funtion myfun() return int
begin
	declare c int default 0;
	select count(*) into c # 赋值
	from employee;
	return c;
end $

（5）查看、删除
show create function 函数名;
drop function 函数名;

(四)流程控制结构

1.IF函数
if (表达式1，表达式2，表达式3)
如果1成立返回2的值，否则返回3的值
2.case结构
顺序结构
（1）类似java中switch
case 变量 | 表达式 | 字段
when 要判断的值 then 返回的值1或语句1;
when 要判断的值 then 返回的值2或语句2;
…
else 返回的值n或语句n;
end case;
（2）类似java中多重if
case
when 要判断的条件 then 返回的值1或语句1;
when 要判断的条件 then 返回的值2或语句2;
…
else 返回的值1或语句1;
end case;
（3）if结构
if 条件1 then 语句1;
elseif 条件2 then 语句2;
…
else 语句n;
end if;
循环结构
iterate类似continue
leave类似break
（4）while
【标签体：】while 循环条件都
循环体;
end while【标签体】;
（5）loop
【标签体:】loop
标签体;
end loop【标签体】;
（6）repeat
【标签体：】repeat
循环体;
until 结束循环的条件
end repeat 【标签】;

liuhan_424

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Mysql知识点复习

服务器登录退出mysql -u root -paaaexit #或者 ctrl+cmysql的常见命令show databases; # 查看当前所有数据库use 库名; # 使用指定数据库show tables; # 查看当前库的所有表show tables from 库名; # 查看其他库的所有表desc 表名; # 查看表结构select version();...
复制链接

扫一扫