一、创建一个完整的表
创建数据表
# 创建一张表
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;