数据库笔记
一、数据库的常见命令
1、mysql服务器的启动方式
● 通过mysql的command client
输入密码,启动成功。如果采用该方式不能登录,可以采用第二种方式启动。
● 在windows的命令行窗口启动mysql的服务
首先要找到mysql的安装路径,设置path。
Mysql.exe服务所在的目录
Set path命令
Set path=C:\Program Files\MySQL\MySQL Server 5.1\bin;
在命令行上设置path,这种设置方式只对当前窗口生效。要想对其他所有窗口都生效,可以采用如下方式设置path环境变量。
在桌面上:“我的电脑”——“属性”——出现如下窗口
在该窗口中选择:“高级”——“环境变量”。弹出如下窗口(环境变量)。
可以采用新建path环境变量的方式(如果不存在path环境变量的话)。
注意:一个path可以有多个值(就是多个不同的路径,每个路径使用英文输入法的分号“;”)。
在windows的命令行下输入mysql
使用指定的身份登录
2、常见命令
1)、查看当前账户下的所有database
show databases;
2)使用指定database
use test;
3)创建一个database
create database mydb;
4)删除指定的database
drop database mydb;
5)显示指定database中所存在的
表
关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf
show tables;
6)在指定的database中创建表
Create table xs.test(
name varchar(10) not null,
Age int(3)
);
注意:如果要创建的表是在当前database下,则database和引导符可以省略。
7)删除表
drop table test;
8)查看表的结构
Desc test;
9)查看表的创建语句
show create table test;
3、数据库设计第一范式:字段不可分
数据库中的表是为了以更好的
格式
pdf格式笔记格式下载页码格式下载公文格式下载简报格式下载
对数据进行存储和管理的。比如有如下数据要存储,如何设计该表,存储数据。
信息:张三今年28岁,于1983年5月26日出生于浙江省嘉兴市,是一个不折不扣的好学生。
如果按如下方式设计表:
create table student(
name varchar(10),
age int(3),
info varchar(800)
);
如果使用该表结构存储学生信心。出了可以具体的查到学生的姓名和年龄,其他信息都是模糊的。不具体。比如学生的出生日期和出生地和学生的详细信息都混在一起了(info列)。这样不利于查询。违背了数据库设计的第一范式(每列都是不可分的)。
按照第一范式的要求,可以将表设计为如下格式:
create table student(
name varchar(10),
age int(3),
birthday date,
province varchar(20),
city varchar(20),
info varchar(800)
);
这个表的结构就比上面的表的设计要详细,和利于查阅管理。
二、数据库的增删改查语句
1、增加记录:insert into 表名称(列名称) values(每列对应的具体值);
以student表为例:
create table student(
name varchar(10),
age int(3),
birthday date,
province varchar(20),
city varchar(20),
info varchar(800)
);
向该表录入一条语句:
insert into student(name,age,birthday,province,city,info)
values('汤少雄',21,'1989-9-9','湖南','长沙','好人呢!!!');
上面的语句是为该表中的每一列都赋值了。那么像这种情况,可以省略表名称之后的列名称集合。上面的语句还可以这样写:
insert into student values('罗志雄',21,'1989-9-9','湖南','长沙','好人呢!!!');
如果允许列非空。我们也可以只为指定的列赋值,比如:
insert into student(name,age) values('尹钊',21);
2、删除记录:delete from 表名称 [条件]
Delete from student where name = ‘尹钊’;
3、修改表中的记录:update 表名 set 列1=新值 , 列2=新值 [条件]
update student set birthday='1988-8-8', province='湖南' where name = '尹钊';
4、查询记录:select 列名1 [[as ] 别名称], 列名2 from 表名称 [条件]
select name , age from student;
注意:如果要想查看表中的每一列。可以使用通配符“*”
select * from student;
使用别名:
select name as '姓名' , age '年龄' from student;
5、常见的数据库中出现的关键字
名称
介绍
Where
表示引出条件的关键字
And
连接两个条件、且的关系
Or
连接两个条件、或的关系
Not
对条件取反
Between and
范围的取值,闭区间取值
In
在指定的范围内任意取值匹配
Is
也是一个boolean类型的结果表达式:is null | is not null
null
表示该列的值不确定,不是没用或空的意思
Like
在进行模糊查询的时候使用,经常和“_”、“%”联合使用
Order by
对查询结果进行排序的时候使用,和ASC(升序)、DESC(降序)联合使用
Group by
分组语句,对表中的记录进行分组
Having
对分组语句查询得到的结果进行过滤
As
对表的列或表名称起别名的一个关键字
Outer join
外连接查询
Left outer join
左外连接查询
Right outer join
右外连接查询
Union all
全连接查询
Distinct
去除重复的结果,重复的结果只保留一个
6、简单查询
1、在student表中查询name等于“尹钊”的记录
select * from student where name='尹钊';
注意:因为name这一列的值不是要求唯一的。那么这条语句的查询结果又可能是多条。
2、在student表中查找age大于15的记录
select * from student where age>15;
3、在student表中查找age小于15的记录
select * from student where age<15;
4、在student表中查找birthday位于“1980-08-08”和“1989-09-09”出生的记录
select * from student where birthday<='1989-9-9' and birthday>='1980-8-8';
注意:在对日期格式的数据进行值比较时,要将日期值用单引号引起来。
5、在student表中查找age大于60 或者age小于18
select * from student where age>=60 or age<18;
6、在student表中查找age取值范围在18至60之间的记录
select * from student where age between 18 and 60;
注意:位于between and之间的值是可以取到的。也就是说会以闭区间匹配。
7、在student表中查找age等于32岁、18岁的记录
select * from student where age=32 or age = 18;
select * from student where age in (18,32);
8、在student表中查找city为null(不确定)的记录
select * from student where city=null;错误的写法
select * from student where city is null;要使用is关键字
9、在student表中查找city不是null值的记录
select * from student where city is not null;
10、在student表中查找age不是18和32的记录
select * from student where age not in (18,32);
11、在student表中查找name以字符“h”开头的记录
select * from student where name='h%';这样写是错误的。
select * from student where name like 'h%';这样写才是对的。
12、在student表中查找name的第二个字符是“x”的记录
select * from student where name like '_x%';
注意:“_”通配一个字符,而“%”通配多个字符。在mysql数据库中对于值的大小写是不敏感的。
13、对student表中的记录按照birthday进行降序查找
select * from student order by birthday desc;
注意:如果是按照升序排,可以使用asc。也可以不写。默认就是升序。
7、数据库设计第二范式:避免数据冗余
现在有如下信息需要保存到数据库中:学生张三(18岁,来自湖南省)在来到华点软件学院一个月了。到目前为止他学习了javaSe的课程,这门课程是java的基础课程。一定要学好才行。很重要。还有其他的一些同学。请设计表存储数据。
按照数据库设计第一范式的要求,可以对以上数据进行类型提取:姓名、年龄、地址、学生介绍、课程名称、课程介绍。因此表的结构如下:
create table student_object(
name varchar(10),
age int(3),
address varchar(200),
sinfo varchar(500),
oname varchar(40),
oinfo varchar(500)
);
测试数据:
insert into student_object values('zs',15,'anhui','good boy.','javaSe','so good......');
insert into student_object values('ls',15,'zhejiang','good boy.','javaSe','so good......');
insert into student_object values('ww',16,'anhui','good boy.','javaWeb','so good......');
insert into student_object values('zl',22,'hunan','good boy.','javaScript','so good......');
insert into student_object values('zb',23,'hunan','good boy.','javaScript','so good......');
然后执行查询:
Select * from student_object;
注意:以上表随然设计完了。但是发行很多数据在该表中不断的重复出现,这在数据库中叫做数据冗余。还有一个问题就是:学院可能开了很多门课程。但是目学生只学了部分课程,那么学生此时没有学习的课程就没有办法在数据库中体现了。显然是不合理的。因此第一范式已经不能解决问题了。由此引出第二范式:
第二范式:一个事物对应一个表。关联表之间采用外键进行关联。而且不允许在表中出现冗余数据。
重新对以上数据进行表的设计:
#每门课程都应该有一个唯一的课程编号。
create table object(
ono varchar(3),
oname varchar(50),
oinfo varchar(500)
);
#学生表中的ono的值应该由Object表中的ono决定。也就是说:student中的ono的值只能#从object中的ono取。
create table student(
sname varchar(10),
age int(3),
address varchar(200),
sinfo varchar(500),
ono varchar(3)
);
向object表中录入测试数据:
insert into object values('001','javaSe','so good!!!!');
insert into object values('002','javaScript','so good!!!!');
insert into object values('002','javaWeb','so good!!!!');
在录入测试数据时发现:两门不同的课程,但是它们的课程编号是相同的。这不符合要求。怎么办呢?这是一种逻辑错误。我们应该想一种办法:让ono这一列的值唯一。那么我们可以采用唯一约束(unique约束)。
重新修改表的结构:
#每门课程都应该有一个唯一的课程编号。
create table object(
ono varchar(3) unique,
oname varchar(50),
oinfo varchar(500)
);
录入测试数据:
insert into object values('001','javaSe','so good!!!!');
insert into object values('002','javaScript','so good!!!!');
insert into object values('002','javaWeb','so good!!!!');
向student表中录入测试数据:
insert into student values('zs',22,'anhui','sssss','001');
insert into student values('ls',23,'anhui','sssss2','001');
insert into student values('ww',23,'zhejiang','sssss3','002');
insert into student values('zl',25,'zhejiang','sssss4','003');
insert into student values('zb',35,'zhejiang','sssss5','007');
这个结果很荒唐(student表中ono没有参照object表中的ono取值)。也属于逻辑错误。怎样可以保证student表中的ono参照object表中的ono取值呢?我们可以使用外键约束(foreign key约束)。
重新定义student表:
create table student(
sname varchar(10),
age int(3),
address varchar(200),
sinfo varchar(500),
ono varchar(3)
);
alter table student add foreign key(ono) references object(ono); 添加外键约束
录入测试数据:
insert into student values('zs',22,'anhui','sssss','001');
insert into student values('ls',23,'anhui','sssss2','001');
insert into student values('ww',23,'zhejiang','sssss3','002');
insert into student values('zl',25,'zhejiang','sssss4','003');
insert into student values('zb',35,'zhejiang','sssss5','007'); 错了。
#该记录违背了外键约束,因为007这个课程编码在object表中不存在对应的记录。
insert into student values(null,'zs',22,'anhui','sssss','002');
insert into student values(null,'zs',22,'anhui','sssss','003');
8、约束
约束类型
功能
Not null
非空约束
Unique
唯一性约束
Foreign key
外键约束
Primary key
主键约束(具备了unique的功能)
主键约束:
● 该列的值是唯一的
● 主键列应该是一个没有任何业务含义的列
● 主键列除了没有业务含义之外,该列习惯用整型数据充当
● 整型的主键列,还习惯是自增长的列
修改上面的表的结构:
create table object(
id int auto_increment primary key,
ono varchar(3) unique,
oname varchar(50),
oinfo varchar(500)
);
create table student(
id int auto_increment primary key,
sname varchar(10),
age int(3),
address varchar(200),
sinfo varchar(500),
ono varchar(3)
);
alter table student add foreign key(ono) references object(ono);
测试数据:
insert into object values(1,'001','javaSe','so good!!!!');
insert into object values(2,'002','javaScript','so good!!!!');
insert into object values(0,'003','javaWeb','so good!!!!');
insert into object values(null,'004','flex','so good!!!!');
insert into object values(8,'005','php','so good!!!!');
insert into object values(null,'006','asp','so good!!!!');
外键约束:
● 在主表和从表中使用
● 由主表定义,从表引用,也就是说:从表中该列的值一定要在主表中取,如果主表中不存在该值,则无法引用
9、对第七节的表结构进行研究
注意,object表中不会出现冗余数据了。但是,student表又出现了冗余数据。显然在第七节的表的结构设计是错误的。
图中显示:5、6条记录和第一条记录发生了数据冗余。
修改第七节的表结构:经分析得出,要创建一个中间关系表。就可以避免student和object表的数据冗余问题了。
create table object(
id int auto_increment primary key,
ono varchar(3) unique,
oname varchar(50),
oinfo varchar(500)
);
create table student(
id int auto_increment primary key,
sno varchar(3) unique,
sname varchar(10),
age int(3),
address varchar(200),
sinfo varchar(500)
);
create table sorefere(
id int auto_increment primary key,
sno varchar(3),
ono varchar(3)
);
alter table sorefere add foreign key(sno) references student(sno);
alter table sorefere add foreign key(ono) references object(ono);
注意:从表引用主表中的列,那么该列的值一定要唯一,也就是说:该列要么是unique约束,或者是主键约束(primary key)。
注意:现在要想添加成绩列,如何做?
只需要该中间关系表即可:
create table sorefere(
id int auto_increment primary key,
sno varchar(3),
ono varchar(3),
grade numeric(5,2)
);
alter table sorefere add foreign key(sno) references student(sno);
alter table sorefere add foreign key(ono) references object(ono);
insert into sorefere values(null,'001','001',25);
insert into sorefere values(null,'001','001',15.666666);
insert into sorefere values(null,'001','001',99.466666);
10、修改表结构
现有表:
create table demo(
naem int
);
1)为指定表添加一列:表示年龄的age
alter table demo add column age int;
alter table demo add age int after name; 在指定的列之后添加一列。
alter table demo add age2 int first; 将要添加的列,作为第一列添加。
2)修改列名称:发现上面的表中姓名列拼写错误
alter table demo change naem name varchar(10);
3)修改指定列的长度:比如上面表中age的长度最多3位即可
alter table demo modify age int(3);
注意:change 和 modify 的区别是:在使用change的时候,原来的列名称也要写上,而使用modify的时候,原来的列名称可以不写。说明,modify主要是修改列的数据类型或长度的。
4)删除列:比如删除上表中的age列
alter table demo drop column age; column 不可省去!!!
注意:在添加、修改时,关键字“column”可以省略不写。即:Alter table 表名称 drop|add [column] 列名称。
三、mysql操作手册的使用
1、数据类型
1)日期类型
● Date 表示的是日期
● Datetime 表示的是日期和时间
● Time 表示的是时间,没有日期
● Timestamp 表示的是日期和时间,默认值是当前系统的日期时间,且和时区关联
2)文本类型
● Char 取值范围是[0,255]
● Varchar 取值范围是[0,65535]
● Text 文本的最大长度是65535个字符
3)逻辑类型
● Boolean 可以存放的是1 or 0。.
2、内置函数
1)日期、时间函数
● 当前日期函数 curdate() 获取的就是当前服务器的日期
● now() 表示的是当前的日期和时间
● sysdate() 获取当前系统的日期和时间
2)对日期、时间格式化输出的函数
● date_format 对日期和时间进行格式化输出
example :select date_format('2011-8-17 11:07:1','%d-%m-%y');
结果是:17-08-11
select date_format('2011-8-17 11:07:1','%d-%m-%Y');
结果是:17-08-2011
模板字符的定义及意义参考:
3)自增长列的最大值
● last_insert_id() 获取当前表的自增长列的当前最大值
3)字符串处理函数
● concat 字符连接函数
select concat('我的名字是:',firstname,lastname) as info from demo2;
● trim 去除空格的函数 select trim(firstname) from demo2;
● substring 截取字串的函数 select substring('abcdefg',2); 结果是:bcdefg
● lpad|rpad 字符填充函数 SELECT LPAD('hi',4,'??'); 结果 :'??hi'
4)对数字进行格式化的函数
● format 例子:select format(456218569322,0); 结果是:456,218,569,322
select format(456218569322,2); 结果是:456,218,569,322.00
5)数据加密函数
● password 可以对数据进行加密
例子:
create table user(
uname varchar(8) not null,
psword varchar(900) not null
);
insert into user values('zs123','666666');
insert into user values('ls123',password('888888'));
Ls123加密之后的数据图
注意:还有很多内置函数,可以参考手册。
四、数据库的事务
在mysql数据库中,事务默认的是自动提交。我们可以通过set autocommit = 0; 命令使事务的提交方式变为手动提交。
例子:
create table demo7(
name varchar(10),
age int(3)
);
Set autocommit = 0; 手动提交事务
Begin; 开始事务
Insert into demo7 values(‘aaaa’,25); 执行insert语句
Commit;手动提交事务,目的是使insert的内容持久化到数据库的表中。也可以使用rollback对事务进行回滚。
事务的几条原则:也就是ACID原则
● 原子性
● 一致性
● 隔离性
● 稳定性
五、高级查询
1、分组查询和聚合函数的使用
样例表:
create table st(
id int auto_increment primary key,
name varchar(10),
age int,
classname varchar(10),
java numeric(5,2),
php numeric(5,2),
flex numeric(5,2)
);
insert into st values(null,'zs',18,'27班',50,41,78);
insert into st values(null,'lsi',20,'27班',90,41,62);
insert into st values(null,'wwu',18,'28班',10,80,20);
insert into st values(null,'zs',18,'28班',88,46,32);
insert into st values(null,'zouba',23,'28班',98,68,78);
insert into st values(null,'zouqi',23,'27班',98,99,78);
1)查询java成绩高于60分的学生,显示其详细信息
select * from st where java>60;
2)查询java成绩最高的学生,显示该生的name和classname
select name,
classname,
java
from st
where java = (select max(java)
from st);
3)统计各班人数
● 首先要会统计总记录数 select count(id) from st;
● 其次要会分组 select classname from st group by classname; 按班级分组
select count(*) ,
classname
from st
group by classname;
4)查询各班java成绩的总分
● 对某列求和
● 分组
select sum(java),
classname
from st
group by classname;
5)查询各班java成绩最高分的学生,显示name、java、classname列信息
select name,
s.classname,
java
from st s,
(select max(java) as hjava,
classname as classname
from st
group by classname) as tmp
where s.java = tmp.hjava
and
s.classname = tmp.classname;
样例表2:
create table object(
id int auto_increment primary key,
ono varchar(3) unique,
oname varchar(50),
oinfo varchar(500)
);
create table student(
id int auto_increment primary key,
sno varchar(3) unique,
sname varchar(10),
age int(3),
address varchar(200),
sinfo varchar(500)
);
create table sorefere(
sno varchar(3),
ono varchar(3),
grade numeric(5,2)
);
alter table sorefere add foreign key(sno) references student(sno);
alter table sorefere add foreign key(ono) references object(ono);
alter table sorefere add primary key(sno,ono);
6)查询平均成绩高于78分的学生,显示name和其平均成绩
select st.name,
tmp.avggrade
from student st,
(
select sno,
avg(grade) avggrade
from sorefere
group by sno
having avg(grade) > 78
) tmp
where st.sno = tmp.sno;
样表:
create table object(
id int auto_increment primary key,
ono varchar(3) unique,
oname varchar(50),
oinfo varchar(500)
);
create table classroom(
id int auto_increment primary key,
cno varchar(10) unique,
cname varchar(10)
);
create table student(
id int auto_increment primary key,
sno varchar(3) unique,
sname varchar(10),
age int(3),
address varchar(200),
sinfo varchar(500),
cno varchar(10)
);
create table sorefere(
sno varchar(3),
ono varchar(3),
grade numeric(5,2)
);
alter table sorefere add foreign key(sno) references student(sno);
alter table sorefere add foreign key(ono) references object(ono);
alter table sorefere add primary key(sno,ono);
alter table student add foreign key(cno) references classroom(cno);
7)查询各班总分数高于200的班级
select cm.cname,
tmp2.sgrade
from classroom cm,
(select sum(tmp.grade) sgrade,
tmp.cno cno
from (select cno,
grade
from student st,
sorefere sf
where st.sno = sf.sno) tmp
group by tmp.cno) tmp2
where cm.cno = tmp2.cno;
8)查询各班学生的总分数高于150分的学生
select st.sname,
tmp.sgrade
from student st,
(select sno,
sum(grade) sgrade
from sorefere
group by sno
having sum(grade)>150) tmp
where st.sno = tmp.sno;
2、外连接查询
测试数据:
insert into classroom values(null,'001','27班');
insert into classroom values(null,'002','28班');
insert into classroom values(null,'003','29班');
insert into student values(null,'001','zs',25,'ddddd','dfddfd','001');
insert into student values(null,'002','zs2',25,'ddddd','dfddfd','001');
insert into student values(null,'003','zb',25,'ddddd','dfddfd','002');
insert into student values(null,'004','zq',25,'ddddd','dfddfd','001');
9)查询各班学生总人数
select cm.cname,
tmp.sums
from classroom cm
left outer join
(select cno,
count(sname) sums
from student
group by cno) tmp
on cm.cno = tmp.cno;
如何将null值替换为指定的值:可以使用ifnull函数
select cm.cname,
ifnull(tmp.sums,0)
from classroom cm
left outer join
(select cno,
count(sname) sums
from student
group by cno) tmp
on cm.cno = tmp.cno;
例子:左外连接和右外连接
create table demo1(
name varchar(20)
);
insert into demo1 values('a');
insert into demo1 values('b');
insert into demo1 values('c');
insert into demo1 values('d');
create table demo2(
name varchar(20)
);
insert into demo2 values('a');
insert into demo2 values('b');
insert into demo2 values('c');
内连接查询的结果:
select d1.name,
d2.name
from demo1 d1,
demo2 d2
where d1.name = d2.name;
左外连接查询
select d1.name,
d2.name
from demo1 d1
left outer join
demo2 d2
on d1.name = d2.name;
右外连接查询:
select d1.name,
d2.name
from demo1 d1
right outer join
demo2 d2
on d1.name = d2.name;
3、联合查询
联合查询一定要保证每个结果集的列数,对应列的数据类型一定要匹配。
10)查询001班的学生总数和005班的学生总数
select count(sname),
cno
from student st
where st.cno = '001'
union all
select count(sname),
cno
from student st
where st.cno = '003';
注意:union 和 union all 的使用区别
select sname, age
from student
where id = 1
union all
select sname, age
from student
where id = 2;
select sname, age
from student
where id = 1
union
select sname, age
from student
where id = 2;
根据查询结果得出:union all 不会去除重复的记录,而union 会去除重复的记录,对于重复的记录只保留一条。和关键字distinct类似。