Mysql笔记整理2

1.upper()函数—将文本变为大写

select * from students;
+------+--------+-------+---------------------+-------+
| s_id | s_name | s_sex | s_birthday          | class |
+------+--------+-------+---------------------+-------+
| 100  | zly    | F     | 1998-05-13 00:00:00 | 3班   |
| 101  | xz     | M     | 1991-10-02 00:00:00 | 1班   |
| 103  | web    | M     | 1997-04-23 00:00:00 | 4班   |
| 105  | zdy    | F     | 2000-06-21 00:00:00 | 2班   |
| 108  | cyq    | F     | 1995-09-21 00:00:00 | 1班   |
| 119  | ym     | F     | 2001-01-20 00:00:00 | 1班   |
| 123  | zyl    | M     | 1996-05-26 00:00:00 | 4班   |
| 134  | zzt    | M     | 1998-10-26 00:00:00 | 2班   |
+------+--------+-------+---------------------+-------+
8 rows in set (0.01 sec)

select s_name,Upper(s_name) as s_name_upcase from students order by s_name;
+--------+---------------+
| s_name | s_name_upcase |
+--------+---------------+
| cyq    | CYQ           |
| web    | WEB           |
| xz     | XZ            |
| ym     | YM            |
| zdy    | ZDY           |
| zly    | ZLY           |
| zyl    | ZYL           |
| zzt    | ZZT           |
+--------+---------------+
8 rows in set (0.00 sec)

2.soundex()函数—将文本转换为相同语音的字母数字
背景：录入数据错误，将Y.lie录成了Y.lee,因而找不到Y.lie的对应记录，此时运用soundex()函数可以找到该条错误记录。

select * from students;
+------+--------+-------+---------------------+-------+
| s_id | s_name | s_sex | s_birthday          | class |
+------+--------+-------+---------------------+-------+
| 100  | zly    | F     | 1998-05-13 00:00:00 | 3班   |
| 101  | xz     | M     | 1991-10-02 00:00:00 | 1班   |
| 103  | web    | M     | 1997-04-23 00:00:00 | 4班   |
| 105  | zdy    | F     | 2000-06-21 00:00:00 | 2班   |
| 108  | cyq    | F     | 1995-09-21 00:00:00 | 1班   |
| 112  | Y.lee  | M     | 1999-10-01 00:00:00 | 4班   |
| 119  | ym     | F     | 2001-01-20 00:00:00 | 1班   |
| 123  | zyl    | M     | 1996-05-26 00:00:00 | 4班   |
| 134  | zzt    | M     | 1998-10-26 00:00:00 | 2班   |
+------+--------+-------+---------------------+-------+
9 rows in set (0.00 sec)
select * from students where s_name='Y.lie';    //显然找不到Y.lie的对应记录
Empty set (0.00 sec)
select * from students where soundex(s_name)=soundex('Y.lie');
+------+--------+-------+---------------------+-------+
| s_id | s_name | s_sex | s_birthday          | class |
+------+--------+-------+---------------------+-------+
| 112  | Y.lee  | M     | 1999-10-01 00:00:00 | 4班   |
+------+--------+-------+---------------------+-------+
1 row in set (0.01 sec)    //找到读音相对应的记录

3.Date()函数—仅取日期部分匹配

select * from students where s_birthday='1991-10-02';
+------+--------+-------+---------------------+-------+
| s_id | s_name | s_sex | s_birthday          | class |
+------+--------+-------+---------------------+-------+
| 101  | xz     | M     | 1991-10-02 00:00:00 | 1班   |
+------+--------+-------+---------------------+-------+
1 row in set (0.00 sec)

select * from students where date(s_birthday)='1991-10-02';
+------+--------+-------+---------------------+-------+
| s_id | s_name | s_sex | s_birthday          | class |
+------+--------+-------+---------------------+-------+
| 101  | xz     | M     | 1991-10-02 00:00:00 | 1班   |
+------+--------+-------+---------------------+-------+
1 row in set (0.00 sec)
//两者看起来没有差别，但是第二种更严谨，因为它只取日期为‘1991-10-02’的记录，而不在乎后面具体的时间是几点几分，而第一种取法只会出现一行记录（即使有多条记录符合‘1991-10-02’）

4.自联结通常用作外部语句来替换从相同表中检索数据的子查询语句，它有可能会比子查询更加快速，性能更好。
举例：可见sql必知必会P107页

5.全文本搜索 fulltext() match() against()

create table productnotes
    -> (
    -> note_id int not null auto_increment,
    -> prod_id char(10) not null,
    -> note_date datetime not null,
    -> note_text text null,
    -> primary key(note_id),
    -> fulltext(note_text)    //括号里是谁就是对谁建立索引
    -> )
    -> engine=myisam;   //这个引擎才支持全文本搜索，而mysql8.0默认的innodb引擎是不支持的
Query OK, 0 rows affected (0.02 sec)
insert into productnotes values(1,'aaa','2016-10-03','Warings,rabbit!');
Query OK, 1 row affected (0.01 sec)
select note_text from productnotes where Match(note_text) Against('rabbit');
Empty set (0.00 sec)    //？？？为什么查询不到呢？引擎不是已经设定了吗？
select note_text from productnotes where Match(note_text) Against('rabbit' with query expansion);  //查询扩展，不仅会返回有rabbit的行，还会返回具有相关性的行
Empty set (0.01 sec)

