MySQL（二）

SQL的数据类型 – 数字类型

我们知道不同的数据会划分为不同的数据类型，在数据库中也是一样：

       MySQL支持的数据类型有：数字类型，日期和时间类型，字符串（字符和字节）类型，空间类型和 JSON数据类型。

数字类型

       MySQL的数字类型有很多：

       整数数字类型：INTEGER，INT，SMALLINT，TINYINT，MEDIUMINT，BIGINT；=>number

       浮点数字类型：FLOAT，DOUBLE（FLOAT是4个字节，DOUBLE是8个字节）；

       精确数字类型：DECIMAL，NUMERIC（DECIMAL是NUMERIC的实现形式）；

SQL的数据类型 – 日期类型

MySQL的日期类型也很多：

YEAR以YYYY格式显示值

       范围 1901到2155，和 0000。

DATE类型用于具有日期部分但没有时间部分的值：

       DATE以格式YYYY-MM-DD显示值 ；

       支持的范围是 ‘1000-01-01’ 到 ‘9999-12-31’；

DATETIME类型用于包含日期和时间部分的值：

       DATETIME以格式’YYYY-MM-DD hh:mm:ss’显示值；

       支持的范围是1000-01-01 00:00:00到9999-12-31 23:59:59;

TIMESTAMP数据类型被用于同时包含日期和时间部分的值：

       TIMESTAMP以格式’YYYY-MM-DD hh:mm:ss’显示值；

       但是它的范围是UTC的时间范围：‘1970-01-01 00:00:01’到’2038-01-19 03:14:07’;

另外：DATETIME或TIMESTAMP 值可以包括在高达微秒（6位）精度的后小数秒一部分

       比如DATETIME表示的范围可以是’1000-01-01 00:00:00.000000’到’9999-12-31 23:59:59.999999’;

SQL的数据类型 – 字符串类型

MySQL的字符串类型表示方式如下：

CHAR类型在创建表时为固定长度，长度可以是0到255之间的任何值；

       在被查询时，会删除后面的空格；

VARCHAR类型的值是可变长度的字符串，长度可以指定为0到65535之间的值；

       在被查询时，不会删除后面的空格；

BINARY和VARBINARY 类型用于存储二进制字符串，存储的是字节字符串；

       https://dev.mysql.com/doc/refman/8.0/en/binary-varbinary.html

BLOB用于存储大的二进制类型；

TEXT用于存储大的字符串类型；

表约束

主键：PRIMARY KEY
一张表中，我们为了区分每一条记录的唯一性，必须有一个字段是永远不会重复，并且不会为空的，这个字段我们通常会
将它设置为主键：

       主键是表中唯一的索引；

       并且必须是NOT NULL的，如果没有设置 NOT NULL，那么MySQL也会隐式的设置为NOT NULL；
       主键也可以是多字段索引，PRIMARY KEY(key_part, …)，我们一般称之为联合主键；

       建议：开发中主键字段应该是和业务无关的，尽量不要使用业务字段来作为主键；

唯一：UNIQUE

       某些字段在开发中我们希望是唯一的，不会重复的，比如手机号码、身份证号码等，这个字段我们可以使用UNIQUE来约束：

使用UNIQUE约束的字段在表中必须是不同的；

对于所有引擎，UNIQUE 索引允许NULL包含的字段具有多个值NULL。

不能为空：NOT NULL

某些字段我们要求用户必须插入值，不可以为空，这个时候我们可以使用 NOT NULL 来约束；

默认值：DEFAULT

某些字段我们希望在没有设置值时给予一个默认值，这个时候我们可以使用 DEFAULT来完成；

自动递增：AUTO_INCREMENT

某些字段我们希望不设置值时可以进行递增，比如用户的id，这个时候可以使用AUTO_INCREMENT来完成；

外键约束也是最常用的一种约束手段，我们再讲到多表关系时，再进行讲解；

创建一个完整的表

创建数据表

# 创建一张表
CREATE TABLE IF NOT EXISTS `users`(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
age INT DEFAULT 0,
telPhone VARCHAR(20) DEFAULT '' UNIQUE NOT NULL
);

删除数据表

# 删除数据库表
DROP TABLE users;
DROP TABLE IF EXISTS users;

修改表

如果我们希望对表中某一个字段进行修改：

# 1.修改表名
ALTER TABLE `moments` RENAME TO `moment`;
# 2.添加一个新的字段
ALTER TABLE `moment` ADD `publishTime` DATETIME;
ALTER TABLE `moment` ADD `updateTime` DATETIME;
# 3.删除字段
ALTER TABLE `moment` DROP `updateTime`;
# 4.修改字段的名称
ALTER TABLE `moment` CHANGE `publishTime` `publishDate` 
DATE;
# 5.修改字段的数据类型
ALTER TABLE `moment` MODIFY `id` INT;

创建新表-删除操作

DML：Data Manipulation Language（数据操作语言）

创建一张新的表

