溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
這篇文章給大家分享的是有關MySQL數據庫常用基礎命令有哪些的內容。小編覺得挺實用的，因此分享給大家做個參考，一起跟隨小編過來看看吧。
1、使用幫助信息
登陸數據庫就不介紹了哦
比如說想做創建的數據庫的操作，不知道命令，就可以查看幫助信息
mysql> help create;
Many help items for your request exist.
To make a more specific request, please type 'help <item>',
where <item> is one of the following
topics:
CREATE DATABASE #最簡單的創建命令
CREATE EVENT
CREATE FUNCTION
CREATE FUNCTION UDF
CREATE INDEX
CREATE LOGFILE GROUP
CREATE PROCEDURE
CREATE SERVER
CREATE TABLE
CREATE TABLESPACE
CREATE TRIGGER
CREATE USER
CREATE VIEW
SHOW
SHOW CREATE DATABASE
SHOW CREATE EVENT
SHOW CREATE FUNCTION
SHOW CREATE PROCEDURE
SHOW CREATE TABLE
SPATIAL
2、創建、刪除、查看數據庫
mysql> create database test_data;
#創建默認字符集的數據庫（默認是拉丁字符集）
Query OK, 1 row affected (0.02 sec)
mysql> show databases like "test%";
+------------------+
| Database (test%) |
+------------------+
| test_data |
+------------------+
1 rows in set (0.00 sec)
創建gbk字符集的數據庫
mysql> create database test_gbk DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;
Query OK, 1 row affected (0.04 sec)
mysql> show create database test_gbk;
#查看創建數據庫的語句
+----------+------------------------------------------------------------------+
| Database | Create Database |
+----------+------------------------------------------------------------------+
| test_gbk | CREATE DATABASE `test_gbk` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)
刪除數據庫
mysql> drop database test_data;
Query OK, 0 rows affected (0.07 sec)
mysql> show databases;
+----------------------------+
| Database |
+----------------------------+
| information_schema |
| test_gbk |
+----------------------------+
3、連接數據庫
mysql> use test_gbk;
#相當于cd命令，切換到數據庫進行操作
Database changed
mysql> select database();
#查看當前連接的數據庫，相當于pwd
+------------+
| database() |
+------------+
| test_gbk |
+------------+
1 row in set (0.00 sec)
mysql> select user();
#查看當前連接數據庫的用戶,相當于whoami
+--------------------+
| user() |
+-------------------+
| root@localhost |
+--------------------+
1 row in set (0.00 sec)
4、創建用戶、授權、收回權限
當數據庫創建完成后，就需要創建用戶，以供需要連接數據庫的人員使用與操作數據庫，不可能人人使用root登陸，所以權限設置也是很重要的
mysql> grant all on test_gbk.* to 'testuser'@'localhost' identified by '123456';
#創建用戶并all權限給在test_gbk庫所有表，密碼‘123456’
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
#刷新權限，使權限生效
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'testuser'@'localhost';
#查看用戶有哪些權限
+-----------------------------------------------------------------------------------------------------------------+
| Grants for testuser@localhost |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'testuser'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
| GRANT ALL PRIVILEGES ON `test_gbk`.* TO 'testuser'@'localhost' |
+-----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
收回權限
mysql> revoke insert,update,select,delete on test_gbk.* from 'testuser'@'localhost';
#將以上權限收回
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'testuser'@'localhost';
+----------------------------------------------------------------------------------------------------+
| Grants for testuser@localhost |
+----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'testuser'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
| GRANT CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `test_gbk`.* TO 'testuser'@'localhost' |
+------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
注：原來不知道all權限到底是哪些權限，采用這種方法之后，應該就清楚了
SELECT,INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER
5、創建、刪除表
mysql> create table test(id int(4)not null,name char(20)not null);
#建表，并且建立兩個字段
Query OK, 0 rows affected (0.06 sec)
mysql> show tables;#查看表
+--------------------+
| Tables_in_test_gbk |
+--------------------+
| test |
+--------------------+
1 row in set (0.00 sec)
mysql> desc test; #查看表結構
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(4) | NO | | NULL | |
| name | char(20) | NO | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> create table test1(id int(4)not null,name char(20)not null);
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+------------------------+
| Tables_in_test_gbk |
+------------------------+
| test |
| test1 |
+--------------------+
2 rows in set (0.00 sec)
刪除表
mysql> drop tables test;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+--------------------+
| Tables_in_test_gbk |
+--------------------+
| test1 |
+--------------------+
1 row in set (0.00 sec)
查看建表
mysql> show create table test1\G
*************************** 1. row ***************************
Table: test1
Create Table: CREATE TABLE `test1` (
`id` int(4) NOT NULL,
`name` char(20) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=gbk
1 row in set (0.00 sec)
感謝各位的閱讀！關于“MySQL數據庫常用基礎命令有哪些”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，讓大家可以學到更多知識，如果覺得文章不錯，可以把它分享出去讓更多的人看到吧！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
