MySQL学习笔记

知勤者笔记

已于 2024-05-27 07:10:55 修改

阅读量427

点赞数 1

分类专栏：后端开发文章标签：数据库服务器

于 2023-03-05 00:08:04 首次发布

本文链接：https://blog.csdn.net/zhiqinzhe/article/details/129341383

版权

后端开发专栏收录该内容

10 篇文章 0 订阅

订阅专栏

库操作

创建数据库


刚安装的数据库，自带四个系统库，是不能进行操作修改的，如果需要使用，可以自己创建数据库在进行操作
格式： CREATE database 库名 字符编码;
示例： CREATE database student charset=utf8;

查看数据库


查看权限范围内的所有数据库
命令： SHOW DATABASES;

切换数据库


在新建表的时候，需要在指定的数据库中建表，所以需要切换数据库
格式： use 数据库名;
示例： use student;

在创建表的时候，需要切换致指定的数据库才能创建表，如果没有会默认一个随机的数据库；

删除数据库


不需要的数据库可以使用删除命令将数据库删除
格式： DROP DATABASE 数据库名;
示例： DROP DATABASE student;

通过已上方式删除数据库，如果数据库不不存在，会报错，可以使用 IF EXISTS解决
格式： DROP DATABASE IF EXISTS 数据库名;
示例： DROP DATABASE IF EXISTS student;

表操作

数据类型

MYSQL在创建表的时候需要指定表中每列的数据类型，MySQL数据类型有很多种，我们不需要都掌握，只需要掌握常用的几个数据类型即可

关键字	类型	说明
int	整型	存放整数类型，长度最大为11
double	浮点型	例如double(5,2)表示最多5位，其中必须有2位小数，即最大值为999.99,存放小数
char	固定长度字符类型	最多255个字符,存字符串使用
varchar	可变长度字符串类型	最多65535个字符,存字符串使用
date	日期类型	存时间年月日，表现格式为：yyyy-MM-dd
time	时间类型	存时间时分秒，表现格式为：hh:mm:ss
datetime	日期时间类型	存时间年月日时分秒，表现格式为yyyy-MM-dd hh:mm:ss
enum	枚举	限定只能插入规定的数据项，例如：enum('男','女')，限制数据只能存男女
text	文本类型	TEXT用来保存文本类型的字符串，一般用于存在文章，存储字符串数量大

创建表


MySQL本身对db和table的个数没有限制，但会受限于底层文件系统的限制，InnoDB最多允许40亿个表，在同一个数据库中表名必须唯一；
格式：
CREATE TABLE 表名(
	列名 列类型,
	列名	列类型
     ……
);

示例：
CREATE TABLE stu(
	sid	    CHAR(6),
	sname	VARCHAR(20),
	age		INT,
	gender	VARCHAR(10) 
);

注意：多个列之间需要用英文逗号隔开，结尾需要输入分好结束！（必须是英文状态下输入）

字段约束


mysql 常见的约束：
not null：非空约束，约束的字段不能为NULL
unique：唯一约束，约束的字段不能重复
primary key：主键约束，约束的字段既不能为NULL，也不能重复，可以跟自增函数组合使用：AUTO_INCREMENT，一张表只能有一个主键和一个自增函数，主键的类型必须是int类型；

其他：
自增函数：AUTO_INCREMENT（使用自增函数必须更住建组合使用）
默认值：DEFAULT '0'
设置备注：COMMENT

查看表


查看当前数据库所有的表： SHOW TABLES;
查看指定表的创建语句： SHOW CREATE TABLE 表名;
查看表结构： DESC 表名;

修改表


添加一列：
格式： ALTER TABLE 表名 ADD(列名 列类型);
示例：给stu表中添加mobile列： ALTER TABLE stu ADD(mobile varchar(128));

修改列类型：
格式： ALTER TABLE 表名 MODIFY 列名 列类型;
示例：将stu表中mobile列的类型改为int(11)： ALTER TABLE stu MODIFY mobile int(11);

