mysql 数据库常用技巧

0

mysql 数据库常用技巧

1. 关于备份数据库

register数据库中有user表和logs表
  1. 备份整个数据库

    mysqldump -uroot -p register > register.sql
    // 备份register数据库并生成register.sql数据库文件
  2. 备份数据库中单张表

    mysqldump -u root -p register user > backups.sql
    // 备份register数据库中user表到backups.sql数据库文件中
  3. 将sql文件写入数据库中

    mysql -u root -p register < backups.sql
    // 将backups.sql文件写入到register数据库中,这里请注意:mysql -u root -p 后面只能跟数据库,而不能跟sql文件!
    这里backups.sql中如果只含有user中的数据,那么执行这个写入过程的时候,不会对logs有任何影响。
  4. 导出数据库的表结构(如果数据库中含有数据,不会导出数据,只会导出字段名)

    mysqldump -d -uroot -p register user > user.sql
    // 将register数据库中的user表结构导出
  5. 备份所有的数据库

    mysqldump -u root -p --all-databases > database_dump.txt
    // 将所有的数据库都备份到database_dump.txt文件中去
  6. 复制表
    使用 SHOW CREATE TABLE 命令获取创建数据表(CREATE TABLE) 语句,该语句包含了原数据表的结构,索引等。

    // 显示创建 user 表的 sql 语句
    SHOW CREATE TABLE workflow_oa

