【SQL server】SQL server基础（二）

一、一些重要的SQL命令

• SELECT - 从数据库中提取数据
• UPDATE - 更新数据库中的数据
• DELETE - 从数据库中删除数据
• INSERT INTO - 向数据库中插入新数据
• CREATE DATABASE - 创建新数据库
• ALTER DATABASE - 修改数据库
• CREATE TABLE - 创建新表
• ALTER TABLE - 变更（改变）数据库表
• DROP TABLE - 删除表
• CREATE INDEX - 创建索引（搜索键）
• DROP INDEX - 删除索引

二、SQL基础

+----+--------------+---------------------------+
| id | name         | age         | country |
+----+--------------+---------------------------+
| 1  | KAY          | 22          | CN      |
| 2  | SKYE         | 15          | CN      |
| 3  | KATE         | 34          | USA     |
| 4  | LORY         | 34          | USA     |
| 5  | JACK         | 66          | EN      |
+----+--------------+---------------------------+

　　1、INSERT INTO实例

　　　　1）无需指定插入数据列名：

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

INSERT INTO DBO.TEST
VALUES(‘7‘,‘casiy‘,20,‘EN‘)           --当没有列名时，values数要与colums数一致（即每个都要赋值）

　　　　2）需要指定插入数据列名：插入指定列数据

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

INSERT INTO DBO.TEST(ID,NAME,AGE)
VALUES(‘6‘,‘LUCK‘,20)

　　　　　　结果　　　　　

　　2.UPDATE

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

1 update [dbo].[Test]
2 set NAME = ‘BEN‘, AGE = 25
3 where ID = ‘7‘

 　　　　*注：当没有where时表中所有数据的name都为ben，age都为25

　　3.DELETE

　　　　DELETE FROM table_name
　　　　WHERE some_column=some_value;　　

DELETE FROM [dbo].[Test]
WHERE ID=‘7‘

            *注当要删除表中内容时可以用　DELETE FROM table_name;或DELETE * FROM table_name;

三、高级

　　1.SQL SELECT TOP

　　　　1）SQL：

　　　　　　SELECT TOP number|percent column_name(s)
　　　　　　FROM table_name;

SELECT TOP 30 PERCENT * FROM [dbo].[Test]                  --选取前30%的数据
SELECT TOP 3 * FROM [dbo].[Test]　　　　　　　　　　　　　　　　--选取前3的数据

 　　　　2）mySQL

　　　　　　SELECT column_name(s)
　　　　　　FROM table_name
　　　　　　LIMIT number;

SELECT *
FROM [dbo].[Test]
LIMIT 3　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　--选取前3的数据

 　　2.通配符

　　　　% 替代一个或多个字符

　　　　_ 仅替代一个字符

　　　　[charlist] 字符列中的任何单一字符

　　　　[^charlist]或者[!charlist] 不在字符列中的任何单一字符

　　　　其中搭配以上通配符可以让LIKE命令实现多种技巧：

　　　　1)LIKE‘Mc%‘ 将搜索以字母 Mc 开头的所有字符串（如 McBadden）。

　　　　2)LIKE‘%inger‘ 将搜索以字母 inger 结尾的所有字符串（如 Ringer、Stringer）。

　　　　3)LIKE‘%en%‘ 将搜索在任何位置包含字母 en 的所有字符串（如 Bennet、Green、McBadden）。

　　　　4)LIKE‘_heryl‘ 将搜索以字母 heryl 结尾的所有六个字母的名称（如 Cheryl、Sheryl）。

　　　　5)LIKE‘[CK]ars[eo]n‘ 将搜索下列字符串：Carsen、Karsen、Carson 和 Karson（如 Carson）。

　　　　6)LIKE‘[M-Z]inger‘ 将搜索以字符串 inger 结尾、以从 M 到 Z 的任何单个字母开头的所有名称（如 Ringer）。

　　　　7)LIKE‘M[^c]%‘ 将搜索以字母 M 开头，并且第二个字母不是 c 的所有名称（如MacFeather）。

　　3.BETWWEN

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

　　　　*带有文本值得BETWEEN操作实例

