常用SQL语句集合

2018-06-18 00:47:22来源:未知 阅读 ()

新老客户大回馈,云服务器低至5折

一、数据定义 
1、创建新数据库:CREATE DATABASE database_name
2、创建新表:CREATE TABLE table_name (column_name datatype,column_name datatype,...)
3、修改数据表:
  添加列:ALTER TABLE table_name ADD column_name datatype
  删除列:ALTER TABLE table_name DROP COLUMN column_name
  修改列:ALTER TABLE table_name ALTER COLUMN column_name datatype
4、删除表:DROP TABLE table_name

 

二、数据操作
选取数据:SELECT 列名称 FROM 表名称
    SELECT * FROM 表名称
过滤重复:SELECT DISTINCT 列名称 FROM 表名称
指定条件:SELECT 列名称 FROM 表名称 WHERE 列 运算符 值
多项条件:SELECT 列名称 FROM 表名称 WHERE 列 运算符 值 AND(OR) 列 运算符 值
排列顺序:SELECT 列名称 FROM 表名称 ORDER BY 列1,列2 DESC(列1升序然后列2倒叙)
插入数据:INSERT INTO 表名称 VALUES (值1, 值2,...)
    INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)
更新数据:UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
删除数据:DELETE FROM 表名称 WHERE 列名称 = 值

 

三、常用SQL语句范例

选取:SELECT LastName,FirstName FROM Persons
去重:SELECT DISTINCT Company FROM Orders
条件:SELECT * FROM Persons WHERE City='Beijing'
AND与:SELECT * FROM Persons WHERE FirstName='Thomas' AND LastName='Carter'
OR或:SELECT * FROM Persons WHERE firstname='Thomas' OR lastname='Carter'
升序:SELECT Company, OrderNumber FROM Orders ORDER BY Company
降序:SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC
插入1:INSERT INTO Persons VALUES ('Gates', 'Bill', 'Xuanwumen 10', 'Beijing')
插入2:INSERT INTO Persons (LastName, Address) VALUES ('Wilson', 'Champs-Elysees')
更新:UPDATE Person SET FirstName = 'Fred' WHERE LastName = 'Wilson'
删除:DELETE FROM Person WHERE LastName = 'Wilson'

限制条数:SELECT * FROM Persons LIMIT 5
字符检索:SELECT * FROM Persons WHERE City LIKE '%BJ%'
  通配符:%:替代一个或多个字符
      _:仅替代一个字符;
      [ABC]:ABC三者之一;
      [^ABC]:除ABC以外
多选条件:SELECT * FROM Persons WHERE LastName IN ('Adams','Carter')
两者之间:SELECT * FROM Persons WHERE LastName BETWEEN 'Adams' AND 'Carter'
指定别名:SELECT po.OrderID, p.* FROM Persons AS p, Product AS po WHERE p.LastName='Adams' AND p.FirstName='John'
联合查询:SELECT Persons.*, Orders.OrderNo FROM Persons, Orders WHERE Persons.Id_P = Orders.Id_P
JOIN联合:SELECT Persons.*, Orders.OrderNo FROM Persons INNER JOIN Orders ON Persons.Id_P = Orders.Id_P
合并结果:SELECT E_Name FROM Employees_China UNION SELECT E_Name FROM Employees_USA

 

标签:

版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点,本站所提供的摄影照片,插画,设计作品,如需使用,请与原作者联系,版权归原作者所有

上一篇:MYSQL-实现sqlserver- row_number() over(partition by order by

下一篇:MySQL备份还原