mysql02

1. 数据完整性

1) 数据的准确性和可靠性。

2) 完整性约束

A. 实体完整性

实体:记录

实体完整性约束保证数据记录之间是准确的(能够唯一标识一个实体)。

主键约束:  唯一的，不能为空。 primary key

# 1.添加主键约束

create table student(

 sid int primary key,

 sname varchar(20)

);

# 2.添加主键约束

create table student(

  sid int,

sname varchar(20),

primary key(sid)

);

# 3.表创建之后添加主键约束

create table student(

    sid int,

sname varchar(20)

);

#主键约束

alter table student add CONSTRAINT PK_SID primary key(sid);

1. 字段选择?

唯一的，不要选择业务字段。

1. 主键添加

一张表中只能有一个主键，但是可以有联合主键(多个字段整体作为主键)。

3.每张表必须设置主键

  唯一约束: 不能重复 可以添加多个(unique)

   

alter table userinfo add CONSTRAINT UQ_CARD unique(card);

  主键自增: 从1开始，每次自身加1（oracle不能使用,序列）

create table student(

 sid int primary key auto_increment,

 sname varchar(20)

)；

 删除约束:

 alter table student drop primary key

B. 域完整性

域: 字段

类型约束

非空约束:  不能为空  not null

默认值:  default 值

#非空和默认值

create table student(

 sid int primary key auto_increment,

 sname varchar(20) not null,

 gender bit(1) default 1

);

C. 引用完整性

一张表中通用列的取值必须参考另外一张表主键字段。

外键约束:foreign key

alter table student add CONSTRAINT FK_CID foreign key(cid)

REFERENCES classroom(cid);

1. 主外键关联
2. 外键关联字段名称可以不一样,但是类型必须一致。

D. 自定义完整性

  check约束: 在mysql中不能使用

Alter table student add constriaint check(age between 1 and 150)

2. 运算符  

#算术运算符  

 select 1+1;

 select 1-1;

 select 1*2;

 select 3/2;   # 1.5

 select 3 div 2; #整除

 select 3/0;   # null

# 比较运算符  = != >= <=  true 1 false 0  select 1!=1;

# is null / is not null / between ...and .../ in / not in

# 逻辑运算符  and or !

  select 1=1 and 1=2;

select 1=1 or 1=2;

select !1=1;

# 位运算符  & | ^

  select 3 & 2;   # 2

select 3 | 2;   # 3

select 3 ^ 2;   # 1

3. DML

1) 添加数据(insert)

Insert into tname[(字段名称…)] values(值…);

#添加数据classroom

#给所有字段添加值,和表中字段顺序一致

insert into classroom values(3,'bd1809');

#给cname字段添加值,和指定字段的顺序必须一致

insert into classroom(des,cname) values('des','bd1810');

#添加三条记录(批量插入)

insert into classroom(cname,des) values('bd1811','des'),

('bd1812','des'),

('bd1901','des');

#将classroom的值整体复制到classroom1

insert into classroom1 select * from classroom;

3) 删除数据 delete

delete from tname [where 条件]

truncate:清空   文件级别清空  自增重置

delete:删除  只修改数据  逐行删除  自增不会重置

4) 修改数据  update

Update tname set 字段=新值,… [where 条件]

update classroom set des = 'des' where cid = 2;

5) 查询数据  select

Select 字段|表达式 from 表|视图|结果集

Where 条件

Group by 分组

Having 分组之后进行检索

Order by 排序

Limit 限制结果

# 查询所有信息

 select * from emp;

 #查询所有员工的姓名和工资

 select ename,sal from emp;

 #查询工资>2000的员工信息

 select * from emp where sal > 2000;

 #查询工资在1000到2000之间的员工信息(范围查询)

 select * from emp where sal >= 1000 and sal <=2000;

 select * from emp where sal between 1000 and 2000;

 #查询编号(empno)为7521,7369,7788的员工信息。(集合查询)

select * from emp where empno = 7521 or empno = 7369 or empno = 7788;

select * from emp where empno in (7521,7369,7788);

 #别名(简化   做解释说明)  [as] 别名

 #字段  表达式  结果集 表 都可以起别名

 select ename,sal*1.05 nsal from emp;

 # 查询所有职位信息  去重

 select distinct job from emp;

 # 查询在10号部门工资最高的员工信息

  #1.10号部门所有员工

select * from emp where deptno =10;

#2.排序,取第一个

select * from emp where deptno =10 order by sal desc limit 0,1;

# 排序 order by 排序字段   默认升序排序 asc | desc

  #根据员工工资降序排序,工资一致的按照员工编号的降序排序。  select * from emp order by sal desc,empno desc;