SELECT *
FROM [dbo].[Test]
WHERE NAME BETWEEN ‘A‘ AND ‘L‘;       --选取name以A和L之间的字母开始的人员数据

　　4.sql别名

　　　　1）列的别名

　　　　　　SELECT column_name AS alias_name
　　　　　　FROM table_name;

SELECT ID as I,NAME as n,CONCAT(AGE,COUNTRY)as info            --CONCAT()as 将age，country和为一列名为ifo
FROM [dbo].[Test] 

 　　　　2）表的命名

　　　　　　SELECT column_name(s)
　　　　　　FROM table_name AS alias_name;

　　　　　　*在下面的情况下，使用别名很有用：

　　　　　　　　在查询中涉及超过一个表

　　　　　　　　在查询中使用了函数

　　　　　　　　列名称很长或者可读性差

　　　　　　　　需要把两个列或者多个列结合在一起

                  for exple:

SELECT w.name, w.url, a.count, a.date 
FROM Websites AS w, access_log AS a 
WHERE a.site_id=w.id and w.name="菜鸟教程";

　　5.SQL连接(JOIN)

+----+--------------+---------------------------+
|s_id | chinese     | english     | 
+----+--------------+---------------------------+
| 1  |　　95        |　　78        | 
| 2  |　　80        |　　66        | 
| 3  |　　66        |　　59        | 
| 5  |　　49        |　　91        |
| 6  |　　90        |　　92        |
| 5  |　　68        |　　NULL      | 
+----+--------------+---------------------------+

　　　　1)INNER JOIN(JOIN)

　　　　　　SQL JOIN 子句用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段。最常见的 JOIN 类型：SQL INNER JOIN（简单的 JOIN）。

　　　　　　 SQL INNER JOIN 从多个表中返回满足 JOIN 条件的所有行。　

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

　　　　　　*TEST 表中的 "id" 列指向 "marks" 表中的字段 "s_id"。上面这两个表是通过 "site_id" 列联系起来的。

　　　　　　然后，如果我们运行下面的 SQL 语句（包含 INNER JOIN）：

SELECT t.ID,t.NAME,m.english,m.chinese
FROM [dbo].[Test] as t
INNER JOIN [dbo].[marks] as m
ON t.ID=m.s_id

　　　　　　*INNER JOIN 关键字在表中存在至少一个匹配时返回行。如果 "TEST" 表中的行在 "marks" 中没有匹配，则不会列出这些行。　　　　

　　　　2)LEFT JOIN

　　　　　　*LEFT JOIN 关键字从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL

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

SELECT t.ID,t.NAME,m.english,m.chinese
FROM [dbo].[Test] as t
LEFT JOIN [dbo].[marks] as m
ON t.ID=m.s_id

　　　　　　结果：

　　　　3)RIGHT JOIN

　　　　　　*RIGHT JOIN 关键字从右表（table2）返回所有的行，即使左表（table1）中没有匹配。如果左表中没有匹配，则结果为 NULL。

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

SELECT t.ID,t.NAME,m.english,m.chinese
FROM [dbo].[Test] as t
RIGHT JOIN [dbo].[marks] as m
ON t.ID=m.s_id

　　　　　　结果：

　　　　4）FULL OUTER JOIN

　　　　　　*FULL OUTER JOIN 关键字只要左表（table1）和右表（table2）其中一个表中存在匹配，则返回行.,FULL OUTER JOIN 关键字结合了 LEFT JOIN 和 RIGHT JOIN 的结果。

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

　　6.UNION

+----+--------------+---------------------------+
| id | name         | age         | country |
+----+--------------+---------------------------+
| 1  | xiaohong         | 22          | CN      |
| 2  | xiaoming         | 15          | CN      |
| 3  | xiaobai          | 34          | CN      |
| 4  | xiaohei          | 34          | CN      |
| 5  | xiaoliu          | 66          | CN      |
+----+--------------+---------------------------+

　　　　*UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

　　　　*请注意，UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。

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

　　　　*默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。

