MySQL学习

select
    groupId,count(pid) as cnt
from
    mydata
where
    salary > 1000
group by
    groupId
having
    cnt > 4
order by
    cnt
limit m,n
;

//执行顺序
from --> where --> group by --> count(pid) --> having
--> select -->order by --> limit;

4.正则表达式

正则表达式关键字REGEXP

1.^ 以a开头的字符串匹配 select 'abc' REGEXP "^a"; ---true

2.$ 以a字符结尾的字符串 select 'abc' REGEXP "a$"; ---true

3. . 字任意字符+b的字符串 select "abc" REGEXP '.b'; ---true

4.[...] 匹配括号内的任意字符 select "abc" REGEXP '[xyz]'; ---false

5.* 匹配0个或者多个a,包括空字符. select 'stav' REGEXP '.ta*v'; ---true;

6.+ 匹配1个或者多个a,不包括空字符. select 'stab' REGEXP '.ta+b'; ---true;

7.? 匹配0个或者一个a. select 'stb' REGEXP '.ta?b'; ---true;

8.| a1|a2 匹配值是a1或者a2. select 'a' REGEXP 'a|b';

5.多表操作

1)外键约束

关键字:foreign key

[constraint <外键名>] foreign key(`字段名`) references `表名`(`字段名`);

alter table emp2 add constraint emp2_fk foreign key(`字段名`) references 表名(`字段名`);

外键约束分为主表和从表,主表的主键控制从表的指定字段,而从表数据可以随意修改,而主表被引用字段不能删除,修改.

删除外键

格式: alter table <表名> drop foreign key <外键约束名>;

外键约束可以一对一,一对多,多对多.

一对多:一个主表对应多个从表;

多对多:一个中间表对应多个主表;

同时还有自关联约束,用本表的主键来约束本表的字段

create table `sg`(
user_id int,
user_name varchar(20),
user_Supervisor_id int,    --外键列

foreign key(user_Supervisor_id) references `sg`(id)    --自关联约束
)

2)多表联合查询

1.交叉连接查询:select * from Table1,Table2;

2.内连接查询

隐式内连接:select * from Table1,Table2 where 条件;

显示内连接:select * from Table1 inner join Table2 条件;

3)外连接查询

左外连接:left join

select * from A left join B on 条件;

左边表作为主表查询,右表没有的数据填充为null;

右外连接:right join

select * from A right join B on 条件;

右边表作为主表查寻,左边表没有的填充为null;

在查询中以左,右为主表进行查询,作为主要查询,如果没有的字段则不显示,而从表没有的字段则为null;

同时后面可跟group by再进行分割和过滤

full join(Oracle方法),而在MySQL中使用union 和union all来进行满外连接查询

select * from emp1 join left emp 2 on a=b union select * from emp1 join right emp2 on a = b;

这样会将左,右查询后的两张表拼接起来.

4)子查询

select的嵌套查询.

例子:查询研发部年龄小于20的员工,包含工号和名字.

子查询:

--现在有两张表,一个部门表和一个员工表,部门表有部门cid和部门名字
--而员工表则有员工id,名字,部门id;

select *
    from `员工表` 
        where cid = (
select cid
    from `部门表`
        where name="研发部")
and age < 20 ;

这里是直接嵌套查询,将部门表查询到的值返回给员工表进行查询.

连接查询:

select * 
    from `部门表` 
        join `员工表`
            on work.cid = club.cid 
and(name = "研发部" and age <20);

这里用关联查询,查询到关联的表后再进行筛选.

或者将两个查询结果进行关联查询:

select * 
    from (
select * 
    from `部门表` 
        where name="研发部") as `club`
join (
    select * 
        from `员工表` 
            where < 20) as `work`
on work.cid = club.cid;

这里将两个表查询的结果当作一个零时表,然后进行关联的id查询,得到相应的结果.

子查询关键字-ALL

格式 :select * from `表名` where num > all(查询语句);

表示,all中返回的所有值与num进行比较,符合则返回true

