MySql基础
概述
结构化查询语言(Structure Query Language)简称SQL。
是一种特殊的，标准的数据库编程语言，，一般的数据库管理系统都支持，用于对数据库进行增删改查等操作，实现数据持久化到本地。
使用完整的管理系统统一管理，可以实现结构化查询，方便管理
基本概念
数据库（DataBase）为了方便数据的存储和管理，它将数据按照特定的 规则存储在磁盘上,就是一个存储数据的仓库。
数据库的相关概念
DB：数据库（DataBase） 存储数据的容器，它保存了一系列有组织的数据。
DBMS：数据库管理系统（DataBase Management System） 又称为数据库软件或数据库产品，用于创建或管理DB
数据表
表(data)是数据库存储最常见简单的形式，是构成数据库的基本元素。表由行和列组成，都包含着数据，每个表都要表头和表体，表头定 义表名和列名 .表中的行被看作是文件中的记录，表中的列被看作是这些记录的字段。
字段
字段是表里的一列，用于保存每条记录的特定信息，数据表的一列包含了特定字段的全部信息。
记录
记录也被称为一行数据，是表里的一行数据。
基本操作
在SQL语言中根据操作不同，又分为不同类型的SQL语句。
DDL
表结构定义语言(Data Defintion Language)，用于创建和修改数据库表结构的语言。
常用的有：create，alter，drop，rename
-- 创建数据库
-- CREATE DATABASE [if not exists] 数据库名 [CHARSET utd8]
CREATE DATABASE test;
-- 创建数据库,并设置字符串编码,并判断数据库是否存在
CREATE DATABASE test IF NOT EXISTS CHARSET utf8;
-- 删除数据库
-- DROP TABLE [if exists]表名
DROP DATABASE test;
-- mysql数据库名一旦创建不能修改，只能修改字符集编码
ALTER DATABASE test CHARSET utf8;
-- 修改表名
RENAME TABLE '旧表名' TO '新表名'
-- 复制表结构
CREATE TABLE '新表名' LIKE '被复制表名';创建一个表
/*
创建表(学生表)
表 表名
列 特定信息
行 数据
步骤：
确定表名: 学生信息 -- 学生表
确定列名: 学生的信息 -- 姓名...
列的数据类型:
字符：
char(长度为n的定长字符串，用来存储长度固定的数据。n=5，存3长度为5)
varchar(最大长度为n的可变字符串。n=5，存3长度为3)
date(日期)
datetime(年月日 时分秒)
整数：
tinyint(1)
samllint(2)
mediumint(3)
int(4)
bigint(8)
signed 有符号，默认为无符号
unsigned 无符号
浮点：
decimal(M,D) M是数据总长度，D小数点后的长度
文本：
tinytext
text
mediumtext
longtext
列的约束: 列的规则
*/
-- 学生表 学号，姓名，性别，生日，电话，地址，身高，注册时间
CREATE TABLE student(
num INT,
NAME VARCHAR(10),
sex CHAR(1),
birthday DATE,
phone CHAR(11),
address VARCHAR(30),
height DECIMAL(3,2),
reg_time DATETIME
)
DROP TABLE stu;
/*
创建表，并未表添加约束
唯一，可以为其添加主键约束，但不能为空，一个表中只能有一个主键约束
PRIMARY KEY 设置主键
AUTO_INCREMENT 设置主键列自动增长，且只能为整数类型
NOT NULL 非空，可以添加至多个列
UNIQUE 唯一性约束，可以添加至多个列
外键约束
check() 检查约束
DEFAULT (default_value) 默认约束
comment '注释' 字段注释
*/
CREATE TABLE student(
num INT PRIMARY KEY AUTO_INCREMENT COMMENT '学号 主键',
NAME VARCHAR(10) NOT NULL,
sex CHAR(1) NOT NULL DEFAULT '男',
birthday DATE,
phone CHAR(11) NOT NULL UNIQUE,
address VARCHAR(30),
height DECIMAL(3,2) CHECK(height < 2.20),
reg_time DATETIME
)
RENAME TABLE stu TO student
CREATE TABLE stu LIKE student
DROP TABLE student;DML
数据操纵语言(Data Manipulation Language)
常用的有：delect，insert，update
-- 表数据操纵语言
-- insert delect 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) 查询语句(查询的列数与插入列数匹配)
*/
-- 法一
INSERT INTO student(NAME,sex,birthday,phone)
VALUES('张三','女','2005-9-30','12345678900')
INSERT INTO student(NAME,sex,birthday,phone,address,height,reg_time)
VALUES('李四','男','2004-4-10','12345678901','汉中',1.88,NOW())
-- 法2
INSERT INTO student SET NAME = '孙七',sex = '男',phone = '12345678904';
-- 法3
INSERT INTO student(NAME,sex,birthday,phone,address,height,reg_time)
VALUES('王五','男','2002-1-11','12345678902','汉中',1.76,NOW()),
('刘六','男','2001-8-6','12345678903','汉中',1.90,NOW())
-- 法4
INSERT INTO stu(NAME,sex,birthday,phone,address,height,reg_time)
SELECT NAME,sex,birthday,phone,address,height,reg_time FROM student
-- 删除数据
DELETE FROM student
WHERE num = 7
-- 清空整张表
TRUNCATE TABLE student;
-- 更改数据 注意条件
UPDATE student SET address = '陕西西安',height = 1.79,reg_time = '2024-5-5 17:02:35'
WHERE num = 1DQL
DQL(Data Query Language)，数据查询语言查询是使用频率最高的一个操作， 可以从一个表中查询数据，也可以从多个表中查询数据。
使用语句：select，where
基本查询
-- 基本查询
-- select 查询的列 from 表名 where 条件
-- select 结果处理 from 表名
-- 查询特定的列
SELECT num,NAME,sex FROM student
-- 查询所有的列(一般不使用)
SELECT * FROM student
-- 算数运算 +(不能连接字符串) - * /
-- CONCAT() 字符串函数，可连接多个字符串
SELECT num * 10,CONCAT(NAME,':',sex) AS NAME FROM student
-- 排除重复 所有的列都相同
SELECT DISTINCT NAME,sex,birthday FROM student
-- 查询函数
SELECT VERSION() FROM DUAL
-- 查询结果中使用函数
-- 函数类似与java的方法函数
-- 单行函数(对每一行操作都记录)
-- 字符函数：
-- length()：获取参数值的字节个数
SELECT NAME,LENGTH(NAME) FROM student
-- char_length()：获取参数值的字符个数
SELECT NAME,CHAR_LENGTH(NAME) FROM student
-- concat(str1,str2,.....)：拼接字符串
SELECT num,CONCAT(NAME,':',sex) AS NAME FROM student
-- upper()/lower()：将字符串变成大写/小写
SELECT UPPER(NAME) ,LOWER(NAME) FROM student
-- substring(str,pos,length)：截取字符串 位置从1开始
SELECT SUBSTRING(NAME,1,2) FROM student
-- instr(str,指定字符)：返回子串第一次出现的索引，如果找不到返回0
SELECT INSTR(NAME,'三') FROM student
-- trim(str)：去掉字符串前后的空格或子串,trim(指定子串 from 字符串)
SELECT TRIM(NAME) FROM student
-- lpad(str,length,填充字符)：用指定的字符实现左填充将str填充为指定长度
SELECT LPAD(NAME,5,'S') FROM student
-- rpad(str,length,填充字符)：用指定的字符实现右填充将str填充为指定长度
SELECT RPAD(NAME,5,'S') FROM student
-- replace(str,old,new)：替换，替换所有的子串
SELECT REPLACE(NAME,'三','3') FROM student
...
-- 逻辑处理
-- case when 条件 then 结果1 else 结果2 end; 可以有多个when
SELECT NAME,
CASE WHEN height >=1.85 THEN '高' ELSE '正常' END AS height
FROM student
SELECT NAME,
(CASE WHEN height >=1.85 THEN '高'
WHEN height >= 1.65 THEN '正常'
ELSE '低' END) AS height
FROM student
-- ifnull(被检测值,默认值)函数检测是否为null，如果为null，则返回指定的值，否则返回原本的值
SELECT NAME,IFNULL(address,'暂未录入') AS sddress FROM student
-- if函数：if else的 效果 if(条件,结果1,结果2)
SELECT NAME,IF(heigth >= 1.85,'高','正常') AS height FROM student
...-- 数学函数：
-- round(数值)：四舍五入
SELECT NAME,ROUND(height) FROM student
-- ceil(数值)：向上取整，返回>=该参数的最小整数
SELECT NAME,CEIL(height) FROM student
-- floor(数值)：向下取整，返回<=该参数的最大整数
SELECT NAME,FLOOR(height) FROM student
-- truncate(数值,保留小数的位数)：截断，小数点后截断到几位
SELECT NAME,TRUNCATE(height,1) FROM student
-- mod(被除数,除数)：取余，被除数为正，则为正；被除数为负，则为负
SELECT NAME,MOD(num,2) FROM student
-- rand()：获取随机数，返回0-1之间的小数
SELECT NAME,RAND() FROM student
...
-- 日期函数:
-- now()：返回当前系统日期+时间
SELECT NAME,NOW() FROM student
-- curdate()：返回当前系统日期，不包含时间
SELECT NAME,CURDATE() FROM student
-- curtime()：返回当前时间，不包含日期
SELECT NAME,CURTIME() FROM student
-- str_to_date(字符串格式日期,格式)：将日期格式的字符转换成指定格式的日期
SELECT STR_TO_DATE('2001-2-2','%Y-%m-%d') FROM student
-- date_format(日期列,格式)：将日期转换成字符串
SELECT DATE_FORMAT(birthday,'%Y-%m') FROM student
-- datediff(big,small)：返回两个日期相差的天数
SELECT DATEDIFF(CURDATE(),birthday) FROM student
SELECT DATEDIFF(STR_TO_DATE('2020-1-1','%Y-%m-%d'),birthday) FROM student
...
日期函数转化的格式：
分组函数
-- 分组函数(又叫统计函数，聚合函数)：
-- 用于统计使用
-- 前两个一般用于处理数值型，后三个可以处理任何类型
-- 这些分组函数都忽略null值
-- count函数的一般使用 count(*) 用作统计行数
-- 和分组函数一同查询的字段要求是 group by 后的字段
-- sum() 求和
SELECT SUM(height) FROM student
-- avg() 平均值
SELECT AVG(height) FROM student
-- max() 最大值
SELECT MAX(height) FROM student
-- min() 最小值
SELECT MIN(height) FROM student
-- count() 计数 count(*)-->推荐 count(1) count(列名)-->列名为空则不统计
SELECT COUNT(height) FROM student
SELECT COUNT(*) FROM student条件查询
-- 条件查询
-- 使用WHERE 子句，将不满足条件的行过滤掉，WHERE 子句紧随 FROM 子句。
-- 语法:select <结果> from <表名> where <条件>
-- 比较：=, != 或<>, >, <, >=, <=
-- 逻辑运算：and 与,or 或,not 非
SELECT * FROM student WHERE num = 1
SELECT * FROM student WHERE sex = '男' AND height >= 1.85 AND address = '汉中'
SELECT * FROM student WHERE sex = '男' OR height >= 1.85
SELECT * FROM student WHERE sex != '男'
SELECT * FROM student WHERE sex <> '男'模糊查询
-- 模糊查询
-- LIKE ：是否匹配于一个模式，一般和通配符搭配使用，可以判断字符型数值
-- 或数值型. 通配符：% 任意多个字符
SELECT * FROM student WHERE NAME LIKE '刘%'
SELECT * FROM student WHERE NAME LIKE '%五'
-- between and 两者之间,包含临界值；
SELECT * FROM student WHERE height >= 1.90 AND height <= 2.00
SELECT * FROM student WHERE height BETWEEN 1.90 AND 2.00
-- in 判断某字段的值是否属于in列表中的某一项
SELECT * FROM student WHERE height = 1.90 OR height = 1.79
SELECT * FROM student WHERE height IN (1.90,1.79)
SELECT * FROM student WHERE height NOT IN (1.90,1.79)
-- IS NULL（为空的）或 IS NOT NULL（不为空的）
-- 错误 查不到
SELECT * FROM student WHERE address = NULL
SELECT * FROM student WHERE address IS NULL
SELECT * FROM student WHERE address IS NOT NULL分组查询
-- 分组查询
-- 将某一类数据分到一个组中处理
-- select 分组函数，列(列要求出现在group by的后面)
-- from 表名
-- where 筛选条件 -->分组前筛选 原始表
-- group by 分组的列表
-- having 分组后的筛选 -->分组后筛选 分组后的结果集
-- order by 子句
SELECT sex,COUNT(*) FROM student GROUP BY sex
SELECT sex,SUM(height) FROM student GROUP BY sex
SELECT sex,AVG(height) FROM student GROUP BY sex
SELECT sex,MAX(height) FROM student GROUP BY sex
-- eg：查询男女生各有多少人 (统计数量)
SELECT sex,COUNT(*) FROM student GROUP BY sex
-- eg: 查询每年出生的人数
SELECT DATE_FORMAT(birthday,'%Y'),COUNT(*) FROM student GROUP BY DATE_FORMAT(birthday,'%Y')
-- eg：查询那些名字重复
SELECT NAME,COUNT(*) FROM student GROUP BY NAME HAVING COUNT(*) > 1关联查询
-- 关联查询 (多表查询)
-- 学生信息 学号，姓名...专业名称
-- 笛卡尔乘积现象 (表与表没有添加关联条件)
SELECT * FROM student,major
-- 添加关联 (内连接inner join，只把满足条件的筛选出来)
-- 语法：Select 结果 from 表1，表2 where 表1.column1 = 表2.column2
SELECT * FROM student,major WHERE majorid = id
SELECT
s.num,
s.name,
s.sex,
m.name
FROM student s INNER JOIN major m ON s.majorid = m.id
-- 添加关联 (左外连接left join，把左边表的所有数据筛选出来，而右边表只会筛选满足条件的)
-- select 结果 from 表1 left join 表2 on 表1.column1 = 表2.column2
SELECT
*
FROM student s LEFT JOIN major m ON s.majorid = m.id
-- 添加关联 (右外连接right join，把右边表的所有数据筛选出来，而左边表只会筛选满足条件的)
-- select 结果 from 表1 right join 表2 on 表1.column1 = 表2.column2
SELECT
COUNT(s.num),
m.name
FROM student s RIGHT JOIN major m ON s.majorid = m.id
GROUP BY m.name
-- eg：四表查询
-- 学号，姓名，性别，专业，课程名称
-- GROUP_CONCAT(c.name) 把同一个组中,多个课程名称连接起来
SELECT
s.num,
s.name,
s.sex,
m.name ,
GROUP_CONCAT(c.name) cname
FROM student s LEFT JOIN major m ON s.majorid = m.id
LEFT JOIN student_course sc ON s.num = sc.student_num
LEFT JOIN course c ON c.id = sc.course_id
GROUP BY s.num,s.name,s.sex,m.name 子查询
-- 子查询 (嵌套查询)
-- 在一个查询中又出现了查询语句
-- 其可以出现在 from(表子查询) 把查询结果当作一个表
-- where(标量子查询) 结果集只有一行、(列子查询) 结果集一列多行
-- 后面
-- 查询身高最高的学生(标量子查询)
SELECT *
FROM student
WHERE height = (SELECT MAX(height)
FROM student)
-- (列子查询)
SELECT *
FROM student
WHERE height IN (SELECT height
FROM student
WHERE height = 1.76 OR height = 1.90)
-- 名字重复的(表子查询)
SELECT *
FROM (SELECT NAME,COUNT(*) AS c FROM student GROUP BY NAME) AS t
WHERE t.c > 1
-- 查询身高大于平均升高
SELECT *
FROM student
WHERE height > (SELECT AVG(height) FROM student)
-- 查询与李四同专业的学生
SELECT *
FROM student
WHERE majorid = (SELECT majorid FROM student WHERE NAME = '李四')
-- 查询每个专业身高最高的
SELECT *
FROM student s INNER JOIN (SELECT MAX(height) mh,majorid FROM student GROUP BY majorid) t
ON s.height = t.mh AND s.majorid = t.majorid结果合并
/*
nuion 合并多个查询结果
注意：合并的结果列数必须一致
1、UNION 的语法如下：
[SQL 语句 1]
UNION
[SQL 语句 2]
2、UNION ALL 的语法如下：
[SQL 语句 1]
UNION ALL
[SQL 语句 2]
当使用union 时，mysql 会把结果集中重复的记录删掉
而使用union all ，mysql 会把所有的记录返回，且效率高于union 。
*/
-- 法1
SELECT num,NAME,sex FROM student WHERE sex = '男'
UNION
SELECT num,NAME,sex FROM student WHERE sex = '女'
-- 法2
-- 合并并去重
SELECT num,NAME,sex FROM student WHERE sex = '男'
UNION
SELECT num,NAME,sex FROM student WHERE height = 1.90
-- 只合并不去重
SELECT num,NAME,sex FROM student WHERE sex = '男'
UNION ALL
SELECT num,NAME,sex FROM student WHERE height = 1.90排序
-- 排序
-- 查询结果排序,使用 ORDER BY 子句排序 order by 排序列 ASC/DESC
-- asc代表的是升序，desc代表的是降序，如果不写，默认是升序
-- order by子句中可以支持单个字段、多个字段
-- 默认
SELECT * FROM student ORDER BY height
-- ASC 升序
SELECT * FROM student ORDER BY birthday ASC
-- DESC 降序
SELECT * FROM student ORDER BY birthday DESC
-- 多个排序 加入条件
SELECT * FROM student WHERE num > 0 ORDER BY birthday DESC ,height ASC数量限制
-- 数量限制
-- limit子句：对查询的显示结果限制数目 (sql语句最末尾位置)
-- 开始位置(开始位置为0)，查询的数量
-- 使用场景 一次只查询一部分数据 提高效率
-- limit(n-1)*2,2
SELECT * FROM student LIMIT 0,2 -- 1 当前页码为n
SELECT * FROM student LIMIT 2,2 -- 2
SELECT * FROM student LIMIT 4,2 -- 3特点
查询列表可以是：表中的字段、常量、表达式、函数
查询的结果是一个虚拟的表格
多表关联
数据库设计范式
1、第一范式-->确保每列保持原子性(数据库中的所有字段都是不可分解的原子值) eg：联系方式-->邮箱、电话...就不满足第一范式
2、第二范式-->需要有主键列，其他字段都依赖于主键列(有主键后可以精确定位)
3、第三范式-->确保每列都和主键列直接相关，要求表中不包含已在其他表中包含的非主关键字信息
eg：如果多个表有关联，只把个各主键关联在一起，其他非主关键字不用关联
-- 专业表
-- 学生表与专业表关联
CREATE TABLE major(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20) NOT NULL
)
INSERT INTO major(id,NAME)
VALUES(1,'计算机')
(2,'管理'),
(3,'网络'),
(4,'数学')添加外键约束
-- 修改表 添加列
ALTER TABLE student ADD majorid INT
-- majorid 为外键，外键必须与另一个表的主键关联且类型一致
-- 表与表的关系：一对多或多对一
-- 外键约束(foreign key)
-- 约束的作用：数据与数据间的束缚，防止随意操作，保证数据的完整性
-- 建表时添加
CONSTRAINT 约束名 FOREIGN KEY(majorid) REFERENCES major(id)
-- 建表后添加
ALTER TABLE student ADD CONSTRAINT fk_student_major_on_majorid FOREIGN KEY(majorid) REFERENCES major(id)
-- 约束名规则:
-- 例：FK_ForeignTable_PrimaryTable_On_ForeignColumn例：学生选课关系
-- 多对多关系 -- 由于一个学生可以选择多个课程,所以在学生表中添加课程外键就不行 -- 需要再设计一个学生选课表(只要存储学生和课程的编号(主键)即可), 一般把这种表也称为关系表
CREATE TABLE course(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
)
CREATE TABLE student_course(
id INT PRIMARY KEY AUTO_INCREMENT,
student_num INT,
courseid INT,
CONSTRAINT fk_student_student_course_on_student_num FOREIGN KEY(student_num) REFERENCES student(num),
CONSTRAINT fk_course_student_course_on_courseid FOREIGN KEY(courseid) REFERENCES course(id)
)
