SQL必知必会

2020-05-31 本文已影响0人粥一样温柔

检索数据

搜索并去重【DISTINCT】：

SELECT DISTINCT authlevel 
FROM dop_app.t_app_user;

限制结果【LIMIT】：

LIMIT指定返回的行数：

SELECT * FROM dop_app.t_app_user LIMIT 5; （展示第1行到第5行的数据）

OFFSET指定从哪儿开始：

SELECT * FROM dop_app.t_app_user LIMIT 5 OFFSET 8;（展示第9行到第13行的数据）

注意：第0行。第一个被检索的行是第0行，而不是第1行。因此，LIMIT 1 OFFSET 1会检索第2行，而不是第1行。
MySQL、MariaDB和SQLite支持简化版的LIMIT 4 OFFSET 3语句，即LIMIT 8,5。逗号之前的值对应OFFSET，逗号之后的值对应LIMIT。

使用注释：

行内注释：注释使用--（两个连字符）嵌在行内。

-- 之后的文本就是注释：select * from app_user； --这是一条注释

多行注释：/* */ :

/* select app_address 
from app_user；*/

排序检索数据

排序检索【ORDER BY】：

SELECT * 
FROM Products 
ORDER BY prod_id;

按多个列排序：

SELECT * 
FROM Products 
ORDER BY prod_price,prod_name;

（仅在多个行具有相同的prod_price值时才对产品按prod_name进行排序。如果prod_price列中所有的值都是唯一的，则不会按prod_name排序。）

指定排序方向【DESC：降序】【ASC：升序】：

SELECT * 
FROM Products 
ORDER BY prod_price DESC;

过滤数据

使用WHERE子句【WHERE】：

SELECT prod_name,prod_price 
FROM Products 
WHERE prod_price = 3.5;

WHERE子句操作符：

= 等于
<> 不等于
< 小于
>大于
!= 不等于
!< 不小于
!> 不大于
>=大于等于
BETWEEN 在指定的两个值之间  （SELECT prod_name,prod_price FROM Products WHERE prod_price BETWEEN 5 AND 10;）
IS NULL Null值（SELECT prod_name,prod_price FROM Products WHERE prod_price IS NULL;）

范围值检查【BETWEEN】：

SELECT * 
FROM products 
WHERE prod_price  BETWEEN 5 AND 10;

高级数据过滤

组合WHERE子句：AND操作符，OR操作符

求值顺序：SQL（像多数语言一样）在处理OR操作符前，优先处理AND操作符。

AND操作符.jpg

求值顺序.jpg

IN操作符

IN操作符用来指定条件范围，范围中的每个条件都可以进行匹配。IN取一组由逗号分隔、括在圆括号中的合法值；

SELECT prod_name,prod_price
FROM Products
WHERE vend_id IN (1,5,6,22);

NOT操作符：

WHERE子句中的NOT操作符有且只有一个功能，那就是否定其后所跟的任何条件；也可以使用＜>操作符来完成。

SELECT prod_name
FROM Products
WHERE NOT vend_id =‘DLL01’
ORDER BY prod_name；

用通配符进行过滤

百分号（%）通配符

百分号通配符

下划线（_）通配符

下划线通配符

尽管SQL的通配符很有用。但是通配符搜索一般比前面讨论的其他搜索要耗费更长的处理时间。
所以注意：

1.尽量不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。
2.在确实需要使用通配符时，也尽量不要把它们用在搜索模式的开始处。把通配符置于开始处，搜索起来是最慢的。

聚集函数

聚集函数：对某些行运行的函数，计算并返回一个值。

AVG()函数

通过对表中行数计数，并计算列值之和的平均值。

SELECT AVG(prod_price) AS avg_price
FROM Products;

COUNT()函数

两种用法：

使用COUNT(*)函数对表中的数目进行计数，不管表中包含的是空值null还是非空值。
2.使用COUNT(column)对特定列中具有的值进行计数，忽略NULL值。

SELECT COUNT(*) AS num_cust
FROM Customers;

SELECT COUNT(cust_email) AS num_cust
FROM Customers;

MAX()函数

MAX()函数返回指定列中的最大值。

