MySQL——表的約束，資料型別，增刪查改

資料型別

數值型別：

• tinyint：1位元組，-128~127，0~255
• smallint：2位元組，-32768~32767，0~65535
• mediumint：3位元組 ，-8388608~8388607 ，0~16777215
• int ：4位元組，-2147483648~2147483647……
• bigint：8位元組

越界測試：

• create table tt1(num tinyint);
• insert into tt1 values(1);//成功
• insert into tt1 values(128);//失敗，越界

在MySQl中，整形可以指定是有符號和無符號的，但是預設是有符號的

可以通過UNSIGNED來說明某個欄位的無符號的

• create table tt2(num tinyint unsigned);
• insert into tt2 values(255);//成功
• insert into tt2 values(-1);//失敗，無符號的

關於unsigned細節

unsigned為無符號，表示非負數，但是在事件使用中會帶來一些負面的影響

建立一個表，倆個欄位都是無符號

• create table tt3(a int unsigned ,b int unsigned);
• insert into tt3 values(1,2);
• select * from tt3;
• select a-b from tt3; //顯示a-b為4294967295
• set sql_mode='NO_UNSIGNED_SUBTRACTION'

注：儘量不要使用unsigned，因為可能會帶來一些意想不到的結果，對於int放不下的資料可能unsigned int同樣放不下，與其如此，還不如直接將int提升為bigint。

bit的使用：

bit[(M)]：位欄位，M表示每個值的位數，範圍從1~64，如果M被忽略，預設為1

• create table tt4(a int ,b bit(8));
• insert into tt4 values(10,10);
• select * from tt4; //會發現，a的資料10沒有出現

注：bit欄位顯示時，是按照ASCII碼對應的值顯示的

• insert into tt4 values（65，65)；
• select * from tt4; // 65 A

小數的基本使用：

1.float[(m,d)][unsigned]；m指定顯示長度，d指定小數位數，佔空間4個位元組

• create table tt6(id int,salary float(4,2));

小數float(4,2)表示的範圍時-99.99~99.99，MySql中儲存至時會四舍五

2.decimal[(m,d)][unsigned]; m指定長度，d表示小數點的位數

• decimal整數的最大位數m為65，支援小數的最大位數d是30，如果d被省略，預設為0，如果m被省略，預設為10
• 看起來和float差不多，但是他們表示的精度不一樣，decimal的精度更高。

字串

• char(L)：固定長度字串，L表示可以儲存的長度，單位是字元，最大長度可以為255
• varchar（L）：可變長度字串，L表示字元長度，最大長度65535個位元組
• 驗證char

create table tt9(id int,name char(2));

insert into tt9 values(100,'ab');

insert into tt9 values(100,'中國')；

//char（2）表示可以存放倆個字元，可以是字母或漢字，但不能超過2個

//char最大為255

• 測試varchar

create table tt10(id int ,name varchar(6));

insert into tt10 values(100,'hello');

insert into tt10 values(100,'我愛你，中國')；

• char和varchar的選擇

1. 如果資料的長度都是一樣的（char），就使用定長，比如身份證，手機號……
2. 如果資料長度是有變化的，就用變長的（varchar），比如名字，地址
3. 定長的磁碟空間比較浪費，但是效率高
4. 變長的磁碟空間比較節省，但是效率低

日期和時間型別：

• fatetime時間格式‘yyy-mmm-dd HH：ii：ss’表示範圍從1000到9999，佔8位元組
• date日期：“yyy-mmm-dd” 佔3位元組
• timestamp：時間戳，從1970年開始的‘yyy-mm-dd HH：ii：ss’格式和datetime完全一致，佔4位元組

//建立一個表

• create table birthday (t1 date ,t2 datetime,t3 timestamp);

//插入資料

• insert into birthday(t1,t2) values('1997-7-1','2008-8-8 12:1:1');

//更新資料

• update birthday set t1=‘2000-1-1’；

列舉和set

• 列舉：enum，列出多個選項但是隻能選其中的一個（單選），處於效率考慮，這些值的時間儲存是“數字”，每個選項對應一個數字，最多65535，新增列舉時，可以直接新增其對應的數字（列舉從1開始）
• set：就是多選，設定時實際儲存的是數字，依次對應“1，2，4，8，16……”最多64個

需求：一個調查表votes，調查人的喜好（登山，籃球，游泳，武術）中去選擇，可以多選

男，女（選項）

• create tables votes（
• username varchar(30),
• hobby set('登山'，‘籃球’，‘游泳’，‘武術’)，
• gender enum（‘男’，‘女’）
• ）；

//插入資料：

• inster into votes values('張三','登山，武術','男');

//查詢語句：

• select* from votes where hobby ='登山';
• //這樣只能查詢出愛好只有登山的人，而有些人的愛好有很多，登山只是其中的一部分，所以就查詢不出來

      要使用find_in_set(sub,str_list)查詢

• select* from votes where find_in_set('登山',hobby);

//如果sub在str_list中，則返回下標，如果不在，返回0

 

表的約束：

