Mysql資料型別與CRUD操作詳細講解

2022-10-22 14:01:20

基本資料型別

整數：可選擇unsigned修飾

intyint 8位元（-128 - 127）

smallint 16位元（-32768 - 32767）

mediumint 24位元（-8388608 - 8388607）

int 32位元大約正負21億

bigint 64位元

實數（帶有小數點）：使用標準的浮點運算進行近似計算

float 4個位元組

double 8個位元組

decimal 最多允許65個數位

範例：decimal(5,2)，說明：5位長度，2位小數精度，如果整數部分+2位小數超長，則報錯，如果只是小數部分超出2兩位，則四捨五入到兩位小數

字串

char：定長：msql根據定義字串的長度一次分配足夠的空間

適用場景：較短的字串，且所有值接近同一長度

varchar 變長字串

比定長型別節約空間
但是ROW_FOMAT=FIXED每行使用定長
適用場景：字串的最大長度比評估長度大很多，列的更新較少
缺點：頻繁修改，且字串的長度變化大時，可能出現頁分裂
不要盲目的給過大的長度
在臨時表或排序時可能遭遇最大長度分配記憶體問題

Text、Blob

1.都為存放很大的資料而設計

2.與其他資料不同，都作為獨立的物件儲存

3.當值太大時，使用外部儲存區儲存，每行只要使用1-4位元組存放一個指標

text儲存字元資料：

tinytext
smalltext
text
mediumtext
longtext

Blob儲存二進位制資料：

tinyblob
smallblob
blob
mediumblob
longblob

日期時間

datetime

精度：秒
與時區無關，8個位元組儲存空間
範圍：1001 至 9999 年

timestamp

儲存1970年1月1日午夜以來的秒數
佔用4個位元組儲存空間
範圍：1970年至 2038年
與時區有關
預設為NOT NULL
通常儘量使用timestamp
精度：秒

date

yyyy-MM-dd

time

HH:mm:ss

選擇識別符號

用來進行關聯操作
在其他表中作為外來鍵
整型通常是標識列的最好選擇
相關的表中使用相同的資料型別
儘量避免字串作為標識列，尤其是隨機生成的字串，（如：uuid）導致insert與select都很慢
插入值被隨機寫到索引的不同位置，insert慢，容易導致頁分裂，磁碟隨機讀取
邏輯上相鄰的行被分佈在磁碟和記憶體的不同地方，select慢
使mysql查詢快取失效
如果需要儲存uuid，則應將“-”去除

（插入值被隨機寫到索引的不同位置，insert慢，容易導致頁分裂，磁碟隨機讀取

邏輯上相鄰的行被分佈在磁碟和記憶體的不同地方，select慢使mysql查詢快取失效如果需要儲存uuid，則應將“-”去除）

資料庫命令

建立資料庫：

create database 資料庫名
create database if not exists 資料庫名 default charset utf8 collate utf8_general_ci;
//預設的資料庫編碼集：utf8
//collate表示校驗規則為utf8_general_ci
	//常用排序型別
		//utf8_general_cs（區分大小寫）
		//utf8_genera_ci（不區分大小寫）

檢視所有資料庫：

show databases

刪除資料庫：

drop database 資料庫名

注意：刪除資料庫是一個危險操作，如要刪除建議先備份

建表與約束

建表

命令格式：

create table 表名稱(

列名稱1 資料型別not null,

列名稱2 資料型別,

列名稱3 資料型別,

unique(列名稱1[,列名稱2,...,列名稱N])

)

範例：

create table t_student
(
   sid int not null comment '學號',
   sname varchar(60) not null comment '姓名',
   sex tinyint not null default 1 comment '性別：1男， 2女',
   age tinyint not null comment ' 年齡',
   icard varchar(18) not null comment '身份證，唯一約束',
   primary key (sid),
   unique key AK_Key_2 (icard)
) comment '學生資訊表';

約束

主鍵約束：

primarykey

增加主鍵（alter table 表名 add primary key(主鍵名稱)）

刪除主鍵（alerttable 表名dropprimarykey）非空約束：

sid int not null comment'學號',

外來鍵約束：

create table t_score
(
   id int not null comment'記錄流水號',
   sid int not null comment'學號',
   cid int not null comment'課程ID',
   score float comment'成績',
   primary key(id),
   foreign key(sid) references t_student (sid) on delete restrict on update redtrict ,
   unique key ak_key_2(sid, cid)
);
//說明： sid為本表的外來鍵，關聯t_student表中的的sid主鍵，on delete restrict on update redtrict說明在本表有資料的情況下，主表的關聯鍵不能刪除或更新。

增加主鍵（alerttable 表名 add foreign key(外來鍵名稱) references 主表名稱(主鍵名稱)）

刪除主鍵（alerttable 表名drop foreign key約束名）

