MySQL数据库常用函数和查询

1.聚合函数
使用聚合函数方便进行数据统计
聚合函数不能在where中使用
常用聚合函数
count():查询总记录数
max():查询最大值
min():查询最小值
sum():求和
avg():求平均值

1.1查询总记录数
count()表示计算总行数，括号中也可以使用字段名
示例：查询学生总数
select count() from students;

1.2查询最大值
max(列)表示求此列的最大值
示例：查询女生的最大年龄
select max(age) from students where sex=‘女’;

1.3查询最小值
min(列)表示求此列的最小值
示例：查询1班的最小年龄
select min(age) from students;

1.4求和
sum(列)表示求此列的和
示例：查询北京学生的年龄总和
select sum(age) from students where hometown=‘北京’;

1.5求平均值
avg(列)表示求此列的平均值
示例：查询女生的平均年龄
select avg(age) from students where sex=‘女’

数据操作-增删改查
1.简单查询
selectfrom表名
例：查询所有学生数据
selectfromstudents
2.添加数据
2.1添加一行数据
格式一：所有字段设置值，值的顺序与表中字段的顺序对应
说明：主键列是自动增长，插入时需要占位，通常使用0或者default或者null来占位，插入
成功后以实际数据为准
insertinto表名values(…)
例：插入一个学生，设置所有字段的信息
insertintostudentsvalues(0,‘亚瑟’,22,177.56)
格式二：部分字段设置值，值的顺序与给出的字段顺序对应
insertinto表名(字段1,…)values(值1,…)
例：插入一个学生，只设置姓名
insertintostudents(name)values(‘老夫子’)
2.2添加多行数据
方式一：写多条insert语句，语句之间用英文分号隔开
insertintostudents(name)values(‘老夫子2’);
insertintostudents(name)values(‘老夫子3’);
insertintostudentsvalues(0,‘亚瑟2’,23,167.56)
方式二：写一条insert语句，设置多条数据，数据之间用英文逗号隔开
格式一：insertinto表名values(…),(…)…
例：插入多个学生，设置所有字段的信息
insertintostudentsvalues(0,‘亚瑟3’,23,167.56),(0,‘亚瑟4’,23,167.56)
格式二：insertinto表名(列1,…)values(值1,…),(值1,…)…
例：插入多个学生，只设置姓名
insertintostudents(name)values(‘老夫子5’),(‘老夫子6’)
3.修改
update表名set列1=值1,列2=值2…where条件
例：修改id为5的学生数据，姓名改为狄仁杰，年龄改为20
updatestudentssetname=‘狄仁杰’,age=20whereid=5
4.删除
格式一：deletefrom表名where条件
例：删除id为6的学生数据
deletefromstudentswhereid=6
逻辑删除：对于重要的数据，不能轻易执行delete语句进行删除。因为一旦删除，数据无法恢
复，这时可以进行逻辑删除。
1、给表添加字段，代表数据是否删除，一般起名isdelete，0代表未删除，1代表删除，默认值为0
2、当要删除某条数据时，只需要设置这条数据的isdelete字段为1
3、以后在查询数据时，只查询出isdelete为0的数据
例：
1、给学生表添加字段(isdelete)，默认值为0，
如果表中已经有数据，需要把所有数据的isdelete字段更新为0
updatestudentssetisdelete=0
2、删除id为1的学生
updatestudentssetisdelete=1whereid=1
3、查询未删除的数据
selectfromstudentswhereisdelete=0
格式二：truncatetable表名（删除表的所有数据，保留表结构）
例：删除学生表的所有数据
truncatetablestudents
格式三：droptable表名（删除表，所有数据和表结构都删掉）
例：删除学生表
droptablestudents
Truncate、Delete、Drop的区别
1、Delete删除数据时，即使删除所有数据，其中的自增长字段不会从1开始
2、Truncate删除数据时，其中的自增长字段恢复从1开始
3、Drop是删除表，所有数据和表结构都删掉
总结
在速度上，drop>truncate>delete
如果想删除部分数据用delete，注意带上where子句
如果想删除表，用drop
如果想保留表而将所有数据删除，自增长字段恢复从1开始，用truncate
数据操作-查询
1.数据准备
1.1创建数据表
droptableifexistsstudents;
createtablestudents(
studentNovarchar(10)primarykey,
namevarchar(10),
sexvarchar(1),
hometownvarchar(20),
agetinyint(4),
classvarchar(10),
cardvarchar(20)
);
1.2插入数据
insertintostudentsvalues
(‘001’,‘王昭君’,‘女’,‘北京’,‘20’,‘1班’,‘340322199001247654’),
(‘002’,‘诸葛亮’,‘男’,‘上海’,‘18’,‘2班’,‘340322199002242354’),
(‘003’,‘张飞’,‘男’,‘南京’,‘24’,‘3班’,‘340322199003247654’),
(‘004’,‘白起’,‘男’,‘安徽’,‘22’,‘4班’,‘340322199005247654’),
(‘005’,‘大乔’,‘女’,‘天津’,‘19’,‘3班’,‘340322199004247654’),
(‘006’,‘孙尚香’,‘女’,‘河北’,‘18’,‘1班’,‘340322199006247654’),
(‘007’,‘百里玄策’,‘男’,‘山西’,‘20’,‘2班’,‘340322199007247654’),
(‘008’,‘小乔’,‘女’,‘河南’,‘15’,‘3班’,null),
(‘009’,‘百里守约’,‘男’,‘湖南’,‘21’,‘1班’,’’),
(‘010’,‘妲己’,‘女’,‘广东’,‘26’,‘2班’,‘340322199607247654’),
(‘011’,‘李白’,‘男’,‘北京’,‘30’,‘4班’,‘340322199005267754’),
(‘012’,‘孙膑’,‘男’,‘新疆’,‘26’,‘3班’,‘340322199000297655’);
2.查询基本语法
2.1查询所有字段
语法：
selectfrom表名

