一,mysql数据库基础:
1)web时代使用最广泛的关系型数据库
- oracle:在大型项目中,银行,电信等项目
- mysql:web时代使用最广泛的关系型数据库
- ms sql server:在微软的项目中使用
- sqlite:轻量级数据库,主要应用在移动平台
2)sql语言:
- 数据库中进行操作的语言,结构化查询语言,可操作oracle,sql server,mysql,sqlite 等等所有的关系型的数据
- 不区分大小写,专门用来操作数据库
- 测试工程师,重点在数据的查询
- DQL:数据查询语言
3)常用数据类型
- decimal(5,2) 共5位数,2位小数,3位整数
- varchar(3) 3个字符,中文和字母都算一个
- datetime 日期
- int 整数
4)约束
- 主键(primary key):物理上存储的顺序
- 非空(not null):此字段不允许填写空值
- 惟一(unique):此字段的值不允许重复
- 默认值(default):当不填写此值时会使用默认值,如果填写时以填写为准
- 外键(foreign key):维护两个表之间的关联关系
5)关系型数据库核心元素
- 数据行(一条记录)
- 数据列(字段)
- 数据表(数据行的集合)
- 数据库(数据表的集合,一个数据库中有n多个数据表)
二,数据表的操作:
1)创建表
create table 表名(
字段名 类型 约束,
字段名 类型 约束
。。。
)
create table students(
id int unsigned primary key auto_increment,
name varchar(10),
age int unsigned,
height decimal(5,2)
)
2)删除表
- drop table 表名
- drop table if exists 表名
三,数据操作
1)增
1,单条新增:
insert into students values(0,'亚瑟',22,177.56)
主键列是自动增长,插入时需要占位,通常使用0或者 default 或者 null 来占位,插入成功后以实际数据为准
insert into students(name) values('老夫子')
部分字段设置值,值的顺序与给出的字段顺序对应
2,多行添加:
方式一:
多条insert,英文分号隔开
insert into students(name) values(‘老夫子2’);
insert into students(name) values(‘老夫子3’);
insert into students values(0,‘亚瑟2’,23,167.56)
方式二:
一条insert语句,设置多条数据,英文逗号隔开
insert into students values(0,‘亚瑟3’,23,167.56),(0,‘亚瑟4’,23,167.56)
insert into students(name) values(‘老夫子5’),(‘老夫子6’)
2)删
delete from 表名 where 条件
对于重要的数据,不能轻易执行delete语句进行删除,一旦删除,数据无法恢复,这时可以进行逻辑删除。
逻辑删除:
- 给表添加字段,代表数据是否删除,一般起名isdelete,0代表未删除,1代表删除,默认值为0
- 当要删除某条数据时,只需要设置这条数据的isdelete字段为1
- 以后在查询数据时,只查询出isdelete为0的数据
3)改
update students set isdelete=0
4)查
select * from XXX
1,as 起别名:
– 表名.字段名
select students.name,students.age from students
– 可以通过 as 给表起别名
select s.name,s.age from students as s
– 如果是单表查询 可以省略表名
select name,age from students
–使用as给字段起别名
select studentNo as 学号,name as 名字,sex as 性别 from students
2,消除重复行:
**select distinct sex from students;**
3,查询条件:
where语句对表中的数据进行筛选
select * from students where id=1;
一,运算符:
where后面支持多种运算符,进行条件的处理:
- 比较运算
- 逻辑运算
- 模糊查询
- 范围查询
- 空判断
比较运算符:
不等于:!= 或 <>
逻辑运算符:
- and
- or
- not
模糊查询:
- like
- %表示任意多个任意字符
- _表示一个任意字符
-
select * from students where name like '孙%'(查询姓孙的学生)
范围查询:
- in表示在一个非连续的范围内
-
select * from students where hometown in('北京','上海','广东')
- between … and …表示在一个连续的范围内
-
select * from students where age between 18 and 20
空判断:
- 注意:null与’'是不同的
-
select * from students where card is null(查询没有填写身份证的学生)
- 判空:is null
-
select * from students where card is not null(查询填写了身份证的学生)
二,排序:order by
- 语法:
select * from 表名
order by 列1 asc|desc,列2 asc|desc,..
- 将行数据按照列1进行排序,如果某些行列1的值相同时,则按照列2排序,以此类推
- 默认按照列值从小到大排列
- asc从小到大排列,即升序
- desc从大到小排序,即降序
- EG:查询所有学生信息,按年龄从大到小排序,年龄相同时,再按学号从小到大排序
-
select * from students order by age desc,studentNo
三,聚合函数:
- count(*) 查询总数
- max(列) 查最大
- min(列) 查最小
- sum(列) 查总和
- avg(列) 查平均值
-
EG:select avg(age) from students where sex='女'
四,分组:group by
- 按照给的字段(变量名)分组,相同的数据分在一组
- 可以对分组后的数据进行统计,做聚合运算
- eg:
查询各种年龄的人数
select age,count(*) from students group by age
数据筛选where 和 having:
- where是对from后面指定的表进行数据筛选,属于对原始数据的筛选
-
select count(*) from students where sex='男'
- having是对group by的结果进行筛选
-
select sex,count(*) from students group by sex having sex='男'
获取部分行:
select * from 表名 limit start,count
- 从start开始,获取count条数据
- start索引从0开始
-
select * from students limit 0,3 (查询前3行学生信息)
五,分页:
- 已知:每页显示m条数据,求:显示第n页的数据
-
select * from students limit (n-1)*m,m
- 求总页数
查询总条数p1
使用p1除以m得到p2
如果整除则p2为总数页
如果不整除则p2+1为总页数
六,连接查询:
- 等值连接查询:inner join
-
eg:查询学生信息及学生的成绩 select * from students stu,scores sc where stu.studentNo = sc.studentNo 等效于 select * from students stu inner join scores sc on stu.studentNo = sc.studentNo
-
eg:查询男生中最高成绩,要求显示姓名、课程名、成绩 select stu.name, cs.name, sc.score from students stu, scores sc, courses cs where stu.studentNo = sc.studentNo and sc.courseNo = cs.courseNo and stu.sex = '男' order by sc.score desc limit 1 --------------------------------------- select stu.name, cs.name, sc.score from students stu inner join scores sc on stu.studentNo = sc.studentNo inner join courses cs on sc.courseNo = cs.courseNo where stu.sex = '男' order by sc.score desc limit 1
- 左,右连接
- left join 相同列和左边的所有
- right join 相同列和右边所有
-
select * from scores sc right join courses cs on cs.courseNo = sc.courseNo left join students stu on stu.studentNo = sc.studentNo
七,子查询
select语句的嵌套:
- 主要查询的对象,第一条 select 语句
- 子查询是嵌入到主查询中
- 子查询是辅助主查询的,要么充当条件,要么充当数据源
- 子查询是可以独立存在的语句,是一条完整的 select 语句
子查询分类:
- 标量子查询: 子查询返回的结果是一个数据(一行一列)
- 列子查询: 返回的结果是一列(一列多行)
- 行子查询: 返回的结果是一行(一行多列)
- 表级子查询: 返回的结果是多行多列
eg:
标量子查询
例1:查询班级学生的平均年龄
查询班级学生平均年龄
select avg(age) from students
查询大于平均年龄的学生
select * from students where age > 21.4167
等效于
select * from students where age > (select avg(age) from students);
例2:查询王昭君的成绩,要求显示成绩
学生表中查询王昭君的学号
select studentNo from students where name = '王昭君'
成绩表中根据学号查询成绩
select * from scores where studentNo = '001'
等效于
select * from scores where studentNo = (select studentNo from students where name = '王昭君')
列级子查询
例3:查询18岁的学生的成绩,要求显示成绩
学生表中查询18岁的学生的学号
select studentNo from students where age=18
成绩表中根据学号查询成绩
select * from scores where studentNo in ('002','006')
等效于
select * from scores where studentNo in (select studentNo from students where age=18)
行级子查询
例4:查询男生中年龄最大的学生信息
select * from students where sex='男' and age=(select max(age) from students)
select * from students where (sex,age)=('男',30)
等效于
select * from students where (sex,age) = (select sex,age from students where sex='男' order by age desc limit 1)
表级子查询
例5:查询数据库和系统测试的课程成绩
select
*
from
scores s
inner join
(select * from courses where name in ('数据库','系统测试')) c
on s.courseNo = c.courseNo
子查询中特定关键字使用
-
in 范围
-
格式: 主查询 where 条件 in (列子查询)
-
any | some 任意一个
-
格式: 主查询 where 列 = any (列子查询) 在条件查询的结果中匹配任意一个即可,等价于 in
-
all
-
格式: 主查询 where 列 = all(列子查询) : 等于里面所有 格式: 主查询 where 列 <>all(列子查询) : 不等一其中所有
-
select * from students where age in (select age from students where age between 18 and 20)