My SQL常用操作汇总

写这篇随笔的目的是我发现了在上一篇关于My SQL的随笔中存在一些不严谨的代码问题，在这里再次简单的总结一下并加以改进，以代码为主。

# ！每行命令必须以分号（;）结尾

先通过命令行进入数据库客户端
mysql -h服务端ip地址 -P(大写)服务端使用的端口，一般为3306 -p(小写)
回车之后输入密码，进入

显示所有数据库
show databases;

创建数据库并设置编码
　　- 数据库创建时可以设置字符集以及排序规则
　　- 字符集一般使用utf8的，排序规则一般使用忽略大小写的，其实也不能说是忽略大小写
　　- 它的原理是放进数据库的都转换成大写，然后不管用户输入的大写还是小写都转换成大写再去数据库里查
　　- 所以看起来就相当于是忽略了大小写
　　- 如果不设置中文会乱码
create database 数据库名字 character set utf8;
create database 数据库名字 charset=utf8;

修改数据库编码
alter database 数据库名字 character set utf8;

展示创建数据库的过程
show create database 数据库名字;

使用某个数据库
use 数据库名字;

判断当前在哪个数据库里
select database();

查看该数据库有哪些表
show tables；

创建数据库中的表
create table 表的名字(
字段名字 类型(范围) [约束],
字段名字 类型(范围) [约束],
字段名字 类型(范围) [约束]);

查看某表的字段（属性）
desc 表的名字;
show create table 表的名字;

往表中添加字段
alter table 表的名字 add 字段名字 类型(范围) [约束];

修改表字段类型
alter table 表名字 modify 字段名字 新类型(范围) [约束];

修改表字段的名字和类型
alter table 表名字 change 旧的字段名字 新的字段名字 新类型(范围) [约束];

给表中的字段添加约束
主键，外键，检查，唯一四个约束要用add constraint，其他的约束可以用modify
alter table 表的名字 add constraint 起个名字(随意) 约束(字段名字);

往表中添加数据
insert into 表的名字(字段名字) values(要添加的数据);

查询表中的数据
select * from 表的名字;

根据范围查询表里的数据
select * from table limit m,n;
- 显示范围是:[m+1行,m+n行],包括m+1和m+n行

根据字段查询表里的数据
select 字段名字1,字段名字2 from 表的名字;

给从表里查询出来的数据的字段取别名
select 字段名字1 as 要取的别名,字段名字2 as 要取的别名 from 表的名字;

根据字段查询表里的数据(去重)
select distinct 字段名字 from 表的名字;

根据条件查询表里的数据(条件可使用not,!=,or,in(值1,值2...),and,between..and..)
select * from 表的名字 where 条件;
模糊查询表里的数据
在根据条件查询的条件中使用like和通配符%(任意字符),_(一个字符)
查询表里的某字段为NULL的值条件必须用is null,不能用= null

对表里的数据排序(先按字段1排，有相同的则再按字段2排)
select * from 表的名字 order by 字段名字1 asc(默认升序)/desc(降序),字段名字2 asc(默认升序)/desc(降序);

修改表中某字段的所有数据
update 要修改的表的名字 set 要修改的字段 = 修改后的内容;

修改表中某字段的指定数据(where 后面是查找条件)
update 要修改的表的名字 set 要修改的字段 = 修改后的内容 where 字段 = 内容;

删除表中的一个字段(这里要注意不能全删光)
alter table 表的名字 drop column 字段名字;

删除表中的数据
truncate 表的名字; (后面不能跟where)
delete from 表的名字;
delete from 表的名字 where 字段名字 = 内容;

删除数据库/表
drop database/table 存在的数据库名字/表的名字;

清屏
system clear

导入导出数据库
导出(终端中): mysqldump -uroot -p 存在的要导出的数据库的名字 > 要导出位置的绝对路径/新名字.sql
导入(终端中): mysql -uroot -p 新数据库的名字 < 路径/要导入的数据库名字.sql
导入(客户端中): 1. 先建一个新的数据库，名字随意
　　　　　　　 2. use 这个空的数据库
　　　　　　　 3. source 写要导入的.sql文件的绝对路径

 

约束
两种添加的时机：1. 建表的时候 2. alter 添加约束

演示代码(不想打的可以直接复制，我写全了，不过代码里也有会报错的地方，认真看错误的原因才能理解的更深):

主键约束(自带唯一约束):
create database yueshu character set utf8;
use yueshu;
show tables;
create table tbl_PK(
id int unsigned(无符号) primary key auto_increment(自动增长),(顺序不能错)
name varchar(30));
insert tbl_PK values(1,'张三'),(2,'李四'),(3,'王五');
select * from tbl_PK;
insert tbl_PK values(0,'张三'),(0,'李四'),(0,'王五');

