第05 章 MYSQL常用命令的使用

第05 章 MYSQL常用命令的使用

三.常用命令讲解
3.1 查询数据库
show databases;
show DATABASES like 'it%';
show databases like 'itpux';

3.2 创建数据库
create database itpux2 default character set UTF8 default collate utf8_general_ci;
CREATE DATABASE if not exists itpux3; --如果检索无此数据库,则创建.
create database `111`; --ok,不建议使用数字 .
在数据库目录下,创建目录也是创建数据库.
3.3 查询创建数据库的语句
show create database itpux2;
3.4 更新数据库选项信息
alter database itpux2 character set gbk;
show create database itpux2;
3.5 删除数据库
drop database itpux2;
DROP DATABASE IF EXISTS itpux1;
DROP DATABASE `111`;
show databases;
3.6 mysqladmin工具
mysqladmin create itpux1 -uroot -p; --只能创建,不能删除.
mysqladmin show databases -uroot -p;
3.7 创建目录也是创建数据库
#cd /mysql/data/3306/data
#mkdir itpux1
mysql>show databases;
#rm -rf itpux1;

3.8 查询数据库
use db_name
use itpuxdb;
select * from bm;
SELECT * FROM itpuxdb.bm;
SELECT * FROM itpux.dept;

四.数据类型
4.1 数值类型

---科学计数法(E),小数点移动几位
insert into itpuxf2 value(0.1234E2,0.12333E3);

4.2 日期类型
1)Timestamp（年月日时分秒）
create table itpuxd2(t timestamp);
select * from itpuxd2;
insert into itpuxd2 values('20181020211700'); //推荐使用
insert into itpuxd2 values('2018-10-20 21:18:18'); //推荐使用

--+0 查看时间戳，显示整数
select t+0 from itpuxd2;

2)Date（年月日）
//显示结果全部为2018-03-15
create table itpuxd3(t date);
select * from itpuxd3;
insert into itpuxd3 values('20180305'); //推荐使用
insert into itpuxd3 values('2018-03-05'); //推荐使用
insert into itpuxd3 values('2018:03:05');
insert into itpuxd3 values('20180305100303');

3)Datetime(年月日时分秒）
//创建一个表格
create table itpuxd1(mydate datetime);
select * from itpuxd1;
//插入方法三种结果一样，但不能超过范围。
insert into itpuxd1 values('20181015231008'); //推荐使用
insert into itpuxd1 values(20181015231008); //推荐使用
insert into itpuxd1 values('2018-10-15 23:10:08'); //推荐使用
select * from itpuxd1;

insert into itpuxd1 values(); //插入空值，显示为null
insert into itpuxd1 values(0); //error
insert into itpuxd1 values('10:10:10'); //error
insert into itpuxd1 values('2018-10-18'); //自动补齐时间

4)Time（时分抄）
D HH:MM:SS
D 代表天，最大可以是34天，代表过去多少时间；
create table itpuxd4(t time);
select * from itpuxd4;
insert into itpuxd4 values('10:30:30'); //推荐使用
insert into itpuxd4 values('5 10:30:30');
5)Year（年）YYY

create table itpuxd5(y year);
select * from itpuxd5;
insert into itpuxd5 values(2018); //推荐使用
insert into itpuxd5 values(2016);
insert into itpuxd5 values(1823); --error,超过范围

4.3 字符类型
1)*Varchar（变长，需要1个字节来保存总长度，故长度为实际字节长度+1）
varchar(m),m表示字符可变长度65535，需要1~2个字节来保存信息，超过255的长度就是2个字节来保存长度。

utf8:一个字符占用3个字节，65535个字节/3- 2个字符=21843个字符。
gbk:一个字符占用2个字节，65535/2=32767字符，另需1至2个存储长度信息，故最大为32765；
故输入字符时，不能超过此长度。
create table itpuxz2(c varchar(65535)); --error
create table itpuxz2(c varchar(21845)); --error
create table itpuxz2(c varchar(21844)); --OK

select * from itpuxz2;
insert into itpuxz2 values('YES');
insert into itpuxz2 values('YES '); --后面空格将删除
insert into itpuxz2 values(' YES'); --前面空格将保留

2)char(m)（定长）,m表示字符固定长度，最长255。
create table itpuxz1(c char(255));
select * from itpuxz1;
insert into itpuxz1 values('YES');
insert into itpuxz1 values('YES '); --后面空格将删除
insert into itpuxz1 values(' YES'); --前面空格将保留

3)Binary（视频、照片等二进制文件存储，在数据库中无法查看，通过应用软件查看）

4）Varbinary

5)blob
存储二进制数据，如图片、视频等。存储和开销与text类型一样，分为四种：
tinyblob:256 bytes,1字节开销
blob:65536 bytes(即64K),2个字节开销
mediumblob:最大16M，相当于16777215bytes ，3字节开销
longblob:最大4GB，4字节开销

