SQL学习

基础（90%可以用到其它DBMS）（database management system：数据库管理系统、关系型数据库）

增删改查：
- inserting data：
- deleting data：
- updating data：
- retrieving data：
汇总数据(summarizing data)
使用子查询来复杂查询(writing complex queries)
基本内置函数(built-in Functions)
- 用于和数字、日期、文本数据打交道。
创建视图（views）
存储过程和函数（Stored procedures）
简单子句：
- SELECT：
  - 可以使用算数表达式
- SELECT customer_id, first_name（选择的是列）
  FROM customers
  -- WHERE customer_id = 1 （-- 为注释）
  ORDER BY first_name
  - FROM、WHERE、ORDER BY三个字句都是可选的，但是顺序不可以改变
  - 列名不能有空格，要加空格的话加 " "或' '
- DISTINCT关键字：
  - 去除重复项（去重）
- AS：
  - 起别名
- WHERE：
  - !=相同于<>
  - 可以使用算数表达式
- AND/OR/NOT（与或非）：
  - AND优先级高于OR
- IN:
  - 同一系列值比较一个属性
    - 例：WHERE state IN('VA', 'FL', 'GA') 等同于 WHERE state = 'VA' OR state = 'FL' OR state = 'GA'
- BETWEEN:
  - 取一个闭区间
    - 例： WHERE points BETWEEN 1000 AND 3000 等同于 WHERE points >= 1000 AND points <= 3000
- 如何检索遵循特定字符串模式的行：
  - LIKE：
    - WHERE last_name LIKE '%b%' (b不区分大小写，%表示任意字符)
    - WHERE last_name LIKE '_y' (_表示一个字符)
    - WHERE address LIKE '%trail%' OR
      address LIKE '%avenue%'
- REGEXP：
  - 正则表达式regular expression缩写
  - 搜索字符串时极其强大
  - "^"表示字符串的开头
    - WHERE last_name REGEXP '^my|se'
  - "$"表示字符串的末尾
    - WHERE last_name REGEXP 'ey$|on$'
  - "|"表示：或（OR）多个搜寻模式
    - WHERE first_name REGEXP 'elka|ambur'
  - "[]":匹配任意在括号里列举的单子符
    - WHERE last_name REGEXP 'b[ru]'
  - "[a-f]":闭区间a到f的任意字母
- 如何搜索缺失了属性的记录（值为null的记录）：
  - IS NULL
    - WHERE shipped_date IS NULL
- ORDER BY:
  - 默认列升序排序
  - DESC(降序) ORDER BY first_name DESC
  - 可以用别名：
    - SELECT *
      FROM order_items
      WHERE order_id = 2
      ORDER BY quantity * unit_price DESC
    - SELECT * , quantity * unit_price AS total_price
      FROM order_items
      WHERE order_id = 2
      ORDER BY total_price DESC
- 如何限定查询返回的记录：
  - LIMIT
    - LIMIT字句永远要放在最后
    - LINIT 3 （显示前三行记录）
    - 可以用于分页
      - --page 1: 1-3
        --page 2: 4-6
        --page 3: 7-9
        LIMIT 6, 3(6为偏移量，跳过前六个记录，获取3个记录)
连接:
- 内连接：
  - INNER JOIN （INNER关键字可写可不写）
  - SELECT order_id， order.customer_id, first_name, last_name
    FROM orders
    JOIN customers ON orders.customer_id = customers.customer_id
  - 给表起别名：
    SELECT order_id， o.customer_id, first_name, last_name
    FROM orders o
    JOIN customers c ON o.customer_id = c.customer_id
  - 跨数据库连接：
    - 要给不在当前数据库的表加前缀（数据库名称）
      - USE sql_inventory;
        SELECT *
        FROM sql_store.order_items oi
        JOIN sql_inventory.products p
        ON oi.product_id = p.product_id
  - 自连接：
    - USE sql_hr;
      SELECT e.employee_id,
      e.first_name,
      m.first_name AS manager
      FROM employees e
      JOIN employees m
      ON e.reports_to = m.employee_id
  - 多表连接：
    - USE sql_store;
      SELECT
      o.order_,
      o.order_date,
      c.firet_name,
      c.last_name,
      os.name AS status
      FROM orders o
      JOIN customers c
      ON o.customer_id = c.customer_id
      JOIN order_statuses os
      ON o.status = os.order_status_id
  - 复合连接：
    - 用于有复合主键表的连接
      - SELECT *
        FROM order_items oi
        JOIN order_items_notes oin
        ON oi.order_id = oin.order_id
        AND oi.product_id = oin.product_id
