数据分析中的SQL整理

1、数据分析人员应该掌握的一些sql语句

(1)基础

SQL SELECT Column_ FROM Mytable
SQL DISTINCT(放在SELECT后使用)
SQL WHERE(设置条件)
SQL AND & OR(逻辑与&逻辑或)
SQL ORDER BY(排序操作，BY跟排序字段)
SQL INSERT INTO VALUES(插入数据)
SQL UPDATE SET(更新数据)
SQL DELETE FROM(删除数据)

(2)高级

</pre><blockquote><pre name="code" class="sql">SQL LIMIT(取第N到第M条记录)
SQL IN(用于子查询)
SQL BETWEEN AND(设置区间)
SQL LIKE(匹配通配符)
SQL GROUP BY(按组查询)
SQL HAVING(跟在“GROUP BY”语句后面的设置条件语句)
SQL ALIAS(AS)(可以为表或列取别名)
SQL LEFT JOIN/RIGHT/FULL JOIN(左连接/右连接/全连接)
SQL OUT/INNER JOIN(内连接/外连接)
SQL UNION/UNION ALL(并集，后者不去重)
SQL INTERSECT(交集)
SQL EXCEPT(差集)
SQL SELECT INTO(查询结果赋给变量或表)
SQL CREATE TABLE(创建表)
SQL CREATE VIEW AS(创建视图)
SQL CREATE INDEX(创建索引)
SQL CREATE PROCEDURE BEGIN END(创建存储过程)
SQL CREATE TRIGGER T_name BEFORE/AFTER INSERT/UPDATE/DELETE ON MyTable FOR (创建触发器)
SQL ALTER TABLE ADD/MODIFY COLUMN/DROP(修改表:增加字段/修改字段属性/删除字段)
SQL UNIQUE(字段、索引的唯一性约束)
SQL NOT NULL(定义字段值非空)
SQL AUTO_INCREMENT(字段定义为自动添加类型)
SQL PRIMARY KEY(字段定义为主键)
SQL FOREIGN KEY(创建外键约束)
SQL CHECK(限制字段值的范围)
SQL DROP TABLE/INDEX/VIEW/PROCEDURE/TRIGGER (删除表/索引/视图/存储过程/触发器)
SQL TRUNCATE TABLE(删除表数据，不删表结构)

(3)函数

常用的文本处理函数

SQL Length(str)(返回字符串str长度)
SQL Locate(substr,str)(返回子串substr在字符串str第一次出现的位置)
SQL LTrim(str)(移除字符串str左边的空格)
SQL RTrim(str)(移除字符串str右边的空格)
SQL Trim(str)(移除字符串str左右两边的空格)
SQL Left(str,n)(返回字符串str最左边的n个字符)
SQL Right(str,n)(返回字符串str最右边的n个字符)
SQL Soundex()
SQL SubString(str,pos,len)/Substr()(从pos位置开始截取str字符串中长度为的字符串)
SQL Upper(str)/Ucase(str)(小写转化为大写)
SQL Lower(str)/Lcase(str)(大写转化为小写)

常用的日期与时间处理函数

SQL AddDate()(增加一个日期，天、周等)
SQL AddTime()(增加一个时间，天、周等)
SQL CurDate()(返回当前日期)
SQL CurTime()(返回当前时间)
SQL Date()(返回日期时间的日期部分)
SQL DateDiff()(计算两个日期之差)
SQL Date_Add()(高度灵活的日期运算函数)
SQL Date_Format()(返回一个格式化的日期或时间串)
SQL Day()(返回一个日期的天数部分)
SQL DayOfWeek()(返回一个日期对应的星期几)
SQL Hour()(返回一个时间的小时部分)
SQL Minute()(返回一个时间的分钟部分)
SQL Month()(返回一个日期的月份部分)
SQL Now()(返回当前日期和时间)
SQL Second()(返回一个时间的秒部分)
SQL Time()(返回一个日期时间的时间部分)
SQL Year()(返回一个日期的年份部分)

常用的数值处理函数

SQL Avg()(求均值)
SQL Max()(求最大值)
SQL Min()(求最小值)
SQL Sum()(求和)
SQL Count()(统计个数)
SQL Abs()(求绝对值)
SQL Cos()(求一个角度的余弦值)
SQL Exp(n)(求e^n)
SQL Mod()(求余)
SQL Pi()(求圆周率)
SQL Rand()(返回一个随机数)
SQL Sin()(求一个角度的正弦值)
SQL Sqrt()(求一个数的开方)
SQL Tan()(求一个角度的正切值)
SQL Mid(ColumnName,Start,[,length])(得到字符串的一部分)
SQL Round(n,m)(以m位小数来对n四舍五入)
SQL Convert(xxx,TYPE)/Cast(xxx AS TYPE) (把xxx转为TYPE类型的数据)
SQL Format() (用来格式化数值)
SQL First(ColumnName)(返回指定字段中第一条记录)
SQL Last(ColumnName)(返回指定字段中最后一条记录)

