mysql读书笔记

     闲来无事对MYSQL的读书笔记进行了一下整理，记录下来。难道真的是人老了，记性越来越差了。

 

数据库的基本概念
      data       数据
      database   数据库 
      DBMS       数据库管理系统
      DBS        数据库系统
      sql        是结构化查询语言
##########################################################################################################################

数据类型种类
      整形：   可以有十进制和十六进制表示
      符点形： 浮点数由一个数字加一个小数点再加上一个数字组成。两个数字序列不能同时为空。
      字符串： 由单引号或者双引号括起来的字符或者数字，字符串中要用转义字符才能表示的特殊符号。
      NUUL值： 是一种无类型的值，表示“空，什么也没有”。
      日期和时间的值：列如 2005-05-30 12：30：25  日期是按年-月-日
_##########################################################################################################################

数值类型
      TINYTIN   非常小的整数      BIGINT   大整数
      SMALLINT  较小的整数        FLOAT    单精度浮点数
      MEDIUMINT 中等大小的整数    DOUBLE   双精度浮点数
      INT       标准整数          DECIMAL  一个串的浮点数
      CHAR      定长字符串        VARCHAR  可变长字符串
      TINYBLOB  非常小的BLOB      BLOB     小BLOB
      MEDIUMBLOB中等BOLB          LONGBLOB 大BLOB
      TINYTEXT  非常小的文本串    TEXT     小文本串
      MEDIUMTEXT中等文本          LONGTEXT 大文本串
      ENUM      枚举：列可赋予某个枚举成员
      SET       集合：列可赋予多个集合成员
##########################################################################################################################
not null 非空     primary key 主键     auto_increment 自增

      常见的完整性约束
      primary key    主码约束（主键）   主码就是主关键字,能唯一标示字段属性
      unique         唯一约束           在一个字段或者一组字段里数据与表中其它行的数据相比是唯一的
      not null       非空值约束
      auto_increment 整数列自增1
      default_value  默认值约束
##########################################################################################################################
      mysql -h主机名 -u用户名 -p密码         //mysql登陆运行命令

      ######mysql输入一个语句结尾时，末尾要有；号。->意为本语句没有输完 等待继续输入。\c用来取消输入。
      auto_increment 自动标识列
      对于想使用此属性的列应该定义为NOT NULL,并定义为PRIMARY KEY 或者定义为UNIQUE键。
      unsigned无符号，此属性禁用负值，是将数值的取值范围从零开始，即前移了取值范围而已.
##########################################################################################################################

DML数据操作语言
      select  查询
      insert  插入
      update  修改
      delete  删除
##########################################################################################################################

DCL数据控制语言
      grant      控制存取许可
      revoke     存取权限
##########################################################################################################################

ddl数据定义语言
      create table    创建数据表  
      create database 创建库名
      drop   table    数据库对象
      alter  table    定义其列

##########################################################################################################################
      use    库名    选择使用数据库 例如：use book;
##########################################################################################################################

创建数据库

      create database 库名;         创建一个指定名称的数据库
      例如：create database book;   创建了一个名为book的数据库
##########################################################################################################################
删除数据库或表操作

      drop table     表名   删除表  drop table if exists badbird;加if exists 删除不存在表名不会出错
      drop database  库名   删除库  同上加if exists不会出错
##########################################################################################################################
显示结构操作
      database       表名
      desc           表名
##########################################################################################################################

show用法

      show tables或show tables from database_name like; -- 显示当前数据库中所有表的名称。
      show databases;                                   -- 显示mysql中所有数据库的名称。
      show columns from table_name from database_name;
      show columns from database_name.table_name; -- 显示表中列名称。
      show grants for user_name;         -- 显示一个用户的权限，显示结果类似于grant 命令。
      show index from table_name;        -- 显示表的索引。
      show status;                       -- 显示一些系统特定资源的信息，例如，正在运行的线程数量。
      show variables;                    -- 显示系统变量的名称和值。
      show processlist;                  -- 显示系统中正在运行的所有进程，也就是当前正在执行的查询。大多数用户可以查看他们自己的进程，但是如果他们拥有process权限，就可以查看所有人的进程，包括密码。
      show table status;                 -- 显示当前使用或者指定的database中的每个表的信息。信息包括表类型和表的最新更新时间。
      show privileges;                   -- 显示服务器所支持的不同权限。
      show create database database_name;-- 显示create database 语句是否能够创建指定的数据库。
      show create table table_name;      -- 显示create database 语句是否能够创建指定的数据库。
      show engies;                       -- 显示安装以后可用的存储引擎和默认引擎。
      show innodb status;                -- 显示innoDB存储引擎的状态。
      show logs;                         -- 显示BDB存储引擎的日志。
      show warnings;                     -- 显示最后一个执行的语句所产生的错误、警告和通知。
      show errors;                       -- 只显示最后一个执行语句所产生的错误。
      show [storage] engines;            --显示安装后的可用存储引擎和默认引擎。

