MySQL的查询数据

查询数据是指从数据库中获取所需的数据。查询数据是数据库中最常用,也是最重要的操作。用户可以根据自己的需求,使用不同的查询方式。


基本查询语句

select属性列表
from 表名和视图列表
where 条件表达式1
group by 属性名1 [having 条件表达式2]
order by 属性名2 [asc|desc]
\\显示员工表内的全部数据
mysql> select * from employee;
+-----+------+----------+------+-----+----------+
| num | d_id | name     | age  | sex | homeaddr |
+-----+------+----------+------+-----+----------+
|   1 | 1001 | zhangsan |   20 |   1 | beijing  |
|   2 | 1002 | lisi     |   24 |   0 | taiyuan  |
|   3 | 1004 | wangwu   |   29 |   1 | tianjin  |
|   4 | 1005 | zhaoliu  |   35 |   1 | tieling  |
|   5 | 1007 | maba     |   18 |   1 | luoyang  |
+-----+------+----------+------+-----+----------+
\\显示员工中小于25岁的人,并按num序号从大到小排列
mysql> select * from employee where age < 25 order by num desc;
+-----+------+----------+------+-----+----------+
| num | d_id | name     | age  | sex | homeaddr |
+-----+------+----------+------+-----+----------+
|   5 | 1007 | maba     |   18 |   1 | luoyang  |
|   2 | 1002 | lisi     |   24 |   0 | taiyuan  |
|   1 | 1001 | zhangsan |   20 |   1 | beijing  |
+-----+------+----------+------+-----+----------+
2 rows in set (0.00 sec)
\\查询指定字段,改变显示顺序,并按年龄排序
mysql> select age,name from employee order by age;
+------+----------+
| age  | name     |
+------+----------+
|   18 | maba     |
|   20 | zhangsan |
|   24 | lisi     |
|   29 | wangwu   |
|   35 | zhaoliu  |
+------+----------+
5 rows in set (0.00 sec)
\\查询指定记录,员工号大于等于1005的人员
mysql> select * from employee where d_id >= 1005;
+-----+------+---------+------+-----+----------+
| num | d_id | name    | age  | sex | homeaddr |
+-----+------+---------+------+-----+----------+
|   4 | 1005 | zhaoliu |   35 |   1 | tieling  |
|   5 | 1007 | maba    |   18 |   1 | luoyang  |
+-----+------+---------+------+-----+----------+
2 rows in set (0.00 sec)
\\带IN关键字的查询,将集合中的记录显示出来
mysql> select * from employee where num in(3,5);
+-----+------+--------+------+-----+----------+
| num | d_id | name   | age  | sex | homeaddr |
+-----+------+--------+------+-----+----------+
|   3 | 1004 | wangwu |   29 |   1 | tianjin  |
|   5 | 1007 | maba   |   18 |   1 | luoyang  |
+-----+------+--------+------+-----+----------+
\\带between and的范围查询
mysql> select age,name from employee where age between 15 and 25 order by age;
+------+----------+
| age  | name     |
+------+----------+
|   18 | maba     |
|   20 | zhangsan |
|   24 | lisi     |
+------+----------+
\\not是可选参数
mysql> select age,name from employee where age not between 15 and 25 order by age;
+------+---------+
| age  | name    |
+------+---------+
|   29 | wangwu  |
|   35 | zhaoliu |
+------+---------+
\\使用like字符匹配查询所有名字中带有a的人员
mysql> select num,name from employee where name like '%a%';
+-----+----------+
| num | name     |
+-----+----------+
|   1 | zhangsan |
|   3 | wangwu   |
|   4 | zhaoliu  |
|   5 | maba     |
+-----+----------+

\\可以使用is null来查询空值
\\使用and进行多条件查询,年龄小于25,名字带a的男性
mysql> select name,age,sex from employee where age between 15 and 25
    -> and sex = 1
    -> and name like '%a%';
