MySQL(一)--基本文法與常用語句

標籤：資料庫管理   default   串連   長度   用戶端串連   ted   儲存   ati   計算   

將大量資料儲存起來，通過電腦加工而成的可以進行高效訪問的資料集合稱為資料庫（Database，DB）。

將姓名、住址、電話號碼、郵箱地址、愛好和家庭構成等資料儲存到資料庫中，就可以隨時迅速擷取想要的資訊了。用來管理資料庫的電腦系統稱為資料庫管理系統（Database Management System，DBMS）。

DBMS有過資料的儲存格式（資料庫的種類）來進行分類，現階段主要有五種類型：層次資料庫（Hierarchical Database，HDB），關聯式資料庫（Relational Database，RDB），物件導向資料庫（Object Oriented Database，OODB），XML 資料庫（XML Database，XMLDB），KVStore for Redis系統（Key-Value Store，KVS）。

DBMS 稱為關聯式資料庫管理系統（Relational Database Management System，RDBMS）。比較具有代表性的 RDBMS 有 Oracle Database ：甲骨文公司；SQL Server ：微軟公司；DB2 ：IBM 公司；PostgreSQL ：開源；MySQL ：開源。

MySQL作為很好的 RDBMS 應用軟體之一，使用率也是upup的。因為懶，文中操作僅在MySQL5.7上加以驗證。

• 零、準備
  • 1、安裝MySQL
  • 2、服務端啟動
  • 3、用戶端串連
  • 4、SQL 陳述式分類
• 一、資料庫操作
  • 1、顯示資料庫
  • 2、建立資料庫
  • 3、使用資料庫
  • 4、使用者管理
  • 5、授權管理
• 二、資料表操作
  • 1、建立表
  • 2、刪除表
  • 3、清空表
  • 4、修改表
• 三、表內容操作
  • 1、增
  • 2、刪
  • 3、改
  • 4、查
  • 5、條件查詢

零、準備1、安裝MySQL

http://www.cnblogs.com/lidyan/p/6587718.html

2、服務端啟動

mysql.server start

?

3、用戶端串連

mysql -u username -p--退出QUIT 或者 Control+D

?
?

4、SQL 陳述式分類

• DDL（Data Definition Language，資料定義語言 (Data Definition Language)）用來建立或者刪除儲存資料用的資料庫以及資料庫中的表等對象。DDL 包含以下幾種指令。

DDL（資料定義語言 (Data Definition Language)）
CREATE：建立資料庫和表等對象
DROP：刪除資料庫和表等對象
ALTER：修改資料庫和表等對象的結構

• DML（Data Manipulation Language，資料操縱語言）用來查詢或者變更表中的記錄。DML 包含以下幾種指令。

DML（資料操縱語言）
SELECT：查詢表中的資料
INSERT：向表中插入新資料
UPDATE：更新表中的資料
DELETE：刪除表中的資料

• DCL（Data Control Language，資料控制語言）用來確認或者取消對資料庫中的資料進行的變更。除此之外，還可以對 RDBMS 的使用者是否有許可權操作資料庫中的對象（資料庫表等）進行設定。DCL 包含以下幾種指令。

DCL（資料控制語言）
COMMIT：確認對資料庫中的資料進行的變更
ROLLBACK：取消對資料庫中的資料進行的變更
GRANT：賦予使用者操作許可權
REVOKE：取消使用者的操作許可權

一、資料庫操作1、顯示資料庫

SHOW DATABASES;

預設資料庫有以下：
mysql - 使用者權限相關資料
test - 用於使用者測試資料
information_schema - MySQL本身架構相關資料

2、建立資料庫

CREATE DATABASE 資料庫名稱 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

一般是utf8。後續檢索、各種小工具都能用起來。

3、使用資料庫

--使用資料庫USE 資料庫名稱;--顯示當前使用的資料庫中所有表SHOW TABLES;  

4、使用者管理

--建立使用者create user '使用者名稱'@'IP地址' identified by '密碼';--刪除使用者drop user '使用者名稱'@'IP地址';--修改使用者rename user '使用者名稱'@'IP地址'; to '新使用者名稱'@'IP地址';--修改密碼set password for '使用者名稱'@'IP地址' = Password('新密碼');

使用者權限相關資料儲存在mysql資料庫的user表中，但不建議直接對其進行操作。

5、授權管理

-- 查看許可權show grants for '使用者'@'IP地址';-- 授權grant  許可權 on 資料庫.表 to   '使用者'@'IP地址' ;-- 取消許可權revoke 許可權 on 資料庫.表 from '使用者'@'IP地址';

經常使用的許可權：
all privileges -除grant外的所有許可權
select -僅查許可權
select,insert -查和插入許可權

