當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

Mysql操作语句

發布時間：2025/3/15 数据库 18 豆豆

生活随笔收集整理的這篇文章主要介紹了 Mysql操作语句小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

MySQL中定義數據字段的類型對你數據庫的優化是非常重要的。

MySQL支持多種類型，大致可以分為三類：數值、日期/時間和字符串(字符)類型。

數值類型

MySQL支持所有標準SQL數值數據類型。

這些類型包括嚴格數值數據類型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似數值數據類型(FLOAT、REAL和DOUBLE PRECISION)。

關鍵字INT是INTEGER的同義詞，關鍵字DEC是DECIMAL的同義詞。

BIT數據類型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表。

作為SQL標準的擴展，MySQL也支持整數類型TINYINT、MEDIUMINT和BIGINT。下面的表顯示了需要的每個整數類型的存儲和范圍。

類型大小范圍（有符號）范圍（無符號）用途

TINYINT	1 字節	(-128，127)	(0，255)	小整數值
SMALLINT	2 字節	(-32 768，32 767)	(0，65 535)	大整數值
MEDIUMINT	3 字節	(-8 388 608，8 388 607)	(0，16 777 215)	大整數值
INT或INTEGER	4 字節	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整數值
BIGINT	8 字節	(-9 233 372 036 854 775 808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	極大整數值
FLOAT	4 字節	(-3.402 823 466 E+38，1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	單精度浮點數值
DOUBLE	8 字節	(1.797 693 134 862 315 7 E+308，2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	雙精度浮點數值
DECIMAL	對DECIMAL(M,D) ，如果M>D，為M+2否則為D+2	依賴于M和D的值	依賴于M和D的值	小數值

日期和時間類型

表示時間值的日期和時間類型為DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每個時間類型有一個有效值范圍和一個"零"值，當指定不合法的MySQL不能表示的值時使用"零"值。

TIMESTAMP類型有專有的自動更新特性，將在后面描述。

類型大小
(字節)范圍格式用途

DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	'-838:59:59'/'838:59:59'	HH:MM:SS	時間值或持續時間
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和時間值
TIMESTAMP	4	1970-01-01 00:00:00/2037 年某時	YYYYMMDD HHMMSS	混合日期和時間值，時間戳

字符串類型

字符串類型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。該節描述了這些類型如何工作以及如何在查詢中使用這些類型。

類型大小用途

CHAR	0-255字節	定長字符串
VARCHAR	0-65535 字節	變長字符串
TINYBLOB	0-255字節	不超過 255 個字符的二進制字符串
TINYTEXT	0-255字節	短文本字符串
BLOB	0-65 535字節	二進制形式的長文本數據
TEXT	0-65 535字節	長文本數據
MEDIUMBLOB	0-16 777 215字節	二進制形式的中等長度文本數據
MEDIUMTEXT	0-16 777 215字節	中等長度文本數據
LONGBLOB	0-4 294 967 295字節	二進制形式的極大文本數據
LONGTEXT	0-4 294 967 295字節	極大文本數據

CHAR和VARCHAR類型類似，但它們保存和檢索的方式不同。它們的最大長度和是否尾部空格被保留等方面也不同。在存儲或檢索過程中不進行大小寫轉換。

BINARY和VARBINARY類類似于CHAR和VARCHAR，不同的是它們包含二進制字符串而不要非二進制字符串。也就是說，它們包含字節字符串而不是字符字符串。這說明它們沒有字符集，并且排序和比較基于列值字節的數值值。

BLOB是一個二進制大對象，可以容納可變數量的數據。有4種BLOB類型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它們只是可容納值的最大長度不同。

有4種TEXT類型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。這些對應4種BLOB類型，有相同的最大長度和存儲需求。

Mysql常用操作命令

1.顯示數據庫

show database;

默認數據庫：
　　mysql - 用戶權限相關數據
　　test - 用于用戶測試數據
　　information_schema - MySQL本身架構相關數據

2.創建數據庫

create database test charset utf8 collate utf8_general_ci?; ?#標明utf8格式,utf8_general_ci 是不區分大小寫

#這里?

? utf8_genera_ci不區分大小寫，ci為case insensitive的縮寫，即大小寫不敏感。

? utf8_general_cs區分大小寫，cs為case sensitive的縮寫，即大小寫敏感。

3.使用數據庫

use database;

4.用戶管理

1 創建用戶 2 create user '用戶名'@'IP地址' identified by '密碼'; 3 刪除用戶 4 drop user '用戶名'@'IP地址'; 5 修改用戶 6 rename user '用戶名'@'IP地址'; to '新用戶名'@'IP地址';; 7 修改密碼 8 set password for '用戶名'@'IP地址' = Password('新密碼') 9 10 PS：用戶權限相關數據保存在mysql數據庫的user表中，所以也可以直接對其進行操作（不建議）

5.授權管理

1 show grants for '用戶'@'IP地址' -- 查看權限 2 grant 權限 on 數據庫.表 to '用戶'@'IP地址' -- 授權 3 revoke 權限 on 數據庫.表 from '用戶'@'IP地址' -- 取消權限

all privileges 除grant外的所有權限select 僅查權限select,insert 查和插入權限...usage 無訪問權限alter 使用alter tablealter routine 使用alter procedure和drop procedurecreate 使用create tablecreate routine 使用create procedurecreate temporary tables 使用create temporary tablescreate user 使用create user、drop user、rename user和revoke all privilegescreate view 使用create viewdelete 使用deletedrop 使用drop tableexecute 使用call和存儲過程file 使用select into outfile 和 load data infilegrant option 使用grant 和 revokeindex 使用indexinsert 使用insertlock tables 使用lock tableprocess 使用show full processlistselect 使用selectshow databases 使用show databasesshow view 使用show viewupdate 使用updatereload 使用flushshutdown 使用mysqladmin shutdown(關閉MySQL)super ??使用change master、kill、logs、purge、master和set global。還允許mysqladmin????調試登陸replication client 服務器位置的訪問replication slave 由復制從屬使用權限

在grant這里:

　　對于權限:

　　　　　　all privileges 除grant外的所有權限 　　

　　　　　　select 僅查權限 　　

　　　　　　select,insert 查和插入權限

　　對于數據庫及內部操作語法有:

　　　　　　A.?數據庫名.* 數據庫中的所有

　　　　　　B.?數據庫名.表指定數據庫中的某張表

　　　　　　C.?數據庫名.存儲過程指定數據庫中的存儲過程

　　　　　　D.?*.* 所有數據庫

　　對于用戶和IP:

　　　　　　A.用戶名@IP地址用戶只能在改IP下才能訪問

　　　　　　B.用戶名@192.168.1.% 用戶只能在改IP段下才能訪問(通配符%表示任意)

　　　　　　C.用戶名@% 用戶可以再任意IP下訪問(默認IP地址為%)

示例:

grant all privileges on db1.tb1 TO '用戶名'@'IP'grant select on db1.* TO '用戶名'@'IP'grant select,insert on *.* TO '用戶名'@'IP'revoke select on db1.tb1 from '用戶名'@'IP'

創建表操作:

1.創建表

1 create table 表名( 2 列名類型是否可以為空， 3 列名類型是否可以為空 4 )ENGINE=InnoDB DEFAULT CHARSET=utf8;

create table dream (st_id int not null auto_increment,name char(8) not null,age int not null,time date,primary key(st_id));

創建一個以st_id為主鍵的表　　

　　　　這里st_id設置了auto_increament 屬性后,就必須設置為主鍵primary key(st_id)

　　　　如果你想插入一條記錄,獲得唯一的一個編號,那就要用到主鍵

　　　　auto_increament:可以理解為遞增的意思，每增加一條記錄，值會自動加1

　　　　primary:是主鍵的意思

下面做了詳細的擴展:

A.是否為空 ? 非字符串

　　not null　　-不可空null　　　　-可空

B.可以設置默認值

默認值，創建列時可以指定默認值，當插入數據時如果未主動設置，則自動添加默認值create table tb1(nid int not null defalut 2,num int not null)

示例:?create table ceshi2( nid int not null default 5,num int not null);

C.自增

自增，如果為某列設置自增列，插入數據時無需設置此列，默認將自增（表中只能有一個自增列）create table tb1(nid int not null auto_increment primary key,num int null)或create table tb1(nid int not null auto_increment,num int null,index(nid))注意：1、對于自增列，必須是索引（含主鍵）。2、對于自增可以設置步長和起始值show session variables like 'auto_inc%';set session auto_increment_increment=2;set session auto_increment_offset=10;shwo global variables like 'auto_inc%';set global auto_increment_increment=2;set global auto_increment_offset=10;

示例:create table ceshi( nid int not null auto_increment,num int not null,index(nid));

D:主鍵

主鍵，一種特殊的唯一索引，不允許有空值，如果主鍵使用單個列，則它的值必須唯一，如果是多列，則其組合必須唯一。create table tb1(nid int not null auto_increment primary key,num int null)或create table tb1(nid int not null,num int not null,primary key(nid,num))

示例:create table ceshi3( nid int not null auto_increment primary key,num int null);

E:外鍵

外鍵，一個特殊的索引，只能是指定內容creat table color(nid int not null primary key,name char(16) not null)create table fruit(nid int not null primary key,smt char(32) null ,color_id int not null,constraint fk_cc foreign key (color_id) references color(nid))

示例:create table ceshi4( nid tinyint not null primary key,name char(8) not null);

D:常用操作

show create table dream; ?查看建表語句

flush privileges; ? 將數據讀取到內存中

drop table 表名; ?刪除表

delete from 表名; ?清空表

desc 表名 ? 查看表結構

select * from user \G; 格式化輸出

show grants for zcq@'%'; 查看權限

grant all privileges on *.* to 'jack'@'%' 　　給已經存在的用戶授權
grant all privileges on *.* to 'jack'@'%' identified by '123' ;　　創建用戶

E:mysql基本數據類型

bit[(M)]二進制位（101001），m表示二進制位的長度（1-64），默認m＝1tinyint[(m)] [unsigned] [zerofill]小整數，數據類型用于保存一些范圍的整數數值范圍：有符號：-128 ～ 127.無符號：0 ～ 255特別的： MySQL中無布爾值，使用tinyint(1)構造。int[(m)][unsigned][zerofill]整數，數據類型用于保存一些范圍的整數數值范圍：有符號：-2147483648 ～ 2147483647無符號：0 ～ 4294967295特別的：整數類型中的m僅用于顯示，對存儲范圍無限制。例如： int(5),當插入數據2時，select 時數據顯示為： 00002bigint[(m)][unsigned][zerofill]大整數，數據類型用于保存一些范圍的整數數值范圍：有符號：-9223372036854775808 ～ 9223372036854775807無符號：0 ～ 18446744073709551615decimal[(m[,d])] [unsigned] [zerofill]準確的小數值，m是數字總個數（負號不算），d是小數點后個數。 m最大值為65，d最大值為30。特別的：對于精確數值計算時需要用此類型decaimal能夠存儲精確值的原因在于其內部按照字符串存儲。FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]單精度浮點數（非準確小數值），m是數字總個數，d是小數點后個數。無符號：-3.402823466E+38 to -1.175494351E-38,01.175494351E-38 to 3.402823466E+38有符號：01.175494351E-38 to 3.402823466E+38**** 數值越大，越不準確 ****DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]雙精度浮點數（非準確小數值），m是數字總個數，d是小數點后個數。無符號：-1.7976931348623157E+308 to -2.2250738585072014E-30802.2250738585072014E-308 to 1.7976931348623157E+308有符號：02.2250738585072014E-308 to 1.7976931348623157E+308**** 數值越大，越不準確 ****char (m)char數據類型用于表示固定長度的字符串，可以包含最多達255個字符。其中m代表字符串的長度。PS: 即使數據小于m長度，也會占用m長度varchar(m)varchars數據類型用于變長的字符串，可以包含最多達255個字符。其中m代表該數據類型所允許保存的字符串的最大長度，只要長度小于該最大值的字符串都可以被保存在該數據類型中。注：雖然varchar使用起來較為靈活，但是從整個系統的性能角度來說，char數據類型的處理速度更快，有時甚至可以超出varchar處理速度的50%。因此，用戶在設計數據庫時應當綜合考慮各方面的因素，以求達到最佳的平衡texttext數據類型用于保存變長的大字符串，可以組多到65535 (2**16 ? 1)個字符。mediumtextA TEXT column with a maximum length of 16,777,215 (2**24 ? 1) characters.longtextA TEXT column with a maximum length of 4,294,967,295 or 4GB (2**32 ? 1) characters.enum枚舉類型，An ENUM column can have a maximum of 65,535 distinct elements. (The practical limit is less than 3000.)示例：CREATE TABLE shirts (name VARCHAR(40),size ENUM('x-small', 'small', 'medium', 'large', 'x-large'));INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),('polo shirt','small');set集合類型A SET column can have a maximum of 64 distinct members.示例：CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));INSERT INTO myset (col) VALUES ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');DATEYYYY-MM-DD（1000-01-01/9999-12-31）TIMEHH:MM:SS（'-838:59:59'/'838:59:59'）YEARYYYY（1901/2155）DATETIMEYYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59 Y）TIMESTAMPYYYYMMDD HHMMSS（1970-01-01 00:00:00/2037 年某時）數據類型

