HeadFirstSQL深入浅出SQL学习笔记（一）

1. 创建数据库和表

create database gregs_list; -- 创建数据库
USE gregs_list; -- 使用这个数据
createtable doughnut_list
(
doughnut_namevarchar(10), 
doughnut_typevarchar(6)  
);
 -- varchar:varible character,可变动字符
 -- 用于保存以文本格式存储的信息，这段文字的长度最多只能有6个字符
 
 create table my_contacts
 (
 last_name varchar(30),
 first_name varchar(20),
 email varchar(50),
 birthdaydate,   -- 可存储日期数据
 proffession varchar(50),
 location varchar(50),
 status varchar(20),
 interests varchar(100),
 seeking varchar(100)
 );
 
 desc my_contacts; -- desc是describe的缩写，检查创建的表

不可以重建已经存在的表或者数据库

删除表：

Drop table my_contacts;

 

2. 数据类型

VARCHAR                             存储文本，（）内是最大字符长度，最大长度可以达255个字符

BLOB                                     大量文本数据，比如一段文本，超过255个字元

INT/INTECER                       整数，也可以处理负数

DEC（DECJMAL的缩写） 提供数值空间，直到装满为止

DATE                                     记录日期，但是不喜欢插手TIME的事情

DATETIME/TIMESTAMP    记录日期和时间。类似TIME，但TIME不喜欢插手DATE；TIMESTAMP通常用于记录“当下”这个时刻，DATETIME更适合存储将来的时间

CHAR/CHARACTER              负责是数据必须是事先设定的长度

 

例子：

Price   5678.39    DEC(6,2)   -- 前者代表总位数，后者是小数点后的位数
Gender  单个字符F或M   CHAR(1)
Anniversary  11/22/2006  DATE
Phone_number 2015552367   CHAR(10) -- 电话号码必为这个长度，而且要把电话号码当成文本字符串，因为虽是数字，却不需要任何数学运算

 

3. INSERT语句

insert into my_contacts
(last_name,first_name,email,gender,birthday,proffession,location,status,interests,seeking)
 values('anderson','jillian','jill_anderson@breakdown.com','f','1980-09--5','technicalwiriter','paloalto,ca','single','kayaking,reptiles','relationship,friends');

• INSERT语句中的CHAR,VARCHAR,DATE,BLOB的值需要加上单引号，这表示DEC,INT等数值不需要加上单引号
• insert插入多行，多行之间用逗号隔开

 

insert into my_contacts
(last_name,gender,birthday)
 values ('anderson','f','1980-09--5',);

不需要提供所有列数据，可以只提供已经知道值的列；

 

NULL与''的区别：NULL代表未定义的值；''空字符串，数据值或者文本字符串，长度为0

NULL使用：

Create table my_contacts
(last_name varchar(30) not null,
first_name varchar(20) not null);-- 一定要提供NOTNULL的列，否则会跳出错误信息

DEFAULT关键字

如果有些列通常有某个特定值，就可以把特定值指派为default默认值；default关键字后面的值会在每次新增记录时自动插入表中——只要没有另外指派其他值。

create table doughunt_list
(doughnut_name varchar(10) not null,
doughnut_type varchar(6) not null,
doughnut_cost dec(3,2) not nulldefault 1.00);

解决不成对的单引号：单引号是特殊字符，放在双单引号之间要进行转义,\'或者'';

不要使用双引号，SQL日后会搭配其他编程语言，单引号才会被视为SQL语句的一部分

 

对文本数据套用比较运算符：

Where drink_name >'L' --返回名称首字母位于‘L’后的名称

不等于：<>

 

IS NULL

Select drink_name from drink_info where calories is null;

 

LIKE：通配符

Select * from my_contacts where location like '%CA'; -- 以CA结尾的值
-- % 百分号是任意数量的未知字符的替身
Select first_name from my_contacts where first_name like '_im';
-- _下划线只是一个未知字符的替身

BEWEEN

Where calories between 30 and 60; -- 等于使用<=和>=

IN

Where rating in ('innovative','fabulous'); -- 相当于or

NOT IN --意思是不包括

 

更多NOT

not可以和beteewn和like一起使用，重点是not一定要紧接在where的后面（not in 例外）

Select drink_name from drink_info where not carbs between 3 and 5;
Select date_name from black_book where not date_name like 'A%' andnot date_name like 'B%'; -- 当not和and 或or一起使用时，则要直接接在and 或or 后面

4. DELETE和UPDATE

DELETE

Delete from clown_info where activities='dancing';

• delete不能删除单一列中的值或者表中某一列的所有值；
• delete可用于删除一行或者多行，根据where子句而定
• delete可以删除表中的每一行：delete from your_table;
• Insert into…delete from这两句一起使用，用于修改表的某一个值（先插入新行，然后删除旧行）
• 除非非常确定where子句只会删除你打算删除的行，否则都应该用select确认情况

 

为避免删错的Delete步骤：

select * from clown_info where activities='dancing';
insert into clown_info values('zippo','millstone mall','f,orangesuit',' baggy pants','dancing,singing');
delete from clown_info where activities='dancing';