##########################################################################################################################

select用法

      select prod_uame from products;                     --查询从products表中检索出名字为prod_nume的列（无序排列）
      select prod_uame，user, password  from products;    --查询从products表中检索出名字为prod_nume,user, password的列
      select * from products;                             --查询从products表中检索出全部列（*表示匹配所有的）
      select distinct prod_id from products;              --查询从products表中检索出名字为prod_id的列（只返回唯一的prod_id行）
      select prod_uame from products  limit 5;            --查询从products表中检索出名字为prod_nume的列不超过5行（无序排列）
      select prod_uame from products  limit 5，6;         --查询从products表中检索出名字为prod_nume从行5开始共6列（行从0开始计算）
      select prod_uame from products  limit 6 offset 5;   --查询从products表中检索出名字为prod_nume从行5开始共显示6列（行从0开始计算）
      select products.prod_uame from database.products;   --查询从database库中products表中检索出名字为prod_nume的列（无序排列）
      select prod_price from products order by prod_price DESC limit1; --查询从products表中检索出名字为prod_price的列最大的值（无序排列）
      select prod_price from products where prod_price=2; --查询从products表中检索出名字为prod_price的列中值为2的列（无序排列）

 

##########################################################################################################################

 
    更改表结构操作

      语法：alter table 表名 action;
      说明：action 可以是如下语句：
      add 列名 <建表语句> [first | after 列名]
      add primary key (列名)为表添加一个主键，如果主键已经存在，则出现错误
      alter 列名 set default 默认值     可以更改指定列默认值
----------------------------------------------------------
      later table 表名 add 插如列的名 varchar(属性)      first(之前)|after（之后）  增加列名
      change 旧列名 新列名 <建表语句> [first | after 列名]
      可以更改列类型和列名称，如果原列的名字和新列的名字相同，则change和modify的作用相同
-----------------------------------------------------------
      modify 列名 <建表语句> [first | after 列名]
      更改列属性：alter table badbird(表名) badtitle(列) modif varchar(属性)；
      更改列名：alter table badbird(表名) change badtitle(列) bad_sad(改的名) varchar(属性) not null(no 空);
      drop 列名               //可以删除一列
      drop primary key        //可以删除主键
      drop index index_name;  //可以删除索引
      删除列：alter table badbird(表名) drop badtitle;
      rename as 新表名       //可以将表名更改
      更改表名：alter table badbird(表名) rename badwolf（表名）；
##########################################################################################################################

    插入记录操作
      语法： 
      insert into 表名(字段名,字段名, ... ) values(字段值,字段值,...);
      例如：insert into badwolf (bad_id,bad_name) values(3,'badbird');  插入
      如果表名后面没写字段名，则默认是向所有的字段添加值，另外字符串值应该用‘ ’或“ ”引号括起来
      多条添加：insert into badwolf values(1,'badbird'),(2,'badbird'),(4,'badbird');

    更改记录操作
      语法：
      update 表名 set 列名=数据值 where 子句；
      例如：update badwolf(表名) set bad_name(列名)='badwolf';
------------------------------------------------------------------
      where 子句是判断语句，用来设定条件，限制只更新匹配的行,如果不带where子句，则更新所有行数据
      删除记录操作 语法：  delete from 表名 where子句；
 例如：delete from badwolf(表明) where bad_id=2;  判断删除所有id=2的值 判断条件可以附加

    null和not null
      缺省为null（空）即插入值时没有在此字段插入值，默认为NULL值.
      如果指定了NOT NULL，则必须在插入值时在此字段添入值，不允许插入NULL值。
      default 通过此属性来指定缺省值，如果没有添加值则默认此值。
      zerofill 前导零填充数值类型以达到列的显示宽度。
