mysql数据库常用命令-CSDN博客

mysql数据库常用命令

USE 数据库名 :

选择要操作的MySQL数据库，使用该命令后所有MySQL命令都只针对该数据库

SHOW DATABASES:

列出 MySQL 数据库管理系统的数据库列表

SHOW TABLES:

显示指定数据库的所有表，使用该命令前需要使用 use 命令来选择要操作的数据库

SHOW COLUMNS FROM 数据表:

显示数据表的属性，属性类型，主键信息，是否为 NULL，默认值等其他信息。

SHOW INDEX FROM 数据表:

显示数据表的详细索引信息，包括PRIMARY KEY（主键）

SHOW TABLE STATUS LIKE 数据表\G:

该命令将输出MySQL数据库管理系统的性能及统计信息。

create database mydb;

创建数据库

drop database mydb;

删除数据库

show character set;

查看支持的所有字符集

alter database testdb character set utf32;

修改数据库的默认字符集

alter database testdb collate utf32_sinhala_ci;

修改数据库的默认字符排序规则

表操作

查看所有数据库中的表

show tables;

show tables from mysql;

查看表结构

desc students;

创建表

create table students(id int unsigned not null primary key, name varchar(30) not null, age tinyint unsigned not null,gender enum('F','M'))

向表中添加字段

alter table students add second_name char(30);

向表中删除字段

alter table students drop second_name;

对表添加主键

alter table students2 add primary key (id);

对表删除主键

alter table students2 drop primary key;

对表添加索引

alter table students2 add index name (name);

对表删除索引

alter table students2 drop index name;

drop index name on students2;

对表添加唯一键

alter table students drop second_name;

对表删除唯一键

alter table students drop index name;

修改字段字义属性

alter table students modify name char(20);

alter table students2 change name new_name char(20) after id;

alter table students2 change name new_name char(20);

modify : 只可以修改字段定义的属性

change : 可以修改字段名称和定义的字段属性

查看表状态

show table status like 'students2'\G

修改表引擎

alter table students2 engine[=]myisam;

查看表的字段描述

desc students;

查看库中的所有表

show tables

show tables from mysql;

删除表

drop table students2;

向表中插入数据

insert weizi value(1,'zhen',30,'F');

insert weizi values(3,'wei',30,'F'),(4,'ping',31,'F');

insert weizi (id,name) value (5,"weizi");

删除表中的数据

delete from weizi where name="zhen";

delete from weizi where age is null;

delete from weizi where age > 30;

delete from weizi where id >=50 and age <=20;

delete from weizi order by age asc limit 5;

修改表中的字段值

update weizi set age=35 where id=3 and age=30;

update weizi set age=age-5 where age=35;

update weizi age=age-age;

update weizi set age=age-5 where order by id desc limit 10;

update weizi set age=age-15 where name not like 'stu%';

Select操作语句

使用格式

select col1,col2,....from tb1_name [where clause] [order y 'col_name'] [limit [m,]n]

字段表示法

* : 表示所有字段

as : 字段别名, col1 as alias1

where clause

操作符:

between....and...

条件逻辑操作

and

not

模糊匹配

rlike 'pattern' : 基于正则表达式匹配

is null

is no null

排序

desc : 降序

asc : 升序

Select示例

select name,age from students where age >30 and age<80;

select name,age from students where age between 30 and 80;

select name from students where name like '%ang%';

select name from students where name rlike '^.*ang.*$;

select name,age from students where age is null;

select name,age form students where age is not null;

select id,name from students order by name;

select id,name from students order by name desc;

权限及授权管理

用户表示方法:

username@host

管理权限的分类

管理权限

数据库

表

字段

存储例程

授权:

授权语法格式

grant pri_type,...on [object_type] db_name.tbl_name to 'user'@'host' [identified by 'PASSWD']

pri_type

all privileges : 表示全部权限

db_name.tbl_name的表示方法:

*.* : 所有库的所有表

db_name.* : 指定库的所有表

db_name.tbl_name : 指定库的特定表

db_name.routine_name : 指定库上的存储过程或存储函数

object_type

table

function

procedure

授权示例:

grant all privileges on mydb.* to 'zhenping'@'172.16.%.%' identified by 'MT8ddd';

取消权限

取消授权语法格式

