MySQL

一、数据库初识

数据库

数据库（简称DB）

存储在计算机磁盘上的有组织，可共享的大量数据的集合。

数据库的应用

文件处理系统的弊端

数据库类型

关系型（数据是结构化的）：MySQL、Oracle、SQL、Server 、 SQLite 、DB2等

非关系型：Redis、MongoDB等

数据库管理系统（DBMS）

DBMS：数据库+访问数据的程序。

主要用于科学组织和存储数据、高效获取和维护数据。

数据抽象

• 物理层
• 逻辑层
• 视图层

实例与模式

数据模型

关系型模型

 

MySQL

MySQL 是目前最流行的开源的、免费的关系型数据库，适用于中小型甚至大型互联网应用，能够在 windows 和 linux 平台上部署。

结构化查询语言

SQL分类

• 结构化查询语句，英文名称为Structured Query Language，简称SQL。

名称	描述	命令
数据定义语言 （DDL）	数据库、数据表的创建、修改和删除	CREATE、ALTER、DROP （create、alter、drop）
数据操作语言 （DML）	数据的增加、修改和删除	INSERT、UPDATE、DELETE （insert、update、delete）
数据查询语言 （DQL）	数据的查询	SELECT（select）
数据控制语言 （DCL）	用户授权、事务的提交和回滚	GRANT、COMMIT、 ROLLBACK （grant授予、commit、rollback）

 

 

 

 

 

 

 

 

数据库操作：增删查改、使用

数据库的创建

//创建数据库XXX(如果不存在)，使用默认字符集XXX、排序规则XXX
CREATE DATEBASE [IF NOT EXISTS] 数据库的名称 DEFAULT CHARACTER SET 字符集 COLLATE 排序规则;

字符集：决定了如何存储字符和如何解释字符

•         GBK：简体中文字符集。
•         UTF-8：MySQL中常用的字符集之一。
•         latin1：MySQL默认字符集。

排序规则：字符集中定义的规则。字符的比较方式、大小写是否区分、特殊字符的处理等

数据库的删除

//删除XXX数据库（如果存在）
DROP DATABASE [IF EXISTS] 数据库名称;

数据库的查看

SHOW DATABASES;

数据库的修改

//修改XXX数据库的字符集为XXX，排序为XXX
ALTER DATABASE 数据库名称 CHARACTER SET 字符集 COLLATE 排序规则;

数据库的使用

USE 数据库名称;

 练习

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

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

//删除数据库lesson

列类型

• 数值类型

        小数位：d 位 整数位：m-d 位

• 日期时间类型

• 字符串类型

列类型修饰属性

数据表类型：

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

MyISAM与InnoDB的区别：

• 事务：涉及的所有操作是一个集体，要么执行，要么不执行。
• 数据行锁定：一行数据，当一个用户在修改改数据时，可以直接将该条数据锁定。

当涉及的业务以查询居多，修改和删除较少时，可以使用MyISAM。

当涉及的业务经常会有修改和删除操作时，使用InnoDB。

数据表操作

创建数据表

CREAT TABLE [IF NOT EXISTS] 数据表名称(
    字段名1 列类型 [修饰属性] [键/索引] [注释],
    字段名2 列类型 [修饰属性] [键/索引] [注释],
    ……………………
) [ENGING = 数据类型] [CHARSET = 字符集编码] [COMMENT = 注释]；

查看表结构

DESC 表名;

修改数据表

• 修改表名

ALTER TABLE 表名 RENAME AS 新表名;

• 修改字段

//MODIFY 只能修改字段的修饰属性
ALTER TABLE 表名 MODIFY 字段名 列类型(长度) [修饰属性] [键/索引] [注释];
// CHANGE 可以修改字段的名字以及修饰属性
ALTER TABLE 表名 CHANGE 字段名 新字段名 列类型(长度) [修饰属性] [键/索引] [注释];

• 增加字段

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

• 删除字段

ALTER TABLE 表名 DROP 字段名;

删除数据表

DROP TABLE [IF EXISTS] 表名;

练习 

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

//将student表名称修改为stu

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

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

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

//将stu表中的 mobile 字段删除

//删除数据表stu

二、MySQL数据的增删改查

DML语句

DML

DML全称为Data Manipulation Language，表示数据操作语言。主要体现于对表数据的增删改操作。因此DML仅包括INSERT、UPDATE和DELEETE语句。

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

UPDATE语句

        WHERE条件子句：AND <=> &&  OR <=> ||

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

DELEETE语句

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

TRUNCATE语句 (truncate,缩短)

//清空表中数据
TRUNCATE [TABLE] 表名;

DELEETE与TRUNCATE的区别

• DELETE语句根据条件删除表中数据，而TRUNCATE语句则是将表中数据全部清空；
• 如果DELETE语句要删除表中所有数据，那么在效率上要低于TRUNCATE语句。
• 如果表中有自增长列，TRUNCATE语句会重置自增长的计数器，但DELETE语句不会。
• TRUNCATE语句执行后，数据无法恢复，而DELETE语句执行后，可以使用事务回滚进行恢复。

自增长:

练习

//创建课程表并插入数据
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);

//将数据库的学分更改为4，学时更改为15

//删除课程表中课程编号为1的数据

//清空课程表数据

DQL语句

DQL

DQL全称是Data Query Language，表示数据查询语言。体现在数据的查询操作上，因此，DQL仅包括 SELECT语句。

SELECT语句

//ALL表示查询所有满足条件的记录，可以省略；DISTINCT表示去掉查询结果中重复的记录
//AS可以给数据列、数据表取一个别名
SELECT [ ALL/DISTINCT ]  字段名1 AS 别名1[,字段名1 AS 别名1, ..., 字段名n AS 别名n] 
FROM 表名 WHERE 查询条件

比较操作符

分组

分组查询

SELECT ALL/DISTINCT * | 字段名1 AS 别名1[,字段名1 AS 别名1, ..., 字段名n AS 别名n] 
FROM 表名 WHERE 查询条件 GROUP BY 字段名1，字段名2,..., 字段名n

分组查询所得结果只是改组的第一个数据

聚合函数

• COUNT() 统计满足条件的数据的数量
• SUM() 求满足条件的字段值的和，只能用于数值类型的字段或者表达式
• AVG() 求平均值，只能用于数值类型的字段或者表达式
• MAX() 计算该满足条件的字段值的最大值，只能用于数值类型的字段或者表达式
• MIN() 计算该满足条件的字段值的最小值，只能用于数值类型的字段或者表达式

分组查询结果筛选

SELECT ALL/DISTINCT * | 字段名1 AS 别名1[,字段名1 AS 别名1, ..., 字段名n AS 别名n] 
FROM 表名 WHERE 查询条件 GROUP BY 字段名1，字段名2,..., 字段名n HAVING 筛选条件

排序

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

ORDER BY 必须位于WHERE 条件之后 

分页

SELECT ALL/DISTINCT * | 字段名1 AS 别名1[,字段名1 AS 别名1, ..., 字段名n AS 别名n] 
FROM 表名 WHERE 查询条件 LIMIT 偏移量, 查询条数

LIMIT的第一个参数表示偏移量，也就是跳过的行数。 LIMIT的第二个参数表示查询返回的最大行数，可能没有给定的数量那么多行。

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

三、常用函数

数学函数

字符串函数

日期和时间函数

条件判断函数

系统信息函数