+----------+------+-----+
| name     | age  | sex |
+----------+------+-----+
| zhangsan |   20 |   1 |
| maba     |   18 |   1 |
+----------+------+-----+
\\使用distinct来除重
mysql> select sex from employee;
+-----+
| sex |
+-----+
|   1 |
|   0 |
|   1 |
|   1 |
|   1 |
+-----+
mysql> select distinct sex from employee;
+-----+
| sex |
+-----+
|   1 |
|   0 |
+-----+
\\按组进行查询,并按组显示
mysql> select * from employee group by sex;
+-----+------+----------+------+-----+----------+
| num | d_id | name     | age  | sex | homeaddr |
+-----+------+----------+------+-----+----------+
|   2 | 1002 | lisi     |   24 |   0 | taiyuan  |
|   1 | 1001 | zhangsan |   20 |   1 | beijing  |
+-----+------+----------+------+-----+----------+
\\group by与group_contcat()函数一起使用,分组显示全部内容
mysql> select sex,group_concat(name) from employee group by sex;
+-----+------------------------------+
| sex | group_concat(name)           |
+-----+------------------------------+
|   0 | lisi                         |
|   1 | zhangsan,wangwu,zhaoliu,maba |
+-----+------------------------------+
\\group by与集合函数一起使用
mysql> select sex,count(sex) from employee group by sex;
+-----+------------+
| sex | count(sex) |
+-----+------------+
|   0 |          1 |
|   1 |          4 |
+-----+------------+
\\group by 与having一起使用
mysql> select sex,count(sex) from employee group by sex having count(sex)>3;
+-----+------------+
| sex | count(sex) |
+-----+------------+
|   1 |          4 |
+-----+------------+
\\where作用于表或者视图,是表和试图的查询条件。
\\having作用于分组后的记录,用于选择满足条件的组。
\\多个字段分组,第一个组相同,则按第二个组排序,同样1001的按年龄排
mysql> select d_id,group_concat(name),age from employee group by d_id,age;
+------+--------------------+------+
| d_id | group_concat(name) | age  |
+------+--------------------+------+
| 1001 | maba               |   18 |
| 1001 | zhangsan           |   20 |
| 1002 | lisi               |   24 |
| 1004 | wangwu             |   29 |
| 1005 | zhaoliu            |   35 |
+------+--------------------+------+
\\group by与with rollup进行汇总
mysql> select sex,count(sex) from employee group by sex with rollup;
+-----+------------+
| sex | count(sex) |
+-----+------------+
|   0 |          1 |
|   1 |          4 |
| NULL |          5 |
+-----+------------+
\\使用limit限制查询结果的数量
mysql> select * from employee limit 2;
+-----+------+----------+------+-----+----------+
| num | d_id | name     | age  | sex | homeaddr |
+-----+------+----------+------+-----+----------+
|   1 | 1001 | zhangsan |   20 |   1 | beijing  |
|   2 | 1002 | lisi     |   24 |   0 | taiyuan  |
+-----+------+----------+------+-----+----------+
2 rows in set (0.00 sec)
mysql> select * from employee limit 4;
+-----+------+----------+------+-----+----------+
| num | d_id | name     | age  | sex | homeaddr |
+-----+------+----------+------+-----+----------+
|   1 | 1001 | zhangsan |   20 |   1 | beijing  |
|   2 | 1002 | lisi     |   24 |   0 | taiyuan  |
|   3 | 1004 | wangwu   |   29 |   1 | tianjin  |
|   4 | 1005 | zhaoliu  |   35 |   1 | tieling  |
+-----+------+----------+------+-----+----------+
4 rows in set (0.00 sec)
\\指定初始位置
mysql> select * from employee limit 2,4;
+-----+------+---------+------+-----+----------+
| num | d_id | name    | age  | sex | homeaddr |
+-----+------+---------+------+-----+----------+
|   3 | 1004 | wangwu  |   29 |   1 | tianjin  |
|   4 | 1005 | zhaoliu |   35 |   1 | tieling  |
|   5 | 1001 | maba    |   18 |   1 | luoyang  |
+-----+------+---------+------+-----+----------+
3 rows in set (0.00 sec)

tips:

在MySQL5.7以后的group by中会直接报1055的错误。
原因是5.7后的版本默认开启only_full_group_by属性,需要在group by查询中使用any_value()函数。
解决的方法一个是改查询语句,一个是改配置文件。
sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
在[mysqld]字段新增一行配置
sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
然后,
sudo service mysql restart


使用集合函数查询

\\count()函数,统计函数
mysql> select count(*) from employee;
\\sum()函数,求和函数
mysql> select d_id,sum(age) from employee;
\\avg()函数,求平均值函数
mysql> select avg(age) from employee group by sex;
\\max()函数,求最大值函数
mysql> select max(age) from employee;
\\min()函数,求最小值函数
mysql> select min(age) from employee;

连接查询

