【mysql-基础篇】

作者: giraffecode9668 | 来源:发表于2020-06-12 16:01 被阅读0次

mysql-基础篇

一、常见命令

1、sql常见命令

查看当前所有的数据库：show databases;
打开指定的库：use 库名;
查看当前库的所有表：show tables;
查看其它库的所有表：show tables from 库名;
创建表：create table 表名（列名列类型, 列名列类型）;
查看表结构：desc 表名;
查看服务器版本：
方式一：登录状态：select version();
方式二：未登录状态：mysql --version或mysql -V

2、mysql语法规范

不区分大小写，建议关键字大写，表名、列名小写
每条命令结尾最好用分号结尾
每条命令根据需要，可以进行缩进或换行
单行注释： #注释文字 --注释文字
多行注释：/* 注释文字 */

二、select查询

1）select 查询列表 from 表名;

查询列表：表字段、常量值、表达式、函数
查询的结果是虚拟表格
查询所有列使用：select *

2）别名使用as或者缺省
select mother as mom from x
select mother mom from x
select mother "ma mi" from x

如果为表起了别名，原来表名不能使用

3）去重：使用distinct关键字
select distinct 字段 from 表

4）+号
mysql只有加法功能

例子说明：
select 1+1;  数值型，做加法运算
select '123'+90; 将其中非数值型装换为数值型做运算，装换失败装换为0做运算
select 'j'+10; =10
 只要一方为null，结果为null

5）拼接：concat(str ... )
如果一个字段为null结果为null
select concat(last_name,first_name) as 姓名 from employee

6）ifnull(expr1,expr2)
如果expr1为null，返回expr2值。相当于if(expr1=null,expr2,expr1);

7）条件查询where
select 字段 from 表名 where 筛选条件

条件表达符：> < = != <> >= >=
逻辑表达符：&& || ! 或者 and or not
模糊查询：like between and in is null

8）模糊字符
%包含多个任意字符，包含0个字符
_包含一个任意字符，普通符号'_'使用转义字符\进行转义，如：\_

9）转义
默认 \
使用ESCAPE指定某个符号位转义字符
select sdf$_sdf escape $;

10）is null
因为没有= null，使用is null
反则：is not null

11）安全等于：<=>
<=> 相当于is 和 =的结合

12）排序
order by 排序列表【asc|desc】
缺省为升序（asc）

13）length()
select length(last_name);字符串字节的长度

三、常见函数

1、单行函数

如concat、length、ifnull等，一行只有一个参数返回值

1.1、字符函数

length(str)：计算字节长度
concat(str...)：拼接字符串
upper(str)：大写
lower(str)：小写
substr(str, pos)：pos索引从1开始，截取str从索引pos的位置到结尾，同substring(str, pos)
substr(str, pos, len)：索引指定长度的字符长度
instr(str1, str2)：返回str2在str1第一次出现的索引，找不到返回0
trim(str)：去除前后空格
trim('a' from 'aa史蒂芬aa')：去除前后a
lpad(str,len,padstr)：用指定的padstr左填充str到len长度
rpad(str,len,padstr)：用指定的padstr右填充str到len长度
replace(str1, str2, str3)：用str3替换在str1中的所有str2

1.2、数学函数

round(x)：将x四舍五入
round(x,d)：小数点保留后d位
ceil(x)：向上取整，返回>=该参数的最小整数
floor(x)：向下取整，返回<=该参数的最大整数
truncate(x,d)：截断，小数点后截断d位小数
mod(x1,x2)：取模，x1%x2

1.3、日期函数

now()：返回当前系统日期+时间
curdate()：返回当前系统日期，不包含时间
curtime()：返回当前的时间，不包含日期
year(now()) ：指定部分，年、月month()，monthname()、day()日、hour()小时、minute()分钟、seconde()秒
str_to_date('9-13-1999','%m-%c-%Y')：将字符根据格式转换为日期
date_format(now(),'%y年%m月%d日')：将日期转换为字符
datediff(date1,date2)：相差天数

image.png

1.4、其他函数

