sql基础

lete cod:

select  Salary as SecondHighestSalary from Employee order by Salary limit 1,1 ;不对啊，当没有第二高的时候返回null，但是这个返回空。

select IFNULL((select Distinct Salary from Employee order by Salary DESC limit 1,1),null) as SecondHighestSalary

select Email from Person group by Email having count(Email)>1 

SELECT Department.Name AS Department, e1.Name AS Employee, e1.Salary AS Salary FROM Employee e1 JOIN Department ON e1.DepartmentId = Department.Id WHERE  ( SELECT COUNT(DISTINCT e2.Salary) FROM Employee e2 WHERE e2.Salary > e1.Salary AND e1.DepartmentId = e2.DepartmentId
            ) <3 ORDER BY Department.Name, e1.Salary DESC

DML数据操作语言

左连接以左边数据为基准，查询右表相关数据，查不到的补null.

右连接以右边数据为基准，查询左表相关数据，查不到的补null.

#创建数据库

create database aa;

show databases;#查看

use aa;#进入数据库

create table ww (id int,age int,sex int);#在aa数据库中创建表,ww是表名

show tables;

insert into ww values (1,19,1)， (2,30,1)，(3,18,2)， (4,19,1);#插入多行数据

select * from ww;#查看表里的全部数据

drop table hy_order_20170410;#删除表

drop database 数据库名;#删除数据库

#定义语言：对对象进行创建、修改、删除create,alter,drop

#操作语言：对表进行操作insert，update，delete

#查询语言：对数据查询select

#控制语言：grant#增加用户权限；revoke#收回权限

第一部分：定义语言部分

#查询表的结构；desc ww;

#alter table对已有表的增删改列；增加，修改需指定类型，更新字段类型，表名。删除不用加类型。

alter table ww add test float;#增加test这一列

alter table ww drop test;#删除test这一列

alter table ww change age years int;#修改表age一列为years

alter table ww rename bb;#修改表名，即可以重命名

第一部分：操作语言部分

#增删改数据。insert,delete,update

#1.增加数据

插入字段和原字段名顺序一致且全部字段都插入，则不用列名。

插入列的顺序与原表不一致（要一一对应）或者插入部分字段（和后面的值对应着不能多不能少），指定列名或部分列名，在表名后面，添加小括号，里面包括应有的列名，values后面对应着各列指定的值。

#创建订单表#省略有

create table hy_order_20170410(idvarchar(100),order_money float,memeber_id int,create_time datetime,status int);

insert into hy_order_20170410 values('A001',100.15,1,'2017-01-01 13:23:12',2);

 #创建订单明细表

create table hy_orderitem_20170410 (idvarchar(100),order_id varchar(100),item_id int,item_name varchar(200), pricefloat,item_num int);

insert into hy_orderitem_20170410values('AB001','A001',1001,'A',0.15,1);

#2.update更正值，改哪张表，改哪几列值，在哪些行生效。删除和修改必须加where 条件，避免数据损坏，修改不过来。

update 表名 set 列名1=新值1，列名2=新值2。。。where 条件 

update hy_order_20170410 set order_money=123 where id='A002';

#3.删除表中某行或者所有行eg:删除交易金额大于等于999的记录；清空整张表。delete要删哪张表，删哪些行，不可以删列的值，删列只能是修改，update。

delete from 表名（不加条件，就是删除整张表或者where 列名称=值）

delete from hy_order_20170410 where id='A001';

#为方便，自动补齐，dbeaver

/**

 * 如果执行所有脚本，alt+x

 *

 *crtl+/单行注释

 */

#4.导出数据

advanced copy  复制粘贴到excel ,也可以右键导出结果。

ctrl+f查找；crtl+shift+/快速注释；选中表名，按住ctrl,即快速观看表结构。help里show可找快捷键。

#select查询，1064是语法错误。

select 列1，列2，列n from哪张表，where 哪些行

\c可以中间退出。

select 模式：列是变量，可计算，也可以代入函数，where后是表达式，为真就把相应的行取出啦。取列叫投影运算，两列间做运算，叫广义投影。条件多时，一定要加小括号，避免歧义。

