sql
结构化查询语言(Structured Query Language)
简称
SQL
，是一种特殊 目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以 及查询、更新和管理关系数据库系统.
SQL优点：
不是某个特定数据库供应商专有的语是言，几乎所有DBMS
都支持
SQL 简单易学，灵活使用可以进行非常复杂和高级的数据库操作
DDL
创建,删除数据库
创建数据库并设置编码格式
CREATE DATABASE [if not exists] 数据库名 [ CHARSET utf8]
删除数据库
DROP DATABASE 数据库名 / [IF EXISTS
数据库名
];
修改字符集
ALTER DATABASE 数据库名 CHARSET gbk;
数据库表的基本概念
1、数据表
表（table）是数据存储的最常见和最简单的形式，是构成关系型数据库的基本元素。 表的最简单形式是由行和列组成，分别都包含着数据。 每个表都有一个表头和表体，表头定 义表名和列名 .表中的行被看作是文件中的记录，表中的列被看作是这些记录的字段。
2、字段
字段是表里的一列，用于保存每条记录的特定信息。如客户订单表的字段包括“订单
ID”、“姓名”、“客户ID”、“职务”、“上级”、“地区”、“运货商”、“国家”等。
数据表的一列包含了特定字段的全部信息。
3、记录
记录也被称为一行数据，是表里的一行数据。
-- 创建数据库
CREATE DATABASE IF NOT EXISTS schooldb CHARSET utf8
-- 删除数据
DROP DATABASE IF EXISTS schooldb
-- mysql中数据库一旦创建,名字不能修改
-- 修改数据库字符集
ALTER DATABASE schooldb CHARSET utf8;
设 计 表
● 对于具体的某一个表，在创建之前，需要确定表的下列特征：
● 表名
(
表信息
)
● 表中的字段
● 字段的数据类型和长度
● 哪些约束
设计表
(
数据类型
)
char（
n
） 长度为
n
的定长字符串
,
最大长度
255
个字符
varchar(n） 最大长度为
n
的可变长字符串
date 日期， 包含年月日
datetime 年月日 时分秒
设计表
(
数据类型
)
浮点
decimal
数据类型(M,D)
M：精度，数据的总长度；
D：标度，小数点后的长度
主键：
在一张表中代表唯一的一条记录,
不能为空
,
不能重复
约束
:
PRIMARY KEY 设置主键约束
NOT NULL 不能为空约束
UNIQUE 唯一性约束
检查约束 设置条件
外键约束
主键自动增长
,
设置为自动增长时
,
只能为整数类型
AUTO_INCREMENT
默认值
DEFAULT default_value
字段注释
:
comment '注释
'
-- 创建表
-- 确定表名 学生信息表
-- 确定表中的字段(列) 学号,姓名,性别,生日,电话,身高,注册时间
-- 确定字段(列)的数据类型 以及长度
-- 确定列的约束
-- 创建表的语法
-- char(n) 定长字符串 固定的存储n个字符,长度如果不够n,默认补空格
-- VARCHAR(n) 可变长度字符串 n是最大长度如果只存储两个字符,实际只占有两个空间位置
CREATE TABLE student(
number INT,
NAME VARCHAR(6),
gender CHAR(1),
birthday DATE,
phone VARCHAR(11),
height DECIMAL(3,2),
reg_time DATETIME
)
删除表，修改表名
删除表
DROP TABLE [if exists ]表名
修改表名
RENAME TABLE 旧表名 TO 新表名
复制表结构
CREATE TABLE 新表名 LIKE 被复制表名;
-- 创建表 并添加约束规则
-- PRIMARY KEY 设置主键约束,添加主键约束后,值不能重复,一个表中只能有一个列是主键
-- 主键列可以唯一的表示某一行数据
-- NOT NULL 不能为空约束
-- UNIQUE 唯一性约束
-- 检查约束 设置条件
CREATE TABLE student(
number INT PRIMARY KEY AUTO_INCREMENT COMMENT'学号主键',
NAME VARCHAR(6) NOT NULL COMMENT'性别',
gender CHAR(1),
birthday DATE,
phone VARCHAR(11) NOT NULL UNIQUE,
height DECIMAL(3,2) CHECK(height<3),
reg_time DATETIME
)
-- 删除表结构 删除后数据不存在
DROP TABLE stduent
DROP TABLE stu
-- 修改表名
RENAME TABLE student TO stu
-- 复制表
CREATE TABLE student LIKE stu DML
●
数据操纵语言
DML
（
Data Manipulation Language
）
●
常用语句：
insert,delete,update
插入数据
方式1: INSERT INTO
表名
(
列
1,
列
2……,
列
n) VALUES(
值
1,
值
2…..,
值
n);
方式2: INSERT INTO
表名
set
列名
1=
值
1,..
列名
n=
值
n;
方式3: INSERT INTO
表名
(
列
1,
列
2……,
列
n) VALUES(
值
1,
值
2…..,
值
n),(
值
1,
值
2…..,
值
n);
方式4:INSERT INTO
表名
(
列
1,
列
2……,
列
n)
查询语句
(
查询的列数与插入列数匹配
)
修改数据
UPDATE 表名
SET
列名
=
‘新值’
WHERE
条件
删除数据
DELETE FROM 表名
WHERE
条件
TRUNCATE TABLE 表名
;
清空整张表
-- DML 数据操作
-- 插入数据
-- 1
INSERT INTO student(NAME,gender,birthday,phone,height,reg_time)VALUE('詹姆斯','男','1980-4-2','15323423',1.98,NOW())
-- 2
INSERT INTO student SET NAME='科比',gender='男',birthday='1989-9-8',phone='4235434563'
-- 3
INSERT INTO student(NAME,gender,birthday,phone,height,reg_time)
VALUES('库里','男','1981-4-2','15623423',1.98,NOW()),
('欧文','男','1987-4-2','15523423',1.98,NOW()),
('哈登','男','1982-4-2','15823423',1.98,NOW())
-- 4
INSERT INTO stu(number,NAME,gender,birthday,phone,height,reg_time)SELECT *FROM student
-- 修改记录
UPDATE stu SET NAME='梅西',height=1.70 WHERE number=1
-- 删除
DELETE FROM student WHERE number=2DQL-基础查询
● DQL（Data Query Language）数据查询语言查询是使用频率最高的一个操作， 可以从一个 表中查询数据，也可以从多个表中查询数据。
●
基础查询
●
语法：
select 查询列表
from
表名
;
●
特点：
查询列表可以是：表中的字段、常量、表达式、函数
查询的结果是一个虚拟的表格
●
查询结果处理：
特定列查询:select column1,column2 from table
全部列查询: select * from table
算数运算符:+ - * /
排除重复行: select distinct column1,column2 from table
查询函数：select
函数
; /
例如
version()
●
查询结果处理：
●
函数
:
类似于
java
中的方法，将一组逻辑语句事先在数据库中定义好
,
可以直接调 用
分类：
单行函数：如concat、
length
、
ifnull
等
分组函数：做统计使用，又称为统计函数、聚合函数、组函数
●
单行函数
●
字符函数
length()：获取参数值的字节个数
char_length()获取参数值的字符个数
concat(str1,str2,.....)：拼接字符串
upper()/lower()：将字符串变成大写
/
小写
substring(str,pos,length)：截取字符串 位置从
1
开始
instr(str,指定字符
)
：返回子串第一次出现的索引，如果找不到返回
0
trim(str)：去掉字符串前后的空格或子串
,trim(
指定子串
from
字符串
)
lpad(str,length,填充字符
)
：用指定的字符实现左填充将
str
填充为指定长度
rpad(str,length,填充字符
)
：用指定的字符实现右填充将
str
填充为指定长度
replace(str,old,new)：替换，替换所有的子串
-- sql -Dql 数据查询
-- select 结果 from 表名
-- 查询指定的列
SELECT number,NAME,gender,birthday FROM student
-- 查询所有的列 *表是所有的列 查询的结果是虚拟的表格,不能操作,是只读的
SELECT * FROM student
-- 查询结果进行算术运算
SELECT NAME,height+1 FROM student
-- 去除重复数据 查询结果中所有的列都相同,就视为重复数据
-- DISTINCT 关键字去除重复数据
SELECT DISTINCT NAME,gender FROM student
-- 函数
-- 单行函数:会对查询中的每一行数据进行处理
-- 字符函数
-- length(列名) 以字节为单位
SELECT NAME,LENGTH(NAME ),gender FROM student
-- char_length(列名) 以字符为单位
SELECT NAME,CHAR_LENGTH(NAME ),gender FROM student
-- concat(str1,str2,str3) 连接多个字符串 as 别名
SELECT CONCAT(NAME,":",CHAR_LENGTH(NAME)) AS NAME,gender FROM student
-- upper() 转大写 lower() 转小写
SELECT UPPER(NAME),LOWER(NAME) FROM student
-- substring(字符串,开始的位置,截取的长度) 截取字符串 开始位置从1
SELECT SUBSTRING(NAME,1,2) FROM student
-- instr(字符串,指定字符) 返回指定字符首次出现的位置,找不到返回0
SELECT INSTR(NAME,'h') FROM student
-- trim(字符串) 默认是去掉字符串前后空格
SELECT TRIM(NAME),NAME FROM student
-- trim(指定的子串 from 字符串) 去掉前后指定的子串
SELECT TRIM('r' FROM NAME),NAME FROM student
-- lpad()左填充 rpad() 右填充
SELECT LPAD(NAME,5,'a') ,RPAD(NAME,5,'b') FROM student
-- replace(列,'old','new')
SELECT REPLACE(NAME,'H','h') FROM student
●
单行函数
•
逻辑处理
case when 条件 then 结果1 else 结果2 end; 可以有多个when
ifnull(被检测值,默认值)函数检测是否为null，如果为null，则返回指定的值，否则返回 原本的值
if函数：if else的 效果 if(条件,结果1,结果2)
-- 逻辑处理
-- case when 条件 then 条件成立结果 else 不成立结果 end
SELECT
NAME,
gender,
(CASE WHEN height>=2 THEN '高个子' ELSE '正常身高' END)AS height
FROM
student
SELECT
NAME,
gender,
(CASE WHEN height>=2 THEN 'A'
WHEN height>=1.8 THEN 'B'
ELSE 'C' END)AS height
FROM
student
-- ifnull(列名,'为空时默认信息')
SELECT
NAME,
gender,
IFNULL(height,'暂未录入身高信息')AS height
FROM
student
-- if(条件,'结果1','结果2')
SELECT
NAME,
gender,
IF(height>=2,'高个子','正常')AS height
FROM
student
●
数学函数
round(
数值
)
：四舍五入
ceil(
数值
)
：向上取整，返回
>=
该参数的最小整数
floor(
数值
)
：向下取整，返回
<=
该参数的最大整数
truncate(
数值
,
保留小数的位数
)
：截断，小数点后截断到几位
mod(
被除数
,
除数
)
：取余，被除数为正，则为正；被除数为负，则为负
rand()
：获取随机数，返回
0-1
之间的小数
-- 数学函数
-- round(数值) 四舍五入
-- ceil(数值)：向上取整，返回>=该参数的最小整数
-- floor(数值)：向下取整，返回<=该参数的最大整数
SELECT NAME,gender,ROUND(height) ,CEIL(height),FLOOR(height)FROM student
-- truncate(数值,保留小数的位数)：截断，小数点后截断到几位
SELECT NAME ,gender,TRUNCATE(height,1),height FROM student
-- mod(被除数,除数)：取余，被除数为正，则为正；被除数为负，则为负
SELECT NAME,gender,MOD(height,1) FROM student
-- rand()：获取随机数，返回0-1之间的小数
SELECT NAME,gender,RAND() FROM student
日期函数
now()：返回当前系统日期
+
时间
curdate()：返回当前系统日期，不包含时间
curtime()：返回当前时间，不包含日期
可以获取指定的部分，年、月、日、小时、分钟、秒
YEAR(日期列),MONTH(
日期
列
),DAY(
日期
列
) ,HOUR(
日期
列
) ,MINUTE(
日期
列
)SECOND(日期
列
)
str_to_date(字符串格式日期,
格式
)
：将日期格式的字符转换成指定格式的日期
date_format(日期列,
格式
)
：将日期转换成字符串
datediff(big,small)：返回两个日期相差的天数
-- 日期函数
-- now() ：年 月 日 时 分 秒 返回当前系统日期+时间
-- CURDATE()： 年 月 日 返回当前系统日期，不包含时间
-- CURTIME()： 时 分 秒 返回当前时间，不包含日期
SELECT NOW(),CURDATE(),CURTIME() FROM student
-- year(日期) 将日期格式化为年
SELECT YEAR(birthday),MONTH(birthday) FROM student
-- str_to_date(字符串格式日期,格式)：将日期格式的字符转换成指定格式的日期
SELECT STR_TO_DATE('2023-3-3','%Y-%m-%d') FROM student
-- date_format(日期列,格式)：将日期转换成字符串
SELECT DATE_FORMAT(birthday,'%Y-%m')FROM student
SELECT COUNT(*),DATE_FORMAT(reg_time,'%Y-%m') FROM student GROUP BY DATE_FORMAT(reg_time,'%Y-%m')
-- datediff(big,small)：返回两个日期相差的天数
SELECT DATEDIFF(CURDATE(),birthday) FROM student日期格式
| %Y | 年，4位 |
| %m | 月，（00-12） |
| %d | 月的天，（00-31） |
| %H | 小时（00-23） |
| %i | 分钟（00-59） |
| %s | 秒（00-59） |
| %f | 微秒 |
| %T | 时间，24-小时（hh:mm:ss） |
| %j | 年的天（001-366） |
| %w | 周的天（0=星期天，6=星期六） |
分组函数
功能：用作统计使用，又称为聚合函数或统计函数或组函数
分类：sum
求和、
avg
平均值、
max
最大值、
min
最小值、
count
计数 （非空）
1.sum，
avg
一般用于处理数值型
max
，
min
，
count
可以处理任何类型
2.以上分组函数都忽略
null
值
3.count函数的一般使用
count
（
*
）用作统计行数
4.和分组函数一同查询的字段要求是
group by
后的字段
-- 分组函数: 聚合函数(查询多行,返回一行) 统计函数
-- sum，avg一般用于处理数值型max，min，count可以处理任何类型
SELECT SUM(height) FROM student -- sum 求和
SELECT AVG(height) FROM student -- avg 求平均值
SELECT MAX(height)FROM student -- max 求最大值
SELECT MIN(height) FROM student -- min 求最小值
SELECT COUNT(*) FROM student -- count 统计个数
●
条件查询
使用WHERE 子句，将不满足条件的行过滤掉，WHERE 子句紧随 FROM 子句。
语法:select <
结果
> from <
表名
> where <
条件
>
比较
=, != 或<>, >, <, >=, <=
逻辑运算
and 与
or 或
not 非
模糊查询
LIKE ：是否匹配于一个模式 一般和通配符搭配使用，可以判断字符型数值 或数值型.
通配符： % 任意多个字符
between and 两者之间,包含临界值；
in 判断某字段的值是否属于in列表中的某一项
IS NULL（为空的）或 IS NOT NULL（不为空的）
-- 条件查询
-- select * from 表名 where 条件
SELECT * FROM student WHERE gender='男' AND height>1.8
-- or 多个条件 只需满足一个即可
SELECT * FROM student WHERE gender='男' OR height>1.8
SELECT * FROM student WHERE gender!='男'
SELECT * FROM student WHERE gender<>'女'
-- 模糊查询
-- like '库%'
-- beyween () and () 两者之间
SELECT * FROM student WHERE height BETWEEN 1.40 AND 1.98
-- in(值1,值2,值n) 在给定的值中
SELECT * FROM student WHERE height IN(1.40,1.98)
SELECT * FROM student WHERE height NOT IN(1.40,1.98)
-- 查询身高为NULL值的数据
SELECT * FROM student WHERE height IS NULL
SELECT * FROM student WHERE height IS NOT NULL
1
、
UNION
的语法如下：
[SQL 语句
1]
UNION
[SQL 语句
2]
2
、
UNION ALL
的语法如下：
[SQL 语句
1]
UNION ALL
[SQL 语句
2]
当使用union 时，mysql 会把结果集中重复的记录删掉，而使用union all ， mysql 会把所有的记录返回，且效率高于union 。
-- union 将多个查询结果合并,合并时列数必须一致,去除重复数据
SELECT * FROM student WHERE gender='男'
UNION
SELECT * FROM student WHERE number=2
-- union all 将多个查询结果合并,不会去除重复数据
SELECT * FROM student WHERE gender='男'
UNION ALL
SELECT * FROM student WHERE number>1
•
排序
查询结果排序,使用 ORDER BY 子句排序 order by 排序列 ASC/DESC
asc代表的是升序，desc代表的是降序，如果不写，默认是升序
order by子句中可以支持单个字段、多个字段
•
数量限制
limit子句：对查询的显示结果限制数目 (sql语句最末尾位置)
SELECT * FROM table LIMIT offset rows;
SELECT * from table LIMIT 0,5;
-- 排序 order by 列 asc(升序) desc(降序)
SELECT * FROM student WHERE number>0 ORDER BY number ASC
SELECT * FROM student WHERE number>0 ORDER BY number DESC
SELECT * FROM student WHERE number>0 ORDER BY height DESC
SELECT * FROM student WHERE number>0 ORDER BY reg_time DESC
SELECT * FROM student WHERE number>0 ORDER BY height ASC,reg_time ASC
-- 数量限制
SELECT * FROM student WHERE number>0 ORDER BY height ASC LIMIT 0,2
SELECT * FROM student WHERE number>0 ORDER BY height ASC LIMIT 2,3
分组查询
语法：
select 分组函数，列（要求出现在
group by
的后面）
from 表
[where 筛选条件
]
group by 分组的列表
[having 分组后的筛选
]
[order by 子句
]
注意：查询列表比较特殊，要求是分组函数和group by
后出现的字段
分组查询中的筛选条件分为两类:
数据源 源位置 关键字
分组前筛选 原始表 group by
子句的前面
where
分组后筛选 分组后的结果集 group by的后面 having
-- 分组查询
-- 分组--在一些统计的场景中,按照某一个条件进行区分(分组)
-- 统计时,按照 性别,班级,年级,部门,年份,月份
-- 把满足某一个条件的数据,分到同一个组中,每个组内进行单独处理
-- 查询男生 女生各有多少人 每一个年级各有多少人 每个部门各有多少人
SELECT COUNT(*) ,gender FROM student GROUP BY gender
SELECT MAX(height),gender FROM student GROUP BY gender
SELECT COUNT(*),DATE_FORMAT(reg_time,'%Y-%m') FROM student GROUP BY DATE_FORMAT(reg_time,'%Y-%m')
-- 统计男生,女生各多少人
SELECT COUNT(*) ,gender FROM student GROUP BY gender
-- 男生和女生谁的人数大于2
-- having 条件 是对分组后的结果进行条件的筛选
SELECT COUNT(*) ,gender FROM student GROUP BY gender HAVING COUNT(*)>2
多表设计_关联查询
●
数据库设计范式
1．第一范式
(
确保每列保持原子性
)
第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就 说明该数据库表满足了第一范式。
2． 第二范式就是要有主键，要求其他字段都依赖于主键。
• 没有主键就没有唯一性，没有唯一性在集合中就定位不到这行记录，所以要主键。
• 其他字段为什么要依赖于主键？因为不依赖于主键，就找不到他们。更重要的是，其 他字段组成的这行记录和主键表示的是同一个东西，而主键是唯一的，它们只需要依 赖于主键，也就成了唯一的。
3.
第三范式
,
确保每列都和主键列直接相关
,
而不是间接相关
,
要求一个数据库表中不包含 已在其它表中包含的非主关键字信息
/*
数据库设计范式(数据库设计规则) 如何将多个信息合理的进行组织
设计3范式
第一范式: (确保每列保持原子性)(不可拆分)
联系方式--->邮箱,电话,qq 可以拆分
第二范式: 数据库表中必须要有主键,能唯一的表示某条记录的列(有些表中可以没有主键)
第三范式: 一张表中存储一类信息,在一张表中关联其他表中的数据时,只需要关联主键即可
专业信息表
专业编号(主键)
专业名称
学生与专业有关系,如何建立学生和专业表的关系
*/
DROP TABLE major
CREATE TABLE major(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
)
-- 修改表为表添加一个列 一对多 多对一
ALTER TABLE student ADD majorid INT
ALTER TABLE student DROP majorid
●
外键：引用另外一个数据表的某条记录。
外键列类型与主键列类型保持一致
数据表之间的关联/引用关系是依靠具体的主键（primary key）和外键 （foreign key）建立起来的。
create table student(
id int not null auto_increment primary key,
num int,
name varchar(10)
majorid int,
CONSTRAINT 约束名 foreign key(majorid ) references major(id)
);
约束名规则:
例：FK_ForeignTable_PrimaryTable_On_ForeignColumn
1、当主表中没有对应的记录时，不能将记录添加到从表
2、不能更改主表中的值而导致从表中的记录孤立
3、从表存在与主表对应的记录，不能从主表中删除该行
4、删除主表前，先删从表
-- 外键在一个表中外键是用来与另一个表的主键关联的
-- 主键 不能为空 唯一 检查
-- 外键约束
-- 外键有两种情况
-- 1.不加外键约束
-- 任意对表数据进行操作,即使两个表中的数据对应不上也没有关系
-- 2.添加外键约束
-- 添加外键约束后,两张表操作时,不能导致外键列和主键列对应关系不成立
ALTER TABLE student ADD CONSTRAINT fk_student_major_majorid FOREIGN KEY(majorid) REFERENCES major(id)
-- 删除外键约束
ALTER TABLE student DROP FOREIGN KEY fk_student_major_majorid
●
关联查询
含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询
笛卡尔乘积现象：表1有
m
行，表
2
有
n
行，结果
=m*n
发生原因：没有有效的连接条件
如何避免：添加有效的连接条件
按功能分类：
内连接
外连接
左外连接
右外连接
内连接(inner join) 把满足了条件的两张表中的交集数据查询出来
语法：Select 结果
from
表
1
，表
2 where
表
1.column1 =
表
2.column2
左外连接(left join)
语法：select 结果 from 表1 left join
表
2 on 表1.column1 =
表
2.column2
右外连接(right join)
语法: select 结果 from 表1 right join 表2 on 表1.column1 = 表2.column2
-- 关联查询 多表关联在一起查询
-- 学号 姓名 宁别 电话 专业名称 --信息来自于两张表
-- 关联时没有任何限制,会产生笛卡尔乘积现象,这是不想要的结果
SELECT * FROM student,major
-- 关联查询 内关联 左外关联 有外关联
-- 内关联
-- 把满足天剑的关联在一起
SELECT
s.stu_name,s.subject,m.name
FROM
student s
INNER JOIN major m
ON s.majorid = m.id
-- 先产生一个笛卡尔乘积 然后在条件筛选
SELECT
s.stu_name,
s.subject,
m.name
FROM
student s,
major m
WHERE s.majorid = m.id
-- 外连接
-- 左外连接 特点 即使不满足连接条件,也会把左边表中所有的数据查询出来
SELECT
s.stu_name,
s.subject,
m.name
FROM
student s
LEFT JOIN major m
ON s.majorid = m.id
-- 右外连接 即使不满足条件,也要把右边表中的所有数据查询出来
SELECT
s.stu_name,
s.subject,
m.name
FROM
student s
RIGHT JOIN major m
ON s.majorid = m.id
子查询
含义：出现在其他语句中的select
语句，称为子查询或内查询；外部的查询语句，称为主查询或 外查询.
分类：
按子查询出现的位置：
from后面：支持表子查询
where：支持标量子查询，列子查询
按功能、结果集的行列数不同：
标量子查询（结果集只有一行一列）
列子查询（结果集只有一列多行）
表子查询（结果集一般为多行多列）
-- 子查询
SELECT
*
FROM
nbaplayer
WHERE height =(SELECT MAX(height) FROM nbaplayer)
-- 表子查询 多行多列 在from后面使用表子查询,把子查询的结果当做另一个表的数据来源
SELECT * FROM nbaplayer WHERE height>2.00
-- 在where后面使用标量子查询 一行一列
SELECT
*
FROM
nbaplayer
WHERE height =(SELECT MAX(height) FROM nbaplayer)
-- 在where后面使用列子查询 一列多行
SELECT * FROM nbaplayer WHERE weight IN(SELECT weight FROM nbaplayer WHERE weight IN(79.0,83.0))
36万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