version()：版本
database()：当前数据库
user()：当前用户

1.5、流程控制函数

if(expr1, expr2, rxpr3) ：效果等同于expr1?expr2:expr3
case：类似switch case的效果，可以当表达式或者单独的语句

# switch
case ？
when ? then ?
when ? then ?
else ?
end 

# if else
case 
when ? then ?
when ? then ?
else 
end

2、分组函数

做统计使用，传入多个参数进行统计返回值，又称统计函数、聚合函数、组函数

2.1、简单实用

sum(字段)：求和
avg(字段)：平均值
min(字段)：最小值
max(字段)：最大值
count(字段)：计数值（非空）

2.2、支持类型

1）sum、avg一般用于处理数值型
max、min、count可以处理任何类型

2）是否忽略null值
sum、avg、max、min、count都忽略null值

3）可以和distince搭配
sum(distinct salary)：去重求和

count(*)：计算总行数，建议使用此方式，效率更快
count(1)：也是统计行数

2.3、分组数据

group by
语法

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

having：对group by结果的数据进行进一步筛选

四、连接查询

1、分类标准

按年代分类：

sql92标准：仅支持内连接
sql99标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接

按功能分类：

内连接：
- 等值连接
- 非等值连接
- 自连接
外连接：
- 左外连接
- 右外连接
- 全外连接
交叉连接

2、sql92标准

1）等值连接
select ? from ?,? where ?=?

2）非等值连接
select ? from ?,? where ?beteween ? and ?

3）自连接
select ? from emp e,emp m where ?=?

3、sql99标准

语法：
select ? from ? [连接类型] join ? on ?
连接类型关键字：

内连接：inner
外连接：
- 左外：left【outer】
- 右外：right【outer】
- 全外：full【outer】
交叉连接：cross

1）内连接
inner join
inner可以省略
交集

2）外连接
查询结果是主表中的所有记录
如果有和它匹配的，显示匹配的值；否则显示null
左外连接，left join左边的是主表
右外连接，right join右边的是主表
左外和右外交换两个表的顺序，可以实现同样的效果

3）全外连接
mysql不支持，效果：左连接+右连接的并集

4）交叉连接
笛卡尔集

五、子查询

子查询外部称为：主查询或外查询

1、分类

按子查询可以出现的位置分类：

select后面：仅支持标量子查询
from后面：仅支持表子查询
where或者having后面：支持标量子查询、列子查询、行子查询
exists后面（相关子查询）：仅支持表子查询

结果集分类：

标量子查询（结果集只有一行一列）
列子查询（结果集只有一列多行）
行子查询（结果集只有多行多列）
表子查询（结果集一般为多行多列）

2、特点：

子查询放在小括号内
子查询一般放在条件右侧
标量子查询、一般搭配这单行操作符使用> < >= <= = <>
列子查询，一般搭配着多行操作符使用in、any/some、all

六、分页查询

语法：limit offset,size
offset：开始索引，起始从0开始，0时可以省略
size：要显示的条目个数
limit放在查询语句最后

limit (page-1)*size,size

七、联合查询

语法：查询语句1 union 查询语句2 union xxx

union：有去重效果
union all：不去重

八、数据库操作语言（DML）

数据操纵语言（DML）DataManipulationLanguage，是数据库的增删改操作的sql语句。

1、插入语句

方式一支持插入多行，方式二不支持
方式一支持子查询，方式二不支持

方式一：
insert into 表名(列名,...)
values (值1,...)
// 如果列名没有，表示所有的列名

方式二：
insert into 表名
set 列名=值, 列名=值....

2、修改语句

# 修改单表的记录
update 表名
set 列=新值, 列=新值, ...
where xxx

# 修改多表的记录
update 表1 别名
join 表 别名
on 条件
set 列=值, ...
where 连接条件

3、删除语句

方式一：delete
delete from 表名 where 筛选条件
delete 表别名,表别名 from 表1 别名,表2 别名 where ? and ?
delete 表别名 from 表 别名 join  ? on ? where ?

