欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-05-18 21:00:04
刷新权限,将某些权限从硬盘刷新到内存中(修改root密码自带隐式刷新权限操作) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> 查看服务端口 mysql> show variables like 'port';... ......

刷新权限,将某些权限从硬盘刷新到内存中(修改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>