revoke pri_type,.... on db_name.tb_name from 'user'@'host';

取消授权示例:

revoke all privileges on mydb.* from 'zhenping'@'172.16.%.%';

查看用户权限

show grants for 'user'@'host';

让新授权的权限立即生效

flush privileges;

1 Mariadb服务进程启动时会读取mysql库中的所有授权表至内存中;

2 grant和revoke等执行权限操作时会保存于表中,mariadb的服务进程会自动重读授权表

3 对于不能够或不能及时重读授权表,可手动让服务进程重启授权表,使用flush privileges

番外往篇

在CentOS 6 上手动编译安装mysql

首先关闭之前启动的mysql服务，并且将其卸载

[root@localhost php.d]# service mysqld stop

[root@localhost ~]# rpm -e mysql-server

warning: /var/log/mysqld.log saved as /var/log/mysqld.log.rpmsave

为了彻底删除，我们也要将它产生的日志文件也给删了

[root@localhost ~]# rm -f /var/log/mysqld.log.rpmsave

下载mariadb的源码包（ mariadb-5.5.43-linux-x86_64.tar.gz），然后创建一个系统用户

[root@localhost ~]# groupadd -r mysql

[root@localhost ~]# id mysql

uid=27(mysql) gid=27(mysql) groups=27(mysql)

将源码包解压缩至指定的目录

[root@localhost ~]# tar xf mariadb-5.5.43-linux-x86_64.tar.gz -C /usr/local

为解压缩出来的目录做一个链接，因为我们用到相关文件时，引用的名称为mysql

[root@localhost ~]# cd /usr/local

[root@localhost local]# ln -sv mariadb-5.5.43-linux-x86_64 mysql

将mysql目录下的文件修改其权限

[root@localhost mysql]# chown -R root:mysql ./*

接下来我们就要创建mysql的专用数据存储目录了。这个很明显，存放数据的目录当然要单独分区，在此处，我们将它放在LVM2上

我们重新挂载上一个硬盘/dev/sdb，将其分成两个区，并调整为8e，即LVM的格式。

[root@localhost ~]# fdisk /dev/sdb

创建完成后，重读一下分区表

[root@localhost ~]# partx -a /dev/sdb

BLKPG: Device or resource busy

error adding partition 1

BLKPG: Device or resource busy

error adding partition 2

如此便创建成功了，接着我们将这两个分区只作为LVM2

[root@localhost ~]# pvcreate /dev/sdb1

Physical volume "/dev/sdb1" successfully created

[root@localhost ~]# pvcreate /dev/sdb2

Physical volume "/dev/sdb2" successfully created

创建vg

[root@localhost ~]# vgcreate myvg /dev/sdb1 /dev/sdb2

Volume group "myvg" successfully created

创建lv

[root@localhost ~]# lvcreate -L10G -n mydata myvg

Logical volume "mydata" created

我们想要在这个lvm上安装xfs文件系统，所以先安装这个文件系统所需要的相关文件

[root@localhost ~]# yum install xfsprogs -y

xfs文件系统比ext4有更好的扩展性

[root@localhost ~]# modprobe xfs

[root@localhost ~]# modinfo xfs

filename: /lib/modules/2.6.32-504.el6.x86_64/kernel/fs/xfs/xfs.ko

license: GPL

description: SGI XFS with ACLs, security attributes, large block/inode numbers, no debug enabled

author: Silicon Graphics, Inc.

srcversion: 4392D4D583B9D2781E4F61E

depends: exportfs

vermagic: 2.6.32-504.el6.x86_64 SMP mod_unload modversions

将lvm格式化为xfs

[root@localhost ~]# mkfs.xfs /dev/myvg/mydata

meta-data=/dev/myvg/mydata isize=256 agcount=4, agsize=655360 blks

= sectsz=512 attr=2, projid32bit=0

data = bsize=4096 blocks=2621440, imaxpct=25

= sunit=0 swidth=0 blks

naming =version 2 bsize=4096 ascii-ci=0

log =internal log bsize=4096 blocks=2560, version=2

= sectsz=512 sunit=0 blks, lazy-count=1

realtime =none extsz=4096 blocks=0, rtextents=0

创建文件系统挂载目录

[root@localhost ~]# mkdir /mydata

将挂载信息添加到配置文件中，使开机时自动挂载

