MySQL语句最全详解

MySQL语句最全详解

一、常见sql语句用法与演示

前置条件

	注释：--注释说明
	快捷注释键：ctrl +/

1、确定mysql数据库的ip地址：ifgonfig
2、确认mysql数据库服务是否开启：netstat -anptu | grep 端口号

连接命令

mysql -h数据库ip -P端口号 -u数据库登录用户 -p数据库登录密码
注：连本机无需写-h和-P

1.常用数据库类型

整型：int 	--有符号（有负数）无符号（整数）
小数：decimal  
例：decimal（5,2）--表示该字段可存5位数，2位小数
字符串：varchar  字母/中文/点  
例：varchar（3）varchar（'数学'）

2.数据约束（数据表中）

主键（primary key）：物理上存储的顺序
非空（not null）：此字段不允许填写空值
唯一（unique）：此字段的值不允许重复
默认值（default）：当不填写此值时会使用默认值，填写时以填写为准
外键（foreign key）：维护两个表之间的联系

3.数据库的备份和还原

备份：选中数据库  右键  转储SQL文件  结构和数据  存入电脑
还原：选中数据库  右键  执行SQL文件  选择电脑中的备份sql文件  开始  选中表  刷新

二、操作数据库（操作数据库之前要通过命令行工具连接到数据库）

1.常见数据库操作命令

查看所有数据库：show databases;（少写了可以后面补）
使用数据库：use数据库名;
查看当前使用的数据库：select database（）;
创建数据库：creat database 数据库名 charset=utf8;
删除数据库：drop database 数据库名;

2.操作数据表

（操作数据表之前要先通过use打开对应的数据库）
常见数据表操作命令：查看当前数据库所有的表：show tables;
查看表结构：desc表名;
查看表的创建语句：show creat table 表名;
创建数据库表：格式：creat table 表名（         
字段名1 类型 约束,
字段名2 类型 约束,
...            ...      ...
);

例：
创建学生表,要求：年龄姓名（长度为10），年龄，身高（保留2位小数）
creat table students（       
id int unsigned primary key auto_increment,
name varchar(20), 
age int unsigned,
height decimal(5,2) 
); 

3.删除数据库表

drop table 表名;                			 				注释：--注释说明
drop table if exists 表名;               		 			快捷注释键：ctrl +/

4.在数据表中添加一行/多行数据

格式如下： --英文逗号
insert into 表名 values (...),(...) 例：（0,'小明',22,168）
insert into 表名 （字段1，字段2，...）values（值1，值2，...）（值1，值2，...）

5.简单查询

select *from 表名;--（查询所有字段）

6.修改数据

格式：
update 表名 set 字段名1=值1,字段名2=值2 ...where 条件
例：
修改id为5的学生数据，姓名改为小红，年龄改为20岁
update students set name ='小红',age=20 where id =5

7.删除数据

delete from 表名 where 条件（物理删除，不用）
例：delete from students where id=1; --删除第一行
常用逻辑删除：通过设定一个字段来标识当前记录已经删除
truncate table 表名--（只删数据）
drop table 表名 --（删除所有数据和表结构）
说明：
delete --删除数据时，若新增数据，新增数据的id是删除的id号的后一个
truncate --删除数据后，若新增数据，是从id=1开始的

8.数据查询

查询部分字段数据  格式：
select 字段1，字段2，...from 表名；

9.起别名

select 别名.字段1，别名.字段2，...from 表名 as 别名	--（表起别名）
select 字段1 as 别名1，字段2 as 别名2，...from 表名	--（字段起别名）

10.去重

格式：
select distinct 字段1，...from 表名

11.条件查询

格式：select 字段1,字段2,...from 表名 where 条件;
说明：where 支持多种运算符进行条件处理
比较运算：=、＞、＞=、＜、＜=、！=
逻辑运算：and、or、not
模糊查询：关键字like、匹配任意多个字符%、 匹配一个任意字符：-
范围查询：连续范围内between、非连续范围in
空判断：判断为空is null、为非空is not null

比较运算

例：
1、查询小豪年龄：
select age from 表名 where name ='小豪';
2、查询20岁以下学生：
select *from 表名 where age ＜20;
3、查询家乡不在新疆的学生：
select * from 表名 where hometown ！='新疆';

逻辑运算

 例 ：
 1、查询年龄小于20的女同学：
 select * from 表名 where age ＜20 and sex ='女';
2、查询女学生或1班的学生：
select * from 表名 where class=‘1班’ or sex ='女';
3、查询非新疆的学生：
select * from 表名 where not hometown='新疆';

模糊查询

例：  
1、查询姓孙的学生：
select * from 表名 where name like '孙%';
2、查询姓孙且名字是一个字的学生： 
select * from 表名 where name like '孙_';
3、查询姓名以乔结尾的学生： 
select * from 表名 where name like '%乔';
4、查询姓名中包含白的学生： 
select * from 表名 where name like '%白%';

范围查询

例：  
1、查询家乡是北京/南京/新疆的学生：
select * from 表名 where hometown in ('北京','南京','新疆');
2、查询年龄为18-20岁的学生：
select * from 表名 where age between 18 and 20;

空判断

例：  
1、查询没有填写身份证的学生：
select * from 表名 where cad is null;
2、查询填写了身份证的学生：
select * from 表名 where cad is not null;

12.排序

格式：
select * from 表名 order by 字段名1 ase/desc,字段名2 ase/desc,...;
说明：ase:升序（默认可以不写）  desc:降序
例：查询所有学生信息，按年龄从小到大排序，年龄相同时，再按学号从小到大排：
select * from 表名 order by age,studentsNo;

13.分组和聚合

聚合函数作用：方便进行数据统计       --注！不能在where中使用！！！
count（）--查询总记录数  
例：
1、查询学生总数：
select count（*）from students;
2、查询1班年龄小于18岁的同学有几个：
select count（*）from students where class='1班' and age＜18;
max（字段名）：--查询最大值 
例：
查询女生的最大年龄：
select max（age）from st where sex='女';
min（字段名）：--查询最小值  
sum（字段名）：--求和  
avg（字段名）：--求平均值  
例：
1、查询所有学生的最大年龄、最小年龄、平均年龄：
select max(age),min(age),avg(age)from students;
2、查询1班年龄小于18岁的同学有几个：
select count（*）from st where class='1班' and age＜18;

14.分组查询

作用：按照字段分组，此字段相同的数据会放到同一个组中
目的：使用聚合函数，对每一组的数据进行统计
格式：
select 字段1,字段2,聚合函数...from 表名 group by 字段1,字段2,...;
例：
1、查询各种性别的人数：
select sex,count（*）from st group by sex;
2、查询每个班级中各种性别的人数：
select class，sex，count（*）from st group by class,sex;
3、查询各个性别中的总人数、最大年龄、平均年龄：
select sex，count（*）,max（age）,avg（age）from st group by class,sex;
注！分组后再过滤不能用where，要用having  
区别：
1、where是对from后面指定的表进行数据筛选，属于对原始数据的筛选
2、having是对group by的结果进行筛选
3、having后面的条件可以用聚合函数，where不能
例：
1、查询每个班级男生的总数：
select class,sex,count（*）from st group by class,sex having sex='男';
2、查询所有班级中不同性别的记录数大于1的信息：
select class,sex,count（*）from st group by class,sex having count（*）>1;

15.分页查询

作用：用来获取一部分的数据/用来分页
格式：select * from 表名 limit start,count;
说明：
1、从start开始，获取count条数据  
2、start索引从0开始
例：
1、select * from 表名 limit 0,5;--从第1条到第5条
2、select * from 表名 limit 1,5;--从第2条到第6条（若总共4条，则从第2条到第4条）
如何实现分页：
select * from st limit（n-1）*m,m; （分页公式）
n：表示显示第几页的数据    m：表示每页显示多少条数据

16.连接查询

内连接：连接两个表时，取得是两个表中都存在的数据  
格式：
select * from 表1 inner join 表2 on 表1.例1=表2.例2;
或 
select * from 表1 ，表2 where 表1.例1=表2.例2;
补充：写表名时可以改名，如students可以写成：
students as stu，之后就可用stu代替表名（as可省略）
例：
1、查询学生信息及学生的课程对应的成绩：
select * from students inner join scores on stu.studentNo=sco.studentsNo
inner join courses on sco.courseNo=courses.courseNo;
2、查询小豪的数据库成绩，要求显示姓名、课程号、成绩：
select students.name,courses.name,scores.score 
from studentsstu 
inner join scoressco 
on stu.studentNo=sco.studentsN 
inner join courses 
on sco.courseNo=courses.courseNo 
where students.name='小豪';
3、查询所有学生的数据库成绩，要求显示姓名、课程号、成绩：
select students.name,courses.name,scores.score 
from students stu 
inner join scoressco 
on stu.studentNo=sco.studentsN 
inner join courses 
on sco.courseNo=courses.courseNo;
4、查询男生中最高成绩，要求显示姓名、课程号、成绩：
select students.name,courses.name,scores.score 
from students stu 
inner join scoressco 
on stu.studentNo=sco.studentsN 
inner join courses 
on sco.courseNo=courses.courseNo 
where students .sex='男' 
order by scores.score 
desc limit 0,1;

三、SQL语句书写顺序和执行顺序

1.书写顺序

​ select->distinct->from->join->on->where->group by->having->order by->limit

2.执行顺序

from->on->join->where->group by（开始使用select中的别名，后面的语句中都可以使用别名）->sum、count、max、avg->having->select->distinct->order by->limit

四、后续在更新进阶.