文章目录
Ⅰ、DQL-数据查询语言
一、字符函数
注意:MySQL中,字符串的位置是从1开始的。
函数 | 用法 |
---|---|
CHAR_LENGTH(s) | 返回字符串s的字符数 |
LENGTH(s) | 返回字符串s的字节数,和字符集有关 |
CONCAT(s1,s2,…,sn) | 连接s1,s2,…,sn为一个字符串 |
CONCAT_WS(x, s1,s2,…,sn) | 同CONCAT(s1,s2,…)函数,但是每个字符串之间要加上x |
INSERT(str, idx, len, replacestr) | 将字符串str从第idx位置开始,len个字符长的子串替换为字符串replacestr |
REPLACE(str, a, b) | 用字符串b替换字符串str中所有出现的字符串a |
UPPER(s) 或 UCASE(s) | 将字符串s的所有字母转成大写字母 |
LOWER(s) 或LCASE(s) | 将字符串s的所有字母转成小写字母 |
LEFT(str,n) | 返回字符串str最左边的n个字符 |
RIGHT(str,n) | 返回字符串str最右边的n个字符 |
LPAD(str, len, pad) | 用字符串pad对str最左边进行填充,直到str的长度为len个字符 |
RPAD(str ,len, pad) | 用字符串pad对str最右边进行填充,直到str的长度为len个字符 |
LTRIM(s) | 去掉字符串s左侧的空格 |
RTRIM(s) | 去掉字符串s右侧的空格 |
TRIM(s) | 去掉字符串s开始与结尾的空格 |
REPEAT(str, n) | 返回str重复n次的结果 |
STRCMP(s1,s2) | 比较字符串s1,s2的ASCII码值的大小 |
SUBSTR(s,index,len) | 返回从字符串s的index位置其len个字符 |
LOCATE(substr,str) | 返回字符串substr在字符串str中首次出现的位置 |
REVERSE(s) | 返回s反转后的字符串 |
NULLIF(value1,value2) | 比较两个字符串,如果value1与value2相等,则返回NULL,否则返回value1 |
二、数字函数
函数 | 用法 |
---|---|
ABS(x) | 返回x的绝对值 |
SIGN(X) | 返回X的符号。正数返回1,负数返回-1,0返回0 |
CEIL(x),CEILING(x) | 向上取整 |
FLOOR(x) | 向下取整 |
LEAST(e1,e2,e3…) | 返回列表中的最小值 |
GREATEST(e1,e2,e3…) | 返回列表中的最大值 |
MOD(x,y) | 返回X除以Y后的余数 |
RAND() | 返回0~1的随机值 |
RAND(x) | 返回0~1的随机值,其中x的值用作种子值,相同的X值会产生相同的随机 |
数 | |
ROUND(x) | 返回一个对x的值进行四舍五入后,最接近于X的整数 |
ROUND(x,y) | 返回一个对x的值进行四舍五入后最接近X的值,并保留到小数点后面Y位 |
TRUNCATE(x,y) | 返回数字x截断为y位小数的结果 |
三、日期和时间函数
1、获取日期、时间
函数 | 用法 |
---|---|
CURDATE() ,CURRENT_DATE() | 返回当前日期,只包含年、月、日 |
CURTIME() , CURRENT_TIME() | 返回当前时间,只包含时、分、秒 |
NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() / LOCALTIMESTAMP() | 返回当前系统日期和时间 |
UTC_DATE() | 返回UTC(世界标准时间)日期 |
UTC_TIME() | 返回UTC(世界标准时间)时间 |
2、日期与时间戳的转换
函数 | 用法 |
---|---|
UNIX_TIMESTAMP() | 以UNIX时间戳的形式返回当前时间 |
UNIX_TIMESTAMP(date) | 将时间date以UNIX时间戳的形式返回 |
FROM_UNIXTIME(timestamp) | 将UNIX时间戳的时间转换为普通格式的时间 |
3、日期的格式化与解析
函数 | 用法 |
---|---|
DATE_FORMAT(date,fmt) | 按照字符串fmt格式化日期date值 |
TIME_FORMAT(time,fmt) | 按照字符串fmt格式化时间time值 |
GET_FORMAT(date_type,format_type) | 返回日期字符串的显示格式 |
STR_TO_DATE(str, fmt) | 按照字符串fmt对str进行解析,解析为一个日期 |
四、流程控制函数
- if 函数
select if(10<5,'大','小');//结果:小
- 方式一:case 函数
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
…
else 要显示的值n或者语句n;
end
select salary 原始工资,department_id,
case department_id
when 30 then salary*1.1
when 40 then salary*1.2
when 50 then salary*1.3
else salary
end as 新工资
from employees
- 方式二:case 函数
case
when 条件1 then 要显示的值1或者语句1
when 条件2 then 要显示的值2或者语句2
…
else 要显示的值n或语句n
end
select salary,
case
where salary>2000 then 'A'
where salary>5000 then 'B'
where salary>15000 then 'C'
else 'D'
end as 工资级别
from employees
五、多表连接
1、92语法
- MySql只支持内连接
# 查询员工的id和部门id(内连接,两表都可以匹配上)
select
e.employee_id ,
d.department_name
from
employees e ,
departments d
where
e.department_id = d.department_id
- MySql不支持外连接,Oracle支持
# 查询所有员工的id和部门id(左外连接)
select
e.employee_id ,
d.department_name
from
employees e ,
departments d
where
e.department_id = d.department_id(+)
# 查询所有员工的id和部门id(右外连接)
select
e.employee_id ,
d.department_name
from
employees e ,
departments d
where
e.department_id(+) = d.department_id
2、99语法
- 内连接(inner 可省略)
select
e.employee_id ,
d.department_name
from
employees e
inner join
departments d
on
e.department_id = d.department_id
# 多个表内连接
select
e.employee_id ,
d.department_name,
l.city
from
employees e
inner join
departments d
on
e.department_id = d.department_id
inner join locations l
on
d.location_id = l.location_id
# 多个表内连接(最后添加连接条件)
select
e.employee_id ,
d.department_name,
l.city
from
employees e
inner join
departments d
inner join locations l
on
e.department_id = d.department_id
and
d.location_id = l.location_id
- 左外连接(outer可省略)
select
e.employee_id ,
d.department_name
from
employees e
left outer join
departments d
on
e.department_id = d.department_id
- 右外连接(outer可省略)
select
e.employee_id ,
d.department_name
from
employees e
right outer join
departments d
on
e.department_id = d.department_id
- 全外连接(outer可省略),MySql不支持全外连接,Oracle支持
select
e.employee_id ,
d.department_name
from
employees e
full outer join
departments d
on
e.department_id = d.department_id
3、7种join操作
- 中图:内连接
select
e.employee_id ,
d.department_name
from
employees e
inner join
departments d
on
e.department_id = d.department_id
- 左上图:左外连接
select
e.employee_id ,
d.department_name
from
employees e
left join
departments d
on
e.department_id = d.department_id
- 右上图:右外连接
select
e.employee_id ,
d.department_name
from
employees e
right join
departments d
on
e.department_id = d.department_id
- 左中图
select
e.employee_id ,
d.department_name
from
employees e
left join
departments d
on
e.department_id = d.department_id
where
d.department_id is null
- 右中图
select
e.employee_id ,
d.department_name
from
employees e
right join
departments d
on
e.department_id = d.department_id
where
e.employee_id is null
-
左下图:满外连接(左上图 union all 右中图)
-
右下图:左中图 union all 右中图
4、SQL99语法新特性
- 自然连接
标准写法:
select employee_id,last_name,department_name
from employees e join departments d
on e.department_id = d.department_id
and e.manager_id = d.manager_id
新特性写法:
select employee_id,last_name,department_name
from employees e
natural join departments d;
- USING连接
标准写法:
select employee_id,last_name,department_name
from employees e join departments d
on e.department_id = d.department_id
新特性写法:
select employee_id,last_name,department_name
from employees e join departments d
USING (department_id)
六、分页查询
①select 字段
②from 表1
③join 表2
④on 连接条件
⑤where 筛选条件
⑥group by 分组字段
⑦having 分组后筛选条件
⑧order by 排序字段
⑨limit offset,size
执行顺序:② ③ ④ ⑤ ⑥ ⑦ ① ⑧ ⑨
- offset 起始索引从0开始
- size 显示的条目个数
select * from employees limit 0,5;
//默认从0开始
select * from employees limit 5;
七、联合查询
union
应用场景:查询结果来自多个表,且多个表没有连接关系,但查询信息一致时。
- 要求多条查询语句的查询列数是一致的
- 要求多条查询语句每一列的类型和顺序最好一致
- union关系字默认去重,如果使用union all 可以包含重复项
Ⅱ、DML-数据操纵语言(增删改)
一、delete和truncate区别
- delete可以加where条件,truncate不能
- truncate效率高,不用过滤
- delete删除数据后,自增值从断电开始。truncate删除后,自增值从1开始
- truncate删除没有返回值,delete删除有返回值
- truncate删除不能回滚,delete删除可以回滚
Ⅲ、DDL-数据定义语言(视图索引等)
一、库的管理
- 创建库books
create database books;
//books库不存在创建,存在则不创建(不报错)
create database if not exists books;
- 修改库books
//修改字符集
alter database books character set utf-8;
无修改库名命令
- 删除库books
drop database books;
//books库存在删除,不存在不删除(不报错)
drop database if exists books;
二、表的管理
- 表的创建
create table book(
id int,
name varchar(20)
)
//查看表结构:
desc book;
- 表的修改
①修改列名
alter table book change column publish_date pub_date datetime;
②修改列的类型或约束
alter table book modify column pub_date timestamp;
③添加新列
alter table book add column annual double;
④删除列
alter table book drop column annual;
⑤修改表名
alter table book rename to book_new;
column 可以省略
- 表的删除
drop table book_new;
//查询所有库
show tables;
- 表的复制
①仅复制表结构
create table boos2 like book;
②复制表结构和内容
create table book3 select * from book;
三、视图的管理
- 视图创建
create view 视图名
as
查询语句;
- 视图修改
alter view 视图名
as
查询语句;
- 视图删除
drop view 视图名1,视图名2,...;
- 视图查看
dec 视图名;
Ⅳ、DCL-数据控制语言(事务)
- DDL语言没有事务,事务只存在于增删改查。
一、事务的特点(ACID)
- 原子性:一个事务不可再分割,要么都成功要么都失败。
- 一致性:一个事务执行使数据从一个一致性状态变换为另一个一致性状态。(可以理解为能量守恒)
- 隔离性:一个事务的执行不受其他事务的干扰。
- 持久性:一个事务一旦提交,则永久的改变数据状态。
二、事务的并发问题
- 脏读:一个事务读到另一个事务修改未提交的数据。
- 不可重复读:一个事务两次读取同一数据,在两次读取之间,另外一个事务将此数据修改或删除,则同一事务,两次读取结果不同。
- 幻读
①幻读是在解决了不可重复读即在可重复读基础上发生的。
②A事务通过检索条件查询一组数据,B事务新增或删除此检索范围内数据并提交。这时候重点来了:A事务通过相同的检索条件查询不会显示(因为可重复读,如果显示就是不可重复读了),但实际已经查询到(只是不显示),若此时根据检索条件delete,会将B事务新增的数据删除
三、事务的隔离级别
- READ UNCOMMITTED (读未提交):允许事务读取未被其他事务提交的变更。脏读、不可重复读、幻读都会出现。
- READ COMMITTED (读已提交):只允许事务读取已经被其他事务提交的变更。避免脏读,不可重复读、幻读会出现。
- REPEATABLE READ (可重复读):可以保证同一个事务两次读取同一数据不变,即使其他事务修改此数据。避免脏读、不可重复读,幻读会出现。
- SERIALIZABLE (串行化):一个事务读取数据,禁止其他事务插入、修改、删除操作。最高级别,所有问题都能避免,但效率特别低。
四、默认隔离级别
- oracle 支持2种事务隔离级别:读已提交、串行化。默认读已提交。
- mysql 支持4种事务隔离级别,默认可重复读。
五、隔离级别演示
//1.显示是否自动提交 on-自动提交 off-手动提交
show variables like 'autocommit';
//2.设置手动提交
set autocommit = 0;
//3.设置自动提交
set autocommit = 1;
//4.开启事务,搭配2事务,可省略
start transaction;
//5.查看事务隔离级别
select @@transaction_isolation;
//6.设置当前会话隔离级别
set session transaction isolation level read committed;
//7.查看正在执行的事务
SELECT * FROM information_schema.INNODB_TRX;
版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。
文章由极客之音整理,本文链接:https://www.bmabk.com/index.php/post/148677.html