一、数据库初识
数据库
数据库(简称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的第二个参数表示查询返回的最大行数,可能没有给定的数量那么多行。
如果一个查询中包含分组、排序和分页,那么它们之间必须按照分组->排序->分页的先后顺序排列。
三、常用函数
数学函数
字符串函数
日期和时间函数
条件判断函数
系统信息函数