MySQL-查询&权限&索引&约束

一.mysql查询语句

1.指定查询条件
    select      用来过滤字段的
    from        指定从哪些表中查询数据
    where       用来过滤行的

    查询ula及格的人的姓名和ula成绩
        mysql> select sname,ula from score where ula>=60;
            +-------+------+
            | sname | ula  |
            +-------+------+
            | tom   |   80 |
            | mary  |   75 |
            | mike  |  100 |
            +-------+------+
            3 rows in set (0.00 sec)
    查询ula和ule都及格的人的姓名和两科成绩
        mysql> select sname,ula,ule from score where ula>=60 and ule>=60;
                +-------+------+------+
                | sname | ula  | ule  |
                +-------+------+------+
                | tom   |   80 |   70 |
                | mike  |  100 |   90 |
                +-------+------+------+
                2 rows in set (0.00 sec)
    查询有任何一科不及格的人的全部信息
        mysql> select * from score where ula<60 or ule<60 or uoa<60;
            +------+-------+------+------+------+
            | sno  | sname | ule  | ula  | uoa  |
            +------+-------+------+------+------+
            |    2 | mary  |   55 |   75 |   65 |
            |    3 | jack  |   75 |   45 |   95 |
            +------+-------+------+------+------+

    查询ula的成绩在75到80之间的人
        mysql> select * from score where ula >= 75 and ula <= 80;         //更加灵活
            +------+-------+------+------+------+
            | sno  | sname | ule  | ula  | uoa  |
            +------+-------+------+------+------+
            |    2 | mary  |   55 |   75 |   65 |
            |    1 | tom   |   70 |   80 |   90 |
            +------+-------+------+------+------+
            2 rows in set (0.00 sec)
        或

        mysql> select * from score where ula between 75 and 80;     //between   and  包含边界值
            +------+-------+------+------+------+
            | sno  | sname | ule  | ula  | uoa  |
            +------+-------+------+------+------+
            |    2 | mary  |   55 |   75 |   65 |
            |    1 | tom   |   70 |   80 |   90 |
            +------+-------+------+------+------+
            2 rows in set (0.00 sec)

2.模糊查询
    like  
    使用通配符
    _    ： 匹配单个字符
    %   ： 匹配0个或者多个字符

   查询名字是三个字符的人的全部信息
        mysql> select sname from score where sname like '___';     //三个下划线
                +-------+
                | sname |
                +-------+
                | tom   |
                +-------+
                1 row in set (0.00 sec)
        mysql> select * from score where sname like 'j%';    //名字以j开头的
            +------+-------+------+------+------+
            | sno  | sname | ule  | ula  | uoa  |
            +------+-------+------+------+------+
            |    3 | jack  |   75 |   45 |   95 |
            +------+-------+------+------+------+
            1 row in set (0.00 sec)
        mysql> select * from score where sname like '%k';    //名字以k结尾的
        mysql> select * from score where sname like '%m%';   //名字中含有m的
            +------+-------+------+------+------+
            | sno  | sname | ule  | ula  | uoa  |
            +------+-------+------+------+------+
            |    1 | tom   |   70 |   80 |   90 |
            |    2 | mary  |   55 |   75 |   65 |
            |    4 | mike  |   90 |  100 |   86 |
            +------+-------+------+------+------+
            3 rows in set (0.00 sec)

        mysql> show variables;     //查看变量值
        mysql> show variables like 'da%';    //查看变量名以da开头的变量的值
            +-----------------+-------------------+
            | Variable_name   | Value             |
            +-----------------+-------------------+
            | datadir         | /data/mysql/      |
            | date_format     | %Y-%m-%d          |
            | datetime_format | %Y-%m-%d %H:%i:%s |
            +-----------------+-------------------+
            3 rows in set (0.00 sec)

3.分组       group by 字段名
    mysql> alter table score add class int;
    mysql> insert into score values(5,'rose',75,85,95,2);
    mysql> update score set class=1 where sno=1;
    mysql> update score set class=1 where sno=3;
       或
      mysql> update score set class=1 where sno in (1,3);  //与上两条语句作用一致，都是sno为1,3时，将class修改为1

    mysql> update score set class=2 where sno=2 or sno=4;
    mysql> select * from score;
        +------+-------+------+------+------+-------+
        | sno  | sname | ule  | ula  | uoa  | class |
        +------+-------+------+------+------+-------+
        |    1 | tom   |   70 |   80 |   90 |     1 |
        |    2 | mary  |   55 |   75 |   65 |     2 |
        |    3 | jack  |   75 |   45 |   95 |     1 |
        |    4 | mike  |   90 |  100 |   86 |     2 |
        |    5 | rose  |   75 |   85 |   95 |     2 |
        +------+-------+------+------+------+-------+
    显示每个班的ule的总成绩和平均成绩（默认为升序排列）
        mysql> select class,sum(ule),avg(ule) from score group by class;
        +-------+----------+----------+
        | class | sum(ule) | avg(ule) |
        +-------+----------+----------+
        |     1 |      145 |  72.5000 |
        |     2 |      220 |  73.3333 |
        +-------+----------+----------+
        2 rows in set (0.02 sec)

    降序显示每个班的ule的总成绩和平均成绩        
    mysql> select class,sum(ule),avg(ule) from score group by class desc;
        +-------+----------+----------+
        | class | sum(ule) | avg(ule) |     
        +-------+----------+----------+
        |     2 |      220 |  73.3333 |
        |     1 |      145 |  72.5000 |
        +-------+----------+----------+


    注意：group by 里面带条件的话，不能使用where；可以与having连用
        显示班级人数大于2的班级的ule的总成绩和平均成绩
            mysql> select class,sum(ule),avg(ule) from score group by class having count(*)>2;
                +-------+----------+----------+
                | class | sum(ule) | avg(ule) |
                +-------+----------+----------+
                |     2 |      220 |  73.3333 |
                +-------+----------+----------+
                1 row in set (0.00 sec)


     where与hvaing区别：
         where      分组前进行数据过滤   不可以使用聚合函数
         hvaing     分组后进行数据过滤   多数需使用聚合函数

