SQl_入门经典_code

ttps://wenku.baidu.com/view/76da1e01581b6bd97f19ea3c.html

https://wenku.baidu.com/view/920fb19f852458fb760b56a9.html

CREATE DATABASE CRASHCOURSE;

USE CRASHCOURSE;

CREATE TABLE EMPLOYEE_TBL

(

EMP_ID    VARCHAR(9)    NOT NULL,

LAST_NAME    VARCHAR(15)    NOT NULL,

FIRST_NAME    VARCHAR(15)    NOT NULL,

MIDDLE_NAME    VARCHAR(15),

[ADDRESS]    VARCHAR(30)    NOT NULL,

CITY    VARCHAR(15)    NOT NULL,

[STATE]    CHAR(2)    NOT NULL,

ZIP    INTEGER    NOT NULL,

PHONE    CHAR(10),

PAGER    CHAR(10)

CONSTRAINT EMP_PK PRIMARY KEY (EMP_ID)

);

CREATE TABLE EMPLOYEE_PAY_TBL

(

EMP_ID    VARCHAR(9)    NOT NULL    PRIMARY KEY,

POSITION    VARCHAR(15)    NOT NULL,

DATE_HIRE    DATE,

PAY_RATE    DECIMAL(4,2),

DATE_LAST_RAISE    DATE,

SALARY    DECIMAL(8,2),

BONUS    DECIMAL(6,2),

CONSTRAINT EMP_FK FOREIGN KEY (EMP_ID) REFERENCES EMPLOYEE_TBL (EMP_ID)

);

CREATE TABLE CUSTOMER_TBL

(

CUST_ID    VARCHAR(10)    NOT NULL    PRIMARY KEY,

CUST_NAME    VARCHAR(30)    NOT NULL,

CUST_ADDRESS    VARCHAR(20)    NOT NULL,

CUST_CITY    VARCHAR(15)    NOT NULL,

CUST_STATE    CHAR(2)    NOT NULL,

CUST_ZIP    INTEGER    NOT NULL,

CUST_PHONE    CHAR(10),

CUST_FAX    VARCHAR(10)

);

CREATE TABLE ORDERS_TBL

(

ORD_NUM    VARCHAR(10)    NOT NULL    PRIMARY KEY,

CUST_ID    VARCHAR(10)    NOT NULL,

PROD_ID    VARCHAR(10)    NOT NULL,

QTY    INTEGER    NOT NULL,

ORD_DATE    DATE

);

CREATE TABLE PRODUCTS_TBL

(

PROD_ID    VARCHAR(10)    NOT NULL    PRIMARY KEY,

PROD_DESC    VARCHAR(40)    NOT NULL,

COST    DECIMAL(6,2)    NOT NULL

);

INSERT INTO EMPLOYEE_TBL VALUES

(

'311549902','STEPHENS','TINA','DAWN','RR 3 BOX 17A',

'GREENWOOD','IN','47890','3178784465',NULL

)

INSERT INTO EMPLOYEE_TBL VALUES

(

'442346889','PLEW','LINDA','CAROL','3301 BEACON','INDIANAPOLIS',

'IN','46224','3172978990',NULL

)

INSERT INTO EMPLOYEE_TBL VALUES

(

'213764555','GLASS','BRANDON','SCOTT','1710 MAIN ST','WHITELAND',

'IN','47885','3178984321','31757676'

)

INSERT INTO EMPLOYEE_TBL VALUES

(

'313782439','GLASS','JACOB',NULL,'3789 WHITE RIVER BLVD',

'INDIANAPOLIS','IN','45734','3175457676','8887345678'

)

INSERT INTO EMPLOYEE_TBL VALUES

(

'220984332','WALLACE','MARIAH',NULL,'7889 KEYSTONE AVE',

'INDIANAPOLIS','IN','46741','3173325986',NULL

)

INSERT INTO EMPLOYEE_TBL VALUES

(

'443679012','SPURGEON','TIFFANY',NULL,'5 GEORGE COURT',

'INDIANAPOLIS','IN','46234','3175679007',NULL

)

INSERT INTO EMPLOYEE_PAY_TBL VALUES

(

'311549902','MARKETING','1999-05-23',NULL,'2009-05-01','4000',NULL

)

INSERT INTO EMPLOYEE_PAY_TBL VALUES

(

'442346889','TEAM LEADER','2000-06-17','14.75','2009-06-01',NULL,NULL

)

