MySQL数据库管理

一、使用数据库

[root@localhost ~]# mysql -u root -ppwd123(你的数据库密码)
mysql> show databases;        //数据库每条操作语句都是以分号结尾

mysql> use mysql
mysql> show tables;        //用于查看当前所在的数据库中包含的表。在操作之前，需要先使用 USE 语句切换到所使用的数据库

mysql> describe user;        //用于显示表的结构，即组成表的各字段(列)的信息。需要指定“数据库名.表名”作为参数;若只指定表名参数，则需先通过 USE语句切换到目标数据库
mysql> select * from user\G;

• • information_schema数据库：保存着关于MySQL服务器所维护的所有其他数据库的信息。如数据库名，数据库的表，表栏的数据类型与访问权 限等
  • performance_schema：用于监控MySQL server在一个较低级别的运行过程中的资源消耗、资源等待等情况
  • mysql：是 MySQL 服务正常运行所需的数据库，其中包含了用户认证相关的表
• MySQL 数据库的数据文件存放在/usr/local/mysqldata 目录下,每个数据库对应一个子目录，用于存储数据表文件。每个数据表对应为三个文件，扩展名分别为“.frm”、“.MYD”和“.MYI”
  • “.frm”文件是与表相关的元数据(meta)信息都存放在“.frm”文件中，包括表结构的定义信息等。不论是什么存储引擎，每一个表都会有一个以表名命名的“.frm”文件。所有的“.frm”文件都存放在所属数据库的文件夹下面
  • “.MYD”文件是 MyISAM 存储引擎专用,存放 MyISAM 表的数据。每一个 MyISAM 表都会有一个“.MYD”文件与之对应，同样存放于所属数据库的文件夹下，和“.frm”文件在一起
  • “.MYI”文件也是专属于 MyISAM 存储引擎的，主要存放 MyISAM 表的索引相关信息对于 MyISAM 存储来说，可以被 cache 的内容主要就是来源于“.MYI”文件中。每一个MyISAM 表对应一个“.MYI”文件，存放于位置和“.frm”以及“.MYD”一样

SQL语言：结构化查询语言

DDL(Data Definition Language，数据定义语言):用来建立数据库、数据库对象和定义字段，如 CREATE、ALTER、DROP

DML(Data Manipulation Language，数据操纵语言):用来插入、删除和修改数据库中的数据，如INSERT、UPDATE、DELETE

DQL(Data Query Language,数据査询语言):用来查询数据库中的数据,如 SELECT、SHOW、DESCRIBE

DCL(Data Control Language，数据控制语言):用来控制数据库组件的存取许可存取权限等，如 COMMIT、ROLLBACK、GRANT、REVOKE

数据类型

        1.精确数字

        2.近似数字

        3.字符串

        4.二进制字符串

        5.日期和时间

二、创建及删除数据库和表

mysql> create database auth;        //CREATE DATABASE 语句:用于创建一个新的数据库，需指定数据库名称作为参数

CREATE TABLE 表名(字段1名称类型，字段 2名称类型,…, PRIMARY KEY(主键名))        
//CREATE TABLE 语句: 用于在当前数据库中创建新的表,需指定数据表名称作为参数,并定义该表格所使用的各字段
mysql> use auth;
mysql> CREATE TABLE users (user_name CHAR(16) NOT NULL, user_passwd CHAR(48) DEFAULT '', PRIMARY KEY (user_name));
//可以在 auth 数据库中按如下操作创建 users 表;其中，字段定义部分的 DEFAULT 语句用于设置默认的密码字串，PRIMARY 语句用于设置主键字段名
//DEFAULT ''  密码默认为空

DROP TABLE    //DROP TABLE 语句：用于删除数据库中的表，需要指定“数据库名.表名”作为参数;若只指定表名参数，则需先通过执行"USE"语句切换到目标数据库
mysql> DROP TABLE auth.users;    //删除数据表
mysql> DROP DATABASE auth;        //删除数据库

三、管理表中数据记录

