T-SQL 从入门到高级

/*
 创建数据表
*/
create table if not exists Customers
(
 CustomerID int not null AUTO_INCREMENT,
 CustomerName varchar(255),
 ContactName varchar(255),
 Address varchar(255),
 City varchar(255),
 PostalCode varchar(255),
 Country varchar(255)
 PRIMARY KEY(CustomerID)

)

-- 插入数据

insert into Customers (CustomerID,CustomerName,ContactName,Address,City,PostalCode,Country)VALUE 
(4,'Wolski','Zbyszek','ul. Filtrowa 68','Walla','01-012','Poland');

-- 【查询数据】
-- 【1】查询所有表的数据
select * from Customers

-- 【2】 查询指定列的数据

select CustomerID, CustomerName from Customers

-- 【3】关键字Distinct,返回不相同的内容
select CustomerName  from Customers  -- 返回指定列所有的值

select Distinct  CustomerName  from Customers -- 返回指定列不同的值

select count(Distinct CustomerName) From Customers; -- f返回不同Name的数量

-- Where子句在Select Update Delete 中的使用

select * from Customers WHERE CustomerID=3;

-- Between  一定范围的查询

select  * from Customers where CustomerID Between 2 and 4;

-- 【Like 搜索模式】
select  * from Customers where CustomerName like 'Wi%'; -- 以Wi开头的

select  * from Customers where CustomerName like '%la'; -- 以la结尾的

select * from Customers where CustomerName Like '%c%'；  -- 任何位置包含c

select * from Customers where CustomerName Like '_h%';  -- 第二个位置有h的任何值

select * from Customers where CustomerName Like 'W_%';  -- 以W开头且长度至少为2

select * from Customers where CustomerName Like 'W__%';  -- 以W开头且长度至少为3

select * from Customers where CustomerName Like 'W_i%';  -- 以W开头且第三个字符为i

select * from Customers where CustomerName Like 'W%i';  -- 以W开头且以i结尾

-- 【in 为一列指定多个可能的值】

select * from Customers where CustomerName in('White Clover Markets','Wolski')

-- 【AND OR NOT】

select * from Customers where CustomerID=3 AND  CustomerName='Wolski';  -- 与

select * from Customers where CustomerID=3 Or  CustomerName='Wolski';  -- 或

select * from Customers where Not CustomerID=3 ;  -- 非

-- 【排序】 Order by /DESC

select * From  Customers order by CustomerID ASC; -- 升序排列

select * From  Customers order by CustomerID DESC; -- 降序排列