MySQL实战系列 -- 2. 约束&SQL查询

IT霈哥
296 阅读11分钟

学习目标

  • 能够使用SQL语句查询数据
  • 能够使用SQL语句进行条件查询
  • 能够使用SQL语句进行排序
  • 能够使用聚合函数
  • 能够使用SQL语句进行分组查询

第一章 SQL约束

  • 什么是约束

约束, 其实就是一种限制条件, 让你不能超出这个控制范围.

而在数据库中的约束, 就是指 表中的数据内容 不能胡乱填写, 必须按照要求填写. 好保证数据的完整性与安全性.

-- 准备数据
CREATE TABLE persons (
  pid int,
  lastname varchar(255),
  firstname varchar(255),
  address varchar(255)
);
insert into persons values(1, '星驰','周','香港');
insert into persons values(1, '德华','刘','香港');
insert into persons values(2, '德华','刘',null);
insert into persons values(null, '润发','周','香港');

1.1 主键约束

PRIMARY KEY 约束:

  • 主键必须包含唯一的值, 不能重复。
  • 主键列不能包含 NULL 值。
  • 每个表都应该有一个主键，并且每个表只能有一个主键。

添加主键约束

  • 方式一：创建表时，在字段描述处，声明指定字段为主键：
    • 格式: 字段名 数据类型[长度] PRIMARY KEY
CREATE TABLE persons (
  pid int primary key, -- 添加了主键约束
  lastname varchar(255),
  firstname varchar(255),
  address varchar(255)
);
INSERT INTO persons VALUES(1, '星驰','周','香港');
INSERT INTO persons VALUES(1, '德华','刘','香港'); -- 设置主键后, 插入失败, 值重复
INSERT INTO persons VALUES(2, '德华','刘',NULL);
INSERT INTO persons VALUES(NULL, '润发','周','香港'); -- 设置主键后, 插入失败, 值不能为 NULL
  • 方式二：创建表时，在constraint约束区域，声明指定字段为主键：
    • 格式：[constraint 名称] primary key (字段列表)
    • 关键字constraint可以省略，如果需要为主键命名，constraint不能省略，主键名称一般没用。
    • 字段列表需要使用小括号括住，如果有多字段需要使用逗号分隔。声明两个以上字段为主键，我们称为联合主键。
CREATE TABLE persons (
  pid int,
  lastname varchar(255),
  firstname varchar(255),
  address varchar(255),
  constraint pk_persons primary key (pid) -- 添加主键约束, 单一字段 
);

CREATE TABLE persons (
  pid INT,
  lastname VARCHAR(255),
  firstname VARCHAR(255),
  address VARCHAR(255),
  CONSTRAINT pk_persons PRIMARY KEY (lastname, firstname) -- 添加主键约束, 多个字段, 我们称为联合主键。
);
INSERT INTO persons VALUES(1, '星驰','周','香港');
INSERT INTO persons VALUES(1, '德华','刘','香港'); 
INSERT INTO persons VALUES(2, '德华','刘',NULL); -- 插入失败
    • 方式三：创建表之后，通过修改表结构，声明指定字段为主键：
      • 格式：ALTER TABLE 表名 ADD [CONSTRAINT 名称] PRIMARY KEY (字段列表)
CREATE TABLE persons (
  pid int,
  lastname varchar(255),
  firstname varchar(255),
  address varchar(255),
);
alter table persons add constraint pk_persons primary key (lastname, firstname); -- 添加联合主键

删除主键约束

如需删除 PRIMARY KEY 约束，请使用下面的 SQL：

  • 格式: ALTER TABLE 表名 DROP PRIMARY KEY
alter table persons drop primary key;

1.2 自动增长列

我们通常希望在每次插入新记录时，数据库自动生成字段的值。

我们可以在表中使用 auto_increment（自动增长列）关键字，自动增长列类型必须是整形，自动增长列必须为键(通常是用于主键)。

  • 下列 SQL 语句把 "persons" 表中的 "pid" 列定义为 auto_increment 主键
  • 格式: 字段名 整数类型[长度][约束] auto_increment
CREATE TABLE persons (
  pid INT PRIMARY KEY AUTO_INCREMENT,
  lastname VARCHAR(255),
  firstname VARCHAR(255),
  address VARCHAR(255)
);
  • 向persons添加数据时，可以不为pid字段设置值，也可以设置成null，数据库将自动维护主键值：