使用* 匹配資料庫名和表名：
test.* -test資料庫所有表
*.* -所有資料庫所有表

使用%匹配IP地址

--舉個例子grant all privileges on *.*TO '使用者名稱'@'%';

二、資料表操作1、建立表

create table 表名(    列名  類型  NULL，    列名  類型  NOT NULL)ENGINE=InnoDB DEFAULT CHARSET=utf8

基礎資料型別 (Elementary Data Type)
MySQL的資料類型大致分為：數字型、字元型、日期型。

• INT型: 用來指定儲存整數的列的資料類型（數字型），不能儲存小數。
• CHAR型: 用來指定儲存字串的列的資料類型（字元型）。可以像 CHAR(200) 這樣，在括弧中指定該列可以儲存的字串的長度（最大長度）。字串超出最大長度的部分是無法輸入到該列中的。當列中儲存的字串長度達不到最大長度的時候，使用半形空格進行補足。
• VARCHAR型: 也可以通過括弧內的數字來指定字串的最大長度(字元型)。但該類型的列是以可變長字串的形式來儲存字串。可變長字串即使字元數未達到最大長度，也不會用半形空格補足。
• DATE型: 用來指定儲存日期（年月日）的列的資料類型（日期型）。

更多資料類型

預設值
建立列時可以指定預設。

create table tb1(nid int not null default 2,num int not null)   

自增
如果為某列設定自增列，插入資料時無需設定此列，預設將自增(表中只能有一個自增列)。

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;

show global variables like 'auto_inc%';
set global auto_increment_increment=2;
set global auto_increment_offset=10;

主鍵
一種特殊的唯一索引，不允許有空值，如果主鍵使用單個列，則它的值必須唯一，如果是多列，則其組合必須唯一。

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 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))

2、刪除表

drop table 表名

3、清空表

delete from 表名truncate table 表名  

4、修改表

--添加列alter table 表名 add column 列名 類型--刪除列alter table 表名 drop column 列名--修改列-- 類型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;

三、表內容操作1、增

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

2、刪

--保留資料表，刪除全部行delete from 表delete from 表 where id＝1 and name＝'dyan';truncate 表

3、改

update 表 set name ＝ 'dyan' where id>1;

4、查

select * from 表select * from 表 where id > 1select nid,name,gender as 新表名 from 表 where id > 1子查詢的運算子 =,<>,>,>=,<,<=is null, not, and, or, 

5、條件查詢

1)條件    select * from 表 where id > 1 and name != 'dyan' 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 表);    2)彙總    COUNT：計算表中的記錄數（行數）    SUM：計算表中數值列中資料的合計值    AVG：計算表中數值列中資料的平均值    MAX：求出表中任意列中資料的最大值    MIN：求出表中任意列中資料的最小值        --後者會得到NULL之外的資料行數    select count(*),count(<列名>) from 表名; 3)萬用字元    select * from 表 where name like 'ale%'  - ale開頭的所有（多個字串）    select * from 表 where name like 'ale_'  - ale開頭的所有（一個字元） 4)限制    select * from 表 limit 5;            - 前5行    select * from 表 limit 4,5;          - 從第4行開始的5行    select * from 表 limit 5 offset 4;   - 從第4行開始的5行 5)分組    select num from 表 group by num    select num,nid from 表 group by num,nid    select num,nid from 表  where nid > 10 group by num,nid order nid desc    select num,nid,count(*),sum(score),max(score),min(score) from 表 group by num,nid     select num from 表 group by num having max(id) > 10     特別的：group by 必須在where之後，order by之前    6）排序    select * from 表 order by 列                  - 根據 “列” 從小到大排列，預設asc升序    select * from 表 order by 列 desc             - 根據 “列” 從大到小排列    select * from 表 order by 列1 desc,列2 asc    - 根據 “列1” 從大到小排列，如果相同則按列2從小到大排序    select 列名1，count(*) from 表 group by 列名1 order by count(*) 7)連表    無對應關係則不顯示    select A.num, A.name, B.name    from A,B    Where A.nid = B.nid     無對應關係則不顯示    select A.num, A.name, B.name    from A inner join B    on A.nid = B.nid     A表所有顯示，如果B中無對應關係，則值為null    select A.num, A.name, B.name    from A left join B    on A.nid = B.nid     B表所有顯示，如果B中無對應關係，則值為null    select A.num, A.name, B.name    from A right join B    on A.nid = B.nid 8)組合    組合，自動處理重合    select nickname    from A    union    select name    from B     組合，不處理重合    select nickname    from A    union all    select name    from B    

MySQL(一)--基本文法與常用語句