DROP TABLE s_emp;

DROP TABLE s_dept;

DROP TABLE s_region;

DROP TABLE s_ord;

DROP TABLE s_customer;

CREATE TABLE s_emp
(id                     INT(7),
 last_name                  VARCHAR(25),
 first_name                 VARCHAR(25),
 userid                     VARCHAR(8),
 start_date                 DATE,
 comments                   VARCHAR(255),
 manager_id                 INT(7),
 title                      VARCHAR(25),
 dept_id                    INT(7),
 salary                      FLOAT(11, 2),
 commission_pct             FLOAT(4, 2),
 CONSTRAINT s_emp_id_pk PRIMARY KEY (id),
 CONSTRAINT s_emp_userid_uk UNIQUE (userid),
 CONSTRAINT s_emp_commission_pct_ck
 CHECK (commission_pct IN (10, 12.5, 15, 17.5, 20))) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

INSERT INTO s_emp VALUES (
    1, 'Velasquez', 'Carmen', 'cvelasqu',
    '1990-05-03', NULL, NULL, 'President',
    50, 2500, NULL);

INSERT INTO s_emp VALUES (
    2, 'Ngao', 'LaDoris', 'lngao',
    '1990-05-08', NULL, 1, 'VP, Operations',
    41, 1450, NULL);

INSERT INTO s_emp VALUES (
    3, 'Nagayama', 'Midori', 'mnagayam',
    '1991-07-01', NULL, 1, 'VP, Sales',
    31, 1400, NULL);

INSERT INTO s_emp VALUES (
    4, 'Quick-To-See', 'Mark', 'mquickto',
    '1990-07-07', NULL, 1, 'VP, Finance',
    10, 1450, NULL);

INSERT INTO s_emp VALUES (
    5, 'Ropeburn', 'Audry', 'aropebur',
    '1990-04-05', NULL, 1, 'VP, Administration',
    50, 1550, NULL);

INSERT INTO s_emp VALUES (
    6, 'Urguhart', 'Molly', 'murguhar',
    '1991-01-18', NULL, 2, 'Warehouse Manager',
    41, 1200, NULL);

INSERT INTO s_emp VALUES (
    7, 'Menchu', 'Roberta', 'rmenchu',
    '1990-05-14', NULL, 2, 'Warehouse Manager',
    42, 1250, NULL);

INSERT INTO s_emp VALUES (
    8, 'Biri', 'Ben', 'bbiri',
    '1990-06-07', NULL, 2, 'Warehouse Manager',
    43, 1100, NULL);

INSERT INTO s_emp VALUES (
    9, 'Catchpole', 'Antoinette', 'acatchpo',
    '1992-02-09', NULL, 2, 'Warehouse Manager',
    44, 1300, NULL);

INSERT INTO s_emp VALUES (
    10, 'Havel', 'Marta', 'mhavel',
    '1991-02-27', NULL, 2, 'Warehouse Manager',
    45, 1307, NULL);

INSERT INTO s_emp VALUES (
    11, 'Magee', 'Colin', 'cmagee',
    '1990-04-14', NULL, 3, 'Sales Representative',
    31, 1400, 10);

INSERT INTO s_emp VALUES (
    12, 'Giljum', 'Henry', 'hgiljum',
    '1992-01-18', NULL, 3, 'Sales Representative',
    32, 1490, 12.5);

INSERT INTO s_emp VALUES (
    13, 'Sedeghi', 'Yasmin', 'ysedeghi',
    '1991-02-18', NULL, 3, 'Sales Representative',
    33, 1515, 10);

INSERT INTO s_emp VALUES (
    14, 'Nguyen', 'Mai', 'mnguyen',
    '1992-01-22', NULL, 3, 'Sales Representative',
    34, 1525, 15);

INSERT INTO s_emp VALUES (
    15, 'Dumas', 'Andre', 'adumas',
    '1991-10-09', NULL, 3, 'Sales Representative',
    35, 1450, 17.5);

