MySQL基础语法
一、SQL 语句分类(5类)
分类 | 描述 | 关键字 |
---|---|---|
DDL | Data Definition Language 【数据定义语言】 定义数据库对象:数据库,表,列 | create,drop,alter |
DML | Data Manipulation Language【数据操作语言】 操作表中数据:增,删,改 | insert,delete,update |
DQL | Data Query Language【数据查询语言】 查询表中数据,对数据库的操作最多就是查询 | select…from… |
DCL | Data Control Language【数据控制语言】 定义数据库的访问权限和安全级别,及创建用户 | grant,revoke |
TCL | Transaction Control Language【事务控制语言】 控制数据库的事务操作 | commit,rollback,savepoint |
一个数据库 可以有 多张表,一张表 可以有 多条记录,记录 = 表结构 + 表数据
操作 | 增 | 删 | 改 | 查 |
---|---|---|---|---|
数据库(database) | create | drop | alter | show |
表(table) | create | drop | alter | show |
表结构(字段) | alter…add | alter…drop | alter…modify/change | / |
表数据(数据) | insert into | delete from | update…set… | select…from |
二、Database 操作
1、创建数据库
-- 直接创建数据库db1
create database db1;
-- 判断如果不存在则创建数据库db2
create database if not exists db2;
-- 创建数据库db3并指定字符集为gbk
create database db3 character set gbk;
-- 关键字用 ``
create database `create`;
2、使用数据库
-- 查看正在使用的数据库
select database();
-- 使用/切换数据库
use db2;
3、查看数据库
-- 查看正在使用的数据库
select database();
-- 查看所有的数据库
show databases;
-- 查看某个数据库的定义信息
show create database db3;
4、修改数据库(了解)
-- 将db3数据库的字符集改成utf8
alter database db3 character set utf8;
Tips:
sql
语句不识别-
,utf-8
要写成utf8
,iso-8859-1
要写成latin1
三、Table 操作
1、创建表
-- 创建student表包含id,name,sex,birthday字段
create table student(
id int,
name varchar(20),
sex char(1),
birthday date
);
-- 创建一个student2表,结构与student相同
create table student2 like student;
2、查看表
-- 显示所有表
show tables;
-- 查看数据表表结构
desc student;
-- 查看数据表的创建信息
show create table student;
3、修改表结构(了解)
语法:ALTER TABLE 表名 (ADD、MODIFY、change、DROP) 字段名 数据类型(长度)
-- 新增字段
alter table student add remark varchar(20);
-- 修改字段
alter table student modify remark varchar(100);
-- 修改字段名(remark -> intro)
alter table student change remark intro varchar(20);
-- 删除字段
alter table student drop intro;
-- 修改表名
rename student to student2;
-- 修改表的编码
alter table student character set gbk;
4、删除表 - drop
-- 直接删除表s1表
drop table s1;
-- 判断表如果存在就删除s1表
drop table if exists s1;
四、Data 操作 - 增删改
1、新增 - insert
-- 准备表
CREATE TABLE student (
id INT,
sex CHAR(2),
NAME VARCHAR(20),
birthday DATE
);
-- 全写形式:插入所有数据,所有的字段名都写出来
insert into stu (id,sex,name,birthday) values (1,'男','tom','2020-05-20');
-- 省略全部字段:插入所有数据,可以不写字段名
insert into stu values (2,'男','汤姆','2020-05-21');
-- 省略部分字段:插入部分数据,需要指定字段名
insert into stu (name,sex) values ('柳岩','女');
-- 批量插入
insert into student(id,name,birthday,sex) values
(4,'ml','1998-01-01','男'),
(5,'qq','1999-01-01','女');
表的备份 & 蠕虫复制
-- 表结构的复制
create table s2 like student;
-- 表数据的复制
insert into s2 select * from student;
-- 蠕虫复制(不断复制自身)
insert into s2 select * from s2;
2、修改 - update
-- 修改表中的性别为'女'
update stu set sex='女';
-- 将表中id为1的性别改为男
update stu set sex='男' where id=1;
-- 修改多个列,用逗号分隔
update student set name='hehe', address = '北京' where id=3;
3、删除 - delete/truncate
-- 删除id为2的学生信息
delete from stu where id=2;
-- 删除整张表中的所有数据
delete from stu;
truncate table stu;
delete
和 truncate
删除的区别
delete
- 将表中的数据一条一条删除,实际上没有删除表中数据的所有信息
- 数据可能恢复(可以理解成隐藏)
- 效率比较低
- 属于DML语句
truncate
- 将整个表摧毁,重新创建一个新的表,新的表结构和原来表结构一模一样
- 数据不可恢复
- 效率比较高
- 属于DDL语句
五、Data 操作 - 查询
1、基本查询
-- 创建商品表
CREATE TABLE product(
pid INT,
pname VARCHAR(20),
price DOUBLE,
category_id VARCHAR(32)
);
INSERT INTO product(pid,pname,price,category_id) VALUES(1,'联想',5000,'c001');
INSERT INTO product(pid,pname,price,category_id) VALUES(2,'海尔',3000,'c001');
INSERT INTO product(pid,pname,price,category_id) VALUES(3,'雷神',5000,'c001');
INSERT INTO product(pid,pname,price,category_id) VALUES(4,'JACK JONES',800,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(5,'真维斯',200,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(6,'花花公子',440,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(7,'劲霸',2000,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(8,'香奈儿',800,'c003');
INSERT INTO product(pid,pname,price,category_id) VALUES(9,'相宜本草',200,'c003');
INSERT INTO product(pid,pname,price,category_id) VALUES(10,'面霸',5,'c003');
INSERT INTO product(pid,pname,price,category_id) VALUES(11,'好想你枣',56,'c004');
INSERT INTO product(pid,pname,price,category_id) VALUES(12,'香飘飘奶茶',1,'c005');
INSERT INTO product(pid,pname,price,category_id) VALUES(13,'果9',1,NULL);
1)基本语法 - select
-- 查询所有商品(*表示查询所有列,不推荐,推荐一一列举)
select * from product;
-- 查询商品的名称和价格
select pname, price from product;
2)别名查询 - as
【表别名】
-- 表名.字段名: 某张表的某个字段
select product.pname, product.price from product;
-- 如果表只有一张,表名可以省略
select pname, price from product;
-- 使用关键字as取别名
select p.pname, p.price from product as p;
-- as可以省略
select p.pname, p.price from product p;
-- 注意: 一旦取了别名,原名不可用
select product.pname, product.price from product as p; -- error
【列别名】
-- 完整写法
select product.pname as '商品名', product.price as '商品价格' from product;
-- as 后的字符串可以不加引号
select product.pname as 商品名, product.price as 商品价格 from product;
-- as可以省略
select product.pname 商品名, product.price 商品价格 from product;
3)去重查询 - distinct
-- 查看商品表中有哪些价格
select price from product; -- 有重复的, 结果20条
select distinct price from product; -- 过滤重复, 结果10条
4)计算查询
-- 将所有商品的价格+10元进行显示
select pname, price+10 newprice from product;
-- 通过ifnull,将null值转化为0再参与运算
select pname, ifnull(price,0) + 10 newprice from product;
2、条件查询
-- 创建学生表
CREATE TABLE student (
id INT,
NAME VARCHAR(20),
age INT,
sex VARCHAR(5),
address VARCHAR(100),
math INT,
english INT
);
INSERT INTO student(id,NAME,age,sex,address,math,english) VALUES
(1,'马云',55,'男','杭州',66,78),
(2,'马化腾',45,'女','深圳',98,87),
(3,'马景涛',55,'男','香港',56,77),
(4,'柳岩',20,'女','湖南',76,65),
(5,'柳青',20,'男','湖南',86,NULL),
(6,'刘德华',57,'男','香港',99,99),
(7,'马德',22,'女','香港',99,99),
(8,'德玛西亚',18,'男','南京',56,65);
(9,'唐僧',25,'男','长安',87,78),
(10,'孙悟空',18,'男','花果山',100,66),
(11,'猪八戒',22,'男','高老庄',58,78),
(12,'沙僧',50,'男','流沙河',77,88),
(13,'白骨精',22,'女','白虎岭',66,66),
(14,'蜘蛛精',23,'女','盘丝洞',88,88);
1)比较运算符
数值:
小于< 大于> 等于= 小于等于<= 大于等于>= 不等于<>,!=
null:
is null 或 is not null
函数:
ifnull(字段, 默认值) 如果字段值为null,则取默认值,否则取字段值
-- 数学成绩大于80分的学生
select * from student where math > 80;
-- 英语成绩小于等于80分的学生(不计null)
select name, english from student where english <= 80;
-- 英语成绩小于或等于80分的学生(null缺考,记作0)
select name, ifnull(english, 0) eng from student where ifnull(english, 0) <= 80;
-- age等于20岁的学生
select * from student where age = 20;
-- age不等于20岁的学生
select * from student where age != 20;
select * from student where age <> 20;
-- 英语成绩为null的同学(缺考)
select * from student where english is null;
select * from student where english is not null;
-- english + 10
select name, english+10 from student;
select name, ifnull(english, 0) + 10 newEng from student;
2)逻辑运算符
与或非:
and or not
in:
多个or时使用 (in里面的每个数据都会作为一次条件,只要满足条件的就会显示)
between:
在一个范围内,between a and b (包含a和b,b>a)
-- age大于35 且 性别为男(两个条件同时满足)
select * from student where age > 35 and sex = '男';
-- age大于35 或 性别为男(两个条件其中一个满足)
select * from student where age > 35 or sex = '男';
-- id是1或3或5
select * from student where id=1 or id=3 or id=5;
select * from student where id in (1, 3, 5);
-- 英语成绩 大于等于75 且 小于等于90
select * from student where english >= 75 and english <= 90;
select * from student where english between 75 and 90;
3)模糊查询
like:
% 表示0个或多个
_ 表示1个
-- 姓马的学生
select * from student where name like '马%';
-- 姓名中包含'德'的学生
select * from student where name like '%德%';
-- 姓马 且 姓名有三个字 的学生
select * from student where name like '马__';
3、排序查询 - sort
order by
desc - descending 降序
asc - ascending 升序(默认)
-- 查询所有数据,按年龄排序
select * from student order by age desc; -- 降序
select * from student order by age asc; -- 升序
select * from student order by age; -- 默认升序
-- 查询所有数据,按年龄降序排列,如果年龄相同,以数学成绩降序排列(多个排序条件以逗号分隔)
select * from student order by age desc, math desc;
4、分页查询 - limit
1)limit语法
limit:
-- limit offset(index), count;
-- offset : 偏移量/索引 (从0开始)
-- count : 最大的查询数量
-- 查询学生表中数据,跳过前面2条,显示6条
select * from student limit 2, 6;
-- 查询前5条数据
select * from student limit 0,5;
select * from student limit 5; -- 当offset=0时,可以省略
-- 共14条数据
select * from student limit 10, 7; -- 只返回4条(不会溢出)
select * from student limit 20, 7; -- 没有就返回null(不会索引越界)
-- 查询第1条数据
select * from student limit 1;
2)分页查询
分页查询:每次只查询某一页数据,每页显示特定的数量
# 查询第page页,每页显示pageSize条数据
select * from student (page-1) * pageSize, pageSize;
-- 第一页: 获取5条(1~5)
select * from student limit 0, 5;
-- 第二页: 获取5条(6~10)
select * from student limit 5, 5;
-- 第三页: 获取5条(11~15)
select * from student limit 10, 5;
5、聚合函数
聚合:
一组值进行运算,最终得出一个结果
函数:
就是方法(sql、javascript中叫函数,java中叫方法)
语法:
SELECT 聚合函数(字段) FROM 表名;
常见聚合函数:
-- 求和 sum(字段参数)
-- 平均 avg(字段参数)
-- 最值 max/min(字段参数)
-- 个数 count(字段参数)
注意事项:
a. null不是值,不会被列入运算
b. where先于聚合执行 having后于聚合执行
c. 聚合函数不能用在where之中,可以用在聚合之后的sql语句中
-- 查询学生总数
select count(*) from student; -- 8 所有字段中,只要有一个有值,就会被统计
select count(english) from student; -- 7 有一个null,没有计入内
select count(id) from student; -- 8 写非空的字段也可以
-- 查询年龄大于40的总数
select count(*) from student where age > 40;
-- 查询数学成绩总分、最低分、最高分
select sum(math) from student;
select max(math) from student;
select min(math) from student;
-- 查询英语成绩平均分(不计null)
select sum(english)/count(english) 英语平均分 from student; -- 81.4286
select avg(english) 英语平均分 from student; -- 81.4286
-- 查询英语成绩平均分(包含null)
select sum(english)/count(id) 英语平均分 from student; -- 71.2500
select avg(ifnull(english, 0)) 英语平均分 from student; -- 71.2500
6、分组查询 - group by
group by:
1)被分组字段有几类,最终结果集中就有几条(每一类只会显示首条结果)
-- 如sex有男,女两类,结果集就只有2条
2)分组查询中,select之后跟【与分组字段一对一关系的字段】和【聚合函数】
-- 因为只会取首条结果,如果不是一对一的关系,没有意义
3)和聚合函数一起使用:
-- 无分组,聚合函数将表中所有符合条件的数据当成一组。
-- 有分组,聚合在分组之后执行,对每一组数据进行聚合
-- 查询男女各多少人
select sex, count(*) from student group by sex;
-- 查询年龄大于25岁的男女各多少人
select sex, count(*) from student where age > 25 group by sex;
-- 查询年龄大于25岁的人,按性别分组,统计每组的人数,并只显示性别人数大于1的数据,并降序输出
select sex, count(*) from student
where age > 25
group by sex
having count(*) > 1 -- 聚合函数不能用在where中,只能用在having中
order by count(*) desc;
六、Data 操作 - 关联查询
-- 创建部门表
CREATE TABLE dept (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
);
INSERT INTO dept (NAME) VALUES
('开发部'),
('市场部'),
('财务部');
-- 创建员工表
CREATE TABLE emp (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
gender CHAR(1),
salary DOUBLE,
join_date DATE,
dept_id INT
);
INSERT INTO emp(NAME, gender, salary, join_date, dept_id) VALUES
('孙悟空','男',7200,'2013-02-24',1),
('猪八戒','男',3600,'2010-12-02',2),
('唐僧','男',9000,'2008-08-08',2),
('白骨精','女',5000,'2015-10-07',3),
('蜘蛛精','女',4500,'2011-03-14',1);
1、笛卡尔积
# 多表查询
select * from A表, B表...;
# 交叉查询:会产生笛卡尔积
select * from emp cross join dept;
# 笛卡尔积:
两张表数据的乘积
# 笛卡尔积的消除
从表.外键 = 主表.主键
-- 查询员工表和部门表 【产生笛卡尔积】
select * from emp, dept;
-- 查询员工表和部门表 【消除笛卡尔积】
select * from emp e, dept d where e.dept_id = d.id;
-- 如果多表查询的结果中字段唯一,可以不指定表名(推荐指定)
select `salary` from emp e, dept d where e.dept_id = d.id;
-- 如果多表查询的结果中有多个同名字段,必须指定表名(不然会报错)
select `NAME` from emp e, dept d where e.dept_id = d.id;
2、内连接 - inner join
-- 效果:
求两张表的交集(消除笛卡尔积 -> 从表.外键 = 主表.主键)
-- 隐式内连接:
select 列名 from 左表, 右表 where 从表.外键 = 主表.主键
-- 显示内连接:(on + 连接条件) 【推荐】
select 列名 from 左表 inner join 右表 on 从表.外键 = 主表.主键
-- 隐式内连接
select e.name, e.salary, d.name
from emp e, dept d
where e.dept_id = d.id and e.name = '唐僧';
-- 显式内连接【推荐】
select e.name, e.salary, d.name
from emp e inner join dept d
on e.dept_id = d.id
where e.name = '唐僧';
3、左外连接 - left join
-- 效果
左表不动,并上右表与之交集的部分, 如果右表没有对应的数据,使用NULL填充。
-- 语法
select 列名 from 左表 left join 右表 on 从表.外键 = 主表.主键
-- 添加一个销售部,暂时还没有员工
insert into dept (name) values ('销售部');
-- 使用内连接查询,缺少销售部
select * from dept d inner join emp e on d.id = e.dept_id;
-- 使用左连接查询,有销售部,销售部员工为null
select * from dept d left join emp e on d.id = e.dept_id;
4、右外连接 - right join
-- 效果
右表不动,并上左表与之交集的部分, 如果右表没有对应的数据,使用NULL填充。
-- 语法
select 列名 from 左表 right join 右表 on 从表.外键 = 主表.主键
-- 在员工表中增加一个员工,还没分配部门
insert into emp values(null,'沙僧','男',6666,'2013-02-24',null);
-- 使用内连接查询,缺少沙僧
select * from dept d inner join emp e on d.id = e.dept_id;
-- 使用右外连接查询,有沙僧,沙僧的部门为null
select * from dept d right join emp e on d.id = e.dept_id;
5、全连接 - union
-- 效果:
左右表的数据都不动, 并上交集数据。
-- oracle语法(mysql不支持)
select 列名 from 左表 full outer join 右表 on 条件
-- mysql语法 - union(去重)
select * from dept d left join emp e on e.dept_id = d.id
union
select * from dept d right join emp e on e.dept_id = d.id;
-- mysql语法 - union all(不去重)
select * from dept d left join emp e on e.dept_id = d.id
union all
select * from dept d right join emp e on e.dept_id = d.id;
注意:执行
UNION ALL
语句时所需要的资源比UNION
语句少。如果明确知道合并数据后的结果数据不存在重复数据,或者不需要去除重复的数据,尽量使用UNION ALL
语句,以提高数据查询的效率。
七、Data 操作 - 子查询
1、子查询定义
# 子查询定义
a语句是b语句的一部分
# 主查询/外层查询 main query
可以是select、update、delete等语句
# 子查询/内层查询 sub query
可以写在 select、from、where、having、exists 之后,最常见在where之后
2、子查询分类
# 按结果集的行列数不同
1. 标量子查询
返回的结果是一条数据,搭配 > < >= <= = <> 使用
select * from article where uid = (select uid from blog where bid = 1);
2. 列子查询
返回的结果是一列(N行1列) 搭配 in、any、some、all 使用
select * from article where uid in (select uid from blog where bid in(1,2));
3. 行子查询
返回的结果是一行(1行N列)
select * from article where (title, content) = (select title, content from blog where bid = 1);
4. 表子查询
返回的结果是一张表(N行N列)
select * from article where (title, content) in (SELECT title, content from blog);
# 按子查询出现的位置
1. select之后(少见)
只支持标量子查询,子查询的结果直接出现在结果集中
2. from之后(有用)
支持表子查询
3. where、having之后(重要)
a. 标量子查询 - 常见
b. 列子查询 - 常见
c. 行子查询
4. exists之后(有用)
都支持,一般是表子查询
# 按关联性
1. 非相关子查询
a. 含义: 独立于外部查询的子查询 (子查询可以独立运行)
b. 执行: 子查询优先于主查询执行,并且只执行一次,执行完将结果传递给外部查询
c. 效率: 较高
d. 举例:
select * from A where A.id in (select id from B)
2. 相关子查询
a. 含义: 依赖于外部查询的数据的子查询
b. 执行: 子查询和外部查询交叉执行,外部查询每执行一行,子查询执行一次
c. 解释: 子查询中查询条件依赖于外层查询中的某个值,子查询要反复求值,以供外层查询使用。
d. 效率: 较低
e. 举例:
select * from emp e1
where exists (select * from emp e2 where e1.empno=e2.mgr);
3、select之后
-- 查询每个部门id、name和对应的员工数
select d.id, d.name, (select count(*) from emp e where e.dept_id = d.id) as 员工数
from dept d;
# select之后(相关子查询)
1. 主查询执行一次 -> select id, name from dept;
2. 子查询执行一次 -> select count(*) from emp where e.dept_id = 1;
3. 后续执行同理,主查询和子查询交叉进行
4、from之后
-- 查询每个部门id、name和对应的员工数
select d.id, d.name, temp.员工数
from (select dept_id, count(*) 员工数 from emp group by dept_id) as temp
inner join dept d
on temp.dept_id = d.id;
# from之后(表子查询)
1. 特点:
将子查询的结果作为一张临时表,和另一张表连接,再进行连接查询
2. 要求:
1. 子查询必须起别名(一般为temp,表示临时表)
2. 若子查询中使用了聚合函数,必须取别名,否则外部语句引用时会报错
例如:不能直接写 temp.count(*),语法不允许
5、where/having之后
【标量子查询】
-- 查询工资最高的员工是谁?
select * from emp where salary = (select max(salary) from emp);
-- 查询工资小于平均工资的员工有哪些?
select * from emp where salary < (select avg(salary) from emp);
-- 查询 部门平均工资 超过 全公司平均工资的 部门id和部门平均工资
select dept_id, avg(salary) from emp
group by dept_id
having avg(salary) > (select avg(salary) from emp);
【列子查询】
-- 查询工资最高的员工是谁?
-- all关键字
select * from emp where salary >= all (select salary from emp);
-- 查询工资大于5000的员工,来自于哪些部门
-- in关键字
select name from dept where id in (select dept_id from emp where salary > 5000);
-- any关键字
select name from dept where id any (select dept_id from emp where salary > 5000);
-- some关键字
select name from dept where id some (select dept_id from emp where salary > 5000);
-- 查询开发部与财务部所有的员工信息
select * from emp where dept_id in (select id from dept where name in ('开发部','财务部'));
6、exist之后
# exists之后(相关子查询)
1. 语法:
exists(完整的查询语句);
2. 结果:
0/1 (false/true)
3. 理解:
当成if来看, exists小括号中的内容是null,返回0,否则返回1
-- select之后,返回0或1
select exists (select * from emp where salary > 1000); -- 返回1
select exists (select * from emp where salary > 10000); -- 返回0
-- where之后,控制结果的显示
select * from emp where 1; -- 条件为1,显示结果
select * from emp where exists (select * from emp where salary > 1000);
select * from emp where 0; -- 条件为0,不显示结果
select * from emp where exists (select * from emp where salary > 10000);
-- 查询工资大于5000的员工,来自于哪些部门
select name from dept d
where exists (select * from emp e where e.salary > 5000 and e.dept_id = d.id);
-- 执行顺序
-- 主查询结果: 1,2,3
-- 第一次执行:
-- a. 先执行主查询: id=1, name=开发部
-- b. 接着执行子查询: 满足条件的数据不为null,返回1
-- c. 子查询返回1,主查询就保留当前行记录
-- 第二次执行: 返回1,会保留 name=市场部
-- 第三次执行: 返回0,不保留 name=财务部
7、exist 和 in
-- exists和in的区别
-- in 后面一般直接跟 非相关子查询(子查询可单独运行。子查询执行完毕,再执行主查询)
-- exists 后面一般都要跟 相关子查询 (子查询和主查询交叉执行,主查询查询一条,子查询执行一次)
-- exists和in的效率哪个高? 视情况而定
-- 其他条件相同,看结果集数量 (有索引)
-- 1. 主查询 > 子查询,用in
-- 2. 主查询 < 子查询,用exists
八、Data 操作 - 常用函数
01、字符串函数
注意:MySQL中,字符串的位置是从1开始的。
函数 | 描述 |
---|---|
char_length(s) | 返回字符串 s 的字符数 |
concat(s1,s2...sn) | 字符串 s1, s2 等多个字符串合并为一个字符串 |
lower(s) | 将字符串 s 的所有字母变成小写字母 |
upper(s) | 将字符串转换为大写 |
substr(s, start,length) | 从字符串 s 的 start 位置截取长度为 length 的子字符串 |
trim(s) | 去掉字符串 s 开始和结尾处的空格 |
示例:
select char_length('love') as '长度';
--执行结果为: 4
select concat('i','love','you');
--执行结果为: iloveyou
select lower('LOVE');
--执行结果为: love
select upper("love");
--执行结果为: LOVE
select substr("love", 1, 2);
--执行结果为: lo
select trim(' love ');
--执行结果为: 'love'
02、数字函数
函数 | 描述 |
---|---|
rand() | 返回 0 到 1 的随机数 |
round(小数, 保留位数) | 四舍五入保留几位小数 |
least(expr1, expr2, expr3, ...) | 返回列表中的最小值 |
greatest(expr1, expr2, expr3, ...) | 返回列表中的最大值 |
示例:
select rand();
-- 返回0-1之间的随机数 0.21809973867433122
select round(3.1415926, 2) ;
-- 执行结果: 3.14
select least(13, 14, 521, 74, 1);
-- 执行结果: 1
select greatest(13, 14, 521, 74, 1);
-- 执行结果: 521
03、日期函数
函数名 | 描述 |
---|---|
now() 和 sysdate() | 返回系统的当前日期和时间 |
curdate() | 返回当前日期 |
curtime() | 返回当前系统时间 |
year(d) | 返回d的中的年份 |
month(d) | 返回d的中的月份 |
day(d) | 返回d中的日 |
date_format(日期,格式) | 日期格式化 |
示例:
select now(); -- 返回系统的当前时间: 年-月-日 时:分:秒
select sysdate(); -- 返回系统的当前时间: 年-月-日 时:分:秒
select curdate(); -- 返回系统当前日期: 年-月-日
select curtime(); -- 返回系统当前时间: 时:分:秒
select year(now()); -- 返回当前日期中的年份
select month(now()); -- 返回当前日期中的月份
select day(now()); -- 返回当前日期中的日
日期格式化函数:date_format(date, format)
的使用
-- 字符串转为日期格式
select date_format('2021-09-20 08:30:45', '%Y-%m-%d %H:%i:%S');
-- 日期转为字符串格式
select date_format(now(), '%Y-%m-%d %H:%i:%s')
MySQL中,date_format(date, format)
函数的 format
格式如下
%M 月名字(January……December)
%W 星期名字(Sunday……Saturday)
%D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。)
%Y 年, 数字, 4 位
%y 年, 数字, 2 位
%a 缩写的星期名字(Sun……Sat)
%d 月份中的天数, 数字(00……31)
%e 月份中的天数, 数字(0……31)
%m 月, 数字(01……12)
%c 月, 数字(1……12)
%b 缩写的月份名字(Jan……Dec)
%j 一年中的天数(001……366)
%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)
%p AM或PM
%w 一个星期中的天数(0=Sunday ……6=Saturday )
%U 星期(0……52), 这里星期天是星期的第一天
%u 星期(0……52), 这里星期一是星期的第一天
%% 一个文字“%”。
04、流程控制函数
函数名 | 描述 |
---|---|
ifnull(v1,v2) | 如果 v1 的值不为 NULL,则返回 v1,否则返回 v2。 |
isnull(expr) | 如果 expr 的值不为 NULL,则返回 0,否则返回 1。 |
if(expr,v1,v2) | 如果 expr 是 true 则返回 v1,否则返回 v2 |
示例:
select ifnull(null, 'Hello Word')
-- 将null值替换为 Hello Word
select isnull(NULL);
-- 执行结果: null返回1,非null返回0
Case函数:只返回第一个符合条件的值,剩下的Case部分将会被自动忽略。
-- 简单Case函数(相当于 switch...case...)
CASE sex
WHEN '1' THEN '男'
WHEN '2' THEN '女'
ELSE '其他' END
-- Case搜索函数(相当于 if...else...)
CASE
WHEN sex = '1' THEN '男'
WHEN sex = '2' THEN '女'
ELSE '其他' END
05、cast()
函数名 | 描述 |
---|---|
cast(expression AS data_type) | 将某种数据类型的表达式显式转换为另一种数据类型。 用AS关键字分隔的源值和目标数据类型。 |
expression
:任何有效的SQServer表达式。AS
:用于分隔两个参数,在AS之前的是要处理的数据,在AS之后是要转换的数据类型。data_type
:目标系统所提供的数据类型,包括bigint
和sql_variant
,不能使用用户定义的数据类型。
SIGNED : 整数
UNSIGNED : 无符号整数
DECIMAL : 浮点数
CHAR() : 字符型,可带参数
BINARY : 二进制,同带binary前缀的效果
DATE : 日期
TIME : 时间
DATETIME : 日期时间型
示例:
select cast('9.0' as decimal);
-- 执行结果: 9
select cast('9.5' as decimal(10,2))
-- 执行结果: 9.50
select now()
-- 执行结果: 2021-04-17 12:46:59
select cast(now() as date)
-- 执行结果: 2021-04-17
select cast(now() as time)
-- 执行结果: 12:46:59
select cast(now() as datetime)
-- 执行结果: 2021-04-17 12:46:59
06、concat 相关函数
1)concat()
- 功能:将多个字符串拼接成一个字符串。
- 拼接结果为连接参数产生的字符串,如果有任何一个参数为null,则拼接结果为null。
- 语法:
concat(str1, str2,...)
select concat(id, username, sex) from `user`;
select concat(id, ',', username, ',', sex) from `user`;
2)concat_ws()
- 功能:将多个字符串连接成一个字符串,但是可以指定分隔符(concat with separator)
- 语法:
concat_ws(separator, str1, str2, ...)
- 第一个参数指定分隔符。需要注意的是分隔符不能为null,如果为null,则拼接结果为null。
select concat_ws(',', id, username, sex) from `user`;
3)group_concat()
- 功能:将group by产生的同一个分组中的值连接起来,返回一个字符串结果。
- 语法:
group_concat ( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator '分隔符'] )
- 使用distinct可以排除重复值;
- 使用order by可以对一个分组中的值进行排序
- separator是一个字符串值,缺省为一个逗号。
select age, group_concat(id) from `user` group by age;
select age, group_concat(id order by id desc separator '_') from `user` group by age;
-- 先拼接同一分组中的每行数据的多个字段,再拼接同一分组
select age, group_concat(concat_ws('-',id,sex) order by sex) from `user` group by age;
-- 在同一分组中的每行数据的拼接结果后加上&
select age, group_concat(concat_ws('-',id,sex), '&') from `user` group by age;
九、索引操作
1、创建索引
【直接创建】
-- 创建普通索引
create index 索引名 on 表名(字段);
-- 创建唯一索引
create unique index 索引名 on 表名(字段);
-- 创建普通组合索引
create index 索引名 on 表名(字段1,字段2);
-- 创建唯一组合索引
create unique index 索引名 on 表名(字段1,字段2);
【修改表时指定】
-- 添加主键索引(唯一且不为Null)
alter table 表名 add primary key(字段);
-- 添加唯一索引(除了NULL外,NULL可能会出现多次)
alter table 表名 add unique 索引名(字段);
-- 添加普通索引
alter table 表名 add index 索引名(字段);
-- 添加全文索引
alter table 表名 add fulltext 索引名(字段);
2、删除索引
-- 直接删除
drop index 索引名 on 表名;
-- 修改表时删除 【掌握】
alter table 表名 drop index 索引名;
3、查看索引
show index from 表名;
# 查询结果说明
1. table : 表
2. Non_unique: 是否唯一(0表示true,1表示false)
3. Key_name : 索引的名字
4. Column_name : 索引所在的列
5. Null : 是否允许为空(YES表示允许,空表示不允许)
6. Index_type: 索引的数据结构类型
十、其他操作
# 查询mysql版本
select version();
# 查询缓存相关配置
show variables like '%cache%';
# 不走缓存查询
select sql_no_cache count(*) from users;
# 走缓存查询
select sql_cache count(*) from users;