Mysql常用命令

一. 連接到MySQL服務器

1. 找到可執行程式mysql.exe所在的檔案夾,把這個檔案夾添加到搜索路徑path中
   2.在Windows中"運行"的地方鍵入cmd,進入命令列狀態
   3.在命令列狀態下鍵入 mysql -u用戶名 -p密碼,然后回車(不夠安全,會被在場的人看到密碼)
   或者鍵入 mysql -u用戶名 -p,然后回車,回車后再鍵入密碼
   出現mysql>表示已連接到MySQL上了
   說明:如果是遠程的服務器,則需要-h引數指明服務器的ip地址

二. MySQL的常用命令

<一>.資料庫的基本操作

1.創建資料庫

在mysql>提示符下鍵入
create database 資料庫名;然后按回車
注意命令結尾必須加上";"(英文的分號),命令輸入完以后,按回車執行
如:create database Xkgl;
或 create database if not exists Xkgl;
如果執行成功則會顯示Query OK,如果出錯則會給出出錯資訊,初學者的出錯資訊多為syntax(語法)錯誤
若顯示 Can’t create database ‘xkgl’; database exists 則表示該資料庫已經存在了,不能再創建了,為了避免這種錯誤,可以把創建資料庫的陳述句寫成
create database if not exists 資料庫名;

2.顯示資料庫

在mysql>提示符下鍵入
show databases; 然后按回車,
注意databases為復數,少了最后的s會出語法錯誤

3.顯示當前資料庫

在mysql>提示符下鍵入

mysql> select daabase();

4.顯示當前用戶

在mysql>提示符下鍵入

mysql> select user();

5.切換當前資料庫

在mysql>提示符下鍵入
use 資料庫名; 然后按回車,
執行成功后會顯示 Database changed,如果資料庫名字輸錯了則會顯示ERROR 1049 (42000): Unknown database ‘資料庫名’

6.洗掉資料庫

在mysql>提示符下鍵入
drop database 資料庫名; 然后按回車,
執行成功后會顯示 Query OK, 0 rows affected (0.02 sec)
排除語法錯誤后可能還會出現的錯誤是:ERROR 1008 (HY000): Can’t drop database ‘資料庫名’; database doesn’t exist 資料庫不存在
為了避免這種錯誤,可以這樣來寫 drop database if exists 資料庫名;

7.備份資料庫

在windows命令列下(如果是在mysql>提示符下,需要先鍵入quit或exit,退出)鍵入
mysqldump -u用戶名 -p密碼 資料庫名 > 檔案名.sql
檔案名允許包含路徑名

8.還原資料庫

匯入前需要先創建資料庫
在windows命令列下(如果是在mysql>提示符下,需要先鍵入quit或exit,退出)鍵入

mysql -u用戶名 -p密碼 資料庫名 < 檔案名.sql 

檔案名允許包含路徑名,還原時資料庫名的資料庫必須存在,否則會出現 Unknown database ‘資料庫名’ 這種錯誤

9.修改root密碼：

mysql> UPDATE mysql.user SET password=PASSWORD('新密碼') 
WHERE User='root';
mysql> flush privileges;

mysql 新設定用戶或更改密碼后需用flush privileges重繪MySQL的系統權限相關表，否則會出現拒絕訪問，還有一種方法，就是重新啟動mysql服務器，來使新設定生效

<二>.表的基本操作

1.創建表

在mysql>提示符下鍵入

create table 表名(
列名1 資料型別 列的屬性1 ... 列的屬性m1,
列名2 資料型別 列的屬性1 ... 列的屬性m2,
...
列名n 資料型別 列的屬性1 ... 列的屬性mn);

2.顯示當前資料庫中的表

在mysql>提示符下鍵入

show tables;

注意tables是復數,少了后面的s會出語法錯

3.顯示表結構

在mysql>提示符下鍵入
describe 表名;

4.查看表的結構(生成表的SQL陳述句)：