F:修改表

添加列：alter table 表名 add 列名類型刪除列：alter table 表名 drop 列名修改列：alter table 表名 modify column 列名類型; -- 類型alter table 表名 change 原列名新列名類型; -- 列名，類型添加主鍵：alter table 表名 add primary key(列名); 刪除主鍵：alter table 表名 drop primary key;alter table 表名 modify 列名 int, drop primary key;添加外鍵：alter table 從表 add constraint 外鍵名稱（形如：FK_從表_主表） foreign key 從表(外鍵字段) references 主表(主鍵字段); 刪除外鍵：alter table 表名 drop foreign key 外鍵名稱修改默認值：ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000; 刪除默認值：ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;

?示例:alter table dream change st_id student_id int; ?這種寫法，直接就把列表名稱也改變了. 以及屬性

表內容操作

1.增('insert'and'alter')

insert into 表 (列名,列名...) values (值,值,值...) insert into 表 (列名,列名...) values (值,值,值...),(值,值,值...) insert into 表 (列名,列名...) select (列名,列名...) from 表

insert into dream (name,age,time) values ('小明',23,'2016-12-27');

alter table dream add course char(8) not null; ? ?新增一列

insert into dream (name,age,time,course) values ('小王',24,'2016-12-30','Hadoop'); ? ? 注意:新增這里如果指定了char，那必須就要加符號,