方式二：truncate
truncate table 表名; #清空表所有数据，不允许用where

delete VS truncate

delete删除后，自增长从断点开始；truncate删除后，自增长从1开始
delete删除有返回值；truncate删除没有返回值
delete删除可以回滚；truncate删除不能回滚

九、数据定义语言DDL

数据定义语言DDL(Data Definition Language)，针对数据库/表操作的sql语言。

1、库的管理

1）创建库
create database xxx;
create database if not exists xxx

2）修改库
更改库的字符集
alter database xxx character set gbk;

3）删除库
drop database xxx;
drop database if exists xxx;

2、表的管理

1）创建表

create table 表名(
    列名 列的类型【(长度) 约束】
    列名 列的类型【(长度) 约束】
    列名 列的类型【(长度) 约束】
)

2）修改表
①修改列名
alter table 表名 change [column] 旧列名新列名类型
②修改列的类型或约束
alter table 表名 modify column 列名新类型
③添加新列
alter table 表名 add column 列名类型
④删除列
alter table 表名 drop column 列名
⑤修改表名
alter table 表名 rename to 新表名

3）删除表
drop table 表名
drop table if exists 表名

4）复制表
仅复制表结构
create table 新表 like 旧表
复制表结构+数据
create table 新表 select * from 旧表
仅复制某些字段
create table 表 select 列 from 表 where 0

十、类型

1、整型

类型	字节
tinyint	1
smallint	2
mediumint	3
int integer	4
bigint	8

如果不设置符号，默认是有符号，如果设置无符号：unsigned
超出范围，报out of range，填充临界值
如果不设置长度，会有默认长度，长度为填充0到长度，配合使用zerofill int (11) zerofill

2、小数

浮点
float(m,d)、double(m,d)

定点
dec(m,d)/decimal(m,d) 字节：m+2

m：整数部位+小数部位的总长度
d：小数部位长度
m和d都可以省略。
如果是decimal，m默认是10，d默认是0；
如果是float和double，没有精度

3、字符

短文本

char(m)：固定长度的字符，空间耗费，效率高，省略默认1
varchar(m)：可变长度的字符，空间节省，低，不可省略
enum(str...)：枚举，插入只能是a/b/c不区分大小写，例如：c1 enum('a','b','c')
set(str...)：集合，插入可以是集合('a')('a,b')...，例如：s1 set('a','b','c','d')

长文本
text
blob

4、日期

date：日期
datetime：日期+时间，8字节
timestamp：时间戳，4字节，最大2038年
time：时间
year：年

十一、约束

1、六大约束

NOT NULL：非空，保证字段值不能为空
DEFAULT：默认，保证有默认值
PRIMARY KEY：主键，唯一非空
UNIQUE：唯一，可以为空
CKECK：检查约束【mysql中不支持】
FOREIGN KEY：外键，用于限制两个表的关系，保证该表的字段值必须来自关联字段值

添加约束的时机：创建表时，修改表时

2、约束添加分类

1）列级约束：六大约束语法上都支持，但外键约束没有效果

# 列级约束直接在字段后面添加
create table student(
    id int primary key,#主键
    stuName varchar(20) not null,#非空
    gender char(1) check(gender='男'or gender = '女'),#检查
    seat int unique,#唯一
    age int default 18,#默认
    majorId int references major(id)#外键（语法不报错但无效）
)

create table major(
    id int primary key,
    majorName varchar(20)
)

2）表级约束：除了NOT NULL、DEFAULT，其他都支持

create table student(
    id int ,
    stuName varchar(20) ,
    gender char(1) ,
    seat int ,
    age int ,
    majorId int
    # 【constraint 约束名】 约束类型(字段)
    constraint pk primary key(id),#主键，mysql中改名没效果，还是primary key
    constraint uq unique(seat),#唯一
    constraint ck check(gender='男'or gender = '女'),#检查
    constraint fk_stuinfo_major foreign key (majorid) refferences major(id)#外键  
)

show index from 表：查看表中所有的索引；
unique只能有一个null

