SQL server 使用

--插入数据
INSERT INTO 表名称 VALUES (值1, 值2,....)                   --插入一行数据
INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)--插入指定列数据
--更新数据
UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
--删除
DELETE FROM 表名称 WHERE 列名称 = 值 --删除一行
DELETE * FROM table_name            --删除所有行

---

sql server TOP 子句用于规定要返回的记录的数目。

select top 50 * from 表名称;    --取50条
select top 50 percent * from 表名称;    --取50%

模糊查询

SELECT * FROM Persons WHERE City LIKE 'N%'  --%为通配符
SELECT * FROM Persons WHERE City not LIKE 'N%'  --不包含N开头的

通配符	描述
%	替代一个或多个字符
_	仅替代一个字符
[charlist]	字符列中的任何单一字符
[^charlist]或者[!charlist]	不在字符列中的任何单一字符

in操作符可以指定多个值：

SELECT * FROM Persons WHERE LastName IN ('Adams','Carter')

BETWEEN ... AND 会选取介于两个值之间的数据范围。这些值可以是数值、文本或者日期。

Id	LastName	FirstName	Address	City
1	Adams	John	Oxford Street	London
2	Bush	George	Fifth Avenue	New York
3	Carter	Thomas	Changan Street	Beijing
4	Gates	Bill	Xuanwumen 10	Beijing

--BETWEEN '包括' AND '不包括'
SELECT * FROM Persons WHERE LastName BETWEEN 'Adams' AND 'Carter'

• JOIN: 如果表中有至少一个匹配，则返回行
• LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行。即左表数据必须要有，哪怕右表没有
• RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行
• FULL JOIN: 只要其中一个表中存在匹配，就返回行

UNION 操作符：合并两个或多个 SELECT 语句的结果集。
UNION ALL 会返回重复的结果

SELECT E_Name FROM Employees_China
UNION ALL
SELECT E_Name FROM Employees_USA

SELECT INTO 语句从一个表中选取数据，然后把数据插入另一个表中。

-- IN 子句可用于向另一个数据库中拷贝表：
SELECT * INTO Persons IN 'Backup.mdb' FROM Persons
-- 拷贝某些域，可以在 SELECT 语句后列出这些域：
SELECT LastName,FirstName INTO Persons_backup FROM Persons WHERE City='Beijing'

drop 删除索引、表和数据库

用于 MS SQL Server 的语法:
DROP INDEX table_name.index_name
DROP TABLE 表名称
DROP DATABASE 数据库名称

仅仅删除表格中的数据,但并不删除表本身：

TRUNCATE TABLE 表名称

ALTER TABLE 语句用于在已有的表中添加、修改或删除列

ALTER TABLE table_name ADD column_name datatyp --增加列
ALTER TABLE table_name DROP COLUMN column_name --删除列
（某些数据库系统不允许这种在数据库表中删除列的方式。）
ALTER TABLE table_name ALTER COLUMN column_name datatype--更改列数据类型

---

SQLdate日期

MySQL：

函数	描述
NOW()	返回当前的日期和时间
CURDATE()	返回当前的日期
CURTIME()	返回当前的时间
DATE()	提取日期或日期/时间表达式的日期部分
EXTRACT()	返回日期/时间按的单独部分
DATE_ADD()	给日期添加指定的时间间隔
DATE_SUB()	从日期减去指定的时间间隔
DATEDIFF()	返回两个日期之间的天数
DATE_FORMAT()	用不同的格式显示日期/时间

SQL server

函数	描述
GETDATE()	返回当前日期和时间
DATEPART()	返回日期/时间的单独部分
DATEADD()	在日期中添加或减去指定的时间间隔
DATEDIFF()	返回两个日期之间的时间
CONVERT()	用不同的格式显示日期/时间

SQL Date 数据类型
MySQL 使用下列数据类型在数据库中存储日期或日期/时间值：

• DATE - 格式 YYYY-MM-DD
• DATETIME - 格式: YYYY-MM-DD HH:MM:SS
• TIMESTAMP - 格式: YYYY-MM-DD HH:MM:SS
• YEAR - 格式 YYYY 或 YY

SQL Server 使用下列数据类型在数据库中存储日期或日期/时间值：

• DATE - 格式 YYYY-MM-DD
• DATETIME - 格式: YYYY-MM-DD HH:MM:SS
• SMALLDATETIME - 格式: YYYY-MM-DD HH:MM:SS
• TIMESTAMP - 格式: 唯一的数字

