SQL语句复习

最近要分析数据，不得不复习sql语句。。。。
忘了好多啊。。。。。。
学习参考：https://www.w3cschool.cn/sql/
1.sqlserver代码编辑器显示行数：打开 工具-----选项------文本编译器 ------所有语言------常规----行号

`
SELECT Country FROM Customers;

SELECT Distinct Country FROM Customers;

SELECT COUNT(Distinct Country) FROM Customers; 

SELECT *FROM Customers
WHERE Country='Mexico';

SELECT * FROM Customers
WHERE Country='Mexico' OR PostalCode='05021'

SELECT * FROM Customers
WHERE CustomerID=1

SELECT * FROM Customers
WHERE Country='Germany' 
AND City='Berlin' 


SELECT City,Region FROM Customers 
WHERE City='Berlin' OR City='München';

SELECT * FROM Customers
WHERE NOT Country='Germany';


SELECT * FROM Customers
WHERE Country='Germany' AND (City='berlin'or City='USA' );
--降序排序DESC 默认升序ASC
SELECT * FROM Customers
ORDER BY Country DESC;

--插入数据
INSERT INTO Customers(CustomerID ,CompanyName, ContactName, Address, City, PostalCode, Country) 
VALUES ('5','Cardinal','Tom B. Erichsen','Skagen 21','Stavanger','4006','Norway');

--使用一个表填充另一个表,这个有错误
INSERT INTO Customers(CustomerID,Address,City) 
SELECT CustomerID,Address,City
From Employees 
[WHERE LastName='King'];

--测试NULL
SELECT ContactName From Customers
WHERE Region Is NOT NULL;
  
  
--创建表
CREATE TABLE CUSTOMS(
	ID INT     NOT NULL,
	NAME VARCHAR(20) NOT NULL,
	AGE INT         NOT NULL, 
	ADDRES CHAR(25),
	SALARY  DECIMAL(18,2),
	PRIMARY key(ID),
);

--更新记录时要小心。省略WHERE子句，所有记录将被更新！ 
UPDATE Customers
SET ContactName='ffff',City='gggg'
WHERE CustomerID='5';


--Delete语句删除表中数据
DELETE FROM Customers
WHERE CompanyName='Alfreds Futterkiste'; 
 /*
 有不同类型的sql表达式
	布尔型基于匹配单个值获取数据
	数值型表达式用于在任何查询中执行任何数学运算
	日期返回当前系统日期和时间值
	*/


---------数值型
/*
SELECT numerical_expression AS  OPERATION_NAME
[FROM table_nameWHERE CONDITION];
*/

--选择数据库 USE语句
USE test;

-------日期表达式
SELECT  GETDATE();`

SQL一些数据类型
|CHARACTER(n) 字符/字符串。固定长度 n。
VARCHAR(n) 或
CHARACTER VARYING(n) 字符/字符串。可变长度。最大长度 n。
BINARY(n) 二进制串。固定长度 n。
BOOLEAN 存储 TRUE 或 FALSE 值
INTEGER§ 整数值（没有小数点）。精度 p。
SMALLINT 整数值（没有小数点）。精度 5。
INTEGER 整数值（没有小数点）。精度 10。
DATE 存储年、月、日的值。
TIME 存储小时、分、秒的值。

SQL连接

内部链接

左连接

右链接

完整外部连接 /待解决

SQL自连接 Self JOIN

----SELECT TOP 子句
SELECT TOP 1 City,Region FROM Customers 
WHERE City='Berlin' OR City='München';

SELECT TOP 50 PERCENT City,Region  FROM Customers
WHERE City='Berlin' OR City='München';

/*待解决
SELECT * FROM Customers
WHERE Country='Germany'
LIMIT 3;*/


/*LIKE 运算符
有两个通配符与LIKE运算符一起使用：
％表示零个，一个或多个字符
_表示单个字符*/
SELECT * FROM Customers
WHERE CompanyName lIKE '%a_';

