mysql简单语法讲解三

最新推荐文章于 2024-05-21 09:46:31 发布

IT萌新@

最新推荐文章于 2024-05-21 09:46:31 发布

阅读量120

点赞数

分类专栏： Mysql 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/m0_49102053/article/details/130459400

版权

Mysql 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

一、函数

字符串函数
数学函数
日期函数
日期-字符串转换函数
流程函数

1.1 字符串函数

函数	解释
CHARSET(str)	返回字串字符集
CONCAT (string2 [,… ])	连接字串
INSTR (string ,substring )	返回substring在string中出现的位置,没有返回0
UCASE (string2 )	转换成大写
LCASE (string2 )	转换成小写
LEFT (string2 ,length )	从string2中的左边起取length个字符
LENGTH (string )	string长度
REPLACE (str ,search_str ,replace_str )	在str中用replace_str替换search_str
STRCMP (string1 ,string2 )	逐字符比较两字串大小,
SUBSTRING (str , position [,length ])	从str的position开始,取length个字符
LTRIM (string2 ) RTRIM (string2 ) trim	去除前端空格或后端空格

-- 今天讲的这些函数,可以应用在CRUD中都行
-- =========== 字符串函数 ============
select charset('abc'); -- 返回字符集
select charset('abc') from dual; -- from dual,只是为了补全sql语句
-- concat(str1,....) 连接字符串 【重要】
select concat('a','1','b','2') from dual;
select concat('a','1','b','2'),sid,sname from stu;
select concat(sid,sname),sid,sname from stu;
select concat('我叫',sname,',今年',age,'明年',age+1,'岁') from stu;
-- instr(string,substring),返回substring在string中出现的位置,没有返回 0
select instr('java','c');
select instr(sname,'三') from stu;

-- 转大写,转小写
select ucase('abc'),lcase('abc');
-- left(string2,length) 从 string2 中的左边起取 length 个字符
select left('java',2)
select left(sname,1) from stu; -- 取出姓氏

-- length 获得长度 , utf8中,一个中文三个字节
select length(sname),sname from stu;
select length('abc');

-- 替换
-- REPLACE (str ,search_str ,replace_str ) 在 str 中用 replace_str 替换 search_str
select replace('java','av','AV');
select replace(sname,'三','叁') from stu;

