MySQL 数据库

数据库(DataBase)定义：长期存放在计算机内，有组织，可共享的大量数据的集合，是一个数据"仓库"，用于保存和管理(即增删改查)数据，可以有效保障数据一致性、完整性、降低数据冗余，可以满足应用的共享和安全方面的要求

分类

1. 关系型数据库/SQL：MySQL，Oracle，SQL Server，SQLite，DB2 处于主流状态

    特点是将数据按表的形式存在，且表与表之间存在关系，需要将数据放在一个服务器，最大优势是保持数据的一致性

2. 非关系型数据库/NOSQL: Redis，MongoDB

    特点是数据分散在不同服务器，即使是大量数据写入，可以分散压力

DBMS(Database Management System): 数据库管理系统，科学组织和存储数据，高效获取和维护数据，增删改查是通过DBMS处理

MySQL特点：开源，免费，可跨系统运行，适用于windows和linux系统

mysql的常用命令，应用于虚拟机中配置的mysql，[]代表内容是可选择的命令，非必填项：

建立名为school的数据库	create database [if not exists] school;
删除名为school的数据库	drop database [if exists] school;
使用school数据库	use school;
查询所有表信息	show table status;
查看所有的库名单	show databases;
修改school库的字符编码	alter database school character set ‘UTF8’;
建school库时指定字符编码	create database school character set ‘UTF8’;
给外部访问授权,如JDBC无法连接数据库/两个数据库做主从复制，需使用	grant all on *.* to 'root'@'%' identified by 'ok';
刷新	flush privileges
创建student表,拥有主键，两种加粗方法，任选其一	create table student( id INT PRIMARY KEY , name VARCHAR(10), PRIMARY KEY (id) );
创建studentDemo，拥有主键，并包含student的外键约束，以及唯一约束 修改外键:先删除外键，再新增正确的外键	create table studentDemo( id INT, name VARCHAR(10), PRIMARY KEY (id),  FOREIGN KEY (id) REFERENCES student(id), UNIQUE (name) );
在已有的studentDemo表中添加外键,主键，唯一键,索引	alter table studentDemo add constraint  foreign key id references student; alter table studentDemo add primary key(id); alter table studentDemo add Unique(key_name); alter table student add index(key_name);
删除外键,主键,唯一键,索引	alter table studentDemo drop foreign key id; alter table studentDemo drop primary key; drop index key_name on student; drop index key_name on student;
student和school，相互关联，在删除表的时候出错：Cannot delete or update a parent row: a foreign key constraint fails。 原因：表关联生成的强制约束问题，在删除的时候回检查表之间的关联关系，从而导致无法删除，处理方法如右图	a. SET foreign_key_checks = 0; // 先设置外键约束检查关闭 b. drop table student; // 删除表，如果要删除视图，也是如此 c. SET foreign_key_checks = 1; // 开启外键约束检查，以保持表结构完整性
将test查询结果导入或复制到 test_copy表	create table test_copy select * from test; create table test_copy as select * from test;
将test表的数据结构克隆到test_clone表	create table test_clone like test;
复制旧表的表格结构（包含表头），不包括具体表格数据	create table studentDemo as select * from student where 1=0;
查看student表结构	DESC student;
删除表全部数据和表结构，立刻释放磁盘空间，不管是 Innodb 和 MyISAM	drop table student;
删除表全部数据，保留表结构，立刻释放磁盘空间 ，不管是 Innodb 和 MyISAM;	truncate table student;
删除表全部数据，表结构不变，对于 MyISAM 会立刻释放磁盘空间，InnoDB 不会释放磁盘空间;	delete from student;
带条件的删除，表结构不变，不管是 innodb 还是 MyISAM 都不会释放磁盘空间;	delete from student where xxx;
查看student表格，位于school数据库	show tables student [in school];
查看student表的建表语句	show create table student；
查看student表的全部内容	select * from student;
显示当前用户id 显示当前用户 显示当前mysql版本	select connection_id(); select user(); select version();
声明语句结束符	DELIMITER $$
查询数据库中有多少张表	SELECT COUNT(*) TABLES, table_schema FROM information_schema.TABLES WHERE table_schema = 'azkaban' GROUP BY table_schema; table_schema = 'azkaban'——azkaban指的是数据库名

常用口令