INSERT INTO EMPLOYEE_PAY_TBL VALUES

(

'213764555','SALES MANAGER','2004-08-14',NULL,'2009-08-01','30000','2000'

)

INSERT INTO EMPLOYEE_PAY_TBL VALUES

(

'313782439','SALESMAN','2007-06-28',NULL,NULL,'20000','1000'

)

INSERT INTO EMPLOYEE_PAY_TBL VALUES

(

'220984332','SHIPPER','2006-07-22','11.00','1999-07-01',NULL,NULL

)

INSERT INTO EMPLOYEE_PAY_TBL VALUES

(

'443679012','SHIPPER','2001-01-14','15.00','1999-01-01',NULL,NULL

)

INSERT INTO CUSTOMER_TBL VALUES

(

'232','LESLE GLEASO

N','798 HARDAWAY DR','INDIANAPOLIS',

'IN','47856','3175457690',NULL

)

INSERT INTO CUSTOMER_TBL VALUES

(

'109','NANCY BUNKER','APT A 4556 WATERWAY','BROAD RIPPLE',

'IN','47950','3174262323',NULL

)

INSERT INTO CUSTOMER_TBL VALUES

(

'345','ANGELA DOBKO','RR3 BOX 76','LEBANON','IN','49967',

'7658970090',NULL

)

INSERT INTO CUSTOMER_TBL VALUES

(

'090','WENDY WOLF','3345 GATEWAY DR','INDIANAPOLIS','IN',

'46224','3172913421',NULL

)

INSERT INTO CUSTOMER_TBL VALUES

(

'12','MARYS GIFT SHOP','435 MAIN ST','DANVILLE','IL','47978',

'3178567221','3178523434'

)

INSERT INTO CUSTOMER_TBL VALUES

(

'432','SCOTTYS MARKET','RR2 BOX 173','BROWNSBURG','IN',

'45687','3178529835','3178529836'

)

INSERT INTO CUSTOMER_TBL VALUES

(

'333','JASONS AND DALLAS GOODIES','LAFAYETTE SQ MALL',

'INDIANAPOLIS','IN','46222','3172978886','317298887'

)

INSERT INTO CUSTOMER_TBL VALUES

(

'21','MORGANS CANDIES AND TREATS','5657 W TENTH ST',

'INDIANAPOLIS','IN','46234','3172714398',NULL

)

INSERT INTO CUSTOMER_TBL VALUES

(

'43','SCHYLERS NOVELTIES','17 MAPLE ST','LEBANON','IN',

'48990','3174346758',NULL

)

INSERT INTO CUSTOMER_TBL VALUES

(

'287','GAVINS PLACE','9880 ROCKVILLE RD','INDIANAPOLIS',

'IN','46224','3172719991','3172719992'

)

INSERT INTO CUSTOMER_TBL VALUES

(

'288','HOLLYS GAMEARAMA','567 US 31 SOUTH','WHITELAND',

'IN','49980','3178879023',NULL

)

INSERT INTO CUSTOMER_TBL VALUES

(

'509','HEATHERS FEATHERS AND THINGS','4090 N SHADELAND AVE',

'INDIANAPOLIS','IN','43278','3175456768',NULL

)

INSERT INTO CUSTOMER_TBL VALUES

(

'610','REGANS HOBBIES INC','451 GREEN ST','PLAINFIELD','IN',

'46818','3178393441','3178399090'

)

INSERT INTO CUSTOMER_TBL VALUES

(

'560','ANDYS CANDIES','RR 1 BOX 34','NASHVILLE','IN',

'48756','8123239871',NULL

)

INSERT INTO CUSTOMER_TBL VALUES

(

'221','RYANS STUFF','2337 S SHELBY ST','INDIANAPOLIS','IN',

'47834','3175634402',NULL

)

INSERT INTO ORDERS_TBL VALUES

(

'56A901','232','11235','1','2009-10-22'

)

INSERT INTO ORDERS_TBL VALUES

(

'56A917','12','907','100','2009-09-30'

)

INSERT INTO ORDERS_TBL VALUES

(

'32A132','43','222','25','2009-10-10'

)

INSERT INTO ORDERS_TBL VALUES

(

'16C17','090','222','2','2009-10-17'

)

INSERT INTO ORDERS_TBL VALUES

(

'18D778','287','90','10','2009-10-17'

)

