CREATE DATABASE day16; USE day16; -- 2.1 在表中设置默认值，当用户对使用默认值的字段不插入值的时候，就使用默认值。（可以插入null） CREATE TABLE student(id INT,NAME VARCHAR(20),address VARCHAR(20) DEFAULT '广州天河'); DESC student; INSERT INTO student(id,NAME,address) VALUES (2,'李四',NULL); SELECT * FROM student; DELETE FROM student WHERE id=2; -- 2.2 非空，作用： 限制字段必须赋值（可以是空不能是NULL） DROP TABLE student; CREATE TABLE student(id INT,NAME VARCHAR(20),gender VARCHAR(2) NOT NULL); INSERT INTO student(id,NAME,gender) VALUES (2,'李四',''); -- 2.3唯一，对字段的值不能重复（唯一字段可以插入多个null） DROP TABLE student; CREATE TABLE student(id INT UNIQUE,NAME VARCHAR(20)); INSERT INTO student(id,NAME) VALUES (2,'王五'); -- 2.4 主键。作用： 非空+唯一，每张表都会设置一个主键字段。用于标记表中的每条记录的唯一性。 -- 建议不要选择表的包含业务含义的字段作为主键，建议给每张表独立设计一个非业务含义的id字段。 DROP TABLE student; CREATE TABLE student(id INT PRIMARY KEY,NAME VARCHAR(20)); SELECT * FROM student; INSERT INTO student(id,NAME) VALUES(1,'张三'); INSERT INTO student(id,NAME) VALUES(2,'张三'); -- 修改语句 UPDATE student SET NAME='李四' WHERE id=2; INSERT INTO student(id,NAME) VALUES(1,'李四'); -- 违反唯一约束 INSERT INTO student(NAME) VALUE('李四'); -- 违反非空约束 -- 2.5 自增长。作用：自动递增。 DROP TABLE student; -- INT(4) ZEROFILL id填入四位数，当位数不够时插入0 CREATE TABLE student(id INT(4) ZEROFILL PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20)); INSERT INTO student(NAME) VALUES('张三'); -- 2.7 外键。约束两种表的数据。解决数据冗余高问题： 独立出一张表。 -- 部门表（主表） CREATE TABLE dept(id INT PRIMARY KEY,deptName VARCHAR(20)); -- 员工表（副表/从表） -- 建表时把部门名称换为部门id，然后声明一个外键约束。 -- 使用级联修改，ON UPDATE CASCADE ON DELETE CASCADE，可以先对主表进行操作修改和删除。 CREATE TABLE employee(id INT PRIMARY KEY,empName VARCHAR(20),deptid INT,CONSTRAINT emplyee_dept_fk FOREIGN KEY(deptid) REFERENCES dept(id)ON UPDATE CASCADE ON DELETE CASCADE); DROP TABLE employee; SELECT * FROM employee; SELECT * FROM dept; INSERT INTO dept(id,deptName) VALUES(4,'总经办') ; INSERT INTO employee(id,empName,deptid) VALUES(3,'王五',2) ; UPDATE employee SET deptid=2 WHERE id=1; DELETE FROM dept WHERE id=1; -- 1）被约束的表称为副表，约束别人的表称为主表，外键设置在副表上的！！！ -- 2）主表的参考字段通用为主键！ -- 3）添加数据： 先添加主表，再添加副表 -- 4）修改数据： 先修改副表，再修改主表 -- 5）删除数据： 先删除副表，再删除主表 -- 3 关联查询（多表查询） -- 查询员工及其所在部门（显示员工姓名，部门名称） -- 3.1 交叉连接查询，不推荐，会产生笛卡尔积 SELECT empName,deptName FROM employee,dept; -- 查询员工及其所在部门(显示员工姓名，部门名称) -- 多表查询规则：1）确定查询哪些表 2）确定哪些哪些字段 3）表与表之间连接条件 (规律：连接条件数量是表数量-1) -- 3.2 内连接查询：只有满足条件的结果才会显示(使用最频繁) SELECT empName,deptName FROM employee,dept WHERE employee.deptid=dept.id; -- 内连接查询的另外一种写法 SELECT empName,deptName FROM employee INNER JOIN dept ON employee.deptid=dept.id; -- 3.3 使用别名 SELECT empName,deptName FROM employee e INNER JOIN dept d ON e.deptid=d.id; -- 3.4 查询每个部门的员工（左外连接查询，使用左边的表去匹配右边表的数据，符合连接条件的结果则显示，如果不符合连接条件则显示null） -- 左边的数据一定会显示 SELECT deptName,empName FROM dept d LEFT OUTER JOIN employee e ON e.deptid=d.id; SELECT * FROM employee; UPDATE employee SET deptid=NULL WHERE id=1; -- 3.5自连接查询 -- 需求：查询员工及其上司 SELECT e.empName,b.empName FROM employee e LEFT OUTER JOIN employee b ON e.deptid=b.id; -- 4.1存储过程语法 DELIMITER $ -- 声明一个存储过程结束符号 CREATE PROCEDURE pro_test() BEGIN -- 可以写多个sql语句 SELECT * FROM employee; END $ -- 执行存储过程 CALL pro_test(); -- CALL 存储过程的名称（参数） -- 参数： -- IN： 表示输入参数，可以携带数据到存储过程中 -- OUT： 表示输出参数，可以从存储过程中返回结果 -- INOUT： 表示输入输出参数，既可以输入功能，也可以输出功能 -- 4.2 带有输入参数的存储过程 DELIMITER $ CREATE PROCEDURE pro_findByid(IN eid INT) -- 输入参数 BEGIN SELECT * FROM employee WHERE id=eid; END $ -- 调用 CALL pro_findByid(3); -- 4.3 带有输出参数的存储过程 DELIMITER $ CREATE PROCEDURE pro_testOut(OUT str VARCHAR(20)) -- 输出参数 BEGIN SET str='hellojava'; END $ -- 调用（变量分为全局变量，会话变量，局部变量，这里使用会话变量） CALL pro_testOut(@str); -- 查看变量的值 SELECT @str; -- 4.4 带有输入输出参数的存储过程 DELIMITER $ CREATE PROCEDURE pro_testInOut(INOUT n INT) -- INOUT： 输入输出参数 BEGIN -- 查看传入的变量 SELECT n; SET n=500; END $ -- 调用 SET @n=10; CALL pro_testInOut(@n); -- 查看返回的值 SELECT @n; -- 带有条件判断的存储过程 -- 需求：输入一个整数，如果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='星期三'; ELSEIF num=4 THEN SET str='星期四'; ELSEIF num=5 THEN SET str='星期五'; END IF; END $ -- 调用 CALL pro_testIf(4,@str); -- 查看结果 SELECT @str; -- 4.5带有循环功能的存储过程 -- 需求： 输入一个整数，求和。例如，输入100，统计1-100的和 DELIMITER $ CREATE PROCEDURE pro_testWhile(IN num INT,OUT result INT) BEGIN -- 定义两个局部变量，一个变量用来保存i，一个变量用来保存结果0 DECLARE i INT DEFAULT 1; DECLARE vsum INT DEFAULT 0; WHILE i<=num DO SET vsum=i+vsum; SET i=i+1; END WHILE; SET result=vsum; END $ -- 删除一个存储过程 DROP PROCEDURE pro_testWhile; -- 调用 CALL pro_testWhile(100,@result); SELECT @result; -- 4.6使用查询的结果赋值给变量（使用INTO） DELIMITER $ CREATE PROCEDURE pro_findByid2(IN eid INT,OUT vname VARCHAR(20)) BEGIN SELECT empName INTO vname FROM employee WHERE id=eid; END $ -- 调用 CALL pro_findByid2(1,@NAME); -- 输出查询的结果 SELECT @NAME; -- 5.0 触发器 -- 当操作了某张表时，希望同时触发一些动作/行为，可以使用触发器完成！！ -- 需求：当向员工表插入一条记录时，希望同时往日志表插入数据。 -- 建立日志表 CREATE TABLE test_log(id INT PRIMARY KEY AUTO_INCREMENT,content VARCHAR(100)); -- 创建触发器 CREATE TRIGGER tri_empAdd AFTER INSERT ON employee FOR EACH ROW INSERT INTO test_log(content) VALUES('员工表插入了一条记录'); -- 向员工表插入数据 INSERT INTO employee(id,empName,deptid) VALUES(4,'赵六',2); SELECT * FROM employee; -- 查看日志表中的内容 SELECT * FROM test_log; -- 修改触发器 CREATE TRIGGER tri_empUpdate AFTER UPDATE ON employee FOR EACH ROW INSERT INTO test_log(content) VALUES('员工表修改了一条记录'); -- 删除触发器 DROP TRIGGER tri_empUpdate; -- 修改员工表数据 UPDATE employee SET empName='joker' WHERE id=4; -- 6.0权限问题 USE mysql; SELECT * FROM USER; SELECT PASSWORD('root'); -- 修改密码 UPDATE USER SET PASSWORD=PASSWORD('123456') WHERE USER='root'; -- 分配权限账户（固定写法）localhost为账户类型（本地或者··）操作哪个数据库的哪张表 -- 注意！！root账户拥有操作数据库的所有权限！！ -- 新建一个用户，并给这个用户分配权限 GRANT SELECT ON day16.employee TO 'eric'@'localhost' IDENTIFIED BY '123456'; GRANT DELETE ON day16.employee TO 'eric'@'localhost' IDENTIFIED BY '123456'; DELETE FROM employee WHERE id=1; INSERT INTO employee(id,empName,deptid) VALUES(2,'李四',3); SELECT * FROM employee;
