MySQL基础语句

--设置mysql root密码
set password for 'root'@localhost=password('123');

排序检索数据
----------------------------------------------
--按价格升序(1 2 3)
select name , price from Products order by price;

--按价格降序(3 2 1)
select name , price from Products order by price desc;

--使用 order by和limit的组合，找出[一个]列中最高值
select price from Products order by price desc limit 1;

使用where子句
位置: 表名的后面,order by子句前面,limit在order by的后面
----------------------------------------------
--where子句操作符
<> 不等于, between 在指定的两个值之间。

--使用between 操作符
--它检索价格在5块钱到100块钱之间的所有产品(包括指定的开始值和结束值)
select name, price from Products where price between 5 and 100;

--空值检查
select id from customers where email is null;

--and操作符(&&)
--查询供应商id等于2并且价格小于1000的CPU,按升序输出3行
--limit要在order by后面
select id, vendors, name, price from Products where vendors= 2 and price <1000 order by price limit 3;

--or操作符(||)
--注意：混合使用or 和 and 操作符的时候要加括号(and 操作符优先级高)
select id, vendors, name, price from Products where (vendors = 2 or vendors=1) and price <200000 order by price;

--in操作符
--下面的SQl语句完成与上面同样的功能
--in最大的优点是可以包含其它的select语句
select id, vendors, name, price from Products where vendors in(1,2) and price <200000 order by price; 

--not操作符
--列出除供应商id 1以外的产品
select id, vendors, name, price from Products where vendors not in(1) and price <200000 order by price;

--like操作符
--百分号(%)通配符
--%告诉MySQL接受AMD之后的任意字符，不管它有多少字符
select *from Products where name like 'AMD%';
--'%MD%'表示匹配任何位置包含文本MD的值，不管它之前或之后出现什么字符
select *from Products where name like '%MD%';
--匹配任何东西，不过不会匹配 null
select *from Products where name like '%';

创建拼接字段
----------------------------------------------
--多数数据库用'+'号来实现拼接，MySQL用concat()
select concat(trim(name), ' (', trim(price), ')') as 报价 from Products order by price;
--下面的SQL语句检索订单号2的汇总信息
select product, quantity, price, (quantity*price) as TotalPrice from orderItems where ordernum=2;
--返回当前日期和时间
select now();

使用数据处理函数
----------------------------------------------
文本处理函数
--将串转换为小写
select lower(name) from Vendors;

日期处理函数
--Date()返回日期时间的日期部分
select num, customer, orderdate from orders where Date(orderdate) = '2008-09-01';

--Year()返回日期时间的年份部分
--检索顾客id为1, 2009年的订单
select num, customer, orderdate from orders where customer = 1 and Year(orderdate) = 2009;

--Month()返回日期时间的月份部分
--检索顾客id为1, 2009年2月份的订单
select num, customer, orderdate
  from orders
  where customer = 1 and Year(orderdate) = 2009 and Month(orderdate) = 2;

使用avg()、count()、max()、min()、sum()函数
----------------------------------------------
--avg()返回某列的平均值
--下面的例子返回特定供应商所提供产品的平均价格
select avg (price) AS IntelAveragePrice from products where vendors=1;

--count()返回某列的行数
--下面的例子返回Customers表的客户总数(COUNT(*)包含null值哦!)
select COUNT(*) AS num_cus from Customers;
--统计有电子邮件的客户总数(COUNT(email)忽略null值哦!)
select COUNT(email) AS num_cus from Customers;

--max()返回某列的最大值
select Max(price) AS MaxPrice from Products;

--min()返回某列的最小值
select Min(price) AS MinPrice from Products;

--sum()返回某列值之和
--返回 2号订单物品数量之和
select sum(quantity) AS Items from orderItems where ordernum=2
--返回 2号订单总价
select sum(quantity*price) AS TotalPrice from orderItems where ordernum=2

分组数据
----------------------------------------------
--group by
位置:where后， order by 前
select vendors, Max(price) AS max_price from Products group by vendors;
--统计所有顾客的订单数目
select customer, count(*) as OrderNum from orders group by customer;
--下面的例子检索订单号和总计订单价格，按“订单号”分组排序输出
select ordernum, sum(quantity*price) AS TotalPrice from OrderItems group by ordernum order by TotalPrice;

--过滤分组
--找到顾客订单数目大于 2的分组
select customer, count(*) as OrderNum from orders group by customer having count(*) >=2;

使用子查询
----------------------------------------------
--检索订购物品id=4的所有顾客
select ordernum from orderitems where product=4;  --结果:2, 7
select customer from orders where num in(2, 7);  --结果:1
select name, email from customers where id in(1);  --结果:apq | [e-mail]
--合起来写就是这个样子
select name, email
  from customers where id in (select customer
  from orders where num in (select ordernum
  from orderitems where product=4));

--作为计算字段使用子查询
select count(*) as totalorders from orders where cust_id=1; --结果:5
--显示customers表每个客户的订单总数
select cust_name,
        (select count(*)
          from orders
          where orders.cust_id = customers.cust_id) AS totalorders from customers;

联结表
----------------------------------------------
select vend_name, prod_name, prod_price
  from vendors, products
  where vendors.vend_id = products.vend_id
  order by vend_name, prod_name;
--上面的语句也可以这么写
select vend_name, prod_name, prod_price
  from vendors inner join products
  on vendors.vend_id = products.vend_id;