\\内连接查询
mysql> select * from employee;
+-----+------+--------+-----+-----+----------------------------------+
| num | d_id | name   | age | sex | homeaddr                         |
+-----+------+--------+-----+-----+----------------------------------+
|   1 | 1001 | 张三   |  26 | 男  | 北京市海淀区学院路16号           |
|   2 | 1001 | 李四   |  24 | 女  | 北京市昌平区北七家镇             |
|   3 | 1004 | Aric   |  18 | 男  | England                          |
+-----+------+--------+-----+-----+----------------------------------+
3 rows in set (0.00 sec)
mysql> select * from department;
+------+-----------+--------------+---------------------+
| d_id | d_name    | function     | address             |
+------+-----------+--------------+---------------------+
| 1001 | 研发部    | 产品研发     | 3号楼5层            |
| 1002 | 生产部    | 产品制造     | 5号楼1层            |
| 1003 | 销售部    | 产品销售     | 1号楼销售大厅       |
+------+-----------+--------------+---------------------+
mysql> select num,name,employee.d_id,age,sex,d_name,function from employee,department where employee.d_id=department.d_id;
+-----+--------+------+-----+-----+-----------+--------------+
| num | name   | d_id | age | sex | d_name    | function     |
+-----+--------+------+-----+-----+-----------+--------------+
|   1 | 张三   | 1001 |  26 | 男  | 研发部    | 产品研发     |
|   2 | 李四   | 1001 |  24 | 女  | 研发部    | 产品研发     |
|   4 | 王五   | 1003 |  25 | 女  | 销售部    | 产品销售     |
mysql> select num,name,employee.d_id,age,sex,d_name,function from employee,department where employee.d_id=department.d_id;
+-----+--------+------+-----+-----+-----------+--------------+
| num | name   | d_id | age | sex | d_name    | function     |
+-----+--------+------+-----+-----+-----------+--------------+
|   1 | 张三   | 1001 |  26 | 男  | 研发部    | 产品研发     |
|   2 | 李四   | 1001 |  24 | 女  | 研发部    | 产品研发     |
|   4 | 王五   | 1003 |  25 | 女  | 销售部    | 产品销售     |
+-----+--------+------+-----+-----+-----------+--------------+
\\左连接查询 left join 表名 on 条件
mysql> select num,name,employee.d_id,age,sex,d_name,function from employee left join department  on employee.d_id=department.d_id;
\\右连接查询 right join 表名 on 条件
mysql> select num,name,employee.d_id,age,sex,d_name,function from employee right join department  on employee.d_id=department.d_id;

子查询

\\带in关键字的子查询
mysql> select * from employee where d_id in(select d_id from department);
+-----+------+--------+-----+-----+----------------------------------+
| num | d_id | name   | age | sex | homeaddr                         |
+-----+------+--------+-----+-----+----------------------------------+
|   1 | 1001 | 张三   |  26 | 男  | 北京市海淀区学院路16号           |
|   2 | 1001 | 李四   |  24 | 女  | 北京市昌平区北七家镇             |
|   4 | 1003 | 王五   |  25 | 女  | 北京市朝阳区双桥                 |
+-----+------+--------+-----+-----+----------------------------------+
\\not查询反向
mysql> select * from employee where d_id not in(select d_id from department);
+-----+------+------+-----+-----+----------+
| num | d_id | name | age | sex | homeaddr |
+-----+------+------+-----+-----+----------+
|   3 | 1004 | Aric |  18 | 男  | England  |
+-----+------+------+-----+-----+----------+


MySQL的触发器

触发器(Trigger)是由事件来触发某个操作。当数据库系统执行这些事件时,就会激活触发器执行相应的操作。

创建触发器

创建只有一个执行语句的触发器

\\create trigger 触发器名 before|after 触发事件 on 表名
\\      for each row 执行语句
mysql> create trigger user_trig1 before insert on user for each row insert into  trigger_time values(now());

mysql> insert into user(id,name,num,phone,address,age,sex) values(2,'su bowen',2,1861225,'tongzhou',4,1);
Query OK, 1 row affected (0.00 sec)
mysql> select * from user;
+----+----------+------+---------+----------+-----+------+
| id | name        | num  | phone   | address  | age | sex  |
+----+----------+------+---------+----------+-----+------+
|  1 | su jingxuan |    1 | 1861109 | beijing  |  35 |    1 |
|  2 | su bowen    |    2 | 1861225 | tongzhou |   4 |    1 |
mysql> select * from trigger_time;
+---------------------+
| exec_time           |
+---------------------+
| 2017-08-13 16:22:14 |
+---------------------+

