.
MySql 数据库编程 笔记
编程MySQL数据结构SQLSQL Server .
9月3日
数据库的历史:发展于六十年代
数据库术语:
数据(Data):数据库中存储的基本对象
数据的定义:描述事物的符号记录
数据的种类:文字,图形,图像,声音
数据的特点:数据与其语义是不可分的
数据库(Database,简称DB):长期储存计算机内,有组织的,可共享的大量数据集合
数据库管理系统(DBMS):位于用户与操作系统之间的一层数据管理软件
DBMS的用途:
科学地组织和存储数据,高效地获取和维护数据
应用程序与数据的对应关系(数据库系统):
应用程序1,应用程序2 -----àDBMS----à数据库
常使用的DBMS有哪些:
MySQL(SUN)
Oracle(Oracle)
MS-SQL(SQL-Server)
DB2(IBM)
如何学习数据库操作系统:
两个方向
1 程序员:
1.1 数据库的标准语言:SQL(结构化查询语言)
1.2 学习不同数据库的独特管理方式
2 DBA(Database administrator):
2.1 数据库的标准语言:SQL(结构化查询语言)
2.2学习不同数据库的独特管理方式
2.3 数据库的调优
2.4 精通数据库的备份,恢复机制
DBA权威认证:OCP
OCP+RHCE ————>DBA
MySQL是一个真正的多用户,多线程SQL数据库服务器,它和其它数据库管理系统一样是以一个客户机/服务器结构的实现(c/s),它由一个服务器守护程序mysqld和很多不同的客户程序和库组成
My-SQL的安装:
1 安装
2 配置:
2.1 选择服务器的类型:
选择开发服务器
2.2 选择数据库的使用方式:
2.2.1 多用途的数据库
2.2.2 事物处理数据库
2.2.3 非事物处理数据库
2.3 数据库存储的盘符,默认在: C:\
2.4 数据库的连接数配置
2.5 MySQL服务器端口,默认:3306
2.6 选择字符编码
2.6.1 手工选择:gb2312---windows下的中文支持
Linx 选UTF-8
2.7 服务器默认安装到windows系统服务中
选择:导入bin目录下
2.8填写MySQL数据库管理系统,管理员的密码
注意:在MySQL中数据库管理员的用户名为root
mysql –uroot -p
mysqld.exe //mysql –server
mysql.ext //mysql-client
SQL的分类:
DML(Data Manipulation Language,数据操作语言):用于检索或者修改数据
DDL(Data Definition Language,数据定义语言);用于定义数据的结构,比如创建,修改或者删除数据库对象
DCL(Data Control Language,数据控制语言):用于定义数据用户的权限
1 查看数据库管理系统中有哪些数据库:
mysql>show databases;
mysql > 准备好接受新命令
- > 等待多行命令的下一行
‘ > 等待下一行,等待以单引号(“ ‘ “)开始的字符串的结束
“ > 等待下一行,等待以双引号(“ “ “)开始的字符串的结束
确定指令后以;号结束
2 创建自己的数据库:
mysql > create database myfrist;
3 删除数据库:
drop database myfrist;
4 向数据库中存储数据
4.1 在数据库中创建表
create table user(name varchar(20),age int, sex char(2)):
4.1.1 进入(使用)数据库
use myfrist;
4.1.2 查看库中的表
show tables;
4.1.3 创建表的常用数据类型
CHAR(M)固定长度字符:用来表示固定长度的字符串,字符串的长度范围是1-255,例如:car_model CHAR(10)
VARCHAR(M) 可变长度字符具有灵活性的字符数据类型
INT(M) [UNSIGNED] [ZEROFILL]:存整型
表的创建:
create table “tablename”(
“column1” “data type”,
“column2” “data type”,
“column3” “data type”);
4.2 向表中存储数据:
insert into user values(‘天蓝’, 18, ‘女’);
4.3 查看数据:
select * from user;
重点
:数据库管理系统(DBMS) -à 数据库(Database)-à表(table) --à数据(data)
数据的存储过程:
1 建库
2 建表
3 插入数据
9月4日
DATE
一个日期,支持的范围是’1000-01-01’到’9999-12-31’.MySQL以’YYYY-MM-DD’格式来显示DATE值,但是允许你使用字符串或数字把值赋给DATE列.
DATETIME
一个日期和时间组合,支持的范围是’1000-01-01 00:00:00’到’9999-12-32 23:59:59’. MySQL以’YYYY-MM-DD HH:MM:SS’格式来显示DATETIME值
TEXT/BLOB 文本和大对象
如果字符串的长度超过了55,或者要将一篇文章保存到数据库中,CHAR和VARCHAR就无法使用了,这里就要用到TEXT和BLOB类型,该类型可以保存的字符串长度在255—65535字节内,BLOB是一个能保存 二进制数据的大对象.两个数据类型是一样的,TEXT不区分大小写,BLOB区分大小写
SQL语句的导入:
1 编写SQL脚本(.sql)
2 导入(不进入sql客房端)
mysql –uroot –p < user.sql
练习:
user_inser.sql
导入5条数据到user2表中
2 删除表中的数据
delete from tablename where 条件
实例:
delete from user2 where id=4;
删除表中所有数据:
delete from tablename;
3 修改表中数据:
update tablename set colname = value where 条件
示例:
update user2 set name = ‘天蓝’ where id=4;
结果:将id 为4 的人,他的名字修改为张四
现在掌握的内容:
1 建库: create database
2 建表: create table
3 插入数据: insert into
4 删除数据: delete from
5 修改数据:update set
6 删除库:drop database databasename;
7 删除表:drop table tablename;
8 查看库:show databases;
9 查看库中的表:show tables;
10 查看表中的数据:select * from tablename;
练习:
需求:在线图书网
实现:对图书的管理
1 建库:onlinebooks
2建表:books;
2.1 书号:id
2.2 书名:title
2.3 作者:author
2.4 出版社:pub
2.5库存::store
3 插入5条测试数据
‘java数据库编程','王力','清华出版社',250
'MySQL数据库','人力','清华出版社',120
'读者','青年','清华出版社',50
'青年文摘','青年','电子出版社',70
'C语言','一名','电子出版社',60
4 将id 为5的数据书名修改为’西游记’;
5 将id 为3 的记录删除:
delete from books where id=3;
6 根据条件进行过滤查找:
出版社为“清华出版社”的所有书籍
select * from books where pub=’ 清华出版社’;
7 查找出库存大于的所有书籍
select * from books where store >50;
8 查找出“西游记“的库存
select title,store from books where title=’ 西游记’;
查看表结构:
desc tablename;
9月5日
MySQL常用函数:
version();
current_date();
now();
user():
数据库的设计:
数据库设计的步骤:
1 收集数据
2与最终用户进行确定
3 数据库的设计
4 控制多余数据
5 绘制E-R模型图(ERD)
6 将ERD转换为数据库中的表
E-R模型概述
设计数据库时,通常采用”实体关系模型”—E-R模型
什么是冗余数据:在数据库存储当中一定量重复数据,它在数据库的实际使用当中会占用数据库的宝贵资源。
什么是E-R模型?
实体:通常用于表示能够被清晰识别的人,地点,事物,对象,事件。例:一个注册号为“1234”的用户,虽然上述实例是真实存在的,但实体也可能是抽象的,比如,一个想法或感觉。
实体————表
属性:它象口语中的形容词,每个实体都有用于对其进行定义的一组特性。
属性————表中的列
关系:它可以定义为实体之间的关联。
一个库——有N张表构成——表与表之间存在关系——关系型数据库(RDBMS)
E-R图:
矩形——实体
椭圆——实体属性
菱形——关系
实体之间的三种类型:
1:1
1:N或N:1
1对多或多对1
M:N
多对多
如何控制冗余数据?
一般来说通过数据库的范式理论,简称设计数据库的范式-----控制数据库冗余
共有五个范式, 一般达到第三范式即可
练习:
创建一个库可以存储学生信息,在学生信息中可以知道某一个学生的班主任,并且在学生信息中记录学生的分数。
需求:
1 当查找学生信息的同时,可以列出班主任信息
create database student_teacher;
student表:id,name,age,score,e-mail,phone,teacher_id
teacher表:id,name,age,sex,e-mail,phone
分别插入5 条测试数据
2 查找学生姓名为xxx 的人
select * from student where name=’xxx’;
3查找学生分数大于80的人
select * from student where score>80;
4查找老师年龄小于30岁的人
select * from teacher where age<30;
5查找老师的姓名,年龄,e-mail其中年龄不为xx岁的老师
select name as ‘姓名’, age as ‘年龄’, e_mail as ‘邮箱’ from teacher where age<>30;
6查找学生分数80分或者90分
select * from student where score=80 or score=90;
7查找学生分数为88分并且性别为男的学生
select * from student where score=88 and sex=’男’;
8查找出学生分数在90—100之间的所有学生
select * from student where score between 90 and 100;
9查找出姓李的老师-----模糊查询
select * from teacher where name like ‘李%’;
10 查找出老师使用的邮箱为lang的
select * from teacher where e_mail like ‘%lang%’;
11查找出老师的号码为159开头的
select * from teacher where phone like’159%’;
数据库的查询:
select[all | distinct] column1[,column2]
from table1[,table2]
[where “conditions”]
[croup by “column-list:]
[having “conditions]
[order by “column-list” [asc | desc]]
sql运算符:
= 等于
>= 大于等于
<= 小于等于
< > != 不等于
or(满足一个条件)
and(都需要满足)
in(‘20’,’18’) (返回两者中的记录or)
between‘20’ and ‘22’; (两者之间记录)
like (与统配符一起使用%,相似的记录)
9月6日
order by //实现排序功能
默认:升序排序(从小到大) ---asc
desc----降序排序(从大到小)
12 查询老师年龄从高到低
select * from teacher order by age desc;
13 查询出每个学生的班主任老师,只列出学生的姓名,年龄,老师的姓名,电话
select student.name as ‘学生姓名’,student.age,teacher.name as’老师姓名’,teacher.phone from student,teacher where student.teacher_id =teacher.id;
14 查询出学生姓名,性别,e_mail
老师姓名,年龄,e_mail
select student.name,student.sex,student.e_mail,teacher.name,teacher.age,teacher.e_mail from student,teacher where student.teacher_id=teacher.id;
GROUP By //对于查询出的数据结果进行分类(分组)
15 将学生的年龄进行分组显示
select age from student group by age;
HAVING //子查询---对于where查询出的结果,再次进行查找
16 查找出学生中年龄是19岁的人,并且19岁人中找出一个叫汪雪的人
select * from student where age=19 having name=’汪雪’;
DISTINCT //过滤查询出的重复型记录,只显示唯一的记录
17 查询出学生年龄--------只显示唯一的
select distinct(age) from student;
limit //限制查询结果的输出数量
//同时也可以实现数据的分页
18 列出老师中年龄最大的3个人
select * from teacher order by age desc limit 3;
19 实现查询记录的分页显示---(将5条记录分为3页)
第一页:
select * from teacher limit 0,2;
第二页:
select * from teacher limit 2,2;
第三页:
select * from teacher limit 4,2;
掌握:
1 建库
2建表
3插入数据
4修改数据
5删除数据
6各种查询
数据库需要完整性
LAMP-----MySQL 之constraint(约束)
约束:
约束定义了表级的强制规则
约束定义数据的完整性
在Mysql ,有下列的强制类型:
NOT NULL 非空约束
create table test1 (id int not null);
desc test1;
UNIQUE 唯一约束
create table test2(id int,unique(id));
DEFAULT 默认约束
create table test3(id int not null default’111111’);
insert into test3 values();
PRIMARY KEY 主键约束
主键是一个字段或一组字段(组合键),用于唯一标识表中的记录,它可以确保每个记录是唯一的
create table test4(id int, primary key(id));
insert into test4 values(1);
ID主键自增值
create table test5(id int not null auto_increment,name varchar(20),primary key(id));
创建一个有合理约束的表----test6
id ----为主键,并且自增值
name---not null
e_mail----
phone---not null
创建表:
create table test6(id int not null auto_increment,name varchar(20)not null,e_mail varchar(20),phone varchar(15)not null,primary key(id));
插入5条测试数据:
insert into test6(name,e_mail,phone) values(‘aaa’,’aa@sina.com’,’123456’);
auto_increment特点:
1 自增值
2当删除某一个值时,它不会自动填充,而是继续自增
delete from test6 where id=4;
insert into test6(name,e_mail,phone) values(‘fff’,’fff@sina.com’,’15886’);
作业:
1 将student_teacher库修改为合理约束的库
Test.sql
2 将19个练习反复实验
FOREING KEY 外键约束
9月8日
什么是(FOREINGN KEY)外键约束?
一张表引入了另一张表的主键,这种情况称之为外键。
如何创建外键?
FOREIGN KEY:定义子表中的列为外键
REFERENCES:标记外键所要参考的父表和列
ON DELETE CASCADE:(可选值)允许在删除父表列的同时,删除子表的列
create table customer(id int unsigned not null auto_increment,name char(60) not null,primary key(id));
insert into customer values(null,'国美');
insert into customer values(null,'苏宁');
create table producer(id int unsigned not null auto_increment,name char(60) not null,c_id int unsigned not null, primary key(id) , foreign key(c_id) references customer(id) on delete cascade);
insert into producer values(null,'三星','1');
insert into producer values(null,'Nokia','2');
insert into producer values(null,'Moto','3'); //错误
delete from customer where id = 2; //父表,子表关联删除
2、聚合函数
MAX( )
select name,max(age) from student;
MIN( )
select name,min(age) from student;
COUNT( ) //统计查询出的记录总数
select COUNT(name) from student;
AVG( ) //平均值
select AVG(score) from student;
SUM( ) //求和
select SUM(score) from student;
DML:
SELECT 用于检索数据
INSERT 用于增加数据到数据库
UPDATE用于从数据库中修改现存的数据
DELETE用于从数据库中删除数据
DDL:
create table
alter table //修改表
drop table
create table
drop index
create view
drop view
DCL:
grant
revoke
set
3、表的修改---了解
属性修改:
Alter table tablename modify colname datatype;
示例:
alter table teacher modify sex char(5);
add //添加列
Alter table tablename add colname datatype;
示例:
alter table teacher add address varchar(70);
drop //删除列
Alter table tablename drop colname;
示例:
alter table teacher drop address;
列的重命名
Alter table tablename change oldCol newCol dataType;
示例:
alter table teacher change sex gender varchar(4);
表的重命名
rename table oldTableName to newTableName;
示例:
rename table test to test2;
4、表的连接---多表查询
创建测试表
major:
id
name
teacher:和以前一样
student:
添加
teacher_id 、major_id
要求:使用外键约束
内连接(inner join)
内连接:又为等值连接,因为它将两个表中的公共字段有相等的值(记录)连接起来,这是最常用的连接。
查询出每个学生的班主任老师
select student.name, student.age, teacher.name from student inner join teacher on student.teacher_id = teacher.teacher_id;
使用内连接查找出学生的主修课程
select student.name, major.name from student inner join major on student.major_id=major.id;
左连接:显示SQL语句中LEFT JOIN 左边表中的所有记录,即使在LEFT JOIN右边的表中没有满足连接条件的数据也被显示。当满足连接条件时,LEFT JOIN右边的表中的相应的记录与LEFT JOIN 左边表中的相应的记录结合显示。
查找出任课老师所带的学生
select teacher. name, student.name from teacher left join student on teacher.id=student.teacher_id;
9月9日
使用右连接查出学生所学课程和学生的姓名,年龄
select major.name, student.name, student.age from student right join major on student.major_id = major.id;
left join 以左边的表查询为主
right join 以右边的表查询为主
示例:
teacher left join student
那么:teacher为左表,student为右表
需要列出,学生的:姓名,性别,年龄,老师,所学课程
select student.name, student.sex, student.age, teacher.name, major.name from student inner join teacher on student.teacher_id=teacher.id inner join major on student.major_id = major.id;
测试数据
现我们对一个商品批发商进行分析
myview.sql
create database myview;
1 客户表----customer
属性:id,name,address,phone
2员工表
属性:id,name,sex,phone
3货品表----goods
属性:id,name
4出货表---sell---业务表
属性:id,employee_id,goods_id,customer_id,selldate;
列出客户名,员工名,货品名
select employee.name as ‘Employee’, goods.name as ‘ Goods’, customer.name as ‘Customer_add’ from sell inner join employee on sell.employee_id = employee.id inner join goods on sell.goods_id = goods.id inner join customer on sell.customer_id = customer.id;
它可以访问一个或多个表中的数据,是从一个或多个表中派生出的数据对象(虚表)
视图特点:
1 将复杂的查询简单化
2提供安全机制,它保证用户只可以看得到的数据,系统中真实的表是不可被存取的(现都支持更改数据)
创建视图:
CREATE VIEW view_name AS SQL;
示例:
show create view view_name;
修改视图:
ALTER VIEW view_name AS SQL;
目标:列出业务人员在什么时间和哪些用户有过什么交易
列出:员工名,员工电话,销售时间,客户名,客户电话,客户地址,货品名
create VIEW all_view as select employee.name as “Employee”,employee.phone as emp_phone, selldate as Date, customer.name as “Customr”, customer.address as customer_add, goods.name from sell, employee,customer,goods where sell.employee_id = employee.id and sell.customer_id = customer.id and sell.goods_id = goods.id;
数据库的数据字典------实际就是一个数据库,它记录着数据库管理系统中其它数据库的操作
mysql中的数据字典:information_schema
use information_schema;
show tables;
MySQL之事物处理(表类型)
什么是事务(Transaction)
所谓事务,它是一个操作序列,这些操作要么都执行,要么都不执行,它是一个不可分割的工作单位。事务是数据库维护数据一致性的单位,在每个事务结束时,都能保持数据一致性。
数据一致性问题:
多用户并发存取同一数据将会导致数据不一致性问题
在Mysql中有多种表的类型,但是分为两类:
1 事务类型:innoDB, BDB
2 非事务类型:HEAP, ISAM, MyISAM, MERGE
MyISAM数据表是ISAM数据表的继承者,也有三个文件,分别:
.frm 结构定义文件
.MYD 数据文件
.MYI 索引文件
在创建表的时候,如何定义表的类型:
create database tabletype;
windows:默认是innodb-----支持事物
create table tablename(id int) engine = myisam;
示例:
create table test(id int) engine=myisam;
innoDB:由于采用表空间的概念来管理数据表,所以它只有一个与数据表对应.frm文件,同一目录下的其它文件表示为表空间,存储数据表的数据和索引
HEAP数据表是一个存在于内存中的表,所以它的数据和索引都存在于内存中,文件系统中只有一个.frm 文件,以定义结构
当Mysql关闭后数据都将消失
create table test_heap(id int) engine = heap;
insert into test_heap values(1);
1 多表连接----重要-----反复练习
2视图------掌握
3 事物
什么是事物
MySQL表的类型:
9月10日
SHOW TABLE STATUS FROM your databasesName; //查看表的类型
示例:
show table status from mytest;
C:\Program Files\MySQL\MySQL Server 6.0 \my,ini //MySQL服务器和客户端的配置文件,决定着客户端或服务器运行参数
default-stoerage-engine=innodb
如何在innodb中实现事物?
BEGIN:告知服务器我要开始一个事物
ROLLBACK:如果事物发生异常,那么数据回滚
COMMIT:事物执行成功,进行数据提交
实现事物的步骤:
1 begin;
2 sql1,sql2,sql3….
3 commit;
MyISAM表如何实现事物机制----了解
Create table mytest2(id int,name varchar(20)) engine=myisam;
对MyISAM进行事物处理-----锁
LOCK TABLE TABLENAME (READ, WRETE)
LOCK TABLE 可以对表进行加锁控制,以保证用户并发访问时非事物表的数据一致性
MyISAM实现锁的步骤:
1 Lock tables tablename write;
2 sql1,sql2,sql3….
3 unlock tables; //解锁
LAMP ---MySQL之Index(索引)
索引的用途:快速找出匹配一个WHERE子句的行,当执行连接时,从其他表检索行,对特定的索引列找出MAX()或MIN()值
索引实际是一个分离的列表,具有一个指向全表的指针
Myisam表,索引被存储为分离的文件
InnoDB,索引存储为表空间的一部分
Mysql,有四种类型的索引:主键,唯一索引,全文索引,普通索引。Primary key, unique, full index, index
创建索引:
create index indexname on tablename(colname);
或
alter table tablename add index tablename(colname);
删除索引:
alter table tablename drop index indexname;
Explain //分析MySQL的查询效率
使用方式:
mysql> explain select * from tablename where 条件;
explain分析Mysql中索引:
1 type:显示连接中使用了何种类型:const(最好)
2 key:实际使用的索引
3 key_len:使用的索引长度,越短越好
4 ref:显示索引哪一列被使用了
5 row:mysql必须检查用来返回请求数据的行数
练习:
创建一张具有主键的表,然后进行查询,分析
create table mytest3(id int null, name varchar(20), primary key(id));
为mytest3表中的name字段创建一个索引
create index test3_index on mytest3 (name);
查找索引:
show keys from tablename;
示例:
show keys from mytest3;
索引的实现算法通常有两种:
1 BTREE
2 BITMAP
使用索引注意事项:
1 合理创建索引,反而会降低数据库的查询效率
2 合理的使用索引,反之创建的索引会失去意义
2.1 where
2.2 表连接
为聊天室设置查询索引
create table chat(id int, content varchar(255)) engine=myisam;
insert into chat values(1,’hello,what is your name?’);
create index myindex on chat(content);
select * from chat;
explain select * from chat where content like ’ wh%’;
全文索引:
create fulltext index content_index on chat(content);
或
alter table chat add fulltext(content);
删除索引:
alter table chat drop index content_index;
数据掌握:
1 建库*
2 建表*
3 数据的增,删,改*
4 数据的查询*
5聚合函数
6 多表连接*
7 约束*
8 视图*
9 索引*
10数据库的备份与恢复
11 事物处理
Mysql备份方式:
Backup
Restore
Copy //冷备份
退出mysql
Mysqldump
Select into
Backup, Restore只能限制MyISAM
创建一个文件夹
use test;
backup table chat to ‘/db_backup’; c:\\db_backup
只生成.frm和.MYD,可以在数据库恢复后重建索引
drop table chat;
restore table chat from ‘/db_backup’;
Copy //冷备份
1 停止MySQL服务器----避免在备份的时候有用户进行数据库的访问
2 复制数据库的文件夹 c:\mysql\data
用mysqldump备份
mysqldump是mysql专门用于mysql数据库备份的工具,它的特点是导出的数据为原有的sql语句
mysqldump备份:
mysqldump –uroot databasesname > xxx.sql
mysql恢复
编辑xxx.sql ----à use databasesname;
mysql –uroot < xxx.sql
9月11日
MySQL其他客户端工具
mysqlshow-----显示数据库,表和列信息:
1 mysqlshow -uroot -p databasename(库信息)
2 mysqlshow -uroot -p databasename tablename(表信息)
3 mysqlshow -uroot -p databasename tablename colname(列信息)
大型数据库,书写数据的过程?
日志先写
用于查看二进制日志文件的实用工具
mysqlbinlog xxx-bin.xxxx
mysqlvheck 客户端可以检查和修复MyISAM表,它还可以优化和分析表
1 mysqlcheck -uroot -p dbname //检测
2 mysqlcheck -uroot -p -auto-repair
dbname //如碰到有问题的表自动进行修复
3 mysqlcheck -uroot -p -optimize //优化表
用select into备份:
只能备份表的结构,不能备份
备份
select * from tablename into outfile
‘/db_backup/table.dat’
mysql> delete from tablename;
恢复
mysql> load data infile
‘/db_backup/tablename,dat’
into table tablename;
MySQL之mysql用户管理
用户密码—mysqladmin
mysqladmin –h localhost –u root –p password “password”
#设置在本地以root身分登录的密码
mysqladmin –h remote –u root password “password”
#设置远程主机以root身分登录的密码
mysqladmin –h localhost –u root –p password “”( 设置密码为空)
密码用””
系统中的权限表
user权限表,记录充许连接到服务器的用户帐号信息,里面的权限是全局级的
db权限表,记录各个帐号在各个数据库上的操作权限
tables_priv权限表,记录数据表级的操作权限
columns_priv权限表,记录数据列表级的操作权限
host权限表,配合db权限表对给定主机上数据库级操作权限作更细致的控制,这个权限表有受GRANT和REVOKE语句的影响
直接修改user权限表
mysql> use mysql;
mysql> update user set password=password(‘password’) where user=’root’;
mysql> flush privileges;
重载权限表,使修改马上生效
添加用户
use mysql;
insert into user(host,user,password) values (‘host’,’user’,password(‘password’));
GRANT
mysql> grant all on *.* to ‘test’@’localhost’ identified by ‘test’;
上例运行后的效果是,test用户能通过’test’密码从本机访问所有数据库
测试:
开始——>运行——>cmd
mysql –uroot -p
练习:
为MySQL添加一个system用户,密码为:manager
REVOKE all ON *.* FROM ‘test’@’localhost’; 删除帐号从本机的所有权限
示例:
删除student的select student库权限
REVOKE select ON student.* from ‘student’@’localhost’;
撤销用户的权限:
注意:REVOKE只可以取消用户权限,所以user表记录需要手工删除
delete from user where user=’’;
flush privileges;
配置MySQL的服务地址:
同学A:MySQL服务器
1 修改my.ini
bind-address = 192.1683.71
2 grant all on *.* to ‘test’@’%’ identified by ‘test’;
3 重启服务
同学B:MySQL客户端
mysql –h 192.168.3.xxx -utest -p;
细节权限的分配与撤销:
分配:CRANT SELECT, DELETE, UPDATE ON student.* TO ‘cls’@’localhost’ identified by ‘test’;
上例运行后的效果是,cls用户只能通过’test’密码从本机访问student,test数据库,并且没有insert权限,所以test用户试图在student 库中任何一个表插入数据的时候是没有权限的
撤销:
REVOKE select, delete, update ON student.* from ‘cls’@’localhost’;
上例子运行后的效果是:将cl s用户在student 数据库中的select, delete, update 权限撤销
1 掌握SQL:
视图,索引,事物
2 掌握这个数据库自己的管理方式:
数据库的备份
数据库用户的管理
3 掌握这种数据库编程语言
PL/SQL //Oracle编程语言
MySQL==Oracle
JDBC ---java数据库连接
JDBC 优势跨数据库平台
工作机制:
java应用程序-----JDBC----数据库驱动---连接不同数据库
在实际应用中,java跨数据库时,只需要替换数据驱动即可
java.sql //包实际JDBC功能
JDBC可以完成的工作:
1 建立与数据库的连接
2 执行SQL声明
3 处理SQL执行结果
ODBC ----开放数据库连接
9月12日
JDBC使用的步骤:
1 得到驱动
Mysql: www.mysql.org
Oracle: java/
Ms-SQL: www.microsoft.com
mysql-connector-java-5.1.6.tar.gz/
mysql-connector-java-xxxx.jar //驱动
MySQL驱动字符串:
String strDriver = “com.mysql.jdbc.Driver”;
Oracle驱动字符串:
Strin strDriver = “oracle.jdbc.driver.OracleDriver”;
MS-SQL 驱动字符串
老版本(2.0)
strDriver = “com.Microsoft.jdbc.sqlserver.SQLServerDriver”;
新版本(3.0__4.0)
String strDriver = “com.mircrosoft.sqlserver.jdbc. SQLServerDriver”;
2 配置驱动
2.1 系统配置(了解):
我的电脑à 属性---à 高级 ----à 环境变量---à
classpath:
. ; c:\mysql-commector-java-xxx.jar;
.;C:\ProgramFiles\Java\jdk1.6.0_06\lib\tools.jar;C:\ProgramFiles\Java\jre1.6.0_06\lib\rt.jar;C:\mysql-connector-java-5.1.6\mysql-connector-java-5.1.6-bin.jar;
测试是否配置成功:
c:\java\MySQLDriverTest.java
2.2 eclipse配置
粘贴MySQLDriverTest.sql的代码
在项目右点最后一个(属性)——》横着的四个选择Libraries -----à点右边的Add External JARS 添加压缩包的mysql-connector-java-5.1.6-bin.jar
3 在Java中使用(加载)驱动
4 连接数据库
1通过URL的方式
jdbc:mysql://[host][:port]/[database]
示例:连接本机的test数据库
jdbc:mysql://localhost/test
2 使用java.sql.DriverManager进行URL的连接
静态方法:
Connection getConnection(String url, String user, String password)
5执行SQL
5.1 编写SQL语句
String strInsert = “insert into mytest vlues(1,’JDBC’)”;
5.2 使用:java.sql.Statement 传输SQL语句
1 stmt = con.createStatement( );
2 int row = stmt.executeUpdate(strInsert);
3 if(row !=0)
System.out.println(“SQL语句执行成功”);
6处理结果集
1 处理查询结果:
String strQuery = “select * from mytest”;
2 rs = stmt.executeQuery( )
7关闭连接
1 stmt.close( );
2 con.close( );
简单的JDBC应用框架
1 构造:加载驱动,连接数据库
2 方法:
public void inserTablename(agrs..)
示例:
public void insertMyTest(int id,String name){
String sql = “insert into Mytest values” + id + “,” + name;
}
public void updateTablename(args)
{}
public void deleteTablenameById(int id)
public void deleteTablenameByName(String name)
public void queryById(int id)
public void queryByName(String name)
示例:
完成对于student数据库stu表的,增,删,改,查
str:
id, 主键盘,自增
name
gender
准备工作:
create database student;
create table stu(id int not null auto_increment, name varchar(20), gender char(4),age int(3),phone varchar(30), primary key(id));
eclipse/
JDBCStudentSample.java
中秋作业:
学生信息管理系统
JDBC,Java Swing;
Java + SQL
9月17日
JDBC的优化:
1 PreparedStatement:
表示预编译的SQL语句的对象,SQL语句被预编译并且存储在PreparedStatement对象中,然后可以使用此对象高效地多次执行该语句.
由于它的高效性,一般在多次执行多次反复SQL语句时通常使用它
通常的情况下,都会使用它
如何使用:
示例:
create database prepared;
create table test(id int, name varchar(2));
新建项目:
JDBCOptimize
新建类:
PreparedTest
问题:
1 连接数据库牺牲时间
2 无法控制用户的连接数
连接池技术:
如何实现连接池?
1 手工实现
ArrayList || Vector
2 使用成熟的连接池工具
开发如何利用连接池:
1 连接池------加载驱动
2 连接池-------创建连接池
3 连接池----控制连接池的大小(连接用户)
4 程序员-----在连接池中获得连接
5 程序员----传入SQL,处理结果集
6 程序员-----关闭连接
DBCP ----DataBaseConnectionPool:
是开源项目,来自于Apache基金会
www.apache.org
使用DBCP:
1 配置方法和配置mysql驱动是一样的
导入三个jar包:
1.1 mysql驱动
1.2 dbcp_xx.jar
1.3 pool_xxx.jar
2 使用方法:
主类:
org.apache.commons.dbcp.BasicDataSource;
作用:创建连接池,初始化数据源(JDBC3.0新特性)
是:java.sql.DataSource的子类
官方固定使用方法:
新建类:MyConnectionPool //创建连接池
使用MyConnectionPool
当大量的JDBC程序需要编写的时候,通常我们需要架构JDBC程序
通常使用:Java EE DAO设计模式进行设计,配合GoF23中的工厂设计模式进行架构
优点:
JDBC程序的可维护性,易读性,便于分工管理
什么是模式:程序架构的固定方式
什么是DAO设计模式?
D-------Date
A-----Access
O-----Object
简称为数据访问对象
如何实现DAO设计模式?
1 写出与数据库中表的对应的类型
类的名字与表名一致
2 写出DAO的访问对象
一般类名的命名:表名+ DAO
示例:
库中有5张表:user, goods, sell …..
User.java
UserDAO.java
Goods.java
GoodsDAO.java
…….
上面的程序会出现多人编写的情况,如果没有标准,很难最后整合到一起
引入:工厂设计模式(Factory 设计模式)
如何使用工厂设计模式:
1 编写标准:
1.1 分析:DAO将要实现的功能
1.2编写:interface
接口的命名标准:表名+ DAO
例: UserDAO
2 实现标准
2.1 类名的命名:表名+ DAO + Impl
2.2 类的具体实现标准
3 编写创建的工厂
3.1 命名:表名+ DAO + Factory
3.2 静态方法:
public static 接口名 get接口名( )
{
return new 接口实现类();
}
使用DAO模式 + 工厂模式操作user表:
create table user(id int, name varchar(20));
对于student库的student 表进行添加,查询
采用DAO + Factory设计模式
1 建库,建表
2.1 Student表的映射对象
2.2 使用工厂设计模式,创建数据访问对象
interface StudentDAO
StudentDAOImpl implements StudentDAO
StudentDAOFactory
3 编写-----实现设计
9月26日
表现层
数据持久层
如果没有DAO模式,那么我们会在界面程序中出现大量操作数据库的代码(SQL + JDBC + Swing)的耦合现象,有了DAO模式,数据库操作和表现层进行了合理的解耦合。这样程序员不仅可以分工,而且可以很好的维护程序代码
在Factory 模式设计接口的时候,
open close //开闭
对扩展开放,对修改关闭 //开闭原则
程序开发中的日志系统:
日志工具的优点:
1 跟踪代码运行轨迹
2 便于进行统计分析
3 监视代码中变量的运行情况
日志工具简介-------Apache log4j
logging-log4j-1.3alpha-8.tar.gz
lab/log4j-all-1.3alpha-8.jar //主要的jar包
文档:
docs/api/index.html
配置log4j
log4j简介
log4j由三个重要部分组成:
1 日志信息的优先级别---priority
2 日志的输出目的地---Appender
3 日志输出的格式----Layout
常用Priority
ERROR,WARN,INFO,DEBUG------由高到低
Appender介绍:
Org.apache.log4j
1 ConsoleAppender(控制台)
2 log4j.FeleAppender(文件)
Layout
%p 输出优先级别,即DEBUG,INFO,WARN,ERROR
%d 输出日志时间点的日期或时间,默认格式为ISO8601,也可以在其后指定格式,比如:%d(yyy MMM dd HH:mm:ss,SSS)
%l(L小写)输出日志事件的发生位置,包括类目名,类的线程,以及在代码中的行数。
新建项目:LoggerTest
基本使用:
1 初始化日志系统
2 快速配置
3 选择优先级别输出日志
实际开发:
1 编写log4j配置文件
1. 1配置输出目的
1.2 配置输出日志格式
默认文件名:log4j.properties
在项目中加private static Logger log = Logger.getLogger(类名(xxxDAOImpl).class);
在catch后加log.info(“zzz”);
log.error(“xx”);
映射对象
POJO -------VO //属性set,get方法
出现异常:
System.out.println( );
程序的debug:-----利用:
1 System.out.println( );
2 assert;
3 eclipse debug功能:
新建项目Debug
DebugTest.java
如何使用eclipse进行程序的Debug:
1 在课程出现错误的代码段,设置断点(小圆点)
2 运行程序的时候使用:debug as 方式进行运行
3 使用F5键(进行程序的逐行调试)
4 可以选中变量击鼠标右键,选择watch 进行变量值详细调试
5 调试完毕(F8将会到跳到下一断点进行调试)
9月27日
MySQL之Mysql SQL编程 -----存储过程
存储过程是SQL语句和流程控制语言的一个集合(类似与我们的一个成员方法),这样客户端通过这样一个方法名,就可以调用到了,所以它可以帮助提高查询的性能,而且可以提供编程式的支持
存储过程其实是数据库中一个预编译好的对象
存储过程的特点:
1 提高性能,不必重复编译,分析
2 减轻网络阻塞
3 便于管理,调试
MySQL中的两种存储过程:
1 过程---à 无返回值
2 函数---à 可返回值
测试数据:
1 create database myStore;
2 use myStore;
3 create table test(id int);
4 insert into test values(1);
5 create procedure first() select * from test;
6 call first();
存储过程中的参数
create procedure(IN name data-type)
create procedure(OUT name data-type)
create procedure(INOUT name data-type)
IN: 为默认参数,为输入参数
OUT: 为输出参数
INOUT: 即可输入又可输出
IN示例:
默认为IN型
create procedure myIN(in id int) set @tmp = id; //为变量赋值
call myIN(100);
select @tmp; //访问变量
OUT示例:
create procedure myOUT(OUT id int) set id = 20;
call myOUT(@myVar);
select @myVar;
示例:
目标:使用自定义存储过程,向myStore的test表中,插入数据,并且返回最新的结果
设置合理的结束符
Delimiter // (设置结束符号为//)
Delimiter ; (恢复结束符号为;)
代码1
create procedure myPro1( )
begin
set @a = 5;
insert into test values(@a);
select id from test;
end;//
call myPro1( )//
查看存储过程 || 和状态
show create procedure YourProcedure;
show procedure status//
代码2:
create procedure myPro2( )
begin
declare a int;
set a = 10;
insert into test values(a);
select id from test;
end;//
流程控制---à 判断语句
if xxxx then
end if;
if xx then
else
end if;
目标:根据用户的参数插入数据到test表中,在插入的时候对于用户的值进行判断,如果 > 0才可以正常插入
示例:
create procedure myIf(myIn int)
begin
if myIn > 0 then
insert into test values(myIn);
end if;
select id from test;
end;//
call myIf(200)//
Case 语句
Case variable
when xxx then sql
when xxx then sql
else sql
end case;
目标:判断用户的输入为1,则插入test表数据为1000,为2 则插入test表数据为2000,否插入50000
示例:
create procedure myCase(myIn int)
begin
set @a = 1000;
set @b = 2000;
set @c = 5000;
case myIn
when 1 then insert into test values(@a);
when 2 then insert into test values(@b);
else insert into test values(@c);
end case;
end;//
call myCase(2)// select * from test//
循环
while … end while
repeat… end repeat
while
while 条件 do
sql
end while
示例:
delete from test//
create procedure mywhile( )
begin
declare i int;
set i = 1;
while i <=10 do
insert into test values(i);
set i = i + 1; /* i ++*/
end while;
end;//
call mywhile( )// select * from test//
PEPEAT-----do while
repeat
sql
set i = i + 1;
until i > 10
end repeat;
示例:
delete from test//
create procedure myrepeat( )
begin
declare i int;
set i = 1;
repeat
insert into test values(i);
set i = i + 1;
until i >10 //没有分号
end repeat;
end;//
call myrepeat( )// select * from test//
JDBC中调用存储过程:
java.sql.CallableStatement;
CallableTest.java
MyConnectionPool.java ---- mystore;
作业:
十一作业:独立将supermarket的DAO+ Factory完成。注(可以加入存储过程实现或视图)
9月28日
练习:
为user表创建存储过程,可以接收用户的in参数,为user表的:id ,name 添加数据,并使用JDBC调用,进行数据的添加
delimiter//
create procedure pro_user(id int,name varchar(20))
begin
insert into user values(id, name);
select * from user;
end;//
call pro_user(1,’aa’)//
testcall.java
MyConnectionPool.java
描述
在我们进行数据库操作(DML)的时候,其实还可以进行事件的监听和相应,这种机制在数据库中称之为----触发器
一个触发器其实也算是特殊的存储过程,当事件产生的时候会自动执行该存储过程
触发器的种类:
1 insert 触发器: 当插入数据时触发
2 update触发器: 当更新数据时触发
3 delete触发器: 当删除数据时触发
触发器的特点:
1 操作数据时,即可触发
2 它不可以显示的调用,执行
3 不可以接收用户的参数
注意:一张表不可以多次添加同样类型的触发器
语法:
create trigger triggername
{before | after}
{insert | update | delete}
on tablename
for each row
begin
SQL
end;//
示例:
create database mytrigger//
use mytrigger//
create table test(id int)//
create trigger insert_trigger before insert on test for each row
begin
set @myvar =‘is inset’;
end;//
select @myvar//
insert into test values(1)//
select @myvar//
练习:
为test表创建insert,update,delete触发器
create trigger update_tirgr before update on test update
for each row
begin
set @myupdate = ‘is update’;
end;//
select @myupdate//
update test set id = ‘2’ where id = 1//
select @myupdate//
目标:当用户向test表插入数据前,向另一张表中记录插入记录的用户名,时间
drop trigger insert_tirgr//
1 create table test_log(name varchar(20), dt datetime)//
2 show tables//
test
test_log
3 create trigger test_insert before insert on test for each row
begin
insert into test_log values(user(),now());
end;//
4 select * from test_log//
5 insert into test values(2)//
6 select * from test_log//
New与Old关键字:
作用:关联当前表中的列
规则:
insert:只有New是合法的
Delete:只有Old是合法的
Update:可以使用New和Old
练习:
为test添加一个delete触发器,这个触发器可以记录谁在什么时间删除了什么数据
1 create table delete_log(name varchar(20), dt datetime, logdata varchar(30))//
2 create trigger test_delete before delete on test
for each row
begin
insert into delete_log values(user(),now(),old.id);
end;//
3 delete from test where id =2//
4 select * from delete_log//
练习:
为test表做一个完整的日志记录系统,日志系统使用触发器实现,分别可以记录:
delete, update, insert 的用户,时间,数据
1 create table all_log(id int not null auto_increment, name varchar(20), logtype varchar(10), newdata varchar(20), olddata varchar(20), dt datetime, primary key(id))//
id主键:
name记录用户名
logtype记录是delete,update,insert
newdata记录insert,update
olddate记录delete,update
2 创建insert触发器
create trigger test_insert before insert on test
for each row
begin
insert into all_log values(null,user( ),'insert',new.id,null,now( ));
end;//
3 创建delete触发器
create trigger test_delete before delete on test
for each row
begin
insert into all_log values(null,user(),'delete',null,old.id,now());
end;//
4 创建update触发器
create trigger test_update before update on test
for each row
begin
insert into all_log values(null,user(),'update',new.id,old.id,now());
end;//
5 insert into test values(1)//
insert into test values(2)//
insert into test values(3)//
6 delete from test where id=2//
7. update test set id=5 where id=3//
8. select * from all_log//
复习:
1 SQL
2 约束
3 聚合函数
4 表连接
5 视图
6 索引
7进技术表类型-------事务处理
8 用户,权限管理
9 数据库的备份,恢复
10 SQL编程(PL/SQL):存储过程,触发器
//数据库掌握的内容
JDBC
1 基本使用
2 优化
3 设计
————————————————————————————————小阶段结束
Web方向: