《SQL必知必会》笔记

SQL

  1. DDL 数据定义语言:用于创建、修改或删除数据库对象,如表、视图、模式、触发器和存储过程等。相关的关键字包括CREATE,ALTER,DROP
  2. DQL数据查询语言:用于数据的检索查询,相关的关键字为SELECT
  3. DML数据操纵语言:用于添加,修改或删除存储在数据库对象中的数据,相关的关键字有INSERT,UPDATE,DELETE
  4. DCL数据控制语言:用于控制访问数据库中特定对象的用户,还可以控制用户对数据库的访问类型,相关的关键字有GRANT,DENY,REVOKE

使用DISTINCT参数,只返回不同的值

SELECT

CONCAT(

v.`vend_name`,

RTRIM(‘ ‘),

v.`vend_country`

) AS ‘拼接后’,

SOUNDEX(v.`vend_address`),

v.`vend_address`,

LEFT(v.`vend_address`,8),

UPPER(v.`vend_city`),

LOWER(v.`vend_city`)

FROM

vendors v ;

— RTRIM去掉右边的所有空格

— LTRIM去掉左边的所有空格

— TRIM去掉左右两边的空格

— LEFT(str,len)返回字符串左边长度的字符

— RIGHT(str,len)返回字符串右边长度的字符

— LOWER(str)将字符串转换为小写

— UPPER(str)将字符串转换为大写

— SOUNDEX()返回字符串的SOUNDEX值

拼接后 soundex(v.`vend_address`) vend_address left(v.`vend_address`,8) upper(v.`vend_city`) lower(v.`vend_city`)

——————– ————————- ————— ———————— ——————– ———————-

Bear EmporiumUSA P62363 500 Park Street 500 Park ANYTOWN anytown

Bears R UsUSA M2363 123 Main Street 123 Main BEAR TOWN bear town

Doll House Inc.USA H2363 555 High Street 555 High DOLLSVILLE dollsville

Fun and GamesEngland G4263 42 Galaxy Road 42 Galax LONDON london

Furball Inc.USA T150 1000 5th Avenue 1000 5th NEW YORK new york

Jouets et oursFrance R5253 1 Rue Amusement 1 Rue Am PARIS paris

SELECT

o.`order_num`,

o.`order_date`

FROM

orders o

WHERE

YEAR(o.`order_date`) = 2012;

— YEAR() 从日期中提取年份

— ABS() 返回一个数的绝对值

— SQRT() 返回一个数的平方

— EXP() 返回一个数的指数值

— PI() 返回圆周率

— COS() 返回一个角度的余弦

— SIN() 返回一个角度的正弦

— TAN() 返回一个角度的正切

order_num order_date

——— ———————

20005 2012-05-01 00:00:00

20006 2012-01-12 00:00:00

20007 2012-01-30 00:00:00

20008 2012-02-03 00:00:00

20009 2012-02-08 00:00:00

聚集函数(操作列,返回一个数):AVG() COUNT() MAX() MIN() SUM()

均忽略NULL值,不统计

COUNT(*)不会忽略NULL

MAX()也可返回该列排序的最后一行

MIN()也可返回该列排序的第一行

当不指定GROUP BY时候,所有类型的WHERE子句都可以用HAVING来替代。

差别在于WHERE过滤行,HAVING过滤分组。

SELECT

o.`cust_id`,

COUNT(*) AS orders

FROM

orders o

WHERE o.`cust_id` <> 1000000003

GROUP BY o.`cust_id`

HAVING COUNT(*) >= 2 ;

cust_id orders

———- ——–

1000000001 2

WHERE在数组分组前进行行过滤,HAVING在数据分组后进行分组过滤。

子查询:

  1. 作为子查询的SELECT语句只能查询单个列。
  2. 作为计算字段使用子查询,多数情况需要使用完全限定名,即A.id = B.id,以免混淆列名。

联结表:

FROM A,B

WHERE A.id = B.id

或者

INNER JOIN … ON …内联结

FROM A AS a1, A AS a2

WHERE a1.id = a2.id自联结

对一个表使用通配符(SELECT *),而对其他表的列使用明确的子集来完成。——自然联结 为包含哪些没有关联行的行。——外联结,包括:左外联结:LEFT OUTER JOIN … ON …右外联结:RIGHT OUTER JOIN … ON …全外联结:FULL OUTER JOIN … ON …

复杂查询的方法:

  1. 子查询
  2. 联结表

通常处理联结比处理子查询快得多。

组合查询:

  1. UNION 必须由两条或两条以上SELECT语句组成,语句之间用关键字UNION分隔
  2. UNION中的每个查询必须包含相同的列、表达式或聚集函数(不过,各个列不必以相同的次序列出)
  3. 列数据必须兼容:类型不必完全相同,但必须是DBMS可以隐含转换的类型(例如,不同的数值或日期类型)

另外,UNION从查询结果集中自动去除了重复行,若要返回所有的匹配行,应使用UNION ALL(不可用WHERE代替)

插入数据:

1INSERT INTO A(id,name,…) VALUES(‘1′,’xiaoli’,…);

这种方法可插入完整行,也可插入行的一部分,前提是表的定义允许在INSERT操作中省略某些列,省略的列必须满足:

  1. 该列定义为允许NULL值(无值或空值)
  2. 在表定义中给出默认值。

2INSERT INTO A(id,name,…) SELECT id,name,… FROM B

插入检索出的数据,注意这里不一定要求列名匹配,它使用的是列的位置。

3CREATE TABLE B AS SELECT * FROM A;

从一张表复制到另一张表。

更新数据:

UPDATE A SET id = ‘…’, name = ‘…’ WHERE …

删除数据:

DELETE FROM A WHERE id = ‘…’;

若从表中删除所有行,可用TRUNCATE TABLE A语句,速度更快,因为不记录数据的变动。

DELETETRUNCATE区别

1.DELETE

DML语言

可以回退

可以有条件的删除

DELETE FROM 表名

WHERE 条件

2.TRUNCATE TABLE

DDL语言

无法回退

默认所有的表内容都删除

删除速度比delete快。

主键外键的写法:

CREATE TABLE Orders1 (

order_num CHAR(10) NOT NULL PRIMARY KEY,指定主键

cust_id CHAR(10) NOT NULL,

CONSTRAINT fk_PerOrders FOREIGN KEY (cust_id) REFERENCES customers (cust_id)添加外键

) ;

ALTER TABLE Orders1

ADD FOREIGN KEY (cust_id) REFERENCES customers (cust_id);添加外键

ALTER TABLE Orders1

ADD CONSTRAINT fk_PerOrders FOREIGN KEY (cust_id) REFERENCES customers (cust_id);添加外键

另外还有,唯一约束UNIQUE,检查约束CHECK,定义参考 <http://www.w3school.com.cn/sql/sql_check.asp>

★唯一约束和主键约束的区别:

  1. 每个表可包含多个唯一约束,但只能有一个主键。
  2. 唯一约束列可包含NULL值。
  3. 唯一约束列可修改或更新。
  4. 唯一约束列的值可重复使用。
  5. 和主键不一样,唯一约束不能用来定义外键。