当前位置：首页 > article >正文

【Day03-MySQL单表】

article 2024/9/20 20:10:39

数据库

数据库介绍

什么是数据库

数据存储的仓库，其本质也是一个文件系统

数据库会按照特定的格式对数据进行存储，用户可以对数据库中的数据进行增加，修改，删除及查询操作。

数据库管理系统层次

数据库管理系统 (DataBase Management System，DBMS) 指一种操作和管理数据库的大型软件。

数据库管理系统的层次：软件-->数据库-->数据表-->数据记录

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. 普通条件: where

select 集合函数() from 表 [where 条件] [group by 分组] [having 过滤] [order by 排序] [limit 截取]