修改列名：
格式： ALTER TABLE 表名 CHANGE 原列名 新列名 列类型;
示例：将stu表的mobile列名改为test： ALTER TABLE stu CHANGE mobile test int(11);

删除列：
格式： ALTER TABLE 表名 DROP 列名;
示例：删除stu表的test列： ALTER TABLE stu DROP test;

修改表名：
格式： ALTER TABLE 原表名 RENAME TO 新表名;
示例：将stu表名改为student：ALTER TABLE stu RENAME TO student;

删除表


不需要的表可以使用删除命令将表删除
格式： drop table 表名;
示例： drop table stu;

通过已上方式删除表，如果表不不存在，会报错，可以使用 IF EXISTS解决
格式： DROP table IF EXISTS 表名;
示例： DROP table IF EXISTS stu;

操作数据

新增数据



格式：insert into 表名 (列名1, 列名2, 列名3, ...) values (值1, 值2, 值3, ...);
示例：
首先创建一个stu表：
CREATE TABLE stu(
	sid	    CHAR(6),
	sname	VARCHAR(20),
	age		INT,
	gender	VARCHAR(10) 
);

在表中插入数据：
INSERT INTO stu (sid, sname, age, gender) VALUES (1, '张三', 18, '男');

同时插入多行：
INSERT INTO stu (sid, sname, age, gender) VALUES (2, '李四', 18, '女'), (3, '王老五', 28, '男');

这里的列名也可以不写（不建议有此习惯）：
INSERT INTO stu VALUES (1, '张三', 18, '男');

注意：
这里的列名的位置可以调换，但必须对应好值！
如果字段设有默认值可以不用插入数据，使用默认的
自增住建不需要插入数据（会自增）

删除数据


格式： DELETE FROM 表名 WHERE 条件;
示例：删除stu表中id=1的数据：  DELETE FROM stu WHERE id = 1;
多个条件：删除stu表中18岁的男性的数据：  DELETE FROM stu WHERE age = 18 or gender = '男';

注意：不加条件会删除所有的数据！

清空表： TRUNCATE TABLE 表名;  ----(谨慎执行)

修改数据


格式： UPDATE 表名 SET 列名1=值1, 列名2=值2…… WHERE 条件
示例：修改stu表中张三的性别改为女：
UPDATE stu SET gender='女' WHERE sname='张三';
修改多个值：修改stu表中id大于18，姓名=张三的数据，性别改为女，年龄改为18： UPDATE stu SET gender='女',age=18 WHERE sname='张三' and id>18;
 
注意：不加条件会修改所有的数据！

查询数据(重要)

语法


数据查询语言，数据库执行DQL语句不会对数据进行改变，而是让数据库发送结果集给客户端

语法：
SELECT selection_list /*要查询的列名称*/
  FROM table_list /*要查询的表名称*/
  WHERE condition /*行条件*/
  GROUP BY grouping_columns /*对结果分组*/
  HAVING condition /*分组后条件*/
  ORDER BY sorting_columns /*对结果排序*/
  LIMIT offset_start, row_count /*结果限定*/

导入测试数据


 -- 创建学生表：
CREATE TABLE `student` (
  `Sid` varchar(10) NOT NULL COMMENT '学生id',
  `Sname` varchar(10) DEFAULT NULL COMMENT '学生姓名',
  `Sage` varchar(10) DEFAULT NULL COMMENT '学生年龄',
  `Ssex` varchar(15) DEFAULT NULL COMMENT '学生性别',
  PRIMARY KEY (`Sid`)
) COMMENT '学生表';

 -- 创建课程表