alter table dream add numbers varchar(8) not null default 100; ? 新增一列，并且指定了不能為空，填補默認值為100,

2.刪

delete from 表 delete from 表 where id＝1 and name＝'alex'

3.改

update 表 set name ＝ 'alex' where id>1

?修改表名:alter table dream rename to dreams ? alter方法修改表名

修改表數據:update 表名 set `字段名`='修改值' where 條件;

切記:如果更新單張表，單行列,必須指定列，不然會把整個表都更新了?

　　　這里set name 可以隨意指定列表名

4.查

select * from 表 select * from 表 where id > 1 select nid,name,gender as gg from 表 where id > 1

查詢語句這里又幾種模式:

　　>查詢語句中你可以使用一個或者多個表，表之間使用逗號(,)分割，并使用WHERE語句來設定查詢條件

　　>select命令可以讀取一條或多條命令

　　>你可以使用星號（*）來代替其他字段，SELECT語句會返回表的所有字段數據

　　>你可以使用 WHERE 語句來包含任何條件

　　>你可以通過OFFSET指定SELECT語句開始查詢的數據偏移量。默認情況下偏移量為0。

　　>你可以使用 LIMIT 屬性來設定返回的記錄數

　　>使用主鍵查詢來作為where字句的條件查詢是非常快速的.

