mysql自学二

1.回顾一下sql

a.and 的优先级要高于or。

b.group by 属性名 [having 条件表达式][with rollup]。

with rollup 关键字将会在每个分组的最后加上一条记录。该记录是分组的总计。

mysql> select * from student;
+------+-------+------+-------+-------+
| id   | st_id | name | kc_id | score |
+------+-------+------+-------+-------+
|    1 |       1 |    ??   |         1 |      78 |
|    2 |       2 |    ??   |         2 |      67 |
|    3 |       3 |    ??   |         1 |      98 |
|    4 |       4 |    ??   |         2 |      70 |
+------+-------+------+-------+-------+
4 rows in set (0.00 sec)
mysql> select kc_id,sum(score) from student group by kc_id with rollup;
+-------+------------+
| kc_id | sum(score) |
+-------+------------+
|     1 |        176 |
|     2 |        137 |
|  NULL |        313 |
+-------+------------+
3 rows in set (0.00 sec)
mysql> select kc_id,avg(score) from student group by kc_id with rollup;
+-------+------------+
| kc_id | avg(score) |
+-------+------------+
|     1 |    88.0000 |
|     2 |    68.5000 |
|  NULL |    78.2500 |
+-------+------------+
3 rows in set (0.00 sec)
mysql> select kc_id,min(score) from student group by kc_id with rollup;
+-------+------------+
| kc_id | min(score) |
+-------+------------+
|     1 |         78 |
|     2 |         67 |
|  NULL |         67 |
+-------+------------+
3 rows in set (0.00 sec)
mysql> select kc_id,max(score) from student group by kc_id with rollup;
+-------+------------+
| kc_id | max(score) |
+-------+------------+
|     1 |         98 |
|     2 |         70 |
|  NULL |         98 |
+-------+------------+
3 rows in set (0.00 sec)
mysql> select kc_id,count(1) from student group by kc_id with rollup;
+-------+----------+
| kc_id | count(1) |
+-------+----------+
|     1 |        2 |
|     2 |        2 |
|  NULL |        4 |
+-------+----------+
3 rows in set (0.00 sec)
mysql> select kc_id,count(1) from student group by kc_id with rollup;
+-------+----------+
| kc_id | count(1) |
+-------+----------+
|     1 |        2 |
|     2 |        2 |
|  NULL |        4 |
+-------+----------+
3 rows in set (0.00 sec)
mysql> select id,kc_id,avg(score) from student group by id,kc_id with rollup;
+------+-------+------------+
| id   | kc_id | avg(score) |
+------+-------+------------+
|    1 |     1 |    78.0000 |
|    1 |  NULL |    78.0000 |
|    2 |     2 |    67.0000 |
|    2 |  NULL |    67.0000 |
|    3 |     1 |    98.0000 |
|    3 |  NULL |    98.0000 |
|    4 |     2 |    70.0000 |
|    4 |  NULL |    70.0000 |
| NULL |  NULL |    78.2500 |
+------+-------+------------+
9 rows in set (0.00 sec)
c.group by 关键字与group_concat()函数一起使用时，每个分组中指定字段值都显示出来。
mysql> select kc_id,group_concat(st_id) from student group by kc_id;
+-------+---------------------+
| kc_id | group_concat(st_id) |
+-------+---------------------+
|     1 | 1,3                 |
|     2 | 2,4                 |
+-------+---------------------+
2 rows in set (0.00 sec)

d.用limit限制查询结果的数量

limit 记录数

mysql> select * from student limit 3;
+------+-------+------+-------+-------+
| id   | st_id | name | kc_id | score |
+------+-------+------+-------+-------+
|    1 |     1 | ??   |     1 |    78 |
|    2 |     2 | ??   |     2 |    67 |
|    3 |     3 | ??   |     1 |    98 |
+------+-------+------+-------+-------+
3 rows in set (0.00 sec)

limit 初始位置，记录数

mysql> select * from student limit 0,2;
+------+-------+------+-------+-------+
| id   | st_id | name | kc_id | score |
+------+-------+------+-------+-------+
|    1 |     1 | ??   |     1 |    78 |
|    2 |     2 | ??   |     2 |    67 |
+------+-------+------+-------+-------+
2 rows in set (0.00 sec)
e.使用正则表达式查询

属性名 regexp '匹配方式‘

