SQL学习

Navicat使用

远程连接： 服务器的IP地址 以及 数据库的账号密码。

约束类型

常用数据类型
int 整数 很大
decimal 小数 decimal（x,y） x-y整数个数、y小数个数
varchar 字符串 varchar（N） 表示最多存储多少个字符 字母和中文都是一个字符
datetime 2021-03-25 10:44:59’

## 约束
主键 primary key 物理上的储存顺序 一旦录入永远存在
唯一 unique 特殊设置为唯一 在索引里面设置
默认值 字符串数据设置默认值 需要加 单引号
外键 表与表的连接作用

注释 Ctrl+/

表操作
创建表 create table 表名字（
字符名 类型 约束**，**
字符名 类型 约束
）；

drop table 表名； 删除表 完善 drop table if exists 表名

创建表
drop table if exists goods；
create table goods（
id int unsigned primary key auto_increment,
无符号 主键 自动递增
name varchar(20),
price decimal(6,2),
number int,
company varchar(20),
remark varchar(30)
）;

增 insert into 表名 values（）；
insert into goods values
(0,‘惠普笔记本’,5000.00,100,‘某东’,‘渣渣’),
(0,‘大叔笔记本’,6000.00,100,‘某东’,‘大渣渣’);

对应添加
insert into goods（goodsname） values(‘惠普笔记本’);

删除delete 不加*
delete from 表名 where 条件

改update
update 表名 set 列1=值，… where 条件**
update goods set price=3000.00 where id=3;

查 select
select * from 表名 where 条件

单表查询

select * from goods;

select goodsname,price from goods;

– 起别名 as 和’’ 都可以省略
select goodsname as 商品名,price as 价格 from goods;

去重
select distinct(company) from goods;

比较运算符

< = !=

模糊查询 like
select * from goods where remark like ‘%一次性口罩’ / %位置根据情况定
可能是 %一次性口罩% 根据位置加%。

范围查询
连续范围 between 起始值 and 结束
不连续 没关联 in （条件，条件）

空判断
select * from goods where remark is null ;
反之 加 not

排序 order by asc(升序)/desc（降序） 默认升序
select * goods order by 条件 asc；

组合 select * goods order by 条件 asc，条件 asc或者desc；

聚合函数
统计数量 count（条件 一般*） select count（*） from goods ；
最高/大 max 反之 min 平均 avg 求和sum

select sum（count） from goods where remark like ‘%条件%’；

分组 group by 与 having 配合 不用where
select company, count(*) from goods group by company;

having使用

select company from goods group by company having company ！=‘xx’; 或者 having company = ‘’；

加入价格select company max（prince） from goods group by company having company ！=‘xx’;

分页查询 limit x,y x起始索引 y行数

select * from goods

每页显示M条数据 看第N页
limit（N-1）*M , M

多表查询

内连接 表1 inner join 表2 on 表1.字段=表2.字段 两张表中存在对应关系，无对应关系的数据不显示

select * from goods inner join category on goods.typeld=category.typeld;

字段限制
选择性的展示表格 此处展示goods表所有信息和category表的名字信息
select goods.*,category.cateName from goods inner join category on goods.typeld=category.typeld;

– 别名 提高工作效率
select go.*,ca.cateName from goods go inner join category ca on go.typeld=ca.typeld;

左连接 left join 关键字 左侧的表信息全部显示 右侧的无对应信息显示null。
– 左连接 表1 left join 表2 on 表1.字段=表2.字段；
select * from goods left join category on goods.typeid=category.typeid;
– 字段限制
select goods.*,category.cateName from goods left join category on goods.typeid=category.typeid;
表1 表2 根据题目要求 选择
查询商品信息 包含商品分类 / 查询商品分类及商品信息。

右连接
– 右连接一般使用在三张表的连接查询。 主表是最后一张表
– 关键字 右侧的表信息全部显示 左侧的无对应信息显示null。
– 右连接 表1 right join 表2 on 表1.字段=表2.字段；
select * from goods right join category on goods.typeid=category.typeid;
– 字段限制
select goods.*,category.cateName from goods right join category on goods.typeid=category.typeid;

连接查询的实质只是把多张表连接成一张表 后续查询还是按照特定条件去查找

自关联
特征：一张表 其中两列存在对应关系
原理：通过起别名的方式将一张表变为2张表。

子查询

– 需求6: 查询价格高于平均价的商品信息
– 查询所有数据
select * from goods;
– 查询平均价格
select avg(price) from goods; – 68.33
– 需求实现
select * from goods where price > 68.33;
– 优化实现 - 子查询
select * from goods where price > (select avg(price) from goods);