ANY和SOME

格式:select * from `表名` where num >any(查询语句);

any表示与子查询返回的任何值进行比较,有一个为真返回true;

some等于是any的别名.

格式:select * from `表名` where num in (查询语句);

in关键字,用于判断num是否在查询的指定集合中,同时可以配合not使用将条件反过来.

EXISTS

格式:select * from `表名` where exists(查询语句);

exists关键字表示,如果查询语句至少返回一条数据,则成立返回true,如果一条数据都没有返回null则返回false,而前面的select是否答应当前查找的值就看是否为true;

exists效率比in高.

--判断公司是否有大于60岁的员工,有则输出
select * from emp3 a where exists(select * from emp3 where a.age > 60);

--查询 有 所属部门的员工信息
select * from emp3 a where exists(select * from dept3 b where a.cid = b.cid);

当一个员工的cid不等于部门表任意一个cid,将不会被输出.

5)自关联查询

MySQL有时候在信息查询时需要进行对表自身进行关联查询,一张表自己和自己关联,一张表当作多张表来用,但是注意要给表起别名.

格式:select `字段列表` from `表1` a ,`表1` b where 条件;

select `字段列表` from `表1` join a ,`表1` b on 条件;

--将sg当作三张表进行关联查询
select 
a.ename,b.ename,c.ename
from `sg` a
    left join `sg` b on a.id = b.id
    left join `sg` c on b.id = c.id;

6.聚合函数

1)group_concat

该函数可以将一列数据全都放入到一行中,并且可以排序

语法:select group_concat(`字段名` order by salary separator ';') from emp;

group_concat里的字段将被合并成一行数据,并且可以排序,同时默认的分隔符为,如果需要更改则需要在末尾加上separator '分隔符'.

同时也可以配合group by使用,可以将对应部门的人都显示出来,并且不会报错

select demp,group_concat(emp_name) from emp group by demp;

2)其他简单的数学函数

abs(x):求x的绝对值. ---|x|

ceil(1.1):向上取整 ---2

floor(1.9):向下取整 ---1

greatest(1,2,3):取列表最大值 ---3

least(1,2,3):取列表最小值 ---1

mod(5,2):取模 ---1

power(2,3):取x的y次方 ---8

rand():取随机数,但是是0-1之间的小数 ---0.294849343342

round(3.115,2):x位四舍五入取整,保留x位小数 ---3.12

truncate(3.14159,3):小数截取x位 ---3.141

3)字符串函数

char_length("abc"):返回字符串长度 ---3

length("abc"):返回字符串字节长度 ---3

length("你好a"):不同字符返回字节不同 ---7

concat("c1","c2"):将c1和c2两列拼接成一行,没有分隔符. ---c1c2

concat_ws('-',"hello","world"):指定分隔符将两列合并. ---hello-world

field("aaa","aaa","bbb","ccc"):返回aaa在后面三个字符串中出现的位置 ---1

ltrim(" cccc"):去除字符串左边空格. ---ccc

trim(" c1cc "):去两端空格 ---c1cc

rtrim("cccc "):去除字符串右端空格 ---cccc

mid("helloworld",2,3):截取字符串,从第2个开始截取三个 ---ell

position("abc" in "helloabcworld"):获取字符在字符串中第一次出现的位置 ---6

replace("helloabcworld","abc","bbb")

reverse("abc"):字符串反转 ---cba

ucase(s):转换为大写

lcase(s):转换为小写

strcmp("hello","world"):字符比较,按照字典的顺序比较第一个字符 --- -1

4)日期函数

unix_timestamp("2022-4-3 10:10:10"):将一个日期字符串转换为毫秒数 --- 1648951810

from_unixtime(1648951810,"%Y-%m-%-%d %H:%i:%s"):将毫秒数转成一个指定的字符串格式

curdate():获取当前年月日

curtime():获取当前时分秒

current_timestamp():获取当前年月日时分秒

date("2022-1-1 12:32:12"):从当前日期字符串中提取年月日 ---2022-1-1

