数据库基础知识

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值

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