HeadFirst SQL 读书摘要

数据库都是用 圆柱形表示的。

数据库中包含表

表中包含行和列

行又叫记录record，  列又叫 字段field

 

创建数据库

create database mypipe_l;

选择数据库

use mypipe_l;

创建表

create table doughnut(
    name VARCHAR(10),
    type VARCHAR(6)
);

查看表

desc doughnut;

删除表

drop table doughnut;

插入数据

insert into doughnut (name, type) values ('Park', 'g');

 

insert into doughnut values ('BoB', 'b');          在插入数据的时候也可以省略列名， 但这是必须输入所有数据值。

 

NULL ： 值可以是NULL，但不能等于NULL，NULL代表未定义的值。

 

指定某列不能是NULL

create table doughnut(
    name VARCHAR(10) NOT NUll,
    type VARCHAR(6)
);

用default填补空白

create table doughnut(
    name VARCHAR(10) NOT NUll,
    type VARCHAR(6) DEFAULT 'po'
);

 

查询

select * from doughnut where id = 1

在SQL使用 单引号 来表示varchar类型的值， 不要使用双引号， 因为 编程语言中的String用的是双引号。 

如果在文本中包含单引号，用 反斜杠转义。  或者用两个单引号来转义

等于 =

不等于 <>

小于 <

大于 >

小于等于 <=

大于等于 >=

 

OR

 

用IS NULL找到NULL

 

LIKE  调用通配符

% 任意数量的位置字符的替身

 SELECT question FROM  exercise_question WHERE  exercise  like  '%ca';

 

_ 下划线 是 任意一个位置字符的替身

SELECT question FROM  exercise_question WHERE  exercise  like  '_ca';

 

BETWEEN  10 AND 30

 

IN (1,2,3,4,5)

 

NOT IN (7,8,9,10)

 

NOT 可以和BETWEEN和 LIKE一起使用。这NOT的位置

 

SELECT question FROM  exercise_question WHERE not exercise  like  '_ca';

SELECT question FROM  exercise_question WHERE not exercise  between 1 and  10;

 

IS NOT NULL

 

delete

delete from clown_info where activities = 'dancing';

慎用delete语句，不确定时，应该先用select看一下 选择了哪些行。

 

update 更新一列或则和多列

update clown_info set type='glazed' where type = 'plain';

set 子句 可以设置一个或者多个列。

update语句 可以和基础数学运算符一起使用

update clown_info set cost = cost+1  where type = 'plain';

原子性数据，小到已经不能再分割了。

原子性规则一：数据的列中不会有多个类型相同的值

原子性规则二：表中不会有多个存储相同数值的列

 

第一范式：每个数据都有主键，即独一无二的识别项。

主键用来 独一无二的 识别出记录。

 

查看建表语句

show create table sometable;

主键不能为空

create table xxx(
 id int not null,
 name varchar(11)
 primary key (id)
);

 

反撇号 有什么作用？ 标识列名

反撇号可以让 保留字也成为 列名。

`select` varchar(50)

但 这不是好的实践。

 

 

auto_increment 自动递增

再次修改建表语句

create table xxx(
 id int not null auto_increment ,
 name varchar(11)
 primary key (id)
);

 

auto_increment 会 忽略NULL

 

给表增加列

 

alter table my_contacks
 add column id int not null auto_increment first,
 add primary key (id);

 

alter命令

alter table doughnut add column phone varchar(13) not null;

alter table doughnut add column phone varchar(13) not null after `name`;

 

change 修改列名称和数据类型

alter table doughnut change column phone3 iphone varchar(15) not null after `name`;

modify 修改数据类型或位置

alter table doughnut modify column phone2 varchar(15) not null after `name`;

drop 删除列

alter table doughnut drop column phone2 ;

修改表名

alter table doughnut rename to dough;

删除主键

alter table doughnut drop primary key

 

取字符的前几个 left， right也一样

select left(name,3) from dough 

还有许多字符串辅助函数。

 

update dough set state = left(`name`,3)  将state 字段设置为 name字段的前3个字符

 

在SQL中写 if判断

update dough set state = 

CASE

WHEN type = 'po'
THEN 'pXp'
ELSE 'xn'

END

 where name = 'f'

 

排序

Order by

select * from dough order by iphone 

select * from dough order by iphone asc ,name desc  根据两列排序,desc是倒叙， asc正序，默认是正序

对某个列求和 

select sum(phone) from dough where name = 'p'

group by

select sum(phone) from dough group by name order by  sum(phone)

avg 平均函数

select avg(phone) from dough group by name order by  sum(phone)

最大值

select max(phone) from dough group by name order by  sum(phone)

 

最小值

select name, min(phone) from dough group by name order by  sum(phone)

 

eg:

select t.year, max(t.cnt), t.month from (select * from orderx order by cnt) t GROUP BY `year`

统计记录 count

select count(1) from dough group by name

 limit 

select name from dough  order by score limit 3   只取前3个

select name from dough  order by score limit 2,3   跳过前2条，再取3条

 

把不符合原子性的列 移出表， 再建一张 关联表。

 

模式schema

对数据库内的数据描述，以及任何相关对象和各种连接方式的描述就称为schema

 

外键是另外一个表的 主键

 

创建带有外键的表

create table `interest` (
    id int not null auto_increment PRIMARY key,
    interest VARCHAR(20),
  main_id int not null,
  CONSTRAINT main_id FOREIGN key (main_id) REFERENCES orderx(id)
)

 引用完整性： 插入外键列的值必须已经存在于父表的来源列中。

外键约束能保证引用完整性， 如果这是你删除主键表中的行，而这个主键是其他表的外键，你就会收到警告。

 

一对一模式：

一对多模式：