唯一约束:
create table tbl_UQ(
id int unsigned primary key auto_increment,
card_id varchar(18) unique);
insert into tbl_UQ values(0,'123123');
select * from tbl_UQ;
insert into tbl_UQ values(0,'123123');
insert into tbl_UQ values(0,'123123321');
select * from tbl_UQ;

非空约束:
create table tbl_NN(
id int unsigned primary key auto_increment,
name varchar(10) not null);
alter table tbl_NN add age int unsigned;
desc tbl_NN;
insert into tbl_NN values(0, '张三');
insert into tbl_NN(id,name) values(0, '张三');
select * from tbl_NN;
insert into tbl_NN(id,age) values(0, 18);

默认约束:
create table tbl_Default(
id int unsigned primary key auto_increment,
gender char(2) not null default '男');
alter table tbl_Default add name varchar(30);
insert into tbl_Default(id, name) values(0,'张三');
select * from tbl_Default;

检查约束:(MySQL中不起作用)
create table tbl_Check(
id int,
age int check ( age > 0 and age < 120),
gender char(2) check ('男' or '女'));
insert into tbl_Check values(0, 180, '中性');
select * from tbl_Check;

外键约束:
create table Employees(
EmpId int unsigned primary key auto_increment,
EmpName varchar(50),
EmpGender char(2),
EmpAge int,
EmpEmail varchar(100),
EmpAddress varchar(500)
DeptId int);

create table Department(
DepId int unsigned primary key auto_increment,
DepName varchar(50));

alter table Employees add constraint FK_DeptId_DepId foreign key(DeptId) references Department(DepId);
外键必须和主键关联
insert into Department values(0,'一部门'),(0,'二部门');
select * from Department;
insert into Employees(EmpId,DeptId) values(0,1);
select * from Employees;
insert into Employees(EmpId,DeptId) values(0,3);
drop table Department;
drop table Employees;
drop table Department;

聚合函数(聚合函数不计算null值)
- 对多条数据进行统计

把某字段中的数据分组
select 字段名字1 from 表的名字 group by 字段名字1;

根据某字段(字段名字1)分组，显示其它字段(字段名字2)成分
select 字段名字1,group_concat(字段名字2) from 表的名字 group by 字段名字1;

求和
select sum(字段名字) as '别名' from 表的名字;

求最大
select max(字段名字) as '别名' from 表的名字;

求最小
select min(字段名字) as '别名' from 表的名字;

求平均
select avg(字段名字) as '别名' from 表的名字;

求某字段所含数据的总个数(不算null那一行)
select count(字段名字) as '别名' from 表的名字;

分组之后显示每组的数据个数
select 字段名字1,count(*) from 表的名字 group by 字段名字1;

保留小数指定位数
select round(那个小数,保留的位数) from 表的名字;

having 对分组之后的数据进行进一步的筛选
类似于 select 和 where 之间的关系
select 字段名字1 from 表的名字 group by 字段名字1 having 条件;

多表查询
- 尽量避免多表查询
- 多表查询会出现笛卡尔积

内连接,满足条件显示(还是会产生笛卡尔积，只是用on进行了条件筛选)
select * from 表1的名字 inner join 表2的名字 on 表1的名字.字段的名字 = 表2的名字.字段的名字;

左连接,以表1为基础，表1全显示,表2不够的用null补全
select * from 表1的名字 left join 表2的名字 on 表1的名字.字段的名字 = 表2的名字.字段的名字;

右连接,以表2为基础，表2全显示,表1不够的用null补全
select * from 表1的名字 right join 表2的名字 on 表1的名字.字段的名字 = 表2的名字.字段的名字;

自关联
- 让表自己和自己进行连接
- 表中的某一列关联了表中的另外一列，但是他们的业务含义是不一样的
select * from 表的名字 inner join 表的名字 on 表的名字.字段的名字 = 表的名字.字段的名字;
代码示例:
Create table areas
(
id int,
atitle varchar(100),
pid int
)

如：
id 编号 pid 上级编号

id  atitle     pid
1   河北省   null
2   石家庄    1
3   平山县    2

子查询
select * from 表1的名字 where 字段1的名字=(select 字段2的名字 from 表2的名字 where 条件)

执行顺序:
1—from 表名
2—where
3—group by
4—select distinct *
5—having
6—order by
7—limit

注意事项：
enum ,添加数据的时候是从1开始的不是从0开始的 ，比如enum('男','女')-----1是男，2是女，以此类推

附上篇随笔链接：http://www.cnblogs.com/wf-skylark/p/9043904.html