更改表名student为stu	ALTER TABLE student RENAME stu; RENAME TABLE student TO stu;
添加表的字段名score	alter table student add score int(2);
删除表字段score	alter table student drop score;
更改表字段age为stuage，表字段属性设为int	alter table student change name
只更改表字段属性	alter table student modify age varchar(5);
新增行数据	insert into student values (1, 'alpha'), (2, 'beta'), (3, 'delta');
增加查询之后的表数据	insert into student select * from student;
删除id=2的行数据	delete from student where id=2;
更改id=1的行数据name为HHH	update student set name='HHH' where id=1;
筛选不在student范围的学生数据	select * from result where stuId not in (select stuId from student);
查询去除重复name数据之后的student数据	select distinct name from student; SELECT DISTINCT name FROM test;
根据表字段zeta查找表数据	select * from student where name = 'zeta';
mysql group by后select的字段受限的解决方法	受限字段前添加any_value
GROUP BY X, Y	是将所有具有相同X字段值和Y字段值的记录放到一个分组里。
查找name为zeta并且id大于30的表数据	select * from student where name = 'zeta' having id>30;
根据id分组查询age小于20的表数据	select * from student group by id having age<20;
查找根据name排序之后的表数据	select * from student order by name;
查找根据name反序排序之后的表数据	select * from student order by name desc;
查找先根据name排序，再根据age排序之后的表数据	select * from student order by name,age;
查找先根据age反序，再根据name排序之后，年龄最大的前三位的表数据	select * from student order by age desc,name limit 3;
查找先根据age反序，再根据name排序之后，年龄最大的前三位，并跳过第一条的表数据	select * from student order by age desc,name limit 3, offset 1; select * from student order by age desc,name limit 1,3;
使用正则查询name表字段中包含字母g的表数据	select * from student where name regexp '.*[g]+.*';

连接&函数查询