CREATE TABLE `course` (
  `Cid` varchar(20) NOT NULL COMMENT '课程id',
  `Cname` varchar(20) DEFAULT NULL COMMENT '课程名',
  `Tid` varchar(10) DEFAULT NULL COMMENT '教师id',
  PRIMARY KEY (`Cid`)
) COMMENT '课程表';

 -- 创建教师表
 CREATE TABLE `teacher` (
  `Tid` varchar(10) NOT NULL COMMENT '教师id',
  `Tname` varchar(20) DEFAULT NULL COMMENT '教师姓名',
  PRIMARY KEY (`Tid`)
) COMMENT '教师表';
 
 -- 创建成绩表
CREATE TABLE `sc` (
  `Sid` int NOT NULL COMMENT '学生id',
  `Cid` varchar(10) NOT NULL COMMENT '课程id',
  `score` varchar(10) DEFAULT NULL COMMENT '成绩',
  PRIMARY KEY (`Sid`,`Cid`)
) COMMENT '成绩表';

 -- 向学生表中添加数据
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '上官青云' , '1989-07-01' , '女');
insert into Student values('08' , '赵二牛' , '1990-01-20' , '女');

 -- 向课程表中添加数据
insert into Course values('01','语文','02');
insert into Course values('02','数学','01'); 
insert into Course values('03','英语','03');

 -- 向教师表中添加数据
insert into Teacher values('01','欧阳政宇');
insert into Teacher values('02','关羽'); 
insert into Teacher values('03','张一德');
 
 -- 向课程表中添加数据
insert into SC values('01','01',80); 
insert into SC values('01','02',90);
insert into SC values('01','03',99); 
insert into SC values('02','01',70);
insert into SC values('02','02',60); 
insert into SC values('02','03',80); 
insert into SC values('03','01',80); 
insert into SC values('03','02',80); 
insert into SC values('03','03',80); 
insert into SC values('04','01',50); 
insert into SC values('04','02',30); 
insert into SC values('04','03',20); 
insert into SC values('05','01',76); 
insert into SC values('05','02',87); 
insert into SC values('06','01',31); 
insert into SC values('06','03',34); 
insert into SC values('07','02',89); 
insert into SC values('07','03',98);

查询所有列


SELECT * FROM student;

查询指定列


SELECT Sid, Sname FROM student;

重命名列


重命名关键字：AS
SELECT Sid AS '学生id', Sname AS '学生姓名' FROM student;
AS可以默认不写，例如：
SELECT Sid '学生id', Sname '学生姓名' FROM student;

条件查询


条件查询就是在查询时给出WHERE子句，在WHERE子句中可以使用如下运算符及关键字：
=、!=、<>、<、<=、>、>=
BETWEEN … AND --- 在……之间
字段 IN(set) --- 在集合中
字段 NOT IN (set) --- 不在集合中
字段 IS NULL ———查询出为null的
字段 IS NOT NULL ———查询出不为null的
AND 和
OR 或者

练习


1.查询性别为女的所有学生：
SELECT * FROM student WHERE Ssex = '女';

2.查询性别为女并且Sid列大于5的学生：
SELECT * FROM student WHERE Ssex = '女' AND Sid > 5;

3.查询性别为男或者出生年月大于1990-05-05的学生姓名和出生年月：
SELECT Sname,Sage FROM student WHERE Ssex = '男' OR Sage > '1990-05-05';

4.查询姓名为郑竹、吴兰、王菊的学生：
SELECT * FROM student WHERE Sname in ('郑竹','吴兰','王菊');

4.查询姓名除王菊、吴兰以外的学生：
SELECT * FROM student WHERE Sname NOT in ('吴兰','王菊');

5.查询年龄为null的记录：
SELECT * FROM student WHERE Sage is null;

6.查询年龄不为null的记录：
SELECT * FROM student WHERE Sage IS NOT NULL;

7.查询id在02至07的学生信息：
SELECT * FROM student WHERE S>=2 AND S<=7;
或者
SELECT * FROM student WHERE S BETWEEN 2 and 7;

8.查询非男性的所有学生：
SELECT * FROM student WHERE Ssex != '男';
-- 或者
SELECT * FROM student WHERE Ssex <> '男';
-- 或者
SELECT * FROM student WHERE NOT Ssex = '男';

