MySQL数据库常用命令操作

数据库的操作

创建数据库

create database 数据库名 [character set 字符集][collate 校对规则];
注: []意思是可选的意思

查看所有数据库

show databases;
查看数据库的定义结构
show create database 数据库名;
删除数据库
drop database 数据库名;
修改数据库
alter database 数据库名 character set 字符集;
字符集：utf8 gbk

切换到某个数据库
use 数据库名;
注意：在创建数据库名之前一定要指定数据库
查看正在使用哪个数据库
select database();

操作表

创建表
create table 表名(
列名 类型 [约束],
列名 类型 [约束],
…
列名 类型 [约束],
);
列：字段
行：记录

整型 一般使用int 或者bigint
浮点/双精度型
默认的范围 float或者double
指定范围 float(M,D) eg: float(4,2) 表达的范围: -99.99~99.99
字符串
固定长度 char(n) eg: char(20), 最大能存放20个字符. ‘aaa’, 还是占20个字符的空间
可变长度 varchar(n) eg:varchar(20), 最大能存放20个字符. ‘aaa’, 占3个字符的空间
一般使用varchar(n) 节省空间; 如果长度(eg:身份证)是固定的话 可以使用char(n) 性能高一点
关于大文件
一般在数据库里面很少存文件的内容, 一般存文件的路径
一般不使用二进制存, 使用varchar(n)存文件的路径
日期
DATE 只有日期
DATETIME 日期和时间
类型小结
整数: int bigint
小数: double
字符串: varchar(长度)
日期: date \ datetime
约束
即规则,规矩 限制;
作用：保证用户插入的数据保存到数据库中是符合规范的
约束 约束关键字
主键 primary key
唯一 unique
非空 not null
约束种类:
not null: 非空 ; eg: username varchar(40) not null, username这个列不能有null值
unique:唯一约束, 后面的数据不能和前面重复; eg: cardNo char(18) unique; cardNo 列里面不可以有重复数据
primary key；主键约束(非空+唯一); 一般用在表的id列上面. 一张表基本上都有id列的, id列作为唯一标识的
auto_increment: 自动增长,必须是设置了primary key之后,才可以使用auto_increment
id int primary key auto_increment; id不需要我们自己维护了, 插入数据的时候直接插入null, 自动的增长进行填充进去, 避免重复了.
注意:
先设置了primary key 才能设置auto_increment
只有当设置了auto_increment 才可以插入null 自己维护 否则插入null会报错
id列:
给id设置为int类型, 添加主键约束, 自动增长
或者给id设置为字符串类型,添加主键约束, 不能设置自动增长

查看表

查看所有表
show tables;
查看表的定义结构
desc 表名；

修改表
增加一列；alter table 表 add 字段 类型 约束；
修改列的类型约束； alter table 表 modify 字段 类型 约束 ；
修改列的名称，类型，约束；alter table 表 change 旧列 新列 类型 约束;
删除一列； alter table 表名 drop 列名；
修改表名 ; rename table 旧表名 to 新表名；
都是以alter table 表名打头
增加列 add
修改列的类型 约束 modify
修改列的名字 类型 约束 change
删除列 drop

删除表

drop table 表名；

操作（表）记录

插入记录-----增

方式一: 插入指定列, 如果没有把这个列进行列出来, 以null进行自动赋值了.
insert into 表(列，列…) values(值，值…)；
方式二: 插入所有的列
insert into 表 values(值，值…);
注意
插入特定的列没有赋值的列,系统自动赋为null(前提是当前列没有设置not null 约束)
列名与列值的类型、个数、顺序要一一对应。
值不要超出列定义的长度。
插入的日期和字符串，使用引号括起来。

更新记录----改

update 表名 set 列 =值, 列 =值 [where 条件]；
注意
如果没有加where 更新整个的
工作里面一般是加where

删除记录----删

delete from 表 [where 条件] ----逻辑删除，不会删除id
truncate table 表;----物理删除，会删除id

delete 和truncate区别【面试题】
DELETE 删除表中的数据，表结构还在; 删除后的数据可以找回,一条一条的删除.
TRUNCATE 删除是把表直接DROP掉，然后再创建一个同样的新表。删除的数据不能找回。执行速度比DELETE快。
工作里面的删除
物理删除: 真正的删除了, 数据不在, 使用delete就属于物理删除
逻辑删除: 没有真正的删除, 数据还在. 搞一个标记, 其实逻辑删除是更新 eg: state 1 启用 0禁用

查询记录----查

基本查询语法
select [][列名 ,列名] [列名 as 别名 …] [distinct 字段] from 表名 [where 条件] ;
查询所有列的记录
select * from 表名；
查询特定列的记录
select 列名，表名，… from 表名；
去重查询
select distinct 列名 from 表名；
注意点: 去重针对某列, distinct前面不能先出现列名
别名查询
对字段取别名: select 字段 as 别名,字段 as 别名,… from 表名
对表取别名:select 表1别名.字段名,… from 表1 as 表1别名
注意: as可以省略 一般都会省略
运算查询（+，-，，/等）
select id（会显示），price * num 总价 from product；

