9.JDBC与MySQL（2）

一、SQL语句基础。

标准的SQL语句通常分为如下几种类型：

-查询语句：主要由select关键字完成。

-DML:（数据操作语言）主要由insert、update和delete三个关键字完成

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

-DCL：(数据控制语言)主要由grant和revoke两个关键字完成。

-事务控制语句：主要由commit、rollback和savepoint三个关键字完成

SQL语句的关键字不区分大小写，也就是说，create和CREATE的作用完全一样。在上面5中SQL语句中，DCL语句用于为数据库用户授权，或者回收指定用户的权限

 

1）创建表的语法

标准的建表语句的语法如下：

create table [模式名.]表名

(

#可以有多个列定义

columnName1 datatype [deffault expr],

...

)

上面语法中圆括号里可以包含多个列定义，每个列定义之间以英文逗号隔开，最后一个列定义不需要使用英文逗号，而是直接以括号结束

例如下面的建表语句：

create table test
(
#整型常用int
test_id int,
#小数点数
test_price decimal,
#普通长度文本，使用default指定默认值
test_name varcahr(255) default 'xxx',
#大文本类型
test_desc text,
#图片
test_img blob,
#时间
test_date datetime
);

上面是比较常见的建表语句，这种建表语句只是创建一个空表，里面没有任何数据，如果使用子查询建表语句，则可以在建表的同时插入数据。子查询建表语法如下：

create table [模式名.] 表名 [column1[,column2...]] as subquery;

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

#创建爱你hehe数据表，该数据表和user_inf完全相同，数据也完全相同
create table hehe
as 
select * from user_inf;

 

MySQL支持的数据类型

 

2）修改表结构的语法

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

增加列定义的语法如下：

alter table 表名
add
(    
     #可以有多个列定义
     column_name1 datatype [default expr] ,
     ...
)

上面的语法格式中圆括号部分与建表语法的圆括号部分完全相同，只是此时圆括号里面的列定义是追加到已有表的列定义后面。还有一点需要指出，如果只是新增一列，则可以省略圆括号，仅在add后面加一个列定义即可。为表追加字段的SQL语句如下：

#为hehe数据表增加一个hehe_id字段，该字段的类型为int
alter table hehe
add hehe_id int;
#为hehe数据表增加aaa、bbb字段，两个字段的类型为varcahr(255)
alter table hehe
add
(
    aaa varchar(255) default 'xxx',
    bbb varchar(255)
);

上面第二条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;

从上面代码中不难看出，使用SQL修改数据表里列定义的语法和为数据表只增加一个列定义的语法几乎完全一样，关键是增加列定义使用add关键字，而修改列定义使用modify关键字。还有一点需要指出，add新增的列名必须是原表中不存在的，而modify修改的列名必须是原表中已存在的。

 

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

alter table 表名
drop column_name



#删除列只要在drop后加上需要删除的列名即可。例如：
#删除hehe表中的aaa字段
alter table hehe drop aaa;

上面介绍的这些增加列、修改列和删除列的语法是标准的SQL语法，对所有的数据库都通用，除此之外，M有SQL还提供了两种特殊的语法：重命名数据表和完全改变列定义。

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

alter table 表名
rename to 新表名


#将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]

#将wawa数据表的bbb字段重命名为ddd
alter table wawa change bbb ddd int;

3).删除表的语法

删除表的语法格式如下：

drop table 表名;

如下SQL语句将会把数据库中已有的wawa数据表删除：

#删除数据表
drop table wawa;

删除数据表的效果如下：

表结构被删除，表对象不再存在。

表里的所有数据也被删除。

该表的所有相关的索引、约束也被删除。

4）truncate表

对于大部分数据库而言，truncate都被当成DDL处理，truncate被称为"截断"某个表——它的作用就是删除该表里的所有数据，但保留表结构。相对于DML里的delete而言，truncate的速度要快得多，另外truncate不像delete可以删除指定的记录，truncate只能一次性删除整个表的全部记录。truncate命令的语法如下：

truncate 表名

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

二、数据库约束

前面创建的数据表仅仅指定了一些列定义，这仅仅是数据表的基本功能。除此之外，所有的关系数据库都支持对数据表使用约束，通过约束可以更好地保证数据表里数据的完整性。约束是在表上强制执行的数据校验规则。除此之外，当表中数据存在相互依赖性时，可以保护相关的数据不被删除。