例如：create table badbird(
      bad int,
      bad unsigned，(无符号，将数值的取值范围从零开始)
      bad unsigned zerofill,(无符号，将数值的取值范围从零开始|前导零填充数值类型以达到列的显示宽度)
      bad unsigned zerofill,(无符号，将数值的取值范围从零开始|前导零填充数值类型以达到列的显示宽度)
     
insert into badbird values(10,10,10,10);
      结果：10,10,0000000010,0000000010。 整形获取值为10，从零开始获取也是10，从零开始以0做填充
insetr into badbird values(-10,-10,-10,-10);
      结果：-10，0，0000000000，0000000000。整形获取值为10，从零开始，从零开始以0做填充
insert into badbird values(2147483647,2147483647,2147483647,2147483647)
      结果：2147483647,2147483647,2147483647,2147483647。整形获取值为10，从零开始获取也是10，从零开始以0做填充 无填充
insert into badbird values(3004005006,3004005006,3004005006,3004005006);
      结果：2147483647，3004005006，3004005006，3004005006。int标准整形最大值

    字符串列类型的属性
binary
      使用此属性可以使列值作为二进制串处理，即比较和排序时区分大小写。
NOT 和NOT NULL
      同数值型功能相同
default
      同数值型功能相同

日期和时间列表
      date   YYYY-MM-DD   格式表示日期值  3字节
      time   hh:mm:ss     格式表示时间值  3字节
      datetime            时间-日期    8字节
      timestamp           时间戳         4字节  时间戳=mysql的时间戳是1970年到至今的豪秒数
      tyar                YYYY年份    1字节
      now()               函数自动获取本地时间
   
mysql算术运算符
      +加  -减  -负  *乘  /除  %取余
      如果第一位是数字的字符串被用于一个算数运算中，那么它被转换为这个数字的值。
      如果一个包含字符和数字混合的字符串不能被正确的转换为数字，那么它被转换成0。
##########################################################################################################################

 mysql比较运算符
     
      and           并且
      or            或
      =             两个操作数相等 为true
      !=|<>         两个操作数不等 为true
      <  <=  >  >=  不用解释你要不知道我也没办法
      in            如果a为b1,b2中的任何一个  为true
      between       如果A的值 在b1与b2之间包括等于b1b2 为true  b1必须比b2大
      like          模式匹对 如果a匹对b 为true
      notlike       模式匹对 如果a不匹对b  为true
      regexp        同like
      not regexp    同notlike
      <=>    a<=>b  如果两者操作数相同 即使null 为true
      is null       如果操作数为null 为true
      is not null   如果操作数不为空 为true
      再次声明mysql 不区分大小写

      %号表示以A开头 任意后缀结尾
      _号表示A开头 后缀任意字
##########################################################################################################################

    查询记录操作
语法：
      select 字段名,字段名, ... (*) from 表名 where 子句   group by子句having子句 order by子句 limit子句 desc(asc)

      group by子句是对匹配where子句的查询结果进行分组
      having子句是对分组后的结果进行条件限制
      order by子句是对查询结果结果进行排序，后面跟desc或asc。　     
      limit子句是对查询的显示结果限制数目
      desc是将最终结果进行降序排列，asc是将最终结果进行升序。
      例如：select * from badbird(表名) where fi=10条件;
##########################################################################################################################

MySQL数据库管理
数据库的启动与关闭,安全、访问控制和权限,数据库的备份与恢复,导入和导出记录

      数据库管理员的职责
      服务器的启动与关闭
      用户帐户维护
      日志文件维护
      数据库的备份与拷贝
      服务器的优化
      数据库管理系统的软件更新
      数据目录的安全性
      服务器的安全性
      灾难恢复
      预防性维护
##########################################################################################################################

    数据库的启动与关闭
   
1、数据库的启动：

    方法1：使用service 命令启动MySQL
    # service mysqld start   // mysqld是MySQL的守护进程，通过运行它来启动MySQL服务。
    方法2：使用mysqld脚本启动MySQL
    # /etc/init.d/mysqld start
    方法3：使用safe_mysqld实用程序启动MySQL服务,此方法可以使用相关参数
    # safe_mysqld&           //使用＆符号将safe_mysqld放在后台运行。
   
2，校验MySQL是否被启动：

    # service mysqld status  //返回如下信息，表示已经启动
      mysqld (pid 1663) is running...  //1663是mysqld运行的进程号，可能根据不同系统运行的进程数量而不同
      若需要重新启动MySQL可以使用如下命令之一：
    # service mysqld restart
    # /etc/init.d/mysqld restart
   
3、数据库的关闭：

    可以使用如下命令之一：
    #  service mysqld stop
    #  /etc/init.d/mysqld stop
    #  /mysqladmin shutdown
##########################################################################################################################
#########     MySQL管理              MySQL管理            MySQL管理            MySQL管理                 MySQL管理   #####
#########     MySQL管理              MySQL管理            MySQL管理            MySQL管理                 MySQL管理   #####
#########     MySQL管理              MySQL管理            MySQL管理            MySQL管理                 MySQL管理   #####
##########################################################################################################################
   
一，修改root管理密码

方法1：
      mysql>set password for ‘帐号’@‘主机’ = old_password(‘输入新密码’);
      update mysql.user set password = old_password(‘输入新密码’) where host = ‘主机’ and user = ‘帐号’;
      flush privileges;

方法2：
      mysqladmin -u 用户  -p 旧密码  password  '新密码';

      用户密码
      对于MySQL密码可以使用PASSWORD()和ENCRYPT()函数进行加密
      mysql>select PASSWORD(“alex”);
      “23fc96e064be0017”
      注：ENCRYPT()在Windows上不可用

方法3：
      使用update语句和password()函数将root口令设置为crp。
      mysql> update mysql.user set password=password('crq')  where user='root';       //返回如下信息，表示授权表user修改成功
      Query OK, 2 row affected (0.09 sec)
      Rows matched:2  changed:2  warnings: 0
      mysql>  flush privileges;   //此时即生效可以使用。注意：由此种方法是直接对授权表user进行修改，而服务器只有在启动时才会加载授权表中的权限设置，因此必须要使用客户端程序mysql环境下的flush privileges命令或使用管理工具mysqladmin的flush-privileges子命令通知服务器重新加载授权表。
##########################################################################################################################

二，改变数据库存储路径

      在Window下，MySQL的所有数据库都保存在“%mysqlroor%\data”目录下。
      停止MySQL服务
      修改%systemroor%\my.ini文件
      [mysqld]
      datadir=D:/data
      将原目录中的所有文件和文件夹内容，全部移动到新的目录D:/data目录中。
      重启MySQL服务
##########################################################################################################################

三，修改MySQL字符集

      找到MySQL配置文件my.ini，一般在C:\window\my.ini。
      在my.ini文件里面加上“default-character-set=gbk #”或gb2312，utf8
      重启MySQL服务
##########################################################################################################################

四，删除匿名用户：

     myslq>  delete from user where user=' ';
     //返回如下信息，表示匿名用户删除成功
     Query OK, 2 row affected (0.03 sec)
   
##########################################################################################################################
   
   

五，添加新的用户权限：

      使用grant语句用于授予用户权限，
      语法： 
      GRANT priv_type[(column_list)][,priv_type[(column_list)]…]
      ON {*.* | * | db_name.* | db_name.tabl_name | db_name}
      TO user_name [IDENTIFIED BY ‘password’]
      [,user_name [IDENTIFIED BY ‘password’]…]
      [WITH GRANT OPTION]
     
##########################################################################################################################
     
     

六，创建新用户

      制定用户名，最长允许为16个英文字符
      制定允许该用户可以访问的数据库和表
      制定允许该用户对数据库做什么的操作
      制定允许该用户从哪些主机/IP进行远程连接
      制定允许该用户对其他用户进行授权或取消授权

      例如：添加一个可以从本地主机连接到MySQL服务器的超级用户crq，但是连接时必须使用口令crqpass
      mysql> grant all on *.* tocrq@localhostidentfied by 'crqpass' with grant option;//返回如下信息，表明权限设置成功
      Query OK, 0 rows affected (0.02 sec)
      例如：使用同样的方法添加一个可以从其他任何地方连接到MySQL服务器的超级用户crq，但是连接时必须使用口令crqpass
      mysql> grant all on *.* tocrq@’%’identified by ‘crqpass’ with grant option;
##########################################################################################################################
七，授予用户特定的权限：

      使用grant语句添加一个可以从本地机连接的MySQL服务器的用户user1，并且只授予其针对sales数据库中employee表的select和insert权限。
      mysql> grant select,insert on sales.employee touser1@localhostidentified by ‘user1pass’;
      说明：使用：# mysql –u user1 –puser1pass 登录数据库以后可以做如下操作：
      mysql> use sales；                 //打开sales数据库
      mysql> show tables;                //只能看到employee表
      mysql> select * from employee;     //可进行查看操作
      mysql> insert into employee …….    //可进行插入操作。
 
 
 
 
 
##########################################################################################################################

八，撤销用户权限：

      语法：REVOKE priv_type[(column_list)][,priv_type[(column_list)]…]
      ON {*.* | * | db_name.* | db_name.tbl_name | db_name}
      FROM user_name [,user_name…]
      例如：撤消用户user1针对sales数据库中employee表的insert权限：
      mysql> revoke insert on sales.employee fromuser1@localhost;
      //返回如下信息，表明权限撤销成功。
      Query OK, 0 row in set affected (0.00 sec)
##########################################################################################################################

九，权限更改何时生效

      当mysqld启动时，所有的授权表内容被读进存储器并且从那点生效。
      用GRANT、REVOKE或SET PASSWORD对授权表施行的修改会立即被服务器注意到。
      如果你手工地修改授权表(使用INSERT、UPDATE等等)，你应该执行一个FLUSH PRIVILEGES语句或运行mysqladmin flush-privileges告诉服务器再装载授权表，否则你的改变将不生效，除非你重启服务器。
##########################################################################################################################

十，权限更改的影响

      当服务器注意到授权表被改变了时，现存的客户连接有如下影响：
      表和列权限在客户的下一次请求时生效。
      数据库权限改变在下一个USE db_name命令生效。
      全局权限的改变和口令改变在下一次客户连接时生效。
##########################################################################################################################

十一， 数据库的备份与恢复

1、数据库的备份：

A、用mysqldump备份数据库：

      语法： mysqldump [OPTIONS] database [table]
      实例：
      将数据库sales备份成sales_bak030630.sql文件。
      # mysqldump –u root –p  --opt sales > sales_bak030630.sql
      //返回如下信息，表明备份成功
      -rw-r--r-- 1 root root  2296 Jun 29 23:30 sales_bak030630.sql
B、用直接拷贝的方法备份数据库：

      不用mysqldump 来备份数据库或表的另一种方法是直接拷贝表文件。通常可利用像cp、tar 或cpio 这样的实用程序来进行。当使用直接拷贝备份（ direct-copy backup）方法时，必须确保没有使用这些表。如果在拷贝一个表的同时服务器正在修改它，则拷贝无效。

      确保拷贝完整性的最好方法是关闭服务器，拷贝文件，然后重新启动服务器。

2、数据库的恢复：

      数据库系统出现崩溃时，应该使用最近的一次备份对数据库进行恢复，如果启用了更新日志，还需要重新执行更新日志中最近一次备份之后对数据库进行修改的任何查询，以尽可能将数据恢复到崩溃时刻所处的状态。
      # mysql –u root –p sales < sales_bak030630.sql
##########################################################################################################################
 优化表
 
      在大多数情况下，优化表的做法很值得。当一个表被多次修改，那它的结构将很快变得支离破碎，其结果是导致性能的下降。这个时候可以使用OPTIMIZE   TABLE 命令来刷新，清理出空间。
      mysql>OPTIMIZE  TABLE   tablename;
##########################################################################################################################
导入和导出记录

      INSERT语句不是向表中插入记录的唯一方法，MySQL还允许使用LOAD  DATA  INFILE  命令一次性插入多条记录。这个命令可以从文本文件读出原始数据（该文件可以放置在连接服务器上或者客户端），在列和行界定符号的基础上进行分析，然后自动产生INSERT 语句来向一个表写数据。

导入数据

      默认情况下，MySQL认为数据文件是在服务器上，在LOAD DATA INFILE 语句中指定位置。
      如果想要使用客户机上的数据，可以在语句中添加LOCAl关键字，告诉ＭｙＳＱＬ在客户机的文件系统上查找文件。
      mysql>LOAD  DATA  LOCAL  INFILE  ‘c:\\data.txt’
      INTO  TABLE user 
      FIELDS TERMINATED  BY  ‘,’  LINES  TERMINATED  BY  ‘\n’;

      最好在此之前加上如下命令
      mysql>LOAD  DATA  LOW_PRIORITY  INFILE  ‘data.txt’  INTO  TABLE  user;
      当然还有IGNORE    REPLACE等关键字。
   
导出数据

      如果不指定一个FIELDS子句，缺省值与这样写的相同：
      FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'
      如果不指定一个LINES子句，缺省值与这样写的相同：
      LINES TERMINATED BY '\n'
      换句话说，缺省值导致读取输入时，LOAD DATA INFILE表现如下：
      在换行符处寻找行边界
      在定位符处将行分成字段
      不要期望字段由任何引号字符封装
      将由“\”开头的定位符、换行符或“\”解释成字段值的部分字面字符

导出数据

      相反，缺省值导致在写入输出时，SELECT ... INTO OUTFILE表现如下：
      在字段之间写定位符
      不用任何引号字符封装字段
      使用“\”转义出现在字段中的定位符、换行符或“\”字符
      在行尾处写换行符
      注意，为了写入FIELDS ESCAPED BY '\\'，对作为一条单个的反斜线被读取的值，你必须指定2条反斜线值。
##########################################################################################################################