[root@localhost ~]# vim /etc/fstab

/dev/myvg/mydata /mydata xfs defaults 0 0

重读一下配置文件，使其挂载上

[root@localhost ~]# mount -a

查看挂载信息

[root@localhost ~]# mount

/dev/mapper/myvg-mydata on /mydata type xfs (rw)

[root@localhost mydata]# mkdir data

修改其属主属组

[root@localhost mydata]# chown mysql:mysql data

[root@localhost mydata]# ll

total 0

drwxr-xr-x. 2 mysql mysql 6 Oct 11 03:37 data

回到mysql的目录

[root@localhost local]# cd /usr/local/mysql/

[root@localhost mysql]# ls scripts/

mysql_install_db

注意这个目录下的此脚本，它是专门用于生成mysql初始化的

查看这个初始化脚本的相关配置信息

[root@localhost mysql]# scripts/mysql_install_db --help

[root@localhost mysql]# scripts/mysql_install_db --user=mysql --datadir=/mydata/data/

此时查看初始化生成的相关目录，其实就是库文件

[root@localhost mysql]# ls /mydata/data

aria_log.00000001 aria_log_control mysql performance_schema test

接着准备mysql的服务脚本

[root@localhost mysql]# cp support-files/mysql.server /etc/rc.d/init.d/mysqld

添加上这个脚本

[root@localhost mysql]# chkconfig --add mysqld

[root@localhost mysql]# chkconfig --list mysqld

mysqld 0:off 1:off 2:on 3:on 4:on 5:on 6:off

接下来就要准备配置文件了，复制一个模板，稍作修改便可成为配置文件

[root@localhost mysql]# mkdir /etc/mysql

[root@localhost mysql]# cp support-files/my-large.cnf /etc/mysql/my.cnf

对这个配置文件，做一下针对性的修改

[root@localhost mysql]# vim /etc/mysql/my.cnf

其中的配置文件分为n段

[client] mysqld客户端要读取的配置

[mysqld] mysqld服务器端要读取的配置

我们仅需要在[mysqld]中，稍作修改即可

thread_concurrency = 8

datadir=/mydata/data

innodb_file_per_table=on

skip_name_resolve=on（跳过名称解析）

说明：mysql每次通过客户端进行连接时，它都会把ip地址反解成主机名，并在本地做权限检查，这相当麻烦。所以干脆跳过，以后主机来访时，我们仅根据其客户端来源ip做检查，授权时也根据ip做授权。

启动mysql服务

[root@localhost mysql]# service mysqld start

[root@localhost mysql]# ss -tnl

State Recv-Q Send-Q Local Address:Port Peer Address:Port

LISTEN 0 128 :::22 :::*

LISTEN 0 128 *:22 *:*

LISTEN 0 128 127.0.0.1:631 *:*

LISTEN 0 128 ::1:631 :::*

LISTEN 0 100 ::1:25 :::*

LISTEN 0 100 127.0.0.1:25 *:*

LISTEN 0 128 127.0.0.1:6010 *:*

LISTEN 0 128 ::1:6010 :::*

LISTEN 0 50 *:3306 *:*

LISTEN 0 128 :::80 :::*

在/usr/local/mysql/bin中有一些二进制程序

[root@localhost mysql]# ls bin

aria_chk myisam_ftdump mysqlbug mysqld_safe mysql_plugin mysql_upgrade

aria_dump_log myisamlog mysqlcheck mysqldump mysql_secure_installation mysql_waitpid

aria_ftdump myisampack mysql_client_test mysqldumpslow mysql_setpermission mysql_zap

aria_pack my_print_defaults mysql_client_test_embedded mysql_embedded mysqlshow mytop

aria_read_log mysql mysql_config mysql_find_rows mysqlslap perror

innochecksum mysqlaccess mysql_convert_table_format mysql_fix_extensions mysqltest replace

msql2mysql mysqladmin mysqld mysqlhotcopy mysqltest_embedded resolveip

myisamchk mysqlbinlog mysqld_multi mysqlimport mysql_tzinfo_to_sql resolve_stack_dump

查看相关的进程

[root@localhost mysql]# ps aux | grep mysql

root 2734 0.0 0.1 11472 1376 pts/0 S 04:02 0:00

/bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/mydata/data --pid-file=/mydata/data/localhost.localdomain.pid

