MYSQL命令（一）

1.mysql 的守护进程是mysqld 

a.开启mysql数据库命令

[root@instance-sk9hvjmd ~]# service mysqld start
Redirecting to /bin/systemctl start  mysqld.service

  b.关闭mysql数据库命令

[root@instance-sk9hvjmd ~]# service mysqld stop
Redirecting to /bin/systemctl stop  mysqld.service

  c.重启mysql数据库命令

[root@instance-sk9hvjmd ~]# service mysqld restart
Redirecting to /bin/systemctl restart  mysqld.service

  d.检查mysql服务器是否在运行

[root@instance-sk9hvjmd ~]# ps -el|grep mysqld
1 S    27  2923     1  0  80   0 - 284356 poll_s ?       00:03:55 mysqld

  2.查找已安装的myslq 版本：

[root@instance-sk9hvjmd ~]# rpm -qa|grep mysql
mysql-community-server-5.7.16-1.el7.x86_64
mysql-community-common-5.7.16-1.el7.x86_64
mysql-community-client-5.7.16-1.el7.x86_64
mysql-community-libs-5.7.16-1.el7.x86_64

  3.查看文件安装路径

[root@instance-sk9hvjmd ~]# whereis mysql
mysql: /usr/bin/mysql /usr/lib64/mysql /usr/share/mysql /usr/share/man/man1/mysql.1.gz

  4.查询运行文件所在路径(文件夹地址)

[root@instance-sk9hvjmd ~]# which mysql
/usr/bin/mysql

 5.mysql登陆

Mysql -u username -p
[root@instance-sk9hvjmd ~]# mysql -u root -p
Enter password:

 6.查询数据库

show databases;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| devtest            |
| mblog              |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
7 rows in set (0.08 sec)

 7.创建数据库

create DATABASE  [DBName];

mysql>  create DATABASE RUNOOB;
Query OK, 1 row affected (0.10 sec)

 8.删除数据库

drop database [DBName];

mysql> drop database RUNOOB;
Query OK, 0 rows affected (0.17 sec)

 9.选择数据库

Use [DBName];

mysql> use devtest
Database changed

 10.查询数据表

mysql> show tables;
+-------------------+
| Tables_in_devtest |
+-------------------+
| INSURANCE_DATA    |
| TEST              |
| insurance         |
| student           |
+-------------------+
4 rows in set (0.00 sec)

 11.创建数据表

mysql> CREATE TABLE IF NOT EXISTS `runoob_tbl`(
    ->    `runoob_id` INT UNSIGNED AUTO_INCREMENT,
    ->    `runoob_title` VARCHAR(100) NOT NULL,
    ->    `runoob_author` VARCHAR(40) NOT NULL,
    ->    `submission_date` DATE,
    ->    PRIMARY KEY ( `runoob_id` )
    -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.18 sec)

12.清楚数据表数据

mysql> delete from runoob_tbl;
Query OK, 1 row affected (0.02 sec)

 13.删除数据表

mysql> drop table runoob_tbl;
Query OK, 0 rows affected (0.04 sec)

 14.查指定数据库占用空间大小

mysql> select TABLE_NAME, concat(truncate(data_length/1024/1024,2),' MB') as data_size,
    -> concat(truncate(index_length/1024/1024,2),' MB') as index_size
    -> from information_schema.tables where TABLE_SCHEMA = 'devtest'
    -> group by TABLE_NAME
    -> order by data_length desc;
+----------------+-----------+------------+
| TABLE_NAME     | data_size | index_size |
+----------------+-----------+------------+
| student        | 163.67 MB | 0.00 MB    |
| insurance      | 0.07 MB   | 0.00 MB    |
| INSURANCE_DATA | 0.07 MB   | 0.00 MB    |
| TEST           | 0.01 MB   | 0.00 MB    |
+----------------+-----------+------------+
4 rows in set (0.02 sec)

 15.查所有数据库占用空间大小

mysql> select TABLE_SCHEMA, concat(truncate(sum(data_length)/1024/1024,2),' MB') as data_size,
    -> concat(truncate(sum(index_length)/1024/1024,2),'MB') as index_size
    -> from information_schema.tables
    -> group by TABLE_SCHEMA
    -> order by data_length desc;
+--------------------+------------+------------+
| TABLE_SCHEMA       | data_size  | index_size |
+--------------------+------------+------------+
| devtest            | 163.84 MB  | 0.00MB     |
| test               | 461.15 MB  | 60.39MB    |
| mblog              | 0.25 MB    | 0.07MB     |
| information_schema | 0.15 MB    | 0.00MB     |
| performance_schema | 0.00 MB    | 0.00MB     |
| mysql              | 4.86 MB    | 0.21MB     |
| sys                | 0.01 MB    | 0.00MB     |
+--------------------+------------+------------+
7 rows in set (1.14 sec)