SQL基础语法（1）

这篇blog是SQL基础语法，只是作为我平时查阅和备份，仅适合初学者学习阅读，高手绕行。
转载时注明地址http://fuchangle.iteye.com/blog/1772395
SQL的全称是Structured Query Language, 也就是结构化查询语言。SQL是操作和检索关系型数据库的标准语言，标准SQ;语言可用于操作任何的关系数据库。
作用：
1.在数据库中检索需要的信息
2.对数据库的信息进行更新
3.改变数据库的结构
4.更改系统的安全设置
5.增加用户对数据库或表的许可权限

标准SQL语句通常可分为如下几种类型：
1.查询语句：主要由于select关键字完成，查询语句是SQL语句最复杂，功能也最丰富的语句。
2.DML语句(Data Manipulation Language,即数据库操作语言)：主要由insert，update和delect三个关键字完成。
3.DDL语句(Data Definition Language，即数据库定义语言): 主要由create、alter、drop和truncate四个关键字完成
5 DCL语句(Data Controll Language,即数据库控制语言)：主要由grant和revoke两个关键字完成。
6 事务控制语句：主要由commit、rollback和savepoint三个关键字.

数据库最基本的数据库对象是数据表，数据表是存放数据的逻辑单元。但数据库里绝不仅包括数据表

对象名称--对应关键字--描述
表 --table--表是存储数据的逻辑单元，以行和列的形式存在；列就是字段，行就是记录
数据字典-- --就是系统的表，存储数据库相关信息的表，系统表里的数据通常由数据库系统维护，程序员通常不应该手动修改系统表及内部的数据，程序员只可查看系统表的数据。
约束--constraint--执行数据校验的规则，用于保证了数据完整性的规则
视图--view--一个或多个数据表里的逻辑显示，视图并不存储数据。
索引--index--用于提高查询的性能，相当于书的目录
函数--function--用于完成一次特定的计算，具有一个返回值
存储过程--procedure--用于完成一次完整的业务逻辑，没有返回值
触发器--tigger--相当于一个事件监听器，当数据库发生特定事件后，触发器被触发，完成响应的处理。


建立数据表只是建立表结构，就是指定该数据表有多少列，每列的数据类型，所以建表语句的重点就是园括号里的列定义，列定义由列名、列类型和可选的默认值组成。
如果使用子查询建表则可以在建表同时插入数据。

create table haha
as
select * from user_inf;

上面语法中新表的字段列表与子查询中的字段列表数量匹配，创建新表时的字段列表可以省略，如果省略了该字段列表，则新表的列名字与选择结果完全相同。

sql建表模板示例：

DROP DATABASE IF EXISTS databaseWeb; --如果存在，则删除数据库databaseWeb
CREATE DATABASE databaseWeb CHARACTER SET utf8; --创建数据库，使用utf8编码

USE databaseWeb; --切换到数据库databaseWeb
set Names 'gbk';	--控制台使用gbk编码

DROP TABLE IF EXISTS tb_person;
CREATE TABLE tb_person(
	id INTEGER AUTO_INCREMENT COMMENT 'id',
	name VARCHAR(45) COMMENT '姓名',
	english_name VARCHAR(45) COMMENT '英文名',
	age INTEGER UNSIGNED COMMENT '年龄',
	sex VARCHAR(45) COMMENT '性别',
	birthday DATE COMMENT '出生日期',
	description TEXT COMMENT '备注',
	create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP() COMMENT '创建时间',
	PRIMARY KEY(id)
);

INSERT INTO tb_person
(name,english_name,age,sex,birthday,description) values('刘京华','Helloweenvsfei','25','男','1982-08-09','无备注');

INSERT INTO tb_person
(name,english_name,age,sex,birthday,description) values('科特柯本','Kurt Cobain','27','男','1967-02-20','Nirvana');

INSERT INTO tb_person
(name,english_name,age,sex,birthday,description) values('李四','Faye','31','女','1969-08-08','狮子座');

INSERT INTO tb_person
(name,english_name,age,sex,birthday,description) values('张三','Foo Bar','18','女','2008-08-08','');

