• 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:Desktopimg_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:Desktopimg_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:Desktopimg_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";
  • 相关阅读:
    pdf工具类之合并pdf文件
    Java 处理不可见特殊字符的坑
    将本地的jar包加到maven中，pom添加依赖
    SQLServer常用个技巧（一）：根据某字符截取后面的字符串，String转int
    java下载文件写的工具类
    使用SWFTools将pdf转成swf
    使用openOffice将office文件转成pdf
    poi处理excel基本操作时写的工具类
    poi处理excel的合并单元格写的工具类,支持xlsx和xls
    java有关正则表达式的工具方法集合1
  • 原文地址：https://www.cnblogs.com/ma-liner/p/14196603.html
Copyright © 2020-2023  润新知