mysql安装
目录:英文,软件和数据文件放在一起
端口号:3306
用户名:root 密码:root
服务名称:MySql
启动服务:
1.管理——服务与应用程序
2.cmd窗口 net start mysql
Net stop mysql
mysql的登录:
在cmd窗口中
1.mysql -uroot -p密码
2.Mysql -uroot -p
输入密码:
密码的重置:
前提 :保证mysql服务关闭
步骤:
1.在cmd窗口中输入 mysqld --console --skip-grant-tables
2.再次打开一个cmd窗口,输入mysql -uroot
3.切换数据库 use mysql
4.修改密码 update user set password=password(‘新密码’) where user=’root’;
5.关闭两个cmd窗口,启动mysql服务
分类:
DDL:数据库定义语言
操作对象:数据库和表
关键词:create alter drop
操作数据库:
创建(☆):
格式:create database 数据库名称;
如:create database mjl;
修改(了解):
就是修改数据库的字符集或者校对规则
格式:alter database mjl character set utf8;
删除(理解):
格式:drop database 数据库名称;
如:drop database mjl;
常用命令:
查看所有的数据库:show databases;
进入或者切换数据库:use 数据库名称;
查看建库语句:show create database 数据库名;
操作表:
创建表:
格式:create table 表名(字段描述1,字段描述2....字段描述n);
字段描述的格式:字段名 字段类型 [约束]
如:create table user(id int,name varchar(20));
修改表:
格式:alter table 表名 ...
修改表名:
格式:alter table 旧表名 rename to 新表名;
添加字段:
格式:alter table 表名 add[column] 新字段描述;
如:alter table user add password varchar(20);
修改字段类型:
格式:alter table 表名 modify 新字段描述;
如:alter table user modify password int;
修改字段名称:
格式:alter table 表名 change 旧字段名 新字段描述;
如:alter table user change password pwd varchar(20);
删除字段:
格式:alter table 表名 drop 字段名;
如:alter table user drop pwd;
删除表:
格式:drop table 表名;
常用命令:
查看当前数据库下所有表:show tables;
查看表结构:desc 表名;
查看建表语句:show create table 表名;
DML:数据库操作语言
操作对象:记录
关键词:insert update delete
插入记录:
格式1:
Insert into 表名 values(值1[,值2...]);
例如:
insert into user values(1,’zhangsan’);
注意:
默认插入全部的字段,values里面的值要和建表语句中字段顺序及类型保持一致。
Int类型可以不使用引号,varchar类型必须使用引号
格式2:
Insert into 表名(字段1[,字段2...]) values(值1[,值2...]);
例如:
Insert into user(name,id) values(‘李四’,2);
注意:
插入指定的字段values里面的值要和前面的字段顺序及类型保持一致。
插入记录乱码问题:
方法1:临时修改
Set names gbk;
方法2:永久修改
安装目录下/my.ini文件
port=3306
[mysql]
default-character-set=utf8
把utf8改成gbk,然后重启服务
修改记录:
格式:
Update 表名 set 字段名1=值1[,字段名2=值2...] where 条件;
例如:
Update user set id=3 where name=’李四’;
Update user set id=4,name=’赵六’ where id=3;
删除记录:
格式:
Delete from 表名 [where 条件];
例如:
Delete from user; -- 清空表
Delete from user where id=4;
扩展:
在开发中添加一个字段用来标识该条记录是否被删除。例如 isdel
DQL:数据库查询语言
关键词:select
查询语句:select * from 表名;
基本查询:
1.查询出所有商品信息(查询全部)
Select * from products;
Select id,name,price,pnum,category from products;(标准)
2.查询出所有商品的名称,价格,类别及数量信息(查询部分)
Select name,price,category,pnum from products;
3.查询出所有的商品类别
去重复操作:distinct
Select distinct category from products;
4.查询出所有商品的名称及价格,将所有商品价格加10
在查询的结果之上进行运算。
Select name,price+10 from products;
注意:
NULL和任意数据进行运算结果都是NULL值。可以通过ifnull(字段名,默认是)
例如:ifnull(price,0) 若价格为null,则按0处理
Select name,ifnull(price,0)+10 from products;
5.查询出每一个商品的总价及名称
Select name,pnum*price from products;
字段起别名:字段 [as] 别名
select name,pnum*price as '总价' from products;
条件查询:
格式:select * from 表名 where 条件;
Select 字段1[,字段2...] from 表名 where 条件;
1.查询所有计算机类商品信息
-- 条件写法1:支持关系运算符 > >= < <= !=(<>)
-- 格式:字段名 运算符 值;
Select * from products where category=’计算机’;
Select * from products where category!=’计算机’;
2.查询出商品价格大于90的商品信息
Select * from products where price >90;
3.查询出商品总价大于10000的商品信息
Select * from products where price*pnum>10000;
4.查询出价格在100-200之间的商品信息
-- 条件写法2:支持逻辑操作符 and or not
-- 格式:表达式1 and|or 表达式2
Not 表达式
Select * from products where price >=100 and price <=200;
-- 条件写法 3:支持between and 操作
-- 格式:字段 between 较小值and 较大值
Select * from products where price between 100 and 200;
5.查询出商品价格是65,100或190的商品信息
Select * from products where price =65 or price =100 or price =190;
-- 条件写法4:支持 in 操作
-- 格式:字段 in (值1,值2...)
Select * from products where price in (65,100,190);
6.查询出商品的名字中包含java的商品信息
-- 条件写法5:匹配操作 like
-- 格式:字段 like 匹配规则;
-- 匹配内容
Like ‘龙’ 值为龙
Like ‘%龙’ 值以龙结尾
Like ‘龙%’ 值以龙开头
Like ‘%龙%’ 值包含龙
-- 匹配个数
Like ‘_’ 值为一个字符
Like ‘__” 值为2个字符
Select * from products where name like ‘%java%’;
7.查询出书名是两个字的商品信息
Select * from products where name like ‘__’;
(很少用,一汉字三字节)Select * from products where length(name)=6;
8.查询出商品价格不为null的商品信息
-- 条件写法6:Null操作
-- 格式1:is null
Select * from products where price is null;
--格式2:is not null
Select * from products where price is not null;
排序:order by
格式1:
Select * from 表名 [where 条件] order by 排序字段 排序方式;
格式2:
Select * from 表名 [where 条件] order by 排序字段1 排序方式,排序字段2,排序方式;
排序方式:
升序:asc(默认)
降序:desc
1.查询出所有商品,并根据价格进行升序排列
Select * from products order by price asc;
2.查询出所有商品,根据数量进行升序排列。如果数量相同,根据价格进行 降序排列
Select * from products order by pnum asc,price desc;
聚集函数:
它是对一列的值进行计算,然后返回一个单一的值;另外聚集函数会忽略空值
Sum(字段):求和
Count(字段):计数
Max(字段):最大值
Min(字段):最小值
Avg(字段):平均值
格式:
Select 聚集函数 from 表名 [where 条件];
1.统计商品表中共有多少条记录
Select count(id) from products;
开发中:
Select count(*) from products;
2.统计商品表中价格大于50的有多少条记录
Select count(*) from products where price >50;
3.统计有多少商品
Select sum(pnum) from products;
4.统计所有商品的总价值
Select sum(price*pnum) from products;
5.统计所有商品的平均价格
Select sum(price*pnum)/sum(pnum) from products;
--round(值,保留位数)
Select round(sum(price*pnum)/sum(pnum),2) from products;
6.统计出记录中pnum的平均值
Select avg(pnum) from products;
7.统计出商品表中price最大值
Select max(price) from products;
8.统计出商品表中price最小值
Select min(price) from products;
9.统计出生活百科类图书的总数量
Select sum(pnum) from products where category=’生活百科’;
分组:group by
格式1:
Select 分组字段[,集合函数] from 表名 [where 条件] group by分组字段;
1.对商品分类别统计,求出每一种类商品的总数量
Select category,sum(pnum) from products group by category;
2.对商品分类别统计,求出每一种类商品的总数量,数量要大于100
Select category,sum(pnum) from products where sum(pnum)>100 group by category; ×where条件后不能跟聚集函数
格式2:
Select 分组字段[,集合函数] from 表名 [where 条件] group by分组字段 sum(pnum)>100;
where和having的区别:
1.where后面不能使用聚集函数,having可以
2.where是对分组之前的数据进行筛选,having是对分组之后的数据进行筛选。
DQL的使用小结:
Select 分组字段[,聚集函数] from 表名 where 条件 group by 分组字段 having 条件 order by 排序字段 asc|desc;
注意:order by永远放在最后
执行顺序:
1.先确定哪张表 from
2.确定是否有条件 where
3.若需要分组,使用group by
4.若需要对分组后的数据进行筛选,执行having
5.确定要显示哪些字段,select
6.确定字段的显示方法,执行order by
DTL:数据库事物语言
DCL:数据库控制语言
字段类型:
Java mysql
Byte tinyint
Short smallint
Int int☆
Long bigint
Float float
Double double double(m,d) m代表的数字的长度,d代表小数位个数
Double(5,2) 存放的最大值为999.99
Char char(n) char(5):长度不可变 如:存abc,在数据库中“abc ”
String varchar(n)☆ varchar(5):长度可变 如:存abc,“abc”
Boolean
Java.sql.Date date:日期
Java.sql.Time time:时间
Java.sql.TimeStamp timestamp:时间戳
dateTime:日期和时间
约束:保证是数据的有效性和完整性
在mysql中有主键约束(primary key),唯一约束(unique),非空约束(not null),外键约束(foreign key)
主键约束(primary key):确定字段是该条记录唯一标识。
注意:
1.主键可以是一个字段,也可以是多个字段
2.一张表只能有一个主键
特点:
被主键约束修饰过的字段唯一非空
使用:
格式1:在声明字段的同时,添加主键约束
Create table pk01(
id int primary key,
name varchar(20)
);
insert into pk01 values(1,'tom'); --成功
insert into pk01 values(1,'tom');--错误,已经存在,保证唯一性
insert into pk01 values(null,'tom');--错误,非空
扩展:
一般情况下,使用id作为主键,id没有任何实际意义。
格式2:在声明字段之后,在约束区域添加主键约束:[constraint] primary key(字段1[,字段2...]);联合主键,都不能为空
Create table pk02(
id int
name varchar(20),
primary key(id,name)
);
Insert into pk02 values(1,’tom’);
Insert into pk02 values(1,’jack’);
格式3:创建完表之后,通过修改表结构添加主键约束:
Alter table 表名 add primary key(字段1[,字段2...]);
Create table pk03(id int,name varchar(20));
Alter table pk03 add primary key(name);
使用小结:
开发中最常使用的是第一种,只能在一个字段上添加主键约束。
若需要在多个字段上添加主键约束(联合主键),请使用第二种或第三种方 式。
唯一约束(unique)
特点:被修饰的字段唯一,对Null值不起作用
使用:
格式1:在声明字段的同时,添加唯一约束
写法:字段名 字段类型 unique
Create table un01(
id int unique,
name varchar(20)
);
Insert into un01 values(null,’tom’); --多次创建成功,对null不起作用
格式2:在声明字段之后,在约束区域添加唯一约束
写法:[constraint] unique(字段1[,字段2...])
Create table un02(
id int,name varchar(20),
unique(id,name)
);--联合唯一约束
格式3:在创建完表之后,通过修改表结构添加唯一约束
Alter table 表名 add unique(字段1[,字段2]));
Alter table un03 add unique(id);
使用小结:
在开发中一般不添加
在项目最后上线的时候通过第三种方式添加唯一约束。
非空约束(not null)
特点:被修饰的字段非空
格式:在声明字段的同时添加非空约束
Create table nn
id int not null,
name varchar(20) not null
);
三个小知识(必须掌握)
auto_increment(开发)
作用:被它修饰过的字段可以自增
格式:
字段名称 字段类型 primary key auto_increment
特点:
1.被修饰的字段类型可以自增,一般是int
2.被修饰的字段必须是一个key,一般是primary key
create table ai01(
id varchar(20) primary key auto_increment,
name varchar(20)
); -- 错误 Incorrect column specifier for column 'id'
create table ai02(
id int auto_increment,
name varchar(20)
); -- 错误 it must be defined as a key
create table ai03(
id int primary key auto_increment,
name varchar(20)
);
使用的注意事项:
插入值得时候可以是指定的值
插入值得时候把它指定为Null
插入值的时候忽略掉它也可以。
insert into ai03 values(33,'sansan');
insert into ai03 values(null,'xiaosan');
insert into ai03(name) values('xiaosi');
truncate(面试)
作用:清空表,干掉表结构,创建一个新表。
格式:truncate table 表名;
delete from 和truncate区别:
1.truncate 属于DDL语句,delete 属于DML语句
2.truncate 是干掉表结构,重新创建一个新表。delete 是逐条删除记录
default(看懂)
作用:添加默认值
格式:字段名称 字段类型 (约束) default 默认值
例如:
create table de(
id int primary key auto_increment,
name varchar(20) default '张三丰'
);
insert into de values(null,'sansan');
insert into de values(null,null); -- null也算是赋值
insert into de(id) values(null);
结论:在不明确给定值得时候采用默认值。
数据库的备份与还原(了解)
备份:
格式:在cmd窗口下输入
mysqldump -uroot -p密码 要备份的数据库>文件磁盘路径
例如:
mysqldump -uroot -pmoujinling321 mjl>d:/mjl.sql
还原:前提是必须手动创建目的地数据库
格式1:在cmd窗口下输入
mysql -uroot -p密码 目的地数据库<文件磁盘路径
例如:
mysql -uroot -pmoujinling321 u34<d:/mjl.sql
格式2:必须在目的地数据库中执行命令
source 文件磁盘路径
例如:
source d:/mjl.sql
常用的命令:
select database(); -- 查看当前所在的数据库
多表的设计与实现☆
关系型数据库:存放实体与实体之间的关系。
用户<拥有> 订单<包含> 商品
一对多
设计:
在实际开发中,我们称一方(用户表)为一表或者主表,称多方(订单表)为多表或者从表。
然后在多表的一方添加一个字段,字段名称自定义(一般使用主表的名称_id,字段的类型一般和主表的id保持一致)。该字段称之为外键。
实现:
user(用户表)
create table user(
id int primary key auto_increment,
username varchar(20)
);
orders(订单表)
create table orders(
id int primary key auto_increment,
price double,
user_id int
);
insert into user values(null,'强哥'),(null,'聪哥');
insert into orders values(null,1314,1);
insert into orders values(null,1315,2);
insert into orders values(null,305,2);
为了保证数据的有效性和完整性,必须给表添加外键约束。
在多表的一方添加外键约束。
格式:通过修改表结构添加外键约束。
alter table 表名 add [constraint [外键名称]] foreign key(多表的外键) references 主表(主键);
例如:
alter table orders add foreign key(user_id) references user(id);
注意:
主表中不能删除从表中已引用的数据
从表中不能添加主表中不存在的数据
外键可以为null
多对多
设计:
在开发中,一般会引入一个中间表,中间表中的字段一般为两表的主键。
通过中间表,就可以把多对多的关系拆分成两个一对多的关系。
一般我们会把中间表的两个字段设置成联合主键。
实现:
list(商品表)
create table list(
id int primary key auto_increment,
price double
);
orders_pro(中间表)
create table orders_pro(
oid int,
pid int,
primary key(oid,lid)
);
insert into list values(null,999),(null,305),(null,10);
insert into orders_list values(1,1),(1,2),(1,3),(2,1),(2,2),(2,3),(3,2);
为了保证数据的有效性和完整性,必须给表添加外键约束。(在中间表上添加)
在中间表上添加两个外键约束
格式:
alter table 表名 add [constraint [外键名称]] foreign key(多表的外键) references 主表(主表的id);
例如:
alter table orders_list add foreign key(oid) references orders(id);
alter table orders_list add foreign key(lid) references list(id);
一对一
设计:
1.若一张表中的字段很少,二表合二为一
2.根据需求,一般在不常用的表上添加外键约束,实际上是把主键设置成外键即可。
实现:
person(人员表)
create table person(
id int primary key auto_increment,
username varchar(20)
);
idcard(身份证表)
create table idcard(
id int primary key auto_increment,
idno varchar(25)
);
设置外键:
alter table idcard add foreign key(id) references person(id);
多表的查询☆
环境搭建:
-- 用户表
create table `user` (
`id` int auto_increment primary key,
`username` varchar(50) -- 用户姓名
);
-- 订单表(orders)
create table `orders` (
`id` int auto_increment primary key,
`price` double,
`user_id` int
);
-- 给订单表添加外键约束
alter table orders add constraint user_fk foreign key (user_id) references user(id);
-- 向user表中添加数据
insert into user values(1,'张三');
insert into user values(2,'李四');
insert into user values(3,'王五');
insert into user values(4,'赵六');
-- 向orders 表中插入数据
insert into orders values(1,1314,1);
insert into orders values(2,1314,1);
insert into orders values(3,15,2);
insert into orders values(4,315,4);
insert into orders values(5,1014,null);
1.查询用户的订单,没有订单的用户不显示
2.查询所有用户的订单详情
3.查询所有订单的用户详情
笛卡尔积:(了解)
格式:
select a.*,b.* from a,b
隐式内连接:(1)
格式:
select a.*,b.* from a,b where 连接条件;
例如:
select user.*,orders.* from user,orders where user.id=orders.user_id;
内连接:(1)
格式:
select a.*,b.* from a [inner] join b on 连接条件;
注意:
内连接的查询结果和隐式内连接的查询结果一样。
例如:
select user.*,orders.* from user inner join orders on user.id=orders.user_id;
左外连接:
格式:
select a.*,b.* from a left join b on 连接条件;
意思:
以Left左边的表为主,显示它的所有记录,根据连接条件查询b表。若符合条件,则显示该条记录。否则以Null值显示。
例如:
select user.*,orders.* from user left join orders on user.id=orders.user_id;
右外连接:
格式:
select a.*,b.* from a right join b on 连接条件;
注意:
左外连接和右外连接可以等价。
意思:
以right右边的表为主,显示它的所有记录,根据连接条件查询a表。若符合条件,则显示该条记录。否则以Null值显示。
例如:
select user.*,orders.* from user right join orders on user.id=orders.user_id;
子查询:
一个查询的条件是另一个查询的结果。
1.查看用户为张三的订单详情
select id from user where username='张三'; --id=1
select * from orders where user_id=1;
合二为一:
select * from orders where user_id=(select id from user where username='张三');
2.查询出订单的价格大于300的所有用户信息。
select user_id from orders where price>300; -- user_id=1,1,4,null
select * from user where id in(1,1,4,null);
合二为一:
select * from user where id in (select user_id from orders where price>300);
3.查询订单价格大于300的订单信息及相关用户的信息。
方式1:select user.*,orders.* from user,orders where user.id=orders.user_id and orders.price>300;
方式2:
首先,查询订单价格大于300的订单信息。
select * from orders where price>300;
然后,通过上一条语句结果和user表进行关联查询即可
select a.*,b.* from a,b where 条件语句;
例如:
select u.*,b.* from user u,(select * from orders where price >300) as b where u.id=b.user_id;
单列多行子查询:
可以使用in,any,all操作
>any:大于子查询中的最小值
>all:大于子查询中的最大值
<any:小于子查询中的最大值
<all:小于子查询中的最小值
!=any或<>any:不等于子查询中的任意值
=any:等于子查询中的任意值
练习:
-- 1.返回拥有员工的部门名、部门号。(dept,emp)
select distinct d.dname,d.deptno from dept d,emp e where d.deptno=e.deptno;
-- 2.工资多于ward的员工信息。
select sal from emp where ename='ward';
select * from emp where sal>(select sal from emp where ename='ward');
-- 3.返回员工和其所属领导的姓名。(自连接)
select e.ename as '员工',l.ename as '领导' from emp e,emp l where e.mgr=l.empno; -- 一张表拆成两张
-- 4.返回雇员的雇佣日期早于其领导雇佣日期的员工及其领导姓名。(在日期类型可以直接比较)
select e.ename '员工' ,l.ename '领导' from emp e,emp l where e.mgr=l.empno and e.hiredate<l.hiredate;
-- 5.返回员工姓名及其所在的部门名称。
select deptno from emp;
select e.ename,d.dname from emp e,(select deptno from emp) d where e.deptno=d.deptno;
-- 6.返回从事clerk工作的员工姓名和所在部门名称
select job from emp where ename='clark';
select e.ename,d.dname from emp e,dept d where e.job='clerk' and e.deptno=d.deptno;
-- 7.返回部门号及其本部门的最低工资。
select deptno,min(sal) from emp group by deptno;
-- 8.返回销售部(sales)所有员工的姓名。
select deptno from dept where dname='sales';
select ename from emp where deptno=(select deptno from dept where dname='sales');
-- 9.返回工资多于平均工资的员工。
select avg(sal) from emp;
select ename from emp where sal>(select avg(sal) from emp);
-- 10.返回与SCOTT从事相同工作的员工。
select job from emp where ename='scott';
select * from emp where job=(select job from emp where ename='scott');
-- 11.返回与30部门员工工资相同的员工姓名与工资。
select sal from emp where deptno=30;
select ename,sal from emp where sal in(select sal from emp where deptno=30);
-- 12.返回工资高于30部门所有员工工资水平的员工信息。
select max(sal) from emp where deptno=30;
select * from emp where sal>(select max(sal) from emp where deptno=30);
select * from emp where sal>all(select sal from emp where deptno=30);
!!!!!!!!!!!!!!!!!!
-- 13.返回部门号、部门名、部门所在位置及其每个部门的员工总数。count(*)=null被忽略了!!
select deptno,count(*) from emp group by deptno;
select d.*,temp.total from dept d,(select deptno,count(*) total from emp group by deptno) temp where d.deptno=temp.deptno;
select d.*,temp.total from dept d left join (select deptno,count(*) total from emp group by deptno) temp on deptno=temp.deptno;
-- 14.返回员工的姓名、所在部门名及其工资。
select deptno,ename,sal from emp;
select d.dname,e.ename,e.sal from dept d,(select deptno,ename,sal from emp) e where d.deptno=e.deptno;
-- 15.返回员工的详细信息。(包括部门名)
select deptno from emp;
select e.*,d.dname from emp e,dept d where e.deptno=d.deptno;
select e.*,d.dname from emp e left join dept d on d.deptno=e.deptno;
-- 16.返回员工工作及其从事此工作的最低工资。
select job from emp;
select min(sal) from emp group by job;
-- 17.返回不同部门经理的最低工资。
select deptno,min(sal) from emp where job='manager' group by deptno;
-- 18.计算出员工的年薪,并且以年薪排序。
select sal*12+ifnull(comm,0) as YearSal from emp order by YearSal asc;
-- 19.返回工资处于第四级别的员工的姓名。
select losal,hisal from salgrade where grade=4;
select e.ename,e.sal from emp e,salgrade s where s.grade=4 and e.sal between s.losal and s.hisal;
-- 20.返回工资为二等级的职员名字、部门所在地、和二等级的最低工资和最高工资
select losal,hisal from salgrade where grade=2;
select e.ename,d.loc,s.losal,s.hisal from emp e,dept d,salgrade s where s.grade=2 and e.deptno=d.deptno and e.sal between s.losal and s.hisal;
select e.ename,d.loc,s.losal,s.hisal from emp e
join salgrade s
on s.grade=2 and e.sal between s.losal and s.hisal
join dept d
on e.deptno=d.deptno;
-- 21.工资等级多于smith的员工信息。
select hisal from salgrade s where (select sal from emp where ename='smith') between s.losal and s.hisal;
select * from emp where sal>(select hisal from salgrade s where (select sal from emp where ename='smith') between s.losal and s.hisal);
转载标明出处:https://www.cnblogs.com/syj1993/p/8410419.html
DDL:数据库定义语言
操作对象:数据库和表
关键词:create alter drop
操作数据库:
创建(☆):
格式:create database 数据库名称;
如:create database mjl;
修改(了解):
就是修改数据库的字符集或者校对规则
格式:alter database mjl character set utf8;
删除(理解):
格式:drop database 数据库名称;
如:drop database mjl;
常用命令:
查看所有的数据库:show databases;
进入或者切换数据库:use 数据库名称;
查看建库语句:show create database 数据库名;
操作表:
创建表:
格式:create table 表名(字段描述1,字段描述2....字段描述n);
字段描述的格式:字段名 字段类型 [约束]
如:create table user(id int,name varchar(20));
修改表:
格式:alter table 表名 ...
修改表名:
格式:alter table 旧表名 rename to 新表名;
添加字段:
格式:alter table 表名 add[column] 新字段描述;
如:alter table user add password varchar(20);
修改字段类型:
格式:alter table 表名 modify 新字段描述;
如:alter table user modify password int;
修改字段名称:
格式:alter table 表名 change 旧字段名 新字段描述;
如:alter table user change password pwd varchar(20);
删除字段:
格式:alter table 表名 drop 字段名;
如:alter table user drop pwd;
删除表:
格式:drop table 表名;
常用命令:
查看当前数据库下所有表:show tables;
查看表结构:desc 表名;
查看建表语句:show create table 表名;
DML:数据库操作语言
操作对象:记录
关键词:insert update delete
插入记录:
格式1:
Insert into 表名 values(值1[,值2...]);
例如:
insert into user values(1,’zhangsan’);
注意:
默认插入全部的字段,values里面的值要和建表语句中字段顺序及类型保持一致。
Int类型可以不使用引号,varchar类型必须使用引号
格式2:
Insert into 表名(字段1[,字段2...]) values(值1[,值2...]);
例如:
Insert into user(name,id) values(‘李四’,2);
注意:
插入指定的字段values里面的值要和前面的字段顺序及类型保持一致。
插入记录乱码问题:
方法1:临时修改
Set names gbk;
方法2:永久修改
安装目录下/my.ini文件
port=3306
[mysql]
default-character-set=utf8
把utf8改成gbk,然后重启服务
修改记录:
格式:
Update 表名 set 字段名1=值1[,字段名2=值2...] where 条件;
例如:
Update user set id=3 where name=’李四’;
Update user set id=4,name=’赵六’ where id=3;
删除记录:
格式:
Delete from 表名 [where 条件];
例如:
Delete from user; -- 清空表
Delete from user where id=4;
扩展:
在开发中添加一个字段用来标识该条记录是否被删除。例如 isdel
DQL:数据库查询语言
关键词:select
查询语句:select * from 表名;
基本查询:
1.查询出所有商品信息(查询全部)
Select * from products;
Select id,name,price,pnum,category from products;(标准)
2.查询出所有商品的名称,价格,类别及数量信息(查询部分)
Select name,price,category,pnum from products;
3.查询出所有的商品类别
去重复操作:distinct
Select distinct category from products;
4.查询出所有商品的名称及价格,将所有商品价格加10
在查询的结果之上进行运算。
Select name,price+10 from products;
注意:
NULL和任意数据进行运算结果都是NULL值。可以通过ifnull(字段名,默认是)
例如:ifnull(price,0) 若价格为null,则按0处理
Select name,ifnull(price,0)+10 from products;
5.查询出每一个商品的总价及名称
Select name,pnum*price from products;
字段起别名:字段 [as] 别名
select name,pnum*price as '总价' from products;
条件查询:
格式:select * from 表名 where 条件;
Select 字段1[,字段2...] from 表名 where 条件;
1.查询所有计算机类商品信息
-- 条件写法1:支持关系运算符 > >= < <= !=(<>)
-- 格式:字段名 运算符 值;
Select * from products where category=’计算机’;
Select * from products where category!=’计算机’;
2.查询出商品价格大于90的商品信息
Select * from products where price >90;
3.查询出商品总价大于10000的商品信息
Select * from products where price*pnum>10000;
4.查询出价格在100-200之间的商品信息
-- 条件写法2:支持逻辑操作符 and or not
-- 格式:表达式1 and|or 表达式2
Not 表达式
Select * from products where price >=100 and price <=200;
-- 条件写法 3:支持between and 操作
-- 格式:字段 between 较小值and 较大值
Select * from products where price between 100 and 200;
5.查询出商品价格是65,100或190的商品信息
Select * from products where price =65 or price =100 or price =190;
-- 条件写法4:支持 in 操作
-- 格式:字段 in (值1,值2...)
Select * from products where price in (65,100,190);
6.查询出商品的名字中包含java的商品信息
-- 条件写法5:匹配操作 like
-- 格式:字段 like 匹配规则;
-- 匹配内容
Like ‘龙’ 值为龙
Like ‘%龙’ 值以龙结尾
Like ‘龙%’ 值以龙开头
Like ‘%龙%’ 值包含龙
-- 匹配个数
Like ‘_’ 值为一个字符
Like ‘__” 值为2个字符
Select * from products where name like ‘%java%’;
7.查询出书名是两个字的商品信息
Select * from products where name like ‘__’;
(很少用,一汉字三字节)Select * from products where length(name)=6;
8.查询出商品价格不为null的商品信息
-- 条件写法6:Null操作
-- 格式1:is null
Select * from products where price is null;
--格式2:is not null
Select * from products where price is not null;
排序:order by
格式1:
Select * from 表名 [where 条件] order by 排序字段 排序方式;
格式2:
Select * from 表名 [where 条件] order by 排序字段1 排序方式,排序字段2,排序方式;
排序方式:
升序:asc(默认)
降序:desc
1.查询出所有商品,并根据价格进行升序排列
Select * from products order by price asc;
2.查询出所有商品,根据数量进行升序排列。如果数量相同,根据价格进行 降序排列
Select * from products order by pnum asc,price desc;
聚集函数:
它是对一列的值进行计算,然后返回一个单一的值;另外聚集函数会忽略空值
Sum(字段):求和
Count(字段):计数
Max(字段):最大值
Min(字段):最小值
Avg(字段):平均值
格式:
Select 聚集函数 from 表名 [where 条件];
1.统计商品表中共有多少条记录
Select count(id) from products;
开发中:
Select count(*) from products;
2.统计商品表中价格大于50的有多少条记录
Select count(*) from products where price >50;
3.统计有多少商品
Select sum(pnum) from products;
4.统计所有商品的总价值
Select sum(price*pnum) from products;
5.统计所有商品的平均价格
Select sum(price*pnum)/sum(pnum) from products;
--round(值,保留位数)
Select round(sum(price*pnum)/sum(pnum),2) from products;
6.统计出记录中pnum的平均值
Select avg(pnum) from products;
7.统计出商品表中price最大值
Select max(price) from products;
8.统计出商品表中price最小值
Select min(price) from products;
9.统计出生活百科类图书的总数量
Select sum(pnum) from products where category=’生活百科’;
分组:group by
格式1:
Select 分组字段[,集合函数] from 表名 [where 条件] group by分组字段;
1.对商品分类别统计,求出每一种类商品的总数量
Select category,sum(pnum) from products group by category;
2.对商品分类别统计,求出每一种类商品的总数量,数量要大于100
Select category,sum(pnum) from products where sum(pnum)>100 group by category; ×where条件后不能跟聚集函数
格式2:
Select 分组字段[,集合函数] from 表名 [where 条件] group by分组字段 sum(pnum)>100;
where和having的区别:
1.where后面不能使用聚集函数,having可以
2.where是对分组之前的数据进行筛选,having是对分组之后的数据进行筛选。
DQL的使用小结:
Select 分组字段[,聚集函数] from 表名 where 条件 group by 分组字段 having 条件 order by 排序字段 asc|desc;
注意:order by永远放在最后
执行顺序:
1.先确定哪张表 from
2.确定是否有条件 where
3.若需要分组,使用group by
4.若需要对分组后的数据进行筛选,执行having
5.确定要显示哪些字段,select
6.确定字段的显示方法,执行order by
DTL:数据库事物语言
DCL:数据库控制语言
字段类型:
Java mysql
Byte tinyint
Short smallint
Int int☆
Long bigint
Float float
Double double double(m,d) m代表的数字的长度,d代表小数位个数
Double(5,2) 存放的最大值为999.99
Char char(n) char(5):长度不可变 如:存abc,在数据库中“abc ”
String varchar(n)☆ varchar(5):长度可变 如:存abc,“abc”
Boolean
Java.sql.Date date:日期
Java.sql.Time time:时间
Java.sql.TimeStamp timestamp:时间戳
dateTime:日期和时间
约束:保证是数据的有效性和完整性
在mysql中有主键约束(primary key),唯一约束(unique),非空约束(not null),外键约束(foreign key)
主键约束(primary key):确定字段是该条记录唯一标识。
注意:
1.主键可以是一个字段,也可以是多个字段
2.一张表只能有一个主键
特点:
被主键约束修饰过的字段唯一非空
使用:
格式1:在声明字段的同时,添加主键约束
Create table pk01(
id int primary key,
name varchar(20)
);
insert into pk01 values(1,'tom'); --成功
insert into pk01 values(1,'tom');--错误,已经存在,保证唯一性
insert into pk01 values(null,'tom');--错误,非空
扩展:
一般情况下,使用id作为主键,id没有任何实际意义。
格式2:在声明字段之后,在约束区域添加主键约束:[constraint] primary key(字段1[,字段2...]);联合主键,都不能为空
Create table pk02(
id int
name varchar(20),
primary key(id,name)
);
Insert into pk02 values(1,’tom’);
Insert into pk02 values(1,’jack’);
格式3:创建完表之后,通过修改表结构添加主键约束:
Alter table 表名 add primary key(字段1[,字段2...]);
Create table pk03(id int,name varchar(20));
Alter table pk03 add primary key(name);
使用小结:
开发中最常使用的是第一种,只能在一个字段上添加主键约束。
若需要在多个字段上添加主键约束(联合主键),请使用第二种或第三种方 式。
唯一约束(unique)
特点:被修饰的字段唯一,对Null值不起作用
使用:
格式1:在声明字段的同时,添加唯一约束
写法:字段名 字段类型 unique
Create table un01(
id int unique,
name varchar(20)
);
Insert into un01 values(null,’tom’); --多次创建成功,对null不起作用
格式2:在声明字段之后,在约束区域添加唯一约束
写法:[constraint] unique(字段1[,字段2...])
Create table un02(
id int,name varchar(20),
unique(id,name)
);--联合唯一约束
格式3:在创建完表之后,通过修改表结构添加唯一约束
Alter table 表名 add unique(字段1[,字段2]));
Alter table un03 add unique(id);
使用小结:
在开发中一般不添加
在项目最后上线的时候通过第三种方式添加唯一约束。
非空约束(not null)
特点:被修饰的字段非空
格式:在声明字段的同时添加非空约束
Create table nn
id int not null,
name varchar(20) not null
);
三个小知识(必须掌握)
auto_increment(开发)
作用:被它修饰过的字段可以自增
格式:
字段名称 字段类型 primary key auto_increment
特点:
1.被修饰的字段类型可以自增,一般是int
2.被修饰的字段必须是一个key,一般是primary key
create table ai01(
id varchar(20) primary key auto_increment,
name varchar(20)
); -- 错误 Incorrect column specifier for column 'id'
create table ai02(
id int auto_increment,
name varchar(20)
); -- 错误 it must be defined as a key
create table ai03(
id int primary key auto_increment,
name varchar(20)
);
使用的注意事项:
插入值得时候可以是指定的值
插入值得时候把它指定为Null
插入值的时候忽略掉它也可以。
insert into ai03 values(33,'sansan');
insert into ai03 values(null,'xiaosan');
insert into ai03(name) values('xiaosi');
truncate(面试)
作用:清空表,干掉表结构,创建一个新表。
格式:truncate table 表名;
delete from 和truncate区别:
1.truncate 属于DDL语句,delete 属于DML语句
2.truncate 是干掉表结构,重新创建一个新表。delete 是逐条删除记录
default(看懂)
作用:添加默认值
格式:字段名称 字段类型 (约束) default 默认值
例如:
create table de(
id int primary key auto_increment,
name varchar(20) default '张三丰'
);
insert into de values(null,'sansan');
insert into de values(null,null); -- null也算是赋值
insert into de(id) values(null);
结论:在不明确给定值得时候采用默认值。
数据库的备份与还原(了解)
备份:
格式:在cmd窗口下输入
mysqldump -uroot -p密码 要备份的数据库>文件磁盘路径
例如:
mysqldump -uroot -pmoujinling321 mjl>d:/mjl.sql
还原:前提是必须手动创建目的地数据库
格式1:在cmd窗口下输入
mysql -uroot -p密码 目的地数据库<文件磁盘路径
例如:
mysql -uroot -pmoujinling321 u34<d:/mjl.sql
格式2:必须在目的地数据库中执行命令
source 文件磁盘路径
例如:
source d:/mjl.sql
常用的命令:
select database(); -- 查看当前所在的数据库
多表的设计与实现☆
关系型数据库:存放实体与实体之间的关系。
用户<拥有> 订单<包含> 商品
一对多
设计:
在实际开发中,我们称一方(用户表)为一表或者主表,称多方(订单表)为多表或者从表。
然后在多表的一方添加一个字段,字段名称自定义(一般使用主表的名称_id,字段的类型一般和主表的id保持一致)。该字段称之为外键。
实现:
user(用户表)
create table user(
id int primary key auto_increment,
username varchar(20)
);
orders(订单表)
create table orders(
id int primary key auto_increment,
price double,
user_id int
);
insert into user values(null,'强哥'),(null,'聪哥');
insert into orders values(null,1314,1);
insert into orders values(null,1315,2);
insert into orders values(null,305,2);
为了保证数据的有效性和完整性,必须给表添加外键约束。
在多表的一方添加外键约束。
格式:通过修改表结构添加外键约束。
alter table 表名 add [constraint [外键名称]] foreign key(多表的外键) references 主表(主键);
例如:
alter table orders add foreign key(user_id) references user(id);
注意:
主表中不能删除从表中已引用的数据
从表中不能添加主表中不存在的数据
外键可以为null
多对多
设计:
在开发中,一般会引入一个中间表,中间表中的字段一般为两表的主键。
通过中间表,就可以把多对多的关系拆分成两个一对多的关系。
一般我们会把中间表的两个字段设置成联合主键。
实现:
list(商品表)
create table list(
id int primary key auto_increment,
price double
);
orders_pro(中间表)
create table orders_pro(
oid int,
pid int,
primary key(oid,lid)
);
insert into list values(null,999),(null,305),(null,10);
insert into orders_list values(1,1),(1,2),(1,3),(2,1),(2,2),(2,3),(3,2);
为了保证数据的有效性和完整性,必须给表添加外键约束。(在中间表上添加)
在中间表上添加两个外键约束
格式:
alter table 表名 add [constraint [外键名称]] foreign key(多表的外键) references 主表(主表的id);
例如:
alter table orders_list add foreign key(oid) references orders(id);
alter table orders_list add foreign key(lid) references list(id);
一对一
设计:
1.若一张表中的字段很少,二表合二为一
2.根据需求,一般在不常用的表上添加外键约束,实际上是把主键设置成外键即可。
实现:
person(人员表)
create table person(
id int primary key auto_increment,
username varchar(20)
);
idcard(身份证表)
create table idcard(
id int primary key auto_increment,
idno varchar(25)
);
设置外键:
alter table idcard add foreign key(id) references person(id);
多表的查询☆
环境搭建:
-- 用户表
create table `user` (
`id` int auto_increment primary key,
`username` varchar(50) -- 用户姓名
);
-- 订单表(orders)
create table `orders` (
`id` int auto_increment primary key,
`price` double,
`user_id` int
);
-- 给订单表添加外键约束
alter table orders add constraint user_fk foreign key (user_id) references user(id);
-- 向user表中添加数据
insert into user values(1,'张三');
insert into user values(2,'李四');
insert into user values(3,'王五');
insert into user values(4,'赵六');
-- 向orders 表中插入数据
insert into orders values(1,1314,1);
insert into orders values(2,1314,1);
insert into orders values(3,15,2);
insert into orders values(4,315,4);
insert into orders values(5,1014,null);
1.查询用户的订单,没有订单的用户不显示
2.查询所有用户的订单详情
3.查询所有订单的用户详情
笛卡尔积:(了解)
格式:
select a.*,b.* from a,b
隐式内连接:(1)
格式:
select a.*,b.* from a,b where 连接条件;
例如:
select user.*,orders.* from user,orders where user.id=orders.user_id;
内连接:(1)
格式:
select a.*,b.* from a [inner] join b on 连接条件;
注意:
内连接的查询结果和隐式内连接的查询结果一样。
例如:
select user.*,orders.* from user inner join orders on user.id=orders.user_id;
左外连接:
格式:
select a.*,b.* from a left join b on 连接条件;
意思:
以Left左边的表为主,显示它的所有记录,根据连接条件查询b表。若符合条件,则显示该条记录。否则以Null值显示。
例如:
select user.*,orders.* from user left join orders on user.id=orders.user_id;
右外连接:
格式:
select a.*,b.* from a right join b on 连接条件;
注意:
左外连接和右外连接可以等价。
意思:
以right右边的表为主,显示它的所有记录,根据连接条件查询a表。若符合条件,则显示该条记录。否则以Null值显示。
例如:
select user.*,orders.* from user right join orders on user.id=orders.user_id;
子查询:
一个查询的条件是另一个查询的结果。
1.查看用户为张三的订单详情
select id from user where username='张三'; --id=1
select * from orders where user_id=1;
合二为一:
select * from orders where user_id=(select id from user where username='张三');
2.查询出订单的价格大于300的所有用户信息。
select user_id from orders where price>300; -- user_id=1,1,4,null
select * from user where id in(1,1,4,null);
合二为一:
select * from user where id in (select user_id from orders where price>300);
3.查询订单价格大于300的订单信息及相关用户的信息。
方式1:select user.*,orders.* from user,orders where user.id=orders.user_id and orders.price>300;
方式2:
首先,查询订单价格大于300的订单信息。
select * from orders where price>300;
然后,通过上一条语句结果和user表进行关联查询即可
select a.*,b.* from a,b where 条件语句;
例如:
select u.*,b.* from user u,(select * from orders where price >300) as b where u.id=b.user_id;
单列多行子查询:
可以使用in,any,all操作
>any:大于子查询中的最小值
>all:大于子查询中的最大值
<any:小于子查询中的最大值
<all:小于子查询中的最小值
!=any或<>any:不等于子查询中的任意值
=any:等于子查询中的任意值
练习:
-- 1.返回拥有员工的部门名、部门号。(dept,emp)
select distinct d.dname,d.deptno from dept d,emp e where d.deptno=e.deptno;
-- 2.工资多于ward的员工信息。
select sal from emp where ename='ward';
select * from emp where sal>(select sal from emp where ename='ward');
-- 3.返回员工和其所属领导的姓名。(自连接)
select e.ename as '员工',l.ename as '领导' from emp e,emp l where e.mgr=l.empno; -- 一张表拆成两张
-- 4.返回雇员的雇佣日期早于其领导雇佣日期的员工及其领导姓名。(在日期类型可以直接比较)
select e.ename '员工' ,l.ename '领导' from emp e,emp l where e.mgr=l.empno and e.hiredate<l.hiredate;
-- 5.返回员工姓名及其所在的部门名称。
select deptno from emp;
select e.ename,d.dname from emp e,(select deptno from emp) d where e.deptno=d.deptno;
-- 6.返回从事clerk工作的员工姓名和所在部门名称
select job from emp where ename='clark';
select e.ename,d.dname from emp e,dept d where e.job='clerk' and e.deptno=d.deptno;
-- 7.返回部门号及其本部门的最低工资。
select deptno,min(sal) from emp group by deptno;
-- 8.返回销售部(sales)所有员工的姓名。
select deptno from dept where dname='sales';
select ename from emp where deptno=(select deptno from dept where dname='sales');
-- 9.返回工资多于平均工资的员工。
select avg(sal) from emp;
select ename from emp where sal>(select avg(sal) from emp);
-- 10.返回与SCOTT从事相同工作的员工。
select job from emp where ename='scott';
select * from emp where job=(select job from emp where ename='scott');
-- 11.返回与30部门员工工资相同的员工姓名与工资。
select sal from emp where deptno=30;
select ename,sal from emp where sal in(select sal from emp where deptno=30);
-- 12.返回工资高于30部门所有员工工资水平的员工信息。
select max(sal) from emp where deptno=30;
select * from emp where sal>(select max(sal) from emp where deptno=30);
select * from emp where sal>all(select sal from emp where deptno=30);
!!!!!!!!!!!!!!!!!!
-- 13.返回部门号、部门名、部门所在位置及其每个部门的员工总数。count(*)=null被忽略了!!
select deptno,count(*) from emp group by deptno;
select d.*,temp.total from dept d,(select deptno,count(*) total from emp group by deptno) temp where d.deptno=temp.deptno;
select d.*,temp.total from dept d left join (select deptno,count(*) total from emp group by deptno) temp on deptno=temp.deptno;
-- 14.返回员工的姓名、所在部门名及其工资。
select deptno,ename,sal from emp;
select d.dname,e.ename,e.sal from dept d,(select deptno,ename,sal from emp) e where d.deptno=e.deptno;
-- 15.返回员工的详细信息。(包括部门名)
select deptno from emp;
select e.*,d.dname from emp e,dept d where e.deptno=d.deptno;
select e.*,d.dname from emp e left join dept d on d.deptno=e.deptno;
-- 16.返回员工工作及其从事此工作的最低工资。
select job from emp;
select min(sal) from emp group by job;
-- 17.返回不同部门经理的最低工资。
select deptno,min(sal) from emp where job='manager' group by deptno;
-- 18.计算出员工的年薪,并且以年薪排序。
select sal*12+ifnull(comm,0) as YearSal from emp order by YearSal asc;
-- 19.返回工资处于第四级别的员工的姓名。
select losal,hisal from salgrade where grade=4;
select e.ename,e.sal from emp e,salgrade s where s.grade=4 and e.sal between s.losal and s.hisal;
-- 20.返回工资为二等级的职员名字、部门所在地、和二等级的最低工资和最高工资
select losal,hisal from salgrade where grade=2;
select e.ename,d.loc,s.losal,s.hisal from emp e,dept d,salgrade s where s.grade=2 and e.deptno=d.deptno and e.sal between s.losal and s.hisal;
select e.ename,d.loc,s.losal,s.hisal from emp e
join salgrade s
on s.grade=2 and e.sal between s.losal and s.hisal
join dept d
on e.deptno=d.deptno;
-- 21.工资等级多于smith的员工信息。
select hisal from salgrade s where (select sal from emp where ename='smith') between s.losal and s.hisal;
select * from emp where sal>(select hisal from salgrade s where (select sal from emp where ename='smith') between s.losal and s.hisal);
转载标明出处:https://www.cnblogs.com/syj1993/p/8410419.html