之前学过一点数据库但由于一直没有使用忘得差不多了,最近重新复习一下相关的知识,把基本的语法YOU又看了一遍,为了强化记忆在写一遍~~~~~~
基本的 select 语句
读取一个表的所有字段
Select * from sales.customer
读取特定字段
Select customerID,Territory fromsales.customer
Where 子句
读取 lastname 是 adams 的记录
Select * from person.contact wherelastname='adams'
运算符: =、>、<、>=、<=、!>、!<、!=
逻辑运算符:and、or、not、between、like、in
读取ModifiedDare 大于2003-1-1 之后的记录
Select * from person.contact whereModifiedDare>'2003-1-1'
读取 lastname 是 adams 并且ModifiedDare 大于2003-1-1 之后的记录
Select * from person.contact wherelastname='adams' and ModifiedDare>'2003-1-1'
读取 lastname 是 adams 或者ModifiedDare 大于2003-1-1 之后的记录
Select * from person.contact wherelastname='adams' or ModifiedDare>'2003-1-1'
读取 lastname 不是 adams 并且ModifiedDare 大于2003-1-1 之后的记录
Select * from person.contact where not(lastname='adams')and ModifiedDare>'2003-1-1'
读取 ModifiedDare大于等于2003-1-1 并且 小于等于 2003-12-31的记录
Select * from person.contact whereModifiedDare>='2003-1-1' and ModifiedDare<='2003-12-31'
读取ModifiedDare 在2003-1-1和2003-12-31之间的记录
Select * from person.contact whereModifiedDare between '2003-1-1' and '2003-12-31'
读取 lastname 中有il (前后可有任意个任意字符) 用like 并用通配符 %
Select * from person.contact where lastnamelike '%il%'
读取 lastname 中有il (前后可有一个任意字符) 用like 并用通配符 _
Select * from person.contact where lastnamelike '_il_'
读取 lastname 中有adams或smith或adina 的记录 用in
Select * from person.contact where lastnamein ('adams','smith','adina')
Order by 子句 (排序)
asc升序(默认) desc降序
读取person.contact 中的所有记录 并modifiedData 降序排序
Select * from person.contact order bymodifiedData desc
读取person.contact 中的所有记录 并modifiedData 降序排序 若modifiedData 相同则按 lastname升序排序
Select * from person.contact order bymodifiedData desc,lastname
Group by 子句 (分组)
聚合函数:count()--返回总数、avg()--计算平均值、min()--计算最小值、max()--计算最大值
返回person.contact表的总数
Select count(*) from person.contact
返回production.product表listprice字段的平均值,取别名 avg_price
Select avg(listprice) avg_price fromproduction.product
返回production.product表listprice字段的最小值、最大值,取别名min_price、max_price
Select min(listprice) min_price,max(listprice)max_price from production.product
把person.contact表按lastname分组,并计算各组总数,取别名qty
Select lastname,count(*) qty fromperson.contact group by lastname
把person.contact表按lastname分组,并计算各组总数,取别名qty,并按lastname排序
Select lastname,count(*) qty fromperson.contact group by lastname order by lastname
把person.contact表按lastname分组,并计算各组总数,取别名qty,并按总数降序排序,相同的按lastname排序
Select lastname,count(*) qty fromperson.contact
group by lastname
order by count(*),lastname
实例:
--INSERT INTO Stores VALUES ('TEST', 'Test Store', '1234 Anywhere Street', 'Here', 'NY', '00319');
/*
INSERT INTO Sales
(StoreCode, OrderNumber, OrderDate, Quantity, Terms, TitleID)
VALUES
('test','TESTORDER2', '01/01/1991', 10, 'NET 30', 1234567);
*/
--SELECT * FROM Person.Contact INNER JOIN HumanResources.Employee ON Person.Contact.ContactID = HumanResources.Employee.ContactID
--SELECT e.NationalIDNumber FROM HumanResources.Employee AS e INNER JOIN Person.Contact AS c ON c.ContactID = e.ContactID
/*
CREATE DATABASE Accounting
ON
(NAME = 'Accounting',
FILENAME = 'D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\AccountingData.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5)
LOG ON
(NAME = 'AccountingLog',
FILENAME = 'D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\AccountingLog.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB);
GO
EXEC sp_helpdb 'Accounting'
EXEC sp_help
*/
/*
USE Accounting
CREATE TABLE Customers
(
CustomerNo int IDENTITY NOT NULL,
CustomerName varchar(30) NOT NULL,
Address1 varchar(30) NOT NULL,
Adress2 varchar(30) NOT NULL,
City varchar(20) NOT NULL,
State char(2) NOT NULL,
Zip varchar(10) NOT NULL,
Contact varchar(25) NOT NULL,
Phone varchar(15) NOT NULL,
FedIDNo varchar(9) NOT NULL,
DateInSystem smalldatetime NOT NULL
);
EXEC sp_help
*/
USE Accounting
CREATE TABLE Employees
(
EmployeeID int IDENTITY NOT NULL,
FirstName varchar(25) NOT NULL,
MiddleInitial varchar(1) NULL,
LastName varchar(25) NOT NULL,
Title varchar(25) NOT NULL,
SSN varchar(11) NOT NULL,
Salary money NOT NULL,
PriorSalary money NOT NULL,
LastRaise AS Salary - PriorSalary,
HireDate date NOT NULL,
TerminationDate date NULL,
ManagerEmpID int NOT NULL,
Department varchar(25) NOT NULL
)
--EXEC sp_help Employees
EXEC sp_helpdb Accounting
本文分享自 机器学习算法与Python学习 微信公众号,前往查看
如有侵权,请联系 cloudcommunity@tencent.com 删除。
本文参与 腾讯云自媒体同步曝光计划 ,欢迎热爱写作的你一起参与!