SQL基础语句

MySQL数据库的一个实例（Server Instance）可以同时包含多个数据库，MySQL使用如下命令查看当前实例下包含多少个数据库：

show databases;

创建新的数据库：

create database [IF NOT EXISTS] 数据库名;

删除指定数据库：

drop database 数据库名;

进入指定数据库：

use 数据库名；

查询该数据库下包含多少个数据表：

show tables;

查看指定数据表的表结构（查看该表有多少列，每列的数据类型等信息）：

desc 表名;

启动MySQL命令行客户端。可连接远程主机的MySQL服务（为了保证较好的安全性，执行该命令时可省略-p后面的密码，执行该命令后系统会提示输入密码）：

mysql -p 密码 -u 用户名 -h 主机名 --default-character-set=utf8

如果需要在建表时显示指定存储机制，则可在标准建表语法的后面添加下面任意一句：

ENGINE=MyISAM--强制使用MyISAM存储机制

ENGINE=InnoDB--强制使用InnoDB存储机制

---

DDL（数据定义语言）语句：主要由create、drop、alter和truncate四个关键字完成

建表示例：

create table test

(

      #整型通用int

     test_id int,

     #小数点数

     test_price decimal,

     #普通长度文本，使用default指定默认值

     test_name varchar(255) default 'xxx',

     #大文本类型

     test_desc text,

     #图片

     test_img blob,

     test_date datetime

);

使用子查询建表语句，可在建表时插入数据，子查询建表语句的语法如下：

create table [模式名.]表名 [column[,column...]]

as subquery;

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

#创建hehe数据表，该数据表和user_inf完全相同，数据也完全相同

create table hehe

as

select * from user_inf;

---

修改表结构的语法

修改表结构使用alter table,修改表结构包括增加列定义、修改列定义、删除列、重命名列等操作。

增加列语法如下：

alter table 表名

add(

     #可以有多个列定义

     column_name1 datatype [default expr],

     ...

);

示例：

#为hehe数据表增加一个hehe_id字段，该字段的类型为int

alter table hehe

add hehe_id int;

#为hehe数据表增加aaa、bbb字段，两个字段类型都为varchar(255)

alter table hehe

add

(

     aaa varchar(255) default 'xxx',

     bbb varchar(255)

);

上面第2条SQL语句增加aaa字段时，为该字段指定默认值为‘xxx'。SQL语句中的字符串值不是用双引号引起，而是用单引号引起的。

修改列定义的语法如下：

alter table 表名

modify column_name datatype [default expr] [first|after col_name];

上面语法中first或者after col_name指定需要将目标修改到指定位置.

从上面修改语法中可以看出，该修改语句每次只能修改一个列定义，如下所示：

#将hehe表的hehe_id列修改为varchar(255)类型

alter table hehe

modify hehe_id varchar(255);

#将hehe表的bbb列修改成int类型

alter table hehe

modify bbb int;

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

alter table 表名

drop column_name;

删除列只要在drop后紧跟需要删除的列名即可。例如：

#删除hehe表中的aaa字段

alter table hehe

drop aaa;

除此之外，MySQL还提供了两种特殊的语法：重命名数据表和完全改变列定义。

重命名数据表的语法格式如下：

alter table 表名

rename to 新表名;

如下SQL语句用于将hehe表命名为wawa：

#将hehe数据表重命名为wawa

alter table hehe

rename to wawa;

MySQL为alter table提供了change选项，该选项可以改变列名。change语法如下：

alter table 表名

change old_column_name new_column_name type [default expr] [first|after col_name];

当不需要修改列名时，使用alter table的modify选项即可，只有当需要修改列名时才会使用change选项，如下：

#将wawa数据表的bbb字段重命名为ddd

alter table wawa

change bbb ddd int;

---

删除表的语法

删除表的语法格式如下：

drop table 表名;

删除数据表的效果如下：

• 表结构被删除，表对象不再存在。
• 表里的所有数据也被删除。
• 该表所有相关的索引、约束也被删除。

---

truncate表

truncate的作用时删除该表里的全部数据，但保留表结构。truncate速度比delete要快，但是不可以删除指定的记录，truncate只能一次性删除整个表的全部记录。语法如下：

truncate 表名;