- 隐式连接语法：
  - 不建议用，如果忘了WHERE字句会变成交叉连接
  - SELECT *
    FROM orders o, customers c
    WHERE o.customer_id = c.customer_id
    等同于
    SELECT *
    FROM orders o
    JOIN customers c
    ON o.customer_id = c.customer_id
- 外连接：
  - OUTER JOIN（OUTER可省去）
  - FROM 里的是左表，JOIN里的是右表
  - 尽量使用左连接
  - 左连接（LEFT JOIN）：
    - 所有左表的记录，会被返回（不管条件ON正确不正确）。
      - SELECT p.product_id,
        name,
        oi.quantity
        FROM products p
        LEFT JOIN order_items oi
        ON p.product_id = oi.product_id
  - 右连接（RIGHT JOIN）：
    - 所有右表的记录，会被返回（不管条件ON正确不正确）。
  - USING
    - ON条件两个表中名称相同时使用
    - 内连接和外连接都可以使用
    - USING（customer_id）
      等同于 ON o.customer_id = c.customer_id
- 自然连接：
  - NATURAL JOIN
  - 危险：
    - 没有ON条件，基于共同的列连接
    - 让数据库引擎自己猜该怎么连接，我们无法控制它（不建议使用）
- 交叉连接：
  - CROSS JOIN
  - 用途：
    - 例：衣服的各种尺寸和各种颜色
  - 结合或者连接第一个表的每条记录和第二个表的每条记录
    - SELECT *
      
      FROM customers c
      CROSS JOIN products p（显示连接更清楚）
      等同于
      FROM customers c，products p（隐式连接）
      顾客表里的每条记录都会和产品表里的每条记录结合
结合：
- UNION：
  - 合并的列数必须相同，否则会报错
  - 合并后的列名取于第一个
- 合并多个查询的结果
  - SELECT customer_id,
    first_name,
    points,
    'Bronze' AS type
    FROM customers
    WHERE points < 2000
    UNION
    SELECT customer_id,
    first_name,
    points,
    'Silcer' AS type
    FROM customers
    WHERE points BETWEEN 2000 AND 3000
    UNION
    SELECT customer_id,
    first_name,
    points,
    'gold' AS type
    FROM customers
    WHERE points > 3000
    ORDER BY first_name
- 行连接：
  - 合并多段查询的记录
    - 有效的和无效的相连接
- 不同表和相同表都可以使用
插入、更新、删除：
- 插入：
  - 单行：
    - INSERT INTO customers(列名)
      VALUES(
      值
      )
  - 多行：
  - INSERT INTO customers(列名)
    VALUES()，
    (),
    ()
  - 插入分层行：
    - 往多表插入数据
      - 父子关系表
  - 复制数据：
    - 复制的新表没有主键和自增
    - CREATE TABLE orders_archived AS
      SELECT * FROM orders(子查询)
      子查询实例：
      - INSERT INTO orders_archived
        SELECT * FROM orders WHERE order_date < '2019-01-01'
      - CREATE TABLE invoices_archived AS
        SELECT i.invoice_id,
        i.number,
        c.name AS client,
        i.invoice_total,
        i.payment_total,
        i.invoice_date,
        i.payment_date,
        i.due_date
        FROM invoices i
        JOIN clients c
        USING (client_id)
        WHERE payment_date IS NOT null
