SQL语法快速学习

文章目录

SQL Database

CREATE/DROP DB

  • 创建/删除数据库

  • 语法:

    -- 创建和删除
CREATE DATABASE database_name;
DROP DATABASE database_name;
BACKUP
  •   -- 备份数据库
  BACKUP DATABASE database_name  TO DISK = "filepath";
  -- 差异备份(只备份不同的部分)
  BACKUP DATABASE database_name TO DISK ="fielpath"
  WITH DIFFERENTIAL;
CREATE/DROP TABLE
  •   -- 创建表
  CREATE TABLE table_name(
      column1 datatype,
      column2 datatype,
      column3 datatype,
      ...
  );
  -- 例子
  CREATE TABLE Persons(
      PersonID int,
      Name varchar(255),
      Address varchar(255),
  );
  --使用其他表创建表
  CREATE TABLE new_table_name AS
  SELECT column1,column2,...
  FROM exist_table_name
  WHERE ...;
  -- 例子
  CREATE TABLE TestTable AS
  SELECT customername,contactname
  FROM customres;
  -- 删除表(表也不存在了)
  DROP TABLE table_name;
  -- 删除表（只删除其中的数据）
  TRUNCATE TABLE table_name;
ALTAER
  •   --添加字段
  ALTER TABLE table_name ADD column_name datatype;
  -- 删除字段
  ALTER TABLE table_name DROP column_name;
  -- 修改字段
  /*SQL Server OR MS Access*/
  ALTER TABLE table_name
  ALTER COLUMN column_name datatype;
  /*MySQL*/
  ALTER TABLE table_name
  MODIFY column_name datatye;
CONSTRAINT(约束)

  • 给数据指定规则，提高表中数据的准确性和可靠性

  •   CAREATE TABLE table_name(
      column1 datatype constraint,
      column2 datatype constraint,
      column3 datatype constraint,
      ...
  );
  
  OR
  ALTAER TABLE table_name ADD UNIQUE (columu_name);
  
  -- 多字段约束
  ALTER TABLE Persons ADD CONSTRAINT UC_Person UNIQUE (ID,LastName);
  
  --删除nuique约束
  /*mysql*/
  ALTER TABLE Persons
  DROP INDEX constraint_name;
  /*other*/
  ALTER TABLE Persons
  DROP CONSTRAINT constraint_name; 
  
  
  --常用的一些约束
  /*
  NOT NULL -确保表中没有null值
  UNIQUE   -确保没有重复的值
  PRIMARY KEY -NOT NULL 和 UNIQUE的组合,唯一标识每一行在一个表中
  FOREIGN KEY -唯一标识一行在另一个表中
  CHECK     -确保表中的值满足特定的条件
  DEFAULT   -当没有指定值时设置一个默认值
  INDEX     -快速创建和检索表中的数据
  */

    说明：具有两个层级 字段级和表级

CREATER INDEX

  • 给数据创建索引，便于更快查询，但在更新一个带有索引的数据库时需要更多时间

  •   -- 创建索引
  CREATE INDEX index_name
  ON table_name (column1,column2,...);
  
  -- 创建唯一索引
  CREATE UNIQUE INDEX index_name
  ON tabel_name (column1,column2,...);
  
  --删除索引
  /*MySQL*/
  ALTER TABLE table_name
  DROP INDEX index_name;
AUTO INCREMENT

  • 当插入一个记录时自动生成一个unique的数

  •   /*MySQL*/
  CREATE TABLE Persons(
      PersonID int NOT NULL AUTO INCREMENT,
      LastName varchar(255) NOT NULL,
      Age int,
      PRIMARY KEY (PersonID)
  );
