MySQL基礎操作

標籤：

MySQL資料庫是一個C/S（用戶端/服務端）架構應用，要訪問MySQL資料庫要使用專門的用戶端軟體

• 登陸到MySQL伺服器

[[email protected] /]# mysql -u rootWelcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 5Server version: 5.5.22-log Source distributionCopyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.mysql> 

 

• SHOW MASTER LOGS 語句可以查看當前資料庫服務的記錄檔資訊

mysql> show master logs;+------------------+-----------+| Log_name         | File_size |+------------------+-----------+| mysql-bin.000001 |     27284 || mysql-bin.000002 |   1031892 || mysql-bin.000003 |       107 |+------------------+-----------+3 rows in set (0.01 sec)

 

• 退出mysql>作業環境

執行"quit" "exit" 可以退出mysql命令工具，返回原來的Shell環境

mysql> quitBye

 

• 查看當前伺服器中有哪些庫

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

 

• 查看當前使用的庫中有哪些表

mysql> use mysql;   #切換到所使用的庫Database changedmysql> show tables;   #查看當前所在的庫中包含的表+---------------------------+| Tables_in_mysql           |+---------------------------+| columns_priv              || db                        || event                     || func                      || general_log               || help_category             || help_keyword              || help_relation             || help_topic                || host                      || ndb_binlog_index          || plugin                    || proc                      || procs_priv                || proxies_priv              || servers                   || slow_log                  || tables_priv               || time_zone                 || time_zone_leap_second     || time_zone_name            || time_zone_transition      || time_zone_transition_type || user                      |+---------------------------+24 rows in set (0.00 sec)mysql資料庫的檔案存放在/usr/lcoal/mysql/data目錄下，每個資料庫對應一個子目錄，用於儲存資料表檔案。每個資料表對應為三個檔案，尾碼名分別為".frm" 、".myd"和".myi"。

 

• 查看錶的結構

describe語句：用於顯示表的結構，即組成表的欄位（列）的資訊。

mysql> use mysql;Database changedmysql> describe user;+------------------------+-----------------------------------+------+-----+---------+-------+| Field                  | Type                              | Null | Key | Default | Extra |+------------------------+-----------------------------------+------+-----+---------+-------+| Host                   | char(60)                          | NO   | PRI |         |       || User                   | char(16)                          | NO   | PRI |         |       || Password               | char(41)                          | NO   |     |         |       || Select_priv            | enum(‘N‘,‘Y‘)                     | NO   |     | N       |       || Insert_priv            | enum(‘N‘,‘Y‘)                     | NO   |     | N       |       || Update_priv            | enum(‘N‘,‘Y‘)                     | NO   |     | N       |       || Delete_priv            | enum(‘N‘,‘Y‘)                     | NO   |     | N       |       || Create_priv            | enum(‘N‘,‘Y‘)                     | NO   |     | N       |       || Drop_priv              | enum(‘N‘,‘Y‘)                     | NO   |     | N       |       || Reload_priv            | enum(‘N‘,‘Y‘)                     | NO   |     | N       |       || Shutdown_priv          | enum(‘N‘,‘Y‘)                     | NO   |     | N       |       || Process_priv           | enum(‘N‘,‘Y‘)                     | NO   |     | N       |       || File_priv              | enum(‘N‘,‘Y‘)                     | NO   |     | N       |       || Grant_priv             | enum(‘N‘,‘Y‘)                     | NO   |     | N       |       || References_priv        | enum(‘N‘,‘Y‘)                     | NO   |     | N       |       || Index_priv             | enum(‘N‘,‘Y‘)                     | NO   |     | N       |       || Alter_priv             | enum(‘N‘,‘Y‘)                     | NO   |     | N       |       || Show_db_priv           | enum(‘N‘,‘Y‘)                     | NO   |     | N       |       || Super_priv             | enum(‘N‘,‘Y‘)                     | NO   |     | N       |       || Create_tmp_table_priv  | enum(‘N‘,‘Y‘)                     | NO   |     | N       |       || Lock_tables_priv       | enum(‘N‘,‘Y‘)                     | NO   |     | N       |       || Execute_priv           | enum(‘N‘,‘Y‘)                     | NO   |     | N       |       || 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)                          | YES  |     |         |       || authentication_string  | text                              | YES  |     | NULL    |       |+------------------------+-----------------------------------+------+-----+---------+-------+42 rows in set (0.00 sec)

 

• 建立新的庫

create database 語句：用於建立一個新的庫，需要指定資料庫名稱作為參數。

mysql> create database auth;Query OK, 1 row affected (0.00 sec)
剛建立的資料庫是空的，其中不包含任何錶，在/usr/lcoal/mysql/data目錄下會自動產生一個與建立的庫名相同的空檔案夾

 

• 建立新的表

create table 語句：用於在當前庫中穿件新的表，需指定資料庫表名稱作為參數，並定義該表格所使用的各欄位

mysql> use auth;Database changedmysql> create table users (user_name char(16) not null, user_passwd char(48) default ‘‘, primary key (user_name));Query OK, 0 rows affected (0.11 sec)

 

• drop table語句：用於刪除庫中的表，需要指定"庫名.表名" 作為參數；若只指定表明參數，則需先通過執行"use"語句切換到目標庫。

mysql> drop table auth.users;Query OK, 0 rows affected (0.00 sec)

 

• 刪除一個資料庫

drop database語句：用於刪除指定的庫，需要指定庫名作為參數。

mysql> drop database auth;Query OK, 0 rows affected (0.00 sec)

 

MySQL基礎操作