- 回忆上周知识
- mysql约束与设计
DDL:操作数据库和表 drop alter create
DML:增删改数据库表的记录 insert update delete
DQL:查询表中记录 select
DCL:管理用户,授权 grant
查询:
- 查询表中的所有行和列的数据
select * from 表名:
select * from student;
- 查询指定列的数据:如果有多个列中间用,号隔开
select 列名1,列名2,列名3,… from 表名;
selcet age,name from student;
- 查询时,指定列的别名
使用关键字 as
使用别名的好处: 显示的时候可以指定新的名字,并不修改表的原有结构
对指定列进行别名操作: select 列名1 as 新名字, 列名2 as 新名字 ,……from表名
对表和列同时进行别名操作:select 列名1 as 新名字, 列名2 as 新名字 ,……from 表名 as 新表名;
如:对学生表:查询学生表的姓名和年龄;
|
select s.name as 姓名 from student as s; -- as关键字可以省略不写. |
- 去除重复查询
使用关键字 distinct 去掉重复的记录
语法格式 select distinct 字段名 from 表名;
例如:
|
-- 查询本班都来自什么地方 |
查询的结果值可以参与数学运算
查询出来的的列和其他列可以进行数学运算
语法 select 列名1+,-,*,/,%, * 列名2,…… from 表名;
能够参与数学运算的前提是列的数据类型使数值型. 整数和小数
例如:
|
-- 查询本班同学的数学成绩和历史成绩的和 -- as可以省略 |
- 条件查询
查询的时候,有时候只想获取符合条件的结果值,并不是获取表中的所有记录
语法: select 字段列表 from 表名 where 条件表达式;
结果值:符合条件的记录就会被返回,如果条件不符合就不返回(过滤掉)
运算符:
|
< > >= <= != = <>(不等于) 没有== |
在sql中,不等于有两种表示方式,!= <> |
|
between… and |
表示的是一个区间范围 如:between A and B [A,B] |
|
in(具体的取值) |
里面放的是一个个数值,如果有多个,中间用,号隔开 |
|
like |
模糊查询,根据关键字查询,一般搭配% |
|
is null |
查询某一列为null的值,注意在sql 不能这样 字段名 = null |
|
is not null |
查询某一列不为null的值 |
操作:
|
-- 比较运算符的操作 -- 查询数学成绩50以上的同学 select * from student as s where s.math <> 100;0 -- between and -- 查询数学成绩在70-100分之间的 select * from student as s where s.math between 70 and 100;
-- in(某几个散列值) -- 查询历史成绩为80 和 100分的同学 select * from student as s where s.history in(80,100);
-- like 模糊查询 搭配使用 % -- 查询学生地址中含有阳的地址 select * from student as s where s.address like "%阳%"; -- 查询本班同学中含有小的同学信息 select * from student as s where s.username like "%刘%" select * from student as s where s.username like "%刘%"
-- 查询生日为空的同学 有哪些 select * from student as s where s.birthday is not null; |
备注:在模糊查询时,通配符 有两种标识方式" % _
%匹配任意多个字符
_匹配单个字符
两者都需要注意书写的位置
一般我们使用 % 号作为模糊查询的通配符
逻辑运算符 与,或,非
与:&& --> and
或:|| --> or
非:!
在sql中建议使用单词来表示 逻辑运算 and or
操作:
|
-- 逻辑运算符 与,或,非 -- 查询历史成绩为80 和 100分的同学 or select * from student as s where s.history = 80 or s.history = 100;
-- 查询本班同学成绩在70分以上的 select * from student as s where s.math>70 and s.history > 70;
-- 取非 ! select * from student as s where ! (s.math>70 and s.history > 70); |
- 排序
- 单列排序
通过 order by 语句来实现排序,只是将查询出来的结果值进行排序,并不影响查询的结果,不进行条件过滤,影响的是显示的方式(从大到小,从小到大)
升序: asc 默认就是升序
降序: desc
单列排序:根据表中的某个字段 某列进行排序
语法: select 字段列表 from 表名 where 条件 order by 字段名 asc/desc
|
-- 对查询出来的同学历史成绩进行降序排序 |
- 组合排序
同时对表中的多个字段进行排序,如果前面的字段值相同,再根据后面的字段再次排序
语法:select 字段列表 from 表名 where 条件 order by 字段1 desc/asc, 字段名2 asc/desc;
操作:
|
-- 根据数学成绩进行升序排序.当数学成绩相同再根据历史成绩降序排序 select * from student as s order by s.math asc,s.history desc; |
- 聚合函数
常用的聚合函数
求一下最大值 max
求一下最小值 min
求一下平均数 avg
求一下总和 sum
统计查询结果的记录数 count
备注:使用count统计记录数的时候,如果count中填写的是表中的某个字段,如果某条记录中该字段为null则该纪录会被过滤掉
ifnull(表达式1,表达式2),如果前面的字段为null,后面的表达式2可以替换掉前面的字段为null的值.可以统计带null的值
- 分组查询 group by
|
select from 表名列表 where 分组之前的条件 group by 分组字段 having 分组之后的条件 limit 分页限定条件 |
概念:
使用group by 对查询的结果信息进行分组,相同的数据分成一组
语法:
select 字段列表 from 表名 group by 分组字段 having 分组之后的条件
例如:
将查询出来的结果值内容,再按照性别进行分组,分组2组
|
-- 将查询出来的结果值内容,再按照性别进行分组,分组2组.文综和是多少 -- 年龄大于18岁的 /* 1.我想要什么样的结果值 2.sql中需要有哪些条件 3.在sql都需要设计到几张表 */ select s.gender,sum(s.history+s.geo+s.political) as 文综和 -- 查询出来需要展示的结果值 from student as s where s.age>18 group by s.gender
-- 统计成年人男女各多少个 select s.gender,count(s.gender) as 个数 from student as s where s.age>=18 group by s.gender |
having和where的区别
where语句:对查询结果分组前的符合条件返回,不符合条件的过滤掉,即先过滤再分组.where后面不能使用聚合函数.
having语句:在分组之后过滤数据.即,先分组再过滤.having后面可以使用聚合函数
- 分页查询
使用关键字 limit,他是mysql的方言操作
作用:对查询的结果值进行分页展示,每次显示多少条记录
语法:select字段列表 from 表名 where 条件 froup by 分组字段 having 分组之后的条件 order by 升降序 limit 分页限定条件
分页限定条件 ---> 起始值 展示的记录数
limit offset,length
操作:
|
-- 查询学生表数据 ,从第四条开始,展示三条 select * from student limit 3,3 -- 分页 转换分页查询的七十至和页码数,每页展示的个数确定的 -- 0,15 第一页 -- 15,15 第二页 -- 30,15 第三页 -- 查询第28也的数据 15条记录 -- 页码数已知的 pageNum 起始值是未知的 beginNum,记录数为num -- totalNum 总记录数 -- beginNum = (pageNum - 1) * 记录数num limit (pageNum - 1) * num;
-- 如果你查询的记录值是从第一条开始的,这个0可以省略 select * from student as s limit 5; -- 如果你查询的是最后几条,有几条显示几条,不会报分页错误 -- 查询从大第三条开始,查询5条 select * from student as s limit 2,5; |
备注:
如果查询从第一条开始,起始值可以省略不写,如果查询最后几条,有几条显示几条,不会报分页错误.
- 数据库备份和还原
图形化界面工具 Navicat
1.备份
选中数据库右键 ---> 转储sql文件 ---> 结构和数据 --->指定磁盘中sql文件储存的位置.
2.还原
通过Navicat创建一个数据库(需要和sql文件中的数据库保持同名)--右键运行sql文件--->浏览磁盘中存储的sql文件,点击运行,最后--->刷新
DOS指令操作
1.备份
在不登录的情况下使用mysqldump -u登录名 -p登陆密码 备份数据库名 >存储磁盘的位置(绝对路径)
mysqldump -uroot -proot java31 > E:/java31.sql
2.还原
登录mysql首先创建同名的数据库,使用当前创建的数据库 use 创建数据库名
mysql -uroot -proot
create database java31 character set utf8;
然后 source 磁盘中sql文件存储的位置
use java31
source D:/java31.sql;
select * from student;
- 数据库表的约束
概述
对表中的数据进行限定,保证数据的有效性,完整性和正确性.一个表一但添加了约束,那么不正确的数据将无法添加进来,
所以一般约束在创建表设定字段的时候添加上
主键约束
主键:一般是用来唯一标识数据库表中的某一条记录,不能为空
通常情况下,一般会给表添加一个id字段,用作唯一标识,设置为主键,逐渐一般是提供给数据库操作使用的(查询,修改,删除等等),主键不能重复也不能为空
比如一个人的身份证号,一个人手机号,一个学生的学号,一个员工的工号
语法: 使用关键字 primary key
操作:
|
-- 查询 select * from user; -- 非法数据插入 INSERT INTO user values (7,‘008‘,‘123456‘,‘小花‘,‘12312312312‘,‘女‘,19);
-- 非法数据插入 null值插入 INSERT INTO user values (null,‘008‘,‘123456‘,‘小花‘,‘12312312312‘,‘女‘,19);
-- 删除主键 sql语句 alter table user drop PRIMARY key;
-- 添加主键 alter table user add primary key(id); |
主键自增
如果希望在添加表记录时,不设定表中主键值,系统会自动给主键添加自增值
语法:
auto_increment 表示主键自动增长
|
-- 非法数据插入 null值插入 INSERT INTO user values (null,‘008‘,‘123456‘,‘小花‘,‘12312312312‘,‘女‘,19); |
根据前一个主键 然后加1
修改自增的起始值为100
alter table user auto_increment = 起始值;
唯一约束
概念:字段值唯一,不允许重复
关键字:unique
语法:字段名 字段数据类型 unique
操作:
备注:如果插入的是null,表名时没有数据,不存在数据重复问题,可以重复插入
|
-- 创建一张表 角色表 create table role( id int primary key auto_increment, -- 主键约束 rolename varchar (10) unique -- 角色唯一约束
)
-- 插入数据 insert into role values(null,‘CEO‘); insert into role values(null,‘manager‘); insert into role values(null,‘CTO‘);
-- 查询角色表 SELECT * FROM role;
insert into role values(null,‘CTO‘);
insert into role values(null,null); insert into role values(null,null); |
非空约束
概念:某一条记录中的某个字段不能为null
语法:字段名 字段数据类型 not null
|
-- 创建一张部门表 create table department( id int PRIMARY key auto_increment, -- id 主键自增 departmentname varchar(10) not null -- 部门不能为空
)
insert into department values(null,‘总裁办‘); insert into department values(null,‘财务部‘); insert into department values(null,‘人事部‘); insert into department values(null,‘研发部‘); insert into department values(null,‘销售部‘);
select * from department;
insert into department values(null,null); |
默认值:
概念:当没有给字段赋值,系统会附上一个指定的默认值
语法:字段名 字段 数据类型 default 默认值
操作:
|
-- 创建一张员工表 employee create table employee ( id int PRIMARY KEY auto_increment, username varchar(10) not null , gender varchar(1) default ‘男‘, age int )
insert into employee (id,username,age) values(null,‘小孙‘,20);
-- 查询员工表 select * from employee;
insert into employee (id,username,gender,age) values(null,‘小丽‘,‘女‘,20); |
备注:
如果给表中的某个字段.即添加了非空约束又添加了唯一约束那么该字段是不是主键呢?
一般情况下,一张表中只能有一个主键
外键约束