2、操作练习

(1)工程项目-供应商-零件数据库

创建供应商表

CREATE TABLE S
(
  sno VARCHAR(2)  PRIMARY KEY,
  sname VARCHAR(6),
  status INT,
  city VARCHAR(8)

);

插入供应商数据

INSERT INTO S VALUES('S1','精益',20,'天津');
INSERT INTO S VALUES('S2','盛锡',10,'北京');
INSERT INTO S VALUES('S3','东方红',30,'北京');
INSERT INTO S VALUES('S4','丰泰盛',20,'天津');
INSERT INTO S VALUES('S5','为民',30,'上海');
SELECT * FROM S;

创建零件表

CREATE TABLE P
(
  pno VARCHAR(2) PRIMARY KEY,
  pname VARCHAR(6),
  color VARCHAR(2),
  weight INT
);

插入零件数据

INSERT INTO P VALUES('P1','螺母','红',12);
INSERT INTO P VALUES('P2','螺栓','绿',17);
INSERT INTO P VALUES('P3','螺丝刀','蓝',14);
INSERT INTO P VALUES('P4','螺丝刀','红',14);
INSERT INTO P VALUES('P5','凸轮','蓝',40);
INSERT INTO P VALUES('P6','齿轮','红',30);
SELECT * FROM P;

创建工程项目表

CREATE TABLE J
(
  jno VARCHAR(2) PRIMARY KEY,
  jname VARCHAR(8),
  city VARCHAR(8)
);

插入工程项目数据

INSERT INTO J VALUES('J1','三建','北京');
INSERT INTO J VALUES('J2','一汽','长春');
INSERT INTO J VALUES('J3','弹簧厂','天津');
INSERT INTO J VALUES('J4','造船厂','天津');
INSERT INTO J VALUES('J5','机车厂','唐山');
INSERT INTO J VALUES('J6','无线电厂','常州');
INSERT INTO J VALUES('J7','半导体厂','南京');
SELECT * FROM J;

创建供应情况表

CREATE TABLE SPJ
(
  sno VARCHAR(2),
  pno VARCHAR(2),
  jno VARCHAR(2),
  qty INT,
  PRIMARY KEY(sno,pno,jno),
  #建立表级完整性约束条件
  FOREIGN KEY(sno) REFERENCES S(sno),
  FOREIGN KEY(jno) REFERENCES J(jno),
  FOREIGN KEY(pno) REFERENCES P(pno)
);

插入供应情况数据

INSERT INTO SPJ VALUES('S1','P1','J1',200);
INSERT INTO SPJ VALUES('S1','P1','J3',100);
INSERT INTO SPJ VALUES('S1','P1','J4',700);
INSERT INTO SPJ VALUES('S1','P2','J2',100);
INSERT INTO SPJ VALUES('S2','P3','J1',400);
INSERT INTO SPJ VALUES('S2','P3','J2',200);
INSERT INTO SPJ VALUES('S2','P3','J4',500);
INSERT INTO SPJ VALUES('S2','P3','J5',400);
INSERT INTO SPJ VALUES('S2','P5','J1',400);
INSERT INTO SPJ VALUES('S2','P5','J2',100);
INSERT INTO SPJ VALUES('S3','P1','J1',200);
INSERT INTO SPJ VALUES('S3','P3','J1',200);
INSERT INTO SPJ VALUES('S4','P5','J1',100);
INSERT INTO SPJ VALUES('S4','P6','J3',300);
INSERT INTO SPJ VALUES('S4','P6','J4',200);
INSERT INTO SPJ VALUES('S5','P2','J4',100);
INSERT INTO SPJ VALUES('S5','P3','J1',200);
INSERT INTO SPJ VALUES('S5','P6','J2',200);
INSERT INTO SPJ VALUES('S5','P6','J4',500);
SELECT * FROM SPJ;
SELECT FOUND_ROWS();

查询练习
供应工程J1零件的供应商号码SNO

SELECT DISTINCT sno FROM SPJ WHERE jno='J1';

供应工程J1零件P1的供应商号码

SELECT DISTINCT sno FROM SPJ WHERE jno='J1' AND pno='P1';

供应工程J1零件为红色的供应商号码

SELECT sno FROM SPJ,P WHERE jno=