INSERT INTO 表名(字段 1,字段 2,...)VALUES(字段1的值，字段2的值,..)        //INSERT INTO 语句:用于向表中插入新的数据记录
mysql>use auth;
mysql>INSERT INTO users(user_name,user_passwd) VALUES('zhangsan', PASSWORD ('123456')); 
//执行以下操作将会向 auth 数据库中的 users 表插入一条记录:用户名为“zhangsan”对应的密码为“123456”。
//需注意的是，VALUES 部分的值应与前面指定的各字段逐一对应
mysql>INSERT INTO users VALUES('lisi', PASSWORD('654321'));
//在插入新的数据记录时，如果这条记录完整包括表中所有字段的值，则插入语句中指定字段的部分可以省略


SELECT 字段名 1,字段名 2....FROM 表名 WHERE 条件表达式    //SELECT 语句:用于从指定的表中查找符合条件的数据记录
mysql>select * from auth.users;        //表示所有字段时，可以使用通配符“*”,若要显示所有的数据记录，则可以省略 WHERE条件子句
mysql>SELECT user_name,user_passwd FROM auth.users WHERE user_name='zhangsan';


UPDATE 表名SET 字段名 1=字段值1[字段名 2=字段值 2]WHERE 条件表达式    UPDATE 语句:用于修改、更新表中的数据记录
mysql>UPDATE auth.users SET user_passwd=PASSWORD('') WHERE user_name= 'lisi'; 
//执行以下操作可以修改 users 表中用户名为“isi”的记录，将密码字串设为空值。验证记录内容可以发现 lisi 用户的密码串值已变为空白
mysql>SELECT * FROM auth.users;
mysql>UPDATE mysql.user SET authentication_string=PASSWORD('123457') WHERE user='root';
//在 MySQL数据库服务器中,用于访问数据库的各种用户(如 root)信息都保存在 mysql数据库的 user 表中，管理员可以直接修改其中的数据记录来完成密码修改或权限赋予，
//但值得注意的是，应当尽量减少人工操作，避免由于操作失误导致数据库无法访问或连接不到数据库等问题。通常都是给对应业务最小权限，某业务用户只负责查询，则只需要给赋予SELECT 权限即可
mysql>FLUSH PRIVILEGES;
[root@www ~]# mysqladmin -uroot -p'123457' password '123456'    
//若是在 Linux 命令行环境中执行，还可以使用 mysqladmin 工具来设置密码。命令格式为“mysqladmin -u root -p'旧密码'password'新密码'”


DELETE FROM 表名 WHERE 条件表达式        //DELETE 语句:用于删除表中指定的数据记录
mysql>DELETE FROM auth.users WHERE user_name='lisi';
mysql>SELECT * FROM auth.users; 
//执行操作可以删除 users 表中用户名为“isi”的数据记录,验证记录内容可以发现 lisi用户的数据记录已经消失

四、数据库表高级操作

        1.清空表

mysql>delete from user01 where user_name='zhangsan';
mysql>delete from user01; 
mysql>insert into user01 select * from auth.users;
mysql>truncate table user01; 
mysql>select count(*) from user01;

• truncate和delete的区别：
  • truncate删除表中的内容，不删除表结构，释放空间，不记录日志
  • delete删除表中的内容，不删除表结构，但不释放空间，记录日志
    • truncate删除数据后重新写数据会从1开始，
    • delete删除数据后只会从删除前的最后一行续写；内存空间上，truncate省空间
  • delete属于DML语句，而truncate和drop都属于DDL语句
    • delete可以在后续加上where进行针对行的删除
    • truncate和drop后面只能加上表名，直接删除表，无法where
• drop table 表名  删除表，内容连带结构一起删除；

        2.临时表

mysql>select * from mytmp;     //查看 mytmp 表是否存在

