MySQL新手命令熟练

控制台下MySQL的一些基本操作

环境：虚拟机Ubuntu 16.04
首先在shell中输入

mysql -u root -p

输入密码，以root权限进入mysql，之后可以输入status;查看mysql信息

mysql  Ver 14.14 Distrib 5.5.62, for debian-linux-gnu (x86_64) using readline 6.3

Connection id:		75
Current database:	
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		5.5.62-0ubuntu0.14.04.1 (Ubuntu)
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	latin1
Db     characterset:	latin1
Client characterset:	utf8
Conn.  characterset:	utf8
UNIX socket:		/var/run/mysqld/mysqld.sock
Uptime:			3 days 4 hours 41 min 7 sec

Threads: 1  Questions: 190  Slow queries: 0  Opens: 49  Flush tables: 1  Open tables: 42  Queries per second avg: 0.000

创建数据库

输入以下命令查看当前用户下所拥有的数据库：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| websecurity        |
+--------------------+
5 rows in set (0.08 sec)

使用create进行创建新的数据库并使用：

mysql> CREATE DATABASE websecurity;
Query OK, 1 row affected (0.07 sec)
mysql> use websecurity;
Database changed

创建一个teacher的表（not null表示不为空，id name sex addr都是变量，后面的char int代表了数据类型，primary key代表了id是一个键值，auto_increment表示id会自动增加）
(在有零填充的时候，int(4)显示0001，而int(11)显示00000000001，仅仅是显示上的区别，实际范围依然是$-2^{31}\sim 2^{31}$)

mysql> create table teacher( id int(4) not null primary key auto_increment,
    -> name char(20) not null,
    -> sex char(10) not null ,
    -> addr char(20) not null);
Query OK, 0 rows affected (0.41 sec)

创建表之后展示：

mysql> show tables;
+-----------------------+
| Tables_in_websecurity |
+-----------------------+
| teacher               |
+-----------------------+
1 row in set (0.02 sec)

创建与修改表

查看当前的表，看到为空，然后再往其中添加各种值，然后再查看当前数据库中的表teacher：

mysql> SELECT * from teacher;
Empty set (0.01 sec)

mysql> INSERT INTO teacher(name,sex,addr) VALUES('Leo','Male','CKC');
Query OK, 1 row affected (0.04 sec)

mysql> INSERT INTO teacher(name,sex,addr) VALUES('Mary','Female','CSC');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO teacher(name,sex,addr) VALUES('Mhffdq','Male','Africa');
Query OK, 1 row affected (0.03 sec)
mysql> SELECT * from teacher;
+----+--------+--------+--------+
| id | name   | sex    | addr   |
+----+--------+--------+--------+
|  1 | Leo    | Male   | CKC    |
|  2 | Mary   | Female | CSC    |
|  3 | Mhffdq | Male   | Africa |
+----+--------+--------+--------+
3 rows in set (0.01 sec)

简单的更新数据库数据以及删除数据库数据操作：

mysql> UPDATE teacher SET name='ivan' where id=1;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * from teacher;
+----+--------+--------+--------+
| id | name   | sex    | addr   |
+----+--------+--------+--------+
|  1 | ivan   | Male   | CKC    |
|  2 | Mary   | Female | CSC    |
|  3 | Mhffdq | Male   | Africa |
+----+--------+--------+--------+
3 rows in set (0.00 sec)

mysql> DELETE FROM teacher WHERE name='ivan';
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * from teacher;
+----+--------+--------+--------+
| id | name   | sex    | addr   |
+----+--------+--------+--------+
|  2 | Mary   | Female | CSC    |
|  3 | Mhffdq | Male   | Africa |
+----+--------+--------+--------+
2 rows in set (0.00 sec)

同样，我们可以从文件中直接加在SQL语句，将其运行，如下所示：

mysql> source /var/www/html/sqlTemplate;
Database changed
ERROR 1050 (42S01): Table 'student' already exists
Query OK, 1 row affected (0.02 sec)

Query OK, 1 row affected (0.01 sec)

Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+----+--------+--------+-----+
| id | name   | sex    | age |
+----+--------+--------+-----+
|  1 | Ray    | Male   | 22  |
|  2 | Jennie | Female | 11  |
|  3 | Jack   | Male   | 33  |
+----+--------+--------+-----+
3 rows in set (0.00 sec)

此时创建了一个新的student表。
下面是使用了 union , select 和 order 的一个小例子：

mysql> select name , sex from teacher union all
     > select name , sex from student order by 2;
+--------+--------+
| name   | sex    |
+--------+--------+
| Mary   | Female |
| Jennie | Female |
| Jack   | Male   |
| Leo    | Male   |
| Mhffdq | Male   |
| Ray    | Male   |
+--------+--------+
6 rows in set (0.00 sec)

SQL注入常用的函数有：
select 后面加：

1. database()：返回当前的数据库名称
2. current_user()：返回当前的用户信息
3. version() ：返回Mysql版本
4. into+outfile ：写入文件
5. loadfile()：()内填入想打开的文件的路径
   当我尝试用loadfile()加载’/etc/hosts’时，结果返回了NULL。百度过后发现，全局变量 secure_file_priv 定义了loadfile()可以加载的目录，在命令行下，可以通过以下命令来查找并修改该变量，可以load这个目录下的文件：

mysql> show global variables like '%secure%';
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_auth      | OFF                   |
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
2 rows in set (0.00 sec)
mysql> select load_file('/var/lib/mysql-files/mysqlloadtest');
+-------------------------------------------------+
| load_file('/var/lib/mysql-files/mysqlloadtest') |
+-------------------------------------------------+
| You Found Me!!!!!!
                             |
+-------------------------------------------------+
1 row in set (0.03 sec)

当我们需要修改secure_file_priv时，我们需要进入/etc/mysql/my.cnf（mysql的），在[mysqld]下添加这个变量，当该变量为NULL时，表示无法load_file，而当该变量名无具体指示时，则表示可以加载任意目录下的文件，如下所示：

[mysqld]
#
# * Basic Settings
#
secure-file-priv=

之后我们输入命令使mysql重启，再查询secure_file_priv的值，得到如下的结果：

root@ubuntu:/etc/mysql# service mysql restart
mysql stop/waiting
mysql start/running, process 98331

mysql> show global variables like '%secure%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_auth      | OFF   |
| secure_file_priv |       |
+------------------+-------+
2 rows in set (0.00 sec)

此时就可以load_file其他目录下的文件了。

LOCK 和 UNLOCK的一些用法和功能

同时进入两个Console，并都进入mysql 也use了同一个database，之后在其中一个console中LOCK TABLES `table_name` WRITE/READ; 则当前的`table_name`就只能执行这个操作了，若执行其他操作会卡住。

导出数据库和加载数据库

如下在console中运行类似命令就可以导出数据库，要加载时只需在mysql中执行命令’source sql文件绝对路径’就可以了。

mysqldump -u root -p websecurity >/home/lcf/Desktop/websecurity2.sql