mysql 3135 0.3 13.2 842784 132716 pts/0 Sl 04:02 0:00

/usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/mydata/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/var/log/mysqld.log --pid-file=/mydata/data/localhost.localdomain.pid --socket=/tmp/mysql.sock --port=3306

root 3176 0.0 0.0 103252 836 pts/0 S+ 04:07 0:00 grep mysql

注意：其中含有mysqld-safe程序

讲述下/usr/local/bin/mysql,这个命令行使用的相关方法

[root@localhost mysql]# mysql

mysql> use mysql;

mysql> SELECT User,Host,Password FROM user;

+------+-----------------------+----------+

| User | Host | Password |

+------+-----------------------+----------+

| root | localhost | |

| root | localhost.localdomain | |

| root | 127.0.0.1 | |

| root | ::1 | |

| | localhost | |

| | localhost.localdomain | |

+------+-----------------------+----------+

6 rows in set (0.00 sec)

设置安全初始化程序，禁止管理员禁止登陆，清空匿名用户，设置管理员的密码

[root@localhost mysql]# /usr/local/mysql/bin/mysql_secure_installation

Enter current password for root (enter for none):

OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB

root user without the proper authorisation.

Set root password? [Y/n] Y

New password:

Re-enter new password:

Password updated successfully!

Reloading privilege tables..

... Success!

By default, a MariaDB installation has an anonymous user, allowing anyone

to log into MariaDB without having to have a user account created for

them. This is intended only for testing, and to make the installation

go a bit smoother. You should remove them before moving into a

production environment.

Remove anonymous users? [Y/n] Y

... Success!

Normally, root should only be allowed to connect from 'localhost'. This

ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] Y

... Success!

By default, MariaDB comes with a database named 'test' that anyone can

access. This is also intended only for testing, and should be removed

before moving into a production environment.

Remove test database and access to it? [Y/n] Y

- Dropping test database...

... Success!

- Removing privileges on test database...

... Success!

Reloading the privilege tables will ensure that all changes made so far

will take effect immediately.

Reload privilege tables now? [Y/n] Y

... Success!

Cleaning up...

All done! If you've completed all of the above steps, your MariaDB

installation should now be secure.

Thanks for using MariaDB!

此时你用root登录，会发现已经不能直接登录了

[root@localhost mysql]# mysql

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

可用这种方式登录

[root@localhost mysql]# mysql -uroot -p

mysql> use mysql;

mysql> SELECT User,Host,Password FROM user;

+------+-----------+-------------------------------------------+

| User | Host | Password |

+------+-----------+-------------------------------------------+

| root | localhost | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |

| root | 127.0.0.1 | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |

| root | ::1 | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |

+------+-----------+-------------------------------------------+

3 rows in set (0.00 sec)

mysql> status

--------------

mysql Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1

Connection id: 18

Current database: mysql

Current user: root@localhost

SSL: Not in use

Current pager: stdout

Using outfile: ''

Using delimiter: ;

Server version: 5.5.43-MariaDB-log MariaDB Server

Protocol version: 10

Connection: Localhost via UNIX socket

Server characterset: latin1

Db characterset: latin1

Client characterset: latin1

Conn. characterset: latin1

UNIX socket: /tmp/mysql.sock

Uptime: 20 min 35 sec

Threads: 1 Questions: 38 Slow queries: 0 Opens: 1 Flush tables: 2 Open tables: 27 Queries per second avg: 0.030

--------------

查看命令的相关帮助文档

mysql> HELP CREATE DATABASE;

mysql> SHOW ENGINES;

mysql> CREATE DATABASE IF NOT EXISTS testdb;

mysql> use testdb;

mysql> CREATE TABLE tb1(id int NOT NULL,name VARCHAR(100) NOT NULL,age tinyint);

mysql> SHOW TABLES;

+------------------+

| Tables_in_testdb |

+------------------+

| tb1 |

+------------------+

1 row in set (0.00 sec)

mysql> SHOW DATABASES;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| testdb |

+--------------------+

4 rows in set (0.00 sec)

mysql> DESC tb1;

+-------+--------------+------+-----+---------+-------+

+-------+--------------+------+-----+---------+-------+

| id | int(11) | NO | | NULL | |

| age | tinyint(4) | YES | | NULL | |

