程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> 全面掌握Mysql知識點及select支持的運算操作

全面掌握Mysql知識點及select支持的運算操作

編輯：MySQL綜合教程

全面掌握Mysql知識點及select支持的運算操作

花了2天時間整理了一下程序員應掌握的Mysql中的操作(包括select支持的運算操作)及相關概念和知識點，希望這篇博客對大家有所幫助。

內容提綱

1、登錄mysql
2、查看mysql信息
3、查看所有數據庫
4、打開指定數據庫
5、李處指定數據庫中的所有表
6、查看指定表的結構
7、查看建表語句
8、建庫建表
9、修改指定表結構
10、查看數據庫的編碼方式
11、DML語句(CRUD)操作
12、注釋方式
13、表數據的導入導出
14、數據庫的導入導出
15、視圖View
16、索引index
17、存儲過程Procedure
18、事務Transaction
19、Mysql用戶創建、授權及刪除
20、select支持的運算操作
21、select支持的邏輯運算操作
附：忘記密碼及修改密碼

1）登錄Mysql(本地、遠程)服務器

登錄本地(假設用戶名：root; 密碼：123456)：mysql -u root -p 123456
登錄遠程(假設服務器ip：192.168.0.111; 用戶名：admin; 密碼：admin)：mysql -h 192.168.0.111 -u admin -p admin

2）查看mysql信息：status

mysql> status
--------------
mysql Ver 14.14 Distrib 5.6.10, for Win32 (x86)

Connection id: 8
Current database:
Current user: root@localhost
SSL: Not in use
Using delimiter: ;
Server version: 5.6.10 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: 17 min 5 sec

3）列出所有數據庫（這裡是以root身份查看的）

4）打開指定數據庫：use db_name;

mysql> use product;
Database changed

5）列出product數據庫中的所有表：show tables;

mysql> show tables;
+-------------------+
| Tables_in_product |
+-------------------+
| test |
+-------------------+

6）查看指定表的結構：desc table_name;

7）查看建表語句DDL： show create table table_name;

| test | CREATE TABLE `test` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`type_name` varchar(10) DEFAULT NULL,
`color_id` varchar(10) DEFAULT NULL,
PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

8）實戰：建庫、建表