大部分数据库支持以下五种完整性约束：

NOT NULL:非空约束，指定某列不能为空

UNIQUE:唯一约束，指定某列或者几列组合不能重复。

PRIMARY KEY:主键，指定该列的值可以唯一的标识该条记录。

FOREIGN KEY:外键，指定该行记录从属于主表中的一条记录，主要用于保证参照完整性。

CHECK:检查，指定一个布尔表达式，用于指定对应列的值必须满足该表达式

虽然大部分数据库都支持上面5种约束，但MySQL不支持CHECK约束。

 

1）NOT NULL约束

非空约束用户确保指定列不允许为空，非空约束是比较特殊的约束，它只能作为列级约束使用，只能使用列级约束 语法定义。这里要介绍一下SQL中的null值，SQL中的null不区分大小写。SQL中的null具有如下特征：

所有数据类型的值都可以是null，包括int、float、boolean等数据类型。

与Java类似的是，空字符串不等于null，0也不等于null。

如果需要在建表时为指定列指定非空约束，只要在列定义后增加not null即可，建表语句如下：

CREATE TABLE HEHE
(
    #建立了非空约束，这意味着hehe_id不可以为null
    hehe_id INT NOT NULL,
    #MySQL的非空约束不能指定名字
    hehe_name varchar(255) default 'xyz' not null,
    #下面列可以为空，默认就是可以为空
    hehe_gender varchar(2) null
);

除此之外，也可以在使用alter table修改表时增加或者删除非空约束，SQL命令如下：

 

#增加非空约束
alter table hehe
modify hehe_gender varchar(255) not null
#取消非空约束
alter table hehe modify hehe_name varchar(255) null;
#取消非空约束，并制定默认值
alter table hehe 
modify hehe_name varchar(255) default 'abc' null;

 

2)UNIQUE约束

唯一约束用于保证指定列或者制定列组合不允许出现重复值，虽然唯一约束的列不可以出现重复值，但可以出现多个null值（因为在数据库中null不等于null）。

同一个表内可以建立多个唯一约束，唯一约束也可以由多列组合而成，当为某列创建唯一约束时，MySQL会为该列相应的创建唯一索引。如果不给唯一约束起名，该唯一约束默认与列名相同。

唯一约束既可以使用列级约束语法建立，也可以使用表级约束语法建立。入股哦需要为多列建组合约束，或者需要为唯一约束指定约束名，则只能用表级约束语法。

当建立唯一约束时，MySQL在唯一约束所在列或列组合上建立对应的唯一索引。

使用列级约束建立唯一约束非常简单，只要简单地在列定义后面增加unique关键字即可。SQL语句如下：

#建表时创建唯一约束，使用列级约束语法建立约束
create table unique_test
(
     #建立了非空约束，这意味着test_id不可以为null
     test_id int not null,
     #unique就是唯一约束，使用列级约束语法建立唯一约束
     test_name varchar(255) unique
);

如果需要为多列组合建立唯一约束，或者想自行指定约束名，则需要使用表级约束语法，表级约束语法格式如下：

[constraint 约束名] 约束定义

 

沙面的标记约束语法格式即可放在create table语句中与列定义并列，也可以放在alter table语句中使用add关键字来添加约束。SQL语句如下：

#建表时创建唯一约束，使用表级约束语法建立约束
create table unique_test2
(
#建立了非空约束，这意味着test_id不可以为null
test_id not null,
test_name varchar(255),
test_pass varchar(255),
#使用表级约束语法建立唯一约束
unique (test_name),
#使用表级约束语法建立唯一约束，而且指定约束名
constraint test2_uk unique(test_pass)
);

上面的建表语句为test_name、test_pass分别建立了唯一约束，这意味着这两列都不能出现重复值，除此之外，还可以为这两列组合建立唯一约束，SQL语句如下：

#建表时创建唯一约束，使用表级约束语法建立约束
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和unique_test3两张表，都是对test_name、test_pass建立唯一约束，其中unique_test2要求test_name、test_pass都不能出现重复值，而unique_test3只要求test_name、test_pass两列值的组合不能重复。

 