唯一約束：uniquekey約束名 (欄位)

建立唯一約束：alert table 表名 add unique(列名稱1[,列名稱2,..])

create unique index UserNameIndex on 't_user' ('username')

刪除唯一約束：alerttable 表名dropindex 唯一約束縮影名稱

預設值約束：default

基本資料操作（CRUD）

資料準備

create database db_t281
use db_t281
-- 1.學生表-t_student
-- sid 學生編號,sname 學生姓名,sage 學生年齡,ssex 學生性別
create table t_student 
(
	sid int not null  auto_increment comment '學號',
	sname varchar(40) not null comment '名稱',
	birthday date not null comment '年齡',
	ssex tinyint not null default 1 comment '1男，2女',
	primary key (sid)
);
INSERT INTO t_student VALUES(1, '趙雷' , '1990-01-01' , 1);
INSERT INTO t_student VALUES(2 , '錢電' , '1990-12-21' , 1);
INSERT INTO t_student VALUES(3 , '孫風' , '1990-12-20' , 1);
INSERT INTO t_student VALUES(4 , '李雲' , '1990-12-06' , 1);
INSERT INTO t_student VALUES(5 , '周梅' , '1991-12-01' , 2);
INSERT INTO t_student VALUES(6 , '吳蘭' , '1992-01-01' , 2);
INSERT INTO t_student VALUES(7 , '鄭竹' , '1989-01-01' , 2);
INSERT INTO t_student VALUES(9 , '張三' , '2017-12-20' , 2);
INSERT INTO t_student VALUES(10 , '李四' , '2017-12-25' , 2);
INSERT INTO t_student VALUES(11 , '李四' , '2012-06-06' , 2);
INSERT INTO t_student VALUES(12 , '趙六' , '2013-06-13' , 2);
INSERT INTO t_student VALUES(13 , '孫七' , '2014-06-01' , 2);
-- 2.教師表-t_teacher
-- tid 教師編號,tname 教師名稱
CREATE TABLE t_teacher 
(
	tid INT NOT NULL AUTO_INCREMENT COMMENT '教師ID',
	tname VARCHAR(40) NOT NULL COMMENT '教師名稱',
	PRIMARY KEY (tid)
);
INSERT INTO t_teacher VALUES(1 , '張五哥');
INSERT INTO t_teacher VALUES(2 , '李衛');
INSERT INTO t_teacher VALUES(3 , '年羹堯');
-- 3.課程表-t_course
-- cid 課程編號,cname 課程名稱,tid 教師名稱
CREATE TABLE t_course 
(
	cid INT NOT NULL COMMENT '課程ID',
	cname VARCHAR(50) COMMENT '課程名稱',
	tid INT COMMENT '教師id',
	PRIMARY KEY (cid)
);
INSERT INTO t_course VALUES(1 , '語文' , 2);
INSERT INTO t_course VALUES(2 , '數學' , 1);
INSERT INTO t_course VALUES(3 , '英語' , 3);
-- 4.成績表-t_score
-- sid 學生編號,cid 課程編號,score 成績
CREATE TABLE t_score 
(
	sid INT NOT NULL COMMENT '學號，外來鍵',
	cid INT NOT NULL COMMENT '課程id',
	score decimal(5,2) COMMENT '成績',
	UNIQUE KEY ak_key_sid_cid (sid, cid)
);
INSERT INTO t_score VALUES(1 , 1 , 80);
INSERT INTO t_score VALUES(1 , 2 , 90);
INSERT INTO t_score VALUES(1 , 3 , 99);
INSERT INTO t_score VALUES(2 , 1 , 70);
INSERT INTO t_score VALUES(2 , 2 , 60);
INSERT INTO t_score VALUES(2 , 3 , 80);
INSERT INTO t_score VALUES(3 , 1 , 80);
INSERT INTO t_score VALUES(3 , 2 , 80);
INSERT INTO t_score VALUES(3 , 3 , 80);
INSERT INTO t_score VALUES(4 , 1 , 50);
INSERT INTO t_score VALUES(4 , 2 , 30);
INSERT INTO t_score VALUES(4 , 3 , 20);
INSERT INTO t_score VALUES(5 , 1 , 76);
INSERT INTO t_score VALUES(5 , 2 , 87);
INSERT INTO t_score VALUES(6 , 1 , 31);
INSERT INTO t_score VALUES(6 , 3 , 34);
INSERT INTO t_score VALUES(7 , 2 , 89);
INSERT INTO t_score VALUES(7 , 3 , 98);
select * from t_student;
select * from t_teacher;
select * from t_course;
select * from t_score;

資料表如下：

t_student學生表 t_teacher教師表

t_course課程表 t_score成績表

