数据库
什么是数据库?
数据库就是存储在硬盘上的文件,数据库产品都是采用一种优良的算法在保存数据,方便我们对数据进行增删改查
数据库服务器: 安装了数据库软件的计算机
数据库:数据库服务器可以创建多个数据库, 一个web应用对应一个数据库
表: 一个库中有多张表,一个javabean对应一张表
sql语句
一、针对数据库和表的操作
数据库 database 表 table
创建 create
修改 alter
删除 drop
查看 show
创建一个数据库
create database a;
查看数据库
show databases;
删除表user
drop table user;
练习
创建一个名称为mydb1的数据库。
create database mydb1;
创建一个使用utf-8字符集的mydb2数据库。
create database mydb2 character set utf8;
创建一个使用utf-8字符集,并带校对规则的mydb3数据库。
create database mydb3 character set utf8 collate utf8_general_ci;
查看数据库的定义信息
show create database mydb2;
删除前面创建的mydb3数据库
drop database mydb3;
// 使用数据库
use mydb1;
创建一个a表
create table a
(
name varchar(20)
);
insert into a (name) values ('aaa');
insert into a (name) values ('bbb');
备份数据库表中的数据
mysqldump -u 用户名 –p密码 数据库名 > 文件名.sql恢复数据库
Source 文件名.sql
备份mydb1
mysqldump -uroot -proot mydb1 > d:\a.sql
恢复数据库的数据
source d:\a.sql
mydb2的字符集修改为gbk
alter database mydb2 character set gbk;
创建一个员工表
字段 属性
id 整形
name 字符型
gender 字符型或bit型
brithday 日期型
entrydate 日期型
job 字符型
salary 小数型
resume 大文本型
create table employee
(
id int,
name varchar(20),
gender varchar(6),
birthday date,
entrydate date,
job varchar(20),
salary float,
resume text
);
查看表的定义信息
show create table employee;
查看表的结构
desc employee;
在上面员工表的基础上增加一个image列。
alter table employee add image blob;
修改job列,使其长度为60。
alter table employee modify job varchar(60);
删除gender列。
alter table employee drop gender;
表名改为users。
rename table employee to users;
修改表的字符集为utf-8
alter table users character set utf8;
列名name修改为username
alter table users change name username varchar(30);
二、针对表中数据的操作
针对表中数据的操作无外乎四个:增删改查 crud : create read update delete
1 增 insert语句
创建一张 employee 表
create table employee
(
id int,
name varchar(20),
gender varchar(10),
birthday date,
salary float,
entry_date date,
resume text
);
向表中添加三条记录
insert into employee (id,name,gender,birthday,salary,entry_date,resume) values (1,'zhangsan','male','1980-11-22',1000,'2000-4-12','good boy');
insert into employee (id,name,gender,birthday,salary,entry_date,resume) values (2,'wangwu','male','1983-11-6',1000,'2004-4-12','good boy');
insert into employee (id,name,gender,birthday,salary,entry_date,resume) values (3,'xiaohong','female','1992-11-22',1000,'2012-2-12','good girl');
insert into employee (id,name,gender,birthday,salary,entry_date,resume) values (4,'小王','male','1985-11-22',1000,'2012-2-12','一个好男孩');
查看表中所有的数据
select * from employee;
查看一下数据库编码的信息
show variables like 'character%';
| Variable_name | Value
+--------------------------+--------
| character_set_client | utf8
| character_set_connection | utf8
| character_set_database | utf8
| character_set_filesystem | binary
| character_set_results | utf8
| character_set_server | utf8
| character_set_system | utf8
修改客户端编码
set character_set_client=gbk;
set character_set_results=gbk;
insert employee values (5,'xiaolan','female','1992-11-22',1000,'2012-2-12','good girl');
insert employee (id,name) values (6,'aaa');
insert employee (id,name) values (7,'bbbb'),(8,'ccc'),(9,'dddd');
2 改 update 语句
将所有员工薪水修改为5000元。
update employee set salary=5000
将姓名为’zhangsan’的员工薪水修改为3000元。
update employee set salary=3000 where name='zhangsan';
将姓名为’wangwu’的员工薪水修改为4000元,gender改为female。
update employee set salary=4000,gender='female' where name='wangwu';
将xiaohong的薪水在原有基础上增加1000元。
update employee set salary=salary+1000 where name='xiaohong';
3 删 delete 语句
删除表中name为’zhangsan’的记录。
delete from employee where name='zhangsan';
删除表中所有记录。
delete from employee;
使用truncate删除表中记录。
truncate employee;
4 查 select 语句
创建一个student表 添加7条记录
create table student(
id int,
name varchar(20),
chinese float,
english float,
math float
);
insert into student(id,name,chinese,english,math) values(1,'张小明',89,78,90);
insert into student(id,name,chinese,english,math) values(2,'李进',67,53,95);
insert into student(id,name,chinese,english,math) values(3,'王五',87,78,77);
insert into student(id,name,chinese,english,math) values(4,'李一',88,98,92);
insert into student(id,name,chinese,english,math) values(5,'李来财',82,84,67);
insert into student(id,name,chinese,english,math) values(6,'张进宝',55,85,45);
insert into student(id,name,chinese,english,math) values(7,'黄蓉',75,65,30);
练习
查询表中所有学生的信息。
select * from student;
查询表中所有学生的姓名和对应的英语成绩。
select name,english from student;
过滤表中重复数据。
select distinct english from student;
在所有学生分数上加10分特长分。
select name,english+10,chinese+10,math+10 from student;
统计每个学生的总分。
select name,english+chinese+math from student;
使用别名表示学生分数。
select name,english+chinese+math as sum from student;
where子句用于加查询条件
查询姓名为李一的学生成绩
select * from student where name='李一';
查询英语成绩大于90分的同学
select * from student where english>90;
查询总分大于200分的所有同学
select *,english+chinese+math sum from student where english+math+chinese>200;
查询英语分数在 80-90之间的同学。
select * from student where english between 80 and 90;
查询数学分数为89,90,91的同学。
select * from student where math in(89,90,91);
查询所有姓李的学生成绩。
select * from student where name like '李%';
查询姓李的名字是两个字的学生
select * from student where name like '李_';
查询数学分>80,语文分>80的同学。
select * from student where math>80 and chinese>80;
查询英语>80或者总分>200的同学
select *,english+chinese+math sum from student where english>80 or english+chinese+math>200;
对数学成绩排序后输出。
select * from student order by math;
对总分排序后输出,然后再按从高到低的顺序输出
select *,english+chinese+math sum from student order by math+english+chinese desc;
对姓李的学生成绩排序输出 order by 从句要位于 where从句的后面
select *,english+chinese+math sum from student where name like '李%' order by math+english+chinese;
合计函数 count
insert into student(id,name,chinese,english,math) values(8,'郭靖',null,null,18);
统计一个班级共有多少学生?
select count(*) from student;
select count(chinese) from student;
统计数学成绩大于90的学生有多少个?
select count(*) from student where math>90;
统计总分大于240的人数有多少?
select count(*) from student where chinese+math+english>240;
合计函数 sum
统计一个班级数学总成绩?
select sum(math) from student;
统计一个班级语文、英语、数学各科的总成绩
select sum(math),sum(english),sum(chinese) from student;
统计一个班级语文、英语、数学的成绩总和
select sum(chinese+math+english) from student;
统计一个班级语文成绩平均分
select sum(chinese)/count(*) from student;
select sum(chinese)/count(chinese) from student;
合计函数 不考虑为null的数据
求一个班级语文平均分?
select avg(chinese) from student;
求一个班级总分平均分
select avg(chinese+math+english) from student;
求班级最高分和最低分
select max(chinese) from student;
订单表
create table orders(
id int,
product varchar(20),
price float
);
insert into orders(id,product,price) values(1,'电视',900);
insert into orders(id,product,price) values(2,'洗衣机',100);
insert into orders(id,product,price) values(3,'洗衣粉',90);
insert into orders(id,product,price) values(4,'桔子',9);
insert into orders(id,product,price) values(5,'洗衣粉',90);
select * from orders group by product;
select *,count(*) quantity,sum(price) from orders group by product;
查询单类商品购买价格高于100的
select *,count(*) quantity,sum(price) from orders group by product having sum(price)>100;
什么是数据库?
数据库就是存储在硬盘上的文件,数据库产品都是采用一种优良的算法在保存数据,方便我们对数据进行增删改查
数据库服务器: 安装了数据库软件的计算机
数据库:数据库服务器可以创建多个数据库, 一个web应用对应一个数据库
表: 一个库中有多张表,一个javabean对应一张表
sql语句
一、针对数据库和表的操作
数据库 database 表 table
创建 create
修改 alter
删除 drop
查看 show
创建一个数据库
create database a;
查看数据库
show databases;
删除表user
drop table user;
练习
创建一个名称为mydb1的数据库。
create database mydb1;
创建一个使用utf-8字符集的mydb2数据库。
create database mydb2 character set utf8;
创建一个使用utf-8字符集,并带校对规则的mydb3数据库。
create database mydb3 character set utf8 collate utf8_general_ci;
查看数据库的定义信息
show create database mydb2;
删除前面创建的mydb3数据库
drop database mydb3;
// 使用数据库
use mydb1;
创建一个a表
create table a
(
name varchar(20)
);
insert into a (name) values ('aaa');
insert into a (name) values ('bbb');
备份数据库表中的数据
mysqldump -u 用户名 –p密码 数据库名 > 文件名.sql恢复数据库
Source 文件名.sql
备份mydb1
mysqldump -uroot -proot mydb1 > d:\a.sql
恢复数据库的数据
source d:\a.sql
mydb2的字符集修改为gbk
alter database mydb2 character set gbk;
创建一个员工表
字段 属性
id 整形
name 字符型
gender 字符型或bit型
brithday 日期型
entrydate 日期型
job 字符型
salary 小数型
resume 大文本型
create table employee
(
id int,
name varchar(20),
gender varchar(6),
birthday date,
entrydate date,
job varchar(20),
salary float,
resume text
);
查看表的定义信息
show create table employee;
查看表的结构
desc employee;
在上面员工表的基础上增加一个image列。
alter table employee add image blob;
修改job列,使其长度为60。
alter table employee modify job varchar(60);
删除gender列。
alter table employee drop gender;
表名改为users。
rename table employee to users;
修改表的字符集为utf-8
alter table users character set utf8;
列名name修改为username
alter table users change name username varchar(30);
二、针对表中数据的操作
针对表中数据的操作无外乎四个:增删改查 crud : create read update delete
1 增 insert语句
创建一张 employee 表
create table employee
(
id int,
name varchar(20),
gender varchar(10),
birthday date,
salary float,
entry_date date,
resume text
);
向表中添加三条记录
insert into employee (id,name,gender,birthday,salary,entry_date,resume) values (1,'zhangsan','male','1980-11-22',1000,'2000-4-12','good boy');
insert into employee (id,name,gender,birthday,salary,entry_date,resume) values (2,'wangwu','male','1983-11-6',1000,'2004-4-12','good boy');
insert into employee (id,name,gender,birthday,salary,entry_date,resume) values (3,'xiaohong','female','1992-11-22',1000,'2012-2-12','good girl');
insert into employee (id,name,gender,birthday,salary,entry_date,resume) values (4,'小王','male','1985-11-22',1000,'2012-2-12','一个好男孩');
查看表中所有的数据
select * from employee;
查看一下数据库编码的信息
show variables like 'character%';
| Variable_name | Value
+--------------------------+--------
| character_set_client | utf8
| character_set_connection | utf8
| character_set_database | utf8
| character_set_filesystem | binary
| character_set_results | utf8
| character_set_server | utf8
| character_set_system | utf8
修改客户端编码
set character_set_client=gbk;
set character_set_results=gbk;
insert employee values (5,'xiaolan','female','1992-11-22',1000,'2012-2-12','good girl');
insert employee (id,name) values (6,'aaa');
insert employee (id,name) values (7,'bbbb'),(8,'ccc'),(9,'dddd');
2 改 update 语句
将所有员工薪水修改为5000元。
update employee set salary=5000
将姓名为’zhangsan’的员工薪水修改为3000元。
update employee set salary=3000 where name='zhangsan';
将姓名为’wangwu’的员工薪水修改为4000元,gender改为female。
update employee set salary=4000,gender='female' where name='wangwu';
将xiaohong的薪水在原有基础上增加1000元。
update employee set salary=salary+1000 where name='xiaohong';
3 删 delete 语句
删除表中name为’zhangsan’的记录。
delete from employee where name='zhangsan';
删除表中所有记录。
delete from employee;
使用truncate删除表中记录。
truncate employee;
4 查 select 语句
创建一个student表 添加7条记录
create table student(
id int,
name varchar(20),
chinese float,
english float,
math float
);
insert into student(id,name,chinese,english,math) values(1,'张小明',89,78,90);
insert into student(id,name,chinese,english,math) values(2,'李进',67,53,95);
insert into student(id,name,chinese,english,math) values(3,'王五',87,78,77);
insert into student(id,name,chinese,english,math) values(4,'李一',88,98,92);
insert into student(id,name,chinese,english,math) values(5,'李来财',82,84,67);
insert into student(id,name,chinese,english,math) values(6,'张进宝',55,85,45);
insert into student(id,name,chinese,english,math) values(7,'黄蓉',75,65,30);
练习
查询表中所有学生的信息。
select * from student;
查询表中所有学生的姓名和对应的英语成绩。
select name,english from student;
过滤表中重复数据。
select distinct english from student;
在所有学生分数上加10分特长分。
select name,english+10,chinese+10,math+10 from student;
统计每个学生的总分。
select name,english+chinese+math from student;
使用别名表示学生分数。
select name,english+chinese+math as sum from student;
where子句用于加查询条件
查询姓名为李一的学生成绩
select * from student where name='李一';
查询英语成绩大于90分的同学
select * from student where english>90;
查询总分大于200分的所有同学
select *,english+chinese+math sum from student where english+math+chinese>200;
查询英语分数在 80-90之间的同学。
select * from student where english between 80 and 90;
查询数学分数为89,90,91的同学。
select * from student where math in(89,90,91);
查询所有姓李的学生成绩。
select * from student where name like '李%';
查询姓李的名字是两个字的学生
select * from student where name like '李_';
查询数学分>80,语文分>80的同学。
select * from student where math>80 and chinese>80;
查询英语>80或者总分>200的同学
select *,english+chinese+math sum from student where english>80 or english+chinese+math>200;
对数学成绩排序后输出。
select * from student order by math;
对总分排序后输出,然后再按从高到低的顺序输出
select *,english+chinese+math sum from student order by math+english+chinese desc;
对姓李的学生成绩排序输出 order by 从句要位于 where从句的后面
select *,english+chinese+math sum from student where name like '李%' order by math+english+chinese;
合计函数 count
insert into student(id,name,chinese,english,math) values(8,'郭靖',null,null,18);
统计一个班级共有多少学生?
select count(*) from student;
select count(chinese) from student;
统计数学成绩大于90的学生有多少个?
select count(*) from student where math>90;
统计总分大于240的人数有多少?
select count(*) from student where chinese+math+english>240;
合计函数 sum
统计一个班级数学总成绩?
select sum(math) from student;
统计一个班级语文、英语、数学各科的总成绩
select sum(math),sum(english),sum(chinese) from student;
统计一个班级语文、英语、数学的成绩总和
select sum(chinese+math+english) from student;
统计一个班级语文成绩平均分
select sum(chinese)/count(*) from student;
select sum(chinese)/count(chinese) from student;
合计函数 不考虑为null的数据
求一个班级语文平均分?
select avg(chinese) from student;
求一个班级总分平均分
select avg(chinese+math+english) from student;
求班级最高分和最低分
select max(chinese) from student;
订单表
create table orders(
id int,
product varchar(20),
price float
);
insert into orders(id,product,price) values(1,'电视',900);
insert into orders(id,product,price) values(2,'洗衣机',100);
insert into orders(id,product,price) values(3,'洗衣粉',90);
insert into orders(id,product,price) values(4,'桔子',9);
insert into orders(id,product,price) values(5,'洗衣粉',90);
select * from orders group by product;
select *,count(*) quantity,sum(price) from orders group by product;
查询单类商品购买价格高于100的
select *,count(*) quantity,sum(price) from orders group by product having sum(price)>100;