my sql(二)

liuming794

　　1.数据约束
　　a.默认值约束
　　默认值(default)
　　CREATE TABLE test(
　　NAME VARCHAR(20),
　　gender VARCHAR(2) DEFAULT '男'
　　)
　　注：当前没有插入默认值字段的时候，默认值才会起作用
　　b.非空约束
　　非空（not null）
　　CREATE TABLE test(
　　NAME VARCHAR(20) NOT NULL,
　　gender VARCHAR(2)
　　)
　　注：不能不插入值，不能插入null
　　c.唯一约束
　　唯一（unique）
　　CREATE TABLE test(

　　>　　NAMEVARCHAR(20)
　　)
　　注：不能插入重复的值，唯一约束不能约束null（可以插入多个null）
　　d.主键约束
　　唯一+非空（primary key）
　　CREATE TABLE test(

　　>　　NAMEVARCHAR(20)
　　)
　　e.自增长约束
　　CREATE TABLE test(

　　>　　NAMEVARCHAR(20)
　　)
　　注：初始值为0，每次递增1
　　f.外键约束
　　外键作用：约束两种表的数据
　　-- 部门表
　　CREATE TABLE dept(

　　>　　NAMEVARCHAR(20)
　　)
　　-- 雇员表
　　CREATE TABLE employee(

　　>　　NAMEVARCHAR(20),
　　deptIdINT,
　　CONSTRAINT employee_dept_fk FOREIGN KEY(deptId) REFERENCESdept(id)
　　--                       外键名称                                         外键字段  参考
　　)
　　注：有了外键约束之后，当往副表中插入，修改主表不存在的数据时外键就会起作用，当删除与副表  有关的主表数据时，外键起作用。当插入和修改数据时，应先向主表中添加；当删除数据时应该先删除    副表中的数据。
　　2.级联
　　介绍：当有了外键的时候，我们希望修改或删除数据的时候，修改或删除了主表的数据，同时能够影响   副表的数据，这时就可以使用级联。
　　CREATE TABLE employee(

　　>　　NAMEVARCHAR(20),
　　deptIdINT,
　　--添加级联修改： ON UPDATE CASCADE
　　--添加级联删除： ON DELETE CASCADE
　　CONSTRAINTemployee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id) ON UPDATE       CASCADE ON DELETE CASCADE
　　--                             外键名称                     外键字段  参考
　　)
　　3.数据库设计的三大范式
　　第一范式：要求表的每个字段必须独立的不可分割的单元。
　　第二范式：在第一范式的基础上，要求表的除主键以外的字段都和主键有依赖关系的。
　　第三范式：在第二范式基础上，要求表的除主键外的字段都只能和主键有直接决定的依赖关系。
　　4.多表查询
　　a.交叉连接查询（笛卡尔乘积: 4*3=12，产生笛卡尔积的原因是没有足够的连接条件）
　　SELECT employee.name,dept.name FROMemployee,dept;
　　b.内连接查询
　　效果：只有满足连接条件的数据才会显示出来
　　SELECT e.name,d.name
　　FROMemployee e,dept d
　　WHEREe.deptId=d.id;
　　-- 另一种表达
　　SELECT e.name,d.name
　　FROMemployee e
　　INNERJOIN dept d
　　ON e.deptId=d.id;
　　c.左外连接查询
　　介绍： 右表（部门表）的数据全部显示，左表（员工）的数据当满足连接条件的时候，就显示满足        条件的数据，但是如果不满足连接条件，则显示null
　　SELECT d.name,e.name
　　FROMdept d
　　LEFTOUTER JOIN employee e
　　ON d.id=e.deptId;
　　d.自连接查询
　　SELECT e.name AS '员工',b.name AS '上司'
　　FROMemployee e
　　LEFTOUTER JOIN employee b
　　ON e.bossId=b.id;
　　--  查询员工姓名及其上司姓名(没有上司的员工也显示
　　4.MySQL存储过程
　　a.特点：
　　①.存储过程保存到数据库服务器端，通过数据库客户端工具调用存储过程
　　②.存储过程的效率会非常高，因为存储过程是在数据库服务器端执行。
　　③.存储过程的移植性非常差的
　　b.存储过程语法
　　创建存储过程
　　-- 定义结束符号
　　DELIMITER 结束符号
　　CREATE PROCEDURE 存储过程名称 (形式参数列表)
　　BEGIN
　　多个sql语句
　　END 结束符号
　　-- 调用存储过程
　　CALL 存储过程名称(实际参数列表);
　　参数类型：
　　IN： 输入参数，可以携带数据到存储过程中
　　OUT: 输出参数，可以携带数据到存储过程外面。
　　INOUT: 输入输出参数。
　　c.几种常见存储过程
　　①. 带有输入参数的存储过程
　　--  传入员工id查询对应的员工
　　DELIMITER $
　　CREATE PROCEDURE pro_testByIn(IN eidINT)  -- 参数类型（IN） 参数名称 数据类型(int)
　　BEGIN

　　SELECT* FROM employee WHERE>　　END $
　　-- 调用
　　CALL pro_testByIn(2);
　　②.带有输出参数的存储过程
　　DELIMITER $
　　CREATE PROCEDURE pro_testByOut(OUT nVARCHAR(20))
　　BEGIN
　　--修改变量n
　　SETn = '输出参数';
　　END $
　　③.带有输入输出参数的存储过程
　　DELIMITER $
　　CREATE PROCEDURE pro_testByInOut(INOUT nVARCHAR(20))
　　BEGIN
　　--查看n变量
　　SELECTn;
　　--修改n变量
　　SETn = '500';
　　END $
　　-- 定义会话变量调用存储过程
　　SET @n='100';
　　CALL pro_testByInOut(@n);
　　-- 查看n
　　SELECT @n;
　　④.带有判断条件的存储过程
　　-- 输入一个num整数，num=1 ,输出‘星期一’,num=2,输出‘星期二’，num=3,输出‘星期
　　-- 三’，否则，输出‘错误参数’
　　DELIMITER $
　　CREATE PROCEDURE pro_testByIf(IN numINT,OUT str VARCHAR(20))
　　BEGIN
　　IFnum=1 THEN
　　SETstr = '星期一';
　　ELSEIFnum= 2 THEN
　　SETstr ='星期二';
　　ELSEIFnum=3 THEN
　　SETstr = '星期三';
　　ELSE
　　SETstr = '错误参数';
　　ENDIF;
　　END $
　　CALL pro_testByIf(5,@str);
　　SELECT @str;
　　⑤.带有循环条件的存储过程
　　--  输入一个num，计算从1到num的总和。
　　DELIMITER $
　　CREATE PROCEDURE pro_testByWhile(IN numINT,OUT score INT)
　　BEGIN
　　-- 定义局部变量
　　DECLARE i INT DEFAULT 1;
　　DECLAREresult INT DEFAULT 0;
　　WHILEi　　END $
　　CALL pro_testByData(2,@sname);
　　SELECT @sname;
　　d.mysql数据库三种变量：
　　①.全局变量。mysql内置的变量，mysql程序关闭的时候全局变量才会失效。
　　show variables:查看全局变量
　　character_set_client: mysql接收的客户端的数据编码
　　character_set_results： mysql使用什么编码输出给客户端数据
　　查看某个全局变量：select @@变量名
　　修改某个全局变量：set @@变量名=值
　　②.会话变量。变量只在某次登录的会话中有效！退出连接，会话变量数据失效。
　　查看某个会话变量： select @变量名
　　修改/定义某个会话变量： set @变量名=值
　　③. 局部变量：在存储过程中定义的变量。存储过程结束局部变量失效。
　　查看某个局部变量： select 变量名
　　修改某个局部变量： set 变量名=值
　　5.触发器
　　定义：当往员工表插入/修改/删除一条数据的时候，同时往日志表记录下来，这时就要使用触发器完       成。
　　触发器语法
　　-- 当往员工表插入一条数据时，往员工日志插入一条记录。
　　-- 员工日志表
　　CREATE TABLE emp_log(

　　>　　contentVARCHAR(20)
　　)
　　-- 创建增加触发器
　　CREATE TRIGGER tri_empAdd AFTER INSERT ONemployee FOR EACH ROW
　　INSERTINTO emp_log(content) VALUES('员工被插入了一条记录');
　　-- 创建修改触发器
　　CREATE TRIGGER tri_empUpd AFTER UPDATE ONemployee FOR EACH ROW
　　INSERTINTO emp_log(content) VALUES('员工被修改了一条记录');
　　-- 创建删除触发器
　　CREATE TRIGGER tri_empDel AFTER DELETE ONemployee FOR EACH ROW
　　INSERTINTO emp_log(content) VALUES('员工被删除了一条记录');
　　DROP TRIGGER tri_empAdd;
　　SELECT * FROM employee;
　　SELECT * FROM emp_log;
　　INSERT INTO employee(NAME,deptId,bossId)VALUES('李四',5,1);

　　UPDATE employee SET NAME='张三' WHERE>
　　DELETE FROM employee WHERE>　　6.mysql权限问题
　　root用户： 超级管理员。    权限：增删改查（数据库，表，数据）
　　给mysql数据库分配不同权限的不同用户
　　mysql数据库：存放mysql配置信息，包括用户信息
　　USE mysql;
　　-- 用户表
　　SELECT * FROM USER;
　　-- 加密函数(md5算法--单向加密)
　　SELECT PASSWORD('root');
　　-- 修改用户密码
　　UPDATE USER SET PASSWORD=PASSWORD('123456')WHERE USER='root';
　　-- 分配权限给不同的用户
　　-- 分配查询day15数据库的employee表的权限给eric用户，密码为‘123456’

　　GRANT SELECT ON day15.employee TO'eric'@'localhost'>
　　GRANT DELETE ON day15.employee TO'eric'@'localhost'>　　SHOW TABLES;