数据库操作知识

一、创建一个完整的表

创建数据表

# 创建一张表

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查询条件（一）

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查询条件（二）

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 ('华为', '小米');

where查询条件（三）

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

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

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

# 查询所有以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有两个常用的值：

1.ASC：升序排字段；

2.DESC：降序排字段；

分页查询

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

在真实开发中，我们都会要求用户传入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;

聚合函数

聚合函数表示对值集合进行操作的组（集合）函数

# 华为手机价格的平均值
SELECT AVG(price) FROM `products` WHERE brand = '华为’;
# 计算所有手机的平均分
SELECT AVG(score) FROM `products`;
# 手机中最低和最高分数
SELECT MAX(score) FROM `products`;
SELECT MIN(score) FROM `products`;
# 计算总投票人数
SELECT SUM(voteCnt) FROM `products`;
# 计算所有条目的数量
SELECT COUNT(*) FROM `products`;
# 华为手机的个数
SELECT COUNT(*) FROM `products` WHERE brand = '华为';

认识Group By

事实上聚合函数相当于默认将所有的数据分成了一组：

如果我们希望划分多个组：比如华为、苹果、小米等手机分别的平均价格，我们可以使用 GROUP BY

GROUP BY通常和聚合函数一起使用：表示我们先对数据进行分组，再对每一组数据，进行聚合函数的计算；

我们现在来提一个需求：

根据品牌进行分组；

计算各个品牌中：商品的个数、平均价格

也包括：最高价格、最低价格；

SELECT brand,
COUNT(*) as count,
ROUND(AVG(price),2) as avgPrice,
MAX(price) as maxPrice,
MIN(price) as minPrice,
AVG(score) as avgScore
FROM `products` GROUP BY brand;

Group By的约束

使用我们希望给Group By查询到的结果添加一些约束，那么我们可以使用：HAVING

比如：如果我们还希望筛选出平均价格在4000以下，并且平均分在7以上的品牌：

SELECT brand,
COUNT(*) as count,
ROUND(AVG(price),2) as avgPrice,
MAX(price) as maxPrice,
MIN(price) as minPrice,
AVG(score) as avgScore
FROM `products` GROUP BY brand
HAVING avgPrice < 4000 and avgScore > 7;