day42_mysql 数据库操作 数据库的约束

今日内容

查询：

查询表中的所有的行和列的数据：

　　select * from 表名：

　　select * from strdent:

查询指定列的数据：如果有多个列，中间用逗号隔开：

　　select 列名1，列名2，列名3，.... from 表名

　　select age , name from student;

查询时指定列的别名：

　　使用关键字 as

　　使用别名的好处：显示的时候指定新的名字，并不修改表的原有结构。

　　对指定列进行别名操作： select 列名1 as 新名字， 列名2 as 新名字， ....from 表名；

　　对表和列同时进行别名操作：select 列名1 as 新名字， 列名2 as 新名字， ....from 表名 as 新表名；

如：对学生表进行操作：查询学生表的姓名和年龄：

selsect
    s.age as  年龄,
    s.name as 姓名
from
    student as s;
--  as关键字可以省略不写。

去除重复查询：

　　使用关键字 distinct

　　语法格式： select sistinct 字段名 from 表名；

　　例如：

-- 查询本班同学都来自什么地方
select distinct address from strdent;

查询的结果值可以参与数学运算

　　查询出来的列和其他列可以进行数学运算

　　语法：select 列名1+，-，*，/ 列名2，。。。from 表名;

　　注意：能够参与数学运算的前提是列的数据类型是数值型，整数和小数

例如：

--  查询本班同学的数学成绩和历史成绩的和：
select s.math +s.histroy as  综合 from  student

条件查询：

　　查询的时候，有时候你想要获取符合条件的结果值，并不是获取表中的所有记录。

　　语法：select 字段列表 from 表名 where 条件表达式；

　　结果值： 符合条件的就会被返回，如果条件不符合就不返回(过滤器)

　　运算符：

>,<,<=,>=,!=,=,<>（不等于）,没有'=='	在sql中，不等于有两种表示方式，！= ，<>
between。。。and	表示的是一个区间范围 如between A and B(A,B)
in（具体的取值）	里面放的是一个个数组，如果有多个，中间用逗号隔开。
like	模糊查询，根据关键字查询
is null	查询某一列为null，注意在sql 不能这样表达’表达式 +字段名=null ‘
is not null	插叙一列不为null的值

　　备注：在进行模糊查询时，通配符 有两种表示方式：% _

　　　　% 匹配任意多个字符

　　　　_ 匹配单个字符

　　两者都需要注意书写的位置：

　　　　一般我们是用 % 作为模糊查询的通配符

　　逻辑运算符 与，或，非

　　　　与：&& --》and

　　　　或：|| --》 or

　　　　非：！

排序

　　单列排序

　　通过 order by语句来实现排序，只是将查询出来的结果值进行排序，并不影响查询的结果，不进行条件过滤，影响的是显示的方式(从大到小还是从小到大)

　　升序：asc默认就是升序

　　降序：desc

　　单列排序：根据表中的某个字段，某列进行排序

　　语法：select 字段列表 from 表名 where 条件 order by 字段名 asc/desc；

　　如：

-- 对查询出来的同学历史成绩进行降序排序
select * from student as s order by s.history desc;

　　组合排序：

　　　　同时对表中的多个字段进行排序，如果前面的字段值相同，再根据后面的字段再次排序

　　　　语法： select * 字段列表 from 表名 where 条件 order by 字段1 desc/asc， 字段名2 desc/asc；

　　　　操作：

-- 根据数学成绩进行升序排序，当数学成绩相同在根据历史成绩降序排序
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组，文综和是多少,年龄大于18岁
/*
    1.想要什么样的结果值
    2.SQL中需要有哪些条件
    3.在sql
*/
SELECT    
    s.gender,SUM(s.history+s.policital+s.geographic) 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

-- 统计成年人男女各多少个？只显示个数小于3的那一组成员
SELECT    
      s.gender,count(s.gender) as 个数
from 
    student as s
where 
     s.age >= 18
group by 
    s.gender
having 
    count(s.gender) < 3 ;

　　having 和 where 的区别

　　where 语句：将查询结果分组前的符合条件返回数据，不符合条件的过滤掉，即先过滤在分组，where后面不能使用聚合函数。

