-
客户端连接MySQL服务
C:\WINDOWS\system32> mysql -uroot -p 连接本地MySQL服务器 C:\WINDOWS\system32> mysql -uroot -p -h11.234.56.34 连接目标MySQL服务器
•mysql –uroot –p 回车,录入mysql的root用户密码
-u代表mysql的用户名,可以不加空格之间填写连接用户-uroot
-p代表用户名对应的密码,可以直接在-p后面写密码,但是旁边有人能看到
mysql -uroot -poracle
-p后面可以不写密码,直接回车后会提醒录入密码,这样比较安全
退出连接 mysql>quit; 或者 exit;
-
查看MySQL服务器版本
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.24 |
+-----------+
1 row in set (0.00 sec)
-
查看当前连接用户
mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
-
查看当前服务器日期和时间
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2018-12-18 12:56:19 |
+---------------------+
1 row in set (0.00 sec)
-
当前用户可以访问的数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
-
查看“mysql”数据库的表
mysql> show tables from mysql;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| engine_cost |
| event |
| func |
...........................
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
31 rows in set (0.00 sec)
-
查看当前使用的数据库
mysql> select database();
+------------+
| database() |
+------------+
| NULL |
+------------+
1 row in set (0.00 sec)
-
查看mysql库的user表的列
mysql> desc mysql.user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(32) | NO | PRI |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) unsigned | NO | | 0 | |
| plugin | char(64) | NO | | mysql_native_password | |
| authentication_string | text | YES | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
| password_last_changed | timestamp | YES | | NULL | |
| password_lifetime | smallint(5) unsigned | YES | | NULL | |
| account_locked | enum('N','Y') | NO | | N | |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
45 rows in set (0.00 sec)
-
查看mysql库的user表的行
mysql> select * from mysql.user\G
*************************** 1. row ***************************
Host: localhost
User: root
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
...........................................
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string: *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE
password_expired: N
password_last_changed: 2018-12-18 12:23:27
password_lifetime: NULL
account_locked: Y
3 rows in set (0.00 sec)
user表的列太多,导致折行,把列转成行来看,这样清晰一些
-
数据库、表、列存放位置
mysql> show variables like 'datadir'; +---------------+----------------+ | Variable_name | Value | +---------------+----------------+ | datadir | D:\mysql\data\ | +---------------+----------------+ 1 row in set, 1 warning (0.00 sec) mysql> show tables from mysql like 'user'; +------------------------+ | Tables_in_mysql (user) | +------------------------+ | user | +------------------------+ 1 row in set (0.00 sec) mysql> select user,host,authentication_string from mysql.user; +---------------+-----------+-------------------------------------------+ | user | host | authentication_string | +---------------+-----------+-------------------------------------------+ | root | localhost | *A4B6157319038724E3560894F7F932C8886EBFCF | | mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | | mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | +---------------+-----------+-------------------------------------------+ 3 rows in set (0.00 sec) mysql> desc mysql.user; +------------------------+-----------------------------------+------+-----+-----------------------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------------+-----------------------------------+------+-----+-----------------------+-------+ | Host | char(60) | NO | PRI | | | | User | char(32) | NO | PRI | | | | Select_priv | enum('N','Y') | NO | | N | | | Insert_priv | enum('N','Y') | NO | | N | max_updates | int(11) unsigned | NO | | 0 | | | max_connections | int(11) unsigned | NO | | 0 | | | max_user_connections | int(11) unsigned | NO | | 0 | | | plugin | char(64) | NO | | mysql_native_password | | | authentication_string | text | YES | | NULL | | | password_expired | enum('N','Y') | NO | | N | | | password_last_changed | timestamp | YES | | NULL | | | password_lifetime | smallint(5) unsigned | YES | | NULL | | | account_locked | enum('N','Y') | NO | | N | | +------------------------+-----------------------------------+------+-----+-----------------------+-------+ 45 rows in set (0.00 sec)
数据库1(目录1)
-
表 (文件)
-
列1,列2,列3,……( 一个表至少有一列 )
-
行1,行2,行3,……(一个表可以有0到N行)
-
数据库2(目录2)
-
表1
-
表2
-
表3
-
……
-
数据库3(目录3)
-
表1
-
……
-
创建数据库
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
创建名字为‘test’的数据库
mysql> create database test character set 'utf8' collate 'utf8_general_ci';
Query OK, 1 row affected (0.01 sec)
创建名为 “test”的数据库,使用utf8编码可以存储中文
character set
指定字符串以何种编码存储。如果在库中建表,没有指定以何种编码存储,默认使用utf8编码存储表中的字符串
collate
定义字符串排序的顺序和比较的方式
utf8_general_ci
字符串按照UTF8编码比较大小,不区分大小写ci = case insensitive
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| demo |
| mysql |
| performance_schema |
| student |
| sys |
| test |
+--------------------+
7 rows in set (0.00 sec)
显示所有数据库
删除数据库
mysql> drop database test;
Query OK, 0 rows affected (0.01 sec)
删除文件名为‘test’的数据库
- 目录会被删除
- 如果里面有表文件也会被删除
-
千万不要删除mysql,sys,performance_schema等这些系统数据库
-
如果删除了,mysql服务器程序崩溃,切记危险!