MYSQL入门操作和常规DML、DDL、DQL使用

刷新权限，将某些权限从硬盘刷新到内存中(修改root密码自带隐式刷新权限操作)

mysql> flush privileges;
query ok, 0 rows affected (0.00 sec)

mysql> 

查看服务端口

mysql> show variables like 'port';
+---------------+-------+
| variable_name | value |
+---------------+-------+
| port          | 3306  |
+---------------+-------+
1 row in set (0.00 sec)

mysql> 

查看数据库字符集

mysql> show variables like '%char%';
+--------------------------+----------------------------------+
| variable_name            | value                            |
+--------------------------+----------------------------------+
| character_set_client     | utf8                             |
| character_set_connection | utf8                             |
| character_set_database   | latin1                           |
| character_set_filesystem | binary                           |
| character_set_results    | utf8                             |
| character_set_server     | latin1                           |
| character_set_system     | utf8                             |
| character_sets_dir       | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.01 sec)

切数据库

mysql> select database();
+------------+
| database() |
+------------+
| test       |
+------------+
1 row in set (0.01 sec)

mysql> show databases;
+--------------------+
| database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> use mysql
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  
mysql> select database();
+------------+
| database() |
+------------+
| mysql      |
+------------+
1 row in set (0.00 sec)
mysql> status
--------------
mysql  ver 14.14 distrib 5.7.25, for linux-glibc2.12 (x86_64) using  editline wrapper

connection id:          6
current database:       mysql
current user:           root@localhost
ssl:                    not in use
current pager:          stdout
using outfile:          ''
using delimiter:        ;
server version:         5.7.25 mysql community server (gpl)
protocol version:       10
connection:             localhost via unix socket
server characterset:    latin1
db     characterset:    latin1
client characterset:    utf8
conn.  characterset:    utf8
unix socket:            /tmp/mysql.sock
uptime:                 2 days 16 hours 47 min 41 sec

threads: 1  questions: 58  slow queries: 0  opens: 138  flush tables: 1  open tables: 131  queries per second avg: 0.000
--------------


mysql> show tables;
+---------------------------+
| tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event                     |
| func                      |
| general_log               |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| server_cost               |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
31 rows in set (0.00 sec)

mysql> 

创建允许远程登陆的用户

mysql> select host,user from mysql.user;
+-----------+---------------+
| host      | user          |
+-----------+---------------+
| localhost | mysql.session |
| localhost | mysql.sys     |
| localhost | root          |
+-----------+---------------+
3 rows in set (0.00 sec)

mysql> grant all privileges on *.* to scott@'%' identified by 'tiger';
query ok, 0 rows affected, 1 warning (0.00 sec)

mysql> 

data目录中每个数据库都创建了一个文件夹，lbdata1、ib_logfile0、ib_logfile1三个是专门为innodb存放数据和日志的共享文件

mysql> create database test;
query ok, 1 row affected (0.01 sec)

mysql> use test;
database changed
mysql> create table tmp(id int);
query ok, 0 rows affected (0.03 sec)

mysql> desc tmp;
+-------+---------+------+-----+---------+-------+
| field | type    | null | key | default | extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | yes  |     | null    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.02 sec)

mysql> insert into tmp value ('2')
    -> ;
query ok, 1 row affected (0.01 sec)

mysql> commit;
query ok, 0 rows affected (0.00 sec)

mysql> select * from tmp;
+------+
| id   |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

mysql> \q
bye
[root@localhost /usr/local/mysql/data]$ ls
auto.cnf  ib_buffer_pool  ibdata1  ib_logfile0  ib_logfile1  ibtmp1  localhost.localdomain.err  localhost.localdomain.pid  mysql  performance_schema  sys  test
[root@localhost /usr/local/mysql/data]$ cd test
[root@localhost /usr/local/mysql/data/test]$ ls
db.opt  tmp.frm  tmp.ibd

每个数据库目录中的db.opt是数据库的信息,表名.frm是表的元信息,表名.ibd是数据信息,其中innodb_file_per_table参数来控制是否单独存储，5.7默认on，之前版本默认off

mysql> show variables like '%per_table%';
+-----------------------+-------+
| variable_name         | value |
+-----------------------+-------+
| innodb_file_per_table | on    |
+-----------------------+-------+
1 row in set (0.01 sec)

mysql> 

.myd .myi是老的myisam存储引擎,myi是索引信息

mysql> create table tmp2(id int);
query ok, 0 rows affected (0.02 sec)

mysql>  show create table tmp;
+-------+-----------------------------------------------------------------------------------------+
| table | create table                                                                            |
+-------+-----------------------------------------------------------------------------------------+
| tmp   | create table `tmp` (
  `id` int(11) default null
) engine=innodb default charset=latin1 |
+-------+-----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>