　　a.條件查詢　　　

select * from 表 where id > 1 and name != 'alex' and num = 12;select * from 表 where id between 5 and 16;select * from 表 where id in (11,22,33)select * from 表 where id not in (11,22,33)select * from 表 where id in (select nid from 表)

　　b.通配符

select * from 表 where name like 'zcq%' - zcq開頭的所有（多個字符串）select * from 表 where name like 'zc_' - zc開頭的所有（一個字符）

　　C.限制

select * from 表 limit 5; - 前5行select * from 表 limit 4,5; - 從第4行開始的5行select * from 表 limit 5 offset 4 - 從第4行開始的

　　D.排序

select * from 表 order by 列 asc - 根據 “列” 從小到大排列select * from 表 order by 列 desc - 根據 “列” 從大到小排列select * from 表 order by 列1 desc,列2 asc - 根據 “列1” 從大到小排列，如果相同則按列2從小到大排序

　　E.分組

select num from 表 group by numselect num,nid from 表 group by num,nidselect num,nid from 表 where nid > 10 group by num,nid order nid descselect num,nid,count(*),sum(score),max(score),min(score) from 表 group by num,nidselect num from 表 group by num having max(id) > 10特別的：group by 必須在where之后，order by之前

　　示例:

　　select name,count(*) from dream group by name; ? ? ?

　　　　　 ? 分析:---把所有查詢到的name信息，以及個數都列入一個* 列表上. ?*可以更改

　　select name, sum(age) as singin_out from dream group by name with rollup;?

　　　　　　分析:--sum(age) ?是收集的信息名稱?

　　　　　　　　 ---查詢到nam信息中的age信息, 這里的group by中的with rollup是一個屬于匯總的用法(默認為null名稱)，如果不加這個方法只會單列出查詢到的信息,

　　還可以修改這個匯總后的名稱:select coalesce(name,'翟超群'),sum(age) as age_all from dream group by name with rollup; ? ?

　　F.連表

無對應關系則不顯示select A.num, A.name, B.namefrom A,BWhere A.nid = B.nid無對應關系則不顯示select A.num, A.name, B.namefrom A inner join Bon A.nid = B.nidA表所有顯示，如果B中無對應關系，則值為nullselect A.num, A.name, B.namefrom A left join Bon A.nid = B.nidB表所有顯示，如果B中無對應關系，則值為nullselect A.num, A.name, B.namefrom A right join Bon A.nid = B.nid

　　G,組合

組合，自動處理重合select nicknamefrom Aunionselect namefrom B組合，不處理重合select nicknamefrom Aunion allselect namefrom B

MySQL NULL 值處理　　

我們已經知道MySQL使用 SQL SELECT 命令及 WHERE 子句來讀取數據表中的數據,但是當提供的查詢條件字段為 NULL 時，該命令可能就無法正常工作。
為了處理這種情況，MySQL提供了三大運算符:
IS NULL: 當列的值是NULL,此運算符返回true。
IS NOT NULL: 當列的值不為NULL, 運算符返回true。
<=>: 比較操作符（不同于=運算符），當比較的的兩個值為NULL時返回true。
關于 NULL 的條件比較運算是比較特殊的。你不能使用 = NULL 或 != NULL 在列中查找 NULL 值。
在MySQL中，NULL值與任何其它值的比較（即使是NULL）永遠返回false，即 NULL = NULL 返回false 。
MySQL中處理NULL使用IS NULL和IS NOT NULL運算符。

?事務

MySQL 事務主要用于處理操作量大，復雜度高的數據。比如說，在人員管理系統中，你刪除一個人員，你即需要刪除人員的基本資料，也要刪除和該人員相關的信息，如信箱，文章等等，這樣，這些數據庫操作語句就構成一個事務！

在MySQL中只有使用了Innodb數據庫引擎的數據庫或表才支持事務
事務處理可以用來維護數據庫的完整性，保證成批的SQL語句要么全部執行，要么全部不執行
事務用來管理insert,update,delete語句

一般來說，事務是必須滿足4個條件（ACID）： Atomicity（原子性）、Consistency（穩定性）、Isolation（隔離性）、Durability（可靠性）

1、事務的原子性：一組事務，要么成功；要么撤回。
2、穩定性?：有非法數據（外鍵約束之類），事務撤回。
3、隔離性：事務獨立運行。一個事務處理后的結果，影響了其他事務，那么其他事務會撤回。事務的100%隔離，需要犧牲速度。
4、可靠性：軟、硬件崩潰后，InnoDB數據表驅動會利用日志文件重構修改。可靠性和高速度不可兼得， innodb_flush_log_at_trx_commit選項決定什么時候吧事務保存到日志里

在Mysql控制臺使用事務來操作

mysql> begin；＃開始一個事務mysql> insert into a (a) values(555);mysql>rollback; 回滾，這樣數據是不會寫入的

索引

MySQL索引的建立對于MySQL的高效運行是很重要的，索引可以大大提高MySQL的檢索速度。

