一、数据库的基本操作命令
1.登录MySQL
# 进入数据库的方法
mysql -h 127.0.0.1(服务器地址) -u root(用户名) -p #然后回车在输入密码进入
2.数据库的基本操作
# 显示数据库版本,记得加;
select version();
# 显示当前时间
select now();
# 查看所有数据库
show databases;
# 创建数据库
create database 数据库名 charset=utf8;
# 创建淘宝数据库
create database taobao;
# 创建淘宝数据库并指定编码
create database taobao charset=utf8;
# 查看创建数据库的语句
show create database taobao;
3.使用数据库taobao数据库
# 使用数据库
use school;
# 显示数据库所有的表
show tables;
# 删除数据库
drop database taobao;
4.数据表的基本操作
#查看当前数据库中所有的表
show tables;
#创建shopping数据表
#auto_increment:自动增长
#not null:表示不为空
#primary key:表示主键
#default:默认值
create table shopping(
id int not null auto_increment primary key,
name varchar(50) not null
);
#插入一条数据到shoping表中
insert into shoping (name) values("name");
#多行插入
insert into shoping (name) values("李四"),("老王");
#查询shoping表中的所有数据
select * from shoping;
#查看创建表的语句
show create table shoping;
#删除表
drop table shoping;
#查看表的字段
desc shoping;
#DEFAULT:默认值
#COMMENT:描述
#FIRST:修改字段为第一个
#AFTER:将字段修改为哪个之后
#添加表的字段
alter table shoping(表名) add mark(字段) longtext(字段类型) not null(约束条件) after id(指定在那个字段后面);
#修改表的字段
alter table shoping modify mark longtext after name;#修改的字段一定要把那个字段类型带着
#修改表的字段名
alter table shoping change mark mark1 logntext;
#删除表的字段
alter table shoping drop mark;
#修改表的数据
#修改表的某字段所有值
update shoping set name = "测试";
#根据条件修改指定字段的值
update shoping set name = "测试" where id = 1;
#查询表的内容
select * from shoping;
#根据条件查询表的内容
select * from shoping where id = 1；
#查询指定列的值
select name from shoping where id = 1;
#将查询结果以别名显示
select name as 名字 from shoping;
#物理删除
delete from shoping where id = 1;
#逻辑删除(用新的字段作为条件限制显示信息)
alter table shoping add is_delete tinyint default 0;
#把id=1的is_delete改为1
update shoping set is_delete=1 where id=1;
#查询然后条件限制为is_delete=0 就可以隐藏数据
select * from shoping where is_delete=0;
5. 数据表的查询操作
#查询所有字段
select * from shoping;
#查询指定字段
select name from shoping;
#给字段起别名(用别名显示)
select name as 姓名 from shoping;
#从指定的表中寻找指定的字段
select shoping.name from shoping;
#用as起别名再用别名调用字段
select s.name, from shoping as s;
#利用distinct字段消除重复行
select distinct name from shoping;
#条件查询(比较运算符)
select * from shoping where id>2;
select * from shoping where id<2;
select * from shoping where id!=18;
#条件查询(逻辑运算符)
select * from shoping where id>=17 and id<=27;
select * from shoping where id>=13 or id>=159;
select * from shoping where not(id<=17 and id=2);
#模糊查询
#查询以"李"开头的所有名字
select * from shoping where name like "李%";
#查询以"王"字结尾的所有名字
select * from shoping where name like "%王";
#查询有"三"的所有名字
select * from shoping where name like "%三%";
#查询有两个字的名字
select * from shoping where name like "__";
#查询有三个字的名字
select * from shoping where name like "___";
#查询至少有两个的名字
select * from shoping where name like "%__%";
#空判断 is null
select * from shoping where name is null;
6. 数据表内数据的排序
#order by 字段 查询改字段内的排序
#asc从小到大排序,默认从小到大
#desc从大到小
select * from shoping order by id;
select * from shoping order by desc id;
7.数据表的集合函数
#聚合函数
#count(*)统计列树,count(字段)一样
select count(*) from shoping;
#最大值,最小值,求和,平均
select max(age),min(age),sum(age),avg(age) from shoping;
8.分组
#group by 按性别分组
select gender from shoping group by gender;
#在students表中,计算每个性别的人数
select gender, count(*) from students group by gender;
#在students表中,通过性别分组显示名字
select gender, group_concat(name) from students group by gender;
#group by + having :用来分组查询后指定一些条件的查询结果
select gender,count(*) from students group by gender having count(*)>2;
9.分页
#查询前3行女生的信息select * from students where is_delete=0 limit(n-1)*m,nselect * from students where gender=2 limit 0,3;
10.连接查询
#先建立一个班级表#内连接查询班级表和学生表select * from students inner join classes on students.cls_id=classes.id;#左连接查询班级表和学生表select * from students as s left join classes as c on c.cls_id=c.id;#右连接查询班级表和学生表select * from students as s right join classes as c on s.cls_id=c.id;
11.自关联
#表中的某一列,关联表中的另一列,这就是自关联
12.子查询
#在一个select语句中,嵌入另外一个select语句,那么嵌入的select语句被称为子查询语句#子查询是嵌入到主查询中#子查询是辅助主查询的,要么充当条件,要么充当数据源#子查询是可以独立存在的语句,是一条完整的 select 语句#标准子查询select * from students where age > (select avg(age) from students);#列级子查询select name from classes where id in (select id from students);#行级子查询select * from students where (height,age) = (select max(height),max(age) from students);
13.视图
#有个查询结果，这个结果表作为创建视图基础，这个结果中不能出现同名字段select g.id, g.name, c.name as cate_name, b.name as brand_name, g.price from goods as g inner join goods_brands as b on g.brand_id=b.id inner join goods_cates as c on c.id=g.cate_id;#新建了一个视图，这个视图它是一个虚拟表，这个表字段是原表字段的引用，可以简单理解为软链接create view v_info as select g.id, g.name, c.name as cate_name, b.name as brand_name, g.price from goods as g inner join goods_brands as b on g.brand_id=b.id inner join goods_cates as c on c.id=g.cate_id;#删除视图drop view v_info
14. 事务
#1.原子性#一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务#的原子性#2.一致性#数据库总是从一个一致性的状态转换到另一个一致性的状态。(在前面的例子中，一致性确保了，即使在执行第三、四条语句之间时系统崩溃，支票账户中也不会损失200美元，因为事务最终没有提交，所以事务中所做的修改也不会保存到数据库中。)#3.隔离性#通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的。(在前面的例子中，当执行完第三条语句、第四条语句还未开始时，此时有另外的一个账户汇总程序开始运行，则其看到支票帐户的余额并没有被减去200美元。)#4.持久性#一旦事务提交，则其所做的修改会永久保存到数据库。(此时即使系统崩溃，修改的数据也不会丢失。)#开启事务begin;start transaction;#提交事务commit;#回滚事务rollback;
15. 索引
#索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。#更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度(创建索引会缩短执行的时间)#查看索引show index from 表名;#创建索引create index 索引名称 on 表名(字符段名称(长度))#删除索引:drop index 索引名称 on 表名;#查询#开启运行时间set profiling=1;#查看执行时间show profiles;
二、MySQL高级查询语句
1.MySQL进阶查询
1.1 按关键字排序——order by
■ 使用ORDER BY 语句来实现排序
■ 排序可针对一个或多个字段
■ ASC：升序，默认排序方式
■ DESC：降序
■ ORDER BY 的语法结构
SELECT column1, column2,…FROM table_name ORDER BY column1,column2,…ASC|DESC;
1.1.1 单字段排序
基于id字段 升序
select * from shoping order by id;
基于id字段 降序
select * from shoping order by id desc;
1.1.2 多字段排序
多个字段排序时，优先排序前一个字段
num字段先降序排序，然后和num字段相同的在对id字段进行升序排序
select * from shoping order by num desc,id;
注意：
oredr by 前面可以加where，只有满足条件时，where才会生效
select * from shoping where num>2 order by num desc,id;
1.2 对结果进行分组——group by
■ 使用 GROUP BY 来实现分组
■ 通常结合聚合函数一起使用
■ 可以按一个或多个字段对结果进行分组
■ 语法结构
SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name;
1.2.1 单个分组
对type_id进行分组，查出每个分组中name的次数
select count(name) from shoping_details group by type_id;
对type_id进行分组，查出每个分组中name的值,以逗号隔开
select group_concat(name) from shoping_details group by type_id;
group_concat函数详解
#group_concat( [DISTINCT] 要连接的字段 [Order BY 排序字段 ASC/DESC] [Separator ’分隔符‘] )#以id分组，把name字段的值在同一行打印出来，逗号分隔(默认)select id,group_concat(name) from shoping_details group by id;#以id分组，把name字段的值在一行打印出来,分号分隔select id,group_concat(name separator ';') from shoping_details group by id;#以id分组,把去除重复冗余的name字段的值打印在一行,逗号分隔select id,group_concat(distinct name) from shoping_details group by id;#以id分组,把name字段的值去重打印在一行,逗号分隔,按照id倒序排列select id,group_concat(DISTINCT name order by id desc) from shoping_details group by id;
1.2.2 分组结合order by使用
按type_id分组，分组后对 count(name)进行排序
select count(name) from shoping_details group by type_id order by count(name) desc;
1.3 限制结果条目——limit
■ 只返回SELECT查询结果的第一行或前几行
■ 使用LIMIT语句限制条目
■ 语法结构
SELECT column1, column2, … FROM table_name LIMIT [offset,] number
LIMIT 的第一个参数是位置偏移量（可选参数），是设置 MySQL 从哪一行开始显示。
如果不设定第一个参数，将会从表中的第一条记录开始显示。需要注意的是，第一条记录的位置偏移量是 0，第二条是 1，以此类推。
第二个参数是设置返回记录行的最大数目。如果 SELECT 查询出的结果记录比较多，用户查看不是很方便。这个时候可以返回固定的、有限的记录数量，使用 MySQL 数据库的 LIMIT 子句即可实现。LIMIT 子句是一种简单的分页方法，它的使用减少了数据结果的返回时间，提高了执行效率，也解决了由于数据量过大从而导致的性能问题。
limit 查询前三行
select * from shoping_details limit 3;
limit 查询 从第三行开始，往下数2行
select * from shoping_details limit 3,2;
1.4 as 用法
1.4.1、设置别名——as
■ 使用AS语句设置别名时，关键字AS可省略。
■ 设置别名时，保证不能与库中其他表或字段名起冲突
■ 别名的语法结构
SELECT column_name AS alias_name FROM table_name;
给字段做别名
select name as 名称 from shoping_details;
表的别名语法结构：
SELECT column_name(s) FROM table_name AS alias_name;
给表设别名，前字段名要加上表的别名
select a.name as 姓名 from shoping_details as a;
注意：
只查询单张表时，给表做了别名，字段名前可以不用加表的别名，因为就一张表。如果涉及多表查询时，那么字段名前就必须写上表的别名。
1.4.2、as 作为连接语句
创建新表shopings,并把shoping表的结构和数据导入进新表中
对比一下 原表 和 新生成得表。发现新生成表主键和自增都没了
1.5 通配符
■ 用于替换字符串中的部分字符
■ 通常与like一起使用，并协同where完成查询
■ 常用通配符
• %：表示零个、一个或多个
• _：表示单个字符
1.5.1 基于 % 号通配符使用的方法
select * from shoping_details where name like '%星期%';
1.5.2 基于 " _ " 通配符使用的方法
select * from shoping_details where name like '星期_';
1.6 子查询
■ 也称为内查询或嵌套查询
■ 先于主查询被执行，其结果将作为外层主查询的条件
■ 在增删改查中都可以使用子查询
■ 支持多层嵌套
■ IN语句是用来判断某个值是否在给定的结果集中
1.6.1 先查shoping_details表中type_id，然后反馈给where后的id，在进行外层的查询
select * from shoping where id in (select type_id from shoping_details);
1.6.2 多层嵌套
当多层嵌套时，每一层的内外字段名要一致才可以，否则报错
select name from shoping where id in (select type_id as id from shoping_details where id in (select id from shopings));
1.6.3 这里from后面跟了一个查询语句，后面必须给起一个别名。因为 select * from shoping只是一个结果集，并不是一个表，所以后面要起别名。
select * from (select * from shoping)as a;
1.6.4 EXISTS结合子查询
#EXISTS用于检查子查询是否至少会返回一行数据#判断exists里面是否返回一条数据,如果返回数据就执行前面的查询,否则就不执行查询select * from shoping where exists(select * from shoping_details where id=10);
1.7 NULL 值
■ 表示缺失的值
■ 与数字0或者空白是不同的
■ 使用 IS NULL 或 IS NOT NULL 进行判断
■ NULL值和空值的区别
• 空值长度为0，不占空间；NULL值的长度为NULL，占用空间
• IS NULL 无法判断空值
• 空值使用 “=” 或 “<>” 来处理（“<>”不等于）
• COUNT()计算，NULL会忽略，空值加入计算
#isnull用法#isnull(exper) 判断exper是否为空，是则返回1，否则返回0select isnull(mark) from shoping;#is not null用法#is not null 判断指定指端是否为空select * from shoping where mark is not null;#ifnull用法#ifnull(exper1,exper2)判断exper1是否为空，是则用exper2代替select ifnull(mark,0) from shoping;#nullif用法#nullif(exper1,exper2)如果 expr1= expr2 成立，那么返回值为NULL，否则返回值为expr1select nullif(mark,1) from shoping;
1.8 正则表达式
■ MySQL 正则表达式通常是在检索数据库记录的时候，根据指定的匹配模式匹配记录中符合要求的特殊字符串。
■ MySQL 的正则表达式使用 REGEXP 这个关键字来指定正则表达式的匹配模式，REGEXP 操作符所支持的匹配模式
| 匹配模式 | 描述 |
|---|---|
| ^ | 匹配文本的开始字符 |
| $ | 匹配文本的结束字符 |
| . | 匹配任何单个字符 |
| * | 匹配零个或多个在它前面的字符 |
| + | 匹配前面的字符 1 次或多次 |
| […] | 匹配字符集合中的任意一个字符 |
| [^…] | 匹配字符集合中的任意一个字符 |
| {n} | 匹配前面的字符串 n 次 |
| {n,m} | 匹配前面的字符串至少 n 次，至多m 次 |
#查询特定字符串开头的记录select * from shoping where mark regexp '^w';#查询特点字符串结束的记录select * from shoping where mark regexp 'e$';#匹配单个字符的记录select * from shoping where mark regexp '.e';#匹配零个或多个在他前面的字符select * from shoping where mark regexp 'b*';#匹配前面的字符一次或多次 select * from shoping where mark regexp 'e+'; #匹配字符集中的任何一个字符 select * from shoping where mark regexp '[c,b]'; #匹配字符集中的任何一个字符 select * from shoping where mark regexp '[^c,b]'; #匹配前面的字符串n次 select * from shoping where mark regexp 'a{1}'; #匹配前面的字符串至少n次,至多m次 select * from shoping where mark regexp 'a{1,3}';
1.9 运算符
MySQL分为四种运算符：
1.算数运算符
2.比较运算符
3.逻辑运算符
4.位运算符
| 优先级由低到高排列 | 运算符 |
|---|---|
| 1 | =(赋值运算）、:= |
| 2 | II、OR |
| 3 | XOR |
| 4 | &&、AND |
| 5 | NOT |
| 6 | BETWEEN、CASE、WHEN、THEN、ELSE |
| 7 | =(比较运算）、<=>、>=、>、<=、<、<>、!=、 IS、LIKE、REGEXP、IN |
| 8 | | |
| 9 | & |
| 10 | <<、>> |
| 11 | -(减号）、+ |
| 12 | *、/、% |
| 13 | ^ |
| 14 | -(负号）、〜（位反转） |
| 15 | ! |
1.9.1、算术运算符
| 运算符 | 描述 |
|---|---|
| + | 加法 |
| - | 减法 |
| * | 乘法 |
| / | 除法 |
| % | 取余数 |
select 1+2 as 加法,2-1 as 减法,2*2 as 乘法,4/2 as 除法,7%2 as 取余;
1.9.2、比较运算符
比较运算符是查询数据记录时经常使用的一类运算符。
通过使用比较运算符可以判断出 表中有哪些记录是符合条件的，如果比较的结果为真则返回 1，如果为假则返回 0，比较的结果如果不确定则返回 NULL。其中字符串在进行比较的时候默认是不区分大小写的，如果要区分大小写可以通过 binary 关键字来实现。常用的比较运算符
| 运算符 | 描述 |
|---|---|
| = | 等于 |
| > | 大于 |
| < | 小于 |
| >= | 大于等于 |
| <= | 小于等于 |
| !=或<> | 不等于 |
| IS NOT NULL | 判断一个值是否不为 NULL |
| BETWEEN AND | 两者之间 |
| IN | 在集合中 |
| LIKE | 通配符匹配 |
| GREATEST | 两个或多个参数时返回最大值 |
| LEAST | 两个或多个参数时返回最小值 |
等号（=）是用来判断数字、字符串和表达式是否相等的，如果相等则返回1，如果不相等则返回0。
如果比较的两者有一个值是 NULL，则比较的结果就是 NULL。其中字符的比较是根据 ASCII 码来判断的，如果 ASCII 码相等，则表示两个字符相同；如果ASCII码不相等，则表示两个字符不相同。
select 2=4,2='2','e'='e',(2+2)=(3+1),'r'=NULL;
从以上查询可以看出来：
-
如果两者都是整数，则按照整数值进行比较。
-
如果一个整数一个字符串，则会自动将字符串转换为数字，再进行比较。
-
如果两者都是字符串，则按照字符串进行比较。
-
如果两者中至少有一个值是 NULL，则比较的结果是 NULL。
1.9.3、逻辑运算符
逻辑运算符又被称为布尔运算符，通常用来判断表达式的真假，如果为真返回1，否则返回0,真和假也可以用TRUE和FALSE表示。MySQL中支持使用的逻辑运算符有四种
| 运算符 | 描述 |
|---|---|
| NOT或! | 逻辑非 |
| AND 或&& | 逻辑与 |
| OR或Ⅱ | 逻辑或 |
| XOR | 逻辑异或 |
##1.逻辑非#逻辑运算符中最简单的运算符就是逻辑非，逻辑非使用NOT或!表示。逻辑非将跟在它后面的逻辑测试取反，把真变为假，把假变为真。如果NOT后面的操作数为О时，所得值为1;如果操作数为非О时，所得值为0;如果操作数为NULL时，所得值为NULL。例如，对非О值和О值分别作逻辑非运算。select not 4,!5,not 0,!0,not (5-5),!(5-5);##2.逻辑与#逻辑与通常用于判断两个值或多个值的有效性，如果所有值都是真返回1，否则返回O逻辑与使用AND或者&&表示。例如，对非О值、О值和NULL值分别作逻辑与运算,0 && NULL 为 0,1 && NULL 为 NULLselect 1 && 1,4 and 5,0 && null,1 and null;##逻辑或#逻辑或表示包含的操作数，任意一个为非零值并且不是NULL值时，返回1，否则返回0。逻辑或通常使用OR或者|来表示。例如，对非О值、О值和NULL值分别作逻辑或运算。 select 0 or 1,1 or 1,0 or 0;###注意！！！###数值之间做 或 的判断时，用 or，不要用 ||，会出问题##逻辑异或#两个非NULL值的操作数，如果两者都是О或者都是非О，则返回0;如果一个为0，另一个为非0，则返回结果为1;当任意一个值为NULL时，返回值为NULL。例如，对非O值、О值和NULL值分别作逻辑异或运算select 1 xor 1,1 xor 0,0 xor 0,0 xor null,1 xor null;
1.9.4 位运算符
最后在将计算结果从二进制变回到十进制格式，方便用户查看。MySQL支持6种位运算符位运算符实际上是对二进制数进行计算的运算符。MySQL内位运算会先将操作数变成二进制格式，然后进行位运算
| 运算符 | 描述 |
|---|---|
| & | 按位与 |
| I | 按位或 |
| ~ | 按位取反 |
| ^ | 按位异或 |
| << | 按位左移 |
| >> | 按位右移 |
二进制转十进制:
小数点前或者整数要`从右到左`用二进制的每个数去`乘以2的相应次方并递增`,小数点后则是`从左往右``乘以二的相应负次方并递减`例如：二进制数`1101.01`转化成十进制`1101.01(2) =1*2^0+0*2^1+1*2^2+1*2^3 +0*2^-1+1*2^-2=1+0+4+8+0+0.25=13.25(10)`#所以总结起来通用公式为：`abcd.efg(2)=d*2^0+c*2^1+b*2^2+a*2^3+e*2^-1+f*2^-2+g*2^-3(10)`#负次方2^-1 = 1/2^1
十进制转二进制:
1.整数转化,`除2取余,逆序排列`13 = 13/2===余1;6/2===余0;3/2===余1;1/2===余1 = 11012.小数转化,`乘2取整，顺序排列`0.25 = 0.25*2===取0;0.5*2===取1 = 01
按位与(&)运算
`与(&):两个二进制位都为1时,结果就为1,否则为0`例:10 & 15,10转化为二进制1010,15转化为二进制为1111即:1010 & 1111`结果:1010``将二进制转化为十进制10`select 10 & 15;
按位或(|)运算
`或(|):两个二进制有一个为1时,结果就为1,否则就为0`例:10 | 15,10转化为二进制为1010,15转化为二进制为1111即:1010 & 1111`结果:1111``将二进制转化为十进制15`select 10 | 15;
按位异或( ^)运算
`异或(^):两个二进制位不同时,结果为1,相同时,结果为0`例:10 ^ 15,10转化为二进制为1010,15转化为二进制为1111即:1010 ^ 1111`结果:0101``将二进制转化为十进制5`select 10 ^ 15;
按位反(~)运算
`反(~):位取反是将参与运算的数据按对应的补码进行反转,也就是做NOT操作,即1取反后变0,0取反后变为1``可以使用BIN()函数查看1取反之后的结果,BIN()函数的作用是将一个十进制数转换为二进制数,SQL语句如下select bin(~1);`select ~1;select bin(~1);
按位与(&)和按位反(~)运算相结合
例:10 & ~ 15,10转化为二进制为1010,15转化为二进制为1111因为反(~)的优先级高于与(&),所以先运算(~),`~1111 = 1111111111111111111111111111111111111111111111111111111111110000`&`1010``结果:0000``将二进制转化为十进制0`select 10 & ~ 15;
按位左(<<)运算
#位左移是按指定值的补码形式进行左移,左移指定位数之后,左边高位的数值被移出并丢弃,右边低位空出的位置用0补齐#位左移的语法格式为:`expr << n`#其中,n指定值expr要移位的位数,n必须为非负数select 1<<2;`1的补码为0001,左移两位之后变成0100,即整数4;`
按位右(>>)运算
#位右移是按指定值的补码形式进行右移,右移指定位数之后,右边低位的数值被移出并丢弃,左边高位空出的位置用0补齐 #位右移语法格式为:`expr >> n`#其中，n指定值expr要移位的位数,n必须为非负数select 4>>2;`1的补码为0001,左移两位之后变成0100,即整数4;`
1.10 内连接
两张或多张表中同时符合某种条件的数据记录组合
FROM子句中使用INNERJOIN关键字连接多张表，并使用ON设置连接条件
是系统默认的表连接方式，可以省略INNER关键字
多表支持连续使用INNERJOIN，建议不超过三个表
#语法结构SELECT column name(s) FROM table1INNER JOIN table2 ON table1.column_name = table2.column_name;select a.name from a inner join b on a.id = b.type_id;
1.11 左连接
#也被称为左外连接#在FROM子句中使用LEFTJOIN关键字来表示#匹配左表中所有行及右表中符合条件的行select a.name from a left join b a.id = b.type_id;
1.12 右连接
#也被称为右外连接#在FROM子句中使用RIGHTJOIN关键字来表示#匹配右表中所有行及左表中符合条件的行select a.name from a right joinb a.id = b.type_id;
2.MySQL数据库函数
#MySQL提供了实现各种功能的函数#常用的函数分类#1.数学函数#2.聚合函数#3.字符串函数#4.日期时间函数
2.1 数学函数
#常用的数学函数#1.abs(x)返回x的绝对值#2.rand()返回0到1的随机数#3.mod(x,y)返回x除以y以后的余数#4.power(x,y)返回x的y次方#5.round(x）返回离x最近的整数#6.round(x,y)保留x的y位小数四舍五入后的值#7.sqrt(x)返回x的平方根#8.truncate(x,y)返回数字x截断为y位小数的值#9.ceil(x)返回大于或等于x的最小整数#10.floor(x)返回小于或等于x的最大整数#11.greatest(x1,×2…)返回集合中最大的值#12.least(x1,x2…)返回集合中最小的值
2.1.1 abs(x)
`abs(X):绝对值`select abs(-1);
2.1.2 rand()
`rand():0-1的随机数`select rand();`rand()*100:1-100的随机数`select rand()*100;
2.1.3 mod(x,y)
`mod(x,y):返回x除以y的余数`select mod(5,4);
2.1.4 power(x,y)
`power(x,y):返回x的y次方`select power(2,128);
2.1.5 round(x)
`round(x):返回离x最近的整数，四舍五入`select round(5.29);
2.1.6 round(x,y)
`round(x,y):保留x的y位小数四舍五入的值`select round(1.258,2);
2.1.7 sqrt(x)
`sqrt(x):返回x的平方根`select sqrt(8);
2.1.8 truncate(x,y)
`truncate(x,y):返回数字x截断为y位的小数的值`select truncate(1.45863,3);
2.1.9 ceil(x)、floor(x)
`ceil(x)、floor(x):返回大于或等于x的最小整数(向上取整)`select ceil(1.258),floor(25.456);
2.1.10 greatest(x,y,z…)、least(x,y,z…)
`greatest(x,y,z...):返回集合中的最大值;least(x,y,z...):返回集合中的最小值,有null就返回null`select greatest(1,2,3,5,7);select least(3,4,7,1);select least(1,2,null);
2.2 聚合函数
#对表中数据记录进行集中概括而设计的一类函数#常用的聚合函数#1.avg()返回指定列的平均值select avg(num) from shoping;#2.count()返回指定列中非NULL值的个数select count(name) from shoping;#3.min()返回指定列的最小值select min(num) from shoping;#4.max()返回指定列的最大值select max(num) from shoping;#5.sum()返回指定列的所有值之和select sum(num) from shoping;
2.3 字符串函数
#常用的字符串函数#1.length(x)返回字符串x的长度#2.trim()返回去除指定格式的值#3.concat(x,y)将提供的参数x和y拼接成一个字符串#4.upper(x)将字符串x的所有字母变成大写字母#5.lower(x)将字符串x的所有字母变成小写字母#6.left(x,y)返回字符串x的前y个字符#7.right(x,y)返回字符串x的后y个字符#8.repeat(x,y)将字符串x重复y次#9.space(x)返回x个空格#10.replace(x,y,z)将字符串z替代字符串x中的字符串y#11.strcmp(x,y)比较x和y，返回的值可以为-1,0,1#12.substring(x,y,z)获取从字符串x中的第y个位置开始长度为z的字符串#13.reverse(x)将字符串x反转
2.3.1 length(x)
#length(x):返回字符串x的长度select lenght("qqqqqq");#空格也算一个长度select length('111 ');#''算空值,没有长度select length('');#'NULL'当做了一个字符串,所以有长度select length('null');#length(NULL)返回的就是NULLselect length(null);
2.3.2 trim()
#trim()返回去除指定格式的值,过滤指定的字符串#函数语法`TRIM([BOTH/LEADING/TRAILING] 目标字符串 FROM 源字符串)``MySQL:TRIM(),RTRIM(),LTRIM()`#TRIM() #删除前后空格#RTRIM() #删除字符串结尾空格#LTRIM() #删除字符串起始空格#参数说明:#TRIM([[位置] [要移除的字串] FROM ] 字串):[位置]的可能值为LEADING(起头), TRAILING(结尾),BOTH(起头及结尾),这个函数将把[要移除的字串]从字串的起头、结尾，或是起头及结尾移除。如果我们没有列出 [要移除的字串] 是什么的话，那空白就会被移除。#LTRIM(字串): 将所有字串起头的空白移除。#RTRIM(字串): 将所有字串结尾的空白移除。#案列1:`SELECT TRIM(' 123 ');`#结果:`123`#案例2-A:`SELECT LTRIM(' 123 ');`#结果:`123 `#案例2-B:`SELECT LTRIM(' 123');`#结果:`123`#案例3-A:`SELECT RTRIM(' 123 ');`#结果:` 123`#案例3-B:`SELECT RTRIM(' 123');`#结果:`123`#案例4：BOTH删除指定的首尾字符`select trim(both 'a' from 'abhdajkha');`#结果:`bhdajkh`#案例5：LEADING 删除指定的首字符`SELECT TRIM(LEADING 'a' FROM 'applea');`#结果:`pplea`#案例6：TRAILING删除指定的尾字符`select trim(trailing 'a' from 'asdnasa');`#结果:`asdnas`
2.3.3 concat(x,y）
#concat(x,y)将提供的参数x和y拼接成一个字符串select concat('asdb','xhy');
2.3.4 upper(x)
#upper(x)将字符串x的所有字母变成大写字母select upper('asdHHj');
2.3.5 lower(x)
#lower(x)将字符串x的所有字母变成小写字母select lower('HHJAOA');
2.3.6 left(x,y)
#left(x,y)返回字符串x的前y个字符select left('sadhjkhfka',3);
2.3.7 right(x,y)
#right(x,y)返回字符串x的后y个字符select right('hasdkjha',3);
2.3.8 repeat(x,y)
#repeat(x,y)将字符串x重复y次select repeat('abs',3);
2.3.9 space(x)
#space(x)返回x个空格select space(5);
2.3.10 replace(x,y,z)
#replace(x,y,z)将字符串z替代字符串x中的字符串yselect replace('ashxnjsl','xnj','zahjsj');
2.3.11 strcmp(x,y)
#strcmp(x,y)比较x和y,返回的值可以为-1,0,1select strcmp(3,3) --- 0select strcmp(3,2) --- 1select strcmp(2,3) --- -1
2.3.12 substring(x,y,z)
#substring(x,y,z)获取从字符串x中的第y个位置开始长度为z的字符串select substring('sahdkjhak',2,5);
2.3.13 reverse(x)
#reverse(x)将字符串x反转select reverse("asdfghjkl");
2.4 日期函数
#MySQL也支持日期时间处理,提供了很多处理日期和时间的函数.#一些常用的日期时间函数如下所示#1.curdate()返回当前时间的年月日#2.curtime()返回当前时间的时分秒#3.now()返回当前时间的日期和时间#4.month(x)返回日期×中的月份值#5.week(x)返回日期×是年度第几个星期#6.hour(x)返回x中的小时值#7.minute(x)返回×中的分钟值#8.second(x)返回×中的秒钟值#9.dayofweek(x)返回×是星期几，1星期日，2星期一#10.dayofmonth(x)计算日期×是本月的第几天#11.dayofyear(x)计算日期×是本年的第几天
2.4.1、curdate() 、curtime()、now(）
#1.curdate()返回当前时间的年月日select curdate();#2.curtime()返回当前时间的时分秒select curtime();#3.now()返回当前时间的日期和时间select now();
2.4.2、month(x)、week(x)、hour(x)
#1.month(x) 返回日期×中的月份值select month('2020.05.01');#2.week(x) 返回日期×是年度第几个星期select week('2020-03-019');#3.hour(x) 返回x中的小时值select hour('15:24:00');
2.4.3、minute(x)、second(x)
#1.minute(x)返回当前时间多少分select minute('15:34:00');#2.second(x)返回当前时间多少秒select second('15:34:12');
2.4.4 dayofweek(x)、dayofmonth(x)、dayofyear(x)
#1.dayofweek(x)返回当前时间本周的第多少天select dayofweek('2020-03-18');#dayofmonth(x)返回当前时间本月的第多少天select dayofmonth('2020-03-18');#dayofyear(x)返回当前时间本年的第多少天select dayofyear('2020-03-18');
3.MySQL存储过程
前面学习的 MySQL 相关知识都是针对一个表或几个表的单条 SQL 语句,使用这样的SQL 语句虽然可以完成用户的需求，但在实际的数据库应用中,有些数据库操作可能会非常复杂,可能会需要多条 SQL 语句一起去处理才能够完成,这时候就可以使用存储过程, 轻松而高效的去完成这个需求
3.1 存储过程的优点
存储过程执行一次后,生成的二进制代码就驻留在缓冲区,之后如果再次调用的话,将直接调用二进制代码,使得存储过程的执行效率和性能得到大幅提升存储过程是 SQL语句加上控制语句的集合,有很强的灵活性,可以完成复杂的运算存储过程存储在服务器端,客户端调用时,直接在服务器端执行,客户端只是传输的调用语句,从而可以降低网络负载存储过程被创建后，可以多次重复调用，它将多条SQL封装到了一起,可随时针对SQL语句进行修改,不影响调用它的客户端存储过程可以完成所有的数据库操作,也可以通过编程的方式控制数据库的信息访问权限
3.2 创建存储过程语法
#使用 CREATE PROCEDURE 语句创建存储过程,其语法格式如下所示:#声明语句结束符，可以自定义:#这里的$$和//符号都是可以自定义的,但是必须要和结束END后面的结束符相同才可以!`DELIMITER $$`或`DELIMITER //`#创建存储过程:CREATE PROCEDURE demo_in_parameter(IN p_in int)BEGIN ## 存储过程开始`中间是存储过程相关逻辑`END $$ ## 存储过程结束#变量赋值:SET @p_in=1 #变量定义:DECLARE l_int int unsigned default 40;#创建mysql存储过程、存储函数:create procedure 存储过程名(参数)#存储过程体:create function 存储函数名(参数)#示例:mysql> delimiter $$ #声明语句结束符,可以自定义mysql> create procedure shoping() ##声明存储过程 -> begin ##存储过程开始 -> select id,name,num from shoping; -> end $$ ##存储过程结束Query OK, 0 rows affected (0.61 sec)mysql> delimiter ; ##将语句的结束符号恢复为分号mysql> call shoping(); ##调用存储过程+----+--------+-----+| id | name | num |+----+--------+-----+| 1 | 测试一 | 10 || 3 | 测试三 | 10 || 4 | 张三 | 5 || 5 | 李四 | 2 || 6 | 王五 | 8 |+----+--------+-----+5 rows in set (0.27 sec)
3.3 存储过程的参数
MySQL存储过程的参数用在存储过程的定义,共有三种参数类型,IN,OUT,INOUT,形式如:
`CREATEPROCEDURE 存储过程名([[IN |OUT |INOUT ] 参数名 数据类形...])`
IN 输入参数:表示调用者向过程传入值(传入值可以是字面量或变量)
OUT输出参数:表示过程向调用者传出值(可以返回多个值)(传出值只能是变量)
INOUT输入输出参数:既表示调用者向过程传入值,又表示过程向调用者传出值(值只能是变量)
3.3.1 in 输入参数
mysql> delimiter && ##设置结束符位&&mysql> create procedure in_param(in p_in int) ##声明存储过程 -> begin ##存储过程开始 -> select p_in; ##查询p_in的值 -> set p_in=2; ##设置p_in的值 -> select p_in; ##查询p_in的值 -> end && ##结束存储过程Query OK, 0 rows affected (0.07 sec)mysql> delimiter ; ##将结束符改为;mysql> call in_param(1); ##调用存储过程+------+| p_in |+------+| 1 |+------+1 row in set (0.00 sec)+------+| p_in |+------+| 2 |+------+1 row in set (0.01 sec)`以上可以看出,p_in在存储过程中被修改,当定义了变量并把值带入进去后,第一个查询出的是外面定义变量的值,而后在存储过程中被修改,所以值变为了2`
3.3.2 out输出参数
mysql> delimiter // ##设置结束符位//mysql> create procedure out_param(out p_out int) ##声明存储过程 -> begin ##存储过程开始 -> select p_out; ##查询p_out的值 -> set p_out=2; ##设置p_out的值 -> select p_out; ##查询p_out的值 -> end // ##结束存储过程Query OK, 0 rows affected (0.10 sec)mysql> delimiter ; ##将结束符改为;mysql> set @out=1;Query OK, 0 rows affected (0.02 sec)mysql> call out_param(@out);+-------+| p_out |+-------+| NULL |+-------+1 row in set (0.06 sec)#因为out是向调用者输出参数，不接收输入的参数，所以存储过程里的p_out为null+-------+| p_out |+-------+| 2 |+-------+1 row in set (0.06 sec)Query OK, 0 rows affected (0.06 sec)
3.3.3 inout输入参数
mysql> delimiter // ##设置结束符位//mysql> create procedure param(inout p int) ##声明存储过程 -> begin ##储存过程开始 -> select p; ##查询p的值 -> set p=4; ##设置p的值 -> select p; ##查询p的值 -> end // ##结束存储过程Query OK, 0 rows affected (0.00 sec)mysql> delimiter ; ##将结束符改为;mysql> set @a =2;Query OK, 0 rows affected (0.04 sec)mysql> call param(@a);+------+| p |+------+| 2 |+------+1 row in set (0.00 sec)+------+| p |+------+| 4 |+------+1 row in set (0.01 sec)Query OK, 0 rows affected (0.02 sec)
2.4 MySQL存储过程的控制语句
2.4.1 变量作用域
内部的变量在其作用域范围内享有更高的优先权,当执行到 end.变量时,内部变量消失,此时已经在其作用域外,变量不再可见了,应为在存储过程外再也不能找到这个申明的变量,但是你可以通过 out 参数或者将其值指派给会话变量来保存其值
mysql> delimiter // ##设置结束符位//mysql> create procedure param() ##声明存储过程 -> begin ##储存过程开始 -> declare x1 varchar(5) default 'outer'; ##定义变量x1,默认值为'outer' -> begin ##存储过程开始 -> declare x1 varchar(5) default 'inner'; ##定义变量x1,默认值为'inner' -> select x1; ##查询x1 -> end; ##结束存储过程 -> select x1; ##查询x1 -> end; ##结束存储过程 -> //Query OK, 0 rows affected (0.01 sec)mysql> delimiter ; ##将结束符设置为;mysql> call param(); ##调用存储过程+-------+| x1 |+-------+| inner |+-------+1 row in set (0.00 sec)##因为作用域是内部结束,所以先查询是内部定义变量的值+-------+| x1 |+-------+| outer |+-------+1 row in set (0.01 sec)##当内部作用域结束之后,内部变量结束就重新读取外部变量的值Query OK, 0 rows affected (0.02 sec)
2.4.2 条件语句
#1.if-then-else语句mysql > DELIMITER // ##设置结束符位//mysql> create procedure param(in p int) ##声明存储过程 -> begin ##储存过程开始 -> declare var int; ##定义变量var -> set var=p+1; ##设置变量的值为var -> if var=2 then ##如果var=2 -> insert into shoping (name,num) values("测试var为2",2); ##插入一条语句 -> end if; ##结束if判断 -> if var=3 then ##如果var=3 -> insert into shoping (name,num) values("测试var为3",3); ##插入一条语句 -> else ##否则 ##插入一条语句 -> insert into shoping (name,num) values("测试var不存在",10); ##结束if判断 -> end if; -> end; -> //Query OK, 0 rows affected (0.02 sec)mysql> delimiter ; ##将结束符设置为;mysql> call param(2); ##调用存储过程mysql> select * from shoping; ##查询插入的数据+----+---------------+--------+-----------+-----+| id | name | mark | is_delete | num |+----+---------------+--------+-----------+-----+| 1 | 测试一 | waskle | 0 | 10 || 3 | 测试三 | NULL | 1 | 10 || 4 | 张三 | NULL | 0 | 5 || 5 | 李四 | NULL | 0 | 2 || 6 | 王五 | 1 | 0 | 8 || 7 | 测试var为2 | NULL | 0 | 2 || 8 | 测试var不存在 | NULL | 0 | 10 || 9 | 测试var为3 | NULL | 0 | 3 |+----+---------------+--------+-----------+-----+8 rows in set (0.00 sec)#2.case语句#CASE case_expression# WHEN when_expression_1 THEN commands# WHEN when_expression_2 THEN commands# ...# ELSE commands#END CASE;mysql> delimiter // ##设置结束符位//mysql> create procedure param(in p int) ##声明存储过程 -> begin ##储存过程开始 -> declare var int; ##定义变量var -> set var=p+1; ##设置变量的值为var -> case var ##case var变量 -> when 0 then ##如果var的值为0 ###插入一条数据 -> insert into shoping (name,num) values("测试case0",0); -> when 1 then ##如果var的值为1 ###插入一条数据 -> insert into shoping (name,num) values("测试case1",1); -> when 2 then ##如果var的值为2 ###插入一条数据 -> insert into shoping (name,num) values("测试case2",2); -> else ##不满足上诉条件 ###插入另一条数据 -> insert into shoping (name,num) values("测试case不存在",10); -> end case; ##结束case -> end; -> //Query OK, 0 rows affected (0.00 sec)mysql> delimiter ;mysql> call param(1);Query OK, 1 row affected (0.05 sec)mysql> select * from shoping;+----+---------------+--------+-----------+-----+| id | name | mark | is_delete | num |+----+---------------+--------+-----------+-----+| 1 | 测试一 | waskle | 0 | 10 || 3 | 测试三 | NULL | 1 | 10 || 4 | 张三 | NULL | 0 | 5 || 5 | 李四 | NULL | 0 | 2 || 6 | 王五 | 1 | 0 | 8 || 7 | 测试var为2 | NULL | 0 | 2 || 8 | 测试var不存在 | NULL | 0 | 10 || 9 | 测试var为3 | NULL | 0 | 3 || 10 | 测试case2 | NULL | 0 | 2 |+----+---------------+--------+-----------+-----+9 rows in set (0.00 sec)#3.循环语句#while 条件 do# --循环体#endwhilemysql> delimiter && ##设置结束符位&&mysql> create procedure param(in p int) ##声明存储过程 -> begin ##存储过程开始 -> declare var int; ##定义变量 -> set var=p; ##设置变量的值 -> while var<3 do ##开始while 循环 ##插入新的语句 -> insert into shoping (name,num) values("测试while循环",var); -> set var=var+1; ##var递增 -> end while; ##结束while循环 -> end; ##结束存储过程 -> &&Query OK, 0 rows affected (0.04 sec)mysql> delimiter ;mysql> call param(1);Query OK, 1 row affected (0.00 sec)mysql> select * from shoping;+----+---------------+--------+-----------+-----+| id | name | mark | is_delete | num |+----+---------------+--------+-----------+-----+| 1 | 测试一 | waskle | 0 | 10 || 3 | 测试三 | NULL | 1 | 10 || 4 | 张三 | NULL | 0 | 5 || 5 | 李四 | NULL | 0 | 2 || 6 | 王五 | 1 | 0 | 8 || 7 | 测试var为2 | NULL | 0 | 2 || 8 | 测试var不存在 | NULL | 0 | 10 || 9 | 测试var为3 | NULL | 0 | 3 || 10 | 测试case2 | NULL | 0 | 2 || 11 | 测试while循环 | NULL | 0 | 1 || 12 | 测试while循环 | NULL | 0 | 2 |+----+---------------+--------+-----------+-----+13 rows in set (0.03 sec)
扫一扫
9128
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
