当前位置：首页 > news >正文

Mysql学习笔记

news 来源：原创 2025/6/9 23:22:34

连接数据库

找到 MySQL 安装目录下的 bin 目录，然后打开命令窗口，在命令窗口中按如下语法输入命令：

mysql - h MySQL 数据库服务器的 IP 地址 - u 用户名 - p

然后按下回车键，输入密码即可

数据库操作

创建数据库

CREATE DATABASE [ IF NOT EXISTS ] 数据库名称 DEFAULT CHARACTER SET 字符集 COLLATE 排序规则;

示例：创建数据库lesson，并指定字符集为 GBK ，排序规则为 GBK_CHINESE_CI

CREATE DATABASE IF NOT EXISTS lesson DEFAULT CHARACTER SET GBK COLLATE

GBK_CHINESE_CI;

修改数据库

ALTER DATABASE 数据库名称 CHARACTER SET 字符集 COLLATE 排序规则 ;

示例：修改数据库lesson的字符集为 UTF8 ，排序规则为 UTF8_GENERAL_CI

ALTER DATABASE lesson CHARACTER SET UTF8 COLLATE UTF8_GENERAL_CI;

删除数据库

DROP DATABASE [IF EXISTS] 数据库名称;

示例：删除数据库lesson

DROP DATABASE IF EXISTS lesson;

查看数据库

SHOW DATABASES;

使用数据库

USE 数据库名称;

示例：使用数据库lesson

USE lesson;

数据表操作

数据表类型

MySQL 中的数据表类型有许多，如 MyISAM 、 InnoDB 、 HEAP 、 BOB 、 CSV 等。其中最常用的就是 MyISAM 和 InnoDB。

创建数据表

CREATE TABLE [ IF NOT EXISTS ] 数据表名称 (

字段名 1 列类型 ( 长度 ) [ 修饰属性 ] [ 键 / 索引 ] [ 注释 ] ,

字段名 2 列类型 ( 长度 ) [ 修饰属性 ] [ 键 / 索引 ] [ 注释 ] ,

字段名 3 列类型 ( 长度 ) [ 修饰属性 ] [ 键 / 索引 ] [ 注释 ] ,

......

字段名 n 列类型 ( 长度 ) [ 修饰属性 ] [ 键 / 索引 ] [ 注释 ]

) [ ENGINE = 数据表类型 ][ CHARSET = 字符集编码 ] [ COMMENT = 注释 ] ;

示例：创建学生表，表中有字段学号、姓名、性别、年龄和成绩

CREATE TABLE IF NOT EXISTS student(
`number` VARCHAR(30) NOT NULL PRIMARY KEY COMMENT '学号，主键',
name VARCHAR(30) NOT NULL COMMENT '姓名',
sex TINYINT(1) UNSIGNED DEFAULT 0 COMMENT '性别：0-男 1-女 2-其他',
age TINYINT(3) UNSIGNED DEFAULT 0 COMMENT '年龄',
score DOUBLE(5, 2) UNSIGNED COMMENT '成绩'
)ENGINE=InnoDB CHARSET=UTF8 COMMENT='学生表';

修改数据表

修改表名

ALTER TABLE 表名 RENAME AS 新表名;

示例：将student表名称修改为 stu

ALTER TABLE student RENAME AS stu;

增加字段

ALTER TABLE 表名 ADD 字段名列类型(长度) [修饰属性] [键/索引] [注释];

示例：在 stu 表中添加字段联系电话(phone)，类型为字符串，长度为11，非空

ALTER TABELE stu ADD phone varchar(11) NOT NULL COMMENT '联系电话';

查看表结构

DESC 表名;

修改字段

-- MODIFY 只能修改字段的修饰属性

ALTER TABLE 表名 MODIFY 字段名列类型(长度) [修饰属性] [键/索引] [注释];

-- CHANGE 可以修改字段的名字以及修饰属性

ALTER TABLE 表名 CHANGE 字段名新字段名列类型(长度) [修饰属性] [键/索引] [注释];

示例：将 stu 表中的 sex 字段的类型设置为 VARCHAR ，长度为2，默认值为'男'，注释为 "性别，男，女，其他"

ALTER TABLE stu MODIFY sex VARCHAR(2) DEFAULT '男' COMMENT '性别：男，女，其他';

示例：将 stu 表中 phone 字段修改为 mobile ，属性保持不变

ALTER TABLE stu CHANGE phone mobile VARCHAR(11) NOT NULL COMMENT '联系电话';

