数据库的高级操作

herohope 2019-12-26

数据库的高级操作
创建一个名称为xuezhiqian的数据库,并在数据库中创建表student1,插入相应数据,使用SELECT语句查询表student:
mysql> CREATE DATABASE xuezhiqian;
Query OK, 1 row affected
mysql> USE xuezhiqian;
Database changed
mysql> CREATE TABLE student1(
    -> id int primary key auto_increment,
    -> name varchar(20),
    -> age int
    -> );
Query OK, 0 rows affected
mysql> INSERT INTO student(name,age) VALUES(‘Tom‘,20),(‘Jack‘,16),(‘Lucy‘,18);
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0
mysql> SELECT * FROM student1;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | Tom  |  20 |
|  2 | Jack |  16 |
|  3 | Lucy |  18 |
+----+------+-----+
3 rows in set
数据的备份
mysqldump -uusername -ppassword dbname [tbname1 [tbname2…]]>filename.sql格式说明:-u后面的参数username表示用户名,-p后面的参数password表示登录密码,dbname表示需要备份的数据库名称,tbname表示数据库中表名,可以指定一个或多个表,多个表名之间用空格分隔,如果不指定则备份整个数据库,filename.sql表示备份文件的名称,文件名前可以加上绝对路径。
  1、备份单个数据库
    mysqldump -uusername -ppassword dbname [tbname1 [tbname2…]]>filename.sql
 
  2、备份多个数据库
    mysqldump –uusername –ppassword -–database dbname1 [dbname2 dbname3…]        >filename.sql

  3、备份所有数据库
    mysqldump –uusername –ppassword -–all-databases>filename.sql
数据的还原
数据库中的数据遭到破坏时,可以通过备份好的数据文件对数据进行还原,这里所说的还原是指还原数据库中的数据,而库是不能被还原的。  
mysql –uusername –ppassword [dbname] <filename.sql
格式说明:username表示登录的用户名,password表示用户的密码,dbname表示要还原的数据库名称,如果使用mysqldump命令备份的filename.sql文件中包含创建数据库的语句,则不需要指定数据库。
  1、首先需要使用DROP语句将数据库example8删除
    mysql> DROP DATABASE example8;
        Query OK, 1 row affected
        mysql> SHOW DATABASES;
       +--------------------+
       | Database           |
       +--------------------+
       | information_schema |
       | chapter03          |
       | chapter06          |
       | cp07               |
       | cp4                |
       | five               |
       | itcast             |
       | mysql              |
       | performance_schema |
       | test               |
       | xyq                |
       +--------------------+
        11 rows in set

  2、由于库是不能被还原的,因此先要创建一个数据库example8
    mysql> CREATE DATABASE chapter08;        Query OK, 1 row affected
  3、使用mysql语句还原C:/backup目录下的example8_2019.sql文件
    mysql -uroot -pitcast example8 <C:/backup/example8_2019.sql
  4、为了验证数据已经还原成功,可以使用SELECT语句查询example8中的数据SELECT * FROM student;
     mysql> SELECT * FROM student1;
      +----+------+-----+
      | id | name | age |
      +----+------+-----+
      |  1 | Tom  |  20 |
      |  2 | Jack |  16 |
      |  3 | Lucy |  18 |
      +----+------+-----+
      3 rows in set

用户管理
user表
  user表是最重要的一个权限表,它记录了允许连接到服务器的账号信息以及一些全局级的权限信息。
  user表中有42个字段,大致可分为4类用户列、权限列、安全列、资源控制列
创建普通用户
1、使用GRANT语句创建用户,是创建用户最常用的方法
        GRANT privileges ON database.table
        TO ‘username‘@‘hostname‘ [IDENTIFIED BY [PASSWORD]‘password‘]
        [,‘username‘@‘hostname [IDENTIFIED BY [PASSWORD]‘password‘]] …
2、使用CREATE USER语句创建用户
    CREATE USER ‘username‘@‘hostname‘[IDENTIFIED BY [PASSWORD]‘password‘]
        [,‘username‘@‘hostname‘[IDENTIFIED BY [PASSWORD]‘password‘]]…
 
3、使用INSERT语句创建用户
    INSERT INTO mysql.user(Host,User,Password,ssl_cipher、x509_issuer、x509_subject)
    VALUES(‘hostname‘,‘username‘,PASSWORD(‘password‘),‘‘,‘‘,‘‘);
