视图：
简单视图：单张表
复杂视图：多张，子查询
物化视图：
MariaDB [hidb]> create view v1_students as select name,age from students;
MariaDB [hellodb]> create view v3_students as select name,age from students where age>40 ;
Query OK, 0 rows affected (0.05 sec)
MariaDB [hellodb]> update v3_students set age=39 where age>40;
Query OK, 3 rows affected (0.14 sec)
Rows matched: 3 Changed: 3 Warnings: 0
MariaDB [hellodb]> select * from v3_students;
Empty set (0.00 sec)
注意：视图存放的不是数据，而是select语句，修改视图相当于修改基表，如果修改完之后，不满足创建视图时候的条件时，再次查询视图就会为空，因为表里的数据已被修改。这时，需要加with check option
MariaDB [hidb]> create view v3_students as select name,age from students where age > 40 with check option;
MariaDB [hidb]> show create view v3_students\G;
*************************** 1. row ***************************
View: v3_students
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3_students` AS select `students`.`Name` AS `name`,`students`.`Age` AS `age` from `students` where (`students`.`Age` > 40) WITH CASCADED CHECK OPTION
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)
MariaDB [hellodb]> select * from v3_students;
+-------------+-----+
| name | age |
+-------------+-----+
| An Qila | 53 |
| Huang Zhong | 46 |
| Sun Wukong | 250 |
+-------------+-----+
3 rows in set (0.13 sec)
MariaDB [hellodb]> update v3_students set age=39 where age>40;
ERROR 1369 (44000): CHECK OPTION failed `hellodb`.`v3_students`
复杂视图：
MariaDB [hidb]> create view v4_students as select s.name student_name,t.name teacher_name from students s join teachers t on s.teacherid=t.tid;
MariaDB [hellodb]> select * from v4_student;
ERROR 1146 (42S02): Table 'hellodb.v4_student' doesn't exist
MariaDB [hellodb]> select * from v4_students;
+----------------+---------------+
| student_name | teacher_name |
+----------------+---------------+
| Sun Shangxiang | Liu Bang |
| Hou Yi | Wu Zetian |
| Da Ji | Cheng Jisihan |
+----------------+---------------+
3 rows in set (0.11 sec)
MariaDB [hidb]> update v4_students set teacher_name='Tie Muzhen' where student_name='da ji';
MariaDB [hellodb]> select * from v4_students;
+----------------+--------------+
| student_name | teacher_name |
+----------------+--------------+
| Sun Shangxiang | Liu Bang |
| Hou Yi | Wu Zetian |
| Da Ji | Tie ef |
+----------------+--------------+
3 rows in set (0.00 sec)
MariaDB [hidb]> create view v5_students as select classid,count(stuid) student_count from students group by classid having classid is not null;
Query OK, 0 rows affected (0.01 sec)
MariaDB [hidb]> select * from v5_students;
+---------+---------------+
| classid | student_count |
+---------+---------------+
| 1 | 4 |
| 2 | 3 |
| 3 | 4 |
| 4 | 4 |
| 5 | 1 |
| 6 | 4 |
| 7 | 3 |
+---------+---------------+
7 rows in set (0.01 sec)
MariaDB [hidb]> update v5_students set student_count=10 where classid=7;
ERROR 1288 (HY000): The target table v5_students of the UPDATE is not updatable
注意：select 语句中包含group by时候不能修改视图中的数据，基表中没有相关信息，所以会报错 。
关于视图的总结
不能修改数据的视图：
1.select 子句中包括distinct
2.select 子句中包含组函数
3.select 子句中包含group by
4.select 子句中包含union
查看创建指定的视图信息
MariaDB [hidb]> show create view v3_students\G;
显示某视图的状态信息
MariaDB [hellodb]> show table status like 'view_students'\G;
查看所有的视图信息
MariaDB [hidb]> select * from information_schema.views\G;
删除视图
MariaDB [hidb]> drop view v1_students;
自定义函数
所有的自定义函数保存在MySQL.proc表中，参数有多个，也可以没有，必须有且只有一个返回值。
MariaDB [mysql]> CREATE FUNCTION simpleFun() RETURNS VARCHAR(20) RETURN "Hello World!"; //不区分大小写
Query OK, 0 rows affected (0.00 sec)
MariaDB [mysql]> select simplefun();
+--------------+
| simplefun() |
+--------------+
| Hello World! |
+--------------+
1 row in set (0.00 sec)
查看所有函数的信息
MariaDB [mysql]> show function status\G;
查看指定自定义函数的定义（不能查看内置函数）
MariaDB [mysql]> show create function simplefun\G;
*************************** 1. row ***************************
Function: simplefun
sql_mode:
Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `simplefun`() RETURNS varchar(20) CHARSET latin1
RETURN "Hello World!"
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)
ERROR: No query specified
带参数的自定义函数
自定义函数里面有多语句，select语句中一旦有分号，就开始执行，与shell中函数不同的是：shell中的函数是先定义再调用，调用时候再发挥作用。所以mysql中函数也是需要预定义的，先不执行，在执行的时候用语句，里面有好多分号作用是在执行语句时候，再一行一行执行。所以修改分号的意义，先不让它执行，用其他分隔符 来当执行语句的标志。
MariaDB [mysql]> delimiter //
注意：在自定义分隔符时，delimiter后面有空格
MariaDB [mysql]> CREATE FUNCTION addTwoNumber(x SMALLINT UNSIGNED, Y SMALLINT UNSIGNED)
-> RETURNS SMALLINT BEGIN
-> DECLARE a, b SMALLINT UNSIGNED;
-> SET a = x, b =y;
-> RETURN a+b;
-> END//
Query OK, 0 rows affected (0.00 sec)
MariaDB [mysql]> delimiter ;
MariaDB [mysql]> select addtwonumber(10,20); //调用函数
+---------------------+
| addtwonumber(10,20) |
+---------------------+
| 30 |
+---------------------+
1 row in set (0.00 sec)
说明：局部变量的作用范围在begin…end程序中，定义的时候必须在begin…end的第一行定义
查看所有的自定义函数
MariaDB [mysql]> show function status\G;
删除自定义函数
MariaDB [mysql]> drop function simplefun2;
Query OK, 0 rows affected (0.00 sec)
存储过程
存储过程把经常使用的sql语句或业务逻辑封装起来，预编译保存在数据库中，当需要时从数据库中直接调用，省去了编译过程。提高了运行速度，降低了网络数据传输量。
所有的存储过程保存在MySQL.proc表中
查看指定的存储过程创建
show create procedure showtime\G;
查看存储过程列表
show procedure status
调用存储过程:
call showtime();
说明:当无参时,可以省略"()",当有参数时,不可省略"()”
修改存储过程
alter 语句修改存储过程只能修改存储过程的注释等无关紧要的东西，不能修改存储过程体，所以要修改存储过程，方法就是删除重建。
删除存储过程
MariaDB [mysql]> drop procedure showtime;
创建无参存储过程
MariaDB [mysql]> delimiter //
MariaDB [mysql]> create procedure showtime()
-> begin
-> select now();
-> end//
Query OK, 0 rows affected (0.00 sec)
MariaDB [mysql]> delimiter ；
call showtime();
MariaDB [hellodb]> call showtime;
+---------------------+
| now() |
+---------------------+
| 2018-09-25 07:55:42 |
+---------------------+
1 row in set (0.04 sec)
Query OK, 0 rows affected (0.04 sec)
触发器trigger
触发器的执行不是由程序调用，也不是由手工启动，而是由事件来触发，激活从而来实现。
创建触发 器
示例：创建触发器，在向学生表INSERT数据时，学生数增加，DELETE学生时， 学生数减少
MariaDB [hellodb]> create table student_info(
-> stu_id int(11) primary key auto_increment,
-> stu_name varchar(255) default null);
Query OK, 0 rows affected (0.01 sec)
MariaDB [hellodb]> create table student_count( student_count int(11) default 0);
Query OK, 0 rows affected (0.02 sec)
Empty set (0.00 sec)
MariaDB [hellodb]> insert into student_count values (0);
Query OK, 1 row affected (0.00 sec)
MariaDB [hellodb]> select * from student_count;
+---------------+
| student_count |
+---------------+
| 0 |
+---------------+
1 row in set (0.06 sec)
MariaDB [hellodb]> create trigger trigger_student_count_insert after insert on student_info for each row update student_count set student_count=student_count+1;
Query OK, 0 rows affected (0.01 sec)
MariaDB [hellodb]> create trigger trigger_student_count_delete after delete on student_info for each row update student_count set student_count=student_count-1;
Query OK, 0 rows affected (0.01 sec)
查看trigger信息
MariaDB [hellodb]> SHOW TRIGGERS\G;
触发器的结果（插入数据时候，count表中数字自加1，删除时候，减一）
MariaDB [hellodb]> insert into student_info values(1,'liuxin');
Query OK, 1 row affected (0.16 sec)
MariaDB [hellodb]> select * from student_count;
+---------------+
| student_count |
+---------------+
| 1 |
+---------------+
1 row in set (0.00 sec)
MariaDB [hellodb]> delete from student_info where stu_id=1;
Query OK, 1 row affected (0.15 sec)
MariaDB [hellodb]> select * from student_count;
+---------------+
| student_count |
+---------------+
| 0 |
+---------------+
1 row in set (0.00 sec)
查询系统表information_schema.triggers的方式指定查询条件，查看指定的 触发器信息。
mysql> USE information_schema;
Database changed
mysql> SELECT * FROM triggers WHERE trigger_name='trigger_student_count_insert';
删除trigger
DROP TRIGGER trigger_name;
DDL(create drop alter)
DML(insert update delete)
DQL(select)
DCL(grant revoke)
mysql 用户和权限管理
注意：用户和主机名必须一起才能表示一个账户
mysql -u root （-h localhost）默认可以不写
不同的账户
root@localhost
root@127.0.0.1
root@192.168.153.7
root@172.18.0.100
含有通配符的账户
root@192.168.%.%
root@192.168.153.%
创建帐户
MariaDB [mysql]> create user liuxin@'192.168.40.134‘ identified by 'centos';
用户重命名：
RENAME USER old_user_name TO new_user_name
MariaDB [mysql]> rename user ms@'192.168.40.%' to fhj@'192.168.40.134';
Query OK, 0 rows affected (0.00 sec)
删除帐户（只能用root删除）
MariaDB [(none)]> drop user liuxin@'192.168.40.134';
Query OK, 0 rows affected (0.06 sec)
删除默认的空用户
DROP USER ''@'localhost';
在centos7 上添加用户root@’192.168.40.134‘，并设置密码为123
MariaDB [(none)]> create user root@'192.168.40.134' identified by '123';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> select user,host,password from mysql.user;
+------+-----------------------+-------------------------------------------+
| user | host | password |
+------+-----------------------+-------------------------------------------+
| root | localhost | |
| root | localhost.localdomain | |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
| | localhost.localdomain | |
| root | 192.168.40.134 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+------+-----------------------+-------------------------------------------+
在centos6上输入mysql –uroot –p123 –h192.168.40.146
[root@centos6 bin]#./mysql -u root -p123 -h 192.168.40.146;
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 5.5.56-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
注意：远程登录时，客户端地址在服务器7上，是用客户端的地址连接的，-h是服务器地址
修改密码
方法一
set password for root@'localhost'=password("redhat");
Query OK, 0 rows affected (0.23 sec)
方法二
update mysql.user set password=password("zhongqiukuaile") where host='localhost';
此方法需要执行下面指令才能生效： mysql> FLUSH PRIVILEGES;
重置mysql管理员帐户root@localhost
vim /etc/my.cnf
[mysqld]
skip_grant_tables
重启服务：systemctl restart mariadb.service
mysql -u root 直接可免密码登录
重新设置密码时候，不能用set直接设置，因为跳过了认证。
MariaDB [(none)]> set password for root@'localhost'=password("123");
ERROR 1290 (HY000): The MariaDB server is running with the --skip-grant-tables option so it cannot execute this statement
MariaDB [(none)]>
MariaDB [(none)]> update mysql.user set password=password("centos") where host='localhost';
Query OK, 1 row affected (0.26 sec)
Rows matched: 1 Changed: 1 Warnings: 0
再把/etc/my.cnf还原，再重启服务 ，完成
授权
在centos6上给centos7授权grant all on hellodb.* to root@'192.168.40.146;
MariaDB [(none)]> grant all on hellodb.* to root@'192.168.40.146'；
Query OK, 0 rows affected (0.06 sec)
在centos7上登录到centos6上并查看授权内容
[root@localhost ~]# mysql -uroot -p123 -h 192.168.40.134
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 14
Server version: 10.2.14-MariaDB-log MariaDB Server
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| hellodb |
| information_schema |
| test |
+--------------------+
3 rows in set (0.09 sec)
将某数据库下的视图授权给某用户，此用户可以修改视图，实际上修改了基表的数据，体现了视图的功能，保证了数据的安全
性
MariaDB [(none)]> grant all on hellodb.view_student to root@'192.168.40.147';
Query OK, 0 rows affected (0.01 sec)
[root@localhost ~]# mysql -uroot -p123 -h 192.168.40.134
MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| view_student |
+-------------------+
1 row in set (0.00 sec)
MariaDB [hellodb]> select * from view_student;
+----------------+-----+
| student_name | sex |
+----------------+-----+3
只授权视图的查看，插入功能，除次之外，功能受限
grant select,insert on hellodb.view_student to root@'192.168.40.134;
MariaDB [hellodb]> insert into view_student values ('ma sai','M');
Query OK, 1 row affected, 1 warning (0.00 sec)
MariaDB [hellodb]> update view_student set sex='F' where name='ma sai';
ERROR 1142 (42000): UPDATE command denied to user 'root'@'192.168.40.134' for table 'view_student'
只授权某些字段
MariaDB [hellodb]> grant select(stuid,name,gender) on hellodb.students to root@'192.168.40.146’
Query OK, 0 rows affected (0.01 sec)
注意：如果后面加上with check option 则被授权的用户可以再去给其他的用户授权
不能查看全部信息，只能查看被授权的
MariaDB [hellodb]> select * from students;
ERROR 1142 (42000): SELECT command denied to user 'root'@'192.168.40.134' for table 'students'
MariaDB [hellodb]> select name,gender from students;
+----------------+--------+
| name | gender |
查看指定用户获得的授权：
Help SHOW GRANTS SHOW GRANTS FOR 'user'@'host';
MariaDB [(none)]> show grants for root@'192.168.40.134;
注意：MariaDB服务进程启动时会读取mysql库中所有授权表至内存
(1)GRANT或REVOKE等执行权限操作会保存于系统表中，MariaDB的服务进 程通常会自动重读授权表，使之生效
(2) 对于不能够或不能及时重读授权表的命令，可手动让MariaDB的服务进程 重读授权表：mysql> FLUSH PRIVILEGES;
回收授权
revoke select,insert on hellodb.view_student from root@'192.168.40.134;
授权和创建用户一起
grant select (stuid,name) on students to root@’192.168.40.147identified by ‘centos’;
所有被授权的信息都存在mysql库中的columns_priv，等以.priv结尾的数据库中
MariaDB [mysql]> select * from columns_priv;
+--------------+---------+------+------------+-------------+---------------------+-------------+
| Host | Db | User | Table_name | Column_name | Timestamp | Column_priv |
+--------------+---------+------+------------+-------------+---------------------+-------------+
| 192.168.40.% | hellodb | ms | students | stuid | 2018-09-25 16:52:49 | Select |
| 192.168.40.% | hellodb | ms | students | name | 2018-09-25 16:52:49 | Select |
+--------------+---------+------+------------+-------------+---------------------+-------------+
2 rows in set (0.00 sec)
存储引擎
MyISAM引擎文件：
tbl_name.frm: 表格式定义
tbl_name.MYD: 数据文件
tbl_name.MYI: 索引文件
查看mysql支持的存储引擎: show engines;
查看当前默认的存储引擎: show variables like '%storage_engine%';
MariaDB [(none)]> show variables like '%storage_engine%';
+----------------------------+--------+
| Variable_name | Value |
+----------------------------+--------+
| default_storage_engine | InnoDB |
| default_tmp_storage_engine | |
| enforce_storage_engine | |
| storage_engine | InnoDB |
+----------------------------+--------+
4 rows in set (0.07 sec)
设置默认的存储引擎：
vim /etc/my.conf
[mysqld]
default_storage_engine= InnoDB;
查看库中所有表使用的存储引擎
Show table status from db_name;
查看库中指定表的存储引擎
show table status like ' tb_name ';
show create table tb_name;
设置表的存储引擎：
CREATE TABLE tb_name(... ) ENGINE=InnoDB;
ALTER TABLE tb_name ENGINE=InnoDB;
约束
查看所有约束
select * from information_schema.`table_constraints`\G;
非空约束
建表时指定
create table t2(id int(10) not null,name varchar(5));
已存在的表增加约束
alter table t2 modify name varchar(5) not null;
MariaDB [hellodb]> desc t2;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id | int(10) | NO | | NULL | |
| name | varchar(5) | NO | | NULL | |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
MariaDB [hellodb]> insert into t2 values(3,null);
ERROR 1048 (23000): Column 'name' cannot be null
删除非空约束 (重新定义)
alter table t2 modify name varchar(5);
查看创建表的详细信息
MariaDB [hellodb]> show create table t2;
+-------+--------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------+
| t2 | CREATE TABLE `t2` (
`id` int(10) NOT NULL,
`name` varchar(5) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
唯一性约束
create table t3(userid int(10) unique,name varchar(10));
向表中添加信息（不能重复）
MariaDB [hellodb]> insert into t3 values(1,'lif');
Query OK, 1 row affected (0.01 sec)
MariaDB [hellodb]> insert into t3 values(1,'fhj');
ERROR 1062 (23000): Duplicate entry '1' for key 'userid'
复合唯一性约束
create table t_user(
-> user_id int(10),
-> user_name varchar(30),
-> id_card varchar(18),
-> constraint un_userid_idcard unique(user_id,id_card)
-> );
MariaDB [hellodb]> desc t_user;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| user_id | int(10) | YES | MUL | NULL | |
| user_name | varchar(30) | YES | | NULL | |
| id_card | varchar(18) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
MariaDB [hellodb]> select * from t_user;
+---------+-----------+--------------------+
| user_id | user_name | id_card |
+---------+-----------+--------------------+
| 1 | fh | 123456789456123123 |
| 2 | ms | 123456789456123123 |
| 1 | ms | 123456789456123124 |
+---------+-----------+--------------------+
3 rows in set (0.00 sec)
注意：复合唯一性约束时，必须同时满足几个条件才能唯一约束，满足任何一个都不会受到约束
增加约束
alter table t6 modify id int unique;
查看指定表的约束信息
MariaDB [hellodb]> select * from information_schema.`table_constraints` where table_name='t
+--------------------+-------------------+-----------------+--------------+------------+---
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CO
+--------------------+-------------------+-----------------+--------------+------------+---
| def | hellodb | userid | hellodb | t3 | UN
+--------------------+-------------------+-----------------+--------------+------------+---
1 row in set (0.00 sec)
alter table t6 add constraint un_t6_name unique(name);
MariaDB [hellodb]> select * from information_schema.`table_constraints` where table_name='t6';
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
| def | hellodb | id | hellodb | t6 | UNIQUE |
| def | hellodb | un_t6_name | hellodb | t6 | UNIQUE |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
2 rows in set (0.00 sec)
删除约束通过重定义或者通过索引删除
alter table t6 drop index un_t6_name;
主键约束
建表时添加
create table test1(user_id int(10) primary key,name varchar(10));
主键非空且唯一
MariaDB [hellodb]> insert into test1 values (null,'ms');
ERROR 1048 (23000): Column 'user_id' cannot be null
MariaDB [hellodb]> insert into test1 values (1,'ms');
Query OK, 1 row affected (0.00 sec)
MariaDB [hellodb]> insert into test1 values (1,'ms');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
删除主键
alter table test1 drop primary key;
表存在时增加主键
alter table test1 modify user_id int(10) primary key;
alter table test1 add constraint test1_user_id_pk primary key (user_id);
复合主键
create table test2 (
-> user_id int(10),
-> user_name varchar(30),
-> age tinyint unsigned,
-> constraint test2_userid_username_pk primary key (user_id,user_name)
-> );
注意：在一张表上不能创建多个主键,但是主键可以由两列一起组成
外键（myisam不支持外键）
详细查看一个表的信息
MariaDB [ms]> select * from information_schema.table_constraints where table_name='students' and constraint_schema='ms';
展示创建表的信息
MariaDB [ms]> show create table students;
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| students | CREATE TABLE `students` (
`id` int(10) unsigned NOT NULL,
`name` varchar(10) DEFAULT NULL,
`age` tinyint(3) unsigned DEFAULT NULL,
`classid` tinyint(3) unsigned DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `ms_stu_class_fk` (`classid`),
CONSTRAINT `ms_stu_class_fk` FOREIGN KEY (`classid`) REFERENCES `classes` (`class`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
给现有表增加外键
alter table students add constraint mage_stu_class_fk foreign key(classid) references classes(classid);
增加外键之后，再往含有外键的表中添加没有班级号的学生，会报错，只能添加有班级的学生。
MariaDB [ms]> insert into students values (3,'rgh',46,5);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`ms`.`students`, CONSTRAINT `ms_stu_class_fk` FOREIGN KEY (`classid`) REFERENCES `classes` (`class`))
MariaDB [ms]> insert into students values (3,'rgh',46,3);
Query OK, 1 row affected (0.00 sec)
当一个表是另一个表的外键时，不能直接删除其内容，要想能删除，需要级联
MariaDB [ms]> delete from classes where class=3;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`ms`.`students`, CONSTRAINT `ms_stu_class_fk` FOREIGN KEY (`classid`) REFERENCES `classes` (`class`))
级联删除（删除不含外键表里的内容时，含有外键的表也跟着删除）
alter table students add constraint mage_stu_class_fk foreign key (classid) references classes (classid) on delete cascade;
MariaDB [ms]> delete from classes where class=3;
Query OK, 1 row affected (0.00 sec)
MariaDB [ms]> select * from students;select * from classes;;
+----+------+------+---------+
| id | name | age | classid |
+----+------+------+---------+
| 1 | fk | 40 | 1 |
| 2 | ms | 29 | 2 |
+----+------+------+---------+
2 rows in set (0.00 sec)
+-------+-----------+
| class | classname |
+-------+-----------+
| 1 | xigong |
| 2 | donggong |
+-------+-----------+
2 rows in set (0.00 sec)
不会被级联删除，但是会设置为null
alter table students add constraint mage_stu_class_fk foreign key (classid) references classes (classid) on delete set null;
更新不含外键表里的内容时，含有外键的表也跟着更新
alter table students add constraint mage_stu_class_fk foreign key (classid) references classes (classid) on update cascade;
自增长
增加
alter table students modify id int(10) unsigned auto_increment;
删除
alter table students modify id int(10) unsigned ;
检查约束
在mysql中不支持.
系统变量
set sql_mode='tranditional';
使用索引
查看指定表上的所有索引
show indexes from students\G;
创建索引
create index index_age on students(age);
查看是否使用索引
explain select * from students where age=20\G
统计索引使用的次数
set global userstat=1;
exit重连
show index_statistics;
