SQL基础（1）

数据库基本查询语句

- 查询语句

SELECT col_one,col_two
FROM table_name

WHERE col_name IS NOT NULL;                         //
WHERE NOT col_name = 'Burger' AND NOT rating <> 6;  //NOT最好放在WHERE后面
WHERE col_name BETWEEN 10 AND 60;                   //比 col_name >= 10 AND col_name <= 60;好
WHERE col_name LIKE '%A';                           //以A结尾，%匹配任意数量的字符
WHERE col_name LIKE '__A';                           //以A结尾，_匹配单个字符
WHERE col_name NOT IN ('aa','bb','cc');

GROUP BY first_name;                                //这个列中的值会多次出现，将该列中重复出现的值汇总起来

ORDER BY title ASC, other_col DESC;                 //按title升序，按other_col降序排列

LIMIT 2;    //仅限前2个数据
LIMIT 0,4;  //从索引0开始，选取后面的4个行

联结语句和子查询

*交叉联结

toys表

toy_id	toy
1	hula hoop
2	balsa glider
3	toy soldiers
4	harmonica
5	baseball cards

boys表

boy_id	boy
1	Davey
2	Bobby
3	Beaver
4	Richie

//交叉联结 ---> 组合
SELECT t.toy, b.boy
FROM toys AS t
    CROSS JOIN
    boys AS b;

---

*集合论

//UNION返回一个并集去重
SELECT title FROM jb_current
UNION
SELECT title FROM jb_desired
UNION
SELECT title FROM job_listings
ORDER BY title;

//UNION ALL返回一个并集不去重
SELECT title FROM jb_current
UNION ALL
SELECT title FROM jb_desired
UNION ALL
SELECT title FROM jb_listings
ORDER BY title;

//从集合创建表
CREATE TABLE my_union AS
SELECT title FROM job_current
UNION
SELECT title FROM job_desired;

//INTERSECT返回交集
SELECT title FROM job_current
INTERSECT
SELECT title FROM job_desired;

//EXCEPT返回A中删除与B有交集的部分
SELECT title FROM job_current
EXCEPT
SELECT title FROM job_desired;

---

*左外联结与右外联结

toys表

toy_id	toy
1	squirt gun
2	crazy straw
3	slinky

girls表

girl_id	girl	toy_id
1	Jen	1
2	Cleo	1
3	Sally	3
4	Martha	3

* ### 左外联结：会匹配左表中的每一行，以及右表符合条件的行，拼接进左表。如果右表中没有符合条件的，左表的数据行会显示NULL

SELECT g.girl, t.toy
FROM toys t
LEFT OUTER JOIN girls g
ON g.toy_id = t.toy_id;

girl	toy
Jen	squirt gun
Cleo	squirt gun
NULL	crazy straw
Sally	slinky
Martha	slinky

* ### 右外联结：会匹配右表中的每一行，以及左表符合条件的行，拼接进右表。如果左表中没有符合条件的，右表的数据行会显示NULL

SELECT g.girl, t.toy
FROM toys t
RIGHT OUTER JOIN girls g
ON g.toy_id = t.toy_id;

girl	toy
Cindy	hula hoop
Jane	toy soldiers
Sally	harmonica

*内联结,自然联结,自联结

my_contacts	contact_id(主键)	name	phone	email	prof_id(外键)
profession	prof_id(主键)	profession

//内联结语句，将profession表中的profession列根据prof_id联结进入my_contacts表
SELECT mc.name, mc.phone, p.profession
FROM my_contacts mc
    INNER JOIN
    profession p
ON mc.prod_id = p.prod_id;        /*根据这个条件来联结表,也可以根据条件，把=改成<>。*/


//自然联结，根据两张表中名字相同的列来内联结
SELECT mc.name, mc.phone, p.profession
FROM my_contacts mc
    NATURAL JOIN
    profession p;

//自联结
SELECT c1.name, c2.name AS boss
FROM clown_info c1                /*把一张表当成两张来联结
INNER JOIN clown_info c2          */ 
ON c1.boss_id = c2.id;

---

*子查询

• 子查询都是单一SELECT语句
• 会出现在：1.SELECT子句中。2.列选择中。3.FROM子句中。4.HAVING子句中。
• 子查询能与INSERT, DELETE, UPDATE, SELECT一起用。