6)text
text和blob的区别：blob用于保存二进制数据，text保存字符数据。
text和char/varchar区别：text不需要指定存储长度；
存储长度：1字节至4GB，分成四类
tinytext:256 bytes,1字节开销
适用场景：文章摘要类。
create table text1(id int,name tinytext);
text:65536 bytes(即64K),2个字节开销
适用场景：文章的正文，最多存储65534个字符。

mediutext :最大16M，相当于16777215bytes ，3字节开销
适用场景：存储白皮书、书籍文本类。

longtest ：最大4GB，4字节开销
适用场景：


7)Enum（权举，用于投票类）
最多65535个枚举项，2个字节的消耗
适用场景：单项选择填
create table itpuxe1(e enum('Y','N'));
select * from itpuxe1;
insert into itpuxe1 values('Y');
insert into itpuxe1 values('N');
insert into itpuxe1 values('Y1'); --error
insert into itpuxe1 values('1');
insert into itpuxe1 values('2');
insert into itpuxe1 values('3'); --error
select e+0 from itpuxe1; --可以转换成数字


8)Set（集合）
集合，相当于多个选择题
create table itpuxs1(s set('A','B','C','D'));
insert into itpuxs1 values('A');
insert into itpuxs1 values('A');
insert into itpuxs1 values('B');
insert into itpuxs1 values('A,C');
insert into itpuxs1 values('A,D,C');
insert into itpuxs1 values('A,E'); --错误
select * from itpuxs1;

总结：关于char,varchar,text的选用总结
01。经常变化 的字段用varchar
02.知道固定长度的用char
03.能用varchar尽量用varchar
04.超过255字符的只能用varchar或text
05.能用varchar的地方，就不用text.



4.4 JSON类型,轻量级的数据交换型的格式,较于XML语言交换更原生,5.7以后出现解析和JAVA操作.
格式:名称/键值
作用:
1.数据有效性检查.
2.查询性能的提升,不需要遍历所有字符串就能找到数据.
3.支持索引,可能通过虚拟列进行索引.

JSON语法规则:
{"NAME":"itpux","emial":"itpux@qq.com"}
数据在键值对齐,
数据由逗号分隔,
花括号保存对象,
方括号保存数组.

--如何在MYSQL中来使用JSON类型
建itpuxj1,设置lastlogininfo列为json类型

-举例:
create table itpuxj1(id int primary key,name varchar(20),lastlogininfo json);
desc itpuxj1;
INSERT INTO itpuxj1 VALUES(1,"itpux01",'{"time":"2018-03-05 13:00:00","ip":"192.168.26.100","result":"fail"}');
INSERT INTO itpuxj1 VALUES(2,"itpux02",'{"time":"2018-03-05 13:10:00","ip":"192.168.26.101","result":"fail"}');
INSERT INTO itpuxj1 values(3,"itpux03",'{"time":"2018-03-05 13:20:00","ip":"192.168.26.102","result":"fail"}');
INSERT INTO itpuxj1 VALUES(6,'itpux06','{"time":"2018-03-05 13:40:00","ip":"192.168.26.105","result":"fail"}');
insert into itpuxj1 values(4,"itpux04",'{"time":"2018-03-05 13:30:00","ip":"192.168.26.103","result":"fail"}');
select * from itpuxj1;

--json_object()函数,将":"分隔符进行转换
INSERT INTO itpuxj1 VALUES(5,"itpux05",json_object("time",NOW(),"ip","192.168.26.104","result","fail"));

--查询
select lastlogininfo from itpuxj1 where name='itpux01';

--JSONO数据使用-->操作符
表达式为：这个json列-->'$.键' == json_extract(json列,'$.键');

表达式为：json列-->'$.键'
select * from itpuxj1 where lastlogininfo ->'$.time' > '2018-03-01';
select * from itpuxj1 where JSON_EXTRACT(lastlogininfo,'$.time' ) > '2018-03-01';

使用JSON_TYPE()函数返回指定属性对应的类型名字.

select JSON_TYPE(lastlogininfo ->'$.time')from itpuxj1;
select JSON_TYPE(lastlogininfo ->'$.ip')from itpuxj1;
select JSON_TYPE(lastlogininfo ->'$.result')from itpuxj1;
select * from itpuxj1;

可以通过虚拟列来实现和SQL语法一样的快速查询：
alter table itpuxj1 add lastlogininfosult varchar(15) generated alter as (lastlogininfo ->'$.result') virtual; 此处错误，未解决，需要后期处理。
SELECT lastlogininfosult FROM itpuxj1 WHERE NAME='itpuxj01';
SELECT lastlogininfosult FROM itpuxj1 WHERE NAME='itpuxj02';