– 需求7: 查询所有来自并夕夕的商品信息, 包含商品分类
– 查询所有数据
select * from goods;
select * from category;
– 并夕夕商品信息
select * from goods where company = ‘并夕夕’;
– 需求实现 - 子查询
select * from category ca
inner join (select * from goods where company = ‘并夕夕’) a on ca.typeId = a.typeId;

数据库学习

E-R模型 entry-relationship 实体-联系

外键
– 主表
drop table if exists class;
create table class(
id int unsigned primary key auto_increment,
name varchar(10)
);

– 从表
drop table if exists stu;
create table stu(
name varchar(10),
class_id int unsigned
– stu 表的 class_id 指向 class 表的 id, class_id 是 stu 表的外键
– 创表时添加外键
– foreign key(自己的字段名) references 目标表名(目标表的主键)
– foreign key(class_id) references class(id)
);

– 扩展1 : 对于已经存在的表添加外键
– alter table 从表名 add foreign key (从表字段) references 主表名(主表主键);
alter table stu add foreign key (class_id) references class(id);

– 扩展2 : 查看外键和删除外键
– 查看外键
– show create table 表名
show create table stu;
– CREATE TABLE stu (
– name varchar(10) DEFAULT NULL,
– class_id int(10) unsigned DEFAULT NULL,
– KEY class_id (class_id),
– CONSTRAINT stu_ibfk_1 FOREIGN KEY (class_id) REFERENCES class (id)
– ) ENGINE=InnoDB DEFAULT CHARSET=utf8

– 删除外键
– alter table stu drop foreign key 外键名称
alter table stu drop foreign key stu_ibfk_1;

索引！
– 开启时间监测
set profiling=1;

– 查询示例数据 num = 10000 的值
select * from test_index where num = 10000;

– 查看运行时间
show profiles;

– 添加索引(对已存在的表添加索引)
– create index 索引名称 on 表名(目标字段)
create index num_index on test_index(num);

– 再次执行查询数据操作
select * from test_index where num = 10000;

– 再次查看运行时间
show profiles;

– 扩展1 : 查看索引
– show index from 表名
show index from test_index;

– 扩展2 : 创表时添加 三种方法
create table create_index(
id int primary key,

name varchar(10) unique,  -- unique : 设置端唯一值

age int,
key(age) -- 指定添加索引方法

);
– 查看索引
show index from create_index;

– 扩展3 : 删除索引
– drop index 索引名称 on 表名;
drop index age on create_index;

命令行操作MYSQL

登录
mysql -u数据库用户名 -p密码

数据库使用
use 数据库名
切换成功 Database changed
选择 select database();
创建 create database 数据库名 charset=utf8；
删除 drop database 数据库名；
显示 show databases；

表操作
显示 show tables；
查询表字段信息 desc 表名；
查询建表信息 show create table 表名；

存储过程

drop table if exists datatest;
– 创建 datatest 表
create table datatest(
id int unsigned primary key auto_increment,
num int
);

– 修改句尾标识符为’//’
delimiter //
– 如果存在 test 存储过程则删除
drop procedure if exists test;
– 创建无参数的存储过程 test
create procedure test()
begin
– 声明变量 i
declare i int;
– 变量初始化赋值为 0
set i = 0;
– 设置循环条件: 当 i 大于 10 时跳出 while 循环
while i < 100000 do
– 往 datatest 表插入数据
insert into datatest values (null, i);
– 循环一次, i 加一
set i = i + 1;
– 结束 while 循环
end while;
– 查看 datatest 表数据
select * from datatest;
– 结束存储过程定义语句
end//
– 恢复句尾标识符为’;’
delimiter ;

– 调用存储过程 test
call test();

事务
要么都执行，要么都不执行。

数据库 必须是 innoDB（）
begin 开始

然后修改更新

最后 提交 commit

撤回 不修改 rollback

视图
封装SQL语句 以视图的形式显示。

– 创建视图语法
– create view 视图名称 as select 语句;

create view v_goods as select goodsName 商品名称, price 价格, num 数量, company 公司 from goods;

– 视图的用法: 当成表查询使用即可
select * from v_goods;

– 复杂 SQL 语句视图封装
select go.goodsName, ca.cateName from goods go inner join category ca on go.typeId = ca.typeId;

– 封装连接查询语句时, 如果存在重名字段名称, 需要通过别名进行修改
create view v_goods_cate as select go.*, ca.id 序号, ca.typeId 类型, ca.cateName from goods go inner join category ca on go.typeId = ca.typeId;

select * from v_goods_cate;

– 删除视图语句
– drop view 视图名称
drop view v_goods_cate;