mysql常用语句操作

概述：

1.通过表结构介绍、来操作mysql常用的语法&语句；

2.现在学习下几个基本的sql语句，万变不离其宗，把基础弄扎实了其它的就很容易了

一、表结构&建表：

1.学生基础信息表student：

DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`sid` int(11) NOT NULL COMMENT '学生id',
`sname` varchar(30) DEFAULT NULL COMMENT '学生姓名',
`sage` int(11) DEFAULT NULL COMMENT '学生年龄',
`ssex` varchar(8) DEFAULT NULL COMMENT '学生性别',
`creation_time` datetime NOT NULL COMMENT '创建时间',
PRIMARY KEY (`sid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2.学生成绩表sc：

DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc` (
    `sid` INT (11) NOT NULL COMMENT '学生id',
    `cid` INT (11) NOT NULL COMMENT '成绩id',
    `score` INT (11) DEFAULT NULL COMMENT '成绩分数',
    `creation_time` datetime NOT NULL COMMENT '创建时间'
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4;

三、常用语法：

查询：

1.简单查询

#查询sage等于25
SELECT * from student where sage=25;

#查询sage不等于25
SELECT * from student where sage<>25;
SELECT * from student where sage!=25;


########逻辑查询 and、or
SELECT * from student where sage=25 and sage='女';
SELECT * from student where sage=25 or sage='女';

#in，not in 的用法
SELECT * FROM student WHERE sname in ('刘亦菲','王康','陈三');
SELECT * FROM student WHERE sname not in('刘亦菲','王康','陈三');


####is 和not is、null
#查询不为null
SELECT * from student where ssex  IS not NULL;
#查询不null
SELECT * from student where ssex  IS NULL;


#############限制返回记录：
#最后三条
select * from student order by  sage desc limit 3; 
#分页查询(范围)
select * from student limit 0,5
#显示前2行
select * from student limit 2

2.范围查询

#查询sage 25到30的数据
SELECT * from student where sage BETWEEN 25 and 30;

#查询 creation_time日期范围
SELECT * from student where creation_time BETWEEN '2021-10-03 00:00:00' and '2021-10-25 18:00:00'

#查询creation_time数据
SELECT * from student where creation_time like '2021-10-%'

3.分组查询

#查询后分组
SELECT * from student where ssex='女' GROUP BY sage;
#分组后倒序
SELECT * from student where ssex='女' GROUP BY sage ORDER BY creation_time desc;

#倒序-desc
SELECT * from student where ssex='女' ORDER BY creation_time desc;
#顺序-asc
SELECT * from student where ssex='女' ORDER BY creation_time asc;

4.like匹配查询

#####查询匹配关键字（%表示所有，_表示任意一个字符）
#匹配包含'刘'记录
select * from student where sname like '%刘%';
#匹配以'伊朗'结尾的记录
select * from student where sname like '%伊朗';
#匹配任意一个字符
select * from student where sname like '刘_菲';

5.子查询

select  * from student  where  sage in(select  sage from  student  where ssex='女' and sage<30 and creation_time like '2021-10%' GROUP BY sage);

6.单表统计

#统计'2021年10月份'每天的sname字段的个数
SELECT date_format(creation_time,'%Y-%m-%d') as _time ,count(sname) FROM student where  creation_time  like '2021-10%' GROUP BY DAY(creation_time);
#DAY()按天统计 , WEEK()按周统计，MONTH()按月统计，QUARTER()按季度统计，YEAR()按年统计 
# date_format日期格式化


#统计某个月的记录数
SELECT count(*) from student where  creation_time like '%2021-10%';

7.聚合函数

#去重
SELECT distinct* from student;

#统计条数
SELECT count(*) from student;

#求和
SELECT sum(sage) from student where ssex='女';

#平均数
SELECT avg(sage) from student where ssex='女';

#ROUND(字段,n)保留小数n位
SELECT ROUND(avg(sage),1) as _avg from student where ssex='女';

#最大值
SELECT max(sage) from student where ssex='女';

#最小值
SELECT min(sage) from student where ssex='女';

8.多表连接查询

#等值查询
select  *  from student st INNER JOIN sc scc on st.sid = scc.sid
where st.sage=25

select  st.sid,st.sname,st.sage,st.ssex,scc.sid,scc.score from student st INNER JOIN sc scc on st.sid = scc.sid
where st.ssex='女';

#左连接
select  st.sid,st.sname,st.sage,st.ssex,scc.sid,scc.score from student st left JOIN sc scc on st.sid = scc.sid
where st.ssex='女';

#右连接
select  st.sid,st.sname,st.sage,st.ssex,scc.sid,scc.score from student st right join sc scc on st.sid = scc.sid
where st.ssex='女';

#左连接：左表不管能不能匹配上条件,最终都会保留:能匹配正确的保留; 若不能匹配,右表的字段都置NULL

#右连接：右表不管能不能匹配上条件,最终都会保留:能匹配正确的保留; 若不能匹配,左表的字段都置NULL。

9.REGEXP正则

#匹配包含某个字符的记录
SELECT * FROM student WHERE sname REGEXP  '刘';
#匹配字段包含数字的记录
SELECT * FROM student WHERE sname REGEXP  '\\d';
SELECT * FROM student WHERE sage REGEXP  '[0-9]';
#匹配包含数字5位以上的记录
SELECT * FROM student WHERE sname REGEXP  '\\d{5}';


#匹配'王'开头
SELECT * FROM student WHERE sname REGEXP  '^王';
#匹配'菲'结尾
SELECT * FROM student WHERE sname REGEXP  '菲$';


#匹配整型至少3位开始
SELECT * FROM student WHERE sage REGEXP  '^\\d{3,}$';
#匹配整型3到5位
SELECT * FROM student WHERE sage REGEXP  '^\\d{3,5}$';


#匹配包含"陈"或"菲"的记录
SELECT * FROM student WHERE sname REGEXP  '陈|菲';

10.联合查询

联合查询结果是将多个select语句的查询结果合并到一块，因为在某种情况下需要将几个select语句查询的结果合并起来显示。需要两个表结构一样

其中union选项有两个选项可选

all：表示无论重复都输出
distinct: 去重(整个重复)（默认的）

select * from student
union all
select * from student111;

备注：不加all，就会过滤相同的记录

11.EXISTS的用法

EXISTS用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值True或False
EXISTS 指定一个子查询，检测行的存在。

select  * from student  
where  sid<10 and EXISTS(
select  sage from  student  
where ssex='女' and sage<30 and creation_time like '2021-10%' GROUP BY sage
);

备注：如果子查询结果为空，那么整体查询也为空

插入：

#指定全字段插入
INSERT INTO student(sid,sname,sage,ssex,creation_time) VALUES('19','招佳轩',32,'女','2021-10-07 16:48:31');
#指定部分字段插入
INSERT INTO student(sid,sname,sage,creation_time) VALUES('20','招佳轩',32,'2021-10-07 16:48:31');
#直接插入
INSERT INTO student
VALUES('21','招佳轩',32,'男','2021-10-07 16:48:31');
#通过set插入
INSERT INTO student
set sid=23,sname='招佳轩',sage=25,ssex='男',creation_time='2021-10-07 16:48:31';

#先查询将查询结果插入数据表：
INSERT INTO 表名1(字段1) SELECT 字段2 FROM 表名2 WHERE 条件表达式;

更新：

#普通更新
UPDATE student SET ssex = '男',sname = '100' WHERE sage=30；

#连表更新
update student,sc set student.sname='这是测试111',sc.score='59' where student.sid=sc.sid and student.sage=29；

删除：

#指定条件删除记录(不加where条件是清空表、where条件后可加and或o)
DELETE FROM student where sage=30
#删除结构及表
drop table 表名
#删除表字段
alter table 表名 drop column 字段
#删除条件为空的数据
delete from 表名  where 字段 is null

库、表的相关命令：

###################表结构修改############
#修改字段类型 
alter table 表名 modify 字段 字段类型;
#添加新的字段 
alter table 表名 add 字段 字段类型;
#添加字段并指定位置  
alter table 表名 add 字段 字段类型   after 字段;
#删除表字段  
alter table 表名 drop 字段名;
#修改指定的字段  
alter table 表名 change 原字段名字  新的字段名字 字段类型;


#################数据库、表常用查询##############
#查看所有的数据库
show databases; 
#创建一个叫test的数据库
create database test; 
#删除一个叫test的数据库
drop database test;
#选中库 ,在建表之前必须要选择数据库
use test;
#在选中的数据库之中查看所有的表
show tables; 
#创建表
create table 表名 (字段1 类型, 字段2 类型);
#查看所在的表的字段
desc 表名;
#删除表
drop table 表名; 
#查看创建库的详细信息
show create database 库名;
#查看创建表的详细信息
show create table 表名;

四、通用语法：

1.连接数据库

#远程连接
mysql -h 主机地址 -u 用户名 -p 用户密码
#本地连接
mysql -u 用户名 -p

2.查询连接数

#查看最大连接数
show variables like '%max_connections%';

#当前打开的连接的数量
show status like '%Threads_connected%';

#重新设置最大连接数
set global max_connections=1000

#同时使用的连接的最大数目
show status like '%Max_used_connections%';

#发往服务器的查询的数量
show status like '%Questions%'

#尝试已经失败的MySQL服务器的连接的次数
show status like '%Aborted_connects%';

#试图连接MySQL服务器的次数。
show status like  '% Connections%';

3.获取当前时间&日期

#获取当前时间戳
select unix_timestamp(now())；
#获取当前日期+时间
SELECT now();
#获取当前日期
SELECT curdate();
#获取当前时间
SELECT curtime();

4.查看数据库信息&表信息

#查询当前用户
select user();
#查询表创建信息
show create table 表名;
#查询数据库版本
select version();
#查询当前使用的数据库
select database();

5.系统级操作&查询

#查看当前使用的存储引擎
show variables like '%storage_engine%';

#查看数据库中已创建的触发器
SHOW TRIGGERS;

#指定字符编码（一般用在建表的时候）
DEFAULT CHARSET=utf8;

6.用户新增&权限&修改密码

#新增用户 (如果想所有用户可在所有主机访问可把 用户名@登录主机 改为%@%)
grant select on 数据库.* to 用户名@登录主机 identified by "密码";

#允许root用户远程访问所有的数据库
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';

#授权用户
grant all on test.*to'xiaoming'@'localhost';
#刷新权限
flush privileges;
#取消授权
revoke all on test.* from 'xiaoming'@'localhost';

#改密码
mysqladmin -u 用户名 -p 旧密码 password 新密码;

#删除用户
drop user 'xiaoming'@'localhost';

7.mysql备份&恢复

#备份：
语法：mysqldump -h服务器地址 -u登录名 -p 数据库名> 文件名
例子：mysqldump -hlocalhost -uroot db>e:/db.sql

#恢复：
语法：mysql -h服务器地址 -u登录名 -p 数据库名<文件名
例子：mysql -hlocahost -uroot -p db2


 8.其他

#创建A表从B表备份(表备份)
CREATE TABLE A表 AS SELECT * FROM 表B where id=1

#创建表先先判断是否存在,存在就先删除
DROP TABLE IF EXISTS `test_chen`;
CREATE TABLE `test_chen` (
`sid` int(11) NOT NULL COMMENT '学生id',
`sname` varchar(30) DEFAULT NULL COMMENT '学生姓名'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

#用文本方式将数据装入数据表中、
LOAD DATA LOCAL INFILE “D:/mysql.txt” INTO TABLE 表名;



#查询字段在哪些表存在
SELECT * FROM  information_schema.COLUMNS  WHERE  COLUMN_NAME='字段';

#查看表的创建时间
select CREATE_TIME from INFORMATION_SCHEMA.TABLES where TABLE_NAME='表名';

#查看表结构引擎及修改结构时间
select * from information_schema.tables where table_schema='db_name' and table_name='table_name';






相关连接：
https://blog.csdn.net/weixin_39411321/article/details/90602030 ............................mysql联合查询、连接查询、子查询
 ............................................MySQL（学生表、教师表、课程表、成绩表）多表查询
 ..................................................mysql操作记录查看

数据

mysql常用语句操作

概述：

一、表结构&建表：

三、常用语法：

查询：

插入：

更新：

删除：

库、表的相关命令：

四、通用语法：

相关

【OF框架】定义框架标准WebApi，按照规范返回状态信息及数据信息

Oracle表空间数据文件迁移

数据结构--栈（C语言实现）

scrapy获取汽车之家数据

ceph数据recovery配置策略（数据recovery流量控制）

jmeter CSV 数据文件设置读取txt文件请求参数中文乱码

linux 恢复mysql 误删除数据

【Gerrit】Gerrit+Postgresql安装方式的数据备份

mysql数据库死锁的产生原因及解决办法

【数据库】快速安装 SQL Server Express

为什么您的公司需要首席数据官

spark 数据流输出

标签