datediff("2022-10-10","2000-2-3"):计算两个日期的天数差 ---8285天

datediff(curdate(),"2020-10-10"):获取当前时间到指定时间的差值

timediff("12:12:32","10:12:32"):获取时间的差值(时分秒)

date_format("2021-1-1 12:21:21","%Y-%m-%d %H:%i:%s"):日期格式化

date_sub("2022-1-1",interval x day):将指定的日期减去x天

date_add("2022-1-1",interval x day):将指定的日期加上x天

ps:日期加减单位是可变的可以是day,month,secound,hour等等时间单位

extract(时间单位 from "2020-10-3" 11:12:32):从指定的日期中获取对应时间单位的值.

例子extract(hour from "2020-10-3" 11:12:32) ----11

extract(month from "2020-10-3" 11:12:32) -----10

last_day("2020-10-12"):获取给定日期的最后一天 ---2020-10-30

makedate("2021",53):获取给定年份和天数换算为日期时间 ---2021-02-22

select 时间单位 ("2021-12-1" 11:21:21):还是根据时间单位获取日期

ps:时间单位 year month minute quarter(季度)等

日期名称 ("2021-1-1 11:11:13"):获取当前日期名称

select monthname("上面日期") ---January

select dayname("上面") ---Friday

select dayofweek("") ---6 (6-1=5) 周日是1

select dayofyear("") ---1 本年第几天

select week("") ---1 本年第几周

5)if逻辑判断语句

if(条件,返回值1,返回值2):跟三元运算符?一样

例子:select *,if(score >= 85,'nice',bad) flag from score;

当分数返回大于或等于85,返回nice.会多出一个flag列来显示.

ifnull(comm,0):如果给定字段为null,那么值就是0.

isnull(`值`):判断值是否为null,如果不是则返回false.

6)窗口函数

select `字段1`,`2`,`3`,lag(`字段`,n,`默认值`) from `表名`

lag()函数可以将给定字段按照指定数读取前第n行的数据.,没有则填充默认值,默认值不写则为null.

lead()函数则相反,是读取后面的值.

first_value(`字段`) over (partition by `字段` order by `字段`) as `别名`,

last_value() from emp;

上面first和last一个是查询表开头该字段的值,而last是到当前行数据的值.

select * from(select name,salary,ntile(n) over(partition by name order by hiredate) as `se` from emp)t where t.se = 1;

ntile()函数可以将当前表分为n组,这里通过名字将一张表分为几张零时表,同时对表内数据进行分组,然后通过分组的别名id进行查找.

7.MySQL视图

视图(view)是一个虚拟表,其实本质是根据SQL语句获得动态的数据集,并为其命名,用户使用时只需使用视图名称就可以获得结果,跟我们Java中封装的工具类一样,我们只需要调用就可以获得想要的结果,而视图可以当作表来使用.

作用:简化代码,可以把重复使用的查询封装成视图重复使用,同时可以使复杂的查询易于理解.

并且我们可以控制对不同的用户输设定不同的视图,比如用户只能看见自己的个人信息,但是管理员可以看见所有人的信息一样.

创建视图语法:

create [or replace] view `视图名字` as `select查询语句`;

or replace如果不加,该视图如果存在则会报错,加了则会用创建的替换现有的视图.

例子:

--创建一个视图,供我们用来进行使用
create view view_name as select * from stu;

--使用视图进行查询
select * from view_name;

--查看当前有哪些视图
show full tables;

修改视图:

修改视图是指修改数据库中已经存在的表的定义,当基本表的某些字段发生改变时,可以通过修改视图来保存视图和基本表之间一致,MySQL中通过create or replace view语句和alter view语句来修改视图.

格式:alter view `视图名` as select语句;

更新视图*:

某些视图是可以更新的,可以在update,delete,insert等语句中使用它,以跟新基表的内容.

对于可更新的视图,在视图中的行和基表之间必须具有一对一的关系.如果包含下面任何一种结构,则不可更新,大白话(视图中的select语句中不能包含下面关键字,因为是辅助进行查询的)

