mysql常用命令

总结+参考整理有关MySQL相关知识

数据库增删改查（平时我们很少几乎不会用到）

1.创建一个数据库     create database 库名;
1.2创建一个db2数据库并设置默认编码为utf8    create database db2 default charset='utf8';
2.显示有哪些数据库   show databases;
3.进入某个数据库    use 库名;
4.删除某个数据库   drop database 库名;

表结构知识整理+增删改查（几乎不会用到）

1、表中常用的数据类型

Int   存储整数（整型：正负10位整数）            　 int(M)   M指最大显示宽度　　　　varchar 存储字符
bigint    存储整数（大整型：（正负20位整数） 　char(M)  M指最大存储字符  　　   char    存储字符
float      存储小数    　　　　 　　　　　　　　    date    存储日期（年月日）　        datetime 存储日期（年月日时分秒）
2、约束

约束用于对表中字段进行限制，保证表中数据的正确性和唯一性
2.1.primary key     主键约束
说明：非空，唯一，用于唯一标识对应的记录。类似身份证。
2.2 foreign key      外键约束（说明：用于表与表建立关系模型，使表与表紧密的结合起来）
2.3 not null    非空约束 说明：字段值不能为Null
2.4 default     默认值约束 说明：默认给字段指定默认值
2.5 auto_increment      自增约束（说明：作用在整数类型，字段默认从1开始自增）

2、创建一个表
create table 表名（字段1名称，数据类型，约束，备注，字段2名称，数据类型，约束，备注，字段3名称，数据类型，约束，备注，）

eg：创建一张学生表tb_user，学号id, 姓名name. 

create table student(id int(2) primary key comment'用户编号id',name varchar(30)comment'用户姓名',)engine=INNODB default CHARSET=utf8 comment'学生表';

3、查看表结构 ：desc 表名   

2.修改表名　　alter table 表名 rename 新表名
3.修改表字段　　alter table 表名 change 原字段名 新字段名 数据类型，约束
4.添加表字段，并放到第一个字段前　　alter table 表名 add 字段名 数据类型 约束 first 
5.添加表字段，并放到某个字段后　　alter table 表名 add 字段名 数据类型 约束 after 字段名
6.同时添加两个字段，默认添加到字段最后　　alter table 表名 add(字段1 数据类型，字段2 数据类型)
7.删除表字段　　alter table 表名 drop 字段
8.删除表两个字段　　alter table 表名 drop 字段1,drop 字段2
9.修改主键id为自增长　　alter table 表名 change 字段名 数据类型 auto_increment
10.删除表　　drop table 表名
11.创建数据库设置编码　　create database db1 character set utf8
12.修改表修改表的编码　alter table 表名 convert to character set utf8
13.修改表的备注　alter table 表名 comment '备注说明'
14.修改表字段的备注　alter table 表名 modify 字段名 数据类型 comment '备注说明'
表内容知识整理+增删改查（常用）

单表查询

1、单筛选条件 select * from  表名  where   接满足条件（字段值=value,字段值 != value或字段值 ><value） （*代表所有字段，同时如何想查某个字段直接替代*就可以，同样多字段原理相同）

2、多筛选条件  select * from  表名  where  条件1 or 条件2 / 条件1 and 条件2  (or至少一个满足，and必须两个同时满足)

3、查询一个条件范围内的数据　　select * from 表名 where 字段 between m and n  between...and ... 指定一个范围

4、查询字段满足在(不在)指定的集合中的数据　　select *   from 表名 where 字段 in / not in(值1，值2，值3)

5、查询字段值为(不为)空的数据　　select * from 表名 where 字段 is /is not null 

13.查询某个字段模糊匹配成功的数据；　　select * from 表名 where 字段 like  “%值%” 　　（ %用于匹配字段开头和结尾）；

14.查询限定的数量的数据；select *  from 表名 where 字段 limit m,n　　（m 指下标，n指限定的数量，下标为m的开始的n条数据）

15.  查询的数据根据某个字段从小到大排序　　select * from 表名 order by 字段 asc　　（order by ...asc 从小到大排序）

