数据库的完整性
用来保证存放到数据库中的数据是有效的,即数据的有效性和准确性确保数据的完整性 = 在创建表时给表中添加约束。完整性的分类：
- 实体完整性(行完整性): - 域完整性(列完整性): - 引用完整性(关联表完整性): 主键约束：primary key 唯一约束：unique [key] 非空约束：not null 默认约束：default 自动增长：auto_increment 外键约束: foreign key建议这些约束应该在创建表的时候设置 ，多个约束条件之间使用空格间隔
实体完整性
实体：即表中的一行(一条记录)代表一个实体（entity） 实体完整性的作用：标识每一行数据不重复。 约束类型： 主键约束（primary key） 唯一约束(unique)自动增长列(auto_increment)
主键约束（primary key）
注：每个表中要有一个主键。 特点：数据唯一，且不能为null
唯一约束(unique)
特点：数据不能重复。
自动增长列(auto_increment)
自增长 数据类型为数值的字段，可以通过使用auto_increment 来完成自增长的设置 主键自增长，当数据插入时，不进行主键的数据插入，则主键的内容会通过自动增长变量来赋值 在主键约束关键字后面加入AUTO_INCREMENT 约束的作用是对表中的数据进行限制，可以更好的保证数据的准确性，有效性和完整性 -- 主键约束 primary key -- 不允许空且不能重复 -- 非空约束 not null -- 限制一个字段的数据不能为空 -- 唯一约束 unique -- 这一列的数据不能重复，前提是不为空
-- 创建person表，三个字段，id 主键自增长，name 唯一，age 非空CREATE TABLE PERSON(ID INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(5) UNIQUE,AGE INT NOT NULL);
数据类型
常用数据类型
int 整型数字
bigint 大型整型数字
double 浮点型数字 score double(5,2);小数点前5位，小数点后2位
date 日期 只有年月日 yyyy-MM-dd
datetime 日期加时间 年月日时分秒 yyyy-MM-dd HH:mm:ss
timestamp 时间戳 13位的数字 从1970年1-1 到现在的毫秒数
varchar 可变长度字符串 4000是数据库存储最大的值 name varchar(20); name 最大使用20个字符
如果有很大的数据不会存储数据库里，把它作为文件存在一个位置，把文件的地址存在数据库里
数值类型
MySQL 支持所有标准 SQL 数值数据类型。
这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL 和 NUMERIC)，以及近似数值数据类型(FLOAT、REAL 和 DOUBLE PRECISION)。
关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。
BIT数据类型保存位字段值，并且支持 MyISAM、MEMORY、InnoDB 和 BDB表。
作为 SQL 标准的扩展，MySQL 也支持整数类型 TINYINT、MEDIUMINT 和 BIGINT。下面的表显示了需要的每个整数类型的存储和范围。
日期和时间类型
表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。
每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。
字符串类型
字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。
注意：char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。
CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。
域完整性
域完整性的作用：限制此单元格的数据正确，不对照此列的其它单元格比较
域代表当前单元格
域完整性约束：数据类型 非空约束（not null） 默认值约束(default) 列级约束 -- 非空约束 not null -- 创建表格或修改表格时，在列名声明的后边加入约束关键字 -- 默认值 -- 创建表格或修改表格时，在列名声明的后边加入default 值
CREATE TABLE PEOPLE(ID INT NOT NULL,NAME VARCHAR(20) DEFAULT "嘿嘿");
引用完整性
外键约束：FOREIGN KEY
creat table(字段列表，外键字段是存别的表的主键值的列constraint 约束名 foreign key(外键字段) references 主表名(字段));
CREATE TABLE SA_DEPT(ID INT,NAME VARCHAR(20),CONSTRAINT SA_DEPT_ID PRIMARY KEY(ID));INSERT INTO SA_DEPT VALUES(1,"研发部"),(2,"测试部"),(3,"公关部");-- ---------------------------------------CREATE TABLE SA_EMP(ID INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20),SALARY INT,DEPT_ID INT,CONSTRAINT S_EMP_DEPT_ID_FK FOREIGN KEY(DEPT_ID) REFERENCES SA_DEPT(ID));ALTER TABLE SA_EMP DROP FOREIGN KEY S_EMP_DEPT_ID_FK;DROP TABLE SA_EMP;DROP TABLE SA_DEPT;INSERT INTO SA_EMP(NAME,SALARY,DEPT_ID) VALUES("张三",10000,3),("李四",90000,2),("王五",18000,3),("赵一",16000,12);TRUNCATE TABLE S_EMP;SELECT * FROM SA_DEPT;SELECT * FROM SA_EMP;
多表查询
多个表之间是有关系的，那么关系靠谁来维护? 多表约束：外键列多表的关系
一对多/多对一关系客户和订单，分类和商品，部门和员工.一对多建表原则：在多的一方创建一个字段，字段作为外键指向一的一方的主键.多对多关系学生和课程多对多关系建表原则：需要创建第三张表,中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键.一对一关系在实际的开发中应用不多.因为一对一可以创建成一张表. 两种建表原则：唯一外键对应：假设一对一是一个一对多的关系，在多的一方创建一个外键指向一的一方的主键，将外键设置为unique.主键对应：让一对一的双方的主键进行建立关系.SELECT 列名列表 FROM 列名列表;
笛卡尔积：当进行多表查询时，多表之间的行数据会自然匹配，查询的结果是自然匹配的乘积
多表查询必须去除笛卡尔积产生的无用数据
因为多表查询是通过where条件消除无用数据，所以当迪科尔基过大时会对查询性能有较大的影响，尽量避免三表或三表以上的多表查询
隐式内连接：通过where条件完成筛出笛卡尔积
显示内连接使用固定的语法格式
SELECT * FROM S_EMP,S_DEPT ;SELECT * FROM S_EMP,S_DEPT WHERE S_EMP.DEPT_ID=S_DEPT.ID;-- 查询员工姓名，工资，部门名称-- 隐式内连接SELECT S_EMP.NAME,SALARY,S_DEPT.NAME FROM S_EMP, S_DEPT WHERE S_EMP.DEPT_ID=S_DEPT.ID;SELECT E.NAME,SALARY,D.NAME FROM S_EMP E, S_DEPT D WHERE E.DEPT_ID=D.ID;-- 显式内连接 select 字段列表 from 表名1 [inner] join 表名2 ON 消除笛卡尔积的条件;SELECT S_EMP.NAME,SALARY,S_DEPT.NAME FROM S_EMP INNER JOIN S_DEPT ON S_EMP.DEPT_ID=S_DEPT.ID;-- 通过内连接，实现自连接-- 有时候存在一些特殊的表格，包含至少两层含义-- select 列名列表 from 表名 别名1, 表名 别名2 where 条件；-- 查询管理者的姓名，工资，职务，通过员工的管理者和管理者的编号SELECT DISTINCT M.ENAME,M.SAL,M.JOB FROM EMP E,EMP M WHERE E.MGR=M.EMPNO;SELECT * FROM salgrade;-- 以上都是等值连接，有些条件是需要使用非等值连接的-- 查询员工姓名，薪资，职务，薪资等级SELECT ENAME,SAL,JOB,GRADE FROM EMP,SALGRADE WHERE SAL>LOSAL AND SAL<HISAL ORDER BY GRADE;
外连接 左外连接/右外连接
左外连接语法
select 字段列表 from 表名1(基表) left [outer] join 表名2(参考表) on 条件;
右外连接语法
select 字段列表 from 表名1(参考表) right [outer] join 表名2(基表) on 条件;
基表
-- 基表的内容会全部查询到
-- 参考表
-- 在查询时，只有多表查询满足查询条件的数据才会被查询到，不满足条件的使用null数据来跟基表数据匹配
普通查询和外连接查询的区别
SELECT * FROM S_EMP,S_DEPT;SELECT * FROM S_EMP,S_DEPT WHERE S_EMP.`DEPT_ID`=S_DEPT.`ID`;
左外连接语法 select 字段列表 from 表名1(基表) left [outer] join 表名2(参考表) on 条件;SELECT * FROM S_EMP LEFT OUTER JOIN S_DEPT ON S_EMP.`DEPT_ID`=S_DEPT.`ID`;右外连接语法 select 字段列表 from 表名1(参考表) right [outer] join 表名2(基表) on 条件;SELECT * FROM S_DEPT RIGHT OUTER JOIN S_EMP ON S_EMP.`DEPT_ID`=S_DEPT.`ID`;
子查询
一个select语句中包含另一个完整的select语句。
子查询就是嵌套查询，即SELECT中包含SELECT，如果一条语句中存在两个，或两个以上SELECT，那么 就是子查询语句了。
子查询有两种不同的查询结果，一是查询结果是单行的，二是查询结果是多行的
-- 如果查询结果是单行单列的，可以用于查询条件
-- 将子查询嵌套在查询条件中
-- 格式：
select 字段列表 from 表名 where 条件(SQL语句);
SELECT ENAME FROM EMP WHERE sal=(SELECT MAX(SAL) FROM EMP);-- 查询部门编号最大的部门的所有员工信息（ename，sal）SELECT ENAME,SAL FROM EMP WHERE DEPTNO=(SELECT MAX(DEPTNO) FROM EMP);-- 查询大于公司平均薪资的人的ename，salSELECT ENAME,SAL FROM EMP WHERE sal>(SELECT AVG(SAL) FROM EMP);-- 子查询的结果是多行单列。可以用作查询条件，用in使用SELECT * FROM S_EMP WHERE ID IN (SELECT ID FROM S_DEPT WHERE NAME="研发部" OR NAME="公关部");-- 子查询的结果是多行多列的。子查询可以作为一张虚拟表参与查询SELECT * FROM (SELECT * FROM EMP WHERE HIREDATE>"1985-1-1") TABLE1,DEPT WHERE TABLE1.`DEPTNO`=DEPT.`DEPTNO`;
练习题
数据表: 雇员表(employee):雇员编号(empid,主键)，姓名(name)，性别 (sex)，职称(title)，出生日期(birthday)，所属部(depid) 部门表(department):部门编号(depid,主键)，部门名称(depname) 工资表(salary):雇员编号(empid)，基本工资(basesalary)，职务工资(titlesalary)，扣除(deduction) 需求: 1. 修改表结构，在部门表中添加部门简介字段 2. 将李四的职称改为“工程师”,并将她的基本工资改成 2000，职务工资 为 700 3. 删除人事部门的部门记录 4. 查询出每个雇员的雇员编号，实发工资，应发工资 5. 查询姓张且年龄小于 40 的员工记录 6. 查询雇员的雇员编号，姓名，职称，部门名称，实发工资 7. 查询销售部门的雇员姓名，工资 8. 统计各职称的人数 9. 统计各部门的部门名称，实发工资总和，平均工资 10. 查询比销售部门所有员工基本工资都高的雇员姓名-- 雇员表CREATE TABLE employee(empid INT PRIMARY KEY AUTO_INCREMENT, #雇员编号NAME VARCHAR(10),#雇员姓名sex VARCHAR(5),#雇员性别title VARCHAR(10),#雇员职称birthday DATE,#雇员出生日期depid INT #雇员部门id);INSERT INTO employee(NAME,sex,title,birthday,depid) VALUES('张三','男','研究员','2000-01-01',1),('李四','女','工程师','2001-01-01',1),('王五','男','经理','2000-12-01',2),('赵六','男','讲师','1990-12-01',3),('周七','女','助理','1980-12-01',4);INSERT INTO employee(NAME,sex,title,birthday,depid) VALUES('张飞','男','助理','1990-12-01',4);CREATE TABLE department(depid INT PRIMARY KEY AUTO_INCREMENT,#部门编号depname VARCHAR(20)#部门名称);INSERT INTO department(depname) VALUES('人事部'),('技术部'),('销售部'),('公关部');CREATE TABLE salary(empid INT,#雇员idbasesalary DOUBLE,#基本工资titlesalary DOUBLE,#职务工资deduction DOUBLE #扣除);INSERT INTO salary(empid,basesalary,titlesalary,deduction) VALUES (1,1000,300,100),(2,1300,200,100),(3,3000,100,100),(4,2400,400,100),(5,4000,100,200);INSERT INTO salary(empid,basesalary,titlesalary,deduction) VALUES (6,7000,200,900);-- 1. 修改表结构，在部门表中添加部门简介字段ALTER TABLE department ADD info TINYTEXT;ALTER TABLE department CHANGE info info VARCHAR(50);SELECT * FROM department-- 2. 将李四的职称改为“工程师”,并将她的基本工资改成 2000，职务工资为 700UPDATE employee,salary SET title='工程师',basesalary=2000,titlesalary=700WHERE employee.`empid`=salary.`empid` AND NAME='李四';SELECT * FROM employee,salary WHERE employee.`empid`=salary.`empid`AND NAME='李四';-- 3. 删除人事部门的部门记录DELETE department,employee,salary FROM department,employee,salaryWHERE department.`depid`=employee.`depid` AND salary.`empid`=employee.`empid`AND department.`depname`='人事部';SELECT * FROM department,employee,salary WHERE department.`depid`=employee.`depid` AND salary.`empid`=employee.`empid`-- 4. 查询出每个雇员的雇员编号，实发工资，应发工资SELECT empid, basesalary+titlesalary '实发工资',basesalary+titlesalary-deduction '应发工资' FROM salary-- 5. 查询姓张且年龄小于 40 的员工记录SELECT * FROM employeeSELECT * FROM employee WHERE employee.`name` LIKE '张%' AND birthday >DATE_ADD(NOW(),INTERVAL -40 YEAR);-- 6. 查询雇员的雇员编号，姓名，职称，部门名称，实发工资SELECT e.`empid`,e.`name`,e.`title`,d.`depname`,s.`basesalary`+s.`titlesalary` '实发工资'FROM employee e,department d,salary s WHERE e.`depid`=d.`depid`AND s.`empid`=e.`empid`-- 7. 查询销售部门的雇员姓名，工资SELECT department.`depname`,employee.`name`,salary.`basesalary`,salary.`titlesalary`FROM department,employee,salary WHEREdepartment.`depid`=employee.`depid` AND employee.`empid`=salary.`empid`AND department.`depname`='销售部'-- 8. 统计各职称的人数SELECT title ,COUNT(*) FROM employee GROUP BY title-- 9. 统计各部门的部门名称，实发工资总和，平均工资SELECT d.depname,SUM(s.`basesalary`+s.`titlesalary`-s.deduction) '实发工资总和',AVG(s.`basesalary`+s.`titlesalary`-s.deduction) '平均工资'FROM employee e,department d,salary sWHERE e.`depid`=d.`depid`AND s.`empid`=e.`empid`GROUP BY d.depname SELECT * FROM employee-- 10. 查询比销售部门所有员工基本工资都高的雇员姓名SELECT NAME FROM employee e , salary s WHERE s.`empid`=e.`empid` ANDs.basesalary>(SELECT MAX(basesalary) FROM salary s,department d ,employee e WHERE e.`depid`=d.`depid`AND s.`empid`=e.`empid`AND d.depname='销售部')
