程式學習日記，如果我幫助了你請讓我知道

1.union,union all

union 連接兩個句子

(select *from articles  limit 2)union all (select *from articles limit 5 )   

重點在用limit or order by 要括號，否則最後的會被當成全部

1.1union all

如果查到重覆的資料，選擇顯示，預設union不顯示

2.複製table

create table xx select *from abc where 1<>1

insert into xx select * from abc

3.輸出data  select * into outfile ''  

3.1select *into outfile 'D:/c.txt' from e

3.2不管Linux,window 皆/

3.2分隔符  terminated by '==='

select *into outfile 'D:/cc.txt' fields terminated by '===' from e

3.3包圍符 enclosed by '='

3.4轉成csv形式

3.5讀入table

use ..load data infile 

4.將table default character change

alter table bb convert to character set utf8

5.看table的index

show index from bb

6.fulltext

select *from bb where match(content) against ('+今日*' in boolean mode)

7.建表時用unique constraint index的方法

create table xdd(i int not null primary key auto_increment,c varchar(15) ,constraint sdf unique(i,c))

create table xdd(i int not null primary key auto_increment,c varchar(15) , unique(i,c))

ALTER TABLE Persons
ADD UNIQUE (Id_P)

ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)

7.foreign key

CREATE TABLE Orders
(
Id_O int NOT NULL,
OrderNo int NOT NULL,
Id_P int,
PRIMARY KEY (Id_O),
CONSTRAINT fk_PerOrders FOREIGN KEY (Id_P)
REFERENCES Persons(Id_P)
)

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Orders
ADD FOREIGN KEY (Id_P)
REFERENCES Persons(Id_P)

如果需要命名 FOREIGN KEY 约束，以及为多个列定义 FOREIGN KEY 约束，请使用下面的 SQL 语法：

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (Id_P)
REFERENCES Persons(Id_P)

ALTER TABLE Orders
DROP FOREIGN KEY fk_PerOrders

alter table a drop index fk_PerOrders

8.check 約束範圍值

MySQL / SQL Server / Oracle / MS Access:

To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

The MySQL Reference Manual says:

The CHECK clause is parsed but ignored by all storage engines.

check在storage engine沒效所以要用trigger

ysql> delimiter //
mysql> CREATE TRIGGER trig_sd_check BEFORE INSERT ON Customer 
    -> FOR EACH ROW 
    -> BEGIN 
    -> IF NEW.SD<0 THEN 
    -> SET NEW.SD=0; 
    -> END IF; 
    -> END
    -> //

8.將欄位改成default

alter table a alter b set default 'iamdefault'

9.處理Null值的函數 IFNULL()

select ifnull(a,111111)from a 

10.改變欄位順序

alter table a modify a text after id

11. 函數

substring =mid

length()

ucase 轉大寫

lcase

round四捨五入

select round(5.230491234,2)  //5.23

now() //2018-03-21 19:58:19