也可以在修改表结构时使用add关键字来增加唯一约束，SQL语句如下：

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

还可以在修改表时使用modify关键字，为单列采用列级约束语法来增加唯一约束，代码如下：

#为unique test3表的test_name列增加唯一约束
alter table unique_test3
modify test_name varchar(255) unique;

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

#删除unique_test3表上的test3_uk唯一约束
alter table unique_test3
drop index test3_uk;

3)PRIMARY KEY约束

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

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

建立主键约束时即可使用列级约束语法，也可以使用表级约束语法。如果需要对多个额字段建立组合主键，则只能使用表级约束语法。使用表级约束语法来建立约束时，可以为该约束指定约束名。但不管用户是否为该主键约束指定约束名，MySQL总是将所有的主键约束命名为PRIMARY。

 

当创建主键约束时，MySQL在主键约束所在列或列组合上建立对应的唯一索引。

创建主键约束的语法和创建唯一约束的语法非常像，一样允许使用列级约束语法为单独的数据列创建主键，如果需要为多列组合建立主键约束或者需要为主键约束命名，则应该使用表级约束语法来建立主键约束。与建立唯一约束不同的是，建立主键约束使用primary key。

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

creaete 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语句后使用drop primary key 子句即可。SQL语句如下：

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

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

#使用表级约束来增加主键约束
alter table primary_test3
add primary key(test_name,test_pass);

如果只是为单独的数据列增加主键约束，则可使用modify修改列定义来实现，如下SQL语句所示：

#使用列级约束语法来增加主键约束
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)
);

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

 

4)FOREIGN KEY约束

外键约束主要用于保证一个或两个数据表之间的参照完整性，外键是构建于一个表的两个字段或者两个表的两个字段之间的参照关系。外键确保了相关的两个字段的参照关系：从表外键列的值必须在主表被参照列的值范围之内，或者为空。

当主表的记录被从表记录参照时，主表记录不允许被删除，必须先把从表里参照该记录的所有记录全部删除以后，才可以删除主表的该记录。还有一种方式，删除主表记录时级联删除从表中所有参照该记录的从表记录。

从表外键参照的只能是主表主键列或者唯一键列，这样才可以保证从表记录可以准确定位到被参照的主表记录。同一个表内可以拥有多个外键。

建立外键约束时，MySQL也会为该列建立索引。

外键约束通常用于定义两个实体之间的一对多、一对一的关联关系。对于一对多的关联关系，通常在多的一端增加外键列，例如老师——学生（假设一个老师对应多个学生，但每个学生只有一个老师，这是典型的一对多的关联关系）。为了建立他们之间的关联关系，则可在学生表中增加一个外键列，该列中保存此条学生记录对应老师的主键，对于一对一的关联关系，则可选择任意一方来增加外键列，增加外键列的表被称为从表，只要为外键列增加唯一约束就可以表示一对一的关联关系了。对于多对多的关联关系，则需要额外增加一个连接表来记录它们的关联关系。

建立外键约束同样可以采用列级约束语法和表级约束语法。如果仅对单独的数据列建立外键约束，则使用列级约束语法即可；如果需要对多列组合创建外键约束，或者需要为外键约束指定名字，则必须使用表级约束语法。

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

#为了保证从表参照的主表存在，通常应该先建主表
create table teacher_table
(
#auto_increment:代表数据库的自动编号策略，通常用作数据表的逻辑主键
teacher_id int auto_increment,
teacher_name varchar(255),
primary key(teacher_id)
);

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

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_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_table1的teacher_id列
java_teacher int,
foreign key(java_teacher) references teacher_table1(teacher_id)
);

如果使用表级语法约束语法，则需要使用foreign key来指定本表的外键列，并使用references来指定参照哪个主表，以及参照到主表的哪个数据列，使用表级约束语法可以为外键约束指定约束名，如果创建外键约束没有指定约束名，则MySQL会为该外键约束命名为table_name_ibfk_n,其中table_name是从该表的表名，而n是从1开始的整数。

如果需要显示指定外键约束的名字，则可使用constraint来指定名字。如下SQL语句所示：

#为了保证从表参照的主表存在,通常应该先建主表
create table teacher_table2
(
teacher_id int auto_increment,
teacher_name varchar(255),
primary key(teacher_id)
);

