MySQL應用管理

這篇文章主要講述基本的SQL語句，以供新手參考使用，不過最好的辦法還是查閱官方文檔和help命令。

進入正題

什么是SQL？

       SQL結構化查詢語言，是關系型數據庫查詢和管理語言，是一種數據庫查詢和程序設計語言，用于存取數據以及查詢，更新和管理關系型數據庫系統。

SQL的分類

           1、DQL 數據查詢語言，SELECT

       2、DML數據操作語言,INSERT,UPDATE,DELETE

       3、TPL(事務處理語言)被DML影響的表能夠及時更新。TRSANCTION，COMMIT，

       4、DCL,數據控制語言，GRANT，REVOKE，COMMIT，ROLLBACK

       5、DDL數據定義語言，CREATE，DROP

       6，CCL指針控制語言，一般都用不到的

       最常用分類：DDL，DML，DCL。DBA，運維主要使用DDL，DCL

MySQL應用管理：

目錄：

    1、數據庫

    2、用戶管理

    3、數據類型

    4、表

    5、DML數據操作

    6、索引

    7、約束

    8、總結

1、數據庫：數據庫是以一定方式存儲在一起，能為多個用戶共享，具有盡可能小的冗余度，與應用程序彼此獨立的數據集合。平時生活中經常遇到，比如excel表格存放的某一文件夾，存儲下來方便以后隨時查閱。

mysql> show databases;         #顯示數據庫
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.15 sec)
mysql> create database test;          #創建test數據庫
Query OK, 1 row affected (0.04 sec)
 
mysql> create database test1 character set utf8;  #創建字符集為utf8的數據庫
Query OK, 1 row affected (0.00 sec)

mysql> use mysql;                    #使用數據庫
Database changed

mysql> alter database test default character set utf8;  #更改數據庫的字符集
Query OK, 1 row affected (0.00 sec)
 
mysql> drop database test;                  #刪除數據庫
Query OK, 0 rows affected (0.05 sec)

2、用戶管理

mysql> select user,host,password from user;   #查看用戶
+--------+-----------+----------+
| user   | host      | password |
+--------+-----------+----------+
| root   | localhost |          |
| system | node1     |          |
| root   | 127.0.0.1 |          |
| root   | ::1       |          |
+--------+-----------+----------+
4 rows in set (0.01 sec)
mysql> create user 'system'@'192.168.198.%' identified by 'redhat';    #創建局域網內可以遠程連接的用戶
Query OK, 0 rows affected (0.00 sec)

mysql> delete from mysql.user where user='system';   #刪除用戶
Query OK, 2 rows affected (0.00 sec)

mysql> update mysql.user set user='system' where host='node1';   #更新用戶名。
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> grant all on *.* to 'test'@'localhost';   #創建test用戶并且授予所有權限。
Query OK, 0 rows affected (0.00 sec)
 
mysql> show grants for 'test'@'localhost';  #查看用戶被授予的權限
+---------------------------------------------------+
| Grants for test@localhost                         |
+---------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'test'@'localhost' |  
+---------------------------------------------------+
1 row in set (0.00 sec)
 
mysql> revoke insert on *.* from 'test'@'localhost';        #吊銷用戶的某些權限
Query OK, 0 rows affected (0.00 sec)
 
使用此命令查看。用戶可以使用的所有權限:
[root@node1 ~]# mysql -S /tmp/mysql.sock3 -e "show grants for 'test'@'localhost';" | grep -i 'grant\>' | tr ',' '\n'   
GRANT SELECT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE

3、數據類型：數據類型是數據的一種屬性，表示數據所表示信息的類型，在MySQL數據庫里面可以使用的數據類型由日期和時間數據類型，字符數據類型和數值數據類型

3.1日期和時間型

3.2字符數據類型

3.3數值數據類型

參考：http://www.cnblogs.com/zbseoag/archive/2013/03/19/2970004.html   mysql數據類型

4、表操作，表是數據庫中用來存儲數據的對象是具有結構數據的集合，是整個數據庫系統的基礎。就像excel表。在表上面有各種控制方式(約束，規則，默認值和數據類型)確保數據的完整性和有效性