+-------+--------------+------+-----+---------+-------+

3 rows in set (0.02 sec)

9.mysql的基础命令

登录进mysql

[root@localhost mysql]# mysql -uroot -predhat

mysql> CREATE DATABASE testdb;

mysql> CREATE TABLE students (id int UNSIGNED NOT NULL PRIMARY KEY,name VARCHAR(20) NOT NULL,age tinyint UNSIGNED);

mysql> DESC students;

+-------+---------------------+------+-----+---------+-------+

+-------+---------------------+------+-----+---------+-------+

| id | int(10) unsigned | NO | PRI | NULL | |

| age | tinyint(3) unsigned | YES | | NULL | |

+-------+---------------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

或者是单独定义修饰符表示的内容，即定义联合式的主键

mysql> CREATE TABLE tbl2 (id int UNSIGNED NOT NULL,name VARCHAR(20) NOT NULL,age tinyint UNSIGNED,PRIMARY KEY(id,name));

这里只要是id，name这两个键的联合内容不相同即可。

mysql> SHOW TABLE STATUS LIKE 'students'\G;

*************************** 1. row ***************************

Name: students

Engine: InnoDB

Version: 10

Row_format: Compact

Rows: 0

Avg_row_length: 0

Data_length: 16384

Max_data_length: 0

Index_length: 0

Data_free: 0

Auto_increment: NULL

Create_time: 2015-10-11 04:33:41

Update_time: NULL

Check_time: NULL

Collation: latin1_swedish_ci

Checksum: NULL

Create_options:

Comment:

1 row in set (0.00 sec)

ERROR:

No query specified

\G的含义是竖排显示

在帮助手册中查看ALTER, CHANGE, MODIFY这三个命令的不同

添加字段

mysql> ALTER TABLE students ADD gender ENUM('m','f');

当修改字段名称时，对剩下的要有定义

mysql> ALTER TABLE students CHANGE id sid int UNSIGNED NOT NULL;

mysql> DESC students;

+--------+---------------------+------+-----+---------+-------+

+--------+---------------------+------+-----+---------+-------+

| sid | int(10) unsigned | NO | PRI | NULL | |

| age | tinyint(3) unsigned | YES | | NULL | |

+--------+---------------------+------+-----+---------+-------+

4 rows in set (0.01 sec)

修改惟一键

mysql> ALTER TABLE students ADD UNIQUE KEY(name);

mysql> DESC students;

+--------+---------------------+------+-----+---------+-------+

+--------+---------------------+------+-----+---------+-------+

| sid | int(10) unsigned | NO | PRI | NULL | |

| age | tinyint(3) unsigned | YES | | NULL | |

+--------+---------------------+------+-----+---------+-------+

4 rows in set (0.01 sec)

添加索引

mysql> SHOW INDEXES FROM students\G;

*************************** 1. row ***************************

Table: students

Non_unique: 0

Key_name: PRIMARY

Seq_in_index: 1

Column_name: sid

Collation: A

Cardinality: 0

Sub_part: NULL

Packed: NULL

Null:

Index_type: BTREE

Comment:

Index_comment:

*************************** 2. row ***************************

Table: students

Non_unique: 0

Key_name: name

Seq_in_index: 1

Column_name: name

Collation: A

Cardinality: 0

Sub_part: NULL

Packed: NULL

Null:

Index_type: BTREE

Comment:

Index_comment:

2 rows in set (0.02 sec)

删除索引

mysql> ALTER TABLE students DROP age;

mysql> SHOW INDEXES FROM students\G;

mysql> DROP INDEX name ON students;

DML:

mysql> INSERT INTO students VALUES (1,'YangGuo','m');

mysql> SELECT * FROM students;

+-----+---------+--------+

| sid | name | gender |

+-----+---------+--------+

| 1 | YangGuo | m |

+-----+---------+--------+

1 row in set (0.00 sec)

mysql> INSERT INTO students (sid,name) VALUES (3,'ZhangWuji'),(4,'ZhaoMin');

mysql> SELECT * FROM students;

+-----+-----------+--------+

| sid | name | gender |

+-----+-----------+--------+

| 1 | YangGuo | m |

| 3 | ZhangWuji | NULL |

| 4 | ZhaoMin | NULL |

+-----+-----------+--------+