SELECT t.NAME,t.COUNTRY
FROM [dbo].[Test] as t
WHERE t.ID>2
UNION ALL
SELECT m.NAME,m.COUNTRY
FROM [dbo].[Test2] AS m
WHERE m.ID>2

 　　　　结果：

　　　7.SELECT INTO

　　　　　　1)SELECT INTO

　　　　　　　　SELECT INTO 语句从一个表复制数据，然后把数据插入到另一个新表中。

　　　　　　　　SELECT column_name(s)
　　　　　　　　INTO newtable [IN externaldb]
　　　　　　　　FROM table1;

SELECT t.ID,t.NAME,m.english,m.chinese INTO Test3
FROM [dbo].[Test] as t
LEFT JOIN [dbo].[marks] as m
ON t.ID=m.s_id

　　　　　　2)INSERT INTO ...SELECT

　　　　　　　　INSERT INTO SELECT 语句从一个表复制数据，然后把数据插入到一个已存在的表中。 

　　　　　　　　INSERT INTO table2(column_name(s))

　　　　　　　　SELECT column_name(s)
　　　　　　　　FROM table1;

INSERT INTO [dbo].[Test2](ID,NAME)　　　　　　　　　　　　--被插入的表
SELECT t.ID,t.NAME from [dbo].[Test] as t
WHERE ID<3

 　　　　　　　　结果：

　　　　　　*两者区别：前者产生一个新表，后者是在旧表中插入

　　8.CREATE DATABASE

　　　　CREATE DATABASE dbname;

　　9.CREATE TABLE　　

　　　　CREATE TABLE table_name
　　　　(
　　　　column_name1 data_type(size),
　　　　column_name2 data_type(size),
　　　　column_name3 data_type(size),
　　　　....
　　　　);

　　　　column_name 参数规定表中列的名称。data_type 参数规定列的数据类型（例如 varchar、integer、decimal、date 等等）。size 参数规定表中列的最大长度。

　　10.create index

　　　　可以在表中创建索引，以便更加快速高效地查询数据。用户无法看到索引，它们只能被用来加速搜索/查询。

 　　　　1)允许使用重复的值

　　　　　　CREATE INDEX index_name

　　　　　　ON table_name(cloumn_name1,cloumn_name2)

　　　　2）在表上创建一个唯一的索引。不允许使用重复的值：唯一的索引意味着两个行不能拥有相同的索引值

　　　　　　CREATE UNIQUE INDEX index_name

　　　　　　ON table_name(cloumn_name1,cloumn_name2)

　　11.SQL 撤销索引、撤销表以及撤销数据库

　　　　1）撤销索引

　　　　　　sql：

　　　　　　　　DROP INDEX table_name.index_name

　　　　　　mysql：

　　　　　　　　ALTER TABLE tabel_name

　　　　　　　　DROP INDEX index_name

　　　　2)撤销表

　　　　　　DROP TABELE table_name

　　　　3)撤销数据库

　　　　　　DROP DATEBASE database_name

　　　　4)TRUNCATE TABLE 仅需要删除表内的数据，但并不删除表本身

　　　　　　TRUNCATE TABLE table_name

　　12.ALTER

　　　　　ALTER TABLE table_name

　　　　　ADD COLUMN colunm_name

　　　　　DROP COLUMN colunm_name

　　　　　ALTER COLUMN colunm_name datatype

　　13.AUTO-INCREMNET

　　　　1)sql：

　　　　Auto-increment 会在新记录插入表中时生成一个唯一的数字。

CREATE TABLE Persons
(
ID int IDENTITY(1,1) PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY(ID)
)

　　　　MS SQL Server 使用 IDENTITY 关键字来执行 auto-increment 任务。在上面的实例中，IDENTITY 的开始值是 1，每条新记录递增 1。

　　　　提示：要规定 "ID" 列以 10 起始且递增 5，请把 identity 改为 IDENTITY(10,5)。

　　　　要在 "Persons" 表中插入新记录，我们不必为 "ID" 列规定值（会自动添加一个唯一的值

　　　　2）mysql:

CREATE TABLE Persons
(
ID int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (ID)
)

