必备SQL和表关系

必备SQL和表关系

1.必备SQL

创建数据

create table depart(
	  id int(4) not null auto_increment primary key,
    title varchar(20) not null
);

create table info(
	  id int(4) not null auto_increment primary key,
    name varchar(20) not null,
    emall varchar(20) not null,
    age int(3)  not null,
  	depart_id  INT COMMENT '部门id',
    FOREIGN KEY (depart_id) REFERENCES depart(id)
);

1.1 条件

1.1.1 where 条件

-- 查找名称为陈旭的员工信息
select * from info where name = '陈旭'

1.1.2 between and 筛选范围操作符

-- 查找年龄在18-49之间的员工信息
select * from info where age between 18 and 49

1.1.3 and 逻辑运算符，用于结合两个或多个条件，确保这些条件同时成立

-- 查找年龄18 并且部门id是1的员工
select * from info where age = 18 and depart_id = 1

1.1.4 or 逻辑运算符，用于结合两个或多个条件，确保其中至少一个条件为真

-- 查找部门是2或者3的
select * from info where depart_id = 2 or depart_id = 3

1.1.5 in 用于指定条件范围，检查某一列是否在指定的值列表中

-- 查找部门是2或者3的
select * from info where depart_id IN(2,3)

1.1.6 exists 用于检查子查询是否返回任何行的关键字。它常常与 select 语句结合使用

-- 存在返回，不存在返回空
-- exists select * from depart where id=5，去查数据是否存在，如果存在，如果不存在。
select * from info where exists (select * from depart where id=5);
select * from info where not exists (select * from depart where id=5);

1.2 通配符,模糊搜索

• 在 MySQL 中，通配符用于模糊搜索，常见的通配符包括百分号 % 和下划线 _

• 百分号 %： 用于表示零个、一个或多个字符。例如，LIKE 'a%' 表示以字母 "a" 开头的任意字符串。

• 下划线 _： 用于表示一个单一的字符。例如，LIKE '_r%' 表示第二个字符是 "r" 的任意三个字符的字符串

1.2.1 %

-- 模糊查询 name %y% 前后任意字符，中间为y的数据
select * from info where name like "%y%"

1.2.2 _

-- 模糊查询 name 两个字符的 "_y" 第一二字符是y的数据

1.3 排序 order by

1.3.1 升序 asc ，默认是升序

-- 根据年龄升序排序
select * from info order by age asc

1.3.2 倒序 desc

-- 根据年龄倒序排序
select * from info order by age DESC

1.3.3 同时使用 asc和desc

 -- 优先按照age从小到大；如果age相同则按照id从大到小
select * from info order by age asc,id desc

1.4 取部分数据 limit

1.4.1 Limit 取部分数据

-- 获取前5条数据
select * from info limit 5;  

-- 先排序，再获取前3条数据
select * from info order by id desc limit 3;	

1.4.2 limit offset 从xx位置开始取xx条

-- 从位置2开始，向后获取前3数据
select * from info limit 3 offset 2;	

1.5 分组 gorup by

• select * from 表名 group by 分组条件

1.5.1 group by 分组

-- 根据年龄分组并统计每个年龄段有多少人
select age,count(1) from info group by age;

1.5.2 having 是在分组之后进行的过滤操作

select age,count(id) from info where id > 2 group by age having count(id) > 1 order by age desc limit 1;
- 要查询的表info
- 条件 id>2
- 根据age分组
- 对分组后的数据再根据聚合条件过滤 count(id)>1
- 根据age从大到小排序
- 获取第1条

分组注意

• where和group by可以同时使用，但是要注意顺序
• where不能使用聚合函数
• 聚合条件放在having后面

sql执行顺序

    where 
    group by
    having 
    order by
    limit 

1.6 distinct 去重

-- 根据部门去重显示
select distinct depart_id from info

1.7 多表查询

1.7.1 子查询