DATE
  •   /**MySQL/
  DATE -format:YYYY-MM-DD
  DATETIME -format:YYYY-MM-DD HH:MI:SS
  TIMESTAMP -format:YYYY-MM-DD HH:MI:SS
  YEAR -format:YYYY/YY
VIEW

  • 基于结果集创建的一个虚拟表

  •   CREATE VIEW view_name AS
  SELECT columnb,column2,...
  FORM table_name
  WHERE condition;
  
  -- example
  CREATE VIEW [Brazil Customers] AS
  SELECT CustomerName,ContactName
  FROM Customers
  WHERE Country="Brazil";
  
  -- 使用
  SELECT * FROM [Brazil Customers];
  
  --更新
  CREATE OR REPLACE VIEW view_name AS
  SELECT column1,column2,...
  FORM table_name
  WHERE condition;
  
  --删除
  DORP VIEW view_name;
INJECATION(注入)
  • 一种代码注入技术，可以侵入/破坏数据库，可以通过参数来防控

SQL Statement

LIKE

两个通配符

  • % -可以代表任意个字符
  • _ -代表一个字符

注意：不同数据库会有差别

语法：

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

例子

LIKE OperatorDescription
WHERE CustomerName LIKE ‘a%’Finds any values that start with “a”
WHERE CustomerName LIKE ‘%a’Finds any values that end with “a”
WHERE CustomerName LIKE ‘%or%’Finds any values that have “or” in any position
WHERE CustomerName LIKE ‘_r%’Finds any values that have “r” in the second position
WHERE CustomerName LIKE ‘a__%’Finds any values that start with “a” and are at least 3 characters in length
Wildcards(通配符)

MS Access适用

SymbolDescriptionExample
*Represents zero or more charactersbl* finds bl, black, blue, and blob
?Represents a single characterh?t finds hot, hat, and hit
[]Represents any single character within the bracketsh[oa]t finds hot and hat, but not hit
!Represents any character not in the bracketsh[!oa]t finds hit, but not hot and hat
-Represents a range of charactersc[a-b]t finds cat and cbt
#Represents any single numeric character2#5 finds 205, 215, 225, 235, 245, 255, 265, 275, 285, and 295

SQL Server适用

SymbolDescriptionExample
%Represents zero or more charactersbl% finds bl, black, blue, and blob
_Represents a single characterh_t finds hot, hat, and hit
[]Represents any single character within the bracketsh[oa]t finds hot and hat, but not hit
^Represents any character not in the bracketsh[^oa]t finds hit, but not hot and hat
-Represents a range of charactersc[a-b]t finds cat and cbt
IN

  • in操作符允许你在where子句中指定多个值

  • 语法：

    SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);
for example:
//从customers表中选择国家为uk或FRANCE的行
SELECT * FROM Customers WHERE Country IN('UK','FRANCE');

    or

    SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT STATEMENT);
eg:
//从customers表和suppliers表中选择相同的国家
SELECT * FROM Customers WHERE Country
IN (SELECT Country FROM Suppliers);
BETWEEN

  • 选择一个范围的数据

  • 语法：

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

  • 实例

    SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;

SELECT * FROM Products
WHERE Price NOT BETWEEN 10 AND 20;

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

SELECT * FROM Products
WHERE ProductName BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni'
ORDER BY ProductName;

SELECT * FROM Orders
WHERE OrderDate BETWEEN #01/07/1996# AND #31/07/1996#;
or
SELECT * FROM Orders
WHERE OrderDate BETWEEN '1996-07-01' AND '1996-07-31';
Alias

  • 给表、表中的一列或者几列取一个临时别名(仅在查询时期间有效)便于操作

  • 语法

    //aliase column
SELECT column_name AS alias_name
FROM table_name;
//aliase table
SELECT column_name(s)
FROM table_name AS alias_name;

  • eg.

    SELECT CustomerID AS ID, CustomerName AS Customer
FROM Customers;

//取带有空格的别名的时候需要用‘’或这[]
SELECT CustomerName AS Customer, ContactName AS [Contact Person] FROM Customers;

//几列取一个别名myasql语法。不同收据库有所不同
SELECT CustomerName, CONCAT(Address,',',PostalCode,',',City,', ',Country) AS Address FROM Customers;
join

  • inner join(返回能够匹配的记录)

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ZP8Lzoqt-1577436895531)(D:\Desktop\img_innerjoin.jpg)]

    语法：

    SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

    eg.

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

//三个表的组合
SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName
FROM ((Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID)
INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID);

  • left join(显示左边的表以及和右边匹配的表)

    在某些数据库中又叫left outer join,以下各个join同样适用

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6xo4RcGI-1577436895533)(D:\Desktop\img_leftjoin.gif)]

    语法：

    SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

    eg.

    SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerName;

  • right join(与left join 作用相反)

  • full join(返回两个表的整体以及返回各自不同的部分)

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-XNIHxLFl-1577436895534)(D:\Desktop\img_fulljoin.gif)]

    语法：

    SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;

    eg.

    SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;

  • self join(表与自身的链接)(表示不太懂)

    语法：

    SELECT column_name(s)
FROM table1 T1, table1 T2//t1 t2是相同表的化名
WHERE condition;
uinon

  • 语法：

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

SELECT column_name(s) FROM table1
UNION ALL//加all不会合并相同的项
SELECT column_name(s) FROM table2;
Group By

  • 将查询结果分组

  • 语法

    SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);

  • eg.

    SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country;
--统计每个国家的游客人数
Having

  • 由于where不能与聚合函数一起使用，having解决了这个问题

  • 语法：

    SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

  • eg.

    --统计游客人数大于等于5国家
SELECT COUNT(CustomerID),Country
FROM Customers
GROUP BY Country
Having Count(CustomerID)>=5;
Exist

  • 测试子查询中是否存在某一个记录

  • 语法：

    SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);

  • eg.

    --列出价格低于20的所有供货商
SLECT SupplierName
FROM Suppliers
WHERE EXISTS
(SELECT ProductName FROM Products WHERE Products.SupplierID=Suppliers.supplierID AND price<20);
Any/All

  • 一般和where/having语句一起使用，Any用于返回true如果子查询有一条符合条件；All同理

    注意：必须要有比较运算符

  • 语法：

    SELECT column_name(s)
FROM table_name
WHERE column_name operator ANY
(SELECT column_name FROM table_name WHERE condition);

  • eg.

    SELECT ProductName
FROM Products
WHERE ProductID = ANY(SELECT ProductID FROM OrderDetails WHERE Quantily=10);
SLECT INTO

  • 把一个表中的数据拷贝到另一个表中

  • 语法：

    SELECT *
INTO newtable [IN externaldb]
FROM oldtable
WHERE condition;

  • eg.

    --把Customers表备份到另外一个数据库的CustomerBackup2017表中
SELECT * INTO CustomersBackup2017 IN 'Backup.mdb'
FROM Customers;
INSERT INTO SELECT

  • 从一个表中复制部分/全部数据并插入到另外一个数据表中去

  • 语法：

    INSERT INTO table2(cloumn1,column2,...)
SELECT * FROM table1
WHERE Condition;

  • eg.

    --复制国家为‘Germany'的suppliers的某些数据到Customers中去
INSERT INTO Customers (CustomerName, City, Country)
SELECT SupplierName, City, Country FROM Suppliers
WHERE Country='Germany';
CASE

  • 与C语言中的case相似

  • 语法：

    CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultn
ELSE result
END;

  • eg.

    -- 根据quantity的不同新增一个字段
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;
FULL Functions

  • 判断是否为空的函数(自我理解)

  • MySQL实例(跟数据库有关)

    -- IFNull()函数
select ProductName,UnitPrice *(UnitInStock + IFNull(UnitsOnOrder,0)) FROM Products;
or
-- COALESCE()函数
select ProductNam,UnitPrice *(UnitInStock+COALESCE(UnitsOnOrder,0)) FROM Products;
Stored Produces(存储步骤)

  • 对于某些经常使用的sql语句，用另外一个名字来保存。

  • 语法：

    -- 创建
CREATE PROCEDURE procedure_name
AS 
sql_statement
GO;
-- 执行
EXEC procedure_name;

  • eg.

    CREATE PROCEDURE SelectAllCustomers
AS
SELECT * FROM Customers
GO;

EXEC SelectAllCustomers

--------------
-- 一个参数
CREATE PROCEDURE SelectAllCustomers @City nvarchar(30)
AS
SELECT * FROM Customers WHERE City = @City
GO;

EXEC SelectAllCustomers @City = "London";
posted @ 2019-12-27 16:58  pony.ma  阅读(57)  评论(0)  编辑  收藏  举报