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.数据约束（数据表中）

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

3.数据库的备份和还原

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

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

1.常见数据库操作命令

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

2.操作数据表

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

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

3.删除数据库表

droptable 表名;                                             注释：--注释说明droptableifexists 表名;                                    快捷注释键：ctrl +/

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

格式如下： --英文逗号insertinto 表名 values(...),(...) 例：（0,'小明',22,168）
insertinto 表名 （字段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=20where id =5

7.删除数据

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

8.数据查询

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

9.起别名

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

10.去重

格式：
selectdistinct 字段1，...from 表名

11.条件查询

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

比较运算

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

逻辑运算

 例 ：
 1、查询年龄小于20的女同学：
 select*from 表名 where age ＜20and sex ='女';2、查询女学生或1班的学生：
select*from 表名 where class=‘1班’ or sex ='女';3、查询非新疆的学生：
select*from 表名 wherenot 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 between18and20;

空判断

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

12.排序

格式：
select*from 表名 orderby 字段名1 ase/desc,字段名2 ase/desc,...;
说明：ase:升序（默认可以不写）  desc:降序
例：查询所有学生信息，按年龄从小到大排序，年龄相同时，再按学号从小到大排：
select*from 表名 orderby 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、查询所有学生的最大年龄、最小年龄、平均年龄：
selectmax(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 表名 groupby 字段1,字段2,...;
例：
1、查询各种性别的人数：
select sex,count（*）from st groupby sex;2、查询每个班级中各种性别的人数：
select class，sex，count（*）from st groupby class,sex;3、查询各个性别中的总人数、最大年龄、平均年龄：
select sex，count（*）,max（age）,avg（age）from st groupby class,sex;
注！分组后再过滤不能用where，要用having  
区别：
1、where是对from后面指定的表进行数据筛选，属于对原始数据的筛选
2、having是对groupby的结果进行筛选
3、having后面的条件可以用聚合函数，where不能
例：
1、查询每个班级男生的总数：
select class,sex,count（*）from st groupby class,sex having sex='男';2、查询所有班级中不同性别的记录数大于1的信息：
select class,sex,count（*）from st groupby class,sex having count（*）>1;

15.分页查询

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

16.连接查询

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

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

1.书写顺序

​ select->distinct->from->join->on->where->groupby->having->orderby->limit

2.执行顺序

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

四、后续在更新进阶