MYSQL数据库基础知识及操作

一、数据库基础知识

• 在任何一个关系数据库中，第一范式是对关系模式的基本要求，不满足第一范式的数据库就不是关系数据库
• 第二范式定义：若关系模式$R\in1NF$，且关系模式R的每个非主属性完全函数依赖于码（候选码），则$R\in2NF$
• 第三范式定义：在2NF基础上，若一个关系模式中所有非主属性完全依赖于码并且不传递依赖于码，则$R\in3NF$
• BC范式定义：设关系模式$R(U, F)\in 1NF$,如果对于R的每个函数依赖$X\to Y$, 若Y不属于X，则X必含有候选码，那么$R\in BCNF$
• 传统的集合运算：交、并、差、广义笛卡尔积
• 专门的关系运算：选择、投影、连接、除

• 索引是一个单独的、存储在磁盘上的数据库结构，它们包含对数据表里所有记录的引用指针。

  1. 组合索引时遵循最左前缀集合

  2. 数据量小的表，建议不要使用索引

     CREATE TABLE PERSON {
         Id int PRIMARY KEY NOT NULL UNIQUE,
         [UNIQUE] [FULLTEXT] [SPATIAL] INDEX(Id)
     };

  3. 在已经存在的表上创建索引

     ALTER TABLE tablename ADD [UNIQUE|FULLTEXT}SPATIAL] [INDEX|KEY]

  4. 使用CREATE INDEX 创建索引

     CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX idnex_name ON table_name (col_name[length],...) [ASC|DESC]

  5. 删除索引

     ALTER TABLE table_name DROP INDEX index_name;
     DROP INDEX index_name ON table_name;

  优点：

  1. 保证数据表每一行数据的唯一性
  2. 大大加快查询速度。
  3. 在实现数据的参考完整性方面，可以加速表与表的链接
  4. 再使用分组和排序子句进行查询时，也可以显著减少查询中分组和排序的时间。

  缺点：

  1. 创建索引和维护索引要耗费时间，数据越大，耗费越多
  2. 索引需要占据磁盘空间。
  3. 对数据进行操作时，索引也要动态维护，维护速度就降低了。

• 视图：虚拟表

  1. 优点：
     <1>. 简单化
     <2>. 安全性
     <3>. 逻辑数据的独立性

  2. 创建及查看视图

     CREATE VIEW view_name (columns_name,...) AS select [column_name] from
     table_name;
     DESC view_name;
     SHOW TABLE STATUS LIKE view_name;

  3. 修改视图

     CREATE OR REPLACE VIEW view_name AS...;
     ALTER VIEW view_name AS...;

  4. 删除视图

     DROP VIEW IF EXISTS view_name;

• 触发器：是一个特殊的存储过程，但不需要CALL语句调用。

  1. 创建Hi you一个执行语句的触发器

     CREATE TRIGGER trigger_name trigger_event
     on table_name FOR EACH ROW trigger_stmt;

     例：
     CREATE TABLE account (acct_num INT, amount DECIMAL(10, 2);
     CREATE TRIGGER ins_num BEFORE INSERT ON account 
     FOR EACH row SET @sum=@sum+NEW.amount;

  2. 查看触发器

     SHOW TRIGGER;
     SELECT * FROM INFOMATION_SHCEMA.TRIGGERS WHERE condition;
     例：
     SELECT * FROM INFOMATION_SCHEMA.TRIGGER WHERE TRIGGER_NAME='trig_update';

  3. 删除触发器

     DROP TRIGGER trigger_name;

• 用户权限

  1. 新建普通用户

     CREATE USER user@localhost IDENTIFIED BY 'password';

     GRANT 语句不仅可以创建新用户，还可以创建的同时对用户授权。
     GRANT privileges[操作] ON db.table[数据表，'.'表示所有表的操作] TO user@localhost IDENTIFIED BY 'password';

  2. 删除用户

     DROP USER user@localhost;
     DELETE FROM MYSQL.user WHERE host='localhost' and user='username';

  3. 修改root密码

     mysqladmin -u username -h localhost -p password "newpawd";
     GRANT USAGE ON *.*  TO 'user'@'localhost' IDENTIFIED BY 'newpwd';
     SET PASSWORD=PASSWORD("newpwd"); 普通用户修改

  4. root用户密码丢失解决方法

     启动--skip-grant-tables选项，MYSQL服务器将不加载权限判断，任何用户都可以访问数据库。
     mysqld_safe --skip-grant-tables user=mysql;
     之后用mysqladmin命令重新设置root密码
     FLUSH PRIVILEGES;

  5. 收回权限

     REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user'@'localhost' ['user2'@'localhost'];

  6. 查看权限

     SHOW GRANTS FOR 'user'@'localhost';

• 数据备份与恢复

  1. MYSQLdump命令备份

     mysqldump -u user -h host -ppassword dynamo[tbname, [tbname]]>filename.sql;
     例：mysqldump -u root -p test > mysql.sql; // 操作系统命令输入窗口中
     mysqldump -u root -p --databases [dbname, dbname]] > myfile.sql
     mysqldump -u root -p --all-databases > filename.sql

  2. 数据恢复

     mysql -u user -p [database_name] < filename.sql;

  3. 数据库迁移
     把数据从一个系统移动到另一个系统上，原因：

     1. 需要安装新的数据库服务器。
     2. MYSQL版本更新

     3. 数据库管理系统的变更

        将www.abs.com主机上的mysql数据库全部迁移到www.bcd.com主机上。
        mysqldump -h www.abc.com -uroot -ppassword daname | 

     4. 表的导出和导入

        SELECT columns FROM table WHERE condition INTO OUTFILE 'filename' [options];
        例：mysql -u root -p "SELECT * FROM test.person" > filename.txt;