-- 将一条SQL语句的查询结果加括号当做另外一条SQL语句的查询条件
-- select * from * where (select * from * where * ;);

1.7.2 联表查询

1.7.2.1 左连接

• 左连接，左表所有的数据都展示出来，没有对应的项就用null表示
• info主表，就以info数据为主，depart为辅
• select * from 主表表名 left join 辅表表名 on 条件筛选;

-- 联查 查看每个人属于那个部门
select info.id,info.name,info.emall,depart.title from info left outer join depart on info.depart_id = depart.id;

1.7.2.2 右链接

• 右链接，右表所有的数据都展示出来，没有对应的项就用null表示
• depart主表，，就以depart数据为主，info为辅
• select * from 主表表名 right join 辅表表名 on 条件筛选;

select info.id,info.name,info.emall,depart.title from info right outer join depart on info.depart_id = depart.id;

1.7.2.3 内链接

• 只拼接两张表中共有的数据部分
• 表 inner join 表 on 条件

select * from info inner join depart on info.depart_id=depart.id;

1.7.2.4 全连接

• 左右两表的数据都展示出来

-- 全连接显示depart表的id和title 和info表的id和name
select id,title from depart 
union
select id,name from info;

2.表关系

2.1 单表，单独一张表就可以将信息保存

• QQ用户

id name age gender email phone addr

# 用户表
id name age gender detail_id

# 用户详情表
id email phone addr

2.2 一对多，需要两张表来存储信息，且两张表存在 一对多 或 多对一关系

• 以员工表和部门表来说
  • 员工可不可以只对应一个部门？
    • 默认是可以的
  • 部门是不是可以有多个员工？
    • 部门可以有多个员工
• 总结
  • 部门是一
  • 员工是多
• 一对多关系将外键建立在哪一方？
  • 将外键建在多的这一方

# 员工表
id name age

# 部门表
id dep_name dep_desc emp_id
1 技术部	1
2 技术部	2
2 技术部	2
3 后勤部

# 员工表
id name age dep_id
1 dream 18 1
2 hope 28  1

# 部门表
id dep_name dep_desc
1 技术部
2 后勤部

# 创建被约束的表
create table dep(
	id int(4) primary key auto_increment comment "部门表id",
    dep_name varchar(32) comment "部门名称",
    dep_desc text comment "部门介绍"
);

# 创建外键约束的表，前提是被建立连接的表先创建
create table emp(
	id int(4) primary key auto_increment  comment "员工id",
    name varchar(32) comment "员工名字",
    age int(4) comment "员工年龄",
    dep_id int(4) comment "外键关联部门id",
	foreign key(dep_id) references dep(id)
);

2.3 多对多，需要三张表来存储信息，两张单表 + 关系表，创造出两个单表之间多对多关系

• 以图书和作者为例
  • 一本书可以有多个作者吗？
    • 一本数据可以有多个作者
  • 一个作者可以写多本书吗？
    • 一个作者可以写多本书
• 总结
  • 图书和作者之间的关系就是多对多

# 图书表
id title price author_id

# 作者表
id name gender book_id 

create table book(
  id int primary key auto_increment,
  title varchar(32),
  price float(10,2),
);


create table author(
  id int primary key auto_increment,
  name varchar(32),
  gender enum('male','female','others'),
);


create table author_book(
  id int primary key auto_increment,
  book_id int,
  foreign key(book_id) references book(id),
  author_id int,
  foreign key(author_id) references author(id) 
);

# 图书表
id title price
1    1     1      
2   2     2

# 作者表
id name gender
1   1     1
2   2     2

# 第三张表 ：author_book
id book_id author_id
1     1       1
2     1       2
3     2       1

注意：

在上述的表：一对多的、多对多的 直接用整型存储就可以，因为他们只要存储关联表的主键ID即可。

在开发中往往还会为他们添加一个 外键约束，保证某一个列的值必须是其他表中的特定列已存在的值，例如：author_book.book_id的值必须是 book.id中已存在的值。