mysql操作常用命令语句学习笔记（有点杂）

学习mysql时全部用命令行熟练命令，安装mysql后会自动安装mysql客户端的命令行。打开输入安装时的密码即可使用

show databases;  

use database_name;

show tables;

create database database_name;

drop database database_name;

select查询显示

select version();

select now();

selcet database();

select user();

select current_date();

select * from table_name;

select * from table_name where name="lisi";

desc table_name;显示表结构

F7找到最近的命令

建立一个mysql新账号

grant all on table_name.*  to  "new"@"localhost" identified by "19890323"

1.create database xiao;

2.use xiao;

3.show tables;

4.create table student(id int(10) primary key auto_increment,name varchar(30),age tinyint(2))

5.show tables;

6.select * from student;

7.desc student;

8.insert into student (name,age)  values("zhangsan",22);

7.drop table student;

数据类型定义与选择

1.create database xiao default character set utf8; 设定库字符集utf8

2.use xiao;

3.create table user(id int(10) unsigned primary key auto_increment,name varchar(60),age tinyint(2)) default character set utf8;表字符集

4.desc  user; 显示表的结构 插入数据时用就近的字符集

5.最好指定字符集 不指定的话使用默认字符集 

6.status; 查看数据库服务器版本 ，字符集，当前数据库名称，当前用户，端口号，连接ID等。

数据类型 

整型           十进制 （常用）十六进制（0x） 八进制（0）

浮点数       2333.2333

字符串       “xiaoxiao”  “” 

null            不确定值

类型                   大小                     范围（有符号的）                   范围（无符号的）              用途

tinyint               1字节                   -128-127                                  0-255                                  小整数

smallint           2字节                   -32768-32767                        0-65535                              大整型

mediuint          3字节                                                                                                                  大整型

int                      4字节                                                                                                                 大整型

bigint                 8字节                                                                                                                 超大整型

在可满足范围内，选最小的，节省空间。

create table c(id tinyint unsigned);

desc c;

insert  into  c(id)  values(1);   插入字段

select * from c;

create table f(price int(10));

desc f;

insert into f(price) values(100);

insert into f(price) values(3988.655);  小数丢失 应用浮点数

浮点数据类型

单精度浮点数

float                 4字节     -3.40E+38         -1.175E-38        0      1.175E-38                   3.40E+38

double             8字节   -1.79E+308       -4.94E-324         0      4.94E-324                  1.79E308

decimal  

表示准确值，而非近似的。一般来保存货币。

decimal（M,N）   M: 显示5位     N:显示小数位数  

create table g(price decimal(5,2));

insert into g(price) values(123.22);

select * from g;

alter table g modify price decimal(10,2);

insert into g(price)  values(4333.66);

select * from g;

delete from g;     删除表中的记录

4.mysql查询   select语句基础重点

create database webclass default  character set utf8; 建立库，指定字符集，建立的库的不必须指定字符集，建立表的时候指定就可以，采用最近的。

use webclass;

create table stu(id int(10) primary key auto_increment,sname char(30) not null,sex tinyint(1) default 0,qq varchar(255));

desc stu;        显示表的结构

insert into stu(sname,sex,qq)  values("李四"，1，3777818291);

insert into stu(sname,sex,qq)  values("李玉"，0，8238182291);

select * from stu;        *表示通配符 全部的 读取表内所有内容  查询语句  不仅可以显示表 也可以显示其他信息。 可以理解为一种输出

select version();          版本号

select database();       当前数据库

set @$=2;  select @$*200;    进行计算。

*笼统返回所有信息，用来看字段信息，不能指定字段出现顺序，还必须把所有字段都返回回来。不需要，浪费带宽，不灵活。非常不建议使用，实际开发很少用。

select后面如果不用*，可以选择某些字段。

select sname,sex from stu;  只返回某些字段

select  * from stu where sname=“lisi”;  找出李四 显示所有字段 按照给定条件查找显示所有字段 先查条件 后执行输出

select * from stu where sname like “li%”;  找出所有姓李的 显示所有字段 like关键字  模糊查询 

insert into stu(sname,sex,qq) values("zhangyumin",0,87312871);

select   id,sname,sex   from stu where sname like "%yu%";   姓名里包含yu的，只取三个选定的字段。

select id,sname,sex  from stu;

sex用1,0表示要转化成文字，可以在其他程序里转，也可以在mysql里。

select  if(sex,"男生","女生"),sname,sex from stu;     条件判断运算符 三元表达式

上述性别位置的名字很不便，可以用关键字as设置别名。

select if(sex,"男生","女生") as stusex,sname,sex from stu;

当多个表进行关联查询时，会有同名字段，为解决这个问题，也会用到别名。

有两个限定条件进行查询时，用and逻辑与运算符。

select sname,sex from stu where sname like "li%" and sex=0;

有两个或条件进行查询时，用or逻辑或运算符。

