数据库基础知识

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

常用的几个聚合函数

本栏目推荐文章
• Jmeter 如何连接mysql数据库？
• SQL Server 检测是不是数字型数据的两种方法
• 【Shell基础】Bash基础与Linux三剑客
• 如何通过Navicat把MySQL远程数据库导入到本地数据库
• 和利时MACS6通过dat文件实现任意时间范围与测点的历史数据提取与科研分析
• 深入理解spring框架：剖析多线程模式下数据库连接
• 数据可视化是如何帮助大型企业提高效率的？
• redis 数据库
• openpyxl模块--------------------------------重复数据
• 跟着阿灵学前端(1)——HTML 基础

基础知识 数据库 基础 知识 数据基础知识 数据库 基础 知识 基础知识 基础 知识 数据 概念性 基础知识 概念 数据库 数据 数据分析 基础知识 科学家 影像 数据 基础知识 基础 算法 数据结构 基础知识 工程师 基础 基础知识 过程 数据库 数据结构 基础知识ptmalloc2 ptmalloc 理论 数据库 知识 数据 知识 数据09