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
or
not
模糊匹配
like
%
_
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;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(100) | 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;
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| id | int(10) unsigned | NO | PRI | NULL | |
| name | varchar(20) | NO | | 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;
+--------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| sid | int(10) unsigned | NO | PRI | NULL | |
| name | varchar(20) | NO | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| gender | enum('m','f') | YES | | NULL | |
+--------+---------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
修改惟一键
mysql> ALTER TABLE students ADD UNIQUE KEY(name);
mysql> DESC students;
+--------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| sid | int(10) unsigned | NO | PRI | NULL | |
| name | varchar(20) | NO | UNI | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| gender | enum('m','f') | 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'