mysql 基础操作

1. 基础操作

// 设置过用户及密码
$ mysql -u root -p xxxxx

// 未设置过用户及密码
$ sudo mysql

// 查看现有的数据库
mysql> show databases;

// 创建数据库
mysql> create database database_name;

// 连接数据库
// 其实也可以省去这一步，但是后面的所有查表之类的操作都要携程database_name.table_name的形式，略有点麻烦。
mysql> use database_name;

// 查看数据库中的表
mysql> show tables;

// 查看数据表结构
mysql> desc table_name;


// 退出
mysql> exit;

 

2. 导入.sql文件

// 将database_name.sql导入到database_name这个数据库中
$ mysql database_name < file_path/database_name.sql -u root -p xxxxx

 

3. 查询字段为null或不为null的row

select * from table_name where xxx is null and yyy is null
select * from table_name where xxx is not null and yyy is not null

 

4. 数据库用户

初始化用户密码 https://dev.mysql.com/doc/refman/8.0/en/default-privileges.html

mysql> SELECT * FROM mysql.user;
+-----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
| Host      | User             | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin                | authentication_string                     | password_expired | password_last_changed | password_lifetime | account_locked |
+-----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
| localhost | root             | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      |          |            |             |              |             0 |           0 |               0 |                    0 | auth_socket           |                                           | N                | 2020-04-11 15:39:44   |              NULL | N              |
| localhost | mysql.session    | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | Y          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                | N          | N            | N                      |          |            |             |              |             0 |           0 |               0 |                    0 | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | N                | 2020-04-11 15:39:44   |              NULL | Y              |
| localhost | mysql.sys        | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                | N          | N            | N                      |          |            |             |              |             0 |           0 |               0 |                    0 | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | N                | 2020-04-11 15:39:44   |              NULL | Y              |
| localhost | debian-sys-maint | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      |          |            |             |              |             0 |           0 |               0 |                    0 | mysql_native_password | *D32894783D155DA3905D98D78745701BA76E167A | N                | 2020-04-11 15:39:45   |              NULL | N              |
+-----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
4 rows in set (0.01 sec)

root用户的authentication_string字段是空的，说明没有密码，只可以通过 sudo mysql 连接。

5. 查看数据库属性

//查看数据库的大小
//从information_schema.TABLES表中取出数据库名，大小从Byte变为MB。
mysql> SELECT table_schema, ROUND(SUM(data_length + index_length)/1024/1024, 2)
    -> FROM information_schema.TABLES 
    -> GROUP BY table_schema; 
+--------------------+-----------------------------------------------------+
| TABLE_SCHEMA       | ROUND(SUM(data_length + index_length)/1024/1024, 2) |
+--------------------+-----------------------------------------------------+
| elegant_prod       |                                                1.11 |
| information_schema |                                                0.00 |
| kerui_prod         |                                                1.09 |
| mysql              |                                                2.52 |
| performance_schema |                                                0.00 |
| sys                |                                                0.02 |
+--------------------+-----------------------------------------------------+
6 rows in set (0.22 sec)

//查看某个数据库的大小
mysql> SELECT table_name, ROUND(((data_length + index_length)/1024/1024), 2)
    -> FROM information_schema.TABLES
    -> WHERE table_schema = "数据库名称";