数据库
数据库介绍
什么是数据库
数据存储的仓库,其本质也是一个文件系统
数据库会按照特定的格式对数据进行存储,用户可以对数据库中的数据进行增加,修改,删除及查询操作。
数据库管理系统层次
SQL介绍
什么是SQL
SQL全称Structured Query Language,翻译为:结构化查询语言
是用来操作数据库的一种语言,通过sql可以实现数据库、数据表、数据记录的增删改查
我们一般把增删改查称为CRUD:create创建、retrieve 检索、update 修改、delete删除
SQL分类
DDL(Data Definition Language) 数据定义语言:用来定义数据库,数据表
DML(Data Manipulation Language) 数据操作语言:用来对数据库中表的数据进行增删改
DQL(Data Query Language) 数据查询语言:用来对数据库中表的数据进行查询
DCL(Data Control Language) 数据控制语言:用来定义数据库的访问权限和安全级别以及创建用户
TCL(Transaction Control Language) 事务控制语言:用于控制数据库的事务操作
书写标准
-
SQL可以单行书写,也可以多行书写, 它以;结束一条SQL语句
-
在windows环境中SQL语句是不区分大小写的
-
在SQL中可以使用注释,一般有两种:单行注释
-- 注释内容
和 多行注释/* 注释内容 */
数据库操作
连接数据库
-- 语法
mysql [-h 服务器地址 -P 端口号] -u用户名 -p密码--实例
mysql -uroot -p
==经典错误1: ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost' (10061) 表示数据库服务停止了==
==经典错误2: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) 表示你的用户名和密码不对==
创建数据库
需求:创建一个名为db1的数据库
-- 语法
create database [IF NOT EXISTS] 数据库名;-- 实例
create database if not exists db1;
查询数据库
需求:查看所有数据数据库
-- 语法
show databases;
删除数据库
需求:删除名为db1的数据库
-- 语法
drop database [IF EXISTS] 数据库名;-- 实例
drop database if exists db1;
切换数据库
需求:查看正在使用的数据库
-- 语法
select database();
需求:切换正在使用的数据库为db1(db1必须存在)
-- 语法
use 数据库名;-- 实例
create database if not exists db1;
use db1;
select database();
数据表操作
创建数据表
基本语法
需求:在db1中创建一张名为student1的数据表,表中字段的要求如下
id:标识,数字类型
name:姓名,字符串类型,长度限制最多30个字符
gender:性别,字符串类型,长度限制为1个字符
age:年龄,数值类型
birthday:生日,日期类型,格式YYYY-mm-dd
-- 语法create table 表名( 字段名1 字段类型1(字段长度) [ comment 字段1注释 ], -- 不是;号 字段名2 字段类型2(字段长度) [ comment 字段2注释 ], .... 字段名n 字段类型n(字段长度) [ comment 字段n注释 ]-- 最后一列后面是没有,的) [ comment 表注释 ];-- 实例
create table student1(id int comment '标识',name varchar(30) comment '姓名',gender varchar(1) comment '性别',age int comment '年龄',birthday date comment '生日'
) comment '学生表';
数据类型
下面列出了最常见的一些数据类型,详细的参照资料中的单独文档
大分类 | 类型 | 描述 |
---|---|---|
数值类型 | tinyint | 小整数 |
int | 大整数 | |
bigint | 大整数 | |
float | 浮点数类型 | |
double | 浮点数类型 | |
字符类型 | varchar(30) | 可变长度字符串,如果插入的长度小于定义长度时,插入多长就存多长 |
char(11) | 固定长度字符串,如果插入的长度小于定义长度,则可以用空格进行填充 | |
日期类型 | date | 日期,格式:yyyy-MM-dd |
datetime | 日期时间,格式:yyyy-MM-dd HH:mm:ss |
-- 实例
create table student2(id int comment '标识',name varchar(30) comment '姓名',gender char(1) comment '性别', -- 性别,长度固定,采用char更合适age tinyint unsigned comment '年龄', -- 年龄,采用无符号的tinyint更合适birthday date comment '生日'
) comment '学生表';
数据约束
约束用于对表中的数据进行进一步的限制,一般作用在表中的字段上,用于保证数据的正确性。
约束种类有:主键约束、唯一约束、非空约束、默认值、外键约束。
约束 | 描述 | 关键字 |
---|---|---|
主键约束 | 主键是一行数据的唯一标识,要求非空且唯一;一张表只能有一个注解列 | primary key(auto increment自增) |
非空约束 | 限制该字段值不能为null | not null |
唯一约束 | 保证字段的所有数据都是唯一、不重复的 | unique |
默认约束 | 保存数据时,如果未指定该字段值,则采用默认值 | default |
外键约束 | 让两张表的数据建立连接,保证数据的一致性和完整性 | foreign key |
需求:在db1中创建一张名为student3的数据表,表中字段的要求如下:
id:数字类型,
主键字段,并且数值自动增长
name:字符串类型,长度限制最多30个字符,
要求姓名不能出现重复
gender:字符串类型,长度限制为1个字符,
如果不插入,默认为男
age:数值类型,
不能为空
birthday:日期类型,格式YYYY-mm-dd
-- 语法create table 表名( 字段名1 字段类型1(字段长度) [ 约束 ] [ comment 字段1注释 ], -- 不是;号 字段名2 字段类型2(字段长度) [ 约束 ] [ comment 字段2注释 ], .... 字段名n 字段类型n(字段长度) [ 约束 ] [ comment 字段2注释 ] -- 最后一列后面是没有,的) [ comment 表注释 ];-- 实例
create table student3(id int primary key comment '标识',name varchar(30) unique comment '姓名',gender char(1) default '男' comment '性别',age tinyint unsigned not null comment '年龄',birthday date comment '生日'
) comment '学生表';
==添加完约束之后,可以查询添加一些违法数据,观察报错==
-- id重复会报错 Duplicate entry '1' for key 'PRIMARY'
-- 姓名重复会报错 Duplicate entry '黑张三' for key 'name'
-- age为null,会报错 Column 'age' cannot be null
查询数据表
==查询当前数据库所有表:==show tables
==查询表结构:==desc 表名
==查询建表语句:==show create table 表名
-- 1. 查看当前库中的所有数据表
show tables;-- 2. 查看student3表的表结构
desc student3;-- 3. 查看student3表的建表语句
show create table student3;
修改数据表
==添加字段:==alter table 表名 add 字段名 类型(长度)
==修改字段类型:==alter table 表名 modify 字段名 新数据类型(长度)
==修改字段名和字段类型:==alter table 表名 change 旧字段名 新字段名 类型 (长度)
==删除字段:==alter table 表名 drop column 字段名
==修改表名:== rename table 表名 to 新表名
-- 1. 修改student3表, 添加一列description 变长字符串类型,长度30
alter table student3 add description varchar(30);-- 2. 修改student3表 description列为定长字符串类型,长度40
alter table student3 modify description char(40);-- 3. 修改student3表 description列名为descr,变长字符串类型,长度20
alter table student3 change description descr varchar(20);-- 4. 删除student3表的descr列
alter table student3 drop column descr;-- 5. 修改student3表的名称为stu
rename table student3 to stu;
删除数据表
==删除表:==drop table [ if exists ] 表名
-- 删除stu表
drop table stu;
表设计案例
需求:参考资料中提供的《智能学习辅助系统》页面原型,设计员工管理模块的表结构(暂不考虑所属部门字段)
drop table if exists tb_emp;
create table tb_emp(id int primary key auto_increment comment '主键ID',username varchar(20) unique not null comment '用户名',password varchar(32) default '123456' null comment '密码',name varchar(10) not null comment '姓名',gender tinyint unsigned not null comment '性别, 1 男, 2 女',image varchar(300) null comment '图像url',job tinyint unsigned null comment '职位, 1 班主任 , 2 讲师 , 3 学工主管, 4 教研主管',entrydate date null comment '入职日期',create_time datetime not null comment '创建时间',update_time datetime not null comment '修改时间'
) comment '员工表';
增删改数据
插入数据
==指定字段添加数据==:insert into 表名 (字段名1, 字段名2) values (值1, 值2)
==全部字段添加数据==:insert into 表名 values (值1, 值2, ...)
-- 1. 为 tb_emp 表的 username, name, gender, create_time, update_time 字段插入值
insert into tb_emp(username, name, gender, create_time, update_time) values ('zhangsan', '张三', 1, now(), now());-- 2. 为 tb_emp 表的 所有字段插入值
insert into tb_emp(id, username, password, name, gender, image, job, entrydate, create_time, update_time)values (null, 'lisi', 'admin', '李四', 0, '1.jpg', 1, '2011-01-01', now(), now());insert into tb_emp values (null, 'wangwu', '123', '王五', 0, '1.jpg', 1, '2011-01-01', now(), now());-- 注意 1. 插入数据时,指定的字段顺序需要与值的顺序是一一对应的2. 字符串和日期型数据应该包含在引号中
==批量添加数据(指定字段)==:insert into 表名 (字段名1, 字段名2) values (值1, 值2), (值1, 值2)
==批量添加数据(全部字段)==:insert into 表名 values (值1, 值2, ...), (值1, 值2, ...、
-- 3. 批量为 为 tb_emp 表的 username , name , gender, create_time, update_time 字段插入数据
insert into tb_emp(username, name, gender, create_time, update_time)values ('zhangsan1', '张三1', 1, now(), now()),('zhangsan2', '张三2', 1, now(), now());
修改数据
==修改数据:==update 表名 set 字段名1 = 值1 , 字段名2 = 值2 , .... [ where 条件 ]
-- 1. 将 tb_emp 表的所有员工的入职日期更新为'2010-01-01'
update tb_emp set entrydate = '2010-01-01';-- 2. 将 tb_emp 表的ID为1员工 姓名name字段更新为'西瓜'
update tb_emp set name = '西瓜' where id = 1;-- 3. 将 tb_emp 表的ID为1员工 姓名name字段更新为'广智',入职日期更新为'2022-01-01'
update tb_emp set name = '传智',entrydate = '2022-01-01' where id = 1;-- 注意 修改语句中如果不加条件,则将所有数据都会被修改!
删除数据
==删除数据:==delete from 表名 [ where 条件 ]
-- 1. 删除 tb_emp 表中ID为1的员工
delete from tb_emp where id = 1;-- 2. 删除 tb_emp 表中的所有员工
delete from tb_emp;-- 注意删除语句中如果不加条件,则将所有数据都会被删除!
小结
-- 增加
insert into 表名 values(值1,值2...)-- 修改
update 表名 set 列1=值1,列2=值2 [where 条件]-- 删除
delete from 表名 [where 条件]
查询数据
创建数据库,使用下面sql创建数据
-- 员工管理(带约束)
drop table if exists tb_emp;
create table tb_emp (id int unsigned primary key auto_increment comment 'ID',username varchar(20) not null unique comment '用户名',password varchar(32) default '123456' comment '密码',name varchar(10) not null comment '姓名',gender tinyint unsigned not null comment '性别, 说明: 1 男, 2 女',image varchar(300) comment '图像',job tinyint unsigned comment '职位, 说明: 1 班主任,2 讲师, 3 学工主管, 4 教研主管',entrydate date comment '入职时间',create_time datetime not null comment '创建时间',update_time datetime not null comment '修改时间'
) comment '员工表';-- 准备测试数据
INSERT INTO tb_emp (id, username, password, name, gender, image, job, entrydate, create_time, update_time) VALUES(1, 'jinyong', '123456', '金庸', 1, '1.jpg', 4, '2000-01-01', '2022-10-27 16:35:33', '2022-10-27 16:35:35'),(2, 'zhangwuji', '123456', '张无忌', 1, '2.jpg', 2, '2015-01-01', '2022-10-27 16:35:33', '2022-10-27 16:35:37'),(3, 'yangxiao', '123456', '杨逍', 1, '3.jpg', 2, '2008-05-01', '2022-10-27 16:35:33', '2022-10-27 16:35:39'),(4, 'weiyixiao', '123456', '韦一笑', 1, '4.jpg', 2, '2007-01-01', '2022-10-27 16:35:33', '2022-10-27 16:35:41'),(5, 'changyuchun', '123456', '常遇春', 1, '5.jpg', 2, '2012-12-05', '2022-10-27 16:35:33', '2022-10-27 16:35:43'),(6, 'xiaozhao', '123456', '小昭', 2, '6.jpg', 3, '2013-09-05', '2022-10-27 16:35:33', '2022-10-27 16:35:45'),(7, 'jixiaofu', '123456', '纪晓芙', 2, '7.jpg', 1, '2005-08-01', '2022-10-27 16:35:33', '2022-10-27 16:35:47'),(8, 'zhouzhiruo', '123456', '周芷若', 2, '8.jpg', 1, '2014-11-09', '2022-10-27 16:35:33', '2022-10-27 16:35:49'),(9, 'dingminjun', '123456', '丁敏君', 2, '9.jpg', 1, '2011-03-11', '2022-10-27 16:35:33', '2022-10-27 16:35:51'),(10, 'zhaomin', '123456', '赵敏', 2, '10.jpg', 1, '2013-09-05', '2022-10-27 16:35:33', '2022-10-27 16:35:53'),(11, 'luzhangke', '123456', '鹿杖客', 1, '11.jpg', 2, '2007-02-01', '2022-10-27 16:35:33', '2022-10-27 16:35:55'),(12, 'hebiweng', '123456', '鹤笔翁', 1, '12.jpg', 2, '2008-08-18', '2022-10-27 16:35:33', '2022-10-27 16:35:57'),(13, 'fangdongbai', '123456', '方东白', 1, '13.jpg', 1, '2012-11-01', '2022-10-27 16:35:33', '2022-10-27 16:35:59'),(14, 'zhangsanfeng', '123456', '张三丰', 1, '14.jpg', 2, '2002-08-01', '2022-10-27 16:35:33', '2022-10-27 16:36:01'),(15, 'yulianzhou', '123456', '俞莲舟', 1, '15.jpg', 2, '2011-05-01', '2022-10-27 16:35:33', '2022-10-27 16:36:03'),(16, 'songyuanqiao', '123456', '宋远桥', 1, '16.jpg', 2, '2010-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:05'),(17, 'chenyouliang', '12345678', '陈友谅', 1, '17.jpg', null, '2015-03-21', '2022-10-27 16:35:33', '2022-10-27 16:36:07'),(18, 'zhang1', '123456', '张一', 1, '2.jpg', 2, '2015-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:09'),(19, 'zhang2', '123456', '张二', 1, '2.jpg', 2, '2012-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:11'),(20, 'zhang3', '123456', '张三', 1, '2.jpg', 2, '2018-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:13'),(21, 'zhang4', '123456', '张四', 1, '2.jpg', 2, '2015-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:15'),(22, 'zhang5', '123456', '张五', 1, '2.jpg', 2, '2016-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:17'),(23, 'zhang6', '123456', '张六', 1, '2.jpg', 2, '2012-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:19'),(24, 'zhang7', '123456', '张七', 1, '2.jpg', 2, '2006-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:21'),(25, 'zhang8', '123456', '张八', 1, '2.jpg', 2, '2002-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:23'),(26, 'zhang9', '123456', '张九', 1, '2.jpg', 2, '2011-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:25'),(27, 'zhang10', '123456', '张十', 1, '2.jpg', 2, '2004-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:27'),(28, 'zhang11', '123456', '张十一', 1, '2.jpg', 2, '2007-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:29'),(29, 'zhang12', '123456', '张十二', 1, '2.jpg', 2, '2020-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:31');
基本查询
==查询指定字段==:select 字段1, 字段2, 字段3 from 表名
==查询所有字段==:select * from 表名
==设置别名==:select 字段1 [ as 别名1 ] , 字段2 [ as 别名2 ] from 表名
==去除重复记录==:select distinct 字段列表 from 表名
-- 1. 查询指定字段 name,entrydate 并返回
select name,entrydate from tb_emp;-- 2. 查询返回所有字段
select * from tb_emp;-- 3. 查询所有员工的 name,entrydate, 并起别名(姓名、入职日期) --- as 关键字可以省略
select name as '姓名' ,entrydate as '入职日期' from tb_emp;
select name '姓名' ,entrydate '入职日期' from tb_emp;-- 4. 查询员工有哪几种职位(不要重复)
select distinct job from tb_emp;
条件查询
==条件查询==:select 字段列表 from 表名
where 条件列表
比较运算符 | 功能 | 逻辑运算符 | 功能 | |
---|---|---|---|---|
> | 大于 | and | 并且 (多个条件同时成立) | |
>= | 大于等于 | or | 或者 (多个条件任意一个成立) | |
< | 小于 | ! | 非 , 不是 | |
<= | 小于等于 | |||
= | 等于 | |||
<> 或 != | 不等于 | |||
is null | 是null | |||
between ... and ... | 在某个范围之内(含最小、最大值) | |||
in(...) | 在in之后的列表中的值,多选一 | |||
like 占位符 | 模糊匹配(_匹配单个字符, %匹配任意个字符) |
-- 1-1. 查询 姓名 为 杨逍 的员工
select * from tb_emp where name = '杨逍';-- 1-2. 查询在 id小于等于5 的员工信息
select * from tb_emp where id <=5;-- 1-3. 查询 密码不等于 '123456' 的员工信息
select * from tb_emp where password != '123456';-- 1-4. 查询 没有分配职位 的员工信息
select * from tb_emp where job is null;-- 1-5. 查询 有职位 的员工信息
select * from tb_emp where job is not null;-- 2-1. 查询 id<=5 并且 job=2 的员工信息
select * from tb_emp where id <=5 and job = 2;-- 2-1. 查询 id<=5 或者 job=2 的员工信息
select * from tb_emp where id <=5 or job = 2;-- 3-1. 查询入职日期 在 '2000-01-01' (包含) 到 '2010-01-01'(包含) 之间的员工信息
select * from tb_emp where entrydate >= '2000-01-01' and entrydate <= '2010-01-01';
select * from tb_emp where entrydate between '2000-01-01' and '2010-01-01';-- 3-2. 查询职位是 2 (讲师), 3 (学工主管), 4 (教研主管) 的员工信息
select * from tb_emp where job = 2 or job = 3 or job = 4;
select * from tb_emp where job in (2,3,4);-- 4-1. 查询姓 '张' 的员工信息
select * from tb_emp where name like '张%';-- 4-2. 查询姓名中包含 '三' 的员工信息
select * from tb_emp where name like '%三%';-- 4-3. 查询姓'张',并且姓名为三个字的员工信息
select * from tb_emp where name like '张__';
聚合函数
==聚合函数:== 将一列数据作为一个整体,进行纵向计算,语法为: select
聚合函数(字段名)
from 表名
函数 | 功能 |
---|---|
count | 统计数量 |
max | 最大值 |
min | 最小值 |
avg | 平均值 |
sum | 求和 |
-- 1. 统计该企业员工数量
select count(*) from tb_emp;
select count(id) from tb_emp; -- 不包含空值的列
select count(1) from tb_emp; -- 任意数字-- 2. 统计该企业最早入职的员工的入职日期
select min(entrydate) from tb_emp;-- 3. 统计该企业最迟入职的员工的入职日期
select max(entrydate) from tb_emp;-- 4. 统计该企业员工ID的平均值
select avg(id) from tb_emp;-- 5. 统计该企业员工的ID之和
select sum(id) from tb_emp;
分组过滤
==分组过滤:== select 分组字段,聚合函数() from 表名
group by 分组字段名
having 分组后过滤条件
-- 1. 根据性别分组, 统计男性和女性员工的数量
select gender,count(1) from tb_emp group by gender;-- 2. 先查询入职时间在 '2015-01-01' (包含) 以前的员工
select * from tb_emp where entrydate <= '2015-01-01';-- 3. 先查询入职时间在 '2015-01-01' (包含) 以前的员工,并对结果根据职位分组
select job,count(1) from tb_emp where entrydate <= '2015-01-01' group by job;-- 4. 先查询入职时间在 '2015-01-01' (包含) 以前的员工,并对结果根据职位分组,获取员工数量大于等于2的职位
select job,count(1) from tb_emp where entrydate <= '2015-01-01' group by job having count(1) > 2;
排序
==排序:== select 字段列表 from 表名 order
by 字段1 排序方式1 , 字段2 排序方式2
ASC:升序(默认值)
DESC:降序
-- 1. 根据入职时间,对员工进行降序排序
select * from tb_emp order by entrydate desc;-- 2. 根据入职时间,对员工进行升序排序
select * from tb_emp order by entrydate asc;-- 3. 根据入职时间对公司的员工进行升序排序,入职时间相同再按照ID进行降序排序
select * from tb_emp order by entrydate asc, id desc;
分页
==分页:== select 字段列表 from 表名
limit 起始索引, 查询记录数
-- 1. 查询第1页员工数据, 每页展示5条记录
select * from tb_emp limit 0,5;-- 2. 查询第2页员工数据, 每页展示5条记录
select * from tb_emp limit 5,5;-- 3. 查询第3页员工数据, 每页展示5条记录
select * from tb_emp limit 10,5;-- 4. 查询第4页员工数据, 每页展示5条记录
select * from tb_emp limit 15,5;
小结
1. 排序: order by 列 desc
2. 截取: limit 开始,几个
3. 聚合函数: count(列)
4. 分组: group by 列
5. 过滤: having 过滤条件
6. 普通条件: whereselect 集合函数() from 表 [where 条件] [group by 分组] [having 过滤] [order by 排序] [limit 截取]