每天进步一点点之Mysql基础教程

Mysql技术总结

1. 用户管理

a) 用户登录

格式: mysql -h主机地址 -u用户名 -p用户密码

例:mysql -h110.110.110.110 -uroot -p123

本地可以直接mysql –uroot -p

b) 用户退出

exit、quit

c) 添加用户

1)insert into

mysql.user表保存的是用户的登录信息。因此可以直接对其进行插入数据的操作,这样可以实现添加用户

insert into mysql.user (host,user,password) values(‘%’,’jifei’,PASSWORD(‘jifei’)); 最新版本这个会出现问题。

注:对于上面的命令首先是password字段已经改名为authentication_string。

其次改正之后还是会报

mysql> insert into mysql.user (host,user,authentication_string) values('%','jifei',PASSWORD('jifei')); 
  ERROR 1364 (HY000): Field 'ssl_cipher' doesn't have a default value

2)grant

grant select on 数据库.* to ‘用户名’@’登录主机’ identified by ‘密码’ WITH GRANT OPTION;

注意:上述的数据库名和数据表名不需要引号。

GRANT命令说明:
ALL PRIVILEGES 是表示所有权限,你也可以使用select、update等权限。

ON 用来指定权限针对哪些库和表。

*.* 中前面的*号用来指定数据库名,后面的*号用来指定表名。

TO 表示将权限赋予某个用户。

jack@’localhost’ 表示jack用户,@后面接限制的主机,可以是IP、IP段、域名以及%,%表示任何地方。注意:这里%有的版本不包括本地,以前碰到过给某个用户设置了%允许任何地方登录,但是在本地登录不了,这个和版本有关系,遇到这个问题再加一个localhost的用户就可以了。

IDENTIFIED BY 指定用户的登录密码。

WITH GRANT OPTION 这个选项表示该用户可以将自己拥有的权限授权给别人。注意:经常有人在创建操作用户的时候不指定WITH GRANT OPTION选项导致后来该用户不能使用GRANT命令创建用户或者给其它用户授权。

备注:可以使用GRANT重复给用户添加权限,权限叠加,比如你先给用户添加一个select权限,然后又给用户添加一个insert权限,那么该用户就同时拥有了select和insert权限。

mysql> grant select,insert on test_db.test_table to 'test_user'@'localhost' identified by '123456' with grant option;
  Query OK, 0 rows affected, 1 warning (0.00 sec)

d) 用户权限

1)添加权限

grant select on 数据库.* to ‘用户名’@’登录主机’ identified by ‘密码’ WITH GRANT OPTION;

这个命令跟创建用户的命令是一模一样的。

权限列表:

权限 权限级别 权限说明
CREATE 数据库、表或索引 创建数据库、表或索引权限
DROP 数据库或表 删除数据库或表权限
GRANT OPTION 数据库、表或保存的程序 赋予权限选项
REFERENCES 数据库或表
ALTER 更改表,比如添加字段、索引等
DELETE 删除数据权限
INDEX 索引权限
INSERT 插入权限
SELECT 查询权限
UPDATE 更新权限
CREATE VIEW 视图 创建视图权限
SHOW VIEW 视图 查看视图权限
ALTER ROUTINE 存储过程 更改存储过程权限
CREATE ROUTINE 存储过程 创建存储过程权限
EXECUTE 存储过程 执行存储过程权限
FILE 服务器主机上的文件访问 文件访问权限
CREATE TEMPORARY TABLES 服务器管理 创建临时表权限
LOCK TABLES 服务器管理 锁表权限
CREATE USER 服务器管理 创建用户权限
PROCESS 服务器管理 查看进程权限
RELOAD  

 

服务器管理

执行flush-hosts, flush-logs, flush-privileges, flush-status, flush-tables, flush-threads, refresh, reload等命令的权限
REPLICATION CLIENT 服务器管理 复制权限
REPLICATION SLAVE 服务器管理 复制权限
SHOW DATABASES 服务器管理 查看数据库权限
SHUTDOWN 服务器管理 关闭数据库权限
SUPER 服务器管理 执行kill线程权限

2)撤销权限

revoke 权限 on 数据库.表 from ‘用户名’@’登录主机’;   //将to改为from

例:revoke all on *.* from ‘jifei’@’%’;

mysql> revoke insert on test_db.test_table from 'test_user'@'localhost';
  Query OK, 0 rows affected (0.00 sec)

3)查看权限

show grants; //自己

mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

show grants for dba@localhost; //指定用户指定host