select goods_id,goods_name,cat_id from goods where cat_id in(4,11)或者是cat_id =4 or cat_id=11

5种语句有严格的顺序，不可以颠倒。select  后可写后面5种语句。where ,group by,having,order by,limit

模糊查询。%通配任意字符，_通配单一字符，3个字符，3个下划线。not like 不像

in在某集合内，not in 不在某集合内，between 在某范围内。and优先级大于or

#把20-29改为20,30-39改为30

Update num  set number=floor(number/10)*10 where number between 20 and 39;#等号左边的number就是新附的变量名。

#将诺基亚。。。的前三个字换成htc

select goods_id,concat('htc',substring(goods_name,4)) from goods where goods_name like '诺基亚%'；

select  *from   temp where name is not null;

select avg(shop_price)  from goods;#查询商品的单价的平均价格

select count(*)  from goods;#数一数有多少产品个数

select sum(goods_num*shop_price） from doods;#求库存商品的总价格

group by一般比较浪费资源，因为它要先排序。所以能避免就避免

select cat_id,max(shop_price) from goods group by cat_id;#每个栏目下最贵的商品价格。

having是对where产生的结果集再次进行筛选

select  goods_id,goods_name,(market_price-shop_price)  as sheng from goods where 1 having sheng>200;（1不能省略，否则出错）

create table cheng(name varchar(20),subject varchar(20),score int);

insert into cheng(values('zhang','数学',90),('zahng','语文',50),('zahng','地理',40),('li','语文',55),('li','政治',45),('wang','政治',30);

#查询每个人的平均成绩

select name,avg(score) as pj from chengji group by name;

#查询两门及两门以上不及格者的平均成绩

select name,subject,score,score<60 from chengji;

select name,sum(score<60) as gk ,  avg(score) as pj from chengji group by name having gk>=2;#count(score<60) 不行，它数有几行，不管他是0还是1和score<60没有关系。

order by 排序，desc降序排，默认是升序。多列排序，指定排序方式，逗号隔开。在cat_id里再降序排shop_price。

select goods_id,cat_id,goods_name,shop_price from goods order by cat_id,shop_price desc;

#价格前3名的limit结果机，第一个参数为偏移量，第二个参数为取的个数。

select goods_id,cat_id,goods_name,shop_price from goods order by shop_price desc limit 3;

#价格的3,4,5名

select goods_id,cat_id,goods_name,shop_price from goods order by shop_price desc limit 2，3;

#取出最新的商品    

select goods_id,cat_id,goods_name,shop_price from goods order by goods_id  desc limit 0，1;

#内连接模板select  xxxxx from table1 inner join table2 on  table1.xx=table2.xx

truncate 表名#清除表里内容，保持表结构和名称

truncate 表名清空表内容。drop 表名是删除整个表。select boy.hid,boy.bname,girl.hid girl.gname from boy inner join girl on boy.hid=girl.hid内链接。

#左右连接select boy.hid,boy.bname,girl.hid girl.gname from boy left  join girl on boy.hid=girl.hid

select boy.hid,boy.bname,girl.hid girl.gname from boy right join girl on boy.hid=girl.hid

#连接之后可看做一个大表，进行操作。

#查询第4个栏目下的商品名，栏目名、价格

select goods_name,cat_name,shop_price from goods left join category on goods.cat_id=category .cat_id  where goods.cat_id=4

#将两个表合并，然后把主决赛序号和队名连在一起，把副决赛序号和队名连在一起。输出格式为mid,hanme(主决赛),mers(比分),gteam(副决赛),matime决赛时间。

select mid ,t1.tname as hname,mers,t2.tname as gteam,matime from m inner join t as t1 on m.hid=t1.hid inner join t as t2 on m.gid=t2.tid

###在上面的基础上，查出2006-6-1到2006-7-1之间的所有比赛。

select mid ,t1.tname as hname,mers,t2.tname as gteam,matime from m inner join t as t1 on m.hid=t1.hid inner join t as t2 on m.gid=t2.tid  where matime between '2006-06-01' and '2006-07-01' ;

##union 把2条或多条sql的查询结果，合并成1个结果集。一个表中的两个条件，或者是两个表中的的条件，但是必须满足，各语句取出的列数要相同，列名称未必要一致，union使用时，完全相等的行将会被合并。合并是比较耗时的操作，因此，一般情况下，要用union all来避免合并。union的字句中不用写order by，没效果。合在一起的总结果，可以用order by .

select goods_id,cat_id from goods   where cat_id=4 union all  select goods_id,cat_id from goods   where cat_id=2;

select uid,name from user union select id,name from temp

##面试题两张表相同的id求和。

select  id ,sum(num) from (select * from a union all select * from b) as temp group by id;

 --查看表结构
 
 desc hy_order_20170410;
 -- 查看前面10行；用来熟悉表结构和内容；
 select * 
 from hy_order_20170410
 limit 10;
 ---找出购买订单金额大于200的顾客
 select id  from hy_order_20170410 where order_money>200;
 --#6.会员会不会重复 distinct
 select distinct  id from hy_order_20170410 where order_money>200;
 ---找出购买订单金额大于20小于200的顾客
 select distinct  id from hy_order_20170410 where order_money>20 and order_money<200; 
 ---按订单金额进行结果降序
 select * from hy_order_20170410  order by order_money desc;
 /******
 * 练习group by 
 * group by 必须结合汇总函数
 * 汇总函数：max,min,avg,count,sum
 * 
 * 
 ******/
---统计顾客的累计购买金额
use exerci2;
select id
  ,sum(order_money) 
from hy_order_20170410
group by id; 
---取别名 as 
---字段可以取别名
---给表取别名
select id as member_name
  ,sum(order_money) as sum_money
from hy_order_20170410
group by  id; 
select a.member_id
  ,sum(order_money) as sum_money
from hy_order_20170410 as a 
group by member_id; 
---统计各个顾客每天的购买金额
select  id
  ,date_format(create_time,'%Y-%m-%d') as day_1
  ,sum(order_money) as sum_money
from hy_order_20170410 
group by  id
  ,date_format(create_time,'%Y-%m-%d'); 
/********
 * 
 * where 条件练习
 * 
 * like 练习
 * 
 */**/
---找出购买订单金额大于20小于200的顾客
---区间用 between and 
 select distinct  id
 from hy_order_20170410
 where order_money>20 and order_money<200; 
  select distinct  id
 from hy_order_20170410
 where order_money between 20 and 200; 
 --查看订单状态为空的记录
 select * 
 from hy_order_20170410
 where status is null; 
 select * 
 from hy_order_20170410
 where status is not null; 
 ---不同订单状态会员数
 select status
  ,count(distinct member_id)  as member_number
 from hy_order_20170410
 group by status;
 ---以A开始C结尾的记录
select * 
from hy_order_20170410
where id like 'A%C';  
select * 
from  hy_order_20170410
where id like 'A_C'; 
/*********
 * 
 * 进行时间函数必须掌握
 * 时间维度是数据分析最为重要的分析维度之一；
 * 天：上午，下午，中午
 * 周：
 * 月：
 * 时间：
 * 
 * 节假日，农历
 * 很重要趋势分析：每天 ，每周，每月
 */*/

--- 默认是星期天是用1，按国外习惯 

select dayofweek(create_time) as dday
   ,week(create_time) as wweek
   ,month(create_time) as mmonth
   ,dayofmonth(create_time) as monthofday
   ,dayofyear(create_time) as dayofyear
       ,create_time
from hy_order_20170410  
---日期
select create_time
   ,date_format(create_time,'%Y-%m-%d') as type1
   ,date_format(create_time,'%y-%m-%d') as type2
   ,date_format(create_time,'%H') as type3
   ,date_format(create_time,'%h') as type4
from hy_order_20170410

/***
 * 
 * hy_order_20170410 订单表 包括：订单ID 订单金额 会员ID 创建时间 订单状态
 * 
 * hy_orderitem_20170410 订单明细表 包括：订单明细表的ID 订单ID 商品ID 商品名称 商品价格 商品数量 
 * 
 * 
 * */

###重要alter database demo character set utf8;

alter table hy_member_20170410 character set utf8;