SQL表操作常用命令以及使用场景

Autumnn
443 阅读9分钟

SQL语句的执行顺序

  • FROM <left_table>

  • ON <join_condition>

  • <join_type> JOIN <right_table>

  • WHERE <where_condition>

  • GROUP BY <group_by_list>

  • HAVING <having_condition>

  • SELECT

  • DISTINCT

  • ORDER BY <order_by_list>

DISTINCT Statement

对查询得到的数据进行去重

SELECT DISTINCT column1, column2, ...
FROM table_name;

INSERT INTO

The INSERT INTO statement is used to insert new records in a table.

  • 指定要在哪些指定的column插入对应的数据

INSERT INTO table_name (column1, column2, column3) VALUES (value1, value2, value3);

  • 但是如果我们给表里面的所有column都插入数据的话，就无需指定column，但是一定要保证插入的value的次序和表中column的顺序一致

INSERT INTO table_name VALUES(value1,value2,value3,...)

SQL NULL Values

Null values 并不等同于零值或者全是空格的字符串，就好像真空和空气一样，虽然空气也是不可见，但是它是确切存在的，而真空是什么都没有 is null就是找值为空的column，is not null就是找值不为空的column

SELECT column_names FROM table_name WHERE column_name IS (NOT) NULL;

UPDATE

update 通常用来更新数据库表中已经存在的数据，如果我们没有使用where 语句指定修改哪几行数据的话，update会把指定列的所有数据都修改

UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;

DELETE

delete 通常用来删除表中的数据，正好和insert into相反，和update一样，如果我们忽略了后面的where语句，那么delete语句会将所有的数据都删除掉，但对表结构不会造成影响

DELETE FROM table_name WHERE condition;

SELECT TOP, LIMIT, FETCH FIRST , ROWNUM

上面的四个语句其实作用都是一样的——查询部分数据，只不过是在不同数据库下使用而已，在一张表的数据量很大的时候，如果一次查询全量会影响性能，所以可以使用select top或者limit... 查询部分数据

因为现在大部分都是使用mysql，所以我在这里就只列出mysql的写法，其中offset是指从哪一条数据开始选取，如果不写就是默认从第一条数据开始

SELECT column_name(s) FROM table_name WHERE condition LIMIT offset,number;

MIN() and MAX()

  1. min()返回当前选取数据的最小值
  2. max()返回当前选取数据的最大值

SELECT MIN(column_name) FROM table_name WHERE condition;

SELECT MAX(column_name) FROM table_name WHERE condition;

COUNT(), AVG() and SUM()

  • COUNT()返回符合我们筛选条件的数据的数量，会忽略到NULL数据

SELECT COUNT(column_name) FROM table_name WHERE condition;

  • AVG()返回符合条件的数据的平均值（选取的数据必须是数字类型的）,如果数据为NULL，会被自动忽略

SELECT AVG(column_name) FROM table_name WHERE condition;

  • SUM()返回符合条件的数据的总数（选取的数据必须是数字类型的）

SELECT SUM(column_name) FROM table_name WHERE condition;

LIKE Operator

LIKE操作符和where操作组合使用，主要功能就是筛选出特定样式的数据

  • like操作主要有两个重要的符号——%()和_(?)，有些数据库也会使用和？，而且这两个符号是可以组合使用的

SELECT column1, column2, ... FROM table_name WHERE columnN LIKE pattern;

下图是一些使用场景，我们可以看到_其实就类似一个占位符，也就是说有_就代表这个位置必须要有字符，而%就好比一个万能符，既可以代表多个字符，也可以代表0个字符

image.png

SQL Wildcards（通配符）

上面的LIKE操作里我们提及到了%和_通配符，那么除了这两个之外，其实Mysql里还有很多其他的通配符

image.png

IN Operator

IN操作允许我们在写where语句时同时使用多个条件，可以简单理解为or操作的加强版,in 语句不仅可以写入多个value作为筛选条件，还可以写入select语句作为筛选条件

SELECT column_name(s) FROM table_name WHERE column_name IN (value1, value2, ...);

SELECT column_name(s) FROM table_name WHERE column_name IN (select statement);

BETWEEN Operator

BETWEEN操作允许我们筛选指定范围内的数据,而且这个范围是闭区间——[value1,value2],并且查询出的结果是自动从小到大排列好的

SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;

而且between操作可以和in操作组合使用，不过between必须先于in

SELECT * FROM Products WHERE Price BETWEEN 10 AND 20 AND CategoryID NOT IN (1,2,3);

SQL Joins

Join操作其实就是允许我们在查询的时候关联多个表进行查询

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate FROM Orders INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

  • (INNER) JOIN: 返回两个表中都具有的匹配值的记录
  • LEFT (OUTER) JOIN: 返回左边的表的全部数据以及右边的表具有匹配值的数据
  • RIGHT (OUTER) JOIN: 返回右边的表的全部数据以及左边的表具有匹配值的数据
  • FULL (OUTER) JOIN: 返回所有的数据只要左边的表或者右边的表具有匹配值

image.png

如何关联多个表？,其实就是一个嵌套，先关联两个表，然后再继续关联后续的表

SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName
FROM((OrdersINNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID)
INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID);

 Self Join

和上述的四个join操作不同，self join关联的表是自己本身，但是需要使用不同的别名

SELECT column_name(s) FROM table1 T1, table1 T2 WHERE condition;

UNION Operator

UNION操作是对多个select语句的结果集进行一个去重组合，如果想显示重复的数据，也可以使用UNION ALL

SELECT column_name(s)  FROM table1
UNION(ALL)
SELECT column_name(s)  FROM table2;

下面这条sql会查询出Customers表和Suppliers表里的City，并将这两行数据都整合在一列——city列里， UNION会对数据去重，如果不想去重，可以使用 UNION ALL

SELECT City FROM Customers
UNION
SELECT City FROM Suppliers
ORDER BY City;

GROUP BY Statement

group by语句通常是用特定的条件将重复的数据整合在一起，而且可以和count(),sum()...等方法组合使用

SELECT column_name
FROM table_name
WHERE condition
GROUP BY column_name
ORDER BY column_name;

下面这条sql的含义是——列出Customers表里各个国家的消费者数量和他们的国家，并按照消费者数量从高到低排序。

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
ORDER BY COUNT(CustomerID) DESC;

HAVING Clause

Having其实和Where很像，因为where语句中不可以使用函数，所以出现了Having

下面的语句要找出employee里lastname为'Davolio'或'Fuller'并且完成订单数大于25的employee

SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders
FROM Orders
INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
WHERE LastName = 'Davolio' OR LastName = 'Fuller'
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 25;

EXISTS Operator

EXISTS语句用来测试子查询里是否有对应的数据，然后再返回对应的数据

下面的语句返回Supplier表里拥有产品价格小于20的供应商名字

SELECT SupplierName
FROM Suppliers
WHERE  EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.supplierID AND Price < 20);

ANY and ALL Operators

select * from Table1 t1 where t1.Col1 < ANY(select value from Table2)

ANY和ALL操作符很类似，允许使用单个值与多个值进行比较（<,>,==,<>等），有一点不同的是，以上面的sql为例子，any是只要t1.Col1比后面的select出来的多个value的其中一个要小，那么就可以返回；但是all是要求t1.Col1比后面select出来的多个value都要小才会返回

总结一下就是：

  • any:1<(0,2,3,4)=true
  • all:1<(-1,-2,0,-3)=true else false

SELECT INTO Statement

select into语句其实就是将我们查询的数据挪动到一张新的表里去，可以用作副本数据,下面的[IN external_database]其实就是指定新表存储的数据库名，如果不指定就默认是当前数据库，而且不仅可以用where语句，我们还可以使用联表查询（join）

SELECT column1, column2, column3, ...
INTO newtable [IN external_database]
FROM oldtable
WHERE condition;

SELECT Customers.CustomerName, Orders.OrderID
INTO CustomersOrderBackup2017
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

上面的sql首先联表查询，将Customers表和Orders表通过CustomerID这个键关联起来，然后再查询出这两张表里的CustomerName和OrderID，将其复制到表CustomersOrderBackup2017

INSERT INTO SELECT Statement

insert into select 语句不仅可以复制数据，还会将这些数据插入到另外一张表里去，但是数据的类型必须要匹配正确

INSERT INTO Customers (CustomerName, City, Country)
SELECT SupplierName, City, Country FROM Suppliers
WHERE Country='Germany';

上面的sql是将Suppliers表里所有Country为Germny的SupplierName，City，Country信息插入到Customers表里的指定列