- 更新：
  - UPDATE
  - 更新单行：
    - UPDATE invoices
      SET payment_total = 10, payment_date = '2019-01-05'（指定一列或者多列的新值）
      WHERE invoice_id = 1 (需要被更新的记录)
  - 更新多行：
    - UPDATE customers
      SET points = points + 50
      WHERE birth_date < '1990-01-01'customers
  - 使用子查询（很强大）：
    - 子查询（另一段SQL语句里的选择语句）
    - 可以作为WHERE语句里的筛选条件
      - UPDATE invoices
        SET
        payment_total = invoice_total * 0.5,
        payment_date = due_date
        WHERE client_id IN
        ( SELECT client_id
        FROM clients
        WHERE state IN ('CA', 'NY')
        )
      - UPDATE orders
        SET commments = 'gold customer'
        WHERE customer_id IN
        
        (SELECT customer_id
        FROM customers
        WHERE points > 3000)
- 删除：
  - DELETE FROM invoices
    WHERE client_id = (
    SELECT *
    FROM clients
    WHERE name = 'Myworks'
    )
- 恢复数据库：
  - file里sql-script
数据汇总：
- 聚合函数：
  - 使用了聚合函数应该要进行分组
  - 只运行非空值
  - SELECT
    MAX(invoice_total) AS highest,
    MIN(invoice_total) AS lowest,
    AVG(invoice_total) AS average,
    SUM(invoice_total) AS total,
    COUNT(invoice_total) AS number_of_invoices
    COUNT(*) AS total_records (全部记录，包含非空值)
    FROM invoices
  - DISTINCT(去重取唯一的)
    - COUNT(DISTINCT client_id) AS total_records
  - SELECT
    'First half of 2019' AS data_range,
    SUM(invoice_total) AS total_sales,
    SUM(payment_total) AS total_payments,
    SUM(invoice_total - payment_total) AS what_we_expect
    FROM invoices
    WHERE invoice_date
    BETWEEN '2019-01-01' AND '2019-06-30'
    UNION
    SELECT
    'Last half of 2019' AS data_range,
    SUM(invoice_total) AS total_sales,
    SUM(payment_total) AS total_payments,
    SUM(invoice_total - payment_total) AS what_we_expect
    FROM invoices
    WHERE invoice_date
    BETWEEN '2019-06-31' AND '2019-12-31'
    UNION
    SELECT
    'all of 2019' AS data_range,
    SUM(invoice_total) AS total_sales,
    SUM(payment_total) AS total_payments,
    SUM(invoice_total - payment_total) AS what_we_expect
    FROM invoices
    WHERE invoice_date
    BETWEEN '2019-01-01' AND '2019-12-31'
  - GROUP BY（分组查询）：
    - SELECT
      client_id,
      SUM(invoice_total) AS total_sales
      FROM invoices
      GROUP BY client_id
      ORDER BY total_sales DESC
  - HAVING：
    - 可以在分组（GROUP UP）后筛选--充当WHERE
  - WITH ROLLUP（汇总数据）:
    - GROUP BY client_id WITH ROLLUP
    - 只在MYSQL中可以使用
复杂查询：
- 子查询（内查询）是在另一个sql语句中的选择语句
  - SELECT *
    FROM products
    WHERE unit_price >
    (SELECT unit_price
    FROM products
    WHERE product_id = 3)
  - IN运算符写子查询：
    - SELECT *
      FROM products
      WHERE product_id NOT IN
      (
      SELECT DISTINCT product_id
      FROM order_items
      )
    - SELECT *
      FROM clients
      WHERE client_id NOT IN
      (
      SELECT DISTINCT client_id
      FROM invoices
      )
- 子查询vs连接
  - 用JOIN重写子查询或取反
    - 用子查询：
      - SELECT customer_id,
        first_name,
        last_name
        FROM customers
        WHERE customer_id IN
        (
        SELECT customer_id
        FROM order_items oi
        JOIN orders o
        USING (order_id)
        WHERE product_id = 3
        )
    - 用连接：（优选）
      - SELECT DISTINCT customer_id,
        first_name,
        last_name
        FROM customers c
        JOIN orders o USING (customer_id)
        JOIN order_items oi USING (order_id)
        WHERE oi.product_id = 3
- ALL关键字：
  - 用于子查询返回一列值，和每一个比较都要符合(大于最大，小于最小)
  - 可以和聚合函数max、min转换
    - SELECT *
      FROM invoices
      WHERE invoice_total > ALL (
      SELECT invoice_total
      FROM invoices
      WHERE client_id = 3
      )
    - SELECT *
      FROM invoices
      WHERE invoice_total > (
      SELECT MAX(invoice_total)
      FROM invoices
      WHERE client_id = 3
      )