聚合函数(sum(),min(),max(),count()等)
distinct
group by
having
union或union all
位于选择列表中的子查询
join
from字句中的不可更新视图
where子句中的子查询,引用from字句中的表
仅引用文字值(在该情况下,没必要更新基本表)

虽然可以更新数据,但是没必要限制太多了.

可以更新例子:

--通过视图对基表进行数据修改
update view_name set ename = "aaa" where ename = "bbb";

--通过视图插入数据到基表
insert into view_name values("aaa");

不可以:

create view view_name as select count(*) from emp;

create view view_name as select distince salary from emp;

create view view_name as select ename from emp group by ename;

create view view_name as select * from emp union select * from emp;

create view view_name as select * from emp where age = (select age from emp);

create view view_name as select * from view_name2(这是不可更新的视图);

删除和修改视图名:

--修改视图名称
rename table view_name to view_name2;

--删除视图
drop view [if exists] view_name;

8.MySQL的存储过程

存储过程就是一组SQL语句集，功能强大，可以实现一些比较复杂的逻辑功能，类似于Java中的方法，存储过程就是数据库SQL语言层面的代码封装与重用。

创建存储过程（函数）:

--创建一个存储过程

delimiter `自定义结束符号`

create procedure `函数名`()
  begin
    --中间是执行的SQL语句
    select empno,ename from emp;
  end `结束符号`

--将delimiter恢复到分号
delimiter ;

----------------------

--示例

delimiter $$

create procedure proc01()
    begin
      select empom,ename from emp;
    end $$
delimiter ;

调用存储过程:

--调用存储过程,可以多次调用
call proc01();
call proc01();
call proc01();

变量

1）局部变量

用户自定义，在begin/end块中有效

语法: 声明变量 declare `变量名` `变量类型` default `默认值`;

第一种赋值方式，set赋值

delimiter $$

create procedure proc02()
  begin
    declare var_name01 varchar(20) default 'aaa'; --这个是局部变量
    set var_name01 = "张三";
    select var_name01;  --输出变量
   end $$

delimiter ;

第二种方式赋值，into

delimiter $$

create procedure proc03()
  begin
    declare var_name01 varchar(20); --这个是局部变量
    --通过表查询，然后将单个值赋值给变量
    select ename into var_name01 from emp where empno = 1001;
    select var_name01;  --输出变量
   end $$

delimiter ;

2)用户变量

用户自定义，在当前会话都有效,类比Java的成员变量.

语法:@`变量名`

不需要提前声明，使用就声明

--用户变量

delimiter $$

create procedure proc04()
    begin

      set @var_name01 = "six";

    end $$

delimiter ;

--调用函数
call proc04()

--在函数体外也可以使用用户变量
select @var_name01;

3)系统变量

系统变量又分为全局变量和会话变量。

全局变量会在MySQL启动的时候，服务器自动将他们初始化默认值，这个默认值可以通过更改my.ini文件来更改。

会话变量是在每次建立一个连接的时候，由MySQL进行初始化。

全局变量可以对整个数据库可见，会话变量只在当前会话可见（等于是当前线程可见）。

全局变量语法:@@global.`全局变量名`;

--查询所有全局变量
show global variables;

--修改全局变量
set @@global.auto_increment_increment = 100;

会话变量语法:@@session.`会话变量名`

--查询会话变量
show session variables;

--修改会话变量
set session sort_buffer_size = 10000;

4)存储过程传参

in表示传入的参数，可以传入数值或者变量,即使传入变量，也不会更改变量的值。

可以内部更改，仅作用在函数体内.

delimiter $$

create procedure proc06(in empno int)
    begin
     select * from emp where emp.empno = empno;
    end $$

delimiter ;

call proc06(1002);

out表示将函数过程内部值给调用者，相当于返回值.

--out返回值

delimiter $$

create procedure proc08(in user_id int,out out_username varchar(20))
    begin

      --通过传入的id查找到相应的名字并且返回
      select username into out_username from emp where id = user_id;
      
    end $$