如果OrderDate为2008-12-26 16:23:55，SELECT * FROM Orders WHERE OrderDate='2008-12-26'将不会查询到值，但可以查询到2008-12-26

提示：如果您希望使查询简单且更易维护，那么请不要在日期中使用时间部分！

NULL与is null

• NULL 值是遗漏的未知数据。
• 默认地，表的列可以存放 NULL 值。
• 本章讲解 IS NULL 和 IS NOT NULL 操作符。

如果表中的某个列是可选的，那么我们可以在不向该列添加值的情况下插入新记录或更新已有的记录。这意味着该字段将以 NULL 值保存。

NULL 值的处理方式与其他值不同。

NULL 用作未知的或不适用的值的占位符。

NULL不等于''也不等于0

Id	LastName	FirstName	Address	City
1	Adams	John		London
2	Bush	George	Fifth Avenue	New York
3	Carter	Thomas		Beijing

SELECT LastName,FirstName,Address FROM Persons WHERE Address IS NULL

• is null 为空
• is not null 不为空

ISNULL()、NVL()、IFNULL() 和 COALESCE() 函数

null 函数

"Products" 表：

P_Id	ProductName	UnitPrice	UnitsInStock	UnitsOnOrder
1	computer	699	25	15
2	printer	365	36
3	telephone	280	159	57

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

如果有 "UnitsOnOrder" 值是 NULL，那么结果是 NULL。

在这里，我们希望 NULL 值为 0。

SQL Server / MS Access：
SELECT ProductName,UnitPrice*(UnitsInStock+ISNULL(UnitsOnOrder,0)) FROM Products

Oracle
Oracle 没有 ISNULL() 函数。不过，我们可以使用 NVL() 函数达到相同的结果：
SELECT ProductName,UnitPrice*(UnitsInStock+NVL(UnitsOnOrder,0))FROM Products

MySQL
MySQL 也拥有类似 ISNULL() 的函数。不过它的工作方式与微软的 ISNULL() 函数有点不同。

在 MySQL 中，我们可以使用 IFNULL() 函数，就像这样：
SELECT ProductName,UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder,0))FROM Products
或者我们可以使用 COALESCE() 函数，就像这样：
SELECT ProductName,UnitPrice*(UnitsInStock+COALESCE(UnitsOnOrder,0))FROM Products

查找空值：

age is null         --判断为null
isnull(age,'')=''   --判断为null或''

SQLserver查看表结构：

sp_help t_sfk;

sp_columns t_sfk;

---

SQL是默认主键从小到大排序

ORDER BY keyid desc --按照主键反向排序
ORDER BY keyid asc --按照主键正向排序
--例如：
SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC, OrderNumber ASC

SQL 时间相关

-- MySQL
now() --系统当前的日期和时间。返回值为datetime类型

-- SQL server
getdate()   --系统当前的日期时间。返回值为datetime类型

convert：转换格式，第一个参数为目标类型，第二个参数要转换的数据，第三个数
SELECT  CONVERT(VARCHAR(10),GETDATE(),120); --日期转字符串：获取当前年月日
select CONVERT(datetime,'2017-05-12',20)    --字符串转日期

查询并按照sno排序

SELECT keyid,row_number() over (order by sno) sno
    FROM t_jzc_mx
    where mainkeyid=@keyid

查找字符索引

 SELECT * from an where charindex('0',shuliang)!=1   第一个字符不是0

截取长度：

从第一位开始截取三位：abd
select substring('abdcsef',1,3)
提取房间号：
BEGIN
DECLARE @roomno varchar(50);
set @roomno='1单元503室';
SELECT SUBSTRING(@roomno,CHARINDEX('元',@roomno)+1,CHARINDEX('室', @roomno)-CHARINDEX('元',@roomno)-1)
END

if ...select...else select...与case when...then...

/*判斷一個數如果大於10，按10統計，如果小於0，按0統計*/
--方法a
DECLARE @AA INT
SET @AA=15
	IF @AA>10
		SELECT 10
	ELSE IF @AA<0
		SELECT 0
	ELSE
		SELECT @AA

--方法b
DECLARE @BB INT 
SET @BB=15
SELECT 
 CASE WHEN @BB>10 THEN 10
	  WHEN @BB<0 THEN 0
ELSE @BB END