SELECT * FROM Customers
WHERE City LIKE '[abc]%'; 



SELECT * FROM Customers 
WHERE City LIKE '[a-c]%';

---疑问
SELECT DISTINCT * FROM Customers
WHERE City LIKE '[!abc]%';

---疑问
SELECT DISTINCT * FROM Customers
WHERE City  NOT LIKE '[abc]%';


----------IN操作符
select * FROM Customers
WHERE Country IN ('Germany','US','France');

select * FROM Customers
WHERE Country   NOT IN  ('Germany','US','France');

SELECT * FROM Customers
WHERE Country IN (SELECT Country FROM Suppliers);

---SQL BETWEEN AND
SELECT *FROM Products
WHERE UnitPrice Between 10 and 20 ;
SELECT * from Products
WHERE UnitPrice NOT Between 10 and 20; 


--下边俩等价
SELECT * FROM Products 
WHERE (UnitPrice BETWEEN 10 AND 20) 
AND CategoryID NOT IN (1,2,3); 


SELECT * FROM Products 
WHERE (UnitPrice BETWEEN 10 AND 20) 
AND NOT CategoryID IN (1,2,3); 
--月日年
SELECT * FROM Orders 
WHERE OrderDate BETWEEN '07/04/1996' AND '07/09/1996';


--join链接
 SELECT Orders.OrderID, Customers.ContactName, Orders.OrderDate 
 From Orders 
 FULL jOIN Customers
	ON Orders.CustomerID=Customers.CustomerID;
 
SELECT Orders.OrderID, Customers.CompanyName, Shippers.CompanyName
FROM (  (Orders  
INNER JOIN Customers 
	ON Orders.CustomerID = Customers.CustomerID)
INNER JOIN Shippers 
	ON Orders.EmployeeID = Shippers.ShipperID);
	
---自连接self join
SELECT A.CompanyName AS CustomerName1, B.CompanyName AS CustomerName2, A.City
FROM Customers A, Customers B
WHERE A.CustomerID != B.CustomerID AND A.City = B.City 
ORDER BY A.City;


 --UNION运算符用于组合两个或更多SELECT语句的结果集
SELECT City from Customers
UNION
SELECT City from Suppliers
ORDER BY City 
 
 
SELECT City from Customers
UNION ALL
SELECT City from Suppliers
ORDER BY City


SELECT City,Country From Customers
WHERE Country='Germany'
UNION 
SELECT City,Country FROM Suppliers
WHERE Country='Germany'
ORDER BY City; 
 
---SELECT INTO 插入新表中
SELECT *
INTO CustomersCopy
FROM Customers;

DELETE FROM CustomersCopy
WHERE Region IS NULL;


SELECT City
INTO CustomersCopy2
FROM Customers
where 1=0;
 
---INSERT  INTO SELECT 语句

INSERT INTO CustomersCopy(Address)
SELECT Address
FROM Employees;


SELECT *
FROM CustomersCopy;

---删除表中数据truncate table
TRUNCATE TABLE CustomersCopy;

--错误：DELETE TABLE CustomersCopy;
CREATE DATABASE my_db;

--错误：SHOW databases;


--ALTER TABLE 现有表中添加、删除或修改列。
ALTER TABLE Persons
ADD  DateOfBirth date;


ALTER TABLE Persons                
ALTER COLUMN DateOfBirth TIME;


---
CREATE TABLE Per(   
             
ID int  IDENTITY(1,1),---AUTO_INCREMENT            
LastName varchar(255) NOT NULL,                
FirstName varchar(255),                
Address varchar(255),                
City varchar(255),                
PRIMARY KEY (ID)       
);

----create view 
CREATE VIEW [Products Above Average Price he] AS                
SELECT ProductName,UnitPrice                
FROM Products                
WHERE UnitPrice>(SELECT AVG(UnitPrice) FROM Products) 

明天再更