数据库常用sql语句详解

雨点打透心脏的1/2处 2022-06-14 01:51 231阅读 0赞

1 数据约束  
1.1 简介  
给表添加数据约束，从而约束用户操作表数据的行为。

1.2 默认值约束

注意： 当前没有插入默认值字段的时候，默认值才会起作用

– 1.1 默认值(default)

CREATE TABLE test(
        NAME VARCHAR(20),
        gender VARCHAR(2) DEFAULT '男'
    )
    
    INSERT INTO test(NAME) VALUES('张三');
    INSERT INTO test(NAME,gender) VALUES('张三',NULL);
    
    SELECT * FROM test;

– 需求：当不插入gender的时候，分配一个‘男’的默认值  
– 注意： 1）当没有插入gender字段的时候，分配一个默认值

1.3 非空约束

– 1.2 非空（not null）

CREATE TABLE test(
        NAME VARCHAR(20) NOT NULL,
        gender VARCHAR(2)
    )

– 需求： name字段一定要有值（不能不插入数据，不能是null），这是给name添加非空约束  
– 1)非空约束，不能不插入值  
INSERT INTO test(gender) VALUES(‘男’); – ERROR 1364 (HY000):  
Field ‘NAME’ doesn’t have a default value

– 2)非空约束，不能插入null  
INSERT INTO test(NAME,gender) VALUES(NULL,’男’);  
– ERROR 1048 (23000): Column ‘NAME’ cannot be null

1.4 唯一约束

CREATE TABLE test(
        id INT UNIQUE,
        NAME VARCHAR(20)
    )

– 需求： id的值不能出现重复值。这时就要给id添加一个唯一约束。  
INSERT INTO test(id,NAME) VALUES(1,’张三’);  
– 1)不能插入重复的值  
INSERT INTO test(id,NAME) VALUES(1,’李四’); – Duplicate entry ‘1’ for key ‘id’  
INSERT INTO test(id,NAME) VALUES(2,’李四’);  
– 2)唯一约束，可以插入多个null。所以唯一约束不能约束null  
INSERT INTO test(id,NAME) VALUES(NULL,’王五’);  
INSERT INTO test(id,NAME) VALUES(NULL,’陈六’);

1.5 主键约束

– 1.4 主键约束(primary key)（唯一+非空）  
– 注意：  
– 1）通常情况下，我们会给每张表都会设置一个主键字段，用来标记记录的唯一性  
– 2）但是不建议把业务含义字段作为主键，因为随着业务的变化，业务字段可能会出现重复。  
– 3）建议给每张张独立添加一个叫id的字段，把这个id字段设置成主键，用来作为记录的唯一性。

– 创建表给id字段施加主键

CREATE TABLE test(
        id INT PRIMARY KEY,
        NAME VARCHAR(20)
    )

– 1)唯一性  
INSERT INTO test(id,NAME) VALUES(1,’张三’);  
INSERT INTO test(id,NAME) VALUES(1,’张三’); – Duplicate entry ‘1’ for key ‘PRIMARY’

– 2）非空性  
INSERT INTO test(id,NAME) VALUES(NULL,’张三’); – ERROR 1048 (23000): Column ‘id’ cannot be null  
1.6 自增长约束（auto\_increment）  
– 1.5 自增长约束（auto\_increment）  
– 创建表给id字段添加主键自增长约束

CREATE TABLE test(
        id INT PRIMARY KEY AUTO_INCREMENT,
        NAME VARCHAR(20)
    )

– 自增长约束：初始值为0，每次递增1  
– 需求：id的值不需要我们开发者管理，交给数据库维护，这时给id添加自增长约束。  
INSERT INTO test(NAME) VALUES(‘张三’);  
INSERT INTO test(NAME) VALUES(‘李四’);  
INSERT INTO test(NAME) VALUES(‘王五’);

– 删除数据  
– delete from test where id=2;  
– 使用truncate table删除数据的时候，可以把自增长的初始值置为0  
TRUNCATE TABLE test;

SELECT \* FROM test;

1.7 外键约束
        外键约束： 约束两种表的情况
        问题：什么情况下会出现两种表？
        答案：员工表中出现部门名称的冗余字段！！！！
        如何解决部门名称冗余问题?
        答案：独立设计一张部门表，把部门名称放到部门表中，这是员工表只需要关联部门的id即可！！
        问题： 当插入员工表的部门id的时候，可能会出现不存在的部门id！！这是非法数据！！！如何防止非法数据的插入？
        答案： 这是可以把员工表的部门id设置为外键约束。
    
        当有了外键约束之后，操作数据的顺序如下：
                插入数据： 先插入主表的数据，再插入副表数据
                修改数据： 先修改主表数据，再修改副表数据
                删除数据： 先删除副表数据，再删除主表数据

