1. 数据库备份与恢复
数据库备份用命令mysqldump ，MySQL的备份文件一般以.sql结尾，做到见名知意
#备份testdb数据库重定向为testdb.sql文件， [root@localhost ~]# mysqldump -uroot -p123 --databases testdb > testdb.sql #备份所有的数据库 [root@localhost ~]# mysqldump -uroot -p123 --all-databases > all_databases.sql #查看备份的文件 [root@localhost ~]# ls all_databases.sql testdb.sql [root@localhost ~]#vimttestdb.mysql ……… CREATE DATABASE /*!32312 IF NOT EXISTS*/ `testdb` /*!40100 DEFAULT CHARACTER SET utf8 */; USE `testdb`; -- -- Table structure for table `areas` -- DROP TABLE IF EXISTS `areas`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `areas` ( `aid` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT NULL, `pid` int(11) DEFAULT NULL, PRIMARY KEY (`aid`) ) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `areas` -- LOCK TABLES `areas` WRITE; /*!40000 ALTER TABLE `areas` DISABLE KEYS */; INSERT INTO `areas` VALUES (1,'北京市',NULL),(2,'天津市',NULL),(3,'河北省',NULL),(4,'山西省 ',NULL),(5,'海淀区',1),(6,'滨海区',2),(7,'沧州市',3),(8,'大同市',4),(9,'朝阳区',1),(10,'武>清区',2),(11,'石家庄',3),(12,'太原市',4),(13,'西二旗',5),(14,'大港',6),(15,'任丘市',7),(16,'清徐',8),(17,'中关村',5),(18,'汉沽',6),(19,'河间市',7),(20,'阳曲',8); /*!40000 ALTER TABLE `areas` ENABLE KEYS */; UNLOCK TABLES; …….. #恢复用命令source MariaDB [(none)]> drop database testdb; Query OK, 3 rows affected (0.082 sec) MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ 3 rows in set (0.000 sec) MariaDB [(none)]> source testdb.sql #导入备份文件 Query OK, 0 rows affected (0.001 sec) Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.000 sec) ……. MariaDB [testdb]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | testdb | +--------------------+ #备份数据库下的某个表，先跟数据库名，再跟表名，如备份testdb下的students表： MariaDB [testdb]> exit Bye [root@localhost ~]# mysqldump -uroot -proot testdb students > students.sql [root@localhost ~]# ls all_databases.sql students.sql testdb.sql #库名后跟的表名可以是多个，以空格分开，即备份同一数据库下的多个表 [root@localhost ~]# mysqldump -uroot -p123 testdb students classes areas > bak.sql [root@localhost ~]# ls all_databases.sql students.sql testdb.sql bak.sql
2. 视图
对于复杂的查询，在多个地方被使用，如果需求发生了改变，需要更改sql语句，则需要在多个地方进行修改，维护起来非常麻烦，
假如因为业务需要将某一表拆分为另外两份表，相对来说原来的表没有了，如果要继续使用该表有两种方案：找前端人员要求每个前端人员将他们相关的调用语句修改了，第二种方法是通过某种方法使得查看原来的表时让它“存在”，此时的表并不是真正的表，而是视图。当然，利用视图的方法更好。
2.1 创建视图
格式： create view 视图名称 as select语句;
视图与表的效果相近，因此在创建视图的名称是一般以v_开头表名是视图文件。
创建视图所含的表如果有相同的字段名必须在创建过程中起别名，使得新创建的一个“表”仅有一个相同的字段，如下列所示。
MariaDB [testdb]> create view v_students as select * from classes inner join students where classes.id=students.cls_id; ERROR 1060 (42S21): Duplicate column name 'id' #重复的id列名 MariaDB [testdb]> create view v_students as select classes.name as cl_name,students.name as stu_name from classes inner join students where classes.id=students.cls_id; MariaDB [testdb]> show tables; +------------------+ | Tables_in_testdb | +------------------+ | areas | | classes | | students | | v_students | +------------------+
2.2 查看视图
格式：show tables ，查看表会将所有的视图也列出来
MariaDB [testdb]> select * from v_students order by cl_name; #查看将两个表联系起来的一个“表”，即视图 +-------------+-------------+ | cl_name | stu_name | +-------------+-------------+ | 云唯_01期 | 周杰伦儿 | | 云唯_01期 | 钱小豪 | | 云唯_01期 | 陈冠希 | | 云唯_01期 | 小明 | | 云唯_01期 | 彭于晏 | | 云唯_01期 | 黄蓉 | | 云唯_01期 | 王祖贤 | | 云唯_01期 | 周杰 | | 云唯_01期 | 谢霆锋 | | 云唯_02期 | 小月月 | | 云唯_02期 | 刘德华 | | 云唯_02期 | 凤姐 | | 云唯_02期 | 和珅 | | 云唯_02期 | 程坤 | +-------------+--------------+
2.3 删除视图
格式：drop view 视图名
MariaDB [testdb]> drop view v_students; Query OK, 0 rows affected (0.000 sec) MariaDB [testdb]> show tables; +---------------------+ | Tables_in_testdb | +---------------------+ | areas | | classes | | students | +---------------------+
3. 事务
假设A欠了B 500 元，A现在给B还钱，对数据库而言，还钱有三个步骤：
1、检查A的账户余额>500元；
2、A 账户中扣除500元；
3、B 账户中增加500元。
如果A现已将前从自己的账户中扣除，在B还没有收到数据库的数据时数据库宕了，会出现A已经将钱从自己账户扣除、B的账户并没有增加的现象。
但现实中对数据库来说这种情况是不存在的，因为数据库存在事务这个概念。
将上述步骤的操作打包成一个事务中，任何一个步骤失败，则必须回滚所有步骤。
数据库中事务默认是关闭的，使用start transaction或begin开启一个事务，完成操作后使用commit命令提交修改或使用rollback撤销所有修改。
3.1 事务的ACID特性
1）原子性（A，atomicity）：事务内的所有操作要么都执行，要么都不执行，视所有操作步骤为一个整体，它是一个不可分割的工作单位。一个执行步骤失败，全部步骤失败，全部步骤成功任务才成功。
2）一致性（C，consistency）：数据库的前后一致，数据库总是从一个一致性的状态转换到另一个一致性的状态。
3）隔离性（I，isolation）：一个事物所做的修改在最终提交之前，对其他事物是不可见的。
4）持久性（D，durability）：将数据写入磁盘当中实现永久保存。事务完成后，该事务内涉及的数据必须持久性的写入磁盘保证其持久性。当然，这是从事务的角度来考虑的的持久性，从操作系统故障或硬件故障来说，这是不一定的。
3.2 事务命令
要求：表的引擎类型必须是innodb类型才可以使用事务，这是mysql表的默认引擎。
查看表的创建语句，可以看到engine=innodb。
命令：
show create table students;
修改数据的命令会触发事务，包括insert、update、delete
开启事务：begin，开启事务后执行修改命令，变更会维护到本地缓存中，而不维护到物理表中。
提交事务：commit，将缓存中的数据变更维护到物理表中。
回滚事务：rollback，放弃缓存中变更的数据。
结束事务：commit和rollback均可结束事务。
#为了体现隔离性，开启两个窗口1和2 #窗口1： MariaDB [testdb]> select * from students; +---+---------------+------+--------+--------+-------+-----------+ | id | name | age | high | gender |cls_id | is_delete | +----+--------------+------+--------+-------+--------+-----------+ | 1 | 小明 | 18 | 180.00 | 男 | 1 | | | 2 | 小月月 | 19 | 180.00 | 男 | 2 | | | 3 | 彭于晏 | 28 | 185.00 | 男 | 1 | | | 4 | 刘德华 | 58 | 175.00 | 男 | 2 | | | 5 | 黄蓉 | 108 | 160.00 | 女 | 1 | | | 6 | 凤姐 | 44 | 150.00 | 保密 | 2 | | | 7 | 王祖贤 | 52 | 170.00 | 女 | 1 | | | 8 | 周杰伦儿 | 34 | NULL | 男 | 1 | | | 9 | 程坤 | 44 | 181.00 | 男 | 2 | | | 10 | 和珅 | 55 | 166.00 | 男 | 2 | | | 11 | 刘亦菲 | 29 | 162.00 | 女 | 3 | | | 12 | 金星 | 45 | 180.00 | 中性 | 4 | | | 13 | 静香 | 18 | 170.00 | 女 | 4 | | | 14 | 郭靖 | 22 | 167.00 | 男 | 5 | | | 15 | 周杰 | 33 | 178.00 | 男 | 1 | | | 16 | 钱小豪 | 56 | 178.00 | 男 | 1 | | | 17 | 谢霆锋 | 38 | 175.00 | 男 | 1 | | | 18 | 陈冠希 | 38 | 175.00 | 男 | 1 | | +----+-------------+------+--------+--------+--- ----+-----------+ MariaDB [testdb]> begin； #开启事务 MariaDB [testdb]> delete from students where id=17; MariaDB [testdb]> delete from students where id=18; #删除两条数据 MariaDB [testdb]> select * from students; ……… | 15 | 周杰 | 33 | 178.00 | 男 | 1 | | | 16 | 钱小豪 | 56 | 178.00 | 男 | 1 | | +----+-------------+------+--------+--------+--------+-----------+ #窗口2： MariaDB [testdb]> select * from students; ……. | 16 | 钱小豪 | 56 | 178.00 | 男 | 1 | | | 17 | 谢霆锋 | 38 | 175.00 | 男 | 1 | | | 18 | 陈冠希 | 38 | 175.00 | 男 | 1 | | +-- -+-------------+------+--------+--------+--------+-----------+ #此时窗口1并没有提交（commit），切换到窗口2查看students表，发现并没有删掉该该两条数据，这便是事务的隔离性，一个事务最终修改在提交之前对其他事务是不可见的。 #窗口1： MariaDB [testdb]> commit; #提交 #窗口2： MariaDB [testdb]> select * from students; ……… | 15 | 周杰 | 33 | 178.00 | 男 | 1 | | | 16 | 钱小豪 | 56 | 178.00 | 男 | 1 | | +---+--------------+------+--------+--------+--------+-----------+ MariaDB [testdb]> begin; #开启事务 Query OK, 0 rows affected (0.000 sec) MariaDB [testdb]> delete from students where id=15; #删掉一行数据 Query OK, 1 row affected (0.002 sec) MariaDB [testdb]> select * from students; ……. | 14 | 郭靖 | 22 | 167.00 | 男 | 5 | | +---+--------------+------+--------+--------+--------+-----------+ MariaDB [testdb]> rollback; #回退，后结束事务 Query OK, 0 rows affected (0.000 sec) MariaDB [testdb]> select * from students; ……. | 15 | 周杰 | 33 | 178.00 | 男 | 1 | | +---+--------------+------+--------+--------+--------+-----------+ #事务之中一个步骤出错，所有步骤出错： MariaDB [testdb]> begin; Query OK, 0 rows affected (0.000 sec) MariaDB [testdb]> delete from students where id=13; Query OK, 1 row affected (0.000 sec) MariaDB [testdb]> delete from students where id=14; #执行两个步骤 Query OK, 0 rows affected (0.000 sec) MariaDB [testdb]> select * from students; ……. | 12 | 金星 | 45 | 180.00 | 中性 | 4 | | +---+--------------+------+--------+--------+--------+-----------+ #在没有结束事务之前结束任务，前面执行的两个步骤都是失败的: MariaDB [testdb]> exit Bye [root@localhost ~]# mysql -uroot -p123; MariaDB [(none)]> use testdb; MariaDB [testdb]> select * from students; | 13 | 静香 | 18 | 170.00 | 女 | 4 | | | 14 | 郭靖 | 22 | 167.00 | 男 | 5 | | +---+--------------+------+---------+-------+--------+-----------+ #注意： truncate执行命令是对硬盘数据进行删除，无法回滚
MariaDB端口：3306。
4. 索引
索引主要用于快速查询。但如果遇到增删改，索引又变得鸡肋。主键默认有索引的功能，因此不必为主键创建索引
4.1 创建索引
方式1：创建表时创建索引，如：
MariaDB [testdb]> create table create_index( -> id int primary key, -> name varchar(10) unique, -> age int, -> key (age) -> );
方式2：对于已创建的表，添加索引
如果指定字段是字符串，需要指定长度，建议长度与定义字段时的长度一致；
字段类型如果不是字符串，可以不填写长度部分
格式：create index 索引名称 on 表名(字段名称(长度))，如
create index age_index on create_index(age);
4.2 查看索引
MariaDB [testdb]> show create table create_index; …….. | create_index | CREATE TABLE `create_index` ( `id` int(11) NOT NULL, `name` varchar(10) DEFAULT NULL, `age` int(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`), KEY `age` (`age`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | ……….
4.3 删除索引
格式： drop index 索引名称 on 表名;
MariaDB [testdb]> drop index age on create_index; MariaDB [testdb]> show create table create_index; …….. | create_index | CREATE TABLE `create_index` ( `id` int(11) NOT NULL, `name` varchar(10) DEFAULT NULL, `age` int(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
4.4 测试时间
打开sql语句执行时间：set profiling=1
关闭sql语句执行时间：set profiling=0
查看sql执行时间：show profiles
5. 外键
5.1 概述
外键是一种约束。如果一个实体的某个字段指向另一个实体的主键，就称为外键。被指向的实体，称之为主实体（主表），也叫父实体（父表）。负责指向的实体，称之为从实体（从表），也叫子实体（子表）
对关系字段进行约束，当为从表中的关系字段填写值时，会到关联的主表中查询此值是否存在，如果存在则填写成功，如果不存在则填写失败并报错。
5.2 查看外键
show create tables 表名
5.3 添加外键
如：alter table students add constraint fk_students_gid foreign key (gid) references grade(gid) on delete cascade; on deleate cascade 允许级联删除
#给students的cls_id设置外键fkey，参考classes表的id MariaDB [testdb]> alter table students add constraint fkey foreign key (cls_id) references classes(id); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`testdb`.`#sql-50a_d`, CONSTRAINT `fkey` FOREIGN KEY (`cls_id`) REFERENCES `classes` (`id`)) #此处报错并不是语法错误，而是因为students.csl_id有1、2、3、4、5，而classes.id只有1、2，无法一一对应，为完成实验，删除students.csl_id为3、4、5的数据 MariaDB [testdb]> delete from students where cls_id>2; Query OK, 4 rows affected (0.001 sec) MariaDB [testdb]> alter table students add constraint fkey foreign key (cls_id) references classes(id); Query OK, 10 rows affected (0.046 sec) Records: 10 Duplicates: 0 Warnings: 0 #测试外键：外键对应id只有1、2，因此无法插入cls_id 为大于2的数据 MariaDB [testdb]> insert into students values (0,'小明',18,180.2,2,2,0); Query OK, 1 row affected (0.001 sec) MariaDB [testdb]> insert into students values (0,'小明',18,180.2,2,3,0); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`testdb`.`students`, CONSTRAINT `fkey` FOREIGN KEY (`cls_id`) REFERENCES `classes` (`id`)) #外键约束classes表和students表有联系即classes表下有students表，因为设置外键后并没有允许级联删除，因此无法删除 MariaDB [testdb]> delete from classes where id=1; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`testdb`.`students`, CONSTRAINT `fkey` FOREIGN KEY (`cls_id`) REFERENCES `classes` (`id`)) MariaDB [testdb]> delete from classes where id=2; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`testdb`.`students`, CONSTRAINT `fkey` FOREIGN KEY (`cls_id`) REFERENCES `classes` (`id`)) MariaDB [testdb]>
5.4 删除外键
不知道设置的外键名可以用show create table 查看表名，含有外键信息；
格式：alter table 表名 drop foreign key 外键名
MariaDB [testdb]> show create table students; CONSTRAINT `fkey` FOREIGN KEY (`cls_id`) REFERENCES `classes` (`id`) MariaDB [testdb]> alter table students drop foreign key fkey; Query OK, 0 rows affected (0.002 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [testdb]> show create table students;
5.5 级联删除
#设置外键允许级联删除 MariaDB [testdb]> alter table students add constraint fkey1 foreign key(cls_id) references classes(id) on delete cascade; MariaDB [testdb]> select * from students order by cls_id; #按班分类 +----+-------------+------+-------+---------+------+----------+ | id | name | age | high | gender |cls_id|is_delete | +----+-------------+------+-------+---------+------+----------+ | 1 | 小明 | 18 | 180.0 | 男 | 1 | | | 3 | 彭于晏 | 28 | 185.00 | 男 | 1 | | | 5 | 黄蓉 | 108 | 160.00 | 女 | 1 | | | 8 | 周杰伦儿 | 34 | NULL | 男 | 1 | | | 7 | 王祖贤 | 52 | 170.00 | 女 | 1 | | | 10 | 和珅 | 55 | 166.00 | 男 | 2 | | | 9 | 程坤 | 44 | 181.00 | 男 | 2 | | | 6 | 凤姐 | 44 | 150.00 | 保密 | 2 | | | 4 | 刘德华 | 58 | 175.00 | 男 | 2 | | | 2 | 小月月 | 19 | 180.00 | 男 | 2 | | | 19 | 小明 | 18 | 180.20 | 女 | 2 | | +----+-------------+------+--------+--------+-------+---------+ 11 rows in set (0.000 sec) #现在可以删除classes的数据了 MariaDB [testdb]> delete from classes where id=1; Query OK, 1 row affected (0.001 sec) MariaDB [testdb]> select * from students; +----+----------+------+--------+--------+-------+----------+ | id | name | age | high | gender | cls_id| is_delete| +----+----------+------+--------+--------+-------+----------+ | 2 | 小月月 | 19 | 180.00 | 男 | 2 | | | 4 | 刘德华 | 58 | 175.00 | 男 | 2 | | | 6 | 凤姐 | 44 | 150.00 | 保密 | 2 | | | 9 | 程坤 | 44 | 181.00 | 男 | 2 | | | 10 | 和珅 | 55 | 166.00 | 男 | 2 | | | 19 | 小明 | 18 | 180.20 | 女 | 2 | | +----+----------+------+--------+--------+-------+----------+
4248
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
