MYSQL 二、SQL语句总结

一、navicat 操作快捷键

        一般都用naviact来操作数据库，故总结一下相关的快捷键：

CTRL+L                                                 历史日志
CTRL+TAB 或 SHIFT+CTRL+TAB       下一个窗口或选项卡
CTRL+Q                                                新建查询
CTRL+F                                                 查找字段
F3                                                           查找下一个字段
F5                                                           刷新
ESC                                                        全选
CTRL+D                                                设计表
SHIFT+CTRL+R                                    运行当前语句

       二、相关sql语句总结：

        1）对库的操作（不常用）

--创建库
create database 库名;
--创建库时判断库是否存在，不存在则创建
create database if not exists 库名;
--查看所有数据库
show databases;
--使用指定数据库
use 库名;
--查看当前指定数据库包含的数据表
show tables;
--查看数据库的结构定义信息
show create database 库名;
--删除数据库
drop database 库名;
--修改数据库的字符集为utf8
alter database 库名 character set utf8;

        2） 对表的操作：创建表、删除表、在表中增加字段、在表中减少字段，修改表字段名等

        对表的操作：

--删除表
drop table 表名;

--删除表时判断表是否存在,若存在则删除
drop table if exists test111 ;



--创建表
create table test111 (

test_id bigint not null comment '主键id',
test_name varchar(50) default null comment '测试名称',
test_age int default null comment '测试年龄',
test_data date default null comment '测试日期',
primary key (`test_id`)
) comment '测试表'


-查看表结构
desc 表名;


--查看创建表的SQL语句
show create table 表名;


--修改表名
alter table 表名 rename to 新的表名;

      对字段的操作：

--添加一个新的字段
alter table 表名 add 字段; 字段类型;


--修改字段名
alter table 表名 rename column 字段名 to 新的字段名;


--修改字段类型(注意类型修改前后数据是否兼容)
alter table 表名 modify column 字段名 新的字段类型;


--删除一个字段
alter table 表名 drop 字段名;

--添加一个字段
alter table test222  add test_year  varchar(20)  not null default '2022' after test_name 

 3）对数据的操作（增删改）

        1、插入数据

--有多少个字段,就要写多少个值,且是一一对应的
insert into 表名 values(值1,值2,值3...值n);


--此方法要写出要插入的字段,并一一对应插入值
insert into 表名(字段1,字段2...字段n) values(值1,值2...值n);

        2、删除数据（delete、truncate）

--删除表中所有数据
delete from 表名;


--删除表中指定的数据
delete from 表名 where 字段 = 值;


--删除表中所有数据(先删除整张表,然后创建一张一样的空表，此方法更高效)
truncate table 表名;

        3、修改数据(update) 

--无限制条件的修改,会修改整张表
update 表名 set 字段 = 值;


--有限制条件的修改,只修改特定记录
update 表名 set 字段 = 值 where 条件(字段 = 值);

4)关于查询的操作

        1.普通条件查询

--查询表中所有数据
select *from 表名;


-- 查询在...到...之间(between and / && / and)
--查询users表中年龄在18~25岁之间的记录
--方式1 between..and..
select *from users where age between 18 and 25;
--方式2 &&
select *from users where age>=18 && age<=25;
--方式3 and
select *from users where age>=18 and age<=25;



--单个条件(or / in)
--查询users表中年龄为18,20,25岁的记录
--方式1 or
select *from users where age=18 or age=20 or age=25;
--方式2 in
select *from users where age in(18,20,25);



--多个条件(and)
--查询users表中年龄为23，性别为女，名字为小楠的记录
select *from users where age=23 and gender='女' and name='小楠'; 


--查询不为NULL值(is not null),为NULL值(is null)
--查询users表中序号不为空的记录
select *from users where id is not null;
--查询user表中序号为空的记录
select *from users where id is null;



--模糊查询（like）
--查询users表中所在城市不相同的记录
--select distinct 字段 from 表名;
select distinct city from users;



--去除重复记录查询（distinct）
--查询users表中记录，并以年龄升序排序
select *from users order by age; --默认升序
 

--排序查询（order by）
--查询users表中记录，并以年龄降序排序
select *from users order by age desc;--desc降序


--查询users表中记录，并体育成绩降序，年龄降序
select *from users order by PE desc,age desc;

        2.使用聚合函数查询

①计算和（sum）
select sum(字段) (as sumvalue) from 表名;


②计算最大值（max）
select max(字段) (as maxvalue) from 表名;


③计算最小值（min）
select min(字段) (as minvalue) from 表名;


④计算平均值（avg）
select avg(字段) (as avgvalue) from 表名;



⑤计算个数（count）
select count(字段) (as totalcount) from 表名;

        3.分组、分页查询

分组查询（group by）
--查询users表中的记录，按照性别分组，查询男，女的体育成绩平均分
select gender,avg(PE) from users group by gender;
--查询users表中的记录，按照性别分组,分别查询男、女的体育成绩平均分,人数
select gender, avg(PE),count(id) from users group by gender;
--查询users表中的记录, 按照性别分组,分别查询男、女的体育成绩平均分,人数 要求:分数低于60分的人，不参与分组
select gender, avg(PE),count(id) from users where PE > 60 group by gender; 
 --查询users表中的记录,按照性别分组,分别查询男、女的体育成绩平均分,人数 要求:分数低于60分的人，不参与分组,分组之后,人数要大于2个人
select gender,avg(PE),count(id) from users where PE > 60 group by gender having count(id)>2;




分页查询（limit）
注意：第一条记录的索引是0

--查询users表中的前10行条记录
select *from users limit 10;
--查询users表中第2~11条记录 (从第2条记录开始累加10条记录)
select *from users limit 1,10;
--查询users表中第5~17条记录 (从第5条记录开始累加13条记录)

        4.内连接、外连接、子查询

内连接查询
--语法1 （隐式内连接）
select 字段1,字段2...
from 表1,表2...
where 过滤条件;


 
--语法2 （显式内连接）
select 字段1,字段2...
from 表1 inner join 表2 ...





外连接查询
外连接查询分为左外连接查询和右外连接查询
--左外连接
select 字段1,字段2..
from 表1 left (outer) join 表2 on 过滤条件;
--右外连接
select 字段1,字段2..
from 表1 right (outer) join 表2 on 过滤条件;

区别如下：

左外连接：是表1和表2的交集再并上表1的其他数据
右外连接：是表1和表2的交集再并上表2的其他数据




子查询就是将查询语句作为条件：
select * from user where user_name in ( select user_name from user where ....)