Mysql數(shù)據(jù)類型與CRUD操作詳細講解

更新時間：2022年10月19日 10:36:20 作者：顧輕舟。

這篇文章主要介紹了Mysql數(shù)據(jù)類型與CRUD操作，文中通過示例代碼介紹的非常詳細，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)吧

基本數(shù)據(jù)類型

整數(shù)：可選擇unsigned修飾

intyint 8位（-128 - 127）

smallint 16位（-32768 - 32767）

mediumint 24位（-8388608 - 8388607）

int 32位大約正負21億

bigint 64位

實數(shù)（帶有小數(shù)點）：使用標(biāo)準的浮點運算進行近似計算

float 4個字節(jié)

double 8個字節(jié)

decimal 最多允許65個數(shù)字

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

字符串

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

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

varchar 變長字符串

??????比定長類型節(jié)約空間
但是ROW_FOMAT=FIXED每行使用定長
適用場景：字符串的最大長度比評估長度大很多，列的更新較少
缺點：頻繁修改，且字符串的長度變化大時，可能出現(xiàn)頁分裂
不要盲目的給過大的長度
在臨時表或排序時可能遭遇最大長度分配內(nèi)存問題

Text、Blob

1.都為存放很大的數(shù)據(jù)而設(shè)計

2.與其他數(shù)據(jù)不同，都作為獨立的對象存儲

3.當(dāng)值太大時，使用外部存儲區(qū)存儲，每行只要使用1-4字節(jié)存放一個指針

text存儲字符數(shù)據(jù)：

tinytext
smalltext
text
mediumtext
longtext

Blob存儲二進制數(shù)據(jù)：

tinyblob
smallblob
blob
mediumblob
longblob

日期時間

datetime

精度：秒
與時區(qū)無關(guān)，8個字節(jié)存儲空間
范圍：1001 至 9999 年

timestamp

保存1970年1月1日午夜以來的秒數(shù)
占用4個字節(jié)存儲空間
范圍：1970年至 2038年
與時區(qū)有關(guān)
默認為NOT NULL
通常盡量使用timestamp
精度：秒

date

yyyy-MM-dd

time

HH:mm:ss

選擇標(biāo)識符

用來進行關(guān)聯(lián)操作
在其他表中作為外鍵
整型通常是標(biāo)識列的最好選擇
相關(guān)的表中使用相同的數(shù)據(jù)類型
盡量避免字符串作為標(biāo)識列，尤其是隨機生成的字符串，（如：uuid）導(dǎo)致insert與select都很慢
插入值被隨機寫到索引的不同位置，insert慢，容易導(dǎo)致頁分裂，磁盤隨機讀取
邏輯上相鄰的行被分布在磁盤和內(nèi)存的不同地方，select慢
使mysql查詢緩存失效
如果需要存儲uuid，則應(yīng)將“-”去除

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

邏輯上相鄰的行被分布在磁盤和內(nèi)存的不同地方，select慢使mysql查詢緩存失效如果需要存儲uuid，則應(yīng)將“-”去除）

數(shù)據(jù)庫命令

創(chuàng)建數(shù)據(jù)庫：

create database 數(shù)據(jù)庫名
create database if not exists 數(shù)據(jù)庫名 default charset utf8 collate utf8_general_ci;
//默認的數(shù)據(jù)庫編碼集：utf8
//collate表示校驗規(guī)則為utf8_general_ci
	//常用排序類型
		//utf8_general_cs（區(qū)分大小寫）
		//utf8_genera_ci（不區(qū)分大小寫）

查看所有數(shù)據(jù)庫：

show databases

刪除數(shù)據(jù)庫：

drop database 數(shù)據(jù)庫名

注意：刪除數(shù)據(jù)庫是一個危險操作，如要刪除建議先備份

建表與約束

建表

命令格式：

create table 表名稱(

列名稱1 數(shù)據(jù)類型not null,

列名稱2 數(shù)據(jù)類型,

列名稱3 數(shù)據(jù)類型,

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

)

示例：

create table t_student
(
   sid int not null comment '學(xué)號',
   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 '學(xué)生信息表';

約束

主鍵約束：

primarykey

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

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

sid int not null comment'學(xué)號',

外鍵約束：

create table t_score
(
   id int not null comment'記錄流水號',
   sid int not null comment'學(xué)號',
   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為本表的外鍵，關(guān)聯(lián)t_student表中的的sid主鍵，on delete restrict on update redtrict說明在本表有數(shù)據(jù)的情況下，主表的關(guān)聯(lián)鍵不能刪除或更新。

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

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

唯一約束：uniquekey約束名 (字段)

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

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

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

默認值約束：default

基本數(shù)據(jù)操作（CRUD）

數(shù)據(jù)準備

create database db_t281
use db_t281
-- 1.學(xué)生表-t_student
-- sid 學(xué)生編號,sname 學(xué)生姓名,sage 學(xué)生年齡,ssex 學(xué)生性別
create table t_student 
(
	sid int not null  auto_increment comment '學(xué)號',
	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 , '孫風(fēng)' , '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 , '李衛(wèi)');
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 , '數(shù)學(xué)' , 1);
INSERT INTO t_course VALUES(3 , '英語' , 3);
-- 4.成績表-t_score
-- sid 學(xué)生編號,cid 課程編號,score 成績
CREATE TABLE t_score 
(
	sid INT NOT NULL COMMENT '學(xué)號，外鍵',
	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;

數(shù)據(jù)表如下：

t_student學(xué)生表 t_teacher教師表

t_course課程表 t_score成績表

1）查詢" 1 "課程比" 2 "課程成績高的學(xué)生的信息及課程分數(shù)

 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 "課程的學(xué)生信息

//方法一
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，課程名稱，最高分，最低分，平均分，選修人數(shù)，及格率，中等率，優(yōu)良率 2）優(yōu)秀率及格為>=60，中等為：70-80，優(yōu)良為：80-90，優(yōu)秀為：>=90 3）要求查詢結(jié)果按人數(shù)降序排列，若人數(shù)相同，按課程號升序排列

SELECT t2.cid '課程ID', 
       t2.cname '課程名稱',
       MAX(t1.score) '最高分',
       MIN(t1.score) '最低分',
       ROUND(AVG(t1.score), 2) '平均分',
       COUNT(t1.sid) '選修人數(shù)',
       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) '優(yōu)良率',
       ROUND(SUM(CASE WHEN t1.score >= 90 THEN 1 ELSE 0 END)/COUNT(t1.sid), 2) '優(yōu)秀率'
 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;

到此這篇關(guān)于Mysql數(shù)據(jù)類型與CRUD的文章就介紹到這了,更多相關(guān)Mysql數(shù)據(jù)類型內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: