Java学习笔记--sql语句

首先明确，sql语句是针对Mysql数据库编程的特殊语句。
1.数据约束（约束用户操作表数据的行为）
1.1默认值约束
关键字：default;
注意：当前字段没有插入默认值的时候，默认值才会起作用。

    CREATE TABLE test(
        NAME VARCHAR(20),
        gender VARCHAR(2) DEFAULT '男'
    );

1.2非空约束(某字段一定要有值)
关键字：NOT NULL
注意：约束字段不能不插入值，也不能插入NULL

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

1.3唯一约束(被约束的字段不能出现重复的值)
关键字：UNIQUE
注意：唯一约束可以多次插入null

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

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

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

1.5自增长约束（初始值为0，每次递增1）
关键字：AUTO_INCREMENT
注意：使用truncate table删除数据的时候，可以把自增长的初始值置为0

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

1.6外键约束(约束两种表的情况)
关键字：FOREIGN KEY REFERENCES
注意：约束别人的表叫主表，被约束的表叫附表
插入数据： 先插入主表的数据，再插入副表数据
修改数据： 先修改主表数据，再修改副表数据
删除数据：先删除副表数据，再删除主表数据

    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)
);

1.7级联技术（当有了外键的时候，我们希望修改或删除数据的时候，修改或删除主表的数据同时也能够影响附表的数据）
关键字：ON * CASCADE

    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三大范式
1.要求表的每个字段必须独立的不可分割的单元
2.在第一范式的基础上，要求表的除主键以外的字段都和主键有依赖关系的。一张表应该只表达一个意思！
3.在第二范式基础上，要求表的除主键外的字段都只能和主键有直接决定的依赖关系。
3.多表查询
3.1交叉连接查询
后果：会产生笛卡尔效应

SELECT employee.name,dept.name FROM employee,dept;

3.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;

3.3左外链接查询

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

3.4右外链接查询

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

3.5自链接查询

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

4.mysql存储过程
4.1mysql存储过程说通俗点就是sql编程
4.2存储过程的特点
1）存储过程保存到数据库服务器端，通过数据库客户端工具调用存储过程
2）存储过程的效率会非常高！因为存储过程是在数据库服务器端执行。
3）存储过程的移植性非常差的！
4.3语法

    DELIMITER 结束符号
CREATE PROCEDURE 存储过程名称 (形式参数列表) 
BEGIN
    多个sql语句
END 结束符号
CALL 存储过程名称(实际参数列表);

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

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

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;

5.触发器
1定义：当往员工表插入/修改/删除一条数据的时候，同时往日志表记录下来，这时就要使用触发器完成。
2语法：关键字–TEIGGER

    创建触发器
    CREATE TRIGGER tri_empAdd AFTER INSERT ON employee FOR EACH ROW
    INSERT INTO emp_log(content) VALUES('员工被插入了一条记录');

删除触发器
DROP TRIGGER tri_empAdd;