# 限制结果查询  limit 下标,长度  (仅适用于mysql,分页查询)

  select * from emp limit 0,5;

# 查询所有有奖金的员工信息  select * from emp where comm is not null;

# 模糊查询  like  %:0到多个任意字符 _:1个字符

  # 查询名字以s打头的员工信息

 select * from emp where ename like 's%';

 #包含   

 select * from emp where ename like '%s%';

 # 第二个字符为L的所有员工信息

 select * from emp where ename like '_l%';

4. 函数

1) 单行函数

#函数

 #数学函数

 select PI()* 2 *2;  #pi

 select CEIL(-12.3); #向上取整

 select FLOOR(12.3); #向下取整

 select ROUND(8.45,-1); #四舍五入

 select MOD(5,2);  #取模

 select RAND();  #随机数 [0,1)

 select POW(2,3); #幂运算

#随机从emp中获取两条记录。

  select * from emp order by RAND() limit 2;

 #字符函数

   select LENGTH('this is a dog');   #获取长度

 select length(ename) from emp;

 select LOWER('THIS');

 select UPPER('this');

 select SUBSTR('this is zs',1,6); #下标从1开始

 #select REPLACE(str,from_str,to_str);

 select trim(' this is '); #去两端空格

 select LPAD('aa',10,'*');  #左填充

 select RPAD('aa',10,'*');  #右填充

#日期函数

  select NOW();  #当前时间

select SYSDATE();  #获取系统时间

select CURRENT_DATE();

  select CURDATE();

select CURRENT_TIME();

  select CURTIME();

  select YEAR('1998-09-09');

select YEAR(NOW());

select MONTH(date);

select DAY(date);

#获取当前月最后一天

select LAST_DAY('2018-02-02');

#日期计算

select DATE_ADD(NOW(),interval 2 MONTH);

6) 聚合函数

7) 分组函数

# 聚合函数

  # min()  max()  avg()  count() sum()

select max(sal) from emp;

select min(sal) from emp;

select avg(sal) from emp;  

select count(*) from emp;  #记录数

select count(1) from emp;  #记录数

select count(comm) from emp; #字段非空总数

select sum(sal) from emp;

 #分组 group by 分组条件   having:分组之后进行检索

  select deptno,avg(sal) from emp group by deptno;

 # 查询平均工资大于2000的部门的编号和平均工资。

   # 1.where在group by之后

 # 2.where中不能使用聚合函数

   select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;

8) 加密函数

#加密函数

 select MD5('root');

 select SHA('root');

 select password('root');

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

　　　　　　　　　　　　　　　　　　　　　　　　练习

# 1.添加主键约束
create table student(
sid int primary key,
sname varchar(20)
);
# 2.添加主键约束
create table student(
sid int,
sname varchar(20),
primary key(sid,sname)
);
# 3.表创建之后添加主键约束
create table student(
sid int,
sname varchar(20)
);
#主键约束
alter table student add CONSTRAINT PK_SID primary key(sid);

#添加唯一约束
create table userinfo(
uid int primary key,
uname varchar(20),
card varchar(18)
);

alter table userinfo add CONSTRAINT UQ_CARD unique(card);

#主键自增
create table student(
sid int primary key auto_increment,
sname varchar(20)
)

#非空和默认值
create table student(
sid int primary key auto_increment,
sname varchar(20) not null,
gender bit(1) default 1
);

#外键约束添加
alter table student add CONSTRAINT FK_CID foreign key(cid)
REFERENCES classroom(cid);

#添加数据classroom

#给所有字段添加值,和表中字段顺序一致
insert into classroom values(3,'bd1809');

#给cname字段添加值,和指定字段的顺序必须一致
insert into classroom(des,cname) values('des','bd1810');

#添加三条记录(批量插入)
insert into classroom(cname,des) values('bd1811','des'),
('bd1812','des'),
('bd1901','des');

#将classroom的值整体复制到classroom1
insert into classroom1 select * from classroom;

#查看
select * from classroom;

create table classroom1 select * from classroom where 0;

#删除
# delete删除逐行删除
delete from classroom where cid = 7;
insert into classroom(cname,des) values('bd1805','des');
delete from classroom;
truncate table classroom;

#修改
update classroom set des = 'des' where cid = 2;

#算术运算符
select 1+1;
select 1-1;
select 1*2;
select 3/2; # 1.5
select 3 div 2; #整除
select 3/0; # null
# 比较运算符 = != >= <= true 1 false 0 select 1!=1;
# is null / is not null / between ...and .../ in / not in
# 逻辑运算符 and or !
select 1=1 and 1=2;
select 1=1 or 1=2;
select !1=1;
# 位运算符 & | ^
select 3 & 2; # 2
select 3 | 2; # 3
select 3 ^ 2; # 1