创建有多个执行语句的触发器

一般情况下,MySQL默认使用;作为结束执行语句。在创建触发器过程中需要用到;。为了解决这个问题可以用DELIMITER语句。如"delimiter&&",可以将结束符号变成&&。当触发器创建完成之后,可以用命令delimiter;来将结束符号还原。

mysql> delimiter&&
mysql> create trigger user_trig2 after delete on user for each row begin insert into trigger_time values('21:00:01'); insert into trigger_time values('22:00:02'); end&&
mysql>delimiter ;
mysql> create table trigger_time(exec_time time);
mysql> delete from user where id=2;
mysql> select * from trigger_time;
+-----------+
| exec_time |
+-----------+
| 21:00:01  |
| 22:00:02  |
+-----------+
mysql> select * from user;                                                            
+----+-------------+------+---------+---------+-----+------+
| id | name        | num  | phone   | address | age | sex  |
+----+-------------+------+---------+---------+-----+------+
|  1 | su jingxuan |    1 | 1861109 | beijing |  35 |    1 |
+----+-------------+------+---------+---------+-----+------+

查看触发器

mysql> show triggers\G
*************************** 1. row ***************************
             Trigger: user_trig1
               Event: INSERT
               Table: user
           Statement: insert into  trigger_time values(now())
              Timing: BEFORE
             Created: 2017-08-13 16:20:33.95
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
             Definer: sujx@%
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
*************************** 2. row ***************************
             Trigger: user_trig2
               Event: DELETE
               Table: user
           Statement: begin
insert into trigger_time values('21:00:01');
insert into trigger_time values('22:00:02');
end
              Timing: AFTER
             Created: 2017-08-13 16:30:24.44
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
             Definer: sujx@%
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
2 rows in set (0.00 sec)
\\可以在trigger表中查看触发器信息
mysql> select * from information_schema.triggers\G

触发器的使用

在MySQL中,触发器执行的顺序是before触发器、表操作、after触发器。

删除触发器

mysql> drop trigger user_trig1;

MySQL的视图

视图是从过一个或者多个表中导出来的表,是一种虚拟存在的表。试图可以使用用户的操作更方便,并且可以保障数据库系统的安全。

简介

视图可以从已经存在的视图基础上定义。数据库中存放了视图的定义,而并没有视图中的数据。使用视图查询数据时,数据库系统会从原来的表中取出对应的数据。视图的数据以来于原来表中的数据。
MySQL的视图不支持输入参数的功能。

作用

  1. 操作简单化
  2. 增加数据的安全性
  3. 提高表的逻辑独立性

创建视图

创建视图是指在已存在的数据库表上建立视图。视图可以建立在一张表中,也可以建立在多张表中。
创建视图时,需要有create view的权限。同时,应该具有查询设计的列的select权限。在MySQL数据库下面的user表中保存这些权限的信息,可以使用select查询。
algorithem,表示试图选择的算法,包括undefined、merge和temptable。undefined表示自动选择算法,merge表示合并,temptable表示存入临时表。
cascaded是可选参数,表示更新试图要满足所有视图和表的条件,为默认值。local表示更新视图时,要满足该试图本身的条件即可。

\\在已有表上创建视图user_view1
mysql> create view user_view1 as select * from user;
mysql> desc user;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | NO   | PRI | NULL    |       |
| name    | varchar(30) | YES  | UNI | NULL    |       |
| num     | int(8)      | YES  |     | NULL    |       |
| phone   | varchar(20) | YES  |     | NULL    |       |
| address | varchar(40) | NO   | MUL | NULL    |       |
| age     | int(4)      | NO   |     | NULL    |       |
| sex     | tinyint(1)  | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
mysql> desc user_view1;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | NO   |     | NULL    |       |
| name    | varchar(30) | YES  |     | NULL    |       |
| num     | int(8)      | YES  |     | NULL    |       |
| phone   | varchar(20) | YES  |     | NULL    |       |
| address | varchar(40) | NO   |     | NULL    |       |
| age     | int(4)      | NO   |     | NULL    |       |
| sex     | tinyint(1)  | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
\\两者显示出来的表的结果是一样的
\\在user表中创建只包含姓名、性别、年龄的视图
mysql> create view user_view2(name,sex,age) as select name,sex,age from user;
mysql> desc user_view2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(30) | YES  |     | NULL    |       |
| sex   | tinyint(1)  | YES  |     | NULL    |       |
| age   | int(4)      | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
\\在多个表中建立跨表的视图
mysql> create algorithm=merge view stu_view( name,age,sex,stu_id,grade) as select name,age,sex,stu_id,grade from user,example2 where user.id = example2.id with local check option;
mysql> desc stu_view;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| name   | varchar(30) | YES  |     | NULL    |       |
| age    | int(4)      | NO   |     | NULL    |       |
| sex    | tinyint(1)  | YES  |     | NULL    |       |
| stu_id | int(11)     | NO   |     | NULL    |       |
| grade  | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

