MySQL小记——约束、多表查询网站首页 技术交流

类型	字节	最小值	最大值
		(带符号的/无符号的)	(带符号的/无符号的)
TINYINT	1	-128	127
		0	255
SMALLINT	2	-32768	32767
		0	65535
MEDIUMINT	3	-8388608	8388607
		0	16777215
INT	4	-2147483648	2147483647
		0	4294967295
BIGINT	8	-9223372036854775808	9223372036854775807
		0	18446744073709551615

CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT, -- 主键自增长
sname VARCHAR(32),
sage TINYINT UNSIGNED -- 0-255
);

外键约束之一对多

保证了数据的有效性和完整性

一般在多表方，添加外键约束，去关联一表的主键。

-- 创建用户表
   CREATE TABLE USER(
       id INT PRIMARY KEY AUTO_INCREMENT,
       username VARCHAR(20)
   )

   -- 创建订单表
   CREATE TABLE orders(
       id INT PRIMARY KEY AUTO_INCREMENT,
       totalprice DOUBLE,
       user_id INT -- 外键
   );

添加外键方式一：

在创建表时，添加主键

foreign key(uid) references user(id);

添加主键方式二：

改变表

alter table orders add foreign key(uid) references user(id);

外键约束特点：

1.从表不能添加数据

2.主表不能删除数据

正确的删除操作是：先删除从表数据，再删除主表数据

级联更新和级联删除

通常和外键约束一起使用

级联更新：on update cascade

级联删除：on delete cascade

外键约束之多对多

引入一张中间表,存放两张表的主键，一般会将这两个字段设置为联合主键，这样就可以将多对多

的关系拆分成两个一对多了，为了保证数据的有效性和完整性，需要在中间表上添加两个外键约束

例：

订单表和商品表为多对多关系

-- 创建订单表
   CREATE TABLE orders(
oid INT PRIMARY KEY AUTO_INCREMENT,
       totalprice DOUBLE,
       user_id INT -- 外键
   );

-- 创建商品表
   create table product(
pid int primary key auto_increment,
       name varchar(20),
       price double
   );

-- 创建中间表
   create table orderitem(
ooid int,
ppid int
   );

-- 添加外键约束
   alter table orderitem add foreign key(ooid) references orders(oid);
   alter table orderitem add foreign key(ppid) references product(pid);

多表查询

多张表关联起来进行查询

若没有进行关联进行查询：

SELECT user.*,orders.* FROM USER,orders

则查询出来的数据按照笛卡尔积顺序排布

表1：

表2：

查询结果：

id   username   id   price   user_id
3   张三   1   1314   3
4   李四   1   1314   3
5   王五   1   1314   3
6   赵六   1   1314   3
3   张三   2   1314   3
4   李四   2   1314   3
5   王五   2   1314   3
6   赵六   2   1314   3
3   张三   3   15   4
4   李四   3   15   4
5   王五   3   15   4
6   赵六   3   15   4
3   张三   4   315   5
4   李四   4   315   5
5   王五   4   315   5
6   赵六   4   315   5
3   张三   5   1014   N
4   李四   5   1014   N
5   王五   5   1014   N
6   赵六   5   1014   N

内连接

添加条件 where

-- 查询所有用户的所有订单。

SELECT user.*,orders.* FROM USER,orders WHERE user.`id`=orders.`user_id`;

-- 查询张三用户的所有订单。

SELECT user.*,orders.* FROM USER,orders WHERE user.`id`=orders.`user_id` AND user.`username`='张三';

内连接写法：

1.隐式内连接

如上所写

2.显式内连接 inner join on

SELECT user.*,orders.* FROM USER INNER JOIN orders ON user.`id`=orders.`user_id`;

外连接

左外连接

-- left关键字分左右，left左边的这张表中的数据，会全部展示出来，在右边没有对应的数据以null展示

内连接查询结果：

左外连接：

SELECT user.*,orders.* FROM USER LEFT OUTER JOIN orders ON user.`id`=orders.`user_id`;

查询结果：

右外连接

查询所有订单信息，以及订单信息对应的用户信息，要求订单信息全部展示出来，订单没有对应的

用户以null展示

-- 以 RIGHT 分左右，右边的表中的数据，全部展示出来，在左边没有对应的以null展示。

SELECT orders.*,user.* FROM USER RIGHT OUTER JOIN orders ON orders.`user_id`=user.`id`;

查询结果：

注意：多表查询常给表起别名

SELECT o.*,u.* FROM orders AS o LEFT OUTER JOIN USER AS u ON u.`id`=o.`user_id`;

as可省略不写

子查询

简而言之，就是查询里面再套一层查询

查看用户为张三的订单详情

SELECT orders.* FROM orders WHERE user_id=(SELECT user.id FROM USER WHERE user.`username`='张三');

自查询

-- 查询这个员工的上司

-- 假设有两张表一张员工表,一张老板表,如果员工的老板号=老板的员工号就表示这个员工是另外一个员工的老板

SELECT a.ename AS 员工姓名,b.ename AS 老板姓名 FROM emp a,emp b WHERE a.mgr=b.empno;

case when语句

根据不同情况做不同的事情

SELECT ename,job,sal 涨之前,
CASE job
WHEN 'MANAGER'
THEN sal + 1000
WHEN 'CLERK'
THEN sal + 800
WHEN 'ANALYST'
THEN sal + 500
ELSE sal + 300
END AS '涨之后'
FROM
emp ;