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