内连接A表和B表，基于相同的id值	select * from A inner join B on A.id=B.id;
左连接A表和B表，基于相同的id值	select * from A left join B on A.id=B.id;
左外连接查询，左表独有数据	select * from A left join B on A.id=B.id where B.id is null;
交叉查询	select * from A cross join B; select * from A,B;
全连接查询 UNION默认不返回重复的数据，UNION ALL则会返回重复的数据	select id, name from A UNION select id,name from B; select id, name from A UNION ALL select id, name from B;
求总数	select count(id) as total from student;
求和	select sum(age) as total from student;
求平均值	select avg(age) as total from student;
求最大值	select max(age) as max_age from student;
求最小值	select min(age) as min_age from student;
绝对值8 圆周率3.141593 大于X的最小整数值6 小于X的最小整数值5 返回集合中最大值30 返回集合中最小值10 余数1 返回0-1的随机数	select abs(-8); select pi(); select ceil(5.5); select floor(5.5); select greatest(10,20,30); select least(10,20,30); select mod(1001,10); select rand();
返回当前日期，时间，日期时间	select current_date, current_time, now();
返回当前时间的小时	select hour(current_time);
datediff函数返回两个日期之间的天数，计算方式为date1-date2	DATEDIFF(date1,date2)
timestampdiff函数显示日期或日期时间表达式之间的整数差，计算方式为datetime2-datetime1	TIMESTAMPDIFF(interval，datetime1，datetime2)，比较的单位interval为year, month,minute,day,week等表示时间的单数形式
查询当天的所有数据	select * from student where DATEDIFF(字段,NOW())=0;
查询昨天的所有数据	select * from student where DATEDIFF(字段,NOW())=1;
求最小值	select * from c_id where score <= all(select c_id from score)
求最大值	select * from c_id where score >= all(select c_id from score)
concat()函数	将多个字符串连接成一个字符串， concat(str1, str2,...) 返回结果为连接参数产生的字符串，如果有任何一个参数为null，则返回值为null。所以使用时最好与ifnull函数结合使用
IFNULL(v1,v2):	v1 不是 NULL，返回 v1 v1为Null，返回 v2
concat_ws()函数	和concat()一样，将多个字符串连接成一个字符串，但是可以一次性指定分隔符～（concat_ws就是concat + separator） 语法：concat_ws(separator, str1, str2, ...) 说明：第一个参数指定分隔符。需要注意的是分隔符不能为null，如果为null，则返回结果为null。 select concat_ws(',',id,name,score) as info from student; 把分隔符指定为null，结果全部变成了null： select concat_ws(null,id,name,score) as info from student;
group_concat()函数	将group by产生的同一个分组中的值连接起来，返回一个字符串结果 使用group_concat()和group by显示相同名字的人的id号： select name,group_concat(id) from student group by name; id号从大到小排序，且用'_'作为分隔符 select name,group_concat(id order by id DESC separator '_') from student group by name;
substring_index（str,delim,count）	substring_index（被截取字段，关键字，关键字出现的次数）  count是正数，从左往右数，第N个分隔符的左边的全部内容 count是负数，从右边开始数，第N个分隔符右边的所有内容 SELECT SUBSTRING_INDEX(‘15,151,152,16’, ’ , ’ , 1); //结果是15 以第一个逗号为分割截取 SELECT SUBSTRING_INDEX(‘15,151,152,16’, ’ , ’ , 2); //结果是15,151 //以第二个逗号为分割截取 SELECT SUBSTRING_INDEX(‘15,151,152,16’, ’ , ’ , -1); //结果是16 //从后面开始算第一个逗号
FIND_IN_SET(str,strlist)	str 要查询的字符串 strlist 字段名 参数以”,”分隔 如 (1,2,6,8) 查询字段(strlist)中包含(str)的结果，返回结果为null或记录
limit分页	Limit位置偏移量，条目数 limit 0,10等价于limit10 范例：表里有107条数据，我们只想要第32，33条数据 limit 31,2 mysql 8.0版本出现offset关键字，例如limit 3 offset 4，获取从第5条记录开始后面的3条记录，和limit 4,3返回的值相同
rank()	有间隔的分级，结果为1,2,2,4 SELECT Score, rank() over(Partition by stuName ORDER BY Score desc) as 'Rank' FROM score
row_number()	求行数，结果为1,2,3,4 SELECT Score, row_number() over(Partition by stuName ORDER BY Score desc) as 'Rank' FROM score 不需要考虑是否并列，哪怕根据条件查询出来的数值相同也会进行连续排名
dense_rank()	无间隔的分级，结果为1,2,2,3 SELECT Score, dense_rank() over(Partition by stuName ORDER BY Score desc) as 'Rank' FROM score
lag(param1, param2, param3) 用于取前N行 param1：表中列名 param2：前N行 param3：超出行数时默认设置值, 没设置就是null	lag（参数1，参数2，参数3）over（partition by...order by 列） 参数3可省略 partition by子句将结果集中的行划分区, 如果partition by未指定子句，则结果集中的所有行都将被视为单个分区
lead()：取后N行	lead（参数1，参数2，参数3）over（partition by...order by 列）
MOD()	取余  MOD(10,4)得到2
case when 简单函数 case expression    when condition1 then result1    when condition2 then result2 。。。   else result end	CASE 表示函数开始，END 表示函数结束。 如果 condition1 成立，则返回 result1, 如果 condition2 成立，则返回 result2， 当全部不成立则返回else后面的 result， 而当有一个成立之后，后面的就不执行 case 后面填了字段，就会和 when 后面的字段比较，相等取 then 后面的值，否则取 else 后面的值。 case when 连一起，when 后面必须是一个表达式，类似于 if () else
case when 搜索函数 case   when condition1 then result1  when condition2 then result2 。。。   else result end
REGEXP 就是 regular expression 正则表达式	^ 表示以后面的字符为开头 [] 表示括号内任意字符 - 表示连续 * 表示重复前面任意字符任意次数 \ 用来转义后面的特殊字符，以表示字符原本的样子，而不是将其作为特殊字符使用 $ 表示以前面的字符为结尾 前缀名以字母开头：^[a-zA-Z] 前缀名包含字母（大写或小写）、数字、下划线_、句点. 和 或 横杠-：[a-zA-Z0-9\_\.\-]* 以域名'@leetcode.com'结尾：(@leetcode\.com)$
substring_index(str,delim,count) str:要处理的字符串 delim:分隔符 count:计数	例子：str=www.wikibt.com substring_index(str,'.',1)  结果是：www substring_index(str,'.',2) 结果是：www.wikibt 如果count是正数，那么就是从左往右数，第N个分隔符的左边的所有内容  如果count是负数，那么就是从右往左数，第N个分隔符的右边的所有内容 substring_index(str,'.',-2) 结果为：wikibt.com