INSERT INTO s_emp VALUES (
    16, 'Maduro', 'Elena', 'emaduro',
    '1992-02-17', NULL, 6, 'Stock Clerk',
    41, 1400, NULL);

INSERT INTO s_emp VALUES (
    17, 'Smith', 'George', 'gsmith',
    '1990-05-08', NULL, 6, 'Stock Clerk',
    41, 940, NULL);

INSERT INTO s_emp VALUES (
    18, 'Nozaki', 'Akira', 'anozaki',
    '1991-02-18', NULL, 7, 'Stock Clerk',
    42, 1200, NULL);

INSERT INTO s_emp VALUES (
    19, 'Patel', 'Vikram', 'vpatel',
    '1991-06-08', NULL, 7, 'Stock Clerk',
    42, 795, NULL);

INSERT INTO s_emp VALUES (
    20, 'Newman', 'Chad', 'cnewman',
    '1991-01-21', NULL, 8, 'Stock Clerk',
    43, 750, NULL);

INSERT INTO s_emp VALUES (
    21, 'Markarian', 'Alexander', 'amarkari',
    '1991-04-26', NULL, 8, 'Stock Clerk',
    43, 850, NULL);

INSERT INTO s_emp VALUES (
    22, 'Chang', 'Eddie', 'echang',
    '1990-11-30', NULL, 9, 'Stock Clerk',
    44, 800, NULL);

INSERT INTO s_emp VALUES (
    23, 'Patel', 'Radha', 'rpatel',
    '1990-10-17', NULL, 9, 'Stock Clerk',
    34, 795, NULL);

INSERT INTO s_emp VALUES (
    24, 'Dancs', 'Bela', 'bdancs',
    '1991-05-17', NULL, 10, 'Stock Clerk',
    45, 860, NULL);

INSERT INTO s_emp VALUES (
    25, 'Schwartz', 'Sylvie', 'sschwart',
    '1991-04-09', NULL, 10, 'Stock Clerk',
    45, 1100, NULL);

