SQL基础语法:
http://www.w3school.com.cn/sql
建立索引提高查询效率的原则,一般是给在where中经常查询的关键字段加
关键字:select、distinct(不重复出现的值)、where、order by(排序DESC降序)、insert、update、delete
union(多个表联合、作用类似合并):联合表的操作字段必须类型匹配。
limit(偏移量,取出条目数量); 放在结尾处
exits:跨表连接词,可以连接内表和外表的字段进行条件表达式
MySql:连接数据库 mysql -h ip(localhost) -user root -p 密码留白
show databases;显示所有数据库 create data base mydata; use mydata;选中指定数据库 show tables;显示表 desc mytables;
基本增删改查;
建表:
声明列 基本字段
create table
msg(
id int,
title varchar(60),
name varchar(10),
content varchar(100)
);
id int,
title varchar(60),
name varchar(10),
content varchar(100)
);
增加列:
alter table
msg
add
age
tinyint unsigned; 一定要指定类型
插入行:
insert into msg
(id,title,name,content)
values
(1,'2号','zhu','我是三号'),
(2,'2号','zhang','我是四号'),
(3,'3号','laozi','我是五号');
插入行:
insert into msg
(id,title,name,content)
values
(1,'2号','zhu','我是三号'),
(2,'2号','zhang','我是四号'),
(3,'3号','laozi','我是五号');
修改行:
update msg set id ='4' where name = 'zhang';
删除行:
delete from msg where id = '2';
update msg set id ='4' where name = 'zhang';
删除行:
delete from msg where id = '2';
修改列:
alter table
msg
change column
name newname
varchar(10);
删除列:
删除列:
alter table
msg
drop
name;
select *from msg;
select *from msg;
select name,content from msg where id>2;
删除表:
drop table msg;
查询:where、order by、having、group 列名在使用中可以当做变量使用
where(表达式like、>、<、=、in、not、and、or、<>不等于、between):select(查找行)where(查找列表达式)
group by:一般用于统计(将具备共性的字段组合一起),配合max、min、avg、count、sum使用
having:区别于where的在表中取数据,having可以对正在使用的数据进行条件查找
案例1:
create table class(
id tinyint not null default 0,name varchar(4) not null default '',course varchar(8) not null default '',score tinyint not null default 0)engine=innodb default charset=utf8;
id tinyint not null default 0,name varchar(4) not null default '',course varchar(8) not null default '',score tinyint not null default 0)engine=innodb default charset=utf8;
insert into class (id,name,course,score)values
(1,"张三","数学",90),(2,"张三","语文",20),(3,"张三","地理",100),(4,"张三","政治",40),
(5,"李四","数学",100),(6,"李四","语文",10),(7,"李四","政治",25),(8,"王五","数学",30),
(9,"王五","语文",80),(10,"赵六","数学",90),(11,"赵六","语文",80),(12,"赵六","地理",70);
(1,"张三","数学",90),(2,"张三","语文",20),(3,"张三","地理",100),(4,"张三","政治",40),
(5,"李四","数学",100),(6,"李四","语文",10),(7,"李四","政治",25),(8,"王五","数学",30),
(9,"王五","语文",80),(10,"赵六","数学",90),(11,"赵六","语文",80),(12,"赵六","地理",70);
查询两门以上挂科的学生平均成绩:
select name,sum(score <60) >=2 as gks,avg(score) from class group by name having gks >=1;
显示学生成绩最高的两个单科:
select id,name,course,score from class order by score desc limit 3;
连接表: 左连接则以左为准右补NULL,右反之 inner查询左右表都具备的数据才显示 外连接
左连接select 列1 列2 列N from
tableA left join tableB
On tableA 列 = tableB 列
where group by having ... ...
外连接:通过union实现
select boy.*,girl.* from boy left join girl on boy.flower=girl.flower
union
select boy.*,girl.* from boy right join girl on boy.flower=girl.flower;
union
select boy.*,girl.* from boy right join girl on boy.flower=girl.flower;
触发器:四要素
监视地点(表)、
监视事件(操作)、
触发事件、
触发事件
new监视新增行信息,old监视撤销行信息
delimiter $ 修改结束符,方便sql中执行多条SQL语句
create trigger triggerName
after/before
insert/update/delete
on
tableName
for each row
Begin
SQL语句
end;
模板代码:
监视增
create trigger tg2
after insert on o
for each row
begin
update g set num = num -new.much where id = new.gid;
end$
监视删
create trigger tg3
after delete on o
for each row
begin
update g set num = num + old.much where id = old.gid;
end$
监视改
create trigger tg4
after update on o
for each row
begin
update g set num = num + old.much -new.much where id = new.gid;
end$
after insert on o
for each row
begin
update g set num = num -new.much where id = new.gid;
end$
监视删
create trigger tg3
after delete on o
for each row
begin
update g set num = num + old.much where id = old.gid;
end$
监视改
create trigger tg4
after update on o
for each row
begin
update g set num = num + old.much -new.much where id = new.gid;
end$
备份:增量备份、整体备份
备份
mysqldump.exe以导表为例
mysqldump -u root -psystem 库名 表1 表2 > 文件名.sql 导出的是建表语句
省略表名则导入库中所有表
mysqldump -u root -psystem -B 库名1 库名2 >文件名.sql 导出多个库库
mysqldump -u root -psystem -A > 文件名.sql 导出所有库
恢复:
Mysql> source 文件名.sql; 读取sql文件读取(以库位单位恢复)
Mysql> use 库名 在库目录下source .sql 即表恢复
导出的是
索引:
索引的主要所用是增加查询效率,在一定程度上会影响其他增删改的操作,
一般在在where使用频繁的列上加
尽量索引散列值
普通索引:增加查询速度
唯一索引:unique index 行上的值不能重复
主键索引:primary key 根据主键索引
全文索引:fulltext
查看索引:show index from 表名;
建立索引:alter table 表名 add index/unique/fulltext/primary 索引名(列名)
删除索引:alter table 表名 drop index 索引名;
JDBC运行方式:
1.导入相对应的数据库驱动:如mysql的
2.注册驱动,三种方式 a.通过new驱动对象 b.设置system属性 c.classforname加载(推荐该方式)
3.连接数据库连接(数据库基础信息,用户名,密码)
4.创建语句 封装SQL语句
5.执行语句 执行SQL语句
6.处理结果
7.关闭资源
一、在处理数据库敏感词传入如:'or 1 or',采用设置PreparedStatement执行sql,可以对关键字进行预处理。
二、DataBaseMetaData dbma = conn.getDataMeta(); 获取数据库元素信息,如数据库名、版本