4.排序      order by 
     order by 字段名[,字段名2,......] [asc|desc]
          asc：升序，默认的排序方式             ascend：上升的
          desc：降序                                  descend：下降的
     按照ule的成绩由低到高的顺序显示学生姓名及ule成绩
            mysql> select sname,ule from score order by ule;
     按照ule的成绩由高到低的顺序显示学生姓名及ule成绩
            mysql> select sname,ule from score order by ule desc;
                +-------+------+
                | sname | ule  |
                +-------+------+
                | mike  |   90 |
                | jack  |   75 |
                | rose  |   75 |
                | tom   |   70 |
                | mary  |   55 |
                +-------+------+
                5 rows in set (0.00 sec)
     order by 编号
        mysql> select sname,ule from score order by 2 desc;

    order by 多个字段
         按照ule成绩排序，如果成绩相同，按照名字的倒序排序
        mysql> select sname,ule from score order by ule;     //按ule进行升序排列
        +-------+------+
        | sname | ule  |
        +-------+------+
        | mary  |   55 |
        | tom   |   70 |
        | jack  |   75 |
        | rose  |   75 |
        | mike  |   90 |
        +-------+------+
        5 rows in set (0.00 sec)

        mysql> select sname,ule from score order by ule,sname desc; //按ule进行升序排列，如ule相同则按sname倒序排列
        +-------+------+
        | sname | ule  |
        +-------+------+
        | mary  |   55 |
        | tom   |   70 |
        | rose  |   75 |
        | jack  |   75 |
        | mike  |   90 |
        +-------+------+
        5 rows in set (0.00 sec)



        按照ule成绩倒序排序，如果成绩相同，按照名字的倒序排序
           mysql> select sname,ule from score order by ule desc,sname desc;

        mysql> select sname,ule from score order by ule desc;
        +-------+------+
        | sname | ule  |
        +-------+------+
        | mike  |   90 |
        | jack  |   75 |
        | rose  |   75 |
        | tom   |   70 |
        | mary  |   55 |
        +-------+------+
        5 rows in set (0.00 sec)

        mysql> select sname,ule from score order by ule desc,sname desc;
        +-------+------+
        | sname | ule  |
        +-------+------+
        | mike  |   90 |
        | rose  |   75 |
        | jack  |   75 |
        | tom   |   70 |
        | mary  |   55 |
        +-------+------+
        5 rows in set (0.00 sec)

5.限制输出     limit
    limit n        只显示查询结果的前n条
        显示ule成绩前3名的学生姓名及ule成绩
            mysql> select sname,ule from score order by ule desc limit 3;
                +-------+------+
                | sname | ule  |
                +-------+------+
                | mike  |   90 |
                | jack  |   75 |
                | rose  |   75 |
                +-------+------+
                3 rows in set (0.00 sec)
    limit m,n          从m+1行开始，显示n行
        mysql> select sname,ule from score limit 2,2;   //显示第3行和第4行

6.子查询
    也叫嵌套查询，将里层查询的结果作为外层查询的条件
    查询ula成绩最高的人的学号，姓名以及ula的成绩
        mysql> select sno,sname,ula from score where ula=(select max(ula) from score);
            +------+-------+------+
            | sno  | sname | ula  |
            +------+-------+------+
            |    4 | mike  |  100 |
            +------+-------+------+
            1 row in set (0.00 sec)

7.SQL语句的执行顺序如下：
    from  where  (group by)  having  select  (order by)  limit



8、多表连接查询

    mysql> select * from score;
    +------+-------+------+------+------+-------+
    | sno  | sname | ule  | ula  | uoa  | class |
    +------+-------+------+------+------+-------+
    |    4 | mike  |   90 |  100 |   86 |     2 |
    |    3 | jack  |   75 |   45 |   95 |     1 |
    |    2 | mary  |   55 |   75 |   65 |     2 |
    |    1 | tom   |   70 |   80 |   90 |     1 |
    |    5 | rose  |   75 |   85 |   95 |     2 |
    +------+-------+------+------+------+-------+
    5 rows in set (0.00 sec)

    mysql> select * from course;
    +------+-----------+-------+
    | sno  | classname | score |
    +------+-----------+-------+
    |    1 | yuwen     |    56 |
    |    2 | shuxue    |    88 |
    |    3 | yingyu    |    85 |
    |    6 | gaoshu    |    99 |
    +------+-----------+-------+


1）自然连接
第一种写法：
mysql> select score.sno,score.sname,course.classname,course.score from score,course where core.sno=course.sno;      
第二种写法：
mysql> select score.sno,score.sname,course.classname,course.score from score inner join  course on score.sno=course.sno;
第三种写法（给表起个别名）：
mysql> select S.sno,S.sname,C.classname,C.score from score S,course C where S.sno=C.sno;
+------+-------+-----------+-------+
| sno  | sname | classname | score |
+------+-------+-----------+-------+
|    3 | jack  | yingyu    |    85 |
|    2 | mary  | shuxue    |    88 |
|    1 | tom   | yuwen     |    56 |
+------+-------+-----------+-------+


2）左外连接
  语法：
    select * from 表1 left join 表2 on 表1.字段=表2.字段

mysql> select S.sno,S.sname,C.sno,C.classname from score S left join course C on S.sno=C.sno;
+------+-------+------+-----------+
| sno  | sname | sno  | classname |
+------+-------+------+-----------+
|    4 | mike  | NULL | NULL      |
|    3 | jack  |    3 | yingyu    |
|    2 | mary  |    2 | shuxue    |
|    1 | tom   |    1 | yuwen     |
|    5 | rose  | NULL | NULL      |
+------+-------+------+-----------+

3）右外连接
   语法：
    select * from 表1 right join 表2 on 表1.字段=表2.字段

mysql> select S.sno,S.sname,C.sno,C.classname from score S right join course C on S.sno=C.sno;
+------+-------+------+-----------+
| sno  | sname | sno  | classname |
+------+-------+------+-----------+ 
|    1 | tom   |    1 | yuwen     |
|    2 | mary  |    2 | shuxue    |
|    3 | jack  |    3 | yingyu    |
| NULL | NULL  |    6 | gaoshu    |
+------+-------+------+-----------+

二.用户和权限管理

用户和权限管理

Information about account privileges is stored in the user, db, host, tables_priv, columns_priv, and procs_priv tables in the mysql database. The MySQL server reads the contents of these tables into memory when it starts and reloads them under the circumstances. Access-control decisions are based on the in-memory copies of the grant tables.

user: Contains user accounts, global privileges, and other non-privilege columns.
user: 用户帐号、全局权限

db: Contains database-level privileges.
db: 库级别权限

host: Obsolete.
host: 废弃

tables_priv: Contains table-level privileges.
表级别权限

columns_priv: Contains column-level privileges.
列级别权限

procs_priv: Contains stored procedure and function privileges.
存储过程和存储函数相关的权限

proxies_priv: Contains proxy-user privileges.
代理用户权限

There are several distinctions between the way user names and passwords are used by MySQL and the way they are used by your operating system:

User names, as used by MySQL for authentication purposes, have nothing to do with user names (login names) as used by Windows or Unix.
MySQL user names can be up to 16 characters long.
The server uses MySQL passwords stored in the user table to authenticate client connections using MySQL native authentication (against passwords stored in the mysql.user table).
MySQL encrypts passwords stored in the user table using its own algorithm. This encryption is the same as that implemented by the PASSWORD() SQL function but differs from that used during the Unix login process. 
It is possible to connect to the server regardless of character set settings if the user name and password contain only ASCII characters. 

用户帐号：
用户名@主机
用户名：16字符以内
主机：
主机名：www.magedu.com, mysql
IP: 172.16.10.177
网络地址:
172.16.0.0/255.255.0.0

        通配符：%，_
            172.16.%.%
            %.magedu.com

--skip-name-resolve

权限级别：
全局级别： SUPER、
库
表: DELETE, ALTER, TRIGGER
列: SELECT, INSERT, UPDATE
存储过程和存储函数

CREATE USER username@host [IDENTIFIED BY ‘password’]

GRANT

GRANT ALL PRIVILEGES ON [object_type] db.* TO username@’%’;

object_type可以为：
TABLE
| FUNCTION
| PROCEDURE

GRANT EXECUTE ON FUNCTION db.abc TO username@’%’;

INSERT INTO mysql.user
mysql> FLUSH PRIVILEGES;

SHOW GRANTS FOR ‘username@host’;

GRANT OPTION 可以将自己获取的权限授予给其他用户
| MAX_QUERIES_PER_HOUR count 每小时最多的查询数量
| MAX_UPDATES_PER_HOUR count 每小时最多的更新数量
| MAX_CONNECTIONS_PER_HOUR count 每小时最大连接数
| MAX_USER_CONNECTIONS count 最多用户连接数

MySQL中的用户存在哪张表中
mysql.user

mysql> select user,host,password from mysql.user;

如果root密码忘记了，该怎么办？
    破解root密码前提，就是你能够停止mysql服务。
1）停止服务
    [root@mysql ~]# /etc/init.d/mysqld stop
2）使用跳过授权表的方式启动数据库
    [root@mysql ~]# mysqld_safe --skip-grant-tables --user=mysql &
3）匿名登录
    [root@mysql ~]# mysql
4）修改user表
    mysql> use mysql;
        Database changed
    mysql> select user,host,password from user;
    mysql> update user set password=password("redhat") where user='root' and host='localhost';
          使用password()函数去生成新密码的加密字符串
5）重启mysql服务
    [root@mysql mysql]# mysqladmin shutdown     //正常情况下使用mysqld_safe启动的服务需要这么关闭
    [root@mysql mysql]# ps -ef | grep mysqld
        root     25049 16794  0 11:39 pts/0    00:00:00 grep mysqld
    --------------------
        如果正常关闭不好使，那么就强制杀死进程
        [root@mysql mysql]# kill -9 `pgrep mysqld`
    --------------------
    [root@mysql mysql]# /etc/init.d/mysqld start
6）重新使用新的密码登录数据库即可
    [root@mysql mysql]# mysql -uroot -predhat

1、创建用户
语法：create user 用户名@主机 identified by ‘密码’;
创建本地用户
mysql> create user local@localhost identified by ‘local’;
mysql> select user,host,password from mysql.user where user=’local’;
+——-+———–+——————————————-+
| user | host | password |
+——-+———–+——————————————-+
| local | localhost | *EF82E77FF4184209858BDA2C853D3D7A5870DE80 |
+——-+———–+——————————————-+
1 row in set (0.00 sec)
登录测试：
[root@mysql ~]# mysql -ulocal -plocal
mysql> show databases; //普通用户，默认只能看到以下两个数据库
+——————–+
| Database |
+——————–+
| information_schema |
| test |
+——————–+
2 rows in set (0.00 sec)
2、删除用户
mysql> drop user local@localhost; //普通用户删除不了用户
ERROR 1227 (42000): Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation
mysql> exit
Bye
[root@mysql ~]# mysql -uroot -predhat
mysql> drop user local@localhost;
Query OK, 0 rows affected (0.03 sec)

3、创建可以远程登录的用户
服务器端：172.16.254.200
客户端：172.16.42.8 保证要有mysql命令
1）服务器端操作
（1）创建远程用户
mysql> create user remote@’172.16.42.8’ identified by ‘simida’; //此处为客户端IP
————————————–
mysql> create user remote@’%’ identified by ‘simida’;
— %表示除了localhost和127.0.0.1之外的所有客户端
mysql> create user remote@’172.16.%.%’ identified by ‘simida’;
— 172.16.%.%表示172.16.0.0/16网段的所有客户端
————————————–
（2）修改配置文件
[root@mysql mysql]# vim /etc/my.cnf
在[mysqld]那段里面添加如下行：5.6版本不需要
skip-name-resolve //跳过名字解析，否则无法远程连接该mysql管理系统
（3）重启服务
[root@mysql mysql]# /etc/init.d/mysqld restart
—————————————————————————-
插曲：如果远程连接报如下错误，那么修改服务器端配置文件，添加一行即可：
[root@MySQL ~]# mysql -u remote -h 172.16.254.200 -p
Enter password:
ERROR 1042 (HY000): Can’t get hostname for your address
—————————————————————————-
2）客户端登录验证：172.16.42.8
[root@MySQL ~]# mysql -u remote -h 172.16.254.200 -p //此处为服务器端IP
Enter password:simida
——————————————————————————————————————————————————
客户端软件的简单使用 navicat

    ————————————————————————————————————————————————