mysql> select * from student;
+------+-------+-------+-------+-------+
| id   | st_id | name  | kc_id | score |
+------+-------+-------+-------+-------+
|    1 |     1 | ??    |     1 |    78 |
|    2 |     2 | ??    |     2 |    67 |
|    3 |     3 | ??    |     1 |    98 |
|    4 |     4 | ??    |     2 |    70 |
|    5 |     5 | alen  |     1 |    56 |
|    6 |     6 | Aer   |     2 |    67 |
|    7 |     7 | emiyt |     1 |    87 |
|    8 |     8 | allen |     1 |    56 |
|    9 |     9 | n     |     1 |    56 |
+------+-------+-------+-------+-------+
9 rows in set (0.00 sec)
mysql> select * from student where name regexp '^a';
+------+-------+-------+-------+-------+
| id   | st_id | name  | kc_id | score |
+------+-------+-------+-------+-------+
|    5 |     5 | alen  |     1 |    56 |
|    6 |     6 | Aer   |     2 |    67 |
|    8 |     8 | allen |     1 |    56 |
+------+-------+-------+-------+-------+
3 rows in set (0.00 sec)
mysql> select * from student where name regexp 't$'
    -> ;
+------+-------+-------+-------+-------+
| id   | st_id | name  | kc_id | score |
+------+-------+-------+-------+-------+
|    7 |     7 | emiyt |     1 |    87 |
+------+-------+-------+-------+-------+
1 row in set (0.00 sec)
mysql> select * from student where name regexp 'e...t'
    -> ;
+------+-------+-------+-------+-------+
| id   | st_id | name  | kc_id | score |
+------+-------+-------+-------+-------+
|    7 |     7 | emiyt |     1 |    87 |
+------+-------+-------+-------+-------+
1 row in set (0.00 sec)

查询name中包含a,b,b字符的记录。

mysql> select * from student where name regexp '[a-c]';
+------+-------+-------+-------+-------+
| id   | st_id | name  | kc_id | score |
+------+-------+-------+-------+-------+
|    5 |     5 | alen  |     1 |    56 |
|    6 |     6 | Aer   |     2 |    67 |
|    8 |     8 | allen |     1 |    56 |
+------+-------+-------+-------+-------+
3 rows in set (0.00 sec)
查询name中不包含a,b,c字符的记录。
mysql> select * from student where name regexp '[^a-c]';
+------+-------+-------+-------+-------+
| id   | st_id | name  | kc_id | score |
+------+-------+-------+-------+-------+
|    1 |     1 | ??    |     1 |    78 |
|    2 |     2 | ??    |     2 |    67 |
|    3 |     3 | ??    |     1 |    98 |
|    4 |     4 | ??    |     2 |    70 |
|    5 |     5 | alen  |     1 |    56 |
|    6 |     6 | Aer   |     2 |    67 |
|    7 |     7 | emiyt |     1 |    87 |
|    8 |     8 | allen |     1 |    56 |
|    9 |     9 | n     |     1 |    56 |
+------+-------+-------+-------+-------+
9 rows in set (0.00 sec)

mysql> select * from student where name regexp 'l';
+------+-------+-------+-------+-------+
| id   | st_id | name  | kc_id | score |
+------+-------+-------+-------+-------+
|    5 |     5 | alen  |     1 |    56 |
|    8 |     8 | allen |     1 |    56 |
+------+-------+-------+-------+-------+
2 rows in set (0.00 sec)
mysql> select * from student where name regexp 'l|n';
+------+-------+-------+-------+-------+
| id   | st_id | name  | kc_id | score |
+------+-------+-------+-------+-------+
|    5 |     5 | alen  |     1 |    56 |
|    8 |     8 | allen |     1 |    56 |
|    9 |     9 | n     |     1 |    56 |
+------+-------+-------+-------+-------+
3 rows in set (0.00 sec)

表示n前面出现过0个或者1个及以上的l。

mysql> select * from student where name regexp 'l*n';
+------+-------+-------+-------+-------+
| id   | st_id | name  | kc_id | score |
+------+-------+-------+-------+-------+
|    5 |     5 | alen  |     1 |    56 |
|    8 |     8 | allen |     1 |    56 |
|    9 |     9 | n     |     1 |    56 |
+------+-------+-------+-------+-------+
3 rows in set (0.00 sec)

表示n前面至少出现一个e。