• Mysql日志

  1. 日志分类：
     <1>. 错误日志：记录Mysql服务的启动、运行或停止Mysql服务时出现的错误。
     <2>. 查询日志：记录建立的客户端连接或执行的语句。
     <3>. 二进制日志：记录所有更改数据的语句，可以用于数据复制。
     <4>. 慢查询日志：记录所有执行时间超过long_query_time的所有的查询或不使用索引的查询。

  2. 启动和设置二进制日志 【默认下关闭】
     <1>. 主要要在my.cnf/my.ini中配置。【Mac中没有my.cnf文件】在[mysqld]组下配置

     log-bin [=path/[filename]]
     expire_logs_days = 10 //清除过期日志的时间
     max_binlog_size = 100M //单个文件的大小

     <2>. 手动删除二进制

     SHOW binary logs; //查询二进制文件
     PURGE MASTER LOGS TO "filename";

     <3>. 暂停或启用二进制日志

     SET sql_log_bin=0 //stop
     SET sql_log_bin=1 //start

  3. 错误日志
     <1>. 配置

     [mysqld]
     log-error[=path/filename]

  4. 通用查询日志

     [mysqld]
     log[=path/filename]

  5. 慢查询日志

     [mysqld]
     log-slow-queries[=path/filename]
     long_query_time=n //默认为10秒，超过该值就是慢查询

二、数据库操作

1. 设置表的属性值自动增加
   
   • id INT(11) PRIMARY KEY AUTO_INCREMENT
2. 使用默认约束
   
   • Id INT(11) DEFAULT 1111 //默认为1111
3. 使用唯一约束
   
   • name CHAR(20) UNIQUE
4. 查看表的结构
   
   • DESC 表名
5. 修改表名
   
   • ALTER TABLE <旧表名> RENAME [TO] <新表名;
6. 修改字段的数据类型
   
   • ALTER TABLE <表名> MODIFY <字段> <数据类型>
7. 修改字段名
   
   • ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>
8. 添加字段
   
   • ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] [first | after 已存在字段名]
   • ALTER TABLE person ADD column INT(11) First //第一列
   • ALTER TABLE PERSON ADD column INT(100) AFTER NAME //NAME后一列
9. 删除字段
   
   • ALTER TABLE <表名>DROP <字段名>
10. 修改字段的排列位置
    
    • ALTER TABLE <> MODIFY <字段1> <数据类型> FIRST | AFTER <字段2>
11. 更改表的存储引擎
    
    • ALTER TABLE <tablename> <script type="math/tex" id="MathJax-Element-7"> </script> ENGINE=<更改后的引擎名>
12. 删除表的外键约束
    
    • ALTER TABLE <tablename> <script type="math/tex" id="MathJax-Element-8"> </script> DROP FOREIGN KEY <外键约束名> <script type="math/tex" id="MathJax-Element-9"> <外键约束名> </script>
13. 删除数据表
    
    • DROP TABLE $[IF EXIST]$ 表1，表2…