16.  查询的数据根据某个字段从大到小排序　　select * from 表名 order by 字段 desc  order by ... desc    从大到小排序

17.  查询的数据根据某个字段进行分组　　select * from 表名 group by 字段　　（group by ...    根据条件分组）

18.  查询的数据根据某个字段进行分组再条件过滤　　select *   from 表名 group by 字段 having 条件　　（having跟在group by 后面，作用相当于where）

单表统计/求和/平均值/最大值/最小值/字段值去重

1. 统计查询数据的数量； 　select count(*) from 表名
2. 查询某个字段求和；    　select sum(字段) from 表名
3. 查询某个字段进行平均值；   select avg(字段)      from 表名
4. 查询某个字段最大值；  　　select max(字段) from 表名
5. 查询某个字段最小值；  　　select min(字段) from 表名
6. 对某个字段进行去重； 　　 select distinct(字段) from 表名

Mysql: 增删改语句

1.表中插入数据　　insert into 表名 values(字段1value,字段2value,字段3value...)

 2.一次性表中插入多条数据　　insert into 表名 values(字段1value,字段2value,字段 3value...),(字段1value,字段2value,字段3value...)

3. 对表中指定字段插入数据　　insert into 表名(字段1，字段2) values(字段1值，字段2值)

4. 删除表中指定数据　　delete from 表名 where 条件

5. 更新表中指定字段数据　　update 表名 set 字段名=值 where 条件

Mysql: 备份表，备份数据

1.备份表，创建一个表与某个表相同（备份表结构）； 　　create table 表1  like    表2

2.备份数据，把一个表的数据插入到另一个表； insert into 表名 select * from 表名    （注意点：插入的表必须要存在）

3.把一个表的某些字段插入到一个新表中　　insert into 表1(字段1，字段2) select 字段1，字段2 from 表2

4.复制表结构及数据到新表；   create table表1 as select * from表2;

子查询

什么是子查询？

一个查询嵌套另一个查询 子查询分

　　1. 标量子查询（返回一个值）　　2. 列子查询  （返回一个列）　　3. 行子查询  （返回一行多列）　　4. 表子查询  （返回一个表)

