HEAD FIRST SQL 深入浅出SQL 笔记

head first系列真是好书。

==================================================================

数据库是保存表和其他相关SQL结构的容器

数据库内的信息组成了表

列是存储在表中的一块数据

行是一组某个事物的列的集合

行列构成了表
********************************
CREATE DATABASE database_name

USE database_name

CREATE TABLE table_name
(
column1 VARCHAR(10),
column2 VARCHAR(4)
);

DESC table_name;

DROP TABLE table_name

********************************

CHAR 取出来都是固定的长度 255
INT INTEGER整数，可以为负。
BLOB 大文本数据
DATE 日期
VARCHAR 文本数据 可调整长度 255
DEC DECIMAL 小数 DEC(6.2) 总位数，小数点后位数
DATETIME 日期和时间

**********************************
INSERT INTO table_name
(column_name1,column_name2)
VALUES
('value1','value2');

可以
1.改变列顺序 （只要列名与数值对应即可）
2.省略列名 （数值顺序与创建表时的列顺序完全相同即可）
3.省略部分列 （只有部分列名与对应的数据）

**********************************

CREATE TABLE table_name
(
column1 VARCHAR(10) [color=red]NOT NULL[/color],
column2 DEC(3.2) NOT NULL [color=red]DEFAULT 1.00[/color]
);


*****************************************

SELECT * FROM table_name
where column_name = 'value'
AND
INT = 0;

DEC INT 不需要用单引号，其他需要

\' or '' 来替代 VALUE中的引号

SELECT column1,column2
FROM table_name
WHERE column_name = 'value'
[color=red]OR[/color]
INT [color=red]<>[/color] 0
OR
name [color=red]IS NULL[/color]
OR
some [color=red]LIKE '%CA'[/color]
OR
some [color=red]LIKE '_im'[/color]
OR
int[color=red] BETWEEN 30 AND 60[/color]
OR
name [color=red]IN[/color] ('name1','name2')
ORhttp://lautherf.iteye.com/admin/blogs/1622447/edit
name [color=red]NOT[/color] IN ('name3');

[color=green]//
WHERE NOT name BETWEEN value1 AND value2
WHERE NOT name LIKE '%XX'
WHERE name IS NOT NULL
WHERE NOT name IS NULL[/color]

****************************************

[color=red]DELETE FROM [/color]table_name
WHERE
column = 'value'

[color=red]UPDATE[/color] table_name
[color=red]SET[/color] column1 = 'value1',column2 = 'value2'
WHERE column3 = 'value3';

UPDATE table_name
SET column = 'value',int = int[color=red] + 1[/color];

*****************************************

原子性数据

[color=red]1NF （first normal form） 原子，主键[/color]
1.每个数据行必须包含有原子性的值
2.每个数据行必须有独一无二的识别项，人称主键（Primary key）
::::主键不能为NULL；插入新记录时必须指定主键值；主键必须简洁；主键值不能改变

*****************************************


[color=red]SHOW[/color] CREATE TABLE table_name;


******************************************

CREATE TABLE table_name
(
contact_id INT NOT NULL [color=red]AUTO_INCREMENT[/color],
[color=red]PRIMARY KEY (contact_id)[/color]
);

******************************************

ALTER TABLE table_name
ADD COLUMN column_name INT NOT NULL AUTO_INCREMENT [color=red]FIRST[/color],
ADD COLUMN1 column1 VARCHAR(10) [color=red]AFTER[/color] column_name,
ADD PRIMARY KEY (content_id);


:: FIRST LAST
:: FIFTH SECOND
:: BEFORE AFTER
*******************************************
[color=red]
ALTER TABLE[/color] table_name
[color=red]RENAME[/color] TO table_name2;

ALTER TABLE table_name2
[color=red]CHANGE[/color] COLUMN column_name1 column_name2 INT NOT NULL AUTO_INCREMENT
[color=red]ADD PRIMARY KEY(column_name2);[/color]

ALTER TABLE table_name
MODIFY COLUMN column_old[color=red] VARCHAR(20);[/color]

ALTER TABLE table_name
DROP COLUMN column_name;
*********************************************

SELECT RIGHT(column,2) FROM table_name
SELECT LEFT(column,2) FROM table_name
SELECT SUBSTRING_INDEX(column,',',1) FROM table_name
UPPER(string) LOWER(string)
REVERSE(string)
LTRIM(string) RTRIM(string)
LENGTH(string)


***********************************************

UPDATE table_name
SET column_name =
CASE
WHEN column1 = value1 THEN newvalue1
WHEN column2 = value2 THEN newvalue2
ELSE newvalue3
END


*************************************************

