百木园-与人分享,
就是让自己快乐。

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;-- 查看数据库名称包含dbSHOW 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\":\"张三丰\"}\');

 

来源:https://www.cnblogs.com/zcxxcvbn/p/15898017.html
图文来源于网络,如有侵权请联系删除。

未经允许不得转载:百木园 » mysql,数据类型与表操作

相关推荐

  • 暂无文章