条件查询
select … from 表 where 条件
//取出表中的每条数据，满足条件的记录就返回，不满足条件的记录不返回

between…and… 区间查询
in(值,值…) 在…查询（一般是id）
like 模糊查询 一般和_或者%一起使用
_ 占一位
% 占0或者n位
and 多条件同时满足
or 任意条件满足

排序查询
排序可以根据一个字段排，也可以根据多个字段排序，排序只是对查询的结果集排序，并不会影响表中数据的顺序。
单列排序
语法: 只按某一个字段进行排序，单列排序
select 字段名 from 表名 [where 条件] order by 字段名 [ASC|DESC];
//ASC: 升序，默认值; DESC: 降序
组合排序
语法: 同时对多个字段进行排序，如果第1个字段相等，则按第2个字段排序，依次类推
select 字段名 from 表名 where 字段=值 order by 字段名1 [ASC|DESC], 字段名2 [ASC|DESC];
eg：select * from student order by score desc ,age desc;

聚合函数
聚合函数 作用
max（列名） 求这一列的最大值
min（列名） 求这一列的最小值
avg（列名） 求这一列的平均值
count（列名） 统计这一列有多少记录
sum（列名） 对这一列求总和
聚合函数查询是 纵向查询 ，它是对一列的值进行计算，然后返回 一个结果值 。聚合函数会忽略空值NULL
语法：SELECT 聚合函数(列名) FROM 表名 [where 条件];
我们发现对于NULL的记录不会统计，建议如果统计个数则不要使用有可能为null的列，但如果需要把NULL也统计进去呢？我们可以通过 IFNULL(列名，默认值) 函数来解决这个问题. 如果列不为空，返回这列的值。如果为NULL，则返回默认值。

分组查询
分组的目的就是为了统计，一般分组会跟聚合函数一起使用
分组语法：
SELECT 字段1,字段2… FROM 表名 [where 条件] GROUP BY 列 [HAVING 条件];
分组后筛选having
– 练习:练习根据性别分组, 统计每一组学生的总人数 显示人数 > 5的(分组后筛选)
select sex,count() from student group by sex having count() >5;
注意事项：
根据某一列进行分组, 将分组字段结果中相同内容作为一组; 有几组 返回的记录就有几条
单独分组 没有意义, 返回每一组的第一条记录
分组的目的一般为了做统计使用, 所以经常和聚合函数一起使用
在分组里面, 如果select后面的列没有出现在group by后面 展示这个组的这个列的第一个数据

where和having的区别【面试】
子名 作用
where 子句 1) 对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，即先过滤再分组。2) where后面不可以使用聚合函数
having字句 1) having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，即先分组再过滤。2) having后面可以使用聚合函数

分页查询
select … from … limit a ,b.
LIMIT a,b;
a起始行数，从0开始计数，如果省略，默认就是0; a=(当前页码-1)*b;
b： 返回的行数（一页查询的数量【固定的,自定义的】）
查询语法小结：
select…from…
select…from…where…
select…from…where…order by…
select…from…where…group by…
select…from…where…limit a,b

select…from…where…group by…having…order by…limit
where…group by…having…order by…limit可混合使用，但是使用的时候一定是按照这个顺序，否则报错

总结：

创建: create database 数据库名;
删除: drop database 数据库名;
查看: show databases;    show create database 数据库名;
修改: alter database 数据库名 character set 字符集; 
其他: use 数据库名;


创建: 
	create table 表名(
      字段名 类型 约束,
       ...
      字段名 类型 约束
    );
	类型: int,double,varchar(长度),date\datetime
    约束:  
	主键: primary key     auto_increment
        	非空: not null
            唯一: unique
 删除: drop table 表名;
 查询: show tables;   desc 表名;
 修改:
	alter table 表名 add 列名 类型 约束;
	alter table 表名 modify 列名 类型 约束;
	alter table 表名 change 旧列名 新列名 类型 约束;
	rename table 旧表名 to 新表名;


 添加:  insert into 表名(字段,字段) values(值,值);
	   insert into 表名 values(值,值,...);
 删除:  
    delete from 表名 where 条件;
        truncate table 表名;
 修改:
    update 表名 set 字段=值,... where 条件
            

  select ... from 表名 where 条件

   select ... from 表名 order by 字段 [asc,desc]

   max(),min(),sum(),avg(),count() 会忽略null  所以会使用ifnull()函数进行判断

  select ... from 表名  group by 字段  having 条件

select ... from 表名   limit a ,b 

查询总结: select…from…where…group by…having…order by…limit