create table student_table2
(
student_id int auto_increment primary key,
student_name varchar(255),
java_teacher int,
#使用表级约束语法建立外键约束，指定外键约束的约束名为student_teacher_fk
constraint student_teacher_fk foreign key(java_teacher) references
teacher_table2(teacher_id)
);

 

删除外键约束的语法很简单，在alter table后增加"drop foreign key 约束名"子句即可，如下代码所示：

#删除student_table2表上名为student_table_fk的外键约束
alter table student_table2
drop foreign key student_teacher_fk;

增加外键约束通常使用add foreign key命令，如下SQL语句所示：

#修改student_table2数据表，增加外键约束
alter table student_table2
add foreign key(java_teacher) references teacher_table2(teacher_id);

值得指出的是，外键约束不仅可以参照其他表，而且还可以参照自身，这种参照自身的情况通常被称为自关联。例如，使用一个表保存某个公司的所有员工记录，员工之间有部门经理和普通员工之分，部门经理和普通员工之间存在一对多的关联关系，但他们都是保存在同一个数据表里的记录，这就是典型的自关联。下面的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。如下SQL语句所示:

create table teacher_table4
(
teacher_id int auto_increment,
teacher_name varchar(255),
primary key(teacher_id)
);

create table student_table4
(
student_id int auto_increment primary key,
student_name varchar(255),
java_teacher int,
foreign key(java_teacher) references teacher_table4(teacher_id) on delete cascade #也可以用on delete set null
);

 三、索引

索引是存放在模式中的一个数据库对象，虽然索引总是从属于数据表，但它也和数据表一样属于数据库对象。创建索引的唯一作用就是加速对表的查询，索引通过使用快速路径访问方法来快速定位数据。

语法：

create index index_name on table_name (column[,column]...);

drop index 索引名 on 表名

 

四、DML语句语法

1）insert into语句

两种用法

a>insert into 表名[(column1...)] values(column1...);

insert into student_table2 values(null,'kobe',1);

b>根据子查询插入数据

insert into student_table2(student_name) select teacher_name from teacher_table2;

2)update语句

update用于修改数据表的记录，每次可以修改多条记录，可以通过where子句限定修改哪些记录。语法格式如下：

update table_name set column = value1[,column2=value2]...[where condition];

下面SQL语句会把teacher_table2表中的所有记录的teacher_name列的值改成'WuKong'.

update teacher_table2 set teacher_name = 'WuKong';

也可以通过添加where条件来指定只修改特定记录，如下SQL语句所示：

#只修改teacher_id大于1的记录
update teacher_table2 set teacher_name = 'BaJie' where teacher_id > 1;

3)delete from 语句

delete from语句用于删除指定数据表的记录。使用delete from 语句删除时不需要指定列名，以为总是整行的删除。

使用delete from语句可以依次删除多行，删除哪些行采用where子句限定，只删除满足where条件的记录，没有where子句限定将会把表里的全部记录删除。

delete from语句的语法格式如下：

delete from table_name [WHERE condition];

如下SQL语句将会把student_table2表中的记录全部删除：

delete from student_table2;

也可以使用where条件来限定只删除指定记录，如下SQL语句所示：

delete from teacher_table2 where teacher_id > 2;

 

单表查询

下面的SQL语句将会选择出teacher_table表中的所有行、所有列的数据

select * from teacher_table2;

像这样的from后只跟了一张表的查询语句，就是单表查询。

当使用select语句进行查询时，还可以在select语句中使用算术运算符(+-*/)，从而形成算术表达式。

 

MySQL中没有提供字符串连接运算符，即无法使用加号将字符串常量、字符串变量或字符串列连接起来。MySQL使用concat函数进行字符串连接运算。如下SQL语句所示：

#选择出teacher_name和'new'字符串连接后的结果
select concat('new',teacher_name) from teacher_table2;

对于MySQL而言，如果在算术表达式中使用null，将会导致整个算术表达式的返回值为null；如果在字符串连接运算中出现null，将会导致连接后的结果也是null。如下SQL语句将会返回null。

select concat(teacher_name,null) from teacher_table2;

 