删除字段

ALTER TABLE 表名 DROP 字段名;

示例：将 stu 表中的 mobile 字段删除

ALTER TABLE stu DROP mobile;

删除数据表

DROP TABLE [IF EXISTS] 表名;

示例：删除数据表 stu

DROP TABLE IF EXISTS stu;

练习

1. 在数据库 exercise 中创建课程表 stu_course ，包含字段课程编号 (number) ，类型为整数，长度为 11 ，是主键，自增长，非空、课程名称 (name) ，类型为字符串，长度为 20 ，非空、学分 (score) ，类型为浮点数，小数点后面保留 2 位有效数字，长度为 5 ，非空

-- 如果数据库不存在就创建数据库

CREATE DATABASE IF NOT EXISTS exercise DEFAULT CHARACTER SET UTF8 COLLATE UTF8_GENERAL_CI;

-- 使用数据库

USE exercise;

-- 在数据库中创建数据表stu_course

CREATE TABLE IF NOT EXISTS stu_course( `number` INT(11) AUTO_INCREMENT PRIMARY KEY NOT NULL COMMENT '课程号', name VARCHAR(20) NOT NULL COMMENT '课程名称', score DOUBLE(5, 2) NOT NULL COMMENT '学分' )ENGINE=InnoDB CHARSET=UTF8 COMMENT '课程表';

2. 将课程表重命名为 course

ALTER TABLE stu_course RENAME AS course;

3. 在课程表中添加字段学时(time)，类型为整数，长度为3，非空

ALTER TABLE course ADD time INT(3) NOT NULL COMMENT '学时';

4. 修改课程表学分类型为浮点数，小数点后面保留1位有效数字，长度为3，非空

ALTER TABLE course MODIFY score DOUBLE(3,1) NOT NULL COMMENT '学分';

5. 删除课程表

DROP TABLE IF EXISTS course;

6. 删除数据库exercise

DROP DATABASE IF EXISTS exercise;

DML数据操作语言

INSERT

-- 需要注意，VALUES后的字段值必须与表名后的字段名一一对应

INSERT INTO 表名(字段名1, 字段名2, ..., 字段名n) VALUES(字段值1, 字段值2, ..., 字段值

n);

-- 需要注意，VALUES后的字段值必须与创建表时的字段顺序保持一一对应

INSERT INTO 表名 VALUES(字段值1, 字段值2, ..., 字段值n);

-- 一次性插入多条数据

INSERT INTO 表名(字段名1, 字段名2, ..., 字段名n) VALUES(字段值1, 字段值2, ..., 字段值

n),(字段值1, 字段值2, ..., 字段值n), ... , (字段值1, 字段值2, ..., 字段值n);

INSERT INTO 表名 VALUES(字段值1, 字段值2, ..., 字段值n), (字段值1, 字段值2, ..., 字段值

n), ..., (字段值1, 字段值2, ..., 字段值n);

示例：向课程表中插入数据

INSERT INTO course(`number`, name, score, `time`) VALUES (1, 'Java基础', 4, 40);

INSERT INTO course VALUES (2, '数据库', 3, 20);

INSERT INTO course(`number`, score, name, `time`) VALUES (3, 5, 'Jsp', 40);

INSERT INTO course(`number`, name, score, `time`) VALUES (4, 'Spring', 4, 5),(5,

'Spring Mvc', 2, 5);

INSERT INTO course VALUES (6, 'SSM', 2, 3), (7, 'Spring Boot', 2, 2);

UPDATA

UPDATE 表名 SET 字段名1=字段值1[,字段名2=字段值2, ..., 字段名n=字段值n] [WHERE 修改条件]

示例：将数据库的学分更改为4，学时更改为15

UPDATA course SET score=4,time=15 WHERE name='数据库';

DELETE

DELETE FROM 表名 [WHERE 删除条件];

示例:删除课程表中课程编号为1的数据

DETELE FROM course WHERE 'number'=1;

TRUNCATE

-- 清空表中数据

TRUNCATE [TABLE] 表名;

示例：清空课程表的数据

TRUNCATE course;

DELETE和TRUNCATE区别

DELETE 语句根据条件删除表中数据，而 TRUNCATE 语句则是将表中数据全部清空；如果 DELETE 语

句要删除表中所有数据，那么在效率上要低于 TRUNCATE 语句。

如果表中有自增长列， TRUNCATE 语句会重置自增长的计数器，但 DELETE 语句不会。

