comment: 在sql语句的后面表示，注释的字段
primary key == 主键 等价于 唯一 (UNIQUE) 且 非空 (NOT NULL)
主键约束的相关：
主键约束：就是一种约束。
主键字段：该字段上添加了主键约束，这样的字段叫做：主键字段
主键值：主键字段中的每一个值都叫做：主键值 primary key
auto_increment key 主键自动增长
mysql中的unsigned 既为非负数，用此类型可以增加数据长度!
例如如果 tinyint最大是127，那 tinyint unsigned 最大 就可以到 127 * 2
unsigned 属性只针对整型， 而binary属性只用于char 和varchar
表的关系:
一对一: 用户 和用户详情
实现方式: 在任意一方通过加入外键,关联另一方主键按,并且设置为外键唯一
一对多:部门和员工
实现方式: 在多的一方建立外键,指向一的一方的主键
多对多: 订单表和商品表
实现方式：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键
外键：
外键约束：
[CONSTRAINT] [外键名称] FOREIGN KEY(外键列名) REFERENCES 主表(主表列名)
-- 创建表时添加外键约束
CREATE TABLE 表名(列名 数据类型,…[CONSTRAINT] [外键名称] FOREIGN KEY(外键列名) REFERENCES 主表(主表列名) ); -- 1.创建表添加外键实例-- 部门表
CREATE TABLE dept(id int primary key auto_increment,dep_name varchar(20),addr varchar(20)
);
-- 员工表
CREATE TABLE emp(id int primary key auto_increment,name varchar(20),age int,dep_id int,-- 添加外键 dep_id,关联 dept 表的id主键CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES dept(id)
);添加数据
-- 添加 2 个部门
insert into dept(dep_name,addr) values
('研发部','广州'),('销售部', '深圳');-- 添加员工,dep_id 表示员工所在的部门
INSERT INTO emp (NAME, age, dep_id) VALUES
('张三', 20, 1),
('李四', 20, 1),
('王五', 20, 1),
('赵六', 20, 2),
('孙七', 22, 2),
('周八', 18, 2);-- 建完表后添加外键约束
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);-- 2.建完表添加外键实例
alter table emp add CONSTRAINT fk_emp_dept FOREIGN key(dep_id) REFERENCES dept(id);删除外键约束ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;-- 3.删除外键实例alter table emp drop FOREIGN key fk_emp_dept;
CREATE TABLE IF NOT EXISTS `xx_user`(
`id` INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户编号',
`username` VARCHAR(20) NOT NULL UNIQUE COMMENT '用户名',
`password` CHAR(32) NOT NULL COMMENT '密码',
`email` VARCHAR(50) NOT NULL UNIQUE COMMENT '邮箱',
`age` TINYINT UNSIGNED NOT NULL DEFAULT 18 COMMENT '年龄',
`sex` ENUM('man','woman','baomi') NOT NULL DEFAULT 'baomi' COMMENT '性别',
`tel` CHAR(11) NOT NULL UNIQUE COMMENT '电话',
`addr` VARCHAR(50) NOT NULL DEFAULT 'beijing' COMMENT '地址',
`card` CHAR(18) NOT NULL UNIQUE COMMENT '身份证号',
`married` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '0代表未结婚，1代表已结婚',
`salary` FLOAT(8,2) NOT NULL DEFAULT 0 COMMENT '薪水'
)ENGINE=INNODB DEFAULT CHARSET=UTF8;用户表 角色表 权限表 用户角色关系表 角色权限关系表
1.用户表
CREATE TABLE `t_user` (`id` varchar(40) NOT NULL,`username` varchar(20) NOT NULL,PRIMARY KEY (`id`));2.角色表
CREATE TABLE `t_role` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(20) NOT NULL,`description` varchar(255) DEFAULT NULL,PRIMARY KEY (`id`));3.权限表
CREATE TABLE `t_permission` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(20) NOT NULL,`description` varchar(255) DEFAULT NULL,PRIMARY KEY (`id`));4.用户角色关系表
CREATE TABLE `user_role` (`id` int(11) NOT NULL AUTO_INCREMENT,`user_id` varchar(40) NOT NULL,`role_id` int(11) NOT NULL,PRIMARY KEY (`id`),CONSTRAINT `fk_user_role_t_role_1` FOREIGN KEY (`role_id`) REFERENCES `t_role` (`id`),
CONSTRAINT `fk_user_role_t_user_1` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`id`)
) ;5、角色权限关系表
CREATE TABLE `role_permission` (`id` int(11) NOT NULL AUTO_INCREMENT,`role_id` int(11) NOT NULL,`permission_id` int(11) NOT NULL,PRIMARY KEY (`id`),CONSTRAINT `fk_role_permission_t_permission_1` FOREIGN KEY (`permission_id`) REFERENCES `t_permission` (`id`), CONSTRAINT `fk_role_permission_t_role_1` FOREIGN KEY (`role_id`) REFERENCES `t_role` (`id`));
6、测试数据
INSERT INTO `role_permission` VALUES ('1','1','1'), ('2','2','2'), ('3','3','3');INSERT INTO `t_permission` VALUES ('1','小说收藏',NULL), ('2','小说发布',NULL), ('3','广告发布',NULL);INSERT INTO `t_role` VALUES ('1','读者',NULL), ('2','作者',NULL), ('3','管理员',NULL);INSERT INTO `t_user` VALUES ('u1','reader'), ('u11','reader1'), ('u2','author'), ('u22','author2'), ('u3','admin'), ('u33','admin2');INSERT INTO `user_role` VALUES ('1','u1','1'), ('2','u2','2'), ('3','u3','3'), ('4','u11','1'), ('5','u22','2'), ('6','u33','3');小说网站，用户表的设计。
用户有着“读者”，“作者”和“管理员”角色，角色有不同权限，如小说收藏，小说发布和广告发布
假定，用户和角色是一对一关系，即一个用户只有一个角色；角色和用户的关系是一对多关系，一个角色对应着多个用户。(方便后面对应英文单词直观反应着关系，如看到reader就是表示读者角色)
角色和权限的关系是多对多关系。即一个角色有着多种权限，同样，一个权限可以分给不同角色。
二、多对多查询
1、查询拥有某角色的用户信息
SELECTu.id,u.usernameFROMt_user u,t_role r,user_role urWHEREr.id=1 AND r.id=ur.role_id AND ur.user_id=u.id;2、查询某用户的对应的角色。
SELECTu.id,u.username,r.`name` role_nameFROMt_user u,t_role r,user_role urWHEREu.username LIKE 'a%' AND u.id=ur.user_id AND ur.role_id=r.id;3、查询拥有某权限的角色
SELECT p.`name`,r.`name`FROMt_permission p,role_permission rp,t_role rWHEREp.`name`='小说发布' AND p.id=rp.permission_id AND rp.role_id=r.id;4、查询某角色拥有的权限。
SELECT r.`name`,p.`name`FROMt_permission p,role_permission rp,t_role rWHEREr.`name`='作者' AND r.id=rp.role_id AND rp.permission_id=p.id;5、查询某用户拥有的权限。
这里用户和角色是一对一关系，通过先查询用户的角色，再查询权限。（单行单例子查询）
SELECT p.`name`FROMt_permission p,role_permission rp,t_role rWHEREr.id=rp.role_id AND rp.permission_id=p.id AND r.idIN(SELECT r.idFROMt_user u,t_role r,user_role urWHEREu.username ='author' AND u.id=ur.user_id AND ur.role_id=r.id);6.查询拥有某权限的用户
权限与角色是多对多关系，角色和用户是一对一关系。
这个是查询是多行单列子查询
SELECTu.id,u.usernameFROMt_user u,t_role r,user_role urWHEREr.id=ur.role_id AND ur.user_id=u.id AND r.idIN(SELECT r.idFROMt_permission p,role_permission rp,t_role rWHEREp.`name`='小说发布' AND p.id=rp.permission_id AND rp.role_id=r.id);