delimiter ;

--调用函数，并且创建一个变量来接收值
call proce08(1001,@user_name);

select @user_name;

inout相当于in和out的结合，使用一个变量传入后，再进行一个接收，不再演示。

9.MySQL触发器

触发器，是一个特殊的存储过程（函数），当对数据库表中数据执行DML操作时自动触发这个SQL片段执行，无需手动调用。

在MySQL中，只有执行insert,delete,update操作时才能触发触发器执行。

可以用在数据库的日志记录，数据校验增操作。

触发器的特性:

什么条件触发:I.D.U
什么时候触发:在操作前面还是后面
触发频率:针对每一行执行
触发器定义在表上,附着在表上

格式:创建只有一个执行语句的触发器

create trigger `触发器名` `触发时间` 触发事件
on `要绑定的表名` for each row
执行的语句;

--其中，触发时间指的是在操作之前或者之后before/after
--触发事件指的是I U D这三个操作语句

格式:创建有多个执行语句的触发器

create trigger 触发器名 触发时间 触发事件
on 绑定表名 for each row
begin
    --需要执行的语句列表
end;

触发器的NEW与OLD

在进行触发器操作的时候，有时候需要获取当前修改的数据数值。

通过NEW和OLD来进行获取.

触发器类型	触发器类型使用
insert型触发器	NEW表示将要或者已经新增的数据
update型触发器	OLD表示修改之前的数据，NEW表示将要或者已经修改后的数据
delete型触发器	OLD表示将要或者已经删除的数据

使用方法:NEW.`列名`

--insert型触发器
create trigger t1 after insert
    on `user` for each row
        insert into user_logs values(
NULL,now(),concat("有新用户添加:",NEW.uid,NEW.`列名`)

);



insert into user values(2,"张三","123456");

--有新用户添加:2张三

create trigger t2 after update
on user for each row
insert into user_logs values(
NULL,now(),concat("更新之前:",OLD.uid,"更新之后",NEW.uid));

10.MySQL索引

索引就是给每个数据一个标记,到时查找时可以迅速找到,等于就是用空间换时间.

在创建主键的时候会自动给主键字段创建索引!!!

1.普通索引

普通索引允许值重复并且可以为null或者空

创建普通索引的方式:

--在创建表的时候创建
create table student(
id int,
name varchar(20),
salary int,
age int,
index `索引名`(`需要创建索引的字段`)
);



--直接创建索引
create index `索引名` on `表名`(`字段名`);



--修改表结构创建

alter table `表名` add index `索引名`(`字段名`);

查看索引:

--查看指定数据库索引
select * from mysql.`innodb_index_stats` a 
where a.`database_name` = `数据库名`


--查看表中的所有索引
show index from `表名`

删除索引:

drop index `索引名` on `表名`;

alter table `表名` drop index `索引名`;

2.单例索引

单例索引不允许索引字段重复,只能加在唯一的列上,但是值可以是空的.

创建:

--创建表时添加单例索引

create table student(
id int primary key,
card_id varchar(20),
age int,
number int,
unique `索引名`(`字段名`)
);


--直接创建
create unique index `索引名` on `表名`(`字段名`);


--修改表结构
alter table `表名` add unique `索引名`(`字段名`);

3.组合索引

组合索引,指建立索引的时候使用多个字段,同时也可以将其定义为普通索引或者单例索引.

格式:

create index `字段名` on `表名`(字段1,字段2);

*全文索引

只有字段数据类型为char,varchar,text系列的才能建立全文索引.

fulltext(`字段名`);

4.空间索引

空间索引是MySQL5.7之后才有的,可以用来对经纬度进行索引.

--创建空间索引

create table student(
id int not null,
shop_name varchar(20) not null,
geom_point geometry not null,
spatial key `索引名`(`字段名`)
);

11.MySQL的事务

MySQL的事务(Transaction)是由存储引擎实现的,在MySQL中只有InnoDB存储引擎才支持事务.