TRUNCATE 语句执行后，数据无法恢复，而 DELETE 语句执行后，可以使用事务回滚进行恢复。

DQL数据查询语言

SELECT

SELECT ALL/DISTINCT * | 字段名1 AS 别名1[,字段名1 AS 别名1, ..., 字段名n AS 别名n]

FROM 表名 WHERE 查询条件

ALL 表示查询所有满足条件的记录，可以省略； DISTINCT 表示去掉查询结果中重复的记录

AS 可以给数据列、数据表取一个别名

示例：从课程表中查询课程编号小于5的课程名称

SELECT name FROM course WHERE 'number'<5;

从课程表中查询课程名称为"Java基础"的学分和学时

SELECT score, `time` FROM course WHERE name='Java基础';

比较操作符

操作符	语法	说明
IS NULL	字段名IS NULL	如果字段的值为 NULL ，则条件满足
IS NOT NULL	字段名IS NOT NULL	如果字段的值不为 NULL ，则条件满足
BETWEEN...AND	字段名BETWEEN 最小值 AND 最大值	如果字段的值在最小值与最大值之间（能够取到最小值和最大值），则条件满足
LIKE	字段名 LIKE '%匹配内容%'	如果字段值包含有匹配内容，则条件满足
IN	字段名 IN (值1,值2...值n)	如果字段值在值 1, 值 2, ... ，值 n 中，则条件满足

示例：从课程表查询课程名为NULL的课程信息

SELECT * FROM course WHERE name IS NULL;

示例：从课程表查询课程名不为NULL的课程信息

SELECT * FROM course WHERE name IS NOT NULL;

示例：从课程表查询学分在2~4之间的课程信息

SELECT * FROM course WHERE score BETWEEN 2 AND 4;

示例：从课程表查询课程名包含"V"的课程信息

SELECT * FROM course WHERE name LIKE '%V%';

示例：从课程表查询课程名以"J"开头的课程信息

SELECT * FROM course WHERE name LIKE 'J%';

示例：从课程表查询课程名以"p"结尾的课程信息

SELECT * FROM course WHERE name LIKE '%p';

示例：从课程表查询课程编号为1,3,5的课程信息

SELECT * FROM course WHERE 'number' IN (1,3,5);

分组

数据表准备：新建学生表 student ，包含字段学号（ no ），类型为长整数，长度为 20 ，是主键，自增长，非空；姓名（name ），类型为字符串，长度为 20 ，非空；性别（ sex ），类型为字符串，长度为 2，默认值为 " 男 " ；年龄（ age ），类型为整数，长度为 3 ，默认值为 0 ；成绩（ score ），类型为浮点数，长度为5 ，小数点后面保留 2 位有效数字。

DROP TABLE IF EXISTS student;

CREATE TABLE student(
no BIGINT(20) AUTO_INCREMENT NOT NULL PRIMARY KEY COMMENT '学号，主键',
name VARCHAR(20) NOT NULL COMMENT '姓名',
sex VARCHAR(2) DEFAULT '男' COMMENT '性别',
age INT(3) DEFAULT 0 COMMENT '年龄',
score DOUBLE(5,2) COMMENT '成绩'
)ENGINE=InnoDB CHARSET=UTF8 COMMENT='学生表';

插入测试数据：

INSERT INTO student(no, name, sex, age, score) VALUES (DEFAULT, '张三', '男', 20,

59);

INSERT INTO student(no, name, sex, age, score) VALUES (DEFAULT, '李四', '女', 19,

62);

INSERT INTO student(no, name, sex, age, score) VALUES (DEFAULT, '王五', '其他',

21, 62);

INSERT INTO student(no, name, sex, age, score) VALUES (DEFAULT, '龙华', '男', 22,

75);

INSERT INTO student(no, name, sex, age, score) VALUES (DEFAULT, '金凤', '女', 18,

80);

INSERT INTO student(no, name, sex, age, score) VALUES (DEFAULT, '张华', '其他',

27, 88);

INSERT INTO student(no, name, sex, age, score) VALUES (DEFAULT, '李刚', '男', 30,

88);

INSERT INTO student(no, name, sex, age, score) VALUES (DEFAULT, '潘玉明', '女',

28, 81);

INSERT INTO student(no, name, sex, age, score) VALUES (DEFAULT, '凤飞飞', '其他',

32, 90);

分组查询

SELECT ALL/DISTINCT * | 字段名1 AS 别名1[,字段名1 AS 别名1, ..., 字段名n AS 别名n]