mysql>CREATE TEMPORARY TABLE `mytmp` ( 
 `id` int(10) NOT NULL AUTO_INCREMENT, 
 `NAME` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, 
 `level` int(10) NOT NULL, 
 PRIMARY KEY (id)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;         //创建临时表
 mysql> insert into mytmp(name,level) values('aa',10);         //插入数据
 
 mysql>select * from mytmp; 
mysql>quit //退出当前连接 
Bye 
mysql>select * from mytmp;     //重新连接 MySQL 之后查看临时表状态

//临时表创建成功之后，使用 SHOW TABLES 命令是看不到创建的临时表的，临时表会在连接退出后被销毁。如果在退出连接之前，也可以手动直接删除
mysql>drop table mytmp;

        3.克隆表

//能够快速、完整的拷贝数据表
mysql>drop table tmp;
mysql> create table tmp as select * from player;        
//AS作为链接语句操作符，可以实现创建表并且将原表的数据拷贝过来，但是这种方法存在一个问题,就是表的索引、默认值等无法复制过来

//方法一:通过 LKE方式克隆表；；通过在创建表时使用 LIKE方法，完整复制表结构
//LIKE方法可以将源表完全一样的复制生成一个新表，包括表的备注、索引、主键、存储引擎等，但是不会复制源表内数据记录
mysal>create table test like mytmp;        //通过 LIKE 方法，复制 mytmp 表生成 test 表
mysql>show create table test\G
mysql>select * from test;            //LIKE 方法复制表结构，不复制数据
mysql>insert into test select *from mytmp;        //将 mytmp 表的数据写入 test 表
mysql>select * from test;

//方法二：通过创建表的方式克隆表；；使用 SHOW CREATE TABLE 命令来获取源表的表结构、索引等信息。
//复制源表结构并修改表名为目标名字,然后执行创建新表的语句。通过这步操作,就可以获得一个和源表结构一样的克降表了。
mysql>show create table mytmp\G
mysql>CREATE TEMPORARY TABLE `test` ( //改名后创建新表
 `id` int(10) NOT NULL AUTO_INCREMENT, 
 `NAME` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, 
 `level` int(10) NOT NULL, 
 PRIMARY KEY (id)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 mysql> insert into test select* from mytmp;        //导入原表数据
 mysql>select * from from mytmp;

数据库用户授权

        1.授予权限

GRANT 权限列表 ON数据库名.表名 TO 用户名@来源地址[IDENTIFIED BY'密码' ]
//GRANT语句:专门用来设置数据库用户的访问权限。当指定的用户名不存在时，GRANT 语句将会创建新的用户;当指定的用户名存在时，GRANT 语句用于修改用户信息

//执行以下操作可以添加一个名为“xiaoqi”的数据库用户，并允许其从本机访问，对 auth数据库中的所有表具有查询权限，验证密码为“123456"
mysgl>GRANT select ON auth.* To 'xiaoqi'@'localhost' IDENTIFIED BY '123456';

• 注意事项：
• 权限列表:用于列出授权使用的各种数据库操作，以逗号进行分隔，如“select，insert,update”。使用“al”表示所有权限，可授权执行任何操作
• 数据库名.表名:用于指定授权操作的数据库和表的名称，其中可以使用通配符"*"
• 用户名@来源地址:用于指定用户名称和允许访问的客户机地址，即谁能连接、能从哪里连接。来源地址可以是域名、IP地址，还可以使用“%"通配符，表示某个区域或网段内的所有地址
• IDENTIFIED BY:用于设置用户连接数据库时所使用的密码字符串。在新建用户时，若省略“IDENTIFIED BY”部分，则用户的密码将为空

        2.查看权限

SHOW GRANTS FOR 用户名@来源地址
//SHOW GRANTS 语句:专门用来查看数据库用户的授权信息，通过 FOR 子句可指定查看的用户对象(必须与授权时使用的对象名称一致)

//执行以下操作可以査看用户 dbuser 从 192.168.10.102 访问数据库时的授权信息
mysqI>SHOW GRANTS FOR 'dbuser'@'192.168.10.102';

        3.撤销权限

REVOKE 权限列表ON数据库名.表名FROM 用户名@来源地址
//REVOKE 语句:用于撤销指定用户的数据库权限，撤销权限后的用户仍然可以连接到MySQL服务器，但将被禁止执行对应的数据库操作

//执行以下操作可以撤销用户 xiaoqi 从本机访问数据库 auth 的所有权限
mysql>REVOKE all ON auth.* FROM 'xiaogi'@'localhost';
mysqI>SHOW GRANTS FOR'xiaoqi'@"localhost;//确认已撤销对 auth 库的权限