数据库基础知识
SQL的分类
| 名称 | 描述 | 举例 |
|---|---|---|
| DDL | 数据定义语言 | create（创建）、alter（修改）、drop（删除）、rename（重命名）、truncate（清空） |
| DML | 数据操作语言 | insert（添加）、delete（删除）、update（修改）、select（查询） |
| DCL | 数据控制语言 | commit（提交）、rollback（撤销）、grant（赋予权限）、revoke（回收权限） |
一、数据库的数据类型
数值
①整数
| 名称 | 含义 | 占用字节 |
|---|---|---|
| tinyint | 十分小的数据 | 1 |
| smallint | 较小的数据 | 2 |
| mediumint | 中等大小的数据 | 3 |
int |
标准的整数（常用） | 4 |
| bigint | 较大的数据 | 8 |
②小数
| 名称 | 含义 | 占用字节 |
|---|---|---|
| float | 单精度浮点数 | 4 |
double |
双精度浮点数 | 8 |
| decimal | 字符串形式的浮点数（金融计算时使用,精度更高） |
字符串
| 名称 | 含义 | 占用字节 |
|---|---|---|
char |
固定字符串 | 0~255 |
varchar |
可变字符串（常用） | 0~65535 |
| tinytext | 微型文本 | 0~255 |
| text | 文本串（常用） | 0~65535 |
char和tinyint的区别
char：在定义时必须填写大小，例:char[20]，创建一个20个字节的char类型数据
tinytext:在定义时无须填写大小，固定为255字节
时间日期
| 名称 | 含义 |
|---|---|
| date | 日期格式：YYYY-DD-MM |
| time | 时间格式：HH:mm:ss |
datetime |
YYYY-DD-MM HH:mm:ss (常用) |
| timestamp | 时间戳，1970.1.1到现在的毫秒数（较为常用） |
| year | 年份表示 |
null
可以理解为空值
不要使用null进行运算，结果为null
二、运算符
2.1、算术运算符
| 运算符 | 含义 |
|---|---|
| + | 加法 |
| - | 减法 |
| * | 乘法 |
| / 或 div | 除法 |
| % 或mod | 取模、取余 |
模[余数]的正负，只与被模数[被除数的]正负有关，例:
-12 % 5 = -2 -12 % -5 = -2
2.2、逻辑运算符
| 运算符 | 语法 | 描述 |
|---|---|---|
| and && | a and b 或 a&&b | 逻辑与（两个都为真，结果为真） |
| or || | a or b 或 a||b | 逻辑或（其中一个为真，结果为真） |
| not ! | not a 或 ！a | 逻辑非（取反） |
2.3、比较运算符
| 运算符 | 名称 | 作用 | 示例 |
|---|---|---|---|
| is null | 为空运算符 | 判断值、字符串或表达式是否为空 | 如果a为null，结果为真 |
| is not null | 不为空运算符 | 判断值、字符串或表达式是否不为空 | 如果a不为null，结果为真 |
| between...and... | 两值之间的运算符 | 判断一个值是否在两个值之间 | 若a在b和c之间，结果为真 |
三、操作数据库
-- 1、创建数据库
create database [if not exists] 数据库名；
-- 1、创建数据库并指明字符集
create database [if not exists] 数据库名 character set 'utf8'；
-- 2、查看当前连接中的所有数据库
show databases;
-- 3、查看当前使用的数据库
select database()
-- 4、切换数据库
use 数据库名；
-- 5、删除数据库
drop database [if exists] 数据库名；
-- 6、更改数据库字符集
alter database 数据库名 character set 'utf8'；
四、操作表
4,.1、创建表
-- 删除表
drop table [if exists] 表名；
-- 创建表
create table 表名(
字段名 类型 约束（主键，非空，唯一，默认值），
字段名 类型 约束（主键，非空，唯一，默认值），
...
字段名 类型 约束（主键，非空，唯一，默认值）
)编码，存储引擎；
CREATE TABLE student
(
sid INT not null,
sname varchar(32),
sage INT,
ssex varchar(8),
primary key (sid)
);
在SQL中，有如下约束：
- not null -指示某列不能存储null值。
- unique -保证某列的每行必须有唯一的值。
- primary key -主键（非空，唯一，not null和unique的结合）。
- foreign key -外键，保证一个表中的数据匹配另一个表中的值的参照完整性。
- check -保证列中的值符合指定的条件。
- default -规定没有给列赋值时的默认值。
-- 例
CREATE TABLE student
(
sid INT,
sname nvarchar(32),
sage INT,
ssex nvarchar(8)
)engine=InnoDB default charset=utf8;
常用命令
show create database 数据库名； -- 查看创建数据库的语句
show create table 表名；--查看创建该表的语句
desc 表名； --查看表的结构
5.2、修改表
5.2.1、对表名进行修改
1、重命名表
-- 把表A的名字改为B
#方式一：
rename table A to B；
#方式二：
alter table A rename [to] B；
2、清空表
-- 清空表A内的数据
truncate table A;
5.2.2、对表内字段进行修改
alter：修改
1、添加列
-- 添加一个字段（默认添加到最后一列）
alter table 表名 add 字段名 [类型]；
-- 添加到第一列
alter table 表名 add 字段名 [类型] first；
-- 添加到指定列的后面（放到A列后面）
alter table 表名 add 字段名 [类型] after A；
2、修改列
-- 修改字段的长度(把A的字符串长度改为10)
alter table 表名 modify A varchar(10);
--修改字段的长度(把A的字符串长度改为10，并添加默认值为a)
alter table 表名 modify A varchar(10) default 'a';
3、重命名列
-- 重命名字段（列名a改成b）
alter table 表名 change a b；
-- 重命名的同时，修改字符串的长度
alter table 表名 change a b varchar(20)；
4、删除列
-- 删除一个字段（列）
alter table 表名 drop column a；
5.3、删除表
-- 删除表A
drop table if exists A；
五、操作数据
5.1、插入数据
-- 插入3条具体数据
insert into student(sid,sname,sage,ssex) values(1,'刘一',18,'男'),
(2,'钱二',19,'女'),
(3,'张三',17,'男')
-- 插入一个结果集（字段与字段类型要对应）
-- 查询语句的返回结果为：（3,2,72），插入到sc表中
insert into sc
select sid,2,(SELECT AVG(score) FROM sc WHERE cid =2)
from student
where sid not in(select sid from sc where cid = 3)
5.2、更新数据（修改数据）
-- 格式：update 表名 set 数据 where 过滤条件
-- 例：把第3条数据的'叶平'改为'王艳'
UPDATE teacher set tname = '王艳' where tid=3;
-- 多表连接更新
UPDATE sc
JOIN (select cid,avg(score) AS avg
from sc
WHERE cid IN(SELECT cou.cid
FROM teacher tea,course cou
WHERE tea.tid = cou.tid AND tea.tname = '叶平')
GROUP BY cid) AS SS
ON sc.cid =SS.cid
SET sc.score = SS.avg
修改数据时，由于约束的影响，可能会造成更新失败的情况
5.3、删除数据
-- 格式：delete from 表名 where 过滤条件
-- 把叶平删除
delete from teacher where tname = '叶平';
删除数据时，由于约束的影响，可能会造成更新失败的情况
5.4、查询数据
查询语句书写的结构
查询语句执行的顺序
AS（alias） 别名
作用：有的时候，列名字不是那么见名知意，可以起一个别名
格式：字段名 AS 别名
-- 别名 给查询的字段起一个名字 AS
select sname as 学生姓名,sage as 学生年龄 from student；
distinct 去重
作用：去除select查询出来的结果中重复的数据，重复的数据只显示一条
格式：distinct 字段名
-- 查询有哪些学生参加了考试；
SELECT sid FROM sc;
-- 发现重复数据，去重
SELECT DISTINCT sid from sc;
where 条件子句
作用：检索数据中符合条件的值
--查询sid=1的学生的所有课程的成绩
SELECT sid,score FROM sc WHERE sid = 1;
in/not in
作用：在某个范围内
like
作用：模糊查询
_:表示单个字符
%：表示0或无数个字符
-- 查询姓李的学生
select sname from student where like '李%';
-- 查询名字中带国的学生
select sname from student where like '%国%';
order by
作用：子句排序，默认升序
- ASC 升序
- DESC 降序
-- 查询学生的所有成绩，并降序排列
select * FROM sc ORDER BY score DESC;
-- 一级排序、二级排序
-- 查询学生的所有成绩，并降序排列，当成绩相同时，按学生id升序排列
select * FROM sc ORDER BY score DESC,sid ASC;
limit
作用：分页
用法：limit n,m
n表示指针的偏移量
m表示每页显示几条数据
分页显示公式：n=（n-1)*m
例：第2页，每页显示5行
limit （2-1）*5,5
limit 5,5
-- 查询学生的所有成绩，以每页5行进行分页，并只展示第二页
SELECT * from sc LIMIT 5,5;
group by
作用：分组
having
作用：过滤数据
必须与group by连用
多表查询
等值连接、非等值连接
-- 等值连接 钱二的语文成绩是多少 79
SELECT student.sid,student.sname,course.cname,sc.score
FROM sc,student,course
WHERE sc.sid = student.sid AND sc.cid = course.cid AND student.sname='钱二' AND course.cname='语文';
-- 非等值连接 查询钱二的成绩在60~90之间的分数及学科
select student.sid,student.sname,course.cname,sc.score
FROM sc,student,course
WHERE sc.sid = student.sid AND sc.cid = course.cid AND student.sname='钱二' AND sc.score>60 AND sc.score<90;
自连接、非自连接
自连接：其核心在于把一个表复制成两个表，找到对应字段后，进行查询
内连接、外连接
六、函数
5.1、基本函数
下面列举其中一些基本函数
5.1.1、字符串函数
| 函数 | 用法 |
|---|---|
| concat(s1,s2,...sn) | 字符串拼接，将s1,s2,...sn拼接成一个字符串 |
| lower(str) | 将字符串str全部转为小写 |
| upper(str) | 将字符串str全部转为大写 |
| lpad(str,n,pad) | 左填充，用字符串pad对str左边进行填充，达到n个字符串长度 |
| rpad(str,n,pad) | 右填充，用字符串pad对str右边进行填充，达到n个字符串长度 |
| trim(str) | 去掉字符串头部和尾部的空格 |
| substring(str,strat,len) | 返回字符串str从start位置起的len个长度的字符串 |
5.1.2、数值函数
| 函数 | 用法 |
|---|---|
| ABS(x) | 返回x的绝对值 |
| CEIL(x) | 向上取整 |
| FLOOR(x) | 向下取整 |
| MOD(x,y) | 返回x除以y的余数 |
| RAND() | 返回0~1的随机值 |
| RAND(x) | 返回0~1的随机值，相同的x值会产生相同的随机数 |
| ROUND(x) | 返回x四舍五入后的整数 |
| ROUND(x,y) | 返回x四舍五入后的小数，小数点保留y位，y可为负数 |
-- RAND(x) 返回0~1的随机值，相同的x值会产生相同的随机数
select rand(1),rand(1),rand(2);
-- 生成6位验证码
lpad(round(rand()*1000000,0),6,'0')
5.1.3、日期函数
| 函数 | 用法 |
|---|---|
| curdate() | 返回当前日期（年月日） |
| curtime() | 返回当前时间（时分秒） |
| now() | 返回当前日期和时间 |
| year('date') | 获取指定date的年份（date格式：2023-6-19） |
| month('date') | 获取指定date的月份 |
| day('date') | 获取指定date的日期 |
| date_add('date',interval expr type) | 返回一个日期/时间加上一个时间间隔expr后的时间值 |
| datediff('day1','dat2') | 返回起始时间date1和结束时间date2之间的天数 |
-- year(date) 获取当前时间的年份
select year(now())
-- month(date) 获取当前时间的月份
select month(now())
-- day(date) 获取当前时间的日期（日）
select day(now())
-- date_add(date,interval expr type)
-- 当前时间加上70天
select date_add(now(),interval 70 day)
-- datediff(day1,day2)
-- 前者减去后者
select datediff('2023-6-19','1996-8-7')
5.1.1、流程控制函数
| 函数 | 用法 |
|---|---|
| if（values,a,b） | 如果values为真，返回a，否则返回b |
| ifnull（values1,values2） | 如果values为空(null)，返回values1，否则返回values2 |
| case when [val1] then [res1]...else [default] end | 如果val1为真，返回res1，...否则返回default默认值 |
| case [erp1] when [val1] then [res1]...else [default] end | 如果erp1=val1，返回res1，...否则返回default默认值 |
-- ifnull（values1,values2）
-- 如果一个值为空(null)时,把它当成0来计算
select ifnull(values,0);
-- case when [val1] then [res1]...else [default] end
-- 求某一个课程的及格率
SELECT sum(CASE WHEN cid =1 and score>60 THEN 1 ELSE 0 END)/sum(CASE WHEN cid =1 THEN 1 ELSE 0 END)
FROM sc
由于avg()、sum()、count()在计算时都不计算空(null)值,所以可以使用ifnull(values,0)转为0进行计算
5.2、聚合函数
常用的几个聚合函数
| 名称 | 作用 | |
|---|---|---|
| AVG（） | 求平均值 | 只适用于数值类型的字段/变量（不计算null值） |
| SUN（） | 求和 | 只适用于数值类型的字段/变量（不计算null值） |
| MAX（） | 求最大值 | 数值类型、字符串类型、日期类型 |
| MIN（） | 求最小值 | 数值类型、字符串类型、日期类型 |
| COUNT（） | 计算指定字段在查询结果中的个数 | （不计算null值） |
