关系型数据库
关系型数据库模型是把复杂的数据结构归结为简单的二元关系(即二维表格形式)。在关系数据库中,对数据的操作几乎全部建立在一个或多个关系表格上,通过对这些关联表的表格分类、合并、连接或选取等运算来实现数据的管理。
关系就是数据能够对应的匹配,在关系型数据库中正式名称叫联结,对应的英文名称叫做 join。
SQL(Structured Query Language)结构化查询语言,用于从数据库中有结构的查询。
NoSQL(非关系型数据库)
NoSQL 仅仅是一个概念,泛指非关系型的数据库,区别于关系数据库,它们不保证关系数据的 ACID 特性,运用非关系型的数据存储。
数据库和表
表名
表名就是表格名,在MySQL中一般使用英文小写 字母来约定表名。
字段
在数据库表中,每一列都是一个字段,第一行是字段名,
字段必须是唯一的,即不能出现同名的字段。
主键
每一张数据库表都可以有一个主键(primary key),主键最大的作用就是用来标识数据。
主键拥有以下的特点:
- 主键是一个特殊字段
- 表格可以没有主键,但是最多只能拥有一个主键
- 主键的值不能为
NULL
,必须有对应的值 - 主键的值必须是绝对唯一的,即不能出现两个相同的主键值,比如名字就不能作为主键,因为会出现重名的情况。
- 我们一般使用主键和其他表进行关联
SQL常用数据类型
类型 | 含义 |
---|---|
VARCHAR | 可变的长字符串,可以类比于Java中的String类型 |
INT | 整型,和Java中的int类型一致 |
DOUBLE | 浮点型,和Java中的double类型一致,一般不加长度限制 |
DATETIME | 时间类型,长度为0,格式为YYYY-MM-DD HH:MM:SS,值为2019-12-31 23:59:59 |
BIGINT | 长整形,和Java中的long类型一致 |
数据
表格中的一行称为一条数据,数据库的一条数据就像是value,而主键就像是它的key,我们通过主键来快速查找数据,也可以通过其他字段来操作数据。
CRUD
创建(Create)、读取(Read)、更新(Update)、删除(Delete),也就是CRUD;
英文 | 中文 | SQL | HTTP |
---|---|---|---|
CREATE | 创建 | INSERT(插入) | POST |
READ | 读取 | SELECT(查询) | GET |
UPDATE | 更新 | UPDATE | POST |
DELETE | 删除 | DELETE | DELETE |
创建表格
CREATE TABLE `user`(
`id` INT(10)NOT NULL,
`mobile` VARCHAR(11) NOT NULL,
`nickname` VARCHAR(40) NOT NULL,
`gmt_created` datetime ,
`gmt_modified` datetime NOT NULL,
PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
在创建表格时我们需要提供以下的属性:
- 表名
- 字段名
- 字段的数据类型
创建一张叫做user
的表格
CREATE TABLE `user`()
字段名+数据类型+长度+是否为 NULL,
`id` INT(10)NOT NULL,
约定主键
PRIMARY KEY ( `id` )
主键拥有以下特点:
- 主键必须是已经约定的字段
- 主键不能为空
- 主键的值不能重复
- 主键最大的作用是标识,所以它最好是由计算机生成,人工不干预主键生成后的值
`id` INT UNSIGNED AUTO_INCREMENT
id 会从 1 开始自增,第二个为 2,第三个为 3,依此类推。
UNSIGNED是指无符号的,也就是说排除了负数
设置储存引擎和编码方式
ENGINE=InnoDB DEFAULT CHARSET=utf8
储存引擎为 InnoDB,默认编码方式为 utf-8。
InnoDB 是 MySQL 的默认储存引擎,utf-8 是一种编码方式
符号
-
所有的符号都是英文输入法输入的
-
`` 这个符号叫作 反引号 ,是用来过滤数据库关键字
删除表格
drop table table_name;
DROP TABLE IF EXISTS table_name;
table_name 是指你要删除的表格名 IF EXISTS 的意思就是如果存在 删除表格是不可逆的
语法
插入数据
使用 INSERT INTO SQL 语句来插入数据
INSERT INTO table_name(field1,field2,...fieldN)
VALUES
(value1,value2,...valueN);
- gmt_created是datetime类型,我们一般使用now()这个函数来获取服务器当前时间,并将它作为值插入
插入语句的简化
- 如果主键设置为自增,则可以不插入主键和对应的数据
- 如果插入的是所有的字段,可以省略字段名,直接插入值,但是类型必须全部一致,比如:
INSERT INTO table_name
VALUES
(value1,value2,...valueN);
这两种情况不可以同时简化。
批量插入数据
INSERT INTO table_name
VALUES
(value1,value2,...valueN),
(value1,value2,...valueN);
查询数据
SELECT field1,field2,.... FROM table_name;
SELECT
id,
hero_name
FROM
timi_adc;
如果查询所有字段可以
SELECT * FROM timi_adc;
WHERE子句
SELECT * FROM table_name WHERE condition;
condition 是指条件,它和if语句一样,可以做简单的逻辑判断,我们查询到的数据是true
SELECT
*
FROM
timi_adc
where
win_rate > 0.5;
Limit子句
强化查询功能
SELECT * FROM table_name LIMIT parameter;
x-y行
SELECT
*
FROM
timi_adc
LIMIT
5, 6;
第一个参数5表示从第6行开始查,第二个参数6,表示一共查询6条记录。
数据库的表格类似数组,是从第0行开始数的
0-x行
SELECT
*
FROM
timi_adc
LIMIT
5;
这句SQL的意思是查询user表的第0-5行,也就是说这句话等价于:
SELECT * FROM timi_adc LIMIT 0,5;
查询第x行
SELECT
*
FROM
timi_adc
LIMIT
4, 1;
和WHERE子句联合使用
SELECT
*
FROM
timi_adc
WHERE
appearance_rate > 0.1
LIMIT
5;
排序
对查询的结果进行排序,使用 ORDER BY 子句。
SELECT * FROM table_name ORDER BY field_name;
SELECT
*
FROM
timi_adc
ORDER BY
win_rate;
排序默认按照升序排序,对于int,double而言,是从小到大,对于varchar而言,是从字母A到Z,对于datetime而言,是从过去到现在。
DESC关键词
ORDER BY 语句的排序默认是正序排序,关键词为ASC(一般不写),我们可以通过加上关键词DESC,使得排序变为逆序。
SELECT
*
FROM
timi_adc
ORDER BY
win_rate DESC;
更新/删除
UPDATE(更新)
UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
UPDATE语句我们必须加入WHERE限定条件,否则的话UPDATE语句就会对整列起作用。
DELETE(删除)
DELETE FROM table_name [WHERE Clause]
由于删除语句是不可恢复的,所以我们务必要增加WHERE语句,否则将会删除整张表格的数据。
DELETE 语句只删除表中的数据,而不用于删除表格。
LIKE查询
相较于的模糊查询
SELECT * FROM table_name WHERE condition LIKE condition;
%
SQL LIKE 子句中,我们的使用百分号 %字符来表示任意字符,如果我们没有使用任何的%,那此时LIKE就相当于=
SELECT
*
FROM
timi_adc
WHERE
hero_name LIKE '%孙%';
%孙%'示这个字符串含孙,'孙%'表示这个字符串以孙开头,'%孙'表示这个字符串以孙结尾。
SELECT
*
FROM
timi_adc
WHERE
hero_name LIKE '_尚香';
_尚香和%尚香的区别在于,假如有一个人叫公孙尚香,前者查询不到,而后者可以。
AND&OR
多个条件的使用时,可以使用AND和OR关键词。
SELECT * FROM table_name WHERE conditionA AND/OR condtionB;
IN/NOT 子句
IN子句需要配合WHERE子句使用,它是一种精准查询。
SELECT * FROM table_name WHERE column IN (condtionA,condtionB);
SELECT
*
FROM
timi_adc
WHERE
fever IN ('T0', 'T3');
表示 表中热度为T0和T3。等价于
SELECT
*
FROM
timi_adc
WHERE
fever = 'T0'
OR fever = 'T3';
NOT IN/NOT LIKE
NOT IN 子句就是在IN前面加一个NOT,NOT就是非的意思,相当于否定这个条件
T0以外:
SELECT
*
FROM
timi_adc
WHERE
fever NOT IN ('T0');
即使只有一个TO,外面的括号也不能省略
NOT LIKE子句就是在LIKE子句前面加一个NOT,和 NOT IN的用法很相似,查询所有名字里面不带孙的英雄。
SELECT
*
FROM
timi_adc
WHERE
hero_name NOT LIKE '%孙%';
查询得到的结果是除了孙尚香和公孙离外的所有射手
NULL值的处理
当提供的查询条件字段为null时,可能无法正常读取。
NULL是 MySQL 字段的数据类型的属性,本身不属于任何数据类型,NULL
值也不等于空值,空值是不占用存储空间的,NULL
占用存储空间。
如何处理NULL值
MySQL
为处理 NULL 值提供了三种运算符:
- IS NULL:当列的值是 NULL 时,这个运算符返回 true
- IS NOT NULL:当列的值不为 NULL,这个运算符返回 true
- <=>:比较操作符,当比较的两个值都为 NULL 时或者相等时,返回为 true
我们不能使用=NULL 或者!=NULL 来查找 NULL 值 NULL = NULL 会返回 NULL。
SELECT field_name1,field_name2
FROM table_name
WHERE field_name2 IS NOT NULL/IS NULL;
字符串的处理(CONCAT函数)
SELECT column_name1,CONCAT(column_name2,str,column_name3),column_name4 FROM table_name;
- 首先这是一个查询语句,最基本的结构是 SELECT 列名 FROM 表名
- CONCAT函数可以拼接列名,也可以拼接字符串
- 在使用CONCAT函数的时候可以同时查询其他的列
- CONCAT函数的参数之间用英文
,
分隔
SELECT
id,
concat(hero_name, '的胜率是', win_rate)
FROM
timi_adc;
,如果拼接的值中有NULL,则结果一律为NULL,如果拼接的值中有NULL,则结果一律为NULL
配合WHERE语句查询
SELECT
concat(hero_name, '的胜率是', win_rate)
FROM
timi_adc
WHERE
id = 3;
别名
优化拼接后的结果另起一个名字
SELECT
concat(hero_name, '的胜率是', win_rate) as result
FROM
timi_adc
WHERE
id = 3;
整理输入的数据(TRIM函数)
TRIM函数可以清理数据中的空格
TRIM (str)
SELECT
trim(hero_name),
trim(fever)
FROM
timi_adc
WHERE
id = 20;
trim()函数也可以精准的去掉前面或者后面的空格,或者其他的字符。
TRIM( BOTH|LEADING|TRAILING removed_str FROM str);
SELECT
TRIM(
TRAILING 'Q'
FROM
fever
)
FROM
timi_adc
WHERE
id = 21;
- TRIM函数可以加上LEADING来只除去前面的空格,或者加上TRAILING来只除去后面的空格,如果都不加,则默认是BOTH。
- TRIM函数可以删除指定的字符串内容,如果不加,则默认删除空格。
REPLACE函数
TRIM()函数不能去掉字符串中间的值,如果要修改中间的值,我们可以使用REPLACE()函数。
UPDATE table_name
SET colunm_name =
REPLACE(column_name,string_find,string_to_replace)
WHERE conditions;
关联查询(多表数据记录关联查询)
外连接
左连接
SELECT
*
FROM
TableA LEFT JOIN
TableB
ON condition;
- JOIN 是关联查询的关键词,基础的结构是 TableA JOIN TableB,即表 A 和表 B 关联查询,LEFT 表示是左连接
- ON 是关联查询的条件,接下来我们会讲到
- 左连接就是返回左表的所有数据,即使右表没有匹配的数据(此时右表会以 NULL 的形式匹配数据)。
SELECT
*
FROM
_teacher
LEFT JOIN _course ON _teacher.id = _course.teacher_id;
右连接
SELECT
*
FROM
TableA RIGHT JOIN
TableB
ON condition;
多表关联查询
多张表
查询时,往往以某一张表为基准,进行LEFT JOIN或RIGHT JOIN 查询。
SELECT
*
FROM
TableA
LEFT JOIN TableB ON conditionA
LEFT JOIN TableC ON conditionB;
内连接
INNER连接
两个表格A和B,通过A和B同时满足某个条件,得到某些数据是内部共有的数据,这样的连接方式称之为内连接。
SELECT
*
FROM
table_a
INNER JOIN table_b
ON
table_a.id = table_b.student_id;
UNION关键字
联合查询所有内容
SELECT
*
FROM
table_a
LEFT JOIN table_b
ON
table_a.id = table_b.student_id
UNION DISTINCT
SELECT
*
FROM
table_a
RIGHT JOIN table_b ON table_a.id=table_b.student_id;
UNION关键字可以将两个查询 语句的结果合并,并去除重复数据。
表格
用户表
备注 | SQL字段的 | 字段类型 |
---|---|---|
主键id | id | VARCHAR(40) |
记录创建时间 | gmt_created | DATETIME(0) |
记录修改时间 | gmt_modified | DATETIME(0) |
登录名 | login_name | VARCHAR(20) |
登录密码 | password | VARCHAR(50) |
手机号 | mobile | VARCHAR(20) |
邮箱 | VARCHAR(50) | |
姓名 | name | VARCHAR(50) |
性别 | gender | VARCHAR(10) |
在MySQL中我们使用驼峰命名法,比如gmt_created,而在Java中我们使用小驼峰命名法,所以相应的属性写为gmtCreated,就是把- 去掉并把后面单词首字母大写。 |
问卷表
备注 | SQL字段的 | 字段类型 |
---|---|---|
主键id | id | VARCHAR(40) |
记录创建时间 | gmt_created | DATETIME(0) |
记录修改时间 | gmt_modified | DATETIME(0) |
问卷标题 | title | VARCHAR(40) |
详细描述,大字段,支持富文本格式 | description | TEXT |
状态:init/inuse/close | status | VARCHAR(20) |
问卷内容 | content | TEXT |
问卷作者,关联User.id | author | VARCHAR(40) |
问卷表用来记录问卷,一个问题是一条数据。问卷的内容我们一般使用json格式,利用fastjson等库转换成字符串后插入到数据库中。 |
回复表
备注 | SQL字段的 | 字段类型 |
---|---|---|
主键id | id | VARCHAR(40) |
记录创建时间 | gmt_created | DATETIME(0) |
记录修改时间 | gmt_modified | DATETIME(0) |
问卷id,关联Questionnaire.id | ques_id | VARCHAR(40) |
问卷的问题的序号 | ques_index | INT(20) |
回复人id,关联User.id | user_id | VARCHAR(40) |
回复内容 | content | TEXT |