外键：
1）在从表设置外键关系
2）类型一致
3）主表的关联列必须是一个key（一般是主键或唯一）
4）插入数据时，先插入主表，再删除从表
5）删除数据时，先删除从表，再删除主表

3、修改表时添加约束

列级：alter table 表 modify column 列类型约束
表级：alter table 表【constraint 约束名】 add 约束(字段) 【外键引用】

4、修改表时删除约束

alter table 表 modify column 列类型
alter table 表 drop primary key
alter table 表 drop index 唯一键名
alter table 表 drop foreign key 约束名称

十二、标识列（自增长列）

可以不用手动插入值，系统提供默认的序列值auto_increment，标识列要求是一个key，一个表至多只有一个标识列，标识列的类型只能为数值型

1、创建时设置标识列

create table 表(
    id int primary key auto_increment,
     ...
);

2、修改表时设置标识列

alter table 表 modify column 列类型 xxx auto_increment

3、删除表时设置标识列

alter table 表 modify column 列类型

十三、事务

1、ACID属性

A：atomicity原子性，要么都发生，要么都不发生
C：consistency一致性，事务状态变换到另一个一致性状态
I：isolation隔离性，事务之间不能互相干扰
D：durability持久性，事务一旦提交，改变就是永久的

2、事务的创建

隐式事务：事务没有明显的开启和结束标志，比如insert、update、delete语句
显式事务：事务具有明显的开启和结束的标志

前提：必须先设置自动提交功能为禁用

步骤1：开启事务
set autocommit = 0;针对当前会话有效
start transaction;可选的

步骤2：编写事务中的sql语句（select insert update delete）

步骤3：结束事务

commit; 提交事务
rollback; 回滚事务

示例：

set autocommit=0;
start transaction;
update ...;
update ...;
commit; 
#rollback;

delete支持回滚,truncate不支持回滚

十四、隔离

mysql隔离级别

1、read uncommitted：出现赃读、不可重复读、幻读
2、read committed：避免赃读，出现不可重复读，幻读
3、repeatable read：避免赃读，不可重复读，出现幻读（默认）
4、serializable：避免赃读、不可重复读、幻读（性能低）

查看当前隔离级别：select @@tx_isolation
设置当前会话隔离级别：set transaction isolation level 隔离级别
设置全局隔离级别：set global transaction isolation level 隔离级别（需要重启）

savepoint 节点名：设置断点

十五、视图

好处：可重用sql，简化sql操作，不必知道查询细节，安全保护数据，

1、创建视图

create view 视图名
as
select ...

2、使用

select x from 视图名

3、视图修改

方式一：
create or replace view as 视图名 
as 
select ...;

方式二：
alter view 视图
as 
select ...;

4、删除视图

drop view 视图名,视图名....;

5、查看视图

desc 视图
show create view 视图名

6、视图的更新

可以执行插入修改删除，如果字段允许的话。
具备特点的视图不允许更新：
1）sql语句包含：分组函数、distinct、group by、having、union或union all
2）常量视图
3）select 中包含子查询
4）join
5）from一个不能更新的视图
6）where子句中的子查询引用了from字句中的表

十六、变量

系统变量
- 全局变量
- 会话变量
自定义变量
- 用户变量
- 局部变量

1、系统变量

指明global为全局变量，不指明或者指明session为会话变量

2、自定义变量

声明、赋值、使用
用户变量：作用域：当前会话
1）声明并初始化
set @用户变量名=值
set @用户变量名:=值
select @用户变量名:=值

2）赋值（更新用户变量的值）
方式一：通过set或select
set @用户变量名=值
set @用户变量名:=值
select @用户变量名:=值

方式二：通过select into
select 字段 into 变量名 from 表

3）使用（查看用户变量的值）
select @用户变量名

局部变量：作用域：仅仅定义在它的begin end中有效；在begin end应用中的第一句话；
1）声明
declare 变量名类型
declare 变量名类型 default 值
2）赋值
方式一：通过set或select
set 用户变量名=值
set 用户变量名:=值
select @用户变量名:=值