数据库基础知识

SQL的分类

一、数据库的数据类型

数值

①整数

②小数

字符串

char和tinyint的区别

char：在定义时必须填写大小，例:char[20]，创建一个20个字节的char类型数据

tinytext:在定义时无须填写大小，固定为255字节

时间日期

null

可以理解为空值

不要使用null进行运算，结果为null

二、运算符

2.1、算术运算符

模[余数]的正负，只与被模数[被除数的]正负有关，例:

-12 % 5 = -2 -12 % -5 = -2

2.2、逻辑运算符

2.3、比较运算符

三、操作数据库

-- 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、字符串函数

5.1.2、数值函数

-- RAND(x) 返回0~1的随机值，相同的x值会产生相同的随机数
select rand(1),rand(1),rand(2);

-- 生成6位验证码
lpad(round(rand()*1000000,0),6,'0')

5.1.3、日期函数

-- 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、流程控制函数

-- 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、聚合函数

常用的几个聚合函数

热门相关：英雄联盟之巅峰王座   我嫂子比我姐姐更紧我   网游三国之城市攻略   嫡嫁千金   上将大叔，狼来了！