Mysql之SQL使用

標籤：lda   使用者   重點   文法   表達   別名   rda   預設值   value   

Mysql之SQL使用

簡介
什麼是 SQL？
SQL 指結構化查詢語言 (SQL)
SQL 使我們有能力訪問資料庫
SQL 是一種 ANSI 的標準電腦語言
備忘：ANSI，美國國家標準化組織

一、SQL語句、文法

1、查：SELECT 語句用於從表中選取資料。結果被儲存在一個結果表中（稱為結果集）。sql select 文法select 列 from table_name；select * from table_name;2、插：INSERT INTO 語句用於向表格中插入新的行。sql insert 文法insert into table_name values (值1,值2,...);insert into table_name (列1,列2,...) values (值1,值2,...);3、改：Update 語句用於修改表中的資料。sql update 文法update table_name set 列1 = 新值 where 列2 = 某值  # 列2指定誰修改，列1指定修改的內容4、刪：DELETE 語句用於刪除表中的行。sql delete 文法delete from table_name where 列 = 值；     # 刪除某一行delete from table_name; 或 delete * from table_name;     #刪除所有行（表的結構、屬性和索引都是完整的）sql truncate table 文法truncate table table_name;      # 與delete一樣刪除表中所有行，效率更快5、top:TOP 子句用於規定要返回的記錄的數目。列(s) = *sql top 文法select top number|precent 列(s) from table_name;     # 列(s) 表示 所有的列名稱 top number例：select top 2 * from table_name; 或 select * from table_name limit 2;top precent例：select top 50 precent * from table_name; # 選取表中50%的記錄6、like:LIKE 操作符用於在 WHERE 子句中搜尋列中的指定模式sql like 文法select 列(s) from table_name where 列 like pattern;例：select * from table_name where 列 like ‘A%‘(在表中搜尋以A開頭的)|‘%B‘(以B結尾的)|‘%AB%‘(包含AB的);7、in:IN 操作符允許我們在 WHERE 子句中規定多個值sql in 文法select * from table_name where 列 in(值1,值2,...)；8、between：操作符 BETWEEN ... AND 會選取介於兩個值之間的資料範圍。這些值可以是數值、文本或者日期。sql between 文法select * from table_name where 列 between 值1 and 值2；  # 範圍取頭不取尾9、alias：為列名稱和表名稱指定別名（Alias）。sql alias 文法表：select * from table_name as alias_name;表別名例：select p.列1,p.列2,p.列3 from table_name1 as p,table_name2 as po where p.列1=‘值1‘ and p.列2=‘值2‘;列：select 列 as alias_name from table_name;列別名例：select 列1 as alias_name1,列2 as alias_name2 from table_name;10、join：用於根據兩個或多個表中的列之間的關係，從這些表中查詢資料。sql join 文法select tb1.列1,tb1.列2,tb2.列 from tb1 inner join tb2 on tb1.列3=tb2.列3 order by tb1.列1;或select tb1.列1,tb1.列2,tb2.列 from tb1,tb2 where tb1.列3=tb2.列3;備忘：不同的 SQL JOIN除了在上面的例子中使用的 INNER JOIN（內串連），還可以使用其他幾種串連。下面列出了可以使用的 JOIN 類型，以及它們之間的差異。JOIN: 如果表中有至少一個匹配，則返回行LEFT JOIN: 即使右表中沒有匹配，也從左表返回所有的行RIGHT JOIN: 即使左表中沒有匹配，也從右表返回所有的行FULL JOIN: 只要其中一個表中存在匹配，就返回行inner join 關鍵字文法：在表中存在至少一個匹配時，INNER JOIN 關鍵字返回行。select * from tb1 inner join tb2 on tb1.列=tb2.列;        # inner join 與join是相同的left join 關鍵字文法：LEFT JOIN 關鍵字會從左表(tb1)那裡返回所有的行，即使在右表(tb2)中沒有匹配的行。select * from tb1 left join tb2 on tb1.列=tb2.列;right join 關鍵字文法：RIGHT JOIN 關鍵字會右表(tb2)那裡返回所有的行，即使在左表(tb1)中沒有匹配的行。select * from tb1 right join tb2 on tb1.列=tb2.列;full join 關鍵字文法：只要其中某個表存在匹配，FULL JOIN 關鍵字就會返回行select * from tb1 full join tb2 on tb1.列=tb2.列;11、union：用於合并兩個或多個 SELECT 語句的結果集(UNION 內部的 SELECT 語句必須擁有相同數量的列。列也必須擁有相似的資料類型。同時，每條 SELECT 語句中的列的順序必須相同)sql union 與 union all 文法select * from table_name1 union select * from table_name2;select * from table_name1 union all select * from table_name2;備忘：預設地，union 選取不同的值，如果允許重複的值，就使用 union all(列出所有的值)12、select into:從一個表中選取資料，然後把資料插入另一個表中。常用於建立表的備份複件或者用於對記錄進行存檔。sql select into 文法select * into new_table_name [in externaldatabase] from old_table_name; # 所有列插入新表select 列 into new_table_name [in externaldatabase] from old_table_name;  # 某一列插入新表例：備份某張表  select * into tb1_backup from tb1;向另一個資料庫中拷貝表  select * into tb1 in ‘backup.mdb‘ from tb1;多表查詢存入到新表   select tb1.列1,tb2.列2 into new_table_name from tb1 inner join tb2 on tb1.列3=tb2.列3;13、create database：用於建立資料庫sql create database 文法create database database_name;14、create table：用於建立資料庫中的表sql create table 文法create table table_name(列1 資料類型,列2 資料類型,...)備忘：常用資料類型有 int(size)-整數、decimal(size,d)-帶小數數字、char(size)-固定長度字串、varchar(size)-可變長字串資料型別後續會繼續補充15、create index：用於在表中建立索引。在不讀取整個表的情況下，索引使資料庫應用程式可以更快地尋找資料。注釋：更新一個包含索引的表需要比更新一個沒有索引的表更多的時間，這是由於索引本身也需要更新。因此，理想的做法是僅僅在常常被搜尋的列（以及表）上面建立索引。sql create index 文法create index index_name on table_name(列);   # 簡單的索引,允許使用重複的值create index index_name on table_name(列1 desc,列2);      # 預設是升序，希望降序在列名稱後添加保留字 desc,索引不止一個列可以逗號隔開sql create unique index 文法      # 唯一的索引create unique index index_name on table_name(列);    # 唯一的索引意味著兩個行不能擁有相同的索引值16、DROP：刪除索引、表以及資料庫sql drop 文法刪除索引：drop index index_name on table_name;刪除表：drop table table_name;刪除庫：drop database database_name;17、alter table : 語句用於在已有的表中添加、修改或刪除列。sql alter table 文法添加列：alter table table_name add 列 資料類型;刪除列：alter table table_name drop column 列;改變列中資料類型：alter table table_name modify column 列 需要修改的類型;只修改列的資料類型的方法:通常可以寫成 alter table 表名 modify column 列名 新的列的類型例如:student表中列sname的類型是char(20),現在要修改為varchar(20),SQL語句如下     alter table student modify column sname varchar(20);同時修改列名和列的資料類型的方法:通常可以寫成 alter table 表名 change column 舊列名 新列名 新的列類型例如:student表中列sname的類型是char(20),現在要修改為stuname varchar(20),SQL語句如下     alter table student change column sname stuname varchar(20);alter table table_name add <新列名> <資料類型> [約束條件][FLRST(添加列到表的最前面)|AFTER(指定列後面)  <已存在的列>]; # 添加列alter table table_name <舊列名> <新列名> <新資料類型>; # 修改列名alter table table_name modify <列名> <資料類型>; # 修改列的資料類型alter table table_name modify <列1(想要改變的列)> <資料類型> FLRST|AFTER <列2>; # 修改列的位置alter table table_name drop <列>;  # 刪除列alter table <舊錶名> rename to <新表名>; # 修改表名18、auto increment：會在新記錄插入表中時產生一個唯一的數字通常希望在每次插入新記錄時，自動地建立主鍵欄位的值。可以在表中建立一個 auto-increment 欄位。sql auto-increment 文法create table table_name(id int not null auto_increment,name varchar(255) not null,PRIMARY key(id)); #id定義為auto_increment主鍵19、view(視圖)： 視圖是可視化的表。視圖包含行和列，就像一個真實的表注釋：資料庫的設計和結構不會受到視圖中的函數、where 或 join 語句的影響。sql create view 文法create view view_name as select 列(s) from table_name where 條件;注釋：視圖總是顯示最近的資料。每當使用者查詢檢視時，資料庫引擎通過使用 SQL 陳述式來重建資料。更新視圖：create or replace view view_name as select 列(*) from table_name where 條件;刪除視圖：drop view view_name;