　　　　MySQL 使用 AUTO_INCREMENT 关键字来执行 auto-increment 任务。默认地，AUTO_INCREMENT 的开始值是 1，每条新记录递增 1。

　　　　要让 AUTO_INCREMENT 序列以其他的值起始，请使用下面的 SQL 语法：ALTER TABLE Persons AUTO_INCREMENT=100

　　14.视图

　　　　视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。

　　　　可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句，也可以呈现数据，就像这些数据来自于某个单一的表一样

　　　　1）创建视图 

　　　　　　CREATE VIEW view_name AS
　　　　　　SELECT column_name(s)
　　　　　　FROM table_name
　　　　　　WHERE condition

　　　　2）修改视图

　　　　　　ALTER VIEW view_name AS
　　　　　　SELECT column_name(s)
　　　　　　FROM table_name
　　　　　　WHERE condition

　　　　3)撤销视图

　　　　　　　DROP VIEW view_name AS

四、SQL约束

　　SQL CREATE TABLE + CONSTRAINT 语法

　　CREATE TABLE table_name
　　(
　　column_name1 data_type(size) constraint_name,
　　column_name2 data_type(size) constraint_name,
　　column_name3 data_type(size) constraint_name,
　　....
　　);

　　在 SQL 中，我们有如下约束：

• NOT NULL - 指示某列不能存储 NULL 值。
• UNIQUE - 保证某列的每行必须有唯一的值。
• PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
• FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
• CHECK - 保证列中的值符合指定的条件。
• DEFAULT - 规定没有给列赋值时的默认值。

　　1.NOT NULL

　　　　CREATE TABLE table_name
　　　　(
　　　　column_name1 data_type(size)  NOT NULL,
　　　　column_name2 data_type(size) constraint_name,
　　　　column_name3 data_type(size) constraint_name,
　　　　....
　　　　);　　

　　2.UNIQUE

　　　　1）CREATE TABLE 时的 SQL UNIQUE 约束

　　　　　　CREATE TABLE table_name
　　　　　　(
　　　　　　column_name1 data_type(size) constraint_name ,
　　　　　　column_name2 data_type(size) constraint_name,
　　　　　　....

　　　　　　UNIQUE(column_name)
　　　　　　);　

　　　　　　定义多列的unique约束 UNIQUE(column_name1,column_name2),定义约束并重命名CONSTRAINT new_name UNIQUE(column_name)

　　　　2)ALTER TABLE 时的 SQL UNIQUE 约束

　　　　　　ALTER TABLE table_name
　　　　　　ADD UNIQUE (column_name)

　　　　　　添加多列的unique约束 UNIQUE(column_name1,column_name2),定义约束并重命名CONSTRAINT new_name UNIQUE(column_name)

　　　　3）撤销 UNIQUE 约束

　　　　　ALTER TABLE table_name
　　　　　DROP CONSTRAINT constraintname

　　　　　*mysql:ALTER TABLE table_name
　　　　　　　　DROP INDEX constraintname

　　3.PRIMARY KEY:唯一且不重复，每个都应有个主键

　　　　语法法和UNIQUEe一样

　　　　撤销 PRIMARY KEY 约束

　　　　　ALTER TABLE table_name
　　　　　DROP CONSTRAINT constraintname

　　　　　*mysql:ALTER TABLE table_name
　　　　　　　　DROP PRIMARY KEY

　　4.FOREIGN KEY:指向另一个表的主键

　　　　1）CREATE TABLE 时的FOREIGN KEY 约束

　　　　　　CREATE TABLE table_name2
　　　　　　(
　　　　　　column_name1 data_type(size) constraint_name ,
　　　　　　column_name2 data_type(size) constraint_name,
　　　　　　....

　　　　　　FOREIGN KEY(column_name1) REFERENCES  table1（column_name1)）
　　　　　　);

　　5.CHECK:CHECK 约束用于限制列中的值的范围。

　　　　　语法同上

　　　　　exp1：

ALTER Table [dbo].[Persons]

ADD CHECK(P_Id>3 AND City=‘CN’)       --输入P_ID时必须大于3且CITY=cn

　　6.DEFAULT:插入默认自