INSERT INTO ORDERS_TBL VALUES

(

'23E934','432','13','20','2009-10-15'

)

INSERT INTO PRODUCTS_TBL VALUES

(

'11235','WITCH COSTUME','29.99'

)

INSERT INTO PRODUCTS_TBL VALUES

(

'222','PLASTIC PUMPKIN 18 INCH','7.75'

)

INSERT INTO PRODUCTS_TBL VALUES

(

'13','FALSE PARAFFIN TEETH','1.10'

)

INSERT INTO PRODUCTS_TBL VALUES

(

'90','LIGHTED LANTERNS','14.50'

)

INSERT INTO PRODUCTS_TBL VALUES

(

'15','ASSORTED COSTUMES','10.00'

)

INSERT INTO PRODUCTS_TBL VALUES

(

'9','CANDY CORN','1.35'

)

INSERT INTO PRODUCTS_TBL VALUES

(

'6','PUMPKIN CANDY','1.45'

)

INSERT INTO PRODUCTS_TBL VALUES

(

'87','PLASTIC SPIDERS','1.05'

)

INSERT INTO PRODUCTS_TBL VALUES

(

'119','ASSORTED MASKS','4.95'

)

View Code

select * from CRASHCOURSE.dbo.EMPLOYEE_TBL
select * from EMPLOYEE_TBL
select * from EMPLOYEE_PAY_TBL
select * from CUSTOMER_TBL
select * from ORDERS_TBL
select * from PRODUCTS_TBL


--  不相等
select 
E.EMP_ID,
E.LAST_NAME,
P.POSITION
from

EMPLOYEE_TBL E,
EMPLOYEE_PAY_TBL P
where
E.EMP_ID<>P.EMP_ID
;

--导表sql server

select * 
into 
    intoproducts_tmp

from 
    products_tbl


create table xx as( select...) --oracle

select 
    s.city,
    p.pay_rate,
    p.salary

into 
    EMP_PAY_TMP
from 
    CRASHCOURSE.dbo.employee_tbl s left join CRASHCOURSE.dbo.employee_pay_tbl p
on 
    s.emp_id=p.emp_id

select 
    prod_desc,
    prod_id,
    cost


from CRASHCOURSE.dbo.products_tbl

where cost< 20

order by prod_desc asc;

--order by 1,2,3 列顺序

select 

    *

from 
    CRASHCOURSE.dbo.products_tbl


where
     prod_id 

in ('13','9','87','119')

--exist 例子

select

            cost
        from
            CRASHCOURSE.dbo.products_tbl
    where 
        cost <100

order by 
    cost



select 
    cost
from 
    CRASHCOURSE.dbo.products_tbl

where
     
    exists(
        select 
            cost
        from
            CRASHCOURSE.dbo.products_tbl
    where 
        cost <100
    )

order by 
    cost

select 
    count(ord_num),
    sum(qty),
    sum(qty)*1.00/count(ord_num) avg_qty
from
    CRASHCOURSE.dbo.orders_tbl

select 

    sum(salary)

from
    CRASHCOURSE.dbo.employee_pay_tbl

group by
    salary

select 
    City,
    count(*)--行数

from
    CRASHCOURSE.dbo.employee_tbl

group by

--emp_pay_tmp


select 
    s.city,
    p.pay_rate,
    p.salary


from 
    CRASHCOURSE.dbo.employee_tbl s left join CRASHCOURSE.dbo.employee_pay_tbl p
on 
    s.emp_id=p.emp_id



select 

    s.city,
    avg(p.pay_rate) avgpayrate,
    avg(p.salary) avgsalary


from 
    CRASHCOURSE.dbo.employee_tbl s left join CRASHCOURSE.dbo.employee_pay_tbl p
on 
    s.emp_id=p.emp_id
group by
    city;

select 

    s.city,
    avg(p.pay_rate) avgpayrate,
    avg(p.salary) avgsalary


from 
    CRASHCOURSE.dbo.employee_tbl s left join CRASHCOURSE.dbo.employee_pay_tbl p
on 
    s.emp_id=p.emp_id 
    
group by
    city

having
     s.city in ('indianapolis','whiteland')

order by 
 2 ,3;

select 
    s.city,
    max(p.pay_rate),
    min(p.salary)


from 
    CRASHCOURSE.dbo.employee_tbl s left join CRASHCOURSE.dbo.employee_pay_tbl p
