MySQL 基础常用命令总结

MySQL 基础常用命令

注意：MySQL在centos中安装的是5.7版本的，编辑MySQL时会有个报错，需要执行：

				?

									set@@global.sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

1. SQL语句

每个命令执行结束加分号结束　　　　　

查询所有数据库:show databases;
切换数据库:use 库命名;
创建数据库:create database [IF NOT EXISTS] 库名;
删除数据库:drop database [IF EXISTS] 库名;
查询数据库创建:show 建库语句;
指定数据库采用的字符集:CHARACTER SET
修改数据库的编码集:alter database 数据库名 CHARACTER SET 编码集;

注意:不要修改mysql服务器的编码集,表的编码集默认和库一致

2. 建表

格式:

create table [if not exists] 表名(
字段1 数据类型字段属性,
字段2 数据类型字段属性,...
字段N 数据类型字段属性
)engine=引擎 default charset=编码集;
查看当前数据库:select database();
查看建表语句:show create table 表名;
查看表结构:desc 表名;
删除:drop table [if exists] 表名;

3.字段属性

not null:没给值数据为默认值(varchar默认值为空
AUTO_INCREMENT定义列为自增的属性,一般用于主键,数值会自动加1
PRIMARY KEY关键字用于定义列为主键,您可以使用多列来定义主键，列间以逗号分隔
ENGINE 设置存储引擎，CHARSET 设置编码
default null:没给值数据就是null
default 值:设置字段的默认值

注意:主键不重复的列

这里我们建立一个student表：

				?

									create table if not EXISTS student (

									id int auto_increment,

									`name` VARCHAR(32),

									 age int,

									sex char(1),

									clazz VARCHAR(32)) charset utf8;

				?

									insert into student values (1001,'zs',18,'男','一班');

									insert into student values (1002,'ls',19,'女','二班');

									 insert into student(`name`,age,sex,clazz) values ('ww',69,'男','一班');

									 insert into student(`name`,age,sex,clazz) values ('we',21,'女','二班');

									insert into student(`name`,age,sex,clazz) values ('ld ',23,'男','一班');

									insert into student(`name`,age,sex,clazz) values ('lq',45,'女','二班');

									insert into student(`name`,age,sex,clazz) values ('lwq',23,'男','一班');

									 insert into student(`name`,age,sex,clazz) values ('ld',12,'女','二班');

4.修改表:alter table

修改表名:alter(rename) table 旧表名 to 新表名;

rename table student1 TO `student`;

添加字段:alter table 表名 add 字段字段数据类型属性;

				?

									alter table student add job varchar(32) default '没有工作' ;

									insert into student (job) VALUES('a');

									insert into student (job) VALUES('b');

									insert into student (job) VALUES('c');

									insert into student (job) VALUES('a');

									 insert into student (job) VALUES('b');

修改字段:alter table 表名 change 旧字段新字段数据类型属性;

				?

									alter table student change clazz clazz varchar(255);

									alter table student change age score double;

修改字段:alter table 表名 modify 字段数据类型属性;

				?

									alter table student MODIFY varchar(356); #这里不能比之前的空间小

注意:

change:修改所有(字段名,数据类型,属性)
modify:修改一部分(数据类型,属性)
修改数据类型时,varchar->int元数据会变为0

5. 增删改查:字符串全部使用''包起来

5.1 增

格式：

				?

									insert into 表名(字段) values(值),(值)...(值);

									 insert into student values (1001,'zs',18,'男','一班');

									insert into student values (1002,'ls',19,'女','二班');

									insert into student(`name`,age,sex,clazz) values ('ww',69,'男','一班');

									insert into student(`name`,age,sex,clazz) values ('we',21,'女','二班');

									insert into student(`name`,age,sex,clazz) values ('ld ',23,'男','一班');

									insert into student(`name`,age,sex,clazz) values ('lq',45,'女','二班');

									insert into student(`name`,age,sex,clazz) values ('lwq',23,'男','一班');

									10 insert into student(`name`,age,sex,clazz) values ('ld',12,'女','二班');

5.2 删

				?

									-- 删除delete from 表名 where 子句;

									delete from student where job='c';

5.3 改

				?

									-- 改update 表名 set 字段1=值1,字段2=值2...字段N=值N where 子句;

									update student set job='b'where name ='ls';

5.4 查

				?

									-- 查select 字段 from 表名 where 子句;

									 select * from student ; #查询全部

									 SELECT id as di,name,job,score from student where score>18; #特定查询，并且展示特定的表 as:表示改字段名称（原来的表不发生变化）

注意:表示所有字段

6. 子句

> < <= >= = <> 大于、小于、大于(小于)等于、不等于
between ...and... 显示在某一区间的值(含头含尾)
in(set) 显示在in列表中的值，例：in(100,200)只能匹配100或200
like '张_' 模糊查询使用% 和 _(%表示匹配所有 _匹配一个)
Is null 判断是否为空
and 多个条件同时成立
or 多个条件任一成立
not 不成立，例：where not(expection>10000);

				?

									-- >   <   <=   >=   =    !=    大于、小于、大于(小于)等于、不等于

									SELECT * from student WHERE id>1006;

									SELECT * from student WHERE id!=1006;

									--between  ...and...    显示在某一区间的值(含头含尾)

									select id,name,job from student  where id BETWEEN  1002 and 1005;

									 select * from student where job BETWEEN 'a' and 'b';

									 -- in(set)    显示在in列表中的值，例：in(100,200)只能匹配100或200

									 select * from student where job in('a','b');

									-- like '张_'    模糊查询  使用% 和 _(%表示匹配所有 _匹配一个)

									 SELECT * from student where name like 'l%';

									 SELECT * from student where name like 'l_';

									select * from student where name is not null;

7.limit分页

格式:
　　语句 limit 开始下标,长度;

				?

									-- limit分页    语句 limit 开始下标,长度;注意：没有where

									select * from student LIMIT 1,2;

									select * from student LIMIT 0,2;

									select * from student LIMIT  2;

注意:
　　如果数据量不够,显示全部

8.去重

格式:
　　DISTINCT 字段1,字段2...字段N

				?

									-- 去重 DISTINCT 字段1,字段2...字段N

									select DISTINCT name from student;

									select count(DISTINCT name) from student;

注意:

　　字段不能在DISTINCT之前,只能在DISTINCT后面

　　DISTINCT之后有多个字段,按照所有字段进行去重

9.聚合函数

　　　　　　count(字段):求多少行数据
　　　　　　sum(字段):求和
　　　　　　avg(字段):平均数
　　　　　　max(字段):最大值
　　　　　　min(字段):最小值

注意:

　　　　　　varchar能比较大小,不能获取avg(没有任何意义)
　　　　　　如果值为Null不参与计算
　　　　　　sum和avg字段的数据不是数值,结果都是0

				?

									-- count(字段):求多少行数据

									select count(*) from student;

									 select count(name) from student;

									-- sum(字段):求和

									select sum(score) from student;

									select sum(job) FROM student;

									select name+score as sum FROM student; #score的值

									 SELECT name*score as cheng FROM student; #0

									-- avg(字段):平均数

									 SELECT avg(score) FROM student;

									 -- max(字段):最大值

									SELECT max(score) FROM student;

									SELECT max(job) FROM student; #c

									-- min(字段):最小值

									SELECT min(score) FROM student;

10.拼接

　　格式1

　　　　concat(str1,str2...)

　　格式2:

　　　　concat_WS(separator,str1,str2,...)

				?

									-- 格式一:concat(str1,str2...)

									 select CONCAT(id,'-',name) as pj FROM student;

									 -- 格式二:concat_WS(str1,str2...)

									SELECT CONCAT_WS('~',id,name,score,job)FROM student; #中间以~隔开

11.日期函数

获取当前日期:

				?

									current_timestamp;--所有

									current_timestamp();--所有

									CURRENT_DATE();-- 年月日

									CURRENT_DATE;-- 年月日

									CURRENT_TIME();-- 时分秒

									CURRENT_TIME;-- 时分秒

									-- 获取当前日期:

									--         current_timestamp;--所有

									SELECT CURRENT_TIMESTAMP from student;

									--         current_timestamp();--所有

									 SELECT CURRENT_TIMESTAMP() from student;

									 --         CURRENT_DATE();-- 年月日

									 select CURRENT_DATE() from student;

									--         CURRENT_DATE;-- 年月日

									 select CURRENT_DATE from student;

									--         CURRENT_TIME();-- 时分秒

									 SELECT CURRENT_TIME() FROM student;

									--         CURRENT_TIME;-- 时分秒

									SELECT CURRENT_TIME FROM student;

时间转str

格式:
date_format(date,format) date:时间 format:格式

str转日期

格式:
str_to_date(str,formaat)

				?

									SELECT * FROM date;

									 -- 时间转str

									 --         格式:

									 --             date_format(date,format)

									--             date:时间

									--             format:格式

									select DATE_FORMAT('2021-09-01','%Y~%m~%d');

									--     str转日期

									--         格式:

									 --             str_to_date(str,formaat)

									 SELECT STR_TO_DATE('2021-09-01','%Y-%m-%d');

日期相减

格式:
datediff(expr1,expr2);

注意:只能相减年月日,时分秒参与运算结果为null

				?

									datediff(expr1,expr2);

									-- 注意:只能相减年月日,时分秒参与运算结果为null

									SELECT DATEDIFF('2021-09-09','2021-09-01');

函数向日期添加指定的时间间隔

格式:
DATE_ADD(date,INTERVAL expr unit);
date:时间
INTERVAL:关键字
expr:间隔的数值
unit:年月日时分秒(..,...,day,..,..,..)

				?

									SELECT DATE_ADD('2021-09-09',INTERVAL +10 YEAR);

									SELECT DATE_ADD('2021-09-09',INTERVAL +10 DAY);

12. 数组计算

round(x,d):四舍五入
x:值
d:保留几位小数点

ceil(x):向上取整
floor(x):向下取整
rand():随机数(0-1之间)

				?

									-- 数组计算

									--     round(x,d):四舍五入

									 --         x:值

									 --         d:保留几位小数点

									SELECT ROUND(1.3,2); #2表示保留几位小数

									--     ceil(x):向上取整

									 SELECT ceil(1.2);

									--     floor(x):向下取整

									 SELECT floor(1.2);

									--     rand():随机数(0-1之间)

									 SELECT rand();

13.排序

格式:
order by 字段1 asc|desc,字段2 asc|desc...字段n asc|desc;

				?

									SELECT * from student ORDER BY score,job;

									 SELECT * from student ORDER BY score desc, job desc;

注意:

默认升序asc,降序desc
如果有多个字段,按照先后顺序依次排序

14. group by 分组

格式:

group by 字段1,字段2...字段n;

注意:

多个字段,按照所有字段进行分组(一起分组)
有多少组显示多少条数据(默认情况下,没有经过条件筛选)
组显示的数据为每组中默认第一条数据
by 通常和聚合函数一起使用

				?

									select max(score) as c from student where score=c;

									select max(score) as c from student having score=c;

									两个都不能运行

									SELECT count(*),job,`name`,id as c from student GROUP BY sex where c>2; #错误

									SELECT count(*) as c,job,`name`,id from student GROUP BY sex HAVING c>2;

									-- select id,name,sex from student where job='a'; # 可以运行

									--select id,name,sex from student having job='a'; #不能运行(显示了之后就没有job)

									-- 执行过程是 from-where-select-having

									-- select count(*) c from student where c>1; -- 不行

									-- select count(*) c from student having c>1;-- 行

									select count(*) c,sex from student group by sex where sex='男';

									select count(*) c,sex from student group by sex having sex='男';

									--where having 一起使用

									SELECT count(*)as c,name,id FROM student where sex='男' HAVING c>3;

									where 是对表中from到的数据进行筛选；

									having是对表中selec显示数据进行晒选；