Mysql基础与高级汇总

SQL语言分类

DDL:定义

DML：操作

DCL:控制(用于定义访问权限和安全级别)

DQL:查询

Sql方言 ->sql：结构化查询语言

mysql:limit 
oracle:rownum 
sqlserver:top 
但是存储过程：每一种数据库软件一样

SQL语法要求: ·SQL语句可以单行或多行书写，以分号结尾;

可以用空格和缩进来来增强语句的可读性;

关键字不区别大小写，建议使用大写;

表名,列名也不区分大小写

值可能区分,根据创建表,数据库的字节集决定

DCL

创建用户

create user zhangshan@localhost indentified by '123'
#创建用户名为张三 ，密码123 的本地用户
1396：用户名已经存在
创建一个远程用户：任何电脑都可以访问mysql服务器
用户名 ： abc123 密码：123
create user abc123@'%' indentified by '123';

DCL了解

数据控制语句:DBA操作DCL:授权,撤销权限操怍
创建用户.除用户,修改用户属于DDL语句  

查看某个用户有什么权限:

show grants for 用户名;

撤销权限:

revoke 权限1，权限2 on 数据库名.*/表名  from  用户 

改root用户：

DDL

-- "select * from user";
-- create database [if not exit] 数据库名称 -- 防止数据库名称存在时创建失败
-- charset :字符集 COLLATE:排列规则数据集(默认是)
--  utf8:中文占三个字节  24位的话，它的大小int类型：4个字节
-- 使用utf8mb4

-- 排序规则推荐使用:utf8mb4_bin

#创建数据库
create database if not exists mydb
default character set utf8mb4
collate utf8mb4_bin
#删除数据库
drop database if exists mydb
#修改 字符集
alter database mydb character set utf8
#查看排序编码
show variables like 'collation_%'
#查看字符集
show variables like 'character_set_%'

修改表：

修改列名：alter tablename change tell iphone varchar(20)
删除某一列：alert tablename drop tell 
修改表明:alert tablename rename to anothertablename
实际开发尽量不要修改表，尽量在表修改之前设计好
查看表结构:desc tablename; 

CRUD增删改查

create table tablename(id in comment '主键',name varchar(20) comment '姓名',sex char(1) comment '性别',borthday date comment '出生日期',address varchar(200) comment '地址'
) comment '学生表';
insert into 表名 values (值1，值12，值3)
sex char(1) -> 2个字节   插入'男' 不报错，可以存下，

给列赋值的顺序，跟创建表的列的顺序一致

值：char varchar date  -> '',就是说字符串，日期都用''引起

日期赋值格式:1999-12-23

alter tablename modify id int not null;
#设置这一属性后，插入或者修改数据是必须要有id不为空这一准则
#修改
1.全表修改
update 表名 set 列名1 = 值1 ,列名2 = 值2......;删除(物理删除，硬删除) ->实际开发中，你自己在软件中删除自己的账号更多的是一种软删除
物理:delete from 表名(后面没写就是全表删除)
只能一行一行的删除加条件的删除(部分行删除)
delete from tbname where name = "李四";DDL删除(删数据) -> 也是全表删除 但是不同于delete:1.delete是DML语句 truncate是DDL语句，delete删除表中的数据，truncate是删除表中的数据(drop table),再重新创建表,delete自增列不会还原，truncate删除表，自增列会还原,delete可以添加条件，部分删除，但是truncate只能全表删除
truncate tablename 表名;
#example:
alert table tablename modify id int primary key auto_increment ;
desc tb_name;
insert into tablename(name,sex) values ('张山','男')；
insert into tablename(name,sex) values ('张山','男')；
insert into tablename(name,sex) values ('张山','男')；
select * from tablename;
delete from tablename;
insert into tablename(name,sex) values ('张山','男')；
select * from tablename;
此时id从4开始 不会还原为1
但是truncate可以还原为id = 1
alter table;
软删除

Mysql函数