# 查询所有信息
select * from emp;
#查询所有员工的姓名和工资
select ename,sal from emp;
#查询工资>2000的员工信息
select * from emp where sal > 2000;
#查询工资在1000到2000之间的员工信息(范围查询)
select * from emp where sal >= 1000 and sal <=2000;
select * from emp where sal between 1000 and 2000;
#查询编号(empno)为7521,7369,7788的员工信息。(集合查询)
select * from emp where empno = 7521 or empno = 7369 or empno = 7788;
select * from emp where empno in (7521,7369,7788);
#别名(简化 做解释说明) [as] 别名
#字段 表达式 结果集 表 都可以起别名
select ename,sal*1.05 nsal from emp;
# 查询所有职位信息 去重
select distinct job from emp;
# 查询在10号部门工资最高的员工信息
#1.10号部门所有员工
select * from emp where deptno =10;
#2.排序,取第一个
select * from emp where deptno =10 order by sal desc limit 0,1;
# 排序 order by 排序字段 默认升序排序 asc | desc
#根据员工工资降序排序,工资一致的按照员工编号的降序排序。 select * from emp order by sal desc,empno desc;
# 限制结果查询 limit 下标,长度 (仅适用于mysql,分页查询)
select * from emp limit 0,5;
# 查询所有有奖金的员工信息 select * from emp where comm is not null;
# 模糊查询 like %:0到多个任意字符 _:1个字符
# 查询名字以s打头的员工信息
select * from emp where ename like 's%';
#包含
select * from emp where ename like '%s%';
# 第二个字符为L的所有员工信息
select * from emp where ename like '_l%';

#字段作为条件尽量不要计算
#1.查询工资提升100元后超过2000元的所有员工信息。
select * from emp where sal >2000;
#2.查询工资在1000-2000之间的10号部门的最高工资的员工信息。
select * from emp where deptno = 10 and sal between 1000 and 2000 order by sal desc
limit 0,1;

#3.将所有工资低于2000的员工工资提升5%。
update emp1 set sal = sal * 1.05 where sal < 2000;

#4.查询名字包含s的在20号部门的员工信息。
select * from emp where deptno = 20 and ename like '%s%';
#5.查询工资大于1000的10号部门的前5条记录。
select * from emp where deptno =10 and sal > 1000 limit 0,5;
#6.将奖金<500的员工奖金提升100元。
update emp1 set comm = IFNULL(comm,0) + 100 where comm is null;

#函数
#数学函数
select PI()* 2 *2; #pi
select CEIL(-12.3); #向上取整
select FLOOR(12.3); #向下取整
select ROUND(8.45,-1); #四舍五入
select MOD(5,2); #取模
select RAND(); #随机数 [0,1)
select POW(2,3); #幂运算
#随机从emp中获取两条记录。
select * from emp order by RAND() limit 2;
#字符函数
select LENGTH('this is a dog'); #获取长度
select length(ename) from emp;
select LOWER('THIS');
select UPPER('this');
select SUBSTR('this is zs',1,6); #下标从1开始
#select REPLACE(str,from_str,to_str);
select trim(' this is '); #去两端空格
select LPAD('aa',10,'*'); #左填充
select RPAD('aa',10,'*'); #右填充
#日期函数
select NOW(); #当前时间
select SYSDATE(); #获取系统时间
select CURRENT_DATE();
select CURDATE();
select CURRENT_TIME();
select CURTIME();
select YEAR('1998-09-09');
select YEAR(NOW());
select MONTH(date);
select DAY(date);
#获取当前月最后一天
select LAST_DAY('2018-02-02');
#日期计算
select DATE_ADD(NOW(),interval 2 MONTH);

# 聚合函数
# min() max() avg() count() sum()
select max(sal) from emp;
select min(sal) from emp;
select avg(sal) from emp;
select count(*) from emp; #记录数
select count(1) from emp; #记录数
select count(comm) from emp; #字段非空总数
select sum(sal) from emp;


#分组 group by 分组条件 having:分组之后进行检索
select deptno,avg(sal) from emp group by deptno;

# 查询平均工资大于2000的部门的编号和平均工资。
# 1.where在group by之后
# 2.where中不能使用聚合函数
select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;

#java: Base64.encode() Base64.decode() 可逆加密
# MD5() 不可逆

#加密函数
select MD5('root');
select SHA('root');
select password('root');