mysql> show grants for 'test_user'@'localhost';
+-------------------------------------------------------------------------------------+
| Grants for test_user@localhost                                                      |
+-------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test_user'@'localhost'                                       |
| GRANT SELECT ON `test_db`.`test_table` TO 'test_user'@'localhost' WITH GRANT OPTION |
+-------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

e) 删除用户

(方法一)drop user 用户名;

语法:drop user 用户名;

作用:删除已经存在的用户,例如要删除yan这个用户,(drop user yan;)默认删除的是yan@”%”这个用户,如果还有其他用户,例如yan@”localhost”,yan@”ip”,则不会一起被删除。如果只存在一个用户yan@”localhost”,使用语句(drop user yan;)会报错,应该用(drop user yan@”localhost”;)如果不能确定(用户名@机器名)中的机器名,可以在mysql中的user表中进行查找,user列对应的是用户名,host列对应的是机器名。

mysql> drop user test_user;
  ERROR 1396 (HY000): Operation DROP USER failed for 'test_user'@'%'
mysql> drop user test_user@'localhost';
  Query OK, 0 rows affected (0.00 sec)

(方法二)delete from user where user=”用户名” and host=”localhost”;(不鼓励用这个方法)

delete也是删除用户的方法,例如要删除yan@”localhost”用户,则可以(delete from user where user=”yan” and host=”localhost”;)

注:drop删除掉的用户不仅将user表中的数据删除,还会删除诸如db和其他权限表的内容。而(方法二)只是删除了user表的内容,其他表不会被删除,后期如果命名一个和已删除用户相同的名字,权限就会被继承。

f) 修改密码

1) mysqladmin命令

格式如下(其中,USER为用户名,PASSWORD为新密码):

mysqladmin -u USER -p password PASSWORD  (建议不要把密码写到命令中)

该命令之后会提示输入原密码,输入正确后即可修改。

mysqladmin -utest_user -p password 
  Enter password: 
  New password: 
  Confirm new password: 
  Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

2)UPDATE user 语句

这种方式必须是先用root帐户登入mysql,然后执行:

UPDATE user SET password=PASSWORD(‘新密码’) WHERE user=’账号名’;   (注意新版的password字段改名为authentication_string)
FLUSH PRIVILEGES;

mysql> update user set authentication_string=PASSWORD('123456') where user='test_user';
  ERROR 1046 (3D000): No database selected

mysql> use mysql   //首先要选择数据库,否则会报错
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> update user set authentication_string=PASSWORD('123456') where user='test_user';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

3) SET PASSWORD语句

这种方式也需要先用root命令登入mysql,然后执行:

SET PASSWORD FOR 用户名=PASSWORD(‘新密码’);

mysql> set password for test_user=password('654321');
  ERROR 1133 (42000): Can't find any matching row in the user table

mysql> set password for test_user@'localhost'=password('654321');
  Query OK, 0 rows affected, 1 warning (0.00 sec)

上述产生的原因是:我并没有用户test_user@‘%’,所以这个时候改不了test_user的密码。因此要用下边的语句修改密码。

4)忘记root密码

i) 首先确认服务器出于安全的状态,也就是没有人能够任意地连接MySQL数据库。

因为在重新设置MySQL的root密码的期间,MySQL数据库完全出于没有密码保护的状态下,其他的用户也可以任意地登录和修改MySQL的信息。可以采用将MySQL对外的端口封闭,并且停止Apache以及所有的用户进程的方法实现服务器的准安全状态。最安全的状态是到服务器的Console上面操作,并且拔掉网线。

ii) 修改MySQL的登录设置:

# vi /etc/my.cnf

在[mysqld]的段中加上一句:skip-grant-tables

例如:

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

skip-grant-tables

保存并且退出vi。

iii) 重新启动mysqld

# /etc/init.d/mysqld restart

Stopping MySQL: [ OK ]

Starting MySQL: [ OK ]

iv) 登录并修改MySQL的root密码
# /usr/bin/mysql 
  Welcome to the MySQL monitor. Commands end with ; or \g. 
  Your MySQL connection id is 3 to server version: 3.23.56 
  Type 'help;' or '\h' for help. Type '\c' to clear the buffer. 
  mysql> USE mysql ; 
    Reading table information for completion of table and column names 
    You can turn off this feature to get a quicker startup with -A 

    Database changed 

  mysql> UPDATE user SET Password = password ( 'new-password' ) WHERE User = 'root' ; 
    Query OK, 0 rows affected (0.00 sec) 
    Rows matched: 2 Changed: 0 Warnings: 0 
  mysql> flush privileges ; 
    Query OK, 0 rows affected (0.01 sec) 
  mysql> quit 
    Bye
v) 将MySQL的登录设置修改回来

# vi /etc/my.cnf

