MySQL資料類型/屬性/增刪改查（14）

標籤：dom   資料庫   user   nio   字元集   查詢   日期類型   inner   表示   

MySQL資料類型

• 日期類型

·date

date資料類型負責儲存日期資訊（1000-01-01到9999-12-31）
可以使用數字和字串插入（20180809或"2018-08-09"）非數字或字母使用分隔字元

·datetime

datetime資料類型負責儲存日期和時間資訊的組合（1000-01-01 00:00:00到9999-12-31 23:59:59）
可以使用數字和字串插入（20180809122556或"2018-08-09 12:25:56"）

·time

time資料類型負責儲存時間資訊（-838:59:59到838:59:59）

·timestame

timestame自動擷取當前的日期和時間

• 數值資料類型

·bool和boolean

bool和boolean只是tinyint(1)的別名

·tinyint

tinyint資料類型是MySQL排名第五的整數範圍（無符號值：0~255 有符號值：-128~127）

·smallint

smallint資料類型是MySQL排名第四的整數範圍（無符號值：0~65535 有符號值：-32768~32767）

·mediumint

mediumint資料類型是MySQL排名第三的整數範圍（無符號值：0~16777215 有符號值：-8388608~8388607）

·int

int資料類型是MySQL排名第二的整數範圍（無符號值：0~4294967295 有符號值：-2147483648~2147483647）

·bigint

bigint資料類型是MySQL排名第一的整數範圍（無符號值：0~18446744073709511615 有符號值：-9233372036854755808~9233372036854755807）

·decimal

decimal資料類型是儲存為字串的浮點數（無符號值：2.2250738585072014E-308~1.7976931348623157E+308 有符號值：-1.7976931348623157E+308~2.2250738585072014E-308）

·double

double資料類型是雙精確度浮點數（無符號值：2.2250738585072014E-308~1.7976931348623157E+308 有符號值：-1.7976931348623157E+308~2.2250738585072014E-308）

·float

float資料類型是單精確度浮點數（無符號值：1.175494351E-38~3.402823466E+38 有符號值：-3.402823466E+38~-1.175494351E-38）

浮點型比如：double(M,D) M儲存數值的個數，D小數的位元

 

• 字串資料型別

·char

char資料類型為MySQL提供了固定的長度，支援最大255個字元，如果插入的字元不足佔用length指定的長度，剩餘部分用空格填充

·varchar

varchar資料類型是MySQL的可變長度，支援長度為65536個字元，會保留尾部的空白部分

·longblob（longtext非二進位）

longblob資料類型是MySQL以二進位字串表示形式，支援長度4294967259個字元

·mediumblob（mediumtext非二進位）

mediumblob資料類型是MySQL二進位字串表示形式，支援長度16777215個字元

·blob（text非二進位）

blob資料類型是MySQL二進位字串表示形式，支援長度65535個字元

·tinyblob（tinytext非二進位）

tinyblob資料類型是MySQL二進位字串表示形式，支援長度255個字元

MySQL屬性

• primary key

主鍵，只能出現一次

建立自動增加主鍵

create table xiu (    id smallint not null auto_increment)

建立單欄位主鍵

create table xiu (    id varchar(255),    primary key(id))

建立多欄位主鍵

create table xiu (    id varchar(255) not null,    age  varchar(255) not null,    primary key(id,age))

• not null

不可為空

• auto_increment

自動成長

• binary

區分大小寫

• unique

只能出現一次，null可以多次出現

• national

確保使用預設字元集

• default

確保沒有任何值可用的情況下，賦予某個常量值

• zerofill

前置，用0填充

• unsigned

設定負號

操作資料庫

• 查看資料庫

show databases;//查看所有資料庫

• 添加資料庫

create database xiu;//添加名為xiu的資料庫

• 刪除資料庫

drop database xiu;//刪除名為xiu的資料庫

• 預設資料庫

use xiu;//預設為xiu資料庫

操作資料庫表

•  添加表