查看视图

查看视图是指查看数据库中已存在的视图的定义。查看视图必须要有show view的权限。

\\使用describe语句
mysql> desc stu_view;
\\使用show table status语句查看视图基本信息,注意like参数和单引号''
\\stu_view是虚拟表,所以属性值大多为空
mysql> show table status like 'stu_view'\G
*********** 1. row ********
           Name: stu_view
         Engine: NULL
        Version: NULL
     Row_format: NULL
           Rows: NULL
 Avg_row_length: NULL
    Data_length: NULL
Max_data_length: NULL
   Index_length: NULL
      Data_free: NULL
 Auto_increment: NULL
    Create_time: NULL
    Update_time: NULL
     Check_time: NULL
      Collation: NULL
       Checksum: NULL
 Create_options: NULL
        Comment: VIEW
1 row in set (0.00 sec)
\\使用show create view查看视图详细信息
mysql> show create view stu_view\G
*************** 1. row **************
                View: stu_view
         Create View: CREATE ALGORITHM=MERGE DEFINER=`sujx`@`%` SQL SECURITY DEFINER VIEW `stu_view` AS select `user`.`name` AS `name`,`user`.`age` AS `age`,`user`.`sex` AS `sex`,`example2`.`stu_id` AS `stu_id`,`example2`.`grade` AS `grade` from (`user` join `example2`) where (`user`.`id` = `example2`.`id`) WITH LOCAL CHECK OPTION
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)
\\在系统views表中查看视图详细信息
\\在MySQL中,所有视图的定义都存在information_schema数据库下的views表中。
mysql> select * from information_schema.views\G
*************** 1. row **************
       TABLE_CATALOG: def
        TABLE_SCHEMA: sujx
          TABLE_NAME: stu_view
     VIEW_DEFINITION: select `sujx`.`user`.`name` AS `name`,`sujx`.`user`.`age` AS `age`,`sujx`.`user`.`sex` AS `sex`,`sujx`.`example2`.`stu_id` AS `stu_id`,`sujx`.`example2`.`grade` AS `grade` from `sujx`.`user` join `sujx`.`example2` where (`sujx`.`user`.`id` = `sujx`.`example2`.`id`)
        CHECK_OPTION: LOCAL
        IS_UPDATABLE: YES
             DEFINER: sujx@%
       SECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
**************** 2. row ***************
       TABLE_CATALOG: def
        TABLE_SCHEMA: sujx
          TABLE_NAME: user_view1
     VIEW_DEFINITION: select `sujx`.`user`.`id` AS `id`,`sujx`.`user`.`name` AS `name`,`sujx`.`user`.`num` AS `num`,`sujx`.`user`.`phone` AS `phone`,`sujx`.`user`.`address` AS `address`,`sujx`.`user`.`age` AS `age`,`sujx`.`user`.`sex` AS `sex` from `sujx`.`user`
        CHECK_OPTION: NONE
        IS_UPDATABLE: YES
             DEFINER: sujx@%
       SECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
************** 3. row **************
       TABLE_CATALOG: def
        TABLE_SCHEMA: sujx
          TABLE_NAME: user_view2
     VIEW_DEFINITION: select `sujx`.`user`.`name` AS `name`,`sujx`.`user`.`sex` AS `sex`,`sujx`.`user`.`age` AS `age` from `sujx`.`user`
        CHECK_OPTION: NONE
        IS_UPDATABLE: YES
             DEFINER: sujx@%
       SECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
3 rows in set (0.01 sec)

修改视图

修改视图是指修改数据库中存在的表的定义。

