MySQL必备技能
1、创建数据库
#创建一个名称为hsp_db01的数据库
create database hsp_db01;
#创建一个使用utf8字符集的hsp_db02数据库
create database hsp_db02 character set utf8;
drop database db01;
#创建一个使用utf8字符集的hsp_db02数据库,并带校对规则的hsp_db03数据库
create database hsp_db03 character set utf8 collate utf8_bin; # 区分大小写，且创建的表的字符集和校对规则如果没有指定，默认和数据库一样
2、查询、删除数据库
#显示所有数据库
show databases;
#显示数据库创建语句,在创建数据库，表的时候，为了规避关键字，可以使用反引号解决
show create database `hsp_db03`;
#删除数据库
drop database hsp_db01;
3、备份数据库
mysqldump备份是一种逻辑备份，它的原理是通过连接到数据库，将需要备份的数据差询出来，转化成相应的sql语句，还原数据时，只要执行这些SQL语句就可以还原，我们甚至可以借助一些连接数据库的文本工具，直接将生成的文件内容拷贝到工具内执行。
mysqldump对innodb存储引擎支持热备，对myisam只支持温备。
3.1 全量备份
#备份hsp_db02和hsp_db03这两个数据库，并恢复
mysqldump -u root -p -B hsp_db02 hsp_db03 > D:\\bakup.sql #进入mysql管理员界面执行
mysqldump -u root -p --databases lxr > D:\\lxrbak.sql #此种方式也可以
mysqldump -u root -p --all-databases > D:\\lxrbak3.sql #全量备份所有库
mysqldump -u root -p hsp_db03 user > D:\\lxrbak4.sql #备份库中的某一张表
drop database hsp_db02;
drop database hsp_db03;
create database lxr;
drop database lxr;
source D:\\bakup.sql #恢复，进入mysql命令行执行
3.2 增量备份
#在mysql配置文件my.cnf(Linux)或者my.ini(wiindows)开启log-bin二进制日志
log-bin=/mysql_log/mysql-bin #二进制日志的位置和名称可自行指定
二进制日志记录了修改数据库的相关语句，不会记录查询相关的语句，主要用于增量备份恢复和差异备份恢复。
二进制日志记录的三种方式
通过在配置文件设置如下参数来生效：
binlog_format=row
- statement：记录对数据库做出修改的语句，只简单的记录语句，不会记录因为语句的修改具体影响了哪些行，因此，使用statement模式，bin-log日志量较少，IO压力小。
- row：记录对数据库做出修改的语句所影响到的数据行以及这些行的修改，记录比较详细，bin-log日志量较大，IO压力大，性能消耗比较大。
- mixed：混合使用上述两种模式，一般的语句使用statment方式进行保存，如果遇到一些特殊的函数，则使用row模式进行记录，生产环境推荐使用row模式
show variables like "%log_bin%";
+---------------------------------+--------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/mysql-bin |
| log_bin_compress | OFF |
| log_bin_compress_min_len | 256 |
| log_bin_index | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| sql_log_bin | ON |
+---------------------------------+--------------------------------+
log_bin：log-bin是否开启
log_bin_basename ：log-bin的位置以及名字
log_bin_index：log-bin的索引文件，没生成一个log-bin都会在此记录
sql_log_bin：用来决定是否将数据库的修改操作记录到log-bin中，通过set sql_log_bin on;进行开启或者关闭
max_binlog_size ：设置单个log-bin日志的大小，以字节为单位，超过设置的值，自动更新一个新的日志
[root@localhost ~]# ll /var/lib/mysql/mysql-bin*
-rw-rw---- 1 mysql mysql 328 11月 25 10:20 /var/lib/mysql/mysql-bin.000001
-rw-rw---- 1 mysql mysql 19 11月 25 10:20 /var/lib/mysql/mysql-bin.index
#查看log-bin日志可用以下两条命令
MariaDB [(none)]> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 328 |
+------------------+-----------+
1 row in set (0.000 sec)
MariaDB [(none)]> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 328 |
+------------------+-----------+
#查看当前正在使用的log-bin
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 328 | | |
+------------------+----------+--------------+------------------+
#查看log-bin记录的模式
MariaDB [(none)]> show variables like "%binlog_format%";
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| binlog_format | MIXED |
| wsrep_forced_binlog_format | NONE |
+----------------------------+-------+
#查看二进制日志内容
show binlog events in "mysql-bin.000002"; #可以指定位置，分页查询等等
+------------------+-----+-------------------+-----------+-------------+------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------------+-----------+-------------+------------------------------------------------+
| mysql-bin.000002 | 4 | Format_desc | 1 | 256 | Server ver: 10.3.28-MariaDB-log, Binlog ver: 4 |
| mysql-bin.000002 | 256 | Gtid_list | 1 | 299 | [0-1-1] |
| mysql-bin.000002 | 299 | Binlog_checkpoint | 1 | 342 | mysql-bin.000002 |
| mysql-bin.000002 | 342 | Gtid | 1 | 384 | GTID 0-1-2 |
| mysql-bin.000002 | 384 | Query | 1 | 472 | DELETE FROM `lxr`.`t6` |
+------------------+-----+-------------------+-----------+-------------+------------------------------------------------+
#手动刷新二进制日志
MariaDB [lxr]> flush logs;
#查看二进制日志的内容也可以使用mysqlbinlog命令
mysqlbinlog 二进制日志名字
同时也可以指定位置，相关参数如下：
mysqlbinlog –start-position 256 mysql-bin.000002
mysqlbinlog –start-position 256 –stop-position 472 mysql-bin.000002
mysqlbinlog –start-datetime “2022-11-4 10:40:00” mysql-bin.000002
mysqlbinlog –start-datetime “2022-11-4 10:40:00” –stop-datetime “2022-11-6 10:55:00” mysql-bin.000002
以上是增量备份和差异备份一定会用到的二进制日志，以下介绍如何使用二进制日志增量备份
增量备份的使用场景： 假如一周做一次全量备份，在做过一次全量备份后，在此期间新增了相关数据，某一天误删或者其它误操作使得数据丢失，这个时候就可以通过二进制日志来进行时间节点恢复，先恢复全量备份，再逐一恢复二进制日志记录的内容。
#先做一次全量备份，在备份的时候通过相关参数做好标记，目的是为了后面增量恢复时，知道从哪儿开始恢复
mysqldump -u root -p --master-data=2 --single-transaction --all-databases --flush-logs > 20221125.sql
--master-data=2
#数值为0，不记录对应二进制日志文件位置；
#数值为1，记录对应二进制日志文件位置；
#数值为2，记录对应二进制日值文件的位置，
#则会在备份文件中生成对应的”CHANGE MASTER TO”语句，
#此语句中标明了备份开始时二进制日志的前缀名以及其所处的position(位置)，
#但是”CHANGE MASTER TO”语句将会被注释，与此值为1时不同，
#此选项值为1时，”CHANGE MASTER TO”语句不会被注释，
#此选项值为2时，”CHANGE MASTER TO”语句会被注释，
#所以，如果只是单纯的为了记录备份时的二进制日志文件位置，那么将此选项值设置为2即可。
--single-transaction
#此参数只对 innodb 存储类型的数据有用，
#对数据库进行热备时，加上此参数，
#mysqldump会自动开启一个”可重复读”的事务，
#基于这个独立的”事务”，备份出一个满足一致性的数据备份。
#全量备份以后到下一次全量备份期间如果有误操作，先恢复全量备份，
#再通过全量备份标记的二进制位置往后进行增量恢复，
#恢复时过滤掉误操作的位置，比如drop，delete等字段
mysqlbinlog –start-position 256 –stop-position 472 mysql-bin.000002 | mysql -uroot -p
#恢复的时候会执行大量的数据库修改语句，
#建议先将 set sql_log_bin=OFF;
#等恢复完成之后再开启，这样可以避免二进制日志做无效的记录
全量备份 master-data=2 就是如下效果：
此句会被注释
全量备份 master-data=1 就是如下效果：
主要用于mysql主从
4、创建表
# 创建一张user表
create table `user`(
id int,
`name` varchar(255),
`password` varchar(255),
`birthday` date)
character set utf8 collate utf8_bin engine innodb;
4.1 修改表
-- 员工表emp的上增加一个image列，varchar类型(要求在resume后面)。
alter table employ add image varchar(25) not null default '' after resume;
desc employ;
-- 修改job列，使其长度为60。
alter table employ modify job varchar(60);
-- 删除sex列。
alter table employ drop sex;
-- 表名改为employee。
rename table employ to employee;
-- 修改表的字符集为utf-8
alter table employee charset utf8;
desc employee;
-- 列名name修改为user_name
alter table employee change column name user_name varchar(20);
5、数据类型(列类型、字段类型)
5.1 数值型
- 整数型
- tinyint(1个字节)
- smallint(2个字节)
- mediumint(3个字节)
- int(4个字节)
- bigint(8个字节)
- 小数型
- float(单精度4个字节)，大约6到9位的数字
- double(双精度8个字节)，大约15到17位的数字
- decimal[M,D],大小不确定；插入精度较高的数据，M是小数位数(精度)的总数，D是小数点(标度)后面的位数；如果D是0，则值没有小数点或分数部分。M最大65，D最大是30，如果D省略，默认是0，M省略，默认是10。
5.2 字符串类型
- char(0~255字符)，固定长度字符串
- varchar(0-65535字节)，(utf8编码最大21844字符，1~3个字节用于记录大小，65535-3除以3=21844；gbk编码除以2=32766字符)
- text(0~65535),2^16-1
- longtext(0~2^32-1)
5.3 二进制数据类型
- blob(0~65535)
- longblob(0-2^32-1)
5.4 日期类型
- date(年月日)
- time(时分秒)
- datetime(年月日时分秒)
- timestamp(时间戳,自动配置)
- year
整型演示
# 整型演示
# tinyint,如果没有指定 unsigned,则tinyint就是有符号
create table t3 (
id tinyint);
insert into t3 values (-129); # 超出范围
insert into t3 values (-128);
insert into t3 values (128); # 超出范围
insert into t3 values (127);
create table t4 (
id tinyint unsigned);
insert into t4 values (-88); # 超出范围
insert into t4 values (0);
insert into t4 values (256); # 超出范围
insert into t4 values (255);
bit类型演示，使用场景较少
# 演示bit类型使用
# bit(m) m在 1~64位
# 添加数据 范围 按照你给的位数来确定，例如m=8，表示一个字节 0~255
# 显示按照bit，即多少位
create table t5 (num bit(8));
insert into t5 values (1);
insert into t5 values (255);
select * from t5;
小数型演示
# 小数型演示
create table t6 (
num1 float,
num2 double,
num3 decimal(30,20));
insert into t6 values (88.12345678912345,88.12345678912345,88.12345678912345);
insert into t6 values (88.12345678912345789,88.12345678912345789123456,88.12345678912345789);
select * from t6;
字符串类型演示
# 演示字符串类型的使用
-- char(size),固定长度字符串，最大255字符
-- varchar(size) 0~65535，可变长度字符串,utf8编码最大21844字符，gbk编码最大32766字符
-- char(4)或者varchar(4)，"abcd"或者"a你好吗"能插入，"abcde"或者"a你好吗b"则不能插入
create table t7 (
`name` CHAR(255));
create table t8 (
`name` varchar(65535)); # 会报错
create table t8 (
`name` varchar(21844)); # utf8编码最大21844字符
create table t9 (
`name` varchar(32767) character set gbk); # 会报错
create table t9 (
`name` varchar(32766) character set gbk); # gbk编码最大32766字符，character set 可以简写成 charset
日期类型演示
# 演示日期类型
create table t10 (
birthday date,
job_time datetime,
login_time timestamp
not null default current_timestamp
on update current_timestamp); # 如果更新这张表中的记录，login_time也会根据当前时间更新
# 只插入birthday,job_time，login_time会自动插入
insert into t10 (birthday,job_time) VALUES('2022-5-11','2022-5-11 11:32:40');
select * from t10;
update t10 set birthday='2022-08-08' where birthday='2022-05-11'; #更新之后按当前时间插入
所有数据类型加在一起混合练习
# 创建一张表，用各种数据类型创建
create table employ (
`id` int,
`name` varchar(15),
`sex` CHAR(1),
`birthday` date,
`entry_date` datetime,
`job` varchar(25),
`salary` double,
`resume` text)
charset utf8 collate utf8_bin engine innodb;
insert into employ
VALUES(1,'tom','男','1995-08-18','2001-12-13 13:48:50',
'运维工程师',18000,'精通Linux,熟悉k8s');
select * from employ;
6、数据库的CRUD语句
6.1 insert
#演练insert语句
-- 1.创建一张商品表goods (id int , goods name varchar(10), price double );
create table `goods` (
id int,
goods_name varchar(10),
price double);
-- 2.添加2条记录
insert into `goods` (id,goods_name,price)
values (10,"华为手机",4800),(20,"苹果手机",3200);
select * from goods;
6.2 update
-- 要求:在上面创建的employee表中修改表中的记录
-- 1.将所有员工薪水修改为5000元。(如果没有加上 where 条件，则会修改所有的数据，需慎用)
select * from employee;
update employee set salary = 8000;
-- 2.将姓名为tom的员工薪水修改为3000元。
update employee set salary = 3000 where user_name = 'tom';
-- 3.将tom的薪水在原有基础上增加1000元。
update employee set salary = salary + 1000 where user_name = 'tom';
6.3 delete
-- 删除表中名称为’jack'的记录。
insert into employee VALUES(2,'jack',
'1992-02-03','2002-08-09 12:50:35',
'java工程师',16000,'精通java语言','D:\\');
desc employee;
select * from employee;
delete from employee where user_name = 'jack';
-- 删除表中所有记录。不加where条件则会删除表中所有记录
delete from employee;
6.4 select
-- select语句
-- 创建一张学生表,并插入数据
create table student(
id int not null default 1,
`name` varchar(20) not null default '',
chinese float not null default 0.0,
english float not null default 0.0,
math float not null default 0.0)
charset utf8 collate utf8_general_ci engine innodb;
desc student;
insert into student values (1,"韩顺平",89,78,90);
insert into student values (2,"张飞",67,98,56);
insert into student values (3,"宋江",87,78,77);
insert into student values (4,"关羽",88,98,90);
insert into student values (5,"赵云",82,84,67);
insert into student values (6,"欧阳锋",55,85,45);
insert into student values (7,"黄蓉",75,65,30);
insert into student values (8,"韩信",45,65,99);
select * from student;
-- 1.查询表中所有学生的信息。
select * from student;
-- 2.查询表中所有学生的姓名和对应的英语成绩。
select `name`,english from student;
-- 3.过滤表中英语成绩重复数据distinct。要查询的记录，每个字段都相同，才会去重
select distinct english from student;
6.4.1 对查询的列进行运算
-- 1.统计每个学生的总分
select `name` ,(chinese+english+math) as `Total_score` from student;
-- 2.在所有学生总分加10分的情况
select `name` ,(chinese+english+math+10) as `Total_score` from student;
-- 3.使用别名表示学生分数。
select `name` ,(chinese+english+math) as `Total_score` from student;
-- 4.在赵云的总分上增加60%
select `name` ,(chinese+english+math)*1.6 as `Total_score` from student where `name` = '赵云';
-- 5.统计关羽的总分。
select `name` ,(chinese+english+math) as `Total_score` from student where `name` = '关羽';
-- 6.使用别名表示学生的数学分数。
select `name`,math as shuxue from student;
6.4.2 where(过滤)
between A and B 是一个闭区间，包含A和B
-- 使用where子句，进行过滤查询
-- 1.查询姓名为赵云的学生成绩
select * from student where `name` = '赵云';
-- 2.查询英语成绩大于90分的同学
select `name`,english from student where english > 90;
-- 3. 查询总分大于200分的所有同学
select `name` ,(chinese+english+math) as `Total_score` from student
where (chinese+english+math) > 200;
-- 4.查询math大于60并且(and) id大于5的学生成绩
select * from student
where math > 60 and id > 5;
-- 5.查询英语成绩大于语文成绩的同学
select * from student
where english > chinese;
-- 6.查询总分大于200分并且数学成绩小于语文成绩,的姓韩的学生
select * from student
where (chinese+english+math) > 200
and math < chinese and `name` like '韩%'; #这里的 like 是模糊查询，% 代表任意个字符
-- 7.查询英语分数在80 - 90之间的同学。
select * from student
where english between 80 and 90;
-- 8.查询数学分数为89,90,91的同学。
select * from student
where math = 89 or math = 90 or math = 91;
# 或者
select * from student
where math in (89,90,91);
-- 9.查询所有姓韩的学生成绩。
select * from student
where `name` like '韩%';
-- 10.查询数学分> 80,语文分> 80的同学。
select * from student
where math > 80 and chinese > 80;
-- 11.查询语文分数在70 - 80之间的同学。
select * from student
where chinese between 70 and 80;
-- 12.查询总分为257,221,170的同学。
select * from student
where (chinese+english+math) in (257,221,170);
-- 13.查询所有姓张或者姓宋的学生成绩。
select * from student
where `name` like '张%' or `name` like '宋%';
-- 14.查询数学比语文多30分的同学。
select * from student
where (math - chinese) > 30;
6.4.3 order by(排序)
-- 演示 order by
-- 对数学成绩排序后输出[升序]。
select * from student
order by math asc;
-- 对总分按从高到低的顺序输出,使用别名排序，SQL语句执行有顺序，where子句中则不能使用别名
select `name`,(chinese + english + math) as Total_score from student
order by Total_score desc;
-- 对姓韩的学生成绩(总分)排序输出(升序)
select `name`,(chinese + english + math) as Total_score from student
where `name` like '韩%'
order by Total_score asc;
6.4.4 select语句执行顺序
from
on
join
where
group by(开始使用select中的别名，后面的语句中都可以使用)
avg,sum…
having
select
distinct
order by
limit
6.4.5 统计函数 count(*)和count(列)
# 演示统计函数的使用
-- 统计一个班级共有多少学生?
select * from student;
select COUNT(*) from student;
-- 统计数学成绩大于90的学生有多少个?
select COUNT(*) from student where math > 90;
-- 统计总分大于250的人数有多少?
select COUNT(*) from student
where (chinese + english + math) > 250;
-- count(*)和count(列)的区别
COUNT(*):返回满足条件的行数，null也会算在其中
COUNT(列):返回某列满足条件的行数，会排出 null
create table tsc (`name` varchar(15));
insert into tsc VALUES('tom'),('jack'),('haden'),('blue'),(null);
select * from tsc;
select COUNT(*) from tsc;
select COUNT(`name`) from tsc;
count(*)
count(列)
6.4.6 合计函数 sum(列)、AVG(列)、max(列)、min(列)
# 演示sum()函数的使用
-- 统计一个班级数学总成绩
select SUM(math) from student;
-- 统计一个班级语文、英语、数学各科的总成绩
select SUM(math),SUM(chinese),SUM(english) from student;
-- 统计一个班级语文、英语、数学的成绩总和
select SUM(chinese + english + math) from student;
-- 统计一个班级语文成绩平均分
select SUM(chinese)/COUNT(*) from student;
select AVG(chinese) from student;
# 演示 avg 的使用
-- 求一个班级数学平均分
select AVG(math) from student;
-- 求一个班级总分平均分
select AVG(chinese + english + math) from student;
# 演示 max 和 min 的使用
# 求班级总分最高分和最低分
select MAX(chinese + english + math) from student;
select MIN(chinese + english + math) from student;
6.4.7 group by(分组查询)
# 演示 group by + having
# 创建一张部门表
create table dept(
deptno mediumint unsigned not null default 0, #部门编号
dname varchar(20) not null default '', #部门名称
loc varchar(13) not null default ''); #部门所在地区
insert into dept VALUES(10,'ACCOUNTING','NEW YORK'),
(20,'RESEARCH','DALLAS'),
(30,'SALES','CHICAGO'),
(40,'OPERATIONS','BOSHTON');
select * from dept;
show create table dept;
# 创建一张员工表
create table emp(
empno mediumint unsigned not null default 0, #员工编号
ename varchar(20) not null default '', #部门名字
job varchar(9) not null default '', #工作
mgr mediumint unsigned, #上级编号
hiredate date not null, #入职日期
sal decimal(7,2) not null, #薪水
comm decimal(7,2), #奖金
deptno mediumint unsigned not null default 0); #部门编号
insert into emp VALUES(7369,'SMITH','CLERK',7902,'1990-12-17',800.00,null,20),
(7499,'ALLEN','SALESMAN',7698,'1991-2-20',1600.00,300.00,30),
(7521,'WARD','SALESMAN',7968,'1991-2-22',1250.00,500.00,30),
(7566,'JONES','MANAGER',7839,'1991-4-2',2975.00,null,20),
(7654,'MARTIN','SALESMAN',7968,'1991-9-28',1250.00,1400.00,30),
(7698,'BLAKE','MANAGER',7839,'1991-5-1',2850.00,null,30),
(7782,'CLARK','MANAGER',7839,'1991-6-9',2450.00,null,10),
(7788,'SCOTT','ANALYST',7566,'1991-4-19',3000.00,null,20),
(7839,'KING','PRESIDENT',null,'1991-11-17',5000.00,null,10),
(7844,'TURNER','SALESMAN',7698,'1991-9-8',1500.00,null,30),
(7900,'JAMES','CLERK',7698,'1991-12-3',950.00,null,30),
(7902,'FORD','ANALYST',7566,'1991-12-3',3000.00,null,20),
(7934,'MILLER','CLERK',7782,'1991-1-23',1300.00,null,10);
select * from emp;
# 创建一张薪水表
create table salgrade(
grade mediumint unsigned not null default 0, # 工资级别
losal decimal(17, 2) not null, # 该级别的最高工资
hisal decimal(17, 2) not null); # 该级别的最低工资
insert into salgrade VALUES(1, 700, 1200),
(2,1201,1400),
(3,1401,2000),
(4,2001,3000),
(5,3001,9999);
select * from salgrade;
-- 如何显示每个部门的平均工资和最高工资
select deptno,AVG(sal),MAX(sal) from emp; # 如果这样写则是求出的所有部门加在一起的平均工资以及最高工资
select deptno,AVG(sal),MAX(sal) from emp group by deptno; # 通过分组将相同组的数据取出来再来处理
-- 显示每个部门的每种岗位的平均工资和最低工资
select deptno,job,AVG(sal),MIN(sal) from emp group by deptno,job;
-- 显示平均工资低于2000的部门号和它的平均工资
select deptno,AVG(sal) avg_sal from emp
group by deptno having avg_sal < 2000;
6.4.8 字符串相关函数
# 字符串函数演示
select * from emp;
# 查看字符集
select CHARSET(sal) from emp;
# 连接字符串，将多个列连接成一列
select CONCAT(ename,' 工作是 ',job) as `work` from emp;
# instr(string,substring),返回substring在string出现的位置，没有则返回0
select INSTR ('hello world', 'r') from dual; # dual，亚元表，可以当作测试表使用
# 转换成大写
select UCASE(ename) from emp;
# 转换成小写
select LCASE(ename) from emp;
# left(string,length)，从string左边起取length个字符；
select LEFT(ename,2) from emp;
# right(string,length)，从string右边边起取length个字符
select RIGHT(ename,2) from emp;
#length(string)，返回string长度[按照字节来算]
select LENGTH(job) from emp;
select LENGTH('韩顺平') from dual;
#replace(str,search_str,replace_str)，在str这一列中用replace_str替换search_str
select job, REPLACE(job,'MANAGER','经理') from emp;
#逐个字符比较两个字符串大小
select STRCMP('lxr','mxr') from dual;
select STRCMP('lxr','kxr') from dual;
#substring(str,position,length),从str的position开始取length个字符
select SUBSTRING(job,2,3) from emp;
#去除左边空格
select LTRIM(' lixirong') from dual;
#去除右边空格
select RTRIM('lixirong ') from dual;
#去除两边空格
select TRIM(' lixirong ') from dual;
# 将emp表中的ename列的首字母改成小写
select CONCAT(LCASE(SUBSTRING(ename,1,1)),SUBSTRING(ename,2)) as new_name from emp;
select CONCAT(LCASE(LEFT(ename,1)),SUBSTRING(ename,2)) as new_name from emp;
# 将emp表中ename列除了首字母大写其余都为小写
select CONCAT(UCASE(LEFT(ename,1)),LCASE(SUBSTRING(ename,2))) as new_name from emp;
6.4.9 数学函数
# 演示数学相关函数
# abs(num),绝对值
select ABS(-21) from dual;
# bin (desimal_number),十进制转二进制
select BIN(255) from dual;
# conv(number2，from_base,to_base),进制转换
select CONV(255,10,2) from dual; #将255当成十进制的255转换成二进制
# ceiling(number),向上取整，得到比number大的最小整数
select CEILING(8.434) from dual;
# floor(number),向下取整，得到比number小的最大整数
select FLOOR(8.434) from dual;
# format(number,decimal_places)，保留小数位数，按照四舍五入保留
select FORMAT(80.4343878942,2) from dual;
# least(number,number2,....),求最小值
select LEAST(0,1,-5,4) from dual;
# mod(number1,number2),求余
select MOD(10,3) from dual;
# rand(),返回0~1之的随机数；rand(seed)，如果seed固定，随机数会固定
select RAND() from dual; # 随机数会变
select RAND(98) from dual; # 随机数会固定
6.4.10 时间日期相关函数
# 演示日期和时间相关的函数
# current_date() 当前日期
select CURRENT_DATE() from dual;
# current_time 当前时间
select CURRENT_TIME() from dual;
# current_timestamp 当前时间戳
select CURRENT_TIMESTAMP() from dual;
select NOW() from dual;
select DATE(datetime) from dual;
# DATE(time)，只显示日期，不显示时间
create table mes(
id int,
content varchar(30),
sendtime datetime)
character set utf8 collate utf8_bin engine innodb;
insert into mes (id,content,sendtime) values (1,"北京新闻",NOW());
select content,DATE(sendtime) from mes; #只会返回年月日，不会返回时分秒
# date_add(date2,interval time),在date2中加上多长时间
insert into mes (id,content,sendtime) values (2,"上海新闻",NOW());
select * from mes where DATE_ADD(sendtime,interval 5 minute) >= NOW(); # 查询5分钟之内发布的新闻
insert into mes (id,content,sendtime) values (3,"广州新闻",NOW());
# date_sub(date2,interval time)，在date2中减去多长时间
select * from mes;
select * from mes where sendtime <= DATE_SUB(NOW(),interval 10 minute); # 查询10分钟之前发布的新闻
# datediff(date1,date2)，日期差
select DATEDIFF("2022-06-28","1991-11-11") from dual;
6.4.11 加密和系统函数
# 加密函数演示
# 查看登录到mysql的有哪些用户以及登录的ip
select USER() from dual;
# database() #查询当前所操作的数据库名称
select DATABASE() from dual;
# md5(str) #为字符串算出一个md5 32位的字符串，常用于用户密码加密，使其不成为明文
select MD5('lxr@123') from dual;
select LENGTH(MD5('lxr@123')) from dual;
# 存放用户名和密码
create table t11(
id int,
user_name varchar(20),
passwd CHAR(32) not null default '');
insert into t11 VALUES(1,'张三',MD5('lxr123@'));
select * from t11;
# password(str) #通常用于数据库的用户密码加密
select PASSWORD('lxr') from dual;
select LENGTH(PASSWORD('lxr')) from dual;
select * from mysql.user;
6.4.12 流程控制函数
# 演示流程控制函数
# if(expr1,expr2,expr3) #如果expr1为true，则返回expr2，否则返回expr3 三元运算
select IF(true,'是的','不是的') from dual;
select IF(false,'是的','不是的') from dual;
select ename,IF(comm is null,0.0,comm) from emp;
# ifnull(expr1,expr2) #如果expr1不为空(null),则返回expr1，否则返回expr2
select IFNULL(null,'你好') from dual;
select * from emp;
select ename,IFNULL(comm,0.0) from emp; # 如果comm为null则会显示0.0
# select case when expr1 then expr2
# when expr3 then expr4
# else expr5 end
# 如果expr1为true,则返回expr2，
# 如果expr3为true，则返回expr4，否则返回expr5，可以多层分支
select ename,job,
(select case when job ='CLERK' then '职员'
when job ='MANAGER' then '经理'
when job ='SALESMAN' then '销售人员'
when job ='ANALYST' then '分析师'
else job end) as chinese_job from emp;
6.5 多表查询
6.5.1 like和limit
# like,模糊查询
# %：表示0或者任意个字符； _：单个任意字符
-- 如何显示员工姓名首字符为s的员工姓名和工资
select ename,sal from emp
where ename like 'S%';
-- 如何显示员工姓名第三个字符为大写o的所有员工的姓名和工资
select ename,sal from emp
where ename like '__O%';
-- 如何显示没有上级的雇员的情况
select * from emp
where mgr is null;
-- 查询表结构
desc emp;
-- 先按部门升序，然后工资降序
select * from emp
order by deptno,sal desc;
# 分页查询
-- 基本语法:select... limit start, rows
-- 表示从start+1行开始取,取出rows行, start 从0开始计算
-- 查询emp表每页显示3条记录，查询第一页，第二页，第三页
-- limit 每页显示记录数*(页数-1),每页显示记录数
select * from emp limit 0,3;
select * from emp limit 3,3;
select * from emp limit 6,3;
### group by 加强使用
-- (1)显示每种岗位的雇员总数、平均工资。
select * from emp;
select job,FORMAT(AVG(sal),2),COUNT(*) from emp
group by job;
-- (2)显示雇员总数，以及获得奖金的雇员数。
select COUNT(*),COUNT(comm) from emp;
-- (3)显示管理者的总人数。
select COUNT(distinct mgr) from emp;
-- (4)显示雇员工资的最大差额。
select MAX(sal) - MIN(sal) from emp;
-- (5) 统计各个部门的平均工资，
并且是大于1000的,并且按照平均工资从高到低排序，取出前两行记录
select deptno,AVG(sal) as avg_sal from emp
group by deptno
having avg_sal > 1000
order by avg_sal desc
limit 0,2;
6.5.2 笛卡尔积
select * from dept;
select * from emp;
select * from salgrade;
select * from dept,emp; # 笛卡尔积
-- 显示扉员名,雇员工资及所在部门的名字【笛卡尔集】
select ename,sal,dname from dept,emp
where dept.deptno = emp.deptno;
-- 多表查询的条件不能少于表的个数-1，否则会出现笛卡尔集
-- 如何显示部门号为10的部门名、员工名和工资
select dname,ename,sal from dept,emp
where dept.deptno = emp.deptno and emp.deptno = 10;
-- 显示各个员工的姓名,工资，及其工资的级别
select ename,sal,grade from emp,salgrade
where sal between losal and hisal;
-- 查询雇员名，雇员工资以及所在部门的名字，并按部门降序排序
select ename,sal,dname,emp.deptno from dept,emp
where dept.deptno = emp.deptno
order by emp.deptno desc;
6.5.3 自连接
- 将同一张表看做两张表
- 需要给表取别名
- 列名想要表达的意思不明确，也可以取别名，取别名的 as 可以省略
-- 查询公司员工名字和他上级的名字
select E1.ename as '员工名',E2.ename as '上司'
from emp as E1,emp as E2
where E1.mgr = E2.empno;
6.5.4 子查询
# 演示子查询
-- 单行子查询
-- 查询和 SMITH 同一个部门的所有员工信息
select * from emp where deptno =
(select deptno from emp where ename = 'SMITH');
-- 多行子查询，使用关键字 in
-- 查询和部门号为10的工作相同的员工的名字、岗位、工资、部门号，
-- 但是不包含10号部门本身的信息
select ename,job,sal,deptno from emp
where job in
(select distinct job from emp where deptno = 10)
and deptno != 10;
-- 多列子查询
-- 查询返回多个列数据的子查询语句
-- 查询与 ALLEN 的部门和岗位完全相同的所有员工(并且不包含 ALLEN 本人)
select * from emp
where (deptno,job) = (select deptno,job from emp where ename = 'ALLEN')
and ename != 'ALLEN';
-- 查询和宋江数学，英语，语文成绩完全相同的学生
select * from student
where (math,english,chinese) =
(select math,english,chinese from student where name = '宋江');
-- 查询每个部门工资最高的人的详细资料
select emp.*,emp2.max_sal from emp,
(select deptno,MAX(sal) as max_sal from emp group by deptno) emp2
where emp.deptno = emp2.deptno and emp.sal = emp2.max_sal;
-- 查询每个部门的信息(包括部门名，编号，地址)和人员数量
select dept.*,emp2.count_emp2 from dept,
(select deptno,COUNT(*) as count_emp2 from emp group by deptno) emp2
where dept.deptno = emp2.deptno;
-- 查询每个部门工资高于本部门平均工资的人的信息
-- 先查询部门号和部门的平均工资(作为临时表) emp2；
-- 把结果当做子查询，和 emp 表进行多表查询
select ename,sal,emp2.avg_sal,emp.deptno from emp,
(select deptno,AVG(sal) as avg_sal from emp group by deptno) emp2
where emp.deptno = emp2.deptno
and emp.sal > emp2.avg_sal;
6.5.5 all(所有)和any(任意)操作符
-- 演示all和any
-- all,所有的
# 查询员工工资比30号部门的所有员工的工资高的员工姓名、工资和部门号
select ename,sal,deptno from emp
where sal > all(select sal from emp where deptno = 30);
# 或者
select ename, sal,deptno from emp
where sal > (select MAX(sal) from emp where deptno = 30);
--any,任意的其中一个
# 查询工资比部门30其中一个员工的工资高的员工姓名、工资和部门号
select ename,sal,deptno from emp
where sal > any(select sal from emp where deptno = 30);
# 或者
select ename, sal,deptno from emp
where sal > (select MIN(sal) from emp where deptno = 30);
7. 表复制
8. 如何删掉一张表的重复记录(经典面试题)
# 假设 emp 表有重复的数据
1、创建一张临时表 my_tmp,结构和 emp 一样
create table my_tmp like emp;
2、把 emp 的记录通过 distinct 处理后将记录复制到 my_tmp （这里复制数据之后删除之前的表，然后将临时表改名也可以）
insert into my_tmp select distinct * from emp;
3、删除 emp 表的记录
delete from emp;
4、把 my_tmp 表的记录复制到 emp
insert into emp select * from my_tmp;
5、drop 掉临时表 my_tmp;
drop table my_tmp
9. 合并查询
# 演示合并查询
# 使用集合操作符号 union,union all
# union all 就是将两个查询结构合并，不会去重
select ename,sal,job from emp
where job = 'MANAGER'
union all
select ename,sal,job from emp where sal > 2500;
union 会去重
select ename,sal,job from emp
where job = 'MANAGER'
union
select ename,sal,job from emp where sal > 2500;
union all
union
10. 外连接
10.1 左外连接
左外连接：如果左侧的表完全显示就是左外连接，能匹配，正确保留，不能匹配，其它表的该字段都置空null
# 列出部门名称和这些部门的员工信息(名字和工作)，同时列出那些没有员工的部门
select emp.deptno,dname,ename,job from
dept left join emp on dept.deptno = emp.deptno order by dname;
10.2 右外连接
右外连接：如果右侧的表完全显示就是右外连接，能匹配，正确保留，不能匹配，其它表的该字段都置空null
# 列出部门名称和这些部门的员工信息(名字和工作)，同时列出那些没有员工的部门
select emp.deptno,dname,ename,job from
emp right join dept on dept.deptno = emp.deptno order by dname;
11. 约束
约束用于确保数据库的数据满足特定的商业规则。
mysql中有五种约束规则：not null、unique、primary key、foreign key、check
11.1 primary key
primary key：用于唯一的标示表行的数据，当定义主键约束后，该列不能有重复值且不能为空。
一张表最多只能有一个主键，但是可以是复合主键。
直接在字段名后面指定 字段名 primary key。
在表的最后面指定 primary key（列名）。
# 字段后面指定主键
create table t11
id int primary key,
`name` varchar(25),
phone_number char(11));
# 表最后面指定
create table t11
id int,
`name` varchar(25),
phone_number char(11)，primary key(`name`));
# 复合主键，后面的 id,`name` 就是复合主键，
# 当添加的数据 id和 name 都相同的时候才会报错
create table t12(
id int ,
`name` varchar(25),
phone_number CHAR(11),primary key(id,`name`));
11.2 not null
not null：当在某一列定义此字段，则表示该列必须要插入数据，不能为空
11.3 unique
unique：唯一的约束键，该列值不能重复。
如果没有指定 not null ，则 unique 字段可以有 null 值，如果指定了，类似于 primary key 。
一张表可以有多个 unique 字段。
# 演示unique
create table t13(
id int unique,
`name` varchar(25),
email varchar(20));
insert into t13 VALUES(null,null,'tom@126.com');
select * from t13;
11.4 foreign key
用于定义主表和从表之间的关系：外键约束要定义在从表上，主表则必须具有主键约束或者unique约束，当定义外键约束后，要求外键列数据必须在主表的主键列存在或者是为null
注意事项：
外键指向的主表字段，要求必须设置 primary key或者 unique
表的类型必须是 innodb
外键字段的类型必须和主键字段的类型一致，长度可以不同
外键字段的值，必须在主键字段出现过，或者为null（前提是外键字段允许为null）
一旦建立主键外键关系，主表数据便不能随意删除
# 演示 foreign key
create table my_class(
id int primary key,
class_name varchar(25));
insert into my_class VALUES(100,'java班'),(200,'PHP班');
select * from my_class;
create table my_stu(
id int,
`name` varchar(25) not null default '',
class_id int,
foreign key (class_id) references my_class(id));
insert into my_stu VALUES(1,'tom',100),(2,'jarry',200);
insert into my_stu VALUES(3,'bob',null); # 这样插入可以成功，因为外键字段允许 null
insert into my_stu VALUES(3,'bob',300); # 这样插入会报错，因为主表没有 id 300
select * from my_stu;
11.5 check
12. auto_increment(自动增长)
字段名 整型 primary key auto_increment
自动增长一般和 primary key 配合使用
自动增长也可以单独使用(但是需要配合一个 unique)
自动增长修饰的字段为整数型的(小数也可以，但是很少使用)
自动增长默认从1开始，你也可以修改开始的值： alter table 表名 auto_increment = 值;
自动增长也可以在插入数据时指定值，不建议使用
# 演示 auto_increment
create table t14(
id int primary key auto_increment,
`name` varchar(20),
email varchar(25));
desc t14;
select * from t14;
insert into t14 VALUES(null,'tom','tom@126.com'); # 指定该字段为空，它会从1开始自动增长，下一个指定为空，则是2
insert into t14 (`name`,email) VALUES('jack','jack@126.com'); # 另外一种写法，只有在自动增长里才适用
insert into t14 VALUES(10,'jerry','jerry@126.com'); # 在插入时，指定值，以指定值为准
insert into t14 VALUES(40,'allen','allen@126.com');
delete from t14 where id = 2; # 删除某一行之后，后面插入的也不会使用之前的数值
delete from t14;
alter table t14 auto_increment = 50; # 可以修改默认值
insert into t14 VALUES(null,'smith','smith@126.com');
13. 索引
# 演示索引
create table t15 (
id int,
`name` varchar(20));
# 查询某张表是否有索引
show index from t15;
show indexes from t15;
show keys from t15;
# 添加索引
# 添加唯一索引
create unique index id_index on t15 (id);
# 添加普通索引
create index id_index on t15 (id);
alter table t15 add index id_index (id);
# 如何选择？
# 如果某列的值是不会重复的，则优先选择 unique 索引，否则选择普通索引
# 删除索引
drop index id_index on t15;
# 删除主键索引
alter table t15 drop primary key;
14. 事务
# 演示事务
create table t16 (
id int,
`name` varchar(25),
phone CHAR(11))
engine innodb;
select * from t16;
-- 开启事务并设置一个保存点
start transaction;
savepoint a;
-- 插入数据
insert into t16 VALUES(1,'张三',13245789823);
-- 再设置一个保存点
savepoint b;
-- 添加数据
insert into t16 VALUES(2,'李四',13698275689);
-- 回退到保存点b
rollback to b;
-- 回退到保存点a
rollback to a;
-- 不指定回退点，则回退全部事务
rollback
-- 提交事务
commit;
14.1 事务隔离级别
14.1.1 读未提交（read uncommitted）
读未提交脏读演示，左边终端开启一个事务，右边终端开启一个事务（右边终端的隔离级别是 read uncommitted ），当右边的终端能读取到左边没有提交的事务所做的修改时，就产生了脏读。
读未提交不可重复读和幻读演示，当右边的终端能读取到左边已经提交事务的修改删除操作，就产生了不可重复读；当右边的终端能读取到左边已经提交事务的插入操作，就产生了幻读。
14.1.2 读已提交（read commited）
读已提交脏读演示，左边终端开启一个事务，右边终端开启一个事务（右边终端的隔离级别是 read committed ），当右边的终端未读取到左边没有提交的事务时，不会产生脏读。
可以看到右边的终端并没有看到左边终端插入的数据。
读已提交不可重复读和幻读演示，当右边的终端能读取到左边已经提交事务的修改删除操作，就产生了不可重复读；当右边的终端能读取到左边已经提交事务的插入操作，就产生了幻读。
可以看到，当左边的终端事务提交以后，右边的终端能够获取到左边事务进行的修改和插入操作。
14.1.3 可重复读（repeatable read）
可重复读这个隔离级别不会产生脏读、不可重复读、幻读。
不会产生脏读
也不会产生不可重复读和幻读
14.1.4 可串行化（serializable）
可串行化这个隔离级别不会产生脏读、不可重复读、幻读，会对表加锁。
当左边的事务没有提交的时候，右边的终端查询的时候会卡主，会报错。
14.2 事务的ACID
15. 表类型和存储引擎
mysql的表类型由存储引擎(storage engines)决定，主要包括 myisam,innodb,memory
mysql数据表主要支持六种类型，分别是：csv、memory、archive、mrg_myisam、myisam、innodb
这六种又分为两类：
一类是“事务安全型”(transaction-safe)，比如：innodb；
其余属于第二类，称为“非事务安全型”(non-transaction-safe),比如：myisam 和 memory
myisam 不支持事务，也不支持外键，但其访问速度快，对事务完整性没有要求。
innodb 提供了具有提交、回滚和崩溃恢复能力的事务安全。但是比起 myisam，innodb写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。
memory 存储引擎使用存在内存中的内容来创建表，每个 memory 表只实际对应一个磁盘文件，memory 类型的表访问非常快，因为它的数据是放在内存中的(没有IO读写)，并且默认使用 hash 索引。但是一旦 mysql 服务关闭，表中的数据就会丢失掉，表的结构还在。
# 查看所有的引擎类型
show engines;
修改表的引擎指令
alter table 表名 engine = 存储引擎
如何选择表的存储引擎？
1、如果你的应用不需要事务，处理的只是基本的 crud 操作，选择 myisam
2、如果需要支持事务，选择 innodb
3、需要速度极快，临时数据选择 memory，例如，用户的在线状态
16. 视图
视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含列，其数据来自对应的真实表(基表)。
通过视图可以修改基表的数据。
基表的改变，也会影响到视图的数据。
视图中可以再使用视图。
# 创建视图
create view 视图名 as select语句
create view emp_view as select empno,ename,job,deptno from emp;
select * from emp_view;
# 修改视图
alter view 视图名 as select语句
# 查看视图创建的命令
show create view 视图名
# 删除视图
drop view 视图名1，视图名2
# 多表联合创建视图
# 针对emp , dept ，和salgrade张三表.创建一个视图,
# 可以显示雇员编号，雇员名，雇员部门名称和薪水级别[即使用三张表，构建一个视图
create view emp_view03 as
select empno,ename,dname,grade from dept,emp,salgrade
where dept.deptno = emp.deptno and
emp.sal between losal and hisal;
select * from emp_view03;
17. 用户管理和权限管理
17.1 用户管理
# mysql用户管理
# 使用场景
# 根据岗位职责，给不同的人赋予可以操作数据库的部分权限
# 创建的用户会存放到 mysql 库中的 user 表
# 创建用户并可以指定登录地址以及密码
# '用户名'@'登录地址'：登录地址本地 localhost，任意地址用 % 表示，
# 也可以是某个ip或者某个网段 192.168.1.%
create user '用户名'@'登录地址' identified by '密码';
# 创建的用户存入 mysql 库的 user 表
# 默认只能查看 information_schema 库
# 用户自己修改自己的密码
set password = password('密码');
# 修改别人的密码，需要权限
set password for '用户名'@'登录地址' = password('密码');
#
删除用户
drop user '用户名'@'登录地址'
# 示例
select * from mysql.user;
create user 'lxr'@'localhost' identified by '123';
# 删除用户
drop user 'lxr'@'localhost';
17.2 权限管理
# 演示权限管理
# 给 lxr 用户授权 hsp_db02 库的 emp 表的查看权限
grant select on hsp_db02.emp to 'lxr'@'localhost';
# 回收权限，用 all 则是回收所有权限
revoke all on hsp_db02.emp from 'lxr'@'localhost';
本文笔记来自韩顺平B站视频，非个人原创，以下链接点击跳转原视频。
韩顺平零基础学习mysql数据库B站链接，点击跳转
2174
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
