DCL ：
一个项目创建一个用户，一个项目的对应的数据库只有一个
这个用户只能对这个数据库有权限,其他的数据库你操作不了
这些操作只有root 用户才有这个本事
1.创建用户:
create user '用户名'@'指定的ip地址' identified by '密码';
->用户只能在指定的ip地址登录
create user '用户名'@'%' identified by '密码'
->用户只能在任意的ip地址登录
2.给用户授权:
grant 权限1,权限2,......权限n on 数据库.数据库对象 to '用户名'@'ip地址'；
->权限 用户 数据库
->给用户分派在制定的数据库上的制定的权限
grant all on 数据库.* to '用户名'@'ip地址'；
给指定的用户在制定的数据数据库是上所有的权限
例如:
grant create,alter,drop,insert,update,delete,select on db3 to '用户名'@'ip地址'
授完权限刷新权限列表或重启服务器
flush privileges;(net stop mysql net start mysql)
3.回收权限:
revoke 权限1,权限2,......权限n on 数据库.* from '用户名'@'ip地址'；
4.查看用户权限:
show grants for '用户名'@'ip地址'
5.删除用户
drop user 用户名@ip地址
6.查看当前登录的用户
select user();
DDL:
查看数据库
show databases;
查看表
show tables;
查看表结构
desc 表名;
查看建表语句
show create table 表名；
创建表
create table if not exists 表名（
列名 数据类型,
列名 数据类型,
。。。。
列名 数据类型
）；
create table tb_stu(num char(11),name varchar(50),age int,gender varchar(10));
修改表:
1.添加列
alter table 表名 add (列名 数据类型,列名 数据类型)
2.修改列类型
alter table 表名 modify 列名1 列类型 ;
3.删除一列
alter table 表名 drop 列名；
4.修改表名称
alter table 表名 rename to 新的表名称
DML:
插入数据:
insert into 表名 （列名1 ，类名2......）values(列值1,列值2......);
修改数据:
update 表名 set 列名=列值,列名2=列值 where 条件(= != <> > >= < <= is null is not null in() between and
删除记录:
delete from 表名 where 条件
DQL:
=================员工表建表语句=====================
CREATE TABLE emp(
empno INT,
ename VARCHAR(50),
job VARCHAR(50),
mgr INT,
hiredate DATE,
sal DECIMAL(7,2),
comm decimal(7,2),
deptno INT
) ;
=================员工初始化语句=====================
INSERT INTO emp values(7839,'如来','董事长',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp values(7782,'玉帝','经理',7839,'1981-06-09',2450,NULL,10);
INSERT INTO emp values(7934,'王母','职员',7782,'1982-01-23',1300,NULL,10);
INSERT INTO emp values(7566,'唐僧','经理',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp values(7788,'孙悟空','分析师',7566,'1987-04-19',3000,NULL,20);
INSERT INTO emp values(7876,'猪八戒','文员',7788,'1987-05-23',1100,NULL,20);
INSERT INTO emp values(7902,'沙僧','分析师',7566,'1981-12-03',3000,NULL,20);
INSERT INTO emp values(7369,'白龙马','文员',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp values(7698,'刘备','经理',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp values(7499,'关羽','销售员',7698,'1981-02-20',1600,300,30);
INSERT INTO emp values(7521,'张飞','销售员',7698,'1981-02-22',1250,500,30);
INSERT INTO emp values(7654,'马超','销售员',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp values(7900,'赵云','职员',7698,'1981-12-03',950,NULL,30);
INSERT INTO emp values(7844,'黄忠','销售员',7698,'1981-09-08',1500,0,30);
======================部门表建表语句=======================
CREATE TABLE dept(
deptno INT,
dname varchar(14),
loc varchar(13)
);
=======================部门表初始化语句===================
INSERT INTO dept values(10, '财务部', '北京');
INSERT INTO dept values(20, '研发部', '上海');
INSERT INTO dept values(30, '销售部', '广州');
INSERT INTO dept values(40, '人事', '杭州');
一 字段(列) 控制
1.查询所有列 select * from 表名
2.查询制定列 select 列1 [,列2，列3，......列n] from 表名
3.完全重复的记录只显示一次 select distinct * |列1 [,列2，列3，......列n] from 表名
4.列运算
1>数量类型的列可以做加减乘除运算
select *,sal from emp;
select *,sal*1.5 from emp;
select *,ename*1.5 from emp;
2>字符串类型的可以做连接运算： concat('要连接的串1',字段1,'要连接的串2',字段2，.....)
select ename, concat('$',sal) from emp;
select concat('我叫',ename,',我的工作是',job) from emp;
3>转换空值(null)
select ename,sal+comm from emp;(任何数字和null 运算都得null)
select *,sal + ifnull(comm,0) from emp;
select ename .,ifnull(mgr,'boss') from emp;
4>别名 as 可以省略
select ename as 姓名,job as 工作 from emp;
select ename 姓名,job 工作 from emp;
二、条件控制
1.条件查询 where 子句
select * from emp where sal > 2000;
select * from emp where comm is not null;
select * from emp where sal between 2000 and 3000;
select * from emp where job in ('分析师','职员'）；
2.模糊查询like _:代表任意一个字符 %：任意0~n个字母
select * from emp where ename like '孙';
三、排序
1.升序 order by asc(asc可以声明省略,默认就是升序)
select * from emp order by sal asc;
2.降序 order by desc（desc不可以省略）
select * from emp order by sal desc;
3.多列排序
select * from emp order by sal asc;
select * from emp order by sal asc ,comm asc,empno desc;
四、聚合函数(纵向查询)
1.count --计算不为null的
select count(*) from emp;--一行上全部为null才算null
select count(comm) from emp;
select count(1) from emp;
select count(200) from emp;
2.sum--求和
select sum(sal) from emp;
select sum(comm) from emp;
select sum(ename) from emp;
3.max--最大
select max(sal) from emp
4.min --最小
select min(sal) from emp;
5.avg --平均
select avg(sal) from emp;
五、分组查询(排队,男的一排 女的一排,北京的一组，河北的一组，戴眼镜的一组，不带的分组)group by
分组查询不能查询个人信息,只能查询组信息
select job,count(*) from emp ;
select job,count(*) from emp group by job ;
select job,count(*),max(sal) from emp group by job ;
select deptno,count(*) from emp where sal > 1500 group by deptno;--每个部门工资大于1500的人数
select deptno,count(*) from emp where sal >1500 group by deptno having count(*) > 2
六、limit 方言
select * from emp limit 0,5;--从0行开始 查5行记录
分页:(当前页-1)*每页记录数
七、sql语句顺序
select from where group by having order by
-------------------------------------------------------------
子查询: 查询中有查询(查看select 关键字的个数)
位置
select
from 子查询位置
where 子查询位置
1.出现的位置
from 后面作为表存在(多行多列)--对结果集进行进一步查询
where 后面作为条件存在
2.条件
(***) 单行单列 : select * from 表名1 别名1 where 列1 [= > < >= <= !=] (select 列 from 表2 别名2 where 条件)
-- 本公司工资最高人的记录:SELECT * FROM emp WHERE sal = (SELECT MAX(sal) FROM emp)
-- 查找高于平均工资的所有人:SELECT * FROM emp WHERE sal >= ( SELECT AVG(sal) FROM emp ) ;
(**) 多行单列 : select * from 表1 别名1 where 列1 [in all any ] (select 列 from 表2 别名2 where 条件)
-- 查找工资大于30部门的所有工资 :SELECT * FROM emp WHERE sal > ALL( SELECT sal FROM emp WHERE deptno = 30 ) ;
-- 查找大于20部门的任意人的所有员工:SELECT sal FROM emp WHERE sal > ANY( SELECT sal FROM emp WHERE deptno = 20 ) ;
(*) 单行多列 : select * from 表1 别名1 where (列1 列2) in (select 列1,列2 from 表2 别名2 where 条件)
--查询工资和部门与张飞完全相同的员工:SELECT * FROM emp WHERE (sal,deptno) IN (SELECT sal,deptno FROM emp WHERE ename = '张飞');
(***) 多行多列 ：select * from 表1 别名1，(select ...) 别名2 where 条件
SELECT e.* FROM (SELECT * FROM emp WHERE deptno = 10) e
连接查询
内连接
外连接
左外连接
右外连接
全外连接(mysql不支持)
自然连接
select * from emp,dept;--笛卡尔积
内连接: 内连接查询出的所有记录都符合条件
方言：select * from 表名1 别名1 ,表名2 别名2 where 别名1.xx = 别名2.xx
标准 : select * from 表名1 别名1 inner join 表名2 别名2 on 别名1.xx = 别名2.xx
外连接
左外：select * from 表1 别名1 left outer join 表2 别名2 on 别名1.xx = 别名2.xx
--> 左表的记录无论是否满足条件都会查询出来,右边只有满足条件的才查询出来,左表
不满足条件的记录,右表部分都为null
左外自然:select * from 表1 别名1 natural left outer join 表2 别名2
右外:select * from 表1 别名1 right outer join 表2 别名2 on 别名1.xx = 别名2.xx
右表记录无论是否满足条件都会查询出来,而左表只有满足条件的才查询出来，右表不满足
条件的记录,其左表部分都为空
右外自然:select * from 表1 别名1 natural right outer join 表2 别名2
--------------------------------------------------------------------------
约束
约束是添加到列上的，用来约束列的
1.主键约束(唯一标识)
主键的特点:
非空
唯一
可被引用(外键引用主键)
当表的某一列被指定为主键后,该列就不能为空,并且不能有重复的值出现
创建表时指定主键的两种方式:
方式一:
create table stu(
sid char(6) primary key,--指定sid列为主键列,既sid 列添加主键约束
sname varchar(20),
age int,
gender varchar(10)
);
方式二:
create table stu(
sid char(6) primary key,
sname varchar(20),
age int,
gender varchar(10),
primary key(sid)--指定sid为主键列
);
修改主键:
alter table stu add primary key(sid);
删除主键:
alter table stu drop primary key;
-------------------------------------------------------
主键自增长:
因为主键的特性是：必须唯一，不能为空,所以我们通常会制定
主键的类型为整型，然后设置其自动增长，这样可以保证在插入
数据的时候主键的唯一和非空特性
方式一:
创建表的时候指定主键自增长
create table stu（
sid int primary key auto_increment,
sname varchar(20),
age int,
gender varchar(10)
）；
修改主键自增长:
alter table stu change sid sid int auto_increment;
删除主键自增长
alter table stu change sid sid int;
测试主键自增长:
insert into stu values(null,'zs',23,'m');
insert into stu(sname,age,gender) values (null,'zs',23,'m');
-----------------------------------------
非空约束；
因为某些列不能设置为空值，所以就可对这列添加非空约束
例如:
create table stu(
sid int primary key auto_increment,
sname varchar(20) not null,
age int,
gender varchar(10)
);
唯一约束:
数据库某些列不能设置重复的值，所以可以对其添加唯一约束
例如:
create table stu(
sid int primary key auto_increment,
sname varchar(20) not null unique,--对sname 设置了非空并且唯一约束
age int,
gender varchar(10)
);
-----------------------------------------------------
------------------------------------------------------事务---------------------------------------------------
1.事务(TRANSACTION)是作为单个逻辑工作单元执行的一系列操作,这些操作作为一个整体一起向系统提交，要么都执行、要么都不执行
事务是一个不可分割的工作逻辑单元
2.在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
3. 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
4. 事务用来管理 insert,update,delete 语句
5.事务是必须满足4个条件（ACID）： Atomicity（原子性）、Consistency（稳定性）、Isolation（隔离性）、Durability（可靠性）
原子性（Atomicity）：事务是一个完整的操作。事务的各步操作是不可分的（原子的）；要么都执行，要么都不执行
一致性（Consistency）：当事务完成时，数据必须处于一致状态
隔离性（Isolation）：对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，它不应以任何方式依赖于或影响 其他事务
永久性（Durability）：事务完成后，它对数据库的修改被永久保持，事务日志能够保持事务的永久性
6.查看事务是否自动提交 show variables like 'autocommit';
7.设置事务自动提交 set autocommit = 0(禁止自动提交)/1(自动提交)
8.开启事务 BEGIN或START TRANSACTION
9.提交事务 commit
10.回滚事务 rollback/ROLLBACK TO identifier 把事务回滚到标记点；
11.设置保存点 SAVEPOINT identifier
12.MYSQL 事务处理主要有两种方法：
1）、用 BEGIN, ROLLBACK, COMMIT来实现
BEGIN 开始一个事务
ROLLBACK 事务回滚
COMMIT事务确认
2)、直接用 SET 来改变 MySQL 的自动提交模式:
SET AUTOCOMMIT=0禁止自动提交
SET AUTOCOMMIT=1 开启自动提交
举例:
SET AUTOCOMMIT = 0; -- 禁止自动提交
BEGIN;
SAVEPOINT point1;
INSERT INTO person VALUES (NULL,'牛犇');
SAVEPOINT point2;
INSERT INTO person VALUES (NULL,'牛犇犇');
SAVEPOINT point3;
INSERT INTO person VALUES (NULL,'牛犇犇犇');
ROLLBACK TO point2;;
COMMIT;
------------------------------------------------------视图----------------------------------------------------
视图：是指计算机数据库中的视图，是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成
1.对于复杂的查询事件，每次查询都需要编写MySQL代码效率低下。为了解决这个问题，数据库提供了视图（view）功能。
2.视图是虚拟表，本身不存储数据，而是按照指定的方式进行查询。
3.视图只是一个查询语句,单表视图可以修改数据,跨表的视图不可以
创建视图:
语法:
CREATE OR REPLACE VIEW 视图名(列1，列2...)
AS SELECT (列1，列2...)
FROM ...;
修改视图:
语法:
alter view 视图名 as select 语句;
应用视图:
1）单表视图--增删改查
2）多表视图--只能查询
查看视图:
show tables;
删除视图
drop view 视图名称
查看视图结构
desc 视图名称
--------------------------------------------------------索引--------------------------------------------------
MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。
打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。
索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。
创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。
实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。
上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
建立索引会占用磁盘空间的索引文件。
普通索引 ：
创建索引
这是最基本的索引，它没有任何限制。它有以下几种创建方式：
CREATE INDEX indexName ON mytable(username(length));
如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。
修改表结构(添加索引)
ALTER table tableName ADD INDEX indexName(columnName)
创建表的时候直接指定
CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
INDEX [indexName] (username(length))
);
删除索引的语法
DROP INDEX [indexName] ON mytable;
CREATE INDEX i_p ON person(pname);
DROP INDEX i_p ON person;
ALTER TABLE person ADD INDEX i_p ON (pname);
ALTER TABLE person ADD INDEX i_p ON (pname(50))
CREATE TABLE person1(
pid INT ,
pname VARCHAR(30),
INDEX ind_p (pname)
);
DROP INDEX ind_p ON person1;
CREATE UNIQUE INDEX ind_p ON person1(pname) ;
--------------------------------------------------------------------------------
唯一索引
它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：
创建索引
CREATE UNIQUE INDEX indexName ON mytable(username(length))
修改表结构
ALTER table mytable ADD UNIQUE [indexName] (username(length))
创建表的时候直接指定
CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
UNIQUE [indexName] (username(length))
);
--------------------------------------------------------------------------------
使用ALTER 命令添加和删除索引
有四种方式来添加数据表的索引：
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索引。
以下实例为在表中添加索引。
mysql> ALTER TABLE testalter_tbl ADD INDEX (c);
你还可以在 ALTER 命令中使用 DROP 子句来删除索引。尝试以下实例删除索引:
mysql> ALTER TABLE testalter_tbl DROP INDEX c;
--------------------------------------------------------------------------------
使用 ALTER 命令添加和删除主键
主键只能作用于一个列上，添加主键索引时，你需要确保该主键默认不为空（NOT NULL）。实例如下：
mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;
mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);
你也可以使用 ALTER 命令删除主键：
mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;
删除主键时只需指定PRIMARY KEY，但在删除索引时，你必须知道索引名。
--------------------------------------------------------------------------------
显示索引信息
使用 SHOW INDEX 命令来列出表中的相关的索引信息。可以通过添加 G 来格式化输出信息。
尝试以下实例:
mysql> SHOW INDEX FROM table_name; G
mysql数据库引擎：
- innodb 支持事务,有行级锁，
适合更新较多场景
- myisam 不支持事务，只有表级锁，
适合更新较少，查询较多场景适合更新较少，查询较多场景
........
~~~~~~~~~~~~~~~~~~~~~~~~变量~~~~~~~~~~~~~~~~~~~~~~~~
一、局部变量
1.局部变量一般用在sql语句块中，比如存储过程的begin/end。其作用域仅限于该语句块，在该语句块执行完毕后，局部变量就消失了
例如:下面存储过程中定义的变量c就是局部变量
--删除存储过程
DROP PROCEDURE IF EXISTS proc_1;
-- 定义存储过程
DELIMITER //
CREATE PROCEDURE proc_1(IN a INT,IN b INT)
BEGIN
DECLARE c INT DEFAULT 5;
SET c = a + b;
SELECT c;
END//
DELIMITER ;
-- 调用存储过程
CALL proc_1(3,5);
二、用户变量。
1.用户变量的作用域要比局部变量要广。用户变量可以作用于当前整个连接，但是当当前连接断开后，其所定义的用户变量都会消失。
2.用户变量使用如下的方式定义：@变量名
3.对用户变量赋值有两种方式，一种是直接用"="号，另一种是用":="号。其区别在于使用set命令对用户变量进行赋值时，两种方式都可以使用；当使用select语句对用户变量进行赋值时，只能使用":="方式，因为在select语句中，"="号被看作是比较操作符。
区别:
set 用于普通的赋值
select用于从表中查询数据并赋值,必须保证筛选的记录只有1条，否则取最后一条，可演示证明
例如1: 下面的例子中@result 就是用户变量
DROP PROCEDURE IF EXISTS mymath;
DELIMITER //
CREATE PROCEDURE mymath(IN a INT,IN b INT)
BEGIN
SET @result = 0;
SELECT @result := a + b;
END//
DELIMITER ;
CALL mymath(3,5);
例如2:
SET @mysum = (SELECT COUNT(*) FROM emp);
SELECT @mysum_1 := (SELECT COUNT(*) FROM emp);
三、系统变量 mysql一启动的时候就会读取系统变量(这样做目的是可以确定mysql的以何种机制或模式运行)，
MySQL用 "set @@系统变量"的形式去改变系统变量的值,但是不涉及定义系统变量(也就是说系统的变量只能读取或改变不能定义)
1.查看系统变量
show variables ;
例如:SHOW VARIABLES LIKE 'char%'; 查看系统的字符集
SHOW VARIABLES LIKE 'auto%'; 查看是否自动提交
2.设置系统变量
例如:
Set @@character_set_client=gbk;
set @@autocommit = 0;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~存储过程~~~~~~~~~~~~~~~~~~~~~~~~
1.简介:(MySQL在5.0之后支持存储过程)我们常用的操作数据库语言SQL语句在执行的时候需要要先编译，然后执行，而存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。
2.存储过程可以包含数据操纵语句、变量、逻辑 控制语句等
3.存储过程通常有以下优点：
(1).存储过程增强了SQL语言的功能和灵活性。存储过程可以用流控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。
(2).存储过程允许标准组件是编程。存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句。而且数据库专业 人员可以随时对存储过程进行修改，对应用程序源代码毫无影响。
(3).存储过程能实现较快的执行速度。如果某一操作包含大量的Transaction-SQL代码或分别被多次执行，那么存储过程要比批处理的执行速 度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询，优化器对其进行分析优化，并且给出最终被存储在系统表中的执行计 划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化，速度相对要慢一些。
(4).存储过程能过减少网络流量。针对同一个数据库对象的操作（如查询、修改），如果这一操作所涉及的Transaction-SQL语句被组织程存 储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而大大增加了网络流量并降低了网络负载。
(5).存储过程可被作为一种安全机制来充分利用。系统管理员通过执行某一存储过程的权限进行限制，能够实现对相应的数据的访问权限的限制 ，避免了非授权用户对数据的访问，保证了数据的安全。
4.格式
CREATE PROCEDURE 存储过程名 ([过程参数[,...]])
[特性 ...] 过程体
例如:
-- 定义用户变量
SET @s = 0;
-- 创建存储过程
DELIMITER //
CREATE PROCEDURE proc_2(OUT s INT)
BEGIN
SELECT COUNT(*) INTO s FROM emp;
END
//
DELIMITER ;
-- 调用存储过
CALL proc_2(@s);
-- 答应结果
SELECT @s;
5、DELIMITER:
DELIMITER //和DELIMITER ;两句，DELIMITER是分割符的意思，因为MySQL默认以";"为分隔符，如果我们没有声明分割符，那么编译器会把存储过程当成SQL语句进行处理，则存储过程的编译过程会报错，所以要事先用DELIMITER关键字申明当前段分隔符，这样MySQL才会将";"当做存储过程中的代码，不会执行这些代码，用完了之后要把分隔符还原。
6.过程参数:
1）IN 输入参数:表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值
-- 定义用户变量
SET @p_in = 2;
-- 创建存储过程
DELIMITER //
CREATE PROCEDURE proc_3(IN p INT)
BEGIN
SET p = 999;
END
//
DELIMITER ;
-- 调用存储过程
CALL proc_3(@p_in);
-- 打印结果
SELECT @p_in;
2）OUT 输出参数:该值可在存储过程内部被改变，并可返回
例如:
-- 设置用户变量
SET @p_out=1;
-- 创建储存过程
DELIMITER //
CREATE PROCEDURE proc_3(OUT p_out INT)
BEGIN
SELECT p_out;
SET p_out=2;
SELECT p_out;
END;
//
DELIMITER ;
-- 调用存储过程
CALL proc_3(@p_out);
-- 打印结果
SELECT @p_out;
INOUT 输入输出参数:调用时指定，并且可被改变和返回
-- 设置用户变量
SET @p_in_out=1;
-- 创建储存过程
DELIMITER //
CREATE PROCEDURE proc_4(INOUT p_in_out INT)
BEGIN
SELECT p_in_out;
SET p_in_out=2;
SELECT p_in_out;
END;
//
DELIMITER ;
-- 调用存储过程
CALL proc_4(@p_in_out);
-- 打印结果
SELECT @p_in_out;
7.begin -- end :
过程体的开始与结束使用BEGIN与END进行标识。
8.定义变量:
DECLARE variable_name [,variable_name...] datatype [DEFAULT value];
例如:
DECLARE l_int int unsigned default 4000000;
DECLARE l_numeric number(8,2) DEFAULT 9.95;
DECLARE l_date date DEFAULT '1999-12-31';
DECLARE l_datetime datetime DEFAULT '1999-12-31 23:59:59';
DECLARE l_varchar varchar(255) DEFAULT 'This will not be padded';
9.局部变量赋值
SET 变量名 = 值
例如:
SELECT 'Hello World' INTO @X;
SELECT @X;
或者
SET @Y='Goodbye Cruel World';
SELECT @Y;
10.调用:call 存储过程名(参数列表)
用call和过程名以及一个括号，括号里面根据需要，加入参数，参数包括输入参数、输出参数、输入输出参数。
11.查看所有存储过程(root用户)
-- root 用户
SELECT * FROM mysql.proc WHERE db='bwdb';
12.删除存储过程:DROP PROCEDURE 存储过程名;
13.条件语句 --if-then -else
DELIMITER //
CREATE PROCEDURE proc_getGrade_1(IN stu_grade INT)
BEGIN
IF stu_grade>=90 THEN
SELECT 'A';
ELSEIF stu_grade<90 AND stu_grade>=80 THEN
SELECT 'B';
ELSEIF stu_grade<80 AND stu_grade>=70 THEN
SELECT 'C';
ELSEIF stu_grade70 AND stu_grade>=60 THEN
SELECT 'D';
ELSE
SELECT 'E';
END IF;
END;
//
DELIMITER ;
CALL proc_getGrade(91);
14、循环语句 -- while
DELIMITER //
CREATE PROCEDURE proc_6()
BEGIN
DECLARE var INT;
SET var=0;
WHILE var<6 DO
INSERT INTO t1 VALUES(NULL,var);
SET var=var+1;
END WHILE;
END;
//
DELIMITER ;
CALL proc_6();
~~~~~~~~~~~~~~~~~~~~~~~~~触发器~~~~~~~~~~~~~~~~~~~~~~~
1.触发器(trigger)：监视某种情况，并触发某种操作。
2.特点
1)是个特殊的存储过程
2)它的执行不是由程序调用，也不是手工启动，而是由事件来触发。 insert，delete， update）
3)触发器经常用于加强数据的完整性约束和业务规则等。
3.为什么要使用触发器?
1)可以使用它来检查或预防坏的数据进入数据库。
2)可以改变或取消INSERT、UPDATE、以及DELETE语句。
3)可以在一个会话中监视数据改变的动作。
4.触发器创建语法四要素：1.监视地点(table) 2.监视事件(insert/update/delete) 3.触发时间(after/before) 4.触发事件(insert/update/delete)
5.语法:
create trigger 触发器名称
after/before insert/update/delete
on 表名
for each row #这句话在mysql是固定的
begin
sql语句;
end;
6.例子
#商品表
create table g
(
id int primary key auto_increment,
name varchar(20),
num int
);
#订单表
create table o
(
oid int primary key auto_increment,
gid int,
much int
);
insert into g(name,num) values('商品1',10),('商品2',10),('商品3',10);
如果我们在没使用触发器之前：假设我们现在卖了3个商品1，我们需要做两件事
1.往订单表插入一条记录
insert into o(gid,much) values(1,3);
2.更新商品表商品1的剩余数量
update g set num=num-3 where id=1;
现在，我们来创建一个触发器：
需要先执行该语句：delimiter $(意思是告诉mysql语句的结尾换成以$结束)
create trigger tg1
after insert on o
for each row
begin
update g set num=num-3 where id=1;
end$
这时候我们只要执行：
insert into o(gid,much) values(1,3)$
会发现商品1的数量变为7了，说明在我们插入一条订单的时候，触发器自动帮我们做了更新操作。
但现在会有一个问题，因为我们触发器里面num和id都是写死的，所以不管我们买哪个商品，最终更新的都是商品1的数量。
比如：我们往订单表再插入一条记录：insert into o(gid,much) values(2,3),执行完后会发现商品1的数量变4了，
而商品2的数量没变，这样显然不是我们想要的结果。我们需要改改我们之前创建的触发器。
我们如何在触发器引用行的值，也就是说我们要得到我们新插入的订单记录中的gid或much的值。
对于insert而言，新插入的行用new来表示，行中的每一列的值用new.列名来表示。
所以现在我们可以这样来改我们的触发器
create trigger tg2
after insert on o
for each row
begin
update g set num=num-new.much where id=new.gid;(注意此处和第一个触发器的不同)
end$
第二个触发器创建完毕，我们先把第一个触发器删掉
drop trigger tg1$
再来测试一下，插入一条订单记录：insert into o(gid,much) values(2,3)$
执行完发现商品2的数量变为7了，现在就对了。
现在还存在两种情况：
1.当用户撤销一个订单的时候，我们这边直接删除一个订单，我们是不是需要把对应的商品数量再加回去呢？
2.当用户修改一个订单的数量时，我们触发器修改怎么写?
我们先分析一下第一种情况：
监视地点：o表
监视事件：delete
触发时间：after
触发事件：update
对于delete而言：原本有一行,后来被删除，想引用被删除的这一行，用old来表示，old.列名可以引用被删除的行的值。
那我们的触发器就该这样写：
create trigger tg3
after delete on o
for each row
begin
update g set num = num + old.much where id = old.gid;(注意这边的变化)
end$
创建完毕。
再执行delete from o where oid = 2$
会发现商品2的数量又变为10了。
第二种情况：
监视地点：o表
监视事件：update
触发时间：after
触发事件：update
对于update而言：被修改的行，修改前的数据，用old来表示，old.列名引用被修改之前行中的值；
修改的后的数据，用new来表示，new.列名引用被修改之后行中的值。
那我们的触发器就该这样写：
create trigger tg4
after update on o
for each row
begin
update g set num = num+old.much-new.much where id = old/new.gid;
end$
先把旧的数量恢复再减去新的数量就是修改后的数量了。
我们来测试下：先把商品表和订单表的数据都清掉，易于测试。
假设我们往商品表插入三个商品，数量都是10，
买3个商品1：insert into o(gid,much) values(1,3)$
这时候商品1的数量变为7；
我们再修改插入的订单记录: update o set much = 5 where oid = 1$
我们变为买5个商品1，这时候再查询商品表就会发现商品1的数量只剩5了，说明我们的触发器发挥作用了。
----------------------------数据库的备份和恢复---------------------------------------------------
数据的恢复和备份: 数据库 --> 脚本文件(备份) 脚本文件-->数据库(恢复)
1.数据库导成sql脚本
mysqldump -u用户名 -p密码 数据库名 > 生成的文价脚本路径
例如:mysqldump -uroot -p123 bwdb >c:wdb.sql
注意:mysqldump 是一个可执行文件
1.不要打分好，不要登录mysql,直接在cmd下运行
2.生成的脚本文件不包含create database语句
2.执行脚本文件
方式一:
mysql -u用户名 -p密码 数据库<脚本文件路径
例如:先删除bwdb 库 再重新创建bwdb
mysql -uroot -p123 bwdb<c:/bwdb.sql
注意:不打分号，不要登录mysql,直接在cmd下运行
方式二:
登录mysql
用source 命令 执行脚本文件
例如:
先删除bwdb库 在重新创建bwdb库
切换到bwdb库
source c：wdb.sql
----------------------------------------------------------------------------------------------