数据库基础

数据库被分为关系型数据库和非关系型数据库

使用Mysql是基于关系型数据库

而SQL全名 Structured Query Language(结构化查询语言)本质上是一种语言，MySQL才是数据库本身。

表结构设计规范

互联网公司在使用 MySQL 在设计表结构时，需要遵从以下的规范：

  1. 表必须要有主键。
  2. 一个字段只表示一个含义。
  3. 总是包含两个日期字段:gmt_created(创建日期),gmt_modified(修改日期)，且这两个字段不应该包含有额外的业务逻辑。
  4. MySQL 中，gmt_created、gmt_modified 使用DATETIME 类型。
  5. 禁止使用复杂数据类型（数组，自定义类型等）。
  6. 禁止使用物理外键，使用逻辑外键
  7. 禁止物理删除，使用逻辑删除 is_deleted

逻辑删除（英语：logical deletion），又被称软删除、假删除，是一种数据库操作，使用标记将数据标为不可用，而不从数据库删除数据本身。使用适当的方法可恢复被删除的数据。在商业公司的实际开发中会使用。

主键

每一张数据库表都可以有一个主键（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类型一致

CRUD

在计算机程序语言中，创建（Create）、读取（Read）、更新（Update）、删除（Delete），也就是CRUD；这是一连串常见的动作行为，而其行为通常是为了针对某个特定资源所作出的举动（例如：创建数据、读取数据等）。这四个行为最常见的用途能够在使用SQL数据库与网站的API端口时发现。这个词语（CRUD）最早被记载于James Martin所撰写的Managing the Data-base Environment书中。

英文中文SQLHTTP
CREATE 创建INSERT（插入）POST
READ读取SELECT（查询）GET
UPDATE更新UPDATE POST
DELETE删除DELETEDELETE

表的创建：

CREATE TABLE `user`(  //创建一个叫user的表
`id` INT(10)NOT NULL, //创建字段   其语法结构基本符合：字段名+数据类型+长度+是否为 NUL

/*id是字段名，我们用``这个符号把它包含起来。
INT 是数据类型，表示 id 这个字段是 INT 值
(10)表示 id 最长为 10 位
datetime 类型没有长度，所以不用定义长度
NOT NULL 表示这个字段不能为空，也就是必须要输入值，否则就会报错，如果这个值可以为不
输入值，那么不加 NOT NUL*/

`mobile` VARCHAR(11) NOT NULL,
`nickname` VARCHAR(40) NOT NULL,
`gmt_created` datetime ,
`gmt_modified` datetime NOT NULL,//表示创建修改时间
PRIMARY KEY ( `id` ) //约定主键 即意思为该表的主键为`id`

/*主键必须是已经约定的字段
 主键不能为空
 主键的值不能重复
 主键最大的作用是标识，所以它最好是由计算机生成，人工不干预主键生成后的值*/

)ENGINE=InnoDB DEFAULT CHARSET=utf8; //设置储存引擎和编码方式
/*这句话的意思是储存引擎为 InnoDB,默认编码方式为 utf-8。 InnoDB 是 MySQL 的默认储存引擎,utf-8 是一种编码方式*/

定义字段的语句，语句间有 , ，最后一句后面没有
SQL 语句以 ; 结尾。

我们有时使用以下来定义主键

`id` INT UNSIGNED AUTO_INCREMENT

这句话的意思是，id 会从 1 开始自增，第二个为 2，第三个为 3，依此类推。

当我们需要删除表时一般使用

drop table table_name;

有时也用

DROP TABLE IF EXISTS table_name;//表示如果存在这个表

插入语句

插入语句完整写法为：

INSERT INTO table_name(field1,field2,...fieldN)  //field表示表内要插入的字段名
VALUES
(value1,value2,...valueN);  //value 表示字段数据，其与上方field一一对应
  • varchar类型插入时要用 ‘ ’ 包裹
  • 对于数字 int 类型可以直接写 1
  • 对于 datetime 类型可以通过 now() 这个函数来获取服务器当前时间

插入语句的简化

  1. 如果主键设置为自增，则可以不插入主键和对应的数据
  2. 如果插入的是所有的字段，可以省略字段名，直接插入值，但是类型必须全部一致,比如：
INSERT INTO table_name
VALUES
(value1,value2,...valueN);

注：两种简化不能同时使用

如果要一次性插入大量数据，可以：

INSERT INTO table_name
VALUES
(value1,value2,...valueN),
(value1,value2,...valueN);

插入一条数据对应表格中的一行,当规定为 NOT NULL 的字段没有给到值时,插入语句会报错。

查询语句

SELECT field1,field2,.... FROM table_name; //field表示要查询的字段，table_name表示表名

如：

SELECT
  id,
  name
FROM
  people;

如果需要查询所以字段则也可以写成

SELECT * FROM people; //查询所有字段

条件查询（where语句）

实际使用中我们往往是定向对数据库进行查询，此时我们就需要使用 where 语句来限制条件。

SELECT * FROM table_name WHERE condition;  //condition表示条件
SELECT
  *
FROM
  people
where
  age > 50;

表示了查询年龄大于50岁的所有字段

Limit语句（限制）

在实际工作的数据查询中,我们有时候需要返回指定行,比如我想查询符合某个条件的前十个数据,这时候就需要使用LIMIT子句来加强查询功能。格式如下

SELECT * FROM table_name LIMIT parameter;  //其中parameter表示limit语句的参数

具体使用如下

SELECT * FROM people LIMIT 5, 6;
//表示从表的第5+1行开始（数据库中的行从第0行开始计数），查询6条记录。即查询6—11行是数据
SELECT * FROM people LIMIT 5;
//表示查询第0-5行与一下代码作用相同
SELECT * FROM timi_adc LIMIT 0,5;

特殊使用也可如下查询第x行数据

SELECT * FROM people LIMIT x, 1;

Limit 语句往往与其他语句如 where 一起使用如

SELECT * FROM people WHERE age>10 LIMIT 5;

即查询年龄大于10的前五条数据（ps where与limit位置的前后决定语句发生的先后）

排序（Order by）语句

我们会需要对查询的数据进行排序，可以使用排序语句，具体如下

SELECT * FROM table_name ORDER BY field_name;

排序默认按照升序排序，对于int,double而言，是从小到大，对于varchar而言，是从字母A到Z，对于
datetime而言，是从过去到现在。

DESC关键词

排序一般使用升序排序，关键词为ASC(一般不写)，我们可以通过加上DESC关键词变为降序排序

SELECT * FROM table_name ORDER BY field_name DESC;

与其他句子连用如：

SELECT * FROM people WHERE age>10 OREDER BY age LIMIT 5;

我们一般先排序再limit

更新/删除

更新语句（update）：

UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

使用Update时必须使用 where 来做限制，否则 update 会对整列起作用

UPDATE
  timi_adc
SET
  ban_rate = 0.01
WHERE
  hero_name = '艾琳';

删除语句（delete）：

DELETE FROM table_name [WHERE Clause]

由于删除语句是不可恢复的，所以我们务必要增加WHERE语句，否则将会删除整张表格的数据。

delete 只是删除表内是数据，如果要删除整个表使用 drop table +表名 的语句,当然，我们一般也这么写 DROP TABLE IF EXISTS table_name;