SELECT MAX(prod_price) AS max_price
FROM Products;

MIN()函数

MIN() 函数返回指定列中的最小值。

SELECT MIN(prod_price) AS min_pirce
FROM Products；

SUM()函数

返回指定列值的总和。

SELECT SUM(quantity) AS items_ordered
FROM OrderItems
WHERE order_num =20050;

数据分组

创建分组

GROUP BY

SELECT vend_id, COUNT(*) AS num_prods
FROM Products
GROUP BY vend_id;

输出

vend_id   num_prods
-------   --------
BRS01     3
DLL01     4
FNG01     1

GROUP BY指示DBMS按照vend_id排序和分组，不必先对每个组的count()进行计算，系统会自动完成。
GROUP BY必须出现在WHERE子句之后，ORDER BY子句之前。

过滤分组

HAVING

通过过滤函数返回值，实现对分组的过滤

SELECT vend_id,COUNT(*) AS num_prods
FROM Products
WHERE prod_price >= 4
GROUP BY vend_id
HAVING COUNT(*) >=2;

分组和排序

ORDER BY和GROUP BY

ORDER BY
对输出进行排序

ORDER BY
对行进行分组，但暑促可能不是分组的顺序

SELECT order_num, COUNT(*) AS items
FORM OrderItems
GROUP BY order_num
HAVING COUNT(*) >=3
ORDER BY items, order_num;

SELECT 子句的执行顺序

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

使用子查询

SELECT cast_id
FROM Orders
WHERE order_num IN (SELECT order_num
                    FROM  OrderItems
                    WHERE prod_id = 'RGAN01');

注意⚠️ 作为子查询的Select 语句只能查询单个列。企图检索多个列将返回错误。

表联结

为什么使用联结

将数据分解为多个表能更有效地存储，更方便地处理，并且可伸缩性更好。

创建联结

SELECT vend_name,prod_name,prod_price
FROM Vendors,Products
WHERE Vendors.id =Products.id;

内联结

基于两个表之间的相等的联结，这种联结也称为内联结（inner join）。

SELECT vend_name,prod_name,prod_price
FROM Vendors INNER JOIN Products
ON Vendors.id =Products.id;

多表联结

SELECT vend_name,prod_name,prod_price,quantity
FROM Vendors,Products,OrderItems
WHERE Vendors.id = Products.id
AND OrderItems.id = Products.id
AND orderNum = '2000007'

注意：性能考虑DBMS在运行时关联指定的每个表，以处理联结。这种处理可能非常耗费资源，因此应该注意，不要联结不必要的表。联结的表越多，性能下降越厉害。

插入数据

INSERT INTO Customers(cust_id,cust_name,cust_address,cust_city,cust_state)
VALUES('10000006','Tony Land','12332 Andy Street','New York','NY');

插入检索出的数据

INSERT INTO Customers(cust_id,cust_name,cust_address,cust_city,cust_state)
SELECT cust_id,cust_name,cust_address,cust_city,cust_state
FROM CustNew；

更新和删除数据

更新数据

基本的UPDATE语句由三部分组成：
要更新的表
列名和它的新直
过滤条件

UPDATE   Customers
SET cust_email = 'kim@163.com'
WHERE cust_id = '10000006';

删除数据

DELETE FROM Customers
WHERE cust_id = '10000006';

创建和操纵表

创建表

CREATE TABLE Products
(
prod_id CHAR(10) NOT NULL,
vend_id CHAR(10) NOT NULL,
prod_name CHAR(50) NOT NULL,
vend_address  ,
);

更新表

添加列

ALTER TABLE Vendors
ADD vend_phone CHAR(20);

删除列

ALTER TABLE Vendors
DROP COLUMN vend_phone;

删除表

DROP TABLE Vendors;

使用视图

视图

视图是虚拟的表。
它们包含的不是数据而是根据需要检索数据的查询。视图提供了一种封装SELECT语句的层次，可用来简化数据处理，重新格式化或保护基础数据。
为什么要使用视图：

重用SQL语句。
简化复杂的SQL操作。在编写查询后，可以方便地重用它而不必知道其基本查询细节。
使用表的一部分而不是整个表。
保护数据。可以授予用户访问表的特定部分的权限，而不是整个表的访问权限。
更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

