一:常见的数据库
Oracle:甲骨文公司的产品。与java平台兼容性最好!!!收费的。中大型企业(中大型应用)
mysql:开源产品。免费的。与java平台兼容性最好!!中小企业(中小应用) 本文主要研究mysql数据库;再分析之前必须先明确先有数据库,再有表,然后才有数据;我们是通过sql语句对mysql数据库进行数据管理 。
二:管理数据库
CREATE TABLE student(
id INT,
NAME VARCHAR(20),
chinese INT,
math INT,
english INT
);
3.3 删除表
1)delete from删除全部,也可以按条件删除,但是truncatetable只能全表删除,不能按条件删除
2)delete from删除的数据可以回滚,truncatetable删除的数据不能回滚。
3)delete from不可以把自增长约束(auto_increment)重置,truncate table可以把自增长约束(auto_increment)重置
4)truncate不可以按指定条件删除
五 查询数据
5.1查询所有列
select * from student;
5.2查询指定列(多个列之间用逗号隔开)
select name,chinese from student;
5.3查询时指定别名
select name as '姓名' from student; --as可以省略
5.4合并列查询
select name ,(chinese+math+english) from student; --一般都是数值的合并
5.5去除重复数据
select distinct chinese from student;
5.6条件查询
(1)逻辑条件 and or
需求:查询id值为3,且性别为男的学生
select * from student where id=3 and sex='男';
需求:查询id为3,或性别为男的学生
select * from student where id =3 or sex='女';
(2)比较条件 > < >= <= = between and
需求: 查询chinese分数大于80分的学生
select * from student where chinese>80;
需求:查询math分数小于或等于80分的学生
select * from student where math<=80;
select * from student where math<80 or math=80;
(3)判空条件 ='' isnull <>'' is not null
需求: 查询name字段为空的学生(包含null,也包含字符串)
select * from student where name is null or name='';
(4)模糊条件 like
like后面跟上符号:%表示任意多个字符;_表示一个字符;
需求: 查询姓李的学生
select * from student where name like '李%';
需求:查询名字第二个字为诗的学生
select * from studnet where name like '_诗%';
5.7聚合查询
(1)需求:查询所有学生chinese的总分 (SUM: 求和函数)
select sum(chinese) from student;
(2)需求:查询所有学生math的平均分(AVG; 平均函数)
select avg(math) from student;
select max(servlet) from student;
(4)一共几个学生(COUNT: 统计数量函数)
select count(*) from student;
5.8 分组查询:
需求: 查询男女的数量各自多少
看第3页(第11到15条)的学生数据sql:select * from student limit 10,5;
看第4页(第16到20条)的学生数据sql: select * fromstudent limit 15,5;
char(20): 固定长度的字符串。不管实际存储的数据的大小,一定占用20个字符空间
varchar(20): 可变长度的字符串。占用的空间大小就是实际存储的数据大小。
(2) int vs int(4)
int:默认最多11位,长度根据实际存储的数值的长度
int(4): 固定的数值长度七 数据约束
CREATE TABLE test(
NAME VARCHAR(20),
gender VARCHAR(2) DEFAULT '男'
)
INSERT INTO test(NAME) VALUES('张三');
INSERT INTO test(NAME,gender) VALUES('张三',NULL);
7.2 非空约束:不能不插入值;不能插入null;
CREATE TABLE test(
NAME VARCHAR(20) NOT NULL,
gender VARCHAR(2)
)
7.3 唯一约束(UNIQUE):不能插入重复值;可以插入多个null;
7.4主键约束(primary key):唯一+非空
注意:
-- 1)通常情况下,我们会给每张表都会设置一个主键字段,用来标记记录的唯一性
-- 2)但是不建议把业务含义字段(姓名,年龄等)作为主键,因为随着业务的变化,业务字段可能会出现重复。
-- 3)建议给每张张独立添加一个叫id的字段,把这个id字段设置成主键,用来作为记录的唯一性。
7.5 自增长约束(auto_acrement):初始值为0,每次自增长1;7.6 外键约束:一般当一个数据表中出现冗余,有可能需要重新设计一张表,这时会增加外键约束;
当有了外键约束之后,操作数据的顺序如下:
插入数据:先插入主表的数据,再插入副表数据
修改数据:先修改主表数据,再修改副表数据
删除数据:先删除副表数据,再删除主表数据
需求:deptId字段值来自于dept表的id字段的值,这时可以给deptId字段添加外键约束。CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
deptId INT,
CONSTRAINT employee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id)
-- 外键名称 外键字段 参考
)
8 数据库设计:
第一范式:要求表的每个字段必须独立的不可分割的单元(每个字段的值唯一确定)
第二范式:在第一范式的基础上,要求表的除主键以外的字段都和主键有依赖关系的(每个字段都和主键有直接或间接的关系)
第三范式: 在第二范式基础上,要求表的除主键外的字段都只能和主键有直接决定的依赖关系(每个表只和主键有关系,只由主键决定)
9 多表查询步骤:(1)确定查询哪些表(2)确定查询那些字段(3)确定连接条件
9.1 交叉连接查询(容易产生笛卡尔积)
9.2 内连接查询
SELECT e.name,d.name
FROM employee e,dept d
WHERE e.deptId=d.id;
9.3 左外连接查询:左表的数据全部显示,又表的数据当满足条件时就显示,不满足就显示为null;
SELECT d.name,e.name
FROM dept d
LEFT OUTER JOIN employee e
ON d.id=e.deptId;
9.4 右外连接查询:右表的数据全部显示,左表的数据当满足连接条件的时候,就显示满足条件的数据,但是如果不满足连接条件,则显示null
SELECT d.name,e.name
FROM employee e
RIGHT OUTER JOIN dept d
ON d.id=e.deptId;
9.5 自连接查询
SELECT e.name AS '员工',b.name AS '上司'
FROM employee e
LEFT OUTER JOIN employee b
ON e.bossId=b.id;
十 mysql存储过程
创建存储过程
-- 定义结束符号
DELIMITER 结束符号
CREATE PROCEDURE 存储过程名称 (形式参数列表)
BEGIN
多个sql语句
END 结束符号
-- 调用存储过程
CALL 存储过程名称(实际参数列表);
参数类型:
IN: 输入参数,可以携带数据到存储过程中
OUT: 输出参数,可以携带数据到存储过程外面。
INOUT: 输入输出参数。如何接受存储过程的输出参数呢?定义变量去接受输出参数
mysql数据库三种变量:
--1)全局变量。mysql内置的变量,mysql程序关闭的时候全局变量才会失效!!
-- show variables:产看全局变量
--character_set_client: mysql接收的客户端的数据编码
--character_set_results: mysql使用什么编码输出给客户端数据
-- 查看某个全局变量: select @@变量名
-- 修改某个全局变量: set @@变量名=值
--2) 会话变量。变量只在某次登录的会话中有效!退出连接,会话变量数据失效!!
-- 查看某个会话变量: select @变量名
-- 修改/定义某个会话变量: set @变量名=值
-- 案例:演示查询和更改会话变量
--3) 局部变量:在存储过程中定义的变量。存储过程结束局部变量失效!!
-- 查看某个局部变量: select 变量名
-- 修改某个局部变量: set 变量名=值
-- 定义某个局部变量: declare 变量名 数据类型;
十一:触发器:当往员工表插入/修改/删除一条数据的时候, 同时往日志表记录下来,这时就要使用触发器完成。11.1语法介绍:
需求: 当往员工表插入一条数据时,往员工日志插入一条记录。
创建增加触发器
CREATE TRIGGER tri_empAdd AFTER INSERT ONemployee FOR EACH ROW
INSERTINTO emp_log(content) VALUES('员工被插入了一条记录');
创建修改触发器
CREATE TRIGGER tri_empUpd AFTER UPDATE ONemployee FOR EACH ROW
INSERTINTO emp_log(content) VALUES('员工被修改了一条记录');
创建删除触发器
CREATE TRIGGER tri_empDel AFTER DELETE ONemployee FOR EACH ROW
INSERTINTO emp_log(content) VALUES('员工被删除了一条记录');
11.2 数据库权限问题root用户:超级管理员。 权限: 增删改查(数据库,表,数据)
-- 加密函数(md5算法--单向加密)
SELECT PASSWORD('root'); --*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B
-- 修改用户密码
UPDATE USER SET PASSWORD=PASSWORD('123456')WHERE USER='root';
-- 分配权限给不同的用户
-- 需求: 分配查询day15数据库的employee表的权限给eric用户,密码为‘123456’
GRANT SELECT ON day15.employee TO'eric'@'localhost' IDENTIFIED BY '123456';