1.1常用的表查询连接方式
内连接：查询的结果为两个表匹配到的数据
语法：
selectfrom表1
innerjoin表2on表1.列=表2.列
例1：查询学生信息及学生的成绩
select
*
from
studentsstu
innerjoinscoressconstu.studentNo=sc.studentNo
扩展：内连接的另一种语法
selectfrom表1,表2where表1.列=表2.列
例1：查询学生信息及学生的成绩
select
*
from
studentsstu,
scoressc
where
stu.studentNo=sc.studentNo
例2：查询课程信息及课程的成绩
select
*
from
coursescs
innerjoinscoressconcs.courseNo=sc.courseNo
例3：查询学生信息及学生的课程对应的成绩
select
*
from
studentsstu
innerjoinscoressconstu.studentNo=sc.studentNo
innerjoincoursescsoncs.courseNo=sc.courseNo
例4：查询王昭君的成绩，要求显示姓名、课程号、成绩
select
stu.name,
sc.courseNo,
sc.score
from
studentsstu
innerjoinscoressconstu.studentNo=sc.studentNo
where
stu.name=‘王昭君’
例5：查询王昭君的数据库成绩，要求显示姓名、课程名、成绩
select
stu.name,
cs.name,
sc.score
from
studentsstuinnerjoinscoressconstu.studentNo=sc.studentNo
innerjoincoursescsonsc.courseNo=cs.courseNo
where
stu.name='王昭君’andcs.name=‘数据库’
例6：查询所有学生的数据库成绩，要求显示姓名、课程名、成绩
select
stu.name,
cs.name,
sc.score
from
studentsstu
innerjoinscoressconstu.studentNo=sc.studentNo
innerjoincoursescsonsc.courseNo=cs.courseNo
where
cs.name=‘数据库’
例7：查询男生中最高成绩，要求显示姓名、课程名、成绩
select
stu.name,
cs.name,
sc.score
from
studentsstu
innerjoinscoressconstu.studentNo=sc.studentNo
innerjoincoursescsonsc.courseNo=cs.courseNo
where
stu.sex=‘男’
orderby
sc.scoredesc
limit1