数据库之SQL语句（一）

此笔记仅仅是对数据库进行一系列操作，一切概念理论知识均由代码展示。如果您想了解更多mysql的相关原理和应用场景，推荐选择阅读 《数据库系统概论（第5版）》

DDL-对数据表的操作

DDL（Data Definition Language）：数据定义语言；
可以通过DDL语句对数据库或者表进行：创建、删除、修改等操作；

• 查看所有表格

SHOW TABLES;

• 新建表

CREATE TABLE IF NOT EXISTS  `students`(
        `name` VARCHAR(10),
        `age` int,
        `score` int,
        `height` DECIMAL(10,2),
        `birthday` YEAR, 
        `phoneNum` VARCHAR(20) DEFAULT '555555' NOT NULL,
);

• 删除表

DROP TABLE IF EXISTS `moment`;

• 查看表的结构

    DESC students;

表的结构.png

• 查看表的sql语句

    SHOW CREATE TABLE `students`;

• 创建表，设置字符集和排序

CREATE TABLE `students` (
  `name` varchar(10) DEFAULT NULL,
  `age` int DEFAULT NULL,
 `score` int DEFAULT NULL
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

• 完整的创建表的语法

CREATE TABLE IF NOT EXISTS `users` (
    id INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(20) NOT NULL,
     age INT DEFAULT 0,
     phoneNum VARCHAR(20) UNIQUE NOT NULL,
     createTime TIMESTAMP
)

• 修改表

1.修改表的名字

 ALTER  TABLE `users` RENAME TO `user`;

2.添加新的列

 ALTER TABLE `user` ADD `updateTime` TIMESTAMP;

3.修改字段的名称

 ALTER TABLE `user` CHANGE `phoneNum` `telphone` VARCHAR(20);

4.修改字段的类型

 ALTER TABLE `user` MODIFY `name` VARCHAR(30);

5.删除某一个字段

 ALTER TABLE `user` DROP `age`;

6.根据一个表的结构去创建另一张表

 CREATE TABLE `user1` LIKE  `user`;

7.根据另一个表的所有内容去创建新的表

 CREATE TABLE `user3` (SELECT * FROM `user`);

DDL对数据库的操作

• 查看所有的数据

SHOW DATABASES;

• 选择一个数据

USE bili;

• 查看当前使用的数据库

SELECT DATABASE();

• 创建一个数据库

 CREATE DATABASE douyu;

• 创建数据库的时候需要判断有没有当前创建的数据库

CREATE DATABASE IF NOT EXISTS douyu; 

• 设置编码

CREATE DATABASE IF NOT EXISTS huya  DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

• 删除数据库

DROP DATABASE IF EXISTS douyu;

• 修改数据库的语句的char acter

ALTER DATABASE huya CHARACTER SET  = utf8  COLLATE = utf8_unicode_ci;

DML-对数据库增删改

DML（Data Manipulation Language）：数据操作语言；
可以通过DML语句对表进行：添加、删除、修改等操作；

• 插入数据

INSERT INTO  `user`VALUES (110,'thunder','110120130','2021/3/21','2021/5/21');
INSERT INTO `user`(`name`,`telphone`,`createTime`,`updateTime`)
VALUES ('kobe','000-11111','2020-10-10','2021-10-10');

• 需求 createTime和updataTime可以自动设置值

ALTER TABLE `user` MODIFY `createTime` TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

ALTER TABLE  `user` MODIFY `updateTime` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
 
INSERT INTO `user`(`name`,`telphone`) VALUES ('LOVE','000-12221');

补充
MySQL的日期类型也很多：

1. YEAR以YYYY格式显示值
   范围 1901到2155，和 0000。
2. DATE类型用于具有日期部分但没有时间部分的值：
   DATE以格式YYYY-MM-DD显示值 ；
   支持的范围是 '1000-01-01' 到 '9999-12-31'；
3. DATETIME类型用于包含日期和时间部分的值：
   DATETIME以格式'YYYY-MM-DD hh:mm:ss'显示值；
   支持的范围是1000-01-01 00:00:00到9999-12-31 23:59:59;
4. TIMESTAMP数据类型被用于同时包含日期和时间部分的值：
   TIMESTAMP以格式'YYYY-MM-DD hh:mm:ss'显示值；
   但是它的范围是UTC的时间范围：'1970-01-01 00:00:01'到'2038-01-19 03:14:07';
5. 另外：DATETIME或TIMESTAMP 值可以包括在高达微秒（6位）精度的后小数秒一部分
   比如DATETIME表示的范围可以是'1000-01-01 00:00:00.000000'到'9999-12-31 23:59:59.999999';

• 删除数据

• 删除所有的数据

DELETE FROM `user`;

• 删除某一条数据

DELETE FROM `user` WHERE id=110;

• 更新数据

• 更新所有数据

UPDATE `user` SET `name`= 'thunder',telphone= '021-22121313';

• 更新一条数据

UPDATE `user` SET `name`= 'thunder',telphone= '021-22121313' WHERE id = 1;

DQL-对数据库查询操作

• 创建product 表
  -- DECIMAL（x,y）x是表示最多可以存储的十进制的x 位数，包括小数点前后，y是表示小数点后面的位数

CREATE TABLE IF NOT EXISTS `product` (
        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),
        pid INT
 );