空屬性：

• null（預設的）和not null(不為空)
• 資料庫預設的欄位基本都為空，但是實際開發時，儘可能保證欄位不為空，因為資料為空沒辦法參與運算

建立一個班級表，包含班級名和班級所在的教室，如果沒有班級名字，不知道你在哪個教室上課，如果教室名字可以為空，就不知道在哪上課。

• cteate table class(
• class_name varchar(20) not null,
• class_name varchar(10) not null);
• inster into myclass(class_name)values('class1'); //插入失敗，沒有給教室資料

預設值：

• default：某一種資料會經常性的出現某個具體的值，可以在一開始就指定好，在需要真實資料的時候，使用者可以選擇性的使用預設值

注：set和enum不能設定為預設值

• create table tt10（
• name varchar(20) not null,
• age tinyint unsigned default 0,
• sex char(2) defalut '男'）；

列描述：

• 列描述comment沒有實際含義，通過desc看不到註釋資訊，通過show可以看到

zerofill：

主鍵：

主鍵：primary key

• 用來唯一的約束該欄位裡面的資料，不能重複，不能為空，主鍵所在的列是整數型別
• 一張表最多隻能由一個主鍵

建立表的時候直接在欄位上指定主鍵

• create table tt13(
• id int unsigned primary key comment '學號不能為空'，
• name varchar（20）not null
• );

在建立表的時候，在所有欄位之後，使用primary key來建立主鍵，如果由多個欄位多為主鍵，可以使用複合主鍵

• create table tt14(
• id int unsigned,
• course char(10) comment '課程程式碼',
• score tinyint unsigned default 60 comment '成績',
• primary key(id,course));

表建立好以後可以在追加主鍵

• alter into tt13 add primary key(欄位)

主鍵約束：主鍵對應的欄位不能重複，一旦重複，操作失敗

• 刪除主鍵：alter table 表名 drop primary key；

自增長：

auto_increment:對應的欄位不給值，就會自動的被系統觸發，系統會從當前欄位，已經有的最大值+1操作，得到一個新的不同的值，通常和主鍵搭配使用，作為邏輯主鍵

特點：

• 如何一個欄位要做自增長，前提是本身是一個索引（key一欄有值）
• 自增長欄位必須是整數
• 一張表最多隻能有一個自增長

唯一鍵：

unique：一張表中往往有多個欄位需要唯一性，資料不能重複，但是一張表中只能有一個主鍵，唯一鍵就可以解決表中有多個欄位需要唯一約束的問題

• 唯一鍵的本質和主鍵差不多，唯一鍵允許為空，而且可以多個為空，空欄位不做唯一性比較

 

表的增刪查改：

增加：

• 語法：insert into tablename values()

例：建立一張商品表

• create table goods(
• id int unsigned primary key,
• goods_name varchar(50) not null default ' ',
• price float not null defalt 0.0
• ）;

插入倆條記錄：

• insert into goods values(100，' 牛排'，78.5)；
• insert into goods values（100，‘披薩’，27.5）；

1. 插入的資料應用欄位的資料型別相同
2. 資料的大小應在規定範圍內
3. 在values中列出的資料位置必須與加入的列的位置相對應
4. 日期和字元型別應包含在單引號內
5. 插入空值，不指定或insert into table values（null）
6. insert into table values（），（），（）一次性新增多條記錄
7. 如果給表中的欄位新增資料，可以不寫前面的欄位名稱，但是給指定的欄位新增資料，則需要寫欄位名

增加進階：

• 在新增資料的時候，假設主鍵對應的值已經存在了 ：插入失敗
• 當主鍵衝突時，可以選擇以下方式進行處理

 1)更新操作

• insert table into goods values('101','ccc',20.5);

           //提醒新增失敗，id是主鍵，id=101，已經存在，所以不能賦值

• insert table into goods values on duplicate key update good_name='ccc',price=20.5;

2)替換：主鍵如果沒有衝突，就之間插入

• replace into goods values（100，’華為‘，999）；

修改

更新表中的資料

語法：update tbl_name set col_name1=expr1 where 列

例：

• 將所有產品的價格修改為300快

update goods set price=300//沒有條件，整表修改

• 將id為100的產品價格修改為1000

update goods set price=1000 where id=100；

• 將id為101的產品價格增加200塊

update goods set price=price 200 where id=101；

update使用細節：

1. update語法可以用新值更新源有表中的各列
2. set子句指示要修改哪些列和要給予哪些值
3. where語句指示要更新哪些行，如果沒有where語句就跟新所有行
4. 如果需要更新多個欄位，可以通過set欄位1=值1，欄位2=值2

更新還可以限制更新數量

update 表名 set 欄位=值 [where 條件] [limit 更新數量]

例：

goods表中有五條ccc產品，我們希望將前三條改成ddd

• update goods set goods_name='ddd' where goods_name='ccc' limit 3;

刪除：

語法：delete from tlb_name [where condition]

刪除表中id為101的資料

• delete from goods where id=101；

刪除時，可以複製一份表，避免資料刪沒了