多对多模式： 这时需要 有一张连接表 把来存储这种多对多的关系。 这张表存两外两张表的主键。

 

1NF 第一范式

规则一：数据列只包含具有原子性的值

规则二：没有重复的数据组。

 

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

 

当某列的数据必须随着另一列而改变时，表示第一列 函数依赖 第二列。

举个例子

name	initials
Fu Peng	FP

在上面这个例子中，一个列是name，另一个是简写，  所以当name改变时，他的首字母简写也要跟着改变。

 

速记符号

T.name -> T.initials

有依赖列， 也有不依赖列

 

部分函数依赖 是指 非主键的列 依赖于 组合主键的某个部分。

比如 上面的例子中 initials依赖于name，而name是组合主键的一部分， 但是 initials又不依赖于其他列， 那么 initials就是部分函数依赖

 

 

传递函数依赖：如果改变 非键列 会造成其他列的改变，这就是传递依赖，即 任何非键列 和 其他非键列有关。

 

避免部分函数依赖的方法， 使用id作为 专用索引的主键， 这样就没有字段依赖它了。

 

2NF，第二范式

规则一，符合1NF

规则二，没有部分函数依赖性。

 

如果表中 有人工主键，又没有组合主键，则符合2NF

 

3NF，第三范式

规则一，符合2NF

规则二，没有传递函数依赖性。

 

表的设计应该向3NF方向靠拢， 其原则是把每一张表的职责划清楚，避免将不同的东西写在一起，然后通关关系，再将各处的数据组合起来。 这大概就是叫关系数据库的原因吧，

所有后面要将如何 连表查询。 这才是RDB的精髓。就是关系关系关系。

 

 

create insert

create table profession (
id int(11) not null auto_increment primary key,
profession varchar(20)    
) as
 select profession from my_contacts
group by profession
order by profession

 

 

别名alias

 

as 用来设置别名， 不过as也可以省略。

 

交叉连接 

select t.toy, b.boy from 
toys as t
cross join
boys as b

//cross join可以省略 写成

select t.toy, b.boy from 
toys as t,
boys as b

交叉连接把第一张表的每个值和第二张表的每个值配对。

cross join返回两张表的每一行相乘的结果。

内连接就是 通过查询中的条件 移除了某些结果数据行后的 交叉连接。

 

内连接，利用条件比较运算符 结合两张表, 

内链接：相等连接

select boys.boy,  toys.toy
from boys
inner join
toys
on boys.toy_id = toys.id

内链接之 不等连接

select boys.boy,  toys.toy
from boys
inner join
toys
on boys.toy_id <> toys.id

自然连接 没看懂

 

ca ta 表别名和列别名是为了让 SQL 写的简化一些。

 

子查询，是被另一个查询包围的查询，也可以称为 内层查询。

 

selet mc.first_name, mc.last_name, jc.title
from
job_current as jc 
natural join 
my_contact as mc
where jc.title in (select title from job_listing);

 

子查询都是单一 select 语句，

一般情况 子查询只需要返回一个值， 使用 in的情况例外。

 

作为 预选取列的 子查询

select mc.first_name ,
(select state from zip_code where mc.zip_code = zip_code) as state 
from  my_contact  mc

 

如果子查询放在 select语句中，用于表示某个预选列，则一次只能从一个列返回一个值

 

非关联子查询

关联子查询： 内层查询的解析需要依赖 外层结果。

 

关联子查询例子

select  mc.first_name 
from my_contact as mc
where 
3 = (select count(1) from contact_interest where contact_id = mc.contact_id);

 

not exists 关联子查询

select mc.first_name
 from my_contact mc
where  not exists
(select * from job_contact jc where mc.contact_id = jc.contact_id) 

外连接

left outer join ，接受左表中的所有行，并用这些行与右表进行匹配。

左连接用的比较多

select g.girl, t.toy
from girls g
left join
toys t
on g.toy_id = t.toy_id

在left join中，如果出现NULL，说明右表中没有与左表匹配的行。

 

自引用外键：某一列的值是 同一张表的主键。

 

自连接

select c1.name, c2.name boss_name 
from clown_info c1
inner join
clown_info c2
on c1.boss_id = c2.id
 

自连接能把单一表当成两张具有完全相同信息的表进行查询。

 

另一种取得 多张表数据的方式 联合Union

select title from job_current
union
select title from job_desire
union 
select title from job_listing

使用联合的规则

每个select语句的列数量必须一致，

列的数据类型必须相同，或者可以互相转换。

select语句的顺序不重要，不会改变结果。

SQL会默认清除联合结果中的重复值

如果处于某种原因 想要看到 重复的结果， 可是使用 union all

 

从联合创建表

create table my_union as
select title from t1 union
select title from t2 union
select title from t3

 

intersect 求交集

except 求差集，  MySQL没有实现这两个SQL运算符

 

检查约束 check

alter table my_contact
add constraint check gender in ('F','M');

 

视图 view

创建视图，视图好像一个 SQL语句的 简称。

视图好像一个 子查询。

 

给子查询一个别名，以便把它当成一个虚拟表

 

事务，在事务执行过程中，所有步骤如果不能不受干扰地全部完成，则不完成任何单一步骤。

 

acid  原子  一致，持久性，隔离性

 

start transaction

commit
rollback

 

在MySQL使用事务之前，必须保证 该表的 存储引擎是 支持事务的，如Innodb。

MyISAM不支持事务。

 

添加用户

create user eliss
identified by '123456'

 

使用grant 语句，可以控制用户对 表和列的 可执行权限。

grant是授权的意思

grant select on clown_info to eliss
grant all on db.* to eliss

撤销权限

revoke select on clown_info from eliss 

 

MySQL 没有 角色 功能