「这是我参与2022首次更文挑战的第1天，活动详情查看：2022首次更文挑战」

坑与注意事项

group by 有一个原则,就是 select 后面的所有列中,没有使用聚合函数的列,必须出现在 group by 后面

数据库认识

5种类型数据库

层次数据库，关系数据库，面向对象数据库，xml数据库，键值存储数据库。

mysql是关系数据库。即RDBMS。

数据库表结构，行称为记录，列称为字段。交汇处称为单元格。

sql语句分为三类

ddl：data definition language，数据定义语言。用于创建或删除数据库，数据表等对象。

- create：创建数据库和表等对象
- drop：删除库和表

- alter：修改库和表（列操作）

dml：data manipulation language，数据操纵语言，查询或变更记录（行）。（重点）

- select：查询
- insert：插入

- update：更新
- delete：删除

dcl：data control language，数据控制语言。确认或取消对数据的变更，设定对象的操作权限。

- commit：确认变更
- rollback：取消变更

- grant：赋予权限
- revoke：取消权限。

sql语句书写规范

分号结尾
关键字不区分大小写，数据区分大小写；表名和字段根据操作系统不同，win不区分大小写，linux和mac区分。统一用小写。

常数写法固定：日期，字符串用引号，数值直接写
单词用半角或空行间隔

常见注意事项如下：

MySQL 本身不区分大小写，但强烈要求关键字大写，表名、列名用小写；
创建表时，使用统一的、描述性强的字段命名规则保证字段名是独一无二且不是保留字的，不要使

用连续的下划线，不用下划线结尾；最好以字母开头；

关键字右对齐，且不同层级的用空格或缩进控制，使其区分开，见样例二；
列名少的时候写在一行里无伤大雅；多的时候以及涉及到 CASE WHEN 或者聚合计算的时候，建

议分行写；个人习惯是逗号在列名前面，方便之后删除某些列，放列名后亦可；

表别名和列别名尽量用有具体含义的词组，不要用 a b c，不然以后 review 的时候会非常痛苦；
运算符前后都加一个空格；
当用到多个表时，请在所有列名前写上引用的表别名，不要嫌麻烦；
每条命令用分号结尾；
养成随手写注释的习惯，注释方法：

• 单行注释 # 注释文字

• 单行注释 – 注释文字

• 多行注释： /* 注释文字 */

语句与语法

数据库层面

-- 创建数据库
CREATE DATABASE < 数据库名称 > ;
create database shop;

-- 插入表数据
CREATE TABLE < 表名 >
( < 列名 1> < 数据类型 > < 该列所需约束 > ,
  < 列名 2> < 数据类型 > < 该列所需约束 > ,
  < 列名 3> < 数据类型 > < 该列所需约束 > ,
  < 列名 4> < 数据类型 > < 该列所需约束 > ,
  .
  .
  .
  < 该表的约束 1> , < 该表的约束 2> ,……);
  
  CREATE TABLE product
(product_id CHAR(4) NOT NULL,
 product_name VARCHAR(100) NOT NULL,
 product_type VARCHAR(32) NOT NULL,
 sale_price INTEGER ,
 purchase_price INTEGER ,
 regist_date DATE ,
 PRIMARY KEY (product_id));
 
 -- 删除表：无法恢复，只能重新插入
 DROP TABLE < 表名 > ;
 DROP TABLE product;
 
 -- 添加列
 ALTER TABLE < 表名 > ADD COLUMN < 列的定义 >;
 ALTER TABLE product ADD COLUMN product_name_pinyin VARCHAR(100);
 
 -- 删除列，无法恢复
 ALTER TABLE < 表名 > DROP COLUMN < 列名 >;
 ALTER TABLE product DROP COLUMN product_name_pinyin;
 
 -- 清空表：比drop和delete快
 TRUNCATE TABLE TABLE_NAME;
 
 -- 更新记录
 UPDATE <表名>
   SET <列名> = <表达式> [, <列名2>=<表达式2>...];  
 WHERE <条件>;  -- 可选，非常重要。
 ORDER BY 子句;  --可选
 LIMIT 子句; --可选
 
 -- 修改所有的注册时间，set后可多写
UPDATE product
   SET regist_date = '2009-10-10';  
-- 仅修改部分商品的单价
UPDATE product
   SET sale_price = sale_price * 10
 WHERE product_type = '厨房用具'; 
 
 UPDATE product
   SET regist_date = NULL
 WHERE product_id = '0008';  
 
 -- 合并后的写法
UPDATE product
   SET sale_price = sale_price * 10,
       purchase_price = purchase_price / 2
 WHERE product_type = '厨房用具';  
 
 -- 插入一行
 INSERT INTO <表名> (列1, 列2, 列3, ……) VALUES (值1, 值2, 值3, ……);  
 
 -- 包含列清单
INSERT INTO productins (product_id, product_name, product_type, sale_price, purchase_price, regist_date) VALUES ('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');
-- 省略列清单
INSERT INTO productins VALUES ('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15'); 

-- 查询
SELECT <列名>, ……
  FROM <表名>
 WHERE <条件表达式>;
 
 -- 聚合查询
 -- 计算全部数据的行数（包含NULL）
SELECT COUNT(*)
  FROM product;
-- 计算NULL以外数据的行数
SELECT COUNT(purchase_price)
  FROM product;
-- 计算销售单价和进货单价的合计值
SELECT SUM(sale_price), SUM(purchase_price) 
  FROM product;
-- 计算销售单价和进货单价的平均值
SELECT AVG(sale_price), AVG(purchase_price)
  FROM product;
-- MAX和MIN也可用于非数值型数据
SELECT MAX(regist_date), MIN(regist_date)
  FROM product;
  
  -- 计算去除重复数据后的数据行数
SELECT COUNT(DISTINCT product_type)
 FROM product;
-- 是否使用DISTINCT时的动作差异（SUM函数）
SELECT SUM(sale_price), SUM(DISTINCT sale_price)
 FROM product;
 
 -- 表分组
 SELECT <列名1>,<列名2>, <列名3>, ……
  FROM <表名>
 GROUP BY <列名1>, <列名2>, <列名3>, ……;
 
 -- 按照商品种类统计数据行数
SELECT product_type, COUNT(*)
  FROM product
 GROUP BY product_type;
 -- 不含GROUP BY
SELECT product_type, COUNT(*)
  FROM product
  
 
 -- 分组与限定
  -- 数字
SELECT product_type, COUNT(*)
  FROM product
 GROUP BY product_type
HAVING COUNT(*) = 2;
-- 错误形式（因为product_name不包含在GROUP BY聚合键中）
SELECT product_type, COUNT(*)
  FROM product
 GROUP BY product_type
HAVING product_name = '圆珠笔';

-- 视图：保存select语句的结果
CREATE VIEW <视图名称>(<列名1>,<列名2>,...) AS <SELECT语句>

CREATE VIEW view_shop_product(product_type, sale_price, shop_name)
AS
SELECT product_type, sale_price, shop_name
  FROM product,
       shop_product
 WHERE product.product_id = shop_product.product_id;
 
 -- 修改视图
ALTER VIEW <视图名> AS <SELECT语句>

ALTER VIEW productSum
    AS
        SELECT product_type, sale_price
          FROM Product
         WHERE regist_date > '2009-09-11'; 
         
DROP VIEW <视图名1> [ , <视图名2> …]
DROP VIEW productSum;

（未完待续）

sql学习小结