创建视图

CREATE VIEW ProductsCustomers AS
SELECT cust_name，cust_id，cust_contract，prod_id
FROM Customers，Products，OrderItems
WHERE Customers.cust_id = Products.prod_id
AND Products.order_num = OrderItems.order_num;
AND Customers.cust_address IS NOT NULL

删除视图

DROP VIEW Products

使用存储过程

存储过程简单来说就是为了以后的使用而保存一条或多条Mysql语句的集合。

为什么要使用存储过程

通过把处理封装在容易使用的单元中，简化复杂的操作。
防止错误，需要执行的步骤越多，出错的可能性越大。防止错误保证了数据的一致性。
提高性能。因为使用存储过程比使用单独的SQL语句要快。
综上所诉：简单，安全，高性能。

使用存储过程

执行存储过程

CALL productpricing(@pricelow,@pricehigh,@priceaverage);

其中，执行名为productpricing的存储过程，它计算并返回产品的最低，最高和平均价格。

创建存储过程

CREATE PROCEDURE product pricing()
BEGIN
       SELECT Avg(prod_price) AS priceacerage
       FROM products;
END;

此存储过程名为product pricing，用CREATE PROCEDURE product pricing()语意定义。
BEGIN和END语句用来限定存储过程，过程体本身仅是一个简单的SELECT语句。

说明：创建存储过程时，需要临时更改命令行的分隔符。

DELIMITER //
CREATE PROCEDURE productpricing()
BEGIN
        SELECT Avg(prod_price) AS priceavrage
        FROM products;
END //
DELIMITER;

其中DELIMITER // 告诉命令行命令使用//作为新的语句结束分隔符，可以看到标志存储过程借宿的END定义为END// 而不是END；
如此，存储过程体内的；仍然保持不动，并且正确地传递给数据库引擎。最后，为恢复为原来的语句分隔符，可以使用DELIMITER；

使用存储过程：

CALL PRODUCTPRICING();

输出：

-------------
price average
-------------
16.133571

删除存储过程

DROP PROCEDURE productpricing;

使用参数

以上例子只是一个简单的存储过程，它简单地显示SELECT语句的结果。一般存储过程并不显示结果，而是把结果返回给你制定的变量。
变量（variable）内存中一个特定的位置，用来临时存储数据。

CREATE PROCEDURE productpricing(
        OUT priceL DECIMAL(8,2),
        OUT priceH DECIMAL(8,2),
        OUT priceA DECIMAL(8,2)
)
BEGIN
        SELECT Min(prod_price)
        INTO priceL
        FROM products;
        SELECT Max(prod_price)
        INTO priceH
        FROM products;
        SELECT Avg(prod_price)
        INTO priceA
        FROM products;
END;

MySQL支持IN（传递给存储过程）、OUT（从存储过程传出，如这里所用）和INOUT（对存储过程传入和传出）类型的参数。存储过程的代码位于BEGIN和END语句内，如前所见，它们是一系列SELECT语句，用来检索值，然后保存到相应的变量（通过指定INTO关键字）。

调用存储过程时，必须指定对应的变量名。注意：所有MySQL变量都必须以@开始。

 CALL productpricing(
                    @pricelow,
                    @pricehigh,
                    @priceaverage
);

检索三个值可如下进行：

SELECT @pricelow,@pricehigh,@priceaverage;

输出

IN和OUT的使用

CREATE PROCEDURE ordertotal(
            IN number INT,
            OUT ototal DECIMAL(8,2)
)
BEGIN
            SELECT Sum(item_price*quantity)
            FROM orderitems
            WHERE orderNum = number
            INTO ototal;
END;

调用这个存储过程，显示这个合计：

CALL ordertotal(200005,@total);
SELECT @total;

游标

游标（cursor）是一个存储在MySQL服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。
游标主要用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或做出更改。
只能用于存储过程 不像多数DBMS, MySQL游标只能用于存储过程（和函数）。

使用游标

使用游标涉及几个明确的步骤。

