java从入门到弃坑数据库二

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

2.默认值约束：当被约束的字段没有值插入时，会自动分配一个默认值。

CREATE TABLE student(
NAME VARCHAR(20),
gender VARCHAR(2)DEFAULT '男'
);
INSERT INTO student(NAME) VALUES('jack');
INSERT INTO student(NAME,gender) VALUES('van',NULL);
SELECT*FROM student;

只要插入数据，哪怕数据为空，也会覆盖该默认值。

3.非空约束：要求被约束的字段一定要有值，不能为null，不能不插入。

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

4.唯一约束：被约束的字段不能插入重复的值，但是可以插入多个null，所以该约束不能约束null

CREATE TABLE student(
id INT UNIQUE ,
NAME VARCHAR(20)
);
INSERT INTO student VALUES(1,'asd');
INSERT INTO student VALUES(NULL,'sdf');
INSERT INTO student VALUES(NULL,'qwe');
SELECT * FROM student;

5.主键约束：被约束的字段唯一+非空。因此一般给每张表添加一个id字段设置成主键作为记录的唯一性。

CREATE TABLE student(
id INT UNIQUE ,
NAME VARCHAR(20)
);
INSERT INTO student VALUES(1,'asd');
INSERT INTO student VALUES(NULL,'sdf');
INSERT INTO student VALUES(NULL,'qwe');
SELECT * FROM student;

6.自增长约束：初始值为0，每次新添加一条数据，被约束的字段自加1.

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

一般设置id为自增长，就不用我们手动进行输入。

7.数据删除：TRUNCATE TABLE student;  该指令会删除表中数据，并使系统的自增长值置零，不会删除该表。

8.外键约束：有时根据需要，我们在创建表时会出现数据冗余的情况，即相对较长的数据在表中频繁出现，此时可以

                     用第二张表来关联第一张表中的数据，这时就需要用到外键约束。

CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
idDept INT ,-- 与部门相连的部门id
CONSTRAINT employee_dept_fk FOREIGN KEY(idDept) REFERENCES dept(id) 
-- 创建外键：   外键名称                外键字段                参考
);
-- 创建部门表 约束员工的表
CREATE TABLE dept(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
);-- 创建时先创建主表（部门表），在创建附表（员工表）

约束情况：A：向附表中插入主表不存在的数据时，外键报错。

                  B：附表中修改为主表不存在数据时，外键报错。

                  C：在附表中有关联主表数据的情况下删除主表数据时，外键报错。

对应操作：A：插入数据时先插入主表数据，在插入附表数据。

                  B：修改数据时先修改主表数据，在修改附表数据。

                  C：删除数据时先删除附表数据，再删除主表数据。

9.级练技术：在拥有外键的情况下，可以使我们在修改或删除主表数据的情况下，同时影响附表的数据。

CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
idDept INT ,
CONSTRAINT employee_dept_fk FOREIGN KEY(idDept) REFERENCES dept(id) ON UPDATE CASCADE ON DELETE CASCADE
-- ON UPDATE CASCADE(修改级联)       ON DELETE CASCADE（删除级联）
);

10.数据库设计：第一范式：要求表的每个字段必须时独立的不可分割的一个单元，即不能把多个字段的内容放在一个

                                             字段中合并。
                          第二范式：在第一范式的基础上，要求表的除主键以外的字段都和主键有依赖关系，即一张表只能表

                                            示一个意思。

                          第三范式：在第二范式的基础上，要求表的除主键外的字段都只能和主键有直接决定的依赖关系。

11.多表查询：交叉连接查询：因为没有足够的连接条件而产生笛卡尔乘积。

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

                       内连接查询：SELECT dept.name,employee.name FROM dept,employee WHERE

                                            employee.idDept=dept.id;

                       左外连接查询：左表数据全显示，右表数据满足就显示，不满足显示null。

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

                       右外连接查询：右表数据全部显示，左表满足连接就显示。

                                SELECT d.name,e.name FROM employee e RIGHT OUTER JOIN dept d ON d.id=e.idDept;
                       自连接查询：SELECT e.name AS '员工',b.name AS '上司' FROM employee e LEFT OUTER JOIN

                                             employee b ON e.bossId=b.id;

12.Mysql存储过程：

创建存储过程

-- 定义结束符号

DELIMITER 结束符号

CREATE PROCEDURE 存储过程名称 (形式参数列表)

BEGIN

         多个sql语句

END 结束符号

 

-- 调用存储过程

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

 

参数类型：

IN： 输入参数，可以携带数据到存储过程中

OUT: 输出参数，可以携带数据到存储过程外面。

INOUT: 输入输出参数。

13. 带有输入参数的存储过程：      

DELIMITER $-- 输入id查看员工信息
CREATE PROCEDURE pro_testid(IN ide INT)
BEGIN
	SELECT * FROM employee WHERE id=ide;
END $
CALL pro_testid(2);

14. 带有输出参数的存储过程：

DELIMITER $ -- 输出信息
CREATE PROCEDURE pro_testout(OUT n VARCHAR(30))
BEGIN
	SET n='long may the sunshine';
END $
-- 可以用SET @n 改变会话变量的值
-- 定义会话变量接收输出参数
CALL pro_testout(@n);
SELECT @n;

15.存储过程的语句：

         条件判断语句：

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 $

           循环语句：

DELIMITER $
CREATE PROCEDURE pro_testwhile(IN num INT,OUT SUM INT)
BEGIN
	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 SUM=result;
END $
-- 调用方法求和
CALL pro_testwhile(153,@n);
SELECT @n;

16.触发器（不是数电= =）：当往员工表插入，删除，修改一条数据时，同时往日志表记录下来，就要使用触发器

-- 创建员工日志表
CREATE TABLE emp_log(
id INT PRIMARY KEY AUTO_INCREMENT,
DO VARCHAR(50)
);

-- 创建增加触发器
CREATE TRIGGER tri_empadd AFTER INSERT ON employee FOR EACH ROW
	INSERT INTO emp_log(DO)VALUES('增加了一条数据');
-- 创建修改触发器
CREATE TRIGGER tri_empupd AFTER UPDATE ON employee FOR EACH ROW
	INSERT INTO emp_log(DO)VALUES('修改了一条数据');
-- 创建删除触发器
CREATE TRIGGER tri_empdel AFTER DELETE ON employee FOR EACH ROW
	INSERT INTO emp_log(DO)VALUES('删除了一条数据');
INSERT INTO employee(NAME,idDept)VALUES('qwe',3);
UPDATE employee SET NAME='saber'WHERE id=2;
DELETE FROM employee WHERE id =5;

SELECT *FROM employee;
SELECT *FROM emp_log;