mysql> show tables;    #查看數據庫里面的表
+----------------+
| Tables_in_test |
+----------------+
| comment        |
| student        |
| student1       |
| test1          |
| test2          |
+----------------+
5 rows in set (0.00 sec)
mysql> create table test(id int(2),name char(20));  #創建表
Query OK, 0 rows affected (0.11 sec)
 
mysql> create table table2 like student;     #根據表student創建表table2，
Query OK, 0 rows affected (0.01 sec)

mysql> create table table5 select * from student;    #根據表student創建表table5，
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> desc student;  #查看表結構
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(4)      | NO   |     | NULL    |       |
| name  | char(20)    | NO   | PRI | NULL    |       |
| age   | tinyint(2)  | NO   |     | 0       |       |
| dept  | varchar(16) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table student modify column name varchar(25);      #修改表中的name列。
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> alter table student add column phone char(15);      #在表student中添加phone列
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> alter table student drop column phone;     #刪除student表中的phone列。             
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table test rename to newtest;   #改變表的名字
Query OK, 0 rows affected (0.03 sec)
 
mysql> drop table newtest;                  #刪除表
Query OK, 0 rows affected (0.01 sec)

5、DML數據操作

基本的DML數據操作可以分為4類，INSERT，SELECT，UPDATE，和DELETE。

mysql> desc test_DML;    #查看表結構
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | NO   |     | NULL    |       |
| name  | char(15) | NO   |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
 
mysql> insert into test_DML values(1,'yun zhonghe');   #插入單個數據
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into test_DML values(2,'yang guo'),(3,'huang yaoshi');  #批量插入數據
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
mysql> create table test_DML1 like test_DML;        #根據test_DML創建表test_DML1
Query OK, 0 rows affected (0.03 sec)
 
mysql> insert into test_DML1 select * from test_DML;#把表test_DML 里面的數據插入test_DML1
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0
 
mysql> select * from test_DML1;        #查詢數據，不建議使用。
+----+--------------+
| id | name         |
+----+--------------+
|  1 | yun zhonghe  |
|  2 | yang guo     |
|  3 | huang yaoshi |
+----+--------------+
 
mysql> select id from test_DML1;  #只查詢id相關的列
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)
 
mysql> select id,name from test_DML1 where id>2;    #查詢表中的id，和name同時滿足id>2
+----+--------------+
| id | name         |
+----+--------------+
|  3 | huang yaoshi |
+----+--------------+
1 row in set (0.00 sec)
3 rows in set (0.00 sec)
 
mysql> update test_DML set id=5 where name='huang yaoshi';   #更新huang yaoshi 的id為5
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> delete from test_DML where id=2;   #刪除id為2的數據
Query OK, 1 row affected (0.02 sec)
 
mysql> select id,name from test_DML;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | yun zhonghe  |
|  5 | huang yaoshi |
+----+--------------+
2 rows in set (0.00 sec)

6、索引

       索引就像書的目錄一樣，如果再字段上建立了索引，那么索引列為查詢條件時可以加快查詢數據的速度，這是mysql優化的重要內容之一。

mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(4)      | NO   |     | NULL    |       |
| name  | varchar(25) | YES  |     | NULL    |       |
| age   | tinyint(2)  | NO   |     | 0       |       |
| dept  | varchar(16) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
 
mysql> create index index_name on student(name); #在student表name字段建索引index_name
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> create unique index index_id_name  on student(id);  #創建唯一索引index_id在id上
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(4)      | NO   | PRI | NULL    |       |
| name  | varchar(25) | YES  | MUL | NULL    |       |
| age   | tinyint(2)  | NO   |     | 0       |       |
| dept  | varchar(16) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
 
mysql> alter table student add index index_age_name(age);   #使用alter建立索引
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> drop index index_age_name on student;   #刪除索引index_age_name
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> show index from student\G       #查看索引
*************************** 1. row ***************************
        Table: student     #表名
   Non_unique: 0          #s如果索引不能包含重復數據為0，如果可以則為1
     Key_name: index_id_name   #索引名稱
 Seq_in_index: 1         #索引的列序列號
  Column_name: id          #列名稱 
    Collation: A    #列以什么方式存儲在索引中，A表示升序，NULL表示無分類
  Cardinality: 0     #
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE   #索引類型，可以是BTREE，FULLTEXT，HASH或者RTREE
      Comment:        #注釋