将刚才在[mysqld]的段中加上的skip-grant-tables删除

保存并且退出vi。

vi) 重新启动mysqld

# /etc/init.d/mysqld restart

Stopping MySQL: [ OK ]

Starting MySQL: [ OK ]

g) 其他一些权限操作

1)刷新权限

flush privileges;

2)对账户重命名

rename user ‘oldusername’@’oldhost’ to ‘newusername’@’newhost’

mysql> rename user 'test_user'@'localhost' to 'test_user1'@'localhost';
Query OK, 0 rows affected (0.00 sec)

3) 查看当前用户

select user();

mysql> select user();
+----------------------+
| user()               |
+----------------------+
| test_user1@localhost |
+----------------------+
1 row in set (0.00 sec)

2. 数据库管理

a) 创建数据库

方法原型:

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name    [create_specification]

create_specification:    [DEFAULT] CHARACTER SET [=] charset_name  | [DEFAULT] COLLATE [=] collation_name

注意:在mysql中DATABASE和SCHEMA是一个东西,但是在其他的关系型数据库中就不一定。

例:CREATE DATABASE IF NOT EXISTS yourdbname DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

mysql> create database if not exists test_db1 charset utf8;
Query OK, 1 row affected (0.00 sec)


mysql> create database if not exists test_db1 character set utf8;
Query OK, 1 row affected (0.00 sec)

注意:CHARACTER跟charset 是一个东西

b) 删除数据库(慎用)

方法一:

方法原型:

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

例: drop database xhkdb;

mysql> drop database if exists test_db1;
Query OK, 0 rows affected (0.00 sec)

方法二:

使用普通用户登陆mysql服务器,你可能需要特定的权限来创建或者删除 MySQL 数据库。

所以我们这边使用root用户登录,root用户拥有最高权限,可以使用 mysql mysqladmin 命令来创建数据库。

在删除数据库过程中,务必要十分谨慎,因为在执行删除命令后,所有数据将会消失。

以下实例删除数据库TUTORIALS(该数据库在前一章节已创建):

[root@host]# mysqladmin -u root -p drop TUTORIALS
Enter password:******
执行以上删除数据库命令后,会出现一个提示框,来确认是否真的删除数据库:

1

2

3

4

5

Dropping the database is potentially a very bad thing to do.

Any data stored in the database will be destroyed.

 

Do you really want to drop the ‘TUTORIALS’ database [y/N] y

Database “TUTORIALS” dropped

c) 得到数据库

show databases;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test_db            |
+--------------------+
2 rows in set (0.00 sec)

d) 选择数据库

use databasename;

mysql> use test_db
  Reading table information for completion of table and column names
  You can turn off this feature to get a quicker startup with -A

  Database changed

3. 数据表管理

a) 创建表

 

b) 删除表

drop table命令用于删除数据表。

drop table命令格式:

DROP [TEMPORARY] TABLE [IF EXISTS]
    tbl_name [, tbl_name] ...
    [RESTRICT | CASCADE]

例如,删除表名为 MyClass 的表:
mysql> drop table MyClass;

mysql> drop table if exists test_table;
Query OK, 0 rows affected (0.14 sec)

DROP TABLE用于删除一个或多个表。您必须有每个表的DROP权限。所有的表数据和表定义会被取消,所以使用本语句要小心!

注意:对于一个带分区的表,DROP TABLE会永久性地取消表定义,取消各分区,并取消储存在这些分区中的所有数据。DROP TABLE还会取消与被取消的表有关联的分区定义(.par)文件。

对于不存在的表,使用IF EXISTS用于防止错误发生。当使用IF EXISTS时,对于每个不存在的表,会生成一个NOTE(警告)。

RESTRICT和CASCADE可以使分区更容易。目前,RESTRICT和CASCADE不起作用。

c) 清空表

方法一:truncate

TRUNCATE [TABLE] tbl_name

方法二:delete

delete from 表名;

注:

不带where参数的delete语句可以删除mysql表中所有内容,使用truncate table也可以清空mysql表中所有内容。效率上truncatedelete快,但truncate删除后不记录mysql日志,不可以恢复数据。

delete的效果有点像将mysql表中所有记录一条一条删除到删完,而truncate相当于保留mysql表的结构,重新创建了这个表,所有的状态都相当于新表。

d) 修改表结构

 

f) 得到表结构

mysql> desc MyClass;
mysql> show columns from MyClass;

mysql> desc test_table;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> show columns from test_table;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

4. 数据操作(增删改查)

a) 插入数据

 

b) 删除数据

 

c) 更新数据

 

d) 查询数据

 

5. 数据库备份

 

发表评论