day26-必备SQL和表关系及授权

说明：day25-MySQL入门 可以参考另一篇文章 MySQL数据库

原文件下载路径：
【数据库开发】必备SQL语句、表关系设计与用户授权：项目开发核心技能详解

day26 必备SQL和表关系及授权

课程目标：掌握开发中最常见的SQL语句和表关系及授权相关知识点。

课程概要：

• 必备SQL（8个必备）

• 表关系

• 授权

1. 必备SQL语句

上一节讲解了最基础SQL语句：增删改查，其实在日常的开发中还有很多必备的SQL语句。

这一部分的SQL语句都是围绕着对 表中的数据进行操作的。

提示：今天的所有操作我都只会在 MySQL自带的客户端工具上进行操作。

create database day26db default charset utf8 collate utf8_general_ci;

create table depart(
    id int not null auto_increment primary key,
    title varchar(16) not null
)default charset=utf8;
​
​
create table info(
    id int not null auto_increment primary key,
    name varchar(16) not null,
    email varchar(32) not null,
    age int,
    depart_id int
)default charset=utf8;

insert into depart(title) values("开发"),("运营"),("销售");

insert into info(name,email,age,depart_id) values("刘亦菲","liuyifei@live.com",19,1);
insert into info(name,email,age,depart_id) values("于超","pyyu@live.com",49,1);
insert into info(name,email,age,depart_id) values("zzc","zzc@live.com",9,2);
insert into info(name,email,age,depart_id) values("tony","tony@live.com",29,1);
insert into info(name,email,age,depart_id) values("kelly","kelly@live.com",99,3);
insert into info(name,email,age,depart_id) values("james","james@live.com",49,1);
insert into info(name,email,age,depart_id) values("李杰","lijie@live.com",49,1);

1.1 条件

根据条件搜索结果。

select * from info where age > 30;
select * from info where id > 1;
select * from info where id = 1;
select * from info where id >= 1;
select * from info where id != 1;
select * from info where id between 2 and 4;   -- id大于等于2、且小于等于4

select * from info where name = '刘亦菲' and age = 19;
select * from info where name = 'zzc' or age = 49;
select * from info where (name = '李杰' or email="pyyu@live.com")  and age=49;

select * from info where id in (1,4,6);
select * from info where id not in (1,4,6);
select * from info where id in (select id from depart);
# select * from info where id in (1,2,3);

# 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);

select * from (select * from info where id>2) as T where age > 10;
select * from info where info.id > 10;
select * from info where id > 10;

1.2 通配符

一般用于模糊搜索。

select * from info where name like "%亦%";
select * from info where name like "%亦";
selec