#获取当前日期
select curdate();
#获取日期加时间
select now()
#时间
select curtime()create table order(id int primary key,createtime datatime sysdate()#设置时间为系统时间
)
insert into order values(1,now());##获取日期各部分的值
select year(now()),month(now()),dayofyear(now())-1,dayofmonth(now());#日期的加减  -- #定时推送
select adddate(date(),7)#当前日期加7天  返回的是指定天数的日期日期格式化 %表示
select date_format(now(),'%年Y年%m月%d日 %H：%i:%s');
#某个日期/月最后一天的日期
#两百天的日期 ,该月的最后一天的日期
select last_day(addDate(now(),200))#流程函数
三个 if ifnull caseselect e.*,if(sal>=1500,'高薪水','底薪水') 薪水条件 from emp;ifnull(表达式,值) --> if(表达式 is null,值,表达式)

 

 

存储过程-游标-索引

修改存储过程需要重新编译(先删除后编译)

变量赋值用set 

变量赋值用set 

repeat

游标

游标解决(类似java迭代器):由于变量只能存一个值，不能存多个值注意！！！！

 

索引

普通查询（一行一行对比，叫全表扫描）

未完

多表查询

1. 联合查询 交集 并集(mysql只支持这一种) 差集

2. 表连接查询 内连接 外连接 自连接(内连接，外连接) 自然连接 全连接(mysql不支持)

3. 子查询

4. 函数查询

联合查询

把多个sql语句的结果集进行处理(交，并，差的处理) mysql只支持并集处理，但是可以sql实现差，交
交集关键字：union/union all
union可以去重
union all不可以去重

select 的sq语句union/union all要求:要求两个sql语句的结果集有相同数量/名字字段  
union:可以达到去重的目的

union可以去重复

union all：不去重

交集intersect：mysql5.7不支持 -> intersect

select * from emp where deptno = 20
intersect
select * from emp where job = 'CLERK'; 
--->不能实现，但是可以间接实现->内连接
替代法：内连接
要求：是同一张表
select * from emp where deptno = 20
and job = 'CLERK';
​
select * from emp where deptno = 'BM02'
and empno = 'E103';

差集(mysql也不支持)

A - B：两个集合的相差 存在与A但不存在于B

间接实现：

select * from where deptno = 20 and job != 'CLERK';

联合查询结束

外连接的方式

表连接

1.自然连接 笛卡尔积

带条件的筛选：(利用笛卡尔积)

其中大部分是基于外键的等值条件

1.1内连接：

inner select *|列名from 表1(表名,查询结果集,一定取别名)

内连接:要求A中的数据,在B表中必须有一条数据对应,查筛选出来

查询员工信息以及部门信息(emp表没有) ->需要两张表(只能：要求A中的数据,在B表中必须有一条数据对应,查筛选出来)

查询所有员工信息以及部门信息(内连接就不能使用，员工可能没有对应的部门，但是内连接要求要求A中的数据,在B表中必须有一条数据对应,查筛选出来)

emp:14条 dept:4条数据 ->查出14条数据

内连接另一种写法：(主流数据库都支持)

1.2外连接

#左外连接:left[ outer] join  
# join左边的表的数据要全部显示，右边的表数据，满足条件才显示
#右外连接right [outer] join
# join右边的表的数据要全部显示，左边的表数据，满足条件才显示
A left [outer] join B; == B right [outer] join A;

查询所有员工信息以及部门信息:

之前TOM显示不出来，因为dept=50不能对应在之前的内连接里面不成立，emp表只有10-30的数据，不能对应，这里外连接没有50对应的？怎么班，那就是null来对应

前者先显示员工，后者先显示部门

显示默认顺序可以改：

#表连接,如果某个字段在两张表名字一样,一定使用表别名.字段名区分

1.3全连接

全连接:两边表的数据要全部显示．full join在mysql不支持 
间接实现 left join和right join还有union（去重）
两个左外，一个Union实现  
oracle可以直接用full关键字实现

1.4自连接：自己跟自己连

例子：查员工的领导信息

因为员工和领导的信息都在一张表里面，查员工以及他的领导信息需要用到自连接，这张表要连两次

例子：查找员工的姓名和领导的姓名

select e1.ename 员工姓名,e2.ename 领导姓名 from emp e1 join emp e2
on e1.mgr = e2.empno;
员工表的领导编号mgr对应领导表的编号empno

备份数据