\\使用create or replace view语句修改视图
mysql> create or replace algorithm=temptable view stu_view(name,sex,grade) as select name,sex,grade from user,example2 where user.id = example2.id;
Query OK, 0 rows affected (0.01 sec)

mysql> desc stu_view;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(30) | YES  |     | NULL    |       |
| sex   | tinyint(1)  | YES  |     | NULL    |       |
| grade | float       | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
\\alter语句修改视图
mysql> alter view stu_view(stu_id,name,sex,grade) as select stu_id,name,sex,grade from user,example2 where user.id=example2.id;
mysql> desc stu_view;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| stu_id | int(11)     | NO   |     | NULL    |       |
| name   | varchar(30) | YES  |     | NULL    |       |
| sex    | tinyint(1)  | YES  |     | NULL    |       |
| grade  | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

更新视图

更新视图是指通过视图来插入、更新和删除表中的数据。因为视图是一个虚拟表,其中没有数据。通过视图来更新时,都是转换到基本表来更新。更新视图时,只能更新权限范围内的数据。超过范围,就不能更新。

删除视图

mysql> drop view if exists stu_view;

MySQL的索引

索引是一种特殊的数据结构,用来快速查询数据库表中的特定记录。MySQL中所有的数据类型都可以被索引。MySQL的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引。

简介

索引由数据库表中的一列或多列组合而成,其作用是提高对表中数据的查询速度。

索引是创建在表上的,是对数据库表中一列或多列的值进行排序的一种结构。索引可以提高查询的速度。通过索引,查询数据时可以不必读完记录的所有信息,而只是查询所有引列。

不同的存储引擎定义了每个表的最大索引数和最大索引长度。
索引的优点是可以提高检索数据的速度。缺点是创建和维护索引需要耗费时间和物理空间;增删查改要动态维护索引,造成数据维护速度降低。

索引可以提高查询速度,但会影响写入速度。插入大量数据的时候,最好的方式是先删除表中的索引,然后插入数据。写入完成之后,再创建索引。

分类

  1. 普通索引
    创建普通索引时,不附加任何限制条件,其值由字段本身的完整性约束条件决定。
  2. 唯一性索引
    使用unique参数设置索引为唯一性索引。主键就是一种特殊的唯一性索引。
  3. 全文索引
    使用FULLTEXT参数设置为全文索引。只能建立在char、varchar或者text类型的字段上。默认不支持区分大小写,只有使用二进制排列之后可以执行区分大小写的全文索引。
  4. 单列索引
    在单个字段上创建索引,只根据该字段进行索引。
  5. 多列索引
    在多个字段上创建索引。
  6. 空间索引
    使用spatial参数设置索引为空间索引。只有MyISAM引擎支持空间索引。

设计原则

  1. 选择唯一性索引
  2. 为经常需要排序、分组和联合操作的字段建立索引
  3. 为常作为查询条件的字段建立索引
  4. 限制索引的数目
  5. 尽量使用数据量少的索引
  6. 尽量使用前缀来索引
  7. 删除不在使用或者很少使用的索引

创建索引

\\创建表的时候创建索引
mysql> create table index1(id int, name varchar(20), sex boolean, index(id));
mysql> show create table index1\G
*************************** 1. row ***************************
       Table: index1
Create Table: CREATE TABLE `index1` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `sex` tinyint(1) DEFAULT NULL,
  KEY `id` (`id`)   //索引被创建
) ENGINE=InnoDB DEFAULT CHARSET=latin1
\\查询索引情况
mysql> explain select * from index1 where id=1\G

**** 1. row ******
           id: 1
  select_type: SIMPLE
        table: index1
   partitions: NULL
         type: ref
possible_keys: id   //索引被使用
          key: id   //索引字段是id
      key_len: 5
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
\\创建唯一性索引
mysql> create table index2(id int unique, name varchar(20), unique index index2_id(id asc));

mysql> show create table index2\G
*************************** 1. row ***************************
       Table: index2