如果不希望直接使用列名作为列标题，则可以为数据列或表达式起一个别名，为数据列或者表达式起别名时，别名紧跟数据列，中间以空格隔开，或者使用as关键字隔开。如下SQL语句所示：

select teacher_id + 5 as MY_ID from teacher_table2;

不仅可以为列取别名，也可以为表起别名，为表起别名的语法和为列或表达式取别名的语法相同。

 

select 默认会把所有符合条件的记录全部选出来，即使两行记录完全一样。如果想去除重复行，则可以使用distinct关键字从查询结果中清楚重复行。比较下面两条SQL语句的执行结果:

#选出所有记录，包括重复行
select student_name,java_teacher from student_table2;

#去除重复行
select distinct student_name,java_teacher from student_table2;

 

SQL中的比较运算符不仅可以比较数值之间的大小，也可以比较字符串、日期之间的大小。

除此之外，SQL还支持如下的特殊的比较运算符。

下面的SQL语句选出student_id大于等于2，且小于等于4的所有记录

select * from student_table where student_id between 2 and 4;

使用between val1 and val2必须保证val1小于val2，否则将选不出任何记录。除此之外，between val1 and val2中的两个值不仅可以是常量，也可以是变量，或者是列名也行。如下SQL语句选出java_teacher小于小于等于2，且student_id大于等于2的所有记录。

select * fro student_table where 2 between  java_teacher and student_id;

 使用in比较运算符时，必须在in后的括号里列出一个或多个值，如果指定列与in括号里任意一个值相等，则会被查询出来。如下所示：

#选出student_id为2或4的所有记录
select * from student_table where student_id in (2,4);

另外要注意的一点，in括号里的值既可以是常量，也可以是变量或者列名，如下SQL语句所示：

#选出student_id或java_teacher列值为2的所有记录
select * from student_table where 2 in(student_id,java_teacher);

 

like运算符主要用于进行模糊查询，例如，若要查询名字以"孙"开头的所有记录，这就需要用到模糊查询，在模糊查询中需要用到like关键字。SQL语句中可以使用两个通配符：下划线"_"和百分号"%"，其中下划线可以代表一个任意的字符，百分号可以代表任意多个字符。如下SQL语句将查询出所有学生中以“孙”开头的学生。

select * from student_table where student_name like '孙%';

在某些情况下，查询的条件里需要使用下划线或百分号，不希望SQL把下划线和百分号当成通配符使用，这就需要使用转义字符，MySQL使用反斜线"\"作为转义字符。

 

is null用于判断某些值是否为空，判断是否为空不要用"=null"来判断，因为SQL中null=null返回null。如下SQL语句将选择出student_table表中student_name为null的所有记录。

如果where子句后有多个条件需要组合，SQL提供了and和or逻辑运算符来组合两个条件，并提供了not来对逻辑表达式求否。如下SQL语句将选出学生名字为2个字符，且student_id大于的所有记录。

select * from student_table
where student_name like '__' and student_id > 3;

 

 

执行查询后的查询结果默认按插入顺序排列，如果需要将查询结果按某列值大小进行排序，则可以使用order by子句。order by子句的语法格式如下:

order by column_name1 [desc],column_name2...

进行排序时默认按升序排列，如果强制按降序排列，则需要在列后使用desc关键字，设定排序列时可以采用列名、列序号和列别名，如下SQL语句选出student_table表中的所有记录，选出以后按java_teacher列的升序排列

select * from student_table
order by java_teacher desc,student_name;

如果需要按多列排序，则每列的asc、desc必须单独设定，如果指定了多个排序列，则第一个拍序列是首要排序列，只有当第一列中存在多个相同的值时，第二个排序列才会起作用。如下SQL语句先按java_teacher列的降序排列，当java_teacher列的值相同时按student_name列的升序排列。

select * from student_table
order by java_teacher desc,student_name

 

分组函数（聚合函数）

分组函数将一组记录作为整体计算，每组记录返回一个结果，常见的分组函数有如下几个：

avg(expr)：计算多行expr的平均值，其中expr可以是变量、常量或数据列，但其数据类型必须是数值型。还可以在变量、列前使用distinct或all关键字，如果使用distinct，则表明不计算重复值，all表明需要计算重复值，用不用效果一样。