my_contacts	contact_id(主)	name	phone	prod_id(外键)	zip_code(外)
job_current	contact_id(主+外)	title	salary	start_date
job_desired	contact_id(主+外)	title	salary_low	salary_high	available	years_exp
contact_interest	contact_id(外)	interest_id(外)
interests	interest_id(主）	interest
contact_seek	contact_id(外)	seeking_id(外)
seeking	seeking_id(主)	seeking
profession	prof_id(主)	professioin
zip_code	zip_code(主)	city	province
job_listings	job_id(主)	title	salary	zip	description

//子查询 ==>  查询符合job_listings表中要求的人的信息
SELECT mc.name, mc.phone, jc.title
FROM job_current jc
    NAUTRAL JOIN 
    my_contacts mc
WHERE jc.title IN 
(SELECT title FROM job_listings
GROUP BY title
ORDER BY title
);

//子查询 ==>  选出薪水最高的一个人
SELECT mc.name, jc.salary
FROM my_contacts mc 
    NATURAL JOIN
    job_current jc
WHERE jc.salary = 
(SELECT MAX(jc.salary)
FROM job_current jc);

//子查询 ==>  选出每一行的名字，手机，城市
SELECT mc.name, mc.phone, 
(SELECT zc.city
FROM zip_code zc
WHERE mc.zip_code = zc.zip_code) AS city
FROM my_contacts mc;

//关联子查询 ==>  选出有3个兴趣爱好的人
SELECT mc.name
FROM my_contacts mc
WHERE 3 = (
SELECT COUNT(*) FROM contact_interest
WHERE contact_id = mc.contact_id
);

//返回至少有一项兴趣爱好，但是名字不在job_current的人的email
SELECT mc.email 
FROM my_contact mc
WHERE EXISTS
(SELECT * FROM contact_interest ci WHERE mc.contact_id = ci.contact_id)
AND
NOT EXISTS
(SELECT * FROM job_current jc
WHERE mc.contact_id = jc.contact_id);

---

*联结与子查询的比较

//1.......
SELECT mc.name, mc.phone, jc.title
FROM job_current jc
    NATURAL JOIN
    my_contacts me
INNER JOIN job_listings jl
ON jc.title = jl.title;


SELECT mc.name, mc.phone, jc.title
FROM job_current jc
    NATURAL JOIN
    my_contacts me
WHERE jc.title IN(
SELECT title 
FROM job_listings);



//把自联结变成子查询
SELECT c1.name, c2.name AS boss
FROM clown_info c1
INNER JOIN clown_info c2
ON c1.boss_id = c2.id;


SELECT c1.name,
(SELECT name FROM clown_info
WHERE c1.boss_id = id) AS boss
FROM clown_info c1;

- 创建, 删除, 修改数据表

//创建
CREATE TABLE table_name
(
    contact_id INT NOT NULL AUTO_INCREMENT,
    first_name VARCHAR(10) NOT NULL,
    cost DEC(5,2) NOT NULL DEFAULT 1.00,
    PRIMARY KEY (contact_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


//删除
DROP TABLE table_name;

//修改
ALTER TABLE my_contact
    ADD COLUMN contact_id INT NOT NULL AUTO_INCREMENT FIRST,    //除了FIRST，还有SECOND，THIRD，...,LAST
    ADD PRIMARY KEY (contact_id);

ALTER TABLE my_contacts
    ADD COLUMN phone VARCHAR(30)
    AFTER first_name;                    //还有BEFORE your_column;

ALTER TABLE projects
    RENAME TO project_list;            //将列名由projects改成project_list

ALTER TABLE project_list
    CHANGE COLUMN old_name new_name VARCHAR(100); //将列改名并修改数据类型，可能会丢失数据

ALTER TABLE project_list
    MODIFY COLUMN col_name VARCHAR(120);          //不改变列名，只修改数据类型

ALTER TABLE project_list
    DROP COLUMN start_date;        //删除列

- 数据类型

固定字符	可变字符	数值空间	长文本	整型	日期	日期与时间
CHAR(n)	VARCHAR(n)	DEC(n,m)	BLOB	INT	DATE	DATETIME

- 描述数据库

SHOW datebases;
SHOW tables;
SHOW CREATE TABLE table_name;

DESC table_name;

- 插入数据

//可以改变列顺序，或者省略部分列
INSERT INTO table_name ( col_1, col_2, ...)
VALUES ( 'value1', 'value2', ....);

- 删除与更新数据:删之前最好用select看看

DELETE FROM table_name
    WHERE col_name = 'aaa';

UPDATE table_name
    SET col_one = 'newValue',
    col_two = 'anotherValue'
    WHERE ......;

UPDATE drink_info
    SET cost = cost + 1
    WHERE
    drink_name IN ('aaa','bbb');

UPDATE my_contacts
    SET state = RIGHT(location,2)    =>  将state列全部填充为location列的右边2个字符

UPDATE my_table
    SET new_column = 
    CASE                                //CASE表达式开始
        WHEN column1 = some_value1      //判断条件1
            THEN new_value1             //将new_column设为new_value1
        WHEN column2 = some_value2      // ...
            THEN new_value2             // ...
        ELSE new_value3
    END;                                //CASE表达式结束

---

便利的数据库函数

/*  字符串函数   */

//RIGHT,LEFT(col,n)函数，选出指定数量的字符
SELECT RIGHT(col_name, 2) FROM my_contacts;

//SUBSTRING_INDEX(col,' ',n)函数
SELECT SUBSTRING_INDEX (location, ',', 1)    //找到第1个","，并截取前面的部分
    FROM my_contacts;

//SUBSTRING(string,start_position,length)
//UPPER(string) LOWER(string)
//REVERSE(string)
//LTRIM(string) RTRIM(string)    => 返回清除左侧/右侧的空字符串的字符串
//LENGTH(string)

/*   数理统计   */
// SUM
// AVG
// MAX
// MIN
//COUNT => 统计出现次数

一些高级的使用实例

//1.有一个服务生7天来每天的销售额，求出7天的总业绩
SELECT SUM(sales)
    FROM cookie_sales
    WHERE first_name = 'Nicole';

//2.有一堆服务生7天来每天的销售额，求出业绩最高的3个服务生
SELECT first_name, SUM(sales)
    FROM cookie_sales
    GROUP BY first_name
    ORDER BY SUM(sales) DESC
    LIMIT 3;

//3.计算每个服务生每天的平均销售额
SELECT first_name, AVG(sales)
    FROM cookie_name
    GROUP BY first_name;

//4.统计每个服务生的出勤天数
SELECT COUNT(sale_date)
    FROM cookie_sale;

//5.去除重复的日期
SELECT DISTINCT sale_date
    FROM cookie_sales
    ORDER BY sale_date;

//6.选出出勤天数最多的人
SELECT first_name, COUNT(DISTINCT sale_date)
    FROM cookie_sales
    GROUP BY first_name;