- ANY:
  - = ANY相当于IN
- 相关子查询：
  - 父表里的每一个员工，它都会执行一次子查询
  - 执行的慢、占用更多存储
  - 子查询和父查询存在相关性 --子查询引用了父查询里表的别名
    - SELECT *
      FROM employees e
      WHERE salary > (
      SELECT AVG(salary)
      FROM employees
      WHERE office_id = e.office_id
      )
- EXISTS运算符：
  - SELECT *
    FROM products
    WHERE product_id NOT IN (
    SELECT product_id
    FROM order_items
    )
  - SELECT *
    FROM products p
    WHERE NOT EXISTS (
    SELECT product_id
    FROM order_items
    WHERE product_id = p.product_id
    )
- SELECT子句中获得子查询：
  - 表达式中不能使用列的别名
    - SELECT
      client_id,
      c.name,
      (SELECT SUM(invoice_total)
      FROM invoices
      WHERE client_id = c.client_id) AS total_sales,
      (SELECT AVG(invoice_total)
      FROM invoices) AS average,
      (SELECT total_sales - average) AS difference
      FROM clients c
- FROM子句中使用子查询：
  - 变得复杂，可以作为视图存储在数据库中
  - SELECT *
    FROM(
    SELECT
    client_id,
    c.name,
    (SELECT SUM(invoice_total)
    FROM invoices
    WHERE client_id = c.client_id) AS total_sales,
    (SELECT AVG(invoice_total)
    FROM invoices) AS average,
    (SELECT total_sales - average) AS difference
    FROM clients c
    ) AS sales_summary
    WHERE total_sales IS NOT NULL
内置函数：
- 用于应对数值、日期时间、字符串值
  - 数值：
    - ROUND（5.7366，2）四舍五入函数（值，精确精度）
    - TRUNCATE()--截断函数
    - CEILING(2.6)--取整-返回大于或等于这个数的最小整数
    - FLOOR（2.6）--地板函数-返回小于或等于这个数字的最大整数
    - ABS(-2.6)--计算绝对值
    - RAND()--用来生成0-1之间的随机浮点数
  - 字符串：
    - LENGTH('asdas')--得到字符串中的字符数
    - UPPER('asdas')--将字符串转化成大写字母
    - LOWER('ASDAS')--将字符串转化成小写字母
    - 去除字符串中不需要的空格：
      - LTRIM(左修整-left trim) LTRIM(' ASDAD')--去除字符串左侧的空白字符
      - RTRIM('ASDAS ')--去除字符串左侧的空白字符
      - TRIM(' asd ')--去除所有空白字符
    - LEFT('sdfdaadasfrs'，4)--返回字符串左侧的几个字符
    - RIGHT('asdasdqasd', 6)--返回字符串右侧的几个字符
    - SUBSTRING('afdasdasa',起始位置，长度)--字符截取函数--可以得到一个字符串中任何位置的字符
      - 第三个参数为可选，默认为返回到末尾
    - LOCATE('b','sadasfbkl' )-- 返回第一个字符或者一串字符匹配位置
      - 不区分大小写，没有返回0
    - REPLACE('zhangziyang', 'yang', 'hai' )--替换一段字符串
    - CONCAT('first', 'LAST')--串联字符串
  - 日期和时间：
    - NOW()--电脑当前日期和时间
    - CURDATE()--当前日期
    - CURTIME()--当前时间
    - YEAR(NOW())--MONTH、DAY、HOUR、MINUTE、SWCOND截取当前年、月、日、时、分、秒--返回整数值
    - DAYNAME(NOW())--MONTHNAME获取星期数、月份数--返回字符串格式
    - EXTRCT(YEAR FROM NOW())---返回2019---(想获取的单位 FROM 时间日期值)--返回字符串格式
    - 例：返回当前年下的订单
      - SELECT *
        FROM orders
        WHERE YEAR(order_date) = YEAR(NOW())
    - 日期格式函数：DATE_FORMAT()
      - DATE_FORMAT(NOW(), '%M %d %Y')
    - 时间格式函数：TIME_FORMAT()
      - TIME_FORMAT(NOW(), '%H:%i %p')
    - 计算日期和时间的函数：
      - DATE_ADD(NOW(), INTERVAL 1/-1 DAY/YEAR)--增加或减少
      - DATEDIFF('2021-07-04','2021-06-17')--计算日期间隔，返回天数--来上海了17天
      - TIME_TO_SEC()---返回从零点计算的秒数--TIME_TO_SEC('09:02') - TIME_TO_SEC('09:00')
- IFNULL：
  - SELECT
    order_id,
    IFNULL(shipper_id, '未分配') AS shipper---如果shipper_id为空(null),改为未分配---替换空值
    FROM orders
- COALESCE(shipper_id, comments, '未分配')：---如果如果shipper_id为空(null)，返回comments列的值，
  如果comments列的值为空，shipper_id改为未分配---提供一堆值，返回第一个非空值
  - 例：SELECT
    CONCAT(first_name,' ', last_name) AS customer,
    COALESCE(phone,'UNKNOW') AS phone
    FROM customers
- IF函数：用来检验--允许单一的表达式
  - IF(表达式，为真时返回的值，为假时返回的值) ---可以返回任何值
    - 例：SELECT
      order_id,
      order_date,
      IF(YEAR(order_date) = YEAR(NOW()), '活跃', '归档') AS category
      FROM orders
    - 例：SELECT
      product_id,
      name,
      COUNT() AS orders,
      IF(COUNT() > 1, 'many times', 'once') AS Frequence
      FROM products p
      JOIN order_items oi USING(product_id)
      GROUP BY product_id, name
- CASE运算符：
  - CASE
    WHEN THEN --每个when子句都有一个测试表达式，满足就返回THEN后的值
    END --关闭通道
    -例：
    - SELECT
      order_id,
      CASE
      WHEN YEAR(order_date) = YEAR(NOW()) THEN 'ACTIVE'
      WHEN YEAR(order_date) = YEAR(NOW()) - 1 THEN 'LAST_year'
      WHEN YEAR(order_date) < YEAR(NOW()) - 1 THEN 'Archived'
      ELSE 'Future'
      END AS category
      FROM orders
    - SELECT
      CONCAT(first_name, ' ', last_name) AS customer,
      points,
      CASE
      WHEN points > 3000 THEN 'Gold'
      WHEN points >= 2000 THEN 'Silver'
      ELSE 'Bronze'
      END AS category
      FROM customers
      ORDER BY points DESC
视图：
- 解决繁琐的查询---相当于一张虚拟表
- 把查询或子查询存到视图里
- 视图不存储数据，数据存在表中
- CREATE VIEW AS
  - 例：
    - CREATE VIEW sales_by_client AS
      SELECT
      c.client_id,
      c.name,
      SUM(invoice_total) AS total_sales
      FROM clients c
      JOIN invoices i USING (client_id)
      GROUP BY client_id, name
    - CREATE VIEW clients_balance AS
      SELECT
      c.client_id,
      c.name,
      SUM(i.invoice_total - i.payment_total) AS blance
      FROM clients c
      JOIN invoices i USING (client_id)
      GROUP BY client_id, name
- 删除视图：
  - DROP VIEW 视图名
- 重建视图：
  - CREATE OR REPLACE VIEW 视图名 AS
- 可更新视图：
  - 没有：DISTINCT、聚合函数、GROUP/HAVING、UNION就为可更新视图
  - 末尾加：WITH CHECK OPTION 防止UPDATE和DELETE语句将行从视图中删除
存储引擎：
- 是一个表存储/组织数据的方式
- 不同的存储引擎，表存储数据的方式不同
- mysql里特有的一个术语，其他数据库中叫法不同
- ENGINE来指定存储引擎，默认为InnoDB
  - 查看当前mysql版本支持哪些存储引擎：
    - show engines \G