在能够使用游标前，必须声明（定义）它。这个过程实际上没有检索数据，它只是定义要使用的SELECT语句。
一旦声明后，必须打开游标以供使用。这个过程用前面定义的SELECT语句把数据实际检索出来。
对于填有数据的游标，根据需要取出（检索）各行。
在结束游标使用时，必须关闭游标。在声明游标后，可根据需要频繁地打开和关闭游标。在游标打开后，可根据需要频繁地执行取操作。

创建游标

定义

CREATE PROCEDURE processorder()
BEGIN
    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;
END;

2.打开游标

OPEN ordernumbers;

使用游标
在一个游标被打开后，可以使用FETCH语句分别访问它的每一行。FETCH指定检索什么数据（所需的列），检索出来的数据存储在什么地方。它还向前移动游标中的内部行指针，使下一条FETCH语句检索下一行（不重复读取同一行）。

CREATE PROCEDURE processorders()
BEGIN
    DECLARE o INT;

    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;

    OPEN ordernumbers;

    FETCH ordernumbers INTO o;

    CLOSE  ordernumbers;
END;

关闭游标

CLOSE ordernumbers；

索引

索引是一种与表有关的结构，它的作用相当于书的目录，可以根据目录中的页码快速找到所需的内容。
当表中有大量记录时，若要对表进行查询，没有索引的情况是全表搜索：将所有记录一一取出，和查询条件进行对比，然后返回满足条件的记录。这样做会执行大量磁盘 I/O 操作，并花费大量数据库系统时间。
而如果在表中已建立索引，在索引中找到符合查询条件的索引值，通过索引值就可以快速找到表中的数据，可以大大加快查询速度。

对一张表中的某个列建立索引，有以下两种语句格式：

ALTER TABLE 表名字 ADD INDEX 索引名(列名)；
CREATE INDEX 索引名(列名) ON 表名字；

如：

ALTER TABLE employee ADD INDEX idx_id(id);
CREATE INDEX idx_name(name) ON employee;

查看索引的语句是：

SHOW INDEX FROM 表名字；

在使用 SELECT 语句查询的时候，语句中 WHERE 里面的条件，会自动判断有没有可用的索引。
比如有一个用户表，它拥有用户名(username)和个人签名(note)两个字段。其中用户名具有唯一性，我们给用户名加上一个唯一索引；个性签名不加任何索引。
这时候，如果你要查找某一用户，使用语句 select * from user where username=? 和 select * from user where note=? 性能是有很大差距的，对建立了索引的用户名进行条件查询会比没有索引的个性签名条件查询快几倍，在数据量大的时候，这个差距只会更大。

管理事务处理

事务处理

事务处理（transaction processing）可以用来维护数据库的完整性，它保证成批的MySQL操作要么完全执行，要么完全不执行。
利用事务处理，可以保证一组操作不会中途停止，它们或者作为整体执行，或者完全不执行（除非明确指示）。如果没有错误发生，整组语句提交给（写到）数据库表。如果发生错误，则进行回退（撤销）以恢复数据库到某个已知且安全的状态。
下面是关于事务处理需要知道的几个术语：
❑ 事务（transaction）指一组SQL语句；
❑ 回退（rollback）指撤销指定SQL语句的过程；
❑ 提交（commit）指将未存储的SQL语句结果写入数据库表；
❑ 保留点（savepoint）指事务处理中设置的临时占位符（place-holder），你可以对它发布回退（与回退整个事务处理不同）。

控制事务处理

开始事务

start transaction

使用ROLLBACK
ROLLBACK命令用来回退（撤销）MySQL语句:

SELECT * FROM ordertotals;
START TRANSACTION;
DELETE FROM ordertotals;
SELECT * FROM ordertotals;
ROLLBACK;
SELECT * FROM ordertotals;

首先执行一条SELECT以显示该表不为空。
然后开始一个事务处理，用一条DELETE语句删除ordertotals中的所有行。
另一条SELECT语句验证ordertotals确实为空。
这时用一条ROLLBACK语句回退STARTTRANSACTION之后的所有语句，最后一条SELECT语句显示该表不为空。
显然，ROLLBACK只能在一个事务处理内使用（在执行一条START TRANSACTION命令之后）。

