mysql，資料型別與表操作

一、mysql基本認知

創建用戶

create host aa identified with mysql_native_password by '';

修改用戶權限

alter user root@'localhost' identfied  with mysql_native_password by '';

/*查看資料庫*/
/* 1. 注釋 - 為單行注釋 多行注釋
2. 不區分大小寫，但有習慣性將默認的關鍵字為大寫
3. 每個陳述句結尾建議有分號
4. mysql中使用 反引號為兼容性操作*/
-- 查看所有資料庫
SHOW DATABASES;
-- 查看資料庫名稱包含db
SHOW DATABASES LIKE 'db%';
-- 查看當前登錄用戶轉為使用的庫
SELECT DATABASE(),USER(),NOW(),VERSION();
-- 查看建立資料庫的陳述句代碼
SHOW CREATE DATABASE d1;
SHOW CREATE DATABASE mysql;

-- 將資料庫的字符集修改
ALTER DATABASE t CHARACTER SET utf8mb4；

二、創建資料庫

-- 創建資料庫沒有指定編碼格式
CREATE DATABASE d1;
-- 指定編碼格式
CREATE DATABASE d2 DEFAULT CHARACTER SET utf8;
-- 如果不存在的資料庫則建立資料庫，不報錯，
CREATE DATABASE IF NOT EXISTS `d3` CHARSET utf8;

三、洗掉資料庫

-- 洗掉資料庫
DROP DATABASE d1;
-- 資料庫不存在不報錯
DROP DATABASE IF EXISTS `d2`;
DROP DATABASE d3;

-- drop，truncate，delete區別

    1、drop (洗掉表)：洗掉內容和定義，釋放空間，簡單來說就是把整個表去掉.以后要新增資料是不可能的,除非新增一個表，

       drop陳述句將洗掉表的結構被依賴的約束（constrain),觸發器（trigger)索引（index);依賴于該表的存盤程序/函式將被保留，但其狀態會變為：invalid，

    2、truncate (清空表中的資料)：洗掉內容、釋放空間但不洗掉定義(保留表的資料結構)，與drop不同的是,只是清空表資料而已，

       注意:truncate 不能洗掉行資料,要刪就要把表清空，

    3、delete (洗掉表中的資料)：delete 陳述句用于洗掉表中的行，delete陳述句執行洗掉的程序是每次從表中洗掉一行，并且同時將該行的洗掉操作作為事務記錄在日志中保存

       以便進行進行回滾操作，

       truncate與不帶where的delete ：只洗掉資料，而不洗掉表的結構（定義）

    4、truncate table 洗掉表中的所有行，但表結構及其列、約束、索引等保持不變，新行標識所用的計數值重置為該列的種子，如果想保留標識計數值，請改用delete，

       如果要洗掉表定義及其資料，請使用 drop table 陳述句，  
    5、對于由foreign key約束參考的表，不能使用truncate table ，而應使用不帶where子句的delete陳述句，由于truncate table 記錄在日志中，所以它不能激活觸發器，

    6、執行速度，一般來說: drop> truncate > delete，

    7、delete陳述句是資料庫操作語言(dml)，這個操作會放到 rollback segement 中，事務提交之后才生效；如果有相應的 trigger，執行的時候將被觸發，

             truncate、drop 是資料庫定義語言(ddl)，操作立即生效，原資料不放到 rollback segment 中，不能回滾，操作不觸發 trigger， 

四、查看字符集資訊

-- 查看支持mysql的所有字符集
SHOW CHARSET;
-- 查看當前mysql開發環境的相關字符集資訊
SHOW VARIABLES LIKE 'char%';

五、資料型別

1.nuymeric數字型別

整數：tinyint smallint mediumint int bigint;

小數： float double decimal(p,s) numeric(p,s)

utf8編碼的varchar

　　Mysql記錄行資料是有限的，大小為64k,即65535個位元組，而varchar要用1-2位元組來存盤欄位長度，小于255的1位元組，大于255的2位元組，
　　

MySQL5.0.3之前varchar(n)這里的n表示位元組數

MySQL5.0.3之后varchar(n)這里的n表示字符數，

mysql varchar(50) 不管中文 還是英文 都是存50個的，但是一個表中所有varchar欄位的總長度跟編碼有關，如果是utf-8，那么大概65535/3，如果是gbk，那么大概65535/2.

CHAR(M)中的M表示位元組，因為是定長字串，如果實際字串實際長度不足M，會自動補齊右側空格，

varchar定義的長度單位是字符，中英文字幕都被當做一個字符來看，最大長度取決于使用的字符集，（存疑）

text定義的是位元組：

TEXT：65,535 bytes，64kb；
MEDIUMTEXT：16,777,215bytes，16Mb；
LONGTEXT：4,294,967,295 bytes，4Gb；

Mysql 5.0后,英文字符固定都是一個位元組，漢字字符根據編碼方式占不同位元組，Utf-8占3個位元組，gbk占了2個位元組，
　　第一，當編碼方式為utf-8時，varchar存到21845就存不下了.也就是最大長度是21844.根據上面資訊可以推算出 （ 65535-2 ）/3=21844余1