-- SUBSTRING (str , position [,length ] 截取
select substring('java',2); -- 从第2位,取到末尾
select substring('java',2,2); -- 从第2位,取2个
-- 取出stu表中姓名,姓,名
select sname 姓名 ,left(sname,1) 姓,substring(sname,2) 名 from stu;

insert into stu (sname) value('java');
insert into stu (sname) value(substring('java',2,2));
update stu set sname = left('史密斯',1) where sid = 1011

2.2 数学函数

函数	解释
ABS (number2 )	绝对值
BIN (decimal_number )	进制转二进制
CEILING (number2 )	向上取整
CONV(number2,from_base,to_base)	进制转换
FLOOR (number2 )	向下取整
FORMAT (number,decimal_places )	保留小数位数
HEX (DecimalNumber )	转十六进制
LEAST (number , number2 [,…])	求最小值
MOD (numerator ,denominator )	求余
RAND([seed])	RAND([seed]),seed是种子,可不写.写了随机数固定
ROUND(x,[d])	将x四舍五入,d是保留的位数,可不写
TRUNCATE(X,D)	截取

-- =========== 数学函数 ============
-- 绝对值
select abs(-1);
select abs(1);
-- 向上取整,向下取整
select ceiling(10.1),floor(10.1);
-- 保留几位小数
select format(1.12345,3);
-- 随机数,0-1之间
select rand();
insert into stu (sid) values (rand() * 1000)
-- 小数四舍五入,可以指定小数点保留的位数
select round(5.12645,2);
-- truncate() 截取数据,后面指定保留的小数点位数
select truncate(5.12645,2);

2.3 日期函数【重要】

函数	解释
SYSDATE()	当前时间
ADDTIME (date2 ,time_interval )	将time_interval加到date2
CURRENT_DATE ( )	当前日期
CURRENT_TIME ( )	当前时间
CURRENT_TIMESTAMP ( )	当前时间戳
DATE (datetime )	返回datetime的日期部分
DATE_ADD (date2 , INTERVAL d_value d_type )	在date2中加上日期或时间
DATE_SUB (date2 , INTERVAL d_value d_type )	在date2上减去一个时间
DATEDIFF (date1 ,date2 )	两个日期差
NOW ( )	当前时间
YEAR\|MONTH\|DAY (datetime )	年月日

-- =========== 日期函数 ============
-- 获得当前时间
select sysdate();
select now();
insert into t10 values (rand()*1000,sysdate());
select current_date(); -- 当前日期
select current_time();  -- 当前时间
select current_timestamp(); -- 当前日期时间
-- 添加时间
select addtime('17:19:10','01:00:50')
-- 添加日期
select adddate('2000-01-30',interval 2 year);
select date_add('2000-01-30',interval 2 month);
select date_add('2000-01-30',interval 2 day);
-- 时间相减
select date_sub('2000-01-30',interval 2 month);
-- 日期相差多少天
select datediff('2022-01-01',now())
-- 获得单独年,月,日
select year(now());
select month(now());
select day(now());
-- 当月生日人数
select count(*) from t10 where month(birthday) = month(now())

2.4 日期字符串转换函数【重要】

函数	解释
date_format(日期,模板)	日期 --> 字符串
str_to_date(字符串,模板)	字符串 --> 日期

%Y：代表4位的年份

%y：代表2为的年份

%m：代表月, 格式为(01……12)

%c：代表月, 格式为(1……12)

%d：代表月份中的天数,格式为(00……31)

%e：代表月份中的天数, 格式为(0……31)

%H：代表小时,格式为(00……23)

%k：代表小时,格式为(0……23)

%h：代表小时,格式为(01……12)

%I：代表小时,格式为(01……12)

%l ：代表小时,格式为(1……12)

%i：代表分钟, 格式为(00……59)

%r：代表时间,格式为12 小时(hh:mm:ss [AP]M)

%T：代表时间,格式为24 小时(hh:mm:ss)

%S：代表秒,格式为(00……59)

%s：代表秒,格式为(00……59)

-- =========== 日期/字符串转换函数 ============
/*
 日期 --> 字符串  date_format(date,'%Y-%m-%d')
 字符串 --> 日期 str_to_date('datestr','%Y-%m-%d') 
 ---------------------
 日期模板
 %Y年 %m月 %d日
 %H时 %i分钟 %S秒
*/
select date_format(now(),'%Y年%m月%d日')
select str_to_date('2022年11月18日','%Y年%m月%d日')

insert into t10 (id,birthday) value (1,str_to_date('2020-01-01','%Y-%m-%d'))

2.5 流程函数【重要！！】

函数	解释
IF(expr1,expr2,expr3)	如果expr1为真，则返回expr2，否则返回expr3
IFNULL(expr1,expr2)	如果 expr1不是NULL,则返回expr1，否则返回expr2；一般用来替换NULL值，因为NULL值是不能参加运算的
CASE WHEN [expr1] THEN [result1]… ELSE [default] END	如果expr1是真, 返回result1,否则返回default
CASE [value] WHEN [value1] THEN[result1]… ELSE[default] END	如果value等于value1, 返回result1,否则返回default

-- 范围判断
-- CASE WHEN [expr1] THEN [result1]… ELSE [default] END	如果expr是真, 返回result1,否则返回default
-- 查询学生id,姓名,成绩,及等级(60以下不及格,60-70,及格,71-80,中等,81-90良好,91-100优秀)
select sid,sname,score,case
when score < 60 then '不及格'
when score <= 70 then '及格'
when score <= 80 then '中等'
when score <= 90 then '良好'
else '优秀'
end as 等级

from stu

二、事务

2.1 介绍

什么是事务?

transaction 事务

事务是一个原子操作。是一个最小执行单元。可以由一个或多个SQL语句组成，在同一个事务当中，所有的SQL语句都成功执行时，整个事务成功，有一个SQL语句执行失败，整个事务都执行失败。

同对才对，一错就错

场景:

银行转账操作,A账号要给B账户转钱. A原有1000块,B原有1000块.A转账给B 100元

A的钱要减少,B的钱要增多
update A set money = money - 100 where id = a

-- 服务器出现异常,导致后面的sql没有执行

update B set money = money + 100 where id = b

2.2 mysql中事务

目前使用的mysql支持事务操作
mysql默认是自动提交事务的,每个sql语句都是单独事务
通过命令查询当前事务的提交方式 SHOW VARIABLES like ‘autocommit’
通过命令设置自动提交关闭 set autocommit = off / 或者= 0 关
set autocommit = on / 或者= 1 开

事务的操作
开启事务 start transaction 或者 begin
提交事务 commit
回滚事务 rollback

2.3 演示事务

准备数据

CREATE TABLE `account` (
 `id` int(50) NOT NULL,
 `name` varchar(50) NOT NULL,
 `money` int(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO account VALUES(1,'张三',1000);
INSERT INTO account VALUES(2,'李四',1000);

-- =============================================
-- 开启事务
start transaction;
-- 开始转账
update account set money = money - 100 where id = 1
-- 出大事了,后面执行不了
update account set money = money + 100 where id = 2
-- 如果一切正常,提交事务
commit;
-- 服务器出现异常,要回滚
rollback;


-- 查询当前事务提交方式
SHOW VARIABLES  like 'autocommit';
-- 手动控制事务,自动提交关闭
set autocommit = off;

-- ============= java 伪代码 ==================
try{
 conn.setAutocommit(false); -- 自动提交,开启手动事务
 conn.execute("update ....")
 System.out.print(1/0)
 conn.execute("update ....")
 conn.commit();   -- 提交
}catch(Exception e) { -- 如果有异常
 conn.rollback();  -- 回滚
}

-- 后面学框架,只需要配置一下就ok

2.4 事务特性

事务的特性(ACID)

原子性（Atomicity）：指事务的整个操作是一个整体，要么都成功,要么都失败
一致性（Consistency）：事务必须使数据库从一个一致性状态变换到另外一个一致性状态。转账前和转账后的总金额不变。
隔离性（Isolation）：事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
讲解:一个事务 A 开始事务,修改 a 表数据,自己查询数据,已经修改,并提交
另外开启一个事务 B:操作同一个表-> 修改 a 表其他列数据,自己查看已经修改
但是在事务提交前, A事务再查表数据,并未看见 B事务做出的修改.同样 B 事务也看不见 A 事务做出的修改.

持久性（Durability）：指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。

2.5 事务的隔离机制

数据库有不同的隔离机制/隔离级别

读未提交-READ UNCOMMITTED: 赃读、不可重复读、虚读都有可能发生。

读已提交-READ COMMITTED: 避免赃读。不可重复读、虚读都有可能发生。

（oracle 默认的）

可重复读-REPEATABLE READ:避免赃读、不可重复读。虚读有可能发生。

（mysql 默认）,行锁

串行化-SERIALIZABLE: 避免赃读、不可重复读、虚读。

串行化,其实是表锁

查看当前数据库的隔离级别: SELECT @@TX_ISOLATION;

总结

1 什么是事务
2 mysql事务怎么操作
3 事务什么特点
4 什么是事务隔离
5 有哪些级别,简单解释
6 mysql默认什么级别

三、索引

是什么?

索引是一种数据结构,可以更方便查询数据.

作用?

提高查询效率.

索引的分类

主键索引：在数据表的主键字段创建的索引，这个字段必须被 primary key 修饰，每张表只能有一个主键
唯一索引：在数据表中的唯一列创建的索引(unique)，此列的所有值只能出现一次，可以为 NULL
普通索引：在普通字段上创建的索引，没有唯一性的限制
复合索引：两个及以上字段联合起来创建的索引
全文索引

索引的使用

创建
create index 索引名 on 表名(列);
ps: 如果表中已经有大量数据,再创建索引,也很耗时

命中索引, select 语句中 where条件得使用索引列
删除 drop index 索引名 on 表名;

演示

准备了一个表,表有300w条数据, 没有索引时查询

select count(id) from tb_index_test;

select * from tb_index_test where id = 2341234;

-- 模糊查询不会命中索引
select * from tb_index_test where name like '%2341234%';
-- 命中索引
select * from tb_index_test where name = '测试2341234';

-- 创建索引
create index name_index on tb_index_test(name);
-- 删除索引
drop index name_index on tb_index_test

索引优缺点

优点: 根据索引查数据很快

缺点: 索引建立,删除,更新很慢. 如果对索引列经常增删改,也会效率很慢

索引本身也是一种数据结构,也会占磁盘空间

mysql索引的底层结构是B+树, 后续慢慢了解一下

扩展阅读:
MySQL | 稀客大大 (heyige.cn)
MYSQL索引连环18问 | 稀客大大 (heyige.cn)

四、存储引擎

存储引擎是数据库存储数据的一种格式.

引擎分类

MyISAM 和 InnoDB 的区别有哪些：

InnoDB 支持事务，MyISAM 不支持
InnoDB 支持外键，而 MyISAM 不支持
InnoDB 是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高；MyISAM 是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针，主键索引和辅助索引是独立的。
Innodb 不支持全文索引，而 MyISAM 支持全文索引，查询效率上 MyISAM 要高；
InnoDB 不保存表的具体行数，MyISAM 用一个变量保存了整个表的行数。
MyISAM 采用表级锁(table-level locking)；InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁。