Mysql——数据库约束和加简单查询

数据库中的约束

在创建表格的过程中可以给某些字段追加约束条件

非空约束 NOT NULL

NK

create table t_user (

id int(3) not null,

username varchar(10),

password varchar(15)

);

唯一约束 UNIQUE

UK

create table t_user (

id int(3) not null,

username varchar(10) unique,

password varchar(15)

);

create table t_user (

id int(3) not null,

username varchar(10),

password varchar(15),

constraint UK_username unique(username)

);

主键约束 PRIMARY KEY

主键（PRIMARY KEY），也称“主键约束”。

MySQL主键约束是一个列或者多个列的组合，其值能唯一地标识表中的每一行。这样的一列或多列称为表的主键，通过它可以强制表的实体完整性。主键主要是用于其他表的外键关联，以及本记录的修改与删除。

主键（PRIMARY KEY）是 MySQL 中使用最为频繁的约束。一般情况下，为了便于 DBMS 更快的查找到表中的记录，都会在表中设置一个主键。

PK   效果上 == 非空约束+唯一约束

create table t_user (

id int(3),

username varchar(10),

password varchar(15),

constraint pk_id primary key(id)

);

联合主键

MYSQL是一个关系型数据库管理系统，其中联合主键是一种非常重要的数据库设计概念。在MYSQL中，联合主键是指使用多个列值来唯一标识一个记录的主键。一个联合主键可能包含两个或更多个字段，而这些字段一起组成了一个唯一标识符。这样可以保证在数据表中每个记录都有唯一的标识。

外键约束 FOREIGN KEY

一对一  一对多  多对多

FK  用到两张表格

t_user （一）

t_book （多）

关系维护在（多）方

uid	Username	password
1	Lufei	lufei
2	Qiaoba	qiaoba

bid	Bookname	author	u_id（外键）
1	笑傲江湖	金庸	1
2	神雕侠侣	金庸	2
3	鹿鼎记	金庸	2
4	连城诀	金庸	2

创建过程中分两步进行：

1. 指定哪一个字段是外键  foreign key(u_id)
2. 外键需要引用哪一个表格的字段  references t_user(id)

create table t_user (

id int(3),

username varchar(10),

password varchar(15),

constraint pk_id primary key(id)

);

create table t_book (

bid int(3),

bookname varchar(6),

author varchar(3),

u_id int(3),

constraint pk_bid primary key(bid),

constraint fk_u_id foreign key(u_id) references t_user(id)

);

一级菜单   二级菜单     自关联

设计一张菜单表：

id	name	url	oneid
1	用户管理	XXX
2	用户添加	XXX	1
3	用户删除	XXX	1
4	用户查询	XXX	1
5	用户修改	XXX	1
6	商品管理	XXX
7	商品上架	XXX	6
8	商品下架	XXX	6

create table t_menu (

id int(3),

name varchar(10),

url varchar(20),

oneid int(3),

constraint pk_id primary key(id),

constraint fk_oneid foreign key(oneid) references t_menu(id)

);

外键状态下的级联操作：

级联删除谨慎使用

create table t_book (

bid int(3),

bookname varchar(6),

ahthor varchar(3),

u_id int(3),

constraint pk_bid primary key(bid),

constraint fk_u_id foreign key(u_id) references t_user(id) on delete cascade

);

create table t_book (

bid int(3),

bookname varchar(6),

ahthor varchar(3),

u_id int(3),

constraint pk_bid primary key(bid),

constraint fk_u_id foreign key(u_id) references t_user(id) on delete set null

);

检查约束 CHECK

在一个范围中间

create table test32(

id number primary key,

age number check(age>0 and age<120)

);

设计表需要注意的东西   数据库三范式

• 1. 1. 第一范式(1NF)
• 第一范式主要是保证数据表中的每一个字段的值必须具有原子性，也就是数据表中的每个字段的值是不可再拆分的最小数据单元
  1. 1. 第二范式(2NF）

• 第二范式要求在满足第一范式的基础上，还要满足数据表里的每一条数据记录，都是可唯一标识的，而且所有的非主键字段，都必须完全依赖主键，不能只依赖主键的一部分。
  1. 1. 第三范式(3NF）

• 第三范式建立在已经满足第二范式的基础上
• 数据表中的每一个非主键字段都和主键字段直接相关

查询机制

emp员工表

字段	描述	类型
empno	员工编号	mediumint
ename	员工姓名	varchar
job	职位	varchar
mgr	领导编号	mediumint
hiredate	入职日期	date
sal	工资	decimal
comm	奖金	decimal
deptno	部门编号	mediumint

dept部门表

字段	描述	类型
deptno	部门编号	mediumint
dname	部门名称	varchar
loc	部门位置	varchar

salgrade工资等级

字段	描述	类型
grade	等级	mediumint
losal	最低工资	decimal
hisal	最高工资	decimal

简单查询

查询所有员工信息

SELECT * from emp;

查询所有并起别名

SELECT empno '员工编号',ename '员工姓名',job '职位',mgr '领导编号' from emp;

SELECT e.empno,e.job,e.sal from emp e;

查询公司中的职位

SELECT  DISTINCT  job from emp;

DISTINCT ：去重关键字

限定查询

SELECT * FROM 表名称 [限定条件] Where  >  <  >=  <=  !=   between...and...  and   or  is null   in  like  ...

查询工资高于1500的所有员工信息

SELECT * from emp WHERE sal > 1500;

查询工资高于1500低于3000的所有员工信息

SELECT * from emp WHERE sal >= 1500 and sal <= 3000;

//between...and...取的区间值

SELECT * from emp WHERE sal BETWEEN 1500 and 3000;

查询没有奖金的所有员工信息  or或者

SELECT * from emp where comm = 0 or comm is NULL;

查询有奖金的员工信息

SELECT * from emp where comm > 0;

查询员工姓名中带有S的员工信息

SELECT * from emp where ename like '%S%';

模糊匹配：

%:占位符 代表0个或者多个字符

_：占位符  代表一个字符

查询入职日期在1981年的员工

SELECT * from emp where hiredate BETWEEN '1981-01-01' and '1981-12-31';

SELECT * from emp where hiredate LIKE '%1981%';

查询员工编号在7499  7521  7566 in包含

SELECT * from emp where empno in (7499,7521,7566);

排序查询

SELECT * FROM 表名称 [限定条件][排序条件] Order by 字段 升序/降序    升序 asc 降序 desc

根据工资由高到底进行排序查询

SELECT * from emp order by sal desc;

如果工资一样，那么按照入职日期升序

SELECT * from emp order by sal desc,hiredate asc;