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日期相关类型

date yyyy-MM-dd 3字节 time hh:mm:ss 3字节 datetime

yyyy-MM-dd hh:mm:ss 8字节

用来存储日期和时间。

格式为”yyyy-mm-dd hh:mm:ss”，在mysq中占8个字节存储范围： 1000-01-01 00:00:00 — 9999-12-31 23:59:59

timestamp

时间戳 yyyy-MM-dd hh:mm:ss 4字节

用来存储时间戳。格式为”yyyymmddhhmmss”，显示为”yyyy-mm-dd hh:mm:ss”。在msql中占4个字节

存储范围为: 1970-01-01 00:00:01 UTC — 2038-01-19 03:14:07

year 用来存储年份数据。格式为”yyyy”，在mysql中占1个字节 存储范围为：1901 — 2155

例：

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 复合类型

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”:”张三丰”}’);

Original: https://www.cnblogs.com/zcxxcvbn/p/15898017.html
Author: 一份人间烟火
Title: mysql数据类型与表操作