select sname,sex from stu where sname like “li%” or sex =0;

把查询的结果组合成一个字符串  用concat关键字  信息头太长了 用别名stuinfo代替

select concat("姓名：",sname,“性别：",sex," QQ",qq)  as stuinfo from stu;

select concat("姓名：",sname,“性别：",if(sex,"男","女"),"QQ",qq)  as stuinfo from stu;

五

追加字段生日birday，date类型。

alter table stu add birday date;

select * from stu;

update stu set birday="1990/2/23"; 更新表 这句会导致所有记录都变成这个生日 要加上条件

update stu set birday="1990/2/23" where id=1; 通过条件约束只更新一条记录。

类似的设置其他几个人的birday

再显示一下

select * from stu;  查询显示所有记录的所有字段。

读取数据库，一般不会取全部：硬盘读取开销考虑，网络带宽考虑，实际需求。

学习取一部分数据，*是通配符，表示取全部字段，这时也要考虑节约概念，只取有需求的字段。

只取两条

select * from stu limit 2;

先排序再取 默认小到大

select * from stu  order by id limit 2;

指定降序

select * from stu order by id  desc;

select * from stu order by id desc limit 2;

指定升序

select * from stu order by id asc limit 2;

按照出生日期排序从早到晚

select * from stu order by birday;

查找年龄最大的学生 limit有一个参数 表示从头开始取的记录个数

select * from stu order by birday asc  limit 1;

查找年龄最小的学生

select * from stu order by birday desc limit 1;

查找年龄第二大的学生 

limit用两个参数 第一个表示起始点（索引从0开始）  第二个参数表示取的个数

select sname,birday from stu order by birday asc limit  1,1;

查询显示年龄最大的两个记录

select sname,birday from stu order by birday asc limit 1,2;

order与limit配合使用，先排序再查询记录。order在limit截取之前。

下面先按照升序排列，再将第三条记录的年龄改成与第二条一样。

select * from stu order by birday asc;

update stu set birday="1989/2/2" where id=1;

再来查找年龄最大的两个人

select * from stu order by birday asc limit 2

此时年龄相同的两个人只出现了一个。该怎么操作呢？

 select * from stu where birday<="1989/2/2";  得到正确结果

但是实际中不能知道第二位的实际年龄，怎么办呢？通过子查询实现。

select * from stu where birday<=(select birday from stu order by birday asc limit 1,1);

得到我们的学生都是哪年的

select  year(birday) as year from stu;

结果是每条记录的出生年都出来了，若数据多，会更多，相同的只需要一个即可。

过滤掉重复内容，加一个关键字distinct即可，表示只想得到不重复的内容。

select distinct year(birday) as "学生出生年份" from stu;

复习 追加字段 limit 一个 两个参数   limit要指定排序  遇到同一天生日的处理 

6 字符集 字符串处理 utf8 gbk 

处理字符串方式有两种

二进制字符串类型

binary,varbinary,blob

声音、图像等二进制数据，不建议把声音图像在表里，建议把地址保存在表里。

与字符集无关的

oxaa oxbb 

非二进制字符串类型

char,varchar,text

六、

字符集utf8 gbk 
排序 校验 比较


1）二进制字符串  以字节进行保存
包含binary varbinary blob字段类型  binary(n)以字节为单位
比如声音、图像等二进制数据，不建议保存到表里，而是把地址以字符串形式保存到表里。
但是也可以存储，字段要指定为二进制字段类型，二进制字段类型与字符集无关的。
0xaa 0xbb 
若不指定文件头header("content-type:image/jpeg");
会把图像二进制流当成普通文本显示，凡是文本都有字符集的概念，会以字符集处理文本，强制转换成字符集，在客户端会看到乱码


2）非二进制字符串    文章 字符 汉子 拼音等标准字符  varchar(3) 以字符个数为单位
包含char varchar text字段类型   都有字符集（utf8 gbk gb2312 big5）和字符集校对的规则



字符集的概念


一堆字符的集合
世界上有成百个字符集。
中国常用字符集种类：gbk（简体 繁体） gb2312（简体） utf8 big5（繁体）
每种包含的文字数量、符号数量、各语言字符是不一样的，每种字符集都是一个仓库，包括汉字、字母等。
计算机在底层认识的只是二进制流，汉字在字符集中保存的都是二进制的形式，比如0xfe和oxfa对应简，
通过输入法输入的内容最终转化成二进制数据，然后到字符集仓库中取出这个字符，通过图像技术呈现给我们。


有些特殊的字没有办法输入，就是当时的字符集中没有这个字。
根据开发场合选择合适的字符集，若选择字符集类型不当，一些特殊的字符就无法存入。




gb2312编码： 简体字字符集，较老的字符集规范，包括6700多个汉字，还有罗马字符，特殊字符，俄文字符等。
以两个字节进行存储，输入一个汉字，在硬盘占用2个字节空间，没有考虑繁体字。


big5编码： 繁体字字符集，台湾和香港使用繁体，13000多个汉字，也叫大五码。一个字符保存成也是两个字节。


gbk编码 ：21000多个汉字，中国内地编写的编码库，简体+繁体，融入了繁体到简体中，还有日文片假名，俄文符号等。
大部分操作系统都是使用gbk编码，比如windows 98，windows XP,Windows 7等。一个字符保存也是两个字节。


unicode编码（万国码）：是国际标准化组织制定的一套涵盖世界上所有语种，所有符号的编码方案。


utf8字符集编码（万国码）：基于unicode，可以在一个页面中体现多个语种多个国家的文字内容。长度可变，1-3个字节，
 a：一个字节  盾：3个字节， 占用空间变大了。



查看mysql支持哪些字符集：
show character set;
use webclass;
字段的默认字符集是utf8
create table demo(name varchar(30) character set utf8,name2 varchar(30) character set gbk);
show create table demo;
show create table demo\G
insert into demo(name,name2) values("盾","盾");
select * from demo;
select length(name),length(name2) from demo;查看一下两个字段的长度 第一个是3个字节 第二个是2个字节
如果不需要多语言支持，选择gbk即可。
insert into demo(name,name2) values("a","a");
select * from demo;
select length(name),length(name2) from demo;发现插入字母时的长度都是1个字节。没有差异。



函数 length()读取字段长度 单位是字节个数。


函数char_length()不考虑字节，只读取有几个字符个数。




非二进制字符集校对规则 用来排序和比较使用的
show character set;
第三列是校对规则  utf8_general_ci表示不区分大小写的比对 utf8_bin区分大小写的 
有很多种校对规则，
show collation;显示所有字符集的校对规则，一般选择通用的。


第一个字段区分大小写 第二个字段不区分大小写。
create table demo2(name varchar(30) character set utf8 collate utf8_bin,
                   name2 varchar(30) character set utf8 collate utf8_general_ci);
show create table demo2;
insert into demo2(name,name2) values('a','a'),('b','b'),('A','A');  插入三条数据
select * from demo2;
select * from demo2 where name2='a';发现第一条和第三条记录都被查询出来了，因为第二个字段不区分大小写。
select * from demo where name='a'; 只出现严格等于a的记录。一般用不区分大小写的。


排序
select * from demo2 order by name2;  A和a到了一起。name2字段不区分大小写
select * from demo2 order by name;


再插入一条记录
insert into demo2(name,name2) values("B","B");
select * from demo2;
select * from order by name
select * from order by name2;




create table demo3(name binary(3),name2 varchar(3)) default character set utf8; binary(3) 表示3个字节 varchar(3) 表示3个文字
show create demo3;
insert into demo3(name,name2) values("a","a"); 可插入字母
select * from demo3;
尝试插入中文
insert into demo3(name,name2) values("后"，"后"); 也能成功插入
插入两个字
insert intodemo3(name,name2) values("后盾"，"后盾");发现插入不成功
desc demo3;



字段指定了字符集，没有指定校对规则，依据默认的校对规则。
如果字段制定了校对规则，没有指定字符集，使用校对规则的字符集。
如果字段没有指定校对规则和字符集，寻找表的字符集和校对规则。
如果表米有指定，寻找库的字符集。
如果库没指定，使用服务器软件的mysql的默认字符集。


最好在表阶段指定字符集，不要依赖于库。





7.字符集操作原理与乱码解决方案


show character set;查看当前数据库支持的字符集
show collation; 查看字符集校对规则
show variables like "%character"; 查看字符集环境变量like模糊查询 %表示通配符
show variables like "%collation%"; 查看有关校对规则的环境变量
有一些字符集不需要校对规则 校对规则用来排序和比较




character_set_server   默认操作字符集


建库的时候是否制定字符集
create database t;
show create database t;
create database y default character set gbk;
show create database y;


寻找层级关系：字段 表 库 mysql服务器


create database t;  库 表 字段都没有指定字符集
use t;
create table user(name char(30));
show create table user;  发现使用mysql默认字符集 这种方式不好 最晚在建立表的时候就指定字符集




character_set_database;当前数据库的字符集 可变的
show variables like "%character%";
create database u default character set gbk;
use u;
show variables like "%character%";




character_set_system 系统元数据字符集 没有必要改变
drop database if exist u; 库的名称u的字符集
create table user(name char(30) default character set gbk) defaut character set utf8;
select * from user;  这句命令的字符集  函数的字符集


character_sets_dir  mysql字符设置目录
character_set_filesystem 文件系统字符




用于处理客户端与数据库的结合交互
character_set_client      客户端的字符集 发送脚本给数据库服务器的理解为客户端  
character_set_connection  连接字符集
character_set_results     返回结果的字符集


这里字符集转换发送的理解欠缺
初学 
set names gbk; 不好的方式