一、概念
数据库的作用,把数据以表的形式存储起来,方便查询
二、RDBMS
关系型数据库系统
核心是以二维表存储数据
行:一条记录,一个事物的信息
列:字段,一个事物的某一属性
一个表中的所有行是一类事物的集合
一个数据库由很多表组成
三、SQL(structured query language)
结构化查询语言,用于关系型数据库,不区分大小写
主要分为:
- DQL数据查询语言(主要,占80%)
- DML数据操作语言
- TPL事物处理语言
- DCL数据控制语言
- DDL数据定义语言
- CCL指针控制语言
四、navicat使用
1、数据库操作
1、连接数据库客户端
2、输入数据库登录密码,连接测试成功后,即可操作数据库
2、数据表操作
3、数据操作-增删改查
4、数据类型与约束
常用数据类型
- 整数 int 有符号范围(-2147483648~2147483847),无符号(0~4294967295)
- 小数 decimal,如decimal(5,2),表示共存5位数,小数占2位,整数占3位
- 字符串 varchar 范围(0~65533)
- 日期时间 datetime
约束
- 主键:物理上存储的顺序
- 非空:此字段不允许填写空值
- 唯一:此字段的值不允许重复
- 默认值:当不填写时使用默认值,填写时以填写为主
- 外键:维护两表之间的关联关系
5、数据库的备份与恢复
五、SQL语言
1、数据表操作
- 创建表
create table 表名(
字段名 类型 约束,
字段名 类型 约束,
…….)
例:创建学生表,字段要求如下
姓名(长度为10),年龄(无符号),身高(保留小数点2位)
create table student(
id int unsigned primary key auto_increment,
name varchar(10),
age int unsigned,
height decimal(5,2)
)
- 删除表
格式一、drop table 表名(如果表存在删除,如果表不存在报错)
格式二、drop table if exists 表名(如果表存在删除,如果表不存在不报错)
2、数据操作-增删改查
- 插入数据
格式:insert into 表名 values(…)
例:插入一个学生,设置所有字段信息
insert into student values(0,'妖刀',10,180.5)
给指定字段写入数据
insert into student(name) values('茨木')
插入多条数据
insert into student(name) values('茨木1'),('茨木2'),('茨木3')
- 修改
格式:update 表名 set 列1=值1,列2=值2….where 条件
例:修改id为5的学生数据,姓名改为 草,年龄改为 8
update student set name='草',age = 8 where id=5
- 删除
格式:delete from 表名 where 条件
例:删除id为6的学生数据
delete from student where id=6
- 逻辑删除
- 设计表,给表添加一个字段isdelete,1代表删除,0代表没有删除
- 把所有isdelete设置为0
- 要删除一个数据时,将isdelete更新为1
- 要查询数据时,只查询isdelete为0的数据
- 查询
格式:select * from 表名
3、数据操作-查询
- 查询所有项
格式:select * from 表名 - 查询字段(列)
格式:select 字段名1,字段名2.. from 表名
可以为字段起别名
格式:select 字段名1 as 别名1,字段名2 as 别名2.. from 表名
可以为表起别名
格式:select 字段名1,字段名2.. from 表名1 as 别名1,表名2 as 别名…
可以根据表的别名查询特定表的字段
格式:select 别名1.字段名1,别名2.字段名2.. from 表名1 as 别名1,表名2 as 别名2… - 消除重复行
格式:select distinct 字段名1,字段名2 …. from 表名 - 条件
使用where子句对表中的数据筛选,符号条件的数据会出现在结果集中
格式:select 字段名1,字段名2 …. from 表名 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 表名 order by 字段名1 asc|desc, 字段名2 asc|desc…
asc升序,desc降序,不填默认升序
例:查询所有学生信息,按年龄从小到大排序,年龄相同的,在按学号从大到小排序
select * from students order by age, studentN0 desc;
中文排序
select * from students order by convert(name using gbk);
聚合函数
为了快速得到统计数据,经常会遇到如下5个聚合函数
聚合函数不能在where中使用
count(*)表示计算总行数,括号中还可以写字段名
例:查询学生总数select count(*) from students;
max(列),min(列)求列中最大、小值
例:查询女生中年龄最大者select max(age) from students where sex='女';
sum(列),求和
avg(列),求均值- 分组
按照字段分组,表示此字段相同的数据会被放到一个组
分组后,分组的依据列会显示在结果集中,其他列不会显示在结果集中
可以对分组后的数据进行统计,做聚合运算
语法:
select 列1,列2,聚合… from 表名 group by 列1,列2….
例:查询各种性别的人数
select sex.count(*) from students group by sex;
分组后的数据筛选
语法:
select 列1,列2,聚合… from 表名 group by 列1,列2…. having 列1….聚合…
having后的条件运算符与where相同,having必须和group by一起用
例:查询男生总人数
方案一:
select count(*) from students where sex='男';
方案二:
select sex.count(*) from students group by sex having sex='男';
- 获取部分行
语法:
select * from students limit start,count; 分页
已知:每页显示m条数据,求:显示第n页的数据select * from students limit (n-1)*m,m;
求总页数
- 查询总条数p1
- 使用p1除以m得到p2
- 如果整除则p2为总页数
- 如果不整除则p2+1为总页数连接查询
当查询结果的列来源于多张表时,需要将多张表连接成一个大的数据集,再选择合适的列返回
等值连接查询:查询的结果为两个表匹配到的数据select * from students as stu,scores as sc where stu.studentno=sc.studentno;
select * from students as stu,scores as sc会产生笛卡尔积,生成临时表(占用内存),在通过where…筛选数据
内连接查询:
select * from students inner join scores on students.studentno=scores.studentno;
先判定on…条件,再连接表students和表scores
左连接查询:查询的结果为两个表匹配到的数据加左表特有的数据,对于右表不存在的数据使用null填充,在inner join左边的表为左表
select * from students left inner join scores on students.studentno=scores.studentno;
右连接查询:查询的结果为两个表匹配到的数据加右表特有的数据,对于左表不存在的数据使用null填充,在inner join右边的表为左表,
select * from students right inner join scores on students.studentno=scores.studentno;
自连接查询
多查自己几次,并关联起来子查询
在select语句中,嵌入了另外一个select语句,那么被嵌入的select语句称之为子查询语句
- 主查询
主要查询的对象,第一条select语句
- 主查询和子查询的关系
- 子查询是嵌入到主查询中
- 子查询是辅助主查询的,要么充当条件,要么充当数据源
- 子查询可以独立纯在的语句,是一条完整的select语句
子查询的分类
- 标量子查询:子查询返回的数据是一个数据(一行一列)
select * from students where studentno = (select studentno from course where name ='王昭君')
- 列子查询:返回的结果是以列(一列多行)
select * from students where studentno in (select studentno from course where age =18)
- 行子查询:返回的结果是以行(一行多列)
select * from students where (studentno,sex) = (select studentno,sex from course where name ='王昭君')
-表级子查询:返回的结果是多行多列(子查询结果充当数据源,必须起别名)
select * from scores
inner join (select * from coures where name in ('数据库','系统测试')) as c on scores.couresno = c.couresno
子查询中特定关键字的使用
in(范围)
格式:主查询 where 条件 in (列子查询)
any|some
格式:主查询 where 条件 列=any (列子查询)
除了=号,还有>,<,<=,>=可用
all
格式:主查询 where 条件 列=all(列子查询):等于里面所有;
格式:主查询 where 条件 列<>all(列子查询):不等于里面所有;