　　　　1）CREATE TABLE 时的 SQL DEFAULT 约束

　　　　　　CREATE TABLE table_name2
　　　　　　(
　　　　　　column_name1 data_type(size)  DEFAULT value ,
　　　　　　column_name2 data_type(size) constraint_name,
　　　　　　....

　　　　　　);

　　　　2）ALTER TABLE 时的 SQL DEFAULT 约束

　　　　　　mysql：

　　　　　　　　ALTER TABLE table_name

　　　　　　　　ALTER  column_name SET value

　　　　　　sql:

　　　　　　　　ALTER TABLE table_name

　　　　　　　　ADD DEFUALT value FOR column_name

　　　　3)撤销 DEFAULT 约束

　　　　　　　　ALTER TABLE table_name

　　　　　　　　DROP CONSTRAINT constraintname

五、函数

　　1.日期函数

　　　　1）sql

 　　　　　　DATE - 格式：YYYY-MM-DD

　　　　　　DATETIME - 格式：YYYY-MM-DD HH:MM:SS

　　　　　　SMALLDATETIME - 格式：YYYY-MM-DD HH:MM:SS

　　　　　　TIMESTAMP - 格式：唯一的数字

　　　　2）mysql

　　　　　　　DATE - 格式：YYYY-MM-DD

　　　　　　DATETIME - 格式：YYYY-MM-DD HH:MM:SS

　　　　　　TIMESTAMP - 格式：YYYY-MM-DD HH:MM:SS

　　　　　　YEAR - 格式：YYYY 或 YY

　　2.ISNULL()

　　　　SQL: ISNULL(A,0) 如果A为NULL，则返回0

　　　　MYSQL:IFNULL()或COALESCE

　　3.SQL Aggregate 函数

　　　　SQL Aggregate 函数计算从列中取得的值，返回一个单一的值。

　　　　有用的 Aggregate 函数：

• AVG() - 返回平均值

　　　　SELECT AVG(column_name) AS average From table_name

• COUNT() - 返回行数

　　　　SELECT COUNT(column_name) AS count From table_name

• MAX() - 返回最大值

　　　　SELECT MAX(column_name) AS max From table_name

• MIN() - 返回最小值

　　　　SELECT MIN(column_name) AS min From table_name

• SUM() - 返回总和

　　　　SELECT SUM(column_name) AS sum From table_name

　　4.GROUP BY 分组

　　　　SELECT column_name, aggregate_function(column_name)
　　　　FROM table_name
　　　　WHERE column_name operator value
　　　　GROUP BY column_name;

　　5.HAVING SQL

　　　　中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用

　　　　SELECT column_name, aggregate_function(column_name)
　　　　FROM table_name
　　　　WHERE column_name operator value
　　　　GROUP BY column_name
　　　　HAVING aggregate_function(column_name) operator value;

　　6.SQL Scalar 函数

　　　　SQL Scalar 函数基于输入值，返回一个单一的值　　　　

• UPPER() - 将某个字段转换为大写

　　SELECT UPPER(column_name) FROM table_name;

• LOWER() - 将某个字段转换为小写

　　SELECT LOWER(column_name) FROM table_name;

• MID() - 从某个文本字段提取字符，MySql 中使用

　　SELECT MID(column_name,start[,length]) FROM table_name;

SubString(字段，1，end) - 从某个文本字段提取字符

• LEN() - 返回某个文本字段的长度

　　SELECT LEN(column_name) FROM table_name;  

　　MYSQL: LENGTH()

• ROUND() - 对某个数值字段进行指定小数位数的四舍五入

　　SELECT ROUND(column_name,decimals) FROM table_name;

　　当decimals为空时，无小数

• NOW() - 返回当前的系统日期和时间

　　SELECT NOW() FROM table_name;

• FORMAT() - 格式化某个字段的显示方式

　　SELECT FORMAT(column_name,format) FROM table_name;

　　SELECT name, url, DATE_FORMAT(Now(),‘%Y-%m-%d‘) AS date FROM Websites;

【SQL server】SQL server基础（二）

原文：https://www.cnblogs.com/dhs94/p/10274795.html