mysql> select * from student where name regexp 'e+n';
+------+-------+-------+-------+-------+
| id   | st_id | name  | kc_id | score |
+------+-------+-------+-------+-------+
|    5 |     5 | alen  |     1 |    56 |
|    8 |     8 | allen |     1 |    56 |
+------+-------+-------+-------+-------+
2 rows in set (0.00 sec)

查询name中l字符出现过1次到2次的记录。

mysql> select * from student where name regexp 'l{1,2}';
+------+-------+-------+-------+-------+
| id   | st_id | name  | kc_id | score |
+------+-------+-------+-------+-------+
|    5 |     5 | alen  |     1 |    56 |
|    8 |     8 | allen |     1 |    56 |
+------+-------+-------+-------+-------+
2 rows in set (0.01 sec)
2.函数

if(expr,v1,v2)函数

mysql> select st_id,if(score>=60,'PASS','FAIL') from student;
+-------+-----------------------------+
| st_id | if(score>=60,'PASS','FAIL') |
+-------+-----------------------------+
|     1 | PASS                        |
|     2 | PASS                        |
|     3 | PASS                        |
|     4 | PASS                        |
|     5 | FAIL                        |
|     6 | PASS                        |
|     7 | PASS                        |
|     8 | FAIL                        |
|     9 | FAIL                        |
+-------+-----------------------------+
9 rows in set (0.00 sec)
ifnull(v1,v2)函数

如果v1不为空，就显示v1的值，否则显示v2。

case函数

获取版本号，连接数和数据库名称的函数

mysql> select version(),connection_id(),database();
+-------------------------+-----------------+------------+
| version()               | connection_id() | database() |
+-------------------------+-----------------+------------+
| 5.5.37-0ubuntu0.12.04.1 |              45 | test       |
+-------------------------+-----------------+------------+
1 row in set (0.00 sec)
3.视图
用户可以不用看到整个数据库表中的数据，而只关心对自己有用的数据。视图可以使用户的操作更方便，而且可以保障数据库系统的安全性。

创建表

mysql> create table work(id int(10) not null unique primary key,
    -> name varchar(20) not null,
    -> sex varchar(4) not null,
    -> age int(5),
    -> address varchar(50),
    -> tel varchar(20)
    -> );
Query OK, 0 rows affected (0.37 sec)

初始化数据

mysql> insert into work values(1,'name1','M',18,'address1','123');
Query OK, 1 row affected (0.45 sec)

mysql> insert into work values(2,'name2','M',22,'address2','234');
Query OK, 1 row affected (0.45 sec)

mysql> insert into work values(3,'name3','F',17,'address3','345');
Query OK, 1 row affected (0.15 sec)

mysql> insert into work values(4,'name4','F',26,'address4','456');
Query OK, 1 row affected (0.44 sec)
查询

mysql> select * from work;
+----+-------+-----+------+----------+------+
| id | name  | sex | age  | address  | tel  |
+----+-------+-----+------+----------+------+
|  1 | name1 | M   |   18 | address1 | 123  |
|  2 | name2 | M   |   22 | address2 | 234  |
|  3 | name3 | F   |   17 | address3 | 345  |
|  4 | name4 | F   |   26 | address4 | 456  |
+----+-------+-----+------+----------+------+
4 rows in set (0.00 sec)

创建视图

mysql> create algorithm=merge view
    -> work_view (id,name,sex,address)
    -> as select id,name,sex,address
    -> from work where age >20
    -> with local check option;
Query OK, 0 rows affected (0.15 sec)

mysql> desc work_view
    -> ;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(10)     | NO   |     | NULL    |       |
| name    | varchar(20) | NO   |     | NULL    |       |
| sex     | varchar(4)  | NO   |     | NULL    |       |
| address | varchar(50) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> show create view work_view\G
*************************** 1. row ***************************
                View: work_view
         Create View: CREATE ALGORITHM=MERGE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `work_view` AS select `work`.`id` AS `id`,`work`.`name` AS `name`,`work`.`sex` AS `sex`,`work`.`address` AS `address` from `work` where (`work`.`age` > 20) WITH LOCAL CHECK OPTION
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

使用视图

mysql> select * from work_view;
+----+-------+-----+----------+
| id | name  | sex | address  |
+----+-------+-----+----------+
|  2 | name2 | M   | address2 |
|  4 | name4 | F   | address4 |
+----+-------+-----+----------+
2 rows in set (0.00 sec)