　　having语句：在分组之后过滤数据，即先分组再过滤，having后面可以使用聚合函数

分页查询

　　使用关键字limit (他是mysql中的方言操作)

　　作用：对查询的结果值进行分页展示，每次显示多少条记录

　　语法：select 字段列表 from 表名 where 条件 group by 分组字段 having 分组之后条件 order by 升降序 limit 分页限定条件

　　分页限定条件 --- 》 起始值 展示的记录数limit offset， length

-- 分页  转换分页查询的起始值和页码数，没页展示的个数确定的    
-- 页码数已知的 ，起始值是未知的   beginNum 记录数为num
-- totalNum 总记录数
-- beginNum =(pageNum - 1) * 记录数  
LIMIT
    (pageNum - 1) * num;
-- 如果你查询的记录值是从第一条开始的，这个0可以省略
SELECT
    *
from 
    student as s
LIMIT
    5;
-- 如果你查询的是最后几条，有几条显示几条，不会报分页错误
-- 查询从第三条开始，查询5条
SELECT
    *
from 
    student as s
LIMIT
    1,5;

备注：如果查询从第一条开始，起始值可以省略，如果查询最后几条，有几条显示几条，不会分页报错

数据库备份与还原　　

　　图形化界面工具 Navicat

　　1.备份

　　　　选中数据库右键---》转储sql文件----》结构和数据 ---》指定磁盘中sql文件存储的位置

　　2.还原

　　　　先通过Navicat创建一个数据库(需要和sql文件中的数据库保持同名) ---》右键运行sql文件 ---》 浏览磁盘中存储的sql文件，点击运行，最后---》 刷新。

　　dos指令操作

　　1.备份：

　　　　在不登录的情况下使用mysqldump -u登录名 -p登录密码 备份数据库名 > 存储磁盘的位置(绝对路径)

　　2.还原

　　　　登录mysql，首先创建同名的数据库，

　　　　然后使用当前创建的数据库 use 创数据库名

　　　　然后source 磁盘中sql文件存储的位置

数据库的约束

　　概述

　　　　对表中的数据进行限定，保证数据的有效型，完整性和正确性。一个表一旦添加了约束，那么不正确的数据将无法添加进来，所以一般约束在创建表设定字段的时候添加上。

　　主键约束

　　　　主键：一般是用来唯一标识数据库表中的某一条记录，不能为空

　　　　通常情况下，一般会给表添加一个id字段，用作唯一标识，设置为主键，主键一般是提供给数据库操作使用的(查询，修改，删除等等)，主键不能重复，也不能为空。

　　　　　　比如：一个人的身份证号，一个人的手机号，一个学生的学号，员工工号

　　　　语法：使用关键字 primary key

-- 查询
select * from user ;
-- 非法数据插入  唯一  不重复
INSERT into user values(7,'0008','123123','消化','12312313212','男',10);
--  非法数据插入  不为空
INSERT into user values(,'0008','123123','消化','12312313212','男',10);
-- 删除主键  sql语句
alter table user DROP PRIMARY key;
-- 添加主键
alter table user add PRIMARY KEY(id);

主键自增

如果希望在添加表记录是，不设定表中主键值，系统会自动给主键添加自增值

　　语法： auto_increment 表示自动增长

--  非法数据插入  null值插入
INSERT into user values(null,'0010','123123','小周','12312313212','男',25);

　　修改自增的起始值为100

　　　　alter table auto_increment = 起始值;

唯一约束

　　概念：字段值唯一，不允许重复

　　关键字：unique

　　语法： 字段名 字段数据类型 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,null);

　　备注：如果插入是null，表名是没有数据，不存在数据重复问题，可以重复插入null。

非空约束

　　概念：记录中的某个字段不能为null

　　语法： 字段名 字段类型 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);

外键约束

-- 创建一张员工表
CREATE table employee(
    id int PRIMARY KEY auto_increment,
    username VARCHAR(10) not null,
    gerder VARCHAR(1) DEFAULT '男',
    age int
)
insert into employee(id,username,age) values(null,'小孙',20);
-- 查询员工表
SELECT * from employee;

备注：某字段既添加了非空约束又添加了唯一约束，这个字段也不是主键

　　一般情况下，一张表中只能有一个主键约束