count(expr):计算多行expr的总条数，其中,expr可以是变量、常量或数据列，其数据类型可以是任意类型；参数如果是星号表示统计该表内的记录行数。

max(expr):计算多行expr的最大值，其中expr可以是变量、常量或数据列，其数据类型可以是任意类型。

min(expr)：计算多行expr的最小值，其中expr可以是变量、常量或数据列，其数据类型可以是任意类型。

sum(expr):计算多行expr的总和，其中，expr可以是变量、常量或数据列，但其数据类型必须是数值型。

 

对于很多数据库而言，分组时有严格的规则，如果查询列表中使用了分组函数，或者select语句中使用了group by分组子句，则要求出现在select列表中的字段，要么使用分组函数包起来，要么必须出现在group by子句中。

 

如果需要对分组进行过滤，则应该使用having子句，having子句的后面也是一个条件表达式，只有满足该条件表达式的分组才会被选出来，having子句和where子句非常容易混淆，它们都具有过滤功能，但有如下区别：

-不能再where子句中过滤组，where子句仅用于过滤行。过滤组必须使用having子句。

-不能再where子句中使用分组函数，having子句才可以使用分组函数

如下SQL语句所示：

select * from student_table2 group by java_teacher
#对数组进行过滤,将查询按教师id分组，并查询出对应教师数量超过2的信息
having count(*) > 2;

 多表连接查询

很多时候，需要选择的数据并不是来自一个表，而是来自多个数据表，这就需要使用多表连接查询，例如，对于上面的student_table2和teacher_table2两张表，如果希望查询所有学生一级他的老师名字，这就需要从两个表中取数据。

1）交叉连接

把多张表都放在from之后，表之间用逗号隔开，没有任何连接条件，即广义笛卡尔积。语法格式如下：

select column1,column2...
from table1 cross join table2

2）自然连接

自然连接会以两个表中的同名列作为连接条件；如果两个表中没有同名列，则自然连接与交叉连接效果完全一样。

如下SQL语句查询出所有学生的资料以及对应的老师姓名。

select s.*,teacher_name
#指定多张表，并指定表别名
from student_table s natural join teacher_table t

如果还需要对记录进行过滤，则将过滤条件和连接条件用and连接起来，如下SQL语句所示：

select s.*,teacher_name
from student_table2 s,teacher_table2 t
where java_teacher = teacher_id and student_name is not null;

3)using子句连接：using子句可以指定一列或多列，用于显示指定两个表中的同名列作为连接条件。假设两个表中有超过一列的同名列，如果使用natural join，则会把所有的同名列当成连接条件；使用using子句，就可以显式指定使用哪些同名列作为连接条件。如下SQL语句所示：

select s.*,teacher_name from student_table2 s join teacher_table2 t  using(teacher_id);

如果使用using子句来指定连接条件，则两个表中必须有同名列，否则就会出现错误。

4)on子句连接：这是最常用的连接方式，连接条件放在on子句中指定，而且每个on子句只指定一个连接条件，这意味着：如果需要进行N表连接，则需要有N-1个join...on对。

如下SQL语句所示：

select s.*,teacher_name from student_table2 s join teacher_table2 t
on s.teacher_id = t.teacher_id;

5)左、右外连接：这三种连接分别使用left[outer] join,right [outer] join,这三种外连接的连接条件一样通过on子句来指定，既可以是等值连接条件，也可以是非等值连接条件。

左外连接会把左边表中所有记录全部列出，右边表中没有记录的部分用null补全，右外连接相反。

子查询

子查询就是指在查询语句中嵌套另一个查询，子查询可以支持多层嵌套。对于一个普通的查询语句而言，子查询可以出现在两个位置。

-出现在from语句后被当成数据表，这种用法也被称为行内视图，因为该子查询的实质就是一个临时视图

-出现在where条件后作为过滤条件的值

使用子查询要注意如下几点：

-子查询要用括号括起来

-把子查询当数据表时，可以为该子查询起别名，尤其是作为前缀来限定数据列时，必须给子查询起别名

-把子查询当成过滤条件时，将子查询放在比较运算符的右边，这样可以增强查询的可读性

-把子查询当成过滤条件时，单行子查询使用单行运算符，多行子查询使用多行运算符。