FROM 表名 WHERE 查询条件 GROUP BY 字段名1，字段名2,..., 字段名n

分组查询所得的结果只是该组中的第一条数据。

示例：从学生表查询成绩在80分以上的学生信息并按性别分组

SELECT * FROM student WHERE score>80 GROUP BY sex;

示例：从学生表查询成绩在60~80之间的学生信息并按性别和年龄分组

SELECT * FROM student WHERE score BETWEEN 60 AND 80 GROUP BY sex, age;

聚合函数

COUNT() ：统计满足条件的数据总条数

示例：从学生表查询成绩在80分以上的学生人数

SELECT COUNT(*) total FROM student WHERE score>80;

SUM()：只能用于数值类型的字段或者表达式，计算该满足条件的字段值的总和

示例：从学生表查询不及格的学生人数和总成绩

SELECT COUNT(*) totalCount,SUM(score) totalScore FROM student WHERE score<60;

AVG()：只能用于数值类型的字段或者表达式，计算该满足条件的字段值的平均值

示例：从学生表查询男生、女生、其他类型的学生的平均成绩

SELECT sex,AVG(score) avgScore FROM student GROUP BY sex;

MAX()：只能用于数值类型的字段或者表达式，计算该满足条件的字段值的最大值

示例：从学生表查询学生的最大年龄

SELECT MAX(age) FROM student;

MIN()：只能用于数值类型的字段或者表达式，计算该满足条件的字段值的最小值

示例：从学生表查询学生的最低分

SELECT MIN(score) FROM student;

分组查询结果筛选

SELECT ALL/DISTINCT * | 字段名1 AS 别名1[,字段名1 AS 别名1, ..., 字段名n AS 别名n]

FROM 表名 WHERE 查询条件 GROUP BY 字段名1，字段名2,..., 字段名n HAVING 筛选条件

分组后如果还需要满足其他条件，则需要使用HAVING子句来完成。

示例：从学生表查询年龄在20~30之间的学生信息并按性别分组，找出组内平均分在74分以上的组

SELECT * FROM student WHERE age BETWEEN 20 AND 30 GROUP BY sex HAVING AVG(score)>74;

排序

SELECT ALL/DISTINCT * | 字段名1 AS 别名1[,字段名1 AS 别名1, ..., 字段名n AS 别名n]

FROM 表名 WHERE 查询条件 ORDER BY 字段名1 ASC|DESC，字段名2 ASC|DESC,..., 字段名n ASC|DESC

示例：从学生表查询年龄在18~30岁之间的学生信息并按成绩从高到低排列，如果成绩相同，则按年龄从小到大排列

SELECT * FROM student WHERE age BETWEEN 18 AND 30 ORDER BY score DESC, age ASC;

分页

SELECT ALL/DISTINCT * | 字段名1 AS 别名1[,字段名1 AS 别名1, ..., 字段名n AS 别名n]

FROM 表名 WHERE 查询条件 LIMIT 偏移量, 查询条数

LIMIT 的第一个参数表示偏移量，也就是跳过的行数。

LIMIT 的第二个参数表示查询返回的最大行数，可能没有给定的数量那么多行。

示例：从学生表分页查询成绩及格的学生信息，每页显示3条，查询第2页学生信息

SELECT * FROM student WHERE score>=60 LIMIT 3, 3;

注意： 如果一个查询中包含分组、排序和分页，那么它们之间必须按照分组 -> 排序 -> 分页的先后顺序排列。

常用函数

常用数学函数

函数	说明	示例
ABS(x)	返回x的绝对值	SELECT ABS(-8);
FLOOR(x)	返回不大于x的最大整数	SELECT FLOOR(1.3);
CEIL(x)	返回不小于x的最小整数	SELETC CEIL(1.3);
TRUNCATE(x,d)	返回数值x保留小数点后d位的值，截断时不进行四舍五入	SELECT TRUNCATE(1.2328,3);
ROUND(x)	返回离x最近的整数，截断时要进行四舍五入	SELECT ROUND(1.8);
RAND()	返回0~1的随机数	SELECT RAND();
MOD(n,m)	返回n除以m后的余数	SELECT MOD(9,2);

常用字符串函数

