Mysql 史上最全学习资料十分硬核

1.DDL 数据库-操作

查询所有数据库： show Databases

查询当前数据库： select DATBASE()

创建数据库： create database [ if not exists ]数据名称 

删除 ： Deop   database   【if  exists】 [数据库名]

使用数据库 ： use  数据库名 

DDL 表操作-查询

查询当前数据所有表： show tables 

查询表结构： desc 表名；

查询指定表的建表语句：show create table 表名；

DDL-表操作-创建

create table 表名（ 字段1 字段1类型 [comment]  ‘具体值’，）

DDL-表操作-数据类型

  TINYINT   1byte   +unsigned 表示 无符号 数值 

SMALLINT  2byte 

IMEDIUMINT 3byte 

INT  或 INTECER 4byte 

BIGINT 8byte

FLOAT 4byte 

DOUBLE 8byte 

DECTMAL  

DDL-表操作-修改：

添加字段： alter Table  表名 add 字段名  类型（长度) [comment注释] [约束]

修改数据类型： alter Table 表名 Modify   字段名 新数据类型 （长度）

修改字段名和字段类型 ： alter Table  表名 change 旧字段名 新字段名 类型（长度） [comment z注释] ；

删除字段：  alter Table 表名 Drop 字段名；

修改表名： alter  Table 表名 Rename to 新表名；

删除表：drop table  【if exists 】 表名；

删除指定表，并重新创建该表 ：truncate table 表名

2.DML-添加数据 

给指定的字段添加数据： Insert  into 表名（字段名 1 字段名2 。。。）values(值1 值2。。。);

给全部字段添加数据 ： Insert into 表名 values （值1 值2.。。。）；

DML-修改数据

update 表名 set 字段名1=值1 字段名2=值2.。。。[where 条件];

例题：Update emply set name=itheim  where id=1;

update employ set enttrdate ='2001-10-29';

DML-删除数据

delete From 表名 [where 条件]；

DQL-语法-结构：

select 字段列表 from 表名  where 条件查询  group by   分组字段列表

order by 分组后条件列表  limit  分页参数。

DQL-基本查询：SQL编写顺序

select   [ distinct   去除重复数据]字段1[as ‘别名’ ] from 具体表名

DQL-条件查询：

Selsect 字段列表 from 具体表名 where（>,<,=,in(),like isnull,and,or not ） 条件列表；

between  min and max 

查询 姓名字段为两个字段的   like'_ _'    

聚合函数：将一列数据作为一个整体，继续纵向计算。

count：统计数量；   max； min;  avg;  sum;

语法：select 聚合函数（字段列表）  from 表名；

DQL-分组查询：

语法：select  字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过条件]

where和having区别：

执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组，而having是分组之后对结果进行过滤。

判断条件不同：where不能对聚合函数进行判断，但是having可以  (即在分组的基础上在进行筛选)。

根据性别分组，统计男女的数量：

select gender ，count（*）from emp group by gender;

查询年龄小于45的员工，并根据工作地址分组，获取员工数量大于等于3的工作地址；

select workaddress  count(* ) from emp where  age<45 group by workaddress having conut(*)>=3;

执行顺序：where>聚合函数>having.

分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。

DQL-排序 ：

select 字段列表 from 表名  order by 字段1 排序方式  ，字段2排序方式

根据年龄进行排序 （降序desc）

selsect * from emp  order by  age  asc（升序）；

先根据 年龄对公司员工进行排序，如果年龄相同，就按照入职时间再排。

select * from emp order by age asc ,entrdate desc;

DQL-分页查询：

select 字段 from 表名 Limit 起始索引，查询记录数；

注意：起始索引从0开始，起始索引=（查询页码-1）*每页显示记录数。

分页查询的是数据库的方言，不同的数据库有不同的实现，MySQL中是limit。

如果查询的是第一页数据，起始索引可以省略，直接简写为limit 10.

分页查询：

查询第一页员工数据， 每页展示10行；

select * from emp limit 0,10 ;

查询第二页员工数据，每页展示10条数据，

select *from emp limit10,10;

例题：

1.查询 年龄为20，21，22，23岁的女性员工信息：

select * from emp  where gender='女' ＆&age= in (20,21,22,23)

2.查询 性别为男，并且年龄在20-40以内 名字为三个字的员工；

select * from emp where gender='男' &&age= between 20 and 40&& name like '_ _ _'

3. 统计员工表中，年龄小于60岁，男性员工和女新员工的数量 ；

select  gender,count(*) from emp where age<60 gruop by gender 

4.查询所有年龄小于等于35岁的员工姓名和年龄，并对查询结果进行升序如果年龄相同就按照入职时间进行降序。

select name,age where age<='35 '  order by age esc,entrdata desc;

5.查询性别为男，且年龄在20-40岁（含）以内的前5个员工信息，对查询的结果按年龄升序排序，年龄相同的按照入职时间进行升序排序。

select * from emp where  gender='男' and age = between 20 and 40 order by age esc,entrdate desc limit 0,5;

DQL-执行顺序：

DCL-介绍：DCL是data control language ，用来管理数据库用户，控制数据库的访问权限。

DCL-用户管理：

查询用户：

use mysql ;

select * from user ;

创建用户：

create user ‘用户名’ @‘主机名’ IDENTIFIED BY ‘密码’；

修改用户密码：

ALTER user ‘ 用户名’@‘主机名’ IDENTIFIED WITH mysql_native_password BY '新密码'；sh

DCL-权限控制：

DCL-权限控制：

1.查询权限：show Grants  for '用户名'@‘主机名’；

2.授予权限：

grant 权限列表 on 数据库。表名 to ‘用户名’@'主机名'；

