MySql知识点全面总结
结构化查询语言SQL概述
SQL(Structured Query Language) 是结构化查询语言的缩写。
SQL是在关系数据库上执行数据操作、检索及维护所使用的标准语言,可以用来查询数据，操纵数据，定义数据，控制数据，所有数据库都使用相同或者相似的语言。
SQL可分为:
- 数据定义语言（DDL） : Data Definition Language
- 数据操纵语言（DML） : Data Manipulation Language
- 事务控制语言（TCL）：Transaction Control Language)
- 数据查询语言（DQL）：Data Query Language
- 数据控制语言（DCL） : Data Control Language
执行SQL语句时，用户只需要知道其逻辑含义，而不需要知道SQL语句的具体执行步骤。
数据定义语言（DDL）
用于建立、修改、删除数据库对象，包括创建语句(CREATE)、修改语句(ALTER)、删除语句(DROP)，比如使用CREATE TABLE创建表，使用ALTER TABLE修改表，使用DROPTABLE删除表等动作。这类语言不需要事务的参与，自动提交。
DDL
create drop alter
创建数据库：
create database 数据库名；
删除数据库
drop database 数据库名；
创建表：
create table 表名(
字段1 id 类型(长度) primary key,auto_increment,(设置主键，自动递增)
字段2 类型(长度) 约束,
字段3 类型(长度)
)
修改表名
alter table 表名 rename 新表名
修改字段
alter table 表名 change 字段名 新字段名(新长度) [新的约束]。
删除表结构
drop table 表名
drop ：删除表删的是表的结构
delete:删除删的是表中的数据
数据操作语言（DML）
用于改变数据库数据，包括INSERT、UPDATE、DELETE三条语句。其中，INSERT语句用于将数据插入到数据库中，UPDATE语句用于更新数据库中已存在的数据，DELETE用于删除数据库中已存在的数据。DML语言和事务是相关的，执行完DML操作后必须经过事务控制语句提交后才真正的将改变应用到数据库中。
DML insert delete update
全表插入数据
Insert into 表名(字段1，字段2，字段3) values(值1，值2，值3)
Insert into 表名 values(值1，值2，值3)
部分插入，给部分字段赋值
Insert into 表名(字段1，字段2) valus(值1，值2)
全表删除数据
Delete from 表名；
DDL全表删删除 truncate table 表名
delete与truncate区别
truncate是DDL，
只能全表删除，不能加条件删除，
truncate删除全表后，auto_increment主键会被清空，下次插入数据，主键值从新的主键开始增长
truncate全表删除，一次性直接删除，删除的速度快，效率高
delete是DML,可以有条件删除
部分数据删除，有条件的删除
delete全表清空后，auto_increment主键不会被清空，下次插入数据，主键值还在原来的auto_increment主键值的基础上自增
delete全表删除，是一条一条删除，速度慢，效率低
delete from 表名 where id=xxx;
全表修改 修改表中的数据
update 表名 set 字段1=新值1，字段2=新值2，字段3=新值3
有条件修改
update 表名 set 字段1=新值，字段2=新值2，子段3=新值3 where 子段=值
数据查询语言（DQL）
用来查询所需要的数据。使用最广泛，语法灵活复杂。
DQL:数据查询语言 select
全表查询
select * from 表名
查询部分字段，并给查询的部分字段取别名
select 字段1 as 新字段1，字段2 新字段2 from 表名
去重关键字：distinct
select distinct(字段) from 表名
-条件查询
条件查询：where ( 数值类型 > < = != <> )
select * from 表名 where 数值字段>10
where ( and or ) : select * from 表名 where 条件1 and/or 条件2
select * from 表名 where 数值字段>=10 and 数值字段<=20
between and (数值/日期 查询在某个区间)
select * form 表名 where 字段 between 10 and 20;
null和 not null 只能用 is null is not null 不能用=null !=null
模糊查询
模糊查询：like(通配符) 通配符：_ 一个字符 % 0或多个字符
select * from 表名 where 字段 like %白
分组查询
分组查询：以什么字段分组，查询的字段就可以是这个分组字段，但是不能出现其他字段，如果希望查询出现其他字段，需要用聚合函数
select 字段1 form 表名 group by 字段1
select 字段1,字段2 form 表名 group by 字段1，字段2
排序
排序：order by 字段1 asc/desc【，字段2 asc/desc，字段3 asc/desc】
asc:升序
desc:降序
order by 默认升序
执行顺序
(5)select (1)from (2)where (3)group by (4)having (6)order by
常用函数
常用的函数 concate(参数1，参数2)连接 substr切割 date_format(日期，格式) upper():字母变大写 lower():字母变小写
select concate('hello','world') from dual;
select concate(字段1，字段2） from 表名
select substr('hello',1,2) from dual//he
select substr(字段,1,2) from dual
select date_format(now(),'%d')
获得当前日期时间
select now();
select sysdate();
聚合函数
聚合函数：查询多条记录，返回一条结果
avg():求平均 sum()：求和 count():统计个数 max():最大 min():最小
select 聚合函数1(),聚合函数2(),分组字段 from 表名 group by 分组字段；
外键
外键：
一张表与另一张表有关联，产生父子关系，表一的外键依赖表2的主键，表1是父表 表2是子表
特点：想要删除子表中的记录，必须先将父表中有依赖子表中的数据删除
加外键：
alter table 表名 add foreign key(父表字段) reference 子表表名(字段)
多表查询
笛卡尔乘积： 每一张表的每条记录，都会匹配另一张表的所有记录 结果记录条数：
表1条数*表2条数
select 表1.*,表2.* from 表1，表2；
等值连接：笛卡尔乘积的基础上，过滤出来两个表中都符合条件的记录
select 表1.*,表2.* from 表1，表2 where 表1.外键=表2.主键
内连接：结果与等值连接一样
select 表1.*，表2.* from 表1 [inner] join 表2 on 表1.外键=表2.主键
左外连接：
左表所有的记录都会显示，右表能匹配上的记录都显示，右表不能能匹配上的记录字段用NUll填充显示
两个表的交集，及左表没有被匹配上的左表信息，右表没匹配上的信息所有字段用Null填充
select 表1.* ,表2.* from 表1 left [outer] join 表2 on 表1.外键=表2.主键
右外连接：
右表所有的记录都会显示，左表能匹配上的记录都显示，左表不能能匹配上的记录字段用NUll填充显示
两个表的交集，及右表没有被匹配上的右表信息，和左表没匹配上的信息所有字段用Null填充
select 表1.* ,表2.* from 表1 right [outer] join 表2 on 表1.外键=表2.主键
自连接：自己与自己做连接
select emp.*,emp1.* from emp,emp1 where emp.mgr=emp1.empno;
select emp.*,emp1.* from emp,emp as emp1 where emp.mgr=emp1.empno;
多张表连接：
select 表1.*,表2.*，表3.*
from 表1
inner join 表2
on 表1.外键=表2.主键
inner join 表3
on 表1.外键=表3.主键
where 表1条件 and 表2条件 and 表3条件
子查询
子查询：where条件不是一个特定的值，而是一个select查询
单行单列子查询： select 字段 from 表名 where 条件=10
外连接：(> < =) select * form 表名 where 字段=(单行单列子查询)
多行单列子查询：select 字段 from 表名 where 条件>10 查询结果相当于一个列表（值1，值2，值3）
外连接：(in not in ><连接着any/all ) select * form 表名 where 字段 in(多行单列子查询)
分页查询
select * from 表名 limit (偏移量（从0开始），每页显示条数)
偏移量：（页数-1）*每页显示多少条数
视图
视图是指计算机数据库中的视图，是一个虚拟表，其内容由查询定义。
同真实的表一样，视图包含一系列带有名称的列和行数据。
但是，视图并不在数据库中以存储的数据值集形式存在。
行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。
-- 创建视图
create view v_emp1 as select * from emp where job='leader';
-- 修改视图
create or replace view v_emp1 as select * from emp where job='manger'
-- 查看所有表及视图
show tables;
-- 查看视图结构
desc v_emp1;
--创建一个有约束的视图
create view v_emp2 as select * from emp where job='clerk' with check option ;
-- 操作视图会间接操作原始表
insert into v_emp2(empno,ename,job) values(1111,'马化腾','leader');-- job只能是clerk,插入leader报错
-- 创建视图依赖两张表
create view v_emp3 as select emp.*,dept.dname,dept.loc from emp inner join dept on emp.deptno=dept.deptno;
desc v_emp3;
-- 可以通过视图插入数据，但是只能基于一个基础表进行插入，不能跨表更新数据
insert into v_emp3(empno,ename,deptno,loc) values(1112,'刘强东',50,'上海');--报错
select * from v_emp1;
--删除视图
drop view if exists 视图名
-索引---
--创建索引，提高sql查询效率
-- CREATE INDEX index_name ON table_name (column_list)
CREATE INDEX index_emp ON emp (ename);
select * from emp where ename='郭芙蓉';
--删除索引
DROP INDEX index_name ON table_name
-存储过程-
-- 存储过程可以一次性执行批量sql
--创建存储过程
delimiter &&
create PROCEDURE P_emp1()
BEGIN
insert into emp(empno,ename,deptno) values(1115,'天线宝宝',10);
update emp set ename='天线宝宝1' where ename='天线宝宝';
select * from emp;
END
&&
delimiter;
--调用存储过程
call P_emp1();
--创建含有输入参数和输出参数的存储过程
delimiter &&
CREATE DEFINER=`root`@`localhost` PROCEDURE `P_emp1`(IN `_ename` VARCHAR(50), OUT `_empno` INT)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
insert into emp(empno,ename,deptno) values(1116,_ename,10);
select max(empno) into _empno from emp;
END
&&
delimiter;
--调用有参数的存储过程
call P_emp1('嫦娥',@_empno);
select @_empno;
触发器
-- 触发器一般使用在日志上，操作一张表，另一张表会受到影响，自动被触发执行一些操作
--创建触发器
elimiter &&
CREATE TRIGGER t_emp1 BEFORE UPDATE ON emp FOR EACH ROW BEGIN
insert into dept(deptno,dname,loc) values(61,'测绘','北京');
END
&&
delimiter;
--更改Emp，触发器会自动被触发
update emp set ename='肖奈3' where ename='肖奈2';
commit;
数据控制语言（DCL）
用于执行权限的授予和收回操作、创建用户等，包括授予(GRANT)语句，收回(REVOKE)语句，CREATE USER语句，其中GRANT用于给用户或角色授予权限， REVOKE用于收回用户或角色已有的权限。DCL语句也不需要事务的参与，是自动提交的。
DCL:数据控制语言
1 创建用户
create user 用户名@xxx identified by 密码；
create user zhangsan@localhost identified by 'root';
2 给用户授权
将数据库n个权限授予用户：
grant 权限1，权限2，...，权限n on 数据库名.* to 用户名@IP;
将操作数据库的所有的权限授予用户：
grant all on 数据库名.* to 用户名@IP;
3 撤销权限：
revoke 权限1，权限2，...，权限n on 数据库名.* from 用户名@IP;
revoke delete on aa.* form zhangsan@localhost;
4 查看用户的权限
show grants for 用户名@IP
show grants for zhangsan@localhost;
5 删除用户
drop user 用户名@IP
drop user zhangsan@localhost;
登录：mysql -u用户名 -p密码
mysql –u zhangsan –p root
或者 mysql --user=用户名 --host=ip地址 --password=密码
退出登录： exit;
事务控制语言（TCL）
用来维护数据一致性的语句，包括提交(COMMIT)、回滚(ROLLBACK)、保存点(SAVEPOINT)三条语句，其中COMMIT用来确认已经进行的数据库改变， ROLLBACK语句用来取消已经进行的数据库改变，当执行DML操作后(也就是上面说的增加、修改、删除等动作)，可以使用COMMIT语句来确认这种改变，或者使用ROLLBACK取消这种改变。SAVEPOINT语句用来设置保存点，使当前的事务可以回退到指定的保存点，便于取消部分改变。
TCL:事务控制语言
事务是访问并可能更新数据库各种数据项的一个程序执行单元，
在关系数据库中，一个事务可以是一条SQL语句，一组SQL语句或整个程序，
当一个事务执行并完成修改时，并不是对目标表立即进行修改，
此时修改的结果只是保存到临时缓存中，只有利用事务控制命令才最终认可这个事务
事务有应该具有4个属性：原子性，一致性，隔离性，持续性 这四个属性
原子性（atomicity）。一个事务是一个不可分割的工作单位，事务中包括的诸操作要么都做，要么都不做。
一致性（consistency）。事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。
隔离性（isolation）。一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
持久性（durability）。持续性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。
set autocommit=false;
设置手动提交；
rollback 回滚；
commit 提交；
savepoint pl 保存；设置一个保存点
rollback to pl 回滚到pl保存点:也就是撤销这个保存点之后的事务操作
RELEASE SAVEPOINT p1 删除创建的保存点:在这个保存点被释放之后，就不能再利用ROLLBACK命令来撤销这个保存点之后的事务操作了。
文章来源: albertyang.blog.csdn.net，作者：Albert Yang，版权归原作者所有，如需转载，请联系作者。
原文链接：albertyang.blog.csdn.net/article/details/83833161
- 点赞
- 收藏
- 关注作者
评论（0）