目录
Mysql优点
sql概述
sql的优点
DDL概述
DDL语言
创建,删除数据库
数据库表的基本概念
创建表的步骤
列的数据类型
列的约束: 列的规则
表的操作
DML概述
DML语言
数据操作
DQL—基础查询
单行函数
字符函数
逻辑处理
数学函数
日期函数
分组函数
条件查询
查询连接
查询排序
分组查询
夺标设计_关联查询
表与表的连接----外键与主键
连接
内连接
左外连接
右外连接
Mysql优点
- 开源免费:降低了使用成本,且源代码可自由修改和分发。
- 高性能:处理大量数据时表现出色,查询效率高。
- 易用性:语法简单易懂,易于学习和维护。
- 稳定可靠:经过广泛应用和测试,具有出色的稳定性和可靠性。
- 可扩展性:支持分布式架构,满足不断增长的业务需求。
- 广泛兼容性:与多种操作系统和编程语言兼容。
- 强大社区支持:庞大的用户社区提供技术支持和帮助。
- 安全性:支持多种安全机制,确保数据的安全性和完整性
sql概述
sql全程:结构化查询语言(Structured Query Language)简称SQL
一种特殊的编程语言,用于对数据库的管理操作和数据的增删改查操作
sql的优点
DDL概述
数据(结构)定义语言DDL(Data Definition Language),是用于创建和修改数据库表结构的语言
常用的语句:create ,alter,drop,rename
DDL语言
创建,删除数据库
创建数据库并设置编码格式
CREATE DATABASE [if not exists] 数据库名 [ CHARSET utf8]
-- 创建数据库
CREATE DATABASE test;-- 创建数据库 并设置字符集编码
CREATE DATABASE schooldb CHARSET utf8;
删除数据库
DROP DATABASE 数据库名 / [IF EXISTS数据库名];
-- 删除数据库
DROP DATABASE test;
注:数据库一旦创建就不可以修改,只能修改字符编码(字符编码默认值是utf8mb4)
修改字符集
ALTER DATABASE 数据库名 CHARSET gbk;
数据库表的基本概念
1.数据表
表是数据存储的最常见最简单的形式。表是构成数据库的基本元素
每个表都拥有自己的表名
2.字段
字段是表里面每一列特定信息 例如:姓名,性别......
3.记录
激励也成为一行数据,是表里的一行数据
创建表的步骤
确定表名: 学生信息 ---- 学生表
确定列名:学生的具体信息 ----- 姓名,性别,生日
列的数据类型
字符串型:
char(n) 长度为n的定长字符串, 例如n=5, 如果我们只存储了2个字符进去,长度依然是5,会补空格
一般用来存储长度固定的数据, 例如性别,电话.
varchar(n) 最大长度为n的变长字符串, 例如n=10,如果我们只存储了3个字符,实际长度为3
日期时间类型:
date 日期--年月日
datetime 时间--年月日 时分秒
数值:
整数
TINYINT 1字符
SMALLINT 2字符
MEDIUMINT 3字符
int 4字符
bigint 8字符
浮点:
decimal(M,D) --- M为总位数,D为小数位数
3,2
1.73
TEXT列字符字符串 长文本类型 例如存储新闻信息,小说...
创建一个简单的表:
CREATE TABLE student(num INT,NAME VARCHAR(10),gender CHAR(1),birthday DATE,phone CHAR(11),address VARCHAR(30),height DECIMAL(3,2),reg_time DATETIME
)
列的约束: 列的规则
- 学号 唯一,不能为空 而且只能有一个学号
- 可以为学号列添加主键约束(唯一不能重复,不能为空,一个表中只能有一个列添加主键约束)
- PRIMARY KEY 设置列为主键
- AUTO_INCREMENT 设置主键列自动增长, 只能修饰主键列,而且主键列类型为整
- not null 不能为空约束 可以添加到多个普通列
- unique 唯一约束 可以添加到多个普通列
- CHECK(height<2.60) 检查约束
添加约束的表
CREATE TABLE student(num INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(10) NOT NULL,gender CHAR(1) NOT NULL,birthday DATE,phone CHAR(11) NOT NULL UNIQUE,address VARCHAR(30),height DECIMAL(3,2) CHECK(height<2.60),reg_time DATETIME
)
表的操作
-- 删除表
DROP TABLE student
-- 修改表名
RENAME TABLE student TO stu
-- 复制表结构
CREATE TABLE stu LIKE student
DML概述
数据操纵语言DML(Data Manipulation Language)
常用语句: insert,delete,update
DML语言
数据操作
插入
- 方式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) 查询语句(查询的列数与插入列数匹配)
-- 方式一
INSERT INTO student(NAME,gender,birthday,phone,address,height,reg_time)VALUES('张山','男','2022-3-9','13090325889','汉中','1.87',NOW())
-- 方式二
INSERT INTO student(NAME,gender,birthday,phone,address,height,reg_time)
SET NAME='张三',SET gender='男',SET birthday='2022-3-9',SET phone='13063025887',SET adddress='陕西',SET height='1.35',SET reg_time=NOW()
-- 方式三
INSERT INTO student(NAME,gender,birthday,phone,address,height,reg_time)
VALUES('张山','男','2022-3-9','13090325884','汉中','1.87',NOW()),
('张山','男','2022-3-9','13090325883','汉中','1.87',NOW()),
('张山','男','2022-3-9','13090325882','汉中','1.87',NOW())
-- 方式四
INSERT INTO stu(NAME,gender,birthday,phone,address,height,reg_time) SELECT NAME,gender,birthday,phone,address,height,reg_time FROM student
修改数据
UPDATE 表名 SET 列名 = ‘新值’WHERE 条件
-- 修改语句 需要注意条件的准确性, 否则修改所有的数据UPDATE student SET address='陕西西',height=1.65,reg_time = '2003-4-5 14:20:10' WHERE num = 1
删除数据
DELETE FROM 表名 WHERE 条件
TRUNCATE TABLE 表名;清空整张表
-- 删除语句DELETE FROM stu -- 没有条件 删除所有的数据DELETE FROM student WHERE num = 13
DQL—基础查询
DQL(Data Query Language)数据查询语言查询是使用频率最高的一个操作, 可以从一个表中查询数据,也可以从多个表中查询数据。
特点: 查询列表可以是:表中的字段、常量、表达式、函数查询的结果是一个虚拟的表格
-- 基本查询语法
-- select 查询的列 from 表名 where 条件 排序 数量限制 分组....
-- select 结果处理 from 表名
特定列查询:select column1,column2 from table
全部列查询: select * from table
排除重复行: select distinct column1,column2 from table
注:sql中运算符只有运算的作用,例如 + 没有相加的作用 sql相加用的是concat
-- 查询特定的列SELECT num,NAME,gender FROM student-- 查询所有的列, 在开发中一般不建议, 使用哪些列,查询哪些SELECT * FROM student-- sql中+ - * / 只能做算数运算 +不能连接字符串SELECT num+100,NAME FROM student-- 字符串函数 连接多个字符串SELECT num,CONCAT(NAME,':',gender)AS NAME FROM student-- 去除查询结果中重复数据, 何为重复数据: 所有的列都相同SELECT DISTINCT NAME,gender,birthday FROM student
单行函数
字符函数
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):替换,替换所有的子串
-- length():获取参数值的字节个数
SELECT NAME,LENGTH(NAME) FROM student
-- char_length()获取参数值的字符个数
SELECT NAME,CHAR_LENGTH(NAME) FROM student
-- concat(str1,str2,.....):拼接字符串
SELECT NAME,CONCAT(NAME,':',gender)AS NAME FROM student
-- upper()/lower():将字符串变成大写/小写
SELECT NAME,UPPER(NAME)AS NAME FROM student
-- substring(str,pos,length):截取字符串 位置从1开始
SELECT NAME,SUBSTRING(NAME,2) FROM student
-- instr(str,指定字符):返回子串第一次出现的索引,如果找不到返回0
SELECT NAME,INSTR(NAME,'张') FROM student
-- trim(str):去掉字符串前后的空格或子串,trim(指定子串 from 字符串)
SELECT NAME,TRIM(NAME) FROM student
-- lpad(str,length,填充字符):用指定的字符实现左填充将str填充为指定长度
SELECT NAME,LPAD(NAME,5,'a') FROM student
-- rpad(str,length,填充字符):用指定的字符实现右填充将str填充为指定长度
SELECT NAME,RPAD(NAME,5,'a') FROM student
-- replace(str,old,new):替换,替换所有的子串
SELECT NAME,REPLACE(NAME,'张','三') FROM student
逻辑处理
case when 条件 then 结果1 else 结果2 end;
可以有多个when ifnull(被检测值,默认值)函数检测是否为null,如果为null,则返回指定的值,否则返回 原本的值
if函数:if else的 效果 if(条件,结果1,结果2)
SELECT NAME,(CASE WHEN height>=1.80 THEN '高个子' ELSE '正常身高' END)AS height,gender FROM studentSELECT NAME,(CASE WHEN height>=1.80 THEN '高个子'WHEN height>=1.60 THEN '正常身高'ELSE '低个子' END) AS height,gender FROM studentSELECT NAME,IFNULL(address,'暂未录入')AS address FROM studentSELECT NAME,IF(height>=1.80,'高个子','正常身高') FROM student
注:case when 条件 then 结果1 else 结果2 end; 这个函数 他是可以先判断后赋值,也就是它可以创建一个新的列
结果示例:
SELECT NAME,IFNULL(address,'暂未录入')AS address FROM student 这个可以判断内容是否非空 为则显示 暂未录入
数学函数
round(数值):四舍五入
ceil(数值):向上取整,返回>=该参数的最小整数
floor(数值):向下取整,返回<=该参数的最大整数
truncate(数值,保留小数的位数):截断,小数点后截断到几位
mod(被除数,除数):取余,被除数为正,则为正;被除数为负,则为负
rand():获取随机数,返回0-1之间的小数
-- round(数值):四舍五入
SELECT NAME,ROUND(height) FROM student
-- 指定保留小数位数
SELECT NAME,ROUND(height,1) FROM student-- ceil(数值):向上取整,返回>=该参数的最小整数SELECT NAME,CEIL(height) FROM student-- floor(数值):向下取整,返回<=该参数的最大整数
SELECT NAME,FLOOR(heigt) FROM student
-- truncate(数值,保留小数的位数):截断,小数点后截断到几位, 不会四舍五入
SELECT NAME,TRUNCATE(height,1) FROM student-- mod(被除数,除数):取余,被除数为正,则为正;被除数为负,则为负
SELECT NAME, MOD(num,3) FROM student-- rand():获取随机数,返回0-1之间的小数
SELECT NAME, RAND() FROM student
日期函数
now():返回当前系统日期+时间
curdate():返回当前系统日期,不包含时间
curtime():返回当前时间,不包含日期 可以获取指定的部分,年、月、日、小时、分钟、秒
YEAR(日期列),MONTH(日期列),DAY(日期列) ,HOUR(日期列) ,MINUTE(日期列) SECOND(日期列)
str_to_date(字符串格式日期,格式):将日期格式的字符转换成指定格式的日期
date_format(日期列,格式):将日期转换成字符串
datediff(big,small):返回两个日期相差的天数
-- 日期函数
-- now():返回当前系统日期+时间
SELECT NAME,NOW() FROM student
-- curdate():返回当前系统日期,不包含时间
SELECT NAME,CURDATE() FROM student
-- curtime():返回当前时间,不包含日期
SELECT NAME,CURTIME() FROM student
-- 可以获取指定的部分,年、月、日、小时、分钟、秒
-- YEAR(日期列),MONTH(日期列),DAY(日期列) ,HOUR(日期列) ,MINUTE(日期列),SECOND(日期列)
SELECT NAME,YEAR(birthday),MONTH(birthday),DAY(birthday),HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()) FROM student
-- str_to_date(字符串格式日期,格式):将日期格式的字符转换成指定格式的日期
-- date_format(日期列,格式):将日期转换成字符串
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s')
-- datediff(big,small):返回两个日期相差的天数
-- 这里的birthday是一个字符串
SELECT NAME,DATEDIFF(DATE_FORMAT(NOW(),'%Y-%m-%d'),birthday) FROM student;
注:date_format(日期列,格式):将日期转换成字符串 这个函数常是与别的函数联合以来一起使用
下表十分重要
分组函数
功能:用作统计使用,又称为聚合函数或统计函数或组函数
分类:sum 求和、avg 平均值、max 最大值、min 最小值、count 计数 (非空)
1.sum,avg一般用于处理数值型max,min,count可以处理任何类型
2.以上分组函数都忽略null值
3.count函数的一般使用count(*)用作统计行数
4.和分组函数一同查询的字段要求是group by后的字段
SELECT SUM(height),AVG(height),MAX(height),MIN(height) FROM student
个人理解:分组函数是找一个组中的数据,如果这里分为二组,那么就会有两行数据,分租函数是对一组数据进行处理
-- 3.count函数的一般使用count(*)用作统计行数
-- 一般不建议使用count(列名) 查询,因为如果为空则查不到,官方推荐使用count(*)查询
SELECT COUNT(height) FROM student
-- 3.count函数的一般使用count(*)用作统计行数
SELECT COUNT(*) FROM student
SELECT COUNT(1) FROM student
-- 一般不建议使用count(列名) 查询,因为如果为空则查不到,官方推荐使用count(*)查询
SELECT COUNT(height) FROM student
条件查询
使用WHERE 子句,将不满足条件的行过滤掉,WHERE 子句紧随 FROM 子句。
语法:select <结果> from <表名> where <条件>
逻辑运算 and 与 or 或 not 非
SELECT * FROM student WHERE height >=1.75
SELECT * FROM student WHERE NAME = '张三'
-- 不等于符
SELECT * FROM student WHERE NAME <> '张三'
模糊查询 LIKE :是否匹配于一个模式 一般和通配符搭配使用,可以判断字符型数值 或数值型.
通配符: % 任意多个字符
between and 两者之间,包含临界值;
in 判断某字段的值是否属于in列表中的某一项
IS NULL(为空的)或 IS NOT NULL(不为空的)
-- 模糊查询`student`
-- LIKE :是否匹配于一个模式 一般和通配符搭配使用,可以判断字符型数值
-- 或数值型. 通配符: % 任意多个字符
SELECT * FROM student WHERE NAME LIKE 'k%'
-- between and 两者之间,包含临界值;
SELECT * FROM student WHERE height BETWEEN 1.75 AND 1.90
-- in 判断某字段的值是否属于in列表中的某一项
SELECT * FROM student WHERE height IN(1.87,1.52)
SELECT * FROM student WHERE height NOT IN(1.87,1.52)
-- IS NULL(为空的)或 IS NOT NULL(不为空的)
SELECT * FROM student WHERE address IS NULL
SELECT * FROM student WHERE height IS NOT NULL
查询连接
重:连接的二个查询语句中的查询元素必须相同,这里UNION 和 UNION ALL 都起到的是合并的作用,但是UNION ALL中没有去重 UNION 中有去重 如下演示
1.UNION 的语法如下:
[SQL 语句 1]
UNION
[SQL 语句 2]
2.UNION ALL 的语法如下:
[SQL 语句 1]
UNION ALL
[SQL 语句 2]
当使用union 时,mysql 会把结果集中重复的记录删掉,而使用union all,mysql 会把所有的记录返回,且效率高于union.union all没有去重,效率更高
UNION 连接
SELECT NAME,height,gender FROM student WHERE gender='男'UNION
SELECT NAME,height,gender FROM student WHERE height=1.87
UNION ALL 连接
SELECT NAME,height,gender FROM student WHERE gender='男'UNION ALL
SELECT NAME,height,gender FROM student WHERE height=1.87
查询排序
查询结果排序,使用 ORDER BY 子句排序 order by 排序列 ASC/DESC
asc代表的是升序,desc代表的是降序,如果不写,默认是升序
order by子句中可以支持单个字段、多个字段
排序的默认排序是降序
例如:下面就是对height进行的排序
-- 默认升序
-- 二个排序,先排前面的,前面的一样在排后面的
SELECT * FROM student ORDER BY height,birthday
-- 升序
SELECT * FROM student ORDER BY height ASC
-- 降序
SELECT * FROM student ORDER BY height DESC
-- limit子句:对查询的显示结果限制数目 (sql语句最末尾位)
-- 常用于卖货的页面,限制每个页面的个数
-- limit 那个数目限制前的公式是 (n-1)*每页个数
SELECT * from table LIMIT 0,5;
0 为开始序号, 5限制的个数
SELECT * FROM student LIMIT 0,2
SELECT * FROM student LIMIT 2,2
SELECT * FROM student LIMIT 4,2
如上图:就是前两行代码的运行结果,也就是在limit函数的限制下,只会显示2个数据
分组查询
1. 查询语法结构:
sql
SELECT 分组函数, 列
FROM 表
[WHERE 筛选条件]
GROUP BY 分组的列表
[HAVING 分组后的筛选]
[ORDER BY 子句]
2. 查询列表要求:
查询列表中的列应该是分组函数和GROUP BY子句后出现的字段。这意味着在SELECT语句中,除了分组函数外,其他列必须出现在GROUP BY子句后面。
3. 分组查询中的筛选条件:
- 分组前筛选:在原始表上进行的筛选条件,出现在GROUP BY子句之前的WHERE子句中。
- 分组后筛选:在分组后的结果集上进行的筛选条件,出现在GROUP BY子句之后的HAVING子句中。
通过正确使用WHERE子句和HAVING子句,您可以在分组查询中实现对原始数据和分组后数据的筛选和过滤
-- GROUP BY 分组条件(列明)
SELECT COUNT(*),gender FROM student GROUP BY gender
SELECT SUM(height),gender FROM student GROUP BY gender
SELECT MAX(height),gender FROM student GROUP BY gender
SELECT AVG(height),gender FROM student GROUP BY gender-- 统计每年出生人数
SELECT COUNT(*),DATE_FORMAT(birthday,'%Y') FROM student GROUP BY DATE_FORMAT(birthday,'%Y')
SELECT COUNT(*),gender FROM student WHERE num>1 GROUP BY gender
分组后筛选
SELECT COUNT(*),gender FROM student GROUP BY gender HAVING COUNT(*)>1
夺标设计_关联查询
表与表的连接----外键与主键
-- 创建外键 主键与外键的类型必须一致 而且 主键与外键有一定的联系 主键可以通过外键来查询数据,外键也可以通过主键查询数据
ALTER TABLE student ADD majoeid INT
-- alter table 表名 change 列名 新的列名 列的类型 ALTER TABLE [表名.]TABLE_NAME
RENAME COLUMN OLD_COLUMN_NAME TO NEW_COLUMN_NAME;
ALTER TABLE student RENAME COLUMN majoeid TO majorid
-- 表与表的关系:多对一的关系 也可以称为一对多
-- 添加外键约束,为什么要添加约束,如果有添加约束,数据与数据之间,没有任何约束,可以随意操作
-- 例如删除专业,但是专业还有学生与他关联,此种场景删除专业就不太好
-- 为外键添加约束后,操作时就不能随便操作,要保证数据的完整性
约束语:FK_ForeignTable_PrimaryTable_On_ForeignColumn
FK: 表示这是一个外键约束(Foreign Key Constraint)。
ForeignTable: 表示外键所在的表名。
PrimaryTable: 表示主键所在的表名。
On: 表示连接两个表的关联条件。
ForeignColumn: 表示外键列名。
链接二个表
ALTER TABLE student ADD CONSTRAINT fk_student_major_majorid FOREIGN KEY (majorid) REFERENCES major(id)
这样也可以看出
注意事项:
- 当主表中没有对应的记录时,不能将记录添加到从表
- 不能更改主表中的值而导致从表中的记录孤立
- 从表存在与主表对应的记录,不能从主表中删除该行
- 删除主表前,先删从表
含义:又称多表查询,当查询的字段来自于多个表时,就会用到连接查询
笛卡尔乘积现象:表1有m行,表2有n行,结果=m*n
发生原因:没有有效的连接条件
如何避免:添加有效的连接条件
连接
示例:
SELECT * FROM student,major WHERE majorid = id
majorid = id -------> 链接条件
student,major ----> 二个需要连接地表名
效果如下:
内连接
SELECTs.num,s.name,s.gender,m.name
FROMstudent sINNER JOIN major mON s.majorid = m.id
只显示二个中都有的也就是,显示的全是选取专业的学生
左外连接
SELECT*
FROMstudent sLEFT JOIN major mON s.majorid = m.id
这个就会显示没有选专业的同学
右外连接
与在外连接同理,一个思路
SELECT*
FROMstudent sRIGHT JOIN major mON s.majorid = m.id