對(duì)mysql數(shù)據(jù)庫(kù)中的數(shù)據(jù)進(jìn)行操作的命令分為DDL和DML兩種：
- DDL命令：用于定義數(shù)據(jù)，管理數(shù)據(jù)庫(kù)的組件，比如管理數(shù)據(jù)庫(kù)，表，索引，視圖，用戶(hù)，存儲(chǔ)過(guò)程等。命令有：CREATE、ALTER、DROP
- DML命令：用于操縱數(shù)據(jù)，管理表中的數(shù)據(jù)，比如對(duì)數(shù)據(jù)進(jìn)行增、刪、改、查。命令有：INSERT， DELETE， UPDATE， SELECT
下面對(duì)一些常用的SQL操作舉出實(shí)例：
[root@localhost ~]# mysql -uroot -p112233 #登錄數(shù)據(jù)庫(kù)
MariaDB [(none)]> SHOW DATABASES; #查看數(shù)據(jù)庫(kù)
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| testdb |
| wordpress |
+--------------------+
MariaDB [mysd]> CREATE DATABASE hidb; #創(chuàng)建數(shù)據(jù)庫(kù)hidb
MariaDB [(none)]> use hidb; #連接數(shù)據(jù)庫(kù)
MariaDB [hidb]> CREATE TABLE tbl1 (id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE KEY,name CHAR(30) NOT NULL);
#創(chuàng)建一個(gè)tbl1表，兩個(gè)字段，字段id長(zhǎng)度為SMALLINT，沒(méi)有符號(hào)，不能為空，自動(dòng)增長(zhǎng)，唯一鍵；定義字段name，長(zhǎng)度30類(lèi)型CHAR,不能為空
MariaDB [hidb]> ALTER TABLE tbl1 ADD gender ENUM('F','M') after id;
#往表tbl1添加枚舉字段gender，添加在id后面；
MariaDB [hidb]> DESC tbl1; #查看字段
+--------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| gender | enum('F','M') | YES | | NULL | |
| name | char(30) | NO | | NULL | |
+--------+----------------------+------+-----+---------+----------------+
MariaDB [hidb]> ALTER TABLE tbl1 DROP gender;
#刪除表tbl1中字段gender
MariaDB [hidb]> DESC tbl1; #查看字段，gender被刪除
+-------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| name | char(30) | NO | | NULL | |
+-------+----------------------+------+-----+---------+----------------+
MariaDB [hidb]> INSERT INTO tbl1 VALUES (1,'hualong'),(2,'tom'),(3,'jerry');
#增加三行數(shù)據(jù)
MariaDB [hidb]> SELECT * FROM tbl1; #查看數(shù)據(jù)
+----+---------+
| id | name |
+----+---------+
| 1 | hualong |
| 2 | tom |
| 3 | jerry |
+----+---------+
MariaDB [hidb]> UPDATE tbl1 SET name='TOM' WHERE id=2;
#修改id為的行中的name值
MariaDB [hidb]> SELECT * FROM tbl1; #查看修改后的數(shù)據(jù)
+----+---------+
| id | name |
+----+---------+
| 1 | hualong |
| 2 | TOM |
| 3 | jerry |
+----+---------+
MariaDB [hidb]> DELETE FROM tbl1 WHERE id=2;
#刪除id為2的那一行
MariaDB [hidb]> SELECT * FROM tbl1; #查看刪除后的數(shù)據(jù)
+----+---------+
| id | name |
+----+---------+
| 1 | hualong |
| 3 | jerry |
+----+---------+
MariaDB [hidb]> DROP TABLE tbl1; #刪除hidb的表tbl1
MariaDB [hidb]> DROP DATABASE hidb; #刪除數(shù)據(jù)庫(kù)hidb
MariaDB [hidb]> LOCK TABLE tbl1 read;
#在本終端上,鎖住表tbl1為只讀，其他終端操作這個(gè)表時(shí)，可以讀取，但是運(yùn)行寫(xiě)或加鎖會(huì)阻塞
MariaDB [hidb]> UNLOCK TABLES;
#本終端解除表鎖定
重新修改mysql登錄密碼
MariaDB [hidb]> SET PASSWORD FOR 'root'@'localhost'=PASSWORD('aabbcc');
#修改掉root的登錄密碼
[root@localhost ~]# systemctl stop mariadb #停止mysql服務(wù)
[root@localhost ~]# vim /etc/my.cnf.d/server.cnf
[mysqld] #添加兩句,登錄mysql時(shí)跳過(guò)驗(yàn)證
skip-grant-tables
skip-networking
[root@localhost ~]# systemctl daemon-reload #重新載入systemd
[root@localhost ~]# systemctl start mariadb
[root@localhost ~]# mysql #輸入命令直接登錄數(shù)據(jù)庫(kù)
MariaDB [(none)]>
MariaDB [(none)]> UPDATE mysql.user SET password=PASSWORD('112233') WHERE user='root';
#重新設(shè)置管理員密碼
[root@localhost ~]# systemctl stop mariadb
[root@localhost ~]# vim /etc/my.cnf.d/server.cnf
#刪除之前添加的兩行
[root@localhost ~]# systemctl daemon-reload
[root@localhost ~]# systemctl start mariadb
[root@localhost ~]# mysql -uroot -p112233
#此時(shí)輸入修改后的密碼成功登錄