2. 常用命令

  1. 连接数据库

    mysql -h[主机地址] -u[用户名] -p[用户密码] 
    
    //举例:连接本地数据库 mysql -h 127.0.0.1 -u root -p 123456 (有些情况下可以不需要输入主机地址):mysql -u root -p
  2. 创建数据库

    create database [库名];
    
    //举例:创建thinkcms数据库 create database thinkcms;
    
    create database [库名] charset utf8; //创建数据库并存储字符集为utf8
    
    create database [库名] DEFAULT CHARSET utf8 COLLATE utf8_general_ci; # utf8编码
    
    create database [库名] DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci; # gbk编码
  3. 显示所有数据库

    show databases;
  4. 打开数据库

    use [库名];
    
    //举例:使用thinkcms数据库 use thinkcms
  5. 删除数据库

    drop database [库名]; 
    
    //举例:删除thinkcms数据库 drop database thinkcms
  6. 创建数据表

    CREATE TABLE [表名] ([字段名] [字段类型] [字段参数]);
    
    //举例:创建reg数据表并将id设置成自增主键,并将innodb设置成存储引擎,字符集设置成utf8
    mysql> CREATE TABLE reg(
       -> id INT NOT NULL AUTO_INCREMENT,
       -> user VARCHAR(100) NOT NULL,
       -> pwd VARCHAR(40) NOT NULL,
       -> create_time DATE,
       -> PRIMARY KEY (id)
       -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
       
  7. 显示数据表字段名(以及字段名备注)

    desc [表名];    (或者使用:show columns from [表名];)
    
    // 举例:显示reg表的字段名 
    desc reg;  
    show columns from reg;  
    // 显示字段名包含备注
    show full columns from reg;
  8. 显示当前数据库下所有的数据表

    show tables;
  9. 删除数据表

    drop table [表名];
    
    //举例:删除reg表 drop table reg;
  10. 清空表内容

    truncate [表名];
    
    //举例:清除 reg 表中所有内容, truncate reg;
  11. 对数据表重命名

    alter table [旧表名] rename as [新表名];
    
    //举例:将register表名称修改为users
    alter table register rename as users;
  12. 修改表

    • 添加列:

      alter table 表名 add 列名 类型
      // 举例: ALTER TABLE menus ADD status TINYINT(1) DEFAULT 1 COMMENT '是否显示' 
    • 删除列:

      alter table 表名 drop column 列名
    • 修改列:

      alter table 表名 modify column 列名 类型;  -- 类型
      
      alter table 表名 change column 原列名 新列名 类型; -- 列名,类型
      ALTER TABLE students CHANGE COLUMN birth birthday VARCHAR(20) NOT NULL;
    • 添加主键:

      alter table 表名 add primary key(列名);
    • 删除主键:

      alter table 表名 drop primary key;
      
      alter table 表名  modify  列名 int, drop primary key;
    • 添加外键:

      alter table 从表 add constraint 外键名称(形如:FK_从表_主表) foreign key 从表(外键字段) references 主表(主键字段);
      
      // 例子
      ALTER TABLE students
      ADD CONSTRAINT fk_class_id
      FOREIGN KEY (class_id)
      REFERENCES classes (id);
      
      其中,外键约束的名称fk_class_id可以任意,FOREIGN KEY (class_id)指定了class_id作为外键,REFERENCES classes (id)指定了这个外键将关联到classes表的id列(即classes表的主键)
    • 删除外键:

      alter table 表名 drop foreign key 外键名称
      
      // 例子 (不会删除外键字段)
      ALTER TABLE students
      DROP FOREIGN KEY fk_class_id;
    • 修改默认值:

      ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
    • 删除默认值:

      ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;
  13. 对数据表的CURD

    插入数据:
    INSERT INTO table_name ( field1, field2,...fieldN )
                           VALUES
                           ( value1, value2,...valueN );
                           
    //举例:在register表中插入一行数据:
    insert into register (`user`,`time`,`pwd`) values ('小明', '2016-04-22 15:25:33', '1');
    //插入多行数据:
    INSERT INTO employee_tbl (`pid`,`user`,`time`,`pwd`) VALUES 
    ('1', '小明', '2016-04-22 15:25:33', '1'), 
    ('2', '小王', '2016-04-20 15:25:47', '3'), 
    ('3', '小丽', '2016-04-19 15:26:02', '2'), 
    ('4', '小王', '2016-04-07 15:26:14', '4'), 
    ('5', '小明', '2016-04-11 15:26:40', '4'),
    ('6', '小明', '2016-04-04 15:26:54', '2');
    
    更新数据:
    UPDATE table_name SET field1=new-value1, field2=new-value2
    [WHERE Clause];
    
    //举例:在register表中更新第一条数据:
    update register set user="王五",pwd=555 where id=1;
    // 所有80分以下的同学成绩加10分
    UPDATE students SET score=score+10 WHERE score<80;
    
    删除数据:
    DELETE FROM table_name [WHERE Clause];
    
    //举例:在register表中删除第一条数据:
    delete from register where id=1;
    
    查询语句:
    SELECT column_name,column_name
    FROM table_name
    [WHERE Clause];
    
    =====================where 条件=====================
    //举例:在register表中查询id=1的数据:
    select * from register where id=1;
    
    select * from 表 where id > 1 and name != 'aylin' and num = 12;
    select * from 表 where id between 5 and 16;
    select * from 表 where id in (11,22,33)
    select * from 表 where id not in (11,22,33)
    select * from 表 where id in (select nid from 表)
    
    =====================通配符 like=====================
    //在register表中查询所有名字name含有张三的人
    select * from register where name like "%张三%";
    select * from register where name like '张三_'  # 张三开头的所有(一个字符)
    
    =====================限制 limit=====================
    select * from 表 limit 5;            - 前5行
    select * from 表 limit 4,5;          - 从第4行开始的5行
    select * from 表 limit 5 offset 4    - 从第4行开始的5行
    
    =====================排序 asc,desc=====================
    select * from 表 order by 列1 desc,列2 asc    - 根据 “列1” 从大到小排列,如果相同则按列2从小到大排序
    
    =====================分组 group by=====================
    select num from 表 group by num
    select num,nid from 表 group by num,nid
    select num,nid from 表  where nid > 10 group by num,nid order nid desc
    select num,nid,count(*),sum(score),max(score),min(score) from 表 group by num,nid
    select num from 表 group by num having max(id) > 10
    
    特别的:group by 必须在where之后,order by之前
    

3. 其它简单命令

  1. 查询时间:

    select now();
  2. 查询当前用户:

    select user();
  3. 查询数据库版本:

    // 方法01、命令行中直接敲以下命令
    mysql -V (大写的V)
    // 方法02、命令行中输入以下命令
    mysql --help | grep Distrib
    // 方法03、连接数据库之后输入
    status
    // 方法04、直接查询版本
    select version();
  4. 查询当前使用的数据库:

    select database();
  5. 查询当前日期:

    select current_date;
  6. 查询当前时间距离指定时间的天数

    SELECT DATEDIFF(CURDATE(), FROM_UNIXTIME(1527782400, '%Y-%m-%d'))
    // 1527782400对应的是2018-06-01 00:00的时间戳,如果今天是2018-06-07,那么返回的值就是6
  7. 时间戳和时间互相转换

    // 时间戳转换成时间
    SELECT FROM_UNIXTIME(1234567890);
    SELECT FROM_UNIXTIME(1234567890, '%Y-%m-%d %H:%i:%S')
    // 会输出 2009-02-14 07:31:30
    
    // 时间转换成时间戳
    SELECT unix_timestamp("2018-6-1 00:00:00");
    // 会输出 1527782400

4. 默认数据库

  • 默认数据库有如下:
    1. mysql :用户权限相关数据
    2. test : 用于用于测试数据
    3. information_schema : MySQL本身架构相关数据
  • 用户管理:
    1. 创建用户

      create user '用户名'@'IP地址' identified by '密码';
    2. 删除用户

      drop user '用户名'@'IP地址';
    3. 修改用户

      rename user '用户名'@'IP地址'; to '新用户名'@'IP地址';
    4. 修改密码

      set password for '用户名'@'IP地址' = Password('新密码');
  • 权限管理:
    1. 查看权限

      show grants for '用户'@'IP地址'
    2. 授权

      grant  权限 on 数据库.表 to   '用户'@'IP地址'

      2.1. 创建一个alex用户,并赋予权限

      grant all privileges on *.* to  alex@"%" identified by "qazwsx" with grant option;
      
      //grant 是授权命令,其中 alex 是我们连接用的用户名、"qazwsx"是连接密码,用户名后面的 "%" 通用符表示允许各 host 操作。
    3. 取消授权

      revoke 权限 on 数据库.表 from '用户名'@'IP地址'
    4. 刷新授权

      flush privileges;
    5. 授权示例如下:

      grant all privileges on db1.tb1 TO '用户名'@'IP'
      
      grant select on db1.* TO '用户名'@'IP'
      
      grant select,insert on *.* TO '用户名'@'IP'
      
      revoke select on db1.tb1 from '用户名'@'IP'

你可能感兴趣的

载入中...