1.数据库备份与恢复
1.建立表的外键
如果出现错误，需要考虑以下三个方面的原因：
- 外键连接的两个字段长度，类型是否相同。（是否为Null值不影响）
- 单独的表是否缺少某项依赖或者主键。
- 表的外键名称不能相同
如果所有都相同，清空数据也出错的情况：
- 删除该字段，再重新建立原字段。最后再建立外键
2.axios的请求参数
可以使用列表作为请求体的内容，而不以json对象作为body数据
如: axios的get, post等方法, 可以传递列表作为请求数据.
3.SQL注入处理
恶意传递有sql意义的字符（如：含引号的数据），就会出现sql数据暴露的情况
所以，一般采用预查询。即杜绝出现多余引号的情况。
2.常用的sql语句
注意：在写sql语句时，先在navicat建立查询，执行sql语句是否可以正常运行。
1.insert 插入
//向students表中插入一行新数据
insert into students values(NULL, "王刚", "男", 20, "13811371377");
// 向students表中插入一行新数据
insert into students(id,username) values(NULL, "王刚");
注意：NULL表示自增id, 字段串需要引号。
插入日期时 ，也是使用字符串格式。 但是在where语句中，就是日期格式。
2.update更新
// 更新students表中的age字段
update students set age=age+1
// 更新students表中的password字段
update students set password='xxxx' where id = 2
3.select查询
// 查询所有users表中的指定的用户名数据
select * from users where username='xiaoming';
4.delete删除
// 删除students表中名为小明的行数据
delete from studens where name="小明"
// 删除students表中的所有数据
delete from students
// 删除students表中的所有数据（效率更高）
truncate students
1.选择删除命令
当你不再需要该表时， 用 drop;
当你仍要保留该表，但要删除所有记录时， 用 truncate;
当你要删除部分记录时, 用 delete。
2.truncate 与delete 比较
1、truncate table 在功能上与不带 WHERE 子句的 delete语句相同：二者均删除表中的全部行。
2、truncate 比 delete速度快，且使用的系统和事务日志资源少。
5.SQL函数
SELECT COUNT(*) FROM student;
COUNT(DISTINCT 字段)，返回不同的非NULL值数目；若找不到匹配的项，则COUNT(DISTINCT)返回 0 。
1.CONCAT(A, B) – 连接两个字符串值以创建单个字符串输出。通常用于将两个或多个字段合并为一个字段。
MySQL的concat函数在连接字符串的时候，只要其中一个是NULL,那么将返回NULL
concat_ws(',','11','22',NULL); 在执行的时候,不会因为NULL值而返回NULL
2、FORMAT(X, D)- 格式化数字X 到D 有效数字。
3、CURRDATE(), CURRTIME()- 返回当前日期或时间。
4、NOW（） – 将当前日期和时间作为一个值返回。
5、MONTH（），DAY（ ），YEAR（），WEEK（），WEEKDAY（） – 从日期值中提取给定数据。
6、HOUR（），MINUTE（）， SECOND（） – 从时间值中提取给定数据。
7、DATEDIFF（ A，B） – 确定两个日期之间的差异， 通常用于计算年龄
8、SUBTIMES（ A， B） – 确定两次之间的差异。
9、FROMDAYS（ INT） – 将整数天数转换为日期值。
3.辅助型sql组合语句
1. if.else.条件判断
没有多分支语句，可以允许只有if语句
单条语句不需要begin…end， 但语句块需要使用
if(@sex == '1' )
begin
print('男')
end
else
begin
print('女')
end
2.case.end 多分支*
1.基本概念
// 多分支结构，类似 switch 和 三目运算作用。
case ... end + where .. then
// （1.switch结构
case sex
when '1' then '男'
when '2' then '女’
else '其他'
end
// （2.三目运算结构
case sex when '1' then '男' else '女' end
2.使用实战
// 在更新的时候使用三目运算
update carts set is_checked=(
case is_checked when '1' then '0' else '1' end
), updated_at='2022-06-06 22:20:21'
where user_id = 45 and id in ("1","2")
3.where.and/or过滤
// 1.where...and...交集过滤
where username="123" and password="xxx"
// 2.where...or...并集过滤
where username="123" or password="xxxx"
4.like 模糊匹配
注意：有字符串出现 ，就一定要添加引号
where username like '%xxx%' 模糊匹配，两侧字符不确定
where username like 'xxx_' 模糊匹配，右侧只有一个字符
where username like '_xxx' 模糊匹配，左侧只有一个字符
5.join…on类型的连接
left join on左连接，他是主表在左表，右表没有的数据会显示null；
right join on右连接，他是主表在右表，左表没有的数据会显示null；
join on 内连接，他是连接你关联的表 on 后面跟条件
注意：连接的优先级比where语句高
// 1.join 。。on内连接
select *,table_user.username from table_board
join table_user
on table_board.user_id = table_user.id
注意点：两个表中有相同字段的，一定要声明具体的字段，否则默认后面的表优先显示。
2.left join。。on连接 （right join.. on同理）
select *,table_user.username from table_board
left join table_user
on table_board.user_id = table_user.id
注意：左连接是以左边为基表，on 的条件不管有没有，都会以左连接的表为基表。
6.orderBy排序
// 排序规则：ASC 升序 DESC降序, 默认升序
// 单个字段
ORDER BY create_time DESC
// 多个字段
order by create_time,counts DESC
//rand随机函数
// 一般和order by， limit混合使用。
select * from categories order by rand() limit 2
7.in, not…in归属
// 1.in 归属
select * from user where number in ('id1','id2','id3');
// 2.in 归属
select * from user where age in (10,11);
8.between.and区间
// [ ]包括两边的端点值
select * from test2 where age between 10 and 14
9.limit 限制查询
// 页数索引（从0开始）, 页的大小
// 当(index=0) limit size 的时候等同于limit 0,size
// 查询页大小为2的时候，第1+1页的数据
select * from test2 limit 1,2
10.as 重命名字段*
// 普通字段重命名
select id as user_id from user
// count函数的重命名 (count 计数函数)
select goods,count(id) as comments_count from goods
// concat函数的重命名 (concat 连接函数)
select goods,`concat("${img_domain}",cover) as cover_url` from goods
11.where in子查询*
// where in 子查询数据
select id,pid,name where pid = 0 and level = 1 and id in (
select pid from categories where level not in (1)
)
12.select 中间表/字段*
一般和left join …on一起使用，有添加新字段的效果（单个字段）
// select + "*" 当作中间表
select * from (select * from categories) as children
// select + '特定字段' 当作子查询字段
update userinfo set user_id =( select user.id from user ) where id >=103 and id<=500;
// select + "*" 当作中间表， 作为连接表(其实也可以是当前表)
select categories.pid,categories.name,cate.id as children from categories
left join (select * from categories) as cate
on categories.id = cate.pid and cate.level not in (1)
where categories.pid = 0 and categories.level = 1
13.json_object联合函数*
// group_concat 和 json_object混合使用，有添加新字段的效果（单个数组类型字段）
// 因为sql语法中没有数组和对象，所以需要前端进行let newa = JSON.parse(a), 将字符串解析出来。
1.将单个学生各科成绩转换为数组JSON串，只返回一个字段
select concat('[', group_concat(json_object('id',id,'name',name )), ']') as list from test2
## 查询结果 list字段: [{"id": 1, "name": "数学" },{"id": 2, "name": "语文" }]
2.将单个学生各科成绩转换为数组JSON串，放在一个单独的字段里
select *,(
select concat('[', group_concat(json_object('id',id,'name',name )), ']') from test2
) as list from test2
## 查询结果
[
{id:1,name:'hello',list: `[{"id": 1, "name": "数学" },{"id": 2, "name": "语文" }]` }
{id:1,name:'hello',list: `[{"id": 1, "name": "数学" },{"id": 2, "name": "语文" }]` }
]
14.sql语法知识
// 声明字段，并设置初始值（mysql 5.7 已经取消declare， 可以直接使用变量）
declare @starttime datetime
set @starttime=getdate()
select * from tb -- 执行语句
select datediff(ss,@starttime,getdate())
4.数据库类型的操作
1.mysql命令
1.登录数据库
1.基本语法
mysql -h 主机名 -u 用户名 -p
-h : 该命令用于指定客户端所要登录的MySQL主机名, 登录当前机器该参数可以省略;
-u : 所要登录的用户名;
-p : 告诉服务器将会使用一个密码来登录, 如果所要登录的用户名密码为空, 可以忽略此选项。
2.使用实例
命令：mysql -u root -p
2.修改数据库密码
1.方法三：（建议使用）
set password for root@localhost = pasword('xxxxx'); 。 设置密码为123456(进入mysql终端)
set password for root@localhost = ''; 设置密码为空(进入mysql终端)
2.方法一：
mysql admin -u root -p 111 password xxx 设置密码为123456
注意：最后刷新密码(flush privileges;)
3.方法二：
SET PASSWORD = PASSWORD('xxxx'); 设置密码为123456(进入mysql终端)
SET PASSWORD = ''; 设置密码为空(进入mysql终端)
注意：最后刷新密码(flush privileges;)
3.重新启动数据库（管理员模式）
net stop mysql （或者MySQL57）
net start mysql （或者MySQL57）
4.其它数据库命令
select version(); 查看数据库版本(进入mysql终端)
2.show显示数据库
show databases; 显示所有数据库
3.use 选中数据库
use students; 选中students数据库
4.backup备份数据库
// 将数据库保存至磁盘目录 C:/MySQL
backup database students to disk = 'C:/MySQL';
5.restore还原数据库
// 从磁盘的C:/MySQL/hello.sql 数据库文件中 恢复至当前的数据库中
restore database students from disk = 'C:/MySQL/hello.sql'
5.数据表类型的操作
1.create创建表
// 创建msg数据库表
create table msg(
id int primarykey auto_increment,
title varchar(60),
name varchar(10),
content varchar(1000)
);
2.alter选择表
// 重命名students表为workmates
alter table students rename workmates
// 删除users表中的sex列
alter table users drop sex;
// 修改students中tel列为 telphone，并且设置新数据类型
alter table students change tel telphone char(13) default "-";
// 添加students表address字段
alter table students add address char(60)
3.drop删除库/表
drop database students; 删除students数据库
drop table workmates; 删除workmates表
4.select的高级操作
// 1.查询指定数据库下的所有数据库表
select table_name from information_schema.TABLES where table_schema = 'hello'
// 2.查询数据库表的所有字段
show full columns from test1
6.高性能数据操作
1.index索引
只是加快连接表时的访问速度。
2.trigger触发器
1.触发器知识
（1.触发器优点
-
触发器是在某个事件发生后自动激活而执行的
-
触发器可以实现比约束更为复杂的完整性要求
-
触发器可以根据表数据修改前后的状态，根据其差异采取相应的措施
-
触发 器可以防止恶意的或者错误的insert, update, delete操作
（2.基本知识点
-
insert触发器
- 添加数据后，原表和inserted表中都有数据、
- exists(select * from inserted)
-
delete触发器
- 添加数据后，原表已经删除数据，而delete表中有数据、
- exists(select * from deleted)
-
delete触发器
-
修改数据后，会生成delete表, inserted表 、
-
exists(select * from deleted) and exists (select * from inserted)
-
2.触发器实例
- 当在学生表S中删除某个学生，在学生选课表SC中的也删除
1.提前创建触发器
create trigger del_s on S
after delete
as
delete from SC where SC.Sno in (select Sno from deleted)
go
2.执行语句
delete from S where Sno = 'S1'
3.也可以删除触发器
drop trigger del_s
- 在学生选课表SC中添加新数据时，新学生的学号Sno必须已经存在于基本信息表S中
1.提前创建触发器，不存在则回滚
create trigger insert_sc on SC
after insert
as
if exists (select * from inserted where Sno in (select Sno from S))
print "添加成功"
else
rollback transaction
go
2.然后执行语句
insert into SC values(1,1,1,1)
3.其它的还有 update, 触发器
略
3.Transaction事务
触发器里面包含事务
java里面有事务的语法结构
基本语法
登录后复制
-- 使用set语句来改变自动提交模式
SET autocommit = 0; /*关闭*/
SET autocommit = 1; /*开启*/
-- 注意:
-- 1.MySQL中默认是自动提交
-- 2.使用事务时应先关闭自动提交
-- 开始一个事务,标记事务的起始点
START TRANSACTION
-- 提交一个事务给数据库
COMMIT
-- 将事务回滚,数据回到本次事务的初始状态
ROLLBACK
-- 还原MySQL数据库的自动提交
SET autocommit =1;
-- 保存点
SAVEPOINT 保存点名称 -- 设置一个事务保存点
ROLLBACK TO SAVEPOINT 保存点名称 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名称 -- 删除保存点
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-MI3gYmdO-1661414994999)(C:/Users/Icy-yun/AppData/Roaming/Typora/typora-user-images/image-20220620112653704.png)]
7. 数据库表的字段
1.int 整数字段
2.varchar 可变字段
3.char 不可变字段
4.float 浮点数字段
5.text 文本字段
blob 保存的是二进制数据，利用这个特性，可以把图片存储到数据库里面。而text只能存储文本。
1.TEXT
TEXT有4有类型:
tinytext 256bytes
text 64kb
mediumtext 16Mb
longtext 4GB
2.BLOB
tinybold·
blod、
mediumblod
longblod
如果是图片：可以考虑使用 “|” 分隔多个路径
6.json对象字段
7.datetime日期对象
与timestamp字段的区别
8.优先字段先用原则
首选int整型，再考虑varchar字段。
user_id 连接用户外键，建议用int
amount 金额，建议用int
phone 电话号码，会超长，建议用varchar
8.数据库选择编码
1.utf8字符编码
untf8是unnicode字段集下的编码格式，可以对每一种语言，采用唯一的字符表示
2.gb2312字符编码
3.gbk字符编码
4.ascii字符编码
9.MySQL函数
一、什么是函数：
函数存储着一系列sql语句，调用函数就是一次性执行这些语句。所以函数可以降低语句重复。【但注意的是函数注重返回值，不注重执行过程，故有一些语句是无法执行的。所以函数并不是单纯的sql语句集合。】
mysql已经提供了大部分常用的函数（已经定义好了的函数），详见：
1.字符串函数
| 函数 | 描述 | 实例 |
|---|---|---|
| ASCII(s) | 返回字符串 s 的第一个字符的 ASCII 码。 | 返回 CustomerName 字段第一个字母的 ASCII 码： SELECT ASCII(CustomerName) AS NumCodeOfFirstChar FROM Customers; |
| CHAR_LENGTH(s) – char_length | 返回字符串 s 的字符数 | 返回字符串 RUNOOB 的字符数 SELECT CHAR_LENGTH(“RUNOOB”) AS LengthOfString; |
| CHARACTER_LENGTH(s) – character_length | 返回字符串 s 的字符数 | 返回字符串 RUNOOB 的字符数 SELECT CHARACTER_LENGTH(“RUNOOB”) AS LengthOfString; |
| CONCAT(s1,s2…sn) – concat | 字符串 s1,s2 等多个字符串合并为一个字符串 | 合并多个字符串 SELECT CONCAT("SQL ", "Runoob ", "Gooogle ", “Facebook”) ASConcatenatedString; |
| CONCAT_WS(x, s1,s2…sn) – concat_ws | 同 CONCAT(s1,s2,…) 函数，但是每个字符串之间要加上 x，x 可以是分隔符 | 合并多个字符串，并添加分隔符： SELECT CONCAT_WS(“-”, “SQL”, “Tutorial”, “is”, “fun!”)ASConcatenatedString; |
| FIELD(s,s1,s2…) – field | 返回第一个字符串 s 在字符串列表(s1,s2…)中的位置 | 返回字符串 c 在列表值中的位置： SELECT FIELD(“c”, “a”, “b”, “c”, “d”, “e”); |
| FIND_IN_SET(s1,s2) – find_in_set | 返回在字符串s2中与s1匹配的字符串的位置 | 返回字符串 c 在指定字符串中的位置： SELECT FIND_IN_SET(“c”, “a,b,c,d,e”); |
| FORMAT(x,n) – format | 函数可以将数字 x 进行格式化 “#,###.##”, 将 x 保留到小数点后 n 位，最后一位四舍五入。 | 格式化数字 “#,###.##” 形式： SELECT FORMAT(250500.5634, 2); – 输出 250,500.56 |
| unix_timestamp(now()) | 获取当前的时间并且转化成时间戳的格式 | unix_timestamp(now()) ---- 1251884321 |
| INSERT(s1,x,len,s2) – insert | 字符串 s2 替换 s1 的 x 位置开始长度为 len 的字符串 | 从字符串第一个位置开始的 6 个字符替换为 runoob： SELECT INSERT(“google.com”, 1, 6, “runnob”); – 输出：runoob.com |
| LOCATE(s1,s) – locate | 从字符串 s 中获取 s1 的开始位置 | 获取 b 在字符串 abc 中的位置： SELECT LOCATE(‘st’,‘myteststring’); – 5 返回字符串 abc 中 b 的位置： SELECT LOCATE(‘b’, ‘abc’) – 2 |
| from_unixtime(1251884321); | 将时间戳转化成日期格式 | from_unixtime(1251884321); ----- 2009-09-02 17:38:41 |
| LCASE(s) – lcase | 将字符串 s 的所有字母变成小写字母 | 字符串 RUNOOB 转换为小写： SELECT LCASE(‘RUNOOB’) – runoob |
| LEFT(s,n) – left | 返回字符串 s 的前 n 个字符 | 返回字符串 runoob 中的前两个字符： SELECT LEFT(‘runoob’,2) – ru |
| LOWER(s) – lower | 将字符串 s 的所有字母变成小写字母 | 字符串 RUNOOB 转换为小写： SELECT LOWER(‘RUNOOB’) – runoob |
| LPAD(s1,len,s2) – lpad | 在字符串 s1 的开始处填充字符串 s2，使字符串长度达到 len | 将字符串 xx 填充到 abc 字符串的开始处： SELECT LPAD(‘abc’,5,‘xx’) – xxabc |
| LTRIM(s) – ltrim | 去掉字符串 s 开始处的空格 | 去掉字符串 RUNOOB开始处的空格： SELECT LTRIM(" RUNOOB") AS LeftTrimmedString;-- RUNOOB |
| MID(s,n,len) – mid | 从字符串 s 的 n 位置截取长度为 len 的子字符串，同 SUBSTRING(s,n,len) | 从字符串 RUNOOB 中的第 2 个位置截取 3个 字符： SELECT MID(“RUNOOB”, 2, 3) AS ExtractString; – UNO |
| POSITION(s1 IN s) – position | 从字符串 s 中获取 s1 的开始位置 | 返回字符串 abc 中 b 的位置： SELECT POSITION(‘b’ in ‘abc’) – 2 |
| REPEAT(s,n) – repeat | 将字符串 s 重复 n 次 | 将字符串 runoob 重复三次： SELECT REPEAT(‘runoob’,3) – runoobrunoobrunoob |
| REPLACE(s,s1,s2) – replace | 将字符串 s2 替代字符串 s 中的字符串 s1 | 将字符串 abc 中的字符 a 替换为字符 x： SELECT REPLACE(‘abc’,‘a’,‘x’) --xbc |
| REVERSE(s) – reverse | 将字符串s的顺序反过来 | 将字符串 abc 的顺序反过来： SELECT REVERSE(‘abc’) – cba |
| RIGHT(s,n) – right | 返回字符串 s 的后 n 个字符 | 返回字符串 runoob 的后两个字符： SELECT RIGHT(‘runoob’,2) – ob |
| RPAD(s1,len,s2) – rpad | 在字符串 s1 的结尾处添加字符串 s2，使字符串的长度达到 len | 将字符串 xx 填充到 abc 字符串的结尾处： SELECT RPAD(‘abc’,5,‘xx’) – abcxx |
| RTRIM(s) – rtrim | 去掉字符串 s 结尾处的空格 | 去掉字符串 RUNOOB 的末尾空格： SELECT RTRIM("RUNOOB ") AS RightTrimmedString; – RUNOOB |
| SPACE(n) space | 返回 n 个空格 | 返回 10 个空格： SELECT SPACE(10); |
| STRCMP(s1,s2) – strcmp | 比较字符串 s1 和 s2，如果 s1 与 s2 相等返回 0 ，如果 s1>s2 返回 1，如果 s1<s2 返回 -1 | 比较字符串： SELECT STRCMP(“runoob”, “runoob”); – 0 |
| SUBSTR(s, start, length) substr | 从字符串 s 的 start 位置截取长度为 length 的子字符串 | 从字符串 RUNOOB 中的第 2 个位置截取 3个 字符： SELECT SUBSTR(“RUNOOB”, 2, 3) AS ExtractString; – UNO |
| SUBSTRING(s, start, length) – substring | 从字符串 s 的 start 位置截取长度为 length 的子字符串 | 从字符串 RUNOOB 中的第 2 个位置截取 3个 字符： SELECT SUBSTRING(“RUNOOB”, 2, 3) AS ExtractString; – UNO |
| SUBSTRING_INDEX(s, delimiter, number) – substring_index | 返回从字符串 s 的第 number 个出现的分隔符 delimiter 之后的子串。 如果 number 是正数，返回第 number 个字符左边的字符串。 如果 number 是负数，返回第(number 的绝对值(从右边数))个字符右边的字符串。 | SELECT SUBSTRING_INDEX(‘ab’,'’,1) – a SELECT SUBSTRING_INDEX(‘ab’,'’,-1) – b SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(‘abcde’,‘‘,3),’’,-1) – c |
| TRIM(s) – trim | 去掉字符串 s 开始和结尾处的空格 | 去掉字符串 RUNOOB 的首尾空格： SELECT TRIM(’ RUNOOB ') AS TrimmedString; |
| UCASE(s) – ucase | 将字符串转换为大写 | 将字符串 runoob 转换为大写： SELECT UCASE(“runoob”); – RUNOOB |
| UPPER(s) – upper | 将字符串转换为大写 | 将字符串 runoob 转换为大写： SELECT UPPER(“runoob”); – RUNOOB |
2.数学函数
| 函数名 | 描述 | 实例 |
|---|---|---|
| ABS(x) – abs | 返回 x 的绝对值 | 返回 -1 的绝对值： SELECT ABS(-1) – 返回1 |
| ACOS(x) – acos | 求 x 的反余弦值(参数是弧度) | SELECT ACOS(0.25); |
| ASIN(x) – asin | 求反正弦值(参数是弧度) | SELECT ASIN(0.25); |
| ATAN(x) – atan | 求反正切值(参数是弧度) | SELECT ATAN(2.5); |
| ATAN2(n, m) – atan2 | 求反正切值(参数是弧度) | SELECT ATAN2(-0.8, 2); |
| AVG(expression) – avg | 返回一个表达式的平均值，expression 是一个字段 | 返回 Products 表中Price 字段的平均值： SELECT AVG(Price) AS AveragePrice FROM Products; |
| CEIL(x) – ceil | 返回大于或等于 x 的最小整数 | SELECT CEIL(1.5) – 返回2 |
| CEILING(x) – ceiling | 返回大于或等于 x 的最小整数 | SELECT CEIL(1.5) – 返回2 |
| COS(x) – cos | 求余弦值(参数是弧度) | SELECT COS(2); |
| COT(x) – cot | 求余切值(参数是弧度) | SELECT COT(6); |
| COUNT(expression) – count | 返回查询的记录总数，expression 参数是一个字段或者 * 号 | 返回 Products 表中 products 字段总共有多少条记录： SELECT COUNT(ProductID) AS NumberOfProducts FROM Products; |
| DEGREES(x) – degrees | 将弧度转换为角度 | SELECT DEGREES(3.1415926535898) – 180 |
| n DIV m – div | 整除，n 为被除数，m 为除数 | 计算 10 除于 5： SELECT 10 DIV 5; – 2 |
| EXP(x) – exp | 返回 e 的 x 次方 | 计算 e 的三次方： SELECT EXP(3) – 20.085536923188 |
| FLOOR(x) – floor | 返回小于或等于 x 的最大整数 | 小于或等于 1.5 的整数： SELECT FLOOR(1.5) – 返回1 |
| GREATEST(expr1, expr2, expr3, …) – greatest | 返回列表中的最大值 | 返回以下数字列表中的最大值： SELECT GREATEST(3, 12, 34, 8, 25); – 34 返回以下字符串列表中的最大值： SELECT GREATEST(“Google”, “Runoob”, “Apple”); – Runoob |
| LEAST(expr1, expr2, expr3, …) – least | 返回列表中的最小值 | 返回以下数字列表中的最小值： SELECT LEAST(3, 12, 34, 8, 25); – 3 返回以下字符串列表中的最小值： SELECT LEAST(“Google”, “Runoob”, “Apple”); – Apple |
| LN – ln | 返回数字的自然对数 | 返回 2 的自然对数： SELECT LN(2); – 0.6931471805599453 |
| LOG(x) – log | 返回自然对数(以 e 为底的对数) | SELECT LOG(20.085536923188) – 3 |
| LOG10(x) – log10 | 返回以 10 为底的对数 | SELECT LOG10(100) – 2 |
| LOG2(x) – log2 | 返回以 2 为底的对数 | 返回以 2 为底 6 的对数： SELECT LOG2(6); – 2.584962500721156 |
| MAX(expression) – max | 返回字段 expression 中的最大值 | 返回数据表 Products 中字段 Price 的最大值： SELECT MAX(Price) AS LargestPrice FROM Products; |
| MIN(expression) – min | 返回字段 expression 中的最小值 | 返回数据表 Products 中字段 Price 的最小值： SELECT MIN(Price) AS LargestPrice FROM Products; |
| MOD(x,y) – mod | 返回 x 除以 y 以后的余数 | 5 除于 2 的余数： SELECT MOD(5,2) – 1 |
| PI() – pi | 返回圆周率(3.141593） | SELECT PI() --3.141593 |
| POW(x,y) – pow | 返回 x 的 y 次方 | 2 的 3 次方： SELECT POW(2,3) – 8 |
| POWER(x,y) – power | 返回 x 的 y 次方 | 2 的 3 次方： SELECT POWER(2,3) – 8 |
| RADIANS(x) – radians | 将角度转换为弧度 | 180 度转换为弧度： SELECT RADIANS(180) – 3.1415926535898 |
| RAND() – rand | 返回 0 到 1 的随机数 | SELECT RAND() --0.93099315644334 |
| ROUND(x) – round | 返回离 x 最近的整数 | SELECT ROUND(1.23456) --1 |
| SIGN(x) – sign | 返回 x 的符号，x 是负数、0、正数分别返回 -1、0 和 1 | SELECT SIGN(-10) – (-1) |
| SIN(x) – sin | 求正弦值(参数是弧度) | SELECT SIN(RADIANS(30)) – 0.5 |
| SQRT(x) --sqrt | 返回x的平方根 | 25 的平方根： SELECT SQRT(25) – 5 |
| SUM(expression) – sum | 返回指定字段的总和 | 计算 OrderDetails 表中字段 Quantity 的总和： SELECT SUM(Quantity) AS TotalItemsOrdered FROM OrderDetails; |
| TAN(x) – tan | 求正切值(参数是弧度) | SELECT TAN(1.75); – -5.52037992250933 |
| TRUNCATE(x,y) – truncate | 返回数值 x 保留到小数点后 y 位的值（与 ROUND 最大的区别是不会进行四舍五入） | SELECT TRUNCATE(1.23456,3) – 1.234 |
3.日期函数
| 函数名 | 描述 | 实例 |
|---|---|---|
| ADDDATE(d,n) – adddate | 计算起始日期 d 加上 n 天的日期 | SELECT ADDDATE(“2017-06-15”, INTERVAL 10 DAY);->2017-06-25 |
| ADDTIME(t,n) – addtime | 时间 t 加上 n 秒的时间 | SELECT ADDTIME(‘2011-11-11 11:11:11’, 5)->2011-11-11 11:11:16 (秒) |
| CURDATE() – curdate | 返回当前日期 | SELECT CURDATE();-> 2018-09-19 |
| CURRENT_DATE() --current _date | 返回当前日期 | SELECT CURRENT_DATE();-> 2018-09-19 |
| CURRENT_TIME – current_time | 返回当前时间 | SELECT CURRENT_TIME();-> 19:59:02 |
| CURRENT_TIMESTAMP() – current_timestamp | 返回当前日期和时间 | SELECT CURRENT_TIMESTAMP()-> 2018-09-19 20:57:43 |
| CURTIME() – curtime | 返回当前时间 | SELECT CURTIME();-> 19:59:02 |
| DATE() – date | 从日期或日期时间表达式中提取日期值 | SELECT DATE(“2017-06-15”); -> 2017-06-15 |
| DATEDIFF(d1,d2) – datedief | 计算日期 d1->d2 之间相隔的天数 | SELECT DATEDIFF(‘2001-01-01’,‘2001-02-02’)-> -32 |
| DATE_ADD(d，INTERVAL expr type) – date_add | 计算起始日期 d 加上一个时间段后的日期 | SELECT ADDDATE(‘2011-11-11 11:11:11’,1)-> 2011-11-12 11:11:11 (默认是天) SELECT ADDDATE(‘2011-11-11 11:11:11’, INTERVAL5 MINUTE)-> 2011-11-11 11:16:11 (TYPE的取值与上面那个列出来的函数类似) |
| DATE_FORMAT(d,f) – date_format | 按表达式 f的要求显示日期 d | SELECT DATE_FORMAT(‘2011-11-11 11:11:11’,‘%Y-%m-%d %r’)-> 2011-11-11 11:11:11 AM |
| DATE_SUB(date,INTERVAL expr type) – date_sub | 函数从日期减去指定的时间间隔。 | Orders 表中 OrderDate 字段减去 2 天： SELECT OrderId,DATE_SUB(OrderDate,INTERVAL2 DAY) AS OrderPayDate FROM Orders |
| DAY(d) – day | 返回日期值 d 的日期部分 | SELECT DAY(“2017-06-15”); -> 15 |
| DAYNAME(d) – dayname | 返回日期 d 是星期几，如 Monday,Tuesday | SELECT DAYNAME(‘2011-11-11 11:11:11’)->Friday |
| DAYOFMONTH(d) – dayofmonth | 计算日期 d 是本月的第几天 | SELECT DAYOFMONTH(‘2011-11-11 11:11:11’)->11 |
| DAYOFWEEK(d) – dayofweek | 日期 d 今天是星期几，1 星期日，2 星期一，以此类推 | SELECT DAYOFWEEK(‘2011-11-11 11:11:11’)->6 |
| DAYOFYEAR(d) – dayofyear | 计算日期 d 是本年的第几天 | SELECT DAYOFYEAR(‘2011-11-11 11:11:11’)->315 |
| EXTRACT(type FROM d) – extract | 从日期 d 中获取指定的值，type 指定返回的值。 type可取值为： · MICROSECOND · SECOND · MINUTE · HOUR · DAY · WEEK · MONTH · QUARTER · YEAR · SECOND_MICROSECOND · MINUTE_MICROSECOND · MINUTE_SECOND · HOUR_MICROSECOND · HOUR_SECOND · HOUR_MINUTE · DAY_MICROSECOND · DAY_SECOND · DAY_MINUTE · DAY_HOUR · YEAR_MONTH | SELECT EXTRACT(MINUTE FROM ‘2011-11-11 11:11:11’) -> 11 |
| FROM_DAYS(n) – from_days | 计算从 0000 年 1 月 1 日开始 n 天后的日期 | SELECT FROM_DAYS(1111)-> 0003-01-16 |
| HOUR(t) – hour | 返回 t 中的小时值 | SELECT HOUR(‘1:2:3’)-> 1 |
| LAST_DAY(d) – last_day | 返回给给定日期的那一月份的最后一天 | SELECT LAST_DAY(“2017-06-20”);-> 2017-06-30 |
| LOCALTIME() – local_time | 返回当前日期和时间 | SELECT LOCALTIME()-> 2018-09-19 20:57:43 |
| LOCALTIMESTAMP() – local_timestamp | 返回当前日期和时间 | SELECT LOCALTIMESTAMP()-> 2018-09-19 20:57:43 |
| MAKEDATE(year, day-of-year) – makedate | 基于给定参数年份 year 和所在年中的天数序号 day-of-year 返回一个日期 | SELECT MAKEDATE(2017, 3);-> 2017-01-03 |
| MAKETIME(hour, minute, second) – maketime | 组合时间，参数分别为小时、分钟、秒 | SELECT MAKETIME(11, 35, 4);-> 11:35:04 |
| MICROSECOND(date) – microsecond | 返回日期参数所对应的微秒数 | SELECT MICROSECOND(“2017-06-20 09:34:00.000023”);-> 23 |
| MINUTE(t) – minute | 返回 t 中的分钟值 | SELECT MINUTE(‘1:2:3’)-> 2 |
| MONTHNAME(d) – monthname | 返回日期当中的月份名称，如 November | SELECT MONTHNAME(‘2011-11-11 11:11:11’)-> November |
| MONTH(d) – month | 返回日期d中的月份值，1 到 12 | SELECT MONTH(‘2011-11-11 11:11:11’)->11 |
| NOW() – now | 返回当前日期和时间 | SELECT NOW()-> 2018-09-19 20:57:43 |
| PERIOD_ADD(period, number) – period | 为 年-月 组合日期添加一个时段 | SELECT PERIOD_ADD(201703, 5); -> 201708 |
| PERIOD_DIFF(period1, period2) – period_diff | 返回两个时段之间的月份差值 | SELECT PERIOD_DIFF(201710, 201703);-> 7 |
| QUARTER(d) – quarter | 返回日期d是第几季节，返回 1 到 4 | SELECT QUARTER(‘2011-11-11 11:11:11’)-> 4 |
| SECOND(t) – second | 返回 t 中的秒钟值 | SELECT SECOND(‘1:2:3’)-> 3 |
| SEC_TO_TIME(s) – sec_to_time | 将以秒为单位的时间 s 转换为时分秒的格式 | SELECT SEC_TO_TIME(4320)-> 01:12:00 |
| STR_TO_DATE(string, format_mask) – str_to_date | 将字符串转变为日期 | SELECT STR_TO_DATE(“August 10 2017”, “%M %d %Y”);-> 2017-08-10 |
| SUBDATE(d,n) – subdate | 日期 d 减去 n 天后的日期 | SELECT SUBDATE(‘2011-11-11 11:11:11’, 1)->2011-11-10 11:11:11 (默认是天) |
| SUBTIME(t,n) – subtime | 时间 t 减去 n 秒的时间 | SELECT SUBTIME(‘2011-11-11 11:11:11’, 5)->2011-11-11 11:11:06 (秒) |
| SYSDATE() – sysdate | 返回当前日期和时间 | SELECT SYSDATE()-> 2018-09-19 20:57:43 |
| TIME(expression) – time | 提取传入表达式的时间部分 | SELECT TIME(“19:30:10”);-> 19:30:10 |
| TIME_FORMAT(t,f) – time_format | 按表达式 f 的要求显示时间 t | SELECT TIME_FORMAT(‘11:11:11’,‘%r’)11:11:11 AM |
| TIME_TO_SEC(t) – time_to_sec | 将时间 t 转换为秒 | SELECT TIME_TO_SEC(‘1:12:00’)-> 4320 |
| TIMEDIFF(time1, time2) – timediff | 计算时间差值 | SELECT TIMEDIFF(“13:10:11”, “13:10:10”);-> 00:00:01 |
| TIMESTAMP(expression, interval) – timestamp | 单个参数时，函数返回日期或日期时间表达式；有2个参数时，将参数加和 | SELECT TIMESTAMP(“2017-07-23”, “13:10:11”);-> 2017-07-23 13:10:11 |
| TO_DAYS(d) – to_days | 计算日期 d 距离 0000 年 1 月 1 日的天数 | SELECT TO_DAYS(‘0001-01-01 01:01:01’)-> 366 |
| WEEK(d) – week | 计算日期 d 是本年的第几个星期，范围是 0 到 53 | SELECT WEEK(‘2011-11-11 11:11:11’)-> 45 |
| WEEKDAY(d) – weekday | 日期 d 是星期几，0 表示星期一，1 表示星期二 | SELECT WEEKDAY(“2017-06-15”);-> 3 |
| WEEKOFYEAR(d) – weekofyear | 计算日期 d 是本年的第几个星期，范围是 0 到 53 | SELECT WEEKOFYEAR(‘2011-11-11 11:11:11’)-> 45 |
| YEAR(d) – year | 返回年份 | SELECT YEAR(“2017-06-15”);-> 2017 |
| YEARWEEK(date, mode) – yearweek | 返回年份及第几周（0到53），mode 中 0 表示周天，1表示周一，以此类推 | SELECT YEARWEEK(“2017-06-15”);-> 201724 |
4.编码函数
| 函数名 | 描述 | 实例 |
|---|---|---|
| BIN(x) -bin | 返回 x 的二进制编码 | 15 的 2 进制编码: SELECT BIN(15); – 1111 |
| BINARY(s) – binary | 将字符串 s 转换为二进制字符串 | SELECT BINARY “RUNOOB”;-> RUNOOB |
| CASE expression WHEN condition1 THEN result1 WHEN condition2 THEN result2 … WHEN conditionN THEN resultN ELSE resultEND | CASE 表示函数开始，END 表示函数结束。如果 condition1 成立，则返回 result1, 如果 condition2 成立，则返回 result2，当全部不成立则返回 result，而当有一个成立之后，后面的就不执行了。 | SELECT CASE WHEN 1 > 0 THEN ‘1 > 0’ WHEN 2 > 0 THEN ‘2 > 0’ ELSE ‘3 > 0’ END->1 > 0 |
| CAST(x AS type) – cast | 转换数据类型 | 字符串日期转换为日期： SELECT CAST(“2017-08-29” AS DATE);-> 2017-08-29 |
| COALESCE(expr1, expr2, …, expr_n) – coalesce | 返回参数中的第一个非空表达式（从左向右） | SELECT COALESCE(NULL, NULL, NULL, ‘runoob.com’, NULL, ‘google.com’);-> runoob.com |
| CONNECTION_ID() – connection_id | 返回服务器的连接数 | SELECT CONNECTION_ID();-> 4292835 |
| CONV(x,f1,f2) – conv | 返回 f1 进制数变成 f2 进制数 | SELECT CONV(15, 10, 2);-> 1111 |
| CONVERT(s USING cs) – convert | 函数将字符串 s 的字符集变成 cs | SELECT CHARSET(‘ABC’)->utf-8 SELECT CHARSET(CONVERT(‘ABC’ USING gbk))->gbk |
| CURRENT_USER() – current_user | 返回当前用户 | SELECT CURRENT_USER();-> guest@% |
| DATABASE() – database | 返回当前数据库名 | SELECT DATABASE(); -> runoob |
| IF(expr,v1,v2) – if | 如果表达式 expr 成立，返回结果 v1；否则，返回结果 v2。 | SELECT IF(1 > 0,‘正确’,‘错误’) ->正确 |
| IFNULL(v1,v2) – ifnull | 如果 v1 的值不为 NULL，则返回 v1，否则返回 v2。 | SELECT IFNULL(null,‘Hello Word’)->Hello Word |
| ISNULL(expression) – isnull | 判断表达式是否为 NULL | SELECT ISNULL(NULL);->1 |
| LAST_INSERT_ID() – last_insert_id | 返回最近生成的 AUTO_INCREMENT 值 | SELECT LAST_INSERT_ID();->6 |
| NULLIF(expr1, expr2) – nullif | 比较两个字符串，如果字符串 expr1 与 expr2 相等 返回 NULL，否则返回 expr1 | SELECT NULLIF(25, 25);-> |
| SESSION_USER() – session_user | 返回当前用户 | SELECT SESSION_USER();-> guest@% |
| SYSTEM_USER() – system_user | 返回当前用户 | SELECT SYSTEM_USER();-> guest@% |
| USER() – user | 返回当前用户 | SELECT USER();-> guest@% |
| VERSION() – version | 返回数据库的版本号 | SELECT VERSION()-> 5.6.34 |
10.表的松耦合性设计
1.高内聚, 高耦合
1.使用表与表之间大量的外键, 即使符合 范式规则, 但还是使设计变得非常困难
2.也使得从数据库获取数据变得更加艰难
2.低内聚, 低耦合
1.表与表之间不建立外键, 但表内都添加对应的id字段, 使用 外连接 去连接数据
2.设计表格变得更轻松, 但存在的大量的数据冗余, 不符合范式规则
3.目前还是比较喜欢使用这种方式设计数据库表
3.三个范式规则
1.第一范式:
数据库表中的所有字段都是单一属性，不可再分的。
即字段最小性原则
2.第二范式:
表中所有的非主属性完全依赖于主键
即表中有唯一性的字段
3.第三范式:
满足第二范式下，非主属性间没有函数依赖，如：传递函数依赖，
即主属性不能有 传递性规则
11.表设计的技巧
1.添加字段创建时间
有重要的表数据，且是多角色操作的，就需要记录操作时间。
有些字段属于可变数据，因此一般设计几个字段 create_time , update_time, delete_time, is_delete
2.所有表都有id字段
所有表的id字段，可以设置为是当前表的主键， 且是唯一的数。
在连接表添加xxx_id字段 ,内外连接，就以这个表的id为连接依据。进行数据的组合
3.预设接口再写代码
先把表设计出来，（再处理接口），最后入手代码。
入手代码的流程： 先写模型， 再写路由， 设置空控制器
4.遵循同类型原则
尽量采用同一种风格去开发。
5.多使用数据库子查询
如果使用单条结构的语句 , 和后端语法逻辑，会导致获取的数据非常缓慢。
减少对数据库的数据获取。极大地减少数据冗余的同时，加快访问速度
注意： 如果是定表改数据，则不使用子查询。因为会导致数据缺失的情况。
扫一扫
3553
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