　　1.标量子查询： 把一个sql执行返回的一个值，作为另一个sql的一个条件
　　2. 列子查询： 执行一个sql把返回的一个列作为另一个sql条件　　select *  from score a where a.id  in( select id from student where  name=‘zhangsan' or name='lisi')　　列子查询返回的是两个字段的值，列子查询使用符号 in or  not  in
　　4. 表子查询：执行一个sql返回的是一个表；select name from (select * from student )a　　  子查询返回的是一个表
 　　select * from student ,取了一个别名a 主查询是从表a中查询name

多表连接

1. 内连接inner  join  2.左连接left  join     3.右连接right  join

1. 内连接查询(查询两个表都符合条件 的数据)
　　基本格式;  select 字段列表 from 表1 inner join 表2  on 表1.字段=表2.字段
　　查询学生的姓名和成绩  　　select name,score from student inner join score  on student.id=score.id
2. 左连接查询(左边表中数据显示全部)
　　左边表中显示全部，右边表符合条件 的显示，不符合条件的以null填充
　　基本格式;    select 字段列表from 表1 left join 表2  on 表1.字段=表2.字段
　　查询学生的姓名和成绩  　　select name,score from student left join score  on student.id=score.id
3. union连接（将两条sql语句查询的值连接在一起展示，得相同数量的列）
　　union连接的两个表中，若有相同的记录，则只显示一条(即会去重)；
　　union all不会去重，直接把两个表的全部内容抖列出来；索引

索引

　　一个索引是存储在表中的数据结构，索引在表的列名 上创建。索引中包含了一个列的值，这些值保存在一 个数据结构中。重点：索引是一　　　　　　　　　　　　　　　　　　　　　　　　　　              种数据结构
1 索引的作用    可以利用索引快速访问数据库中的特定信息.
 2.索引分类
　　a. 普通索引　　b. 唯一索引　　c. 主键索引
3查看有哪些索引　　  show index from 表名
4.创建一个索引
　　alter table table_name add index index_name(column_list); #创建普通索引
　　alter table table_name add unique index_uni_name(column_list); #创建唯一索引
　　alter table table_name add primary key(column_list); #创建主键索引
6.删除一个普通索引　　  alter table 表名 drop index 索引名;
7.删除主键索引　　  alter table 表名 drop primary key;
主键和外键
主键和外键是把多个表组织成一个有效的关系型数据库的粘 合剂,主键和外键对数据库的性能和可用性起着很大作用
主键的作用
1. 唯一的标识一行　　2. 作为一个被外键有效引用的对象
外键的作用
一张表记录的数据太过于冗余尽量让表的数据单一化 保存数据的一致性和完整性
注意点
数据库类型必须是INNODB类型，MYSIAM类型不支持使用 外键
.创建表时增加外键约束
CREATE TABLE `student` (
  `sid` int(5) NOT NULL DEFAULT '0' COMMENT '学生id',
  `name` varchar(10) NOT NULL COMMENT '学生姓名',
  `age` int(3) DEFAULT '18' COMMENT '学生年龄',
  KEY `i_sid` (`sid`))ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生表';
 
CREATE TABLE `score` (
  `sid` int(5) NOT NULL DEFAULT '0' COMMENT '学生id',
  `score` int(3) DEFAULT NULL COMMENT '成绩',
foreign key(sid)references student(sid) on update cascade on delete cascade
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='成绩表';
外键约束
主表student表 ，       从表score表
        
2.增加外键约束（从表的字段类型和大小要与主表的一致，主表的字段必须有索引）
alter table score add constraint fk_student_sid foreign
key(sid) references student(sid) on update cascade on delete cascade;
3.查看mysql所有的主键，外键约束名称
select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE;
4.删除一个外键
alter table score drop foreign key fk_student_sid

存储过程

1.     什么是存储过程    

　　存储过程是完成特定功能的sql语句集合。通过编译后存储在数据 库中，通过指定的存储过程名称调用执行它。存储过程=sql语句集合+控制 语句
2.     Mysql5.0版本后支持存储过程 

3.使用存储过程的优点
　　1.     存储过程创建可以多次调用，不需要重新编写存储过程语句。存储过程支持接收参数，返回输出值  存储过程加快程序的运行速度
　　2.     存储过程增加sql语句的功能和灵活性
　　4.创建一个存储过程

注意点：有4个地方不能加分号：存储过程名后面，begin、end后面，while∙∙∙do后面         2.循环自增变量，要带冒号和set 如： set i:=i+1;
 While循环：
drop procedure if exists sum;      #如果sum存储过程存在则删除
create procedure sum(n int)
begin
         declare sum int default 0;   #定义一个变量并设默认值为0
         declare i int default 0;      #定义一个变量并设默认值为0
         while i<=n do     #while循环
                set sum:=sum+i;
                set i:=i+1;
         end while;
         select sum;
end

If判断：
drop procedure if exists panduan;
create procedure panduan(n int)
begin
            if (n>=0 && n<=59)then
              select '不及格';
            else if (n>=60 && n<=80)then
              select '良好';
            else if (n>=81 && n<=90)then
              select '优秀';
            else if (n>=91 && n<=100)then
              select '非常优秀';
            else
              select '输入错误';
            end if; end if;     end if;     end if;
end
If判断和while循环：
drop procedure if exists insert_2;
create procedure insert_2(n int)
begin     
            declare i int default 1;
            declare num int default 0;
            truncate student;#清空student表
            while i<=n do 
              select count(1) into num from student;#将sql查出来的统计值赋值给num
              if (num<100) then
                     insert into student(sid,name)values(i,concat('name',i));
              else
                     select 'student 表中已有1万条数据，不能继续添加了';
              end if;     set i:=i+1;
            end while;
            select * from student;
end
5.     调用一个存储过程
call 存储过程名称（）
6.     删除一个存储过程
drop procedure 存储过程名称
drop procedure if exists 存储过程名称 #加强代码的健壮性
7.查询数据库中的存储过程
show procedure status
8.查看存储过程的创建代码
show create procedure存储过