约束类型
主键约束 PRIMARY
外键约束 FORGIVEN…REFERENCES…
唯一约束 UNIQUE
检查约束 CHECK
默认值约束 DEFAULT
非空约束 NOT NULL
事务四大特性
• 原子性(Atomicity)
事务是一个不可再分割的工作单位。整个事务中的所有操作,要么全部完成,要么全部
不完成,不可能停滞在中间某个环节。事务在执行过程中发生错误,会被回滚
(Rollback)到事务开始前的状态。
• 一致性(Consistency)
在事务开始之前和事务结束以后,数据库的完整性约束没有被破坏。即数据库事务不能
破坏关系数据的完整性以及业务逻辑上的一致性。
• 隔离性(Isolation)
隔离状态执行事务,一个事务不能影响另一个事务,使它们好像是系统在给定时间内执
行的唯一操作。
• 持久性(Durability)
在事务完成以后,该事务所对数据库所作的更改便持久的保存在数据库之中,并不会被
回滚
三大范式
第一范式(1NF):每一列保持原子特征。
列是基本数据项,原子的,否则设计成一对多的关系。不满足第一范式不能称之为关系型数据
库。通俗讲就是:一个字段只存储一项信息。
示例如下:
学生表中只有一个字段
学生表(学生信息),里面存的数据为 张三是高一2班的男学生。这种表是不符合1NF的。
拆分改造后:
学生表(学生名称,性别,学生年级,学生班级)
对应的数据为 学生名称:张三,性别:男,年级:高一,班级:2
第二范式(2NF):属性完全依赖于主键(针对联合主键进行消除部分依赖)
1NF基础上,非主属性完全依赖于主键。如果不是依赖主键,应该拆分成新的主体,拆分成一对多的关系。通俗讲就是:表要有主键,并且其他字段都依赖于主键,不能给无关的表使用
示例如下:
学生表(学生ID、姓名、性别、年龄、省、市、区)
其中省、市、区 并不是完全依赖学生ID。
拆分改造后:
学生表(学生ID、姓名、性别、年龄、地址ID)——主键:学生ID
地址表(地址ID、省、市、区)——主键:地址ID
键(学生ID,地址ID)
第三范式(3NF):
2NF基础上,属性不依赖于其它非主属性(消除依赖传递)。通俗讲就是:一个数据库表中不
包含已在其它表中已包含的非主关键字信息,即表与表之间的非主键信息不能重复。
示例如下:
学生表(学生ID、学生姓名、学生性别、学院名称、学院电话)——主键:学生ID
学院表(学院ID、学院名称、学院电话)——主键:学院ID
拆分改造后:
学生表(学生ID、学生姓名、学生性别、学院ID)——主键:学生ID
学院表(学院ID、学院名称、学院电话)——主键:学院ID
备注:通过范式学习,应用范式越高表越多。表多带来的问题:(1)查询时需要连接多个表,
增加了查询的复杂性。(2)查询时需要连接多个表,降低了数据库查询的性能。
————————————————————————————————————————————
2. 日期/时间类型
包括 YEAR、TIME、DATE、DATETIME 和 TIMESTAMP
3. 字符串类型
包括 CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM 和 SET 等
4. 二进制类型
包括 BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB
其中TEXT、带有VAR字段的都是可变长度类型
**当查询列中既有空值也有非空值时,最好用查询条件
where 列名 is not null and 列名=’查询对象’
例:where A=is not null and A=’a’;
创建数据库
create database wyu407;
切换使用当前数据库,相当于双击数据库名
use wyu407
创建一个数据表
create table t407 (
id int(11),
class_name varchar(20)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
查询全部
select * from t407;
按字段查询
insert into t407(id,class_name)
values(1, ‘407 class’);
插入新增记录 示例
INSERT INTO table_name ( field1, field2,…fieldN )
VALUES ( value1, value2,…valueN )
更新数据表
update t407
set class_name = ‘408 class’
where id = 1 ;
删除表数据
delete from t407
where id = 1 ;
删除表
drop table t407;
删除该数据库
drop database wyu407;
查看数据表
SHOW TABLES;
查看数据表字段信息
DESCRIBE 数据表名;
DESCRIBE flea_market;
数据表重命名
ALTER TABLE flea_market RENAME TO fm;
CREATE TABLE flea_market( #重命名后再创建原表
item VARCHAR(100) NOT NULL,
price INT UNSIGNED ,
expire_date DATE DEFAULT’2099-12-31’
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
删除数据表
DROP TABLE 数据表名;
删重命名的表
DROP TABLE fm;
表中新增新的字段
ALTER TABLE 数据表名 ADD 新增字段 字段类型;
示例:
ALTER TABLE flea_market ADD item_type VARCHAR(10) NOT NULL DEFAULT ‘NULL’;
查看表情况
直接使用DESCRIBE<表名>;或者SELECT * FROM <表名>;都行SELECT * FROM flea_market;
DESCRIBE flea_market;
修改已存在的表的字段信息
#修改字段数据类型
ALTER TABLE flea_market MODIFY item_type CHAR(100);
DESCRIBE flea_market; #查看信息
#修改字段名称及数据类型
ALTER TABLE flea_market CHANGE item_type item_status CHAR(10);
DESCRIBE flea_market; #查看信息
示例
————————插入数据
create table emp(
id int not null unique auto_increment,
name varchar(20) not null,
sex enum(‘male’,‘female’) not null default ‘male’, #性别
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int, #部门
depart_id int
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values
(‘jason’,‘male’,18,‘20170301’,’ teacher’,7300.33,401,1); #以下是教学部
insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values
(‘egon’,‘male’,78,‘20150302’,‘teacher’,1000000.31,401,1);
insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values
(‘kevin’,‘male’,81,‘20130305’,‘teacher’,8300,401,1);
insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values
(‘tank’,‘male’,73,‘20140701’,‘teacher’,3500,401,1);
insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values
(‘owen’,‘male’,28,‘20121101’,‘teacher’,2100,401,1);
insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values
(‘jerry’,‘female’,18,‘20110211’,‘teacher’,9000,401,1);
insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values
(‘nick’,‘male’,18,‘19000301’,‘teacher’,30000,401,1);
insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values
(‘sean’,‘male’,48,‘20101111’,‘teacher’,10000,401,1);
insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values
(‘aa’,‘female’,48,‘20150311’,‘sale’,3000,402,2);#以下是销售部门
insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values
(‘bb’,‘female’,38,‘20101101’,‘sale’,2000,402,2);
insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values
(‘cc’,‘female’,18,‘20110312’,‘sale’,1000,402,2);
insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values
(‘dd’,‘female’,18,‘20160513’,‘sale’,3000,402,2);
insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values
(‘abc’,‘female’,28,‘20170127’,‘sale’,4000,402,2);
insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values
(‘bcc’,‘male’,28,‘20160311’,‘operation’,10000,403,3); #以下是运营部门
insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values
(‘add’,‘male’,18,‘19970312’,‘operation’,20000,403,3);
insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values
(‘aff’,‘female’,18,‘20130311’,‘operation’,19000,403,3);
insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values
(‘rrr’,‘male’,18,‘20150411’,‘operation’,18000,403,3);
insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values
(‘ttt’,‘female’,18,‘20140512’,‘operation’,17000,403,3);
————————插入数据
区间查找 between …and 或 >、<
select * from emp where salary between 3000 and 4000;
或
select id,name,salary from emp where salary >= 3000 and salary <= 4000;
like查找
#% 匹配任意个数任意字符 匹配姓名中带o字符的所有
select name,salary from emp where name like ‘%o%’;
not in查找
#查询薪资不在20000,18000,10000范围的数据
select * from emp where salary not in (20000,18000,10000);
NULL 查找
#查询岗位描述为空的员工名与岗位名
注意: 针对null不能用等号,只能用is
select name,post from emp where post_comment = ‘NULL’; # 查询为空!因为把它当成了一个字符,只是用NULL混淆
select name,post from emp where post_comment is NULL;#这样才能有结果
select name,post from emp where post_comment is not NULL;
group by 分组查找
#按部门分组
注:分组的select只能选一个字段,显示该字段的分类,可以加求算最高最低平均或和,
select post from emp group by post; # 分组后取出的是每个组的第一条数据
以组为单位统计组内数据>>>聚合查询(聚集到一起合成为一个结果)
select post,max(salary) from emp group by post;# 每个部门的最高工资
select post,min(salary) from emp group by post;# 每个部门的最低工资
select post,avg(salary) from emp group by post;# 每个部门的平均工资
select post,sum(salary) from emp group by post;# 每个部门的工资总和
select post,count(id) from emp group by post;# 每个部门的人数
limit用来限制查询条数查找
如果有一张表的数据量特别大,一次查询全部需要很久,那么久用limit 限制查询的条数
select * from emp limit 3;# 限制展示条数
select * from emp order by salary desc limit 1;# 查询工资最高的人的详细信息
select * from emp limit 0,5; # 分页显示
第一个参数表示起始位置,第二个参数表示的是条数,不是索引位置
select * from emp limit 5,6;
##这个表示从第5个开始,往后取6个
order by排序查找
select * from emp order by salary asc; #(默认升序排)
select * from emp order by salary desc; #降序排
having查找
语法:select 查询字段1,查询字段2,… from 表名
where 过滤条件
group by分组依据
having 过滤条件
#语法这么写,但是执行顺序却不一样
from
where
group by
having
select
多表查询
查询员工及所在部门的信息
select * from user,dep where user.dep_id = dep.id;
#查询部门为技术部的员工及部门信息
select * from user,dep where user.dep_id = dep.id and dep.name = ‘技术’;
#将两张表关联到一起的操作,有专门对应的方法
#内连接:只取两张表有对应关系的记录
select * from user inner join dep on user.dep_id = dep.id;
select * from user inner join dep on user.dep_id = dep.id
where dep.name = “技术”
#将两张表关联到一起的操作
#左连接: 在内连接的基础上保留左表没有对应关系的记录
select * from user left join dep on user.dep_id = dep.id;
#右连接: 在内连接的基础上保留右表没有对应关系的记录
select * from user right join dep on user.dep_id = dep.id;
#全连接:在内连接的基础上保留左、右面表没有对应关系的的记录
select * from user left join dep on user.dep_id = dep.id
union
select * from user right join dep on user.dep_id = dep.id;
每行按插入提示信息
SELECT
‘雇员姓名:’ || ename
|| ‘,职位:’
|| job info
FROM
Emp
CREATE TABLE category (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(32) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
//插入数据到category表
INSERT INTO category
VALUES (‘1’, ‘家电’);
INSERT INTO category
VALUES (‘2’, ‘cc’);
select * from category
create table product(
id int NOT NULL AUTO_INCREMENT,
name varchar(30) DEFAULT NULL,
price float D
CREATE TABLE student (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(32) DEFAULT NULL,
age int(11),
PRIMARY KEY (id)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
————————————————————————————————————————————
SET SQL_SAFE_UPDATES =0
create database hibernate_db
use hibernate_db;
CREATE TABLE employee
(
id
int(11) NOT NULL AUTO_INCREMENT,
first_name
varchar(20) DEFAULT NULL,
last_name
varchar(20) DEFAULT NULL,
salary
int(11) DEFAULT NULL,
PRIMARY KEY (id
)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8;
CREATE TABLE certificate
(
id
int(11) NOT NULL AUTO_INCREMENT,
certificate_name
varchar(30) DEFAULT NULL,
employee_id
int(11) DEFAULT NULL,
PRIMARY KEY (id
),
KEY FK_1t5pca769lsff735cnwkxbdq4
(employee_id
),
CONSTRAINT FK_1t5pca769lsff735cnwkxbdq4
FOREIGN KEY (employee_id
) REFERENCES employee
(id
)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
SET SQL_SAFE
SET SQL_SAFE_UPDATES =0;
drop table employee;
select * from employee;
select * from certificate;