– 1.6 外键约束  
– 外键作用： 约束两种表的数据  
– 问题： 约束哪种情况下的两种表呢？  
– 员工表

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(‘陈六’,’软件开发部’);

– 问题：每次插入员工数据时，部门名称数据会出现重复（冗余）,如果数据出现冗余，那么会浪费数据库存储空间。  
– 如何解决部门名称数据冗余的问题? 这时就可以设计一张独立的 部门表，把部门名称放到部门表中。

– 员工表  
CREATE TABLE employee(  
id INT PRIMARY KEY AUTO\_INCREMENT,  
NAME VARCHAR(20),  
deptId INT  
)

– 部门表  
CREATE TABLE dept(  
id INT PRIMARY KEY AUTO\_INCREMENT,  
NAME VARCHAR(20)  
)

SELECT \* FROM employee;  
SELECT \* FROM dept;

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(‘张三’,3); – 问题：在插入员工表的部门id的时候，插入了不存在的部门id，如何防止这种非法数据的插入，这时就添加外键约束了。

– 添加外键约束（foreign key）  
– 需求：deptId字段值来自于dept表的id字段的值，这时可以给deptId字段添加外键约束。  
– 员工表（副表： 被别的表约束。外键设置在副表）

CREATE TABLE employee(
        id INT PRIMARY KEY AUTO_INCREMENT,
        NAME VARCHAR(20),
        deptId INT,
        CONSTRAINT employee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id)
        --          外键名称                     外键字段  参考          
    )

– 部门表（主表：约束别人的表）  
CREATE TABLE dept(  
id INT PRIMARY KEY AUTO\_INCREMENT,  
NAME VARCHAR(20)  
)

SELECT \* FROM employee;  
SELECT \* FROM dept;

INSERT INTO employee(NAME,deptId) VALUES(‘陈六’,1);  
INSERT INTO employee(NAME,deptId) VALUES(‘王五’,1);  
– 插入数据  
INSERT INTO employee(NAME,deptId) VALUES(‘张三’,3); – 违反外键约束：a foreign key constraint fails  
– 修改数据  
UPDATE employee SET deptId=5 WHERE id=2; – a foreign key constraint fails  
– 删除数据  
DELETE FROM dept WHERE id=1; – a foreign key constraint fails

– 外键约束在什么情况下，会其作用？  
– 插入数据：当往副表插入了主表不存在的数据时，外键起作用  
– 修改数据：当往副表修改为主表不存在的数据时，外键起作用  
– 删除数据: 副表中有关联主表的数据的时候，当删除主表数据，外键其作用

– 当有了外键之后，应该如何管理数据呢？  
– 插入数据： 先插入主表的数据，再插入副表数据  
– 修改数据： 先修改主表数据，再修改副表数据  
UPDATE employee SET deptId=3 WHERE id=5;  
UPDATE dept SET id=3 WHERE id=2;  
– 删除数据：先删除副表数据，再删除主表数据  
DELETE FROM dept WHERE id=3;  
DELETE FROM employee WHERE deptId=3;

1.8 级联技术
        级联： 当有了外键的时候，我们希望修改或删除数据的时候，修改或删除了主表的数据，同时能够影响副表的数据，这时就可以使用级联。

CREATE TABLE employee(
        id INT PRIMARY KEY AUTO_INCREMENT,
        NAME VARCHAR(20),
        deptId INT,
        -- 添加级联修改： ON UPDATE CASCADE
        -- 添加级联删除： ON DELETE CASCADE 
        CONSTRAINT employee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id) ON UPDATE CASCADE ON DELETE CASCADE 
        --          外键名称                     外键字段  参考          
    )

2 数据库设计  
2.1 引入  
需求分析： 原始需求 -> 业务需求  
客户 业务模型：客户（姓名 登记时间 邮箱 微信号 ）  
需求设计：  
业务模型（客户业务模型）： -》 抽取实体模型： class Customer\{name email weixin\}  
业务模型（客户业务模型） -> 数据模型：设计数据库表（customer表： 字段）

2.2 数据库设计的三大范式
            第一范式： 要求表的每个字段必须独立的不可分割的单元
    
                        学生表： student    name             --违反第一范式
                                         张三|狗娃
                                         王含|张小含
    
                    查询： 现用名中包含‘张’的学生
                            select * from student where name like '%张%';
    
    
                        学生表： student  name   old_name             --符合第一范式
                                         张三      狗娃
                                         王含      张小含
    
        第二范式： 在第一范式的基础上，要求表的除主键以外的字段都和主键有依赖关系的。
                            一张表应该只表达一个意思！！！！
    
                    员工表：employee
                        员工编号  员工姓名  部门名称   订单名称      --违反第二范式
    
                    员工表：employee
                        员工编号  员工姓名  部门名称        --符合第二范式
    
                    订单表：
                        订单编号  订单名称
    
        第三范式： 在第二范式基础上，要求表的除主键外的字段都只能和主键有直接决定的依赖关系。
    
    
                    员工表：employee                                  --不符合第三范式（出现数据冗余）
                        员工编号  员工姓名  部门编号 部门名称
                        1       张三         1    软件开发部
                        2       李四      1     软件开发部 
    
                    员工表：employee                                  --符合第三范式（降低数据冗余）
                        员工编号  员工姓名  部门编号 
                        1       张三         1    
                        2       李四       1    
                    部门表： dept
                        部门编号  部门名称
                            1    软件开发部
    
                数据库存储空间  和 数据查询效率

