MYSQL数据库基础知识及操作

红太狼 2022-06-18 08:16 334阅读 0赞

一、数据库基础知识

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

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

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

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

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

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

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

    4. 使用CREATE INDEX 创建索引

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

    5. 删除索引

      1. ALTER TABLE table_name DROP INDEX index_name;
      2. DROP INDEX index_name ON table_name;

    优点：

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

    缺点：

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

  • 视图：虚拟表

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

    2. 创建及查看视图

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

    3. 修改视图

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

    4. 删除视图

      1. DROP VIEW IF EXISTS view_name;

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

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

      1. CREATE TRIGGER trigger_name trigger_event
      2. on table_name FOR EACH ROW trigger_stmt;
      4. 例：
      5. CREATE TABLE account (acct_num INT, amount DECIMAL(10, 2);
      6. CREATE TRIGGER ins_num BEFORE INSERT ON account 
      7. FOR EACH row SET @sum=@sum+NEW.amount;

    2. 查看触发器

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

    3. 删除触发器

      1. DROP TRIGGER trigger_name;

  • 用户权限

    1. 新建普通用户

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

    2. 删除用户

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

    3. 修改root密码

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

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

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

    5. 收回权限

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

    6. 查看权限

      1. SHOW GRANTS FOR 'user'@'localhost';

  • 数据备份与恢复

    1. MYSQLdump命令备份

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

    2. 数据恢复

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

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

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

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

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

      4. 表的导出和导入

        1. SELECT columns FROM table WHERE condition INTO OUTFILE 'filename' [options];
        2. 例：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]组下配置

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

      <2>. 手动删除二进制

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

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

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

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

      1. [mysqld]
      2. log-error[=path/filename]

    4. 通用查询日志

      1. [mysqld]
      2. log[=path/filename]

    5. 慢查询日志

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

二、数据库操作

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

发表评论

表情：
评论列表 （有 0 条评论，334人围观）

还没有评论，来说两句吧...

相关阅读

    相关 MYSQL数据库基础知识及操作

    一、数据库基础知识 在任何一个关系数据库中，第一范式是对关系模式的基本要求，不满足第一范式的数据库就不是关系数据库 第二范式定义：若关系模式R∈1NF，且关系

    红太狼红太狼/ 0 赞/ 335 阅读