删除普通用户
  1、使用DROP USER语句删除用户
    DROP USER ‘username‘@‘hostname‘[,‘username‘@‘hostname‘];
  2、使用DELETE语句删除用户
    DELETE FROM mysql.user WHERE Host=‘hostname‘ AND User=‘username‘;
修改用户密码
  root用户修改root用户密码
  1、使用mysqladmin命令修改
    mysqladmin –u username [–h hostname] –p password new_password
 
  2、使用UPDATE语句修改
    UPDATE mysql.user set Password=PASSWORD(‘new_password‘)
    WHERE User=‘username‘ and Host=‘hostname‘;
 
  3、使用SET语句修改
    SET PASSWORD=PASSWORD(‘new_password‘);
 
  root用户修改普通用户密码
  1、使用GRANT USAGE语句修改
    GRANT USAGE ON *.* TO ‘username‘@‘localhost‘ IDENTIFIED BY [PASSWORD]‘new_password‘;
 
  2、使用UPDATE语句修改
    UPDATE mysql.user set Password=PASSWORD(‘new_password‘) WHERE User=‘username‘ and Host=‘hostname‘;
 
  3、使用SET语句修改
    SET PASSWORD=PASSWORD(‘new_password‘);
 
  普通用户修改密码
    SET PASSWORD=PASSWORD(‘new_password‘);
如何解决root用户密码丢失问题
  停止MySQL服务
    net stop mysql
  使用--skip-grant-tables启动MySQL服务
    mysql --skip-qrant-tables
  登录MySQL服务器
      重新开启一个运行对话框,在运行对话框中登录MySQL服务器,具体命令如下:
    mysql -u root
  使用UPDATE语句设置root用户密码
    UPDATE mysql.user SET Password=PASSWORD(‘itcast‘) WHERE User=‘root‘ AND Host=‘localhost‘;
  加载权限表
    FLUSH PRIVILEGES;
       上述步骤执行完,可以使用EXIT或\q命令退出服务器,然后使用新密码重新登录。至此,便完成了root用户的密码设置。
 
权限管理
MySQL的权限
  MySQL中的权限信息被存储在mysql数据库的user、db、host、tables_priv、column_priv和procs_priv表中,当MySQL启动时会自动加载这些权限信息,并将这些权限信息读取到内存中。
授予权限
  我们之所以可以对数据进行增删改查的操作,是因为数据库中的用户拥有不同的权限,合理的授权可以保证数据库的安全。
  GRANT privileges [(columns)][,privileges[(columns)]] ON database.table
    TO ‘username‘@‘hostname‘ [IDENTIFIED BY [PASSWORD]‘password‘]
    [,‘username‘@‘hostname‘ [IDENTIFIED BY [PASSWORD]‘password‘]] …
    [WITH with_option [with_option]…]
 
  privileges:表示权限类型。
  columns:参数表示权限作用于某一列,该参数可以省略不写,此时权限作用于整个表,username:表示用户名。
  hostname表示主机名。
  IDENTIFIED BY:参数为用户设置密码。
  PASSWORD参数为关键字.
  password为用户的新密码。WITH关键字后面可以带有多个参数with_option,这个参数有五个取值。
 
  GRANT OPTION:将自己的权限授予其他用户MAX_QUERIES_PER_HOUR count:设置每小时最多可以执行多少次(count)查询。
  MAX_UPDATES_PER_HOUR count:设置每小时最多可以执行多少次更新。
  MAX_CONNECTIONS_PER_HOUR count:设置每小时最大的连接数量。
  lMAX_USER_CONNECTIONS:设置每个用户最多可以同时建立连接的数量。
查看权限
  SHOW GRANTS FOR ‘username‘@‘hostname‘;
收回权限
  为了保证数据库的安全性,需要将用户不必要的权限收回
  1、收回用户指定权限
    REVOKE privileges [columns][,privileges[(columns)]] ON database.table
    FROM ‘username‘@‘hostname‘[,‘username‘@‘hostname‘] …
  2、收回用户全部权限
    REVOKE ALL PRIVILEGES,GRANT OPTION
    FROM  ‘username‘@‘hostname‘ [,‘username‘@‘hostname‘] …

相关推荐

jiong / 0评论 2020-09-17