# sql 对大小写不敏感

# 查询表中的所有信息

select * from `Customers`;

# 查询指定字段 CustomerName,Country

select CustomerName,Country from `Customers`

# distinct 去重复

select * distinct Country  from `Customers`

# 客户的国家数量

select COUNT(DISTINCT Country) from `Customers`

#where 语句

select * from `Customers` where Country = 'Mexico'

#= 等于  <> 不等于 > 大于 < 小于  >= 大于等于  <= 小于等于  between 在某个范围内  like 搜索某种模式  IN  为列指定多个可能的值

select * from `Customers` where `CustomerID`=1

#<>  不等于1

select * from `Customers` where CustomerID <> 1

#>

select * from `Customers` where CustomerID > 3

#<

select * from `Customers` where CustomerID < 3

#>=

select * from `Customers` where CustomerID >= 3

#<=

select * from `Customers` where CustomerID <= 3

#between and 3~5

select * from `Customers` where CustomerID between 3 and 5

#like

select * from `Customers` where `City` like '%n%'

select * from `Customers` where `City` like '%n'

#and

select * from `Customers` where `Country`='Germany' and City = 'Berlin'

#or

select * from `Customers` where `Country`='Germany' or City = 'London'

#Not

select * from `Customers` where not Country = 'Germany'

#结合 and or

select * from `Customers` where Country = 'Germany' and (City='Berlin' OR City='München')

#order by

select * from `Customers` order by Country;

#order by desc 降序排列

select * from `Customers` order by Country desc;

#order by 多列

select * from `Customers` order by Country,CustomerName;

#order by 多列实例2

select * from `Customers` ORDER BY Country ASC,CustomerName DESC;

#insert into 实例 插入数据

insert into `Customers`(CustomerName,ContactName,Address,City,PostalCode,Country)

values ('Cardinal','Tom B. Erichsen','Skagen 21','Stavanger','4006','Norway')

#仅在指定的列中插入数据

insert into `Customers`(CustomerName,City,Country) values ('Cardinal','Stavanger','Norway')

#NULL 空值 如何测试NULL值

select CustomerName,ContactName,Address from `Customers` where Address is null

#is not null

select * from `Customers` where Address is not null

#更新表中的记录 update

# update table_name  set column1 = value1,column2 = value2,.... where condition

update `Customers` set ContactName = 'Alfred Schmidt',City='Frankfurt999999' where CustomerID =1

#更新多个记录数据

update Customers set ContactName = 'Juan' where Country='Mexico'

#删除数据  delete

# delete from table_name where condition

delete from Customers where CustomerName='Alfreds Futterkiste'

#删除所有的数据

#delete from table_name

#或者

# delete * from table_name

###############################################SQL 高级##########################

#前4条数据

select * from `Customers` limit 4

#like  搜索 关键字

#  %  百分号标识零个，一个或多个字符

# _ 下划线标识单个字符

select * from `Customers` where CustomerName like 'a%'

select * from Customers where CustomerName like '%or%'

# _ 单个字符

#选择客户名称在第二位具有r的所有客户

select * from Customers where CustomerName like '_r%'

#以a开头 至少有三个字符

select * from Customers where CustomerName like 'a_%_%'

#以a开头并且以o结尾

select * from Customers where CustomerName like 'a%o'

# 不以a开头的所有客户

select * from Customers where CustomerName not like 'a%'

# 不以bsp 开头

select * from Customers where CustomerName like '[!bsp]%'

#IN 运算符允许您在where子句中指定多个值  IN运算符是多个or条件的缩写

select * from Customers where Country in ('Germany', 'France', 'UK')

# not in

select * from Customers where Country not in ('Germany', 'France', 'UK')

#选取来自同一国家的所有客户作为供应商：

select * from `Customers` where Country in (select Country from Suppliers)

#between and

select * from Products where Price between 10 and 20

#not betwee and

select * from Products where price not between 10 and 20

#价格在10到20之间但CategoryID不是1、2或3的所有产品

select * from Products where (Price Between 10 and 20) and not CategoryID in (1,2,3)

#时间段内的所有订单

select * from Orders WHERE OrderDate BETWEEN '#07/04/1996#' AND '#07/09/1996#'

#inner join  on  交集

select Orders.`OrderID`,`Customers`.`CustomerName`,`Orders`.`OrderDate` from `Orders`

inner join `Customers` on Orders.`CustomerID`=Customers.`CustomerID`

#inner join 关键字（内部连接） 内部链接inner join 关键字选择两个表中具有匹配值的记录

#返回所有下订单的客户

select Customers.`CustomerName`,Orders.`OrderID` from Customers

inner join `Orders`

on Customers.`CustomerID` = Orders.`CustomerID`

order by Customers.`CustomerName`

#查询出医院下面的科室

select `biz_hospital`.`name`,`biz_hospital`.`hospital_id`,`biz_hospital`.`city`,`biz_hospital`.`level`,

`biz_room`.`room_id`,`biz_room`.`name` room_name from `biz_hospital`

inner join `biz_room` on `biz_hospital`.`hospital_id` = `biz_room`.`hospital_id` and `biz_hospital`.`status` =1 and `biz_room`.`status` =1

#查询出来医院下面所有科室下面的所有医生

select `biz_hospital`.`name` hospital_name,`biz_hospital`.`city`,`biz_hospital`.`level`,`biz_room`.`room_id`,`biz_room`.`name` room_name,

`biz_doctor`.`name` doctor_name

from ((`biz_hospital` inner join `biz_room` on `biz_hospital`.`hospital_id` = `biz_room`.`hospital_id` and `biz_hospital`.`status` =1 and `biz_room`.`status`=1 )

inner join `biz_doctor` on `biz_room`.`room_id` = `biz_doctor`.`room_id` and `biz_doctor`.`status`=1) 

#有开放科室的医院

select `biz_hospital`.`hospital_id`,`biz_hospital`.`name` hospital_name,`biz_hospital`.`city`,`biz_hospital`.`level`

,`biz_open_room`.`room_name`