模糊查询


当想查询姓名中包含张的学生时就需要使用模糊查询了。模糊查询需要使用关键字LIKE
配合百分号和下划线使用
%：匹配所有字符，包含空
_：匹配单个字符，不包含空

查询姓名由两个字符构成的学生记录
SELECT * FROM student WHERE Sname LIKE '__';

查询姓李，并且姓名由两个字符构成的学生记录
SELECT * FROM student WHERE Sname LIKE '李_';

查询姓赵的学生记录
SELECT * FROM student WHERE Sname LIKE '赵%';

查询姓名包含“云”的学生信息：
SELECT * FROM student WHERE Sname LIKE '%云%';

字段控制查询


去重函数： DISTINCT
行或两行以上记录中系列的上的数据都相同，例如我们搜索学生表中性别有哪些的时候，执行一下语句查询：
SELECT Ssex FROM student;
通过上面的语句查询出性别有男和女，有多行，这时候我们就可以使用去重语句进行去重
SELECT DISTINCT Ssex FROM student;

IFNULL()函数，将列表中数据为空的转换为指定的数据：
IFNULL(a,b)a表示列名，b是需要指定的数据，当a列中数据为null时，会显示为b
SELECT Sname,IFNULL(Ssex,'未知') FROM student;

排序


排序函数： ORDER BY
查询成绩表，成绩列升序排序：（升序：从小到大）
SELECT * FROM sc ORDER BY score ASC;

查询成绩表，成绩列降序排序：（降序：从大到小）
SELECT * FROM sc ORDER BY score DESC;

查询成绩表，成绩列降序排序，如果成绩相同，就使用课程ID降序排序：（降序：从大到小）
SELECT * FROM sc ORDER BY score DESC,Cid DESC;

聚合函数


聚合函数是用来做纵向运算的函数：
COUNT()：统计指定列不为NULL的记录行数；
MAX()：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
MIN()：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
SUM()：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；
AVG()：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；

COUNT():
查询学生表有多少个学生：
SELECT count(*) FROM student;

查询男生有多少人数：
SELECT count(*) FROM student WHERE Ssex='男';

MAX() 和 MIN()：
查询学生ID为01的学生最高成绩和最低成绩
SELECT MAX(score),MIN(score) FROM sc WHERE Sid='01';

查询年纪最小的学生出生日期：
SELECT MIN(Sage) FROM student;

SUM() 和 AVG()：
查询学生的总成绩和平均成绩
SELECT SUM(score),AVG(score) FROM sc WHERE Sid='01';

分组函数


分组关键字： GROUP BY （阿华警告，分组要好好理解）
例如，我们对学生表进行分组，按男女分：
SELECT Ssex FROM student GROUP BY Ssex;

1.查询男生和女生的人数：
SELECT Ssex,count(Ssex) '人数' FROM student GROUP BY Ssex;

2.查询每个学生的平均成绩和学生ID：
SELECT Sid,avg(score) FROM sc GROUP BY Sid;


分组后条件: HAVING
WHERE是对分组前的条件进行约束，而 HAVING是对分组后数据的约束

1.查询学生平均成绩大于80 的学生ID和平均成绩
SELECT Sid,avg(score) FROM sc GROUP BY Sid HAVING avg(score) > 80;

作业：
1.查询姓赵的学生有多少个

2.查询每门课程的平均成绩

3.查询学生总成绩大于200分的学生ID和总成绩

限定查询


LIMIT用来限定查询结果的起始行，以及总行数

1.查询学生表前五位同学
SELECT * FROM student LIMIT 5;

2.查询成绩排行前三的成绩
SELECT score FROM sc ORDER BY score DESC limit 3;

3.查询3条记录，从3开始
SELECT score FROM sc ORDER BY score DESC LIMIT 3,3;

连表查询


关系型数据库,同一个数据库中，表与表直接是有相互联系，相互约束的，mysql使用join来完成表链接的，JOIN 按照

