MySql初学日记

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小数点后的长度文本：tinytexttextmediumtextlongtext列的约束: 列的规则
*/

-- 学生表 学号，姓名，性别，生日，电话，地址，身高，注册时间
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 studentWHERE num = 7
-- 清空整张表
TRUNCATE TABLE student;


-- 更改数据 注意条件
UPDATE student SET address = '陕西西安',height = 1.79,reg_time = '2024-5-5 17:02:35'
WHERE num = 1

DQL

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,指定字符)：返回子串第一次出现的索引，如果找不到返回0SELECT 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; 可以有多个whenSELECT NAME,CASE WHEN height >=1.85 THEN '高' ELSE '正常' END AS heightFROM studentSELECT NAME,(CASE WHEN height >=1.85 THEN '高' WHEN height >= 1.65 THEN '正常' ELSE '低' END) AS heightFROM 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 studentSELECT 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 studentSELECT 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.00SELECT * FROM student WHERE height BETWEEN 1.90 AND 2.00

-- in 判断某字段的值是否属于in列表中的某一项SELECT * FROM student WHERE height = 1.90 OR height = 1.79SELECT * 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 = NULLSELECT * FROM student WHERE address IS NULLSELECT * 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.column2SELECT * FROM student,major WHERE majorid = idSELECT 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.column2SELECT *FROM student s LEFT JOIN major m ON s.majorid = m.id-- 添加关联 (右外连接right join，把右边表的所有数据筛选出来，而左边表只会筛选满足条件的) 
-- select 结果 from 表1 right join 表2 on 表1.column1 = 表2.column2SELECT COUNT(s.num),m.nameFROM student s RIGHT JOIN major m ON s.majorid = m.idGROUP BY m.name-- eg：四表查询    
-- 学号，姓名，性别，专业，课程名称
-- GROUP_CONCAT(c.name) 把同一个组中,多个课程名称连接起来SELECTs.num,s.name,s.sex,m.name ,GROUP_CONCAT(c.name) cnameFROM student s LEFT JOIN major m ON s.majorid = m.idLEFT JOIN student_course sc ON s.num = sc.student_numLEFT JOIN course c ON c.id = sc.course_idGROUP BY s.num,s.name,s.sex,m.name

子查询

-- 子查询 (嵌套查询)
-- 在一个查询中又出现了查询语句
-- 其可以出现在 from(表子查询) 把查询结果当作一个表
--      where(标量子查询) 结果集只有一行、(列子查询) 结果集一列多行
-- 后面 

-- 查询身高最高的学生(标量子查询)SELECT * FROM student WHERE height = (SELECT MAX(height) FROM student)

-- (列子查询)SELECT *FROM studentWHERE height IN (SELECT heightFROM studentWHERE height = 1.76 OR height = 1.90)

-- 名字重复的(表子查询)SELECT *FROM (SELECT NAME,COUNT(*) AS c FROM student GROUP BY NAME) AS tWHERE t.c > 1



-- 查询身高大于平均升高SELECT *FROM studentWHERE height > (SELECT AVG(height) FROM student)

-- 查询与李四同专业的学生SELECT *FROM studentWHERE majorid = (SELECT majorid FROM student WHERE NAME = '李四')-- 查询每个专业身高最高的SELECT *FROM student s INNER JOIN (SELECT MAX(height) mh,majorid FROM student GROUP BY majorid) tON 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 。

*/

-- 法1SELECT num,NAME,sex FROM student WHERE sex = '男'UNIONSELECT 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 ALLSELECT 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,2SELECT * FROM student LIMIT 0,2    -- 1  当前页码为nSELECT * FROM student LIMIT 2,2    -- 2SELECT * 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)
)