4、授权 grant
grant不仅可以授权，还能创建用户
授权的语法：grant 权限列表 on 库名.表名 to 用户@主机 identified by ‘密码’;
1）授予全部权限
mysql> grant all on . to allpriv@’172.16.%.%’ identified by ‘allpriv’;
mysql> flush privileges; //刷新权限
Query OK, 0 rows affected (0.00 sec)
测试：
[root@mysql mysql]# mysql -uallpriv -h 172.16.254.200 -p
Enter password:
——————————————————
插曲：
mysql> grant select on test.score to douni@’%’ identified by ‘douni’;
ERROR 1142 (42000): GRANT command denied to user ‘allpriv’@’172.16.254.200’ for table ‘score’
用root用户创建用户时，希望新的用户能够给别人授权的话，执行如下操作：
mysql> grant all on . to allpriv1@’172.16.%.%’ identified by ‘allpriv1’ with grant option;
[root@mysql mysql]# mysql -uallpriv1 -h 172.16.254.200 -p
Enter password:
mysql> grant select on test.score to douni2@’%’ identified by ‘douni’; //能够执行成功
——————————————————
2）授予部分权限
mysql> create database up;
Query OK, 1 row affected (0.00 sec)

    mysql> use up;
    Database changed
    mysql> create table upt1 (id int);
    Query OK, 0 rows affected (0.01 sec)

    mysql> insert into upt1 values();
    Query OK, 1 row affected (0.00 sec)

    mysql> insert into upt1 values();
    Query OK, 1 row affected (0.01 sec)

    mysql> insert into upt1 values();
    Query OK, 1 row affected (0.00 sec)

    mysql> create table upt2 (id int);
    Query OK, 0 rows affected (0.03 sec)

    mysql> grant select,insert,update on up.upt1 to douwo@'%' identified by 'douwo';
    mysql> flush privileges;
    验证权限
    [root@mysql ~]# mysql -udouwo  -h 172.16.254.200 -p
    Enter password: 
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | test               |
    | up                 |
    +--------------------+
    3 rows in set (0.00 sec)
    mysql> use up;
    Database changed
    mysql> show tables;
    +--------------+
    | Tables_in_up |
    +--------------+
    | upt1         |      //只对该表有权限，就只能看到这张表
    +--------------+
    1 row in set (0.00 sec)         
    mysql> select * from upt1;
    +------+
    | id   |
    +------+
    | NULL |
    | NULL |
    | NULL |
    +------+
    3 rows in set (0.00 sec)
    mysql> update upt1 set id=1 where id is null;   //成功
    mysql> insert into upt1 values();   //成功
    Query OK, 1 row affected (0.00 sec)
    mysql> delete from upt1 where id is null;     //失败
        ERROR 1142 (42000): DELETE command denied to user 'douwo'@'172.16.254.200' for table 'upt1'
    mysql> drop table upt1;   //失败
        ERROR 1142 (42000): DROP command denied to user 'douwo'@'172.16.254.200' for table 'upt1'

5、回收权限 revoke
取消用户权限： 1）删除用户 2）revoke
回收语法：
revoke 权限 on 库名.表名 from 用户@主机;
[root@mysql ~]# mysql -uroot -p
Enter password:
mysql> revoke all on . from allpriv@’172.16.%.%’;
回收部分权限
mysql> show grants for part@’%’; //查看用户的权限的
+—————————————————————————————————–+
| Grants for part@% |
+—————————————————————————————————–+
| GRANT USAGE ON . TO ‘part’@’%’ IDENTIFIED BY PASSWORD ‘*57FA4A97AD182F6A0872282CACBE109822E9E801’ |
| GRANT SELECT, INSERT, UPDATE ON test.score TO ‘part’@’%’ |
+—————————————————————————————————–+
2 rows in set (0.00 sec)
mysql> revoke update on test.score from part@’%’;
Query OK, 0 rows affected (0.00 sec)

 mysql> show grants for part@'%';
    +-----------------------------------------------------------------------------------------------------+
    | Grants for part@%                                                                                   |
    +-----------------------------------------------------------------------------------------------------+
    | GRANT USAGE ON *.* TO 'part'@'%' IDENTIFIED BY PASSWORD '*57FA4A97AD182F6A0872282CACBE109822E9E801' |
    | GRANT SELECT, INSERT ON `test`.`score` TO 'part'@'%'                                                |
    +-----------------------------------------------------------------------------------------------------+
    2 rows in set (0.00 sec)

6、查看用户的权限
mysql> select user,host from mysql.user;
+———-+————-+
| user | host |
+———-+————-+
| douni | % |
| douni2 | % |
| douwo | % |
| part | % |
| root | 127.0.0.1 |
| allpriv | 172.16.%.% |
| allpriv1 | 172.16.%.% |
| remote | 172.16.42.8 |
| root | ::1 |
| | localhost |
| root | localhost |
| | mysql |
| root | mysql |
+———-+————-+
13 rows in set (0.00 sec)
mysql> show grants for part@’%’; //查看用户的权限
+—————————————————————————————————–+
| Grants for part@% |
+—————————————————————————————————–+
| GRANT USAGE ON . TO ‘part’@’%’ IDENTIFIED BY PASSWORD ‘*57FA4A97AD182F6A0872282CACBE109822E9E801’ |
| GRANT SELECT, INSERT, UPDATE ON test.score TO ‘part’@’%’ |
+—————————————————————————————————–+
2 rows in set (0.00 sec)

三.数据类型

数据类型的分类
1、数值型
2、字符串类型
3、日期时间型

一、数值型
分类：整型、浮点型
1、整型
表示的数值范围不同，存储这样的一个数据占用的磁盘空间不同
存储占用字节数 有符号数的范围 无符号数范围
tinyint 1B(8 bits) -128 ~ 127 0 ~ 255 (2^8-1)
smallint 2B -2^15 ~ 2^15-1 0 ~ 65535 (2^16-1)
mediumint 3B -8388608 to 8388607 0 to 16777215
int 4B -2147483648 to 2147483647 0 to 4294967295
bigint 8B
2、浮点型
单精度浮点数：float(M,D) M是总长度，D是小数位的位数 float(5,2) 存储占用空间 4B
双精度浮点数：double(M,D) 存储占用空间 8B

 整型也可以指定显示宽度的，但是并不影响取值范围的。

 [root@mysql mysql]# mysql -u root -predhat
 mysql> use up;
 mysql> create table table1 (
        -> tiny tinyint,
        -> small smallint,
        -> medium mediumint,
        -> nint int,
        -> big bigint,
        -> fl float(6,3),
        -> df double(6,3)
        -> );    
mysql> desc table1;
    +--------+--------------+------+-----+---------+-------+
    | Field  | Type         | Null | Key | Default | Extra |
    +--------+--------------+------+-----+---------+-------+
    | tiny   | tinyint(4)   | YES  |     | NULL    |       |
    | small  | smallint(6)  | YES  |     | NULL    |       |
    | medium | mediumint(9) | YES  |     | NULL    |       |
    | nint   | int(11)      | YES  |     | NULL    |       |
    | big    | bigint(20)   | YES  |     | NULL    |       |
    | fl     | float(6,3)   | YES  |     | NULL    |       |
    | df     | double(6,3)  | YES  |     | NULL    |       |
    +--------+--------------+------+-----+---------+-------+
    7 rows in set (0.01 sec)
mysql> insert into table1 values(123456789,123456789,123456789,123456789,123456789,1234.56789,1234.56789);
    Query OK, 1 row affected, 5 warnings (0.02 sec)
mysql> show warnings
    -> ;
    +---------+------+-------------------------------------------------+
    | Level   | Code | Message                                         |
    +---------+------+-------------------------------------------------+
    | Warning | 1264 | Out of range value for column 'tiny' at row 1   |
    | Warning | 1264 | Out of range value for column 'small' at row 1  |
    | Warning | 1264 | Out of range value for column 'medium' at row 1 |
    | Warning | 1264 | Out of range value for column 'fl' at row 1     |
    | Warning | 1264 | Out of range value for column 'df' at row 1     |
    +---------+------+-------------------------------------------------+
    5 rows in set (0.00 sec)