on 
    s.emp_id=p.emp_id

group by
    city

/*
ROLLUP和CUBE 用法           Oracle的GROUP BY语句除了最基本的语法外，还支持ROLLUP和CUBE语句。如果是Group by  ROLLUP(A, B, C)的话，首先会对(A、B、C)进行GROUP BY，然后对(A、B)进行GROUP BY，然后是(A)进行GROUP BY，最后对全表进行GROUP BY操作。

      如果是GROUP BY CUBE(A, B, C)，则首先会对(A、B、C)进行GROUP BY，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，最后对全表进行GROUP BY操作。 grouping_id()可以美化效果。除了使用GROUPING函数，还可以使用GROUPING_ID来标识GROUP BY的结果。

       也可以 Group by Rollup(A,(B,C)) ，Group by A Rollup(B,C)，…… 这样任意按自己想要的形式结合统计数据，非常方便。

Rollup()：分组函数可以理解为group by的精简模式，具体分组模式如下：

　　Rollup(a,b,c): (a,b,c),(a,b),(a),(全表)

Cube():分组函数也是以group by为基础，具体分组模式如下：

　　cube(a,b,c):(a,b,c),(a,b),(a,c),(b,c),(a),(b),(c),(全表)
*/


select 
    e.city,
    e.zip,
    AVG(p.pay_rate),
    AVG(p.salary)


from EMPLOYEE_TBL E
inner join EMPLOYEE_PAY_TBL P

on 

    e.EMP_ID=p.EMP_ID

group by e.city,e.zip
order by city,zip

---------------

select 
    e.city,
    e.zip,
    AVG(p.pay_rate),
    AVG(p.salary)


from EMPLOYEE_TBL E
inner join EMPLOYEE_PAY_TBL P

on 

    e.EMP_ID=p.EMP_ID

group by rollup (e.city,e.zip)


select 
    e.city,
    e.zip,
    AVG(p.pay_rate),
    AVG(p.salary)


from EMPLOYEE_TBL E
inner join EMPLOYEE_PAY_TBL P

on 

    e.EMP_ID=p.EMP_ID

group by cube (e.city,e.zip)

select
    city,
    AVG(pay_rate),
    avg(salary)

from 
    emp_pay_tmp
where city<> 'greewood'

group by city

having avg(salary)>20000
order by 3

--coalesce，按顺序，显示第一个不是null的 单元格
select 
    *
from 
    EMPLOYEE_PAY_TBL

select 
    
    EMP_ID,
    coalesce(BONUS,salary,pay_rate)
from 
    EMPLOYEE_PAY_TBL

select ascii('A')

65

select 
    emp_id,
    concat(last_name,',',first_name) name,
    concat(substring(emp_id,1,3),'-',
        substring(emp_id,4,2),'-',
        substring(emp_id,6,4)
        )
     ID
from 
    EMPLOYEE_TBL

select getdate();

select now();

select 
    date_hire,
    dateadd(month,1,date_hire)

from EMPLOYEE_PAY_TBL;

select 
    p.PROD_desc,
    o.QTY
from PRODUCTS_TBL p,
    ORDERS_TBL o
where
    p.PROD_ID=o.PROD_ID


select 
    p.PROD_desc,
    o.QTY
from 
    PRODUCTS_TBL p
left outer join
    ORDERS_TBL o
ON
    p.PROD_ID=o.PROD_ID;

--查询表结构
sp_help orders_tbl
sp_columns orders_tbl

--union 显示不重复 
select
    emp_id from EMPLOYEE_TBL
union
select
    emp_id from EMPLOYEE_pay_TBL;


--不相关查询
select
    PROD_DESC from PRODUCTS_TBL
union
select
    LAST_NAME from EMPLOYEE_TBL;

--xml
select 

    salary,
    pay_rate

from 
    EMP_pay_tmp
for xml auto

select 
    count(*),
    count(1),
    count(2),
    count(3),
    count(100)
    
from EMP_PAY_TMP

--oracle
http://blog.csdn.net/szstephenzhou/article/details/8446481  Oracle 中count(1) 和count(*) 的区别



select 

  count(1),
  count(3),
  count(100),
  count(*)

from

  sc

posted @ 2017-12-07 16:34 木石1987 阅读(272) 评论(0) 收藏举报

刷新页面返回顶部

阿伯特

SQl_入门经典_code

公告