#若存在mydb數據庫則刪除
drop database if exists mydb;
#建庫mydb
create database mydb default charset=utf8;
#打開數據庫
use mydb;
#若存在mytest表則刪除
drop table if exists mytest;
#建表
create table mytest (
Id int(11) NOT NULL AUTO_INCREMENT,
type_name varchar(10) DEFAULT NULL,
color_name varchar(10) DEFAULT NULL,
PRIMARY KEY (Id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
#外鍵約束(級聯刪除)：constraint 約束名 foreign key 該表的字段 references dept(Id) on delete cascade on update cascade;
#查看mydb裡面有哪些表
show tables;
#查看mytest的表結構
desc mytest;
#查看mytest的建表語句
show create table mytest;
#刪除表：drop table mytest;
#刪除指定數據庫下的所有表
mysql> use information_schema;
mysql> select concat('drop table if exists ',table_name, ';' ) from tables where table_schema='db_name';
#刪除庫：drop database mydb;

9）修改mytest表結構

#創建臨時表：臨時表是指創建後只在當前數據庫內有效，斷開與數據庫的連接後表就自動刪除
create temporary table tb_temp(
id integer,
name varchar(100),
primary key(id)
);

10）查看數據庫的編碼方式

11）DML語句（select、insert、update、delete），簡稱CRUD

#插入
mysql> insert into test01(type_name,description) values('綜藝','最近更新');

#更新
mysql> update test01 set type_name='電影' where type_name='綜藝';

#查詢(由於select支持的查詢語句非常廣，所以後面會單獨拿出一篇來寫)
mysql> select * from test01 where Id=1;
#查詢test01表中有幾行記錄
mysql> select count(*) from test01;
#注意：count(*)和count(1)時統計包括null的, 而count(列名)不包含null值，且經試驗得出count(1)的速度最快(Oracle中也是如此)。

#刪除(單刪、全刪)
mysql> delete from test01 where Id=1 and type_name='電影';
mysql> delete from test01;

#清空數據：Id從1開始
mysql> truncate table user;/*清除user表中所有數據*/

12）注釋方式：#、--、/**/

13）表中數據的導入、導出

#查看表test01的數據
mysql> select * from test01;
+----+-----------+-------------+
| Id | type_name | description |
+----+-----------+-------------+
| 1 | 綜藝 | 最近更新 |
| 2 | 電影 | 精彩預告 |
+----+-----------+-------------+
#導出：將表test01中的記錄導出到f盤的test.txt文件中
mysql> select * from test01 into outfile 'f:\\test.txt';
#導出數據後，清空表test01中的數據
mysql> truncate table test01;
#可以看到，test01表中當前無數據
mysql> select * from test01;
Empty set (0.00 sec)

#導入：將f盤的test.txt文件中備份的數據導入到表test01中
mysql> load data infile 'f:\\test.txt' into table test01;
#可以看到，數據已被導入到表test01中
mysql> select * from test01;
+----+-----------+-------------+
| Id | type_name | description |
+----+-----------+-------------+
| 1 | 綜藝 | 最近更新 |
| 2 | 電影 | 精彩預告 |
+----+-----------+-------------+

14）數據庫的導入、導出

#導出數據庫：-d導出表結構，-t導出數據，不加是導出整個數據庫
mysql> mysqldump -uroot -p [-d|-t] dbname [tab_name]>e:\mybk.sql

#導入數據庫：
mysql> mysql -uroot -p new_dbname

15）視圖View

視圖的工作機制: 當調用視圖的時候，才會執行視圖中的sql，進行取數據操作。視圖的內容並沒有存儲，而是在視圖被引用的時候才派生出數據。這樣不會占用空間，由於是即時引用，視圖的內容總是與真實表的內容是一致的。從視圖的工作機制可以看出：視圖是一種虛擬的表，它並沒有保存內容，只是引用數據。一般在要經常需要某個查詢結果做子查詢的時候會使用到視圖，注意運行創建/刪除視圖的語句需要用戶具有創建/刪除視圖的權限。創建視圖的語法：create view 視圖名 as select 查詢語句; 實例說明如下：

mysql> select * from goods;
+----------+------------+------------+--------+
| goods_id | goods_name | shop_price | cat_id |
+----------+------------+------------+--------+
| 1 | 蘋果 | 8.80 | 2 |
| 2 | 西瓜 | 2.98 | 2 |
| 3 | 香蕉 | 6.80 | 2 |
| 4 | 白菜 | 1.80 | 1 |
| 5 | 韭菜 | 2.80 | 1 |
| 6 | 茄子 | 1.80 | 1 |
| 7 | 哈密瓜 | 3.80 | 2 |
+----------+------------+------------+--------+

如果要頻繁查詢某個種類下面的商品平均價格：mysql> select cat_id, avg(shop_price) from goods group by cat_id;
那麼我們可以創建針對這種平均價格的視圖：mysql> create view avgPrice as select cat_id, avg(shop_price) from goods group by cat_id;
然後我們就可以通過普通的查詢語句來簡化查詢了：
mysql> select * from avgPrice;
+--------+-----------------+
| cat_id | avg(shop_price) |
+--------+-----------------+
| 1 | 2.133333 |
| 2 | 5.595000 |
+--------+-----------------+

可以看到，通過視圖我們可以簡化查詢操作，視圖還有一個好處就是可以進行權限控制。比如：對於goods表，我們不想讓別人看到我們的銷售價格，這時候我們就可以把查看goods表的權限封閉，創建一張視圖：mysql> create view baseGoods as select goods_id,goods_name from goods;
mysql> select * from baseGoods;
+----------+------------+
| goods_id | goods_name |
+----------+------------+
| 1 | 蘋果 |
| 2 | 西瓜 |
| 3 | 香蕉 |
| 4 | 白菜 |
| 5 | 韭菜 |
| 6 | 茄子 |
| 7 | 哈密瓜 |
+----------+------------+
這樣就可以不出現銷售價格列了。當然我們也可以修改視圖，修改視圖也比較簡單：alter view 視圖名 as select語句，比如對於avgPrice視圖，我們還想看到商品名稱：

mysql> alter view avgPrice as select cat_id,goods_name, avg(shop_price) from goods group by cat_id;
mysql> select * from avgPrice;
+--------+------------+-----------------+
| cat_id | goods_name | avg(shop_price) |
+--------+------------+-----------------+
| 1 | 白菜 | 2.133333 |
| 2 | 蘋果 | 5.595000 |
+--------+------------+-----------------+

刪除視圖：mysql> drop view avgPrice;

16）索引index

索引分單列索引和組合索引。單列索引，即一個索引只包含單個列，一個表可以有多個單列索引，但這不是組合索引。組合索引是一個索引包含多個列。
對字段建立索引可以大大提高查詢速度。假如我們創建了一個mytable表：
mysql> desc mytable;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| ID | int(11) | NO | | NULL | |
| username | varchar(16) | NO | | NULL | |
+----------+-------------+------+-----+---------+-------+
MySQL索引類型包括：
（1）普通索引，這是最基本的索引，它沒有任何限制。它有以下幾種創建方式：
-- 創建索引
CREATE INDEX indexName ON mytable(username(10)); -- 單列索引
-- CREATE INDEX indexName ON mytable(username(10),Id(11)); -- 組合索引
-- indexName為索引名，mytable表名，username和Id為列名, 如果是CHAR，VARCHAR類型，前綴長度可以小於字段實際長度；如果是BLOB和TEXT類型，必須指定前綴長度。

#實例演示：創建普通索引
mysql> create index indexName on mytable(username(10));
mysql> desc mytable;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| ID | int(11) | NO | | NULL | |
| username | varchar(16) | NO | MUL | NULL | |
+----------+-------------+------+-----+---------+-------+
#修改索引
mysql> alter table mytable add index indexName (username(10));
mysql> desc mytable;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| ID | int(11) | NO | | NULL | |
| username | varchar(16) | NO | MUL | NULL | |
+----------+-------------+------+-----+---------+-------+
#刪除索引
mysql> drop index indexName on mytable;
mysql> desc mytable;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| ID | int(11) | NO | | NULL | |
| username | varchar(16) | NO | | NULL | |
+----------+-------------+------+-----+---------+-------+

（2）唯一索引：它與前面的普通索引類似，不同的就是：索引列的值必須唯一，但允許有空值。如果是組合索引，則列值的組合必須唯一。它有以下幾種創建方式（僅僅在創建普通索引時關鍵字 INDEX 前加 UNIQUE

#創建唯一索引
mysql> create unique index indexName on mytable(username(10));
#修改唯一索引
mysql> alter table mytable add unique index indexName (username(10));
#刪除唯一索引
mysql> drop index indexName on mytable;

（3）主鍵索引：它是一種特殊的唯一索引，不允許有空值。在建表的時候同時創建的主鍵即為主鍵索引。主鍵索引無需命名，一個表只能有一個主鍵。主鍵索引同時可是唯一索引或者全文索引，但唯一索引或全文索引不能共存在同一索引。
#通過修改表結構來創建主鍵索引
mysql> alter table mytable add primary key(ID);
mysql> desc mytable;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| ID | int(11) | NO | PRI | NULL | |
| username | varchar(16) | NO | | NULL | |
+----------+-------------+------+-----+---------+-------+

（4）全文索引：InnoDB存儲引擎不支持全文索引
#創建全文索引
mysql> create fulltext index indexName on mytable(username(10));
#修改表結構來創建索引
mysql> alter table mytable add fulltext index indexName (username(10));
#刪除全文索引
mysql> drop index indexName on mytable;

17）存儲過程Procedure

mysql> call pro_test1;--執行存儲過程

還可以在存儲過程中使用自定義(用戶)變量，簡單實例演示：
mysql> create procedure pro_bigworld() select concat(@greeting,' world');
mysql> set @greeting='Hi';
mysql> call pro_bigworld;
+----------------------------+
| concat(@greeting,' world') |
+----------------------------+
| Hi world |
+----------------------------+

再看一個if-then-else語句：
delimiter ;
drop procedure if exists pro_test2;
delimiter //
create procedure pro_test2(IN para int)
begin
declare var1 int;
set var1=para+1;
if var1=0 then
insert into test1(name,value) values('var1name','var1value'+0);
end if;
if para=0 then
update test1 set value=value+'0';
else
update test1 set value=value+'1';
end if;
end;
//
mysql> call pro_test2(1);
mysql> //
mysql> select * from test1;

關於Mysql存儲過程中的控制語句比較多，這裡限於篇幅就不一一列舉了。

18）事務Transaction

事務幾個基本特性: ACID
原子性(atomicity)：事務在執行時，要做到“要麼不做，要麼全做”，也就是說不允許事務部分得執行。即使因為故障而使事務不能完成，在rollback時也要消除對數據庫得影響！
一致性(consistency)：在事務處理執行前後，數據庫是一致的(兩個賬戶要麼都變，或者都不變)。
隔離性(isolcation)：一個事務處理對另一個事務處理沒有影響。
持續性(durability)：事務處理的效果能夠被永久保存下來。
MYSQL的事務處理主要有兩種方法:
1、用begin,rollback,commit來實現
begin 開始一個事務
rollback 事務回滾
commit 事務確認
2、直接用set來改變mysql的自動提交模式
MYSQL默認是自動提交的，也就是你提交一個QUERY，它就直接執行！我們可以通過
select @@autocommit;//查看當前事務是否為自動提交(1為自動提交)
set autocommit=0 禁止自動提交
set autocommit=1 開啟自動提交
來實現事務的處理。

【注意】當你用 set autocommit=0 的時候，你以後所有的SQL都將做為事務處理，直到你用commit確認或rollback結束，注意當你結束這個事務的同時也開啟了個新的事務！按第一種方法只將當前的作為一個事務！

19）MySQL用戶創建，授權以及刪除

創建用戶yy,密碼123： create user yy identified by '123';
授權：grant all privileges on dbname.* to yy@localhost identified by '123';
授予用戶yy查詢數據庫dbname中所有表的權限：grant select on dbname.* to yy;
撤銷用戶yy查詢數據庫dbname中所有表的權限：revoke select on dbname.* from yy;
刪除用戶：drop user yy;

20）select支持的運算操作

select case 1 when 1 then 'one' when 2 then 'two' end;/*選擇事件1：推薦使用單引號表示字符串*/
select if(1<2,'yes','no');/*判斷語句*/
select char_length('qwert');/*求字符長度*/
select concat('my','sql');/*連接字符串*/
select locate('bar','foobarhat');/*查找母串'bar'所在位置,若未找到則為0*/
select substring('yichun university',5,6);/*提取字串*/
select trim(' bar ');;/*去掉前後空格*/
select repeat('mysql',3);/*mysql字符串重復3次*/
select reverse('abcd');/*反轉*/
select round(2.5);/*四捨五入到3*/
select rand()*10;/*返回0~10之間的數*/
select hex('cat');/*查看ASCII碼*/
select true,false;/*輸出1,0*/
select now();/*顯示當前日期時間*/
select curtime();/*顯示當前時間*/
select current_time();/*顯示當前時間*/
select curdate();/*顯示當前日期*/
select current_date();/*顯示當前日期*/
select current_timestamp();/*顯示當前時間戳*/
select database();/*顯示當前數據庫*/
select user();/*顯示當前用戶*/
select version();/*顯示當前mysql版本*/
select database();/*顯示當前打開的數據庫*/
select left(type_name,2) from test;/*查看test表中名為type_name列的所有值的前兩個字母*/
select right(type_name,2) from test;/*查看test表中名為type_name列的所有值的後兩個字母*/

21）select支持的邏輯運算操作

select 2>1&&3>2;/*邏輯與1*/
select 2>1 and 3>2;/*邏輯與1*/
select 1>2 || 3>2;/*邏輯或1*/
select 1>2 or 3>2;/*邏輯或1*/
select !(1>2);/*邏輯非1*/
select not(1>2);/*邏輯非1*/
select 15 ^ 2;/*邏輯異或：15的二進制1111，2的二進制0010，根據"同0異1"原則，結果為1101十進制13*/
select 15 xor 2;/*邏輯異或：13*/

附1：忘記密碼

1.關閉正在運行的MySQL。
2.打開DOS窗口，轉到mysql\bin目錄。
3.輸入mysqld --skip-grant-tables回車。如果沒有出現提示信息，那就對了。
4.再開一個DOS窗口(因為剛才那個DOS窗口已經不能動了)，轉到mysql\bin目錄。
5.輸入mysql回車，如果成功，將出現MySQL提示符 >,接下來就可以修改密碼了。

附2：修改密碼
方法一:
(適用於管理員或者有全局權限的用戶重設其它用戶的密碼)
進入命令行模式
mysql> use mysql;
mysql> UPDATE user SET password="newPassword" WHERE user='name';
mysql> FLUSH PRIVILEGES; //刷新權限
mysql> QUIT

方法二:
(應用同上,只是方法不同)
mysql> SET PASSWORD FOR name=PASSWORD('new password');
mysql> QUIT