CASE Statement

这个其实就是sql里的switch语句，在遇到第一个为true的判断语句时就返回结果

SELECT OrderID, Quantity,
CASE
    WHEN Quantity > 30 THEN 'The quantity is greater than 30'
    WHEN Quantity = 30 THEN 'The quantity is 30'
    ELSE 'The quantity is under 30'
END AS QuantityText
FROM OrderDetails;

上面的sql语句就是将OrderDetails表里Quantity大于30，等于30和小于30的数据做了一个分类区分

我们还可以用case语句来做子查询,下面这条sql语句的含义是——如果city为空就用Country来排序，否则用city

SELECT CustomerName, City, Country FROM Customers
ORDER BY (CASE
WHEN City IS NULL THEN Country
ELSE City
END);

NULL Functions

在我们查询数据的时候，可能会在写sql的时候就预先把数据进行一些运算处理，但是假设有些值为null怎么办，我们可不可以用0或者指定的值去替换达到方便计算的目的呢？——当然可以

SELECT ProductName, UnitPrice * (UnitsInStock + IFNULL(UnitsOnOrder, 0))
FROM Products;

上面的SQL语句出现了一个函数——IFNULL(UnitsOnOrder, 0)，这个函数的作用是，如果此时的UnitsOnOrder是null，就用0来将其替代，这个0也可以指定为其他数字

Stored Procedures

Stored Procedures可以看作是一个函数，定义完之后可以在查询数据时重复执行

1.创建

CREATE PROCEDURE SelectAllCustomers @City nvarchar(30), @PostalCode nvarchar(10)\
AS
SELECT * FROM Customers WHERE City = @City AND PostalCode = @PostalCode
GO;

2.执行

EXEC SelectAllCustomers @City = 'London', @PostalCode = 'WA1 1DP';

有了procedure，我认为可以把一些高频出现的sql语句封装起来，提高效率

GROUP_CONCAT Statement

GROUP_CONCAT会将我们分组得到的非空数据联结成一个字符串形式的数据返回，如果有一组里的数据都为空，那么它也会返回NULL

SELECT pub_id,GROUP_CONCAT(cate_id) FROM book_mast GROUP BY pub_id;

如果想对联结的数据去重，只需要再在括号里面加上distinct关键字即可

UPPER,LOWER,CONCAT AND SUBSTRING

  • UPPER():将字母全部变成大写
  • LOWER():将字母全部变成小写
  • CONCAT():将多个字符串组合成一个字符串
  • SUBSTRING(col_name,start,end):将长字符串按区间[start,end]截断

image.png

PIVOT diagram and UNPIVOT diagram

当我们想将一列数据用多个列显示或者将多个列的数据组合成一列的时候，我们可以使用PIVOT和UNPIVOT

但是mysql是不支持这两个方法的，所以在mysql里只能使用union all来替代

# Write your MySQL query statement below
select product_id,'store1'   store , store1  price
from Products
where store1 is not NULL
union all
select product_id,'store2' store , store2 price
from Products
where store2 is not NULL
union all
select product_id,'store3' store , store3 price
from Products
where store3  is not NULL;

image.png

image.png

TRUNCATE和ROUND

  • TRUNCATE:作为函数的时候，TRUNCATE其实是用来自定义保留几位小数的，比如TRUNCATE(15.678,2)=15.67
  • ROUND:而ROUND()其实也是用来保留小数的，唯一的区别在与而ROUND()是根据四舍五入来保留的，而TRUNCATE()是直接省略后面的数字

IF FUNCTION

mysql可以使用if()来进行流程控制，具体的用法如下

if (expr,val1,val2)
SELECT IF(500<1000, "YES", "NO") // 输出yes

当expr为true,大于0或者不为空的时候，会返回val1，否则就返回val2

事务

简单来说，事务代表一系列操作要么同时成功，要么同时失败，假设我们现在要使用sql语句实现一个转账操作，即id为1的用户给id为2的用户转账100元

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

使用BEGIN开启一个事务，使用COMMIT提交一个事务，这种事务被称为显式事务

如果多条sql语句想要被当作一个事务执行，那么必须使用显式事务

有些时候我们想要让这个事务执行失败，可以使用ROLLBACK

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
ROLLBACK;
avatar
文章
阅读
粉丝