视图:
简单视图:单张表
复杂视图:多张,子查询
物化视图:
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;