一、数据库简单介绍
1、 按照数据库的发展时间顺序，主要出现了以下类型数据库系统：
Ø 网状型数据库
Ø 层次型数据库
Ø 关系型数据库
Ø 面向对象数据库
上面4中数据库系统中，关系型数据库使用最为广泛。面向对象数据库则是由面向对象语言催生的新型数据库，目前的一些数据库系统，如：SQL Server 2005、Oracle10g等都开始增加面向对象的特性。
二、常用基本SQL语句/语法
Ø SQL 语句基础理论
SQL是操作和检索关系型数据库的标准语言，标准SQL语句可用于操作然后关系型数据库。
标准的 SQL 语句通常划分为以下类型：
查询语句： 主要由于select关键字完成，查询语句是SQL语句中最复杂，功能最丰富的语句。
DML （Data Munipulation Language，数据操作语言）语句，这组DML语句修改后数据将保持较好的一致性；操作表的语句，如插入、修改、删除等；
DDL （Data Definition Language，数据定义语言）语句，操作数据对象的语言，有create、alter、drop。
DCL （Data Control Language，数据控制语言）语句，主要有grant、revoke语句。
事务控制语句： 主要有commit、rollback和savepoint三个关键字完成
DDL 语句
DDL语句是操作数据库对象的语句，包括创建create、删除drop、修改alter数据库对象。
常见数据库对象
|
对象名称 |
对应关键字 |
描述 |
|
表 |
table |
表是数据库存储的逻辑单元，以行和列的形式存在；列是字段，行就是一条数据记录 |
|
数据字典 |
就是系统表，存储数据库相关信息的表，系统表里的数据通常有数据库系统维护。系统表结构和数据，开发人员不应该手动修改，只能查询其中的数据 |
|
|
视图 |
view |
一个或多个数据表里的数据的逻辑显示，视图就是一张虚拟的表，并不真正存储数据 |
|
约束 |
constraint |
执行数据检验规则，用于保证数据完整性的规则 |
|
索引 |
index |
用于提高查询性能，相当于书的目录 |
|
函数 |
function |
用于完成一个特定的计算，具有返回值和参数 |
|
存储过程 |
procedure |
完成某项完整的业务处理，没有返回值，但可通过传出参数将多个值传个调用环境 |
|
触发器 |
trigger |
相当于一个事件的监听器，当数据库发生特定的事件后，触发器被触发，完成响应处理 |
上面的对象都可以通过用create、alter、drop完成相关的创建、修改、删除操作。
常用数据类型
|
列类型 |
说明 |
|
tinyint/smallint/mediumint int(integer)/bigint |
1字节、2字节、3字节、4字节、8字节整数，又可分有符号和无符号两种。这些整数类型的区别仅仅表现范围不同 |
|
float/double |
单精度、双精度浮点类型 |
|
decimal(dec) |
精确小数类型，相当于float和double不会产生精度丢失问题 |
|
date |
日期类型，不能保存时间。当Java里的Date对象保存到该类型中，时间部分丢失 |
|
time |
时间类型，不能保存日期。当Java的Date对象的保存在该类型中，日期部分丢失 |
|
datetime |
日期、时间类型 |
|
timestamp |
时间戳类型 |
|
year |
年类型，仅保存年份 |
|
char |
定长字符串类型 |
|
varchar |
可变长度字符串类型 |
|
binary |
定长二进制字符串类型，它以二进制形式保存字符串 |
|
varbinary |
可变长度的二进制字符串类型，二进制形式保存字符串 |
|
tingblob/blob mediumblob/longblob |
1字节、2字节、3字节、4字节的二进制大对象，可存存储超图片、音乐等二进制数据，分别可存储：255/64K/16M/4G的大小 |
|
tingtext/text mediumtext/longtext |
1字节、2字节、3字节、4字节的文本对象，可存储超长长度的字符串，分别可存储：255/64K/16M/4G的大小的文本 |
|
enum(‘val1’, ‘val2’, …) |
枚举类型，该列的值只能是enum括号中出现的值的之一 |
|
set(‘value1’, ‘value2’, …) |
集合类型，该列的值可以是set中的一个或多个值 |
Ø 常用查询
MySQL结束符是“；”结束。
1、 显示所有数据库
show databases;
2、 删除数据库
drop
database
dbName;
3、 创建数据库
create
database
[
if
not
exists
] dbName;
中括号部分可选的，判断该数据不存在就创建
4、 切换、使用指定数据库
use
dbName;
5、 显示当前使用数据库所有的表对象
show tables；
6、 显示表结构describe（
desc
）
desc
tableName;
7、 创建一张表
create
table
user
(
--
int
整型
uId
int
,
--小数
uPrice
decimal
,
--普通长度文本，default设置默认值
uName
varchar
(255)
default
‘zhangsan’,
--超长文本
uRemark text,
--图片
uPhoto
blob
,
--日期
uBirthday datetime
);
8、 子查询建表方法
部分列名匹配模式：
create
table
userInfo (
name
varchar
(20),
sex
char
)
as
select
name, sex
from
user
;
上面的列名和子查询的列名以及类型要对应
全部列名模式：
create
table
userInfo
as
select
*
from
user
;
直接将整个表的类型和数据备份到新表userInfo中
9、 添加表字段
添加单列
alter
table
user
add
tel
varchar
(11)
default
‘02012345678’;
添加多列
alter
table
user
add
(
photo
blob
,
birthday
date
);
上面就同时增加了多列字段
10、 修改表字段
修改tel列
alter
table
user
modify
tel
varchar
(15)
default
‘02087654321’;
修改tel列的位置，在第一列显示
alter
table
user
modify
tel
varchar
(15)
default
'02087654321'
first
;
修改tel列的位置，在指定列之后显示
alter
table
user
modify
tel
varchar
(15)
default
'02087654321'
after
age;
注意：
alter
modify不支持一次修改多个列，但是Oracle支持多列修改
但是MySQL可以通过多个modify的方式完成：
alter
table
user
modify
tel
varchar
(15)
default
'02087654321'
first
,
modify
name
varchar
(20)
after
tel;
11、 删除指定字段
alter
table
user
drop
photo;
12、 重命名表数据
表重命名
alter
table
user
rename
to
users;
字段重命名
alter
table
users change name u_name
varchar
(10);
alter
table
users change sex u_sex
varchar
(10)
after
u_name;
如果需要改变列名建议使用change，如果需要改变数据类型和显示位置可以使用modify
13、 删除表
drop
table
users;
drop删除表会删除表结构，表对象将不存在数据中；数据也不会存在；表内的对象也不存在，如：索引、视图、约束；
truncate删除表
truncate都被当成DDL出来，truncate的作用就是删除该表里的全部数据，保留表结构。相当于DDL中的delete语句，
但是truncate比delete语句的速度要快得多。但是truncate不能带条件删除指定数据，只会删除所有的数据。如果删除的表有外键，
删除的速度类似于delete。但新版本的MySQL中truncate的速度比delete速度快。
Ø 约束
MySQL中约束保存在information_schema数据库的table_constraints中，可以通过该表查询约束信息；
约束主要完成对数据的检验，保证数据库数据的完整性；如果有相互依赖数据，保证该数据不被删除。
常用五类约束：
not
null
：非空约束，指定某列不为空
unique
： 唯一约束，指定某列和几列组合的数据不能重复
primary
key
：主键约束，指定某列的数据不能重复、唯一
foreign
key
：外键，指定该列记录属于主表中的一条记录，参照另一条数据
check
：检查，指定一个表达式，用于检验指定数据
MySQL不支持check约束，但可以使用check约束，而没有任何效果；
根据约束数据列限制，约束可分为：
单列约束：每个约束只约束一列
多列约束：每个约束约束多列数据
MySQL中约束保存在information_schema数据库的table_constraints中，可以通过该表查询约束信息；
1、
not
null约束
非空约束用于确保当前列的值不为空值，非空约束只能出现在表对象的列上。
Null类型特征：
所有的类型的值都可以是null，包括int、float等数据类型
空字符串“”是不等于null，0也不等于null
create
table
temp(
id
int
not
null
,
name
varchar
(255)
not
null
default
‘abc’,
sex
char
null
)
上面的table加上了非空约束，也可以用alter来修改或增加非空约束
增加非空约束
alter
table
temp
modify
sex
varchar
(2)
not
null
;
取消非空约束
alter
table
temp
modify
sex
varchar
(2)
null
;
取消非空约束，增加默认值
alter
table
temp
modify
sex
varchar
(2)
default
‘abc’
null
;
2、
unique
唯一约束是指定table的列或列组合不能重复，保证数据的唯一性。虽然唯一约束不允许出现重复的值，但是可以为多个null
同一个表可以有多个唯一约束，多个列组合的约束。在创建唯一约束的时候，如果不给唯一约束名称，就默认和列名相同。
唯一约束不仅可以在一个表内创建，而且可以同时多表创建组合唯一约束。
MySQL会给唯一约束的列上默认创建一个唯一索引；
create
table
temp (
id
int
not
null
,
name
varchar
(25),
password
varchar
(16),
--使用表级约束语法，
constraint
uk_name_pwd
unique
(name, password)
);
表示用户名和密码组合不能重复
添加唯一约束
alter
table
temp
add
unique
(name, password);
alter
table
temp
modify
name
varchar
(25)
unique
;
删除约束
alter
table
temp
drop
index
name;
3、
primary
key
主键约束相当于唯一约束+非空约束的组合，主键约束列不允许重复，也不允许出现空值；如果的多列组合的主键约束，
那么这些列都不允许为空值，并且组合的值不允许重复。
每个表最多只允许一个主键，建立主键约束可以在列级别创建，也可以在表级别上创建。MySQL的主键名总是PRIMARY，
当创建主键约束时，系统默认会在所在的列和列组合上建立对应的唯一索引。
列模式：
create
table
temp(
/*主键约束*/
id
int
primary
key
,
name
varchar
(25)
);
create
table
temp2(
id
int
not
null
,
name
varchar
(25),
pwd
varchar
(15),
constraint
pk_temp_id
primary
key
(id)
);
组合模式：
create
table
temp2(
id
int
not
null
,
name
varchar
(25),
pwd
varchar
(15),
constraint
pk_temp_id
primary
key
(name, pwd)
);
alter删除主键约束
alter
table
temp
drop
primary
key
;
alter添加主键
alter
table
temp
add
primary
key
(name, pwd);
alter修改列为主键
alter
table
temp
modify
id
int
primary
key
;
设置主键自增
create
table
temp(
id
int
auto_increment
primary
key
,
name
varchar
(20),
pwd
varchar
(16)
);
auto_increment自增模式，设置自增后在插入数据的时候就不需要给该列插入值了。
4、
foreign
key
约束
外键约束是保证一个或两个表之间的参照完整性，外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系。
也就是说从表的外键值必须在主表中能找到或者为空。
当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖该记录的数据，
然后才可以删除主表的数据。还有一种就是级联删除子表数据。
注意：外键约束的参照列，在主表中引用的只能是主键或唯一键约束的列，假定引用的主表列不是唯一的记录，
那么从表引用的数据就不确定记录的位置。同一个表可以有多个外键约束。
创建外键约束：
主表
create
table
classes(
id
int
auto_increment
primary
key
,
name
varchar
(20)
);
从表
create
table
student(
id
int
auto_increment,
name
varchar
(22),
constraint
pk_id
primary
key
(id),
classes_id
int
references
classes(id)
);
通常先建主表，然后再建从表，这样从表的参照引用的表才存在。
表级别创建外键约束：
create
table
student(
id
int
auto_increment
primary
key
,
name
varchar
(25),
classes_id
int
,
foreign
key
(classes_id)
references
classes(id)
);
上面的创建外键的方法没有指定约束名称，系统会默认给外键约束分配外键约束名称，命名为student_ibfk_n，
其中student是表名，n是当前约束从1开始的整数。
指定约束名称：
create
table
student(
id
int
auto_increment
primary
key
,
name
varchar
(25),
classes_id
int
,
/*指定约束名称*/
constraint
fk_classes_id
foreign
key
(classes_id)
references
classes(id)
);
多列外键组合，必须用表级别约束语法：
create
table
classes(
id
int
,
name
varchar
(20),
number
int
,
primary
key
(name, number)
);
create
table
student(
id
int
auto_increment
primary
key
,
name
varchar
(20),
classes_name
varchar
(20),
classes_number
int
,
/*表级别联合外键*/
foreign
key
(classes_name, classes_number)
references
classes(name, number)
);
删除外键约束：
alter
table
student
drop
foreign
key
student_ibfk_1;
alter
table
student
drop
foreign
key
fk_student_id;
增加外键约束
alter
table
student
add
foreign
key
(classes_name, classes_number)
references
classes(name, number);
自引用、自关联（递归表、树状表）
create
table
tree(
id
int
auto_increment
primary
key
,
name
varchar
(50),
parent_id
int
,
foreign
key
(parent_id)
references
tree(id)
);
级联删除：删除主表的数据时，关联的从表数据也删除，则需要在建立外键约束的后面增加on
delete
cascade
或on
delete
set
null
，前者是级联删除，后者是将从表的关联列的值设置为null。
create
table
student(
id
int
auto_increment
primary
key
,
name
varchar
(20),
classes_name
varchar
(20),
classes_number
int
,
/*表级别联合外键*/
foreign
key
(classes_name, classes_number)
references
classes(name, number)
on
delete
cascade
);
5、 check约束
MySQL可以使用check约束，但check约束对数据验证没有任何作用。
create
table
temp(
id
int
auto_increment,
name
varchar
(20),
age
int
,
primary
key
(id),
/*check约束*/
check
(age > 20)
);
上面check约束要求age必须大于0，但没有任何作用。但是创建table的时候没有任何错误或警告。
Ø 索引
索引是存放在模式（
schema
）中的一个数据库对象，索引的作用就是提高对表的检索查询速度，
索引是通过快速访问的方法来进行快速定位数据，从而减少了对磁盘的读写操作。
索引是数据库的一个对象，它不能独立存在，必须对某个表对象进行依赖。
提示：索引保存在information_schema数据库里的STATISTICS表中。
创建索引方式：
自动：当表上定义主键约束、唯一、外键约束时，该表会被系统自动添加上索引。
手动：手动在相关表或列上增加索引，提高查询速度。
删除索引方式：
自动：当表对象被删除时，该表上的索引自动被删除
手动：手动删除指定表对象的相关列上的索引
索引类似于书籍的目录，可以快速定位到相关的数据，一个表可以有多个索引。
创建索引：
create
index
idx_temp_name
on
temp(name);
组合索引：
create
index
idx_temp_name$pwd
on
temp(name, pwd);
删除索引：
drop
index
idx_temp_name
on
temp;
Ø 视图
视图就是一个表或多个表的查询结果，它是一张虚拟的表，因为它并不能存储数据。
视图的作用、优点：
限制对数据的访问
让复杂查询变得简单
提供数据的独立性
可以完成对相同数据的不同显示
创建、修改视图
create
or
replace
view
view_temp
as
select
name, age
from
temp;
通常我们并不对视图的数据做修改操作，因为视图是一张虚拟的表，它并不存储实际数据。如果想让视图不被修改，可以用with
check
option来完成限制。
create
or
replace
view
view_temp
as
select
*
from
temp
with
check
option
;
修改视图：
alter
view
view_temp
as
select
id, name
from
temp;
删除视图：
drop
view
view_temp;
显示创建语法：
show
create
view
v_temp;
Ø DML语句
DML主要针对数据库表对象的数据而言的，一般DML完成：
插入新数据
修改已添加的数据
删除不需要的数据
1、 insert
into
插入语句
insert
into
temp
values
(
null
, ‘jack’, 25);
主键自增可以不插入，所以用null代替
指定列
insert
into
temp(name, age)
values
(‘jack’, 22);
在表面后面带括号，括号中写列名，values中写指定列名的值即可。当省略列名就表示插入全部数据，
注意插入值的顺序和列的顺序需要保持一致。
Set方式插入，也可以指定列
insert
into
temp
set
id = 7, name =
'jason'
;
MySQL中外键的table的外键引用列可以插入数据可以为null，不参照主表的数据。
使用子查询插入数据
insert
into
temp(name)
select
name
from
classes;
多行插入
insert
into
temp
values
(
null
, ‘jack’, 22), (
null
, ‘jackson’ 23);
2、
update
修改语句
update主要完成对数据的修改操作，可以修改一条或多条数据。修改多条或指定条件的数据，需要用where条件来完成。
修改所有数据
update
temp
set
name = ‘jack2’;
所有的数据的name会被修改，如果修改多列用“,”分开
update
temp
set
name = ‘jack’, age = 22;
修改指定条件的记录需要用where
update
temp
set
name = ‘jack’
where
age > 22;
3、
delete
删除语句
删除table中的数据，可以删除所有，带条件可以删除指定的记录。
删除所有数据
delete
from
temp;
删除指定条件数据
delete
from
temp
where
age > 20;
Ø select 查询、function 函数
select查询语句用得最广泛、功能也最丰富。可以完成单条记录、多条记录、单表、多表、子查询等。
1、 查询某张表所有数据
select
*
from
temp;
*代表所有列，temp代表表名，不带条件就查询所有数据
2、 查询指定列和条件的数据
select
name, age
from
temp
where
age = 22;
查询name和age这两列，age 等于22的数据。
3、 对查询的数据进行运算操作
select
age + 2, age / 2, age – 2, age * 2
from
temp
where
age – 2 > 22;
4、 concat函数，字符串连接
select
concat(name, ‘-eco’)
from
temp;
concat和null进行连接，会导致连接后的数据成为null
5、
as
对列重命名
select
name
as
‘名称’
from
temp;
as也可以省略不写，效果一样
如果重命名的列名出现特殊字符，如“‘”单引号，那就需要用双引号引在外面
select
name
as
“名’称”
from
temp;
6、 也可以给table去别名
select
t.name Name
from
temp
as
t;
7、 查询常量
类似于SQL Server
select
5 + 2;
select
concat(
'a'
,
'bbb'
);
8、
distinct
去掉重复数据
select
distinct
id
from
temp;
多列将是组合的重复数据
select
distinct
id, age
from
temp;
9、
where
条件查询
大于>、大于等于>=、小于<、小于等于<=、等于=、不等于<>
都可以出现在where语句中
select
*
from
t
where
a > 2
or
a >= 3
or
a < 5
or
a <= 6
or
a = 7
or
a <> 0;
10、
and
并且
select
*
from
temp
where
age > 20
and
name = ‘jack’;
查询名称等于jack并且年龄大于20的
11、
or
或者
满足一个即可
select
*
from
tmep
where
name = ‘jack’
or
name = ‘jackson’;
12、
between
v
and
v2
大于等于v且小于等于v2
select
* form temp
where
age
between
20
and
25;
13、
in
查询
可以多个条件 类似于or
select
*
from
temp
where
id
in
(1, 2, 3);
查询id在括号中出现的数据
14、
like
模糊查询
查询name以j开头的
select
*
from
temp
where
name
like
‘j%’;
查询name包含k的
select
*
from
temp
where
name
like
‘%k%’;
escape转义
select
*
from
temp
where
name
like
‘\_%’
escape
‘\’;
指定\为转义字符，上面的就可以查询name中包含“_”的数据
15、
is
null
、
is
not
null
查询为null的数据
select
*
from
temp
where
name
is
null
;
查询不为null的数据
select
*
from
temp
where
name
is
not
null
;
16、
not
select
*
from
temp
where
not
(age > 20);
取小于等于20的数据
select
*
from
temp
where
id
not
in
(1, 2);
17、
order
by
排序，有desc、asc升序、降序
select
*
from
temp
order
by
id;
默认desc排序
select
*
from
temp
order
by
id
asc
;
多列组合
select
*
from
temp
order
by
id, age;
