Mysql学习笔记

连接数据库

找到 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);