修改表结构的语法
修改表结构使用alter table，修改表包括增加列定义、修改列定义、删除列、重命名列等操作。增加列定义的语法是
添加新数据列
alter table haha
add {
aaa varchar(255) default 'xxx',
bbb varchar(255)
}

修改数据列(不支持一次修改多个列定义，oracle可以，和add格式相似)

alter table hehe
modify hehe_id varchar(255);

如果修改数据列的默认值，只会对以后的插入操作有作用，对以前已经存在的数据不会有任何影响。

从数据库中删除列的语法比较简单

alter table hehe
adop aaa;

从数据表中删除列定义通常总是可以成功的

MySQL提供两种特殊的语法，重命名数据列和完全改变列定义
如下SQL语句用于将hehe表命名为wawa

alter table hehe
rename to wawa;

MySql为alter table 提供了change选项，该选项可以改变列名，change语法如下
一般而言，如果不需要改变列名，使用alter table的modify选项即可，只有当需要修改列名时才会使用change选项

alter table wawa
change bbb ddd int;

truncate表
truncate被称为"截断"某个表--它的动作是删除该表里的全部数据，但保留表结构。只能删除整个表的全部记录
truncate 表名
-----------------------------------------
约束概述
通过约束可以更好的保证数据表的完整性，约束是在表上强制执行的数据校验规则，约束主要用于保证数组库里的完整性。除此之外，当表中数据存在相互依赖时，可以保护相关

数据不被删除。
大部分数据库支持下面五类完整性约束；
NOT NULL:非空约束，指定某列不能为空。
UNIQUE:唯一约束，指定某列或者几列组合不能重复。
PRIMARY KEY:主键，指定该列的值可以唯一地标识该条记录。
FOREIGN KEY:外键，指定该行记录从属于主表的一条记录，主要用于保证参照完整性。
CHECK：检查，指定一个布尔型表达式，用于指定对应列的值必须满足该表达式
根据约束对数据列限制，可以分为如下两类：
1，单行约束：每个约束只约束一列。
2，多行约束：每个约束约束多个数据列。
为数据表指定约束有两个时机：
1，在建表的同时为相应的数据列指定约束。
2，建表后创建，以修改表的方式来增加约束。
一、NOT NULL约束
非空约束用于确保指定列不允许为空，非空约束是比较特殊的约束，只能作为列级使用。这里介绍一下SQL中的NULL 有如下特征：
1，所有的数据类型的值都可以为null,包括int float等数据类型。
2，与java类似的是，空字符串不等于null，0也不等于null.
建表示指定非空约束：
hehe_id int not null,
也可以在使用alter table修改表时增加或者删除非空约束

--增加非空约束
alter table hehe
modify hehe_gender varchar(2) not null;
--取消非空约束
alter table hehe
modify hehe_nanes varchar(2) null;

二、UNIQUE 约束
唯一性约束用于保证指定列或指定列的组合不允许出现重复值，可以出现多个NULL
唯一约束既可以使用列级约束语法建立，也可以使用表级约束的语法建立。如果需要为多列建组合约束，或者需要为唯一约束指定约束名，则只能用表级约束语法。
使用列级约束语法来建立唯一约束非常简单，只要简单的在列定义后增加unique关键字即可
test_name varchar(255) unique
如果需要为多列组合建立唯一约束，或者想自行指定约束名，则需要使用表级约束语法,表级约束语法如下：
[constraint 约束名] 约束定义
--建表时创建唯一约束，使用表级的约束语法建约束

--建表时创建唯一约束，使用表级的约束语法建约束
create table unique_test2{
	--建立了非空约束，着意味着test_id不可以为NULL
	test_id int not null,
	test_name varchar(255),
	test_pass varchar(255),
	--使用行级约束语法建唯一约束
	unique(test_name),
	--使用表级约束语法建唯一约束，而且指定约束名
	constraint test_uk unique(test_pass)
};
上面建表语句为test_name、test_pass分别建立唯一约束，这意味着两列不能为空，除此之外还可以为这两列组合建立唯一约束，
--建表时创建唯一约束，使用表级约束语法建约束
create table unique_test3{
	--建立了非空约束，着意味着test_id不可以为NULL
	test_id int not null,
	test_name varchar(255),
	test_pass varchar(255),
	--使用表级约束语法建唯一约束，指定两列组合不能为空
	constraint test3_uk unique(test_name, test_pass)
};

