1 二、mysql操作
Day01
1.1 mysql 数据库的概述
1.1.1 数据库
Oracle（付费的，市场占有份额高，学习用不付费），DB2
MS Sql Server（微软开发的）
MySql（开放源代码，免费的，用的也非常多，国内的电商网站都用，可以承受很多人同时攻击）
sqlite（嵌入，手机上用的就是这种数据库）
1.1.2 Mysql历史介绍
=====================================
* 开源免费
* mysql 卖给sun公司(10亿美金),一年后，Oracle（公司） 收购了sun（公司）
* mysql 免费开源社区版
闭源的收费版（oracle公司添加新的一些功能，出现的新版本）
* mysql 未来有完全闭源的风险（未来前景）
* mysql 创始人，又开发了一个（源代码创始人卖了10亿美金后又开发了）mysql的分支 mariadb（新的开源数据库）
* 开源社区已经放弃支持 mysql,（mysql的开发速度已经大大的放慢,linux社区已经放弃）转为支持 mariadb
（yum 在线安装—提供一个软件的全部依赖包）
(yum list |grep mysql ,找不见，已经放弃mysql了)
1.1.3 数据库的分类
======================================
* 关系型数据库
*) 用“表”保存数据，相关数据存入一张表。
* 非关系型数据库
*) 对象数据库
*) 键值数据库
1.1.4 mysql 的安装
======================================
* 安装 mysql 社区版 community
* 安装 mariadb
* linux yum 安装 mariadb
yum -y install mariadb-server mariadb mariadb-devel
systemctl start mariadb
1.2 mysql 数据库的使用
======================================
1.2.1 * 登录 mysql 服务器
mysql -hlocalhosst -P3306 -uroot -p
mysql -uroot -p
password:
1.2.2 * 查看数据库列表
show databases;
show schemas;
*) database 数据库
*) schema 数据库中一组对象的集合,即数据库
1.2.3 * 进入数据库
use test;
use db1;
use mysql;
use 后面是库名。
1.2.4 * 查看数据库表
show tables; //查看这个库中所有的表
1.2.5 * 查询表中的数据
select * from user;
1.2.6 * 创建数据库
create database db1 charset utf8;
show databases;
1.2.7 * 数据库保存目录
linux:
ls /var/lib/mysql
windows:
mysql安装目录\data
1.2.8 * 查看数据库的库配置文件
配置两项内容：
*)数据库使用的默认字符编码
*)字符的排序方式
1.2.8.1 * 数据库使用的默认字符编码
linux:
cat /var/lib/mysql/db1/db.opt
windows:
把文件拖拽到记事本
1.2.8.2 * 字符排列方式
collation 排列、校对、字符排序
show collation;
show collation like 'gbk%';
show collation like 'utf8\_%';
一般只用默认选择
gbk_chinese_ci
utf8_general_ci
1.2.9 * 修改数据库
修改编码
alter database db1 charset gbk;
修改排序(很少用)
alter database db1 collate gbk_bin;
1.2.10 * 创建表
column/field 列、字段
use db1;
-- 用户表
create table user(
name varchar(16),
age int
) engine=innodb charset=gbk;
show tables;
desc user;
1.2.11 * 数据库目录中的表文件
user.frm --表结构文件
user.myd --数据存储文件
user.myi --索引文件
linux:
ls -al /var/lib/mysql/db1
windows:
找到 data\db1 目录
1.2.12 * 存储引擎
1.2.12.1 *)innodb
*)支持数据库高级功能:
*)事务
*)外键
1.2.12.2 *)myisam
*)数据存储基本功能
*)效率非常高
1.2.13 * 在表中插入数据
insert into user values('张三', 23);
insert into user(age, name) values(22, '李四');
查询数据
select * from user;
1.2.14 * 修改表
1.2.14.1 *) 修改表名
rename table user to tb_user;
1.2.14.2 *) 修改表属性(引擎、字符编码)
alter table tb_user
engine=myisam charset=utf8;
show create table tb_user;
1.2.14.3 *) 添加字段
*)first 加载第一个
*)after 指定加在某个字段之后
alter table tb_user add id int first;
alter table tb_user add(
gender char(1),
tel char(11)
);
select * from tb_user;
1.2.14.4 *) 修改字段
1.2.14.4.1 *)改字段名
-- gender 改成 sex
alter table tb_user
change gender sex varchar(10);
desc tb_user;
select * from tb_user;
1.2.14.4.2 *)改数据类型
alter table tb_user
modify tel varchar(20);
1.2.14.4.3 *)改顺序
alter table tb_user
modify tel varchar(20) after age;
desc tb_user;
alter table tb_user
modify sex varchar(10) after name;
desc tb_user;
1.2.14.5 *) 删除字段
alter table tb_user
drop sex;
desc tb_user;
1.2.15 * 删除表(不可恢复)
drop table tb_user;
1.2.16 * 删除库(不可恢复)
drop database db1;
1.3 mysql-day01练习
========================================
1.3.1 1. 创建 jtds 数据库,使用 utf8 编码
create database jtds charset utf8;
use jtds;
1.3.2 2. 创建 tb_item 商品表
create table tb_item2(
id bigint(20), -- id
cid bigint(10), -- 分类id
brand varchar(50), -- 品牌
model varchar(50), -- 型号
title varchar(100), -- 商品名
sell_point varchar(500), -- 卖点文本
price long, -- 价格*100
num int(10), -- 库存量
barcode varchar(30), -- 条码
image varchar(500), -- 图片路径
status tinyint(4), -- 状态1正常2下架3删除
created timestamp, -- 创建时间
updated timestamp -- 修改时间
) engine=innodb charset=utf8;
1.3.3 3. 在 tb_item 表插入商品数据
insert into tb_item(id, brand, title, price, created)
values(7, '苹果', 'iPhone x', 999999, now());
select id,brand,title,price,created from tb_item;
1.3.4 4. 修改 id 是7的商品，降价 12%，修改库存量为20
update tb_item
set price=round(price*0.88), num=20 -- round表示保留两位小数点，四舍五入
where id=7;
select id,brand,title,price,created,num
from tb_item;
1.3.5 5. 删除商品 7
delete from tb_item where id=7;
day02回归day01
1.右键桌面， 终端 -- Windows cmd窗口
2.mysql -uroot –p -- 账户
password:直接回车 -- 密码
3.show databases; 显示所有数据库
4.create database test2 charset utf8;
创建数据库，指定默认字符编码
5.use test2;
进入数据库
6.退出 mysql
exit 或 \q
1.4 mysql数据库表的数据类型
==================================================
1.4.1 创建数据表
1.数据类型
2.约束
3.表的增删改查
创建表
删除表
修改表结构
查看表
1.4.2 建表语句
==============================================
create table user(
id int,
username varchar(32),
password varchar(32)
) engine=innodb charset=utf8;
show tables;
1.4.3 数据类型
===========================================
1.4.3.1 字符串
-----------------------------------
char(固定长度) 定长字符串,最多 255 个字节
varchar(最大长度) 变长字符串,超过出错,65535 个字节,一般不超过 255个字节
text 65535 字节
blob 大数据对象，以字节方式存储数据（如图片，视频）
1.4.3.2 数字
-----------------------------------
不带符号, 添加 unsigned,
zerofill 显示时，位数不足，可以补0
tinyint 1字节
SMALLINT 2字节
int 4字节
BIGINT 8字节
int(6)
不会影响保存的数据范围，只影响查询时显示的位数。
float 4字节
double 8字节
可能保存不精确的值
DECIMAL/numeric
保存精确浮点数
需要指定总长度和小数位位数
decimal(10,2) 整数位数是：八，小数位数是：二。
1.4.3.3 日期
-----------------------------------
date 年月日
time 时分秒
datetime 年月日时分秒
到 9999 年
timestamp 同 datetime
到 2038 年
第一个 timestamp 字段，在行数据更新时，自动修改为系统当前时间
也可以使用 bigint 整数表示时间
java 中时间是用长整型毫秒值表示
毫秒值： 1970-1-1 0点开始
1.4.3.4 表字段总字节量限制为 65535
====================================================
* varchar 需要额外两个字节来表示长度
* 一个字段如果允许 null 值，需要额外 1 个字节
* text 类型 65535 个字节，但仅占用表的 10 个字节额度
1.4.4 约束 5种
====================================================
* 所有的约束保存在 information_schema 库中，table_constraints 表中。
* 限制字段的取值
* 主键、非空、唯一、外键、检查(mysql不支持)
1.4.4.1 *主键
1.4.4.1.1 *)对一行数据的唯一标识
*)不重复
*)不能为空 null
1.4.4.1.2 *)尽量使用业务无关数据作为主键
*)连续自增整数 auto_increment
*)随机字符串
1.4.4.1.3 *)主键自动添加索引
1.4.4.1.4 *)添加主键
1.4.4.1.4.1 *)建表时添加
create table tb1(
id bigint primary key auto_increment,
...
);
1.4.4.1.4.2 *)修改表添加
alter table tb1 add primary key(id);
1.4.4.1.5 *)删除主键
alter table tb1 drop primary key;
1.4.4.1.6 *)auto_increment 自增
*)查看下一个自增值:show create table tb1\G;
*)一直增加不会退
*)如果在表中手动插入一个更大的主键值，会从这个值继续递增
*)只有主键才能自增
*)如果删除自增主键约束，先取消自增，再取消主键约束
1.4.4.1.7 *) 双主键(不推荐)
两个字段和并作为主键
create table tb1(
id ..,
name ...,
...,
primary key(id, name)
);
1.4.4.2 *非空
不能取 null 值
1.4.4.2.1 *)添加非空约束
1.4.4.2.1.1 *)创建表时
create table tb1(
id int primary key,
name varchar(20) not null
);
1.4.4.2.1.2 *)查看表中的非空约束
desc tb1;
show create table tb1\G
1.4.4.2.1.3 *)修改字段,添加/删除非空约束
alter table tb1 modify name varchar(32) not null; -- 添加非空
alter table tb1 modify name varchar(32) null; -- 删除非空
1.4.4.3 *唯一约束
限制字段的值不能重复，但允许多个 null 值
1.4.4.3.1 *)添加唯一约束
1.4.4.3.1.1 *)创建表时
create table tb1(
...,
name varchar(20) unique,
...
);
create table tb1(
...
name ...,
gender ...,
...,
unique key(name, gender)
);
1.4.4.3.1.2 *)修改字段，添加唯一约束
alter table student
modify name varchar(20) unique;
alter table student
add unique key(name, gender);
1.4.4.3.2 *)查看唯一约束
show create table tb1\G
1.4.4.3.3 *)取消唯一约束
alter table tb1 drop index 约束名
1.4.4.4 *外键约束（foreign key）
References参考
外键一般引用另一个主键中的值
*)mysql的 innodb 引擎才支持外键,效率低
myisam 引擎不支持外键,效率高
*)foreign key(Item_cat_id) references tb_item_cat(id)
学生表 联系方式表
id name .. id stu_id tel .. ..
1 张三 1 1 43634634
2 李四 2 3 47542345
3 王五 3 2 678434
4 2 845345354
5 null 45568753
6 1 44523234
1.4.4.4.1 *)添加外键约束
1.4.4.4.1.1 *)创建表时添加
create table tb2(
...,
stu_id ...,
...,
foreign key(stu_id) references student(id)
);
1.4.4.4.1.2 *)修改字段添加外键
alter table tb2
add foreign key(stu_id) references student(id);
1.4.4.4.2 *)取消外键约束
alter table tb2 drop foreign key 约束名;
1.4.4.5 *检查约束(mysql 不支持)
create table tb1(
...
gender
age
...,
check(gender='M' or gender='F'),
check(age>=16 and age<=60)
);
1.4.5 默认值
===========================================
create table tb_item(
...
status tinyint default 0,
...
);
1.5 Mysql-day02练习
====================================================
1.5.1 1. 测试数据类型，以及表的 65535 字节量限制
drop table tb1; -- 删除表
create table tb1(
id int(6), -- 4字节
title varchar(21842) -- 21842*3+2+1
)engine=innodb charset=utf8;
1.5.2 2.测试 text 类型
drop table tb2; -- 删除表
create table tb2(
id int(6), -- 4字节
title varchar(21839), -- 21842*3+2+1
des text -- 占10个字节
)engine=innodb charset=utf8;
1.5.3 3. 测试主键
create table student(
id bigint primary key auto_increment,
name varchar(20)
);
-- 插入一行数据
insert into student(name) values('张三');
insert into student(name) values('李四');
-- 查询
select * from student;
1.5.4 4. 手动向自增主键插入更大的值
insert into student(id,name) values(10000, '王五');
select * from student;
show create table student\G;
1.5.5 5. 删除主键约束
-- 取消自增
alter table student modify id bigint;
-- 删除主键约束
alter table student
drop primary key;
1.5.6 6. 学生姓名非空
use test2;
drop table if exists student;
create table student(
id bigint unsigned primary key auto_increment,
name varchar(20) not null,
gender char(1)
);
desc student;
show create table student\G;
insert into student(gender) values('M');-- 失败
insert into student(name, gender)
values('张三', 'M');
show warnings;
select * from student;
1.5.7 7. 修改学生表添加学号字段，不能重复
alter table student
add stu_num int unsigned unique -- 不带符号的唯一
after id; -- 字段顺序添加在 id 后面
desc student;
show create table student\G; -- 查看唯一约束名
-- 在约束表中查看
use information_schema;
select * from table_constraints
where table_schema='test2'; -- 过滤test2库中的约束
use test2;
-- 插入重复学号
insert into student(name, stu_num)
values('张三', 2017001);
insert into student(name, stu_num)
values('张三', 2017002);
-- 删除唯一约束,约束名字叫stu_num
alter table student drop index stu_num;
-- 再添加唯一约束
-- 如果已经有重复数据，不能添加唯一约束
alter table student add unique key(stu_num);
*) 添加联系方式表,用 stu_id 引用学生的主键id值
create table contacts(
id bigint unsigned primary key auto_increment,
stu_id bigint unsigned,
tel varchar(20),
foreign key(stu_id) references student(id)
);
desc contacts;
show create table contacts\G;
select * from student;
insert into contacts(stu_id,tel)
values(1, '345364564');
insert into contacts(stu_id,tel)
values(1, '956454');
insert into contacts(stu_id,tel)
values(2, '7455');
insert into contacts(stu_id,tel)
values(5, '4565363');
insert into contacts(stu_id,tel)
values(5, '2467457');
insert into contacts(stu_id,tel)
values(9999, '2467457');
-- 取消外键约束
alter table contacts
drop foreign key contacts_ibfk_1;
1.5.8 *) 创建 分类表和商品表
-- 删除数据库 jtds
drop database if exists jtds;
-- 重新创建数据库 jtds
create database jtds charset utf8;
-- 进入 jtds 库
use jtds;
-- 新建分类表 tb_item_cat
create table tb_item_cat(
id bigint primary key auto_increment comment '自增主键',
parent_id bigint comment '父分类',
name varchar(50) comment '分类名称',
status tinyint default 1 comment '状态码',
sort_order int comment '排序顺序号',
is_parent tinyint default 1 comment '是否父分类',
created datetime comment '创建时间',
updated datetime comment '修改时间',
foreign key(parent_id) references tb_item_cat(id)
)engine=innodb charset=utf8;
show create table tb_item_cat;
create table tb_item(
id bigint primary key auto_increment comment '主键',
cid bigint not null comment '分类id',
brand varchar(50) comment '品牌',
model varchar(50) comment '型号',
title varchar(100) comment '名称',
sell_point varchar(500) comment '卖点',
price bigint comment '价格*100',
num int comment '库存数量',
barcode varchar(30) comment '条码',
image varchar(500) comment '图片路径',
status tinyint default 2 comment '状态码',
created datetime comment '创建时间',
updated datetime comment '修改时间',
foreign key(cid) references tb_item_cat(id)
)engine=innodb charset=utf8;
作业
================================================
4.2.2 商品描述表(tb_item_desc)
item_id 外键引用商品主键
timestamp 修改成 datetime
4.2.1 商品规格参数表(tb_item_param)
Item_cat_id 分类id引用分类表主键
timestamp 修改成 datetime
4.2.1 用户表(tb_user)
timestamp 修改成 datetime
day03
回顾
mysql mariadb
客户端连接
mysql -hxxx.xx.xx.xxx -P3306 -uroot -p
客户端退出
exit 或 \q
显示所有数据库
show databases;
show schemas;
创建数据库
create database db1
charset utf8;
删除数据库
drop database db1
drop database if exists db1
查看表
show tables;
desc tb1; -- 查看 tb1 的表结构
show create table tb1\G -- 查看建表语句
表的增删改查
创建
create table tb1(
id int primary key auto_increment,
name varchar(20) not null,
num int not null unique,
xid int,
foreign key(xid) references tb2(id)
)engine=innodb charset=utf8;
修改
alter table tb1
add gender char(1) after name;
alter table tb1
modify num int null;
alter table tb1
modify id int;
alter table tb1
modify id int auto_increment;
alter table tb1
drop primary key;
alter table tb1
drop foreign key(外键约束名);
alter table tb1
drop index 约束名;
删除表
drop table if exists tb1;
数据类型
数字
tinyint,int,bigint,decimal(10,2)
字符串
char(6),varchar(6),text
日期
date,time,datetime,timestamp
表总字节量最大 65535
约束
主键、非空、唯一、外键、 检查
默认值
num int default 1
1.6 mysql的 sql_mode 变量
linux 中安装 mysql，sql_mode 变量默认是空值，
表示运行在“不严格”模式，
非空字段会插入 ''
字符串超长会截断插入
...
可以修改这个变量使mysql运行在严格模式
1.6.1 -- 查看 sql_mode 变量的值
show variables like 'sql_mode';
-- variables 变量
1.6.2 -- 修改
set global sql_mode='STRICT_TRANS_TABLES';
1.6.3 -- 退出，重新进入 mysql，再查看变量
show variables like 'sql_mode';
1.7 sql 补充
========================================
* structured query language
结构化查询语言
* sql 标准语法
1.7.1 * 个数据库厂商都有自己的扩展语法
*) mysql 扩展
*) oracle plsql
*) sql server t-sql
1.7.2 * sql 分类
*) DDL - 定义语言,建库建表修改表...
*) DML - 数据操作语言，增删改
*) DQL - 数据查询语言，select
1.8 Mysql操作表数据的语法
1.8.1 插入数据 insert
======================================================
* insert into tb1 values(5, 'abc');
全部字段按字段顺序插入值
* insert into tb1(gender, name) values('M', '张三');
向指定的字段插入值
* insert into tb1(gender, name)
values('M', '张三'),
('F', '李四'),
('M', '王五');
向表中一次插入多条数据(非标准sql)
* insert into tb1 select * from tb2
insert into tb1(name, gender) select name, gender from tb2;
向 tb1 插入 tb2 表中的数据
1.8.2 修改数据 update
======================================================
* update tb1 set name='abc', age=23, gender=null
where id=43;
1.8.3 删除数据 delete
======================================================
* delete from tb1 where ... -- id=10
1.8.4 查询数据 select
=============================================
* where 子句 -- 条件语句
= 等
<> 不等
> 大于
< 小于
>= 大于等于
<= 小于等于
between 小值 and 大值 范围
in 指定几个固定取值 -- in(5,6,8)
like 模糊查询 通常只查字符串
% －匹配0 到多个任意字符
_ －匹配单个任意字符
escape '\' ： 指定转移运算符,也就是java中的转移符
\_ 普通下划线字符
\% 普通百分号字符
is null -- 是空
not -- 不
-------------------------------
not between xxx and zzz -- 不在xxx到zzz范围内
not in(x,a.xx,zz) -- 不包含括号内的内容。
is not null -- 非空
and -- 且，与
or -- 或
107x9706
1.9 Mysql-day03练习
==============================================
1.9.1 1. 商品描述表 tb_item_desc
drop table if exists tb_item_desc;
create table tb_item_desc(
item_id bigint primary key comment '引用商品id',
item_desc text comment '描述',
created datetime comment '创建时间',
updated datetime comment '修改时间',
foreign key(item_id) references tb_item(id) -- references 参考
)engine=innodb charset=utf8;
1.9.2 2. 规格参数模板表 tb_item_param
drop table if exists tb_item_param;
create table tb_item_param(
id bigint primary key auto_increment comment '自增主键',
Item_cat_id bigint not null comment '分类id',
param_data text comment '规格参数模板',
created datetime comment '创建时间',
updated datetime comment '修改时间',
foreign key(Item_cat_id) references tb_item_cat(id)
)engine=innodb charset=utf8;
1.9.3 3. 具体商品的规格参数表 tb_item_param_item
drop table if exists tb_item_param_item;
create table tb_item_param_item(
id bigint primary key auto_increment comment '自增主键',
item_id bigint not null comment '商品id',
param_data text comment '规格参数',
created datetime comment '创建时间',
updated datetime comment '修改时间',
foreign key(item_id) references tb_item(id)
)engine=innodb charset=utf8;
1.9.4 4. 用户表 tb_user
drop table if exists tb_user;
create table tb_user(
id bigint primary key auto_increment comment '自增主键',
username varchar(50) not null unique comment '用户名',
password varchar(32) not null comment '密码',
phone varchar(20) unique comment '电话',
email varchar(50) unique comment '邮箱',
created datetime comment '创建时间',
updated datetime comment '修改时间'
)engine=innodb charset=utf8;
1.9.5 5. 新建数据库
create database stu charset utf8;
use stu;
1.9.6 6. 新建学生表 students
drop table if exists students;
create table students(
id int primary key auto_increment,
name varchar(20),
gender char(1),
birth date
);
1.9.7 7. 向学生表插入数据
insert into students
values(null, '张三', 'M', '2017-10-10');
insert into students(birth,name,gender)
values('2013-12-1','李四','F'),
('2013-12-3','王五','M'),
('2013-8-17','赵六','M'),
('2013-9-25','钱七','F');
insert into students(name)
values('张三三'),('李四四');
select * from students;
1.9.8 8. 同表复制数据
2条->4条->8条->16条->32条
insert into students(name,gender,birth)
select name,gender,birth from students;
1.9.9 9. 从 students 表查询的结果，创建成一张新的表
create table xueshengs
as
select id,gender,name,birth from students;
--查询
desc xueshengs;
select * from xueshengs limit 5;
-- 加主键
alter table xueshengs
add primary key(id);
-- 加自增
alter table xueshengs
modify id int auto_increment;
1.9.10 10. 联系方式表，与学生表一对一关系
一对一关系: 不重复的非空外键
drop table if exists lianxi;
create table lianxi(
xs_id int primary key,
tel varchar(100),
qq varchar(20),
email varchar(50),
foreign key(xs_id) references xueshengs(id)
);
insert into lianxi(xs_id,tel)
values
(1, '215321346234'),
(4, '2456246'),
(5, '8463'),
(8, '5657436235'),
(9, '7456456');
insert into lianxi(xs_id,tel)
values(9999999, '7456456');-- 违反外键约束
select * from lianxi;
-- 先删 lixanxi 表的外键约束
alter table lianxi
drop foreign key lianxi_ibfk_1;
-- 再加 xueshengs 表的自增
alter table xueshengs
id int auto_increment;
-- 再把 lianxi 表的外键加回来
alter table lianxi
add foreign key(xs_id) references xueshengs(id);
1.9.11 11. 修改学生5的年龄
select * from xueshengs where id=5;
update xueshengs set birth='1992-1-1' where id=5;
select * from xueshengs where id=5;
update xueshengs set
name='张三', gender='M', birth='1970-1-1'
where id in(3,6,7,8,11);
select * from xueshengs where id in(3,6,7,8,11);
1.9.12 12.删除名字叫张三的学生
-- 用所有张三的id过滤，删除联系方式
delete from lianxi where xs_id in(
select id from xueshengs where name='张三'
);
delete from xueshengs where name='张三';
1.9.13 13. 添加 banji 表
drop table if exists banji;
create table banji(
id int primary key auto_increment,
name varchar(20)
);
1.9.14 14. 修改 xueshengs 表，添加外键字段 banji_id
alter table xueshengs add banji_id int;
alter table xueshengs
add foreign key(banji_id)
references banji(id);
1.9.15 15. 添加班级“男班”,“女班”
insert into banji(name) values('男班');
insert into banji(name) values('女班');
insert into banji(name) values('妖班');
1.9.16 16. 男女分班
update xueshengs set banji_id=1 where gender='M';
update xueshengs set banji_id=2 where gender='F';
update xueshengs set banji_id=3 where gender is null;
select
x.id,x.name,
b.id banid,b.name banname
from
xueshengs x, banji b
where
x.banji_id=b.id
limit 20;
1.9.17 17. 创建课程表 kecheng，
与学生多对多关系中间表 xs_kc_link
drop table if exists kecheng;
create table kecheng(
id int primary key auto_increment,
name varchar(20)
);
-- 多对多关系中间表
drop table if exists xs_kc_link;
create table xs_kc_link(
xs_id int not null,
kc_id int not null,
foreign key(xs_id) references xueshengs(id),
foreign key(kc_id) references kecheng(id),
unique key(xs_id, kc_id)
);
1.9.18 18. 添加课程，学生选课
insert into kecheng(name)
values('语文'),('数学'),('英语');
insert into xs_kc_link
values(2,1),
(2,2),
(2,3),
(4,1),
(4,3),
(5,2),
(5,3);
1.9.19 19. 查询谁选了什么课程
select
x.name,
k.name kecheng
from
xueshengs x,xs_kc_link l,kecheng k
where
x.id=l.xs_id and
k.id=l.kc_id;
1.9.20 20. 准备数据，公司员工管理系统数据
9.zip 提取到此处 hr.sql
复制到 /home/soft01
窗口右上角在终端打开
ls
cp hr_mysql.sql ~/
ls ~/
cat ~/hr_mysql.sql
mysql>source /home/soft01/hr_mysql.sql
show tables;
desc employees;
desc departments;
select * from employees;
select * from departments;
1.9.21 21.查询 employees 表中
employee_id、first_name、salary
select
employee_id,first_name,salary
from employees;
1.9.22 22.查询 employees 表中
薪水大于等于10000的员工
select
employee_id,first_name,salary
from employees
where salary>=10000;
1.9.23 23. 工种代码（job_id）是 IT_PROG 的员工
select
employee_id,first_name,salary,job_id
from employees
where job_id='IT_PROG';
1.9.24 24. 上司工号（manager_id）是 100 的员工
select
employee_id,first_name,salary,manager_id
from employees
where manager_id=100;
1.9.25 25. 部门编号（department_id）是 30 的员工
select
employee_id,first_name,salary,department_id
from employees
where department_id=30;
1.9.26 26. 不在 50 部门的员工
select
employee_id,first_name,salary,department_id
from employees
where department_id<>50;
1.9.27 27. 薪水范围［5000，8000］（范围：between xxx and yyy）
select
employee_id,first_name,salary
from employees
where salary between 5000 and 8000;
1.9.28 28.
10 , 20 , 60 , 90 四个部门中所有员工 （ in 过滤）
select
employee_id,first_name,salary,department_id
from employees
where department_id in(60,10,90,20);
1.9.29 29. 查询 first_name 中包含 ar 的员工
select
employee_id,first_name,salary
from employees
where first_name like '%ar%';
1.9.30 30. 查询 first_name 第三个字符是 e 的员工
select
employee_id,first_name,salary
from employees
where first_name like '__e%';
1.9.31 31. 查询工种（job_id）以 SA 开头的员工
select
employee_id,first_name,salary,job_id
from employees
where job_id like 'SA%';
作业
================================================
4.2.3 订单表(tb_order)
4.2.4 订单详情表(tb_order_item)
drop table if exists tb_order;
create table tb_order(
order_id varchar(50) primary key,
user_id bigint not null,
add_id bigint not null,
payment decimal(10,2),
payment_type int,
post_fee decimal(10,2),
status int,
payment_time datetime,
consign_time datetime,
end_time datetime,
close_time datetime,
shipping_name varchar(20),
shipping_code varchar(20),
buyer_message varchar(100),
buyer_nick varchar(50),
buyer_rate int,
create_time datetime,
update_time datetime,
foreign key(user_id) references tb_user(id)
)engine=innodb charset=utf8;
drop table if exists tb_order_item;
create table tb_order_item(
id varchar(20) primary key,
item_id bigint not null,
order_id varchar(50) not null,
num int,
title varchar(200),
price decimal(10,2),
total_fee decimal(10,2),
pic_path varchar(200),
created datetime,
updated datetime,
foreign key(item_id) references tb_item(id),
foreign key(order_id) references tb_order(order_id)
)engine=innodb charset=utf8;
Day04
回顾
增删改
insert into tb1(a,b,c,d) values(1,2,3,4);
update tb1 set a=7,b=3,d=9 where ...;
delete from tb1 where ...;
* where 子句
= 等
<> 不等
> 大于
< 小于
>= 大于等于
<= 小于等于
between 小值 and 大值 范围
in 指定几个固定取值
like 模糊查询 通常只查字符串
% －匹配0 到多个任意字符
_ －匹配单个任意字符
escape '\' ： 指定转移运算符
\_ 普通下划线字符
\% 普通百分号字符
is null
not
-------------------------------
not between and
not in
is not null
and
or
1.10 sql语句语法
1.10.1 * order by 子句
排序
select ..
from ..
where ..
order by a 按a字段从小到大
order by a,b 按a字段排，a相等按b排
asc ascend 升序(默认)
desc descend 降序
order by a asc
order by a desc
order by a desc, b asc
1.10.2 * distinct －去除重复数据
select distinct a from ...
查询字段 a 并去除重复数据
select distinct a,b from ...
a,b 组合去除重复
1.10.3 * 字段别名
select
name n,
age a,
gender as g
from ...
* mysql 中 where 子句不能使用字段别名
1.11 mysql 函数
=========================================================================
1.11.1 字符串
char_length('a中') - 字符数
length('a中') - 字节数
concat('a','b','cde','fff') - 字符串连接，其他数据库可用 || 连接字符串，'abc' || 'def'
concat_ws(';','abc','def','ggg') - 用分隔符连接字符串
instr('abcdefgdef','def') - 返回第一个子串的位置，从1开始，找不到返回0
locate('abc', '---abc---abc---abc-') - 返回第一个子串的位置，从1开始，找不到返回0
locate('abc', '---abc---abc---abc-',5) - 从指定位置向后找
insert('abcdefghijkl',2, 11, '---') - 用子串取代从2位置开始的11个字符
lower('AdFfLJf') - 变为小写
upper('AdFfLJf') - 变为大写
left('AdFfLJf',3) - 返回最左边的三个字符
right('AdFfLJf',3) - 返回最右边的三个字符
lpad('abc', 8, '*') - 左侧填充，指定长度比源字符串少，相当于left
rpad('abc', 8, '*') - 右侧填充，指定长度比源字符串少，相当于right
trim(' a bc ') - 去除两端空格
substring('abcdefghijklmn', 3) – 截取从3位置开始的所有字符
substring('abcdefghijklmn', 3, 6) – 截取从3位置开始的6个字符
repeat('abc', 3) - 重复三遍abc
REPLACE('Hello MySql','My','Your') - 子串替换
REVERSE('Hello') - 翻转字符串
SPACE(10) - 返回10个空格
1.11.2 数字
floor(3.94) - 舍掉小数
format(391.536, 2) - 数字格式化为字符串，###,###.###，四舍五入，第二个参数为小数位数
round(673.4974) - 四舍五入
round(673.4974, 2) - 四舍五入到小数点后两位
round(673.4974, -2) - 四舍五入到百
TRUNCATE(234,31, 1) - 舍去至小数点后1位
1.11.3 日期
NOW() 返回当前的日期和时间
CURDATE() 返回当前的日期
CURTIME() 返回当前的时间
DATE(时间) 提取日期或日期/时间表达式的日期部分
TIME(时间) 提取日期或日期/时间表达式的时间部分
EXTRACT(字段 From 日期) 返回日期/时间按的单独部分
1.11.4 字段的合法值：
MICROSECOND microsecond
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH
DATE_ADD(日期, INTERVAL 数量 字段) 给日期添加指定的时间间隔
字段的合法值同上
DATE_SUB(日期, INTERVAL 数量 字段) 从日期减去指定的时间间隔
DATEDIFF(日期1, 日期2) 返回两个日期之间的天数
DATE_FORMAT(日期, 格式) 用不同的格式显示日期/时间
格式字符： %Y-%m-%d %H:%i:%s
%d/%m/%Y
%Y年%m月%d日
%a 缩写星期名
%b 缩写月名
%c 月，数值
%D 带有英文前缀的月中的天
%d 月的天，数值(00-31)
%e 月的天，数值(0-31)
%f 微秒
%H 小时 (00-23)
%h 小时 (01-12)
%I 小时 (01-12)
%i 分钟，数值(00-59)
%j 年的天 (001-366)
%k 小时 (0-23)
%l 小时 (1-12)
%M 月名
%m 月，数值(00-12)
%p AM 或 PM
%r 时间，12-小时（hh:mm:ss AM 或 PM）
%S 秒(00-59)
%s 秒(00-59)
%T 时间, 24-小时 (hh:mm:ss)
%U 周 (00-53) 星期日是一周的第一天
%u 周 (00-53) 星期一是一周的第一天
%V 周 (01-53) 星期日是一周的第一天，与 %X 使用
%v 周 (01-53) 星期一是一周的第一天，与 %x 使用
%W 星期名
%w 周的天 （0=星期日, 6=星期六）
%X 年，其中的星期日是周的第一天，4 位，与 %V 使用
%x 年，其中的星期一是周的第一天，4 位，与 %v 使用
%Y 年，4 位
%y 年，2 位
LAST_DAY(日期) - 返回当月最后一天
NULL 相关
IFNULL(数据1,数据2) - 数据1是null返回数据2；不是null返回数据1
coalesce(数据1,数据2,......) - 从左向右第一个不是null的数据
加密
md5()
sha()
1.12 多行函数
=============================================
1.12.1 * 多行数据交给函数处理，产生一个计算结果
count() 计数，数量
max() 最大值
min() 自小值
avg() 平均
sum() 求合
* 不能直接与普通字段一起查询
1.13 Mysql-day04练习
============================================================
1.13.1 1. 销售提成 commission_pct 不是null 的员工
use hr;
select
employee_id,first_name,
salary,commission_pct,job_id
from employees
where commission_pct is not null;
1.13.2 2. 没有上司的人 manager_id
select
employee_id,first_name,salary,manager_id
from employees
where manager_id is null;
1.13.3 3. 没有部门的人 department_id
select
employee_id,first_name,salary,department_id
from employees
where department_id is null;
1.13.4 4. 部门 50 中，薪水大于等于 5000 的员工
select
employee_id, first_name,salary,
department_id
from employees
where
department_id=50 and
salary>=5000;
1.13.5 5. 姓名中有 en ，且在80 部门中的员工
select
employee_id,
first_name,
last_name,
salary,
department_id
from
employees
where
(first_name like '%en%' or last_name like '%en%') and
department_id=80;
--first_name是名，last_name是姓
1.13.6 6. 部门 90 中所有员工 ，和工种后缀是 CLERK 的员工
select
employee_id,
first_name,
salary,
department_id,
job_id
from
employees
where
department_id=90 or job_id like '%CLERK';
1.13.7 7. 薪水从小到大排列
select
employee_id,first_name,salary
from employees
order by salary;
1.13.8 8. 按部门降序排列，相同部门中，按薪水升序排列
select
employee_id,first_name,salary,department_id
from employees
order by department_id desc, salary asc;
1.13.9 9. 薪水大于等于 10000 的员工，按姓名排序
select
employee_id,first_name,salary
from employees
where salary>=10000
order by first_name,last_name
1.13.10 10. 查询 员工 first_name 是 Bruce 的员工
select
employee_id,first_name,salary
from employees
where first_name='Bruce';
1.13.11 11. 查询 薪水大于10000，且在100部门的 员工
select
employee_id,first_name,salary
from employees
where salary>10000 and department_id=100;
1.13.12 12. 查询 薪水小于3000的员工，和100部门所有的员工
select
employee_id,first_name,salary
from employees
where salary<3000 or department_id=100;
1.13.13 13. 查询员工编号为 100,150,200,250 的员工
select
employee_id,first_name,salary
from employees
where employee_id in(100,150,200,250);
1.13.14 14. 工资不在 3000-10000 范围内的员工
select
employee_id,first_name,salary
from employees
where salary not between 3000 and 10000;
1.13.15 15. 工作代码 job_id 不是
IT_PROG、SH_CLERK、PU_CLERK、ST_CLERK
select
employee_id,first_name,salary,job_id
from employees
where job_id not in('IT_PROG','SH_CLERK','PU_CLERK','ST_CLERK');
1.13.16 16. 查询所有员工，按部门编号升序排列
相同部门，按first_name升序排列
select
employee_id,first_name,salary,job_id,department_id
from employees
order by department_id,first_name;
1.13.17 17. 查询 50 和 80 部门的员工，
按工资降序排列，工资相同按工种代码排列
select
employee_id,first_name,salary,job_id,department_id
from employees
where department_id in(50, 80)
order by salary desc, job_id;
1.13.18 18. 部门编号
select distinct department_id
from employees
where department_id is not null;
1.13.19 19. 工种代码
select distinct job_id
from employees order by job_id;
1.13.20 20. 主管id
select distinct manager_id
from employees
where manager_id is not null;
1.13.21 22. 部门中的主管id
select distinct department_id,manager_id
from employees
where department_id is not null
and manager_id is not null
order by department_id,manager_id;
1.13.22 23. 查询姓名和年薪
select
employee_id,
concat(first_name, ' ', last_name) name,
salary*12 anu_sal
from
employees
where salary*12>100000;
1.13.23 24. 字符串函数测试
select char_length('a中');
select length('a中');
use stu;
select char_length(name), length(name) from kecheng;
select instr('abcdefgdef','def');-- 子串的起始位置
select instr('abcdefgdef','xxx');-- 找不到得到 0
-- first_name 第三四个字符是 'en'
select
employee_id, first_name,salary
from employees
where instr(first_name, 'en')=3;
1.13.24 25. 电话号码中的 "44" 替换为 "88"
select
employee_id,first_name,salary,
phone_number,
replace(phone_number,'44','88')
from
employees;
1.13.25 26. email 中去掉第一个字符，与 last_name 不相同的员工
select
employee_id,first_name,last_name,salary,
email,
substring(email, 2)
from
employees
where
last_name<>substring(email, 2);
1.13.26 27. first_name, last_name 长度相同
select
employee_id,first_name,last_name
from employees
where
char_length(first_name)=char_length(last_name);
1.13.27 28. first_name, last_name 拼接显示，空格居中
select
concat(lpad(first_name,20,' '),' ',last_name) name
from employees;
1.13.28 29. 数字函数测试
select floor(3.94); -- 向下
select floor(-3.94); -- 向下
select format(574346391.536, 2); -- 数字格式化为字符串，###,###.###，四舍五入，第二个参数为小数位数
select round(673.4974); -- 四舍五入
select round(673.4974, 2); -- 四舍五入到小数点后两位
select round(673.4974, -2); -- 四舍五入到百
select TRUNCATE(234.91, 1); -- 舍去至小数点后1位
select TRUNCATE(234.99, 1);
1.13.29 30. 涨工资 11.37%，向上取整到 10 位
select
employee_id,first_name,salary,
truncate(salary*1.1137+10, -1)
from
employees;
346469.345
346460.345
1.13.30 31. 测试日期函数
select now(); -- 当前日期时间
select curdate(); -- 当前日期
select curtime(); -- 当时时间
use stu;
insert into xueshengs(name, birth)
values('aaa', now());
insert into xueshengs(name, birth)
values('aaa', curdate());
select * from xueshengs
order by id desc limit 1;
select date(now()); -- 取日期部分
select time(now()); -- 取时间部分
select extract(year from now()); -- 取年
select extract(month from now());-- 月
select extract(day from now()); -- 日
select date_add(now(), interval 10 year); -- 加10年
select date_add(now(), interval -24 month); -- 减 24 个月
select datediff(now(), '1995-6-12');
select date_format(now(),'%Y-%m-%d %H:%i:%s');
select date_format(now(),'%Y-%m-%d %H:%i:%S');
select date_format(now(),'%d/%m/%Y');
select date_format(now(),'%Y年%m月%d号');
select last_day(now());
1.13.31 32. 入职20年以上的员工
use hr;
select
employee_id, first_name, salary,
hire_date
from
employees
where
datediff(now(), hire_date)>20*365+5;
-- hire_date<date_add(now(),interval -20 year);
1.13.32 33. 1997年上半年入职的人
select
employee_id,first_name,salary,
hire_date
from
employees
where
extract(year from hire_date)=1997 and
extract(month from hire_date)<7;
-- hire_date
-- between '1997-1-1' and '1997-6-30';
1.13.33 34. 不论哪一年，所有1月份入职的人
select
employee_id,first_name,salary,
hire_date
from
employees
where
extract(month from hire_date)=1;
1.13.34 35. null 函数测试
select ifnull(345, 678);
select ifnull(null, 678);
select coalesce(null,45,null,765);
select coalesce(null,null,234,765);
1.13.35 36. 求年薪，有提成，年薪*(1+提成)
select
employee_id,first_name,salary,
commission_pct,
salary*12*(1+ifnull(commission_pct,0)) anu_sal
from employees;
1.13.36 37. 加密函数测试
select md5('abc'); -- 更常用
select sha('abc'); -- 更安全的加密方式
1.13.37 38. 多行函数测试
select sum(salary) from employees;
select first_name, sum(salary)
from employees;
select
sum(salary), -- 求合
avg(salary), -- 求平均
count(salary), -- 数量
max(salary), -- 最大值
min(salary) -- 最小值
from employees;
1.13.38 39. 测试 count 对 null 值的处理
-- 忽略 null 值
select count(commission_pct)
from employees;
-- 求平均，忽略null值，对35人求平均
select avg(commission_pct)
from employees;
select sum(commission_pct)/count(commission_pct)
from employees;
-- 107人求平均
select avg(ifnull(commission_pct,0))
from employees;
1.13.39 40. 用 count() 求数据行数
select count(*) from employees;
select count(1) from employees; -- 效率更高
1.13.40 41. 1997 年入职的人数
select count(1)
from employees
where
extract(year from hire_date)=1997;
1.13.41 40. 50 部门的人数
select count(1)
from employees
where
department_id=50;
1.13.42 41. 最后进公司的人的入职时间
select max(hire_date)
from employees;
1.13.43 42. 122号员工是个主管，他的手下有多少人
select count(1)
from employees
where manager_id=122;
作业
==================================================
4.2.1 收藏夹表(tb_collect)
4.2.2 购物车表(tb_cart)
4.2.5 订单物流表(tb_order_shipping)
查询 first_name 以 es 结尾的人
first_name 和 last_name 首字母相同的人
做文员的员工人数（job_id 中 含有 CLERK 的）
销售人员 job_id: SA_XXXXX 的最高薪水
最早和最晚入职时间
drop table if exists tb_order_shipping;
create table tb_order_shipping(
order_id varchar(50) primary key,
receiver_name varchar(20),
receiver_phone varchar(20),
receiver_mobile varchar(30),
receiver_state varchar(10),
receiver_city varchar(10),
receiver_district varchar(20),
receiver_address varchar(200),
receiver_zip varchar(6),
cereated datetime,
updated datetime,
foreign key(order_id) references tb_order(order_id)
)engine=innodb charset=utf8;
drop table if exists tb_collect;
create table tb_collect(
id bigint primary key auto_increment,
user_id bigint not null,
item_id bigint not null,
item_title varchar(100),
item_price bigint,
item_image varchar(200),
item_param_data varchar(200),
status tinyint default 1,
created datetime,
updated datetime,
foreign key(user_id) references tb_user(id),
foreign key(item_id) references tb_item(id)
)engine=innodb charset=utf8;
drop table if exists tb_cart;
create table tb_cart(
id bigint primary key auto_increment,
user_id bigint not null,
item_id bigint not null,
num int default 1,
item_title varchar(100),
item_image varchar(200),
item_price bigint,
created datetime,
updated datetime,
foreign key(user_id) references tb_user(id),
foreign key(item_id) references tb_item(id)
)engine=innodb charset=utf8;
查询 first_name 以 es 结尾的人
select
employee_id,first_name,salary
from
employees
where
first_name like '%es';
first_name 和 last_name 首字母相同的人
select
employee_id,first_name, last_name,salary
from
employees
where
left(first_name,1)=left(last_name,1);
做文员的员工人数（job_id 中 含有 CLERK 的）
select count(*)
from employees
where job_id like '%CLERK%';
销售人员 job_id: SA_XXXXX 的最高薪水
select max(salary)
from employees
where job_id like 'SA\_%';
最早和最晚入职时间
select min(hire_date), max(hire_date)
from employees;
day05
回顾
* where
=
<>
> >= < <=
like % _
between ... and ...
in
is null
not between ... and ...
not in
is not null
* order by
order by a,b
order by a desc, b asc
* distinct
去除重复
select distinct a,b ...
* 函数
字符串、数字、日期、加密
* 多行函数
count(),max(),min(),avg(),sum()
1.14 sql子句
1.14.1 group by 分组子句
========================================
* 求多行函数时， 用 group by 分组计算
1 张三 30
2 李四 30 30部门 3 人
3 王五 30
4 赵六 70
5 钱七 70 70部门 2 人
select department_id,count(*) from emp
group by department_id
1.14.2 having 过滤子句
===============================================
* 多行函数分组计算，
对多行函数结果进行过滤，
不能用 where，而使用 having 代替
*) where 过滤普通条件
*) having 过滤多行函数结果
1.14.3 子查询
================================================
1.14.3.1 * 用一个查询的结果，作为另一个查询的过滤条件
select id,name,sal...
from ...
where sal=(select min(sal) from...);
1.14.3.2 * 单值子查询
=
<>
>
1.14.3.3 * 多值子查询
In()
> all(1,2,3,4) 大于最大值
> any(1,2,3,4) 大于最小值
1.15 行内视图
==============================================
* 从一个查询的查询结果再查询
select ... from (select ... from...) tb
1.16 多表连接查询
===============================================
1.16.1 * 用外键将多张表连接成一张大表
select
a.xx,a.xx,a.xx,
b.xx,b.xx
from a, b
where
a.id = b.xid
1.16.2 * 自连接
*)外键与本表中的主键连接
*)将一张表看做是两张表
1.16.3 * 标准 sql 连接语法
上面非标准语法，多数数据库都支持
select ...
from
a inner join b on a.id=b.xid
inner join c on c.id=a.xid
[inner] join d on ...
[inner] join e on ...
1.16.4 * 左外链接、右外链接
a left [outer] join b on ...
将左表 a 中连接条件之外的数据也查出来
a right [outer] join b on ...
将右表 b 中连接条件之外的数据也查出来
1.17 Mysql-day05练习
======================================
1.17.1 1. 每个部门的人数
select department_id,count(1) c
from employees
where department_id is not null
group by department_id
order by c;
1.17.2 2. 每个部门中，每个主管的手下人数
部门 主管id 手下
1 1 3
1 2 5
1 3 2
2 4 6
2 5 1
select department_id,manager_id,count(1) c
from employees
where department_id is not null and
manager_id is not null
group by department_id,manager_id
order by
department_id,c;
1.17.3 3. 每种工作的平均工资
select job_id,avg(salary)
from employees
group by job_id;
1.17.4 4. 每年的入职人数
select extract(year from hire_date) year,count(*) c
from employees
group by year //以年为单位分组
order by c; //排序
1.17.5 5. 只有一个人的部门
select department_id,count(*) c
from employees
where department_id is not null
group by department_id
having c=1; -- 对多行函数结果进行过滤
1.17.6 6. 拿最低工资的员工信息
select
employee_id,first_name,salary
from employees
where salary=
(select min(salary) from employees);
1.17.7 7. 只有一个下属的主管信息
100 张三
110 李四
120 王五 100
130 赵六 110
select
employee_id,first_name,salary
from employees
where employee_id in (
select manager_id
from employees
where manager_id is not null
group by manager_id
having count(*)=1;
);
select manager_id
from employees
where manager_id is not null
group by manager_id
having count(*)=1;
1.17.8 8. 平均工资最高的部门编号
1.分组求平均
2.得到最高平均工资
1.
select department_id, avg(salary)
from employees
where department_id is not null
group by department_id;
2.
select max(avg)
from (
select department_id, avg(salary) avg
from employees
where department_id is not null
group by department_id
) a;
select department_id, avg(salary) avg
from employees
where department_id is not null
group by department_id
order by avg desc
limit 1;
1.17.9 9. 下属人数最多的人，查询其个人信息
1. 得到下属最多的人他的 id
2. 用 id 过滤查询他的个人信息
1.
select manager_id
from employees
where manager_id is not null
group by manager_id
order by count(*) desc
limit 1;
2.
select employee_id,first_name,salary
from employees
where employee_id=(
select manager_id
from employees
where manager_id is not null
group by manager_id
order by count(*) desc
limit 1
);
1.17.10 10. 拿最低工资的人的信息
select
employee_id,first_name,salary
from employees
where salary=(
select min(salary)
from employees
);
1.17.11 11. 最后入职的员工信息
select
employee_id,first_name,salary,hire_date
from employees
where hire_date=(
select max(hire_date) from employees
);
1.17.12 12. 工资多于平均工资的员工信息
select
employee_id,first_name,salary
from employees
where salary>(
select avg(salary) from employees
);
1.17.13 13. 部门表
select * from departments;
1.17.14 14. 查询员工信息，部门名称
select
e.employee_id,e.first_name,e.salary,
d.department_id,d.department_name
from employees e, departments d
where e.department_id=d.department_id;
1.17.15 15. 查询部门信息，和部门经理的信息
select
d.department_id,d.department_name,d.manager_id,
e.employee_id,e.first_name,e.salary
from departments d, employees e
where d.manager_id=e.employee_id;
1.17.16 17. 地区表
select * from locations;
1.17.17 18. 员工信息，部门名称，所在城市
1 张三 7000 销售 纽约
select
e.employee_id,e.first_name,e.salary,
d.department_name,
l.city
from
employees e,
departments d,
locations l
where
e.department_id=d.department_id and
d.location_id=l.location_id;
1.17.18 19. Seattle 市所有的员工信息
1 张三 7000
select
e.employee_id,e.first_name,e.salary,
d.department_name
from
employees e,
departments d,
locations l
where
e.department_id=d.department_id and
d.location_id=l.location_id and
l.city='Seattle';
1.17.19 20. 按城市分组，计算每个城市的员工数量
Beijing 12
Seattle 18
...
select
l.city, count(*) c
from
employees e,
departments d,
locations l
where
e.department_id=d.department_id and
d.location_id=l.location_id
group by
l.city
order by c;
1.17.20 22. 查询员工信息和他的主管姓名
1 张三
2 李四
3 王五 1 张三
4 赵六 1 张三
5 钱七 2 李四
select
e1.employee_id,e1.first_name,e1.salary,
e2.first_name mgr_name
from
employees e1,
employees e2
where
e1.manager_id=e2.employee_id;
1.17.21 23. 员工信息，员工主管名字，部门名
1 张三
2 李四
3 王五 1 张三 销售
4 赵六 1 张三 销售
5 钱七 2 李四 IT
select
e1.employee_id,e1.first_name,e1.salary,
e2.first_name mgr_name,
d.department_name
from
employees e1,
employees e2,
departments d
where
e1.manager_id=e2.employee_id and
e1.department_id=d.department_id;
1.17.22 24. 员工信息，部门名，和部门经理
select
e1.employee_id,e1.first_name,e1.salary,
d.department_name,
e2.first_name mgr_name
from
employees e1,
departments d,
employees e2
where
e1.department_id=d.department_id and
d.manager_id=e2.employee_id;
1.17.23 25. 用户信息，工作岗位，直接上司名，部门名，城市，国家，州
select * from jobs;
select * from locations;
select * from countries;
select * from regions;
select
e1.employee_id,e1.first_name,e1.salary,
j.job_title,
e2.first_name mgr_name,
d.department_name,
l.city,
c.country_name,
r.region_name
from
employees e1
join jobs j on e1.job_id=j.job_id
join employees e2 on e1.manager_id=e2.employee_id
join departments d on e1.department_id=d.department_id
join locations l on d.location_id=l.location_id
join countries c on l.country_id=c.country_id
join regions r on c.region_id=r.region_id;
1.17.24 26. 员工和他所在部门名，没有部门显示 null
select
e.employee_id,e.first_name,e.salary,
d.department_name
from
employees e
left join departments d
on e.department_id=d.department_id;
作业
==================================================
4.2.14 用户地址表 (tb_address)
Is_default 后面加两个字段
user_id bigint 外键
status tinyint
4.2.6 内容表(tb_content)
4.2.7 内容分类表(tb_content_category)
工资多于工种 IT_PROG 平均工资的员工
select
employee_id,first_name,salary
from
employees
where
salary>(select avg(salary) from employees where job_id='IT_PROG');
平均工资最高的工种, 显示工作全名
select
job_id,job_title
from
jobs
where
job_id=(
select job_id
from employees
group by job_id
order by avg(salary) desc
limit 1
);
------------
select
e.job_id,j.job_title,avg(salary) avg
from
employees e join jobs j on e.job_id=j.job_id
group by e.job_id,j.job_title
order by avg desc
limit 1;
每个部门拿最高工资的人
1.每个部门的最高工资
2.再用这些工资过滤
select
employee_id,first_name,salary,department_id
from employees
where (department_id,salary)=(60, 9000);
select
employee_id,first_name,salary,department_id
from employees
where (department_id,salary) in(
select
department_id, max(salary)
from employees
where department_id is not null
group by department_id
);
每年第一个入职的人
1.每年最小入职时间
2.用这些时间过滤
select
employee_id,first_name,salary,hire_date
from employees
where hire_date in(
select min(hire_date)
from employees
group by extract(year from hire_date)
);
外连接，查询全部27个部门，有部门经理显示经理名
select
d.department_id,d.department_name,
e.first_name
from
departments d left join employees e
on d.manager_id=e.employee_id;
day06
1.18 事务
1.18.1 概述与特征
*) 事务是数据操作的最小单元
*) 多个数据增删改操作，完成的一项业务处理
*) 如果事务事务成功，其中每一项操作都生效；如果事务事务失败，其中每一项操作都失败。
*) 数据库数据操作，以事务为一个最小操作单元，
不应该以一个sql语句为一个操作单元；
要么整个事务成功，要么整个事务失败
*) 事务的原子性
*) 在当前事务中对数据的修改，只对当前连接可见
*) ACID
------------
A - 原子性 Atomic
C - 一致性 Consistency
转账前 a+b = 100
转帐后 a+b = 100
I - 隔离性 Isolation
一个事物进行中时，
另一事物不能操作数据
D - 持久性 Durancy
提交事务之后，
数据持久生效
1.18.2 事务操作
1.18.2.1 *)开始事务
start transaction;
set autocommit=0;
1.18.2.2 *)提交事务
commit;
1.18.2.3 *)回滚事务
rollback;
*) mysql 默认不开始事务，需要手动启动事务
*)每一步数据修改都直接生效
1.18.3 查看事务是否开启
show variables like 'autocommit';
自动提交开启，没有事务
自动提交关闭，开始事务
1.18.4 innodb 提供行级锁
innodb这个引擎提供事物行级锁
一个事务修改一行数据未提交时，
该行数据会被锁定，
不允许其他事务修改
1.18.5 事务隔离级别
set tx_isolation='READ-UNCOMMITTED'; -- 脏读
set tx_isolation='read-committed'; -- 幻读
set tx_isolation='repeatable-read';
set tx_isolation='serializable';
1.18.5.1 1) 脏读 READ-UNCOMMITTED 读未提交数据
一个事务修改一半的数据，被其他事务读取
1.18.5.2 2) 不允许脏读 read-committed 读提交数据
只能读取其他事务已提交的数据
虚读 update
幻读 insert,delete
1.18.5.3 3) 可重复读 (mysql 默认) repeatable-read
事务过程中，前后读取的数据一致，
即使过程中数据已被其他事务修改，也读取旧的数据
1.18.5.4 4) 串行化 serializable
所有事务排队依次执行，不能同时执行
1.18.6 查看隔离级别
show variables like 'tx%';
1.19 视图 view
============================================
* 将一个查询的查询结果当做表
* 可以从这个虚拟的表查询
drop view if exists v1;
create [or replace] view v1
as
selet ...;
* 视图不是表，不保存数据，
从视图查询时，本质是从查询结果再查询(两层查询)
1.19.1 * 查看视图
show tables;
desc v1;
show create view v1;
* 为什么使用视图
*)视图在大的项目中非常常用
1.19.2 *)简化查询
select
from
a join b .. join c...join d...join e...
select .. from v1;
1.19.3 *)安全
创建视图给用户访问，
隐藏真实数据表
*)一般只从视图查询，不对视图做增删改操作
1.20 索引
==============================================
* 数据存储位置目录
*)哈希索引
*)红黑树索引
* 提高查询效率，首先考虑创建索引
* where name='abc'
name数据创建索引
* where name='abc' and birth='1993-5-3'
name和birth两个字段创建索引
对 where name='abc' 单字段过滤也有效
但对 where birth='1993-5-3' 单字段过滤无效
1.20.1 * 创建索引
create index index_name on tb1(name);
create index first_name_index
on employees(first_name);
create index job_dept_index
on employees(job_id, department_id);
1.21 数据备份和恢复
================================================
1.21.1 * 导出库表及数据
语法：
mysqldump -uroot –p --default-character-set=utf8 库名>/home/soft01/hr.sql
案例：
mysqldump -uroot -p --default-character-set=utf8 stu>/home/soft01/stu.sql
cat ~/stu.sql |more -- 分页查询
1.21.2 * 将备份数据重新导入数据库
语法：
mysql -uroot -p --default-character-set=utf8 库名<文件路径
案例：
mysql -uroot -p --default-character-set=utf8 stu2</home/soft01/stu.sql
drop database if exists jtds;
create database jtds charset=utf8;
将 jtds.sql 导入 jtds 库
1.22 limit分页查询
=================================================
* limit 5
前5条
* limit 0,10
从第一条 开始的10条
* limit 10,10
从第十一条开始的10条
* limit 20,10
从第二十一条开始的10条
* 查询第 page 页，每页10条
第一个参数： (page-1)*10
第二个参数： 10
*公式：limit (page-1)*pageCount,count
查询第page页，每页count条
1.23 Mysql-day06练习
==========================================
1.23.1 1. 测试事务操作
start transaction;
insert into regions(region_name) values('aaa');
insert into regions(region_name) values('bbb');
insert into regions(region_name) values('ccc');
select * from regions;
-- 新开终端，查询 regions 表
-- 第一个客户端
commit;
-- 第二个客户端再查询
-- 第一个客户端
start transaction;
insert into regions(region_name) values('aaa');
insert into regions(region_name) values('bbb');
insert into regions(region_name) values('ccc');
select * from regions;
rollback;
select * from regions;
-- 第一个客户端
start transaction;
update regions set region_name='南极'
where region_id=5;
select * from regions;
-- 第二个客户端
start transaction;
update regions set region_name='北极' where region_id=5;
1.23.2 2. 测试隔离级别
1) 脏读
两个客户端中都设置
set tx_isolation='READ-UNCOMMITTED'; -- 脏数据
-- 第一个客户端
start transaction;
insert into regions(region_name) values('eee');
update regions set region_name='月球'
where region_id=5;
-- 第二个客户端
select * from regions;
-- 第一个客户端
rollback;
2) 幻读
两个客户端中都设置
set tx_isolation='read-committed'; -- 读提交数据、幻读
-- 第一个客户端
rollback;
start transaction;
insert into regions(region_name) values('5555555');
update regions set region_name='水星'
where region_id=5;
-- 第二个客户端
rollback;
start transaction;
select * from regions;
-- 第一个客户端
commit;
-- 第二个客户端
select * from regions; -- 看到第一个客户端提交的数据
3) 可重复读
两个客户端中都设置
set tx_isolation='repeatable-read'; -- 读提交数据、幻读
-- 第一个客户端
rollback;
start transaction;
insert into regions(region_name) values('888888');
update regions set region_name='土星'
where region_id=5;
-- 第二个客户端
rollback;
start transaction;
select * from regions;
-- 第一个客户端
commit;
-- 第二个客户端
select * from regions; -- 看到第一个客户端提交的数据
1.23.3 3. 视图测试
drop view if exists v1;
create or replace view v1
as
select
employee_id,first_name,salary,department_id
from employees
where (department_id,salary) in(
select
department_id, max(salary)
from employees
where department_id is not null
group by department_id
);
show tables;
select * from v1;
1.23.4 4. 商品表 tb_item
select * from tb_item;
1.23.5 5. 有什么品牌
select distinct brand from tb_item;
1.23.6 6. 最贵商品的品牌、名称、价格
select brand,title,price
from tb_item
order by price desc
limit 1;
1.23.7 7. 分页查看商品
select id,brand,title,price
from tb_item
order by price desc
-- limit 0,10;
-- limit 10,10;
-- limit 20,10;
limit 30,10;
1.23.8 8. 商品名和商品描述
select
title,item_desc
from
tb_item i join tb_item_desc d
on i.id=d.item_id
limit 1;
1.23.9 9. 商品分类表 tb_item_cat
select * from tb_item_cat;
select * from tb_item_cat
where name like '%电脑%' or
name like '%笔记本%' or
name like '%手机%';
1.23.10 10. 查询所有的顶层分类
select * from tb_item_cat
where is_parent=1 and parent_id is null;
1.23.11 11. 查询 161 下的二层分类
select * from tb_item_cat
where parent_id=161 order by sort_order;
1.23.12 12. 查询 162 下的三层分类
select * from tb_item_cat
where parent_id=162 order by sort_order;
1.23.13 13. 用户表
select * from tb_user;
1.23.14 14. 订单表
select * from tb_order;
用户 id 是14的订单
select * from tb_order where user_id=14;
1.23.15 15. 订单编号是 20161001490698615071
查询这个订单的所有商品
select * from tb_order_item
where order_id=20161001490698615071;
1.24 day06作业
===========================================================
查询没中类别的商品数量
查询 类别 163 的商品
查询商品价格不大于100的商品名称列表
查询品牌是联想,且价格在40000以上的商品名称和价格
查询品牌是三木,或价格在10以上的商品名称和价格
查询品牌是三木、广博、齐心的商品名称和价格
查询品牌不是联想、戴尔的商品名称和价格
查找品牌是联想且价格大于10000的电脑名称
查询联想或戴尔的电脑名称列表
查询联想、戴尔、三木的商品名称列表
查询不是戴尔的电脑名称列表
查询所有是记事本的商品品牌、名称和价格
查询品牌是末尾字符是'力'的商品的品牌、名称和价格
名称中有联想字样的商品名称
查询卖点含有'爆款'电脑名称
查询开头字母是A的电脑名称
将地址表中的城市、地区、详细地址组合到一起,
获取订单编号为 20161001490698615071 中的每种商品的标题、单价、数量、总价
统计商品表中各个品牌的商品数量
统计商品表中各个品牌的商品数量,并且按照数量从少到多排序
统计商品表中各个品牌的商品数量,去除品牌为NULL的行,并且按照数量从少到多排序
查询不同品牌中最贵的商品价格
将不同品牌中最贵的商品按照价格降序排序
找出不同品牌中最贵的商品的前三名
查询订购了10000028商品的客户姓名和联系方式
首先查询订单表,根据10000028这个商品编号找到有该商品的订单编号
然后查询订单表,根据上面得到的订单编号找到订购此订单的用户编号
最后查询用户表,根据第二部中查到的用户编号,找到对应的用户名和联系方式
查询没中类别的商品数量
select cid,count(*) from tb_item group by cid
查询 类别 163 的商品
select id,title,price from tb_item where cid=163;
查询商品价格不大于100的商品名称列表
select id,title,price from tb_item where price<100;
查询品牌是联想,且价格在40000以上的商品名称和价格
select id,title,price from tb_item where brand='联想' and price>40000;
查询品牌是三木,或价格在50以下的商品名称和价格
select id,brand,title,price from tb_item where brand='三木' or price<50;
查询品牌是三木、广博、齐心的商品名称和价格
select id,brand,title,price from tb_item where brand in('三木','广博','齐心');
查询品牌不是联想、戴尔的商品名称和价格
select id,brand,title,price from tb_item where brand not in('联想','戴尔');
查找品牌是联想且价格大于10000的电脑名称
select id,brand,title,price from tb_item where brand='联想' and price>10000;
查询联想或戴尔的电脑名称列表
select id,brand,title,price from tb_item where brand='联想' or brand='戴尔';
查询联想、戴尔、三木的商品名称列表
select id,brand,title,price from tb_item where brand in('联想','戴尔','三木');
查询不是戴尔的电脑名称列表
select id,brand,title,price from tb_item where brand<>'戴尔';
查询所有是记事本的商品品牌、名称和价格
select id,brand,title,price from tb_item where title like '%记事本%';
查询品牌是末尾字符是'力'的商品的品牌、名称和价格
select id,brand,title,price from tb_item where brand like '%力';
名称中有联想字样的商品名称
select id,brand,title,price from tb_item where title like '%联想%';
查询卖点含有'爆款'电脑名称
select id,brand,title,price from tb_item where cell_point like '%爆款%';
查询开头字母是A的电脑名称
select id,brand,title,price from tb_item where title like 'A%';
将地址表中的城市、地区、详细地址组合到一起,
select concat(receiver_city, receiver_district,receiver_address) addr from tb_address;
获取订单编号为 20161001490698615071 中的每种商品的标题、单价、数量、总价
select title,price,num,total_fee from tb_order_item where order_id='20161001490698615071';
统计商品表中各个品牌的商品数量
select brand,count(*) from tb_item group by brand;
统计商品表中各个品牌的商品数量,并且按照数量从少到多排序
select brand,count(*) c from tb_item group by brand order by c;
统计商品表中各个品牌的商品数量,去除品牌为NULL的行,并且按照数量从少到多排序
select brand,count(*) c from tb_item where brand is not null group by brand order by c;
查询不同品牌中最贵的商品价格
select id,title,brand,price from tb_item where (brand,price) in(
select brand,max(price) m from tb_item where brand is not null group by brand);
将不同品牌中最贵的商品按照价格降序排序
select id,title,brand,price from tb_item where (brand,price) in(
select brand,max(price) m from tb_item where brand is not null group by brand)
order by price desc;
找出不同品牌中最贵的商品的前三名
select id,title,brand,price from tb_item where (brand,price) in(
select brand,max(price) m from tb_item where brand is not null group by brand)
order by price desc
limit 3;
查询订购了10000028商品的客户姓名和联系方式
首先查询订单表,根据10000028这个商品编号找到有该商品的订单编号
然后查询订单表,根据上面得到的订单编号找到订购此订单的用户编号
最后查询用户表,根据第二部中查到的用户编号,找到对应的用户名和联系方式
select id, username, phone, email from tb_user where id in
(select user_id from tb_order where order_id in
(select order_id from tb_order_item where item_id=10000028))
select
distinct u.id, u.username, u.phone, u.email
from
tb_user u join tb_order o on u.id=o.user_id
join tb_order_item i on o.order_id=i.order_id
where
i.item_id=10000028
day07
1.25 存储的程序
========================================
* 数据库中存储的程序，对数据进行运算处理
* 存储过程
* 函数
* 触发器
1.26 会话变量
========================================
* 一次会话过程中，可以设置一些变量保存数据
* set @name='张三'
* @表示回话变量
* @@表示全局变量，指定是系统变量
set @@sql_mode='STRICT_TRANS_TABLES';
set @@autocommit=OFF
set sql_mode='STRICT_TRANS_TABLES';
set autocommit=OFF
* 显示变量的值
select @name, @a, @b, @c;
1.27 存储过程
==================================================
1.27.1 * 创建存储过程
delimiter // -- 设置语句的结束符号
create procedure p1()
begin
select * from t;
end;
//
delimiter ; -- 语句的结束符重新设置会成分号
1.27.2 * 调用存储过程
call p1();
1.27.3 * 删除存储过程
drop procedure [if exists] p1;
1.27.4 * 参数
存储过程的参数三种：
in 输入参数（默认）
out 输出参数
inout 即可输入也可输出
定义存储过程： p2(in a int, out b int)
...
...
set b = a*2;
调用 p2()：
call p2(255, @v1)
select @v1;
1.28 流程控制
1.28.1 *)选择判断
if 条件 then
...
end if;
-----------------
if 条件 then
...
else
...
end if;
==========================
case
when 条件 then ...;
when 条件 then ...;
when 条件 then ...;
else ...;
end case;
1.28.2 *) 循环
while 条件 do
...
end while;
---------------
loop
...
end loop;
---------------
repeat
...
until 条件 end repeat;
---------------
leave 跳出循环
iterate 直接进入下次迭代
---------------
循环命名
lp: loop
leave lp;
iterate lp;
end loop;
1.29 declare定义局部变量
declare
定义局部变量
*) declare a int; -- null
*) declare a int default 1;
*) 局部变量在 end 结束时销毁
begin
declare a int default 1;
begin
declare b int default 2;
end;
select a; -- ok
select b; -- 错误
end;
1.30 函数
==================================================
* 与存储过程类似，
* 函数有返回值
* 用 select f() 调用函数
* 创建
create function f(参数) returns int
begin
....
return 计算结果;
end;
1.31 游标 cursor
游标 cursor
* 游标： 查询结果的数据指针
* 只能一步一步向下一行移动
* 不能任意的被定位
* 游标操作：
*)定义游标：
declare c cursor for select ...
*)打开游标：
open c;
*)从游标取一行数据（自动下移）：
-- 从游标取出几个字段的数据，存入几个变量
fetch c into v1,v2,v3,v4;
*)关闭游标：
close c;
* 从头到尾用游标访问数据
mysql 的错误处理机制：
declare continue handler for ...
当出现指定错误(not found)时，执行一段代码(done=)，
使程序可以继续正常执行(continue)
declare done int default 0;
declare c cursor for select ...
declare continue handler for not found set done=1;
open c;
while done=0 do
-- 在末尾取不到数据，会出现 not found 错误
-- 触发 done=1 执行
fetch c into ...
end while;
1.32 触发器
=============================================
* 对一行数据进行增删改操作，
可以触发一端代码执行
* 六种： 一张表最多就能创建6个触发器
before insert
before update
before delete
after insert
after update
after delete
* 创建触发器
create trigger 名称 before insert
on tb1 for each row
begin
....
end;
* 两个隐含对象
new - 新的数据行
old - 旧的数据行
new.id, new.name, new.price
old.id, old.name, old.price
*)插入操作
new - 新插入的一行数据
old - 没有
*)修改操作
new - 修改后的新数据
old - 修改前的旧数据
*)删除操作
new - 没有
old - 被删掉的数据
1.33 mysql-day07练习
===================================================
1.33.1 1. 新建库新建表
create database db1 charset=utf8;
use db1;
create table t(
c int
);
1.33.2 2. 创建存储过程
delimiter // -- 修改语句结束符
drop procedure if exists p1; -- 删除存储过程
//
create procedure p1() -- 定义存储过程 p1
begin
insert into t values(floor(rand()*10));
select * from t;
end;
//
call p1();// -- 调用存储过程
//
1.33.3 3. 测试输入参数和输出参数
drop procedure if exists p2;
//
create procedure p2(in a int, out b int)
begin
set b = a*2; -- 设置输出变量 b 的值
end;
//
call p2(1, @a); -- a变量在p2()存储过程中被赋值
call p2(2, @b);
call p2(3, @c);
select @a, @b, @c;
//
1.33.4 4. 测试选择判断
drop procedure p3 if exists;
//
create procedure p3(in a int)
begin
if a=1 then -- 如果a是1
select 10;
end if;
--
case
when a=2 then select 20; -- 如果a是2
when a=3 then select 30; -- 如果a是3
when a=4 then select 40; -- 如果a是4
else select 100; -- 如果a是234之外的其他值
end case;
end;
//
call p3(1);
call p3(2);
call p3(3);
call p3(4);
call p3(5);
//
1.33.5 5. 循环测试，循环插入 1,2,3,4,5
truncate table t; -- 删除再重建表
//
drop procedure if exists p4;
//
create procedure p4(in a int)
begin
-- 定义变量1.局部变量 2.会话变量
declare i int default 1; -- 局部变量 i
--
while i<=a do -- 当<=a时重复执行
insert into t values(i);
set i = i+1;
end while;
--
set i=1;
lp: loop
insert into t values(i);
set i = i+1;
if i>a then -- 如果超过a离开循环
leave lp;
end if;
end loop;
--
set i=1;
repeat
insert into t values(i);
set i = i+1;
until i>a end repeat; -- 直到超过a结束循环
end;
//
call p4(5);
//
select * from t;
//
1.33.6 6. 测试函数，求 a 的 b 次方
drop function if exists func_pow;
//
create function func_pow(a bigint, b bigint) returns bigint
begin
declare r bigint; -- 用来保存结果
declare i bigint default 1; -- 用来控制循环次数
set r = a;
while i<b do -- 重复b-1次
set r = r*a; -- 重复地乘a
set i=i+1; -- 次数递增
end while;
return r; -- 返回计算结果
end;
//
select func_pow(2,3);
select func_pow(3,2);
select func_pow(2,8);
//
select c, func_pow(c, 2) from t;
//
1.33.7 7. 产生随机字符串
可以产生大量随机字符串填入表中，
对查询效率进行测试
drop function if exists rand_str;
//
-- 产生的随机字符串长度范围 [a, b)
create function rand_str(a int, b int) returns varchar(255)
begin
-- 从这些字符当中随机挑选几个
declare s0 varchar(600) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ的一是在不了有和人这中大为上个国我以要他时来用们生到作地于出就分对成会可主发年动同工也能下过子说产种面而方后多定行学法所民得经十三之进着等部度家电力里如水化高自二理起小物现实加量都两体制机当使点从业本去把性好应开它合还因由其些然前外天政四日那社义事平形相全表间样与关各重新线内数正心反你明看原又么利比或但质气第向道命此变条只没结解问意建月公无系军很情者最立代想已通并提直题党程展五果料象员革位入常文总次品式活设及管特件长求老头基资边流路级少图山统接知较将组见计别她手角期根论运农指几九区强放决西被干做必战先回则任取据处队南给色光门即保治北造百规热领七海口东导器压志世金增争济阶油思术极交受联什认六共权收证改清己美再采转更单风切打白教速花带安场身车例真务具万每目至达走积示议声报斗完类八离华名确才科张信马节话米整空元况今集温传土许步群广石记需段研界拉林律叫且究观越织装影算低持音众书布复容儿须际商非验连断深难近矿千周委素技备半办青省列习响约支般史感劳便团往酸历市克何除消构府称太准精值号率族维划选标写存候毛亲快效斯院查江型眼王按格养易置派层片始却专状育厂京识适属圆包火住调满县局照参红细引听该铁价严龙飞';
declare len int; -- 保存随机长度值
declare s varchar(255) default ''; -- 保存随机字符串结果
declare i int default 0; -- 用来控制次数
declare j int; -- 随机位置
-- 随机长度，范围 [a, b)
set len = a+floor(rand()*(b-a));
-- 产生 len 个随机字符连接到 s
while i<len do
-- 随机位置 [1, char_length(s0))
set j = 1+floor(rand()*(char_length(s0)-1));
-- 从s0取i位置字符，连接到 s
set s = concat(s, substring(s0, j, 1));
set i = i+1; -- i递增
end while;
return s; -- 返回随机字符串结果
end;
//
select rand_str(3,8);
select rand_str(3,8);
select rand_str(3,8);
select rand_str(3,8);
select rand_str(3,8);
//
1.具体长度 len
[3,8)
3+[0,5)
3+floor(rand()*5)
len = a+floor(rand()*(b-a))
0 0
0.1 *5 0.5
0.2 1
0.3 1.5
0.8 4
0.9999 4.xxxx
rand()
2. 需要一个空串 s=''
3. 循环 len 次
4. 产生随机位置 j
1+ [0, char_length(s0)-1)
j = 1+floor(rand()*(char_length(s0)-1))
5. 截取 s0 的 i 位置字符
substring(s0, j, 1)
6. 这个字符连到 s 上
s = concat(s, substring(s0, j, 1))
1.33.8 8. 在表中插入大量随机字符串
1.创建一个内存表
engin=innodb
myisam
memory 内存表
2.循环向内存表插入 10 万数据
3.将内存的10万数据直接插入磁盘表
-- 内存表
drop table if exists tm;
create table tm(
c varchar(255)
)engine=memory;
//
-- 磁盘表
drop table if exists td;
create table td(
id int primary key auto_increment,
c varchar(255)
)engine=innodb;
//
-- 存储过程
drop procedure if exists rand_data;
//
create procedure rand_data(n int)
begin
declare i int default 0; -- 用来控制循环多少次
while i<n do -- 循环 n 次
-- 向内存插入随机字符串
insert into tm values(rand_str(3,6));
set i=i+1;
end while;
-- 内存数据全部插入磁盘表
insert into td(c) select * from tm;
delete from tm; -- 清空内存表
end;
//
call rand_data(9);
//
call rand_data(20000);//
* 查询 td 表的 c 字段
select * from td where id=2918757
//
select * from td where c='值w农计调';
//
select * from td where c like '值w%';
//
select * from td where c like '%计调';
//
select * from td where c like '王__';
//
* 对 c 字段创建索引，再查询
或删除索引，在查询
create index td_c_index on td(c);
//
explain select * from td where c like '值w%';
//
explain select * from td where c like '%计调';
//
-- 删除索引
alter table td drop index td_c_index;
//
* 游标测试
drop procedure if exists p5;
//
create procedure p5()
begin
declare sum int default 0; -- 累加变量
declare a int; -- 保存每行的值
declare done int default 0; -- 结束标志
declare c cursor for select * from t; -- 游标
-- 错误处理，找不到数据值将标志值设置成1
declare continue handler for not found set done=1;
open c; -- 打开游标
while done=0 do -- 当结束标志是0，没结束
fetch c into a; -- 抓取下一行数据存到变量a
set sum = sum + a;
end while;
close c;
select sum;
end;
//
call p5();
//
* 商品分类表(tb_item_cat)
修改数据时，不必手动修改 updated 字段，
用触发器自动填入当前时间
update tb_item_cat set name='新类别'
where id=16;
update tb_item_cat set name='新类别',updated=now()
where id=16;
use jtds//
create trigger cat_updated_trigger
before update on tb_item_cat
for each row
begin
set new.updated=now();-- 新数据的更新时间字段填入系统时间
end;
//
select id,name,updated from tb_item_cat
where id in(3,4,5,6,7);
//
update tb_item_cat
set name=concat('>>>', name)
where id in(3,4,5,6,7);
//
* 阻止删除商品表数据
*)当 before delete 时，手动产生一个错误，
使删除操作失败
create trigger item_del_trigger
before delete on tb_item
for each row
begin
-- 手动暴力产生错误
delete from 不允许商品表的删除操作;
end;
//
delete from tb_item where id=10000028;
//
作业
=======================================================
用触发器1实现：
tb_item 商品表的商品数量，不允许减为负数
如果是负数，暴力产生错误
用触发2器实现：
向订单详情表(tb_order_item)插入数据时，
商品表商品数量减少
drop trigger if exists item_num_trigger;
//
create trigger item_num_trigger before update on tb_item for each row
begin
if new.num<0 then
delete from 商品数量不能是负数;
end if;
end;
//
drop trigger if exists order_item_num_trigger;
//
create trigger order_item_num_trigger before insert on tb_order_item for each row
begin
declare n_id bigint;
declare n_num int;
set n_id = new.item_id;
set n_num = new.num;
update tb_item set num=num-n_num where id=n_id;
end;
//
insert into tb_order_item(id,item_id,order_id,num,title,price,total_fee,pic_path,cereated,updated)
values(1,101434521126763,20161001490698615071,50,'aa',99,99,'',now(),now());
//
insert into tb_order_item(id,item_id,order_id,num,title,price,total_fee,pic_path,cereated,updated)
values(1,101434521126763,20161001490698615071,3,'aa',99,99,'',now(),now());
//
扫一扫
452
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
