SQL:structure Query Language;结构化查询语言
DDL:数据定义语言:定义数据库,数据表的结构:create(创建),drop(删除),alter(修改)
DML:数据操纵语言:主要是用来操作数据:insert(插入),update(修改),delete(删除)
DCL:数据控制语言:定义访问权限,取消访问权限,安全设置grant
DQL:数据查询语言:select(查询) from子句 where 子句
数据库的CRUD的操作
1.首先要登录数据库服务器:mysql -uroot -prootroot
创建数据库
create database 数据库名称
创建数据库的时候,指定字符集
create database 数据库名称 character set utf8;
创建数据库的时候,指定校对规则
create database 数据库名称 character set utf8 college 校对规则;
查看数据库
查看所有数据库
show databases;
查看数据库定义的语句
show create database 数据库名称
修改数据库的操作
修改数据的字符集
alter database 数据库名称 character set 字符集
删除数据库的操作
删除数据库
drop database 数据库名称
其他数据库的操作命令
切换数据库
use 数据名称;
查看一下当前正在使用的数据库
select database();
表的CRUD操作
创建表
create database 数据库的名称;
create table 表名(
列名 列的类型(长度) 约束,
列名2 列的类型(长度) 约束
);
列的类型(与java对比)
java sql
int int
char/string char/varchar
char:固定长度
varchar:可变长度
char(3):一 (剩下两个用空格去填满)
varchar(3):一 (长度代表的是可以存放的字符个数)
double double
float float
tate tate:YYYY-MM-DD
time:hh:mm:ss
datetime:YYYY-MM-DD hh:mm:ss 默认值是null
timestamp:YYYY-MM-DD hh:mm:ss 默认是当前时间
text:主要存放文本
blob:存放的是二进制
列的约束:
主键约束:primary key
唯一约束:unique
非空约束:not null
自增长:auto_increment
创建表
1.分析实体:学生
2.学生id
3.姓名
4.性别
5.年龄
create table student(
sid int primary key,
sname varchar(20),
ssex varchar(20),
)
查看表
查看所有表
show tables;
查看表的定义
show create table 表名;
查看表的结构
desc 表名;
修改表
添加列(add)
alter table 表名 add 列名 列的类型 列的约束;
alter table student add age int not null;
修改列(modify)
alter table student modify age varchar(22);
修改列名(change)
alter table student change age gender varchar(23);
删除列(drop)
alter table student drop gender;
修改表的字符集
alter table student character set utf8;
修改表名
rename table student to heima;
删除表
drop table 表名;
插入数据
insert into 表名 (列名1,列名2,列名3)values(值1,值2,值3);
insert into student (id,name,sex,age)values(1,'xiaohei','男','18');
简单的写法
insert into student(2,'zhangsan','nan','19');
批量插入
insert into student('3',lisi','nan','23'),('3',wangwu','nan','23'),('5',laoliu','nan','23'),
查看表中的数据
select * from student;
删除记录
delete from 表名 [where 条件]
delete from student where sid =1;
delete删除数据与truncate删除数据有什么差别
delete:DML一条一条删除表中的数据;
truncate:DDL先删除表再重建表;
关于哪些执行效率高:具体要看表中的数据量
如果数据比较少,delete比较高效;
如果数据比较多,truncate比较高效
更新表记录
update 表名 set 列名=列的值,列名2=列的值2 [where 条件]
查询数据
select [distinct(去除重复的数据)] [*] [列名1,列名2] from 表名 [where 条件]
商品分类:手机数码,鞋靴箱包
1.分类id
2.分类名称
3.分类描述
create table category(
cid int primary key auto_increment,
sname varchar(20),
cdesc varchar(31)
);
insert into category values(null,'shuma','全场最低价'),(null,'yifufushi','全场最低价2'),(null,'huashenglingshi','全场最低价3');
编码
set names gbk是什么意思
所有商品
1.商品id
2.商品名称
3.商品的价格
4.生产日期
5.商品的分类
商品的所属分类:所属关系
create table product(
pid int primary key auto_increment,
pname varchar(10),
price double,
pdate timestamp,
cid int
);
insert into product values(null,'华为mate 20',1999,null,1),(null,'锥子',999,null,1),(null,'耐克',599,null,2),(null,'葵瓜子',9.9,null,3);
别名查询
表的别名
select p.pname,p,price form product as p;
列的别名
select pname as 商品名称,price as 商品价格 from product;
省略as
select pname 商品名称,price 商品价格 from product;
去掉重复的值
查询所有商品的价格
select price from product;
select distinct price from product;
select 运算查询:仅仅在查询结果上做了运算
select *,price*1.5 as 折后价 from product;
条件查询
select * from product where price > 60;
关系运算符:>,>=,<,<= , = , != <>
<>:不等于:标准的SQL语法
!=:不等于:非标准SQL语法
判断是否为空:is null ,is not null
查询商品价格是1-100;
select * from product where price >0 and price <100;
between...and...
select * from product price between 0 and 100;
运算符:and ,or ,not
查询商品价格小于100,或大于800;
select * from product price <100 or price >800;
模糊查询:like
_:代表一个字符
%:代表的是多个字符;
select * from product where pname like '%华%';
in 在弄个范围中获得值
查询出商品分类id 在1,4,5里面的所有商品
select * from product where cid in (1,4,5);
排序查询
asc:ascend 升序(默认的排序方式)
desc:descend 降序
查询所有商品,按价格进行排序
select * from product order by price
查询所有的商品,按价格进行降序排序(asc升序,desc降序)
select * from product order by price desc;
查询“子”的商品,按价格进行降序排序
select * from product where pname like '%子%' order by price asc;
聚合函数
sum():求和
avg():求平均值
count():统计数量
max():最大值
min():最小值
获得所有商品的价格:
select sum(price) from product;
获得所有所有商品平均价格:
select avg(price) from product;
获得所有商品的个数
select count(*) from product;
!where条件后面不能使用聚合函数
查询价格都大于平均价格的所有商品
select * from product where price>(select avg(price) from product);
分组
根据字段名cid 进行分组,分组后统计个数
select cid,count(*) product group by cid;
根据cid分组,分组统计每组商品的平均价格,并且商品平均价格>100
select cid,avg(price) from product group by cid having avg(price) >100;
where 关键字:它是不可以接聚合函数,出现在分组之前
having 关键字:可以接聚合函数,出现在分组之后
编写顺序
sfwgho
select...from...where..group by..having...order by..
执行顺序
fwghso
掌握mysql中多表的创建及多表的查询;
掌握mysql中的表关系分析并正确建表;
外键约束:foreign key
给product中的这个cno添加一个外键约束
alter table product add foreign key(cno) references category(cid);
从分类表中,删除分类为5的信息
delete from category where id=5;
首先得去product表中,删除所有分类为5的商品
建库原则:
通常情况下,一个项目/应用建一个数据库
多表之间的建表原则
一对多:商品和分类
建表原则:在多的一方添加一个外键,指向另一方的主键
多对多:学生和课程,学生和老师
多对多的建表:
多建一张中间表,将多对多的关系拆成一对多的关系,中间表至少要有两个外键,这两个外键分别指向原来的那张表
一对一:将一对一的情况,当做一对多情况处理,在任意一张表添加一个外键,并且这个外键要唯一,指向另外一张表;
商城案例
用户表:user(用户id ,用户名,密码,电话号码)
create table user(
uid int primary key auto_increment,
username varchar(31),
password varchar(31),
phone varchar(11)
);
订单表:orders(订单编号,总金额,订单创建时间,地址,用户id(外键))
create table orders(
oid int primary key auto_increment,
sum int,
otime timestamp,
address varchar(11),
uno int,
foreign key(uno) references user(uid)
);
商品表:product
create table product(
pid int primary key auto_increment,
pname varchar(10),
price double,
cno int,
foreign key (cno) references category(cid)
);
商品分类表:category
create table category(
cid int primary key auto_increment,
cname varchar(15),
cdesc varchar(100)
);
订单项:中间表orderitem
create table orderitem(
ono int ,
pno int,
foreign key (ono) references orders(oid),
foreign key(pno) references product(pid),
ocount int,
subsum double
);
多表之间的关系如何维护:外键约束:foreign key
添加一个外键:alter table 表名 add foreign key 列名 references 主键表名(主键列名);
删除的时候,先删除外键关联的所有数据,再才能删除分类中的数据
建表原则:
一对多:在多的一方增加一个外键,指向另一方
多对多:将多对多转成一对多的关系,创建一个中间表
一对一:不常用,拆表操作
主键约束:默认就是不能为空,唯一
外键都是指向另外一张表的主键,可以作为外键的指向;
主键一张表只能有一个
唯一约束:列内的内容,必须是唯一,不能出现重复的情况,可以为空
不能作为其它表的外键
一张表中可以有多个唯一约束;
数据库客户端软件
SQLyog
多表查询
交叉连接查询 笛卡尔积
select * from product;
select * from category;
select * from product,category;
笛卡尔积:查出来时两张表的乘积,查出来的结果没有意义
过滤出有意义的数据
select * from product,category where cno =cid;
select * from product as p, category as c where p.cno=c.cid;
select * from product p, category c where p.cno=c.cid;
内连接查询(inner,outer可以省略)
隐式内链接
select * from product p, category c where p.cno=c.cid;
显示内链接
select * from product p inner join category c on p.con=c.cid;
左外链接
select * from product p left outer join category c on p.com=c.cid;
右外链接
select * from product p right outer join category c on p.com=c.cid;
分页查询
每页数据数据3
起始索引从0
第一页11
第一个参数是索引
第二个参数是显示个数
select * from product limit 0,3;