数据库操作
1、创建数据库
(1)语法:create database 数据库名称;
(2)编码:默认utf-8
2、删除数据库
(1)语法:drop database 数据库名称;
3、修改数据库
(1)语法:alter database 数据库名称 character set ‘gbk’ collate ‘校对规则/utf8’;
4、查询数据库
(1)显示数据库:show databases;
(2)切换数据库:use 数据库名称;
(3)查看数据库的创建的信息:show create database 数据库名称;
(4)查询当前正在使用的数据库:select database();
数据类型
1、字符串类型
(1)varchar:长度是可变的
(2)char:固定长度。 长度不够,空格补全
2、数值类型
(1)TINYINT
(2)MALLINT
(3)BIGINT
(4)INT
(5)FLOAT
(6)DOUBLE
3、日期类型
(1)DATE:只包含日期(年月日/2019-02-11)
(2)TIME:只包含时间(时分秒/13:14:15)
(3)DATETIME:包含日期和时间。如果插入数据的时候,字符值为空,字段的值就是空
(4)TIMESTAMP:包含日期和时间。如果插入数据值为空,获取当前系统时间,保存到字段中
4、大数据类型
(1)BLOB:字节(电影、MP3)
(2)TEXT:字符(文本的内容)
5、逻辑性类型—BIT
(1)在Java中是true或false
(2)在数据库bit类型是1或0
表操作
1、创建表
(1)语法
create table 表名称(
字段1 类型(长度) 约束,
字段2 类型(长度) 约束,
字段3 类型(长度) 约束
);
(2)注意
1. 创建表的时候,后面用小括号,后面用分号
2. 编写字段,字段与字段之间使用逗号,最后一个字段不能使用逗号
3. 如果声明字符串数据的类型,长度必须在指定范围内
4. 如果不指定数据长度,有默认值的。int类型的默认长度为11
2、删除表
(1)drop table 表名称;
3、修改表
(1)添加一个新字段
alter table 表名称 add 字段名称 数据类型(长度) 约束;
(2)修改字段的数据类型、长度、约束
alter table 表名称 modify 字段名称 数据类型(长度) 约束;
(3)删除某个字段
alter table 表名称 drop 字段名称;
(4)修改字段的名称
alter table 表名称 change 旧字段 新字段 数据类型(长度) 约束;
(5)修改表的名称
rename table 旧表名 to 新表名;
4、查看表结构
(1)查询所有的表结构,前提条件,先切换到数据库中
show tables;
(2)查询表结构的详细的信息
desc 表名称;
(3)查看表结构定义的信息
show create table 表名称;
5、练习
(1)在emp表中,增加一个age列
alter table emp add age int;
(2)修改job列,使其长度为30
alter table emp modify job varchar(30);
(3)删除sex列
alter table emp drop sex;
(4)把emp表名修改成uer表
rename table emp to user;
(5)列名name 修改成 username
alter table user change name username varchar(20);
数据操作
1、增加数据
(1)添加数据,使用insert关键字
1. insert into 表名称 (字段1,字段2,字段3) values (值1,值2,值3);指定字段
2. insert into 表名称 values (值1,值2,值3); 所有字段添
(2)注意事项
1. 插入的数据与字段数据的类型相同
2. 数据中的数据的列的位置和字段位置得相同
3. 字符串和日期类型的数据,必须要使用单引号括起来
2、删除数据
(1)删除合符条件的数据,使用的delete 关键字
delete from 表名称 where 条件;
(2)删除所有数据
delete from 表名称;
(3)删除所有数据,但保留表结构
truncate 表名称;
(4)delete与truncate的区别
1. delete删除的数据,可以恢复。而truncate删除,数据不可恢复。
2. delete删除,支持事务
3. truncate删除效率更快
3、修改数据
(1)修改数据,使用update关键字
1. update 表名称 set 字段1=值1,字段2=值2 where 条件;
2. update 表名称 set 字段=字段+1000 where 条件; 在基础上,增加1000
(2)注意事项
1. 如果没有where的关键字,说明修改的默认所有的记录
2. 如果有where的关键字,修改的是符合条件的记录
4、查询数据
(1)查询数据,使用select关键字
1.select * from 表名称; 默认查询所有的字段的数据
2. select 字段1,字段2 from 表名称; 查询指定的字段的数据
3. distinct 去除点重复的关键字
4. 可以对查询的列进行运算
5、取别名,使用的as关键字
(1)别名的真正用法,采用的多表的查询,为了区分每张表,而取的别名。
(2)as的关键字可以省略不写,中间需要使用空格
(3)常见的取别名的方法
1. select s.name,s.math from student s;
2. select s.name,.u.name from student s,user u;
6、练习
(1)查询stu表中所有学生的信息
select * from stu;
(2)查询表中所有学生的姓名和对应的英语成绩
select name,english from stu;
(3)过滤表中英语重复数据
select distinct english from stu;
(4)在所有学生分数上加10分
select name,math+10,english+10,chinese+10 from stu;
(5)统计每个学生的总分
select name,(math+english+chinese) from stu;
(6)使用别名表示学生总分数
select name,(math,english,chinese) as T from stu;
(7)查询姓名为美美的学生成绩
select * from stu where name = ‘美美’;
(8)查询英语成绩大于90分的同学
select name,enlish from stu where english > 90;
(9)查询总分大于200分的所有同学
select name,(math+english) from stu where (math+english) > 200;
Where条件的符号
1、数学符号
(1)— >、<、>=、<=、=、<>(不等于)
2、in 代表的范围
(1)在stu表中查询,条件是数学=88
select * from stu where math = 88;
(2)在stu表中查询,数学的成绩是18,88,90
select * from stu where math in (18,88,90);
3、like 模糊查询
(1)“_”占位符号,补全一个
select * from stu where name like ‘张_’;
(2)“%”占位符,补全多个
select * from stu where name like ‘张%’;
4、其他符号
(1)and ...与...
(2)or ...或...
(3)not ...非
(4)between ... and 在...中间
5、练习
(1)查询英语分数在60-90之间的同学
1. select name,english from stu where english >60 and <90;
2. select name,english from stu where english between 60 and 90;
(2)查询数学分数为89,90,91的同学
select name,math from stu where math in (88,90,91);
(3)查询所有姓邓的学生成绩
select * from stu where name like ‘邓%’;
(4)查询数学分数>80与语文>80的同学
select * from stu where math > 80 and chinese > 80;
(5)查询数学分数>80或语文>80的同学
select * from stu where math > 80 or chinese > 80;
Order by查询
1、order by排序分类
(1)asc 代表升序(默认)
(2)desc 代表降序
2、注意事项
(1)order by放在select语句的末尾
3、练习
(1)对数学成绩排序后,输出
1. select name,math from stu order by math asc;
2. select name,math from stu order by math desc;
(2)对总分排序按从高到低的排序输出
1. select name,(math+english) from stu order by (math+english) desc;
2. select name,(math+english) as T from stu order T desc;
聚合函数
1、求数量函数
(1)关键字:count()
(2)语法:select count(*)/count(列名) from 表名称;
2、求和函数
(1)关键字:sum()
(2)语法:select sum(*)/sum(列名) from 表名称;
3、求平均值函数
(1)关键字:avg()
(2)语法:select avg(*)/avg(列名) from 表名称;
4、求最大值函数
(1)关键字:max()
(2)语法:select max(*)/max(列名) from 表名称;
5、求最小值函数
(1)关键字:min()
(2)语法:select min(*)/min(列名) from 表名称;
6、练习
(1)统计一个班级有多少学生
1. select count(*) from stu;
2. select count(id) from stu;
(2)统计数学成绩大于90的学生有多少个?
select count(math) from stu where math > 90;
(3)统计一个班级数学总成绩
select sum(math) from stu;
(4)统计一个班语文、数学各科的总和
1. select sum(chinese)+sum(math) from stu;
2. select sum(chinese+math) from stu;
(5)统计一个班级语文成绩的平均分
select sum(chinese)/count(*) from stu;
(6)求一个班级语文平均分
select avg(chinese) from stu;
(7)求一个班级总分的平均分
select avg(math+english+chinese) from stu;
(8)求班级最高分
select max(math+english+chinese) from stu;
(9)求班级最低分
select min(math+english+chinese) from stu;
分组查询
1、分组查询
(1)关键字:group by
(2)语法:select * from 表名 order by 字段名;
2、having与where的区别
(1)having是在分组后,对数据进行过滤。where则是在分组前,对数据过滤。
(2)having后面可以使用聚合函数,where后面则不可以使用聚合函数。
3、练习
(1)对orders订单表商品归类后,显示每一类商品的总价?
select product,sum(price) from orders group by product;
(2)查询购买几类商品,并且每类商品总价大于100的商品?
select product,sum(price) from orders group by product having sum(price) > 100;
(3)查询购买几类商品,并且商品的价格需要大于100,每类总价大于100的商品?
select product,sum(price) from orders where price > 100 group by product having sum(price) > 100;
约束
1、约束作用
(1)保证数据的完整性
2、约束分类
(1)主键约束:代表记录的唯一标识
1.关键字:primary key 通过该关键字声明某一列为主键
2. 唯一 值就不能相同
3. 非空 值也不能为空
4. 被引用 和外键一起来使用
5. 示例
创建新的数据:
create table person(
id int primary key,
username varchar(20)
);
添加数据:
insert into person values (1,’美美’);
insert into person values (2,’小花’);
(2)唯一约束
声明字段值是唯一的,关键字 unique
(3)非空约束
声明字段的值是不能为空的,关键字 not null
3、主键的自动增长
(1)特点
可以把主键的值交给数据库维护
(2)关键字
auto_increment
(3)注意事项
自动增长只能使用int和bigint类型
(4)示例
创建表:
create table person(
id int primary key auto_increment,
username varchar(20)
);
添加数据:
insert into person values (null,’美美’);
insert into person values (null,’小花’);
4、外键约束
(1)作用
保证表结构中数据的完整完整性
(2)关键字
foreign key
(3)格式
foreign key (外键字段名) references 主表 (主键字段名)
(4)示例
创建部门表:
create table department(
d_id int primary key auto_increment,
d_name varchar(20)
);
创建员工表:
create table employee(
e_id int primary key auto_increment,
e_name varchar(20),
salary double,
dno int,
foreign key (dno) references department (d_id)
);
向部门表添加数据:
insert into department values (null,’研发部’);
insert into department values (null,’测试部’);
向员工表添加数据,员工所属部门:
insert into employee values (null,’张三’,9000,2);
insert into employee values (null,’李四’,10000,1);
多表设计
1、对一(不常用)
(1)特点
主键唯一对应
2、对多
(1)场景
部门与员工、用户与订单
(2)特点
主表(一方)、从表(多方)
(3)原则
在多方表中添加字段,把字段作为外键,指向一方的主键
(4)格式
foreign key (外键字段名) references 主表 (主键字段名)
(5)视图
外键:foreign key (uid) references 用户表名称 (id)
3、多对多
(1)场景
订单与商品、用户与角色
(2)原则
需要一张中间表,建立两张表的联系
(3)视图
外键1:foreign key (uid) references 用户表名称 (id)
外键2:foreign key (rid) references 角色表名称 (id)
多表查询
1、内链接
(1)前提条件
两张表必须有联系,通过外键关联
(2)普通内连接
1. 关键字:inner join....on
2. 语法:... jnner jion ... on 条件;
(3)注意事项
1. 在inner join 关键字之前写 表1
2. 在 inner join 关键字之后写 表2
3. on 后面写条件
(4)示例
select * from dept inner join emp on dept.did = emp.dno;
2、隐式链接(常用)
(1)语法
1. select * from 表1,表2 where 表1.字段 = 表2.字段;
2. 示例:select * from dept,emp where dept.did = emp.dno;
(2)取别名
select * from dept d,emp e where d.did = e.dno;
(3)指定字段
select d.dname,e.ename,e.salary from dept d,emp e where d.did = e.dno;
3、外连接
(1)左外链接
1. 关键字:left jion ... on
2.语法:select * from 表1 left jion 表2 on 条件;
2.示例:select * dept left join emp on dept.did = emp.dno;
(2)右外连接
1.关键字:right jion ... on
2.语法:select * from 表1 left jion 表2 on 条件;
2.示例:select * dept left join emp on dept.did = emp.dno;
4、内连接与外链接的区别
(1)内连接查询,查询的结果,是主外键关联的数据
(2)左外链接查询,查询的结果,左表中所有的数据+两张表主外键关联的数据
(3)右外链接查询,查询的结果,右表中所有的数据+两张表主外键关联的数据
子查询
1、定义
(1)把查询语句的结果作为另一条查询语句的条件
2、示例
(1)查询出英语成绩大于英语平均分的同学
1. 先计算出英语的平均分
select avg(english) from stu;
2. 再编写select语句
select name,english from stu where english > (select avg(english) from stu);