一、SQL 语言
定义:Structured Query Language 结构化查询语言
SQL 是一门特殊的语言, 专门用来操作关系型数据库,当前关系型数据库都 支持使用 SQL 语言进行操作, 也就是说可以通过 SQL 语言操作 oracle、mysql、 sql server、sqlite 等等所有的关系型数据库。
SQL 语言主要分为:
- DQL:数据查询语言,用于对数据进行查询,如 select;
- DDL:数据定义语言,进行数据库、表的管理等,如 create、drop;
- DML:数据操作语言,对数据进行增、删、改,如:insert,update,delete;
- TPL:事务处理语言,对事务进行处理,包括 begin transaction,commit,rollback;
SQL 语言不区分大小写。
二、SQL 语言基础
1、MySQL 常用数据类型
- 整数:int,有符号范围(-2147483648 ,2147483647),无符号范围(0 , 4294967295),如:int unsigned,代表设置一个无符号的整数;
- 小整数:tinyint, 有符号范围(-128,127),无符号范围 (0,255),如:tinyint unsigned,代表设置一个无符号的小整数
- 小数:decimal,如 decimal(5,2)表示共存 5 位数,小数占 2 位,不能超过 2 位;整数占 3 位,不能超过三位;
- 字符串:varchar,如 varchar(3)表示最多存 3 个字符,一个中文或一个字母 都占一个字符;
- 日期时间: datetime,范围(1000-01-01 00:00:00 ~ 9999-12-31 23:59:59),如 '2020-01-01 12:29:59'。
2、表、字段、记录
- 表(table): 表(table),数据库中存储数据的基本单位,表是一个由行和列组成的二维表格结构。
- 字段(field): 表中的列,在数据库中,叫做字段(field)。
- 记录(record): 表中的一行,在数据库中,叫做记录(record)。
3、CREATE TABLE 创建表
语法:
create table 表名(
字段名 数据类型, 字段名 数据类型
... );
示例:
创建表 c,字段要求如下:id:数据类型为 int (整数);
name 姓名:数据类型为 varchar(字符串)长度为 20,
age 年龄:数据类型为 tinyint unsigned(无符号小整数);
create table c(
id int,
name varchar(20),
age tinyint unsigned
);
4、INSERT 添加数据
添加一条记录
SQL 语句中,字符串用单引号’’或者双引号””引起来,数字可以省略引号。
格式⼀:所有字段设置值,值的顺序与表中字段的顺序对应
insert into 表名 values (...);
例 1:表 c 插入一条记录,设置所有字段的信息
insert into c values (0,'亚瑟',22);
格式⼆:部分字段设置值,值的顺序与给出的字段顺序对应
insert into 表名 (字段 1,...) values (值 1,...);
例 2:表 c 插入一条记录,只设置 id 和姓名 name
insert into c (id,name) values (1,'老夫子');
添加多条记录
格式一:写多条 insert 语句,语句之间用英文分号隔开
例 3:表 c 插入三条记录,写三条 insert 语句, 语句之间用分号隔开
insert into c (id,name,age) values (2,'秦始皇',20);
insert into c (id,name,age) values (3,'汉武帝',26);
insert into c values (4,'唐太宗',23);
格式二:写一条 insert 语句,添加多条记录, 数据之间用英文逗号隔开
insert into 表名 values (...),(...)... ;
例 4:表 c 插入多条记录,用一条 insert 语句, 数据之间用逗号隔开
insert into c values (5,'宋太祖',23),(6,'庄子',23);
格式三:写一条 insert 语句,设置指定字段值
insert into 表名(字段 1,...) values (值 1,...),(值 1,...)... ;
例 5:表 c 插入多条记录,用一条 insert 语句, 只设置姓名和年龄
insert into c (name, age) values ('刘备',26),('张飞', 35);
5、SELECT 简单查询
查询所有字段
语法
select * from 表名
例 1:查询表 c 所有数据
select * from c;
查询指定字段
select 字段 1,字段 2,.... from 表名
例 2:查询表 c 的姓名(name 字段)和年龄(age 字段)
select name,age from c;
6、UPDATE 修改数据
语法:
update 表名 set 字段 1=值 1,字段 2=值 2... where 条件;
例 1:修改表 c,所有人的年龄(age 字段)改为 50
update c set age=50;
例 2:修改表 c,id 为 2 的记录,姓名(name 字段)改为 ‘狄仁杰‘,年龄(age 字段)改为 20
update c set name='狄仁杰',age=20 where id=2;
7、DELETE 删除表中的记录
语法:
delete from 表名 where 条件;
例 1:删除表 c 中 id 为 6 的记录
delete from c where id=6;
例 2:删除表 c 的所有记录
delete from c;
8、TRUNCATE 删除表中的记录
语法:(删除表的所有数据,保留表结构)
truncate table 表名;
例:删除表 c 的所有数据
truncate table c;
9、DELETE 和 TRUNCATE 的区别
- 在速度上,truncate > delete;
- 如果想删除部分数据⽤delete,注意带上 where⼦句;
- 如果想保留表⽽将所有数据删除,⾃增⻓字段恢复从 1 开始,⽤truncate;
10、DROP TABLE 删除表
语法 1:
drop table 表名;
语法 2:
drop table if exists 表名;
例 1:删除表 a
drop table a;
或
drop table if exists a;
三、字段的约束
1、常用约束介绍
- 主键(primary key):值不能重复,auto_increment 代表值自动增长;
- 非空(not null):此字段不允许填写空值;
- 惟一(unique):此字段的值不允许重复;
- 默认值(default):当不填写此值时会使用默认值,如果填写时以填写为准。
2、创建带约束字段的语法格式
create table 表名(
字段名 数据类型 约束,
字段名 数据类型 约束
... );
3、主键与自增长
- 带有 primary key(主键)的字段,值不能重复;
- auto_increment 为自增长;
语法:
create table 表名(
字段名 数据类型 primary key auto_increment,
字段名 数据类型 约束
... );
例 1:创建表 d,字段要求如下:
id : 数 据 类 型 为 int unsigned( 无 符 号 整 数 )
primary key( 主键),auto_increment(自增长);
name 姓名:数据类型为 varchar(字符串)长度为 10;
age 年龄:数据类型为 int(整数)
create table d(
id int unsigned primary key auto_increment,
name varchar(10),
age int
);
主键并且自增长字段的表,insert 语句插入数据,如果不指定 id 字段,id 字 段的值会自动增长
例 2:不指定 id 字段值,系统自动填写, 且自增长
insert into d (name, age) values ('慈禧',65);
insert into d (name, age) values ('光绪',30);
insert into d (name, age) values ('溥仪',12);
如果插入数据时, 插入所有字段,但又没写自增长字段的值,insert 语句会出错
例 3:所有字段都插入,但没写自增长字段值, 该语句会出现错误
insert into d values ('乾隆',60);
解决方案 :使用占位符,通常使⽤0 或者 null (空)来
例 4:用 0 或者 null 来占位,实现自增长字段的插入
insert into d (id, name, age) values (0, '乾隆',60);
insert into d (id, name, age) values (null,'康熙',41);
4、非空
带有 not null(非空)的字段,值不能为空;
语法:
create table 表名(
字段名 数据类型 not null, ... );
例 1:创建表 e,字段要求如下:
id:数据类型为 int unsigned (无符号整数);
name 姓名:数据类型为 varchar(字符串)长度为 10,not null(非空);
age 年龄:数据类型为 int(整数);
create table e(
id int unsigned,
name varchar(10) not null,
age int
);
非空字段表,insert 插入数据一定要指定字段值,不然插入会失败
例 2:insert 插入数据,没有指定 name 的值会失败,因为 name 字段 not null(非空)
例 2:insert 插入数据,没有指定 name 的值会失败,因为 name 字段 not null(非空)
insert into e (id,age) values (2,25);
例 3:表 e 正确的插入数据语句
例 3:表 e 正确的插入数据语句
insert into e values (1,'嘉庆',33);
5、唯一
唯一(unique):此字段的值不允许重复;
语法:
create table 表名(
字段名 数据类型 unique, ... );
例 1:创建表 f,字段要求如下:
id:数据类型为 int (整数);
name 姓名:数据类型为 varchar(字符串)长度为 10,unique(唯一);
age 年龄:数据类型为 int(整数);
create table f(
id int,
name varchar(10) unique,
age int
);
如果插入的记录中 name 值重复, 会插入失败
6、默认值
默认值(default):当不填写此值时会使用默认值,如果填写时以填写为准;
语法:
create table 表名(
字段名 数据类型 default 值, ... );
例 1:创建表 g,字段要求如下:
id:数据类型为 int (整数);
name 姓名:数据类型为 varchar(字符串)长度为 10;
age 年龄:数据类型为 int(整数),default(默认值)30;
create table g(
id int,
name varchar(10),
age int default 30
);
如果 insert 没有指定 age 的值,默认 age 为 30
四、别名与重复记录
1、查询所有字段
select*
from students;
2、查询指定字段
select name,sex,age
from students;
3、字段的别名
在select后面的字段名部分,可以使用as为字段起别名,这个字段的别名出 现在SELECT查询结果中
例1:students 表的name字段别名为 姓名,sex字段别名为 性别,age字段别 名为 年龄
select name as 姓名,sex as 性别,age as 年龄
from students;
4、表的别名
在 from 后面的表名,可以使用 as 为表起别名
例 1:students 表的别名为 s
select name, age
from students as s;
as 可以省略
select name, age
from students s;
5、distinct 消除重复记录
在 select 后面字段前使用 distinct 可以消除重复的记录
语法:
select distinct 字段 from 表名;
例 1:查询 students 表的 sex 字段,用 distinct 取消重复记录
select distinct sex
from students;
五、条件查询
1、where 子句
使用 where 子句对表中的数据筛选,符号条件的数据会出现在结果集中
语法:
select 字段 1,字段 2... from 表名 where 条件;
例 1:查询 students 表中学号 studentNo 等于’001’ 的记录
select * from students where studentNo ='001';
- select 后面的*或者字段名,决定了返回什么样的字段(列);
- select 中 where 子句,决定了返回什么样的记录(行);
where 后面支持多种运算符,进行条件的处理
- 比较运算;
- 逻辑运算;
- 模糊查询;
- 范围查询;
- 空判断
2、比较运算符
- 等于: =
- 大于: >
- 大于等于: >=
- 小于: <
- 小于等于: <=
- 不等于: != 或 <>
例:查询 students 表中 hometown(家乡)不在’北京’的学生记录
select * from students where hometown!='北京';
3、逻辑运算符
and(与)
- and 有两个条件;
- 条件 1 and 条件 2;
- 两个条件必须同时满足;
or(或)
- or 有两个条件
- 条件 1 or 条件 2;
- 两个条件只要有一个满足即可;
not(非)
- not 只有一个条件;
- not 条件;
- 如果条件为满足,not 后变为不满足。如果条件为不满足,not 后变为满足;
例 :查询 hometown 老家非’天津’的学生记录
select * from students where hometown!='天津';
/* 或 */
select * from students where not hometown='天津';
4、模糊查询
- like
- %表示任意多个任意字符;
- _表示一个任意字符。
例 1:查询 name 姓名中以’孙’开头的学生记录
select * from students where name like '孙%';
例 2:查询 name 姓名以’孙’开头,且名只有一个字的学生记录
select * from students where name like '孙_';
例 3:查询 name 为任意姓,名叫’乔’的学生记录
select * from students where name like '%乔';
例 4:查询 name 姓名中含’白’的学生记录
select * from students where name like '%白%';
5、范围查询
in 表示在一个非连续的范围内
between ... and ...表示在一个连续的范围内
例 1:查询 hometown 家乡是’北京’或’上海’或’广东’的学生记录
select * from students where hometown in('北京','上海','广东');
例 2:查询 age 年龄为 25 至 30 的学生记录
select * from students where age between 25 and 30;
6、空判断
- 注意:null 与''是不同的
- null:代表什么都没有;
- ‘’:代表长度为 0 的字符串;
判断空:is null
例 1:查询 card 身份证为 null 的学生记录
select * from students where card is null;
判断非空:is not null
例 2:查询 card 身份证非 null 的学生记录
select * from students where card is not null;
7、where 子句在 update 与 delete 语句中同样有效
例 1:修改 age 为 25,并且 name 为’孙尚香’的学生 class 为’2 班’
update students set class = '2 班' where age = 25 and name = '孙尚香';
例 2:删除 class 为’1 班’,并且 age 大于 30 的学生记录
delete from students where age > 30 and class = '1 班';
六、排序
为了方便查看数据,可以对数据进行排序;
语法:
select * from 表名
order by 字段 1 asc|desc,字段 2 asc|desc,...
- 将行数据按照字段 1 进行排序,如果某些行字段 1 的值相同时,则按照字段 2 排序,以此类推;
- 默认按照字段值从小到大排序;
- asc(默认值)从小到大排序,即升序;
- desc 从大到小排序,即降序;
例 1:查询所有学生记录,按 age 年龄从小到大排序
select * from students order by age;
例 2:查询所有学生记录,按 age 年龄从大到小排序,
年龄相同时,再按 studentNo 学号从小到大排序
select * from students order by age desc,studentNo;
七、聚合函数
为了快速得到统计数据,经常会用到如下 5 个聚合函数
!!!注意:聚合函数不能在 where 后面的条件中使用!!!
1、count 总记录数
count(*)表示计算总记录数,括号中写*与字段名,结果是相同的
例 1:查询学生总数(学生总数就是 students 表中记录的总数)
select count(*) from students;
select count(name) from students;
例 2:查询性别 sex 为’女’的学生总数
select count(*) from students where sex=’女’;
2、max 最大值
max(字段)表示求此字段的最大值
例 3:查询最大 age 年龄
select max(age) from students;
例 4:查询性别 sex 为’女’的最大 age 年龄
select max(age) from students where sex='女';
聚合函数不能在 where 后面的条件中使用
例 5:错误!!!不能用如下语句查询年龄最大学生的信息
select * from students where age=max(age);
聚合函数不能与普通字段同时出现在查询结果中
例 6:错误!!!不能用如下语句查询年龄最大学生的信息
select name, max(age) from students;
3、min 最小值
min(字段)表示求此字段的最小值
例 7:查询学生最小 age 年龄
select min(age) from students;
例 8:查询 class 班级为’1 班’的最小 age 年龄
select min(age) from students where class='1 班';
4、sum 求和
sum(字段)表示求此字段的和
例 9:查询学生 age 年龄总和
select sum(age) from students;
例 10:查询 hometown 为’北京’的学生 age 年龄总和
select sum(age) from students where hometown='北京';
5、avg 平均值
avg(字段)表示求此字段的平均值
例 11:查询学生平均年龄
select avg(age) from students;
例 12:查询 sex 性别为’男’的平均年龄
select avg(age) from students where sex='男';
八、数据分组
1、分组
- 按照字段分组,表示此字段相同的数据会被放到一个组中;
- 分组的目的是配合聚合函数,聚合函数会对每一组的数据分别进行统计;
语法:
select 字段 1,字段 2,聚合函数... from 表名 group by 字段 1,字段 2...
例 1:查询各种 sex 性别的人数
select sex,count(*) from students group by sex;
例 2:查询各种 age 年龄的人数
select age,count(*) from students group by age;
2、分组后的数据筛选
语法:
select 字段 1,字段 2,聚合... from 表名
group by 字段 1,字段 2,字段 3...
having 字段 1,...聚合...
having 后面的条件运算符与 where 的相同
例 1:使用 where 子句,查询男生总人数
select count(*) from students where sex='男';
例 2:使用 having 子句,查询男生总人数
select sex,count(*) from students group by sex having sex='男';
对比 where 与 having
- where 是对 from 后面指定的表进行数据筛选,属于对原始数据的筛选;
- having 是对 group by 的结果进行筛选;
- having 后面的条件中可以用聚合函数,where 后面的条件不可以使用聚合函数
九、数据分页显示
1、获取部分行
当数据量过大时,在一页中查看数据是一件非常麻烦的事情;
语法:limit 开始行,获取行数;
select * from 表名 limit start,count
- 从 start 开始,获取 count 条数据;
- start 索引从 0 开始,如省略 start 默认从 0 开始
例 1:查询前 3 行学生记录
select * from students limit 0,3;
例 2:省略 start,查询前 5 行学生记录
select * from students limit 5;
2、分页
当一张表记录特别多的时候,就需要用到分页显示;
已知:每页显示 m 条数据,求:查询第 n 页的数据
已知:每页显示 m 条数据,求:查询第 n 页的数据
select * from students limit (n-1)*m,m
例 1:每页显示 4 条记录,查询第 3 页的数据
m=4;
n=3;
(n-1)*m = (3-1)*4=8;
select * from students limit 8,4;
例 2:查询 students 表,每页显示 5 条记录,求总页数
- 查询记录总条数 a;
- 使用 a 除以每页显示条数 5,得到 b,;
- 如果 b 为整数,则 b 为总数页;
- 如果 b 不为整数,则 b+1 为总页数;