打個比方，如果合理的設計且使用索引的MySQL是一輛蘭博基尼的話，那么沒有設計和使用索引的MySQL就是一個人力三輪車。

索引分單列索引和組合索引。單列索引，即一個索引只包含單個列，一個表可以有多個單列索引，但這不是組合索引。組合索引，即一個索包含多個列。

創建索引時，你需要確保該索引是應用在 SQL 查詢語句的條件(一般作為 WHERE 子句的條件)。?

實際上，索引也是一張表，該表保存了主鍵與索引字段，并指向實體表的記錄。

上面都在說使用索引的好處，但過多的使用索引將會造成濫用。因此索引也會有它的缺點：雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對表進行INSERT、UPDATE和DELETE。因為更新表時，MySQL不僅要保存數據，還要保存一下索引文件。建立索引會占用磁盤空間的索引文件

JOIN 按照功能大致分為如下三類：

INNER JOIN（內連接,或等值連接）：取得兩個表中存在連接匹配關系的記錄。

LEFT JOIN（左連接）：取得左表（table1）完全記錄，即是右表（table2）并無對應匹配記錄。

RIGHT JOIN（右連接）：與 LEFT JOIN 相反，取得右表（table2）完全記錄，即是左表（table1）并無匹配對應記錄。

注意：mysql不支持Full join,不過可以通過UNION 關鍵字來合并 LEFT JOIN 與 RIGHT JOIN來模擬FULL join.

###

內外聯結的區別是內聯結將去除所有不符合條件的記錄，而外聯結則保留其中部分。外左聯結與外右聯結的區別在于如果用A左聯結B則A中所有記錄都會保留在結果中，此時B中只有符合聯結條件的記錄，而右聯結相反，這樣也就不會混淆

為什么會存在差異，這和on與where查詢順序有關。

我們知道標準查詢關鍵字執行順序為 from->where->group by->having->order by[?記得不是很清楚呢]

left join 是在from范圍類所以先on條件篩選表，然后兩表再做left join。

而對于where來說在left join結果再次篩選。

1.Inner join (內連接)

內連接,也叫等值連接,inner join 產生同時符合A和B的一組數據

mysql> select * from student inner join class on student.sid = class.cid; +-----+--------+--------+----------+----------+-----+--------------+ | sid | sname | gender | class_id | teachers | cid | caption | +-----+--------+--------+----------+----------+-----+--------------+ | 1 | 鋼蛋 | 女 | 1 | 波多 | 1 | 三年二班 | | 2 | 鐵錘 | 女 | 1 | 飯島 | 2 | 一年三班 | | 3 | 山炮 | 男 | 2 | 0 | 3 | 三年一班 | +-----+--------+--------+----------+----------+-----+--------------+

2.Left join

mysql> select student.sname,score.number from student left join score on student.sid = score.sid; +--------+--------+ | sname | number | +--------+--------+ | 鋼蛋 | 60 | | 鐵錘 | 59 | | 山炮 | 100 | +--------+--------+ 3 rows in set (0.00 sec)

?注釋:個人理解left join這里的用法: ?就是 select來查詢你需要的值這里寫入的是(student.sname,score.number), 而后面from到左邊的表(就是student.sname所在的表),接來下就是left join (以左邊為主,來連接另外一個表即:left join score) ?,on條件篩選，選擇一個共同的值.

?3.Right join

同left join方法一樣

4.Cross join (笛卡爾交集)

cross join：交叉連接，得到的結果是兩個表的乘積，即笛卡爾積

笛卡爾（Descartes）乘積又叫直積。假設集合A={a,b}，集合B={0,1,2}，則兩個集合的笛卡爾積為{(a,0),(a,1),(a,2),(b,0),(b,1), (b,2)}。可以擴展到多個集合的情況

可以聯合使用

?#實例

