SQL结构化查询语言SQL的分类DDL: 数据定义语言Create, Drop,Alter
Grant(授权), if...
DML:数据操纵语言insert, update, delete,
DQL:数据查询语言select
对数据库进行操作创建数据库create database 数据库名称 character set 字符集 collate 字符集校对规则;
查看数据库查看数据库服务器中所有的数据库:
show databases;
查看某个数据库的定义信息:
show create database 数据库名;
修改数据库alter database 数据库名称 character set 字符集 collate 校对规则;
删除数据库drop database 数据库名称;
切换数据库use 数据库名;
查看当前正在使用的数据库select database( );
对数据库表进行操作创建表create table 表名称(字段名称 字段类型(长度) 约束, 字段名称 字段类型(长度) 约束.....);
字段类型:一个实体对应一个表,一个实体属性对应表的一个字段.
java中的类型: mysql中的类型:
byte/short/int/long tinyint/smallint/int/bigint
float float
double double
boolean bit(0/1)非零为真
char/string char和varchar
date date/time/datetime/timestamp
file BLOB/TEXT
char 和 varchar的区别?
char: 通常代表的是固定长度的字符或字符串
定义类型char(8),向这个字段存入字符串hello,那么数据库使用三个空格将其补全.
varchar: 代表的是可变长度的字符串
定义类型是varchar(8),向这个字段存入字符串hello,那么存入到数据库的就是hello.
datetime 和 timestamp的区别?
datetime: 就是既有日期又有时间的日期类型,如果没有像这个字段中存值,数据库使用null存入到数据库中
timestamp: 也是既有日期又有时间的日期类型,如果没向这个字段中存值,数据库会使用当前的系统时间存入到数据库中
约束约束作用:保证数据的完整性
单表约束分类:
主键约束: primary key 主键约束默认就是唯一 非空的
唯一约束:unique
非空约束:not null
建表语句create database web_test1;
use web_test1;
create table user(
• id int primary key auto_increment, //自动增长,id递增
• usename varchar(20) unique,
• password varchar(20) not null,
• age int,
• birthday date
);
查看表查看某个数据库下的所有的表
show tables;
查看某个表的结构信息
desc 表名;
删除表
drop table 表名;
修改表修改表: 添加列
alter table 表名 add 列名 类型(长度)约束;
修改表:修改列类型,长度和约束
alter table 表名 modify 列名 类型(长度) 约束;
修改表: 删除列
alter table 表名 drop 列名;
修改表: 修改列名称
alter table 表名 change 旧列名 新列名 类型(长度) 约束;
修改表: 修改表名
rename table 表名 to 新的表名;
修改表: 修改表的字符集
alter table 表名 character set 字符集;
添加列/添加字段*
alter table 表名 add 字段名 字段类型(长度) 字段的约束;
修改字段的类型或长度*
alter table 表名 modify 字段名 字段类型(长度) 字段的约束;
对数据库表的记录进行操作(重点)添加表的记录向表中插入某些列:
insert into 表名 (列名1,列名2,列名3...) values (值1,值2,值3...);
向表中插入所有列:
insert into 表名 values(值1,值2,值3...);
注意事项(重要):
1 值的类型要与数据库中表的列的类型一致
2 值的顺序要与数据库中表的列的顺序一致
3 值的最大长度不能超过列设置的最大长度
4 值的类型是字符串或日期类型, 需要使用单引号引起来
添加记录
添加某几列
insert into 表名(id,username,password) values (null,'aaa','123');
添加所有列
insert into 表名 values(null,'bbb','123',23,'1996-12-17'); // 和表中字段顺序保持一致
select * from 表名 //即可查看
添加中文记录
insert into 表名 values(null,'张三','123',23,'1996-12-17');
直接向数据库中插入中文记录会出现错误!!!!!解决办法:
show variables like '%character%'; 查看数据库与字符集相关参数
需要将mysql数据库服务器中的客户端部分的字符集改为gbk
找到mysql安装路径: my.ini文件,修改文件中[client]下的字符集
重启mysql服务器 services.msc
修改表的记录
update 表名 set 列名=值,列名=值 [where 条件];
注意事项:
1 值的类型要与列的类型一致
2 值的最大长度不能超过列设置的最大长度
3 字符串类型和日期类型添加单引号
修改某一列的所有值
update 表名 set password = 'abc';
按条件修改数据:
update 表名 set password = 'xyz' where usename = 'bbb';
按条件修改多个列
update 表名 set password = '123', age = 34 where username = 'aaa';
删除表的记录
delete from 表名 [where 条件];
注意事项:
1 删除表的记录, 指的是删除表中的一行记录
2 删除如果没有条件, 默认是删除表中的所有记录
删除某一条记录
delete from 表名 where id = 2;
删除表中的所有记录
delete from 表名;
删除表中的记录有两种做法:
delete from 表名;
删除所有记录, 属于DML 语句,一条记录一条记录的删除,事务可以作用在DML语句上的
truncate table 表名;
删除所有记录, 属于DDL 语句 ,将表删除,然后重新创建一个结构一样的表,事务不能控制DDL
rollback: 回滚
查看表的记录(重点) 环境的准备：
create table exam(
• id int primary key auto_increment,
• name varchar(20),
• english int,
• chinese int,
• math int
);
insert into exam values (null,'张三',85,74,91);
insert into exam values (null,'李四',95,90,83);
insert into exam values (null,'王五',85,84,59);
insert into exam values (null,'赵六',75,79,76);
insert into exam values (null,'田七',69,63,98);
insert into exam values (null,'李老八',89,90,83);
基本查询
select [distinct] * |列名 from 表[条件];
查询所有学生考试成绩信息
select * from exam;
查询所有学生的姓名和英语成绩
select name, english from exam;
查询英语成绩信息(不显示重复的值)
select distinct english from exam;
查看学生的姓名和学生的总成绩
select name,english+chinese+math from exam;
别名查询
select name,english+chinese+math sum from exam;
条件查询
使用where子句
大于> , < , >= , <= <> ,=
like: 模糊查询
in : 范围查询
条件关联: and or not
查询李四学生的成绩:
select * from exam where name = '李四';
查询名称叫李四并且英语大于90分的
select * from exam where name = '李四' and english>90;
查询姓李的学生的信息
select * from exam where name like '李%';
like可以进行模糊查询,在like子句中可以使用下划线或百分号作为占位符
下划线只能代表一个字符,而百分号可以代表多个字符
like '李_' : 名字中必须是两个字,而且是姓李的
like '李%' : 名字中姓李的学生,李字后可以是一个或人任意个字符
like '%四' : 以四结尾的
like '%王%' : 只要名称中包含这个字就可以
查询英语成绩是69,75,89学生的信息
select * from exam where english in(69,75,89);
排序查询
使用 order by 字段名称 asc升序/desc降序 默认升序
查询学生信息并按照语文成绩进行排序
select * from exam order by chinese;
查询学生信息并按照语文成绩倒序排序
select * from exam order by chinese desc;
查询学生信息,先按照语文成绩倒序排序,如果成绩相同就按照英语成绩升序排序
select * from exam order by chinese desc, english asc;
查询姓李的学生的信息,按照英语成绩的降序排序
select * from exam where name like '李%' order by english desc;
分组统计查询
聚合函数使用
sum():
获取所有学生的英语的成绩的总和
select sum(english) from exam;
获取所有学生的英语成绩总和和数学成绩总和
select sum(english),sum(math) from exam;
查询姓李的学生的英语成绩总和
select sum(english) from exam where name like '李%';
查询所有学生各科的总成绩
select sum(english)+sum(chinese)+sum(math) from exam;
select sum(english+chinese+math) from exam;
有什么不同?
第一行: 按照列的方式统计,英语成绩总和+语文成绩总和+数学成绩总和
第二行: 先计算英语+数学+语文 然后再求和
count():
获得学生的总数
select count(*) from exam;
max():
获得数学成绩的最高分
select max(math) from exam;
min():
获得语文成绩的最低分
select min(chinese) from exam;
avg():
获得英语成绩的平均值
select avg(english) from exam;
分组查询使用 group by 字段名称
环境准备:
create table orderitem( id intprimary key auto_increment, productvarchar(20), pricedouble);insert into orderitem values (null,'电视机',2999);insert into orderitem values (null,'电视机',2999);insert into orderitem values (null,'洗衣机',1000);insert into orderitem values (null,'洗衣机',1000);insert into orderitem values (null,'洗衣机',1000);insert into orderitem values (null,'冰箱',3999);insert into orderitem values (null,'冰箱',3999);insert into orderitem values (null,'空调',1999);
按商品名称统计,每类商品所购买的个数
select product, count(*) from orderitem group by product;
按商品名称统计,每类商品所花费的总金额
select product,sum(price) from orderitem group by product;
按商品名称统计,花费金额在5000以上的商品
where 子句后边不能跟着聚合函数 如果现在要使用带有聚合函数条件的过滤(分组后的条件过滤) 需要使用一个关键字 having
select product,sum(price) from orderitem group by product having sum(price) >5000;
按商品名称统计,统计没类商品花费的总金额在5000以上的商品, 总金额升序
select product,sum(price) from orderitem group by product having sum(price) >5000 order by sum(price) asc;
总结S(select)...F(from)...W(where)...G(group by)...H(having)...O(order by);
数据库的备份第一步: 在cmd命令行窗口 输入mysqldump -u root -p 要备份的数据库名称 > 备份路径
数据库的还原一第一步:在数据库服务器内部创建数据库
mysql -u root -p
create database 数据库名
第二步:在命令行窗口
mysql -u root -p 要还原数据的名字 < 备份的路径
数据库的还原二第一步:在数据库服务器内部创建数据库
mysql -u root -p
create database 数据库名
第二步: 切换到该数据库使用source命令还原
use 数据库名
source 备份的路径
mysql多表多表约束外键约束： 用来保证数据完整性（多表之间）
alter table 表名 add foreign key(要添加外键的字段) reference key dept (要和哪个表的哪个字段关联);
表与表之间的关系一对多关系
一个部门下可以有多个员工，一个员工只能属于某一个部门
建表原则： 在多的一方创建外键指向一的一方的主键
多对多关系
一个学生可以选择多门课程，一门课程也可以被多个学生选择
建表原则：需要创建第三张表（中间表）， 在中间表中至少两个字段，分别作为外键去指向多对多双方的主键。
一对一的关系
一个公司可以有一个注册地址，一个注册的地址只能对应一个公司
唯一外键的对应的方式：假设一对一是一个一对多关系，需要在多的一方创建外键指向一怼的一方的主键，将外键设置为唯一（unique）
主键对应的方式：将两个表的主键建立关系即可
多表查询的分类连接查询交叉连接： cross jion 查询到的是两个表的笛卡尔积。
语法： select * from 表一 cross join表二；
select * from 表一，表二；
内连接：inner join（inner是可以省略的）
显示内连接：在SQL中显示的调用inner join关键字
语法：select * from 表一 inner join 表二 on 关联条件；
隐式内连接：在SQL中没有调用inner join 关键字
语法：select * from 表一，表二 where 关联条件；
外连接：outer join(outer 可以省略)
左外连接：
语法：select * from 表一 left outer join 表二 on 关联条件；
以左边为基准，根据关联条件去右表查询，如果匹配上了，则返回匹配上的数据，如果匹配不上则返回null
右外连接：
语法：语法：select * from 表一 right outer join 表二 on 关联条件；
内连接和外连接的区别？子查询一个查询语句条件需要以来另一个查询语句的结果。
带in的子查询
查询学生生日在91年之后的班级的信息。
select * from classes where cid in(select cno from student where birthday > '1991-01-01');
带exists的子查询
查询学生生日大于91年1月1日，如果记录存在，执行语句
select * from classes where exists(select cno from student where birthday > '1991-01-01');
带any的子查询
大于任意一个
select * from classes where cid> any(select cno from student);
带all的子查询
select * from classes where cid > all(select cno from student);
多表查询练习查询班级名称，和班级总人数
select c.cname,count(*) from classes c, student s where c.cid = s.cno group by c.cname;
查询学生的姓名和学生所选的总课程平均分
select s.sname,avg(sc.score) from student, s,stu_cour sc where s.sid = sc.sno group by s.sname;
查询学生的姓名和学生的选课总数，显示选课超过2门的学生姓名
select s.name, count(*) from student, s,stu_cour sc where s.sid = sc.sno group by s.sname;
查询平均趁成绩大于80 的学生成绩
select count(*) from student s where s.sid in(select sc.sno,avg(sc.score) from stu_cour sc group by sc.sno having avg(sc.score)>80);
查询学生和平均成绩，但是平均成绩大于01班的任何一个学生的平均成绩。
select s.sname,avg(sc.score) from student s,stu_cour sc where s.sid = sc.sno group by
s.sname having avg(sc.score) > any(SELECT AVG(sc.score) FROM student
s,stu_cour sc,classes c WHERE s.sid = sc.sno AND s.cno = c.cid AND c.cname= '01班' GROUP BY s.sname);
事务的概念指的是逻辑上的一组操作，组成这组操作的各个逻辑单元，要么全都成功，要么全都失败
开启事务：
start transaction;
提交事务：
commit;
回滚事务：
rollback;
事务的特性原子性
事务的不可分割，组成事务的各个逻辑单元不可分割
一致性
事务执行的前后，数据完整性保持一致
隔离性
事务执行不应该受到数据库中其他事务的干扰
持久性
事务一旦结束（包括提交和回滚），数据就持久化到数据库中。
隔离性事务执行不应该受到数据库中其他事务的干扰
如果不考虑隔离性（一个事务执行收到其他的事务的干扰），引发一些安全问题，主要体现在读取数据上
在读取数据上：
脏读：一个事务都到了另一个事务未提交的数据，导致查询结果不一样
不可重复读：一个事务读到了另一个事务已经提交的update的数据，导致多次查询结果不一致
虚读/幻读： 一个事务读到了另一个事务已经提交的insert的数据，导致多次查询结果不一致
解决这些安全性问题：
设置事务的隔离级别
read uncommitted: 脏读， 不可重复读，虚读都有可能发生
read committed: 避免脏读，但是不可重复读和虚读是有可能发生
repeatable read: 避免脏读和不可重复读，但是虚读有可能发生
serializable: 避免脏读，不可重复读，虚读
JDBC开发步骤步骤1:注册驱动：Class.forName()
步骤2:获得连接：Connection conn = DriverManger.getConnection(url,username,password)// Connection java.sql包下的
步骤3:编写SQL语句，String sql =""，以前的值的地方用占位符代替 ?就是占位符
步骤4:预编译sql并且获得可执行sql语句的对象PreparedStatement ps = conn.prepareStatemet(String sql)
步骤5:为sql语句设置值，用值代替sql语句中的? ps.setXXX(int n,值) n：代表第几个问号的意思，n从1开始
步骤6:执行sql语句
如果是查询：ResutSet rs = ps.executeQuery();
遍历结果集：
while(rs.next()){
rs.getXXX(String columnName)/rs.getObject(String columnName) ；
//columnName默认是表地段名，如果取了别名，用别名
}
如果是增删改：int num = ps.executeUpdate() ;// num 代表影响的行数
步骤7:释放资源
工具类的抽取：Properties对象的使用Properties prop = new Properties();
prop.load(new FileInputStream(String path));
prop.getProperty(String key)
属性文件：.properties
key=value
aaa=adfadf
bbb=dafda