SQL语句

约束

• 主键（primary key）：物理上存储的顺序。当主键存在时，表中数据的物理顺序就被固定下来，当数据被清空时，新数据的顺序不会从1开始计数，而是从之前存入数据顺序的下一位继续记录，能保证数据在顺序上是唯一的。
  一般要求：整数/无符号/自动递增/不能为空
• 非空（not null）：此字段不允许空值
• 唯一（unique）：字段值不可重复
• 默认值（default）：不填写时会使用默认值。
  注：字符串数据类型设置默认值时，默认数据要添加引号’ '，否则报错。
• 外键（foreign key）：维护两表之间的关联关系。

外键

定义：一个实体（数据表）的某个字段指向另一个实体的主键。被指向的实体称之为主实体（主表），也叫父实体（父表）；负责指向的实体称之为从实体（从表），也叫子实体（子表）。
作用：对关系字段进行约束，当为从表中的关系字段添加数据时，会关联到主表中，查询该值是否存在，若存在则添加成功，反之添加失败并报错。

• 创表时添加：foreign key(自己的字段名) references 目标表名(目标表主键)
• 对于已存在的表添加外键：alter table 从表名 add foreign key(从表字段名) references 主表名(主表主键);
• 查看外键：show create table 表名;
  或Navicat：设计表-查看外键
• 删除外键：alter table 表名 drop foreign key 外键名称;

--主表
drop table if exists class;
create table class(
id int unsigned primary key auto_increment,
class_name varchar(10)
);
insert into class values(0,'1班'),
                        (0,'2班'),
                        (0,'3班');
--从表
drop table if exists stu;
create table stu(
id int unsigned primary key auto_increment,
stu_name varchar(10),
class_id int unsigned,
foreign key(class_id) references class(id)
);
--从表stu的class_id指向主表class的id，class_id是从表stu的外键

若向stu表中插入数据时class_id不在class表的id范围内，则会报错

insert into stu values(0,'张三',4);

创表语句

unsigned：无符号
auto_increment：自动递增

create table 表名(
字段名1 类型 约束,
字段名2 类型 约束
);

create table category(
id int unsigned primary key auto_increment,
typeId int,
cateName varchar(10)
);

--修改表名
alter table 原来的表名 rename to 新表名;

删表语句

-- 删除表, 包括表结构和数据
drop table 表名;
drop table if exists 表名; --检查表是否存在

插入语句

insert into 表名 values();
insert into 表名(字段名) values();
--插入一条数据
insert into category values(1,1,'1号');
--插入多条数据
--主键是自动增长，插入时要占位，通常用 0/default/null
insert into category values(0,1,'1号'),
                           (0,2,'2号'),
                           (0,3,'3号'),
                           (0,4,'4号');
--为指定字段名插入数据
insert into category(cateName) values('5号');
insert into category(typeId,cateName) values(6,'6号');

修改语句

update 表名 set 列1=值1,列2=值2 where 条件;
update category set typeId=5 where id=5;

删除数据

delete from 表名 where 条件;
delete from category where id=6;

--清空表数据(只清空数据,保留结构), 但不会重置主键计数
delete from 表名;
--清空表数据(只清空数据,保留结构), 并会重置主键计数(截断表),运行更快
truncate table 表名;

查询语句

简单查询

• 查询所有数据：select * from 表名;
• 查询部分字段：select 字段名1,字段名2 from 表名;
• 起别名：

select 字段名 as '别名' from 表名;
--as可省
select cateName '类别名称' from category;
--引号''可省
select cateName 类别名称 from category;

• 去重：select distinct(字段名) from 表名;

条件查询

1. where条件

比较运算符：=、>、>=、<、<=、!=或<>（不等于）
逻辑运算符：and、or、not
注：where后面的字符串数据类型必须加引号’ ’

select * from goods where price = 30;
select * from goods where price > 30 and price <= 90;
select * from goods where price = 30 or price = 77;
select * from goods where goodsName = '蓝莓';

2. 模糊查询 like

%：表示0个或多个字符，例：‘abc123’可用‘%bc%’
_：表任意单个字符，例：‘abc’可用‘a_c’

select * from goods where 字段名 like '%信息%';
select * from goods where 字段名 like '_信息';

select * from goods where company like '_宝';

3. 范围查询

• between 起始值 and 结束值：表一个连续的范围（从小到大）
  相当于 x >= 起始值 and x <= 结束值

select * from goods where price between 30 and 100;
select * from goods where price >= 30 and price <= 100;

select * from goods where price not between 30 and 100;
select * from goods where price < 30 or price > 100;

• in(条件1,条件2,…)：表示在某个不连续的范围内

select * from goods where price in(20,110);
select * from goods where price = 20 or price = 110;

select * from goods where price not in(20,110);
select * from goods where price != 20 and price != 110;

select * from goods where company in('某东','x宝');

4. 判断空 is null / is not null

注：null：空；‘ ’：空字符（例：空格或制表符tab）

select * from 表名 where 字段名 is null;
select * from goods where remark is null;
--取反
select * from goods where remark is not null;

排序

order by 字段名 asc（升序）/desc（降序）
注：默认按升序查询

