MySQL 教程
MySQL 函数
1、库
mysql - u root - proot
SHOW VARIABLES LIKE 'datadir' ;
create database [if not exists ] 数据库名;
show databases;
drop database [if exists ] 数据库名;
use 数据库名;
select database();
show variables like 'lower_case_table_names' ;
ALTER USER 'root' @'localhost' IDENTIFIED WITH mysql_native_password BY '你的新密码' ;
2、表
create table 表名(
字段名1 字段类型1 (字段长度),
字段名2 字段类型2 (字段长度),
....
字段名n 字段类型n(字段长度)
);
show tables;
desc 数据表名;
show create table 数据表名;
alter table 表名 add 字段名 列类型;
alter table 表名 modify 列名 列类型;
alter table 表名 change 旧列名 新列名 列类型;
alter table 表名 drop 列名;
rename table 旧表名 to 新表名;
drop table 表名;
truncate 表名;
create table t_student2 AS select * from t_student1;
create table t_student3 AS select * from t_student1 where 1 = 2 ;
create table t_student4 AS select sno, sname, age from t_student where sno = 2 ;
2.1、列级约束
create table t_student(
sno int (6 ) primary key auto_increment,
sname varchar (5 ) not null ,
sex char (1 ) default '男' check (sex = '男' or sex = '女' ),
age int (3 ) check (age >= 18 and age <= 50 ),
enterdate date ,
classname varchar (10 ),
email varchar (15 ) unique
);
2.2、表级约束
create table t_student(
sno int (6 ) auto_increment,
sname varchar (5 ) not null ,
sex char (1 ) default '男' ,
age int (3 ),
enterdate date ,
classname varchar (10 ),
email varchar (15 ),
constraint pk_stu primary key (sno),
constraint ck_stu_sex check (sex = '男' or sex = '女' ),
constraint ck_stu_age check (age >= 18 and age <= 50 ),
constraint eq_stu_email unique (email)
);
2.3、在创建表以后添加约束
drop table t_student;
create table t_student(
sno int (6 ),
sname varchar (5 ) not null ,
sex char (1 ) default '男' ,
age int (3 ),
enterdate date ,
classname varchar (10 ),
email varchar (15 )
);
alter table t_student add constraint pk_stu_sno primary key (sno);
alter table t_student modify sno int (6 ) auto_increment;
alter table t_student add constraint ck_stu_sex check (sex = '男' or sex = '女' );
alter table t_student add constraint ck_stu_age check (age >= 18 and age <= 50 );
alter table t_student add constraint eq_stu_email unique (email);
3、增删改
insert into 表名 values (值1 , 值2 , ...);
insert into 表名 (列名1 , 列名2 , ...) values (值1 , 值2 , ...);
insert into 表名 (列名1 , 列名2 , ...) values (值1 , 值2 , ...), (值1 , 值2 , ...), (值1 , 值2 , ...) ...;
update 表名 set 列名1 = 值1 , 列名2 = 值2 , ... [where 条件];
delete from 表名 [where 条件] [order by 列名] [limit 行数];
truncate 表名;
insert into imc_class(class_name)
values ('MYSQL' ), ('Redis' ), ('MongoDB' ), ('Oracle' );
create unique index uqx_classname on imc_class(class_name);
insert into imc_class(class_name)
values ('MYSQL' ), ('Redis' ), ('MongoDB' ), ('Oracle' )
on duplicate key update
add_time = current_time ;
delete a
from imc_type a
join (
select type_name, min (type_id) as min_type_id, count (* )
from imc_type
group by type_name having count (* ) > 1
) b
on a.type_name = b.type_name and a.type_id > min_type_id;
create unique index uqx_typename on imc_type(type_name);
update imc_course a
join (
select course_id,
avg (content_score) as avg_cotent,
avg (level_score) as avg_level,
avg (logic_score) as avg_logic,
avg (score) as avg_score
from imc_classvalue
group by course_id
) b on a.course_id = b.course_id
set a.content_score = b.avg_cotent,
a.level_score = b.avg_level,
a.logic_score = b.avg_logic,
a.score = b.avg_score;
4、单表查询
4.1、简单查询
select * from 表名;
select 列名1 , 列名2 , ... from 表名;
select distinct 列名 from 表名;
select 列名(直接进行四则运算) from 表名;
select name, math + chinese + english from student;
select name, math + chinese + ifnull(english, 0 ) from student;
select 列名 [as ] 别名 from 表名;
union
union all
SELECT
order_id,
order_date,
'Active' AS status
FROM orders
WHERE order_date >= '2019-01-01'
UNION
SELECT
order_id,
order_date,
'Archived' AS status
FROM orders
WHERE order_date < '2019-01-01' ;
SELECT first_name AS name_of_all
FROM customers
UNION
SELECT name
FROM products
4.2、条件查询
select * from emp where binary job = 'clerk' ;
select * from 表名 where 列名 like '%值%' ;
4.3、高级查询
select * from 表名 order by 列名1 [asc 正序(默认) | desc 倒序], 列名2 [asc 正序(默认) | desc 倒序]
select * from 表名 limit 开始索引(从 0 开始计数), 截取多少条;
select * from emp limit (页码数 - 1 ) * 页大小, 页大小;
select 集合函数(列名) from 表名;
select 列名1 , 聚合函数(列名2 ) from 表名 group by 列名1 ;
select 列名1 , 聚合函数(列名2 ) from 表名 group by 列名1 having 过滤条件;
select 聚合函数() from 表 [where 条件] [group by 分组] [having 过滤] [order by 排序] [limit 截取];
5、多表查询
select * from 左表 [inner ] join 右表 on 连接条件;
select * from 左表 left [outer ] join 右表 on 连接条件;
select * from 左表 left [outer ] join 右表 on 连接条件 where 右边.key is null ;
SELECT
e.employee_id,
e.first_name,
m.first_name AS manager
FROM employees e
JOIN employees m
ON e.reports_to = m.employee_id
SELECT
e.employee_id,
e.first_name,
m.first_name AS manager
FROM employees e
LEFT JOIN employees m
ON e.reports_to = m.employee_id
SELECT *
FROM order_items oi
JOIN order_item_notes oin
ON oi.order_id = oin.order_Id AND oi.product_id = oin.product_id
/ USING (order_id, product_id)
select * from 左边, 右表;
select * from emp, dept;
select * from 左表, 右表 on 连接条件 where 条件;
select * from emp e, dept d where e.dept_id = d.id;
select * from emp e inner join dept d where e.dept_id = d.id;
select e.gender, e.salary, d.name
from emp e
inner join dept d on e.dept_id = d.id
where e.name = '猪八戒' ;
select * from 左表 left outer join 右表 on 连接条件;
select *
from emp e
left outer join dept d
on e.dept_id = d.id;
6、子查询
SELECT *
FROM employees
WHERE salary > (
SELECT AVG (salary)
FROM employees
)
SELECT *
FROM products
WHERE product_id NOT IN (
SELECT DISTINCT product_id
FROM order_items
)
SELECT *
FROM invoices
WHERE invoice_total > ALL (
SELECT invoice_total
FROM invoices
WHERE client_id = 3
)
SELECT *
FROM invoices
WHERE invoice_total > ANY (
SELECT invoice_total
FROM invoices
WHERE client_id = 3
)
select * from emp
where salary = (select max (salary) from emp);
select * from emp
where salary < (select avg (salary) from emp);
select * from dept
where id in (select dept_id from emp where salary > 5000 );
select * from
(select * from emp where join_date >= '2011-01-01' ) as a
left outer join dept as d
on a.dept_id = d.id;
7、SQL 执行顺序
from join -> where -> group by -> having -> select -> distinct -> order by -> limit
8、事务
8.1、事务特性
* 原子性: atomicity 一个事务操作是不可分割的整体, 一个事务中的操作一定是要么全部成功, 要么全部失败
* 一致性: consistency 一个事务执行前后, 数据库的状态是一致的
* 隔离性: isolation 当多个事务同时执行的时候, 互相是不会产生影响的
* 持久性: durability 当事务执行完毕之后, 其对数据库产生的影响一定会持久的保存在磁盘上
8.2、事务原理
begin ;
commit ;
rollback ;
savepoint 保存点;
rollback to 保存点;
show variables like 'autocommit' ;
set @@autocommit = on ;
set @@autocommit = off;
8.3、事务的隔离性
在并发事务没有进行隔离的情况下，会发生如下问题
脏读：一个事务读取到了另外一个事务没有提交的数据（没提交）
不可重复读：一个事务读取到了另外一个事务修改的数据（修改）
幻读：一个事务读取到了另外一个事务新增的数据（新增）
级别
名字
隔离级别
脏读
不可重复读
幻读
数据库默认隔离级别
1
读未提交
read uncommitted
✓
✓
✓
2
读已提交
read committed
×
✓
✓
Oracle 和 SQL Server
3
可重复读
repeatable read
×
×
✓
MySQL
4
序列化
serializable
×
×
×
9、数据类型
大分类
类型
描述
数字类型
int
整型
float
单精度浮点数类型
double
双精度浮点数类型
decimal
浮点数类型，decimal 类型的字段没有精度丢失的情况，和钱有关的数据
字符类型
varchar
可变长度字符串类型，它的长度可以由我们自己指定，最大值是 65535，使用效率低，省磁盘存储空间
char
固定长度字符串类型，如果没有指定长度，默认长度是 255，查询效率高，浪费磁盘存储空间
日期类型
date
日期，格式：yyyy-MM-dd
datetime
日期时间，格式：yyyy-MM-dd HH:mm:ss，占用 8 字节的存储空间
timestamp
日期时间，格式：yyyy-MM-dd HH:mm:ss，取值范围 1970-2038，占用 4 字节存储，性能要优于datetime
9.1、整数
9.2、浮点数
9.3、时间
9.4、字符串
10、主键和外键
主键就是能唯一标识表中每条记录的字段（唯一 + 非空）
外键约束的特点
对于主表来说，不能删除被从表引用的数据
对于从表来说，不能添加主表中不存在的数据
添加数据的时候，要先添加主表数据
删除数据的时候，要先删除从表数据
create table 表名(
列名 列类型,
[constraint ] [约束名(外键列_fk)] foreign key (列名) references 主表(主键)
);
alter table 表名 add [constraint ] [约束名] foreign key (列名) references 主表(主键);
alter table 表名 drop foreign key 约束名;
10.1、一对多
create table class(
id int primary key,
name varchar (20 )
);
create table student(
id int primary key,
name varchar (20 ),
class_id int
);
alter table student add constraint class_id_fk foreign key (class_id) references class(id);
10.2、多对多
create table student(
id int primary key,
name varchar (20 )
);
create table cource(
id int primary key,
name varchar (20 )
);
create table sc(
student_id int ,
cource_id int ,
constraint student_id_fk foreign key (student_id) references student(id),
constraint cource_id_fk foreign key (cource_id) references cource(id)
);
insert into student values (1 , '旭哥' ), (2 , '苗哥' );
insert into cource values (1 , 'JavaEE' ), (2 , 'UI' ), (3 , '挖掘机' );
insert into sc values (1 , 1 ), (1 , 2 ), (1 , 3 ), (2 , 1 );
10.3、一对一
create table husband(
id int primary key,
name varchar (20 )
);
create table wife(
id int primary key,
name varchar (20 ),
husband_id int unique ,
foreign key (husband_id) references husband(id)
);
10.4、外键策略
create table t_class(
cno int (4 ) primary key auto_increment,
cname varchar (10 ) not null ,
room char (4 )
);
insert into t_class values
(null , 'java001' , 'r803' ),
(null , 'java002' , 'r416' ),
(null , '大数据001' , 'r103' );
create table t_student(
sno int (6 ) primary key auto_increment,
sname varchar (5 ) not null ,
classno int (4 ),
constraint fk_stu_classno foreign key (classno) references t_class (cno)
);
insert into t_student values
(null , '张三' , 1 ),
(null , '李四' , 1 ),
(null , '王五' , 2 ),
(null , '朱六' , 3 );
update t_student set classno = null where classno = 2 ;
delete from t_class where cno = 2 ;
alter table t_student drop foreign key fk_stu_classno;
alter table t_student
add constraint fk_stu_classno
foreign key (classno) references t_class (cno)
on update cascade
on delete cascade;
update t_class set cno = 5 where cno = 3 ;
delete from t_class where cno = 5 ;
alter table t_student drop foreign key fk_stu_classno;
alter table t_student
add constraint fk_stu_classno
foreign key (classno) references t_class (cno)
on update set null
on delete set null ;
update t_class set cno = 8 where cno = 1 ;
alter table t_student
add constraint fk_stu_classno
foreign key (classno) references t_class (cno)
on update cascade
on delete set null ;
11、三大范式
第一范式：列原子性（地址表：数据库表每一列都是不可分割基本数据项，同一列中不能有多个值）
第二范式：消除部分依赖，数据和联合主键完全相关性（选课表：确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关）
第三范式：消除间接依赖，数据和主键直接相关性（班级表：确保数据表中的每一列数据都和主键直接相关，而不能间接相关，属性不依赖于其他非主属性）
列原子性：数据库表每一列都是不可分割基本数据项，同一列中不能有多个值
确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关
确保数据表中的每一列数据都和主键直接相关，而不能间接相关，属性不依赖于其他非主属性
12、索引
explain select class, score, id from test where class = 3 ;
12.1、介绍
索引的建立对于 MySQL 的高效运行是很重要的，索引可以大大提高 MySQL 的检索速度
普通索引：仅加速查询
唯一索引：加速查询 + 列值唯一（可以有 null），唯一约束
主键索引：加速查询 + 列值唯一（不可以有 null）+ 表中只有一个，主键约束
组合（联合）索引：使用多个列联合起来作为一个索引
索引的优缺点
优点：可以大幅度提高查询的速度
缺点：对于增删改来说，需要对索引进行维护，降低效率；会占用一定磁盘空间
索引的创建原则
将经常需要查询的字段设置为索引，便于查询
将经常需要连接的字段（主键、外键）设置为索引，便于连接
将经常需要排序的字段设置为索引，便于使用索引排序
常见索引失效情况
使用 like 关键字时，模糊匹配使用 % 开头
使用 or 连接条件时，如果条件中存在没有索引的
任何操作（计算、函数、自动 / 手动类型转换）
使用 !=、not in、is null、is not null 时
12.2、命令
create table student (
id int ,
username varchar (30 ),
telephone varchar (30 ),
sex char (2 )
);
create index username_idx on student(username);
create unique index telephone_idx on student(telephone);
create unique index uts_idx on student(username, telephone, sex);
alter table student add primary key (id);
alter table student add unique (id);
alter table student add index (username);
create table student(
id int ,
username varchar (30 ),
telephone varchar (30 ),
sex char (2 )
primary key (id),
unique (telephone),
index (username)
);
12.3、前缀索引
当索引的列是字符串时（包括 CHAR、VARCHAR、TEXT、BLOG），尤其是当字符串较长时
CREATE INDEX idx_lastname ON customers (last_name(20 ));
最佳字符数如何确定：可以利用 COUNT、DISTINCT、LEFT 关键词和函数
SELECT
COUNT (DISTINCT LEFT (last_name, 1 )),
COUNT (DISTINCT LEFT (last_name, 5 )),
COUNT (DISTINCT LEFT (last_name, 10 ))
FROM customers
从前 1 个到前 5 个字符，效果提升是很显著的
13、函数
13.1、字符串
1. concat(s1, s2 ... sn)
描述: 把字符串 s1, s2 等多个字符串合并为一个字符串
实例: select concat("传", "智", "播", "客");
2. char_length(str)
描述: 返回字符串 str 的字符数
实例: select char_length("传智播客");
3. length(str)
描述: 返回字符串 s 的字节数
实例: select length("传智播客");
4. upper(s)
描述: 将字符串转换为大写
实例: select upper("itcast");
5. lower(s)
描述: 将字符串转换为小写
实例: select lower("ITCAST");
6. locate(s1, s)
描述: 从字符串 s 中获取 s1 的开始位置, 位置从 1 开始
实例: select locate('he', 'itheima');
7. trim(str) | ltrim(str) | rtrim(str)
描述: 字符串去空格
实例: select trim(" 传智 人 ");
8. replace(s, s1, s2)
描述: 字符串 s, 用 s2 替代 s1
实例: select replace('abc', 'a', '*');
9. substr(s, start, length) | substring(s, start, length)
描述: 从字符串 s 的 start(从 1 开始计数) 位置截取长度为 length 的子字符串
实例: select substr("itcast", 2, 3);
10. strcmp(str1, str2)
描述: 比较字符串大小, 左大于右时返回 1, 左等于右时返回 0, 左小于于右时返回 -1
实例: select strcmp("a", "b");
11. concat_ws(sep, s1, s2 ... sn)
描述: 用指定的分隔符 sep 把字符串 s1, s2 等多个字符串合并为一个字符串
实例: select concat_ws("-", "传", "智", "播", "客");
12. format(x, d[,locale])
描述: 将数字 N 格式化为格式, 如 "#,###,###.##", 并舍位到 D 位小数
实例: select format(123456.789, 4); -- 123,456.7890
13. left(str, len) | right(str, len)
描述: 从字符串的左 / 右边起, 返回 len 长度的子字符串
实例: select left("itcast", 2), right("itcast", 2);
14. substring_index(str, delim, count)
描述: 返回字符串 str 按 delim 分割的前 count 个子字符串
实例: select substr("192.168.0.100", ".", -2); -- 0.100
15. trim([remstr from] str)
描述: 从字符串 str 两端删除不需要的字符 remstr
实例: select trim("x" from "xxxximoocxxxx");
16. md5(str)
描述: 返回 str 的 md5 值
13.2、日期函数
1. now() | curdate() | curtime()
描述: 获取系统当前日期时间、日期、时间
实例: select now();
2. year(DATE) | month(DATE) | day(DATE)
描述: 从日期中选择出年、月、日
实例: select year(now());
3. last_day(DATE)
描述: 返回月份的最后一天
实例: select last_day(now());
4. adddate(DATE, n) | subdate(DATE, n)
描述: 计算起始日期 DATE 加(减) n 天的日期
实例: select adddate(now(), 10);
5. quarter(DATE)
描述: 返回日期 DATE 是第几季节(季度), 返回 1 到 4
实例: select quarter(now());
6. datediff(d1, d2)
描述: 计算日期 d1->d2 之间相隔的天数
实例: select datediff('2019-08-01', '2019-07-01');
7. date_format(d, f)
描述: 按表达式 f 的要求显示日期 d
实例: select date_format(now(), '%Y-%m-%d');
8. sec_to_time(seconds)
描述: 把秒数转换为(小时:分:秒)
实例: select sec_to_time(60);
9. time_to_sec(time)
描述: 把(小时:分:秒)转换为秒数
实例: select time_to_sec('1:00:00');
10. date_add(DATE, INTERVAL expr unit)
描述: 对给定的日期 DATE 加(减) 指定的时间单元
时间单元: year 年、day 天、hour 小时、minutes 分钟、second 秒
实例: select date_add(now(), INTERVAL 1 DAY);
11. extract(unit from date)
描述: 返回日期 date 的指定部分
实例: select extract(year from now());
12. unix_timestamp()
描述: 返回 unix 时间戳
实例:
13. from_unixtime()
描述: 把 unix 时间戳转换为日期函数
实例:
14. timestampdiff(unit, d1, d2)
描述: 返回 d1 与 d2 的时间差
实例: timestampdiff(hour, concat(m.date, " ", m.start), concat(m.date, " ", m.end)) as hour
格式
描述
%a
缩写星期名
%b
缩写月名
%c
月，数值
%D
带有英文前缀的月中的天
%d
月的天，数值（01-31）
%e
月的天，数值（0-31）
%f
微秒
%H
小时（00-23）
%h
小时（01-12）
%I
小时（01-12）
%i
分钟，数值（00-59）
%j
年的天（001-366）
%k
小时（0-23）
%l
小时（1-12）
%M
月名
%m
月，数值（01-12）
%p
AM 或 PM
%r
时间，12-小时（hh:mm:ss AM 或 PM）
%S
秒（00-59）
%s
秒（00-59）
%T
时间，24-小时 (hh:mm:ss）
%U
周（00-53）星期日是一周的第一天
%u
周（00-5） 星期一是一周的第一天
%V
周（01-53）星期日是一周的第一天，与 %X 使用
%v
周（01-53）星期一是一周的第一天，与 %x 使用
%W
星期名
%w
周的天 （0=星期日，6=星期六）
%X
年，其中的星期日是周的第一天，4 位，与 %V 使用
%x
年，其中的星期一是周的第一天，4 位，与 %v 使用
%Y
年，4 位
%y
年，2 位
13.3、数字函数
1. abs(x)
描述: 返回 x 的绝对值
实例: select abs(-1);
2. ceil(x) | floor(x)
描述: 向上(下)取整
实例: select ceil(1.5);
3. mod(x, y)
描述: 返回 x mod y 的结果, 取余
实例: select mod(5, 4);
4. rand()
描述: 返回 0 到 1 的随机数
实例: select rand();
6. round(x)
描述: 四舍五入
实例: select round(1.23456);
7. truncate(x, y)
描述: 返回数值 x 保留到小数点后 y 位的值
实例: select truncate(1.23456, 3);
8. round(x, d)
描述: 对数字 x 进行四舍五入保留 d 位小数
实例: select round(1.23456, 3);
13.4、判断函数
select if(布尔表达式, 为真时的值, 为假时的值) from 表名;
select ename, salary + ifnull(bonus, 0 ), if(salary + ifnull(bonus, 0 ) > 20000 , "家有娇妻", "单身狗") from emp;
select
case [字段]
when 判断条件 1 then 希望的到的值 1
when 判断条件 2 then 希望的到的值 2
when 判断条件 n then 希望的到的值 n
else 前面条件都没有满足情况下得到的值
end
from 表名;
select ename, job_id,
case job_id
when 1 then '糊口度日'
when 2 then '小康生活'
when 3 then '人生巅峰'
else '土豪'
end
as 'status'
from emp;
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步