3 多表查询

– 1.1 交叉连接查询（笛卡尔乘积: 4\*3=12，产生笛卡尔积的原因是没有足够的连接条件）  
– 需求： 员工及其部门  
SELECT employee.name,dept.name FROM employee,dept;

– 多表查询的步骤： 1）确定查询哪些表 2）确定查询哪些字段 3）确定连接条件(规则： 表数量-1)  
– 内连接效果：只有满足连接条件的数据才会显示出来  
– 1.2 内连接查询（使用最多）

SELECT e.name,d.name
        FROM employee e,dept d
        WHERE e.deptId=d.id;

– 另一种语法

SELECT e.name,d.name
        FROM employee e
        INNER JOIN dept d
        ON e.deptId=d.id;

– 1.3 左外连接查询（其次）  
– 需求： 查询部门及其部门的员工（部门全部显示）  
– 左外连接查询效果： 左表（部门表）的数据全部显示，右表（员工）的数据当满足连接条件的时候，  
– 就显示满足条件的的数据，但是如果不满足连接条件，则显示null  
– 预期结果：  
– 软件开发部 张三  
– 软件开发部 王五  
– 软件维护部 李四  
– 软件维护部 陈六  
– 秘书部 null

SELECT d.name,e.name
        FROM dept d
        LEFT OUTER JOIN employee e
        ON d.id=e.deptId;

– 1.4 右外连接查询  
– 右外连接查询效果： 右表（部门表）的数据全部显示，左表（员工）的数据当满足连接条件的时候，  
– 就显示满足条件的数据，但是如果不满足连接条件，则显示null

SELECT d.name,e.name
        FROM employee e
        RIGHT OUTER JOIN dept d
        ON d.id=e.deptId;

– 1.5 自连接查询  
– 需求： 查询员工姓名及其上司姓名(没有上司的员工也显示)  
– 预期结果：  
员工 上司  
– 张三 null  
– 李四 张三  
– 王五 李四  
– 陈六 王五

SELECT e.name AS '员工',b.name AS '上司'
        FROM employee e
        LEFT OUTER JOIN employee b
        ON e.bossId=b.id;

4 mysql存储过程  
4.1 引入  
存储过程，其实就是带逻辑的（多个）sql语句。也是sql编程。  
4.2 存储过程的特点  
1）存储过程保存到数据库服务器端，通过数据库客户端工具调用存储过程  
2）存储过程的效率会非常高！因为存储过程是在数据库服务器端执行。  
3）存储过程的移植性非常差的！  
4.3 存储过程语法

创建存储过程  
– 定义结束符号  
DELIMITER 结束符号  
CREATE PROCEDURE 存储过程名称 (形式参数列表)  
BEGIN  
多个sql语句  
END 结束符号

– 调用存储过程  
CALL 存储过程名称(实际参数列表);

参数类型：  
IN： 输入参数，可以携带数据到存储过程中  
OUT: 输出参数，可以携带数据到存储过程外面。  
INOUT: 输入输出参数。

– 3.1 带有输入参数的存储过程  
– 需求： 传入员工id查询对应的员工

DELIMITER $
    CREATE PROCEDURE pro_testByIn(IN eid INT)  -- 参数类型（IN） 参数名称 数据类型(int)
    BEGIN
        SELECT * FROM employee WHERE id=eid;
    END $

– 调用  
CALL pro\_testByIn(2);

– 3.2 带有输出参数的存储过程

DELIMITER $
    CREATE PROCEDURE pro_testByOut(OUT n VARCHAR(20))
    BEGIN
        -- 修改变量n
        SET n = '输出参数';
    END $

– 问题： 如何接收存储过程的输出参数？？？  
– 定义变量去接收输出参数数据。

