1.约束表的建立和插入

　　数据类型告诉我们需要存储什么样子的数据，而约束告诉我们这些数据具体需要满足的规则。如：age int, 我们可以存储负整数，price float，我们可以存储负小数，但是负数是不满足实际规则的，因此我们需要约束。

　　常见约束：

　　非空约束：not null

　　主键约束：primary key唯一约束：unique

　　外键约束：foreign key

　　1.1非空约束：

　　1.定义：

　　在建立表的字段时，在后面带上not null就可，正如字面意思，代表当插入数据时该字段必须有值：

　　2.演示建立：

　　#创建一个名为goods的表，有两个字段商品名和商品价格：

　　create table if not exists goods(

　　goods_name varchar(20) not null comment '商品名',#设置不能为空

　　goods_price float not null comment '商品价格'

　　);

　　3.演示操作：

　　##插入：

　　insert into goods values('饼干1','2.3');

　　insert into goods(goods_price) values('23.0')##不能执行

　　1.2唯一约束：

　　1.定义：

　　在建立表的字段时，在后面带上unique就可，正如字面意思，代表当插入数据时该字段不能有重复值

　　2.演示建立：

　　#创建一个名为goods的表，有两个字段商品名和商品价格：

　　drop table goods;

　　create table goods(

　　goods_id int not null unique comment '商品编号',#设置为非空，唯一

　　goods_name varchar(20) not null comment '商品名',#设置不能为空

　　goods_price float not null comment '商品价格'

　　);

　　3.演示操作：

　　##插入：

　　insert into goods values(001,'饼干1','2.3');

　　insert into goods values(001,'饼干2',2.5);##报错 Error Code: 1062. Duplicate entry '1' for key 'goods_id' 0.00022 sec

　　insert into goods values(002,'饼干2',2.5);

　　1.3外键约束：

　　1.定义：

　　在建立表的时候，在后面带上 foreign key(本表字段) references goods其他表(其他表字段)就可;外建约束是比较有用的，在建立多对一，多对多，一对一，继承等关系的数据库时都要用到。

　　作用：使一个表参考另一个表的字段，使两表建立关系。比如：商品表中有id号，在商品订单表中将商品id作为外建，这样就可以通过查找订单表中的商品id，从而通过连接查询查询到商品的详细信息;

　　2.演示建立：

　　#MySQL创建关联表可以理解为是两个表之间有个外键关系，但这两个表必须满足三个条件

　　#1.两个表必须是InnoDB数据引擎

　　#2.使用在外键关系的域必须为索引型(Index)

　　#3.使用在外键关系的域两者数据类型需要相似

　　#创建关联表

　　#创建商品表:

　　create table if not exists goods(#判断表是否存在进行创建

　　goods_id int not null auto_increment primary key comment '商品编号',#设置为主键，自动增长

　　goods_name varchar(20) not null comment '商品名',#设置不能为空

　　gooods_price float not null comment '商品价格',

　　goods_maker varchar(20) default null comment '生产商',#morning为空

　　index(goods_id)#建立外键关系的域必须为索引类型

　　)engine=innodb character set utf8 collate utf8_general_ci auto_increment=1;#设置引擎以及字符集

　　#创建订单表

　　create table if not exists detil(

　　customer_id int not null primary key comment '客户id',#设置为主键

　　goods int not null comment '商品id',

　　count int not null comment '数量',

　　index(goods),#经测试此去的索引可以省去。

　　foreign key(goods) references goods(goods_id) on delete cascade on update cascade#建立外键，使用goods(goods_id)作为外键。

　　#on delete(update) cascade 意思为当goods表有相关记录删除(修改)时，detil想要的记录也会被删去(修改)。

　　)engine=innodb character set utf8 collate utf8_general_ci;#设置引擎以及字符集

　　3.演示操作：

　　#1.插入演示：

　　#向goods表添加数据

　　insert into goods values(null,'饼干1','2.3','三无产品');

　　insert into goods values(null,'饼干2','2.3','三无产品');

　　insert into goods values(null,'饼干3','2.3','三无产品');

　　insert into goods values(null,'饼干4','2.3','三无产品');

　　insert into goods values(null,'饼干5','2.3','三无产品');

　　insert into goods values(null,'饼干6','2.3','三无产品');

　　insert into goods values(null,'饼干7','2.3','三无产品');

　　insert into goods values(null,'饼干8','2.3','三无产品');