UPDATE

与其插入新行后再删除旧行，其实可以重新使用已经存在的记录，真正做到只调整需要改变的列。（可以取代delete与insert的组合）

Update doughnut_ratings
set type='glazed' where type='plain glazed';
使用update，可以改变单一列或者所有列的值，在set子句中加入更多column=value组，其间以逗号分隔：
Update your_table
Set first_column ='newvalue';
Second_column='another_value';

Update可用于更新单一行或多行，一切都交给where子句决定

 

5. 表的设计

主键：

每个数据行必须有独一无二的识别项，即主键。

主键不可以为NULL；插入新纪录时必须制定主键值；主键必须简介；主键值不可以被修改。

最佳方式或许是另外创建一个包含唯一性主键的列

 

Show create table my_contacts;
-- 返回可以重建表但没有数据的create_table语句，这样就可以随时查看表的可能创建方式。
Show columns from tablename; -- 显示表的所有列及其数据类型，还有其他关于各列的细节信息。
Show create dataase databasename; -- 提供重建表所需的语句
Show index from tablename; -- 显示任何编了索引的列以及索引类型
Show warnings; -- 如果你从控制台收到SQL命令造成的错误信息，这个命令就可以取得确切的

create table my_contacts
 (
Contact_id int not null,
 last_name varchar(30) defualt null,
 first_name varchar(20) defualt null,
 email varchar(50) defualt null,
 birthday date defualt null,   -- 可存储日期数据
 proffession varchar(50) defualt null,
 location varchar(50) defualt null,
 status varchar(20) defualt null,
 interests varchar(100) defualt null,
 seeking varchar(100) defualt null,
Primary key (contact_id)
 );

 

AUTO_INCREMENT

SQL自动为该列填入数字，第一行填入1，后面依序递增;如果id要求not null 但是输入了null,auto_increment会忽略null,而在没有auto_increment的情况下会收到错误信息。

create table my_contacts
 (
Contact_id int not null auto_increment,
…
Primary key (contact_id)
 );

 

6. ALTER

ALTER（如同更新表中每条记录一般）：以表中现有数据为前提，修改表的名称及整体结构。

ALTER搭配ADD

ALTER搭配 DROP

ALTER搭配CHANGE

StringFunctions

为现有的表添加主键，且主键为auto_crement：

Alter table my_contacts
Add column contact_id int not null auto_crement first, -- first要求把新列放在最前面（把主键列放在最前面是一个不错的习惯），类似还有second,third
Add primary key(contact_id);
为表添加新的一列（非主键）：
Alter table my_contacts
Add column phone varchar(10); -- 这里电话号码均是10个字符的长度
After first_name; --关键字after紧跟在新添加的列的名称后面，把phone列放在first_name列后，默认是放在表的最后一列

 

修改表

ALTER命令计划能让你改变表里的一切，而且不需要重新插入数据，但也要小心，如果改变列的类型可能就会有遗失数据的风险

CHANGE可同时改变现有列的名称和数据类型

MODIFY修改现有列的数据类型或位置

ADD在当前表中添加一列，可自选类型

DROP从表中删除某列

 

RENAME表改名

Alter table projekts
Rename to project_list;

 

ALTER和CHANGE

Alter table project_list
Chage column number proj_id int not null auto_increment,
Add primary key(proj_id); --同时改变名和类型

改变两个列：

Alter table project_list
Change column descriptionofproj proj_desc varchar(100),
Chage column contractoronjob con_name varchar(30);

数据改变为另一类型，可能会丢失数据

 

MODIFY

改变列的类型：

Alter table project_list
Modify column proj_desc varchar(120); 

删除列

Alter table project_list
Drop column start_date;

综合使用：

Alter table hooptie
Rename to car_table,
Add column car_id int not null auto_increment first,
Add primary key(car_id),
Add column vin varchar(16) after car_id,
Change column mo model varchar(20), -- 这里必须提供重命名的列的数据类型
Modify column color after model,
Modify column year sixth,
Change column howmuch price decimal(7,2);

 

一些字符串函数

select最后两个字符：

Right()和left()可从列中选出指定数量的字符

Select right(location,2) from my_contacts; -- 从使用的列的右侧开始选取两个字符

select逗号前的所有内容：

Substring_index()则可攫取部分列值（子字符串），找出指定字符或字符串前的所有内容。

Select substring_index(location,',',1) from my_contacts; -- 1代表命令寻找第一个逗号

其他字符串函数：uper(),lower(),length(),ltrim(),rtrim()清楚多余空格

重要：字符串函数不会改变存储在表中的内容，只是把字符串修改后的模样当成查询结果返回。

 

UPDATE

改变每一行中的同一列的值的语法：

Update table_name
Set column_name=newvalue;
以现有列的内容填入新列：
Update my_contacts
Set state=right(location,2); -- 这个函数会攫取location列的最后两个字符存储到新列里；首先抓出第一条记录的location列并套用函数，然后抓到第二行的location列套用函数…