四, mysql 常用函数---主要内容和思路来源:开课吧学习笔记
1, 字符串函数
字符串合并 concat, concat_ws:
concat(s1, s2, ..., sn), 将多个对象合并, 某个对象为
null, 全体为空.
%%sql
select concat('bill', 'is', 'a', 'student.') as bill
* mysql+pymysql://root:***@localhost/test
1 rows affected.
bill
billisastudent.
%%sql
select concat(curdate(), '_', 12.34, '_')
* mysql+pymysql://root:***@localhost/test
1 rows affected.
concat(curdate(), '_', 12.34, '_')
2020-05-17_12.34_
concat_ws(sep, s1, s2, ..., sn), 用分隔符
sep 将对象连接,
sep 为 null, 全体为空, 其他为 null 无影响.
%%sql
select concat_ws('_', 'bill', 'is', 'a', 'student') as bill
* mysql+pymysql://root:***@localhost/test
1 rows affected.
bill
bill_is_a_student
%%sql
select concat_ws(curdate(), '_', 12.34, '什么情况', '_')
* mysql+pymysql://root:***@localhost/test
1 rows affected.
concat_ws(curdate(), '_', 12.34, '什么情况', '_')
_2020-05-1812.342020-05-18什么情况2020-05-18_
字符串比较 strcmp:
strcmp(s1, s2), s1 > s2 返回 1, s1 < s2 返回 -1, 等于返回 0
%%sql
select strcmp(1, 2),
strcmp('a', '3'),
strcmp('2', 1.6)
* mysql+pymysql://root:***@localhost/test
1 rows affected.
strcmp(1, 2)
strcmp('a', '3')
strcmp('2', 1.6)
-1
1
1
字符串长度 length, char_length:
length() 返回字符串的长度，以字节为单位, utf8 编码下, 一个汉字三个字节，一个数字或字母一个字节, gbk 下, 一个汉字两个字节，一个数字或字母一个字节.
%%sql
select 'bill', length('bill'), '中文', length('中文')
* mysql+pymysql://root:***@localhost/test
1 rows affected.
bill
length('bill')
中文
length('中文')
bill
4
中文
6
char_length() 返回字符串的长度，以字符为单位.
%%sql
select 'bill', char_length('bill'), '中文', char_length('中文')
* mysql+pymysql://root:***@localhost/test
1 rows affected.
bill
char_length('bill')
中文
char_length('中文')
bill
4
中文
2
大小写转换 lower, upper:
%%sql
select 'bill', upper('bill'), 'BILL', lower('BILL')
* mysql+pymysql://root:***@localhost/test
1 rows affected.
bill
upper('bill')
BILL
lower('BILL')
bill
BILL
BILL
bill
字符串查找
find_in_set(s1,s2) 返回 s1 在 s2 中的位置, 只能用逗号
,隔开.
%%sql
select find_in_set('bill', 'bob, bill, jack,bill') as 'bill 的位置'
* mysql+pymysql://root:***@localhost/test
1 rows affected.
bill 的位置
4
field(s, s1, s2, ..., sn) 返回第一个字符串 s 匹配的字符串的位置.
%%sql
select field('bill', 'bob', 'jack', 'bill') 'bill 的位置'
* mysql+pymysql://root:***@localhost/test
1 rows affected.
bill 的位置
3
locate(s1, s),
position(s1 in s) 返回 s1 在 s 中开始的位置.
%%sql
select locate('bill', 'bob jack bill')
* mysql+pymysql://root:***@localhost/test
1 rows affected.
locate('bill', 'bob jack bill')
10
%%sql
select position('bill' in 'bob jack bill')
* mysql+pymysql://root:***@localhost/test
1 rows affected.
position('bill' in 'bob jack bill')
10
字符串截取子串
left(str, n) 获取字符串的前 n 个字符.
%%sql
select left('中bill文', 2)
* mysql+pymysql://root:***@localhost/test
1 rows affected.
left('中bill文', 2)
中b
right(str, n) 获取字符串后 n 个字符.
%%sql
select right('中bill文', 2)
* mysql+pymysql://root:***@localhost/test
1 rows affected.
right('中bill文', 2)
l文
substring(str, m, n) 获取字符串从 m 开始的 n 个字符.
%%sql
select substring('中bill文', 2, 4)
* mysql+pymysql://root:***@localhost/test
1 rows affected.
substring('中bill文', 2, 4)
bill
去除字符串的首尾空格
ltrim() 去除首空格,
rtrim() 去除尾空格,
trim() 去除两边空格.
%%sql
select concat(ltrim(' bill '), rtrim('is_ '), 'a', trim(' _student ')) bill
* mysql+pymysql://root:***@localhost/test
1 rows affected.
bill
bill is_a_student
替换字符串
insert(str, m, n, newstr) 将字符串 str 中, 位置从 m 起长度为 n 的子串替换为 newstr. m 超过字符串长度, 返回 str, n 大于 str 剩余长度, 从 m 开始替换, 任何参数为 null, 返回 null.
%%sql
select '这是mysql数据库' MySQL, insert('这是mysqlL数据库', 3, 5, 'Oracle') 转为
* mysql+pymysql://root:***@localhost/test
1 rows affected.
MySQL
转为
这是mysql数据库
这是OracleL数据库
%%sql
select '这是mysql数据库' MySQL, insert('这是mysql数据库', 3, 10, 'Oracle') 转为
* mysql+pymysql://root:***@localhost/test
1 rows affected.
MySQL
转为
这是mysql数据库
这是Oracle
replace(str, substr, newstr) 用 newstr 替换 str 中的 substr.
%%sql
select '这是mysql数据库' mysql, replace('这是mysql数据库', 'mysql', 'Oracle') Oracle
* mysql+pymysql://root:***@localhost/test
1 rows affected.
mysql
Oracle
这是mysql数据库
这是Oracle数据库
2, 数值函数
rand() 返回 0~1 之间的随机数, ceil(x) 返回大于等于 x 的最小整数, floor(x) 返回小于等于 x 的最大整数.
%%sql
select rand(), ceil(3.14), floor(3.14)
* mysql+pymysql://root:***@localhost/test
1 rows affected.
rand()
ceil(3.14)
floor(3.14)
0.806298252014721
4
3
truncate(x, y) 返回 x 保留小数点后 y 位的数, round(x) 返回 x 四舍五入的整数, round(x, y) x 四舍五入保留小数点后 y 位.
%%sql
select truncate(3.14159, 4), round(3.1415), round(3.14159, 4)
* mysql+pymysql://root:***@localhost/test
1 rows affected.
truncate(3.14159, 4)
round(3.1415)
round(3.14159, 4)
3.1415
3
3.1416
3, 日期时间函数
curdate() 返回当前日期, curtime() 返回当前时间, now() 返回当前日期时间, week(date) 返回日期在一年的第几周, year(date) 返回日期所在年份, hour(time) 返回时间的小时数, minute(time) 返回时间的分钟数, monthname(date) 返回日期的月份名称.
%%sql
select curdate() 日期, curtime() 时间, now() 当前
* mysql+pymysql://root:***@localhost/test
1 rows affected.
日期
时间
当前
2020-05-18
11:12:06
2020-05-18 11:12:06
%%sql
select week('2020-05-18') w,
year('2020-05-18') y,
hour('10:57:19') h,
minute('98-02-03 10:05:03') min,
monthname('98-02-05') m
* mysql+pymysql://root:***@localhost/test
1 rows affected.
w
y
h
min
m
20
2020
10
5
February
4, 系统信息函数
version() 返回数据库版本号, database() 返回当前数据库名, user()返回当前用户名.
%%sql
select version() 版本, database() 数据库, user() 用户
* mysql+pymysql://root:***@localhost/test
1 rows affected.
版本
数据库
用户
5.7.27-log
test
root@localhost
五, 索引
(此知识点需要重新深入理顺逻辑)
1, 索引介绍
索引是存储引擎用于快速查找数据记录的一种数据结构, 而不必扫描整个数据库, 索引优化是查询性能优化非常有效的手段, 根据存储类型可以分为 B(B+) 树索引(innodb 引擎), 哈希索引(memory 引擎).
索引确保查询速度, 同时还能保证数据完整性, 通常主键, 唯一键, 联合键等有约束功能的字段就是索引, 还可以设置无约束的普通索引.
2, 普通索引的创建
无约束条件, 可以创建在任何字段上, 有三种方法创建索引.
建表时指定索引:
create table 表名(
字段 数据类型 属性 ...,
...,
index|key [索引名](字段, [(长度)] [asc|desc]));
在表中指定字段作为索引:
create index 索引名 on 表名 (字段 [(长度)] [asc|sesc]);
修改表结构指定索引:
alter table 表名 add index|key 索引名 (字段 [(长度)] [asc|desc])
创建多列索引时, 上述语句中指定多个字段即可
创建唯一索引, 上述语句 index 前加上 unique
全文索引, 主要设置在数据量较大的字符串类型字段, 创建全文索引, 在上述语句 index 前加上 fulltext
3, 删除索引
创建主键, 外键等自动创建索引, 删除键, 并没有删除索引.
drop index 索引名 on 表名;
六, 视图
1, 视图介绍
视图是从数据库的基本表(可以是多个不同的表)中, 将某些有特定联系和特定需要的数据筛选出来联系在一起动态生成的虚拟表, 相当于查询语句得到的中间结果.
视图的建立和删除不影响基本表, 视图内容的更新(增删改)直接影响基本表, 但视图来自多个表时, 不允许添加和删除数据.
视图的作用:
a, 对需要重复查询的数据, 直接建立视图便可重复使用, 减少重复的甚至复杂的 sql 语句查询.
b, 如果数据库重构, 例如表拆分, 之前的脚本程序可能会受影响, 利用视图可以恢复脚本使用.
c, 可以对不同的用户设定不同的视图, 隐藏需要保密的信息, 提高数据安全性.
d, 把需要的数据, 创建相应的视图, 使数据更加清晰.
2, 视图的基本操作
视图不能和表名和其他视图重名, 视图是封装了查询语句中间表, 查询视图数据和查询表一样.
创建视图:
create view 视图名 as 查询语句;
查看视图:
show table status; #显示所有的表与视图
show create view 视图名; # 显示创建视图的语句
desc 视图名; # 显示视图结构
删除视图:
drop view 视图名;
修改视图:
create or replace view 视图名 as 查询语句; # 有视图就修改, 没有就创建
alter view 视图名 as 查询语句;
# 视图是虚表, 还可以删除视图重新创建.
七, 数据库设计三范式
设计关系型数据库, 遵从一定的规范要求, 越高的范式, 数据冗余越小, 但是会降低数据读写效率, 效率低, 又需要反范式.
第一范式(1NF): 原子性, 字段不可再分, 某个属性有多个值时, 必须拆分为不同属性, 也就是一个字段只存储一项信息. 关系型数据库, 自动满足第一范式.
第二范式(2NF): 在第一范式基础上建立. 有主键, 确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关(主要针对联合主键而言). 也就是说在一个数据库表中，一个表只能保存一种数据，不可以把多种数据保存在同一张数据库表中. 例如一张订单编号和商品编号为联合主键, 存储订单信息和商品信息的表, 就不满足第二范式.
第三范式(3NF): 必须先满足第二范式. 非主键字段不能相互依赖, 每一列与主键直接依赖, 不存在传递依赖. 例如一个订单信息表, 订单编号为主键, 可将客户编号作为外键与订单信息表建立联系.
扫一扫
4094
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