　　#向detil表添加数据

　　#必须在goods中有goods_id的编号，能正确插入detil;

　　insert into detil values(001,1,2);

　　insert into detil values(002,2,2);

　　insert into detil values(003,3,2);

　　insert into detil values(004,4,2);

　　insert into detil values(005,1,2);

　　insert into detil values(006,1,2);

　　insert into detil values(007,1,2);

　　insert into detil values(008,1,2);

　　#insert into detil values(008,30,2);#30外键不存在会报错。

　　#2.删除演示：

　　##删除goods中的一行，detil对应的外键等于2的列也会被删去。

　　delete from goods where goods_id=2;

　　#3.查询演示：从订单表中查询对应的商品;

　　select * from goods where goods_id=(select goods from detil where customer_id=001)

　　#4.删除外键约束：

　　alter table tableName drop foreign key 外键名;

　　4.一个例子：

　　-- 解决数据冗余高的问题：给冗余的字段放到一张独立表中

　　-- 独立设计一张部门表

　　CREATE TABLE dept(

　　id INT PRIMARY KEY,

　　deptName VARCHAR(20)

　　);

　　-- 添加员工表

　　CREATE TABLE employee(

　　id INT PRIMARY KEY,

　　empName VARCHAR(20),

　　deptId INT,-- 把部门名称改为部门ID

　　-- 声明一个外键约束

　　CONSTRAINT emlyee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id)

　　ON UPDATE CASCADE ON DELETE CASCADE -- ON CASCADE UPDATE ：级联修改

　　-- 外键名称 外键 参考表(参考字段)

　　);

　　INSERT INTO dept(id,deptName) VALUES(1,'软件开发部');

　　INSERT INTO dept(id,deptName) VALUES(2,'应用维护部');

　　INSERT INTO dept(id,deptName) VALUES(3,'秘书部');

　　INSERT INTO employee VALUES(1,'张三',1);

　　INSERT INTO employee VALUES(2,'李四',1);

　　INSERT INTO employee VALUES(3,'王五',2);

　　INSERT INTO employee VALUES(4,'陈六',3);

　　-- 1)当有了外键约束，添加数据的顺序： 先添加主表，再添加副表数据

　　-- 2)当有了外键约束，修改数据的顺序： 先修改副表，再修改主表数据

　　-- 3)当有了外键约束，删除数据的顺序： 先删除副表，再删除主表数据

　　-- ON CASCADE UPDATE ：级联修改

　　UPDATE dept SET id=4 WHERE id=3;

　　1.4主键约束：

　　1.定义：

　　在建立表的字段时，在后面带上primary key就可;主键约束：不允许为空，不允许重复，主键一般自增长一起使用：auto_increment

　　2.演示建立：

　　#创建一个名为goods的表，有两个字段商品名和商品价格：

　　drop table goods;

　　create table if not exists goods(#判断表是否存在进行创建

　　goods_id int not null auto_increment primary key comment '商品编号',#设置为主键，自动增长

　　goods_name varchar(20) not null comment '商品名',#设置不能为空

　　goods_price float not null comment '商品价格'

　　);

　　3.演示操作：

　　##插入：

　　insert into goods values(001,'饼干1','2.3');#标号001，没有用，生成的标号为1;标号按1增长

　　insert into goods values(null,'饼干2','2.3');#可以直接这样插入

　　insert into goods values('饼干3','2.3');#这样报错，提示值不匹配

　　insert into goods(goods_name,goods_price) values('饼干3','2.3');##这样可以正常插入

　　##删除主键：

　　alter table tablename drop primary key ;

　　2.存储过程：这里只进行简单介绍

　　存储过程是一种存储在书库库中的程序(就像正规语言里的子程序一样),准确的来说,MySQL 支持的“ routines (例程)”有两种:一是我们说的存储过程, 二是在其他 SQL 语句中可以返回值的函数(使用起来和 Mysql 预装载的函数一样,如 pi() )。

　　2.1存储过程入门：

　　出现的缘由：

　　存储过程是可复用的组件

　　存储过程将被保存

　　存储过程可以 移植

　　存储过程会使系统运行更快

　　语法：

　　DELIMITER $ /*定义分隔符*/

　　CREATE PROCEDURE procedure1/* name 存储过程名 */

　　(IN parameter1 INTEGER) /* parameters 参数 */