– mysql数据库三种变量：  
– 1）全局变量。mysql内置的变量，mysql程序关闭的时候全局变量才会失效！！  
– show variables:产看全局变量  
– character\_set\_client: mysql接收的客户端的数据编码  
– character\_set\_results： mysql使用什么编码输出给客户端数据  
– 查看某个全局变量： select @@变量名  
– 修改某个全局变量： set @@变量名=值  
– 2) 会话变量。变量只在某次登录的会话中有效！退出连接，会话变量数据失效！！  
– 查看某个会话变量： select @变量名  
– 修改/定义某个会话变量： set @变量名=值  
– 案例：演示查询和更改会话变量  
– 3) 局部变量：在存储过程中定义的变量。存储过程结束局部变量失效！！  
– 查看某个局部变量： select 变量名  
– 修改某个局部变量： set 变量名=值  
– 定义某个局部变量： declare 变量名 数据类型;

– 定义会话变量去接收输出参数数据  
– set @n=’eric’;  
CALL pro\_testByOut(@n);  
– 查看会话变量n  
SELECT @n;

– 3.3 带有输入输出参数的存储过程

DELIMITER $
    CREATE PROCEDURE pro_testByInOut(INOUT n VARCHAR(20))
    BEGIN
        -- 查看n变量
        SELECT n;
        -- 修改n变量
        SET n = '500';
    END $

– 定义会话变量调用存储过程

SET @n='100';
    CALL pro_testByInOut(@n);
    -- 查看n
    SELECT @n;

– 3.4 带有判断条件的存储过程  
– 需求： 输入一个num整数，num=1 ,输出‘星期一’,num=2,输出‘星期二’，num=3,输出‘星期三’，否则，输出‘错误参数’

DELIMITER $
    CREATE PROCEDURE pro_testByIf(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_testByIf(5,@str);
    SELECT @str;

– 3.5 带有循环条件的存储过程  
– 需求： 输入一个num，计算从1到num的总和。

DELIMITER $
    CREATE PROCEDURE pro_testByWhile(IN num INT,OUT score INT)
    BEGIN
        -- int result =0;
        -- for(int i=1;i<=100;i++){
        --  result += i;
        -- }
    
        -- 定义局部变量
        DECLARE i INT DEFAULT 1;
        DECLARE result INT DEFAULT 0;
        WHILE i<=num DO
            SET result = result + i;
            SET i = i+1;
        END WHILE;
        SET score = result;
    END $
    
    
    CALL pro_testByWhile(200,@score);
    SELECT @score;

– 3.6 携带数据库的数据给输出参数(INTO)  
– 需求： 传入员工id，查询对应的员工，输出员工姓名

DELIMITER $
    CREATE PROCEDURE pro_testByData(IN eid INT,OUT sname VARCHAR(20))
    BEGIN
        SELECT NAME INTO sname FROM employee WHERE id=eid;
    END $
    
    CALL pro_testByData(2,@sname);
    SELECT @sname;ss

– 练习  
存储过程练习  
在student表的数据基础上，设计一个存储过程：

1）计算出所有学生的英语平均分
    2）
        如果平均分，大于80分，且小于等于100分，输出'优秀'
        如果平均分，大于等于60分，且小于等于80分，输出'良好'
        如果平均分，小于60分，输出'不及格'

USE day14;
    
    SELECT * FROM student2;
    
    DELIMITER $
    CREATE PROCEDURE pro_testByAvg(OUT str VARCHAR(20),OUT tavg FLOAT)
    BEGIN
        DECLARE savg FLOAT DEFAULT 0.0;
        SELECT AVG(english) INTO savg FROM student2;
        IF savg>80 AND savg<=100 THEN
            SET str = '优秀';
        ELSEIF savg>=60 AND savg<=80 THEN
            SET str = '良好';
        ELSE 
            SET str = '不及格';
        END IF;
        SET tavg = savg;    
    END $
    
    -- 删除存储过程
    DROP PROCEDURE pro_testByAvg;
    
    
    CALL pro_testByAvg(@str,@tavg);
    SELECT  @str;
    SELECT @tavg;

5 触发器  
5.1 简介  
当往员工表插入/修改/删除一条数据的时候，同时往日志表记录下来，这时就要使用触发器完成。  
5.2 触发器语法  
– 员工日志表

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('员工被插入了一条记录');

– 创建修改触发器

CREATE TRIGGER tri_empUpd AFTER UPDATE ON employee FOR EACH ROW
        INSERT INTO emp_log(content) VALUES('员工被修改了一条记录');

– 创建删除触发器

CREATE TRIGGER tri_empDel AFTER DELETE ON employee FOR EACH ROW
    INSERT INTO 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 id=10;  
DELETE FROM employee WHERE id=10;

5.3 数据库的权限问题  
– 五、mysql权限问题  
– root用户： 超级管理员。 权限： 增删改查（数据库，表，数据）  
– 给mysql数据库分配不同权限的不同用户

– 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 day15.employee TO 'eric'@'localhost' IDENTIFIED BY '123456';
    GRANT DELETE ON day15.employee TO 'eric'@'localhost' IDENTIFIED BY '123456';

SHOW TABLES;