mysql> show create table xsxxb;
±------±------------------------
| Table | Create Table |
±------±------------------------
| xsxxb | CREATE TABLE xsxxb (
xsid int(11) NOT NULL AUTO_INCREMENT,
xh varchar(10) DEFAULT NULL,
xm varchar(50) DEFAULT NULL,
xb char(2) DEFAULT NULL,
xmsx varchar(10) DEFAULT NULL,
PRIMARY KEY (xsid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
±------±-------------------------------------------------------------

5 修改表名

在mysql>提示符下鍵入
alter table 原表名 rename 新表名;

6 修改表結構

在mysql>提示符下鍵入
alter table 表名 具體命令;
1）增加一個欄位格式：
add column (欄位名 欄位型別); ----此方法帶括號
2）指定欄位插入的位置：
　 add column 欄位名 欄位型別 after 某欄位；
3) 洗掉一個欄位：
　 drop 欄位名;
4）修改欄位名稱/型別
　 change 舊欄位名 新欄位名 新欄位的型別;

6 增加主鍵，外鍵，約束，索引(見實體)

1. 約束（主鍵Primary key、唯一性Unique、非空Not Null）
2. 自動增張 auto_increment
3. 外鍵Foreign key-----與reference table_name(col_name列名)配合使用，建表時單獨使用
4. 洗掉多個表中有關聯的資料----設定foreign key 為set null —

7…匯出一個表
mysqldump -u 用戶名 -p 資料庫名 表名> 匯出的檔案名
mysqldump -uroot -p123 xkgl xsxxb > xsxxb.sql

練習1 創建xsxxb表

mysql> create table xsxxb(
    -> xsid int auto_increment not null primary key,
    -> xh varchar(10),
    -> xm varchar(50),
    -> xb char(2),
    -> xmsx varchar(10));

Query OK, 0 rows affected (0.06 sec)

練習2 創建kcxxb表

mysql> create table kcxxb(
    -> kcid int auto_increment not null primary key,
    ->  kcdm varchar(10) not null,
    -> kcmc varchar(50),
    ->  xf numeric(5,2));

Query OK, 0 rows affected (0.07 sec)

練習3 創建xkjlb表,在創建表時設定外鍵

mysql> create table xkjlb (
    -> xkid int auto_increment primary key,
    -> xsid int,constraint foreign key (xsid) references xsxxb(xsid) on delete set null,
    -> kcid int,constraint foreign key (kcid) references kcxxb(kcid) on delete set null,
    -> cj int);

Query OK, 0 rows affected (0.11 sec)

練習4 創建xkjlb表,在創建表時不設定外鍵
4.1洗掉xkjlb

mysql> drop table if exists xkjlb;

Query OK, 0 rows affected (0.06 sec)

4.2洗掉xkjlb

mysql> create table xkjlb(
    -> xkid int auto_increment not null primary key,
    -> xsid int,
    -> kcid int,
    -> cj int);

Query OK, 0 rows affected (0.10 sec)

4.3 給xkjlb表設定外鍵

mysql> alter table xkjlb add constraint foreign key (xsid) references xsxxb(xsid) on delete set null;
Query OK, 0 rows affected (0.20 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table xkjlb add constraint foreign key(kcid) references kcxxb(kcid) on delete set null;
Query OK, 0 rows affected (0.13 sec)

Records: 0 Duplicates: 0 Warnings: 0
　　
練習5. 向xsxxb表中插入資料,同時插入多條
　　insert into users (xh,xm,xb) values (101,‘張三’,‘男’),(102,‘李四’,‘男’),(103,‘王五’,‘男’);

三. 一些特殊情況的處理

<一> 字符集出現錯誤解決辦法

出現的問題：

mysql> update xsxxb set xm='小明' where xsid=2;

ERROR 1366 (HY000): Incorrect string value: '/xB9/xD8/xD3/xF0' for column 'xm' at row 1

向表中插入中文字符時，出現錯誤，
　　
　　mysql> select xsid,xm from xsxxb;
　　±-------±---------+
　　| xsd | xm |
　　±-------±---------+
　　| 1 | ??? |
　　| 2 | ??? |
　　| 3 | ?í?ù |
　　±-------±---------+
　　3 rows in set (0.00 sec)
　　表中的中文字符位亂碼，

解決辦法：
　　使用命令：mysql> status

     mysql  Ver 8.0.18 for Win64 on x86_64 (MySQL Community Server - GPL)
    Connection id:          38
    Current database:    xkgl
    Current user:           root@localhost
   SSL:                    Cipher in use is TLS_AES_256_GCM_SHA384
   Using delimiter:        ;
   Server version:         8.0.18 MySQL Community Server - GPL
   Protocol version:       10
   Connection:             localhost via TCP/IP
　Server characterset: latin1
　Db characterset: latin1
   Client characterset:    gbk
   Conn.  characterset:    gbk
   TCP port:               3306

Uptime: 1 day 2 hours 14 min 32 sec

查看mysql發現Server characterset，Db characterset的字符集設成了latin1，所以出現中文亂碼，
　　
　　更改表的字符集，
　　mysql> alter table xsxxb character set GBK;
　　Query OK, 3 rows affected (0.08 sec)
　　Records: 3 Duplicates: 0 Warnings: 0

  如果向表中插入中文然后有錯誤，

mysql> insert into xsxxb (xh,xm) values  (101,'張三');
　　ERROR 1366 (HY000): Incorrect string value:

 '/xD6/xD0/xCE/xC4' for column 'xm' at row 1

這時就需要更改xsxxb表的xm的字符集，

mysql> alter table xsxxb modify xm varchar(20) character set gbk;

在表中已經有資料的情況下，所以更改xm字符集的操作可能會出錯,這時可先清空xsxxb表中的資料,然后再修改

mysql> truncate table xsxxb;

Query OK, 3 rows affected (0.01 sec)

從新更改xsxxb表中xm的字符集

mysql> alter table xsxxb modify xm varchar(50) character set gbk;

Query OK, 0 rows affected (0.06 sec)
　　Records: 0 Duplicates: 0 Warnings: 0

這時再插入中文字符，就沒有問題了，

mysql> insert into xsxxb (xh,xm) values  (101,'張三');

Query OK, 1 row affected (0.01 sec)

<二>.SQLyog連接MySQL時出現的2058錯誤解決方法

配置新連接報錯：錯誤號碼 2058，原因是 mysql 8.0 密碼加密方法變了，
解決方法：windows 下cmd 登錄 mysql -u root -p 登錄你的 mysql 資料庫，然后執行這條SQL陳述句：
mysql>ALTER USER ‘root’@‘localhost’ IDENTIFIED WITH mysql_native_password BY ‘password’;（注意分號）

<三>.創建函式時出錯

在MySql中創建自定義函式報錯資訊如下：
ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)
解決方法：
mysql>set global log_bin_trust_function_creators=1;

<四>. 匯入并執行外部sql腳本檔案

mysql> USE 資料庫名;
mysql> SOURCE 外部檔案名;