　　BEGIN /* start of block 语句块头 */

　　DECLARE variable1 CHAR(10); /* variables 变量声明 */

　　IF parameter1 = 17 THEN /* start of IF IF 条件开始 */

　　SET variable1 = 'birds'; /* assignment 赋值 */

　　ELSE

　　SET variable1 = 'beasts'; /* assignment 赋值 */

　　END IF; /* end of IF IF 结束 */

　　INSERT INTO table1 VALUES (variable1); /* statement SQL 语句 */

　　END $ /* end of block 语句块结束 */

　　简单实例说明：

　　#1.存储过程(方法建立)--

　　-- 创建存储过程--

　　DELIMITER $ #-- 声明结束符为$

　　create procedure pro_test()

　　begin

　　SELECT * FROM employee;

　　INSERT INTO employee(id,deptId) VALUES(5,1);

　　END $

　　````

　　4.存储过程的操作：

　　````

　　-- 执行存储过程--

　　CALL pro_test();

　　drop procedure pro_test;#删除存储过程

　　2.2创建带输入参数的函数(存储过程) ：

　　前面我们建立的函数，参数列表是空的。Mysql数据库的存储过程是可以带参数的，包括输入输出参数，输入参数的声明方式：

　　CREATE PROCEDURE p1

　　([IN] name data-type) ...

　　1.简单演示创建：

　　-- 创建带输入参数的函数

　　delimiter $

　　create procedure pro_findById(in eid int) -- in:输入参数 类型为int

　　begin

　　select * from employee where id=eid;

　　end $

　　2.调用：

　　-- 调用带输入参数的方法--

　　call pro_findById(4);

　　2.3创建带输出参数的函数(存储过程) ：

　　同样可以代输出参数，相当于创建了变量，使得在函数结束后，可以获得变量的值;输出参数的声明方式：

　　CREATE PROCEDURE p2

　　([out] name data-type) ...

　　1.简单演示创建：

　　-- 创建带有输出参数的函数

　　delimiter $

　　create procedure pro_testout(out str varchar(20), out sid int)-- 定义两个输出参数，str存储员工名字，id存储最小的员工编号

　　begin

　　select min(id) into sid from employee;-- 使用into，给输出变量赋值

　　select empName from employee where id=3 into str;

　　end $

　　2.带输出参数的函数调用：

　　-- 带输出参数的函数调用

　　-- 变量用@name表示

　　call pro_testout(@str,@sid);

　　select @str,@sid;#使用输出变量

　　2.4变量介绍：

　　全局变量(内置变量)：

　　mysql数据库内置的变量 (所有连接都起作用)

　　查看所有全局变量： show variables

　　如： character_set_client: mysql服务器的接收数据的编码

　　character_set_results：mysql服务器输出数据的编码

　　查看某个全局变量：

　　select @@变量名

　　select @@character_set_client;输出：utf8

　　修改全局变量：

　　set 变量名=新值

　　会话变量： 输出参数属于会话变量

　　只存在于当前客户端与数据库服务器端的一次连接当中。如果连接断开，那么会话变量全部丢失!

　　定义会话变量:

　　set @变量=值

　　查看会话变量：

　　select @变量

　　局部变量：

　　在存储过程中使用的变量就叫局部变量。只要存储过程执行完毕，局部变量就丢失!!

　　局部变量的定义：

　　DECLARE 变量名 INT DEFAULT 1;#默认值为1

　　DECLARE i INT DEFAULT 1;

　　查看局部变量：

　　select 变量

　　select i;

　　2.5带有输入输出参数的存储过程：

　　参数既是输入参数，也是输出参数，输出参数的声明方式：

　　CREATE PROCEDURE p2

　　([INTOUT] name data-type) ...

　　1.简单演示创建：

　　DELIMITER $

　　CREATE PROCEDURE pro_testInOut(INOUT n INT) -- INOUT： 输入输出参数

　　BEGIN

　　-- 查看变量

　　SELECT n;

　　SET n =500;-- 修改变量

　　END $

　　2.简单调用：

　　-- 调用

　　SET @n=10;

　　CALL pro_testInOut(@n);

　　SELECT @n;#显示修改后的值

　　2.6,带条件循环的存储过程：

　　Mysql的存储过程中可以使用if，while，case等语句：

　　带if的存储过程：

　　-- 带有条件判断的存储过程