二、SQL 約束(constraints)

sql約束：約束用於限制加入表的資料的類型。是一種限制，它通過對錶的行或列的資料做出限制，來確保表資料的完整性、唯一性
可以在建立表時規定約束（通過 CREATE TABLE 語句），或者在表建立之後也可以（通過 ALTER TABLE 語句）。

1、not null 約束：強制列不接受 NULL 值，強制欄位始終包含值。這意味著，如果不向欄位添加值，就無法插入新記錄或者更新記錄。例：create table table_name(id int not null,name varchar(255) not null); # 強制id和name不可為空2、unique 約束：唯一標識資料庫表中的每條記錄,確保表中的一列資料沒有相同的值UNIQUE 和 PRIMARY KEY 約束均為列或列集合提供了唯一性的保證。PRIMARY KEY 擁有自動定義的 UNIQUE 約束。每個表可以有多個 UNIQUE 約束，但是每個表只能有一個 PRIMARY KEY 約束例：create table時在id列建立unique約束create table table_name(id int not null,name varchar(255) not null, unique (id));例2：為多個列定義uniquecreate table table_name(id int not null,name varchar(255) not null, constraint uc_personID unique (id,name))例3：表已建立情況下，建立unique約束alter table table_name add unique(id);多個：alter table table_name add constraint uc_personid unique(id,name);例4：撤銷uniquealter table table_name drop index uc_personid;3、PRIMARY KEY 約束：PRIMARY KEY 約束唯一標識資料庫表中的每條記錄。主鍵必須包含唯一的值。主鍵列不能包含 NULL 值。每個表都應該有一個主鍵，並且每個表只能有一個主鍵。 primary key = unique +  not null 例：create table時在id列建立PRIMARY KEY約束create table table_name(id int not null,name varchar(255) not null, PRIMARY KEY(id));例2：為多個列定義PRIMARY KEYcreate table table_name(id int not null,name varchar(255) not null, constraint pk_personID PRIMARY KEY (id,name))例3：表已建立情況下，建立PRIMARY KEY約束alter table table_name add PRIMARY KEY(id);多個：alter table table_name add constraint pk_personid PRIMARY KEY(id,name);例4：撤銷PRIMARY KEYalter table table_name drop index uc_personid;4、FOREIGN KEY 約束：一個表中的 FOREIGN KEY 指向另一個表中的 PRIMARY KEY外鍵外鍵用來在兩個表資料之間建立連結，它可以是一列或多列。一個表可以有一個或多個外鍵外鍵對應得是參照完整性，一個表得外鍵可以為空白值，若不為空白值，則每一個外索引值必須等於另一個表中主鍵得某個值。FOREIGN KEY 約束用於預防破壞表之間串連的動作。FOREIGN KEY 約束也能防止非法資料插入外鍵列，因為它必須是它指向的那個表中的值之一。例：create table時在id列建立 FOREIGN KEY 約束create table table_name1(id int not null,name varchar(255) not null,Id_P int, PRIMARY KEY(id),FOREIGN KEY (Id_P) REFERENCES table_name2(Id_P));例2：為多個列定義 FOREIGN KEY create table table_name1(id int not null,name varchar(255) not null,Id_P int, PRIMARY KEY(id) constraint fk_pertb2 FOREIGN KEY (Id_P)REFERENCES table_name2(Id_P) )例3：表已建立情況下，建立 FOREIGN KEY 約束alter table table_name add ADD FOREIGN KEY (Id_P) REFERENCES table_name1(Id_P);多個：alter table table_name add constraint pk_personid PRIMARY KEY(id,name);例4：撤銷 FOREIGN KEY alter table table_name drop FOREIGN KEY fk_pertb2;外鍵約束mysql> create table bookcategory(    -> category_id int primary key,    -> category varchar(20),    -> parent_id int);mysql> create table bookinfo(    -> book_id int primary key,    -> book_category_id int,    -> constraint fk_cid foreign key(book_category_id) references bookcategory(category_id));5、CHECK 條件約束：用於限制列中的值的範圍如果對單個列定義 CHECK 條件約束，那麼該列只允許特定的值。如果對一個表定義 CHECK 條件約束，那麼此約束會在特定的列中對值進行限制。例：create table時在id列建立 CHECK 條件約束create table table_name(id int not null,name varchar(255) not null,CHECK(id>0));例2：為多個列定義 CHECK 條件約束create table table_name(id int not null,name varchar(255) not null, constraint chk_tbname CHECK(id>0 and name=‘xxx‘));例3：表已建立情況下，建立 CHECK 條件約束alter table table_name ADD constraint chk_tbname CHECK (Id_P>0 AND name=‘xxx‘); 例4：撤銷 CHECKalter table table_name drop constraint chk_tbname;6、DEFAULT 約束：用於向列中插入預設值如果沒有規定其他的值，那麼會將預設值添加到所有的新記錄。例：create table時在id列建立 DEFAULT 約束，類似 GETDATE() 這樣的函數，DEFAULT 約束也可以用於插入系統值create table table_name(id int not null,name varchar(255) not null DEFAULT ‘lxq‘,timedate date DEFAULT GETDATE());例2：表已建立情況下，建立 DEFAULT 約束alter table table_name alter name set DEFAULT ‘lxq2‘;例3：撤銷 DEFAULTalter table table_name ALTER name DROP DEFAULT;

