目录
一、常见SQL语句用法、演示
前置条件
注释:--注释说明
快捷注释键:Ctrl +/
确定MySQL数据库的IP地址:ipconfig
确认MySQL数据库服务是否开启:netstrat -anptu | grep 端口号
连接命令
MySQL -h数据库IP -P端口号 -u数据库登录用户 -p数据库登录密码
注:连本机无需写-h、-p
1.1常用数据库类型
整数:int --有符号(有负数)无符号(整数)
小数:decimal
实例:
--表示该字段可存5位数,2位小数
decimal(5,2);
字符串:varchar 字母/中文/点
实例:
varchar(3) varchar('数字')
1.2数据约束(数据表中)
主键(primary key):物理上存储的顺序
非空(not null):此字段不允许填写空值
唯一(unique):此字段的值不允许重复
默认值(default):当不填写此值时会使用默认真,填写时以填写为准
外键(foreign key):维护两个表之间的联系
1.3数据库备份、还原
备份:选中数据库 右键 转储SQL文件 结构和数据 存入电脑
还原:选中数据库 右键 执行SQL文件 选择电脑中的备份SQL文件 开始选中表 刷新
二、操作数据库
🚩[ 操作数据库之前要通过命令行工具连接到数据库 ]
2.1常见数据库操作命令
查看所有数据库:show database;(少写了可以后面补)
使用数据库:user数据库名;
查看当前使用的数据库:select database();
创建数据库:create database 数据库名 charset=utf8;
删除数据库:drop database 数据库名;
2.2操作数据表
【操作数据之前要先通过use打开对应的数据库】
常见数据表操作命令:查看当前数据库所有的表:show tables;
查看表结构:desc表名;
查看表的创建语句:show create table 表名;
创建数据库表
create table 表名(
字段名1 类型 约束,
字段名2 类型 约束,
... ... ...
);
实例:
--创建学生表,要求:年龄姓名(长度为10),年龄,身高(保留2位小数)
create table students(
id int unsigned primary key auto_increment,
name varchar(20),
age int unsigned,
height decimal(5,2));
2.3删除数据库表
注释:--注释说明
drop table 表名;
快捷注释键:Ctrl +/
drop table if exists 表名;
2.4在数据表中添加一行/多行数据
注:--必须英文逗号
insert into 表名 values (...),(...) 例:(0,'小明',22,168);
insert into 表名 (字段1,字段2,...) values (值1,值2,...) (值1,值2,...);
2.5简单查询
--(查询所有字段)
select * from 表名;
2.6修改数据
update 表名 set 字段名1=值1,字段名2=值2 ....where 条件;
实例:
--修改ID为5的学生数据,姓名改为小张,年龄改为18岁
update students set name ='小张',age=18 where id=5;
2.7删除数据
delete from 表名 where 条件(物理删除,不用)
实例:
--删除第一行
delete from students where id =1;
常用逻辑删除:通过设定一个字段来标识当前记录已经删除
--(只删数据)
truncate table 表名;
--(删除所有数据和表结构)
drop table 表名;
说明:
delete --删除数据时,若新增数据,新增数据的id是删除的id号的后一个
truncate --删除数据后,若新增数据,是从id=1开始的
2.8数据查询
--查询部分字段数据
select 字段1,字段2, ... from 表名;
2.9起别名
--表起别名
select 别名.字段1,别名.字段2, ...from 表名 as 别名;
--字段起别名
select 字段1 as 别名1,字段2 as 别名2, ...from 表名;
2.10去重
select distinct 字段1, ...from 表名;
2.11条件查询
select 字段1,字段2,...from 表名 where 条件;
说明:
where 支持多种运算符进行条件处理
比较运算:=、>、>=、<、<=、!=
逻辑运算:and、or、not
模糊查询:关键字like、匹配任意多个字符%、匹配一个任意字符:-
范围查询:连续范围内between、非连续范围in
空判断:判断为空is null、为非空is not null
2.11.1比较运算
实例:
--查询小张年龄:
select age from 表名 where name='小张';
--查询18岁以下学生:
select * from 表名 where age <18;
--查询家乡不在西藏的学生:
select * from 表名 where hometown !='西藏';
2.11.2逻辑运算
实例:
--查询年龄小于18的女同学
select * from 表名 where age <18 and sex='女';
--查询女同学或1班的学生
select * from 表名 where class='1班' or sex='女';
--查询非西藏的学生
select * from 表名 where not hometown='西藏';
2.11.3模糊查询
实例:
--查询姓张的学生
select * from 表名 where name like '张%';
--查询姓张且名字是一个字的学生
select * from 表名 where name like '张_';
--查询姓名以美结尾的学生
select * from 表名 where name like '%美';
--查询姓名中包含丽的学生
select * from 表名 where name like '%丽%';
2.11.4范围查询
实例:
--查询家乡是北京/上海/西藏的学生
select * from 表名 where hometown in ('北京','上海','西藏');
--查询年龄为12-18岁的学生
select * from 表名 where age between 12 and 18;
2.11.5空判断
实例:
--查询没有填写身份证的学生
select * from 表名 where cad is null;
--查询填写了身份证的学生
select * from 表名 where cad is not null;
2.12排序
select * from 表名 order by 字段名1 ase/desc,字段2 ase/desc,...;
说明:
ase:升序(默认可以不写)
desc:降序
实例:
--查询所有学生信息,按年龄从小到大排序,年龄相同时,再按学员从小到大排
select * from 表名 order by age,studentsNo;
2.13分组和聚合
聚合函数作用:方便进行数据统计 --注!不能在where中使用!!!
count() --查询总记录数
实例:
--查询学生总数:
select count(*) from students;
--查询1班年龄小于18岁的同学有几个
select count(*) from students where class='1班' and age<18;
max(字段名): --查询最大值
实例:
--查询女生的最大年龄:
select max(age) from st where sex='女';
min(字段名): --查询最小值
sum(字段名): --求和
avg(字段名): --求平均值
实例:
--查询所有学生的最大年龄、最小年龄、平均年龄
select max(age),min(age),avg(age) from students;
--查询1班年龄小于18岁的同学有几个
select count(*) from st where class='1班' and age<18;
2.14分组查询
作用:按照字段分组,此字段相同的数据会放到同一个组中
目的:使用聚合函数,对每一组的数据进行统计
select 字段1,字段2,聚合函数... from 表名 group by 字段1,字段2,...;
实例:
--查询各种性别的人数
select sex,count(*) from st group by sex;
--查询每个班级中各种性别的人数
select class,sex,count(*) from st group by class,sex;
--查询各个性别中的总人数、最大年龄、平均年龄
select sex,count(*),max(age),avg(age) from st group by class,sex;
注!分组后再过滤不能用where,要用having
区别:
where是对from后面指定的表进行数据筛选,属于对原始数据的筛选
having是对group by的结果进行筛选
having后面的条件可以用聚合函数,where不能
实例:
--查询每个班级男生的总数
select class,sex,count(*) from st group by class,sex having sex='男';
--查询所有班级中不同性别的记录数大于1的信息
select class,sex,count(*) from st group by class,sex having count(*)>1;
2.15分页查询
作用:用来获取一部分的数据/用来分页
select * from 表名 limit start,count;
说明:
从start开始,获取count条数据
start索引从0开始
实例:
--从第1条到第5条
select * from 表名 limit 0,5;
--从第2条到第6条(若总共4条,则从第2条到第4条)
select * from 表名 limit1,5;
如何实现分页
select * from st limit (n-1) *m,m; --(分页公式)
n:表示显示第几页的数据 m:表示每页显示多少条数据
2.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 = coureses.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语句书写顺序、执行顺序
3.1书写顺序
select ->distinct ->from ->join ->on ->where ->group by ->having ->order by ->limit
3.2执行顺序
from ->on ->join ->where ->group by (开始使用select中的别名,后面的语句中都可以使用别名)->sum、count、max、avg ->having -> select ->distinct ->order by ->limit
跟着April一起学习更多IT相关知识,记得分享给身边的小伙伴哦~💕