　　-- 需求：输入一个整数，如果1，则返回“星期一”,如果2，返回“星期二”,如果3，返回“星期三”。其他数字，返回“错误输入”;

　　DELIMITER $

　　CREATE PROCEDURE pro_testIf(IN num INT,OUT str VARCHAR(20))

　　BEGIN

　　IF num=1 THEN

　　SET str='星期一';

　　ELSEIF num=2 THEN

　　SET str='星期二';

　　ELSEIF num=3 THEN

　　SET str='星期三';

　　ELSE

　　SET str='输入错误';

　　END IF;

　　END $

　　CALL pro_testIf(4,@str);

　　SELECT @str;

　　drop procedure pro_testIf;

　　2.带有while的存储过程

　　-- 带有循环功能的存储过程

　　-- 需求： 输入一个整数，求和。例如，输入100，统计1-100的和

　　DELIMITER $

　　CREATE PROCEDURE pro_testWhile(IN num INT,OUT result INT)

　　BEGIN

　　-- 定义局部变量

　　DECLARE i INT DEFAULT 1;

　　DECLARE vsum INT DEFAULT 0;

　　WHILE i<=num DO

　　SET vsum = vsum+i;

　　SET i=i+1;

　　END WHILE;

　　SET result=vsum;

　　END $

　　#调用

　　CALL pro_testWhile(100,@result);

　　drop procedure pro_testWhile;

　　SELECT @result;

　　3.带有case的存储过程：

　　CREATE PROCEDURE p13 (IN parameter1 INT)

　　BEGIN

　　DECLARE variable1 INT;

　　SET variable1 = parameter1 + 1;

　　CASE variable1

　　WHEN 0 THEN INSERT INTO t VALUES (17);

　　WHEN 1 THEN INSERT INTO t VALUES (18);

　　ELSE INSERT INTO t VALUES (19);

　　END CASE;

　　END; $

　　3.其他介绍：

　　3.1触发器

　　-- 当进行 update,insert,delete的前后触发一个事件;

　　-- 创建日志表

　　create table test_log(

　　id int primary key auto_increment,

　　content varchar(20)

　　);

　　-- 需求： 当向员工表插入一条记录时，希望mysql自动同时往日志表插入数据

　　-- 1.创建触发器(添加)After

　　create trigger tri_empAdd After insert on employee for each row

　　insert into test_log(content) values('员工插入了一条记录');

　　-- 插入数据;

　　insert into employee values(7,'peace3',1,3);

　　##显示触发的数据：

　　select * from test_log;

　　-- 2.创建触发器(修改) before

　　CREATE TRIGGER tri_empUpd before UPDATE ON employee FOR EACH ROW -- 当往员工表修改一条记录时

　　INSERT INTO test_log(content) VALUES('员工表修改了一条记录');

　　-- 修改

　　UPDATE employee SET empName='eric' WHERE id=7;

　　##显示触发的数据：

　　select * from test_log;

　　-- 3.创建触发器(删除)before

　　CREATE TRIGGER tri_empDel before DELETE ON employee FOR EACH ROW -- 当往员工表删除一条记录时

　　INSERT INTO test_log(content) VALUES('员工表删除了一条记录');

　　-- 删除

　　DELETE FROM employee WHERE id=7;

　　##显示触发的数据：

　　SELECT * FROM employee;

　　SELECT * FROM test_log;

　　3.2mysql权限问题

　　-- ***********mysql权限问题****************

　　-- mysql数据库权限问题：root ：拥有所有权限(可以干任何事情)

　　-- 权限账户，只拥有部分权限(peace)例如，只能操作某个数据库的某张表

　　-- 如何修改mysql的用户密码?

　　-- password: md5加密函数(单向加密)

　　SELECT PASSWORD('root'); -- *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B

　　-- mysql数据库，用户配置 : user表

　　USE mysql;

　　show tables;

　　-- 查看所有使用者

　　select user from user;

　　-- 创建用户账号

　　create user peace identified by '1234';

　　-- 授权 select,insert,其他一样--

　　grant select,insert on day01.student to peace;

　　-- 授权所有：grant all on *.* to peace;

　　-- 设置与更改用户名--

　　set password for peace = password('123456');

　　-- 展示权限--

　　show grants for peace;

　　-- 撤销用户权限--

　　-- 命令：

　　revoke insert on day01.student from peace;

　　-- 删除用户--

　　drop user peace;