欧美做电影 迅雷下载网站,建设一个外贸网站.,免费注册企业邮箱域名,杭州富阳建设局网站首页学习网站#xff1a;https://www.w3schools.com/sql/#xff0c;提供在线编程 以下内容仅SQL常见语法总结
数据
Customers表 Products表 OrderDetails表 Orders表 Shippers表 Employees表 选择查询
SELECT
select语句用来从头数据库中选择数据
SELECT column1, column2,…学习网站https://www.w3schools.com/sql/提供在线编程 以下内容仅SQL常见语法总结
数据
Customers表 Products表 OrderDetails表 Orders表 Shippers表 Employees表 选择查询
SELECT
select语句用来从头数据库中选择数据
SELECT column1, column2, ...
FROM table_name;# 选择Customers表所有数据
select *
from Customers;SELECT DISTINCT
select distinct用来返回非重复值
SELECT DISTINCT column1, column2, ...
FROM table_name;# 计算Customers表country类别数
select count(distinct Country)
from Customers;条件查询
WHERE
where用来过滤指定条件的纪录包括betweenlikein
SELECT column1, column2, ...
FROM table_name
WHERE condition;# 筛选Customers表ID1的纪录
select * from Customers
where CustomerID1;LIKE
like用于在where条件中搜索指定列样式的记录
%用于表示0个1个或者多个字符_用于表示1个字符
SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern;# 在Customers表中搜索客户名第二个位置是r
select *
from Customers
where CustomerName like _r%;AND OR
where可以用来包含一个或多个and or运算符表示多个过滤条件
SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 OR condition3 ...;# 选择country是spain并且客户名字开头是G或R
select *
from Customers
where Country Spain and (CustomerName like G% or CustomerName like R%);NOT
Not表示与条件相反的结果
SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;# 选择不是来自Paris和London的客户
select *
from Customers
where City not in (Paris, London);IN
in允许在where子句中指定多个值是多个or条件的简写
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);# 选择没有出现在订单表的所有客户
select *
from Customers
where CustomerID not in (select CustomerID from Orders);BETWEEN
Between选择给定范围内的值包含开始和结束
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;# 从Products选择价格在10-20ID是1/2/3的产品
select *
from Products
where Price between 10 and 20
and CategoryID in (1,2,3);连接查询
SQL中的连接包括四种
内连接(INNER) JOIN返回两个表中具有匹配值的记录左连接LEFT (OUTER) JOIN返回左表中的所有记录以及右表中匹配的记录右连接RIGHT (OUTER) JOIN返回右表中的所有记录以及左表中匹配的记录全连接FULL (OUTER) JOIN当左表或右表中有匹配项时返回所有记录 INNER JOIN
inner join和join是相同的
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name table2.column_name;# 选择所有订单的编号客户名和托运人名
select Orders.orderID, Customers.CustomerName, Shippers.ShipperName
from ((Orders
inner join Customers on Orders.CustomerID Customers.CustomerID)
inner join Shippers on Orders.ShipperID Shippers.ShipperID);LEFT JOIN
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name table2.column_name;# 查询所有客户对应的订单
select Customers.CustomerName, Orders.OrderID
from Customers
left join Orders
on Customers.CustomerID Orders.CustomerID;RIGHT JOIN
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name table2.column_name;# 查询所有雇员对应的订单号并按订单号排序
select Orders.OrderID, Employees.LastName, Employees.FirstName
from Orders
right join Employees
on Employees.EmployeeID Orders.EmployeeID
order by Orders.OrderID;FULL OUTER JOIN
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name table2.column_name
WHERE condition;# 查询所有客户和所有订单
select Customers.CustomerName, Orders.OrderID
from Customers
full join Orders
on Customers.CustomerIDOrders.CustomerID
order by Customers.CustomerName;聚合函数
MIN() MAX()
Min()返回选择列的最小值max()返回选择列的最大值
SELECT MIN(column_name)
FROM table_name
WHERE condition;SELECT MAX(column_name)
FROM table_name
WHERE condition;# 从Products表中选择最小的价格命名为SmallestPrice
select min(Price) as SmallestPrice
from Products;COUNT()
count()用来返回匹配指定条件的行数如果指定column_name而不是用*NULL值不会被计数
SELECT COUNT(column_name)
FROM table_name
WHERE condition;# 从Products表选择Price大于20的价格种类数
select count(distinct Price) as [number of price]
from Products
where Price 20;SUM()
Sum()函数用来返回一个数值列的总和
SELECT SUM(column_name)
FROM table_name
WHERE condition;# 从OrderDetails表中选择产品id为1的总金额数单价为10
select sum(Quantity * 10)
from OrderDetails
where ProductID 1;AVG()
Avg()函数用来返回一个数值列的平均值NULL值会被忽略
SELECT AVG(column_name)
FROM table_name
WHERE condition;# 从Products中选择价格大于平均值的记录
select *
from Products
where Price (select avg(Price) from Products);结果排序
ORDER BY
order by用于排序结果集升降
SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;# 按country升序排列CustomerName降序排列
select * from Customers
order by Country ASC, CustomerName DESC;分组查询
GROUP BY
group by语句将具有相同值的行分组到汇总行中例如“查找每个国家/地区的客户数量”语句通常与聚合函数COUNT()、MAX()、MIN()、SUM()、AVG()一起使用以按一列或多列对结果集进行分组。
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);select count(CustomerID), Country
from Customers
group by Country
order by count(CustomerID) desc;# 查询所有托运人的订单数量并且排列
select Shippers.ShipperName, count(Orders.orderID) as [number of orders]
from Shippers
left join Orders
on Shippers.ShipperID Orders.ShipperID
group by ShipperName
order by count(Orders.orderID);修改数据
INSERT INTO
insert into用来在表格里插入新记录
# 指定列插入值
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);# 不指定列需要确保值和表中列完整对应
INSERT INTO table_name
VALUES (value1, value2, value3, ...);# 同时插入多条记录
insert into Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
values
(Cardinal, Tom B. Erichsen, Skagen 21, Stavanger, 4006, Norway),
(Greasy Burger, Per Olsen, Gateveien 15, Sandnes, 4306, Norway),
(Tasty Tee, Finn Egan, Streetroad 19B, Liverpool, L1 0AA, UK);NULL Value
字段可以设置为optional这样插入和更新记录时可以不添加该字段的值会存储为NULL Value
# 选择空值的记录
SELECT column_names
FROM table_name
WHERE column_name IS NULL;# 选择非空的记录
SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;# 选择地址非空的客户
select CustomerName, ContactName, Address
from Customers
where Address is not null;UPDATE
update用来更新表格中已经存在的记录注意where条件如果漏掉where会更新所有记录
UPDATE table_name
SET column1 value1, column2 value2, ...
WHERE condition;# 更新客户1的ContactName和City
update Customers
set ContactName Alfred Schmidt, City Frankfurt
where CustomerID 1;DELETE
Delete用来删除表格中已经存在的记录注意where条件如果漏掉where会删除所有记录
# 删除表格中指定条件的记录只删除记录不删除表格
DELETE FROM table_name WHERE condition;# 完全删除表格
DELETE FROM table_name;# 删除Alfreds Futterkiste的记录
delete from Customers
where CustomerNameAlfreds Futterkiste;分页显示
LIMIT
LIMIT用来指定返回记录的个数在SQL Server中语法是SELECT TOP在MySQL是LIMIT
SELECT column_name(s)
FROM table_name
WHERE condition
LIMIT number;# 选择CustomerName倒序前三名
select * from Customers
order by CustomerName desc
limit 3;表(列)别名
As
As用于为表或表中的列提供临时名称在大多数数据库语言里可以省略命名为多个的时候可以用[]和
# 作用于列
SELECT column_name AS alias_name
FROM table_name;# 作用于表
SELECT column_name(s)
FROM table_name AS alias_name;# 选择ProductName列并且修改为my product
select ProductName as [my product]
from Products;
