// sql语言 ************************************************
//******数据库******：
//查看所有数据库
show databases;
//创建数据库
create database name;
//删除数据库
drop database name;
//修改数据库默认码表
alter database name default character set utf-8;
//选择数据库
use name;
//******表******：
//查看所有表
show tables;
//创建表
create table student(sid int, sname varchar(20),sage int);
//查看表结构
desc student;
//删除表
drop student;
//修改表的名字
alter table student rename to teacher;
//添加表的字段（属性）
alter table student add column sgender varchar(2);
//删除字段
alter table student drop column sgender;
//修改字段类型
alter table student modify column sgender varchar(1);
//替换字段
alter table student change column sgender sex varchar(2);
//******数据******
//增加一组数据
insert into student values('313....','cz',23);
//插入部分数据
insert into student (saccount,sname) values('313....','cz');
//修改一列所有数据
update student set saccount='3139801004';
//带条件修改
update student set saccount='3139801004' where sname='cz';
//修改多个属性
update student set account='3139801004',sage=23 where sname='cz';
//删除所有数据
delete from student;
//带条件删除
delete from student where age=21;
//另一种删除方式，不能带条件，但是不同于delete，可以把约束删除
truncate from student;
//查询所有列
select * from student;
//查询制定列
select age,sname from student;
//查询时添加常量列,临时添加，显示完消失，下同
select age,sname,'浙江大学'as'学校' from student;
//查询时合并列,只能数值合并
select sname,(sage+smoney)as‘abc’ from student;
//查询时去除重复
select distinct sage from student;
//条件查询
/*
可用的逻辑表达式：
and,or,<,>,<=,>=,<>(不等于sage<>20)
between and: sage between 20 and 25;(包前包后)
is null,is not null;
*/
select sname from student where .....
//模糊查询
select sname frome student where like 'chen%'; //任意字数
select sname frome student where like 'chen_'; //固定字数
//聚合查询
/*
聚合函数: sum(),avg(),max(),min(),count()(求个数，不记录null);
*/
select avg(sage) as 'avgage' from student;
//分页查询 limit 第几页，几个数据
select * from student limit 0,2;
//查询排序 ASC正序 DESC反序
select * from student order by sage asc; // 默认正序
//分组排序 group by
select sex,count(*) from student group by sex; //效果 man 3; women 2;
//分组后再筛选
select sex,count(*) from student where group by having count(*)>2;
//Select into语句
select sname into tercher from student; //用于表
select sname into x from student where sid=1; //用于变量
//DDL,DML,DQL,DCL
1. 数据查询语言DQL //SELECT，FROM，WHERE
2 .数据操纵语言DML //INSERT, UPDATE, DELETE
3. 数据定义语言DDL //CREAT,DROP，ALTER，TRUNCATE
4. 数据控制语言DCL //GRANT
//******约束******
/*
普通约束:
default 默认值
not null 非空
unique 唯一
primary key 主键,不能重复
zerofill primary key auto_increament 主键自动从0开始填充
*/
/*
外键约束格式：
constraint 本表名_参考的表名_fk foreign key(本表内的元素) references 参考表名(参考表元素)
on update cascade : 级联修改
on delete cascade : 级联删除
表1：lesson(
id int primary key, //被参考的表中要有个primary key
lessonName varchar(20); //1=语文，2=数学， 3=英语
)
表2: student(
name varchar(20),
sex varchar(2),
less int, //课程
constraint student_lesson_fk foreign key(less) references lesson(id) on update cascade on delete cascade
// 效果：不允许加入less的数字是lesson表中没有的，lesson表中的id改了或删除了student表中也一起变更
)
*/
create table student(
age int ...普通约束...;
外键约束
);
//******关联查询******
/*
student(
studentId int,
sname varchar(20),
lessId int,
)
lesson(
lname varchar(20),
lessonId int
)
要的效果：
chenzhou math
chenzhen english
。。。。。
*/
//内连接：内连接必须两方都有的才显示出来
select sname,lname fronm student lesson where student.lessId=lesson.lessonId; //或者
select s.sname l.lname
from student s
inner join lesson l
on s.lessId=l.lessonId; //s,l为表的别名，可以不用
//外连接:
//左（右：把left该right）连接:左连接不同于内连接，左边值（s.sname）会全部显示出来，右边的没有匹配的就是NULL
select s.sname l.lname
from student s
left join lesson l
on s.lessId=l.lessionId;
//******存储过程******
//创建
delimiter$
create procedure TEXT(..in age int, out .....参数..) //参数前缀in,out,inout：输入，输出，输入输出
begin
。。。。。。
end$
//执行
call TEXT(10,....)
//语法
//赋值
set age=10;
//变量，声明变量 declare x int,赋值 set x = 1;
@@x //全局变量, 由服务器创建
@x //会话变量，一次与数据库连接的会话中
x //局部变量，存储过程中
//控制流程
if 条件 then
。。。。;
elseif 条件 then
。。。。;
elseif 条件 then
。。。。;
else
。。。。;
end if;
where 条件 do
。。。;
。。。;
end where;
//******触发器******
//触发器类型insert on , update on, delete on
create trigger 名字 after insert on 表 for each row -- 当往员工表插入一条记录时
。。。。。。; //执行动作
//查看活跃的连接列表
show processlist;
//******数据库范式******
第一范式： 要求表的每个字段必须是不可分割的独立单元(一个单位一个值，name='chenzhou' 不能 name='chenzhou|chenzhen'）
第二范式： 在第一范式的基础上，要求每张表只表达一个意思。表的每个字段都和表的主键有依赖。(错误：[课程代号 学号])
第三范式： 在第二范式基础，所有字段都只能与主键有直接决定依赖关系。（ 错误：[姓名 学号 课程代码 课程分数] ）
// jdbc ************************************************
//java连接数据库的方法
/*
连接数据库要用的三个要素：
1.url："jdbc:mysql://localhost:3306/chenzhou"
jdbc协议：数据库协议：主机：端口/数据库名
2.user: 用户名
3.password: 密码
*/
//连接方法一,推荐
Class.forName("com.mysql.jdbc.Driver"); //加载了该Driver类,运行了其中静态代码块，向DriverManager注册了驱动
Connection conn = DriverManager.getConnection(url,user,password);
//方法二
Driver driver = new com.mysql.jdbc.Driver();
Properties pro = new Properties();
pro.setProperty("user",user);
pro.setProperty("password",password);
Connection conn = driver.connect(url,pro);
//jdbc常用API
//关闭Connection
conn.close();
//创建statement对象
Statement statement = conn.createStatement();
//执行静态sql更新语句（DDL，DML)
int r = statement.executeUpdate(String sql); //返回更新行数
ResultSet result = statement.executeQuery(String sql);
//关闭Statement
statement.close();
//ResultSet操作
XX e = result.getXX("name");
XX e = result.getXX(index); //列的序号，从1开始
boolean flag = result.newx(); //光标移动到写一行
//PrepareStatement 常用
String sql = "UPDATE student SET name=? WHERE id=?" //？处是不同于静态sql
PrepareStatement pre = conn.prepareStatement(sql);
pre.setString(1,"cz"); //第一个？参数设置为"cz";
pre.setInt(2,3139); //第二个设置为3139;
int flag = pre.executeUpdate();
//Result result = pre.executeQuery(); 同静态，用于DQL语句
//存储过程
String sql = "CALL text(?,?)" //第一个是输入，第二个是输出
CallableStatement call = conn.prepareCall();
call.setInt(1,10);
call.registerOutParameter(2,java.sql.Types.VARCHAR);
call.executeQuery(); //存储过程只有executeQuery();
String result = call.getString(2); //结果
//数据库元数据
DatabaseMetaData md = conn.getMetaData();
//获取数据库信息
String user = md.getUserName(); //用户名
String url = md.getURL(); //获取连接数据库的url
String p = md.getDataBaseProductName();//Mysql
//statement的元数据
PreparedStatement ps = conn.prepareStatement(sql);
ParameterMetaData pm = ps.getParameterMetaData();
//获取sql语句中的？数，即参数数量
int num = pm.getParameterCount();
//ResultSet的元数据
ResultSet rs = ps.executeQuery(sql);
ResultSetMetaData rsmd = rs.getMetaData();
//获取列数和列名称
int count = rsmd.getColumnCount(); //获取结果的列数
String name = rsmd.getColumnName(1); //索引从1开始
网友评论