窗口函数

MySQL窗口函数简介_Backcanhave7-CSDN博客_mysql 窗口函数

Leetcode--SQL刷题(176-262)_代码写的越急，程序跑得越慢。-CSDN博客

外键： 给数据一定的限制条件，一般来说，定义的数据范围来自一个表的一个字段（唯一且非空），来源要用到这些数据的表，要和该表建立外键约束，那么使用的数据不能超出来源的范围

select r.*, @a:=@a+1 from result r, (select @a:=0) s;——

@a用于定义临时变量，:=起赋值作用，s是临时表(select @a:=0)的表别名

(@a:=@a+1)代表定义一个变量，每次叠加1；

行转列

case subjectNo when 2 then score else 0 end '语文';

case when subjectNo=3 then score else 0 end '数学';

if(subjectNo=4,score,0)'英语'

from result where subjectNo in(2,3,4);

Mysql Sql 语句练习题 （50道） - 梅花GG - 博客园 (cnblogs.com)https://www.cnblogs.com/kangxinxin/p/11585935.html

查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
(下述方法写的太漂亮了，添加到博客）
SELECT st.* FROM student st
JOIN score sc ON sc.`s_id`=st.`s_id`
GROUP BY st.`s_id`
HAVING SUM(IF(sc.`c_id`="01" OR sc.`c_id`="02" ,1,0))>1

查询没有学全所有课程的同学的信息
select st.* from Student st
left join Score S on st.s_id = S.s_id
group by st.s_id
having count(c_id)<(select count(c_id) from Course)

查询和"01"号的同学学习的课程完全相同的其他同学的信息

select st.* from student st
left join score sc on sc.s_id=st.s_id
group by st.s_id
having group_concat(sc.c_id) = (
select group_concat(sc2.c_id) from student st2
left join score sc2
on sc2.s_id=st2.s_id
where st2.s_id ='01' )

查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩 select st.s_id,st.s_name,avg(sc.s_score)
from student st
left join score sc on sc.s_id=st.s_id
where sc.s_id in (
select sc.s_id from score sc
where sc.s_score<60 or sc.s_score is NULL
group by sc.s_id
having COUNT(sc.s_id)>=2 )
group by st.s_id

按各科成绩进行排序，并显示排名
SELECT c1.s_id,c1.c_id,c1.c_name,c1.s_score,@i:=@i+1 FROM

(SELECT c.c_name,sc.* FROM course c 
LEFT JOIN score sc ON sc.c_id=c.c_id
WHERE c.c_id="01" ORDER BY sc.s_score DESC) c1 ,
(SELECT @i:=0) a
UNION ALL 
SELECT c2.s_id,c2.c_id,c2.c_name,c2.s_score,@ii:=@ii+1 FROM

(SELECT c.c_name,sc.* FROM course c 
LEFT JOIN score sc ON sc.c_id=c.c_id
WHERE c.c_id="02" ORDER BY sc.s_score DESC) c2 ,
(SELECT @ii:=0) aa 
UNION ALL
SELECT c3.s_id,c3.c_id,c3.c_name,c3.s_score,@iii:=@iii+1 FROM (

SELECT c.c_name,sc.* FROM course c 
LEFT JOIN score sc ON sc.c_id=c.c_id
WHERE c.c_id="03" ORDER BY sc.s_score DESC) c3;
SET @iii=0;

第二高的薪水

select ifNull
((select distinct Salary 
from Employee
order by Salary Desc
limit 1,1),null)
as SecondHighestSalary

拓展: 第N高的薪水
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
set N = N - 1;
  RETURN (
      select ifnull((
          select distinct Salary 
          from Employee
          order by Salary desc limit N,1),null) as getNthHighestSalary
  );
END

引用力扣前排答题，利用用户变量实现对连续出现的值进行计数
select distinct Num as ConsecutiveNums
from (
  select Num, 
    case 
      when @prev = Num then @count := @count + 1
      when (@prev := Num) is not null then @count := 1
    end as CNT
  from Logs, (select @prev := null,@count := null) as t
) as temp
where temp.CNT >= 3

1）逻辑：构建两个变量@prev 和@count，
@prev与Num做比较判断，@count用于@prev和Num相等时的条件计数；

2）(select @prev := null,@count := null) as t 
初始化两个变量，并将初始化后的变量放到一张临时表t中，
:=符号在MySQL中是赋值的意思；

3）when @prev = Num then @count := @count + 1
when (@prev := Num) is not null then @count := 1 
这两个语句不能交换顺序，赋值语句永远非NULL，所以一旦执行顺序来
到了第二个when，@count 是一定会被赋值为1的，后者放到前面的话就
达不到计数的目的；

4）(@prev := Num) is not null这部分去掉后面加的判断，SQL也能正
常执行，上面SQL中case when的这种用法，when后是判断条件，赋值后又
加判断，我原以为这样会好理解点；

5）case when本质是一个函数，有值时就返回内部处理得到的值，无值就
返回NULL，针对每一个Num，上面SQL中的case when 都会有一个计数，并
把这个计数返回给CNT。



扩展窗口函数写法
SELECT DISTINCT Num as ConsecutiveNums 
FROM(
    SELECT Id,Num,
    Lead(Num,1)OVER() as num_1, #选择指定行的向前一行的内容
    Lead(Num,2)OVER() as num_2  #选择指定行的向前两行的内容
    FROM Logs
) as c
WHERE c.Num = c.num_1 AND c.num_1 = c.num_2

更换性别 

update Salary 
set sex=if(sex='m','f','m');

count（1）	遍历全表，但是不取值，server 层对返回的每一行数据新增一个 1，然后进行判断累加。包括NULL值行	若表多个列并且没有主键，则 count（1） 的执行效率优于 count（*）
count（*）	返回表格中所有存在的行的总数包括值为null的行	若表只有一个字段，则 select count（*）最优。
count（列名）	判断字段是否为NULL，在过滤统计。不包含NULL值行	若列名为主键，count(列名)会比count(1)快 若列名不为主键，count(1)会比count(列名)快     若表有主键，则 select count（主键）的执行效率是最优的

事务: 将一组sql数组放在同一批次执行，要么都执行，要么都不执行

特性	信息
原子性(atomicity)	代表不能再分割，事务开始后所有操作，全部做完/不做
一致性（consistency）	事务开始前和结束后，数据库的完整性约束没有被破坏 。比如A向B转账，不可能A扣了钱，B却没收到
隔离性（isolation)	多线程环境下，一个线程中的事务不能被其他线程中的事务打扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账
持久性（durability)	事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚

事务实现方法与口令

set autocommit	使用set语句来改变自动提交模式
set autocommit=0	关闭自动提交模式
set autocommit=1	开启自动提交模式
mysql默认是自动提交，使用事务时应先关闭自动提交
开启事务	Start Transaction，任何一条DML语句(insert、update、delete)执行，标志事务的开启
结束事务	End Transaction
提交事务	Commit Transaction
回滚事务	Rollback Transaction 数据回到本次事务的初始状态
事务处理过程中无错误时提交 COMMIT，事务处理过程中有错误需回滚

   

 数据库索引

作用：提高查询速度；确保数据的唯一性；可以加速表和表之间的连接，实现表与表之间的参照完整性；使用分组和排序子句进行数据检索时，可以显著减少分组和排序的时间；全文检索字段进行搜索优化

主键索引(PRIMARY KEY)	数据库默认会为主键字段添加索引，主键索引只能有一个     某一个属性或属性的组合能唯一标识一条记录
唯一索引（UNIQUE）	避免同一个表中某数据列中的值重复，唯一索引可有多个
常规索引（INDEX）	快速定位特定数据，index和key关键字都可设置常规索引，不宜添加太多常规索引，影响数据的插入、删除和修改
全文索引（FULLTEXT）	快速定位特定数据，只能用于MyISAM类型的数据表，只能用于 CHAR 、 VARCHAR、TEXT数据列类型，适合大型数据集

MySQL数据库备份方法

mysqldump备份工具（mysqdump与mysql是同级别的命令，都是在bin目录下的exe执行文件，所所以 mysqldump 不是 在mysql命令里执行的）

mysqldump  -h 主机名 –u 用户名 –p   [options]   数据库名   [ table1 table2 table3 ]   > path/filename.sql直接输入口令如下，可备份school表格