unique_test2要求test_name, test_pass不能为空，而unique_test3只要求test_name, test_pass不能同时为空。
也可以修改表结构增加唯一约束

--添加唯一约束
alter table unique_test3
add unique(test_name, test_pass);

也可以在修改表时使用modify关键字，为单列采用列级约束语来增加唯一约束

--为haha表的text_name列增加唯一约束
alter table_haha
modify test_name varchar(255) unique;

对于大多数数据库而言，删除约束都是在alter table语句后使用drop constraint约束名的语法删除约束，但mysql并不使用这种方式，而是使用drop index 约束名的方式来删除

约束，例如:

alter table unique_test3
drop index_test3_uk

三、PRIMARY KEY约束
主键约束相当于非空约束和唯一约束，即主键上约束不允许出现重复值，也不允许出现null值，如果对多列组合建立主键约束，则多列里包含的每一列都不能为空，但只要求这些

列组合不能重复。
每个表中最多允许一个主键，但这个主键约束可由多个数据列组合而成。主键是表中能唯一确定一行记录的字段或字段组合。
建立主键约束时既可以使用列级约束语，也可使用表级约束语。如需要对多个字段建立组合主键约束时，则只能使用表级约束语法。
MySql总是将所有主键约束命名为PRIMARY.
建表时创建主键约束，使用的是列级约束语法：

create table primary_test(
	--建立主键约束
	test_id int primary key,
	test_name varhar(255)
);

建表时创建主键约束，使用表级约束语法
create table primary_test2(
	test_id int not null,
	test_name varchar(255),
	test_pass varchar(255),
	#指定主键名为test2_pk，对大多数数据库有效，但对mysql无效
	#MySQL数据库中该主键约束依然是primary
	constraint test2_pk primary key(test_id)	
);


建表时创建主键约束，以多列建立组合主键，只能使用表级约束语法：
create table primary_test3(
	test_name varchar(255),
	test_pass varchar(255),
	#使用表级约束建立多列组合主键约束
	primary key(test_name, test_pass)
);

如果需要删除指定表的主键约束，则在alter table语句后使用drop primary_key字句即可。

#删除主键约束
alter table primary_test3
drop primary key;

如果需要为指定表增加主键约束，即可通过modify修改列定义来增加主键约束，这将采用列级约束语法增加主键约束
也可通过add来增加主键约束，这将采用表级约束语法来增加主键约束

#使用列级约束语法增加主键约束
alter table_primary_test3
modify test_name varchar(255) primary key;
#使用表级约束语法增加主键约束
alter table primary_test3
add primary key(test_name,test_pass);

MySQL只用auto_increment来设置自增长

create table primary_test4(
	#建立主键约束，使用自增长
	test_id int auto_increment primary key,
	test_name varchar(255),
	test_pass varchar(255),
);

四、FOREIGN KEY约束
外键约束主要保证一个或两个数据表之间的参照完整性，外键是构建于一个表的两个字段或者两个表的两个字段之间的参照关系。
关系：子(从)表外键列的值必须在主表被参照列的值范围之内，或者为空。
当主表的记录被从表的记录参照时，主表记录不允许被删除，必须先把从表里参照该记录的所有记录全部删除后，才能可以删除主表
从表外键参照的只能是主表主键列或者唯一键列，这样才可保证从表记录可以准确定位到被参照的主表记录。同一个表内可以拥有多个外键。
外键约束通常用于定义两个实体之间的一对多，一对一的关联关系，对于一对多的关联关系，通常在多的一端增加外键列。
对于一对一的关联关系，则可以选择任意一方增加外键列，增加外键列的表被称为从表
对于多对多的关联关系，则需要额外增加一个连接表来记录他们的关联关系。‘
建立外键约束同样可以采用列级约束语法和表级约束语法。如果仅对单独的数据列建立外键约束，则可以使用列级约束语法即可；如果需要对多列组合创建外键约束，或者需要为

外键指定名字，则必须使用表级约束语。
采用列级约束语法建立外键约束直接使用references关键字，references指定该列参照哪个表，以及参照主表的那一列。