功能大致分为如下三类：
INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录

在连接之前首先要找到两张表的关联字段才能进行链接，如学生表中有Sid，和成绩表中的Sid关联，成绩表中的Cid又和课程表的Cid关联

1.将成绩表与学生表关联起来：
SELECT * FROM student join sc ON student.Sid = sc.Sid;

2.将成绩表、学生表、课程表关联起来：
SELECT * FROM student join sc ON student.Sid = sc.Sid JOIN course ON sc.Cid = course.Cid;

3.查询总成绩前三的学生姓名
SELECT student.Sname,SUM(score) FROM student join sc ON student.Sid = sc.Sid GROUP BY student.Sid ORDER BY SUM(score) DESC LIMIT 3;

4.查询数学成绩排行前三的学生姓名和数学成绩
SELECT Sname,score FROM student join sc ON student.Sid = sc.Sid JOIN course ON sc.Cid = course.Cid WHERE Cname='数学' ORDER BY score DESC LIMIT 3;

子查询


子查询：将查询结果作为下一个查询的条件

1.查询赵二牛的总成绩
SELECT sum(score) FROM sc WHERE Sid = (SELECT Sid FROM student WHERE Sname='赵雷') GROUP BY Sid;

2.查询总成绩大于200分的学生姓名
SELECT Sname FROM student WHERE Sid in (SELECT Sid FROM sc  GROUP BY Sid HAVING SUM(score) > 200);

合并结果集


作用：合并结果集就是把两个select语句的查询结果合并到一起，前提：列数必须一致！
合并结果集有两种方式：
 	UNION：去除重复记录，例如：SELECT * FROM t1 UNION SELECT * FROM t2；
 	UNION ALL：不去除重复记录，例如：SELECT * FROM t1 UNION ALL SELECT * FROM t2。

查询成绩表中，学生成绩最低的和学生成绩最高的所有信息
(SELECT * FROM sc ORDER BY score DESC LIMIT 1)
UNION ALL
(SELECT * FROM sc ORDER BY score asc LIMIT 1);

常用函数


-- 获取当前时间：
SELECT NOW();

-- 统计字符串个数，str是字符串
CHAR_LENGTH(str)

-- 流程控制语句 CASE （允许套用多个WHEN）
格式：CASE WHEN 条件 THEN 如果前面条件成立就执行  ELSE 否则 END

示例：
SELECT *,(CASE WHEN s_score <= '30' THEN '超级不及格' WHEN s_score > 30 AND s_score <60 THEN '不及格'  ELSE '及格' END) FROM score;

-- 字符串拼接
SELECT s_id,c_id,s_score,CONCAT(s_score,'分') FROM score 

-- 字符串切片：
-- 从左边开始切1位
SELECT *,LEFT(s_name,1) FROM student;

-- 从右边边开始切2位
SELECT *,RIGHT(s_name,2) FROM student;

-- 保留两位小数
-- format(a,b) a是小数，b是保留的位数，四舍五入
SELECT sid, FORMAT(avg(score),2) FROM sc GROUP BY sid;

-- 字符串替换 REPLACE(string, from_string, new_string)
string：原始字符串，from_string：要替换的子字符串，new_string:新的替换子字符串

-- 设置变量
set @i= 90;
SELECT @i as tb_name;

知勤者笔记

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
打赏
0
评论
MySQL学习笔记

my.ini文件内容如下，直接复制进文件中，其中basedir 为mysql的安装目录，datadir 为数据库的数据的存放目录，需要根据自己安装的文件修改 (注意：路径中用 / ，不要用 \ )新建系统变量，变量名称：MYSQL_HOME，变量值：C:/installFile/mysql-5.7.36-winx64，变量值需要根据自己的安装目录修改。MYSQL在创建表的时候需要指定表中每列的数据类型，MySQL数据类型有很多种，我们不需要都掌握，只需要掌握常用的几个数据类型即可。
复制链接

扫一扫