SHOW DATABASES;
CREATE DATABASE day20;
USE day20;
SHOW TABLES;
-- 默认值约束(当没有插入默认值字段的时候，默认值才会有作用)
CREATE TABLE test(
NAME VARCHAR(20),
gender VARCHAR(2) DEFAULT '男'
);
INSERT INTO test(NAME) VALUES('张三');
INSERT INTO test(NAME,gender) VALUES('张三',NULL);
DELETE FROM test;
DROP TABLE test;
-- 非空约束（not null）
CREATE TABLE test(
NAME VARCHAR(20) NOT NULL,
gender VARCHAR(2)
);
DESC test;
-- 1、不能不插入值(虽然说设置了不能为空，但是由于使用外部软件插入数据，
-- 他会默认插入一个''，所以数据还是可以存入数据库，但是在使用MySQL自带的操作界面的时候就会报错)
INSERT INTO test(gender) VALUES('男');-- Field 'NAME' doesn't have a default value
-- 2、插入值不能为null
INSERT INTO test(NAME,gender) VALUES(NULL,'男');-- Column 'name' cannot be null
-- 唯一约束(添加唯一约束的字段不能出现重复值)
CREATE TABLE test(
id INT UNIQUE,
NAME VARCHAR(20)
);
-- 1、不能添加重复的值
INSERT INTO test(id,NAME) VALUES(1,'李四');
INSERT INTO test(id,NAME) VALUES(1,'王五');-- Duplicate entry '1' for key 'id'
-- 2、不能约束null，可以添加多个null
INSERT INTO test(id,NAME) VALUES(NULL,'赵六');
INSERT INTO test(id,NAME) VALUES(NULL,'赵');
-- 主键约束（primary key）== 唯一+非空
CREATE TABLE test(
id INT PRIMARY KEY,
NAME VARCHAR(20)
);
-- 唯一性
INSERT INTO test VALUES(1,'戴安娜');
INSERT INTO test VALUES(1,'孔明');-- Duplicate entry '1' for key 'PRIMARY'
-- 非空性
INSERT INTO test(NAME) VALUES('张三');
INSERT INTO test(id,NAME) VALUES(NULL,'李四');-- Column 'id' cannot be null
-- 自增长约束(初始值为0，每一递增1)
CREATE TABLE test(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
);
INSERT INTO test(NAME) VALUES('刘德华');
INSERT INTO test(NAME) VALUES('张学友');
INSERT INTO test(NAME) VALUES('白小春');
INSERT INTO test(NAME) VALUES('复方木');
-- 删除数据使用delete不会重置主键，使用truncate会重置主键
DELETE FROM test;
TRUNCATE TABLE test;
SELECT * FROM test;
-- -------------------------------------------------------
-- 外键约束，当一张表中出现另一张表的数据的，且数据存在冗余
-- 有了外键约束后，操作数据的顺序：
-- 插入数据：先插入主表的数据，再插入副表的数据
-- 删除数据：先删除副表的数据，再删除主表的数据
-- 修改数据：先修改主表的数据，再修改副表的数据
CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
deptName VARCHAR(20)
);
INSERT INTO employee(NAME,deptName) VALUES('张三','软件开发部');
INSERT INTO employee(NAME,deptName) VALUES('李四','软件维护部');
INSERT INTO employee(NAME,deptName) VALUES('王五','软件开发部');
INSERT INTO employee(NAME,deptName) VALUES('赵六','软件维护部');
INSERT INTO employee(NAME,deptName) VALUES('李齐','软件开发部');
-- 部门名称存在冗余，解决方法，再创建一个部门表
CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
deptId VARCHAR(20)
);
CREATE TABLE dept(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
);
INSERT INTO dept(NAME) VALUES('软件开发部');
INSERT INTO dept(NAME) VALUES('软件维护部');
INSERT INTO dept(NAME) VALUES('后勤部');
INSERT INTO employee(NAME,deptId) VALUES('陈六',1);
INSERT INTO employee(NAME,deptId) VALUES('王五',1);
INSERT INTO employee(NAME,deptId) VALUES('赵六',2);
INSERT INTO employee(NAME,deptId) VALUES('李毅',1);
INSERT INTO employee(NAME,deptId) VALUES('小春',2);
INSERT INTO employee(NAME,deptId) VALUES('刘姐',3);
-- 再没有添加外键约束的时候，非法数据也可以插入，比如说本身没有4号部门，但是却可以插入4号部门的成员
INSERT INTO employee(NAME,deptId) VALUES('刘姐',4);
-- 添加外键约束
-- 创建的时候，先创建主表，再创建副表
-- 主表：约束副表的表
-- 副表：被约束的表
CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
deptId INT,
CONSTRAINT employee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id)
-- 申明 外键名字 外键 字段 依赖于 哪张表的字段
);
-- 插入数据：先插入主表的数据，再插入副表的数据
INSERT INTO dept(NAME) VALUES('秘书部');
INSERT INTO employee(NAME,deptId) VALUES('刘姐',4);
-- 删除数据：先删除副表的数据，再删除主表的数据
DELETE FROM employee WHERE id=7;
DELETE FROM dept WHERE id=4;
-- 修改数据：先修改主表的数据，再修改副表的数据
UPDATE dept SET id=4 WHERE id=3;
UPDATE employee SET deptId=4 WHERE id=5;
DROP TABLE employee;
DROP TABLE dept;
SELECT * FROM employee;
SELECT * FROM dept;
SHOW TABLES;
-- ------------------------------------------------------
-- 级联删除
-- 当删除或者修改主表的数据的时候。对副表造成影响副表的数据
CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
deptId INT,
CONSTRAINT employee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id) ON UPDATE CASCADE ON DELETE CASCADE
);
DELETE FROM dept WHERE id=2;
-- ------------------------------------------------------------
-- 多表查询
-- 步骤：1、确定查询哪些表2、确定查询哪些字段，3、确定连接条件（规则，表数量减一）
-- 效果：只有满足连接条件的的数据才能显示出来
-- 1、交叉查询（笛卡尔乘积4*3=12，产生原因是没有足够的连接条件）
-- 查询员工及其部门
SELECT employee.name,dept.name FROM employee,dept;
-- 2、内连接查询
-- 方式一：使用from。。。where
SELECT e.name,d.name
FROM employee e,dept d
WHERE e.deptId=d.id;
-- 方式二：使用from...inner join..on
SELECT e.name,d.name
FROM employee e
INNER JOIN dept d
ON e.deptId=d.id;
-- 3、左外连接查询
-- 效果：左表的数据会完全显示出来，右表的符合连接条件的显示数据，不满足连接条件的显示为null
-- 需求：查询部门及其部门的员工
SELECT d.name,e.name
FROM dept d
LEFT OUTER JOIN employee e
ON d.id=e.deptId;
-- 4、右连接查询
-- 效果：右表的数据会完全显示，左表的数据符合连接条件的显示数据，不满足连接条件的显示为null
SELECT e.name,d.name
FROM employee e
LEFT OUTER JOIN dept d
ON e.deptId=d.id;
-- 5、内连接查询
-- 查询一张表里面的数据，表中数据存在内在关系
ALTER TABLE employee ADD COLUMN sname INT;
SELECT * FROM employee;
-- 查询员工和上司
SELECT e.name AS '员工',m.name AS '上司'
FROM employee e
LEFT OUTER JOIN employee m
ON e.id=m.sname;
-- ----------------------------------------------------
-- 存储过程
-- 创建存储过程
-- delimiter 结束符号
-- create procedure 存储过程名字（形式参数列表）
-- begin
-- 多个sql语句
-- end结束符号
-- 参数类型：
-- IN： 输入参数，可以携带数据到存储过程中
-- OUT: 输出参数，可以携带数据到存储过程外面。
-- INOUT: 输入输出参数。
-- 带有输入参数
-- 需求：传入员工id查询对应的员工
DELIMITER $
CREATE PROCEDURE pro_getId(IN eid INT)
BEGIN
SELECT * FROM employee WHERE id=eid;
END$
-- 调用存储过程
CALL pro_getId(2);
-- 带有输出参数
DELIMITER $
CREATE PROCEDURE pro_getOut(OUT n VARCHAR(20))
BEGIN
SET n='输出参数';
END$
-- 获取输数参数
-- 设置输出
-- set @n='ada';
CALL pro_getOut(@n);
SELECT @n;
-- mysql数据库有三种变量
-- 1、全局变量，MySQL内置的变量，只有当程序关闭的时候全局变量才会失效
-- 查看全局变量
SHOW VARIABLES;
-- 查看某个全局变量
-- select @@+全局变量名
SHOW VARIABLES LIKE 'character%';
SELECT @@character_set_client;
-- 如何去改变数据库的全局变量
-- set @@+全局变量=值
SET @@character_set_client=gbk;
-- 2、会话变量。变量只在某次登录的会话中有效！退出连接，会话变量数据失效！！
-- 查看某个会话变量： select @变量名
SELECT @n;
-- 修改/定义某个会话变量： set @变量名=值
SET @n='da';
-- 3、局部变量：在存储过程中定义的变量。存储过程结束局部变量失效！！
-- 查看某个局部变量： select 变量名
-- 修改某个局部变量： set 变量名=值
-- 定义某个局部变量： declare 变量名 数据类型;
CREATE TABLE student(
id INT,
NAME VARCHAR(20),
chinese FLOAT,
english FLOAT,
math FLOAT
);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(1,'张小明',89,78,90);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(2,'李进',67,53,95);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(3,'王五',87,78,77);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(4,'李一',88,98,92);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(5,'李来财',82,84,67);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(6,'张进宝',55,85,45);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(7,'黄蓉',75,65,30);
SELECT * FROM student;
SELECT AVG(english) FROM student;
DELIMITER $
CREATE PROCEDURE stu_avgEnglish(OUT tavg FLOAT,OUT pifen VARCHAR(20))
BEGIN
-- 定义一个局部变量
DECLARE eavg FLOAT DEFAULT 0.0;
-- 携带数据库的数据给输出参数（into）
SELECT AVG(english) INTO eavg FROM student;
IF eavg>80 AND eavg<=100 THEN
SET pifen='优秀';
ELSEIF eavg>=60 AND eavg<=80 THEN
SET pifen='良好';
ELSE
SET pifen='不及格';
END IF;
SET tavg=eavg;
END$
-- 调用存储过程
CALL stu_avgEnglish(@tavg,@pifen);
-- 查看会话变量
SELECT @pifen;
-- 删除存储过程
DROP PROCEDURE stu_avgEnglish;
-- 触发器
-- 当往表中插入、修改、删除一天数据的时候，同时使用日志记录下来，这是就要使用触发器实现
CREATE TABLE emp_log(
id INT PRIMARY KEY AUTO_INCREMENT,
content VARCHAR(20)
);
-- 插入数据的触发器
CREATE TRIGGER tri_empAdd AFTER INSERT ON employee FOR EACH ROW
INSERT INTO emp_log(content) VALUES('插入一条数据');
INSERT INTO employee(NAME,deptId) VALUES('阿发啊',1);
-- 修改数据的触发器
CREATE TRIGGER tri_empUpdate AFTER UPDATE ON employee FOR EACH ROW
INSERT INTO emp_log(content) VALUES('修改一条数据');
UPDATE employee SET NAME='发' WHERE id=3;
-- 删除数据的触发器
CREATE TRIGGER tri_empDelete AFTER DELETE ON employee FOR EACH ROW
INSERT INTO emp_log(content) VALUES('删除一条数据');
DELETE FROM employee WHERE id=4;
SELECT * FROM emp_log;
-- -------------------------------------------------------
-- 权限问题
-- root用户，超级管理员，权限：增删改查（数据库，表，数据）
-- MySQL数据库：存放mysql配置信息，包括用户信息
USE mysql;
-- 用户表
SELECT * FROM USER;
-- 加密函数（MD5-加密--单向加密）
SELECT PASSWORD('root');-- *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B
-- 修改用户密码
UPDATE USER SET PASSWORD=PASSWORD('123456') WHERE USER='root';
-- 分配不同权限给不同的用户
-- 需求： 分配查询day15数据库的employee表的权限给eric用户，密码为‘123456’
GRANT SELECT ON day20.employee TO 'eric'@'localhost' IDENTIFIED BY '123456';
GRANT DELETE ON day20.employee TO 'eric'@'localhost' IDENTIFIED BY '123456';
