一、数据库基础知识

  • 在任何一个关系数据库中，第一范式是对关系模式的基本要求，不满足第一范式的数据库就不是关系数据库
  • 第二范式定义：若关系模式R∈1NF，且关系模式R的每个非主属性完全函数依赖于码（候选码），则R∈2NF
  • 第三范式定义：在2NF基础上，若一个关系模式中所有非主属性完全依赖于码并且不传递依赖于码，则R∈3NF
  • BC范式定义：设关系模式R(U,F)∈1NF,如果对于R的每个函数依赖X→Y, 若Y不属于X，则X必含有候选码，那么R∈BCNF
  • 传统的集合运算：交、并、差、广义笛卡尔积
  • 专门的关系运算：选择、投影、连接、除
  • 索引是一个单独的、存储在磁盘上的数据库结构，它们包含对数据表里所有记录的引用指针。
  1. 组合索引时遵循最左前缀集合
  2. 数据量小的表，建议不要使用索引
CREATE TABLE PERSON {
    Id int PRIMARY KEY NOT NULL UNIQUE,
    [UNIQUE] [FULLTEXT] [SPATIAL] INDEX(Id)
};
  1. 在已经存在的表上创建索引
ALTER TABLE tablename ADD [UNIQUE|FULLTEXT}SPATIAL] [INDEX|KEY]
  1. 使用CREATE INDEX 创建索引
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX idnex_name ON table_name (col_name[length],...) [ASC|DESC]
  1. 删除索引
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;
  1. 修改视图
CREATE OR REPLACE VIEW view_name AS...;
ALTER VIEW view_name AS...;
  1. 删除视图
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;
  1. 查看触发器
SHOW TRIGGER;
SELECT * FROM INFOMATION_SHCEMA.TRIGGERS WHERE condition;
例：
SELECT * FROM INFOMATION_SCHEMA.TRIGGER WHERE TRIGGER_NAME='trig_update';
  1. 删除触发器
DROP TRIGGER trigger_name;
  • 用户权限
  1. 新建普通用户
CREATE USER user@localhost IDENTIFIED BY 'password';
GRANT 语句不仅可以创建新用户，还可以创建的同时对用户授权。
GRANT privileges[操作] ON db.table[数据表，'.'表示所有表的操作] TO user@localhost IDENTIFIED BY 'password';
  1. 删除用户
DROP USER user@localhost;
DELETE FROM MYSQL.user WHERE host='localhost' and user='username';
  1. 修改root密码
mysqladmin -u username -h localhost -p password "newpawd";
GRANT USAGE ON *.*  TO 'user'@'localhost' IDENTIFIED BY 'newpwd';
SET PASSWORD=PASSWORD("newpwd"); 普通用户修改
  1. root用户密码丢失解决方法
启动--skip-grant-tables选项，MYSQL服务器将不加载权限判断，任何用户都可以访问数据库。
mysqld_safe --skip-grant-tables user=mysql;
之后用mysqladmin命令重新设置root密码
FLUSH PRIVILEGES;
  1. 收回权限
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user'@'localhost' ['user2'@'localhost'];
  1. 查看权限
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
  1. 数据恢复
mysql -u user -p [database_name] < filename.sql;
  1. 数据库迁移
    把数据从一个系统移动到另一个系统上，原因：
  1. 需要安装新的数据库服务器。
  2. MYSQL版本更新
  3. 数据库管理系统的变更
将www.abs.com主机上的mysql数据库全部迁移到www.bcd.com主机上。
mysqldump -h www.abc.com -uroot -ppassword daname |
  1. 表的导出和导入
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
  1. 错误日志
    <1>. 配置
[mysqld]
log-error[=path/filename]
  1. 通用查询日志
[mysqld]
log[=path/filename]
  1. 慢查询日志
[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 [IFEXIST]