目录
目录
一.SQL概念
当谈到SQL(Structured Query Language)时,它是一种用于管理和操作关系型数据库的标准语言。以下是一些SQL的基本概念:
数据库(Database):数据库是用于存储和组织数据的集合。它可以包含多个表,索引,视图和其他数据库对象。
表(Table):表是数据库中数据的结构化表示形式。它由行和列组成,每列定义了特定的数据类型和名称。
列(Column):列是表中的一个字段,用于存储特定类型的数据。它具有名称和数据类型。
行(Row):行是表中的一个记录,它包含了一组相关的数据。
主键(Primary Key):主键是表中的一列或一组列,用于唯一标识表中的每一行。主键必须具有唯一性和非空性。
外键(Foreign Key):外键是表中的一列,它建立了与其他表的关联关系。外键用于实现表之间的关联和引用完整性。
查询(Query):查询是通过SQL语句从数据库中获取数据的方式。常见的查询操作包括选择(SELECT)、插入(INSERT)、更新(UPDATE)和删除(DELETE)。
索引(Index):索引是一种数据结构,用于提高数据库中数据的访问速度。它允许快速搜索和检索数据。
视图(View):视图是基于一个或多个表的查询结果集。它类似于虚拟表,可以简化复杂查询和数据访问。
事务(Transaction):事务是数据库操作的单个逻辑单位。它要么完全执行,要么完全回滚,以确保数据的一致性和完整性。
二.数据库操作
2.0.连接数据库
-- 语法
mysql [-h 服务器地址 -P 端口号] -u用户名 -p密码
--实例
mysql -u账号 -p密码
2.1创建数据库
-- create database [IF NOT EXISTS] 数据库名;
create database day035;
2.2使用数据库
-- use 数据库
use day035;
2.3切换数据库
-- 语法
select database();
2.4删除数据库(慎用!!)
-- 语法 drop database 数据库名
drop database day035;
2.5查询数据库
-- 语法
show databases;
三.表操作
3.0了解数据类型和数据约束
数据类型:数据类型定义了一个列或值可以存储的数据的种类。不同的数据类型具有不同的特性和存储要求。常见的 SQL 数据类型包括整数(INTEGER),浮点数(FLOAT),字符串(VARCHAR),日期时间(DATETIME)等。定义正确的数据类型可以提高存储效率,减少存储空间的消耗,并确保数据的合法性和一致性。
大分类 | 类型 | 描述 |
---|---|---|
数值类型 | int | 大整数 |
float | 浮点数类型 | |
double | 浮点数类型 | |
字符类型 | varchar(30) | 可变长度字符串,如果插入的长度小于定义长度时,插入多长就存多长 |
char(11) | 固定长度字符串,如果插入的长度小于定义长度,则可以用空格进行填充 | |
日期类型 | date | 日期,格式:yyyy-MM-dd |
datetime | 日期时间,格式:yyyy-MM-dd HH:mm:ss |
数据约束:数据约束用于限制或定义表中数据的规则和限制条件。它们确保数据的完整性和一致性,并防止非法数据的插入或更新。常见的数据约束包括主键约束(PRIMARY KEY),唯一约束(UNIQUE),外键约束(FOREIGN KEY),非空约束(NOT NULL)等。通过定义适当的数据约束,可以确保数据的有效性和正确性,并防止不符合业务规则的数据被插入到表中。
约束 | 描述 | 关键字 |
---|---|---|
主键约束 | 主键是一行数据的唯一标识,要求非空且唯一;一张表只能有一个注解列 | primary key(auto increment自增) |
非空约束 | 限制该字段值不能为null | not null |
唯一约束 | 保证字段的所有数据都是唯一、不重复的 | unique |
默认约束 | 保存数据时,如果未指定该字段值,则采用默认值 | default |
外键约束 | 让两张表的数据建立连接,保证数据的一致性和完整性 | foreign key |
3.1创建表
- 语法
语法:
create table 表名(
字段名:1 字段类型1(字段长度) comment "字段1注释 ",
字段名2 字段类型2(字段长度) comment "字段2注释 "
....
字段名n 字段类型n(字段长度) comment "字段n注释 "
) comment ”表表名注解“ ;
--其中comment 可以省略不写
- 需求:创建下列字段的表
id:标识,数字类型
name:姓名,字符串类型,长度限制最多30个字符
gender:性别,字符串类型,长度限制为1个字符
age:年龄,数值类型
birthday:生日,日期类型,格式YYYY-mm-dd
- 代码实现
create table student1(
id int comment '标识',
name varchar(30) comment '姓名',
gender char(1) comment '性别',
age int comment '年龄',
birthday date
);
3.2查询表
查询当前数据库所有表:show tables
查询表结构:desc 表名
查询建表语句:show create table 表名
-- 1. 查看当前库中的所有数据表
show tables;
-- 2. 查看student3表的表结构
desc student3;
-- 3. 查看student3表的建表语句
show create table student3;
3.3修改表
添加字段: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;
3.4删除表
-- 删除stu表
drop table stu;
四.数据操作
4.1插入数据
指定字段添加数据:insert into 表名 (字段名1, 字段名2) values (值1, 值2)
全部字段添加数据:insert into 表名 values (值1, 值2, ...)
CREATE TABLE DEPT(
DEPTNO int PRIMARY KEY,##部门编号
DNAME VARCHAR(14) , ##部门名称
LOC VARCHAR(13) ##部门地址
) ;
--全部添加
INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
--选择字段添加
INSERT INTO DEPT(DEPTNO,LOC) VALUES(50,'hr')
INSERT INTO DEPT(LOC) VALUES('测试')
结果:
注意事项:
1.如果表内有数据约束按照约束字段来添加
比如 INSERT INTO DEPT(LOC) VALUES('测试') 这段代码因为没用遵循 DEPTNO 的主 键约束(PRIMARY KEY)无法添加
2. 如果添加单个数据其他数据会默认插入null
3. 插入数据时,指定的字段顺序需要与值的顺序是一一对应的
4. 字符串和日期型数据应该包含在引号中
批量插入数据
批量添加数据(指定字段):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());
4.2修改数据
修改数据: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;
-- 注意
修改语句中如果不加条件,则将所有数据都会被修改!
4.3删除数据
删除数据:delete from 表名 [ where 条件 ]
-- 1. 删除 tb_emp 表中ID为1的员工
delete from tb_emp where id = 1;
-- 2. 删除 tb_emp 表中的所有员工
delete from tb_emp;
-- 注意
删除语句中如果不加条件,则将所有数据都会被删除!
4.4数据查询
4.4.1准备数据
-- 员工管理(带约束)
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 not null comment '性别, 说明: 1 男, 2 女',
image varchar(300) comment '图像',
job tinyint 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');
4.4.2基本语法
查询所有字段:select * from 表名
查询指定字段:select 字段1,字段2 from 表名
查询指定字段起别名:select name as '别名1' ,entrydate as '别名2' from tb_emp;
查询不重复字段: select distinct 字段 from tb_emp;
-- 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;
4.4.3条件查询
条件查询:selsct 字段列表 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 '张__';
4.4.4聚合函数
函数 | 功能 |
---|---|
count | 统计数量 |
max | 最大值 |
min | 最小值 |
avg | 平均值 |
sum | 求和 |
count 解释:
count(1):统计所有的数量(包括null)。
count(*):统计所有的数量(包括null)。
count(字段):统计该"字段"不为null的数量。
count(distinct 字段):统计该"字段"去重且不为null的数量。
4.4.5分组过滤
分组过滤: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;
4.4.6排序
排序: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;
4.4.7分页
分页: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;
五.多表查询
5.0准备数据
-- 创建部门表(主表)
CREATE TABLE dept (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
);
INSERT INTO dept (NAME) VALUES ('开发部'),('市场部'),('财务部');
-- 创建员工表(从表)
CREATE TABLE emp (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
gender CHAR(1), -- 性别(sex)
salary DOUBLE, -- 工资
join_date DATE, -- 入职日期
dept_id INT -- 外键字段
);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('孙悟空','男',7200,'2013-02-24',1);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('猪八戒','男',3600,'2010-12-02',1);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('蜘蛛精','女',6666,'2011-03-14',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('沙僧','男',4500,'2017-03-04',null);
5.1了解多表查询
项目开发中,因为业务之间相互关联,所以实体与实体之间存在联系,故而表跟表之间也存在着各种联系
我们把这种能够表示表间关系的数据库称为关系型数据库(RDBMS)
在数据库设计上,表关系分为三种:一对多、多对多、一对一
-
多表查询:多张表联合在一起查询。
-
查询方式:
-
笛卡尔积(交叉查询)(了解)
-
内连接:隐式内连接、显示内连接
-
外连接:左外连接、右外连接
-
子查询
-
5.2笛卡尔积(交叉查询)
语法:select * from A,B;
-- 语法:select * from A,B;
select * from dept, emp;
5.3 内连接:隐式内连接、显示内连接
-- 内连接:隐式内连接、显示内连接
-- 隐式内连接语法:select * from A, B where 连接条件-- 显示内连接: select * from A a inner join B b on a.主键 = b.外键;
select * from dept, emp where dept.id = emp.dept_id;
select * from dept as d, emp e where d.id = e.dept_id;
select * from dept d inner join emp e on d.id = e.dept_id;
三张表以上操作
select * from A a, B b, C c, D d
where a.id = b.a_id and b.id = c.bid and c.id = d.c_id;
select * from A a
inner join B b on a.id = b.a_id
inner join C c on b.id = c.bid
inner join D c on c.id = d.c_id;
5.4外链接(左外链接,右外链接)
-- 外连接:左外连接、右外连接
-- 左外连接:查询左表A的所有数据,如果条件成立将关联显示右表B的数据,如果条件不成立将显示null
-- 语法:select * from A a left outer join B b on 条件;
-- 需求:显示所有的员工,同时关联显示部门信息,没有显示null-- 右外连接:查询右表B的所有数据,如果条件成立将关联显示左A表的数据,如果条件不成立将显示null
-- 语法:select * from A a right outer join B b on 条件;
select * from emp e
left outer join dept d on e.dept_id = d.id;
select * from dept d
right outer join emp e on e.dept_id = d.id;
5.5 子查询
子查询:一条sql语句成为另一条sql语句语法的一部分。
5.6代码实现
-- 子查询 ,查询员工时,显示部门信息
select id,name,gender, dept_id from emp;
-- 通过 部门id 查询部门名称
select name from dept where id = 1
select id,name,gender, (select name from dept where id = dept_id) as deptName from emp;
-- 子查询:查询id=1和id=2员工信息,同时显示部门信息
-- 查询id=1和id=2员工信息
select * from emp where id in (1,2)
-- 多表查询
select * from t, dept d where t.dept_id = d.id;
select * from (select * from emp where id in (1,2)) as t, dept d where t.dept_id = d.id;
六.sql常用的单词积累
单词 | 翻译 |
---|---|
SELECT | 查询 |
INSERT | 插入 |
UPDATE | 更新 |
DELETE | 删除 |
FROM | 从哪个表 |
WHERE | 条件 |
JOIN | 连接 |
GROUP BY | 分组 |
ORDER BY | 排序 |
ASC | 升序 |
DESC | 降序 |
COUNT | 计数 |
SUM | 求和 |
AVG | 平均值 |
MAX | 最大值 |
MIN | 最小值 |
DISTINCT | 去重 |
auto_increment | 自增 |
UNION | 合并两个查询的结果集 |
LIKE | 模糊匹配 |
BETWEEN | 在指定的两个值之间 |
IN | 包含在指定的值集合中 |
NOT | 否定条件 |
IS NULL | 包含空值的行 |
EXISTS | 判断是否存在 |
ALL / ANY | 与子查询一起使用 |
ON | 在 JOIN 操作中指定关联条件 |
AS / alias | 给表或列起别名 |
NULL | 空值 / 未知值 |
PRIMARY KEY | 主键 / 唯一标识符 |
FOREIGN KEY | 外键 |
INDEX | 索引 |