约束
概念:对表中的数据进行限定,保证数据的正确性、有效性和完整性。
分类:
主键约束:prinary key
非空约束:not null
唯一约束:unique
外键约束:foreign key
非空约束:not null,某一列的值不能为null;
1.创建表时添加约束
CREAT TABLE stu(
id INT,
NAME VARCHAR(20) NOT NULL --name 为非空
)
2.创建表完后,添加非空约束
ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;
3.删除name的非空约束
ALTER TABLE stu MODIFY NAME VARCHAR(20);
唯一约束:unique,某一列不能重复
-
注意
唯一约束可以有NULL值,但是只能有一条记录为null
-
在创建表时,条件唯一约束
CREAT TABLE stu(
id INT,
phone_number VARCHAR(20) UNIQUE --手机号
);
-
删除唯一约束
ALTER TABLE stu DROP INDEX phone_number;
-
在表创建完后,添加唯一约束
ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;
主键约束:primary key
1.注意
- 含义:非空且唯一
- 一张表只能有一个字段为主键
- 主键就是表中记录的唯一标识
2.在创建表时,添加主键约束
CREAT TABLE stu(
id INT PRIMARY KEY, --给id添加主键约束
name VARCHAR(20)
);
3.删除主键
–错误 ALTER TABLE stu MODIFY id INT;
ALTER TABLE stu DROP PRIMARY KEY;
4.创建完表后,添加主键
ALTER TABLE stu MODIFY id INT PRINMARY KEY;
5.自动增长
1.概念:如果某一列是数值类型的,使用auto_increament 可以来完成值的自动增长
2.创建表时,添加主键约束,并且完成主键自增长
creat table stu(
id int primary key auto_increament, --给id添加主键约束
name varchar(20)
);
3.删除自动增长
ALTER TABLE stu MODIFY id INT;
4.添加自动增长
ALTER TABLE stu MODIFY id INT AUTO_INCREAMENT;
外键约束:foreign key,让表与表产生关系,从而保证数据的正确性。
1.在创建表时,可以添加外键
语法:
creat table 表名(
…
外键列
constraint 外键名称 foriegn key 外键列名称 references 主表名称(主表列名称)
);
2.删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
3.创建表之后,添加外键
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名称)REFERENCES 主表名称(主表列名称);
4.级联操作
1.添加级联操作
语法:ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名称)REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE (ON DELETE CASCADE);
2.分类
1.级联更新:ON UPDATE CASCADE
2.级联删除:ON DELETE CASCADE
数据库的设计
-
多表之间的关系
1.分类
1.一对一(了解):
如:人和身份证
分析:一个人只能有一个身份证,一个身份只能对应一个人
2.一对多(多对一)
如:部门和部员
分析:一个部门有多个员工,一个员工只能对应一个部门
3.多对多
如:学生和课程
分析:一个学生可以选择很多课程,一个课程也可以被很多学生选择
2.实现关系
1.一对多(多对一):
如:部门和员工
实现方式:在多的一方建立外键,指向一的一方的主键。
2.多对多:
如:学生和课程
实现方式:多对多关系实现需要借助第三张中间表。中间表至少包含两个字段,这两个字段作为第三张表的外键,分别指向两张表的主键
3.一对一(了解):
如:人和身份证
实现方式:一对一关系实现,可以在任意一方添加唯一外键指向另一方的主键。
-
数据库设计的范式
概念:设计数据库时,需要遵循的一些规范。要遵循后边的范式要求,必须先遵循前边的所有范式要求
设计关系数据库时,遵从不同的规范要求,设计出合理的关系型数据库,这些不同的规范要求被称为不同的范式,各种范式呈递次规范,越高的范式数据库冗余越小。
关系数据库有六种范式:第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF)和第五范式(5NF,又称完美范式)。
分类:
-
第一范式(1NF):每一列都是不可分割的原子数据项
-
第二范式(2NF):在1NF的基础上,非码属性必须完全依赖于候选码(在1NF基础上消除非主属性对主码的部分函数依赖)
几个概念:
1.函数依赖:A–>B,如果通过A属性(属性组)的值,可以确定唯一B属性的值。则称B依赖于A
例如:学号–>姓名 (学号,课程名称)–>分数
2.完全函数依赖:A-->B,如果A是一个属性组,则B属性值得确定需要依赖于A属性组中所有的属性值
例如:(学号,课程名称)–>分数
3.部分函数依赖:A–>B,如果A是一个属性组,则B属性值得确定只需要依赖于A属性组中某一些值即可
例如:(学号,课程名称)–>姓名
4.传递函数依赖:A–>B,B–>C,如果A属性(属性组)的值,可以确定唯一B属性的值,在通过B属性(属性组)的值可以确定唯一C属性的值,则称C传递函数依赖于A
例如:学号–>系名,系名–>系主任
5.码 :如果在一张表中,一个属性或属性组,被其他所有属性所完全依赖,则称这个属性(属性组)为该表的码
例如:该表中的码为:(学号,课程名称)
*主属性:码属性组中的所有属性
*非主属性:除过码属性组的属性
-
第三范式(3NF):在2NF基础上,任何非主属性不依赖于其它非主属性(在2NF基础上消除传递依赖)
数据库的备份和还原
-
命令行
语法:
*备份:mysqlddump -u用户名 -p密码 数据库的名称>保存的路径
*还原:
1.登录数据库
2.创建数据库
3.使用数据库
4.执行文件。source文件路径
-
图形化工具
多表查询:
查询语法:
select
列名列表
from
表名列表
where…
笛卡尔积:
*有两个集合A,B,取这两个集合的所有组成情况
*要完成多表查询,需要消除无用的数据
多表查询的分类:
-
内连接查询:
1.隐式内连接:使用where条件消除无用数据
例子:
SELECT * FROM emp,dept WHERE emp.‘dept_id’ = dept.‘id’;
SELECT emp.name,emp.gender,dept.name FROM emp,dept WHERE emp.‘dept_id’ = dept.‘id’;
SELECT
t1.name,
t1.gender,
t2.name,
FROM
emp t1,
dept t2
WHERE
t1.‘dept_id’ = t2.‘id’;
2.显式内连接:
*语法:select 字段列表 from 表名1 [inner] join 表名2 on 条件
*例如:
*SELECT * FROM emp INNER JOIN dept ON emp.‘dept_id’ = dept.‘id’;
*SELECT * FROM emp JOIN dept ON emp.‘dept_id’ = dept.‘id’;
3.内连接查询:
从哪些表中查询数据
条件是什么
查询哪些字段
-
外连接查询:
1.左外连接:
*语法:select 字段列表 from 表1 left [outer] join 表2 on 条件;
*查询的是左表所有数据以及其交集部分
2.右外连接:
*语法:select 字段列表 from 表1 right [outer] join 表2 on 条件;
*查询的是右表所有数据以及其交集部分
-
子查询:
*概念:查询中嵌套查询,称嵌套查询为子查询。
–查询最高的工资
1.查询最高的工资是多少 9000
SELECT MAX(salary) FROM emp;
2.查询员工信息,并且工资等于9000的
SELECT * FROM emp WHERE emp.‘salary’ = 9000;
一条sql就完成这个操作。子查询
SELECT * FROM emp WHERE emp.‘salary’ = (SELECT MAX(salary) FROM emp);
*子查询的不同情况
1.子查询的结果是单行单列的
*子查询可以作为条件,使用运算符去判断。运算符:> >= < <= =
*查询员工工资小于平均工资的人
SELECT * FROM emp WHERE emp.salary < (SELECT AVG (salary) FROM emp);
2.子查询的结果是多行单列的
*子查询可以作为条件,使用运算符in来判断
–查询‘财务部’和‘市场部’所有的员工信息
SELECT id FROM dept WHERE NAME = ‘财务部’ OR NAME = ‘市场部’;
SELECT * FROM emp WHERE dept_id = 3 OR dept_id = 2;
–子查询
SELECT * FROM emp WHERE dept_id IN(SELECT id FROM dept WHERE NAME = ‘财务部’ OR NAME = ‘市场部’);
3.子查询的结果是多行多列的
子查询可以作为一张虚拟表参与查询
–查询员工的入职日期是2011-11-11日之后的员工信息和部门信息
–子查询
SELECT * FROM dept t1,(SELECT * FROM emp WHERE emp.‘join_date’>‘2011-11-11’) t2 WHERE t1.id = t2.dept_id;
–普通内连接
SELECT * FROM emp t1,dept t2 WHERE t1.‘dept_id’ = t2.‘id’ AND t1.‘join_date’>‘2011-11-11’;
*多表查询练习
DCL:
*SQL分类:
- DDL:操作数据库和表
- DML:增删改表中数据
- DQL:查询表中数据
- DCL:管理用户,授权
*DBA:数据库管理员
*DCL:管理用户,授权
-
管理用户
1.添加用户:
*语法:CREATE USER ‘用户名’@‘主机名’ IDENTIFIED BY ‘密码’;
2.删除用户:
*语法:DROP USER ‘用户名’@‘主机名’;
3.修改用户密码
UPDATE USER SET PASSWORD = PASSWORD(‘新密码’) WHERE USER = ‘用户名’;
UPDATE USER SET PASSWORD = PASSWORD(‘abc’) WHERE USER = ‘lisi’;
SET PASSWORD FOR ‘用户名’@‘主机名’ = PASSWORD(‘新密码’);
SET PASSWORD FOR ‘root’@‘localhost’ = PASSWORD(‘123’);
*mysql中忘记了root用户的密码?
1.cmd --> net stop mysql 通知mysql服务
2.使用无验证方式启动mysql服务:mysql --skip-grant-tables
3.打开新的cmd窗口,直接输入mysql命令,敲回车。就可以登陆成功
4.use mysql
5.update user set password = password(‘你的新密码’) where user = ‘root’;
6.关闭两个窗口
7.打开任务管理器,手动结束mysql.exe的进程
8.启动mysql服务
9.使用新密码登录
4.查询用户
--1.切换到mysql数据库
USE myql;
--2.查询user表
SELECT * FROM USER;
*通配符:%表示可以在任意主机使用用户登录数据库
-
权限管理:
1.查询权限:
--查询权限
SHOW GRANTS FOR ‘用户名’@‘主机名’;
SHOW GRANTS FOR ‘lisi’@‘%’;
2.授予权限
--授予权限
GRANT 权限列表 ON 数据库名.表名 to ‘用户名’@‘主机名’;
--给张三用户授予所有权限,在任意数据库任意表上
GRANT ALL ON . TO ‘zhangsan’@‘localhost’;
3.撤销权限:
--撤销权限
revoke 权限列表 on 数据库名.表名 from ‘用户名’@‘主机名’;
REVOKE UPDATE ON db3.‘account’ FROM ‘lisi’@‘%’;