事务处理用来管理INSERT、UPDATE和DELETE语句。你不能回退CREATE或DROP操作。

使用COMMIT

一般的MySQL语句都是直接针对数据库表执行和编写的。这就是所谓的隐含提交（implicitcommit），即提交（写或保存）操作是自动进行的。
在事务处理块中，提交不会隐含地进行。为进行明确的提交，使用COMMIT语句。

STRART TRANSACTION;
DELETE FROM orderitems WHERE order_num = 20010;
DELETE FROM orders WHERE order_num = 20010;
COMMIT;

在这个例子中，从系统中完全删除订单20010。因为涉及更新两个数据库表orders和orderitems，所以使用事务处理块来保证订单不被部分删除。最后的COMMIT语句仅在不出错时写出更改。如果第一条DELETE起作用，但第二条失败，则DELETE不会提交（实际上，它是被自动撤销的）。

使用保留点

简单的ROLLBACK和COMMIT语句就可以写入或撤销整个事务处理。但是，只是对简单的事务处理才能这样做，更复杂的事务处理可能需要部分提交或回退。
为了支持回退部分事务处理，必须能在事务处理块中合适的位置放置占位符。这样，如果需要回退，可以回退到某个占位符。这些占位符称为保留点。为了创建占位符，可如下使用SAVEPOINT语句：

SAVEPOINT delete1;

每个保留点都取标识它的唯一名字，以便在回退时，MySQL知道要回退到何处。为了回退到本例给出的保留点，可如下进行：

ROLLBACK TO delete1;

保留点在事务处理完成（执行一条ROLLBACK或COMMIT）后自动释放。自MySQL 5以来，也可以用RELEASE SAVEPOINT明确地释放保留点。

安全管理

访问控制

MySQL服务器的安全基础是：用户应该对他们需要的数据具有适当的访问权，既不能多也不能少。换句话说，用户不能对过多的数据具有过多的访问权。

创建用户账号

为了创建一个新用户账号，使用CREATE USER语句：

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

username：你将创建的用户名
host：指定该用户在哪个主机上可以登陆，如果是本地用户可用localhost，如果想让该用户可以从任意远程主机登陆，可以使用通配符%
password：该用户的登陆密码，密码可以为空，如果为空则该用户可以不需要密码登陆服务器
例子：
CREATE USER 'dog'@'localhost' IDENTIFIED BY '123456';
CREATE USER 'pig'@'192.168.1.101_' IDENDIFIED BY '123456';
CREATE USER 'pig'@'%' IDENTIFIED BY '123456';
CREATE USER 'pig'@'%' IDENTIFIED BY '';
CREATE USER 'pig'@'%';

重命名用户

RENAME USER olderName to newName;

删除用户

DORP USER newName;

SQL必知必会

检索数据

搜索并去重【DISTINCT】：

限制结果【LIMIT】：

使用注释：

排序检索数据

排序检索【ORDER BY】：

指定排序方向【DESC：降序】【ASC：升序】：

过滤数据

使用WHERE子句【WHERE】：

范围值检查【BETWEEN】：

高级数据过滤

组合WHERE子句：AND操作符，OR操作符

求值顺序：SQL（像多数语言一样）在处理OR操作符前，优先处理AND操作符。

IN操作符

NOT操作符：

用通配符进行过滤

百分号（%）通配符

下划线（_）通配符

聚集函数

AVG()函数

COUNT()函数

MAX()函数

MIN()函数

SUM()函数

数据分组

创建分组

GROUP BY

过滤分组

HAVING

分组和排序

SELECT 子句的执行顺序

使用子查询

表联结

为什么使用联结

创建联结

内联结

多表联结

插入数据

插入检索出的数据

更新和删除数据

更新数据

删除数据

创建和操纵表

创建表

更新表

使用视图

视图

创建视图

删除视图

使用存储过程

为什么要使用存储过程

使用存储过程

删除存储过程

使用参数

IN和OUT的使用

游标

使用游标

创建游标

索引

管理事务处理

事务处理

控制事务处理

使用COMMIT

使用保留点

安全管理

访问控制

创建用户账号

重命名用户

删除用户

猜你喜欢

热点阅读