mysql> select * from table1;
+——+——-+———+———–+———–+———+———+
| tiny | small | medium | nint | big | fl | df |
+——+——-+———+———–+———–+———+———+
| 127 | 32767 | 8388607 | 123456789 | 123456789 | 999.999 | 999.999 |
+——+——-+———+———–+———–+———+———+
1 row in set (0.00 sec)

超出数据类型能够表示的范围，默认插入该类型的最大值。

无符号数举例 unsigned
mysql> create table table2 (tiny tinyint unsigned);
mysql> desc table2;
+——-+———————+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+——-+———————+——+—–+———+——-+
| tiny | tinyint(3) unsigned | YES | | NULL | |
+——-+———————+——+—–+———+——-+
1 row in set (0.00 sec)
mysql> insert into table2 values(128),(256);
mysql> show warnings;
+———+——+———————————————–+
| Level | Code | Message |
+———+——+———————————————–+
| Warning | 1264 | Out of range value for column ‘tiny’ at row 2 |
+———+——+———————————————–+
1 row in set (0.00 sec)

 mysql> select * from table2;
        +------+
        | tiny |
        +------+
        |  128 |
        |  255 |
        +------+
        2 rows in set (0.00 sec)

zerofill：零填充 ，显示宽度不足时，补0
    mysql> create table zf (id tinyint(3) unsigned zerofill);
    mysql> desc zf;
        +-------+------------------------------+------+-----+---------+-------+
        | Field | Type                         | Null | Key | Default | Extra |
        +-------+------------------------------+------+-----+---------+-------+
        | id    | tinyint(3) unsigned zerofill | YES  |     | NULL    |       |
        +-------+------------------------------+------+-----+---------+-------+
        1 row in set (0.00 sec)
    mysql> insert into zf values(1),(11),(111);
    mysql> select * from zf;
        +------+
        | id   |
        +------+
        |  001 |
        |  011 |
        |  111 |
        +------+
        3 rows in set (0.00 sec)

3.日期时间型
    DATE：日期型     格式：'YYYY-MM-DD'
    TIME：时间型     格式：'HH:MM:SS'
    DATETIME：日期时间型     格式'YYYY-MM-DD HH:MM:SS'
    TIMESTAMP：时间戳类型    格式'YYYY-MM-DD HH:MM:SS'
    YEAR：格式：  'YYYY'
               'YY'
           对于两位的年：
            00 - 69       2000 - 2069
            70 - 99       1970 - 1999

   mysql> create table ttime (ttime time,tdate date,tdt datetime,tts timestamp,ty year);
   mysql> insert into ttime values(curtime(),curdate(),now(),now(),year(now()));
   mysql> select * from ttime;
        +----------+------------+---------------------+---------------------+------+
        | ttime    | tdate      | tdt                 | tts                 | ty   |
        +----------+------------+---------------------+---------------------+------+
        | 11:20:48 | 2015-09-11 | 2015-09-11 11:20:48 | 2015-09-11 11:20:48 | 2015 |
        +----------+------------+---------------------+---------------------+------+
        1 row in set (0.00 sec) 
  mysql> insert into ttime values('11:22:33','2015-09-11','2015-09-11 11:22:36','2015-09-11 11:22:56','2048');
  mysql> insert into ttime values('11:22:33','2015-09-11','2015-09-11 11:22:36','2015-09-11 11:22:56','66');
  mysql> select * from ttime;
        +----------+------------+---------------------+---------------------+------+
        | ttime    | tdate      | tdt                 | tts                 | ty   |
        +----------+------------+---------------------+---------------------+------+
        | 11:20:48 | 2015-09-11 | 2015-09-11 11:20:48 | 2015-09-11 11:20:48 | 2015 |
        | 11:22:33 | 2015-09-11 | 2015-09-11 11:22:36 | 2015-09-11 11:22:56 | 2048 |
        | 11:22:33 | 2015-09-11 | 2015-09-11 11:22:36 | 2015-09-11 11:22:56 | 2066 |
        +----------+------------+---------------------+---------------------+------+
        3 rows in set (0.00 sec).

4.字符串型
char：定长字符串 char(10) 存储占用空间10个字节 括号中的长度范围是0~255
varchar：变长字符串 varchar(10) 存储占用的空间是根据字符串的实际长度来进行分配
括号里面的范围0~65535
对于varchar类型，需要额外占用1个或者2个字节来存储字符串的长度的。
因为最大长度是65535，需要用2个字节能表示下该长度，
如果字符串的长度在0~255之间，仅需要一个字节来表示长度

注意：对于char型，存储时，如果长度不够，在字符串后面补空格；但是查询时，会自动将字符串右侧的空格脱掉。
    mysql> create table str (name char(10),name2 varchar(10));
    mysql> insert into str values('abcdefg','abcdefg');
    mysql> select * from str;
        +---------+---------+
        | name    | name2   |
        +---------+---------+
        | abcdefg | abcdefg |
        +---------+---------+
        1 row in set (0.00 sec)
   mysql> insert into str values('abcd ','abcd ');
   mysql> select concat('(',name,')'),concat('(',name2,')') from str;
        +----------------------+-----------------------+
        | concat('(',name,')') | concat('(',name2,')') |
        +----------------------+-----------------------+
        | (abcdefg)            | (abcdefg)             |
        | (abcd)               | (abcd )               |
        +----------------------+-----------------------+
        2 rows in set (0.00 sec)

enum：枚举类型 插入多个给定的值中的一个值
set：集合类型 插入多个给定的值中的一个或者多个值
mysql> create table es (sex enum(‘male’,’female’),os set(‘Linux’,’AIX’,’HP-UX’,’Centos’));
mysql> insert into es values(‘male’,’Linux,HP-UX’);
对于枚举类型，可以使用值的编号向表中插入数据
mysql> insert into es(sex) values(2);
Query OK, 1 row affected (0.02 sec)
mysql> select * from es;
+——–+————-+
| sex | os |
+——–+————-+
| male | Linux,HP-UX |
| female | NULL |
+——–+————-+
2 rows in set (0.00 sec)
mysql 5.5里，允许向表中插入未定义的值；但是在mysql 5.6版本中是不允许这样操作的。

四.索引

索引：类似于书的目录，饭店的菜单、字典的目录
功能：加快数据检索的速度，提高查询效率

