一、数据库
1.数据库的介绍
数据库就是存储和管理数据的仓库,数据按照一定的格式进行存储,用户可以对数据库中的数据进行增加、删除、修改和查询等操作。
2.数据的分类
- 关系型数据库
- 非关系型数据库
(1)关系型数据库
关系型数据库是指猜用了关系模型来组织数据的数据库,简单来说,关系模型指的就是二维表格模型,好比Excel文件中的表格,强调使用表格的方式来存储数据
关系型数据库中的核心元素:
- 数据行
- 数据列
- 数据表
- 数据库(数据表的集合)
2.常用的关系型数据库:
- Oracle
- MySQL
- Microsoft SQL Server
(2)非关系型数据库
- 非关系型数据库,又被成为NoSQL,(Not Only SQL),意为不仅仅是SQL,对NoSQL最普遍的定义是“非关联型的“,强调Key-Value的方式存储数据。
- 常用的非关系型数据库:
- MongoDB
- Redis
3.数据库的作用
数据库就是存储和管理数据的
(1)数据库的特点
- 持久化保存数据
- 读写速度极高
- 保证数据的完整性和有效性
(2)关系型数据库管理系统
- 关系型数据库管理系统的介绍
数据库管理系统(简称RDBMS)是为管理关系型数据库而设计的软件系统。
- 关系型数据库管理系统可以分为:
- 关系型数据库客户端软件
- 关系型疏忽库服务端软件
- 关系型数据库服务端软件:
主要负责管理不同的数据库,而每个数据库里面会有一系列的数据文件,数据文件是用来存储数据的,其实数据库就是一系列数据文件的集合
- 关系型数据库客户端软件
主要负责和关系型数据库服务端软件进行通信,向服务端传输数据或者从服务端获取数据
- 关系型数据库管理系统的效果图:
说明:(1)用户操作关系型数据库客户端,实现数据库相关操作
(2)关系型数据库客户端借助网络使用SQL语言和关系型数据库服务端进行通信
(3)关系型数据库服务端管理着不同的数据库,每个数据都有一系列的数据文件。
通过上面的效果图可得知,数据可客户端和数据库服务器想要通信需要通过SQL语言。
二、SQL语言
- SQL是结构化查询语言,是一种用来操作RDBMS的数据库语言,也就是通过SQL可以操作关系型数据库。
- SQL的作用是实现数据可客户端和数据库服务端的通信,SQL就是通信的桥梁
- SQL语言主要分为:
- DQL,数据查询语言,用于对数据进行查询,如select
- DML,数据操作语言,对数据进行增删改,如insert,delete,update
- TPL,事务处理语言,对事务进行处理包括begin,commit,rollback
- DCL,数据控制语言,进行授权与权限回收,grant,revoke
- DLL,数据定义语言,对数据库、表的管理等。如create,drop
说明:
- 对于程序员来讲,重点是数据的增、删、改、查,必须熟练编写DQL、DML,能够编写DDL完成数据库、表的操作,其它操作如TPL、DCL了解即可.
- SQL语言不区分大小写
- MySQL数据库
4.MySQL数据库的介绍
MySQL是一个关系型数据库管理系统,在WEB应用方面,MySQL是最好的RDBMS应用软件,由它是由瑞典MySQL AB 公司开发,目前属于 Oracle 旗下产品,MySQL 是最流行的关系型数据库管理系统中的一个。
5.MySQL的特点:
(1)MySQL是开源的,所以你不需要支付额外的费用。
(2)MySQL支持大型的数据库。可以处理拥有上千万条记录的大型数据库。
(3)MySQL使用标准的SQL数据语言形式。
(4)MySQL可以安装在不同的操作系统,并且提供多种编程语言的操作接口。这些编程语言包括C、C++、Python、Java、Ruby等等。
6.MySQL数据库的安装
(1)MySQL数据库服务端软件的安装
在Ubuntu中打开终端,输入安装命令
Sudo apt-get install mysql-server
(2)显示MySQL服务端安装包信息
Apt-cache show mysql-server
(3)查看MySQL服务效果图
Ps -aux | grep mysql
(4)查看MySQL服务状态:
sudo service mysql status
(5)停止MySQL服务:
sudo service mysql stop
(6)启动MySQL服务:
sudo service mysql start
(7)重启MySQL服务:
sudo service mysql restart
(8)MySQL配置文件的介绍:
配置文件路径为: /etc/mysql/mysql.conf.d/mysqld.cnf
主要配置信息说明:
- port表示端口号,默认为3306
- bind-address表示服务器绑定的ip,默认为127.0.0.1
- datadir表示数据库保存路径,默认为/var/lib/mysql
- log_error表示错误日志,默认为/var/log/mysql/error.log
7.MySQL数据库客户端软件的安装:
客户端是程序员或者dba使用的软件,通过socket方式与服务端程序通信。
常用的MySQL数据库客户端软件有
- 图形化界面客户端(比较简单)
- 命令行客户端mysql
命令行客户端mysql的安装
在Ubuntu中打开终端,输入下面的命令:
sudo apt-get install mysql-client
8.MySQL客户端的使用
mysql -uroot -p
- -u: 表示MySQL服务端的用户名
- -p: 表示MySQL服务端的密码
- quit 或者 exit 或者 ctr + d 表示退出
三、数据库数据类型和约束
- 数据类型是指在创建表的时候为表中字段指定数据类型,只有数据符合要求才能存储起来,使用数据类型的原则是够用九行,尽量使用取值范围小的,这样可以节省很多存储空间。
- 常用的数据类型如下:
- 整数:int ,bit
- 小数:decimal
- 字符串:varchar,var
- 日期类型:data,time,datetime
- 枚举类型:enum
数据类型说明:
- decimal表示浮点数,如 decimal(5, 2) 表示共存5位数,小数占 2 位.
- char表示固定长度的字符串,如char(3),如果填充'ab'时会补一个空格为'ab ',3表示字符数
- varchar表示可变长度的字符串,如varchar(3),填充'ab'时就会存储'ab',3表示字符数
- 对于图片、音频、视频等文件,不存储在数据库中,而是上传到某个服务器上,然后在表中存储这个文件的保存路径.
- 字符串 text 表示存储大文本,当字符大于 4000 时推荐使用, 比如技术博客.
3.数据约束
数据约束是指在数据丶数据类型限定的基础上额外增加的要求
常见的约束如下:
- 主键 primary key: 物理上存储的顺序. MySQL 建议所有表的主键字段都叫 id, 类型为 int unsigned.
- 非空 not null: 此字段不允许填写空值.
- 惟一 unique: 此字段的值不允许重复.
- 默认 default: 当不填写字段对应的值会使用默认值,如果填写时以填写为准.
- 外键 foreign key: 对关系字段进行约束, 当为关系字段填写值时, 会到关联的表中查询此值是否存在, 如果存在则填写成功, 如果不存在则填写失败并抛出异常.
(1)数据类型附表:
1. 整数类型
类型 | 字节大小 | 有符号范围(Signed) | 无符号范围(Unsigned) |
TINYINT | 1 | -128 ~ 127 | 0 ~ 255 |
SMALLINT | 2 | -32768 ~ 32767 | 0 ~ 65535 |
MEDIUMINT | 3 | -8388608 ~ 8388607 | 0 ~ 16777215 |
INT/INTEGER | 4 | -2147483648 ~2147483647 | 0 ~ 4294967295 |
BIGINT | 8 | -9223372036854775808 ~ 9223372036854775807 | 0 ~ 18446744073709551615 |
2. 字符串
类型 | 说明 | 使用场景 |
CHAR | 固定长度,小型数据 | 身份证号、手机号、电话、密码 |
VARCHAR | 可变长度,小型数据 | 姓名、地址、品牌、型号 |
TEXT | 可变长度,字符个数大于 4000 | 存储小型文章或者新闻 |
LONGTEXT | 可变长度, 极大型文本数据 | 存储极大型文本数据 |
3. 时间类型
类型 | 字节大小 | 示例 |
DATE | 4 | '2020-01-01' |
TIME | 3 | '12:29:59' |
DATETIME | 8 | '2020-01-01 12:29:59' |
YEAR | 1 | '2017' |
TIMESTAMP | 4 | '1970-01-01 00:00:01' UTC ~ '2038-01-01 00:00:01' UTC |
说明:数据类型和约束保证了表中的数据的准确性和完整性
四、数据库的基本操作
1.登录数据库
登录数据库
mysql -uroot -p
2.退出数据库
exit,quit,ctrl+d
3.登录数据成功后可查看当前时间
select now();
五、数据库操作的基本SQL语句
1.查看所有数据库
show databases;
2.创建数据库
create database 数据库名 charset=utf8;
3.使用数据库
use 数据库名;
4.查看当前正在使用的数据库
select database();
5.删除数据库
drop database 数据库名;
六、表结构操作的SQL语句
1.查看当前数据库中的所有表
show tabales;
2.创建表
-
create table students( id int unsigned primary key auto_increment not null COMMENT 序号, name varchar(20) not null COMMENT 姓名, age tinyint unsigned default 0 COMMENT 年龄, height decimal(5,2) COMMENT 身高, gender enum('男','女' ,'保密') COMMENT 性别 );
说明:create table 表名(
字段名称 数据类型 可选的约束条件,
column1 datatype contrai,
...
);
3.查看表结构的描述信息
desc 表名;
4.修改表-添加字段
alter table 表名 add 字段 字段类型 约束;
5.修改表-修改字段类型
alter table 表名 modify 字段 字段类型 约束;
6.修改表-修改字段名和字段类型
alter table 表名 change 原字段 新字段 类型 约束;
7.修改表-删除字段
alter table 表名 drop 字段名;
8.查看创建表的SQL语句
show create table 表名;
9.查看创建库的SQL语句
show create database 库名;
10.删除表
drop table 表名;
七、表数据的操作SQL语句
1.查询数据
select * from 表名;
select 列1,列2,… from 表名;
2.添加数据
全列插入,值的顺序与表结构字段顺序完全一一对应
insert into 表名 values(…),(…),…
部分列插入:值的顺序与给出的列的顺序一一对应
insert into 表名 (列1,列2,..) values(值1,值2,..),(值1,值2…)
说明:
- 主键列是自动增长,但是在全列插入时需要占位,通常使用空值(0或者null或者default)
- 在全列插入时,如果字段列有默认值可以使用 default 来占位,插入后的数据就是之前设置的默认值
3.修改数据
update 表名 set 列1=值1,列=值2….where 条件
4.删除数据
delete from 表名 where 条件
问题:
上面的操作称之为物理删除,一旦删除就不容易恢复,我们可以使用逻辑删除的方式来解决这个问题。
(1)添加删除字段,0表示未删除,1表示删除
alter table students add is_delete bit default 0;
(2)逻辑删除数据(其实就是修改操作)
update students set is_delete = 1 where id = 3;
八、as和distinct关键字
1. as关键字
在使用SQL语句显示结果的时候,往往在屏幕显示的字段名并不具备良好的可读性,此时可以使用 as 给字段起一个别名。
-
使用 as 给字段起别名 select id as 序号, name as 名字, gender as 性别 from students; 可以通过 as 给表起别名 -- 如果是单表查询 可以省略表名 select id, name, gender from students; -- 表名.字段名 select students.id,students.name,students.gender from students; -- 可以通过 as 给表起别名 select s.id,s.name,s.gender from students as s;
说明:
- 在这里给表起别名看起来并没有什么意义,然而并不是这样的,我们在后期学习 自连接 的时候,必须要对表起别名。
2.distinct关键字
distinct可以去除重复数据行。
select distinct 列1,... from 表名
例:
查询班级中学生的性别
select name, gender from students;
-- 看到了很多重复数据 想要对其中重复数据行进行去重操作可以使用 distinct
select distinct name, gender from students;
九、where条件查询的介绍
使用where条件查询可以对表中的数据进行筛选,条件成立的记录会出现在结果集中。where语句支持的运算符:
- 比较运算符
- 逻辑运算符
- 模糊查询
- 范围查询
- 空判断
1.where条件查询语法格式如下:
select * from 表名 where 条件;
例:
select * from students where id = 1;
2. 比较运算符查询
- 等于: =
- 大于: >
- 大于等于: >=
- 小于: <
- 小于等于: <=
- 不等于: != 或 <>
例1:查询编号大于3的学生:
select * from students where id > 3;
例2:查询编号不大于4的学生:
select * from students where id <= 4;
例3:查询姓名不是“黄蓉”的学生:
select * from students where name != '黄蓉';
例4:查询没被删除的学生:
select * from students where is_delete=0;
3. 逻辑运算符查询
- and
- or
- not
例1:查询编号大于3的女同学:
select * from students where id > 3 and gender=0;
例2:查询编号小于4或没被删除的学生:
select * from students where id < 4 or is_delete=0;
例3:查询年龄不在10岁到15岁之间的学生:
select * from students where not (age >= 10 and age <= 15);
说明:
- 多个条件判断想要作为一个整体,可以结合‘()’。
4. 模糊查询
- like是模糊查询关键字
- %表示任意多个任意字符
- _表示一个任意字符
例1:查询姓黄的学生:
select * from students where name like '黄%';
例2:查询姓黄并且“名”是一个字的学生:
select * from students where name like '黄_';
例3:查询姓黄或叫靖的学生:
select * from students where name like '黄%' or name like '%靖';
5. 范围查询
- between .. and .. 表示在一个连续的范围内查询
- in 表示在一个非连续的范围内查询
例1:查询编号为3至8的学生:
select * from students where id between 3 and 8;
例2:查询编号不是3至8的男生:
select * from students where (not id between 3 and 8) and gender='男';
6. 空判断查询
- 判断为空使用: is null
- 判断非空使用: is not null
例1:查询没有填写身高的学生:
select * from students where height is null;
注意:
- 不能使用 where height = null 判断为空
- 不能使用 where height != null 判断非空
- null 不等于 '' 空字符串
7. 小结
- 常见的比较运算符有 >,<,>=,<=,!=
- 逻辑运算符and表示多个条件同时成立则为真,or表示多个条件有一个成立则为真,not表示对条件取反
- like和%结合使用表示任意多个任意字符,like和_结合使用表示一个任意字符
- between-and限制连续性范围 in限制非连续性范围
- 判断为空使用: is null
- 判断非空使用: is not null
十、排序
1. 排序查询语法
排序查询语法:
select * from 表名 order by 列1 asc|desc [,列2 asc|desc,...]
语法说明:
- 先按照列1进行排序,如果列1的值相同时,则按照 列2 排序,以此类推
- asc从小到大排列,即升序
- desc从大到小排序,即降序
- 默认按照列值从小到大排序(即asc关键字)
例1:查询未删除男生信息,按学号降序:
select * from students where gender=1 and is_delete=0 order by id desc;
例2:显示所有的学生信息,先按照年龄从大-->小排序,当年龄相同时 按照身高从高-->矮排序:
select * from students order by age desc,height desc;
- 排序使用 order by 关键字
- asc 表示升序
- desc 表示降序
十二、分页查询
1. 分页查询的介绍
当我们在京东购物,浏览商品列表的时候,由于数据特别多,一页显示不完,一页一页的进行显示,这就是分页查询
2. 分页查询的语法
select * from 表名 limit start,count
说明:
- limit是分页查询关键字
- start表示开始行索引,默认是0
- count表示查询条数
例1:查询前3行男生信息:
select * from students where gender=1 limit 0,3;
简写
select * from students where gender=1 limit 3;
3. 分页查询案例
已知每页显示m条数据,求第n页显示的数据
提示: 关键是求每页的开始行索引
查询学生表,获取第n页数据的SQL语句:
select * from students limit (n-1)*m,m
十三、聚合函数
1. 聚合函数的介绍
聚合函数又叫组函数,通常是对表中的数据进行统计和计算,一般结合分组(group by)来使用,用于统计和计算分组数据。
常用的聚合函数:
- count(col): 表示求指定列的总行数
- max(col): 表示求指定列的最大值
- min(col): 表示求指定列的最小值
- sum(col): 表示求指定列的和
- avg(col): 表示求指定列的平均值
2. 求总行数
-- 返回非NULL数据的总行数.
select count(height) from students;
-- 返回总行数,包含null值记录;
select count(*) from students;
3. 求最大值
-- 查询女生的编号最大值
select max(id) from students where gender = 2;
4. 求最小值
-- 查询未删除的学生最小编号
select min(id) from students where is_delete = 0;
5. 求和
-- 查询男生的总身高
select sum(height) from students where gender = 1;
-- 平均身高
select sum(height) / count(*) from students where gender = 1;
6. 求平均值
-- 求男生的平均身高, 聚合函数不统计null值,平均身高有误
select avg(height) from students where gender = 1;
-- 求男生的平均身高, 包含身高是null的
select avg(ifnull(height,0)) from students where gender = 1;
说明
- ifnull函数: 表示判断指定字段的值是否为null,如果为空使用自己提供的值。
7. 聚合函数的特点
- 聚合函数默认忽略字段为null的记录 要想列值为null的记录也参与计算,必须使用ifnull函数对null值做替换。
十四、分组查询
1. 分组查询介绍
分组查询就是将查询结果按照指定字段进行分组,字段中数据相等的分为一组。
分组查询基本的语法格式如下:
GROUP BY 列名 [HAVING 条件表达式] [WITH ROLLUP]
说明:
- 列名: 是指按照指定字段的值进行分组。
- HAVING 条件表达式: 用来过滤分组后的数据。
- WITH ROLLUP:在所有记录的最后加上一条记录,显示select查询时聚合函数的统计和计算结果
2. group by的使用
group by可用于单个字段分组,也可用于多个字段分组
-- 根据gender字段来分组
select gender from students group by gender;
-- 根据name和gender字段进行分组
select name, gender from students group by name, gender;
3. group by + group_concat()的使用
group_concat(字段名): 统计每个分组指定字段的信息集合,每个信息之间使用逗号进行分割
-- 根据gender字段进行分组, 查询gender字段和分组的name字段信息
select gender,group_concat(name) from students group by gender;
4. group by + 聚合函数的使用
-- 统计不同性别的人的平均年龄
select gender,avg(age) from students group by gender;
-- 统计不同性别的人的个数
select gender,count(*) from students group by gender;
5. group by + having的使用
having作用和where类似都是过滤数据的,但having是过滤分组数据的,只能用于group by
-- 根据gender字段进行分组,统计分组条数大于2的
select gender,count(*) from students group by gender having count(*)>2;
6. group by + with rollup的使用
with rollup的作用是:在最后记录后面新增一行,显示select查询时聚合函数的统计和计算结果
-- 根据gender字段进行分组,汇总总人数
select gender,count(*) from students group by gender with rollup;
-- 根据gender字段进行分组,汇总所有人的年龄
select gender,group_concat(age) from students group by gender with rollup;
7. 小结
- group by 根据指定的一个或者多个字段对数据进行分组
- group_concat(字段名)函数是统计每个分组指定字段的信息集合
- 聚合函数在和 group by 结合使用时, 聚合函数统计和计算的是每个分组的数据
- having 是对分组数据进行条件过滤
- with rollup在最后记录后面新增一行,显示select查询时聚合函数的统计和计算结果
十五、连接查询
1.连接查询的分类
- 自连接
- 右连接
- 左连接
- 内连接
- 内连接
2.内连接查询语法格式:
查询两个表中符合条件的共有记录
select 字段 from 表1 inner join 表2 on 表1.字段1 = 表2.字段2
说明:
- inner join 就是内连接查询关键字
- on 就是连接查询条件
例1:使用内连接查询学生表与班级表:
select * from students as s inner join classes as c on s.cls_id = c.id;
3.左连接查询
以左表为主根据条件查询右表数据,如果根据条件查询右表数据不存在使用null值填充
左连接查询语法格式:
select 字段 from 表1 left join 表2 on 表1.字段1 = 表2.字段2
说明:
- left join 就是左连接查询关键字
- on 就是连接查询条件
- 表1 是左表
- 表2 是右表
例1:使用左连接查询学生表与班级表:
select * from students as s left join classes as c on s.cls_id = c.id;
4.右连接查询
以右表为主根据条件查询左表数据,如果根据条件查询左表数据不存在使用null值填充
右连接查询语法格式:
select 字段 from 表1 right join 表2 on 表1.字段1 = 表2.字段2
说明:
- right join 就是右连接查询关键字
- on 就是连接查询条件
- 表1 是左表
- 表2 是右表
例1:使用右连接查询学生表与班级表:
select * from students as s right join classes as c on s.cls_id = c.id;
4. 自连接查询
左表和右表是同一个表,根据连接查询条件查询“两个”表中的数据,自连接查询就是把一张表模拟成左右两张表,然后进行连接查询,连接的表还是这张表本身。
(1)自连接查询的使用方法
areas 表中结构和数据如下:
使用自连接方法进行查询:
语法:表 as 别名1 inner join 表 as 别名2 on 别名1.字段 条件 别名2.字段;
select * from areas as c inner join areas as p on c.pid = p.id where p.title=’辽宁省’
结果如下:
说明:
- 自链接查询必须对表起别名
- 这里再强调一个命令:source
- source是执行文件给表导入统一导入数据的一条命令
- 例:source students.sql // 将students.sql文件中的数据导入students表中