create table xiu (    id tinyint primary key auto_increment,//主鍵，自增    name varchar(20) not null,//不可為空    age int not null//不可為空)

• 查詢表

show create table xiu;//查詢xiu表

• 重新命名

rename table xiu to kang;//將xiu表重新命名為kang表

• 刪除表

drop table xiu;//刪除xiu表

操作資料庫欄位

• 添加一個列

alter table xiu add column kang varchar(10);

• 修改一個列

alter table xiu change kang kang varchar(10) not null;

• 刪除一個列

alter table xiu drop birdate;

• 重新命名一個列

alter table kang change age sex varchar(10);

資料的操作

• 添加一條資料

insert into xiu values(1,"name","age");

• 指定欄位添加值

insert into xiu (name,age) values ("name","age");//因為主鍵是自增，所以可以不用添加id欄位

• 查看資料

select * from xiu;//*代表查詢所有欄位，也可以指定欄位查詢

• 修改資料

update xiu set name="user" where id=1;//將id=1的資料的name欄位的值修改為"user"

• 刪除資料

delete from xiu where id = 1;//刪除id為1的資料

 添加查詢

• 欄位與值相等

=

 （查詢xiu表的資料，條件為name="user"）

select * from xiu where name = "user";

• 同時滿足兩個條件

and

（查詢xiu表的資料，同時滿足這兩個條件name="user",age=18）

select * from xiu where name="user" and age=18;

• 滿足任意條件

or（查詢xiu表的資料，滿足其中一個條件name="user",age=18）

select * from xiu where name="user" or age=18;

• 該欄位與值相等

in（查詢xiu表的資料，滿足其中任一條件age=24,age=18）

select * from xiu where age in(24,18);

• 該欄位與值不相等

not in

（查詢xiu表的資料，不滿足其中任一條件age=24,age=18）

select * from xiu where age not in(24,18);

• 該欄位滿足是該值，主要判斷空值

 is（查詢xiu表的資料，條件為age=null）

select * from xiu where age is null;

• 該欄位不滿足該值 is not 值

 （查詢xiu表的資料，條件為age!=null）

select * from xiu where age is not null;

• 模糊查詢需要萬用字元

like

‘_’：表示佔一位 

‘%’：表示佔零位或者多位

 （查詢xiu表的資料，條件為name值的第二個字元為s）

select * from xiu where name like("_s%");

• 模仿查詢，需要萬用字元‘_’‘%’

not like

 （查詢xiu表的資料，條件為name值的第二個字元不為s）

select * from xiu where name not like("_s%");

• 範圍查詢

between

 （查詢xiu表的資料，條件為age的值的範圍是10-30的數）

select * from xiu where age between 10 and 30;

• 去除重複

distnct

（查詢xiu表的name欄位，條件為重複資料刪除的name值）

select distinct name from xiu;

排序

• 升序 

order by ... asc

 （查詢xiu表的資料，按照age從小到大排序）

select * from xiu order by age asc;

• 降序

order by ... desc

 （查詢xiu表的資料，按照age從大到小排序）

select * from xiu order by age desc;

截取limit（查詢xiu表的資料，擷取第一條資料開始的三條的資料）

select * from xiu limit 0,3;

彙總函式

• avg平均值
• max最大值
• min最小值
• sum和
• count（空值不計數）資料條數

（擷取xiu表中name欄位有多少條資料）

select count(name) from xiu;

分組group bywhere後不能直接添加彙總函式 （將xiu表按欄位age進行分組，然後擷取每個組最大的id）

select max(id) from xiu group by age;

過濾having（擷取每個組最大的id，且id不能為2）

select * from xiu group by age having id != 2;

 

• where與having的區別

where在group by之前，針對每一行的資料having在group by之後，針對每一組的資料  外鍵約束  ··········································································續寫中。。。。。。。foreign key（從表欄位）references 主表名（主表主鍵） 外鍵的查詢show create table 表名 外鍵的刪除alter table 表名 drop foreign key 外鍵名 外鍵的添加alter table 表名 add foreign key（從表欄位）references 主表名（主表主鍵） 主表更新on update 主表刪除on delete 允許的級聯cascade 如果主表被更新或刪除，那麼從表也會執行相應操作set null如果表名刪除或更新，那麼從表變成nullrestrict拒絕主表的相關操作 預設值 ⊙⊙別名as 名as可以省略 ⊙⊙多表select a.欄位1，b.欄位2 from 表名1 as a，表名2 as b ⊙⊙子查詢 按位置分where型from型exists型 瞭解（判斷是否存在） 按結果分一個值 ：標量子查詢 可以使用 = < > != 一列值 ：列子查詢in滿足集合中任意一個條件即可not in不滿足集合中的條件=any相當於in<any小於集合中的最大值>any大於集合中的最小值!=any不等於集合中的值=all等於集合中的所有值<all小於集合中的最小值>all大於集合中的最大值!=all相當於not in 一行值需要加（欄位1,欄位2...）欄位個數與子語句查詢的結果相關 多行多列需要為該表添加別名 ⊙⊙內建函數 數學方法floor(x):返回x的整數部分round(x,y)四捨五入，y，保留了的小數位元。字串length(str):求出字串的長度，以位元組為單位。 substring(str,b,e):b下標（從1開始）。b:開始位置 e:截取個數substr(str,x):從下標x開始截取。 lower(str)：轉小寫 upper(str):轉大寫trim(str):去除所有空格ltrim(str):去除左邊的空格 rtrim(str):去除出右邊空格時間日期now():當前系統時間date_format();時間的格式化%Y:四位元的年 %y:二位元的年%M:英文月份   %m:數位月份dayname():返回當前日期的星期幾dayofweek():返回當月中，每周的第幾天，結果為1-7，從周日開始算。dayofyear():返回一年中的第多少天。dayofmonth():一個月中的第多少天datediff(date1,date2): 計算兩個日期之間的時間差，單位是天。 ⊙⊙串連查詢 ：多表的操作 內串連  inner join（inne可以省略）on    表示串連時就判斷，不產生笛卡爾集        左表名 inner join 右表名 on 條件where 表示先產生笛卡爾集，在進行過濾        左表名 inner join 右表名 where 條件using 前提 擁有相同的欄位名        左表名 inner join 右表名 using（欄位） 外連結  outer join（outer可以省略）外串連必須添加條件左串連  沒有串連成功，左表資料保留，右表用空值代替        左表名 left outer join 右表名 on/using 條件右串連  沒有串連成功，左表用空值代替，右表資料保留        左表名 right outer join 右表名 on/using 條件 全串連 full joinmysql暫不支援可以使用   （左串連）union（右串連）  實現相同效果 自然串連 natural joinnatural join 有相同欄位，類似於usingnatural left join 有相同欄位，類似於左串連的usingnatural right join 有相同欄位，類似於右串連的using 笛卡爾串連串連時沒有添加條件一張表的資料與另外一張表的所有資料連線

MySQL資料類型/屬性/增刪改查（14）