Index_comment:
*************************** 2. row ***************************
        Table: student
   Non_unique: 1
     Key_name: index_name
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
2 rows in set (0.00 sec)
 
mysql> alter table student drop index index_name;   #使用alter刪除索引。
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

7、完整性約束

數據的完整性是保證數據的正確性和一致性，可以通過在創建或修改表時候定義完整性約束。完整性約束分為行級別和表級兩類，處理機制是一樣的，行約束放在行后，表約束放在表之后。完整性約束是一種規則，不占用任何數據庫的空間。

在MySQL數據庫里面，常見的約束類型有NOT NULL，主鍵，唯一鍵和外鍵。

7.1NOT NULL 在某列上不允許輸入空值，只能約束在列級。

7.2PRIMARY KEY主鍵 ，某一列或者多個列的組合的取值必須是唯一的，不能有重復數據，也不能存在空值，只能在表上創建一個主鍵，為表創建主鍵的同時，也會自動創建與主鍵同名的索引。

mysql> create table Const (id int primary key,name varchar(20) not null);     #id上創建主鍵，name非空約束
Query OK, 0 rows affected (0.04 sec)
 
mysql> alter table Const drop primary key;  #刪除主鍵
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> alter table Const add primary key(id);   #增加主鍵。
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

7.3UNIQUE，唯一鍵，用于指定列表上某一列或者多個列的組合取值必須是唯一的。不能有重復數據。

創建方式和創建主鍵類似，只需指定鍵類型。

7.4 FOREIGN KEY。外鍵指定一個表中的數據和另外一個表中的數據之間的關系，在表上創建外鍵之前必須先在另外一個表上創建主鍵，擁有主鍵的表稱為主鍵表。擁有外鍵的表稱為外鍵表。在外鍵列上的數據只能是主鍵列上已經存在的數據或者是空值，如果再外鍵列上插入或更新的數據在主鍵列上不存在，則無法操作。

如果要刪除(ON DELETE)或者更新(ON UPDATE)主鍵列上的數據的同時，可以通過以下動作影響外鍵列上的數據。

RESTRICT ： 禁止刪除主鍵列上面的數據

NO ACTION：在檢查約束的同時，檢查約束的同時，如果還存在任何音樂行，則顯示錯誤信息，如果不聲明任何內容，那么他就是默認的行為。

CASCADE：刪除主鍵上的數據的同時，外鍵列上的數據也會自動刪除

SET NULL：刪除主鍵的數據的時候，外鍵的數據設置為空

SET DEFAULT：刪除主鍵列上數據的同時，外鍵列上的數據設置為默認值。

mysql> create table table1 (id int(2) primary key,name char(20));  
Query OK, 0 rows affected (0.02 sec)
 
mysql> create table table2(id int(2),sales int);
Query OK, 0 rows affected (0.08 sec)
 
mysql> alter table table2 add constraint fk_table2_id foreign key(id) references table1(id);  #創建外鍵。
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> alter table table2 drop foreign key fk_table2_id;  #刪除外鍵
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> alter table table2 drop column id;   #刪除id列。
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> alter table table2 add column id int(2);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> alter table table2 add constraint fk_table2_id foreign key(id) references table1(id) on delete CASCADE;  #刪除外鍵列上數據的同時，也會刪除主鍵上的數據。
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

到這里算是結束mysql的基礎管理了。

8、總結

1、這篇文章主要是面向剛學習mysql的新手，所以才舉了這么多的實例，而這些實例比較占用篇幅，所以寫了這么多。

2、最好的學習方式還是查閱官方文檔，這里也只是我最近的學習總結，方便后來查閱使用。盡量布局好看一些。

3、文章還有很多不足的地方，但愿不影響閱讀

參考：

linux應用大全–服務器架設

http://www.cnblogs.com/zbseoag/archive/2013/03/19/2970004.html   mysql數據類型

http://www.bkjia.com/Mysql/995562.html   詳細解讀mysql權限