6.insert select语句—插入查询出来的数据

InnoDB—可靠的事务处理引擎，不支持全文本搜索
MyISAM—支持全文本搜索，性能极高，但不支持事务处理
MEMORY—功能等同于MyISAM，但因数据存储在内存而不是磁盘，故速度特别快，特别适合创建临时表

8.rename table—重命名表

rename table card to cards,grade to grades,user to users;
Query OK, 0 rows affected (0.08 sec)

9.视图—是一个虚拟的表，不包含表中的任何数据，而只是一个sql查询（往往用于隐藏复杂的sql查询语句）

如何创建一个视图：

create view students_scores as select c_id,points,students.* from scores,students where scores.s_id=students.s_id;
Query OK, 0 rows affected (0.01 sec)
select * from students_scores;
+------+--------+------+--------+-------+---------------------+-------+
| c_id | points | s_id | s_name | s_sex | s_birthday          | class |
+------+--------+------+--------+-------+---------------------+-------+
| 001  |     98 | 100  | zly    | F     | 1998-05-13 00:00:00 | 3班   |
| 002  |    100 | 101  | xz     | M     | 1991-10-02 00:00:00 | 1班   |
| 001  |     99 | 103  | web    | M     | 1997-04-23 00:00:00 | 4班   |
| 003  |     76 | 103  | web    | M     | 1997-04-23 00:00:00 | 4班   |
| 001  |     68 | 105  | zdy    | F     | 2000-06-21 00:00:00 | 2班   |
| 004  |     86 | 105  | zdy    | F     | 2000-06-21 00:00:00 | 2班   |
| 003  |     66 | 108  | cyq    | F     | 1995-09-21 00:00:00 | 1班   |
| 002  |     89 | 119  | ym     | F     | 2001-01-20 00:00:00 | 1班   |
| 003  |     97 | 134  | zzt    | M     | 1998-10-26 00:00:00 | 2班   |
| 004  |     72 | 134  | zzt    | M     | 1998-10-26 00:00:00 | 2班   |
+------+--------+------+--------+-------+---------------------+-------+
10 rows in set (0.00 sec)   //尽管可以查询出来，但它还是回到原来的两张表中进行查询找到的
select * from students_scores where s_sex='F';
+------+--------+------+--------+-------+---------------------+-------+
| c_id | points | s_id | s_name | s_sex | s_birthday          | class |
+------+--------+------+--------+-------+---------------------+-------+
| 001  |     98 | 100  | zly    | F     | 1998-05-13 00:00:00 | 3班   |
| 001  |     68 | 105  | zdy    | F     | 2000-06-21 00:00:00 | 2班   |
| 004  |     86 | 105  | zdy    | F     | 2000-06-21 00:00:00 | 2班   |
| 003  |     66 | 108  | cyq    | F     | 1995-09-21 00:00:00 | 1班   |
| 002  |     89 | 119  | ym     | F     | 2001-01-20 00:00:00 | 1班   |
+------+--------+------+--------+-------+---------------------+-------+
5 rows in set (0.00 sec)   //可以对视图像表一样进行过滤操作

10.存储过程—为以后的使用而保存的一条或多条mysql语句的集合，可将其视为批文件
例1：
存储过程的创建：

mysql> delimiter //           //改变分隔符为‘//’
mysql> create procedure pointsavg()
    -> begin
    -> select avg(points) as points_avg from scores;
    -> end //
mysql> delimiter ;   //把分隔符改回来
Query OK, 0 rows affected (0.03 sec)

存储过程的执行：call关键字 //和函数的调用好像

call pointsavg();
+------------+
| points_avg |
+------------+
|    85.1000 |
+------------+
1 row in set (0.00 sec)

存储过程的删除：drop

drop procedure pointsavg;    //!!注意这里不要加()
Query OK, 0 rows affected (0.01 sec)

例2：

delimiter //
mysql> create procedure scoresdealing(
    -> out pl decimal(8,2),    //out是指从存储过程传出，而in是指传递给存储过程，inout是指对存储过程传入传出
    -> out ph decimal(8,2),     //decimal(8,2)是指最大可存储8位数字，其中两位是小数
    -> out pa decimal(8,2)
    -> )
    -> begin
    -> select min(points) into pl from scores;
    -> select max(points) into ph from scores;   //into赋值
    -> select avg(points) into pa from scores;
    -> end //
Query OK, 0 rows affected (0.01 sec)

call scoresdealing(@plow,@phigh,@paverage) //    //指定3个用户变量名（相当于java中的成员变量），不需要声明，使用即声明
Query OK, 1 row affected (0.00 sec)     //调用该存储过程

select @plow,@phigh,@paverage //    //所有sql用户变量均要以@开始
+-------+--------+-----------+
| @plow | @phigh | @paverage |
+-------+--------+-----------+
| 66.00 | 100.00 |     85.10 |
+-------+--------+-----------+
1 row in set (0.00 sec)   

例3，见必知必会P169 好智能啊
例4，P171 更加厉害了

delimiter //
create procedure sp1()
begin
declare var1 varchar(16) default 'unknown';   //声明一个局部变量
set var1 = 'SF';            //set赋值     
select var1;
end //
call sp1() //