SQL:Structure Query Language(结构化查询语言)简称SQL,数据库管理系统可以通过SQL管理数据库;定义和操作数据,维护数据的完整性和安全性。
SQL的分类:
1、DDL(Data Definition Language)数据定义语言,对数据的结构和形式进行定义,一般用于数据库和表的创建、删除、修改等。
2、DML(Data Manipulation Language) 数据操作语言,用来操作数据库中表里的数据;常用语句:INSERT、 UPDATE、 DELETE
3、DCL(Data Control Language) 数据控制语言,用来操作访问权限和安全级别; 常用语句:GRANT、DENY
4、DQL(Data Query Language) 数据查询语言,用来查询数据 常用语句:SELECT
一、DDL数据库操作
1.1 查询数据库
# 显示当前mysql中的数据库列表:
show databases;
# 显示指定名称的数据的创建的SQL指令:
show create database <dbName>;
1.2 创建数据库
# 创建数据库,dbName表示创建的数据库名称,可以自定义:
create database <dbName>;
# 创建数据库,当指定名称的数据库不存在时创建:
create database if not exists <dbName>;
# 在创建数据库的同时指定数据库的字符集(z字符集:数据存储在数据库中采用的编码格式 utf8 gbk):
create database <dbName> character set utf8;
1.3 修改数据库
# 修改数据库的字符集:
alter database db_test character set utf8;
1.4 删除数据库:(删除数据库时会删除当前数据库中所有表和数据)
drop database <dbName>
# 使用/切换数据库:
use <dbName>
1.5 创建数据表
# 查询数据表:
show tables;
# 查询表结构:
desc <tableName>;
# 删除数据表:
drop table <tableName>;
# 当数据表存在时删除:
drop table if exists <tableName>;
# 修改数据表:
# 修改表名:
alter table students rename to 新名;
# 数据表也是有字符集的,默认字符集和数据库一致:
alter table <tableName> character set utf8;
# 添加字段(列):
alter table <tableName> add 字段名 类型;
# 修改字段的列表和类型:
alter table <tableName> change 字段名 新的字段名 新的类型;
# 只修改字段的类型:
alter table <tableName> modify 字段名 新的类型;
# 删除字段:
alter table <tableName> drop 字段名;
二、mysql数据类型
数据类型指的是数据表中的列支持存放数据的类型
2.1 数值类型
在mysql中有多种数据类型可以存放数值,不同的类型存放的数值的范围或者形式是不同的
类型 | 内存空间大小/byte | 范围 | 说明 |
tinyint | 1 | 有符号: -128到127 无符号0-255 | 特小型整数 |
smallint | 2 | 有符号: -32768到32767 无符号0-65535 | 小型整数 |
mediumint | 3 | 有符号: -2^31到2^31-1 无符号0-2^31-1 | 中型整数 |
int | 4 | 整数 | |
bigint | 8 | ||
float | 4 | 单精度 | |
double | 8 | 双精度 | |
decimal | decimal(10,2)表示一共有10位,小数2位 |
2.2 字符串类型
存储字符序列的类型
类型 | 字符序列的长度范围 | 说明 |
char | 0-255 | 定长字符串,最多可以存储255个字符串,指定char(n) |
varchar | 0-65535 | 可变长度字符,此类型最多65536个字符串,指定varchar(n) |
tinyblob | 0-255 | 存储二进制字符串 |
blob | 0-65535 | 存储二进制字符串 |
mediumblob | 0-1677215 | 存储二进制字符串 |
longblob | 0-4294367295 | 存储二进制字符串 |
tinytext | 0-255 | 文本数据(字符串) |
text | 0-65535 | 文本数据(字符串) |
mediumtext | 0-1677215 | 文本数据(字符串) |
longtext | 0-4294367295 | 文本数据(字符串) |
2.3 日期类型:
在mysql数据库中,可以使用字符串来存储时间,如果需要基于时间字段进行查询
data | 2021-09-13 | 只能存储年月日 |
time | 11:12:13 | 只能存储时分秒 |
year | 2021 | 年份 |
datatime | 2021-09-13 11:12:13 | 存储年月日时分秒 |
timestamp | 20210913111213 | 时间戳 |
2.4 字段约束
在创建数据表的时候,指定的对数据表的列的数据限制的要求
字段常见的约束:
非空约束(not null): 限制此列的值必须提供,不能为null
唯一约束(unique):在表中的多条数据,此列的值不能重复
主键约束(primary key):非空+唯一,能够唯一标识数据表中的一条数据
外键约束(foreign key):建立不同表之间的关联关系
create table books(
book_isbn char(4) unique primary key,
book_name varchar(10) not null,
book_author varchar(6),
primary key(字段名)
);
删除数据表主键约束:atler table 表名 drop primary key;
创建表之后添加主键约束:alter taable 表名 modify 字段名 类型 primary key;
主键自动增长:自动增长从1开始,每添加一条记录,列会自动+1,但如果删除某条记录,自动增长的数据不会重复
定义
create table type(
type_id int primary key auto_increment,
type_name varchar(20) not null,
type_remark varchar(100)
);
联合主键
将数据表中的多列组合在一起设置为表的主键
定义联合主键
create table grade(
stu_num char(8),
course_id int,
score int,
primary key(stu_num,course_id)
);
外键约束:在多表关联部分
三、DML数据操纵语言
用于完成对数据表中数据的插入,删除,修改操作
3.1 插入数据
insert into 表名(字段1,字段2....) values(value1,value2....);
示例:
insert into students(stu_num,stu_name,stu_gender,stu_age,stu_tel) values('20120101','张三','男','18','1300000000');
数据表后的字段名可以与表中字段顺序不一致,但值要与插入时的字段顺序对应
给表中所有字段添加数据,可以不写字段名,但是值必须要与数据表字段顺序对应
3.2 删除数据
从数据表中删除满足特定条件的数据
delete from 表名 where conditions;
示例:
delete from students where stu_num='20210101';
如果删除语句没有where,则表示删除当前数据表中所有记录
3.3 修改数据
对数据表已经添加的记录进行修改
update 表名 set 字段名=value [where conditions]
示例:
update students set stu_name='李四' where stu_num='20210101';
修改多列:update students set stu_name='李四',stu_age='20' where stu_num='20210101';
如果update语句没有where,则表示修改当前表中所有行
四、DQL数据查询语言
从数据表中提取满足特定条件的记录
单标查询
多表联合查询
4.1 查询基础语法
# 指定查询列:
select 字段名1,字段名2,字段名3..... from 表名;
# 查询所有列:
select * from 表名;
4.2 where子句
在删除、修改及查询的语句后都可以添加where语句(条件),用于筛选满足特定的条件的数据进行删除、修改、查询操作
条件:= != > < >= <= between and
多条件查询:and or not
like:在where子句的条件中,可以使用like关键字实现模糊查询
select * from 表名 where 字段名 like 'reg';
在like关键字后的reg表达式中:%表示任意多个字符(%o%包含字母o) _表示任意一个字符(_o%第二个字母为o)
4.3 对查询结果的处理
# 设置查询的列:
select 字段1,字段2... from students where 条件;
# 计算列:对从数据表中查询的记录进行一定的运算再显示出来。
select stu_name,2021-stu_age from students;
# 字段别名:可以为查询结果的列名取一个语义性更强的别名。
select stu_name as 别名,2021-stu_age as 别名 from students;
#消除重复:从查询结果中将重复的删除:
select distinct 字段名 from 表名;
# 查询结果排序order by排序:
升序:select * from 表名 where 条件 order by 字段名 asc;(默认)
降序:select * from 表名 where 条件 order by 字段名 desc;
多字段排序(先满足第一个,若1相同再按照2排序):select * from 表名 where 条件 order by 字段名1 desc,字段名2 asc;
4.4 聚合函数:
sql中提供了一些可以对查询的记录列进行计算的函数
count()统计函数:select count(字段名) from students where 条件;
max()最大值:select max(字段名) from students where 条件;
min()最小值:select min(字段名) from students where 条件;
sum()计算和:select sum(字段名) from students where 条件;
avg()求平均值:select avg(字段名) from students where 条件;
4.5 日期函数:
当我们向日期类型的列添加数据时,可以通过字符串类型赋值(字符串的格式必须是yyyy-MM-dd hh:mm:ss)
如果想要获取当前系统时间添加到日期类型的列,可以使用now() 或者 sysdata()
示例:
insert into students(stu_num,stu_name,stu_gender,stu_age,stu_tel,stu_qq,stu_enterence) value('20120104','hh','男','18','1300000004',10101010,2022-05-28 02:22:36);
insert into students(stu_num,stu_name,stu_gender,stu_age,stu_tel,stu_qq,stu_enterence) value('20120104','hh','男','18','1300000004',10101010,now());
4.6 字符串函数:
# concat拼接:将列的数据使用符号连接:
select concat(字段名1,'-',字段名2) from 表名;
# upper 将字段中所有转换成大写。
select upper(字段名) from 表名;
# lower 将字段中所有转换成小写。
select lower(字段名) from 表名;
# substring截取 (截取字段名,截取开始位置,截取长度):
select 主键,substring(字段名,7,4) from 表名;
4.7 分组查询
分组就是将数据表中的记录按照指定的类进行分组。
select 分组字段/聚合函数 from 表名 【where 条件】group by 分组字段 [having 条件]
select 后使用*显示对查询结果进行分组后,显示每组的第一条记录
select后通常显示分组字段和聚合函数(对分组后的数据进行统计,求和,平均值等)
语句执行顺序:先根据where条件从数据库查询记录,对查询记录进行分组,having对分组的结果再进行筛选。
示例:
#先对查询的学生信息按照性别分组,然后再分别统计每组学生的个数
select stu_gender,count(stu_num) from students group by stu_gender;
#按年龄进行分组,然后分别统计每组的人数,再筛选当前组人数大于1的组显示出来
select stu_age,count(stu_num) from students group by stu_age having count(stu_num)>1 order by stu_age;
分页查询
当数据表中的记录比较多的时候,如果一次性全部查询出来,用户的可读性就不好,因此我们对这些数据进行分页展示
select * from 表名 where... limit param1,param2
param1 int,表示获取查询语句的结果中的第一条数据的索引
param2 int,表示获取的查询记录条数
五、数据表的关联关系
mysql是一个关系型数据库,不仅可以存储数据,还可以维护数据与数据之间的关系——通过在数据表中添加字段建立外键约束
数据与数据之间的关系分为四种:
一对一关联
一对多关联
多对一关联
多对多关联
5.1 一对一关联
方案1:主键关联——两张数据表中主键一对一关联
constraint FK_STUDENTS_CLASSES foreign key(外键名) reference classes(class_id)
方案二:唯一外键——在任意一张表中添加一个字段添加外键约束与另一张表主键关联,并且将外键列添加唯一约束
alter table 表名 add constraint 外键名 foreign key(列名) reference 主表明(列名);
删除外键约束:alter table students drop foregin key FK_STUDENTS_CLASSES;
5.2 一对多与多对一
方案:在多的一端添加外键,与一的一端主键进行关联
5.3 多对多
方法:额外创建一张关系表来维护多对多关联——在关系表中定义两个外键,分别与两个数据表的主键进行关联
5.4 外键约束
外键约束——将一个列添加外键约束与另一张表的主键(唯一列)进行关联之后,这个外键约束的列添加的数据必须要在关联的主键字段中存在
5.5 外键约束级联操作
在添加外键时,设置级联修改和级联删除
#删除原有外键
alter table students drop foreign key FK-STUDENTS_CALSSES;
#重新添加外键,并设置级联修改和级联删除
alter table students add constraint FK-STUDENTS_CALSSES foreign key(cid) references classes(class_id) ON UPDATE CASCADE ON DELETE CASCADE;
六、连接查询(从多张表中提取数据)
在mysql中可以使用join实现多张表的联合查询——连接查询,join按照其功能不同分为三个操作:
inner join 内连接
left join 左连接
right join 右连接
6.1数据准备
创建班级信息表和学生信息表
6.2内连接 inner join
select * from tablename1 inner join tablename2;
6.2.1笛卡尔积
如果直接执行select * from tablename1 inner join tablename2;会获取两种数据表中的数据集合的笛卡尔积(依次使用tablename1表中的每一条记录去匹配tablename2的每条数据)
6.2.2内连接条件
使用where:先生成笛卡尔积再过滤(效率低)
select *from students inner join classes where students.cid=classes.class_id;
使用ON设置连接查询条件:先判断条件是否成立,再生成查询结果
select *from students inner join classes where students.cid=classes.class_id;
6.3左连接left join
显示左表中的所有数据,如果在右表中存在于左表记录满足匹配条件的数据,则进行匹配,如果右表中不存在匹配数据,则显示为null
select * from lefttable left join righttable on 匹配条件;
示例:select * from students left join classes ON students.cid=classes.class_id;
6.4右连接right join
显示右表所有数据
select * from lefttable right join righttable on 匹配条件;
示例:select *from students right join classes ON students.cid=classes.class_id;
6.5数据表别名
如果在连接查询的多张表中存在相同名字的字段,我们可以使用 表名.字段名来进行区分。如果表名太长不便于编写,可以使用数据表别名
示例:select s.*,c.class_name from students s inner join classes c on s.cid =c.class_id;
6.6子查询/嵌套查询
第二次查询是基于第一次查询结果进行的。
示例:查询班级名称为“Java2104”班级中的学生信息
传统:a.查询Java2104班的班级编号
select class_id from classes where class_name='Java2104';
b.查询此班级编号下的学生信息
select * from students where cid=1;
子查询:
select * from students where cid=(select class_id from classes where class_name='Java2104');
如果第一次查询结果的返回值是单列多行,使用in
select * from students where cid in (select class_id from classes where class_name like 'Java%');
如果第一次查询结果的返回是多行多列,将第一次的返回结果作为一个整体的虚拟表,再基于这个虚拟表查询(虚拟表需要别名)
select *from (select *from students where cid=1) t where t.stu_gender='男';