MySQL对truncate的处理比较特殊——如果使用非InnoDB存储机制，truncate比delete速度要快；如果使用InnoDB存储机制，在MySQL5.0.3之前，truncate和delete完全一样，在5.0.3之后，truncate table比delete效率高，但如果该表被外键约束所参照，truncate又变为delete操作，在5.0.3之后，快速truncate总是可用，即比delete性能要好。

---

数据库约束

大部分数据库支持下面5种完整性约束。

• NOT NULL：非空约束，指定某列不能为空。
• UNIQUE：唯一约束，指定某列或者几列组合不能重复。
• PRIMARY KEY：主键，指定该列的值可以唯一地标识该条记录。
• FOREIGN KEY：外键，指定该行记录从属于主表中的一条记录，主要用于保证参照完整性。
• CHECK：检查，指定一个布尔表达式，用于指定对应列的值必须满足该表达式。（MySQL不支持CHECK约束）

根据约束对数据列的限制，约束分为如下两类：

• 单列约束：每个约束只约束一列。
• 多列约束：每个约束可以约束多个数据列。

为数据表指定约束有如下两个时机：

• 建表的同时为相应的数据列指定约束。
• 建表后创建，以修改表的方式来增加约束。

对于大部分数据库而言，删除约束都时在alter table语句后使用“drop constraint 约束名”语法来完成的，但MySQL并不使用这种方式，而是使用“drop index 约束名”的方式来删除约束。例如如下：

#删除unique_test3表上的test3_uk唯一约束

alter table unique_test3

drop index test3_uk;

---

PRIMARY KEY

主键约束相当于非空约束和唯一约束。即主键约束的列即不允许出现重复值，也不允许出现null值；如果对多列组合建立主键约束，则多列里包含的每一列都不能为空，但只要求这些列组合不能重复。主键列的值可用于唯一地标识表中的一条记录。

每个表中最多允许有一个主键，但这个主键约束可由多个数据列组合而成，主键是表中能唯一确定一行记录的字段或字段组合。

建表时创建约束，使用列级约束语法：

create table primary_test

(

     #建立了主键约束

     test_id int primary key,

     test_name varchar(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 primary_test3

drop primary key;

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

#使用表级约束语法增加主键约束

alter table primary_test3

add primary key(test_name,test_pass);

#使用列级约束语法增加主键约束

alter table primary_test3

modify test_name varchar(255) primary key;

指定自增长功能通常用于设置逻辑主键列——该列的值没有任何物理意义，仅仅用于标识每行记录。MySQL使用auto_increment来设置自增长，SQL语句如下：

create table primary_test4

(

     #建立主键约束，使用自增长

     test_id int auto_increment primary key,

     test_name varchar(255),

     test_pass varchar(255)

);

一旦指定了某列具有自增长特性，则向该表插入记录时可不为该列指定值，该列的值由数据库系统自动生成。

---

FOREIGN KEY约束

采用列级约束语法建立外键约束直接使用reference关键字，reference指定该列参照哪个主表，以及参照主表的哪一列。如下：

#为了保证从表参照的主表存在，通常应该先建主表

create table teacher_table

(

     #auto_increment:代表数据库的自动编号策略，通常用作数据表的逻辑主表

     teacher_id int auto_increment,

     teacher_nam varchar(255),

     primary key(teacher_id)

);

create table student_table

(

     #为本表建立主键约束

     student_id int auto_increment primary key,

     student_name varchar(255),     

     #指定Java_teacher参照到teacher_table的teacher_id列

     java_teacher int references teacher_table(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 auto_increment primary key,

     student_name varchar(255),

     #指定java_teacher参照到teacher_table的teacher_id列

     java_teacher int,

     foreign key(java_teacher) references teacher_table(teacher_id)

);

如果需要显式指定外键约束的名字，则可使用constraint来指定名字。如：

constraint student_teacher_fk foreign key(java_teacher) references teacher_table(teacher_id)

如果需要建立多列组合的外键约束，则必须使用表级约束语法。如下：

foreign key(java_teacher_name,jave_teacher_pass) references teacher_table(teacher_name,teacher_pass)

删除外键约束的语法：在alter table 后增加“drop foreign key 约束名”子句即可。如下：

alter table student_table

drop foreign key student_table_ibfk_1;

增加外键约束通常使用add foreign key命令。

alter table student_table

add foreign key(teacher_teacher_name,java_teacher_pass) references teacher_table(teacher_name,teacher_pass);