—选择（投影）列1，列2，列3
    select ProductName ,price,store
    —来自（来源）表明
    from Product
    —条件 列=‘值’ and(并且)/or(或者) 列 !=‘值’
    where price>200

    select *from Customer

    —where CustomerName like ‘[0-9,a-z,A-Z,张,李,陈]%’
    —where address like ‘%武城职%’ —模糊
    —%匹配任意长度字符，
    — _匹配单个字符，
    — []匹配指定字符
    — where CustomerName=’小’ —完全匹配（绝对匹配）

    select ProductName,price,store from Product
    order by Price desc ,store asc —默认从小到大（ASC），

    —最大MAX 最小MIN 求和SUM 平均数AVG 计数COUNT
    select from Product
    select max(price) from Product
    select min(price) from Product
    select sum(price) from Product
    select avg(price) from Product
    select count(    ) from Product

    —分组
    select * from product
    select categoryid ,max(price),min(price) from product
    group by categoryid

    —用户表中的数据
    select Customername,gender from Customer
    —请帮我查出男的女的各多少人
    select gender,count(*) from Customer
    group by Gender

    select gender from Customer
    group by gender

    select max(Customername) from Customer
    group by gender

    select count (Customername),gender
    from Customer
    —where gender=’男’
    group by gender
    —having count(Customername)>5

    —查询每种商品的平均价格并按照降序排列
    select avg(price) 平均价,CategoryID 商品分类 from Product
    group by CategoryID
    order by avg (price) desc

    —连接查询又可分为内连接，外连接和交叉连接
    —连接查询
    —第一种写法
    select from Category
    select     from Product
    select ProductName,CategoryName,Price from Category,Product
    where Category.CategoryID=Product.CategoryID
    and Price<100
    —这种写法把连接的条件和帅选的条件放在一起，不好区分

    —标准写法
    select ProductName,CategoryName,Price from Category join Product on
    Category.CategoryID=Product.CategoryID
    where Price<100

    select Productname 产品名,Categoryname 分类,quantity 数量
    from Product join Category on Product.CategoryID=Category.CategoryID join OrderDetails on OrderDetails.ProductID=Product.ProductID

    select CustomerName ,Categoryname
    from Customer join Orders on Customer.CustomerID=Orders.CustomerID
    join OrderDetails on OrderDetails.OrderID=Orders.OrderID
    join Product on Product.ProductID=OrderDetails.ProductID
    join Category on Category.CategoryID=Product.CategoryID

    select * from Product right outer join Category on Product.CategoryID=Category.CategoryID

    —子查询
    —1、先看一下所有分类
    select from Category where CategoryName=’儿童玩具’
    —2、看一下玩具分类下有哪些商品
    select     from Product where Categoryid=6
    select * from Product where Categoryid=(select CategoryID from Category where CategoryName=’儿童玩具’)
    —一个查询的条件，来自于，另一个查询的结果，这种查询就叫做子查询

    —小练习，我先看看小艾（客户表customers）这个人，买过几次东西（订单order）
    select Customerid from Customer where CustomerName=’小艾’
    select count(*)购买次数 from Orders where Customerid=(select Customerid from Customer where CustomerName=’小艾’)

    —小练习：
    —查询小艾购买过的商品
    —思路：1、查出小艾的ID，2、查出和小艾有关的订单ID，3、查询订单中的商品ID，4、查询商品名字，5、如果有重复的则消除重复（分组或distinct）
    ———————————————————————————————————————————————————————————————————
    select Customerid from Customer where CustomerName=’小艾’
    select Orderid from Orders where CustomerID=(select Customerid from Customer where CustomerName=’小艾’)
    select Productid from OrderDetails where OrderID in (select OrderID from Orders where CustomerID=(select Customerid from Customer where CustomerName=’小艾’))
    select Productname from Product where ProductID in(select Productid from OrderDetails where OrderID in(select OrderID from Orders where CustomerID=(select Customerid from Customer where CustomerName=’小艾’)))

    —多行子查询：（in all any |some）
    select *from Product where ProductID <all (select Productid from Product where ProductID in (2,3,5))

    ——————————————————————-子查询的其他应用———————————————————————————————————-
    —from中使用子查询
    select * from Category join (select Productid,Productname,Categoryid from Product)as p on Category.CategoryID=p.CategoryID

    —Exists-存在—
    select from Category
    where exists(select     from Category where CategoryID=1)
    —‘嘉嘉’!=’乐乐’
    —CategoryID <>3
    —CategoryID =3

    ————————————————————DML
    —(inser,update,delete)—————————————————————————————
    update Category
    set CategoryName=’小米电脑’,P_Cate=’5’
    where CategoryID>=13

    select * from Category
    delete from Category
    where CategoryID in (13)

    —手动创建视图
    select * from V_1
    ———————————————————针对于结构操作————————————————————————-
    ———————————————————DDL—数据定义语言—————————————（Create\Alter\Drop）

    ———————————————————针对于数据操作——————————————————————————-
    ———————————————————DML—数据操作语言—————————————（insert\update\delete）
    ———————————————————DQL数据库查询语言—————————————（select）

    —创建视图
    CREATE View V_2
    as
    select ProductID,ProductName,price
    from Product

    select from V_2
    ———————-创建——————————
    create view V_3
    as
    select     from Product
    where ProductID>20 and ProductID<=30
    ———————修改————————
    ALTER view V_3
    as
    select * from Product
    where ProductID between 20 and 30

    ——————-删除视图—————————-
    DROP VIEW V_3

    select * from V_3
    —可以对视图做增删改，但尽量不要这么操作—————————
    ———————————————————-增——————————————————————-
    insert into V_3(productname,categoryid,price,store)values(‘aaaaaaa’,’8’,’9999’,’9999’)