三、sql函數

1、date

函數及描述NOW()：返回當前的日期和時間CURDATE()：返回當前的日期CURTIME()：返回當前的時間DATE()：提取日期或日期/時間運算式的日期部分EXTRACT()：返回日期/時間按的單獨部分DATE_ADD()：給日期添加指定的時間間隔DATE_SUB()：從日期減去指定的時間間隔DATEDIFF()：返回兩個日期之間的天數DATE_FORMAT()：用不同的格式顯示日期/時間

四、數字類型

MySQL中，有三種主要的類型：文本、數字和日期/時間類型
1、Text 類型

CHAR(size)：儲存固定長度的字串（可包含字母、數字以及特殊字元）。在括弧中指定字串的長度。最多 255 個字元。VARCHAR(size)：儲存可變長度的字串（可包含字母、數字以及特殊字元）。在括弧中指定字串的最大長度。最多 255 個字元。注釋：如果值的長度大於 255，則被轉換為 TEXT 類型。TINYTEXT：存放最大長度為 255 個字元的字串。TEXT：存放最大長度為 65,535 個字元的字串。BLOB：用於 BLOBs (Binary Large OBjects)。存放最多 65,535 位元組的資料。MEDIUMTEXT：存放最大長度為 16,777,215 個字元的字串。MEDIUMBLOB：用於 BLOBs (Binary Large OBjects)。存放最多 16,777,215 位元組的資料。LONGTEXT：存放最大長度為 4,294,967,295 個字元的字串。LONGBLOB：用於 BLOBs (Binary Large OBjects)。存放最多 4,294,967,295 位元組的資料。ENUM(x,y,z,etc.)：允許你輸入可能值的列表。可以在 ENUM 列表中列出最大 65535 個值。如果列表中不存在插入的值，則插入空值。注釋：這些值是按照你輸入的順序儲存的。可以按照此格式輸入可能的值：ENUM(‘X‘,‘Y‘,‘Z‘)SET：與 ENUM 類似，SET 最多隻能包含 64 個清單項目，不過 SET 可儲存一個以上的值。