CREATE TABLE s_dept
(id                         int(7),
 name                       VARCHAR(25),
 region_id                  int(7),
 CONSTRAINT s_dept_id_pk PRIMARY KEY (id),
 CONSTRAINT s_dept_name_region_id_uk UNIQUE (name, region_id)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

INSERT INTO s_dept VALUES (
    10, 'Finance', 1);
INSERT INTO s_dept VALUES (
    31, 'Sales', 1);
INSERT INTO s_dept VALUES (
    32, 'Sales', 2);
INSERT INTO s_dept VALUES (
    33, 'Sales', 3);
INSERT INTO s_dept VALUES (
    34, 'Sales', 4);
INSERT INTO s_dept VALUES (
    35, 'Sales', 5);
INSERT INTO s_dept VALUES (
    41, 'Operations', 1);
INSERT INTO s_dept VALUES (
    42, 'Operations', 2);
INSERT INTO s_dept VALUES (
    43, 'Operations', 3);
INSERT INTO s_dept VALUES (
    44, 'Operations', 4);
INSERT INTO s_dept VALUES (
    45, 'Operations', 5);
INSERT INTO s_dept VALUES (
    50, 'Administration', 1);

CREATE TABLE s_region
(id                         int(7),
 name                    VARCHAR(50),
 CONSTRAINT s_region_id_pk PRIMARY KEY (id),
 CONSTRAINT s_region_name_uk UNIQUE (name)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

 INSERT INTO s_region VALUES (
     1, 'North America');
INSERT INTO s_region VALUES (
    2, 'South America');
INSERT INTO s_region VALUES (
    3, 'Africa / Middle East');
INSERT INTO s_region VALUES (
    4, 'Asia');
INSERT INTO s_region VALUES (
    5, 'Europe');

CREATE TABLE s_customer
(id                         int(7),
 name                    VARCHAR(50),
 phone                   VARCHAR(25),
 address                VARCHAR(400),
 city                        VARCHAR(30),
 state                      VARCHAR(20),
 country                    VARCHAR(30),
 zip_code                   VARCHAR(75),
 credit_rating              VARCHAR(9),
 sales_rep_id               int(7),
 region_id                  int(7),
 comments                   VARCHAR(255),
 CONSTRAINT s_customer_id_pk PRIMARY KEY (id),
 CONSTRAINT s_customer_credit_rating_ck CHECK (credit_rating IN ('EXCELLENT', 'GOOD', 'POOR')))ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

INSERT INTO s_customer VALUES (
    201, 'Unisports', '55-2066101',
    '72 Via Bahia', 'Sao Paolo', NULL, 'Brazil', NULL,
    'EXCELLENT', 12, 2, NULL);
INSERT INTO s_customer VALUES (
    202, 'OJ Atheletics', '81-20101',
    '6741 Takashi Blvd.', 'Osaka', NULL, 'Japan', NULL,
    'POOR', 14, 4, NULL);
INSERT INTO s_customer VALUES (
    203, 'Delhi Sports', '91-10351',
    '11368 Chanakya', 'New Delhi', NULL, 'India', NULL,
    'GOOD', 14, 4, NULL);
INSERT INTO s_customer VALUES (
    204, 'Womansport', '1-206-104-0103',
    '281 King Street', 'Seattle', 'Washington', 'USA', NULL,
    'EXCELLENT', 11, 1, NULL);
INSERT INTO s_customer VALUES (
    205, 'Kam''s Sporting Goods', '852-3692888',
    '15 Henessey Road', 'Hong Kong', NULL, NULL, NULL,
    'EXCELLENT', 15, 4, NULL);
INSERT INTO s_customer VALUES (
    206, 'Sportique', '33-2257201',
    '172 Rue de Rivoli', 'Cannes', NULL, 'France', NULL,
    'EXCELLENT', 15, 5, NULL);
INSERT INTO s_customer VALUES (
    207, 'Sweet Rock Sports', '234-6036201',
    '6 Saint Antoine', 'Lagos', NULL, 'Nigeria', NULL,
    'GOOD', NULL, 3, NULL);
INSERT INTO s_customer VALUES (
    208, 'Muench Sports', '49-527454',
    '435 Gruenestrasse', 'Stuttgart', NULL, 'Germany', NULL,
    'GOOD', 15, 5, NULL);
INSERT INTO s_customer VALUES (
    209, 'Beisbol Si!', '809-352689',
    '792 Playa Del Mar', 'San Pedro de Macon''s', NULL, 'Dominican Republic',
    NULL, 'EXCELLENT', 11, 1, NULL);
INSERT INTO s_customer VALUES (
    210, 'Futbol Sonora', '52-404562',
    '3 Via Saguaro', 'Nogales', NULL, 'Mexico', NULL,
    'EXCELLENT', 12, 2, NULL);
INSERT INTO s_customer VALUES (
    211, 'Kuhn''s Sports', '42-111292',
    '7 Modrany', 'Prague', NULL, 'Czechoslovakia', NULL,
    'EXCELLENT', 15, 5, NULL);
INSERT INTO s_customer VALUES (
    212, 'Hamada Sport', '20-1209211',
    '57A Corniche', 'Alexandria', NULL, 'Egypt', NULL,
    'EXCELLENT', 13, 3, NULL);
INSERT INTO s_customer VALUES (
    213, 'Big John''s Sports Emporium', '1-415-555-6281',
    '4783 18th Street', 'San Francisco', 'CA', 'USA', NULL,
    'EXCELLENT', 11, 1, NULL);
INSERT INTO s_customer VALUES (
    214, 'Ojibway Retail', '1-716-555-7171',
    '415 Main Street', 'Buffalo', 'NY', 'USA', NULL,
    'POOR', 11, 1, NULL);
INSERT INTO s_customer VALUES (
    215, 'Sporta Russia', '7-3892456',
    '6000 Yekatamina', 'Saint Petersburg', NULL, 'Russia', NULL,
    'POOR', 15, 5, NULL);

CREATE TABLE s_ord
(id                         int(7),
 customer_id           int(7),
 date_ordered               DATE,
 sales_rep_id               int(7),
 total                      FLOAT(11, 2),
 payment_type               VARCHAR(6),
 order_filled               VARCHAR(1),
 CONSTRAINT s_ord_id_pk PRIMARY KEY (id),
 CONSTRAINT s_ord_payment_type_ck CHECK (payment_type in ('CASH', 'CREDIT')),
 CONSTRAINT s_ord_order_filled_ck CHECK (order_filled in ('Y', 'N')))ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

INSERT INTO s_ord VALUES (
    100, 204, '1992-08-31',
    11, 601100, 'CREDIT', 'Y');
INSERT INTO s_ord VALUES (
    101, 205, '1992-08-31',
    14, 8056.6, 'CREDIT', 'Y');
INSERT INTO s_ord VALUES (
    102, 206, '1992-09-01',
    15, 8335, 'CREDIT', 'Y');
INSERT INTO s_ord VALUES (
    103, 208, '1992-09-02',
    15, 377, 'CASH', 'Y');
INSERT INTO s_ord VALUES (
    104, 208, '1992-09-03',
    15, 32430, 'CREDIT', 'Y');
INSERT INTO s_ord VALUES (
    105, 209, '1992-04-01',
    11, 2722.24, 'CREDIT', 'Y');
INSERT INTO s_ord VALUES (
    106, 210, '1992-09-07',
    12, 15634, 'CREDIT', 'Y');
INSERT INTO s_ord VALUES (
    107, 211, '1992-08-07',
    15, 142171, 'CREDIT', 'Y');
INSERT INTO s_ord VALUES (
    108, 212, '1992-09-07',
    13, 149570, 'CREDIT', 'Y');
INSERT INTO s_ord VALUES (
    109, 213, '1992-09-08',
    11, 1020935, 'CREDIT', 'Y');
INSERT INTO s_ord VALUES (
    110, 214, '1992-09-09',
    11, 1539.13, 'CASH', 'Y');
INSERT INTO s_ord VALUES (
    111, 204, '1992-09-09',
    11, 2770, 'CASH', 'Y');
INSERT INTO s_ord VALUES (
    97, 201, '1992-09-09',
    12, 84000, 'CREDIT', 'Y');
INSERT INTO s_ord VALUES (
    98, 202, '1992-09-09',
    14, 595, 'CASH', 'Y');
INSERT INTO s_ord VALUES (
    99, 203, '1992-09-09',
    14, 7707, 'CREDIT', 'Y');
INSERT INTO s_ord VALUES (
    112, 210, '1992-09-09',
    12, 550, 'CREDIT', 'Y');

-- 找出员工的所有的信息;*代替所有的列(企业中不允许)
select * from s_emp;

-- 从s_emp表中查询出员工的名字,薪水,职位;
select first_name,salary,title from s_emp;
-- 从s_emp表中查出员工的姓氏,名字;
select last_name,first_name from s_emp;

-- 找出本公司的所有的职称(title);
select title from s_emp;

-- 补充 - title发现有大量重复的,排重关键字distinct
select distinct title from s_emp;

-- 找出所有员工的姓名、工资、入职日期
select last_name,first_name,salary,start_date from s_emp;

-- 找出所有的客户名及他的电话号码
select name,phone from s_customer;

-- 找出员工姓名及他的职称
select last_name,first_name,title from s_emp;

-- 找出每个订单的费用、支付方式、出货日期
select total,payment_type,date_ordered from s_ord;

-- 查询员工的名字，他的年薪（不包含提成）
select first_name,salary * 12 from s_emp;

select concat(last_name,' ',first_name) from s_emp;

select salary*12 年薪 from s_emp;
select salary*12 as 年薪 from s_emp;
select salary*12 "年薪" from s_emp;
select salary*12 as "年薪" from s_emp;

-- error
select salary*12 年 薪 from s_emp;
-- success
select salary*12 "年 薪" from s_em

-- 输出员工的id,firstname以及员工的年薪[包含提成]
select id,first_name,salary*12*(1+commission_pct/100) '年薪' from s_emp;
-- 上述语句只有有提出的才显示出来
-- 结论：null 的列参加计算的时候，结果是null
select id,first_name,salary*12*(1+coalesce(commission_pct,0)/100) '年薪' from s_emp;

> < >= <= <> =

-- 闭区间[m,n],在[m,n]之间 
between m and n 
-- 在 ... 之内
in 
-- 不在 ... 之内
not in
-- SQL语句优化的时候不建议使用 not in 语法 - 会导致索引失效
-- 注意的是：对于null值得判断不能使用 = 
-- 判断为空
is null 
-- 判断不为空
is not null

-- 连接条件表达式，当所有得表达式同时成立，结果为 true
and
-- 连接条件表达式，只要一个为true，结果就为true
or

-- 比如查询first_name中包含c的员工.
select first_name from s_emp where first_name like '%c%';//模糊查询大小写不敏感

-- 查询第三个字母是a
select first_name from s_emp where first_name like '__a%';

-- 把名字更新一下
update s_emp set first_name='Car%men' where id=1;
update s_emp set first_name='Mar%k' where id=4;

-- 查询
select * from s_emp where first_name like '%\%%';

select * from s_emp where first_name like '%?%%' escape '?';

select first_name,salary from s_emp where dept_id=41;

select id,first_name,salary from s_emp where salary>1500;

select concat(last_name,' ',first_name) 姓名,salary,title from s_emp where salary>1200;

select first_name,salary,dept_id from s_emp where dept_id=41 or dept_id=42 or dept_id=50;

select first_name,salary,dept_id from s_emp where dept_id in(41,42,50);

select first_name,salary from s_emp where salary>=1200 and salary<=1500;
select first_name,salary from s_emp where salary between 1200 and 1500;

select first_name from s_emp where first_name like 'M%';

select first_name from s_emp where first_name like '__e%';

select first_name from s_emp where commision_pct is null;

select first_name from s_emp where coalesce(commission_pct,0)=0;

select id,payment_type,total from s_ord where total>10000;

select first_name,title,salary from s_emp where salary between 950 and 1200;

select first_name from s_emp where first_name like '%a%';

SELECT 列 From 表名
WHERE 条件语句
ORDER BY 列 [ASC|DESC]

ASC - 升序 - 默认可以不写.
DESC - 降序

-- order by 后面可以放什么？
-- 直接跟列名
select concat(last_name,' ',first_name) 姓名,salary,title from s_emp where title='Stock Clerk' order by salary desc;

-- 直接跟列别名
select concat(last_name,' ',first_name) 姓名,salary _sal,title from s_emp where title='Stock Clerk' order by _sal desc;

-- 跟查询的序号，MySQL中序号从1开始 - 推荐使用
select concat(last_name,' ',first_name) 姓名,salary ,title from s_emp where title='Stock Clerk' order by 2 desc;

select first_name,salary,title from s_emp where title like '%VP%' order by 2 desc;

select first_name,title,salary*12*(1+coalesce(commission_pct,0)/100) year_salary from s_emp where salary*12*(1+coalesce(commission_pct,0)/100)<25000 order by 3,1 desc;

-- 子查询 - 把查询出来的结果作为"虚拟表".
select * from(
select first_name,title,salary*12*(1+coalesce(commission_pct,0)/100) year_salary from s_emp) core_ order by 3,1 desc;

select id,name,
	case region_id
		when 1 then '北美'
		when 2 then '南美'
		when 3 then '非洲/中东'
		when 4 then '亚洲'
		else '未知区域'
	end 区域名 from s_dept;

	
-- 工资高于1500的,显示土豪
-- 工资[1200,1500],显示中产
-- 工资低于1200,显示拖后腿
select id,first_name,salary,
	case
		when salary>1500 then '土豪'
      	when salary>=1200 and salary<=1500 then '中产'
      	else '拖后腿'
    end 工资等级 from s_emp;