一、概念：數據： data數據庫： DB數據庫管理系統：DBMS數據庫系統：DBSMySQL：數據庫 mysql：客戶端命令（用來連接服務或發送sql指令）SQL：結構化查詢語言，其中MySQL支持這個。SQL語言分為4個部分：DDL、DML、DQL、DCL二、連接數據庫：mysql -h 主機名 -u 用戶名 -p密碼庫名C:\>mysql --采用匿名賬號和密碼登陸本機服務C:\>mysql -h localhost -u root -proot --采用root賬號和root密碼登陸本機服務C:\>mysql -u root -p --推薦方式默認登陸本機Enter password: ****C:\>mysql -u root -p lnmp61 --直接進入lnmp61數據庫的方式登陸三、授權：格式：grant 允許操作 on 庫名.表名 to 賬號@來源 identified by '密碼';--實例：創建zhangsan賬號，密碼123，授權lnmp庫下所有表的增/刪/改/查數據,來源地不限mysql> grant select,insert,update,delete on lnmp61.* to zhangsan@'%' identified by '123';Query OK, 0 rows affected (0.00 sec)四、SQL的基本操作mysql>show databases; --查看當前用戶下的所有數據庫mysql>create database 數據庫名; --創建數據庫mysql> use test; --選擇進入test數據庫mysql> drop database 數據庫名; --刪除一個數據庫 mysql> show tables; --查看當前庫下的所有表格mysql> select database(); --查看當前所在的數據庫mysql> desc tb1; --查看tb1的表結構。mysql> create table demo( --創建demo表格-> name varchar(16) not null,-> age int,-> sex enum('w','m') not null default 'm');Query OK, 0 rows affected (0.05 sec)mysql> desc demo; --查看表結構+-------+---------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+---------------+------+-----+---------+-------+| name | varchar(16) | NO | | NULL | || age | int(11) | YES | | NULL | || sex | enum('w','m') | NO | | m | |+-------+---------------+------+-----+---------+-------+3 rows in set (0.00 sec)mysql>drop table if exists mytab; -- 嘗試刪除mytab表格--添加一條數據mysql> insert into demo(name,age,sex) values('zhangsan',20,'w');Query OK, 1 row affected (0.00 sec)mysql> insert into demo values('lisi',22,'m'); --不指定字段名來添加數據Query OK, 1 row affected (0.00 sec)mysql> insert into demo(name,age) values('wangwu',23); --指定部分字段名來添加數據Query OK, 1 row affected (0.00 sec)--批量添加數據mysql> insert into demo(name,age,sex) values('aaa',21,'w'),("bbb",22,'m');Query OK, 2 rows affected (0.00 sec)Records: 2 Duplicates: 0 Warnings: 0mysql> select * from demo; --查詢數據mysql> update demo set age=24 where name='aaa'; --修改Query OK, 1 row affected (0.02 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> delete from demo where name='bbb'; --刪除Query OK, 1 row affected (0.00 sec)mysql>\h -- 快捷幫助mysql>\c -- 取消命令輸入mysql>\s -- 查看當前數據庫的狀態mysql>\q -- 退出mysql命令行五、 MySQL數據庫的數據類型：MySQL的數據類型分為四大類：數值類型、字串類型、日期類型、NULL。5.1 數值類型：*tinyint(1字節)smallint(2字節)mediumint(3字節)*int(4字節) bigint(8字節)*float(4字節) float(6,2)*double(8字節) decimal(自定義)字串形數值5.2 字串類型普通字串*char 定長字串 char(8) *varchar 可變字串 varchar(8)二進制類型tinyblobblobmediumbloblongblob文本類型tinytext*text 常用于<textarea></textarea>mediumtextlongtext*enum枚舉set集合5.3 時間和日期類型：date 年月日time 時分秒datatime 年月日時分秒timestamp 時間戳year 年5.4 NULL值NULL意味著“沒有值”或“未知值”可以測試某個值是否為NULL不能對NULL值進行算術計算對NULL值進行算術運算，其結果還是NULL0或NULL都意味著假，其余值都意味著真MySQL的運算符：算術運算符：+ - * / % 比較運算符：= > < >= <= <> != 數據庫特有的比較：in，not in, is null,is not null,like, between and 邏輯運算符：and or not六、表的字段約束：unsigned 無符號(正數)zerofill 前導零填充auto_increment 自增default 默認值not null 非空PRIMARY KEY 主鍵（非null并不重復）unique 唯一性（可以為null但不重復）index 常規索引七: 建表語句格式：create table 表名(字段名類型 [字段約束],字段名類型 [字段約束],字段名類型 [字段約束],...);mysql> create table stu(-> id int unsigned not null auto_increment primary key,-> name varchar(8) not null unique,-> age tinyint unsigned,-> sex enum('m','w') not null default 'm',-> classid char(6)-> );Query OK, 0 rows affected (0.05 sec)mysql> desc stu;+---------+---------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+---------+---------------------+------+-----+---------+----------------+| id | int(10) unsigned | NO | PRI | NULL | auto_increment || name | varchar(8) | NO | UNI | NULL | || age | tinyint(3) unsigned | YES | | NULL | || sex | enum('m','w') | NO | | m | || classid | char(6) | YES | | NULL | |+---------+---------------------+------+-----+---------+----------------+5 rows in set (0.00 sec)mysql> show create table stu\G --查看建表的語句*************************** 1. row ***************************Table: stuCreate Table: CREATE TABLE `stu` (`id` int(10) unsigned NOT NULL auto_increment,`name` varchar(8) NOT NULL,`age` tinyint(3) unsigned default NULL,`sex` enum('m','w') NOT NULL default 'm',`classid` char(6) default NULL,PRIMARY KEY (`id`),UNIQUE KEY `name` (`name`)) ENGINE=MyISAM DEFAULT CHARSET=utf81 row in set (0.00 sec)mysql>mysql> insert into stu(id,name,age,sex,classid) values(1,'zhangsan',20,'m','lnmp61');Query OK, 1 row affected (0.00 sec)mysql> insert into stu(name,age,sex,classid) values('lisi',22,'w','lnmp61');Query OK, 1 row affected (0.00 sec)mysql> insert into stu(name,age,classid) values('wangwu',21,'lnmp61');Query OK, 1 row affected (0.00 sec)mysql> insert into stu values(null,'qq',24,'w','lnmp62');Query OK, 1 row affected (0.00 sec)mysql> insert into stu values(null,'aa',20,'m','lamp62'),(null,'bb',25,'m','lnmp63');Query OK, 2 rows affected (0.00 sec)Records: 2 Duplicates: 0 Warnings: 0mysql> select * from stu;+----+----------+------+-----+---------+| id | name | age | sex | classid |+----+----------+------+-----+---------+| 1 | zhangsan | 20 | m | lnmp61 || 2 | lisi | 22 | w | lnmp61 || 3 | wangwu | 21 | m | lnmp61 || 4 | qq | 24 | w | lnmp62 || 5 | aa | 20 | m | lnmp62 || 6 | bb | 25 | m | lnmp63 |+----+----------+------+-----+---------+6 rows in set (0.00 sec) 簡單實例操作 -- 多表查詢： -- 1. 嵌套方式的多表查詢 -- 2. where關聯查詢 -- 3. 左聯和右聯，內聯的查詢。================================================= -- 已知條件： mysql> select * from stu; +----+------------+-----+------+---------+ | id | name | sex | age | classid | +----+------------+-----+------+---------+ | 1 | zhangsan | m | 20 | lnmp01 | | 2 | lisi | w | 21 | lnmp02 | | 3 | wangwu | m | 25 | lnmp01 | | 4 | zhaoliu | w | 29 | lnmp02 | | 5 | qq01 | w | 28 | lnmp01 | | 6 | qq02 | m | 30 | lnmp02 | | 7 | qq03 | w | 31 | lnmp03 | | 8 | xiaowang | m | 18 | lnmp04 | | 9 | xiaoli | w | 19 | lnmp03 | | 10 | xiaobai | m | 22 | lnmp02 | | 11 | xiaosun | w | 24 | lnmp01 | | 12 | xiaozhang2 | w | 36 | lnmp05 | +----+------------+-----+------+---------+ 12 rows in set (0.05 sec)mysql> select * from grade; +----+-----+------+-------+ | id | sid | php | mysql | +----+-----+------+-------+ | 1 | 4 | 80 | 92 | | 2 | 2 | 78 | 80 | | 3 | 1 | 99 | 95 | | 4 | 6 | 58 | 62 | | 5 | 7 | 89 | 98 | | 6 | 3 | 68 | 54 | +----+-----+------+-------+ 6 rows in set (0.00 sec)mysql>-- 一、嵌套查詢：一個查詢結果是另外一個查詢的條件。-- 例如在學生表中獲取年齡最大的信息 mysql> select max(age) from stu; +----------+ | max(age) | +----------+ | 36 | +----------+ 1 row in set (0.00 sec)mysql> select * from stu where age=(select max(age) from stu); +----+------------+-----+------+---------+ | id | name | sex | age | classid | +----+------------+-----+------+---------+ | 12 | xiaozhang2 | w | 36 | lnmp05 | +----+------------+-----+------+---------+ 1 row in set (0.00 sec)mysql>-- 獲取php考試成績最好的是誰？mysql> select max(php) from grade; --獲取php最高成績 +----------+ | max(php) | +----------+ | 99 | +----------+ 1 row in set (0.11 sec)mysql> select sid from grade where php=99; --獲取php成績在99分的sid學號信息 +-----+ | sid | +-----+ | 1 | +-----+ 1 row in set (0.00 sec)mysql> select * from stu where id=1; --獲取學號為1的學生信息 +----+----------+-----+------+---------+ | id | name | sex | age | classid | +----+----------+-----+------+---------+ | 1 | zhangsan | m | 20 | lnmp01 | +----+----------+-----+------+---------+ 1 row in set (0.00 sec)mysql> select * from stu where id=(select sid from grade where php=99); mysql> select * from stu where id in(select sid from grade where php=99); +----+----------+-----+------+---------+ | id | name | sex | age | classid | +----+----------+-----+------+---------+ | 1 | zhangsan | m | 20 | lnmp01 | +----+----------+-----+------+---------+ 1 row in set (0.19 sec)-- 三層嵌套查詢，解決考試php成績最好的信息 mysql> select * from stu where id in(select sid from grade where php=(select max(p hp) from grade)); +----+----------+-----+------+---------+ | id | name | sex | age | classid | +----+----------+-----+------+---------+ | 1 | zhangsan | m | 20 | lnmp01 | +----+----------+-----+------+---------+ 1 row in set (0.00 sec)-- 二、 where關聯查詢-- 查詢stu表與grade表的關聯查詢 mysql> select * from stu,grade where stu.id=grade.sid; +----+----------+-----+------+---------+----+-----+------+-------+ | id | name | sex | age | classid | id | sid | php | mysql | +----+----------+-----+------+---------+----+-----+------+-------+ | 4 | zhaoliu | w | 29 | lnmp02 | 1 | 4 | 80 | 92 | | 2 | lisi | w | 21 | lnmp02 | 2 | 2 | 78 | 80 | | 1 | zhangsan | m | 20 | lnmp01 | 3 | 1 | 99 | 95 | | 6 | qq02 | m | 30 | lnmp02 | 4 | 6 | 58 | 62 | | 7 | qq03 | w | 31 | lnmp03 | 5 | 7 | 89 | 98 | | 3 | wangwu | m | 25 | lnmp01 | 6 | 3 | 68 | 54 | +----+----------+-----+------+---------+----+-----+------+-------+ 6 rows in set (0.13 sec) -- 查詢stu表與grade表的關聯查詢，只顯示部分字段 mysql> select s.id,s.name,g.php,g.mysql from stu s,grade g-> where s.id=g.sid; +----+----------+------+-------+ | id | name | php | mysql | +----+----------+------+-------+ | 4 | zhaoliu | 80 | 92 | | 2 | lisi | 78 | 80 | | 1 | zhangsan | 99 | 95 | | 6 | qq02 | 58 | 62 | | 7 | qq03 | 89 | 98 | | 3 | wangwu | 68 | 54 | +----+----------+------+-------+ 6 rows in set (0.03 sec)-- 查詢lnmp01班的學生考試信息，顯示：學號、姓名、班級、php和MySQL字段。 mysql> select s.id,s.name,s.classid,g.php,g.mysql from stu s,grade g-> where s.id=g.sid and s.classid='lamp01'; +----+----------+---------+------+-------+ | id | name | classid | php | mysql | +----+----------+---------+------+-------+ | 1 | zhangsan | lnmp01 | 99 | 95 | | 3 | wangwu | lnmp01 | 68 | 54 | +----+----------+---------+------+-------+ 2 rows in set (0.00 sec)-- 統計每個班的考試人數和平均成績：顯示：班級，人數和平均成績 mysql> select s.classid,count(*) num,avg(g.php) php ,avg(g.mysql) mysql-> from stu s,grade g-> where s.id=g.sid group by s.classid; +---------+-----+---------+---------+ | classid | num | php | mysql | +---------+-----+---------+---------+ | lnmp01 | 2 | 83.5000 | 74.5000 | | lnmp02 | 3 | 72.0000 | 78.0000 | | lnmp03 | 1 | 89.0000 | 98.0000 | +---------+-----+---------+---------+ 3 rows in set (0.00 sec)-- 查詢姓名中含有ang的考試信息 mysql> select s.id,s.name,g.php,g.mysql from stu s,grade g-> where s.id=g.sid and s.name like '%ang%'; +----+----------+------+-------+ | id | name | php | mysql | +----+----------+------+-------+ | 1 | zhangsan | 99 | 95 | | 3 | wangwu | 68 | 54 | +----+----------+------+-------+ 2 rows in set (0.00 sec)--三、左聯和右聯、內聯（等價于where）查詢 --------------------------------------------------------查詢所有學生的考試信息（包含沒有考試的） -- 采用左聯查詢（以左邊為主，右邊沒有的對應數據補null） mysql> select s.id,s.name,g.php,g.mysql from stu s left join grade g-> on s.id=g.sid; +----+------------+------+-------+ | id | name | php | mysql | +----+------------+------+-------+ | 2 | lisi | 78 | 80 | | 5 | qq01 | NULL | NULL | | 6 | qq02 | 58 | 62 | | 7 | qq03 | 89 | 98 | | 3 | wangwu | 68 | 54 | | 10 | xiaobai | NULL | NULL | | 9 | xiaoli | NULL | NULL | | 11 | xiaosun | NULL | NULL | | 8 | xiaowang | NULL | NULL | | 12 | xiaozhang2 | NULL | NULL | | 1 | zhangsan | 99 | 95 | | 4 | zhaoliu | 80 | 92 | +----+------------+------+-------+ 12 rows in set (0.00 sec)-- 采用右聯查詢（以右邊為主，左邊沒有的對應數據補null） mysql> select s.id,s.name,g.php,g.mysql from grade g right join stu s-> on s.id=g.sid; +----+------------+------+-------+ | id | name | php | mysql | +----+------------+------+-------+ | 2 | lisi | 78 | 80 | | 5 | qq01 | NULL | NULL | | 6 | qq02 | 58 | 62 | | 7 | qq03 | 89 | 98 | | 3 | wangwu | 68 | 54 | | 10 | xiaobai | NULL | NULL | | 9 | xiaoli | NULL | NULL | | 11 | xiaosun | NULL | NULL | | 8 | xiaowang | NULL | NULL | | 12 | xiaozhang2 | NULL | NULL | | 1 | zhangsan | 99 | 95 | | 4 | zhaoliu | 80 | 92 | +----+------------+------+-------+ 12 rows in set (0.00 sec) -- 內聯查詢，就是兩邊表都存在的。 mysql> select s.id,s.name,g.php,g.mysql from grade g inner join stu s-> on s.id=g.sid; +----+----------+------+-------+ | id | name | php | mysql | +----+----------+------+-------+ | 4 | zhaoliu | 80 | 92 | | 2 | lisi | 78 | 80 | | 1 | zhangsan | 99 | 95 | | 6 | qq02 | 58 | 62 | | 7 | qq03 | 89 | 98 | | 3 | wangwu | 68 | 54 | +----+----------+------+-------+ 6 rows in set (0.00 sec)mysql> -- 查詢lnmp01班的學生考試信息 mysql> select s.id,s.name,s.classid,g.php,g.mysql -> from stu s left join grade g-> on s.id=g.sid -> where s.classid='lnmp01'; +----+----------+---------+------+-------+ | id | name | classid | php | mysql | +----+----------+---------+------+-------+ | 1 | zhangsan | lnmp01 | 99 | 95 | | 3 | wangwu | lnmp01 | 68 | 54 | | 5 | qq01 | lnmp01 | NULL | NULL | | 11 | xiaosun | lnmp01 | NULL | NULL | +----+----------+---------+------+-------+ 4 rows in set (0.00 sec)mysql> 高級操作

轉載于:https://www.cnblogs.com/zcqdream/p/6222882.html

總結

以上是生活随笔為你收集整理的Mysql操作语句的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

上一篇： 93-类型转换实便
下一篇： mysql之ALTER COLUMN、C