select * from goods order by price desc;
--先按price降序排列，当price相同时，按num升序排列
select * from goods order by price desc,num asc;

聚合函数

• 数据总数：count(*)
  注：统计数据总数时, 推荐使用 * (按照数据最多的那一列统计总数)
• 最大值：max(字段名)
• 最小值：min(字段名)
• 平均值：avg(字段名)
• 求和：sum(字段名)

select min(price) from goods;
--计算一次性口罩的总数量
select sum(num) from goods where remark like '%一次性口罩%';

分组

group by 字段名：一般分组会配合聚合函数一起使用，对分组后的数据进行进一步统计。
注：分组后在进行条件筛选，可用having关键字

--查询每家公司的商品信息数量
select company 公司,count(*) 公司商品数量 from goods group by company;

--查询指定公司最贵商品价格
select company,max(price) from goods group by company having company in ('x宝','某东');
select company,max(price) from goods group by company having company != '并夕夕';

--按公司分类查询商品的平均价格，并筛选出平均价格大于50的公司及其平均价
select company,avg(price) from goods group by company having avg(price) >= 50;

补充：对比where和having

1. where是对from后的表进行数据筛选，属于对原始数据筛选
2. having是对group by的结果进行筛选
3. having后面的条件可以使用聚合函数，where后面不可

分页查询

limit 起始索引（实际数据顺序-1）,数据行数

--查询第5-10行数据：limit 4,6
select * from goods limit 4,6;

--获取当前数据第一行时，起始索引可省略
select * from goods limit 0,6;
select * from goods limit 6;

公式：每页显示m条数据，求显示第n页的数据
limit (n-1)*m,m

--每页显示3条数据，查询第二页显示的数据 limit 3,3
 select * from goods limit 3,3;

连接查询

连接查询共用知识点：

• 对连接后的表进行字段显示限制，要由对应的 表名.字段名 方式实现，防止表与表之间有相同字段名
• 连接查询中，往往会给表起别名，目的：缩短表名；给表单独创造

连接查询实质：只是将分布于多张表的数据通过连接的方式进行整理，形成数据源
注：①连接操作完成后，之前的所以查询语法均可继续使用；
②具体连接方法的选择以具体需求为准

select * from goods go 
left join category ca on go.typeId = ca.typeId 
where price <=120;

1. 内连接

显示两张表中存在对应关系的数据，无对应关系的不显示
语法：表1 inner join 表2 on 表1.字段名=表2.字段名

select * from goods go inner join category ca on go.typeId = ca.typeId;
--查询指定字段
select go.*,ca.id,ca.cateName from goods go inner join category ca on go.typeId = ca.typeId;

4号和11号的typeId为空，与表2无对应关系，内连接不显示

2. 左连接

以left join为界，关键字左侧表的信息全部显示，右侧表的信息有对应的显示，无对应的部分以null占位填充
语法：表1 left join 表2 on 表1.字段名=表2.字段名

select * from goods go left join category ca on go.typeId = ca.typeId;

3. 右连接

以right join为界，关键字右侧表的信息全部显示，左侧表的信息有对应的显示，无对应的部分以null占位填充
语法：表1 right join 表2 on 表1.字段名=表2.字段名
注：右连接主要解决3张及以上表进行连接查询。

select * from goods go right join category ca on go.typeId = ca.typeId;

4. 自关联

特征：只有1张表，表中至少存在两列字段存在对应关系
原理：通过起别名的方式，将一张表变成两张表，通过对应字段的对应关系，进行条件对比，实现连接查询。

--查询表中河南省所有的市
select * from areas a1 
left join areas a2 on a1.aid = a2.pid 
where a1.atitle = '河南省';

--查询表中河南省所有的市和区
select * from areas a1 
inner join areas a2 on a1.aid = a2.pid
left join areas a3 on a2.aid = a3.pid
where a1.atitle = '河南省';

子查询

• 定义 : 在一条查询语句当中, 利用另一条语句作为条件或数据源, 充当条件或数据源的语句称之为子查询语句。
  注：子查询语句在使用时，务必使用括号括起来。

①充当条件

--查询价格高于平均价的商品
select * from goods 
where price > (select avg(price) from goods);

②充当数据源

--查询所有来自并夕夕的商品信息, 包含商品分类
select * from category ca 
inner join (select * from goods where company = '并夕夕') go on ca.typeId = go.typeId;

select * from (select * from goods where company = '并夕夕') go 
inner join category ca on ca.typeId = go.typeId;

• 分类（根据子查询语句返回的结果形式体现）：
  ①标量子查询：返回结果是一个值（一行一列）
  ②列子查询：返回结果是一列数据（一列多行）
  ③行子查询：返回结果是一行数据（一行多列）
  ④表级子查询：返回结果相当于一张表（多行多列）
• 子查询关键字
  • ①in：范围

select * from goods
where price in (select price from goods where price between 25 and 100);

• ②any/some：任意一个

select * from goods where price = any (select price from goods where price between 25 and 100);
select * from goods where price = some (select price from goods where price between 25 and 100);

• ③all：全部（=all：等于所有；!=或<>all：不等于所有）

select * from goods where price != all (select price from goods where price between 25 and 100);