Create Table: CREATE TABLE `index2` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  UNIQUE KEY `id` (`id`),
  UNIQUE KEY `index2_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
\\创建全文索引,注意只有MyISAM支持
mysql> create table index3(id int unique,
    -> info varchar(20),
    -> fulltext index index3_info(info))
    -> engine=MyISAM;

mysql> show create table index3\G
**** 1. row *****
       Table: index3
Create Table: CREATE TABLE `index3` (
  `id` int(11) DEFAULT NULL,
  `info` varchar(20) DEFAULT NULL,
  UNIQUE KEY `id` (`id`),
  FULLTEXT KEY `index3_info` (`info`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

\\创建单列索引
mysql> create table index4(id int, subject varchar(40), index index4_st(subject(10)) );
\\对于字符型的数据,可以不用查询全部信息,而只查询前面若干字符信息
mysql> show create table index4\G
***** 1. row ******
       Table: index4
Create Table: CREATE TABLE `index4` (
  `id` int(11) DEFAULT NULL,
  `subject` varchar(40) DEFAULT NULL,
  KEY `index4_st` (`subject`(10))
) ENGINE=InnoDB DEFAULT CHARSET=latin1

\\创建多列索引
mysql> create table index5(id int, name varchar(20), sex char(4), index index5_ns(name,sex));
mysql> show create table index5\G
********* 1. row **********
       Table: index5
Create Table: CREATE TABLE `index5` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `sex` char(4) DEFAULT NULL,
  KEY `index5_ns` (`name`,`sex`)    \\索引建立
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
\\查询索引情况
mysql> explain select * from index5 where name='hjh'\G
************* 1. row *****
           id: 1
  select_type: SIMPLE
        table: index5
   partitions: NULL
         type: ref
possible_keys: index5_ns
          key: index5_ns
      key_len: 23
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
\\使用多列索引要注意,只有使用了索引中的第一个字段才会触发索引。如果没有使用索引中的第一个字段,那么这个多列索引就不会起作用。
mysql> explain select * from index5 where sex='n'\G
********** 1. row **********
           id: 1
  select_type: SIMPLE
        table: index5
   partitions: NULL
         type: ALL
possible_keys: NULL     //没有起作用
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where
\\在已有表上建立索引
mysql> create index index7_id on user(id);
mysql> show create table user\G
********* 1. row *******
       Table: user
Create Table: CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `name` varchar(30) DEFAULT NULL,
  `num` int(8) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `address` varchar(40) NOT NULL,
  `age` int(4) NOT NULL,
  `sex` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index7_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

\\创建唯一性索引
mysql> create unique index index_name8 on user(name);
mysql> show create table user\G
************** 1. row **************
       Table: user
Create Table: CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `name` varchar(30) DEFAULT NULL,
  `num` int(8) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `address` varchar(40) NOT NULL,
  `age` int(4) NOT NULL,
  `sex` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_name8` (`name`),    //以那么为唯一性索引
  KEY `index7_id` (`id`)    //id为普通索引
) ENGINE=InnoDB DEFAULT CHARSET=latin1

\\建立多列索引
mysql> create index index11_na on user(name,address);
mysql> show create table user\G
************ 1. row *************
       Table: user
Create Table: CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `name` varchar(30) DEFAULT NULL,
  `num` int(8) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `address` varchar(40) NOT NULL,
  `age` int(4) NOT NULL,
  `sex` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),       //主键
  UNIQUE KEY `index_name8` (`name`),    //唯一性索引
  KEY `index7_id` (`id`),       //普通索引
  KEY `index10_addr` (`address`),   //单列索引
  KEY `index11_na` (`name`,`address`)   //多列索引
) ENGINE=InnoDB DEFAULT CHARSET=latin1

\\使用alter table来创建索引
mysql> alter table user add index index13_phone(phone);
mysql> show create table user\G
********** 1. row ************
       Table: user
