SQL基础（1）

数据库基本查询语句

- 查询语句

SELECT col_one,col_twoFROM table_nameWHERE 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.boyFROM toys AS t    CROSS JOIN    boys AS b;

---

*集合论

//UNION返回一个并集去重SELECT title FROM jb_currentUNIONSELECT title FROM jb_desiredUNIONSELECT title FROM job_listingsORDER BY title;//UNION ALL返回一个并集不去重SELECT title FROM jb_currentUNION ALLSELECT title FROM jb_desiredUNION ALLSELECT title FROM jb_listingsORDER BY title;//从集合创建表CREATE TABLE my_union ASSELECT title FROM job_currentUNIONSELECT title FROM job_desired;//INTERSECT返回交集SELECT title FROM job_currentINTERSECTSELECT title FROM job_desired;//EXCEPT返回A中删除与B有交集的部分SELECT title FROM job_currentEXCEPTSELECT 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.toyFROM toys tLEFT OUTER JOIN girls gON 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.toyFROM toys tRIGHT OUTER JOIN girls gON 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.professionFROM my_contacts mc    INNER JOIN    profession pON mc.prod_id = p.prod_id;        /*根据这个条件来联结表,也可以根据条件，把=改成<>。*///自然联结，根据两张表中名字相同的列来内联结SELECT mc.name, mc.phone, p.professionFROM my_contacts mc    NATURAL JOIN    profession p;//自联结SELECT c1.name, c2.name AS bossFROM 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.titleFROM job_current jc    NAUTRAL JOIN     my_contacts mcWHERE jc.title IN (SELECT title FROM job_listingsGROUP BY titleORDER BY title);//子查询 ==>  选出薪水最高的一个人SELECT mc.name, jc.salaryFROM my_contacts mc     NATURAL JOIN    job_current jcWHERE jc.salary = (SELECT MAX(jc.salary)FROM job_current jc);//子查询 ==>  选出每一行的名字，手机，城市SELECT mc.name, mc.phone, (SELECT zc.cityFROM zip_code zcWHERE mc.zip_code = zc.zip_code) AS cityFROM my_contacts mc;//关联子查询 ==>  选出有3个兴趣爱好的人SELECT mc.nameFROM my_contacts mcWHERE 3 = (SELECT COUNT(*) FROM contact_interestWHERE contact_id = mc.contact_id);//返回至少有一项兴趣爱好，但是名字不在job_current的人的emailSELECT mc.email FROM my_contact mcWHERE EXISTS(SELECT * FROM contact_interest ci WHERE mc.contact_id = ci.contact_id)ANDNOT EXISTS(SELECT * FROM job_current jcWHERE mc.contact_id = jc.contact_id);

---

*联结与子查询的比较

//1.......SELECT mc.name, mc.phone, jc.titleFROM job_current jc    NATURAL JOIN    my_contacts meINNER JOIN job_listings jlON jc.title = jl.title;SELECT mc.name, mc.phone, jc.titleFROM job_current jc    NATURAL JOIN    my_contacts meWHERE jc.title IN(SELECT title FROM job_listings);//把自联结变成子查询SELECT c1.name, c2.name AS bossFROM clown_info c1INNER JOIN clown_info c2ON c1.boss_id = c2.id;SELECT c1.name,(SELECT name FROM clown_infoWHERE c1.boss_id = id) AS bossFROM 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_listALTER 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;