修改视图

mysql> alter algorithm=merge view work_view(id,name,sex,address)
    -> as select id,name,sex,address
    -> from work where age<20
    -> with local check option;
Query OK, 0 rows affected (0.14 sec)

mysql> show create view work_view\G
*************************** 1. row ***************************
                View: work_view
         Create View: CREATE ALGORITHM=MERGE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `work_view` AS select `work`.`id` AS `id`,`work`.`name` AS `name`,`work`.`sex` AS `sex`,`work`.`address` AS `address` from `work` where (`work`.`age` < 20) WITH LOCAL CHECK OPTION
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

mysql> select * from work_view;
+----+-------+-----+----------+
| id | name  | sex | address  |
+----+-------+-----+----------+
|  1 | name1 | M   | address1 |
|  3 | name3 | F   | address3 |
+----+-------+-----+----------+
2 rows in set (0.00 sec)
更新视图中的记录，原表中的记录也会更新。

mysql> update work_view set sex ='M' where id =3;
Query OK, 1 row affected (0.13 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from work_view;
+----+-------+-----+----------+
| id | name  | sex | address  |
+----+-------+-----+----------+
|  1 | name1 | M   | address1 |
|  3 | name3 | M   | address3 |
+----+-------+-----+----------+
2 rows in set (0.00 sec)

mysql> select * from work;
+----+-------+-----+------+----------+------+
| id | name  | sex | age  | address  | tel  |
+----+-------+-----+------+----------+------+
|  1 | name1 | M   |   18 | address1 | 123  |
|  2 | name2 | M   |   22 | address2 | 234  |
|  3 | name3 | M   |   17 | address3 | 345  |
|  4 | name4 | F   |   26 | address4 | 456  |
+----+-------+-----+------+----------+------+
4 rows in set (0.00 sec)
删除视图

mysql> drop view work_view;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from work_view\G
ERROR 1146 (42S02): Table 'test.work_view' doesn't exist
mysql>
4.触发器

由insert ,update 和delete等事件触发某种特定操作。

创建相关表

mysql> create table stu_log(id int(10) not null unique primary key auto_increment,
    -> name varchar(50) not null,
    -> inserttime time not null);
Query OK, 0 rows affected (0.35 sec)

创建触发器

mysql> create trigger insertstu before insert on student for each row
    -> insert into stu_log values(null,'insert action',now());
Query OK, 0 rows affected (0.24 sec)

mysql> select * from stu_log;
Empty set (0.00 sec)

mysql> insert into student values (10,10,'name10',1,89);
Query OK, 1 row affected (0.12 sec)

mysql> select * from stu_log;
+----+---------------+------------+
| id | name          | inserttime |
+----+---------------+------------+
|  2 | insert action | 23:35:17   |
+----+---------------+------------+
1 row in set (0.00 sec)

查看触发器

mysql> select trigger_name from information_schema.triggers;
+--------------+
| trigger_name |
+--------------+
| insertstu    |
+--------------+
1 row in set (0.01 sec)

删除触发器

mysql> drop trigger insertstu;
Query OK, 0 rows affected (0.00 sec)

mysql> select trigger_name from information_schema.triggers;
Empty set (0.00 sec)
5.存储过程

创建存储过程

delimiter &&
create procedure st_score_count1(out max_score int)
reads sql data
begin
    declare temp int;
    declare score_cursor cursor for select max(score) from student;
    declare exit handler for not found close score_cursor;    
    open score_cursor;
    repeat
            fetch score_cursor into temp;
            set max_score = temp;
    until 0 end repeat;
    close score_cursor;
end &&
delimiter ;

查看

mysql> show create procedure st_score_count1\G
*************************** 1. row ***************************
           Procedure: st_score_count1
            sql_mode:
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `st_score_count1`(out max_score int)
    READS SQL DATA
begin
    declare temp int;
    declare score_cursor cursor for select max(score) from student;
    declare exit handler for not found close score_cursor;    
    open score_cursor;
    repeat
            fetch score_cursor into temp;
            set max_score = temp;
    until 0 end repeat;
    close score_cursor;
end
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)
调用

mysql> call st_score_count1(@count);
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select @count;
+--------+
| @count |
+--------+
|     98 |
+--------+
1 row in set (0.00 sec)
删除
mysql> drop procedure st_score_count1;
Query OK, 0 rows affected (0.00 sec)