insert into persons(lastname, firstname, address) values('富成','郭','香港');
insert into persons(pid,lastname, firstname, address) values(null,'龙','成','香港');
  • 扩展：默认AUTO_INCREMENT 的开始值是 1，如果希望修改起始值，请使用下列 SQL 语法：
alter table persons auto_increment=100;
  • 面试题

问：针对auto_increment ，删除表中所有记录使用 delete from 表名 或使用 truncate table 表名，二者有什么区别？

删除过程:
	delete: 表中记录一条一条删除, auto_increment 计数不会重置为1; 新记录添加时在原有计数基础上＋1
	truncate: 直接将表删除,重新创建新表, auto_increment 计数重置为1;

1.3 非空约束

NOT NULL 约束:

  • 列不接受 NULL 值。

  • 要求字段始终包含值。这意味着，如果不向字段添加值，就无法插入新记录或者更新记录。

下面的 SQL 语句要求 "lastname" 列和 "firstname" 列不接受 NULL 值：

添加非空约束

  • 格式: 字段名 数据类型[长度] NOT NULL
CREATE TABLE persons (
  pid INT PRIMARY KEY AUTO_INCREMENT,
  lastname VARCHAR(255) not null,
  firstname VARCHAR(255) not null,
  address VARCHAR(255)
);

删除非空约束

  • 格式: ALTER TABLE 表名 MODIFY 字段名 数据类型[长度]
alter table persons modify lastname varchar(255);
alter table persons modify firstname varchar(255);

1.4 唯一约束

UNIQUE 约束:

  • 指定列的值 不能重复.

注意:

  1. UNIQUE 和 PRIMARY KEY 约束均为列提供了唯一性的保证。PRIMARY KEY 是自动定义的 UNIQUE 约束。
  2. 每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。
  3. UNIQUE 不限制 null 值 出现的次数

添加唯一约束

与主键添加方式相同，共有3种

  • 方式一：创建表时，在字段描述处，声明唯一：
    • 格式: 字段名 数据类型[长度] UNIQUE
CREATE TABLE persons (
  pid INT,
  lastname VARCHAR(255),
  firstname VARCHAR(255),
  address VARCHAR(255) UNIQUE -- 添加唯一约束
);

INSERT INTO persons(pid,lastname, firstname, address) VALUES(1,'富成','郭','香港');
INSERT INTO persons(pid,lastname, firstname, address) VALUES(2,'润发','周','香港');-- 插入失败,值重复
  • 方式二：创建表时，在约束区域，声明唯一：
    • 格式：[constraint 名称] UNIQUE (字段)
CREATE TABLE persons (
  pid INT,
  lastname VARCHAR(255),
  firstname VARCHAR(255),
  address VARCHAR(255),
  CONSTRAINT uni_address UNIQUE (address) -- 添加唯一约束
);
  • 方式三：创建表后，修改表结构，声明字段唯一：
    • 格式: ALTER TABLE 表名 ADD [CONSTRAINT 名称] UNIQUE (字段)
CREATE TABLE persons (
  pid INT,
  lastname VARCHAR(255),
  firstname VARCHAR(255),
  address VARCHAR(255)
);
ALTER TABLE persons ADD CONSTRAINT uni_address UNIQUE (address) -- 添加唯一约束

删除唯一约束

  • 如需删除 UNIQUE 约束，请使用下面的 SQL：
  • 如果添加唯一约束时，没有设置约束名称，默认是当前字段的字段名。
    • 格式: ALTER TABLE 表名 DROP INDEX 名称
alter table persons drop index uni_address; -- 有唯一约束名称, 使用约束名称删除
哦
alter table persons drop index address; -- 没有唯一约束名称, 使用字段名删除

1.5 默认约束

default 约束:

  • 用于指定字段默认值。
  • 当向表中插入记录时，如果没有明确的为字段赋值，则自动赋予默认值。

添加默认约束

  • 添加默认约束，在创建表时候添加
    • 格式: 字段名 数据类型[长度] DEFAULT 默认值