事务处理可以用来维护数据库的完整性,保证SQL语句要么全部执行成功,要么全部失败不执行.

事务用来管理DDL,DML,DCL操作,都是默认自动提交的.

1)事务操作

--开启事务
Start Transaction

--提交事务
Commit Transaction

--回滚事务
Rollback Transaction

其实在使用SQL操作的时候有事务,但是MySQL自动帮我们提交了.

我们也可以手动关闭MySQL的自动提交.

--关闭自动提交
set autocommit = 0 

--开启自动提交
set autocommit = 1

事务的特性:

原子性:事务是一个不可分割的整体,要么全部完成,要么全部失败.
一致性:从一个正确的状态迁移到另一个正确的状态.
隔离性:每个事务的对象对其他事务的操作对象互相隔离,事务提交前对其他事务不可见.
持久性:事务一旦提交,则会写到主表中结果是永久性的

事务隔离级别:

read uncommitted 读未提交
read committed 读已提交 (Orlace默认事务隔离级别)
repeateable read 可重复读(MySQL默认隔离级别)
serializable 序列化

读未提交:一个事务可以读另一个事务未提交事务中的数据,会造成脏读.

读已提交:一个事务要等到另一个事务提交后才能读取数据,会造成不可重复读.

可重复读:在开始读取数据(事务开启)时,不再允许修改操作,会造成幻读.

串行:最高事务隔离级别,保证不会发生前三种问题,但是效率低下,一般不用.

2)事务隔离级别操作

查看隔离级别: show variables like '%isolation%';

设置隔离级别:

--设置读未提交
set session transaction isolation level read uncommitted;

--读已提交
set session transaction isolation level read committed;

--可重复读
set session transaction isolation level repeatable read;

--串行
set session transaction isolation level serializable;

12.MySQL日志

1)错误日志

错误日志记录了MySQL启动和停止时,以及服务器在允许过程中发生任何严重错误的相关信息,当数据库出现任何故障导致无法正常使用时,可以查看此日志.

查看日志位置:show variables like 'log_error%';

2)二进制日志(binlog)

该日志记录了所有的DDL语句和DML语句,但是不包括数据库查询语句,此日志对于灾难时的数据恢复起着极其重要的作用,MySQL的主从复制就是通过该日志实现的.

二进制日志,MySQL8.0默认开启,低版本的MySQL需要通过配置文件开启,并且配置MySQL格式

Windows系统:my.ini Linux系统:my.cnf

#配置开启binlog日志,日志的文件前缀为mysqlbin,例如:mysqlbin.01 mysqlbin.02
log_bin=mysqlbin


#配置二进制日志的格式
binlog_format=STATEMENT

查看日志

--查看MySQL是否开启了binlog日志
show variables like 'log_bin';

--查看所有日志
show binlog events;

--查看最新的日志
show master status;

--查看指定日志数据
show binlog events in '日志名';

--同时还支持指定位置查看和分页显示
show binlog events in `日志名` from 123 limit 1,2;

3)查询日志

所有的DQL查询操作都会被记录到这个里面.

默认情况是未开启的,如果要开启则要进行配置:

#设置开启查询日志,0关1开
general_log=1


#设置日志的文件名,如果没有指定默认名未host_name.log
general_log_file=日志名

4)慢查询日志

慢查询日志记录了所有执行时间超过参数long_query_time设置值并且扫描记录数据不小于min_examined_row_limit的所有SQL语句的日志.

long_query_time默认为10s,最小为0,精度可以到微秒.

#该参数用来控制慢查询日志是否开启
slow_query_log=1


#该参数用来指定文件名
slow_query_log_file=slow_query.log


#用来配置查询的时间限制,超过这个时间则被认为是慢查询,进行记录
long_query_time=10

--查看是否开启慢查询日志
show variables like 'slow_query_log%';


--开启慢查询日志
set global slow_query_log = 1;

--查看慢查询的超时时间
show variavbles like 'long_query_time%';