2、Number 類型

TINYINT(size)：-128 到 127 常規。0 到 255 無符號*。在括弧中規定最大位元。SMALLINT(size)：-32768 到 32767 常規。0 到 65535 無符號*。在括弧中規定最大位元。MEDIUMINT(size)：-8388608 到 8388607 普通。0 to 16777215 無符號*。在括弧中規定最大位元。INT(size)：-2147483648 到 2147483647 常規。0 到 4294967295 無符號*。在括弧中規定最大位元。BIGINT(size)：-9223372036854775808 到 9223372036854775807 常規。0 到 18446744073709551615 無符號*。在括弧中規定最大位元。FLOAT(size,d)：帶有浮動小數點的小數字。在括弧中規定最大位元。在 d 參數中規定小數點右側的最大位元。DOUBLE(size,d)：帶有浮動小數點的大數字。在括弧中規定最大位元。在 d 參數中規定小數點右側的最大位元。DECIMAL(size,d)：作為字串儲存的 DOUBLE 類型，允許固定的小數點。重點：這些整數類型擁有額外的選項 UNSIGNED。通常，整數可以是負數或正數。如果添加 UNSIGNED 屬性，那麼範圍將從 0 開始，而不是某個負數。

3、Date 類型

DATE()：日期。格式：YYYY-MM-DD注釋：支援的範圍是從 ‘1000-01-01‘ 到 ‘9999-12-31‘DATETIME()：*日期和時間的組合。格式：YYYY-MM-DD HH:MM:SS注釋：支援的範圍是從 ‘1000-01-01 00:00:00‘ 到 ‘9999-12-31 23:59:59‘TIMESTAMP()：*時間戳記。TIMESTAMP 值使用 Unix 紀元(‘1970-01-01 00:00:00‘ UTC) 至今的描述來儲存。格式：YYYY-MM-DD HH:MM:SS注釋：支援的範圍是從 ‘1970-01-01 00:00:01‘ UTC 到 ‘2038-01-09 03:14:07‘ UTCTIME()：時間。格式：HH:MM:SS 注釋：支援的範圍是從 ‘-838:59:59‘ 到 ‘838:59:59‘YEAR()：2 位或 4 位格式的年。注釋：4 位格式所允許的值：1901 到 2155。2 位格式所允許的值：70 到 69，表示從 1970 到 2069。重點：即便 DATETIME 和 TIMESTAMP 返回相同的格式，它們的工作方式很不同。在 INSERT 或 UPDATE 查詢中，TIMESTAMP 自動把自身設定為當前的日期和時間。TIMESTAMP 也接受不同的格式，比如 YYYYMMDDHHMMSS、YYMMDDHHMMSS、YYYYMMDD 或 YYMMDD

Mysql之SQL使用