MYSQL数据库
MySQL基本语句
MySQL数据库的一个实例(Server Instance)可以同时句含多个数据库，MySQL使用如下命令查看当前实例下包含多少个数据库:
如果用户需要创建新的数据库，则可以使用如下命令:
create database [IF NOT EXISTS]数据库名;
如果用户需要删除指定数据库，则可以使用如下命令:
建立了数据库之后，如果想操作该数据库(例如为该数据库建表，在该数据库中执行查询等操作) 则需要进入该数据库。进入指定数据库可以使用如下命令:
进入指定数据库后，如果需要查询该数据库下包含多少个数据表，则可以使用如下命令:
如果想查看指定数据表的表结构(查看该表有多少列，每列的数据米刑等信息)，则可以使用如下命令:
MySQL 数据库安装成功后，在其安装目录下有一个bin路径，该路径下包含一个mysql命令，该命今用于启动MySQL命令行客户端。执行mysql命令的语法如下：
mysql -p密码 -u 用户名 h 主机名 --default-character-set=utf-8
例：
DDL 语句
1.创建表的语法
标准的建表语句的语法如下:
create table[模式名.]表名
（
#可以有多个列定义
-columnName1 datatype [default expr],
...
例如下面的建表语句:
create table test(
test_id int,
test_price decimal,
test_name varchar(255) default 'xxx',
test_desc text,
test_img blob,
test_date datetime
);
如果使用子查询建表语句，则可以在建表的同时插入数据。子查询建表语句的语法如下:
create table[模式名.]表名 [column[，column...]]
as subquery;
下面语句使用子查询来建表。
#创建hehe数据表，该数据表和user_inf完全相同，数据也完全相同
create table hehe
as
select * from user inf;
2.修改表结构的语法
①增加列定义的语法如下:
alter table 表名
add（
#可以有多个列定义
column_namel datatype [default expr],
...
);
为数据表增加字段的SQL语句如下:
# 为hehe数据表增加一个heheid字段，该字段的类型为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)
);
②修改列定义的语法如下:
alter table 表名
modify column_name datatype [default expr] [first|after col_name];
上面语法中first或者after col_name指定需要将目标修改到指定位置。
从上面修改语法中可以看出，该修改语句每次只能修改一个列完义，代码加下
# 将hehe表的hehe id列修改成varchar(255)出开
alter table hehe
modifty hehe id varchar (255);
将hehe表的bbb列修改成1nt类型
alter table hehe
modify bbb int;
③从数据表中删除列的语法比较简单:
删除列只要在drop后紧跟需要删除的列名即可。例如:
# 删除hehe表中的aaa字段
alter table hehe
drop aaa;
④重命名数据表的语法格式如下:
如下SQL语句用于将hehe表命名为wawa:
# 将hehe数据表重命名为wawa
alter table hehe
rename to
MySQL 为alter table提供了change选项，该选项可以改变列名。change选项的语法如下
alter table表名
chanae old column_name new_column name type [default expr] [firstiafter col name]
对比chane和modify两个选项，不难发现:change选项比modify选项书了一个列名，因为change选项可以改变列名，所以它需要两个列名。一般而言，如果不需要改变列名，使用alter table 的modify选项即可，只有当需要修改列名时才会使用change选项。语句如下:
# 将wawa数据表的bbb字段重命名为ddd
alter table wawa
change bbb ddd int;
3.删除表的语法
删除表的语法格式如下:
如下SQL语句将会把数据库中已有的wawa数据表删除:
。
4. truncate表
对于大部分数据库而言，truncate都被当成DDL处理，truncate被称为“截断”某个表一一它的作用是删除该表里的全部数据，但保留表结构。相对于DML里的delete命令而言，truncate的速度要快得多，而且truncate不像delete 可以删除指定的记录，truncate只能一次性删除整个表的全部记录trucate 命令的语法如下:
5 数据库约束
1.NOT NULL 约束
非空约束用于确保指定列不允许为空，非空约束是比较特殊的约束，它只能作为列级约束使用，只能使用列级约束语法定义。
这里介绍一下SQL中的null值，SQL中的null不区分大小写。
2. UNIQUE 约束
唯一约束用于保证指定列或指定列组合不允许出现重复值。虽然唯一约束的列不可以出现重复值，但可以出现多个null值(因为在数据库中null不等于null)
3. PRIMARY KEY 约束
建表时创建主键约束，使用列级约束语法：
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语句后使用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;
自增长
SQL语句如下:
create table primary_test4
(
# 建立主键约束，使用自增长
test_id int auto_increment primary key,
test_name varchar(255),
test_pass varchar(255)
);
4. FOREIGN KEY 约束
SQL语句如下:
# 为了保证从表参照的主表存在，通常应该先建主表
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,
student_name varchar(255),
# 指定 java_teachet 参照到 teacher_table的teacher_id列
java_teachet int references teacher_table(teacher_id)
);
如果要使MySQL中的外键约束生效，则应使用表级约束语法。
# 为了保证从表参照的主表存在，通常应该先建主表
create table teacher_table1
(
# auto_increment;代表数据库的自动编号策略、通常用作数据表的逻辑主键
teacher_id int autoincrement,
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来指定名字。SOL语句如下:
# 为了保证从表参照的主表存在，通常应该先建主表
create table teacher_table2
(
# auto_increment:代表数据库的自动编号策略，通常用作数据表的逻辑主键
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)
);
如果需要建立多列组合的外键约束，则必须使用表级约束语法，SQL语句如下
# 为了保证从表参照的主表存在，通常应该先建主表
create table teacher_table3
（
teacher_name varchar(255),
teacher_pass varchar(255),
# 以两列建立组合主键
primary key(teacher_name, teacher_pass)
);
create table student_table3
# 为本表建立主键约束
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_table3(teacher name, teacher_pass)
);
删除外键约束的语法很简单，在alter table后增加“drop foreign key约束名”子句即可。代码如下
# 删除student_table3表上名为studenttable3_ibfk_1的外键约束
alter table student_table3
drop foreign key
增加外键约束通常使用add foreign key命令。SQL语句如下:
# 修改student table3数据表，增加外键约束
alter table student_ table3
add foreign key(java_teacher_name, java_teacher_pass)
references teacher_table3(teacher_ name, teacher_pass);
自关联
# 使用表级约束语法建立外约束键，且直接参照自身
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
# auto increment:代表数据库的自动编号策略，通常用作数据表的逻辑主键
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
);
5. CHECK约束
当前版本的MySQL支持建表时指定CHECK约束,但这个CHECK约束不会有任何作用。建立CHECK约束的语法很简单，只要在建表的列定义后增加check(逻辑表达式》图可。SQL语句如下：
create table check_test
(
emp_id int auto_increment,
emp_name varchar(255),
emp salary decimal,
primary key(emp_id),
# 建立CHECK约束
check (emp_salary>0)
);
虽然上面的SQL语句建立的check_test表中有CHECK约束,CHECK约束要求emp_salary大于0,但这个要求实际上并不会起作用。
6 索引
创建索引的语法格式如下：
create index index_ name
on table_name (column[, column]...);
下面的索引将会提高对employees表基于last_name字段的查询速度。
create index emp_last name idx
on employees (last_name);
也可同时对名列建立索引，SOL语句如下:
# 下面语句为employees 的first_name和last name两列同时建立索引
create index emp_last_name_idx2
on employees (first_name,last_name);
MySQL中删除索引需要指定表，采用如下语法格式:
如下SQL语句删除了employees表上的emp_last name_idx2索引:
drop index emp_last name_idx2
on
7 视图
创建视图的语法如下:
create or replace view 视图名
as
如下SQL语句就创建了一个简单的视图:
create or replace view view_test
as
select teacher_namne, teacher_pass from teacher_table;
通常不推荐直接改变视图的数据,因为视图并不存储数据，它只是相当于一条命名的查询语句而已。为了强制不允许改变视图的数据，MySQL允许在创建视图时使用with check option子句，使用该子句创建的视图不允许修改，如下所示。
create or replace view view_test
as
select teacher_name from teacher_table
#指定不允许修改该视图的数据
with check option;
注意
大部分数据库都采用with check option来强制不允许修改视图的数提，但Oracle 采用 with read only来强制不允许修改视图的数据。
删除视图使用如下语句:
如下SQL语句删除了前面刚刚创建的视图
8 DML 语句语法
1. insert into语句
insert into语句的语法格式如下:
insert into table_name [(column [, column...])]
values(value [, value...]);
执行插入操作时，表名后可以用括号列出所有需要插入值的列名，而values后用括号列出对应需要插入的值。
在表名后使用括号列出所有需要插入值的列:
insert into teacher_table2(teacher_name)
values('xyz');
一次插入多条记录。SQL 语句如下:
insert into teacher_ table2
#同时插入多个值
values (null, "Yeeku"),
(null, "Sharfly");
2. update语句
update语句的语法格式如下:
update table name
set columnl= valuel[, column2 = value2]
[WHERE condition];
下面的SQL语句将会把teacher_table2表中所有记录的teacher name列的值都改为孙悟空。
update teacher table2
set teacher_name = '孙悟空';
也可以通过添加where条件来指定只修改特定记录，SQL语句如下:
并只修改teacher_id大于1的记录
update teacher_ table2
set teacher name = '猪八戒
where teacher_id > 1
3. delete from语句
delcte from语句的语法格式如下:
deiete from table name
[WHERE condition];
如下SQL语句将会把student_table2表中的记录全部删除
delete from student_table2;
也可以使用where条件来限定只删除指定记录，SQL语句如下，
delete from teacher_ table2
where teacher_id > 2;
9 单表查询
单表查询的select语句的语法格式如下:
select columnl, column2...
from 数据源
[where condition]
where条件用于确定选择哪些行，只有满足where条件的记录才会被选择出来;如果没有where条件，则默认选出所有行。如果想选择出所有列，则可使用星号(*)代表所有列。
下面的SQL语句将会选择出 teacher_table表中的所有行、所有列的数据。
select *
from teacher_table;
如果增加where条件，则只选择出符合where条件的记录。如下SQL语句将选择出student _table表中java teacher值大于3的记录的student_name列的值
select student_name
from student_table
where java_teacher > 3;
concat函数
MySQL中没有提供字符串连接运算符，即无法使用加号(+)将字符串常量、字符串变量或字符串列连接起来。MySQL使用concat函数来进行字符串连接运算。
SQL语句如下:
#选择出teacher _name和'xx'字符串连接后的结果
select concat(teacher_name, 'xx')
from teacher table;
对于MySQL而言，如果在算术表达式中使用null，将会导致整个算术表达式的返回值为null;如 果在字符串连接运算中出现null，将会导致连接后的结果也是null。如下SQL语句将会返回null
select concat(teacher name, null)
from teacher table;
对某些数据库而言，如果让字符串和null进行连接运算，它会把null当成空字符串处理。
如果不希望直接使用列名作为列标题，则可以为数据列或表达式起一个别名，为数据列或表达式起别名时，别名紧跟数据列，中间以空格隔开，或者使用as关键字隔开。SQL语句如下:
select teacher_id + 5 as MY_ID
from teacher table;
为列起别名，可以改变列的标题头，用于表示计算结果的具体含义。如果列别名中使用特殊字符(例如空格)，或者需要强制大小写敏感，都可以通过为别名添加双引号来实现。SQL语句如下:
#可以为选出的列起别名，别名中包括单引号字符，所以把别名用双引号引起来
select teacher id + 5 as "MY'id"
from teacher table;
如果需要选择多列，并为多列起别名，则列与列之间以逗号隔开，但列和列别名之间以空格隔开。SQL语句如下:
select teacher_id + 5 My_ID, teacher_name 老师名
from teacher_table;
不仅可以为列或表达式起别名，也可以为表起别名，为表起别名的语法和为列或表达式起别名的语 法完全一样，SQL语句如下:
select teacher_id + 5 MY_ID, teacher_name老师名
# 为teacher_table起别名 t
from teacher_table t;
前面已经提到，列名可以当成变量处理，所以运算符也可以在名列之间进行运算，SOL语句如下
select teadher_id + s MY_ID, concat ( teacher_name, teacher_id ) teacher_name
from teacher_table
where teacher_id * 2 > 3;
甚至可以在 select、where子句中都不出现列名，SQL语句加下
select 5+4
from teacher table
where 2<9;
这种情况比较特殊:where语句后的条件表达式总是true，所有会把teacher table表中的每条记录都选择出来——但SOL语句没有选择任何列，仅仅选择了一个常量，所以SOL会把该常量当成一列，teacher_table表中有多少条记录，该常量就出现多少次。
对于选择常量的情形，指定数据表可能没有太大的意义，所以MySQL提供了一种扩展语法,允许select语句后没有from子句，即可写成如下形式:
上面这种语句并不是标准SQL语句。例如，Oracle就提供了一个名为dual的虚表(最新的MySQL数据库也支持dual虚表)，它没有任何意义，仅仅相当于from后的占位符。如果选择常量，则可使用如下语句:
select默认会把所有符合条件的记录全部选出来，即使两行记录完全一样。如果想去除重复行，则可以使用distinct关键字从查询结果中清除重复行。比较下面两条SQL语句的执行结果:
#选出所有记录，包括重复行
select student_name,java_teacher
from student_table;
#去除重复行
select distinct student_name,java_teacher
from student_table;
注意:
使用distinct去除重复行时，distinct紧跟select关键字。它的作用是去除后面字段组 合的重复值，而不管对应记录在数据库里是否重复。例如，(1,‘a’,‘b’)和(2,‘a’,‘b’)两条记录在数据库里是不重复的，但如果仅选择后面两列，则distinct会认为两条记录重复。
前面已经看到了where子句的作用——可以控制只选择指定的行。因为where子句里包含的是一个条件表达式，所以可以使用>、>=、<、<=、=和<>等基本的比较运算符。SQL中的比较运算符不仅可以比较数值之间的大小，也可以比较字符串、日期之间的大小。
注意：
SQL中判断两个值是否相等的比较运算符是单等号，判断不相等的运算符是<>;SQL 中的赋值运算符不是等号，而是冒号等号(:=)
除此之外，SQL还支持如表13.3所示的特殊的比较运算符
between
下面的SQL语句选出student id大于等于2，且小于等于4的所有记录。
select * from student table
where student_id between 2 and 4;
使用between vall and val2必须保证vall小于val2,否则将选不出任何记录。除此之外,between val andvaD中的两个值不仅可以是常量,也可以是变量,或者是列名也行。如下SOL语句选出java_teache 小于等于2，student_id大于等于2的所有记录。
select * from student_table
where 2 between java_teacher and student_id;
in
使用in比较运算符时，必须在in后的括号里列出一个或多个值，它要求指定列必须与in括号里任意一个值相等。SQL语句如下:
#选出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运算符主要用于进行模糊查询，例如，若要查询名字以“张”开头的所有记录，这就需要用到模糊查询，在模糊查询中需要使用like关键字。
通配符
在SQL语句中可以使用两个通配符:下画线(_)和百分号(%)，其中下画线可以代表任意一个字符，百分号可以代表任意多个字符。如下SQL语句将查询 出所有学生中名字以“张”开头的学生。
select * from student table
where student name like'张%';
下面的SQL语句将查询出名字为两个字符的所有学生。
select * from student table
#下面使用两个下画线代表两个字符
where student name like '__';
转义字符
在某些特殊的情况下，查询的条件里需要使用下画线或百分号，不希望SOL把下画线和百分号当 成通配符使用，这就需要使用转义字符，MySQL使用反斜线()作为转义字符，SQL语句如下: #选出所有名字以下画线开头的学生
select * from student table
where studentname like '\_%';
标准SQL语句并没有提供反斜线()的转义字符，而是使用escape关键字显式进行转义。例如 ,为了实现上面功能需要使用如下SQL语句:
#在标准的SQL中选出所有名字以下画线开头的学生
select * from student_table
where student_name like '\_%s' escape'\';
is null用于判断某些值是否为空,判断是否为空不要用=null来判断,因为SOL中null=null返回null。如下SOL语句将选择出 student_table表中student_name为null的所有记录。
select * from student_table
where student_name is null;
如果where字句后有多个条件需要组合。SQL提供了add和or逻辑运算符来组合两个条件，并提供了not来对逻辑表达式求否。如下SOL语句将选出学生名字为2个字符，且student_id大于3的所有记录。
select * from student_table
#使用and来组合多个条件
where student_name like '__' and student_id>3;
下面的SQL语句将选出student_table表中姓名不以下画线开头的所有记录
select * from student_table
#使用not对where条件取否
where not student_name like'\_%';
优先级
当使用比较运算符、逻辑运算符来连接表达式时，必须注意这些运算符的优先级。SQL中比较运管符、逻辑运算符的优先级如表13.4所示。
如果SQL代码需要改变优先级的默认顺序，则可以使用括号，括号的优先级比所有的运算符高。如下SQL语句使用括号来改变逻辑运算符的优先级。
select * from student_table
#使用括号强制先计算or运算
where (student_id > 3 or student name> '张')
and java_teacher > 1;
order by子句
执行查询后的查询结果默认按插入顺序排列;如果需要查询结果按某列值的大小进行排序，则可以使用order by子句。order by子句的语法格式如下:
order by column_name1 [desc], column_name2...
进行排序时默认按升序排列，如果强制按降序排列，则需要在列后使用desc关键字(与之对应的是asc关键字，用不用该关键字的效果完全一样，因为默认是按升序排列)。
上面语法中设定排序列时可采用列名、列序号和列别名。如下SQL语句选出student table表中的所有记录，选出后按java_teacher列的升序排列。
select * from student_table
order by java_teacher;
如果需要按多列排序，则每列的asc、desc必须单独设定。如果指定了多个排序列，则第一个排序列是首要排序列，只有当第一列中存在多个相同的值时，第二个排序列才会起作用。如下SQL语句先 按java_teacher列的降序排列，当java_teacher列的值相同时按student_name列的升序排列
select * from student_table
order by java_teacher desc,
10 数据库函数
执行函数的语法如下：
单行函数
下面通过一些例子来介绍MySQL单行函数的用法。
#选出teacher_table表中teacher_name列的字符长度
select char_length (teacher_name)
from teacher table;
#计算teacher_name列的字符长度的sin值
select sin(char_length(teacher_name))
from teacher_table;
#计算1.57的sin值，约等于1
select sin(1.57);
#为指定日期添加一定的时间
#在这种用法下interval是关键字，需娶一个数值，还需要一个单位
SELECT DATE_ADD('1998-01-02',interval 2 MONTH);
#这种用法更简单
select ADDDATE('1998-01-02'，3);
#获取当前日期
select CURDATE();
#获取当前时间
select curtime();
#下面的MD5是MD5加密函数
select MD5('testing');
处理null的函数，
》 ifnul(expr1, expr2):如果cxpr1为null，则返回expr2，否则返回expr1.
》nullif(expr1,expr2):如果erpr1和cxpr2相等，则返回null，否则返回expr1.
》if(expr1,expr2,expr3):有点类似于?:三目运算符，如果expr1为true、不等干0，且不等于null,则返回expr2，否则返回expr3。
isnull(expr1):判断expr1是否为null，如果为null则返回true，否则返回false
#如果student_name列为nul1，则返回'没有名字'
select ifnull(student_name,'没有名字')
from student_table;
#如果student name列等于'张三'，则返回null
selectnulliE(student name,'张三')
from student_table;
如果studen_name列为null，则返回'没有名字'，否则'有名字'
select if(isnull(student name)，'没有名字’,'有名字')
from student_table;
case函数
MySQL还提供了一个case函数，该函数是一个流程控制函数。
case函数有两个用法，case 函数第一个用法的语法格式如下:
case value
uhen compare_value1 then resultl
when compare_value2 then result2
...
else result
end
case函数用value和后面的compare_value1、 compare_value2…依次讲行比较，如果value和指定的compare value1相等，则返回对应的result1，否则返回else后的result。
例如如下SQL语句:
#如果java_teacher为1，则返回'Java老师'，为2返回'Ruby老师"，否则返回'其他老师'
select student_namer case java_teacher
when 1 then 'Java老师"
when 2 then 'Ruby老师
else'其他老师'
end
from student_table;
case函数第二个用法的语法格式如下:
case
when conditionl then result1
when condition2 then result2
...
else result
end
在第二个用法中，condition1、condition2都是一个返回boolean值的条件表达式，因此这种用法更加灵活。
例如如下SQL语句:
#id小于3的为初级班，3~6的为中级班，其他的为高级班
select student_name, case
when student_id <= 3 then '初级班‘
when student id <= 6 then '中级班'
else'高级班'
erd
from student_table;
11 分组和组函数
组函数也就是前而提到的多行函数，组函数将一组记录作为整体计算，每组记录返回一个结果，而不是每条记录返回一个结果。常用的组函数有如下5个。
》avg([distinct|all]exor);计算多行expr的平均值，其中，expr可以是变量、常量或数据列，但其数据类型必须是数值型。还可以在变量、列前使用distinct或all关键字，如果使用distinct，则表明不计算重复值，all用和不用的效果完全一样，表明需要计算重复值。
》count({*|[distinct[alll]expr}):计算多行expr的总条数，其中expr可以是变量、常量或数据列，其数据类型可以是任意类型，用星号(*)表示统计该表内的记录行数;distinct表示不计算重复值。
》max(expr);计算名行expr的最大值，其中expr可以是变量、常量或数据列，其数据类型可以是任意类型。
》min(expr):计算多行expr的最小值，其中expr可以是变量、常量或数据列，其数据类型可以是任意类型。
》 sum([distinct|all]expr):计算多行expr的总和，其中expr可以是变量、常量或数据列，但其数具类型必须是数值型；distinct表示不计算重复值。
#计算student_table表中的记录条数
select count(*)
from student_table;
#计算java_teacher列总共有多少个值
select count(distinct iava_teacher)
from student_table;
#统计所有student_id的总和
select sum(student_id)
from student table;
#计算的结果是20 * 记录的行数
select sum(20)
from student_table;
#选出student_table表中student_id最大的值
select max(student_id)
from student_table;
#选出teacher_table表中teacher_id最小的值
select min (teacher_id)
from teacher_table.
#因为sum里的expr是常量34，所以每行的值都相同
#使用distinct强制不计算重复值，所以下面计算结果为34
select sum(distinct 34)
fron student_table;
#使用count统计记录行数时，null不会被计算在内
select count(student name)
from student_table;
对于可能出现null的列，可以使用ifnull函数来处理该列。
#计算java_teacher列所有记录的平均值
select avg(ifnull(java_teacher, O))
from student_ table;
值得指出的是，distinct和*不同时使用，如下SQL语句有错误。
select count(distinct *)
from student_table;
group by子句
在默认情况下，组函数会把所有记录当成一组，为了对记录进行显式分组，可以在select语句后使用group by子句，group by子句后通常跟一个或多个列名，表明查询结果根据一列或多列进行分组——当一列或多列组合的值完全相同时，系统会把这些记录当成一组。SQL语句如下:
#count(*)将会对每组得到一个结果
select count(*)
from student_table
#将java_teacher列值相同的记录当成一组
group by java_teacher;
如果对多列进行分组，则要求多列的值完全相同才会被当成一组。SQL语句如下:
select count*()
from student table
# 当java_teacher、 student_name两列的值完全相同时才会被当成一组
group by java_teacher, student_name;
对于很多数据库而言，分组计算时有严格的规则——如果查询列表中使用了组函数，或者select语句中使用了group by分组子句，则要求出现在select列表中的字段，要么使用组函粉包起来，要么必须出现在group by子句中。这条规则很容易理解，因为一日使用了组函数或使用了group by子句，都将导致多条记录只有一条输出，系统无法确定输出多条记录中的那一条记录。
对干MySQL来说，并没有上面的规则要求，如果某个数据列既没有出现在group by之后，也没有使用组函数包起来，则MySQL会输出该列的第一条记录的值。图13.15显示了MySQL的处理结果。
如果需要对分组进行过滤，则应该使用having子句，having子句后面也是一个条件表达式，只有满足该条件表达式的分组才会被选出来。having子句和where子句非常容易混淆，它们都有过滤功能，但它们有如下区别。
》不能在where子句中讨滤组，where子句仅用于过滤行。过滤组必须使用having子句。
》不能在where子句中使用组函数，having子句才可使用组函数
SOL语句如下:
select
from student_table
group by java_teacher
#对组进行过滤
having count(*)>2;
12 多表连接查询
很多时候,需要选择的数据并不是来自一个表,而是来自多个数据表,这就需要使用多表连接查询。 例如，对于上面的student_table和teacher_table两个数据表，如果希望查询出所有学生以及他的老师名字，这就需要从两个表中取数据。
多表连接查询有两种规范，较早的SQL 92 规范支持如下几种多表连接查询。
》等值连接。
》非等值连接。
》外连接。
》广义笛卡儿积。
SQL 99 规范提供了可读性更好的多表连接语法，并提供了更多类型的连接查询。SQL 99 支持如下 几种多表连接查询。
》交叉连接。
》自然连接。
》使用using子句的连接。
》使用on子句的连接。
》全外连接或者左、右外连接。
1. SQL 92 的连接查询
SQL 92中多表连接查询的语法格式如下:
select column1, column2 ...
from tablel, table2 ...
[where join_condition]
如下的SQL语句查询出所有学生的资料以及对应老师的姓名。
select s,*, teacher name
并指定多个数据表，并指定表别名
from student table s, teacher table t
样使用where指定连接条件
where s.java teacher m t.teacher id;
执行上面查询语句,将看到如图13.16所示的结果。
上面的查询结果正好满足要求，可以看到每个学生以及他对应的老师的名字。实际上，多表查询的过程可理解成一个嵌套循环，这个嵌套循环的伪码如下:
//依次遍历teacher table表中的每条记录
for t in teacher_table
//遍历student table表中的每条记录
for s in student_table{
//当满足连接条件时，输出两个表连接后的结果
if (s.java_teacher = t.teacher_id)
outputs + t
}
}
}
理解了上面的伪码之后,接下来即可很轻易地理解多表连接查询的运行机制。如果求广义笛卡儿积，则where子句后没有任何连接条件,相当于没有上面的if语句,广义笛卡儿积的结果会有n*m条记录。 只要把where后的连接条件去掉，就可以得到广义笛卡儿积，SQL语句如下:
#不使用连接条件，得到广义笛卡儿积
select s.* ,teacher_name
#指定多个数据表，并指定表别名
from student_table s, teacher_table t;
与此类似的是，非等值连接的执行结果可以使用上面的嵌套循环来计算，SQL语句如下:
select s.*, teacher_name
#指定多个数据表，并指定表别名
from student_table s, teacher_table t
#使用where指定连接条件，非等值连接
where s.java_teacher > t.teacher_id;
上面SQL语句的执行结果相当于if条件换成了s.java teacher > t.teacher id。
如果还需要对记录进行过滤，则将过滤条件和连接条件使用and连接起来，SQL语句如下:
select s.* teacher_name
#指定多个数据表，并指定表别名
from student_table s, teacher_table t
#使用where指定连接条件，并指定student_name列不能为null
where s.java_teacher = t.teacher_id and student_name is not null;
外连接
虽然MySQL不支持SQL 92中的左外连接、右外连接，但本书还是有必要了解一下SQL 92中的左外连接和右外连接。SQL 92中的外连接就是在连接条件的列名后增加括号包起来的外连接符(+或*， 不同的数据库有一定的区别)，当外连接符出现在左边时称为左外连接,出现在右边时则称为右外连接。SQL语句如下:
select s.*, teacher name
from student_table s, teacher_table t
#右外连接
where s.java_teacher = t.teacher_id(*);
外连接就是在外连接符所在的表中增加一个“万能行”，这行记录的所有数据都是null，而且该行可以与另一个表中所有不满足条件的记录进行匹配,通过这种方式就可以把另一个表中的所有记录选来，不管这些记录是否满足连接条件。
自连接
除此之外，还有一种自连接，正如前面介绍外键约束时提到的自关联，如果同一个表中的不同记录之间存在主、外键的束关联，例如把员工、经理保存在同一个表里，则需要使用自连接查询。
注意：
自连接只是连接的一种用法、并不是一种连接类型、不管是SQL 92还是SQL 99都可 以使用自连接查询、自连接的本质就是把一个表当成两个表来用。
下面的SQL句建立了一个自关联的数据表，并向表中插入了4条数据。
create table emp_table
(
emp_id int auto_ineroment primary key,
enp_nane varchar(255),
manager_id int,
foreign key(manager_id) referenees emp_table(emp_id)
);
insert into emp_table
values (null, '唐僧'，null),
(null,'孙悟空'，1)，
(null，'猪八戒'，1)，
(null，'沙憎'，1);
如果需要查询该数据表中的所有员工名，以及每个员工对应的经理名，则必须使用自连接查询。所谓自连接就是把一个表当成两个表来用，这就需要为一个表起两个别名，而且查询中用的所有数据列都要加表别名前缀，因为两个表的数据列完全一样。下面的自连接查询可以查询出所有的员工名，以及对应的经理名。
select emp.emp_id, emp.emp_name员工名，mgr.emp_name经理名
from emp table_emp, emp_table mqr
where emp.manager_id = mgr.emp_id;
2. SQL 99的连接查询
交叉连接(cross join)
SQL语句如下:
select s.*, teacher_name
#SQL 99 多表连接查询的from后只有一个表名
from student_table s
#cross join交叉连接，相当于广义笛卡儿积
cross join teacher_table t;
自然连接(natural join)
SQL语句如下:
select s.*, teacher_name
#SQL 99 多表连接查询的from后只有一个表
from student_table s
#natural join自然连接使用两个表中的同名列作为连接条件
natural join teacher _table t;
using子句连接
SQL语句如下：
elect s.*, teacher_name
#SQL 99 事表连接查询的from后只有一个表名
from student table s
# join连接另一个表
join teacher_table t
using (teacher id);
运行上面语句将出现一个错误,因为student_table表中并不存在名为tcacher_id的列。也就是说，如果使用using子句来指定连接条件，则两个表中必须有同名列，否则就会出现错误。
on子句连接
SQL语句如下:
select s.*, teacher_name
# SQL 99 多表连接查询的from后只有一个表名
from student_table s
# join连接另一个表
join teacher_table t
# 使用on来指定连接条件
on s.java_teacher = t.teacher_id;
使用on子句的连接完全可以代替SQL 92中的等值连接、非等值连接，因为on子句的连接条件除等值条件之外，也可以是非等值条件。如下SQL语句就是SQL 99中的非等值连接。
select s.* ,teacher_name
# SQL 99 多表连接查询的from后只有一个表名
from student_table s
* join 连接另一个表
join teacher_tabie t
# 使用on来指定连接条件:非等值连接
on s.java_teacher > t.teacher_id;
左、右、全外连接
下面使用右外连接，连接条件是非等值连接。
select s.* , teacher_name
# SQL99多表连接查询的from后只有一个表名
from student_table s
# right join右外连接另一个表
right join teacher_table t
# 使用on来指定连接条件，使用非等值连接
on s.java_teacher < t.teacher_id;
下面使用左外连接，连接条件是非等值连接。
select s.*, teacher name
# SQL 99 多表连接查询的from后只有一个表名
from student_table s
# left join左外连接另一个表
left join teacher_table t
# 使用on来指定连接条件，使用非等值连接
on s.java_teacher > t.teacher_id;
下面的SQL语句使用全外连接，连接条件是等值连接。
select s.*, teacher_name
# SQL 99 多表连接查询的from后只有一个表名
from student_table s
# full join全外连接另一个表
full join teacher_table t
# 使用on来指定连接条件，使用等值连接
on s.java.teacher = t.teacher_id;
SQL 99 的全外连接将会把两个表中所有不满足连接条件的记录全部列出。
注意:
运行上面查询语句时会出现错误，这是因为 MySQL 并不支持全外连接
13 子查询
下面的SQL语句示范了把子查询当成数据表的用法。
select *
# 把子查询当成数据表
from (select * from student_table) t
where t.java_teacher > 1;
还有一种情形:把子查询当成where条件中的值，如果子查询返回单行、单列值，则被当成一个标量值使用，也就可以使用单行记录比较运算符。例如如下SQL语句:
select *
from student_table
where java_teacher >
# 返回单行、单列的子查询可以当成标量值使用
(select teacher_id
from teacher_table
where teacher_name='Yeeku');
上面查询语句中的子查询(粗体字部分)将返回一个单行、单列值(该值就是1)，如果把上面查询语句的括号部分换成1，那么这条语句就再简单不过了一一实际上,这就是这种子查询的实质，单行、单列子查询的返回值被当成标量值处理。
如果子查询返回多个值，则需要使用in、any 和all等关键字，in可以单独使用，与前面介绍比较.运算符时所讲的in完全一样，此时可以把子查询返回的多个值当成一个值列表。SQL语句如下：
select *
from student_table
where student_id in
(select teacher_id
from teacher_table);
上面查询语合中的子查询(粗体字部分)将返回多个值，这多个值将被当成一个值列表，只要student id与该值列表中的任意一个值相等，就可以选出这条记录。
any和all可以与>、>=、<、<=、<>、=等运算符结合使用，与any结合使用分别表示大于、大于等于、小于，小于等于、不等于、等于其中任意一个值;与all结合使用分别表示大于、大于等于、小于，小于等于、不等于、 等于全部值。从上面介绍中可以看出，=any的作用与in的作用相同。如下SQL 语句使用=any来代替上面的in.
select *
from student_table
where student_id =
any(select teacher_id
from teacher_table);
<ANY 只要小于值列表中的最大值即可,>ANY要求大于值列表中的最小值。<All要求小于值列表中的最小值，>ANY要求大于值列表中的最大值。
下面的SOL语句选出student_table表中student_id 大于teacher_table表中所有teacher id的记录.
select *
from student_table
where student id >
all(select teacher_id
from teacher_table);
还有一种子查询可以返回多行、多列，此时where子句中应该有对应的数据列，并使用圆括号将多个数据列组合起来。SQL语句如下:
select *
from student_table
where (student_id, student_name)
= any(select teacher_id, teacher_name
from teacher_table);
14 集合运算
1. union 运算
union运算的语法格式如下:
下面的SQL语句查询出所有教师的信息和主键小于4的学生信息。
# 查询结果集包含两列，第一列为int类型，第二列为varchar类型
select * from teacher_table
union
# 这个结果集的数据列必须与前一个结果集的数据列一一对应
select student_id, student_name from student_table;
2. minus 运算
minus运算的语法格式如下:
上面的语法格式十分简单，不过很遗憾，MySQL并不支持使用minus运算符，因此只能借助子查询来“曲线”实现上面的minus运算。
假如想从所有学生记录中“减去”与老师记录的ID相同、姓名相同的记录、则可进行如下的minus运算:
select student_id, student_name from student_table
minus
# 两个结果集的数据列的数量相等，数据类型一一对应，可以进行minus运算
select teacher_id, teacher_name from teacher_table;
不过，MySQL并不支持这种运算。但可以通过如下子查询来实现上面运算。
select student_id, student_name from student_table
where (student_id, student_name
not in
(select teacher_id, teacher_name from teacher_table)
3. intersect 运算
intersect 运算的语法格式如下:
select 语句 intersect select
上面的语法格式十分简单，不过很遗憾，MySQL并不支持使用 intersect 运算符，因此只能借助于多表连接查询来“曲线”实现上面的 intersect 运算。
假如想找出学生记录中与老师记录中的D相同、姓名相同的记录,则可进行如下的intersect运算:
select student_id, student_name from student_table
intersect
#两个结果集的数据列的数量相等，数据类型一一对应，可以进行intersect运算
select teacher_id, teacher_name from teacher_table;
不过，MySOL并不支持这种运算。但可以通过如下多表连接查询来实现上面运算
select student_id, student_name from student_table
join
teacher_table
on(student_id = teacher_id and student_name = teacher_name);
需要指出的是，如果进行intersect运算的两个select子句中都包括了where条件，那么将intersect 运算改写成多表连接查询后还需要将两个where条件进行and运算。假如有如下 intersect 运算的SQL 语句:
select student_id, student_name from student_table where student_id < 4
intersect
# 两个结果集的数据列的数量相等，数据类型一一对应，可以进行intersect运算
select teacher_id, teacher_name from teacher_table where teacher_name like '李%';
上面语句改写如下:
select student_id, student_name from student_table
join
teacher_table
on (student_id = teacher_id and student_name = teacher_name)
where student_id < 4 and teacher_name like '李%';