3.撤销权限：

revoke 权限列表 on 数据库.表名 from ‘用户名’@‘主机名’。

字符串函数：

 concat 字符串拼接：

select concat('hello)

select substring("hello Mysql"，1，5)

例题：由于业务需求的变更，企业员工的工号，统一为5位数，目前不足5位数的全部在前面补0，例如1号员工应该为00001。

lpad(字段名，位数，’填充字符‘)字符串填充 在左边  相同的还有rpad();

update emp set workno =lpad(workno,5,'0')

数值函数：

Ceil（x） 向上取整，

select ceil(1.9)  ==>2

floor（x）向下取整 

floor(1.9)===>1

mod(x,y)返回x/y的模 

mod(5/4)===>1

rand（）返回0-1的随机数  

 round（x,y）求参数x的四舍五入的值，保留y位小数。

select round(2.345,2)  ===>2.35

例：通过数据库的函数，生成一个六位数的随机验证码；

select  lpad(round( rand()*100000,6),6,'0')

日期函数

 select curdate();  ====>当前日期；

select  curtime()=====> 当前时间；

select  now()=====>当前日期和时间；

year（now() ）====>2022;

date_add(date,interval  expr type ):

select  date_add (now(),INTERVAL 70 day);当前时间向后推70天。

select datediff('2021-12-01','2021-11-01' );===> 61

例：查询所有员工的入职天数，并对入职天数进行倒叙排序

select  name, datediff(curdate(),entrdate  )  as '入职天数' from emp    order by  入职天数 desc

流程控制函数

 select if（true ，'ok','error' ）====>ok;

ifNull( 'ok','default')====>ok

if(null,'default')=====>defalut

case  when  then else  end

需求：查询emp表的员工姓名和工作地址；（北京、上海-----》一线城市，其他-----》二线城市）

select  name  ( case  workdress  when '北京' then   '一线城市' workdress when    then '一线城市'  else  ‘二线城市’  end)  as '工作地址'

from emp 

约束： 

概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。

目的：保证数据库中的数据的正确，有效和完整性。

分类：

 根据需求，完成表结构的创建：

id 自动增长 需要用 auto_increment    

建表语句：create table user（  

id int primary key auto_increment comment '主键',

name varchar(10)  not null  unique  comment ' 姓名'，

age int check( age>0&&age<=120)  comment '年龄'，

status char (1) defailt  '1'  comment' 状态',

gender char(1)  comment '性别'

）comment  '用户表'；

外键：

概念：外键是用来让两个表的数据之间建立连接，从而保证数据的一致性和完整性。

添加 主键与外键的关系：

 alter table  表名 addconstraint 外键名称  foreign key (外键字段名)  references 主表列名；

  

 多表查询：

 

多表查询：

 select * from emp,dept;(笛卡尔集)；多表查询时要消除笛卡尔积

select * from emp,dept   where emp.dept_id=dept.id;对相应的条件进行筛选

内连接 ：相当于时查询A,B表交集部分；

   隐式内连接：select  字段名 from 表一 表二 where 条件。。。；

显示内连接： select  字段名 from 表一 【inner】 表2  on  连接条件。。。；

例题：查询 每一个员工的姓名，及关联的部门的名称（隐式内连接）

select   emp.name ,dept.name from  emp,dept where  emp.dept_id= dept.id;

显示内连接： INNER join 。。。on 

select    e.name d.name from  emp e inner(可省略)   join dept   on    e.dept_id=d.id;

外连接：

       左外连接 ：查询左表所有的数据，以及两张表交集的部分

       右外连接：查询右表所有数据，以及两张表交集部分

自连接：当前表与自身进行连接查询，自联结必须使用别名。

 例题 ：查询 emp表的所有数据和对应的部门信息（左外连接 ）： 

操作的表结构 emp 和dept   

   select   e.*, d.name from  emp e left   outer(可省略) join  dept  d     on e.dept_id= d.id;

例题 ：查询 dept表的所有数据 和对应的员工信息（右外连接）

select    d.*  ,  e.*  from emp e right join  dept d on   d.id= e.dept_id ;

自连接：

 自连接查询 ，可以是内连接查询，可以是外连接查询。

例题： 查询员工 及其所属领导的名字：

select    a.name  b.name from emp a emp b  where  a.manager_id=b.id

select a.name ' 员工'  b.name ' 领导'   from emp a left  join emp b on   a.managerid=cb.id;

联合查询

   将 薪资低于5000的员工  和 年龄大于 50岁的员工 全部查询出来：

  select * from emp where  salary  <5000 

union all

select *from emp where age >50;

子查询：

 标量子查询：

 

 查询 比研发部其中任意一个人工资高的员工信息：

 1.查询 研发部 所有人的工资：

   select salary from emp where  dept_id =(select id from dept where  name='研发部');

2.比研发部其中任意一个人工资高的员工信息；

some ==any

selsect * from emp  where  salary >any ( select salary from emp where  dept_id =(select id from dept where  name='研发部'));

 

表 子查询：

 事物：

  事物操作：

查看、设置设置事物提交方式：

 select @@autocommit ;

set @@autocommit=0;   0为手动 1为自动；

提交事物  commit ；  回滚事物  rollback

开启事物  start transaction

事物四大特性：

 多个并发事物执行之中出现的问题：

 事物的隔离级别：

read uncommitted   读未提交；

read committed   读已提交；

repeatable read  （默认）可重复读；

serializable 串行化  ；

 性能从高到低  隔离级别 低到高    隔离级别越高性能越差

查看事物隔离级别：

select  @@ transaction_isolation;

 设置事物隔离级别：