01 SQL脚本语句的分类和语法
什么是标准SQL
标准SQL:Structured Query Language 结构化的查询语言,所有关系型数据库通用的语言。用来对数据库和数据进行管理的命令。
标准SQL语句分类
-
Data Definition Language(DDL) [建库建表]
数据定义语言,简称DDL,用于对数据库和表的管理(建库,建表,删库,删表,修库,修表) -
Data Manipulation Language(DML) [表数据记录的增删改]
数据操纵语言,简称DML,用于对记录的增删改(表中的数据,增删改) -
Data Query Language(DQL) [查询表数据]
数据查询语言,简称DQL,用于对记录查询操作(查询数据的,可以很复杂) -
Data Control Language(DCL) [用户角色权限的管理]
数据控制语言,简称为DCL,用于对用户的管理和权限的设置(创建数据库的用户,管理权限的,一般是运维或者数据库管理员操作的)
MySQL的语法
SQL语句语法规则:
-
每条SQL语句以分号结束,在SQLyog客户端中可以省略。
-
在SQL不区分大小写,通常会自动修改成大写。
-
三种注释:
注释的语法 | 说明 |
---|---|
– 空格 | 单行注释 |
/* */ | 多行注释 |
# | 单行注释,只在mysql中可以用 |
02 DDL:创建数据库
创建数据库
-- 创建数据库db1 (选中要执行的SQL语句,如果没有选中运行所有的SQL语句)
create database db1;
-- 判断是否存在,如果不存在则创建数据库db2
create database if not exists db2;
-- 创建数据库并指定字符集为gbk,如果没有指定默认是utf-8,在安装的时候已经指定。按tab自动补全
create database db3 character set gbk;
查看数据库
-- 查看所有的数据库
show databases;
-- 查看某个数据库的定义信息
show create database db1;
- 创建数据库:create database 数据库名称;
- 查看有哪些数据库: show databases;
03 DDL:修改和删除数据库
修改数据库
-- 将db3数据库的字符集改成utf8
alter database db3 character set utf8;
删除数据库
-- 删除db2数据库
drop database db2;
使用数据库
-- 查看正在使用的数据库
select database();
-- 使用db1数据库
use db1; # 切换数据库
DDL语句操作 | 关键字 |
---|---|
创建 | create database |
修改 | alter database |
查看 | show databases |
删除 | drop database |
04 DDL:创建表结构
创建表的语法
-- 字段名就是列名,字段类型:数据类型,约束。多个字段之间使用逗号来分隔
create table 表名 (
字段名1 字段类型1 约束1,
字段名2 字段类型2 约束2
)
MySQL数据类型
常使用的数据类型如下
数据类型 | 关键字 |
---|---|
整型 | int或integer |
浮点型 | double或float |
字符串型 | varchar或char |
日期类型 | date或datetime |
常用数据类型说明
具体操作
-- 创建数据库day14
create database day14;
use day14;
-- 创建student表包含id整数,name变长字符串长20,sex性别定长型1,birthday字段日期类型
create table student(
-- 字段名 字段类型
id int,
name varchar(20),
sex char(1),
birthday date
);
- 建表语句的关键字:
create table 表名(字段名 字段类型)- 常用的数据类型:
int, double,date,varchar,datetime
05 DDL:查看表结构
语法
查看某个数据库中的所有表
show tables;
查看某个表结构
desc 表名;
查看创建表的SQL语句
show create table 表名;
创建相同的表结构
create table 新表名 like 旧表;
操作
-- 查看mysql数据库中的所有表
use mysql;
show tables;
-- 查看student表的结构
use day14;
desc student;
-- 查看student的创建表SQL语句
show create table student;
-- 创建s1表,s1表结构和student表结构相同
create table s1 like student;
desc s1;
- 查看表结构:desc 表名;
- 查看创建表的SQL语句: show create table 表名;
- 复制表结构:
- – 创建student01 表,student01表结构和student表结构相同
– 表的复制。(只是复制表结构,没有复制表数据)
CREATE TABLE student01 LIKE student;
06 DDL:删除表
直接删除表
drop table 表名;
判断表是否存在再删除表
drop table if exists 表名;
操作
-- 直接删除表s1表
drop table s1;
-- 判断表是否存在并删除s1表
drop table if exists s1;
删除表的代码:drop table 表名;
07 DDL:修改表结构
语法
添加表列ADD
alter table 表名 add 字段名 数据类型;
修改列类型MODIFY
alter table 表名 modify 字段名 新的数据类型;
修改列名和类型 CHANGE
alter table 表名 change 旧列名 新列名 数据类型;
删除列 DROP
alter table 表名 drop 列名;
修改表名
注:MySQL中没有直接修改库名的语句
rename table 旧表名 to 新的表名;
操作
-- 为学生表添加一个新的字段remark,类型为char(20)
desc student;
alter table student add remark char(20);
-- 将student表中的remark字段的改成varchar(100)
alter table student modify remark varchar(100);
-- 将student表中的remark字段名改成intro,类型varchar(30)
alter table student change remark intro varchar(30);
-- 删除student表中的字段intro
alter table student drop intro;
-- 将学生表student改名成student2
rename table student to student2;
说说下面修改表结构分别使用什么关键字?
- 添加一列:add
- 修改类型:modify
- 修改字段名和类型:change
- 删除一列:drop
08 DML:插入记录
插入全部字段
insert into 表名 values(值1,值2...);
插入部分字段
-- 前面与后面的个数要一样
insert into 表名 (列名1,列名2) values(值1,值2);
注:没有添加数据的字段值为NULL
插入多条记录
-- 添加3条记录
insert into 表名 values(值1,值2),(值1,值2),(值1,值2);
操作
-- 查询表中记录,* 表示所有的列,没有查询条件,查询的是所有的行
select * from student;
-- 向表中插入所有字段,不写字段名
-- sql中字符串使用单引号,在mysql中也可以使用双引号。
-- 日期也使用单引号
insert into student values(1, '牛魔王', '男', '2001-11-11' );
-- now() 函数表示现在的时间:同时包含日期和时间
insert into student values(2, '红孩儿', '女', now());
-- 插入部分数据,往学生表中添加id, name数据
insert into student (id,name) values (3,'太上老君');
-- 一条insert语句插入多条记录
insert into student values(4, '孙悟空', '男', '1999-11-11' ),(5, '猪肛裂', '男', '1999-11-11' ),(6, '沙悟净', '男', '1999-10-11' );
命令行下汉字乱码的问题
# 编码格式不一致
# 解决方法
set names gbk;
# 告诉服务器,客户端使用的GBK编码
09 DML:更新表记录
目标
如何更新数据库中的记录
不带条件修改数据
修改所有行的记录
update 表名 set 字段名=值
带条件修改数据
修改符合条件的数据
update 表名 set 字段名=值 where 条件
##操作
-- 不带条件修改数据,将所有的性别改成女
update student set sex='女';
select * from student;
-- 带条件修改数据,将id号为2的学生性别改成男
update student set sex='男' where id=2;
-- 一次修改多个列,把id为3的学生,修改性别为男,生日:1996-03-08
update student set sex='男', birthday='1996-03-08' where id=3;
- 带条件更新:update 表名 set 字段名=值 where 条件
- 不带条件更新:update 表名 set 字段名=值
10 DML:删除表记录
不带条件删除数据
-- 删除表中所有的记录,通常作用不太。只删除记录不删除表结构
delete from 表名;
带条件删除数据
-- 删除符合条件的记录
delete from 表名 where 条件
删除表中所有记录
-- 与上面的语句区别:
-- 相当于先删除表结构:drop table 表名;
-- 创建一张相同结构的表:create table 表名;
truncate 表名;
操作
-- 带条件删除数据,删除id为3的记录
delete from student where id=3;
select * from student;
-- 不带条件删除数据,删除表中的所有数据
delete from student;
-- 删除表结构再创建表
truncate student;
功能 | 语句 |
---|---|
添加记录 | insert into 表名 values(值) |
更新记录 | update 表名 set 字段名=值 where 条件 |
删除记录 | delete from 表名 where 条件 |
11 DQL:无条件查询
查询表所有行和列的数据
select * from 表名;
-- 星号表示所有列(字段名)
查询指定的多个列
select 列名1,列名2 from 表名;
操作
-- 查询student表中的id 和name 列
select * from student;
select id,name from student;
-- 没有指定条件,查询的是所有的行。条件是对行进行过滤
select name, birthday from student;
指定列的别名
使用关键字 as
使用别名的好处:列名可以使用别名
注:as可以省略
操作
-- 使用别名,定义别名关键字是:as
select id as 编号,name as 姓名 from student;
-- as关键字可以省略
select name 姓名, birthday 生日 from student;
清除重复值
查询指定的列让结果不出现重复行
select distinct 1个或多个字段名 from 表名
操作
-- 查询学生来至于哪些地方,并且去掉重复行
select address from student;
select distinct address from student;
-- 查询学生的姓名和地址,去掉重复行。必须几个列都相同,才会去除
select distinct name,address from student;
查询结果参与运算
CREATE TABLE student02 (
id int, -- 编号
name varchar(20), -- 姓名
age int, -- 年龄
sex varchar(5), -- 性别
address varchar(100), -- 地址
math int, -- 数学
english int -- 英语
);
INSERT INTO student02
(id,NAME,age,sex,address,math,english)
VALUES
(1,'马云',55,'男','杭州',66,78),(2,'马化腾',45,'女','深圳',98,87),
(3,'马景涛',55,'男','香港',56,77),(4,'柳岩',20,'女','湖南',76,65),
(5,'柳青',20,'男','湖南',86,NULL),(6,'刘德华',57,'男','香港',99,99),
(7,'马德',22,'女','香港',99,99),(8,'德玛西亚',18,'男','南京',56,65);
某列数据和固定值运算
select 列+数值 from 表名;
某列数据和其他列数据参与运算
select 列1+列2 from 表名;
注意: 参与运算的必须是数值类型
操作
-- 修改student表结构,添加数学和英语成绩列
alter table student add math int, add english int;
select * from student;
-- 查询姓名、数学,将数学每个减10分
select name,math from student;
-- 只影响查询结果
select name,math-10 from student;
-- 查询所有列与math + english的和并使用别名”总成绩”
select *,(math+english) 总成绩 from student;
- 查询多个列,使用什么符号分隔列名?
逗号- 指定列的别名使用哪个关键字?
as (可以不写)- 清除重复值使用哪个关键字?
distinct
12 DQL:查询条件包含运算符
条件查询的语法
select 列名 from 表名 where 条件 |
---|
流程:取出表中的每条数据,满足条件的记录就返回,不满足条件的记录不返回 |
准备数据
创建一个学生表并插入记录:
CREATE TABLE student3 (
id int, -- 编号
name varchar(20), -- 姓名
age int, -- 年龄
sex varchar(5), -- 性别
address varchar(100), -- 地址
math int, -- 数学
english int -- 英语
);
INSERT INTO student3
(id,NAME,age,sex,address,math,english)
VALUES
(1,'马云',55,'男','杭州',66,78),(2,'马化腾',45,'女','深圳',98,87),
(3,'马景涛',55,'男','香港',56,77),(4,'柳岩',20,'女','湖南',76,65),
(5,'柳青',20,'男','湖南',86,NULL),(6,'刘德华',57,'男','香港',99,99),
(7,'马德',22,'女','香港',99,99),(8,'德玛西亚',18,'男','南京',56,65);
比较运算符
>、<、<=、>=、=、<>
<> 在SQL中表示不等于,mysql中也可以使用!=
= 等于
操作
-- 查询math分数大于80分的学生
select * from student3 where math > 80;
-- 查询english分数小于或等于78分的学生
select * from student3 where english <=78;
-- 查询age等于20岁的学生
select * from student3 where age = 20;
-- 查询age不等于20岁的学生,注:不等于有两种写法
select * from student3 where age <> 20;
select * from student3 where age != 20;
逻辑运算符
在SQL语句中建议使用前面的
逻辑运算符 | 说明 |
---|---|
and 或 && | 全真为真 |
or 或 || | 见真为真 |
not 或 ! | 取反 |
操作
-- 查询age大于35且性别为男的学生(两个条件同时满足)
select * from student3 where age > 35 and sex='男';
select * from student3 where age > 35 && sex='男';
-- 查询age大于35或性别为男的学生(两个条件其中一个满足)
select * from student3 where age > 35 or sex='男';
-- 查询id是1或3或5的学生
select * from student3 where id=1 or id=3 or id=5;
- 比较运算符有哪些:
大于,小于,等于,不等于- 逻辑运算符有哪些:
and or not
13 DQL:条件中的关键字
in关键字
select 列名 from 表名 where 列名 in (值1,值2,值3); |
---|
in里面的每个数据都会作为一次匹配条件,只要满足条件的就会显示 |
操作
-- 查询id是1或3或5的学生
select * from student3 where id in(1,3,5);
-- 查询id不是1或3或5的学生
select * from student3 where id not in (1,3,5);
范围查询
通常用于数值类型,注:小值一定要放在前面,大值要放在后面。
select 列名 from 表名 where 列名 between 小值 and 大值 |
---|
表示从值1到值2范围,包头又包尾 |
操作
-- 查询english成绩大于等于77,且小于等于87的学生
select * from student3 where english between 77 and 87;
select * from student3 where english >=77 and english<=87;
select * from student3 where english<=87 and english >=77;
like关键字
作用:用于字符串的模糊查询 |
---|
语法:select 列名 from 表名 where 列名 like ‘字符串’ |
MySQL通配符
如果没有通配符,相当于等于号。char, varchar
通配符 | 说明 |
---|---|
% | 匹配多个字符 |
_ | 匹配一个字符 |
操作
-- 查询姓马的学生
select * from student3 where name like '马';
-- 相当于
select * from student3 where name = '马';
-- 模糊查询必须使用通配符
select * from student3 where name like '马%';
-- 查询姓名中包含'德'字的学生
select * from student3 where name like '%德%';
-- 查询姓马,且姓名有2个字的学生
select * from student3 where name like '马_';
查询为空的列
关键字
根据字段为空查询使用IS NULL 不写成=NULL(写成null查不了)
操作
-- 查询英文成绩为NULL的学生
select * from student3 where english is NULL;
-- 查询英语成绩不为NULL的学生
select * from student3 where english is not NULL;
-- 查询姓名和英语成绩,如果英语为null,则显示为0分
select name,english from student3;
-- IFNULL(列名, 默认值): 如果这一列有值,则显示它的值,如果为NULL,则显示后面默认值
select name,ifnull(english,0) 英语 from student3;
相关的函数说明
mysql函数 | 说明 |
---|---|
IFNULL(字段名,默认值) | 如果这一列有值,则显示它的值,如果为NULL,则显示后面默认值 |
关键字小结
查询关键字 | 作用 |
---|---|
AS | 定义别名 |
DISTINCT | 去除重复 |
<> | 不等于 |
AND/OR/NOT | 与,或,非 |
BETWEEN…AND… | 范围查询,包头又包尾 |
IN(多个值) | 等于多个值中任何一个值都可以 |
LIKE '张%' | 模糊查询,% 匹配多个字符 _ 匹配一个字符 |
IS NULL | 判断列是否为空 |
修改字体
14 DQL:查询结果排序
语法
对指定的列进行排序,默认是asc升序
select 列名 from 表名 order by 列名 asc/desc |
---|
升序: asc |
降序: desc |
单列排序
-- 查询所有数据,使用年龄降序排序
select * from student3 order by age desc;
多列排序
-- 查询所有数据大于20岁的学生,在年龄降序排序的基础上,如果年龄相同再以数学成绩升序排序
select * from student3 order by age desc, math desc;
排序的关键字是什么?升序和降序的关键字是什么?
order by 、asc 、desc
注意:排序一般放在SQL语句的最后面。
15 DQL:聚合函数。
学习SQL中的五个聚合函数
MySQL数据库内置函数。
概述
之前我们做的查询都是横向查询,它们都是根据条件一行一行的进行判断,而使用聚合函数查询是列向查询,它是对一列的值进行计算,然后返回一个结果值。
五个聚合函数
通过都是对数值列操作,聚合函数会忽略空值。NULL不会参与运算
SQL中的聚合函数 | 作用 |
---|---|
count(列名) | 统计个数(行) |
sum(列名) | 求和,对数值类型的列求和 |
avg(列名) | 求平均,对数值类型的列求平均 |
max(列名) | 求这一列中最大值 |
min(列名) | 求这一列中最小值 |
操作
-- 聚合函数针对列来操作
-- 查询学生总数
select count(id) from student3; -- 8
select count(english) from student3; -- 7
select count(*) from student3; -- 8
-- 查询年龄大于40的总数
select count(*) from student3 where age > 40;
-- 查询数学成绩总分
select sum(math) 数学总分 from student3;
-- 查询数学成绩平均分
select avg(math) 数学平均分 from student3;
-- 查询数学成绩最高分
select max(math) 数学最高分 from student3;
-- 查询数学成绩最低分
select min(math) 数学最低分 from student3;
五个聚合函数分别是? sum, max, count, min,avg
聚合函数不能用在where之后,用在select之后
16 DQL:分组查询
语法
select 列名 from 表名 group by 列名 having 条件 |
---|
对表中所有的行,进行分组查询,默认是返回每一组的第一行记录可以使用having对分组后的结果再进行过滤 |
GROUP BY怎么分组的
直接分组
将分组字段结果中相同内容作为一组,如按性别将学生分成2组。
GROUP
BY将分组字段结果中相同内容作为一组,并且返回每组的第一条数据,所以单独分组没什么用处。分组的目的就是为了统计,一般分组会跟聚合函数一起使用。
-- 按性别分组,查询每组的第一条记录
select * from student3 group by sex ;
与聚合函数一起使用
-- 按性别进行分组,求男生和女生数学的平均分
select sex, avg(math) from student3 group by sex;
-- 按性别进行分组,求男生和女生数学的总和
select sex, sum(math) from student3 group by sex;
实际上是将每组的math进行求和,返回每组统计的结果,当我们使用某个字段分组,在查询的时候也需要将这个字段查询出来,否则看不到数据属于哪组的。
聚合函数与条件一起使用
查询年龄大于25岁的人,按性别分组,统计每组的人数
- 先过滤掉年龄小于等于25岁的人。
- 再分组。
- 最后统计每组的人数
-- 先使用Where过滤掉一些数据,再进行分组
-- 查询年龄大于25岁的人,按性别分组,统计每组的人数
select sex, count(*) from student3 where age >25 group by sex;
对分组后的结果过滤
先分组,再过滤 查询年龄大于25岁的人,按性别分组,统计每组的人数,并只显示性别人数大于2的数据。以下代码是否正确?
-- 查询年龄大于25岁的人,按性别分组,统计每组的人数,并只显示性别人数大于2的数据。
select sex, count(*) from student3 where age >25 group by sex where count(*) > 2; -- 错误的
-- 如果要对分组后的结果再进行过滤,使用having
select sex, count(*) from student3 where age >25 group by sex having count(*) > 2; -- 正确
having与where的区别
关键字 | 功能 |
---|---|
where 子句 | 1. 先过滤,再进行分组 2. where后面不能使用聚合函数 |
having子句 | 1. 先分组,再进行过滤 2. having后面可以使用聚合函数,having用在group by后面 |
分组使用哪个关键字,如果要对分组后的结果再过滤使用哪个关键字?
group by 分组列 having 条件
17 DQL:分页操作
给student3添加新的数据:
INSERT INTO student3
(id,NAME,age,sex,address,math,english)
VALUES
(9,'唐僧',25,'男','长安',87,78), (10,'孙悟空',18,'男','花果山',100,66),
(11,'猪八戒',22,'男','高老庄',58,78), (12,'沙僧',50,'男','流沙河',77,88),
(13,'白骨精',22,'女','白虎岭',66,66), (14,'蜘蛛精',23,'女','盘丝洞',88,88);
limit的作用:
-- 返回0到3这三条记录 (起始行,返回行数)
select * from student3 limit 0,3;
-- 查询学生表中数据,从第3条开始显示,显示6条。
select * from student3 limit 2,6;
使用场景
分页:比如我们登录京东,淘宝,返回的商品信息可能有几万条,不是一次全部显示出来。是一页显示固定的条数。假设我们一每页显示5条记录的方式来分页。
-- 每页显示5条
-- 第一页:从0开始,显示5条
select * from student3 limit 0,5;
-- 第二页:从5开始,显示5条
select * from student3 limit 5,5;
-- 第三页:从10开始,显示5条
select * from student3 limit 10,5;
select语句的关键字顺序
这些关键字有先后顺序,不能乱写
select 字段 from 表
where 条件
group by 分组列
having 过滤条件
order by 排序列
limit 跳过行, 返回行
执行顺序 form =》 where =》 group by =》 having =》 字段 =》 order by =》 limit
18 约束:主键约束
数据库约束的概述
表约束的作用
约束通常是在创建表结构的时候创建的,表中约束,防止不符合要求的数据添加到表中,保证表中数据的正确性。如果表中已经有违反约束的数据,会导致约束创建失败。
约束种类
约束关键字 | 约束名 |
---|---|
primary key | 主键 |
not null | 非空 |
unique | 唯一 |
foreign key … references | 外键 |
default | 默认 |
主键约束
- 主键的作用:
1. 唯一标识表中每一条记录,通过主键来查询到每一条记录。- 哪种类型的字段作为表的主键
1. 通常是能够唯一代表这条记录的字段,尽量不要使用与业务相关的主键。如:身份证,学生证
2. 最好这个主键是没有意义。主键值不具备业务意义,是独立的值!
复合主键
一张表中如果有多列做为主键,称为复合主键。需要通过多列才能唯一标识表中一行记录。只要这几列加到一起不重复就可以了。
主键的特点:
- 非空:不能为NULL。
- 唯一:不能重复。
创建主键
语法
- 在创建表的时候给字段添加主键
create table 表名 (
字段名字 数据类型 primary key
)
- 在已有表中添加主键,修改表结构
alter table 表名 add primary key(列名);
操作
-- 创建表学生表st1, 包含字段(id, name, age)将id做为主键
create table st1(
id int primary key,
name varchar(20),
age int
);
desc st1;
-- 插入重复的主键值
select * from st1;
insert into st1 values(1,'张三',30);
insert into st1 values(1,'李四',20);
-- 插入NULL的主键值
insert into st1 values(NULL,'张三',30);
删除主键
-- 通过主键查询
select * from student3 where id=1;
-- 也可以做为主键
select uuid();
-- 创建表学生表st1, 包含字段(id, name, age)将id做为主键
create table st1(
id int primary key, -- 在建表的时候添加主键
name varchar(20),
age int
);
desc st1;
-- 插入重复的主键值
select * from st1;
insert into st1 values(1,'张三',30);
insert into st1 values(1,'李四',20);
-- 插入NULL的主键值
insert into st1 values(NULL,'张三',30);
-- 删除st1表的主键
alter table st1 drop primary key;
-- 表存在的情况下,添加主键
alter table st1 add primary key(id);
主键的关键字是什么?
primary key
not null
23
19 约束:主键自增和修改
为什么会有主键的自增长
由系统自动增长主键,不需要人为添加主键的值,避免重复。
auto_increment 主键自增 只能用在主键上,不能用在其它的字段上
自增只能针对于主键。且主键是整型才可以。
语法
create table 表名 (
字段名字 数据类型 primary key auto_increment
)
操作
-- 创建学生表st2, 包含字段(id, name, age)将id做为主键并自动增长
create table st2(
id int primary key auto_increment,
name varchar(20),
age int
);
select * from st2;
-- 插入数据
insert into st2 (name,age)values('张三',10);
-- 如果不写前面保证列数匹配
insert into st2 values(null,'张三',10);
修改主键自增的起始值
-- 将主键的起始值设置为1000
alter table tb_user02 auto_increment = 1000;
-- delete删除表不会影响自增的id值
DELETE FROM tb_user02;
-- TRUNCATE删除表,自增恢复到1开始。
TRUNCATE tb_user02;
主键自增长的关键字是什么?
auto_increment
alter table tb_student04 auto_increment = 1000;
学习总结
-
能够使用SQL语句操作数据库
-
创建:create database
-
修改:alter database
-
删除:drop database
-
查询: show create database
-
-
能够使用SQL语句操作表结构
-
添加列:alter table add
-
修改列:alter table modify
-
删除列:alter table drop
-
改表名:alter table rename
-
-
能够使用SQL语句进行数据的添加、修改删除的操作
-
添加:insert
-
修改:update
-
删除:delete
-
-
能够使用SQL语句简单查询数据
select * from 表名 where 条件
-
能够使用SQL语句进行排序
order by ... asc /desc
-
能够使用聚合函数
聚合函数 sum() avg() max() min() count() -
能够使用SQL语句进行分组查询
group by ... having