- 常用存储引擎：
  - MyISAM:
    - 使用三个文件表示每个表：
      - 格式文件：mytable.frm
      - 数据文件：.MYD
      - 索引文件：.MYI---索引相当于一本书的目录，缩小查找范围，提高检索效率。（还比如字典的abcd）
    - 优势：可以被转换为压缩、只读来节省空间
  - Innodb：
    - 最主要的特点：非常安全
    - 支持事务：事务就是为了解决数据安全问题
    - 支持外键
    - 支持行锁和表锁
    - 数据库崩溃后自动恢复机制

进阶

触发器（triggers）
事件（events）
事务和并发（transactions）
- 事务(重点):
  - 一个事务其实就是一个完整的业务逻辑，是一个最小的工作单元，不可再分。
  - 是一系列SQL语句（增删改）必须要么全部执行成功，要么全部执行失败（回滚）。
  - 例：转账
  - 只有数据增删改（DML）与事务有关，其它语句和事务无关----保证数据安全
  - 是怎么做到一系列SQL语句（增删改）要么全部执行成功，要么全部执行失败？
    - Innodb存储引擎：提供一组用来记录事务性活动的日志文件
    - 事务开起了：
      - insert
      - delete
      - update
      - insert
      - delete
      - update
    - 事务结束了！
    - 在事务执行过程中，每一条dml操作都会被记录到日志文件中，可以提交事务，也可以回滚事务。
      - 提交事务：全部执行成功 ---commit语句
      - 回滚事务：全部执行失败 ---rollback语句
  - 四个特性：ACID
    - A---（atomicity）原子性---最小工作单元，不可再分
    - C---（consistency）一致性---要么同时成功，要么同时失败，保证数据的一致性
    - I---（Isolation）隔离性---允许多个事务同时对数据库操作
      - 四个隔离级别：A教室和B教室中级有一道墙，可以很厚，也可以很薄。
      - 读未提交（最低）：事务A可以读取到事务B未提交的数据（脏读）--墙太薄
      - 大多数都是二档起步！
      - 读已提交：解决了脏读，但不可重复读取数据---每一次读到的数据不一样，比较真实---orchal中默认
      - 可重复读：数据一致，每一次读到的可能是幻想，不够真实---mysql中默认
      - 序列化/串性化（最高）：解决了所有问题，效率最低---不能并发（线程同步）
    - D---（Durability）持久性--- 事务结束后，将数据保存到磁盘上
并发（concurrency）

高阶

设计数据库（Designing Databases）重点
- 数据库表的设计依据（三范式）：
  - 第一范式：要求任何一张表必须有主键，每一个字段原子性不可再分
  - 第二范式：建立在第一范式基础上，要求所有非主键字段完全依赖主键，不要产生部分依赖
    - 多对多，三张表，关系表两外键
  - 第三范式：建立在第二范式基础上，要求所有非主键字段直接依赖主键，不要产生传递依赖
    - 一对多，两张表，多的表加外键
  - 根据三范式设计表的目的：
    - 避免表中数据的冗余，空间的浪费。
索引（Indexing for high performance）重点
- 相当于目录
- Mysql中，主键上以及unique字段会自动添加索引
- 创建、删除索引：
  - CREATE INDEX 索引名 ON 表名(字段名)
  - DROP INDEX 索引名 ON 表名
- 在mysql中，怎么查看一个SQL语句是否使用了索引进行检索？
  - EXPLAIN SWLECT * FROM 表名 WHERE 字段=''
- 索引失效：
  - LIKE 查询时 '%'以%开头
  - 使用or的时候，如果使用or要求or两边的条件字段都要有索引才会用索引查询。
  - 在WHERE中索引列参加了运算
- 什么情况需要为字段创建索引：
  - 数据量庞大时
  - 该字段经常出现在WHWERE后面时（总是被查询）
  - 该字段很少DML操作（DML之后，索引会重新排序）
- 不要随便添加索引，因为索引需要维护，太多会降低系统的性能
- 建议通过主键，unique约束的字段进行查询
数据库安全（Securing Databases）

posted @ 2021-07-06 16:32 把悲伤留给自己zzy 阅读(156) 评论(0) 收藏举报

刷新页面返回顶部

把悲伤留个自己

SQL学习

SQL学习

基础（90%可以用到其它DBMS）（database management system：数据库管理系统、关系型数据库）

进阶

高阶

公告