函数	说明	示例
CHAR_LENGTH(str)	计算字符串字符个数	SELECT CHAR_LENGTH('中国');
LENGTH(str)	返回值为字符串str的长度，单位为字节	SELECT LENGTH('中国');
CONCAT(str1,str2,...)	将多个字符串拼接起来，其中任意一个为NULL则返回值为NULL	SELECT CONCAT('ad','min');
UPPER(str) UCASE(str)	将字符串中的字母全部转换成大写	SELECT UPPER('abcde'); SELECT UCASE('abcde');
LOWER(str) LCASE(str)	将字符串中的字母全部转换成小写	SELECT LOWER('ABCDE'); SELECT LCASE('ABCDE');
LEFT(s,n) RIGHT(s,n)	前者返回字符串s从最左边开始的n个字符后者返回字符串s从最右边开始的n个字符	SELECT LEFT('abcdefg',5); SELECT RIGHT('abcdefg',5);
LTRIM(s) RTRIM(s)	前者返回字符串s，其左边的所有空格被删除后者返回字符串s，其右边的所有空格被删除	SELECT LTRIM(' abcde '); SELECT RTRIM(' abcde ');
TRIM(s)	返回字符串删除了两边空格后的字符串	SELECT TRIM(' abcde ');
REPLACE(s,s1,s2)	返回一个字符串，用字符串s2代替s中所有字符串s1	SELECT REPLACE('ababac','ab','d');
SUBSTRING(s,n,len)	从字符串s中返回第一个第n个字符开始长度为n的字符串	SELECT SUBSTRING('abcdef',2,3);

日期和时间函数

函数	说明	示例
CURDATE() CURRENT_DATE()	返回当前日期	SELECT CURDATE(); SELECT CURRENT_DATE();
CURTIME() CURRENT_TIME()	返回当前时间	SELECT CURTIME(); SELECT CURRENT_TIME();
NOW() CURRENT_TIMESTAMP() SYSDATE()	返回当前日期和时间	SELECT NOW(); SELECT CURRENT_TIMESTAMP(); SELECT SYSDATE();
YEAR(d)	返回日期d中的年	SELECT YEAR(NOW());
MONTH(d)	返回日期d中的月份值，范围1~12	SELECT MONTH(NOW());
DAYOFMONTH(d)	返回给定日期是当月的第几天	SELECT DAYOFMONTH(NOW());
HOUR(d)	返回给定日期d的小时数	SELECT HOUR(NOW());
MINUTE(d)	返回给定日期d的分钟数	SELECT MINUTE(NOW());
SECOND(d)	返回给定日期d的秒数	SELECT SECOND(NOW());
ADDDATE(d,n)	返回起始日期d加上n天的日期	SELECT ADDDATE(NOW(),3);
TIMESTAMPDIFF(INTERVAL,d1,d2)	返回给定日期d1和d2的时间差	SELECT TIMESTAMPDIFF(YEAR,'2019-10-10','2021-10-1');
DATE_FROMAT(d,f)	返回给定日期格式的字符串	SELECT DATE_FROMAT(NOW(),'%Y-%m-%d %H:%i:%s');

条件判断函数

IF(条件，表达式1，表达式2)

如果条件满足，则使用表达式 1, 否则使用表达式 2

IFNULL(字段，表达式)

如果字段值为空，则使用表达式，否则，使用字段值

CASE...WHEN

CASE WHEN 条件1 THEN 表达式1 [WHEN 条件2 THEN 表达式2 ...] ELSE 表达式n END

如果条件 1 满足，则使用表达式 1 ；【如果条件 2 满足，则使用表达式 2 ， ... 】否则，使用表达式 n 。相当于if..else

CASE 表达式 WHEN 值 1 THEN 表达式 1 [WHEN 值 2 THEN 表达式 2 ...] ELSE 表达式 n END

如果表达式的执行结果为值 1 ，则使用表达式 1 ；【执行结果为值 2 ，则使用表达式 2 ， ... 】否则，使用

表达式 n 。相当于switch

其他函数

数字格式化函数

FORMAT(X,D) ，将数字 X 格式化，将 X 保留到小数点后 D 位，截断时要进行四舍五入。

示例：SELECT FORMAT(1.2353,2);

系统信息函数

函数	说明	示例
VERSION()	获取数据库的版本号	SELECT VERSION();
CONNECTION_ID()	获取服务器的连接数	SELECT CONNECTION_ID();
DATABASE() SCHEMA()	获取当前数据库名	SELECT DATABASE(); SELECT SCHEMA();
USER() SYSTEM_USER() SESSION_USER()	获取当前用户名	SELECT USER(); SELECT SYSTEM_USER(); SELECT SESSION_USER();
CURRENT_USER() CURRENT_USER	获取当前用户名	SELECT CURRENT_USER(); SELECT CURRENT_USER;