1）查詢" 1 "課程比" 2 "課程成績高的學生的資訊及課程分數

 SELECT stu.sid,stu.sname,stu.ssex,c1.cid, c1.score, c2.cid, c2.score
   FROM t_student stu
        INNER JOIN  (SELECT t1.sid, t1.cid, t1.score FROM t_score t1 WHERE t1.cid = 1 ) c1 ON stu.sid = c1.sid
        INNER JOIN  (SELECT t2.sid, t2.cid, t2.score FROM t_score t2 WHERE t2.cid = 2) c2 ON stu.sid = c2.sid 
 WHERE c1.score > c2.score

2）查詢同時選修" 1 "課程和" 2 "課程的學生資訊

//方法一
SELECT stu.sid,stu.sname,stu.ssex,c1.cid, c1.score, c2.cid, c2.score
   FROM t_student stu
        INNER JOIN  (SELECT t1.sid, t1.cid, t1.score FROM t_score t1 WHERE t1.cid = 1 ) c1 ON stu.sid = c1.sid
        INNER JOIN  (SELECT t2.sid, t2.cid, t2.score FROM t_score t2 WHERE t2.cid = 2) c2 ON stu.sid = c2.sid
//方法二
SELECT stu.`sid`,stu.`sname`, stu.`ssex`, tmp.c1num, tmp.c2num FROM t_student stu INNER JOIN 
   (
     SELECT t.`sid`, 
            SUM(CASE WHEN t.cid = 1 THEN t.`score` ELSE 0 END) c1num, 
            SUM(CASE WHEN t.cid = 2 THEN t.`score` ELSE 0 END) c2num FROM t_score t GROUP BY t.`sid`
   ) tmp ON stu.sid = tmp.sid AND tmp.c1num > 0 AND tmp.c2num > 0;

3）查詢選修" 1 "課程但沒有選修" 2 "課程的情況

SELECT stu.* FROM t_student stu 
  WHERE stu.sid IN(SELECT t1.sid FROM t_score t1 WHERE t1.cid = 1)
	AND stu.sid NOT IN (SELECT t1.sid FROM t_score t1 WHERE t1.cid = 2)
SELECT stu.`sid`,stu.`sname`, stu.`ssex`, tmp.c1num, tmp.c2num FROM t_student stu INNER JOIN 
   (
     SELECT t.`sid`, 
            SUM(CASE WHEN t.cid = 1 THEN t.`score` ELSE 0 END) c1num, 
            SUM(CASE WHEN t.cid = 2 THEN t.`score` ELSE 0 END) c2num FROM t_score t GROUP BY t.`sid`
   ) tmp ON stu.sid = tmp.sid AND tmp.c1num > 0 AND tmp.c2num = 0;

4）查詢不存在" 1 "課程但存在" 2 "課程的情況

 SELECT t1.sid,t1.cid,t1.score 
    FROM t_score t1 
  WHERE t1.cid = 2 AND t1.sid NOT IN (SELECT t2.sid FROM t_score t2 WHERE t2.cid = 1);

查詢各科成績最高分、最低分和平均分：

1）顯示列：課程ID，課程名稱，最高分，最低分，平均分，選修人數，及格率，中等率，優良率 2）優秀率及格為>=60，中等為：70-80，優良為：80-90，優秀為：>=90 3）要求查詢結果按人數降序排列，若人數相同，按課程號升序排列

SELECT t2.cid '課程ID', 
       t2.cname '課程名稱',
       MAX(t1.score) '最高分',
       MIN(t1.score) '最低分',
       ROUND(AVG(t1.score), 2) '平均分',
       COUNT(t1.sid) '選修人數',
       ROUND(SUM(CASE WHEN t1.score >= 60 THEN 1 ELSE 0 END) / COUNT(t1.sid), 2) '及格率',
       ROUND(SUM(CASE WHEN t1.score >=70 AND t1.score < 80 THEN 1 ELSE 0 END)/COUNT(t1.sid),2) '中等率',
       ROUND(SUM(CASE WHEN t1.score >=80 AND t1.score < 90 THEN 1 ELSE 0 END)/COUNT(t1.sid),2) '優良率',
       ROUND(SUM(CASE WHEN t1.score >= 90 THEN 1 ELSE 0 END)/COUNT(t1.sid), 2) '優秀率'
 FROM t_score t1
      INNER JOIN t_course t2 ON t1.cid = t2.cid
  GROUP BY t2.cid, t2.cname
  ORDER BY COUNT(t1.sid) DESC, t2.cid ASC;

到此這篇關於Mysql資料型別與CRUD的文章就介紹到這了,更多相關Mysql資料型別內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！

Mysql資料型別與CRUD操作詳細講解

目錄

基本資料型別

資料庫命令

建表與約束

建表

約束

基本資料操作（CRUD）

熱門文章