CREATE TABLE IF NOT EXISTS `products`(
`id` INT PRIMARY KEY AUTO_INCREMENT,
`title` VARCHAR(20),
`description` VARCHAR(200),
`price` DOUBLE,
`publishTime` DATETIME
);

插入数据：

INSERT INTO `products` (`title`, `description`, `price`, `publishTime`) VALUES ('iPhone', 'iPhone12只要998', 998.88, '2020-10-10');
INSERT INTO `products` (`title`, `description`, `price`, `publishTime`) VALUES ('huawei', 'iPhoneP40只要888', 888.88, '2020-11-11');

删除操作-更新操作

删除数据：

# 删除数据
# 会删除表中所有的数据
DELETE FROM `products`;
# 会删除符合条件的数据
DELETE FROM `products` WHERE `title` = 'iPhone';

修改数据：

# 修改数据
# 会修改表中所有的数据
UPDATE `products` SET `title` = 'iPhone12', `price` = 1299.88;
# 会修改符合条件的数据
UPDATE `products` SET `title` = 'iPhone12', `price` = 1299.88 WHERE `title` = 'iPhone';

DQL语句

DQL：Data Query Language（数据查询语言）

       SELECT用于从一个或者多个表中检索选中的行（Record）。

查询的格式如下：

SELECT select_expr [, select_expr]…
              [FROM table_references]
              [WHERE where_condition]
              [ORDER BY expr [ASC | DESC]]
              [LIMIT {[offset,] row_count | row_count OFFSET offset}]
              [GROUP BY expr]
              [HAVING where_condition]

准备数据：

准备一张表：

CREATE TABLE IF NOT EXISTS `products` (
       id INT PRIMARY KEY AUTO_INCREMENT,
       brand VARCHAR(20),
       title VARCHAR(100) NOT NULL,
       price DOUBLE NOT NULL,
       score DECIMAL(2,1),
       voteCnt INT,
       url VARCHAR(100)
       );

基本查询

查询所有的数据并且显示所有的字段：

       SELECT * FROM products;
查询title、brand、price：

       SELECT title, brand, price FROM products;

我们也可以给字段起别名：

       别名一般在多张表或者给客户端返回对应的key时会使用到；

       SELECT title as t, brand as b, price as p FROM products;

where查询条件

在开发中，我们希望根据条件来筛选我们的数据，这个时候我们要使用条件查询：

       条件查询会使用 WEHRE查询子句；

WHERE的比较运算符

# 查询价格小于1000的手机
SELECT * FROM `products` WHERE price < 1000;
# 查询价格大于等于2000的手机
SELECT * FROM `products` WHERE price >= 2000;
# 价格等于3399的手机
SELECT * FROM `products` WHERE price = 3399;
# 价格不等于3399的手机
SELECT * FROM `products` WHERE price != 3399;
# 查询华为品牌的手机
SELECT * FROM `products` WHERE `brand` = '华为';

WHERE的逻辑运算符

# 查询品牌是华为，并且小于2000元的手机
SELECT * FROM `products` WHERE `brand` = '华为' and `price` < 2000;
SELECT * FROM `products` WHERE `brand` = '华为' && `price` < 2000;
# 查询1000到2000的手机（不包含1000和2000）
SELECT * FROM `products` WHERE price > 1000 and price < 2000;
# OR: 符合一个条件即可
# 查询所有的华为手机或者价格小于1000的手机
SELECT * FROM `products` WHERE brand = '华为' or price < 1000;
# 查询1000到2000的手机（包含1000和2000）
SELECT * FROM `products` WHERE price BETWEEN 1000 and 2000;
# 查看多个结果中的一个
SELECT * FROM `products` WHERE brand in ('华为', '小米');

模糊查询

模糊查询使用LIKE关键字，结合两个特殊的符号：

       %表示匹配任意个的任意字符；

       _表示匹配一个的任意字符；

# 查询所有以v开头的title
SELECT * FROM `products` WHERE title LIKE 'v%';
# 查询带M的title
SELECT * FROM `products` WHERE title LIKE '%M%';
# 查询带M的title必须是第三个字符
SELECT * FROM `products` WHERE title LIKE '__M%';

查询结果排序

当我们查询到结果的时候，我们希望讲结果按照某种方式进行排序，这个时候使用的是ORDER BY；

ORDER BY有两个常用的值：

       ASC：升序排字段；

       DESC：降序排字段；

       SELECT * FROM products WHERE brand = ‘华为’ or price < 1000 ORDER BY price ASC;

分页查询

当数据库中的数据非常多时，一次性查询到所有的结果进行显示是不太现实的：

       在真实开发中，我们都会要求用户传入offset、limit或者page等字段；

       它们的目的是让我们可以在数据库中进行分页查询；

       它的用法有[LIMIT {[offset,] row_count |row_count OFFSET offset}]

SELECT * FROM `products` LIMIT 30 OFFSET 0;
SELECT * FROM `products` LIMIT 30 OFFSET 30;
SELECT * FROM `products` LIMIT 30 OFFSET 60;
# 另外一种写法：offset, row_count
SELECT * FROM `products` LIMIT 30,30;