缺点：
1）创建和维护索引都需要消耗时间，消耗时间的长短取决于数据量的多少
2）会占用磁盘空间
3）更新数据库中的数据时，索引也会更新

什么时候都可以创建索引吗？
不是。对于数据频繁更新的表不适合创建索引。(更新包括insert、update、delete)

索引的分类：
非聚集索引 MyISAM
聚集索引 Innodb 一个表只能有一个

稠密索引 主键索引
稀疏索引

主索引
辅助索引

索引的分类
单列索引、多列索引、唯一性索引

索引创建的场景：
1、表中的数据比较稳定
2、频繁使用某列或某几列进行查询
3、varchar列不太适合创建索引
4、模糊查询时，通配符在前部适合创建索引

（一）.创建索引
1.在创建表时直接创建索引
create table 表名 (字段名 字段类型，… …，index 索引名);
mysql> create table ind1 (id int,name varchar(10),index (id));
mysql> show create table ind1\G
***************** 1. row *****************
Table: ind1
Create Table: CREATE TABLE ind1 (
id int(11) DEFAULT NULL,
name varchar(10) DEFAULT NULL,
KEY id (id) //索引相关的行 KEY 索引名 (索引字段)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
创建索引时，如果不指定索引名字的话，那么默认创建和字段名同名的索引。

  2.使用create index 命令去创建索引
        语法：create index 索引名(必须有) on 表名(字段名);
        mysql> create index ind1_name on ind1(name);
        mysql> show create table ind1 \G
        *************************** 1. row ***************************
               Table: ind1
        Create Table: CREATE TABLE `ind1` (
          `id` int(11) DEFAULT NULL,
          `name` varchar(10) DEFAULT NULL,
          KEY `id` (`id`),
          KEY `ind1_name` (`name`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8
        1 row in set (0.00 sec)
   3.对于已经存在的表，添加索引
        语法：alter table 表名 add index [索引名](索引字段);
        mysql> alter table zf add index zf_id(id);

        唯一性索引     unique index [索引名](索引字段);
        mysql> insert into zf values(111);
        mysql> select * from zf;
            +------+
            | id   |
            +------+
            |  001 |
            |  011 |
            |  111 |
            |  111 |
            +------+
            4 rows in set (0.00 sec)
        mysql> alter table table1 add unique index t1_tiny(tiny);
        mysql> insert into table1(tiny) values(127);
            ERROR 1062 (23000): Duplicate entry '127' for key 't1_tiny'
                                            重复的   条目    

（二）.查看索引
1.mysql> show create table 表名\G
2.show index from 表名\G
mysql> show index from ind1(表名）\G
***************** 1. row *****************
Table: ind1
Non_unique: 1
Key_name: id //索引名
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
***************** 2. row *****************
Table: ind1
Non_unique: 1
Key_name: ind1_name
Seq_in_index: 1
Column_name: name
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
（三）.删除索引
1.drop index 索引名 on 表名;
mysql> drop index id on ind1;
2.alter table 表名 drop index 索引名;
mysql> alter table ind1 drop index ind1_name;

测试：
1.创建表
mysql> create table shop (id int,name varchar(20),price float(10,2),street varchar(20),city varchar(20));
2.写脚本生成插入语句
[root@mysql ~]# vim insert.sh
[root@mysql ~]# insert.sh
#!/bin/bash
i=1
while [ $i -le 1000000 ] do echo “insert into shop values($i,'name$i’,$i.00,'street$i’,’city$i’);” >> /tmp/insert.sql
echo $i
let i++
done
3.执行脚本生成sql文件
[root@mysql ~]# sh insert.sh
4.向表中插入数据
mysql> use up;
mysql> source /tmp/insert.sql; //漫长的等待

    mysql> select count(*) from up.shop;
        +----------+
        | count(*) |
        +----------+
        |   217535 |
        +----------+
        1 row in set (0.14 sec)

5.开启可以查看每个查询语句执行时间的功能
mysql> show variables like ‘%profi%’;
+————————+——-+
| Variable_name | Value |
+————————+——-+
| have_profiling | YES |
| profiling | OFF |
| profiling_history_size | 15 |
+————————+——-+
mysql> set profiling=1;
6.创建一张带索引的表
mysql> create table shop1 (id int,name varchar(20),price float(10,2),street varchar(20),city varchar(20),index (id));
7.复制表shop到shop1
mysql> insert into shop1 select * from shop;
8.对两个表分别执行如下语句
先看shop表：
mysql> select * from shop; // 全表扫描
mysql> select id from shop; //只查询某个字段
mysql> select * from shop where id=1;
mysql> select id,name from shop where id=1;
mysql> select * from shop where id=550000;
mysql> select id,name from shop where id=550000;
mysql> select id from shop where price=10000.00;
再看shop1表：
mysql> select * from shop1; // 全表扫描
mysql> select id from shop1; //只查询某个字段
mysql> select * from shop1 where id=1;
mysql> select id,name from shop1 where id=1;
mysql> select * from shop1 where id=550000;
mysql> select id,name from shop1 where id=550000;
mysql> select id from shop1 where price=10000.00;

查询执行计划
EXPLAIN语句解析：
id:SELECT语句的标识符，一般为数字，表示对应的SELECT语句在原始语句中的位置。没有子查询或联合的整个查询只有一个SELECT语句，因此其id通常为1。在联合或子查询语句中，内层的SELECT语句通常按它们在原始语句中的次序进行编号。但UNION操作通常最后会有一个id为NULL的行，因为UNION的结果通常保存至临时表中，而MySQL需要到此临时表中取得结果。

select_type:
即SELECT类型，有如下值列表：
SIMPLE：简单查询，即没有使用联合或子查询；
PRIMARY：UNION的最外围的查询或者最先进行的查询；
UNION：相对于PRIMARY，为联合查询的第二个及以后的查询；
DEPENDENT UNION：与UNION相同，但其位于联合子查询中(即UNION查询本身是子查询)；
UNION RESULT：UNION的执行结果；
SUBQUERY：非从属子查询，优化器通常认为其只需要运行一次；
DEPENDENT SUBQUERY：从属子查询，优化器认为需要为外围的查询的每一行运行一次，如用于IN操作符中的子查询；
DERIVED：用于FROM子句的子查询，即派生表查询；

table:
输出信息所关系到的表的表名，也有可能会显示为如下格式：

五.约束

 约束也叫完整性约束(integrity constraint )

什么叫完整性？
完整性约束就是确保数据库中的数据是有意义的、正确的

什么是约束？
为了保证数据的正确性，对关系模型提出的某些约束条件或者是规则。

约束一般作用于字段上
约束有哪些？
非空、唯一、默认值、主键、外键、自增
语法：
字段名 字段类型 [not null | unique | default 默认值 | auto_increment]

1、默认值
mysql> use up;
Database changed
mysql> create table tdef (name char(10),city char(10) default ‘shenyang’);
mysql> desc tdef;
+——-+———-+——+—–+———-+——-+
| Field | Type | Null | Key | Default | Extra |
+——-+———-+——+—–+———-+——-+
| name | char(10) | YES | | NULL | |
| city | char(10) | YES | | shenyang | |
+——-+———-+——+—–+———-+——-+
mysql> insert into tdef values();
mysql> select * from tdef;
+——+———-+
| name | city |
+——+———-+
| NULL | shenyang |
+——+———-+
1 row in set (0.00 sec)
默认值：当用户向表中插入数据时，若指定该字段的值，那么就插入指定值；如果没有指定该字段的值，那么就插入默认值。
mysql> insert into tdef values(‘huangtao’,’qiqihaer’);
mysql> select * from tdef;
+———-+———-+
| name | city |
+———-+———-+
| NULL | shenyang |
| huangtao | qiqihaer |
+———-+———-+
对于已经存在的表，如何设置字段的默认值
mysql> alter table tdef modify name char(10) default ‘mary’;

 2、非空   not null
    mysql> create table tnn (id int not null,name char(10) not null );
    mysql> insert into tnn values();     //会将不允许为空的id字段值转换成0
    mysql> show warnings;
        +---------+------+-----------------------------------------+
        | Level   | Code | Message                                 |
        +---------+------+-----------------------------------------+
        | Warning | 1364 | Field 'id' doesn't have a default value |
        +---------+------+-----------------------------------------+
    mysql> select * from tnn;
        +----+------+
        | id | name |
        +----+------+
        |  0 |  |
        +----+------+
        1 row in set (0.00 sec)
    mysql> desc tnn;
        +-------+----------+------+-----+---------+-------+
        | Field | Type     | Null | Key | Default | Extra |
        +-------+----------+------+-----+---------+-------+
        | id    | int(11)  | NO   |     | NULL    |       |
        | name  | char(10) | YES  |     | NULL    |       |
        +-------+----------+------+-----+---------+-------+
    mysql> alter table tnn modify name char(10) not null;
    mysql> select * from tnn;
        +----+------+
        | id | name |
        +----+------+
        |  0 |      |
        +----+------+
        1 row in set (0.00 sec)
  mysql> select * from tnn where name is null;
        Empty set (0.00 sec)

  mysql> select * from tnn where name='';    //匹配空字符串
        +----+------+
        | id | name |
        +----+------+
        |  0 |      |
        +----+------+
        1 row in set (0.00 sec)
    注意：非空的约束，对于数值型，未指定插入值时，默认会插入0；对于字符串型，未指定插入值时，默认会插入空字符串。

3、唯一 unique
mysql> create table tuni (id int unique,name char(10));
‘’ nullmysql> desc tuni;
+——-+———-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+——-+———-+——+—–+———+——-+
| id | int(11) | YES | UNI | NULL | |
| name | char(10) | YES | | NULL | |
+——-+———-+——+—–+———+——-+
mysql> insert into tuni values();
mysql> insert into tuni values(); //注意：唯一性约束对空值无效
mysql> insert into tuni values(1,’tom’);
mysql> insert into tuni values(2,’mary’);
mysql> insert into tuni values(1,’jack’);
ERROR 1062 (23000): Duplicate entry ‘1’ for key ‘id’

    对于唯一性约束，要么向该字段插入空值，要么就向该字段插入唯一的值。

4、自增 auto_increment
要求：1）该字段的类型必须是数值型的
2）字段上要有唯一性索引或主键
mysql> create table zz (id int primary key auto_increment,name char(10));
mysql> desc zz;
+——-+———+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+——-+———+——+—–+———+—————-+
| id | int(11) | NO | PRI | NULL | auto_increment |
+——-+———+——+—–+———+—————-+
1 row in set (0.00 sec)
mysql> insert into zz(name) values(‘loring’)
Query OK, 1 row affected (0.02 sec)

        mysql> select * from zz;
            +----+
            | id |
            +----+
            |  1 |
            +----+
            1 row in set (0.00 sec)
        mysql> insert into zz values(5);
            Query OK, 1 row affected (0.00 sec)

        mysql> select * from zz;
        几点说明：
            1）当自增字段发生断档时，值从最大值继续自增
                mysql> insert into zz values();
                    Query OK, 1 row affected (0.01 sec)

                mysql> select * from zz;
                    +----+
                    | id |
                    +----+
                    |  1 |
                    |  2 |
                    |  5 |
                    |  6 |
                    +----+
                    4 rows in set (0.00 sec)    
             2）当用delete删除最大值时，下一个值仍然从最大值的下一个继续自增
                mysql> delete from zz where id=6;
                    Query OK, 1 row affected (0.04 sec)

                mysql> insert into zz values();
                    Query OK, 1 row affected (0.00 sec)

                mysql> select * from zz;
                    +----+
                    | id |
                    +----+
                    |  1 |
                    |  2 |
                    |  5 |
                    |  7 |
                    +----+
                    4 rows in set (0.00 sec)
             3）当你truncate一个表时，值从1开始重新计算
                mysql> truncate table zz;
                    Query OK, 0 rows affected (0.05 sec)

                mysql> insert into zz values();
                    Query OK, 1 row affected (0.00 sec)

                mysql> select * from zz;
                    +----+
                    | id |
                    +----+
                    |  1 |
                    +----+
                    1 row in set (0.00 sec)

5、主键 primary key
主键是表中的特殊字段，这个字段能够唯一标识表中的每一条记录。
主键用途：快速定位数据的
主键在一张表中最多只能有一个
主键可以有一个或多个字段
主键满足的条件：非空且唯一
primary key = not null + unique
1）使用单个字段做主键
a、在字段后直接指定主键约束(列级约束)
mysql> create table pri (id int primary key,age int,name char(10));
mysql> desc pri;
+——-+———-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+——-+———-+——+—–+———+——-+
| id | int(11) | NO | PRI | NULL | |
| age | int(11) | YES | | NULL | |
| name | char(10) | YES | | NULL | |
+——-+———-+——+—–+———+——-+
3 rows in set (0.00 sec)
mysql> insert into pri(id) values (1);
Query OK, 1 row affected (0.01 sec)

      mysql> insert into pri(id) values (1);
        ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'  
      mysql> insert into pri values();
        Query OK, 1 row affected, 1 warning (0.01 sec)

      mysql> show warnings;
        +---------+------+-----------------------------------------+
        | Level   | Code | Message                                 |
        +---------+------+-----------------------------------------+
        | Warning | 1364 | Field 'id' doesn't have a default value |
        +---------+------+-----------------------------------------+
        1 row in set (0.00 sec)
     mysql> select * from pri;
        +----+------+------+
        | id | age  | name |
        +----+------+------+
        |  0 | NULL | NULL |
        |  1 | NULL | NULL |
        +----+------+------+
        2 rows in set (0.00 sec)
   mysql> insert into pri values();
        ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY'
 b、整张表的所有字段都定义完成之后再去指定主键(表级约束)
    mysql> create table pri1 (id int,name char(10),primary key(id));
    mysql> desc pri1;
        +-------+----------+------+-----+---------+-------+
        | Field | Type     | Null | Key | Default | Extra |
        +-------+----------+------+-----+---------+-------+
        | id    | int(11)  | NO   | PRI | 0       |       |
        | name  | char(10) | YES  |     | NULL    |       |
        +-------+----------+------+-----+---------+-------+
        2 rows in set (0.00 sec)
 2）多个字段联合做主键
    mysql> desc mysql.user;    //user和host两个字段联合做主键
        +------------------------+-----------------------------------+------+-----+---------+-------+
        | Field                  | Type                              | Null | Key | Default | Extra |
        +------------------------+-----------------------------------+------+-----+---------+-------+
        | Host                   | char(60)                          | NO   | PRI |         |       |
        | User                   | char(16)                          | NO   | PRI |         |       |
        | Password               | char(41)                          | NO   |     |         |       |
mysql> select user,host from mysql.user;
    +----------+-------------+
    | user     | host        |
    +----------+-------------+
    | douni    | %           |
    | douni2   | %           |
    | douwo    | %           |
    | part     | %           |
    | root     | 127.0.0.1   |
    | allpriv  | 172.16.%.%  |
    | allpriv1 | 172.16.%.%  |
    | remote   | 172.16.42.8 |
    | root     | ::1         |
    |          | localhost   |
    | ni       | localhost   |
    | root     | localhost   |
    |          | mysql       |
    | root     | mysql       |
    +----------+-------------+
    14 rows in set (0.05 sec)
注意：联合主键只能在所有字段都定义之后，再去定义主键
mysql> create table pri2 (id int,name char(10),age int,primary key (id,name));
mysql> insert into pri2 values(1,'Tom',22);
    Query OK, 1 row affected (0.00 sec)

mysql> insert into pri2 values(1,'Tom',22);
    ERROR 1062 (23000): Duplicate entry '1-Tom' for key 'PRIMARY'
mysql> insert into pri2 values(1,'Mary',20);
    Query OK, 1 row affected (0.20 sec)

mysql> insert into pri2 values(2,'Tom',20);
    Query OK, 1 row affected (0.00 sec)
想一想：
    对一个已经存在的表，如何添加主键？如何删除主键？
    添加主键：
    alter table score add primary key(sno);
    删除主键
    alter table score drop primary key;  

6、外键 foreign key
外键：一个表B中的数据依赖于另一张表A的主键列的数据，如果A表中未出现的值，是不能够出现在B表中的
A：父表
B：子表，外键在子表中

     主键和外键就像是表之间的粘合剂，能够将多个表关联起来。

 创建外键的条件：
    1）存储引擎是innodb，是mysql5.5默认的引擎
    2）相关联字段数据类型要一致
    3）最好在外键列上建索引
 例子：
    dept：部门表
    emp：员工表
    mysql> create table dept (dno int,dname char(10),primary key (dno))  engine=innodb;
    mysql> create table emp (eno int,edno int,ename char(10),index(edno),foreign key (edno) references dept(dno)engine=innodb;
    向父表中插入数据
    mysql> insert into dept values(100,'manager'),(101,'dba'),(102,'hr'),(103,'sa');
    向子表中插入数据
    mysql> insert into emp values(1,100,'Tom');
    mysql> insert into emp values(2,101,'Mary');
    mysql> insert into emp values(3,108,'Daji');  //反例：插入父表中不存在的部门号
        ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`up`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`edno`) REFERENCES `dept` (`dno`))


    删除父表中的数据
    mysql> select * from dept;
        +-----+---------+
        | dno | dname   |
        +-----+---------+
        | 100 | manager |
        | 101 | dba     |
        | 102 | hr      |
        | 103 | sa      |
        +-----+---------+
   mysql> select * from emp;
        +------+------+-------+
        | eno  | edno | ename |
        +------+------+-------+
        |    1 |  100 | Tom   |
        |    2 |  101 | Mary  |
        +------+------+-------+
        2 rows in set (0.00 sec)
  mysql> delete from dept where dno=102;
  mysql> delete from dept where dno=101;
       ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`up`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`edno`) REFERENCES `dept` (`dno`))

   以上实验小结：
        1）子表中的数据依赖于父表，不能向子表中插入父表中不存在的值
        2）不能删除父表中被子表所依赖的行

on delete cascade      级联删除
on update cascade     级联更新
    mysql> drop table emp;
    完整的外键创建
    mysql> create table emp (eno int,edno int,ename char(10),index(edno),foreign key (edno) references dept(dno) on delete cascade on update cascade) engine=innodb;
    mysql> insert into emp values(1,100,'Tom');
    mysql> insert into emp values(2,101,'Songjiang');    
    mysql> insert into emp values(5,101,'Likui');
    mysql> delete from dept where dno=101;
    mysql> select * from dept;
        +-----+---------+
        | dno | dname   |
        +-----+---------+
        | 100 | manager |
        | 103 | sa      |
        +-----+---------+
        2 rows in set (0.00 sec)

    mysql> select * from emp;
        +------+------+-------+
        | eno  | edno | ename |
        +------+------+-------+
        |    1 |  100 | Tom   |
        +------+------+-------+
        1 row in set (0.00 sec)

   mysql> update dept set dno=110 where dno=100;
   mysql> select * from emp;
        +------+------+-------+
        | eno  | edno | ename |
        +------+------+-------+
        |    1 |  110 | Tom   |
        +------+------+-------+
        1 row in set (0.00 sec)
  有了级联删除和级联修改选项，父表中的数据发生删除或者更新时，子表中的数据也会发生相应的变化。

删除外键
查看外键的名字
mysql> show create table emp\G
***************** 1. row *****************
Table: emp
Create Table: CREATE TABLE emp (
eno int(11) DEFAULT NULL,
edno int(11) DEFAULT NULL,
ename char(10) DEFAULT NULL,
KEY edno (edno), //索引
CONSTRAINT emp_ibfk_1 FOREIGN KEY (edno) REFERENCES dept (dno) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> alter table emp drop foreign key emp_ibfk_1;-