• 基本查询
• 查询所有的字段

 SELECT * FROM product;

• 查询指定的字段

 SELECT title,price FROM product;

• 对指定字段起别名 这里的as可以省略

 SELECT title AS  phoneTitle,price as currentPrice FROM product;

• where 条件
• 案例： 价格小于1000的手机

 SELECT title,price FROM product WHERE price < 1000;

• 案例：价格为999的手机

 SELECT * FROM product WHERE price=999;

• 案例：价格不等于999的手机 != 或<>

 SELECT * FROM product WHERE price<>999;

• 案例 查询品牌是华为手机

 SELECT * FROM product WHERE brand = '华为';

• 逻辑运算语句

 SELECT *FROM  product WHERE price>1000 AND price < 2000;
  SELECT *FROM  product WHERE price>1000 && price < 2000;

• 包含2000

    SELECT *FROM  product WHERE price BETWEEN 1000 AND 2000;

• 案例2 价格在5000以上或者品牌是华为的手机 || 或者 or

SELECT * FROM product WHERE price > 5000 || brand='华为';

• 将某些值设置为null

UPDATE  product SET url = NULL WHERE id>=85 AND id<=88;

• 查询某个值为null

SELECT * FROM product WHERE url IS NULL;

• 模糊查询 :_p：前面的什么无所谓只是第二个字符是P

  SELECT * FROM product WHERE title LIKE '%M%';
SELECT * FROM product WHERE title LIKE '%P%';
SELECT * FROM product WHERE title LIKE '_P%';

• IN 表示取多个值中的其中一个即可 || 或者 or

SELECT * FROM product WHERE brand='华为' || brand ='小米' ||brand ='苹果';
SELECT * FROM product WHERE brand IN ('华为','小米','苹果');

• 结果排序

SELECT * FROM product WHERE brand IN ('华为','小米','苹果') ORDER BY price DESC;

• 分页查询

LIMIT 20 OFFSET 0
LIMIT offset,limit

SELECT * FROM product LIMIT 20 OFFSET 0;
SELECT * FROM product LIMIT 20 OFFSET 20;
SELECT * FROM product LIMIT 0,20;

DQL-聚合函数GroupBy

• 聚合函数的使用
• 求所有手机的价格的总和

SELECT SUM(price) totalPrice FROM `product`;

• 求一下华为手机的价格总和

SELECT SUM(price) FROM `product` WHERE brand="华为";

• 求华为手机的平均价格

SELECT AVG(price) FROM `product` WHERE brand="华为";

• 最高手机的价格，和手机最低的价格

SELECT MAX(price) FROM `product`;
SELECT MIN(price) FROM `product`;

• 求华为手机的个数

SELECT COUNT(*) FROM `product` WHERE brand='华为';
SELECT COUNT(*) FROM `product` WHERE brand='苹果';
SELECT COUNT(url) FROM `product` WHERE brand='苹果';

-- DISTINCT :取消重复的

SELECT COUNT(price) FROM `product`; 
SELECT COUNT(DISTINCT price) FROM `product`; 

• GROUP BY的使用

SELECT brand,AVG(price),COUNT(*),AVG(score) FROM `product` GROUP BY brand;

• HAVING 的使用 对分组之后查询到的结果进行筛选

SELECT brand,AVG(price) avgprice,COUNT(*),AVG(score) FROM `product` GROUP BY brand HAVING avgprice>2000;

• 需求：求评分 > 7.5分的手机的平均价格是多少

升级：平均分大于7.5的手机，按照品牌进行分类，求出平均价格
先查找出全部评分大于 7.5的手机，然后在根据brand分组，在根据avg（price）

SELECT AVG(price) FROM product WHERE score > 7.5
SELECT  brand,AVG(price) FROM product WHERE score > 7.5 GROUP BY brand;

数据库之SQL语句（二）