创建用户
    create user '用户名'@'IP地址' identified by '密码';
删除用户
    drop user '用户名'@'IP地址';
修改用户
    rename user '用户名'@'IP地址'; to '新用户名'@'IP地址';;
修改密码
    set password for '用户名'@'IP地址' = Password('新密码')

数据库名.*           数据库中的所有
            数据库名.表          指定数据库中的某张表
            数据库名.存储过程     指定数据库中的存储过程
            *.*                所有数据库

用户名@IP地址         用户只能在改IP下才能访问
            用户名@192.168.1.%   用户只能在改IP段下才能访问(通配符%表示任意)
            用户名@%             用户可以再任意IP下访问(默认IP地址为%)

show grants for '用户'@'IP地址'

grant  权限 on 数据库.表 to   '用户'@'IP地址'

revoke 权限 on 数据库.表 from '用户'@'IP地址'

grant all privileges on db1.tb1 TO '用户名'@'IP'

grant select on db1.* TO '用户名'@'IP'

grant select,insert on *.* TO '用户名'@'IP'

revoke select on db1.tb1 from '用户名'@'IP'

show tables;                    # 查看数据库全部表

select * from 表名;             # 查看表所有内容

create table 表名(
    列名  类型  是否可以为空，
    列名  类型  是否可以为空
)ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `tab1` (
  `nid` int(11) NOT NULL auto_increment,                   # not null表示不能为空,auto_increment表示自增
  `name` varchar(255) DEFAULT zhangyanlin,                 # default 表示默认值
  `email` varchar(255),
  PRIMARY KEY (`nid`)                                      # 把nid列设置成主键
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

drop table 表名

delete from 表名
truncate table 表名

添加列：   alter table 表名 add 列名 类型
删除列：   alter table 表名 drop column 列名
修改列：
          alter table 表名 modify column 列名 类型;  -- 类型
          alter table 表名 change 原列名 新列名 类型; -- 列名，类型
  
添加主键：
          alter table 表名 add primary key(列名);
删除主键：
          alter table 表名 drop primary key;
          alter table 表名  modify  列名 int, drop primary key;
  
添加外键： alter table 从表 add constraint 外键名称（形如：FK_从表_主表） foreign key 从表(外键字段) references 主表(主键字段);
删除外键： alter table 表名 drop foreign key 外键名称
  
修改默认值：ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
删除默认值：ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;

insert into 表 (列名,列名...) values (值,值,...)
insert into 表 (列名,列名...) values (值,值,...),(值,值,值...)
insert into 表 (列名,列名...) select (列名,列名...) from 表
例：
    insert into tab1(name,email) values('zhangyanlin','zhangyanlin8851@163.com')

delete from 表                                      # 删除表里全部数据
delete from 表 where id＝1 and name＝'zhangyanlin'   # 删除ID =1 和name='zhangyanlin' 那一行数据

update 表 set name ＝ 'zhangyanlin' where id>1

select * from 表
select * from 表 where id > 1
select nid,name,gender as gg from 表 where id > 1

select * from 表 where id > 1 and name != 'aylin' and num = 12;
    select * from 表 where id between 5 and 16;
    select * from 表 where id in (11,22,33)
    select * from 表 where id not in (11,22,33)
    select * from 表 where id in (select nid from 表)

select * from 表 where name like 'zhang%'  # zhang开头的所有（多个字符串）
    select * from 表 where name like 'zhang_'  # zhang开头的所有（一个字符）

select * from 表 limit 5;            - 前5行
    select * from 表 limit 4,5;          - 从第4行开始的5行
    select * from 表 limit 5 offset 4    - 从第4行开始的5行

select * from 表 order by 列 asc              - 根据 “列” 从小到大排列
    select * from 表 order by 列 desc             - 根据 “列” 从大到小排列
    select * from 表 order by 列1 desc,列2 asc    - 根据 “列1” 从大到小排列，如果相同则按列2从小到大排序

select num from 表 group by num
    select num,nid from 表 group by num,nid
    select num,nid from 表  where nid > 10 group by num,nid order nid desc
    select num,nid,count(*),sum(score),max(score),min(score) from 表 group by num,nid
    select num from 表 group by num having max(id) > 10
 
    特别的：group by 必须在where之后，order by之前

视图view

视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。对其中所引用的基础表来说，视图的作用类似于筛选。定义视图的筛选可以来自当前或其它数据库的一个或多个表，或者其它视图。通过视图进行查询没有任何限制，通过它们进行数据修改时的限制也很少。视图是存储在数据库中的查询的SQL 语句，它主要出于两种原因：安全原因， 视图可以隐藏一些数据。

1、创建视图

--格式：CREATE VIEW 视图名称 AS  SQL语句
CREATE VIEW v1 AS SELET nid, name FROM tab1 WHERE nid > 4

2、删除视图

--格式：DROP VIEW 视图名称
DROP VIEW v1

3、修改视图

-- 格式：ALTER VIEW 视图名称 AS SQL语句
ALTER VIEW v1 AS
SELET A.nid,B. NAME FROM tab1
LEFT JOIN B ON A.id = B.nid
LEFT JOIN C ON A.id = C.nid
WHERE tab1.id > 2

也就只是改了把create改成alter，中间的语句更换了。

4、使用视图

使用视图时，将其当作表进行操作即可，由于视图是虚拟表，所以无法使用其对真实表进行创建、更新和删除操作，仅能做查询用。

select * from v1

存储过程procedure

1、我们为什么要用存储过程呢 ？

我们都知道应用程序分为两种，一种是基于web，一种是基于桌面，他们都和数据库进行交互来完成数据的存取工作。假设现在有一种应用程序包含了这两 种，现在要修改其中的一个查询sql语句，那么我们可能要同时修改他们中对应的查询sql语句，当我们的应用程序很庞大很复杂的时候问题就出现这，不易维 护！另外把sql查询语句放在我们的web程序或桌面中很容易遭到sql注入的破坏。而存储例程正好可以帮我们解决这些问题。

2、创建存储过程

创建存储过程这块主要有两种，一种是带参数的，一种是不带参数的，先讲不带参数的再说不带参数的

不带参数案例：

-- 创建存储过程
delimiter //        --自定义语句结尾符号，因为这里要执行好多句sql语句，所以就得自定义，以防止出错 create procedure p1() BEGIN
    select * from tab1;
END// delimiter ;         --自定义局域结尾符号结束

-- 执行存储过程 call p1()

带参数案例这块主要有三个类

• in          仅用于传入参数用
• out        仅用于返回值用
• inout     既可以传入又可以当作返回值

-- 创建存储过程
delimiter \\ create procedure p1(
    in i1 int,                        -- 传入参数i1
    in i2 int,                        -- 传入参数i2
    inout i3 int,                     -- 即传入又能得到返回值
    out r1 int -- 得到返回值
) BEGIN
    DECLARE temp1 int;
    DECLARE temp2 int default 0;  
    set temp1 = 1;
    set r1 = i1 + i2 + temp1 + temp2; 
    set i3 = i3 + 100;
end\\
delimiter ;

-- 执行存储过程
DECLARE @t1 INT default 3;           --  设置变量默认值为3 DECLARE @t2 INT;                     --  设置变量 CALL p1 (1, 2 ,@t1, @t2);            --  执行存储过程，并传入参数，t2自动取消
SELECT @t1,@t2;                      --  查看存储过程输出结果

2、删除存储过程

drop procedure p1;

3、python用pymysql模块调用存储过程，因为我们学习这些就是为了语言调用

#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='', db='day39b_')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 执行存储过程
row = cursor.callproc('p1',(1,2,3))
# 存储过程的查询结果
selc = cursor.fetchall()
print(selc)
# 获取存储过程返回
effect_row = cursor.execute('select @_p1_0,@_p1_1,@_p1_2')
# 曲存储过程的返回值
ret = cursor.fetchone()
print(ret)
# 提交，不然无法保存新建或者修改的数据
conn.commit()
# 关闭游标
cursor.close()
# 关闭连接
conn.close()

函数function

在mysql中有很多内置函数，比如我们经常用的求平均值，求和，个数，各式各样，先给大家来一部门内置函数，然后再说说自定义函数吧，函数也可以传参数，也可以接收返回值，但是函数没办法得到执行语句得到的结果，存储过程可以。

1、自定义创建函数

delimiter \\ create function f1(
    i1 int,
    i2 int) returns int BEGIN
    declare num int;
    set num = i1 + i2; return(num);
END \\
delimiter ;

2、删除函数

drop function f1;

3、执行函数

# 获取返回值
declare @i VARCHAR;
select UPPER('alex') into @i;
SELECT @i;

# 在查询中使用
select f1(11,nid) ,name from tb2;

事务

事务用于将某些操作的多个SQL作为原子性操作，一旦有某一个出现错误，即可回滚到原来的状态，从而保证数据库数据完整性。例如：当两张银行卡之间进行转账，甲方钱转出去了，突然光缆坏了，乙方还没收到钱，钱跑哪里去了，就为了防止这种情况，事务就出来了，事务可以防止这种事情发生。

应用事务实例：

delimiter \\ create PROCEDURE p1(
    OUT p_return_code tinyint
) BEGIN 
  DECLARE exit handler for sqlexception 
  BEGIN 
    -- ERROR 
    set p_return_code = 1; 
    rollback; 
  END; 
 
  DECLARE exit handler for sqlwarning 
  BEGIN 
    -- WARNING 
    set p_return_code = 2; 
    rollback; 
  END; 
 
  START TRANSACTION; 
    DELETE from tb1;                   -- sql语句都放在这个里面 insert into tb2(name)values('seven');
  COMMIT; 
 
  -- SUCCESS 
  set p_return_code = 0; 
 
  END\\
delimiter ;

执行存储过程：

DECLARE @i TINYINT;
call p1(@i);
select @i;

触发器TRIGGER

触发器，简单来说就是当你在执行这条语句之前或者之后触发一次增删改查，触发器用于定制用户对表的行进行【增/删/改】前后的行为。

1、基本语法

# 插入前
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 插入后
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 删除前
CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 删除后
CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 更新前
CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 更新后
CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
BEGIN
    ...
END

事例一插入前：

-- 在往tab1插入数据之前往tab2中插入一条name = 张岩林，当然是在判断往tab1中插入的名字是不是等于aylin
delimiter // CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN

IF NEW. NAME == 'aylin' THEN
    INSERT INTO tb2 (NAME) VALUES ('张岩林') END
END// delimiter ;

事例二插入后：

delimiter // CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
    IF NEW. num = 666 THEN
        INSERT INTO tb2 (NAME) VALUES ('张岩林'), ('很帅') ;
    ELSEIF NEW. num = 555 THEN
        INSERT INTO tb2 (NAME) VALUES ('aylin'), ('非常帅') ;
    END IF;
END// delimiter ;

同样的删，改，查都是同样的道理

特别的：NEW表示即将插入的数据行，OLD表示即将删除的数据行。

2、删除触发器

DROP TRIGGER tri_after_insert_tb1;

3、使用触发器

触发器无法由用户直接调用，而知由于对表的【增/删/改】操作被动引发的。

insert into tb1(name) values(‘张岩林’)

彩蛋：

最后希望大家多多支持 一键三连+评论下 并献上我自己整理的“大厂真题+微服务+MySQL+分布式+SSM框架+Java+Redis+数据结构与算法+网络+Linux+Spring全家桶+JVM+高并发+各大学习思维脑图+面试集合”