98. set character_set_client = gbk;
set character_set_connection = gbk;
set character_set_database = gbk;
set character_set_results = gbk;
set character_set_server = gbk;
set collation_connection = gbk_chinese_ci ;
set collation_database = gbk_chinese_ci ;
set collation_server = gbk_chinese_ci ;2.2.4 MySQL字符集的设置
119. use choose;
insert into my_table values(now(),'a');
insert into my_table values(now(),'a');
insert into my_table values(now(),NULL);
insert into my_table values(now(),'');
select * from my_table; 2.4.5 表记录的管理
133. 使用“show global variables;”命令即可查看MySQL服务器内存中所有的全局系统变量信息(有393项之多)。
使用“show session variables;”命令即可查看与当前会话相关的所有会话系统变量以及所有的全局系统变量(有405项之多),此处session关键字可以省略。 2.5.2 查看系统变量的值
134. MySQL中有一些系统变量仅仅是全局系统变量,例如 innodb_data_file_path。
show global variables like 'innodb_data_file_path';
show session variables like 'innodb_data_file_path';
show variables like 'innodb_data_file_path';2.5.2 查看系统变量的值
135. MySQL中有一些系统变量仅仅是会话系统变量,例如MySQL连接ID会话系统变量pseudo_thread_id。
show session variables like 'pseudo_thread_id';
show variables like 'pseudo_thread_id';2.5.2 查看系统变量的值
137. 此时查看会话系统变量的方法:
show session variables like 'character_set_client';
show variables like 'character_set_client';
此时查看全局系统变量的方法:
show global variables like 'character_set_client';2.5.2 查看系统变量的值
141. 设置全局系统变量的值的方法:
set @@global.innodb_file_per_table = default;
set @@global.innodb_file_per_table = ON;
set global innodb_file_per_table = ON;2.5.2 查看系统变量的值
142. 设置会话系统变量的值的方法:
set @@session.pseudo_thread_id = 5;
set session pseudo_thread_id = 5;
set @@pseudo_thread_id = 5;
set pseudo_thread_id = 5;2.5.2 查看系统变量的值
188. 2.删除约束条件
(1)删除表的主键约束条件语法格式比较简单,语法格式如下。
alter table 表名 drop primary key
(2)删除表的外键约束时,需指定外键约束名称,语法格式如下(注意需指定外键约束名)。
alter table 表名 drop foreign key 约束名3.3.2 修改约束条件
190. alter table 表名 engine=新的存储引擎类型
alter table 表名 default charset=新的字符集
alter table 表名 auto_increment=新的初始值
alter table 表名 pack_keys=新的压缩类型 3.3.3 修改表的其他选项
201. create table book(
isbn char(20) primary key,
name char(100) not null,
brief_introduction text not null,
price decimal(6,2),
publish_time date not null,
unique index isbn_unique (isbn),
index name_index (name (20)),
fulltext index brief_fulltext (name,brief_introduction),
index complex_index (price,publish_time)
) engine=MyISAM default charset=gbk;3.5.4 创建索引
214. 在insert语句中使用select子句可以将源表的查询结果添加到目标表中,语法格式如下。
insert into 目标表名[(字段列表1)]
select (字段列表2) from 源表 where 条件表达式
注意:字段列表1与字段列表2的字段个数必须相同,且对应字段的数据类型尽量保持一致。
如果源表与目标表的表结构完全相同,“(字段列表1)”可以省略。4.1.5 使用insert….select插入结果
215. replace语句的语法格式有三种语法格式。
语法格式1:replace into 表名 [(字段列表)] values (值列表)
语法格式2:replace [into] 目标表名[(字段列表1)]
select (字段列表2) from 源表 where 条件表达式4.1.6 使用replace插入新记录
216. replace语句的语法格式有三种语法格式。
语法格式3:
replace [into] 表名
set 字段1=值1, 字段2=值24.1.6 使用replace插入新记录
245. (1)左连接的语法格式
from 表1 left join 表2 on 表1和表2之间的连接条件
说明:语法格式中表1左连接表2,意味着查询结果集中须包含表1的全部记录,然后表1按指定的连接条件与表2进行连接,若表2中没有满足连接条件的记录,则结果集中表2相应的字段填入NULL。5.1.3 使用from子句指定数据源
246. 任务布置2:完成本书场景描述2的任务要求。
5.1.3 使用from子句指定数据源
247. (2)右连接的语法格式
from 表1 right join 表2 on 表1和表2之间的连接条件
说明:语法格式中表1右连接表2,意味着查询结果集中须包含表2的全部记录,然后表2按指定的连接条件与表1进行连接,若表1中没有满足连接条件的记录,则结果集中表1相应的字段填入NULL。 5.1.3 使用from子句指定数据源
248. 任务布置3:完成本书场景描述3的任务要求。
5.1.3 使用from子句指定数据源
249. 以3个表为例,语法格式如下。
from 表1 [连接类型] join 表2 on 表1和表2之间的连接条件
[连接类型] join 表3 on 表2和表3之间的连接条件5.1.4 多表连接
250. (本页无文本内容)
251. 数据库中存储着海量数据,数据库用户往往需要的是满足特定条件的记录,where子句可以实现结果集的过滤筛选。
where子句的语法格式:
where 条件表达式123is NULL运算符使用单一的条件过滤结果集select语句与字符集4使用逻辑运算符5使用like进行模糊查询5.2 使用where子句过滤结果集
254. is NULL用于判断表达式的值是否为空值NULL(is not 恰恰相反),is NULL的语法格式如下。
表达式 is [ not ] NULL
说明:不能将“score is NULL”写成“score = NULL;”,原因是NULL是一个不确定的数,不能使用“=”、“!=”等比较运算符与NULL进行比较。
5.2.2 is NULL运算符
258. 2.and逻辑运算符
使用and逻辑运算符连接两个布尔表达式时,只有两个布尔表达式的值都为true时,整个逻辑表达式的结果才为true。语法格式如下。
布尔表达式1 and 布尔表达式2 5.2.4 使用逻辑运算符
259. 另外MySQL还支持between…and…运算符,between…and…运算符用于判断一个表达式的值是否位于指定的取值范围内,between…and…的语法格式如下。
表达式 [not] between 起始值 and 终止值5.2.4 使用逻辑运算符
260. 3.使用or逻辑运算符
使用or逻辑运算符连接两个布尔表达式时,只有两个表达式的值都为false时,整个逻辑表达式的结果才为false。语法格式如下。
布尔表达式1 or 布尔表达式2 5.2.4 使用逻辑运算符
261. 另外MySQL还支持in运算符,in运算符用于判定一个表达式的值是否位于一个离散的数学集合内,in的语法格式如下。
表达式 [not] in (数学集合)5.2.4 使用逻辑运算符
262. like运算符用于判断一个字符串是否与给定的模式相匹配。
模式是一种特殊的字符串,特殊之处在于不仅包含普通字符,还包含有通配符。在实际应用中,如果不能对字符串进行精确查询,此时可以使用like运算符与通配符实现模糊查询,like运算符的语法格式如下。
字符串表达式 [ not ] like 模式5.2.5 使用like进行模糊查询
264. 模糊查询“%”或者“_”字符时,需要将“%”或者“_”字符转义,例如检索学生姓名中所有带“_”的学生信息,可以使用下面的SQL语句,其中new_student表在表记录的更新操作章节中创建。执行结果如图5-23所示。
select * from new_student where student_name like '%\_%';5.2.5 使用like进行模糊查询
265. 如果不想使用“\”作为转义字符,可以使用escape关键字自定义一个转义字符,例如下面的SQL语句使用字符“!”作为转义字符。
select * from new_student where student_name like '%!_%' escape '!';5.2.5 使用like进行模糊查询
266. select语句的查询结果集的排序由数据库系统动态确定,往往是无序的,order by子句用于对结果集排序。在select语句中添加order by子句,就可以使结果集中的记录按照一个或多个字段的值进行排序,排序的方向可以是升序(asc)或降序(desc)。order by子句的语法格式如下。
order by 字段名1 [asc|desc] [ … ,字段名n [asc|desc] ]5.3 使用order by子句对结果集排序
269. group by子句将查询结果按照某个字段(或多个字段)进行分组(字段值相同的记录作为一个分组。123group by子句与having子句group by子句与聚合函数group by子句与group_concat()函数4group by子句与with rollup选项5.5 使用group by子句对记录分组统计
270. group by子句通常与聚合函数一起使用。
group by子句的语法格式如下。
group by 字段列表 [ having条件表达式 ] [ with rollup ]
任务布置7:完成本书场景描述7的任务要求。
5.5 使用group by子句对记录分组统计
271. 例如统计每一个班的学生人数。
例如统计每个学生已经选修多少门课程,该生的最高分、最低分、总分及平均成绩。5.5.1 group by子句与聚合函数
272. having子句用于设置分组或聚合函数的过滤筛选条件,having子句通常与group by子句一起使用。having子句语法格式与where子句语法格式类似,having子句语法格式如下。
having条件表达式
其中条件表达式是一个逻辑表达式,用于指定分组后的筛选条件。5.5.2 group by子句与having子句
273. 例如检索平均成绩高于70分的学生信息及平均成绩。5.5.2 group by子句与having子句
274. 下面select语句的语法格式中,select语句的执行过程为:首先使用where子句对结果集进行过滤筛选,接着group by子句分组where子句的输出,最后having子句从分组的结果中再进行筛选。
select 字段列表
from 数据源
where条件表达式
group by 分组字段 having条件表达式 5.5.2 group by子句与having子句
275. group_concat()函数的功能是将集合中的字符串连接起来,此时group_concat()函数的功能与字符串连接函数concat()的功能相似。5.5.3 group by子句与group_concat()函数
276. group_concat()函数的功能是将集合中的字符串连接起来,此时group_concat()函数的功能与字符串连接函数concat()的功能相似。
例如下面SQL语句中的group_concat()函数以及concat()函数负责将集合中('java', '程序', '设计')三个字符串连接起来。
select group_concat('java','程序','设计'),
concat('java','程序','设计');5.5.3 group by子句与group_concat()函数
277. group_concat()函数还可以按照分组字段,将另一个字段的值(NULL值除外)使用逗号连接起来。 concat()却函数没有提供这样的功能。 5.5.3 group by子句与group_concat()函数
278. group by子句将结果集分为若干个组,使用聚合函数可以对每个组内的数据进行信息统计,有时需要对各个组进行汇总运算,则需要在每个分组后加上一条汇总记录,这个任务可以通过with rollup选项实现。5.5.4 group by子句与with rollup选项
279. 5.6 合并结果集 使用union可以将多个select 语句的查询结果集组合成一个结果集。
select 字段列表1 from table1
union [all]
select 字段列表2 from table2...
说明:字段列表1与字段列表2的字段个数必须相同,且具有相同的数据类型。合并产生的新结果集的字段名与字段列表1中的字段名对应。
280. union 与 union all 的区别:
当使用union时,MySQL 会筛选掉select结果集中重复的记录(在结果集合并后会对新产生的结果集进行排序运算,效率稍低)。而使用union all时,MySQL会直接合并两个结果集,效率高于union。如果可以确定合并前的两个结果集中不包含重复的记录,建议使用union all。5.6 合并结果集
300. 简单地说,MySQL全文检索使用特定的分词技术、利用查询关键字和查询字段内容之间的相关度进行检索,通过全文索引提高文本匹配的速度。全文检索的语法格式如下。
select字段列表
from 表名
where match (全文索引字段1,全文索引字段2,...) against (搜索关键字 [ 全文检索方式 ])5.10 全文检索
302. create table book(
isbn char(20) primary key,
name char(100) not null,
brief_introduction text not null,
price decimal(6,2),
publish_time date not null,
unique index isbn_unique (isbn),
index name_index (name (20)),
fulltext index brief_fulltext (name,brief_introduction),
index complex_index (price,publish_time)
) engine=MyISAM default charset=gbk; 创建书籍book表(注意该表为MyISAM存储引擎),在book表中的字段组合(name, brief_introduction)创建一个全文索引 。5.10.1 全文检索的简单应用
303. insert into book(isbn,name,brief_introduction,price,publish_time) values
('978-7-115-25626-3','PHP Fundamentals & Practices','Web Database Applications MySQL offers web developers a mixture of theoretical and practical information on creating web database applications. ','42.0','2012-7-1'),
('978-7-115-25626-4','MySQL COOKBOOK','The MySQL database management system has become quite popular in recent years.','128.0','2008-1-1'),
('978-7-115-25626-5','Beginning MySQL',' MySQL is especially heavily used in combination with a web server for constructing database-backed web sites that involve dynamic content generation.','98.0','2008-1-1'); 向book表插入测试数据。5.10.1 全文检索的简单应用
304. 例如检索书名或者简介中涉及到practices单词的所有图书信息,可以使用下面的SQL语句。
select * from book where match (name,brief_introduction) against ('practices')\G5.10.1 全文检索的简单应用
305. 例如检索书名或者简介中涉及到practices或者cookbook单词的所有图书信息,可以使用下面的SQL语句。
select * from book where match (name,brief_introduction) against ('practices cookbook')\G5.10.1 全文检索的简单应用
307. 如果希望忽略阈值的因素,例如检索书名或者简介中涉及到“mysql”单词的所有图书信息,可以使用下面的SQL语句,执行结果如图5-66所示,该SQL语句使用了布尔检索模式(稍后讲解)。
select * from book where match (name,brief_introduction) against ('mysql' in boolean mode)\G5.10.1 全文检索的简单应用
314. 禁用自定义的停用字,重启MySQL服务器,并重建全文索引后,检索书名或者简介中涉及“mysql”但不涉及“php”单词的的所有图书信息,可以使用下面的SQL语句。
select * from book where match (name,brief_introduction) against ('+mysql -php' in boolean mode)\G5.10.3 布尔检索模式的复杂应用
325. 1.字符串常量
字符串常量是指用单引号或双引号括起来的字符序列。select 'I\'m a \teacher' as col1, "you're a stude\nt" as col2;
由于大多编程语言(例如Java、C等)使用双引号表示字符串,为了便于区分,在MySQL数据库中推荐使用单引号表示字符串。6.1.1 常量
360. [开始标签:] begin
[局部]变量的声明;
错误触发条件的声明;
游标的声明;
错误处理程序的声明;
业务逻辑代码;
end[结束标签];6.1.5 重置命令结束标记
361. delimiter $$
select * from student where student_name like '张_'$$
delimiter ;
select * from student where student_name like '张_';6.1.4 begin-end语句块
371. 1.查看函数的定义
(1)查看当前数据库中所有的自定义函数信息,可以使用MySQL命令“show function status;”。如果自定义函数较多,使用MySQL命令“show function status like 模式;”可以进行模糊查询。 6.2.3 函数的维护
372. 1.查看函数的定义
(2)查看指定数据库(例如choose数据库)中的所有自定义函数名,可以使用下面的SQL语句,如图6-28所示。
select name from mysql.proc where db = 'choose' and type = 'function' ;6.2.3 函数的维护
373. 1.查看函数的定义
(3)使用MySQL命令“show create function 函数名;”可以查看指定函数名的详细信息。例如查看get_name_fn()函数的详细信息,可以使用“show create function get_name_fn\G” 6.2.3 函数的维护
374. 1.查看函数的定义
(4)函数的信息都保存在information_schema数据库中的routines表中,可以使用select语句检索routines表,查询函数的相关信息。
select * from information_schema.routines where routine_name='get_name_fn'\G6.2.3 函数的维护
423. 最为常用的数据类型转换函数是convert(x,type)与cast(x as type)函数,另外MySQL还提供了“十六进制字符串”转换为“十六进制数”的函数unhex(x)。
(1)convert()函数
convert()函数有两种用法格式:convert(x using charset)函数返回x的charset字符集数据(刚刚讲过,这里不再赘述)。 6.3.3 数据类型转换函数
424. convert()函数还有另外一种语法格式:convert(x,type),可以实现数据类型的转换。convert(x,type)函数以type数据类型返回x数据(注意x的数据类型没有变化)。除此以外cast(x as type)函数也实现了convert(x,type)函数相同的功能。 6.3.3 数据类型转换函数
429. (3)case函数
case函数的语法格式如下。如果表达式的值等于when语句中某个“值n”,则case函数返回值为“结果n”;如果与所有的“值n”都不相等,case函数返回值为“其他值”。
case 表达式 when 值1 then 结果1 [ when 值2 then 结果2 ]… [ else 其他值 ] end6.3.4 条件控制函数
531. 例如下面的存储过程:delimiter $$
create procedure get_choose_number_proc(in student_no1 int,out choose_number int)
reads sql data
begin
select count(*) into choose_number from choose where student_no=student_no1;
end
$$
delimiter ;8.1.1 创建存储过程的语法格式
532. 调用存储过程须使用call关键字,另外还要向存储过程传递in参数、out参数或者inout参数。
例如: set @student_no = '2012001';
set @choose_number = 0;
call get_choose_number_proc(@student_no,@choose_number);
select @choose_number;8.1.2 存储过程的调用
534. delimiter $$
create procedure get_choose_number1_proc(inout number int)
reads sql data
begin
select count(*) into number from choose where student_no=number ;
end
$$
delimiter ;8.1.2 存储过程的调用
535. set @number = '2012001';
call get_choose_number1_proc(@number);
select @number;8.1.2 存储过程的调用
537. 可以使用下面四种方法查看存储过程的定义、权限、字符集等信息。
1.使用show procedure status命令查看存储过程的定义。
2.查看某个数据库(例如choose数据库)中的所有存储过程名,可以使用下面的SQL语句。
select name from mysql.proc where db = 'choose' and type = 'procedure';8.1.4 查看存储过程的定义
564. 当使用fetch语句从游标中提取最后一条记录后,再次执行fetch语句时,将产生“ERROR 1329 (02000): No data to FETCH”错误信息,数据库开发人员可以针对MySQL错误代码1329,自定义错误处理程序以便结束“结果集”的遍历。
注意:游标错误处理程序应该放在声明游标语句之后。游标通常结合错误处理程序一起使用,用于结束“结果集”的遍历。8.3.1 使用游标
565. 4.关闭游标
关闭游标使用close语句,其语法格式如下。
close 游标名
关闭游标的目的在于释放游标打开时产生的结果集,节省MySQL服务器的内存空间。游标如果没有被明确地关闭,游标将在它被声明的begin-end语句块的末尾关闭。8.3.1 使用游标
598. 保存点(也称为检查点)可以实现事务的“部分”提交或者“部分”撤销。
使用MySQL命令“savepoint 保存点名;”可以在事务中设置一个保存点,使用MySQL命令“rollback to savepoint 保存点名;”可以将事务回滚到保存点状态。9.1.6 保存点
599. (本页无文本内容)
600. 任务布置5:上机操作,完成本书场景描述6的任务要求,理解保存点的作用。9.1.6 保存点
601. 说明:“rollback to savepoint B”仅仅是让数据库回到事务中的某个“一致性状态B”,而“一致性状态B”仅仅是一个“临时状态”,该“临时状态”并没有将更新回滚,也没有将更新提交。事务回滚必须借助于rollback(而不是“rollback to savepoint B”),事务的提交需借助于commit。9.1.6 保存点
602. 使用MySQL命令“release savepoint 保存点名;”可以删除一个事务的保存点。
如果该保存点不存在,该命令将出现错误信息:ERROR 1305 (42000): SAVEPOINT does not exist。如果当前的事务中存在两个相同名字的保存点,旧保存点将被自动丢弃。9.1.6 保存点
622. 方法1.在查询(select)语句中,为符合查询条件的记录施加共享锁,语法格式如下所示。
select * from 表 where 条件语句 lock in share mode;
方法2.在查询(select)语句中,为符合查询条件的记录施加排他锁,语法格式如下所示。
select * from 表 where 条件语句 for update;9.2.4 InnoDB表的行级锁
629. 意向共享锁(IS):向InnoDB表的某些记录施加行级共享锁时,InnoDB存储引擎会自动地向该表施加意向共享锁(IS)。也就是说:执行“select * from 表 where 条件语句 lock in share mode;”后,InnoDB存储引擎在为表中符合条件语句的记录施加共享锁前,InnoDB会自动地为该表施加意向共享锁(IS)。9.2.6 InnoDB表的意向锁
630. 意向排它锁(IX):向InnoDB表的某些记录施加行级排它锁时,InnoDB存储引擎会自动地向该表施加意向排它锁(IX)。也就是说:执行更新语句(例如insert、update或者delete语句)或者“select * from 表 where 条件语句 for update;”,InnoDB存储引擎在为表中符合条件语句的记录施加排他锁前,InnoDB会自动地为该表施加意向排它锁(IX)。9.2.6 InnoDB表的意向锁