一、數據表的操作
1、創建表
create table 表名(
列名 類型 是否可以為空，
列名 類型 是否可以為空
)ENGINE=InnoDB DEFAULT CHARSET=utf8
#查看數據表創建語句
show create table t5 \G
descript table
create table tb_emp1
(
id int(11),
name varchar(25),
deptid int(11),
salary float
);2、清空表
delete from 表名
truncate table 表名3、刪除表
drop table 【IF EXISTS】 表1,表2....#IF EXISTS表示如果數據表不存在會提示警告，但不會出錯4、修改數據表
修改表名 - alter table <old table> rename [to] <new table>
alter table tb_dept3 rename tb_deptments;
DESCRIBE tb_dept1;修改字段數據類型 - alter table <tablename> modify <field> <type>
alter table tb_dept1 modify name VARCHAR(30);
DESCRIBE tb_dept1;修改字段名或修改字段數據類型 - alter table <tablename> change <oldfield> <newfield> <newtype>
alter table tb_dept1 change location loc VARCHAR(50);
DESCRIBE tb_dept1;添加字段 - alter table <tablename> add <field> <type> [約束條件] [First|After Field] #添加字段,First表示設為第一個字段，After表示插入到指定字段的下一列
alter table tb_dept1 add managerid int(10) First;
alter table tb_dept1 add column2 VARCHAR(12) not null after name;
DESCRIBE tb_dept1;刪除字段 - alter table <tablename> drop <field>
alter table tb_dept1 drop column2;
DESCRIBE tb_dept1;修改字段排列位置 - alter table <tablename> modify <field1> <type> first|after<field2>
alter table tb_dept1 modify column1 VARCHAR(12) first;
alter table tb_dept1 modify column1 VARCHAR(12) after loc;
DESCRIBE tb_dept1;修改表的存儲引擎 - alter table <tablename> engine <存儲引擎名>
alter table tb_deptments engine=myisam;
show create table tb_deptments; #查看錶結構刪除表外鍵約束 - alter table <tablename> drop foreign key <foreignname>
create table tb_emp9
(
id int(11) PRIMARY key,
name VARCHAR(25),
deptid int(11),
salary FLOAT,
constraint fk_emp_dept foreign key (deptid) REFERENCES tb_dept1(id)
)
alter table tb_emp9 drop foreign key fk_emp_dept;
show create table tb_emp9;常用修改表語句
添加列：alter table 表名 add 列名 類型
刪除列：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;5、會話級別 - 表示當前連接
show session variables like 'auto_inc%' 查看會話級別的全局變量
set session auto_increment_increment=2; 設置會話步長
set session auto_increment_offset=2; 設置會話起始值6、全局級別 - 表示所有連接
show global variables like 'auto_inc%';
set global auto_increment_increment=200
set global auto_increment_offset=10
當全局修改時，所有登錄的用户均根據全局設置
二、數據表約束
1、可空約束
not null - 不可空
null - 可空
create table tb_dept1
(
id int(11) primary key,
name VARCHAR(22) not null,
location VARCHAR(50)
);
2、默認值約束
create table tb1(
nid int not null defalut 2,
num int not null
)3、主鍵約束
#一種特殊的唯一索引，不允許有空值，如果主鍵使用單個列，則它的值必須唯一，如果是多列，則其組合必須唯一。
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)
)4、外鍵約束
#一種特殊的索引，只能是指定內容
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)
)
show create table fruit;
# fk_cc為約束名（不能重複） - foreign key(外鍵名) references 表名(列名)
# 當主鍵為兩列時，對應的外鍵也必須為兩列，如constraint fk_t1_t2 foreign key (id1,id2 references t1(nid,pid)5、唯一約束
#不能重複、加速查找，與主鍵的區別為可以為空
unique 約束名(列名)
unique 約束名(列名1，列名2) 列名1+列名2 聯合唯一
create table tb_dept2
(
id int(11) PRIMARY key,
name VARCHAR(22) UNIQUE,
location VARCHAR(50)
)
#或者直接定義約束名
create table tb_dept3
(
id int(11) PRIMARY KEY,
name VARCHAR(25),
location VARCHAR(50),
constraint STH UNIQUE(name)
);6、自增列
#一個表中只能有一個自增列且必須為主鍵
#插入值時忽略自增列
create talbe t3(
id int auto_increment primary key,
name varchar(20)
)
#修改自增列的當前值
alter table t10 auto_increment=20;
三、數據表操作
1、增加數據
insert into 表 (列名1,列名2...) values (值1,值2,值3...)
insert into 表 (列名1,列名2...) values (值1,值2,值3...),(值1,值2,值3...)
insert into 表 (列名1,列名2...) select (列名1,列名2...) from 表
示例:
insert into tb(name,age) values('alex',12),('root',18)
insert into tb12(name,age) select name,age from tb12
2、刪除數據
delete from 表
delete from 表 where id＝1 and name＝'alex'3、修改數據
update 表 set name ＝ 'alex' where id>1
update tbl2 set name='alex' where id>12 name='xx'
update tb12 set name='alex',age=19 where id>12 and name='xx'4、外部導入數據 - 導入文件格式需要與數據表格式一致
--infile後跟文件路徑，路徑分隔符使用/（非\）<br>--fields terminated by表示分隔符 <br>--ignore 表示忽略多少行
load data local infile 'E:/LiWork/wy/lession/data/orderinfo.csv' into table orderinfo
fields terminated by ','
ignore 1 lines;
四、數據的基本類型
1、數值型 - 整數型
1）tinyint - 微小整數，數據類型用於保存一些範圍的整數數值範圍，1字節
unsigned - 0 ～ 255
signed - -128 ～ 127
PS: MySQL中無布爾值，使用tinyint(1)構造
2）smllint - 小整數，數據類型用於保存一些範圍的整數數值範圍，2字節
unsigned - 0 ～ 65535
signed - -32768 ~ 32767
3）mediumint - 中等整數，數據類型用於保存一些範圍的整數數值範圍，3字節
unsigned - 0 ～ 16777215
signed - -8388608 ～ 8388607
4) int[(m)] - 整數，數據類型用於保存一些範圍的整數數值範圍，4字節
unsigned - 0 ～ 4294967295
signed - -2147483648 ～ 2147483647
PS：整數類型中的m為顯示寬度（實際值小於顯示寬度以空格填充，大於顯示寬度則顯示實際值），對存儲範圍無限制。例如： int(5),當插入數據2時，select 時數據顯示為： 00002
系統會為整型數據添加默認的顯示寬度，可使用desc查看
5）bigint[(m)] - 大整數，數據類型用於保存一些範圍的整數數值範圍，8字節
unsigned - 0 ～ 18446744073709551615
signed - -9223372036854775808～ 9223372036854775807
2、浮點數和定點數 - 都可以使用(m,d)表示，m為精度，表示總位數，d為標度，表示小數位數
1）FLOAT[(M,D)] - 單精度浮點數（非準確小數值），4字節
unsigned - 1.175494351E-38 to 3.402823466E+38
signed - -3.402823466E+38 to -1.175494351E-38
PS：數值越大，越不精準
2）DOUBLE[(M,D)] - 雙精度浮點數（非準確小數值），8字節
unsigned - 2.2250738585072014E-308 to 1.7976931348623157E+308
signed - -1.7976931348623157E+308 to -2.2250738585072014E-308
3）decimal[(m[,d])] - 準確的小數值，m最大值為65，d最大值為30，佔用m+2字節
PS: 不同於浮點數，實際存儲時以字符串存放的，對於精確數值計算時需要用此類型，decaimal能夠存儲精確值的原因在於其內部按照字符串存儲
無論定點數或浮點數，如果實際數值超出精度範圍，則四捨五入，Decimal默認精度為(10,0)，浮點數則根據計算機硬件和操作系統而定
查看警告信息 - show warnings;
3、字符串型
1）char (m) - 用於表示固定長度的字符串，可以包含最多達255個字符。其中（1<M<255）m代表字符串的長度，實際存儲為M字節
PS: 即使數據小於m長度，也會佔用m長度
2）varchar(m) - 用於表示變長的字符串，可以包含最多達255個字符。其中m代表該數據類型所允許保存的字符串的最大長度，只要長度小於該最大值的字符串都可以被保存在該數據類型中，實際存儲
PS：varchar佔用空間少，但char數據類型的處理速度更快，有時甚至可以超出varchar處理速度的50%。
因此，用户在設計數據庫時應當綜合考慮各方面的因素，以求達到最佳的平衡，創建數據表時將定長的數據列往前放，先檢索定長列執行速度會得到優化
3）text - text數據類型用於保存變長的大字符串，可以組多到65535 (2**16 − 1)個字符
4）mediumtext - A TEXT column with a maximum length of 16,777,215 (2**24 − 1) characters
5）longtext - A TEXT column with a maximum length of 4,294,967,295 or 4GB (2**32 − 1) characters
PS：當文件長度超出longtext時，則應保存在服務器硬盤上，數據庫中保存指向文件的路徑
6）關於字符長度
UTF－8：一個漢字＝3個字節
GBK：一個漢字＝2個字節
varchar(n) 表示 n 個字符，無論漢字和英文，Mysql 都能存入 n 個字符，僅是實際字節長度有所區別，MySQL 檢查長度，可用 SQL 語言來查看：
select LENGTH(fieldname) from tablename4、日期型
1）DATE - YYYY-MM-DD（1000-01-01 ~ 9999-12-31），3字節，僅用於需要顯示日期值時
輸入格式為：'YYYY-MM-DD'、'YYYYMMDD'、當使用'YY'時，則70~99表示1970~1999、00~69表示2000~2069
2）TIME - HH:MM:SS（'-838:59:59' ~ '838:59:59'），3字節，用於僅需要顯示時間信息值時
輸入格式為：'D HH:MM:SS'（標準）、'HH:MM:SS'、'HH:MM'、'D HH:MM'、'D HH'、或'SS'，D表示日，取值範圍0~34，插入數據庫時轉換為小時進行保存，'HHMMSS'必須具有時間意義，否則存儲為00:00:00
使用冒號 '11:12'表示為11:12:00，不使用冒號 '1112'則表示為00:11:12
3) DATETIME - YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 Y），8字節，同時包含日期和時間信息的值
輸入格式為：'YYYY-MM-DD HH:MM:SS'、'YYYYMMDDHHMMSS'、YY為兩位時參照1
Mysql允許"不嚴格"語法 - 如 '98.12.31 11+30+45'、'98/12/31 11*30*45'、'98@12@31 11^30^45'都可以正確的插入數據庫
4）TIMESTAMP - YYYYMMDD HHMMSS（1970-01-01 00:00:01 UTC ~ 2038-01-19 03:14:07 UTC），4字節，格式同DATATIME，固定在19個字符，取值範圍小於DATATIME，UTC為世界標準時間
輸入格式為：'YYYY-MM-DD HH:MM:SS'、'YYYYMMDDHHMMSS'
除了取值範圍與DATATIME不同外，DATATIME按實際輸入的格式存儲，而TIMESTAMP存儲時對當前時區進行轉換，檢索時再轉換回當前時區，查詢時根據當前的時區不同，顯示的時間值不同
set time_zone='+10:00'; #設置時區（中國一般為東8區）
select CURRENT_TIME(); #由於設置時區不同，則顯示的時間值不同
5）YEAR - YYYY（1901 ~ 2155），1字節
輸入格式為：'YYYY'或YYYY
輸入2位時，00~69表示2000~2069,70~99表示1970~1999
4、枚舉類型
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');
5、集合類型
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');6、二進制類型
bit[(M)] - 二進制位（101001），m表示二進制位的長度（1-64），默認m＝1
TINYBLOB - 不超過 255 個字符的二進制字符串，0-255字節
BLOB - 二進制形式的長文本數據，0-65 535字節
MEDIUMBLOB - 二進制形式的中等長度文本數據，0-16 777 215字節
LONGBLOB - 二進制形式的極大文本數據，0-4 294 967 295字節