CREATE TABLE persons (
  pid INT,
  lastname VARCHAR(255),
  firstname VARCHAR(255),
  address VARCHAR(255) DEFAULT '香港' -- 添加默认约束
);

INSERT INTO persons(pid,lastname, firstname, address) VALUES(1,'富成','郭','北京');
INSERT INTO persons(pid,lastname, firstname) VALUES(2,'龙','成'); -- 使用address的默认值
INSERT INTO persons(pid,lastname, firstname, address) VALUES(3,'润发','周', NULL);

删除默认约束

  • 格式: ALTER TABLE 表名 MODIFY 字段名 数据类型[长度]
ALTER TABLE persons MODIFY address VARCHAR(255);

第二章 SQL语句(DQL)

2.1 DQL准备工作和语法

准备工作

#创建商品表：
create table product(
	pid int primary key,
	pname varchar(20),
	price double,
	category_id varchar(32)
);
INSERT INTO product(pid,pname,price,category_id) VALUES(1,'联想',5000,'c001');
INSERT INTO product(pid,pname,price,category_id) VALUES(2,'海尔',3000,'c001');
INSERT INTO product(pid,pname,price,category_id) VALUES(3,'雷神',5000,'c001');
INSERT INTO product(pid,pname,price,category_id) VALUES(4,'JACK JONES',800,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(5,'真维斯',200,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(6,'花花公子',440,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(7,'劲霸',2000,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(8,'香奈儿',800,'c003');
INSERT INTO product(pid,pname,price,category_id) VALUES(9,'相宜本草',200,'c003');
INSERT INTO product(pid,pname,price,category_id) VALUES(10,'面霸',5,'c003');
INSERT INTO product(pid,pname,price,category_id) VALUES(11,'好想你枣',56,'c004');
INSERT INTO product(pid,pname,price,category_id) VALUES(12,'香飘飘奶茶',1,'c005');
INSERT INTO product(pid,pname,price,category_id) VALUES(13,'果9',1,NULL);

语法

select [distinct] 
* | 列名,列名 
from 表 
where 条件

2.2 简单查询

  • 练习
-- 查询所有的商品.	
SELECT * from product;
-- 查询商品名和商品价格.
select pname,price from product;
-- 查询价格,去掉重复值.
select DISTINCT price from product;
-- 查询结果是表达式（运算查询）：将所有商品的价格+10元进行显示. 
select pname,price+10 from product;
-- 别名查询.使用的关键字是as（as可以省略的）.列别名
select pname,price+10 as '价格' from product;
select pname,price+10 '价格' from product;
-- 别名查询.使用的关键字是as（as可以省略的）.表别名
select * from product as p;
select * from product p;

2.3 条件查询

比较运算符< <= > >= = !=大于、小于、大于(小于)等于、不等于
BETWEEN ...AND...显示在某一区间的值(含头含尾)
IN(set)显示在in列表中的值，例：in(100,200)
LIKE ‘字符’模糊查询，like语句中，% 代表零个或多个任意字符，_ 代表一个字符， 例如：first_name like '_a%';
IS NULL判断是否为空
逻辑运行符and多个条件同时成立
or多个条件任一成立
not不成立，例：where not(salary>100);
  • 练习
#查询商品名称为“花花公子”的商品所有信息：
select * from product where pname='花花公子';
#查询价格为800商品
select * from product where price=800;
#查询价格不是800的所有商品
select * from product where price<>800;
select * from product where price!=800; -- mysql特有的符号
#查询商品价格大于60元的所有商品信息
select * from product where price>60;
#查询商品价格在200到1000之间所有商品
select * from product where price>=200 and price<=1000;
select * from product where price between 200 and 1000;
#查询商品价格是200或800的所有商品
select * from product where price=200 or price=800;
select * from product where price in(200,800);
# LIKe 中的 %代表匹配任意长度的任意字符; _代表匹配一个任意字符
#查询商品名称含有'霸'字的所有商品
select * from product where pname like '%霸%';
#查询商品名称以'香'开头的所有商品
select * from product where pname like '香%';
#查询商品名称第二个字为'想'的所有商品
select * from product where pname like '_想%';
#商品没有分类id的商品
select * from product where category_id is NULL;
#查询有分类id的商品
select * from product where category_id is NOT NULL;

2.4 排序查询

通过order by语句，可以将查询出的结果进行排序。暂时放置在select语句的最后。

  • 格式:
SELECT * FROM 表名 ORDER BY 排序字段 ASC|DESC;
#ASC 升序 (默认), 从小到大排序
#DESC 降序, 从大到小排序
  • 练习：
#使用价格排序(降序)
select * from product order by price desc;
#在价格排序(降序)的基础上，以分类排序(降序)
select * from product order by price desc,category_id desc;
#显示商品的价格(去重复)，并排序(降序)
select DISTINCT price from product order by price desc;

2.5 聚合查询

之前我们做的查询都是横向查询，它们都是根据条件一行一行的进行判断，而使用聚合函数查询是纵向查询，它是对一列的值进行计算，然后返回一个单一的值；另外聚合函数会忽略空值。

今天我们学习如下五个聚合函数：

  • count：统计指定列不为NULL的记录行数；

  • sum：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；

  • max：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；

  • min：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；

  • avg：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；

练习：

#查询商品的总条数
select count(*) from product; -- 不推荐
select count(pid) from product;
select count(category_id) from product;

#查询价格大于200商品的总条数
select count(*) from product where price>200;

#查询分类为'c001'的所有商品的价格总和
select sum(price) from product where category_id='c001';

#查询分类为'c002'所有商品的平均价格
select avg(price) from product where category_id='c002';

#查询商品的最大价格和最小价格
select max(price),min(price) from product;

2.6 分组查询

分组查询是指使用group by子语句对查询信息进行分组。

  • 格式：
SELECT 字段1,字段2… FROM 表名 GROUP BY分组字段 HAVING 分组条件;

分组操作中的having子语句，是用于在分组后对数据进行过滤的，作用类似于where条件。

  • having与where的区别:

    • having是在分组操作执行后, 对分组后的数据进行过滤.

      where是在分组操作执行前, 对分组前的数据 只能使用表原始列进行条件过滤

    • having后面可以使用 聚合函数

      where后面不可以使用 聚合函数。

    • 当一条SQL语句中, 既有where 又有 group by \ having时, 先执行 where, 再执行 group by, 最后执行having

练习：

#统计各个分类商品的个数
select category_id,count(*) from product group by category_id;

#统计各个分类商品的个数,且只显示个数大于1的信息
SELECT category_id,count(*) from product GROUP BY category_id HAVING count(*)>1;

#统计价格>200元的 各个分类商品的个数,且只显示个数大于1的信息
-- select * from product where price>200 group by category_id;
-- select category_id,count(*) from product where price>200 group by category_id ;
select category_id,count(*) from product where price>200 group by category_id HAVING count(*)>1;

2.7 条数限制查询

LIMIT是MySQL内置函数，其作用是用于限制查询结果的条数。

格式: select * from 表名 limit m,n
其中: m是指记录开始的index，从0开始，表示第一条记录
	 n是指从第m+1条开始，取n条。

例如:
	select * from tablename limit 2,4 -- 即取出第3条至第6条，4条记录

应用场合:分页

分页查询格式: 
SELECT * FROM 表名 LIMIT startRow,pageSize;

例如：
select * from products limit 0,5;	#第一页，每页显示5条。
select * from products limit 5,5;	#第二页，每页显示5条。
select * from products limit 10,5;	#第三页，每页显示5条。
select * from products limit startRow,5; #第curPage页，每页显示5条, startRow的值如何计算呢?

-- 后台计算出页码、页数(页大小)
-- 分页需要的相关数据结果分析如下,
-- 注意:下面是伪代码不用于执行
int curPage = 2; -- 当前页数
int pageSize = 5; -- 每页显示数量
int startRow = (curPage - 1) * pageSize; -- 当前页, 记录开始的位置(行数)计算
int totalSize = select count(*) from products; -- 记录总数量
int totalPage = Math.ceil(totalSize * 1.0 / pageSize); -- 总页数

作者： 能阅读至此, 注定了我们今生有缘! 我已从事Java教育十多年, 怀着教书育人的情怀, 正在让更多的同学们少走弯路, 改变千万IT人的命运!

请动动你的小手给'霈哥'点赞加关注, 持续更新离不开你的支持! 3Q

avatar
文章
阅读
粉丝