3 rows in set (0.00 sec)

查看创建命令的帮助信息

mysql> HELP DELETE;

Name: 'DELETE'

Description:

Syntax:

Single-table syntax:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name

[WHERE where_condition]

[ORDER BY ...]

[LIMIT row_count]

Multiple-table syntax:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]

tbl_name[.*] [, tbl_name[.*]] ...

FROM table_references

[WHERE where_condition]

Or:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]

FROM tbl_name[.*] [, tbl_name[.*]] ...

USING table_references

[WHERE where_condition]

mysql> HELP SELECT;

Name: 'SELECT'

Description:

Syntax:

SELECT

[ALL | DISTINCT | DISTINCTROW ]

[HIGH_PRIORITY]

[STRAIGHT_JOIN]

[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]

[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]

select_expr [, select_expr ...]

[FROM table_references

[WHERE where_condition]

[GROUP BY {col_name | expr | position}

[ASC | DESC], ... [WITH ROLLUP]]

[HAVING where_condition]

[ORDER BY {col_name | expr | position}

[ASC | DESC], ...]

[LIMIT {[offset,] row_count | row_count OFFSET offset}]

[PROCEDURE procedure_name(argument_list)]

[INTO OUTFILE 'file_name'

[CHARACTER SET charset_name]

export_options

| INTO DUMPFILE 'file_name'

| INTO var_name [, var_name]]

[FOR UPDATE | LOCK IN SHARE MODE]]

mysql> SELECT * FROM students WHERE gender='m';

+-----+---------+--------+

| sid | name | gender |

+-----+---------+--------+

| 1 | YangGuo | m |

+-----+---------+--------+

1 row in set (0.00 sec)

mysql> SELECT * FROM students WHERE gender IS NULL;

+-----+-----------+--------+

| sid | name | gender |

+-----+-----------+--------+

| 3 | ZhangWuji | NULL |

| 4 | ZhaoMin | NULL |

+-----+-----------+--------+

2 rows in set (0.00 sec)

mysql> SELECT * FROM students ORDER BY name DESC LIMIT 1,2;

+-----+-----------+--------+

| sid | name | gender |

+-----+-----------+--------+

| 3 | ZhangWuji | NULL |

| 1 | YangGuo | m |

+-----+-----------+--------+

2 rows in set (0.01 sec)

mysql> SELECT * FROM students WHERE sid>=2 AND sid<=4;

+-----+-----------+--------+

| sid | name | gender |

+-----+-----------+--------+

| 3 | ZhangWuji | NULL |

| 4 | ZhaoMin | NULL |

+-----+-----------+--------+

2 rows in set (0.00 sec)

mysql> SELECT * FROM students WHERE sid BETWEEN 2 AND 4;

+-----+-----------+--------+

| sid | name | gender |

+-----+-----------+--------+

| 3 | ZhangWuji | NULL |

| 4 | ZhaoMin | NULL |

+-----+-----------+--------+

2 rows in set (0.00 sec)

mysql> SELECT * FROM students WHERE name LIKE 'z%';

+-----+-----------+--------+

| sid | name | gender |

+-----+-----------+--------+

| 3 | ZhangWuji | NULL |

| 4 | ZhaoMin | NULL |

+-----+-----------+--------+

2 rows in set (0.00 sec)

mysql> SELECT * FROM students WHERE name RLIKE '.*[A-G]u.*';

+-----+---------+--------+

| sid | name | gender |

+-----+---------+--------+

| 1 | YangGuo | m |

+-----+---------+--------+

1 row in set (0.00 sec)

mysql> SELECT sid as stuid,name as stuname FROM students;

+-------+-----------+

| stuid | stuname |

+-------+-----------+

| 1 | YangGuo |

| 3 | ZhangWuji |

| 4 | ZhaoMin |

+-------+-----------+