#为了保证从表参照的主表存在，通常应该先创建主表
create table teacher_table1(
	#auto_increment：代表数据库的自动编号策略，通常用作数据库的逻辑主键
	teacher_id int auto_increment,
	teacher_name varchar(255),
	primary key(teacher_id)
);
create table student_table1(
	student_id int auto_increment primary key,
	student_name varchar(255),
	#指定java_teacher参照到teacher_table的teacher_id列
	java_teacher int references teacher_table1(teacher_id)
);

但值得指出的是，虽然mysql支持使用列级约束语法来建立外键约束，但这种列级约束语法建立的外键约束不会生效，mysql提供这种列级约束语法仅仅是为了和标准的SQL保持良好

的兼容性，如果要使用mysql中的外键约束生效，则应该使用表级约束语法。

#为了保证从表参照的的主表存在，通常应该先建主表
create table teacher_table(
	#auto_increment：代表数据库的自动编码策略，通常用作数据表的逻辑主键
	teacher_id int auto_increment,
	teacher_name varchar(255),
	primary key(teacher_id)
);
create table student_table(
	student_id int auto_increment primary key,
	studnet_name varchar(255),
	#指定java_teacher参照到teacher_table的teacher_id列
	java_teacher int,
	foreign key(java_teacher) references teacher_table(teacher_id)
);

如果使用表级约束语，则需要使用foreign key来指定本表的外键列，并使用references来指定参照到那个表，以及参照到主表的哪个数据库。
如果需要建立多列组合的外键约束，则必须使用表级约束语法，如下

create table teacher_table(
	teacher_name varchar(255),
	teacher_pass varchar(255),
	#以两列建立组合主键
	primary key(teacher_name, teacher_pass)
);

create table student_table(
	#为本表建立主键约束
	student_id int auto_increment primary key,
	student_name varchar(255),
	java_teacher_name varchar(255),
	java_teacher_pass varchar(255),
	#使用表级约束语法建立外键约束，指定两列的联合外键
	foreign key(java_teacher_name, java_teacher_pass)
		references teacher_table(teacher_name, teacher_pass)
);

删除外键约束的语法也很简单，在alter table后增加"drop foreign key 约束名"字句即可，如

#删除student_table3表上名为student_talbe_ibkf_1的外键约束
alter table student_table
drop foreign key student_talbe_ibkf_1;

增加外键约束通常使用add foreign key命令，如下

#修改student_table的数据表，增加外键约束
alter table student_table
add foreign key(java_teacher_name, java_teacher_pass)
		references teacher_table(teacher_name, teacher_pass);

值得指出的是，外键约束不仅可以参照其他表，而且可以参照自身，这种参照自身的情况通常被称为自关联。
下面的SQL语句用于建立自关联的外键约束。

#使用表级约束语法建立外约束键，且直接参照自身
create table foreign_test(
	foreign_id int auto_increment primary key,
	foreign_name varchar(255),
	#使用该表的refer_id参照到本表的foreign_id列
	refer_id int,
	foreign key(refer_id) references foreign_test(foreign_id)
);

如果想定义当删除主表纪录时，从表的记录也会随之删除，则需要在建立外键约束后添加on delete cascade或添加 on delete set null,
第一种是删除主表纪录时，把参照该主表记录的从表记录全部级联删除；第二种是指定当删除主表纪录时，把参照该主表记录的从表记录外键设为NULL，如下：

create table teacher_table(
	teacher_id int auto_increment,
	teacher_name varchar(255),
	primary key(teacher_id)
);
create table studnet_table(
	#为本表建立主键约束
	student_id int auto_increment primary key,
	studnet_name varchar(255),
	java_teacher int
	foreign key(java_teacher) references teacher_table(teacher_id)
		on delete cascade 
		#也可以使用on delete set null
);

五CHECK约束
当前版本的mysql支持建表时指定CHECK约束，但这个CHECK约束不会有任何作用，建立CHECK约束的语法很简单，只要在建表的列定义后增加check即可。如

create table check_test(
	emp_id int auto_increment,
	emp_name varchar(255),
	emp_aslary decimal,
	#创建CHECK约束
	check(emp_salary>0)
);

虽然上面的SQL语句建立的check_test表中有CHECK约束，CHECK约束要求emp_salary大于0，但这个要求实际上并不会起作用。

待续...