SELECT column
FROM table
WHERE
title LIKE 'A%'
ORDER BY column2 DESC;
//ASC DESC

SELECT SUM(col)
FROM table
WHERE col = 'value'
GROUP BY col
ORDER BY col;

//AVG(col) MAX(col) MIN(col) COUNT(col)

***************************************************

SELECT DISTINCT col
FROM table
ORDER BY col
LIMIT 2;

//LIMIT 0,4

****************************************************

外键 是表中的某一列，它引用到另一个表的主键，可以NULL，不唯一

约束 constraint

CREATE TABLE table (
int_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
interest VARCHAR(50) NOT NULL,
contact_id INT NOT NULL,
CONSTRAINT my_contacts_contacts_contact_id_fk
FOREIGN KEY (contact_id)
REFERENCES my_contacts (contact_id)
);

数据模式 一对一 一对多 多对多

*******************************************************

组合键 就是由多个数据列构成的主键，组合各列后形成具有唯一性的主键

T.x -> T.y
x影响y y依赖x

传递函数依赖 任何非键列于另一个非键列有关联
部分函数依赖 非主键的列依赖于组合主键的某个部分

********************************************************

第二范式 2NF
1. 达成1NF
2.没有部分函数依赖

第三范式 3NF
1.达成2NF
2.没有传递函数依赖

********************************************************

CREATE TABLE profession
(
id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
profession VARCHAR(20)
)[color=red]AS[/color]
SELECT profession FROM table
GROUP BY profession
ORDER BY profession;

AS 输出通道

SELECT profession AS mc_prof
FROM my_contact AS mc
GROUP BY mc_prof
ORDER BY mc_prof;

**********************************************************

SELECT t.boy,b.boy
FROM toys AS t
CROSS JOIN
boys AS b;

//交叉联接 全联接

SELECT boys.boy,toys.toy
FROM boys
INNER JOIN
toys
ON boys.toy_id = toys.toy_id;
ORDER BY boys.boy;

//内联接

SELECT boys.boy,toys.toy
FROM boys
NATURE JOIN
toys;

//自然联接 无ON 无条件联接 共享列名

******************************************

SELECT col1,col2
FROM table
WHERE col = (SELECT col FROM table );

包含查询 或 外层查询 子查询

SELECT mc.first_name,mc.last_name,
(SELECT state
FROM zip_code
WHERE mc.zip_code = zip_code) AS state
FROM my_contacts mc;

***********************************************

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

g-t

//左外联接 主表 join 附表 附表可为NULL

SELECT title FROM table
UNION
SELECT title FROM table1
UNION
SELECT title FROM table2
ORDER BY title;

//UNION ALL 所有的值 可重复

SELECT title FROM job_current
INTERSECT
SELECT title FROM job_desired;


//INTERSECT EXCEPT
*****************************************


[url=http://www.iteye.com/topic/465807]http://www.iteye.com/topic/465807[/url]

左联接以左表为主 left join 或者 left outer join
右联接以右表为主 right join 或 right outer join
全联接以项多的表为主 full join 或者 full outer join
内联接以以两表都有的项为主 inner join 或者 join
交叉连接以符合WHERE条件的笛卡尔积组成 cross join 不带 where 条件的
笛卡尔积联接 无WHERE的交叉联接
自联接表示从属，上下级关系
******************************************

CREATE TABLE table
(
id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
coin CHAR(1） CHECK (coin IN('P','N','D','Q'))

*******************************************

CREATE VIEW view AS
SELECT * FROM table;

************************
事务
SELECT * FROM
(SELECT col FROM table WHERE col = 'value' AS table_name);

ACID
ATOMICITY 原子性 不能只执行一部分事务
CONSISTENCY 一致性 事务完成后应保持数据库一致
ISOLATION 隔离性 每次的事务应看到同一个数据库
DURABILITY 持久性 事务完成后能够正确的保存在数据库中，免受断电等危害

START TRANSACTION;
COMMIT;
ROLLBACK;

***********************************************

SET PASSWORD FOR 'root'@'localhost'=PASSWORD('XXXX');

CREATE USER name IDENTIFIED BY 'XXXX';

GRENT SELECT ON table_name TO someone;

REVOKE SELECT ON table_name FROM someone;

REVOKE SELECT ON table_name FROM someone CASCADE; 取消传递的权限


CREATE ROLE data_entry;

GRENT SELECT,INSERT ON some_table TO data_entry;

GRENT data_entry TO someone;


GRENT data_entry TO someone WITH ADMIN OPTION; 允许赋予别人

REVOKE data_entry FROM someone CASCADE;

REVOKE data_entry FROM someone RESTRICT; 收到错误信息，如已经赋权给别人

DROP ROLE data_entry ;