3 rows in set (0.00 sec）

mysql> HELP UPDATE

Name: 'UPDATE'

Description:

Syntax:

Single-table syntax:

UPDATE [LOW_PRIORITY] [IGNORE] table_reference

SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...

[WHERE where_condition]

[ORDER BY ...]

[LIMIT row_count]

创建用户

mysql> CREATE USER 'wpuser'@'%' IDENTIFIED BY 'wppasswd';

mysql> use mysql;

查看存储用户的信息表

mysql> SELECT User,Host,Password FROM user;

+--------+-----------+-------------------------------------------+

| User | Host | Password |

+--------+-----------+-------------------------------------------+

| root | localhost | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |

| root | 127.0.0.1 | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |

| root | ::1 | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |

| wpuser | % | *7CD61EFBFDDDBD978EA9017F2A26A59DE4589025 |

+--------+-----------+-------------------------------------------+

4 rows in set (0.01 sec)

这样任意主机，就可以通过wpuser进入mysql

我们通过192.168.1.110连接本机的mysql

[root@localhost ~]# mysql -uwpuser -h192.168.1.109 -p

注意：如果连接不上，注意查看一下防火墙

MariaDB [(none)]> SHOW DATABASES;

+--------------------+

| Database |

+--------------------+

| information_schema |

+--------------------+

1 row in set (0.04 sec)

MariaDB [(none)]> HELP GRANT;

Name: 'GRANT'

Description:

Syntax:

GRANT

priv_type [(column_list)]

[, priv_type [(column_list)]] ...

ON [object_type] priv_level

TO user_specification [, user_specification] ...

[REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}]

[WITH with_option ...]

GRANT PROXY ON user_specification

TO user_specification [, user_specification] ...

[WITH GRANT OPTION]

object_type:

TABLE

| FUNCTION

| PROCEDURE

priv_level:

| *.*

| db_name.*

| db_name.tbl_name

| tbl_name

| db_name.routine_name

user_specification:

user

[

IDENTIFIED BY [PASSWORD] 'password'

| IDENTIFIED WITH auth_plugin [AS 'auth_string']

]

ssl_option:

SSL

| X509

| CIPHER 'cipher'

| ISSUER 'issuer'

| SUBJECT 'subject'

with_option:

GRANT OPTION

| MAX_QUERIES_PER_HOUR count

| MAX_UPDATES_PER_HOUR count

| MAX_CONNECTIONS_PER_HOUR count

| MAX_USER_CONNECTIONS count

MariaDB [(none)]> HELP SHOW GRANTS;

Name: 'SHOW GRANTS'

Description:

Syntax:

SHOW GRANTS [FOR user]

MariaDB [(none)]> SHOW GRANTS FOR 'wpuser'@'%';

+-------------------------------------------------------------------------------------------------------+

| Grants for wpuser@% |

+-------------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'wpuser'@'%' IDENTIFIED BY PASSWORD '*7CD61EFBFDDDBD978EA9017F2A26A59DE4589025' |

+-------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

mysql> SHOW GRANTS FOR 'root'@'localhost';

mysql> SHOW GRANTS FOR CURRENT_USER;

mysql> GRANT SELECT,DELETE ON testdb.* TO 'testdb'@'%' IDENTIFIED BY 'testpass';

以192.168.1.110访问数据库

[root@localhost ~]# mysql -utestdb -h192.168.1.109 -p

MariaDB [(none)]> SHOW DATABASES;

+--------------------+

_ | Database |

+--------------------+

| information_schema |

| testdb |

+--------------------+

2 rows in set (0.00 sec)

MariaDB [(none)]> use testdb;

MariaDB [testdb]> SHOW GRANTS FOR CURRENT_USER;

+-------------------------------------------------------------------------------------------------------+

| Grants for testdb@% |

+-------------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'testdb'@'%' IDENTIFIED BY PASSWORD '*00E247AC5F9AF26AE0194B41E1E769DEE1429A29' |

| GRANT SELECT, DELETE ON `testdb`.* TO 'testdb'@'%' |

+-------------------------------------------------------------------------------------------------------+

2 rows in set (0.00 sec)

MariaDB [testdb]> CREATE TABLE tbl2(id int);

ERROR 1142 (42000): CREATE command denied to user 'testdb'@'192.168.1.110' for table 'tbl2'

此时会发现，因为我们之前设定的原因，当前用户没有权限创建表

MariaDB [testdb]> DELETE FROM students WHERE sid=4;

Query OK, 1 row affected (0.01 sec)

回收权限

MariaDB [testdb]> REVOKE DELETE ON testdb.* FROM 'testdb'@'%';

ERROR 1044 (42000): Access denied for user 'testdb'@'%' to database 'testdb'

转载于:https://blog.51cto.com/10971946/2091680