1. 複製表結構：create table goods2 like goods；
2. 把goods表中的資料複製到goods2中：insert into goods2 select * from goods；
3. 刪除表中記錄：delete from goods；//刪除表中的資料，但是表的結構還在
4. 使用truncate刪除表中記錄：truncate table goods；//這個指令把真個表記錄刪除

delete和truncate刪除整表的區別：

• 效果一樣，但是truncate速度快
• delete可以帶where條件，刪除更靈活
• delete可以返回被刪除的記錄數，而truncate返回0
• 推薦使用delete

delete使用細節

• 如果不使用where子句，將刪除整個表中所有資料
• delete語句不能刪除某一列的值（可以使用update置null）
• 使用delete語句僅刪除記錄，不刪除表本身（drop table）

select

語法：select */{column1，column2……} from table;

• 指定查詢某些列：查詢id，name，math

selete id,name,math from student;

• *表示查詢所有列（*的效率低，用哪些欄位就取哪些欄位）
• distinct：如果結果中有重複行，就刪除重複行

select distinct math from student；

• select 語句中可以使用表示式對查詢的列進行計算
• select語句中可以使用as對列起別名
  • select column as 名字 from 表；

例：

1.在所有學生總分數上加十分

• select id,name,(math+chinese+english)+10 as total from student;

2.將所有姓唐的學生成績增加60%

• select name ,(math+Chinese+English）*1.6 as total from student where name like '唐%'

select 的where語句

• between…and…：在某區間內；
• in（a,b）:列表中的值
• like，not like ：模糊查詢
• and，or，not ：與，或，非

查詢姓李的學生的成績：

• select * form student where name like '李%';

查詢英語成績大於90分的同學：

• select * from student where English>90;

查詢總分大於200的所有同學

• select name,(math+english+chinese）as 'total' from student where math+english+chinese>200;

查詢姓李並且id>10的同學

• select * from student where name like ‘李%’ and id>10；

查詢英語成績大於語文成績的同學

• select * from student where English>chinese;

查詢總分>200分並且數學成績小於語文成績的姓唐的學生

• select* from student where english+math+chinese>200 and math<chinese and name like '唐%'；

查詢英語成績在80~90之間的同學

• select * from student where english between 80 and 90;

查詢數學成績為89，90，91的同學

• select * from student where math in(98,90,91);

 

select的order by語句

語法：select column1 …from table order by column asc/desc…

• order by指定排序的列，排序可以使用表中的列名，也可以使用別名
• asc，升序（從小到大），desc 降序（從大到小）
• order by 子句應該位於select語句的結尾

對數學成績進行排序：

• select * from student order by math；

對總分進行排序後，按從高到低輸出

• select id，name，math+English+Chinese as total from student order by total desc；

對姓李的學生按成績進行從低到高排序

• select id，name，math+english+chinses as total from student where name like ‘李%’ order by total asc；

 

count：返回某列，行的總數

• 語法：select count(*)/count(列名) from table where condition

例：

統計一個班級有多少學生

• select count（id）from table ；//對某個列名進行統計，不包含null值

統計數學成績大與等於90的學生人數

• select count（*）from student where math>=90;

統計總分大於250的人數

• select count（*）from student where math+e+c>250;
• count(*)會統計一共的記錄數，count（列名）會派出null情況

 

sum：返回滿足where條件的行的和

• 語法:select sum(列名) from table [where…]

例：

統計一個班的數學總成績

• select sum（math）from student；

統計一個班級語文，英語，數學各科的總成績

• select sum（Chinese），sum（math），sum（english）from student；

統計一個班語文，英語，數學的成績總和

• select sum（c+e+m）from student

統計一個班的語文成績 的平均成績

• select sum（chinense）/count（name）from student;

注：sum僅對數值起作用，否則結果無意義

 

avg：返回滿足where條件的一列的平均值

求一個班級的數學平均分

• select avg（math）from student；

求一個班及總分平均值

• select avg（m+c+e） from student；

 

max/min：返回滿足where條件的最大/最小值

• 語法：select max（列名） from table [where…]；

例：

求班級最高分和最低分

• select max（c+e+m），min（c+e+m）from student；

 

group by：對子句進行分組

• 語法：delect column1… from table group by column

顯示一個部門的最高工資和平均工資

• select deptno，max（sal），avg（sal）from EMP group by deptno；

顯示每個部門每個崗位平均工資和最低工資

• select depton，job，min（sal），avg（sal）from EMP group by depton ，job；

顯示平均工資低於2000的部門和他的平均工資

• select depton，avg（sal）from EMP gurop by depton having avg（sal）<2000;

函式：

常用日期函式：

• current_date()：獲取年月日
• current_time():獲取時分秒
• current_timestamp()：獲取使勁戳
• date(datetime)：返回datetime的日期部分
• date_add(date2,interval date)：在date2上加上一個時間
• date_sub(date2, interval date)：在date2上減去一個時間
• datedeff(date1,date2)：倆個日期差（結果是天數）
• now()：當前時間