使用mysql存盤字串的時候，varchar型別的長度可能并不能滿足我們的需求，這是我們可以使用text型別

mysql中text 最大長度為65,535(2的16次方–1)字符的TEXT列；

MEDIUMTEXT最大長度為16,777,215；

LONGTEXT最大長度為4,294,967,295；

Text主要是用來存放非二進制的文本

例1：

創建表（欄位使用資料型別）

CREATE TABLE student(
id INT UNSIGNED AUTO_INCREMENT,
NAME VARCHAR(30),
/* age bigint , 不能這樣寫，bigint 是占用8位元組 */
age TINYINT, /* 整數只能存盤整數 18*/
money DECIMAL(10,2),/* 99999999.99 最大數字 如果沒有數字則是 0.00 */
PRIMARY KEY(id)
)

INSERT INTO student VALUE(NULL,'李四',12,3.40);

查詢

 無符號 ： [UNSIGNED]

填充0： [ZEROFILL]

例2：

CREATE TABLE t1(
NAME VARCHAR(10),
n1 INT,
n2 INT UNSIGNED,
n3 INT(3), /*此時3沒有意思沒有任何作用*/
n4 INT(5) ZEROFILL /* 如果內容不夠5位，則補0 */
);
INSERT INTO t1 VALUES('jack',1,2,3,4);
SELECT * FROM t1;

 2.string字串集

定長字串 char()

變長字串 varchar(n) tinytext text mediumtext longtext(4GB字串)

-- char varchar tinytext text mediumtext longtext

-- 演示

create table t2( c1 char(5),/*定長字串，功能是，如果設定5，則插入資料不到被空格也要占用5位元組*/ c2 varchar(5),/*變長字串，如果設定5，最多插5個字符，如果插入2個字，則占用2個字符 的位元組數*/ c3 tinytext, c4 mediumtext, c5 text, c6 longtext );

insert into t2 value('a','b','c','d','e','f');

select * from t2;

-- 建立表

create table user( sno char(5),/*定長*/ name varchar(3)/*變長字串 3個字串不是3個位元組*/ );

insert into user value('20211','李四六');

insert into user value('1','jac');

select name,char_length(name),length(name) from user;

select sno,char_length(sno),length(sno) from user;

3.date、time、datetime日期相關型別

例：

CREATE TABLE t.t5(
NAME VARCHAR(20),
create_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
INSERT INTO t.t5(NAME) VALUES('李五'),('張三');`t5`
UPDATE t.t5 SET NAME='李水' WHERE NAME='張三';
DROP TABLE t.t5;

 4.enum set 復合型別

bit 位型別，只能存盤1 或 0 其實mysql底層使用的是　tinyint unsigned

-- bit 理解（boolean）
create TABLE t.t6(
id int unsigned auto_increment,
name varchar(20),
gender enum('男','女') default '男',
km set('java','phpo','高等數學'),
primary key(id)
)
insert into t.t6 value(null,'張三',null,'java');
select *from t.t6;
create table t.t7(
id int unsigned auto_increment,
name varchar(15),
isstt bit,
primary key(id)
)
insert into t.t7 values(100,'jack',true),(200,'李四',0),(null,'張三豐',3=2);
select *from t.t7;
select *from t.t7 where isstt;
select true=1,true=0,false=0,false=1;

-- mysql5.7 json型別 關于json的一些用法最好去官方手冊https://dev.mysql.com/doc/search/
select json_type('[1,2,3]'); -- json陣列型別
select json_type('{"id":3,"name":"李四"}');-- json 鍵值物件
select json_type('"23"');
select json_array('java','mysql','javascript');
select JSON_OBJECT('id',20,'name','李四');-- 生成物件

create table t.t8(
id int unsigned auto_increment,
dept VARCHAR(30),
worker json,
PRIMARY KEY(id)
)ENGINE=innodb default charset=utf8 auto_increment=202200;

select *from t8;
insert into t8 values(null,'財務部',JSON_OBJECT('id',3,'name','王五','age','19'));
insert into t8 values(null,'財務部',JSON_OBJECT('id',33,'name','andy','love',JSON_ARRAY(100,200,300,400)));
insert into t8 values(null,'財務部',JSON_OBJECT('id',33,'name','andy','love',JSON_ARRAY('zhang','smds')));

select dept,worker->>'$.name',worker->>'$.age' from t8;-- 查詢t8表中部門人員名字和年齡
select dept,worker->'$.love[0]' from t8;

-- 排序
select JSON_EXTRACT(worker, "$.name") from t8 order by worker ->"$.name" asc;

-- 去掉單引號''
select JSON_UNQUOTE(JSON_EXTRACT(worker, "$.name")) from t8 order by worker ->"$.name" asc;

-- 追加模式，在元素后面追加
select JSON_ARRAY_APPEND('["",10,20,30]','$[0]',100);

-- ["id","num"]
select json_keys('{"id":1,"num":100}');

--合并
select JSON_MERGE('{"id":1,"name":"jack"}','{"name":"張三豐"}');

標籤：其他

上一篇：MongoDB 帶訪問控制的副本集部署

下一篇：快取和資料庫一致性問題