数据模型
关系型数据库
定义
关系型数据库是建立在关系模型基础上的数据库,简单说,关系型数据库是由多张能相互连接的二维表组成的数据库
.frm
为表文件
.MYD
为数据文件
优点
- 都是使用表结构,格式一致,易于维护
- 使用通用的SQL语言操作,使用方便,可用于复杂查询
- 数据存储在磁盘中,安全(存储在内存中并不安全)
SQL简介
- 英文名:Structured Query Lanuage
- 结构化查询语言,一门操作关系型数据库的编程语言
- 定义操作所有关系型数据库的统一标准
- 对于同一个需求,每一种数据库操作的方式可能会存在一些不一样的地方,我们称为“方言”
SQL语法
1.SQL语句可以单行或多行书写,以分号结尾。
2.MySQL数据库的SQL语句不区分大小写,关键字建议使用大写
3.注释
直接ctrl
+/
即可
- 单行注释:
--
或#
(MySQL特有) - 多行注释:
/* 注释 */
SQL分类
- DDL(Data Definition Language)数据定义语言,用来定义数据库对象:数据库,表,列等
- DML(Data Manipulation Language)数据操作语言,用来对数据库中表的数据进行增删改
- DQL(Data Query Language)数据查询语言,用来查询数据库中表的记录(数据)
- DCL(Data Control Language)数据控制语言,用来定义数据库 的访问权限和安全级别,及创建用户
DDL
操作数据库
1.查询
SHOW DATABASES
结果如下
mysql> show Databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| k |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.02 sec)
k
是我自己创建的表
information_schema
是一种特殊的表,叫做视图,是逻辑表,所以在文件夹中找不到这个文件
mysql
存储的是数据库最为核心的一些信息,权限,安全等
performance_schema
存储的是性能相关的信息
sys
存储的是关于系统的一些的信息
2创建数据库
create database db1;
create database if not exists db1;
两者等价但第二个有判断条件
3.删除数据库
drop database db1;
drop database if exists db1;
两者等价但第二个有判断条件
4.使用数据库
- 查看当前使用的数据库
SELECT DATABASE();
- 使用数据库
USE db1;
操作表
1.查询表
- 查询当前数据库下所有表名称
SHOW TABLES;
- 查询表结构(不是内容)
DESC 表名称;
查询结构的结果
+-------+------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------------------+------+-----+---------+-------+
| name | char(64) | NO | PRI | | |
| ret | tinyint | NO | | 0 | |
| dl | char(128) | NO | | | |
| type | enum('function','aggregate') | NO | | NULL | |
+-------+------------------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
2.创建表
CREATE TABLE 表名 (
字段1 数据类型1,
字段2 数据类型2,
···
字段n 数据类型n
);
最后一行末尾不能加逗号
数据类型
MySQL 中定义数据字段的类型对你数据库的优化是非常重要的。
MySQL 支持多种类型,大致可以分为三类:数值、日期/时间和字符串(字符)类型。
MySQL 支持所有标准 SQL 数值数据类型。
这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL 和 NUMERIC),以及近似数值数据类型(FLOAT、REAL 和 DOUBLE PRECISION)。
关键字INT是INTEGER的同义词,关键字DEC是DECIMAL的同义词。
BIT数据类型保存位字段值,并且支持 MyISAM、MEMORY、InnoDB 和 BDB表。
作为 SQL 标准的扩展,MySQL 也支持整数类型 TINYINT、MEDIUMINT 和 BIGINT。下面的表显示了需要的每个整数类型的存储和范围。
类型 | 大小 | 范围(有符号) | 范围(无符号) | 用途 |
---|---|---|---|---|
TINYINT | 1 Bytes | (-128,127) | (0,255) | 小整数值 |
SMALLINT | 2 Bytes | (-32 768,32 767) | (0,65 535) | 大整数值 |
MEDIUMINT | 3 Bytes | (-8 388 608,8 388 607) | (0,16 777 215) | 大整数值 |
INT或INTEGER | 4 Bytes | (-2 147 483 648,2 147 483 647) | (0,4 294 967 295) | 大整数值 |
BIGINT | 8 Bytes | (-9,223,372,036,854,775,808,9 223 372 036 854 775 807) | (0,18 446 744 073 709 551 615) | 极大整数值 |
FLOAT | 4 Bytes | (-3.402 823 466 E+38,-1.175 494 351 E-38),0,(1.175 494 351 E-38,3.402 823 466 351 E+38) | 0,(1.175 494 351 E-38,3.402 823 466 E+38) | 单精度 浮点数值 |
DOUBLE | 8 Bytes | (-1.797 693 134 862 315 7 E+308,-2.225 073 858 507 201 4 E-308),0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) | 0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) | 双精度 浮点数值 |
DECIMAL | 对DECIMAL(M,D) ,如果M>D,为M+2否则为D+2 | 依赖于M和D的值 | 依赖于M和D的值 | 小数值 |
- 注意
字段名 double(总长度,小数点后保留的位数)
比如定义0到100,小数点后保留两位score double(5,2)
日期和时间类型
表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。
每个时间类型有一个有效值范围和一个"零"值,当指定不合法的MySQL不能表示的值时使用"零"值。
TIMESTAMP类型有专有的自动更新特性,将在后面描述。
类型 | 大小 ( bytes) | 范围 | 格式 | 用途 |
---|---|---|---|---|
DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
TIME | 3 | ‘-838:59:59’/‘838:59:59’ | HH:MM:SS | 时间值或持续时间 |
YEAR | 1 | 1901/2155 | YYYY | 年份值 |
DATETIME | 8 | ‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’ | YYYY-MM-DD hh:mm:ss | 混合日期和时间值 |
TIMESTAMP | 4 | ‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-19 03:14:07’ UTC 结束时间是第 2147483647 秒,北京时间 2038-1-19 11:14:07,格林尼治时间 2038年1月19日 凌晨 03:14:07 | YYYY-MM-DD hh:mm:ss | 混合日期和时间值,时间戳 |
字符串类型
字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。
类型 | 大小 | 用途 |
---|---|---|
CHAR | 0-255 bytes | 定长字符串 |
VARCHAR | 0-65535 bytes | 变长字符串 |
TINYBLOB | 0-255 bytes | 不超过 255 个字符的二进制字符串 |
TINYTEXT | 0-255 bytes | 短文本字符串 |
BLOB | 0-65 535 bytes | 二进制形式的长文本数据 |
TEXT | 0-65 535 bytes | 长文本数据 |
MEDIUMBLOB | 0-16 777 215 bytes | 二进制形式的中等长度文本数据 |
MEDIUMTEXT | 0-16 777 215 bytes | 中等长度文本数据 |
LONGBLOB | 0-4 294 967 295 bytes | 二进制形式的极大文本数据 |
LONGTEXT | 0-4 294 967 295 bytes | 极大文本数据 |
注意:char(n) 和 varchar(n) 中括号中 n 代表字符的个数,并不代表字节个数,比如 CHAR(30) 就可以存储 30 个字符。
VARCHAR是一种数据类型,比CHAR更加灵活,VARCHAR可以保存可变长度的字符串,而CHAR不能。就是说char(10)所占的空间就是10个字符,而varchar(10)假如存储了两个字符,存储空间就是两个字符。VARCHAR和CHAR同样用于表示字符数据。其中M代表该数据类型所允许保存的字符串的最大长度,只要长度小于该最大值的字符串都可以被保存在该数据类型中。所以,对于那些难以估计确切长度的数据对象来说,使用VARCHAR数据类型更加明智。
BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR,不同的是它们包含二进制字符串而不要非二进制字符串。也就是说,它们包含字节字符串而不是字符字符串。这说明它们没有字符集,并且排序和比较基于列值字节的数值值。
BLOB 是一个二进制大对象,可以容纳可变数量的数据。有 4 种 BLOB 类型:TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。
有 4 种 TEXT 类型:TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型,可存储的最大长度不同,可根据实际情况选择。
3.修改表
- 修改表名(TABLE不能忘)
ALTER TABLE 表名 RENAME TO 新的表名;
- 添加一列
ALTER TABLE 表名 ADD 列名 数据类型;
- 修改数据类型
ALTER TABLE 表名 MODIFY 列名 新数据类型;
- 修改列和数据类型
ALTER TABLE 表名 CHANGE 列名 新数据类型;
- 删除列
ALTER TABLE 表名 DROP 列名 ;
4.删除表
DROP TABLE 表名;
DROP TABLE IF EXISTS 表名;
DML
添加数据(insert)
-- 查询所有数据
SELECT * FROM r;
-- 给指定列添加数据
INSERT INTO r(name,num) VALUES('张三',123);
-- 给所有列添加数据
INSERT INTO r(num,name,gender,birthday,score,email,tel,adrd) VALUES(123,'张三','男','2022-1-3',99,'djflks.cn@','123213232','曹县');
-- 省略写法但不建议
INSERT INTO r VALUES(123,'张三','男','2022-1-3',99,'djflks.cn@','123213232','曹县');
-- 批量添加数据
INSERT INTO r(num,name,gender,birthday,score,email,tel,adrd) VALUES(123,'张三','男','2022-1-3',99,'djflks.cn@','123213232','曹县'),(123,'张三','男','2022-1-3',99,'djflks.cn@','123213232','曹县'),(123,'张三','男','2022-1-3',99,'djflks.cn@','123213232','曹县');
修改数据(update)
UPDATE 表名 SET 列名1=值1,列名2=值2,··· [WHERE 条件];
- 注意:修改语句中如果不加条件,则将所有数据都修改
删除数据
DELETE FROM 表名 [WHERE 条件];
- 注意:删除语句中如果不加条件,则将所有数据都删除
DQL
聚合函数
1.概念
将一列数据作为一个整体,进行纵向运算
2.分类
函数名 | 功能 |
---|---|
count(列名) | 统计数量(一般选用不为null的列,会少数) |
max(列名) | 最大值 |
min(列名) | 最小值 |
sum(列名) | 求和 |
avg(列名) | 平均值 |
注意: count() 后面的值最好是主键(因为主键值不能为null)或* | |
3.语法 |
SELECT 聚合函数名(列名) FROM 表;
注意:null值不参加所有聚合函数运算
查询语法
基础查询
-- 基础查询
SELECT * FROM students;
-- * 最好不用,不好加注释
-- 查询两列
SELECT `name` FROM students;
-- 去除重复记录
SELECT DISTINCT region from students;
-- 起别名 AS可以省略但要有空格
SELECT region AS 地区 , age AS 年龄 FROM students;
条件查询(where)
-- 查询大于17岁的学生
SELECT * FROM students WHERE age > 17;
-- 查询大于等于17岁的学生
SELECT * FROM students WHERE age >= 17;
-- 查询大于等于17岁且小于18岁的学生
SELECT name FROM students WHERE age >= 17 && age < 18;
SELECT name FROM students WHERE age >= 17 AND age < 18;
SELECT name FROM students WHERE age BETWEEN 20 AND 30;
-- 查询生日在2004年间的学生
SELECT * FROM students where birthday BETWEEN '2004-01-01'AND '2004-12-31';
-- 查询年龄不等于17岁的同学
SELECT name, age FROM students where age != 17;
SELECT name, age from students WHERE age <> 17;
-- 查询年龄等于18或17或16的学生
SELECT name,age from students where age = 17 or age = 18 or age = 16;
SELECT name,age from students where age = 17 || age = 18 || age = 16;
-- 查询地区为null的学生(比较null值只能用is或is not)
SELECT name,age from students where region is NULL;
SELECT name,age from students where region is not NULL;
分组查询(group by)
1.语法
SELECT 字段列表 FROM [WHERE 分组前条件限定] GROUP BY 分组字段名 [HAVING 分组后条件过滤];
注意: 分组之后,查询的字段为聚合函数和分组字段,查询其他字段无任何意义
where和having的区别:
执行时机不一样:where是分组前进行限定,having是分组之后进行过滤
可判断的条件不一样:where不能对聚合函数进行判断,having可以。
执行顺序: where > 聚合函数 > having
-- 查询男女各自的数学平均分,以及各自人数,要求分数低于80的不参与分组,分组之后人数大于2人
select avg(math),gender,count(*) from students where math> 80 group by gender having count(*)>2;
排序查询(order by)
order by 如果和where同时使用,order by必须在where 的后面 ASC
升序排列是默认值
SELECT 字段列表 FROM 表名 ORDER BY 排序字段1 [排序方式1],排序字段2 [排序方式2]…;
mysql> -- 按照年龄升序排列
mysql> select name,age from students order by age asc;
+------+------+
| name | age |
+------+------+
| 李四 | 15 |
| 赵六 | 15 |
| 王五 | 16 |
| 陈八 | 16 |
| 王五 | 16 |
| 陈八 | 16 |
| 孙七 | 17 |
| 李四 | 17 |
| 孙七 | 17 |
| 张三 | 18 |
| 张三 | 18 |
| 赵六 | 18 |
+------+------+
12 rows in set (0.00 sec)
mysql> -- 按照年龄降序排列
mysql> select name,age from students order by age desc;
+------+------+
| name | age |
+------+------+
| 张三 | 18 |
| 张三 | 18 |
| 赵六 | 18 |
| 孙七 | 17 |
| 李四 | 17 |
| 孙七 | 17 |
| 王五 | 16 |
| 陈八 | 16 |
| 王五 | 16 |
| 陈八 | 16 |
| 李四 | 15 |
| 赵六 | 15 |
+------+------+
12 rows in set (0.00 sec)
mysql> -- 根据年龄降序排列,如果年龄一样,按照英语成绩升序排列
mysql> -- 如果有多个排序条件,当前边的条件值一样时,才会根据第二条件进行排序
mysql> select name,age,english_score from students order by age desc, english_score asc;
+------+------+---------------+
| name | age | english_score |
+------+------+---------------+
| 张三 | 18 | 85.50 |
| 张三 | 18 | 85.50 |
| 赵六 | 18 | 91.20 |
| 孙七 | 17 | 83.40 |
| 孙七 | 17 | 83.40 |
| 李四 | 17 | 92.30 |
| 陈八 | 16 | 77.80 |
| 陈八 | 16 | 77.80 |
| 王五 | 16 | 78.90 |
| 王五 | 16 | 78.90 |
| 赵六 | 15 | 91.20 |
| 李四 | 15 | 92.30 |
+------+------+---------------+
12 rows in set (0.00 sec)
分页查询(limit)
1.语法
SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询条目数;
- 起始索引:从零开始
- 计算公式:起始索引=(当前页码 - 1) * 每页显示的条数
- 分页查询limit是MySQL数据库的方言
- Oracle 分页查询使用 rownumber
- SQL Server分页查询使用top
mysql> select * from students limit 0, 3;
+----+------+------+--------+--------+-------+---------------+------------+
| id | name | age | gender | region | math | chinese_score | birthday |
+----+------+------+--------+--------+-------+---------------+------------+
| 1 | 张三 | 18 | 男 | 北京 | 85.50 | 79.20 | 2003-05-12 |
| 2 | 李四 | 15 | 女 | NULL | 80.00 | 88.70 | 2004-08-25 |
| 3 | 王五 | 16 | 男 | 广州 | 78.90 | 81.40 | 2005-11-07 |
+----+------+------+--------+--------+-------+---------------+------------+
3 rows in set (0.00 sec)
mysql> select * from students limit 4, 3;
+----+------+------+--------+--------+-------+---------------+------------+
| id | name | age | gender | region | math | chinese_score | birthday |
+----+------+------+--------+--------+-------+---------------+------------+
| 5 | 孙七 | 17 | 男 | 山东 | 83.40 | 90.10 | 2004-06-08 |
| 6 | 陈八 | 16 | 女 | NULL | 77.80 | 84.70 | 2005-11-30 |
| 7 | 张三 | 18 | 男 | 北京 | 85.50 | 79.20 | 2003-05-12 |
+----+------+------+--------+--------+-------+---------------+------------+
3 rows in set (0.00 sec)