Create Table: CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `name` varchar(30) DEFAULT NULL,
  `num` int(8) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `address` varchar(40) NOT NULL,
  `age` int(4) NOT NULL,
  `sex` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_name8` (`name`),
  KEY `index7_id` (`id`),
  KEY `index10_addr` (`address`),
  KEY `index11_na` (`name`,`address`),
  KEY `index13_phone` (`phone`)     //使用alter table新增索引
) ENGINE=InnoDB DEFAULT CHARSET=latin1

\\删除索引
mysql> drop index index13_phone on user;
mysql> show create table user\G
*********** 1. row ******
       Table: user
Create Table: CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `name` varchar(30) DEFAULT NULL,
  `num` int(8) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `address` varchar(40) NOT NULL,
  `age` int(4) NOT NULL,
  `sex` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_name8` (`name`),
  KEY `index7_id` (`id`),
  KEY `index10_addr` (`address`),
  KEY `index11_na` (`name`,`address`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

MySQL表的操作

表示数据库存储数据的基本单位。一个表包含若干个字段或者记录。
表的操作包括创建新表、修改表和删除表。这些操作是数据库管理中最基本,也是最重要的操作。

创建表

表名不能是SQL语言的关键字
create table 表名 (属性名 数据类型 [完整性约束条件],属性名 数据类型 [完整性约束条件]);

约束条件说明
Primary Key主键,唯一的标识对应的元组
Foreign Key外键,与之联系的其他表的主键
NOT NULL属性值非空
UNIQE属性值唯一
Auto_increment属性值自动增加
DEFAULT属性默认值

查看表结构

mysql>describe 表名           //查看表的基本定义
mysql>desc 表名
mysql>show create table 表名      //查看表的详细结构
//建立学生信息表并查看表
mysql> create table example1(stu_id int primary key, stu_name varchar(20), stu_sex boolean);

mysql> desc example1;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| stu_id   | int(11)     | NO   | PRI | NULL    |       |
| stu_name | varchar(20) | YES  |     | NULL    |       |
| stu_sex  | tinyint(1)  | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

//建立课业学习表,并设置学生ID和课程ID为主键,查看新表的详细信息
mysql> create table example2(stu_id int, course_id int, grade float, primary key(stu_id,course_id));

mysql> show create table example2\G
*************************** 1. row ***************************
       Table: example2
Create Table: CREATE TABLE `example2` (
  `stu_id` int(11) NOT NULL,
  `course_id` int(11) NOT NULL,
  `grade` float DEFAULT NULL,
  PRIMARY KEY (`stu_id`,`course_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

//设置新表,将上个表为父表,将上表主键设置为外键
mysql>create table example3(id int primary key, stu_id int, course_id int, constraint c_fk foreign key(stu_id,course_id) references example2(stu_id,course_id));
mysql> desc example3;
+-----------+---------+------+-----+---------+-------+
| Field     | Type    | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| id        | int(11) | NO   | PRI | NULL    |       |
| stu_id    | int(11) | YES  | MUL | NULL    |       |
| course_id | int(11) | YES  |     | NULL    |       |
+-----------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)

修改表

修改表包括修改表名、字段数据类型、字段名、增加/删除字段、修改字段的排列位置、更改默认存储引擎和删除表的外键约束。

mysql>create table example0(id int primary key, name varchar(20) not null, sex tinyint(1));
\\修改表名
msyql>alter table example0 rename user;
\\修改数据类型
mysql>alter table user modify name varchar(30);
\\修改字段名
mysql>alter table example1 change stu_name name varchar(30);
mysql>desc example1;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| stu_id  | int(11)     | NO   | PRI | NULL    |       |
| name    | varchar(30) | YES  |     | NULL    |       |
| stu_sex | tinyint(1)  | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
\\使用change同时修改字段名和数据类型
mysql>alter table example1 change stu_sex sex int(2);
mysql>desc example1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| stu_id | int(11)     | NO   | PRI | NULL    |       |
| name   | varchar(30) | YES  |     | NULL    |       |
| sex    | int(2)      | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
\\增加字段并设置数据类型
mysql>alter table user add age int(4) not null;
\\新增字段并设置新增字段的位置
mysql>alter table user add num int(8) primary key first;
\\指定位置新增字段
mysql>alter table user add address varchar(40) not null after phone;
mysql> desc user;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| num     | int(8)      | YES  |     | NULL    |       |
| id      | int(11)     | NO   | PRI | NULL    |       |
| name    | varchar(30) | YES  |     | NULL    |       |
| sex     | tinyint(1)  | YES  |     | NULL    |       |
| phone   | varchar(20) | YES  |     | NULL    |       |
| address | varchar(40) | NO   |     | NULL    |       |
| age     | int(4)      | NO   |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
\\删除字段
mysql> alter table user drop id;
\\修改现有字段的位置 
mysql> alter table user modify name varchar(30) first;
mysql> alter table user modify sex tinyint(1) after age;
mysql> desc user;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(30) | YES  |     | NULL    |       |
| num     | int(8)      | YES  |     | NULL    |       |
| phone   | varchar(20) | YES  |     | NULL    |       |
| address | varchar(40) | NO   |     | NULL    |       |
| age     | int(4)      | NO   |     | NULL    |       |
| sex     | tinyint(1)  | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
\\修改表的存储引擎
mysql> alter table user engine=myisam;
\\删除外键
mysql> alter table example3 drop foreign key c_fk;
\\删除表
mysql> drop table example5;