进入数据库：命令行直接写mysql，回车即可
进入指定数据库：mysql -uwordpress -p wordpress
退出数据库：Ctrl+d
一、创建操作
1.1 创建数据库
create database test;
MariaDB [(none)]> create database test;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| lianxi |
| mysql |
| performance_schema |
| test |
| wordpress |
+--------------------+
6 rows in set (0.00 sec)
MariaDB [(none)]>
1.2 创建用户
grant all on wordpress.* to 'wordpress'@'172.16.1.%' identified by '123456';
grant all on wordpress.* to 'wordpress'@'localhost' identified by '123456';
1.3 创建表和字段
create table tl_user(id int primary key auto_increment,name nvarchar(20),age int,sex varchar(4),address nvarchar(50),updatetime date);
MariaDB [test]> create table tl_user(id int primary key auto_increment,name nvarchar(20),age int,sex varchar(4),address nvarchar(50),updatetime date);
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> show tables from test;
+----------------+
| Tables_in_test |
+----------------+
| tl_user |
+----------------+
1 row in set (0.00 sec)
MariaDB [test]>
1.4 向表中插入数据
insert into 数据库名.表名(表字段,……) values(对应字段的值,……)
如：insert into test.tl_user(name,age,sex,address,updatetime) values('张三',23,'男','北京朝阳区某某号','2019-6-11');
MariaDB [test]> insert into tl_user(name,age,sex,address,updatetime) values('张三',23,'男','北京朝阳区某某号','2019-6-11');
Query OK, 1 row affected, 1 warning (0.00 sec)
MariaDB [test]> select * from tl_user\G
*************************** 1. row ***************************
id: 1
name: 张三
age: 23
sex: ?
address: 北京朝阳区某某号
updatetime: 2019-06-11
1 row in set (0.00 sec)
MariaDB [test]>
二、删除操作
2.1 删除数据库
drop database 数据库名;
如：drop database test;
MariaDB [(none)]> drop database test;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| lianxi |
| mysql |
| performance_schema |
| wordpress |
+--------------------+
5 rows in set (0.00 sec)
MariaDB [(none)]>
2.2 删除用户
drop user 'oldboy'@'localhost'
用户删除之后：flush privileges
2.3 删除表
drop table 数据库名.表名;
2.4 删除表中的某条数据
delete from 数据库名. 表名 where 要删除的条件
如：delete from test.tl_user where id=2;
MariaDB [test]> select * from tl_user;
+----+--------+------+------+--------------------------+------------+
| id | name | age | sex | address | updatetime |
+----+--------+------+------+--------------------------+------------+
| 1 | 张三 | 23 | ? | 北京朝阳区某某号 | 2019-06-11 |
| 2 | dddd | 23 | nv | dfdfdfdfdfd | 2019-06-11 |
| 3 | 李四 | 23 | nv | 的辅导辅导辅导费 | 2019-06-11 |
+----+--------+------+------+--------------------------+------------+
3 rows in set (0.00 sec)
MariaDB [test]> delete from tl_user where id=2;
Query OK, 1 row affected (0.00 sec)
MariaDB [test]> select * from tl_user;
+----+--------+------+------+--------------------------+------------+
| id | name | age | sex | address | updatetime |
+----+--------+------+------+--------------------------+------------+
| 1 | 张三 | 23 | ? | 北京朝阳区某某号 | 2019-06-11 |
| 3 | 李四 | 23 | nv | 的辅导辅导辅导费 | 2019-06-11 |
+----+--------+------+------+--------------------------+------------+
2 rows in set (0.00 sec)
MariaDB [test]>
三、查询操作
3.1 查询数据库
show databases;
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
MariaDB [(none)]>
3.2 查询数据库中的用户（指定字段）
select user,host from mysql.user;
MariaDB [(none)]> select user,host from mysql.user;
+------+-----------+
| user | host |
+------+-----------+
| root | 127.0.0.1 |
| root | ::1 |
| | localhost |
| root | localhost |
| | web01 |
| root | web01 |
+------+-----------+
6 rows in set (0.00 sec)
3.3 查询数据库中的用户（所有字段）
select * from mysql.user;
为了显示好看可以纵向显示，直接在语句后面加\G，如：select * from mysql.user\G
3.4 显示当前所用的用户
select user();
3.5 使用数据库（进入指定数据库）
use 数据库名；如：use mysql;
MariaDB [(none)]> use wordpress;
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
MariaDB [wordpress]>
3.6 显示当前使用的数据库
select database();
MariaDB [wordpress]> select database();
+------------+
| database() |
+------------+
| wordpress |
+------------+
1 row in set (0.00 sec)
MariaDB [wordpress]>
3.7 只显示1条数据
select * from mysql.user limit 1 ;
select * from mysql.user limit 1\G
MariaDB [wordpress]> select * from wp_posts limit 1 \G
*************************** 1. row ***************************
ID: 1
post_author: 1
post_date: 2019-06-10 09:30:38
post_date_gmt: 2019-06-10 09:30:38
post_content:
<p>Welcome to WordPress. This is your first post. Edit or delete it, then start writing!</p>
……
MariaDB [wordpress]>
四、更新操作
4.1 根据条件更新表中的某些字段的值
update 数据库名.表名 set 要更新的字段=值 where 根据什么条件;
如：更新tl_user表中张三的地址
update test.tl_user set address='黑龙江省齐齐哈尔市某某号' where id=1;
MariaDB [test]> select * from tl_user;
+----+--------+------+------+--------------------------+------------+
| id | name | age | sex | address | updatetime |
+----+--------+------+------+--------------------------+------------+
| 1 | 张三 | 23 | ? | 北京朝阳区某某号 | 2019-06-11 |
| 3 | 李四 | 23 | nv | 的辅导辅导辅导费 | 2019-06-11 |
+----+--------+------+------+--------------------------+------------+
2 rows in set (0.00 sec)
MariaDB [test]> update tl_user set address='黑龙江省齐齐哈尔市某某号' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [test]> select * from tl_user;
+----+--------+------+------+--------------------------------------+------------+
| id | name | age | sex | address | updatetime |
+----+--------+------+------+--------------------------------------+------------+
| 1 | 张三 | 23 | ? | 黑龙江省齐齐哈尔市某某号 | 2019-06-11 |
| 3 | 李四 | 23 | nv | 的辅导辅导辅导费 | 2019-06-11 |
+----+--------+------+------+--------------------------------------+------------+
2 rows in set (0.00 sec)
MariaDB [test]>
五、备份操作
1>导出所有的数据库
mysqldump -uroot -p -all-database >/root/all.sql
mysqldump -uroot -p -A >/root/all.sql
五、恢复数据库
mysql -uroot -p </root/all.sql
