一、mysql基本认知
创建用户
create host aa identified with mysql_native_password by "";
修改用户权限
alter user root@"localhost" identfied with mysql_native_password by "";
-- 查看所有数据库
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 TINYINT,
money DECIMAL(10,2),
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),
n4 INT(5) ZEROFILL
);
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), c2 varchar(5), 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) );
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 |
|
timestamp |
用来存储时间戳。格式为“yyyymmddhhmmss”,显示为"yyyy-mm-dd hh:mm:ss"。在msql中占4个字节
|
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":"张三丰"}");