数据挖掘笔记<2016年12月29日-2017年2月28日>

2016年12月29日

1.正态分布

2.偏度

偏度的绝对值数值越大表示表示分布形态的偏斜程度越大。

3.峰度

4.频率

分析->描述统计->频率

分析->描述统计—>描述

5.探索分析

分析->描述统计->探索

6.假设检验

假设检验原理：

7.多重响应法

2016年12月30日

1.共线性的评价指标:容许度，方差膨胀因子，特征值，条件指数。

2.一元线性回归分析

3.多元线性回归

4.曲线回归

2017年1月3日

标准化

(2)系统聚类分析

两步聚类

2017年1月5日

数据库架构：

服务器->多个数据库->多张数据库->列和行

cmd运行之后

C:\User>mysql -h 127.0.0.1 -u root -p 远程登录

Enter password:123456

C:\User>mysql -u root -p 本地登录

Enter password:123456

查看当前所有存在的数据库

show databases;

1、创建测试数据库test_db，

create database test_db;

2、查看创建好的数据库test_db的定义

mysql> show create database test;
+----------+---------------------------------------------------------------+
| Database | Create Database |
+----------+---------------------------------------------------------------+
| test | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)

3、删除测试数据库test_db，

drop database test_db;

4、选择数据库：

use 数据库名;

mysql> use test;
Database changed

创建员工表tb_emp1。
首先创建数据库test_db，SQL语句如下：

选择创建表的数据库，SQL语句如下：

查看数据表是否创建成功，SQL语句如下：

查看表格的结构：

1、定义数据表tb_emp2，其主键为id，SQL语句如下：
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(25) | no | | NULL | |
| deptId | int(11) | YES | | NULL | |
| salary | float | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+

create table 表名

(字符名数据类型字段属性，

字段名2............

);

create table tb_tmp1

(id int(11),

name varchar(25),

deptId int(11),

salary float

);

show tables;查看表名

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| tb_emp1 |
+----------------+
1 row in set (0.00 sec)

describe 数据表名，查看表格的结构

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+---
-----------+------+------------+
| Engine | Support | Comment | Tr
ansactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+---
-----------+------+------------+
| FEDERATED | NO | Federated MySQL storage engine | NU
LL | NULL | NULL |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO
| NO | NO |
| MyISAM | YES | MyISAM storage engine | NO
| NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO
| NO | NO |
| CSV | YES | CSV storage engine | NO
| NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO
| NO | NO |
| ARCHIVE | YES | Archive storage engine | NO
| NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YE
S | YES | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO
| NO | NO |
+--------------------+---------+----------------------------------------------------------------+---
-----------+------+------------+
9 rows in set (0.09 sec)

2、定义数据表tb_emp3，其主键为id，SQL语句如下：

+--------+-------------+------+-----+
| Field | Type | Null | Key |
+--------+-------------+------+-----+
| id | int(11) | NO | PRI |
| name | varchar(25) | YES | |
| deptId | int(11) | YES | |
| salary | float | YES | |
+--------+-------------+------+-----+

主键:primary key 对字段具有非空和唯一的约束(索引)。

create table tb_tmp2

(id int(11) not null primary key,

name varchar(25) not null,

deptId int(11),

salary float

);

create table tb_tmp3

(id int(11) not null ,

name varchar(25) not null,

deptId int(11),

salary float,primary key(id)

);

3、定义数据表tb_emp4，假设表中间没有主键id，为了唯一确定一个员工，可以把name、deptId联合起来做为主键，SQL语句如下：
+--------+-------------+------+-----
| Field | Type | Null | Key |
+--------+-------------+------+-----+
| name | varchar(25) | NO | PRI |
| deptId | int(11) | NO | PRI |
| salary | float | YES | |
+--------+-------------+------+-----+

mysql> create table tb_emp4
-> ( name varchar(25) not null,deptId int(11) not null,
-> salary float,
-> primary key(name,deptId));

Query OK, 0 rows affected (0.21 sec)

4、定义数据表tb_emp5，并在tb_emp5表上创建外键约束。

创建一个部门表tb_dept1，SQL语句如下：
+----------+-------------+------+-----+
| Field | Type | Null | Key |
+----------+-------------+------+-----+
| id | int(10) | NO | PRI |
| name | varchar(22) | NO | |
| location | varchar(50) | YES | |
+----------+-------------+------+-----+

普通索引 index

create table tb_emp1
( id int(11) not null primary key,
name varchar(22) not null,
location varchar(50));

外键： foreign key

constraint 外键 foreign key(字段名) references 表名

create table tb_emp5
( id int (11) not null primary key,
name varchar(25),
deptId int(11),
salary float,
constraint fk_ed foreign key(deptId) references tb_emp1(id));

定义数据表tb_emp5，让它的键deptId作为外键关联到tb_dept1的主键id，SQL语句为：
+--------+-------------+------+-----+
| Field | Type | Null | Key |
+--------+-------------+------+-----+
| id | int(11) | NO | PRI |
| name | varchar(25) | YES | |
| deptId | int(11) | YES | MUL |
| salary | float | YES | | 普通索引index
+--------+-------------+------+-----+

5、定义数据表tb_emp6，员工的姓名不能为空，SQL语句如下：
+--------+-------------+------+-----+
| Field | Type | Null | Key |
+--------+-------------+------+-----+
| id | int(11) | NO | PRI |
| name | varchar(25) | NO | |
| deptId | int(11) | YES | |
| salary | float | YES | |
+--------+-------------+------+-----+

6、定义数据表tb_dept2，指定部门的名称唯一，SQL语句如下： unique key

+----------+-------------+------+-----+
| Field | Type | Null | Key |
+----------+-------------+------+-----+
| id | int(11) | NO | PRI |
| name | varchar(22) | YES | UNI |
| location | varchar(50) | YES | |
+----------+-------------+------+-----+

create table db_dept2
(id int(11) not null primary key,
name varchar(22) unique key,
location varchar(50));

7、定义数据表tb_dept3，指定部门的名称唯一，SQL语句如下：

+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(22) | YES | UNI | NULL | |
| location | varchar(50) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+

create table tb_dept3
(id int(11) not null primary key,
name varchar(22) unique key,
location varchar(50) );

8、定义数据表tb_emp7，指定员工的部门编号默认为1111，SQL语句如下： default
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(25) | NO | | NULL | |
| deptId | int(11) | YES | | 1111 | |
| salary | float | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+

create table tb_emp7
( id int (11) not null primary key,
name varchar(25),
deptId int(11) default 1111,
salary float );

9、定义数据表tb_emp8，指定员工的编号自动递增，SQL语句如下： auto_increment
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(25) | NO | | NULL | |
| deptId | int(11) | YES | | NULL | |
| salary | float | YES | | NULL | |
+--------+-------------+------+-----+---------+----------------+

create table tb_emp8
( id int(11) not null primary key auto_increment,
name varchar(25),
deptId int(11),
salary float );

自增：只能添加到具有主键或唯一键的字段名上。
10、分别使用DESCRIBE和DESC查看表tb_dept1和表tb_emp1的表结构。
查看tb_dept1表结构，SQL语句如下：

11、将数据表tb_dept2改名为tb_deptment2。再将表名tb_deptment2改为tb_dept2；

alter table 表名 rename 新表名

alter table tb_dept2 rename tb_deptment2;

12、将数据表tb_dept1中name字段的数据类型由VARCHAR(22)修改成VARCHAR(30)。 modify

alter table 表名 modify 字段名新数据类型字段属性;

alter table tb_dept2 modify name varchar(30)not null;

13、将数据表tb_dept1中的location字段名称改为loc，数据类型保持不变，change;

alter table 表名 change字段名新字段名数据类型字段属性。

alter table tb_dept2 change location loc varchar(30);

14、将数据表tb_dept1中的loc字段名称改为location，同时将数据类型变为VARCHAR(60)，

alter table tb_dept2 change loc locament varchar(60);
15、在数据表tb_dept1中添加一个没有完整性约束的INT类型的字段managerId（部门经理编号）

alter table 表名 add 字段名数据类型字段属性 first|after 字段名

alter table tb_dept2 add managerId int;

alter table tb_dept2 add managerId1 int(11) not null first;

alter table tb_dept2 add managerId3 int(11) not null after name ;

16、在数据表tb_dept1中添加一个不能为空的VARCHAR(12)类型的字段column1，SQL语句如下：

alter table tb_dept2 add column1 varchar(12) not null;

17、在数据表tb_dept1中添加一个INT类型的字段column2，SQL语句如下：

alter table tb_dept2 add column2 int;

18、删除数据表tb_dept1表中的column2字段。

alter table 表名 drop 字段名

alter table tb_dept2 drop managerId3;

19、将数据表tb_dept1中的column1字段插入到location字段后面，SQL语句如下：注意column1字段已经存在。。。

alter table tb_dept2 modify column1 varchar(12) after column2;

alter table tb_dept2 change column1 column1 varchar(12) not null after managerId;

20.删除唯一键

drop index|key 唯一键名字

2017年1月6日

一、创建数据库company,按照下面表1和表2给出的表结构，在company数据库中创建两个数据表offices和employees

表1 offices表结构

字段名	数据类型	主键	外键	非空	唯一	自增
officeCode	INT(10)	是	否	是	是	否
city	INT(11)	否	否	是	否	否
address	VARCHAR(50)	否	否	否	否	否

表2 employees表结构

字段名	数据类型	主键	外键	非空	唯一	自增
enumber	INT(11)	是	否	是	是	是
lastName	VARCHAR(50)	否	否	否	否	否
firstName	VARCHAR(50)	否	否	否	否	否
mobile	VARCHAR(25)	否	否	否	是	否
officeCode	INT(10)	否	是	是	否	否

create table offices (
officeCode int(10) not null primary key unique key,
city int(11) not null,
address varchar(50));

create table employees
(enumber int(11) primary key not null unique key auto_increment,
lastName varchar(50),
firstName varchar(50),
mobile varchar(25) unique key,
officeCode int(10) not null,
constraint off_foreign foreign key(officeCode) references offices(officeCode));

1、将数据表offices中字段名address改为ads,数据类型不变；

alter table offices change address ads varchar(50);

2、将employees的表名改为employee。

alter atble employees rename employee;

3、将employee表中的officeCode的位置调整到firstName的后面；

alter table employee change officeCode officeCode int(11) after firstName;

4、删除表employee中的主键

先删除自增，才能删除主键。

alter table employee modify enumber int(11) not null;

alter table employee drop key enumber;

5、添加表employee中的主键

alter table employee add primary key(enmuber);

6、将表employee中mobile的唯一约束删除掉；

alter table employee drop key|index mobile;

7、在表employee中mobile上添加唯一约束；

alter table employee add unique key(mobile);

1、建立表格person
+-------+------------------+------+-----+---------+----------------+
| 字段名 | 数据类型 | 空 |主键 | 默认值 | 自增 |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | 是 |
| name | char(40) | NO | | | 否 |
| age | int(11) | NO | | 0 | 否 |
| info | char(50) | YES | | NULL | 否 |
+-------+------------------+------+-----+---------+----------------+

create table person
(id int(10) unsigned not null primary key auto_increment,
name char(40) not null default '',
age int(11) not null default 0,
info char(50) );

2、向表格person中插入数据
+----+-------+-----+----------+
| id | name | age | info |
+----+-------+-----+----------+
| 1 | green | 21 | lawyer |
| 2 | suse | 22 | dancer |
| 3 | tom | 22 | musician |
| 4 | mike | 20 | teacher |
+----+-------+-----+----------+

insert into 表名（字段名）

values(数据1）,（数据2)....

insert into person(id,name,age,info)

values(1,'green',21,'lawyer');

insert into person

values(1,'green',21,'lawyer');

insert into person(name,age,info)
values('tom',22,'musician'),('mike',20 ,'teacher');

查询表格信息：

select 字段名from 表名 where 条件

3、建立表格person_old
+-------+------------------+------+-----+---------+----------------+
| 字段名| 数据类型 | 空 |主键 | 默认值 | 自增 |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | 是 |
| name | char(40) | NO | | NULL | 否 |
| age | int(11) | NO | | 0 | 否 |
| info | char(50) | YES | | NULL | 否 |
+-------+------------------+------+-----+---------+----------------+

4、向表格pers

create table person_old
(id int(10) unsigned not null primary key auto_increment,
name char(40) not null,
age int(11) not null default 0,
info char(50));

on_old中插入数据

+----+------+-----+---------+
| id | name | age | info |
+----+------+-----+---------+
| 11 | l | 20 | student |
| 12 | m | 30 | police |
+----+------+-----+---------+

insert into person_old(id,name,age,info)
values(11,'l',20,'student'),(12,'m',30,'police');

5、将另一张表转存到一张表中

insert into 表名(字段名） select 字段名 from 表名2 where 条件。

insert into person(id,name,age,info)select id,name,age,info from person_old;

删除数据：

delete from 表名 where条件。

delete from person_old where id>10;

创建表格person1，表结构和person相同:

create table 表名1 like 表名2;
create table person1 like person;

按照person表信息查询的结果创建表格person2：

create table person2 as select* from person where id>10;

6，更新数据
对数据表person中id为11的age改为15，name改为lining；

update 表名 set 字段名1=数据，字段名2=数据...where 条件。

update person set age=15,name='lingling' where id=11;

在person表中，更新age值为19-22的记录，将info字段值都改成student；

update person set info='student' where age>=19&&age<=21;

update person set info='student1' where age>=19 and age<=21;

update person set info='studen4' where age between 19 and 22;(包括19和22)
3,删除数据
delete from tale_name[where condition] (condtion条件）
删除数据表person中id为11的记录

delete from person where id=11;

4,在person表中，删除age值为19-22的记录

delete from person where age between 19 and 22;

下面以一个例子说明如何使用SELECT从单个表中获取数据。
首先定义数据表fruits，输入语句如下：
+---------+--------------+------+-----+---------+
| Field | Type | Null | Key | Default |
+---------+--------------+------+-----+---------+
| f_id | char(10) | NO | PRI | NULL |
| s_id | int(11) | NO | | NULL |
| f_name | char(50) | NO | | NULL |
| f_price | decimal(8,2) | NO | | NULL |
+---------+--------------+------+-----+---------

为了演示如何使用SELECT语句，需要插入如下数据：
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| a1 | 101 | apple | 5.20 |
| a2 | 103 | apricot | 2.20 |
| b2 | 104 | berry | 7.60 |
| b5 | 107 | xxxx | 3.60 |
| bj1 | 101 | blackberry | 10.20 |
| bs1 | 102 | orange | 11.20 |
| bs2 | 105 | melon | 8.20 |
| c0 | 101 | cherry | 3.20 |
| l2 | 104 | lemon | 6.40 |
| m1 | 106 | mango | 15.60 |
| m2 | 105 | xbabay | 2.60 |
| m3 | 105 | xxtt | 11.60 |
| o2 | 103 | coconut | 9.20 |
| t1 | 102 | banana | 10.30 |
| t2 | 102 | grape | 5.30 |
| t4 | 107 | xbababa | 3.60 |
+------+------+------------+---------+

insert into fruits
values('b2',103,'berry',7.60),
('b5',107,'xxxx',3.60),
('bj1',101,'blackberry',10.20),
('bs1',102,'orange',11.20),
('c0',101,'cherry',3.20),
('l2',104,'lemon',6.40),
('m1',106,'mango',15.60),
('m2',105,'xxtt',2.60),
('m3',105,'xxtt',11.60),
('o2',103,'coconut',9.20),
('t1',102,'banana',10.30),
('t2',102,'grape',5.30),
('t4',107,'xbababa',3.60);

select 字段名 from 表名

where 条件1 or|and|xor 条件2b

group by字段名

having 条件

order by 字段名 asc(升序)|desc(降序)

limit m,n;

1、从fruits表中检索所有字段的数据，SQL语句如下：

select * from fruits;

2、查询fruits表中f_name列所有水果名称，SQL语句如下：

select f_name from fruits;

3、例如，从fruits表中获取f_name和f_price两列，SQL语句如下：

select f_name,f_price from fruits;

4、查询价格为10.2元的水果的名称，SQL语句如下：

select f_name from fruits where f_price=10.2;

select f_name ,f_price from fruits where f_price=10.2;

5、查找名称为“apple”的水果的价格，SQL语句如下：

select f_price,f_name from fruits where f_name="apple";

6、查询价格小于10的水果的名称，SQL语句如下：

select f_name,f_price from fruits where f_price<10;
7、s_id为101和102的记录，SQL语句如下：

select * from fruits where s_id=101 or s_id=102;

select *from fruits where s_id in(101,102);

8、查询所有s_id不等于101也不等于102的记录，SQL语句如

不等于 <> !=

select *from fruits where s_id !=101 and s_id!=102;

select *from fruits where s_id not in (101,102);

select * from fruits where s_id<> 101 and s_id !=102;
9、查询价格在2.00元到10.20元之间的水果名称和价格，SQL语句如下：

select f_name,f_price from fruits where f_price>=2.00 and f_price<=10.20;

select f_name,f_price from fruits where f_price between 2.00 and 10.20;

10、查询价格在2.00元到10.20元之外的水果名称和价格，SQL语句如下：

select f_name,f_price from fruits where f_price<2.00 or f_price>10.20;

select f_name,f_price from fruits where f_price not between 2.00 and 10.20;

UNION合并表达查询
union（去重的结果）
union all（不删除重复的结果）
11、查询数据表fruits中f_price小于10的结果和fruits_new中s_id为101和103的结果，SQL语句如下：

create table fruits_new like fruits;(like fruits_new 与fruits的表的属性都是一样，但是没有数据)

insert into fruits_new select * from fruits;

create table fruits_new as select * from fruits;(as 表的结构属性不同，但是fruits_new 有数据)

select *from fruits where f_price<10 union select *from fruits_new where s_id in (101,103);

去重：distinct
12、查询fruits表中的s_id有哪些：

select distinct s_id from fruits ;

EXISTS

create database if not exists test_db;

13、查询fruits表中是否存在s_id=107的供应商，如果存在，则查询fruits表中的记录，SQL语句如下

select * from fruits
where exists (select *from fruits where s_id=107);

14、查询fruits表中是否存在s_id=107的供应商，如果存在，则查询fruits表中的f_price大于10.20的记录，SQL语句如下：

select f_name, f_price from fruits where exists(select *from fruits where s_id=107)and f_price>10.20;

15、查询fruits表中是否存在s_id=107的供应商，如果不存在则查询fruits表中的记录，SQL语句如下
);

select *from fruits where not exists (select* from fruits where s_id=107);

16、ORDER BY 字段名 asc（升序）|desc（降序）:
查询数据表fruits中水果的信息，并按照价格进行升序排列,SQL语句为

select * from fruits order by f_price asc;

17、查询数据表fruits中水果的信息，首先按照s_id升序，在按照f_price进行降序排列

select * from fruits order by s_id asc,f_price desc;

LIMIT
18、查询数据表fruits中前5行的信息

select *from fruits limit 0,5;

19、查询数据表fruits中第3行到第6行的信息

select *from fruits limit 2,4;

求最大值:select max(f_price) from fruits;

求最小值:select min(f_price) from fruits;

求和:select sum(f_price) from fruits;

求平均数:select avg(f_price) from fruits;

计算:select count(f_price) from fruits;

　　select count(*) from fruits;
GROUP BY

select s_id,count(s_id) from fruits group by s_id;

20、查询数据表fruits中每个供应商各自水果的平均价格

select s_id,avg(f_price) from fruits group by s_id;

HAVING
21、查询每个供应商各自水果的平均价格大于5元的记录；

select *from fruits group by s_id having avg(f_price)>5;

22、查找f_name所有以’b’字母开头的水果，SQL语句如下：

like 通配符: % 单个字符:_

select f_name from fruits where f_name like 'b%';

23、在fruits表中，查询f_name中包含字母'g’的记录，SQL语句如下：

select f_name from fruits where f_name like '%g%';

24、查询以’b’开头，并以’y’结尾的水果的名称，SQL语句如下：

select f_name from fruits where f_name like 'b%y';
25、在fruits表中，查询以字母’y’结尾，且’y’前面只有4个字母的记录，SQL语句如下：

select f_name from fruits where f_name like '_ _ _ _y';

26、在fruits表中，f_name中包含‘b’或者‘x'的记录，SQL语句如下：

select * from fruits where f_name like '%b%' or f_name like '%x%';

正则表达式：regexp

以b为开头

select * from fruits where f_name regexp '^b';
以y为结尾

select * from fruits where f_name regexp 'y$';

包含b或x（|两边可写单个字符，也可写字符串）

select * from fruits where f_name regexp 'b|x';

包含b或x,只能匹配单个字符

select * from fruits where f_name regexp '[bx]';

匹配字符集中除去a到x的其他字符。

select * from fruits where f_name regexp '[^a-z]';

匹配包含y且y前面最少四个字符.

select * from fruits where f_name regexp '^____y$';
.*:*前面的字符连续出现n个，n包含0；*={0,} a.*b|b.*a

(ba)+:+表示符号之前的字符连续出现1次以上；{1,}

ba* b ba baa baaaa..... {0,}

ba+ ba baa baaaa.... {1，}

ba一起连续出现两次以上

(ba){2}

20167年 1月7日

对数据进行验证

(1)数据->验证->定义规则

注意连续性变量分类型变量

2017年 1月8日

计算年龄，日期

分段

(1)计算变量中if

之后

数据->定义便令属性

(2)转换->重新编码为不同的变量

(3)转换->可视化离散化

$$$$$$多个条件进行排序。。数据->排序个案

2017 年 1 月 9日

mysql修改密码

cmd运行

客户端运行

多表查询：

1、首先定义第一张数据表fruits，输入语句如下：
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| f_id | char(10) | NO | PRI | NULL | |
| s_id | int(11) | NO | | NULL | |
| f_name | char(50) | NO | | NULL | |
| f_price | decimal(8,2) | NO | | NULL | |
+---------+--------------+------+-----+---------+-------+

2、然后需要插入如下数据：
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| a1 | 101 | apple | 5.20 |
| a2 | 103 | apricot | 2.20 |
| b1 | 101 | blackberry | 10.20 |
| b2 | 104 | berry | 7.60 |
| b5 | 107 | xxxx | 3.60 |
| bs1 | 102 | orange | 11.20 |
| bs2 | 105 | melon | 8.20 |
| c0 | 101 | cherry | 3.20 |
| l2 | 104 | lemon | 6.40 |
| m1 | 106 | mango | 15.60 |
| m2 | 105 | xbabay | 2.60 |
| m3 | 105 | xxtt | 11.60 |
| o2 | 103 | coconut | 9.20 |
| t1 | 102 | banana | 10.30 |
| t2 | 102 | grape | 5.30 |
| t4 | 107 | xbababa | 3.60 |
+------+------+------------+---------+

create table suppliers
(s_id int(11) not null primary key auto_increment,
s_name char(50) not null ,
s_city char (50) ,
s_zip char(10),
s_call char(50));

insert into suppliers
values(101,'FastFruit Inc','TianJin','300000','48075'),
(102, 'LT Supplies','Chongqing','400000','44333'),
(103,'ACME','Shanghai','200000','90046'),
(104,'FNK Inc','Zhongshan','528437','11111'),
(105,'Good Set','Taiyuan','030000','22222'),
(106,'Just Eat Ours','BeiJing','010','45678'),
(107,'Dk Inc','Zhengzhou','450000','33332');

5、在fruits表和suppliers表之间使用内连接查询。
查询之前，查看两个表的结构：

select *from fruits,suppliers where fruits.s_id=suppliers.s_id;

连接查询：

6、在fruits表和suppliers表之间，使用INNER JOIN语法进行内连接查询，SQL语句如下：

7、在fruits表和suppliers表之间，使用left join语法进行左连接查询，SQL语句如下：

8、在fruits表和suppliers表之间，使用right join语法进行右连接查询，SQL语句如下：

select 字段名 from 表1 inner|left|right 表2

on 表1.字段名=表2.字段名 and 条件(这个条件其实就是两表之间的联系) where条件

select * from fruits inner join suppliers on fruits.s_id=suppliers.s_id;(公共的条件显示)

select * from fruits left join suppliers on fruits.s_id=suppliers.s_id;(以左边fruits表为准)

select * from fruits right join suppliers on fruits.s_id=suppliers.s_id;(以右边suppliers为准)

左右都要，用union连接

select * from fruits left join suppliers on fruits.s_id=suppliers.s_id union select * from fruits right join suppliers on fruits.s_id=suppliers.s_id;

取别名：as

select * from fruits as f1 inner join suppliers as s1 on f1.s_id=s1.s_id;(公共的条件显示)

语法书写顺序

select 字段名 from 表名

where 条件

group by 字段名

having 条件

order by 字段名

limit m，n

执行顺序

from

where

group by

having

select as

union

order by

limit
判断语句：case

case

when 条件 then 语句

else 语句

end

select * ,case when f_price<5 then '5元以内' when f_price between 5 and 10 then
'5-10元'
else ' 10 元以上'
end

from fruits;

select * ,case when f_price<5 then '5元以内' when f_price between 5 and 10 then
'5-10元'
else ' 10 元以上'
end as 价格区间

from fruits;

该支持汉字

安装文件，找到配置文件my.ini

退出服务，启动服务，要把mysql、mysqld (启动任务管理器，进程)等服务关掉。。

‘’子查询

9、使用内连接查询供应f_id= ‘a1’的水果供应商提供的水果种类，SQL语句如下：

select * from fruits where s_id=(select s_id from fruits where f_id='a1');

select * from fruits where s_id in (select s_id from fruits where f_id='a1');

select * from

( select f_id,fruits.s_id as fs_id,f_name,f_price,suppliers.* from fruits inner join suppliers on fruits.s_id=suppliers.s_id) as f1

where s_id in (select s_id from fruits where f_id='a1');

10、ANY和SOME关键字是同义词，表示满足其中任一条件，它们允许创建一个表达式对子查询的返回值列表进行比较，只要满足内层子查询中的任何一个比较条件，就返回一个结果作为外层查询的条件。
下面定义两个表tb1和tb2：

tb1：
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| num1 | int(11) | NO | | NULL | |
+-------+---------+------+-----+---------+------

create table tb1
( num1 int(11) not null );

tb2：
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| num2 | int(11) | NO | | NULL | |
+-------+---------+------+-----+---------+-------+

create table tb2
(num2 int(11) not null);

11、分别向两个表中插入数据：

insert into tb1
values(1),(3),(5),(7),(9),(13),(17);

insert into tb2
values(2),(4),(6),(8),(10),(12);

12、返回tb2表的所有num2列，然后将tb1中的num1的值与之进行比较，只要大于num2的其中一个值，其为符合查询条件的结果。

select num2 from tb2;

select num1 from tb1 where num1>any(select num2 from tb2);

select num1 from tb1 where num1>(select min(num2) from tb2);

13、返回tb1表中比tb2表num2 列所有值都大的值，SQL语句如下：

select num1 from tb1 where num1>all(select num2 from tb2);
select num1 from tb1 where num1>(select max(num2) from tb2);

1、绝对值ABS:求2，-3.3和-33的绝对值，输入语句如下：

select abs(2),abs(-3.3),abs(-33);

2、求余数MOD(X,Y):对MOD(31,8)，MOD(234, 10)，MOD(45.5,6)进行求余运算，输入语句如下：

select mod(31,8),mod(234,10),mod(45.5,6);

3、返回最小整数CEILING:使用CEILING函数返回最小整数，输入语句如下(大于等于)：

select ceiling(23.45),ceiling(-23.45);
4、使用ROUND(x)函数对操作数进行四舍五入操作，输入语句如下：

select round(23.45),round(23.123456,2),round(23.12345,0),round(12345,-1);

5、使用CHAR_LENGTH函数计算字符串字符个数，输入语句如下：

select char_length('football'),length('football');

6、使用CONCAT函数连接字符串，输入语句如下：

select concat('foot','ball');

7、使用LEFT函数返回字符串中左边的字符，输入语句如下：

select left('football',4);

8、使用RIGHT函数返回字符串中右边的字符，输入语句如下：

select right('football',4);

9、使用MID()函数获取指定位置处的子字符串，输入语句如下：

select mid('football',5 ) as f1,
mid( 'football',-5) as f2,
mid('football',4,2) as f3,

mid('football',-5,2) as f4;

10、使用LOCATE，POSITION，INSTR函数查找字符串中指定子字符串的开始位置，输入语句如下：

select locate('ball','football'),
position('ball' in 'football'),
instr( 'football','ball');

11、使用FIND_IN_SET()函数返回子字符串在字符串列表中的位置，输入语句如下：

select find_in_set('ba','ball,foot,football,ba');

12、使用ifnull()函数对null空值进行判断，如果为空，输出第二个值。
select *,if(f_price<10,'小于10' ,'大于10') from fruits;

select ifnull(null,2);

select ifnull(null,2),ifnull(4,2);

13、使用日期函数current_date()获取系统当前日期，输入语句如下：

select current_date(),curdate();

14、使用时间函数current_time()获取系统当前时间，输入语句如下：

select current_time(),curtime();

15、使用日期时间函数current_timestamp(),localtime(),now(),sysdate()获取当前系统日期和时间，输入语句如下：

select current_timestamp(),localtime(),now(),sysdate();

16、使用MONTH()函数返回指定日期中的月份，输入语句如下：

select month(now());

17、使用MONTHNAME()函数返回指定日期中的月份的名称，输入语句如下：

select monthname(now());

18、查询两个日期相差几天datediff（时间1，时间2);

select datediff('2017-01-09','2016-12-27');

19、对时间进行更新可使用date_add(date,interval expr type)和date_sub()函数;

select date_add('2016-12-09',interval 4 day);

select date_sub('2016-12-09',interval 4 day);

2016年1月10日

1、从fruits表中检索所有字段的数据，SQL语句如下：

select * from fruits ;
2、查询fruits表中f_name列所有水果名称，SQL语句如下：

select f_name from fruits;

3、从fruits表中获取f_name和f_price两列，SQL语句如下：

select f_name ,f_price from fruits;

4、查询价格为10.2元的水果的名称，SQL语句如下：

select f_name,f_price from fruits where f_price=10.2;

5、查找名称为“apple”的水果的价格，SQL语句如下：

select f_price,f_name from fruits where f_name='apple';

6、在fruits表中查询s_id = 101或者102，且f_price大于5，并且f_name=‘apple’的水果价格
和名称，SQL语句如下：

select f_price ,f_name from fruits
where s_id in(101,102)
and f_price>5
and f_name='apple';

select f_price ,f_name from fruits
where (s_id =101 or s_id=102)
and f_price>5
and f_name='apple';
7、在suppliers表中查询s_city等于“Tianjin”的供应商s_id，然后在fruits表中查询所有该
供应商提供的水果的种类，SQL语句如下：

select s_id,f_name from fruits
where s_id in (select s_id from suppliers where s_city='Tianjin');

8、在suppliers表中查询s_city等于“Tianjin”的供应商s_id，然后在fruits表中查询所有非
该供应商提供的水果的种类，SQL语句如下：

select * from fruits
where s_id not in(select s_id from suppliers where s_city='Tianjin');

select s_id,f_name from fruits
where s_id !=(select s_id from suppliers where s_city='Tianjin');

select s_id,f_name from fruits
where s_id <>(select s_id from suppliers where s_city='Tianjin');

9、查询fruits表，为f_name取别名fruit_name，f_price取别名fruit_price，为fruits表取别
名f1，查询表中f_price < 8的水果的名称，SQL语句如下：

select f_name as fruit_name,f_price as fruit_price
from fruits as f1 where f_price<8;

10、在fruits表中，查找f_name字段中包含字母’o’或者’t’的记录，SQL语句如下：

select * from fruits where f_name like '%o%' or f_name like '%t%';

select * from fruits where f_name regexp '[ot]'; 'o|t'

11、在ts表中字段名为s_id上建立外键连接到suppliers表s_id上，SQL语句为：

外键必须字段的类型相同，并且外键表的外键字段名必须在外表的字段之中。。

delete from fruits where s_id not in(select s_id from suppliers);

create table ts
( s_id int(11) ,
constraint s_fk foreign key(s_id) references suppliers(s_id));

12、删除fruits表中的外键,SQL语句为：

alter table fruits drop foreign key f_name;

13、查询fruits表中的信息，并将结果首先按照s_id升序排列，在按照水果名称降序排列：

select * from fruits order by s_id asc,f_name desc;

14、修改fruits表中s_id的字段名，将字段名改为fs_id，其余信息不变，SQL语句为：

alter table fruits change s_id fs_id int(11) not null;

15、输出fruits表中第3行到第7行的数据，SQL语句为：

select * from fruits limit 2,5;

16、查询fruits表中供应商信息及各供应商提供水果的平均价格，SQL语句为：

select fs_id,avg(f_price) from fruits group by fs_id;

17、查询fruits表中每个供应商供应的水果大于4.5元的记录，SQL语句为：

select * from fruits where f_price>4.5;

18、查询fruits表中供应商的信息，SQL语句如下：

select distinct fs_id from fruits;

19、查询fruits表中有多少个供应商，SQL语句如下：
先去重，在计数。
select count( distinct fs_id) from fruits;

20、查询fruits表中每个供应商所供应水果价格最高的记录；

select * from fruits where (s_id,f_price) in (select s_id,max(f_price) from fruits
group by s_id);

21、在fruits表中添加一字段名为num1的字段，数据类型为int，默认值为3；（该字段为销售量
的意思）
alter table fruits add num1 int default 3;

22、查询fruits表中供应商总销售额前三名的信息及销售额；
select fruits.s_id ,s_name,s_city,sum(f_price*num1)
from fruits left join suppliers
on fruits.s_id=suppliers.s_id
group by f_id
order by sum(f_price*num1) desc
limit 0,3;

select s_id,sum(f_price*num1)
from fruits
group by f_id
order by sum(f_price*num1) desc
limit 0,3;

23、查询fruits表的信息，并添加‘单价状况’的信息，如果单价低于5元，则标明‘低于5元’
，如果单价高于10元，则表明’高于10元’，其他的情况，请表明‘5-10元’，SQL语句为：

select *,case
when f_price<5 then '低于5元'
when f_price>10 then '高于10元'
else '5-10元'
end as 单价状况
from fruits;

24、怎样查询fruits表中fs_id和f_name信息重复的记录；

select * ,count(f_name) from fruits group by s_id,f_name having count(f_name)>1;

rollup的使用方法：
1、创建表格tb_1：
+---------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| s_id | char(8) | NO | PRI | NULL | 供应商 |
| f_name | char(20) | NO | PRI | NULL | 水果名 |
| f_price |float(8,2)| NO | | NULL |水果价格|
| f_no | int(4) | NO | | NULL |销售数量|
+---------+----------+------+-----+---------+-------+

create table tb_1
(s_id char(8) not null comment '供应商',
f_name char(20) not null comment '水果名',
f_price float(8,2) not null comment '水果价格',
f_no int(4) not null comment '销售数量',
primary key(s_id,f_name));

2、在表格tb_1中插入一下数据：
+------+--------+---------+------+
| s_id | f_name | f_price | f_no |
+------+--------+---------+------+
| 001 | apple | 3.5 | 3 |
| 001 | banana | 3.5 | 2 |
| 001 | orange | 4.5 | 4 |
| 002 | apple | 3.3 | 5 |
| 002 | banana | 3.8 | 0 |
| 002 | orange | 4.7 | 3 |
+------+--------+---------+------+

insert into tb_1
values('001','apple', 3.5,3),
('001','banana',3.5,2),
('001' ,'orange', 4.5 , 4 ),
('002',' apple',' 3.3',' 5 '),
('002','banana' , 3.8 , 0),
('002 ' ,'orange',4.7 , 3 );

5、按照供应商来查询表中水果的总销售额：

select ifnull( s_id,'总计') ,f_name,sum(f_price*f_no),ifnull(f_name,'总计') from
tb_1 group by s_id,f_name with rollup;

select s_id,f_name,sum(f_price*f_no), f_price from tb_1 group by s_id,f_name with
rollup;

存储过程：
查询fruits信息的存储过程；

delimiter //
create procedure pro1(in x int)
begin
select * from fruits
where fs_id=x;
end;//

drop procedure pro1

delimiter ;

create procedure 存储过程名(参数)
SQL语句;
end;//
调用存储过程：

call 存储过程名(参数);

定义变量：
declare 变量名数据类型初始值

参数:in/out/inout 参数名数据类型

in:
create procedure pro2(in pro_in int)
begin
select pro_in;
set pro_in=2;
select pro_in;
end;//

set @x=1 //
select @x //

call pro2(@x) //

out:
create procedure pro3(out pro_out int)
begin
select pro_out;
set pro_out=2;
select pro_out;
end;//

set@x=1//
select @x//
call pro3(@x) //elec
select @y//

inout:
create procedure pro4(inout pro_inout int)
begin
select pro_inout;
set pro_inout=2;
select pro_inout;
end;//

查看存储过程
show procedure status;
定义变量:
declare 变量名数量类型初始值
declare s int default 0;
declare i int default 1;

1+2+3....+100;

while 循环:

create procedure pro5(out z int)
begin
declare s int default 0;
declare i int default 1;
while i<=100 do
set s=s+i;
set i=i+1;
end while;
select s;
set z=s;
select z;
end;//

判断:

case:
create procedure pro6(in x int)
begin
case x
when 1 then select 1;
when 2 then select 2;
else select 3;
end case

create procedure pro6(in x int)
begin
case
when x<1 then select 1;
when x>2 then select 2;
else select 3;
end case;
end ;//

if:
create procedure pro7(in y int)
begin
if y=1 then select 1;
elseif y=2 then select 2;
else select 3;
end if;
end;//

从MySQL中导出数据：sql

注:命令行导入数据库，>改成< , \改成/(不保证，有待检验)
1、将某数据库中的所有表导出来：

mysqldump -u 用户名 -p 数据库名>路径文件名

C:\Users\lx>mysqldump -u root -p test>C:\aa\test.sql
Enter password: ******

2、如何以库为单位把数据导出来;

mysqldump -u root -p -B test>C:\aa\testB.sql

3、如何导出所有的库：

mysqldump -u root -p -A >C:\aa\ad.sql

4、如何导出特定库中特定表的数据：

mysqldump -u root -p test fruits>C:\aa\fruits.sql

将数据导入到数据库中

5、如何以库为单位导入sql文件：

source C:/aa/fruit2.sql

mysql -u root -p <C:\aa\testB.sql

6、对于表级的备份文件：

use t1;

source C:/aa/fruits.sql

mysql -u root -p test <C:/aa/fruits.sql
7、只导出数据结构，不导出数据
mysqldump -u root -p -d test fruits>C:/aa/fruits5.sql

8、MySQL中用into outfile导出fruits的csv或txt文件

SELECT * INTO OUTFILE 文件路径 FROM 表名

select * into outfile 'C:/Users/lx/Desktop/aa.txt'
fields terminated by ','
optionally enclosed by '"'
lines terminated by '\r\n'
from fruits;

9、导出为txt的文件：

select * into outfile 'c:/users/fruits.txt'
fields terminated by','
optionally enclosed by '"'
lines terminated by '\r\n'
from fruits;
where fs_id between 101 and 109;

10、将csv或txt文件导入到数据库中

Load data infile 文件路径 into table 数据表

load data infile 'C:/Users/lx/Desktop/aa.txt' into table fruits
character set gbk
fields terminated by ','
optionally enclosed by '"'
escaped by '"'
lines terminated by '\r\n';

导出csv和txt文件时，格式要定义好，
fields terminated定义字段终止用什么来区分，我们用的是“，”来区分的，optionally enclosed by’”’表示把数据文件中的字符串加双引号“ " ”来封闭，escaped by用来规定转义字符。
，lines terminated 表示换行。

2017年1月11日

触发器:
创建表格订单表ts1:
+----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| order_id | char(10) | NO | PRI | NULL | |
| pro_id | char(10) | NO | | NULL | |
| num | int(10) | YES | | NULL | |
+----------+----------+------+-----+---------+-------+

create table ts1
(order_id char(10) not null primary key ,
pro_id char(10) not null,
num int(10) );

创建表格商品表tt1：
+----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| pro_id | char(10) | NO | PRI | NULL | |
| pro_name | char(10) | NO | | NULL | |
| num | int(10) | NO | | NULL | |
+----------+----------+------+-----+---------+-------+

create table tt1
(pro_id char(10) not null primary key ,
pro_name char(10) not null,
num int(10) not null);

在商品表tt1中插入数据：
insert into tt1
values('1111','apple',50),
('1112','banana',60),
('1113','orange',50);

创建触发器：

监控地点：
监控事件： insert ,update ,delete
触发时间： after before
触发事件： insert update delete

delimiter //

create trigger 触发器名
after|before insert|update|delete on 表名
for each row
begin
SQL 语句;
end;//

1、客户下订单，订单表中插入数据：('1001','1112',4),创建触发器，要求商品表自动减去相
应的商品库存：new.num

delimiter//

create trigger tr1
after insert on ts1
for each row
begin
update tt1 set num=num-new.num
where pro_id=new.pro_id;
end;

查看触发器
show triggers;

insert into ts1
values ('1004','1112',50);

2、客户取消订单，取消订单'1001',创建触发器，要求商品表自动添加相应的商品库存：
old.num

create trigger tr2
after delete on ts1
for each row
begin
update tt1 set num=num+old.num
where pro_id=old.pro_id;
end;//
3、客户修改订单，将订单'1001'的数量改为8，要求商品表的库存自动进行相应的调整.
create trigger tr3
after update on ts1
for each row
begin
update tt1 set num=num+old.num
where pro_id=old.pro_id;
update tt1 set num=num-new.num
where pro_id=new.pro_id;
end;//

update ts1 set num=8 where order_id='1002';

4、客户下订单，限购要求：每种商品最多只能购买5件，创建存储过程，自动调整下单num和商
品表相应的库存.

create trigger tr4
before insert on ts1
for each row
begin
if new.num>5 then
set new.num=5;
end if;
update tt1 set num=num-new.num
where pro_id=new.pro_id;
end;//

drop trigger 触发器名

mysql 数据字典
数据库:information_schema 注:为系统视图，不是基本表，不存存储数据。

表格和数据库的信息
select* from tables where table_schema='数据库名'|table_name='表名';

表格的字段名信息

select * from columns where talbes_schema='数据库名'|table_name='表名';

查询关联表的信息:
select * from KEY_COLUMN_USAGE where 条件;

1、添加用户：

crate user '用户名' @'服务器名' identified by '密码';

create user 'hongqianjin' @'localhost' identified by '123456';

drop user 'zhangsan' @'localhost';

select * from user;

2、赋予用户权限：

grant 权限 on 数据库.数据表 to '用户名'@'服务器';
grant select on company.* to 'hongqianjin'@'localhost';

全部权限:

grant all privileges on *.* to 'lisi'@'localhost' identified by '123456';

查看权限：
show grants for '用户名'@'服务器';

show grants for 'hongqianjin'@'localhost';

3、取消用户权：

revoke 权限 on 数据库.数据名 from '用户名'@'服务器’;

revoke select on test.* from 'hongqianjin'@'localhost';
4、刷新权限：

flush privileges;

5、删除用户：

drop user '用户名'@'服务器名'

drop user 'lisi'@'localhost';
6、修改密码：

update user set password=password('密码') where user='用户名'and host='服务器';

1.关闭服务器
net stop mysql;
2.使用mysqld服务登录，忽悠权限
mysql -n --skip- grant-tables
3使用客户端登录，无需密码

update user set password=password('密码') where user='用户名'and host='服务器';
4.修改管理员root密码，修改后刷新权限。
flush privileges;

5.关闭客户端和命令窗口，登录mysql

There’s a table X as below :

product	SALES	PRICE
A	50	70
B	70	80
C	60	90
D	100	70
E	10	40

Please draw the results from the query ;

Select X1.* FROM X AS X1 LEFT JOIN X AS X2 ON

X1.PRODUCT=X2.PRODUCT

WHERE X1.SALES>50;

product	SALES	PRICE
B	70	80
C	60	90
D	100	70

Select X1.* FROM X AS X1 LEFT JOIN X AS X2 ON

X1.PRODUCT=X2.PRODUCT

WHERE X2.SALES>50;

product	SALES	PRICE
B	70	80
C	60	90
D	100	70

There’s a table X as below :

Date	result
2005/5/9	win
2005/5/9	win
2005/5/9	loss
2005/5/9	loss
2005/5/10	win
2005/5/10	loss
2005/5/10	loss

How to write a SQL script to get the results like below?

Date	win	loss
2005/5/9	2	2
2005/5/10	1	2

Select * ,sun(result=’win’) as win,sun(result=’loss’) as loss from x group by dat;

select date,count(case when result='win' then 1 else null end) as win,
count(case when result='loss' then 1 else null end) as loss from x
group by date
Order by date desc;

mysql> select *,result='win' from x;

mysql> select *,sum(result='win') as win,sum(result='loss') as loss from x
    -> group by dat;

mysql> select *,if(result='win',result,null) from x;
    ->

mysql> select *,count(if(result='win',result,null)) as win from x
    -> group by dat;

请取出 tb_send表中日期（SendTime 字段）为昨天的所有记录：（sendTime 字段为datetime型，包含日期与时间）；

数据表A(i)为过去第n个月的表格，包括客户号 CUSTID、交易日期 TXNDATE、交易金额 TXNAMT、商户类型 MCC。

如何知道在过去的 3 个月里至少连续 2 个月有 POS 交易的客户数

select count(*) from

(select distinct custid from a1 Where custid in(select distinct custid from a2)

union

select distinct custid from a2 Where custid in(select distinct custid from a3)) as f1;

select count(*) from

(slect distinct custid from a1 inner join a2 on a1.custid=a2.custid

union

select distinct custid from a1 inner join a2 on a1.custid=a2.custid) as f1;

select custid from

(select distinct custid from A1

union all

select distinct custid from A2) as f1

group by custid having count(custid)>1;

表格A包含客户号custid，、交易日期 TXNDATE、交易金额 TXNAMT、商户类型 MCC，求客户的购买周期；

请用存储过程编写，要求输入两个时间参数，查询该时间段内客户的购买周期：

购买周期=(最后一次购买时间 – 第一次购买时间)/( 购买次数-1)

设定购买一次的客户购买周期是0；

create procedure pro4(in x date,in y date)
begin
select custid,ifnull(datediff(max(txndate),min(txndate))/
(count(custid)-1),0) as 购买周期 from
(select distinct custid,date(txndate) as txndate from A
where date(txndete)>x and date(txndate)<y
) as f1
group by custid;
end;//

create procedure pro4(in x date,in y date)
begin
select custid,datediff(max(txndate),min(txndate))/
(count(custid)-1) as 购买周期 from
(select distinct custid,date(txndate) as txndate from A
where date(txndete)>x and date(txndate)<y
) as f1
group by custid
having count(custid)>1
union
select custid,0 from
(select distinct custid,date(txndate) as txndate from A
where date(txndete)>x and date(txndate)<y
) as f1
group by custid
having count(custid)=1;

(select distinct custid,date(txndate) as txndate from A
where date(txndete)>x and date(txndate)<y
) as f1;

select custid,datediff(max(txndate),min(txndate))/
(count(custid)-1) as 购买周期 from f1
group by custid
having count(custid)>1
union
select custid,0 from f1
group by custid
having count(custid)=1;

select custid,ifnull(datediff(max(txndate),min(txndate))/
(count(custid)-1),0) as 购买周期 from f1
where txndete>时间1 and txndate<时间2
group by custid;

查看：
call pro4('2016-1-09','2016-12-09');

事务

(1) start transcation;

(2)

update a set mon=4500-100
where id=001;

(3) update a set mon=2000+100 where id='002';

(4)commit;

结束事务:

回滚:rollback;修改取消

提交:commit 事务文件---》数据表

2017年1月12日

1、 employee 表结构 comment’字段说明’

字段名	字段说明	数据类型	主键	外键	非空	唯一	自增
E_no	员工编号	INT(11)	是	否	是	是	否
E_name	员工姓名	VARCHAR(50)	否	否	是	否	否
E_gender	员工性别	CHAR(2)	否	否	否	否	否
Dept_no	部门编号	INT(11)	否	是	是	否	否
E_job	职位	VARCHAR(50)	否	否	是	否	否
E_salary	薪水	INT(11)	否	否	是	否	否
hiredate	入职日期	DATE	否	否	是	否	否

create table employee

(E_no int(11) not null primary key,

E_name varchar(50) not null,

E_gender char(2),

Dept_no int(11) not null,

E_job varchar(50) not null,

E_salary int(11) not null,

hiredate date not null,

constraint fk_ed foreign key(Dept_no) references dept(D_no)

);

2、 dept表结构

字段名	字段说明	数据类型	主键	外键	非空	唯一	自增
D_no	部门编号	INT(11)	是	否	是	是	是
D_name	部门名称	VARCHAR(50)	否	否	是	否	否
D_location	部门地址	VARCHAR(100)	否	否	否	否	否

create table dept

(D_no int(11) not null primary key auto_increment,

D_name varchar(50) not null,

D_location varchar(100)

);

3、 employee 表中的记录

E_no	E_name	E_gender	Dept_no	E_job	E_salary	hiredate
1001	SMITH	m	20	CLERK	800	2005-11-12
1002	ALLEN	f	30	SALESMAN	1600	2003-05-12
1003	WARD	f	30	SALESMAN	1250	2003-05-12
1004	JONES	m	20	MANAGER	2975	1998-05-18
1005	MARTIN	m	30	SALESMAN	1250	2001-06-12
1006	BLAKE	f	30	MANAGER	2850	1997-02-15
1007	CKARK	m	10	MANAGER	2450	2002-09-12
1008	SCOTT	m	20	ANALYST	3000	2003-05-12
1009	KING	f	10	PRESIDENT	5000	1995-01-01
1010	TURNER	f	30	SALESMAN	1500	1997-10-12
1011	ADAMS	m	20	CLERK	1100	1999-10-05
1012	JAMES	f	30	CLERK	950	2008-06-15

load data infile 'C:/Users/Administrator/Desktop/22.csv' into table employee

character set gbk

fields terminated by ','

optionally enclosed by '"'

escaped by '"'

lines terminated by '\r\n';

4、 dept表中的记录

D_no	D_name	D_location
10	ACCOUNTING	ShangHai
20	RESEARCH	BeiJing
30	SALES	ShenZhen
40	OPERATIONS	FuJian

load data infile 'C:/Users/Administrator/Desktop/11.csv' into table dept

character set gbk

fields terminated by ','

optionally enclosed by '"'

escaped by '"'

lines terminated by '\r\n';

作业：

1、将表employee、dept建在数据库company下

2、在employee表中，查询所有记录的e_no、e_name和e_salary字段值。

select e_no, e_name, e_salary from employee;

3、在employee表中，查询dept_no等于10和20的所有记录。

Select * from employee

Where Dept_no in (10,20);

4、在employee表中，查询工资范围在800-2500之间的所有记录。

Select * from employee

Where E_salary >=800 and E_salary <=2500;

5、在employee表中，查询部门编号为20的部门中的员工信息。

Select * from employee

Where Dept_no=20;

6、在employee表中，查询每个部门最高工资的员工信息。

select * from employee

where (dept_no, E_salary) in (select dept_no,max(E_salary) from employee group by dept_no);

select *,max(E_salary) from (select * from employee order by E_salary desc) as f1

group by dept_no;

7、查询员工BLAKE所在部门和部门所在地。

select D_name, D_location from dept

where Dept_no=(select D_no from employee where E_name='BLAKE');

select e_name,dept.* from employee left join dept

on employee .dept_no=dept.d_no

where E_name='BLAKE';

8、在employee表中，计算每个部门各有多少名员工。

select Dept_no,count( E_no) from employee

group by Dept_no;

9、在employee表中，计算不同类型职工的总工资数。

select E_job,sum(E_salary) from employee

group by E_job;

10、在employee表中，计算不同部门的平均工资。

select Dept_no,avg(E_salary) from employee

group by Dept_no;

11、在employee表中，查询工资低于1500的员工信息。

select * from employee

where E_salary<1500;

12、在employee表中，将查询记录先按部门编号由高到低排列，再按员工工资由高到低排列。

select * from employee

order by Dept_no desc, E_salary asc;

13、查询employee表中第4行到第8行的信息。

select * from employee

limit 3,5;

14、重新建立一张表，表名为new_emp,结构与employee相同，并且把数据输入到新表中。

create table new_emp like employee;

insert into new_emp

select * from employee;

15、将employee表中dept_no改为d_no,数据类型不变；

alter table employee drop foreign key fk_ed;

alter table employee drop key fk_ed;

alter table employee change dept_no d_no int(10) not null;

16、删除employee表中工资少于1000的数据；

delete from employee

where E_salary<1000;

索引：
设有N条随机记录，不用索引，平均查找N/2次。
如果使用索引后呢？

btree(二叉树)索引
log以2为底N的对数次

hash(哈希)索引：1次（理论上）

建立数据表books_1,在pubdate上建立普通索引，数据如下

字段名数据类型主键外键非空唯一自增索引
b_id int(11) 是否是是否否
b_name varchar(50) 否否是否否否
author varchar(100) 否否是否否否
price float 否否是否否否
pubdate year(4) 否否是否否普通索引
note varchar(100) 否否是否否否
num int(11) 否否是否否否

SQL语句如下：

create table books_1
(b_id int(11) null primary key,
b_name varchar(50) not null,
author varchar(100) not null,
price float not null,
pubdate year(4) not null,
note varchar(100) not null,
num int(11) not null,
index (pubdate));
二，在已经存在的表上创建索引；

alter table books_1 add key|index (num);
create index iprice on books_1(price);
三，删除索引

alter table books_1 drop key num;
drop index iprice on books_1;

视图：
视图是由查询结果形成的一张虚拟表，表格数据的更新，视图显示的数据也会同时更新，但是
，视图的增删改同样会修改表中的数据（只有一一对应的时候才可以修改）。

create (algorithm=merge|temptable|undefined) view 视图名
as select 字段名 from 表名
where 条件;

create view vfruits as select * from fruits;

create view vfruits as select * from fruits inner join suppliers on fs_id=

show table status;

drop view vfruits;

2017年1月13日

1、创建数据库school；

2、创建数据表class，自己设定表格结构，并存储数据；（可直接导入数据无代码）

专业编号	专业名	学院名	成立时间
001	统计	经济学院	1965/9/1
002	自动化	电气工程学院	1945/9/1
003	电气	电气工程学院	1945/9/1
004	外语	外语学院	1985/9/1
005	化学	化工学院	1955/9/1
006	计算机	信息科学与工程学院	1995/9/1
007	物流	管理学院	1995/9/1

3、创建数据表student，自己设定表格结构，并存储数据；（可直接导入数据无代码）

注：性别中1为男，2为女；

学号	专业编号	姓名	性别	英语	数学	语文	计算机	体育
1501	001	张三	1	64	56	60	74	63
1532	002	李四	1	51	63	57	84	63
1554	002	赵武	1	76	87	81	56	75
1558	001	王六	1	75	75	75	72	74
1534	003	赵明鱼	1	42	76	38	68	45
1564	003	诸葛强	1	76	77	76	70	74
1507	004	张秀	2	81	56	68	87	73
1589	005	王五	1	86	74	80	64	76
1509	005	徐凤东	1	79	85	39	66	46
1510	005	郑明明	2	79	79	35	78	59
1511	006	张宝全	1	77	77	77	73	76
1512	006	刘泽宏	1	77	74	75	77	75
1576	006	贺军委	2	76	78	77	76	76
1523	003	于晨	2	78	78	78	79	78
1515	003	刘立彪	1	75	79	39	79	49
1567	002	叶震伟	1	91	74	82	78	81
1517	002	王志平	2	85	78	81	81	81
1543	007	黄莺	2	82	78	80	84	81
1519	007	王文彬	1	91	87	89	94	90

4、统计表中男女各自的人数；

5、统计表中各科全部及格的人数；

6、查询student中总成绩前三名的信息；

7、统计表中总平均分以上的人数；

8、查询各专业的平均分；

9、查询各科成绩的及格率；

10、查询每位同学的信息和不及格的科目数；

11、学号尾数为4的学生总成绩；

12、查询建立时间超过50年的学院学生各有多少人；

13、如果总分不到300分的学生‘留级’，总分数在不到350分的学生进‘三中’，总分不到400分的进‘二中’，总分达到400分的进‘一中’，请在表中添加字段名‘所升学校’，并添加数据；

14、学号为’1511’的同学语文成绩误判，现改为68分；

15、不及格的科目要求重考，每科补考费60元，student中同学补考费总共多少元；

16、因各个学校整改，现外语专业调到别的学校，现把外语专业的信息删除；

17、查询总分在400分以上的学生和专业的信息；

18、怎样确保student表中专业编号的范围不会超出class表中专业编号的范围；

19、如果知道学生的学号X，查询出他的学号、姓名、性别（’男’,’女’）、专业名、各科成绩、总成绩的信息，请使用存储过程来实现；

20、查询class表中记录没有重复的信息；

1、create database school;

2、创建表格class：
create table class
(专业编号 char(10) primary key,
专业名 char(50),
学院名 char(50),
成立时间 date
);

插入数据：
load data infile 'C:/Users/Administrator/Desktop/西线学院/MYSQL/aa.csv' into table class
character set gbk
fields terminated by ','
optionally enclosed by '"'
escaped by '"'
lines terminated by '\r\n';

3、创建表格student：
create table student
(学号 int(11) primary key,
专业编号 char(10),
姓名 char(50),
性别 int(11),
英语 int(11),
数学 int(11),
语文 int(11),
计算机 int(11),
体育 int(11),
constraint f_key foreign key(专业编号) references class(专业编号)
);

插入数据：
load data infile 'C:/Users/Administrator/Desktop/西线学院/MYSQL/bb.csv' into table student
character set gbk
fields terminated by ','
optionally enclosed by '"'
escaped by '"'
lines terminated by '\r\n';

4、
select if(性别=1,'男','女') 性别,count(性别) as 人数 from student
group by 性别;

5、
select count(*) as 全部及格人数 from student
where 英语>=60 and 数学>=60 and 语文>=60 and 计算机>=60 and 体育>=60;

select sum(英语>=60 and 数学>=60 and 语文>=60 and 计算机>=60 and 体育>=60) as 全部及格人数 from student;

6、
select * from student
order by 总分 desc
limit 3;

7、select count(*) from student
where 总分>(select avg(总分) from student);

8、select 专业编号,avg(英语),avg(数学),avg(语文),avg(计算机),avg(体育),round(avg(总分),2) from student
group by 专业编号;

9、
select concat(round(sum(英语>=60)/count(学号)*100,2),'%') as 英语及格率,
concat(round(sum(数学>=60)/count(学号)*100,2),'%') as 数学及格率,
concat(round(sum(语文>=60)/count(学号)*100,2),'%') as 语文及格率,
concat(round(sum(计算机>=60)/count(学号)*100,2),'%') as 计算机及格率,
concat(round(sum(体育>=60)/count(学号)*100,2),'%') as 体育及格率
from student;
10、
select *,(英语<60)+(数学<60)+(语文<60)+(计算机<60)+(体育<60) as 不及格科目数 from student;
11、
select 学号,总分 from student
where 学号 like '%4';
12、
select 学院名,count(学号) as 人数 from student inner join class
on student.专业编号=class.专业编号
where year(成立时间)<year(now())-50
group by 学院名;
13、
select *,case
when 总分<300 then '留级'
when 总分<350 then '三中'
when 总分<400 then '二中'
else '一中'
end as 所升学校
from student;
14、
update student set 语文=68 where 学号='1511';
15、
select *,((英语<60)+(数学<60)+(语文<60)+(计算机<60)+(体育<60))*60 as 补考费 from student;

16、
delete from student
where 专业编号=(select 专业编号 from class where 专业名='外语');
delete from class
where 专业名='外语';
17、
select student.*,class.* from student inner join class
on student.专业编号=class.专业编号
where 总分>400;
18、
alter table student add constraint wj foreign key(专业编号) references class(专业编号);
19、
delimiter //
create procedure pro(in x int)
begin
select 学号,姓名,专业名,if(性别=1,'男','女'),英语,数学,语文,计算机,体育,总分
from student inner join class
on student.专业编号=class.专业编号
where 学号=x;
end;//
20、
select * from class
group by 专业编号,专业名,学院名,成立时间
having count(*)=1;

总结

插入数据

insert into 表1（字段）select 字段名 from表2 where 条件；

1、创建数据库company

2、创建表格personnel，该表结构如下：

Field	Type	Null	Key	Default	Extra
员工编号	char(10)	NO	PRI	NULL
员工姓名	varchar(50)	NO		NULL
性别	char(10)	NO		NULL
年龄	int(11)	NO		NULL
所属部门	int(11)	NO		NULL
工资	float(10,2)	NULL		NULL

3、创建成绩表department，该表结构如下：

Field	Type	Null	Key	Default	Extra
部门编号	int(11)	NO	PRI	NULL
部门名称	varchar(50)	NO	UNI	NULL
部门地址	varchar(50)	NO		NULL

4、向数据表personnel中插入如下数据：

员工编号	员工姓名	性别	年龄	所属部门	工资
001	程亮	男	27	1001	8000
002	刘辉	男	24	1001	5000
003	苏康	男	26	1001	7500
004	王红	女	24	1001	5200
005	张三	男	28	1001	7200
006	唐宏	男	25	1001	5600
007	丁丁	女	24	1001	5200
008	李乐	男	26	1001	5900
009	郑艳	女	31	1002	7500
010	徐丽	女	25	1002	4500
011	崔霞	女	26	1002	5800
012	苏德	男	29	1003	8500
013	赵康	男	26	1003	7600
014	孙晓刚	男	24	1003	5600
015	王红	女	26	1003	6000
016	李明	男	29	1003	8200
017	李小小	女	24	1004	5500
018	周小康	男	25	1004	6300
019	李秀霞	女	26	1004	7000
020	赵晓晓	女	24	1004	5200
021	王红	女	27	1004	7650
022	李乐	男	23	1003	4800

5、向数据表department中插入如下数据：

部门编号	部门名称	部门地址
1001	销售部	A座1楼
1002	财务部	A座3楼
1003	技术部	A座3楼
1004	人事部	A座2楼

6、查询personnel表中员工的编号，姓名，年龄，部门名称，工资的信息；

7、查询department表中各个部门的人数；

8、对personnel表中员工工资进行倒叙排序；

9、查询personnel表中第5行到第10行的数据；

10、因公司销售状况超出预计目标，故提高销售部人员的工资，工资提高10%，对personnel的数据进行更新；

11、查询出生日期在1990年之前的员工的信息及其所在部门的信息；

12、查询该公司在A座中3楼有多少人；

13、查询personnel表中的信息，信息中添加‘工资范围’一列，主要有’5000元之内’,’5000到8000’，’8000到10000’和’10000以上’;

14、查询员工工资在平均工资以上的有哪些；

15、在’三八妇女节’,公司对员工发福利，女员工工资在5000元以内的发200元,5000元到8000元的发300元,8000元以上的发400元，男员工统一发200元，请问公司合计要花费多少；

16、创建一个简单的存储过程pro1，要求调用存储过程时，输入员工编号参数可以查询该员工的信息；

17、创建一张新表person_new,将personnel和department的信息全部存储进去；

18、在personnel表中的所属部门上添加索引；成功后删除该索引；

19、怎样才能确保personnel表中部门编号的范围在department表中部门编号的范围之内；

20、查询personnel表中同名同性的人员的信息；

21、请创建一个存储过程pro2，要求调用该索引时输出1+2+…+100的和；

方法很多，不一定一样

1、create database company;

use company;

2、CREATE TABLE personnel (
员工编号 char(10) NOT NULL,
员工姓名 varchar(50) NOT NULL,
性别 char(10) NOT NULL,
年龄 int(11) NOT NULL,
所属部门 int(11) NOT NULL,
工资 float(10,2) NOT NULL,
primary key(员工编号)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

3、CREATE TABLE department (
部门编号 int(11) NOT NULL,
部门名称 varchar(50) NOT NULL,
部门地址 varchar(50) NOT NULL,
PRIMARY KEY (部门编号)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

4、INSERT INTO personnel VALUES
('001','程亮','男',27,1001,8000),
('002','刘辉','男',24,1001,5000),
('003','苏康','男',26,1001,7500),
('004','王红','女',24,1001,5200),
('005','张三','男',28,1001,7200),
('006','唐宏','男',25,1001,5600),
('007','丁丁','女',24,1001,5200),
('008','李乐','男',26,1001,5900),
('009','郑艳','女',31,1002,7500),
('010','徐丽','女',25,1002,4500),
('011','崔霞','女',26,1002,5800),
('012','苏德','男',29,1003,8500),
('013','赵康','男',26,1003,7600),
('014','孙晓刚','男',24,1003,5600),
('015','王红','女',26,1003,6000),
('016','李明','男',29,1003,8200),
('017','李小小','女',24,1004,5500),
('018','周小康','男',25,1004,6300),
('019','李秀霞','女',26,1004,7000),
('020','赵晓晓','女',24,1004,5200),
('021','王红','女',27,1004,7650),
('022','李乐','男',23,1003,4800);

5、INSERT INTO department
VALUES (1001,'销售部','A座1楼'),
(1002,'财务部','A座3楼'),
(1003,'技术部','A座3楼'),
(1004,'人事部','A座2楼');

6、select 员工编号,姓名,年龄,部门名称,工资 from personnel,department
where 所属部门=部门编号;

7、select 部门编号,部门名称,count(*) as 部门人数 from personnel,department
where 所属部门=部门编号
group by 部门编号;

8、select * from personnel
order by 工资 desc;

9、select * from personnel
limit 4,6;

10、update personnel set 工资=工资*1.1
where 所属部门 in
(select 部门编号 from department where 部门名称='销售部');

11、select * from personnel,department
where 所属部门=部门编号 and year(now())-1990<年龄;

12、select 部门地址,count(*) as 人数 from department join personnel
on 所属部门=部门编号
where 部门地址='A座3楼';

13、select *,case
when 工资<5000 then '5000元以内'
when 工资<8000 then '5000到8000'
when 工资<10000 then '8000到10000'
else '10000元以上'
end as 工资范围
from personnel;

14、select * from personnel
where 工资>(select avg(工资) from personnel);

15、select sum(工资<5000 and 性别='女')*200+
sum(工资 between 5000 and 8000 and 性别='女')*300
+sum(工资>8000 and 性别='女')*400+sum(性别='男')*200 as 花费
from personnel;

16、delimiter //
create procedure pro1(in x char(10))
begin
select * from personnel where 员工编号=x;
end ; //

delimiter ;

17、create table person_new as
select * from personnel left join department on 所属部门=部门编号
union
select * from personnel right join department on 所属部门=部门编号;

18、alter table personnel add index (所属部门);

alter table personnel drop index 所属部门;

19、alter table personnel add constraint fk_pd foreign key(所属部门) references department(部门编号);

20、select * from personnel
where (姓名,性别) in(
select 姓名,性别 from personnel
group by 姓名,性别
having count(*)>1);

21、delimiter //
create procedure pro2(out z int)
begin
declare i int default 0;
declare s int default 1;
while s<=100 do
set i=i+s;
set s=s+1;
end while ;
set z=i;
select z ;
end ; //

delimiter ;

2017年1月14日

2017年2月5日

1.一元线性回归

(1)相关关系特指就是这种直线关系。

(2)相关关系指共同的趋向，但没有因果关系。

(3)一元线性回归只涉及到一个变量的回归，自变量(x)和因变量(y)之间是线性关系。

注:是yi的平均值

决定系数

(1)反映模型的解释能力

(2)反映回归直线的拟合程度

(3)决定系数平方根等于相关系数(仅仅在一元线性回归合适)

(4)决定系数特点

取值范围在[0,1]之间

R2(R的平方)->1,说明回归方程拟合的越好

R2(R的平方)->0,说明回归方程拟合的越差

2.方差分析

1.掌握了t检验后，就可以对两组样本的平均水平进行比较了。但如果面对的是多组样本的比较，t检验就不行了，可以用方差分析。

2.方差分析的基本思想:把数据的全部变异--总变异分解成两个或者多个组成部分，在做分析。

3.方差分析的术语:观测变量、控制变量、控制变量的水平

4.方差分析的应用:两组或两组以上均数是否相等，两个或多个因素的交互作用，回归方程的线性假设检验

5.总方差:反映全部个体的变异情况

6.组内方差

(1)各组组内个体之间的变异

(2)属于随机误差

7.组间方差

(1)各组均数和总均数的差异

(2)除了随机误差，还可能有系统误差

(3)F值

2017年2月6日

1.相关性分析:

(1)在做数据分析时，除了关注每组数据自身的特点，我们还会关系数据和数据之间的关系，是否有着某种共同的变化趋势，这种趋势就是相关性，观察相关性最简单的方法就是用可视化进行探索。除此之外，还可以借助一些数字指标进行量化。

(2)相关性分析概述:

(I)函数关系:对应的确定关系

(2)统计关系:线性关系和非线性关系。

(3)线性关系:正线性关系、负线性关系

2.线性关系的量化指标

(1)Pearson(皮尔逊)相关系数:两个数值变量间的相关性。

(2)相关系数特点:取值范围是[-1,1],|r|趋势于0表示关系越弱。对称性，x与y的相关系数和y与x之间的相关系数相等。r值大小与x和y尺度无关。x与y之间线性关系的一个度量，不能用于描述非线性关系。

(3)相关性强弱：

(I)|r|>0.8时，高度相关

(II)0.5<|r|<0.8时，中度相关

(III)0.3<|r|<0.5时，低度相关

(IV)|r|<0.3时，相关程度极弱，可视为不相关。

(4)相关系数的检验

2017年2月7日

1.假设检验

(1)在完成基础统计分析后，我们对数据轮廓有了一定掌握。通过数据分析比较，也形成初步结论。但我们得到的结论、做出的判断，到底有多可信？借助假设检验的这个工具可以得到科学的判断。

(2)假设检验执行步骤:建立要检验的假设，确定检验水准，选择并计算事宜的统计量，确定P值，做出推断。

(3)t分布的特点

(I)单峰，以0为中心，左右对称

(II)分布形态和样本数量n有关

(III)n->无穷大时，逼近标准正态曲线

(IV)t曲线不是一条曲线，而是一族曲线

(4)t检验是基于t分布的比较均数的检验方法

2017年2月9日

练习题
下面三个关系表：
CARD 借书卡： CNO 卡号，NAME 姓名，CLASS 班级
BOOKS 图书表： BNO 书号，BNAME 书名,AUTHOR 作者，PRICE 单价，QUANTITY 库存册数
BORROW 借书记录： CNO 借书卡号，BNO 书号，RDATE 还书日期
备注：限定每人每种书只能借一本；库存册数随借书、还书而改变。

要求实现如下15个处理：

1、写出建立BORROW表的SQL语句，要求定义主码完整性约束和引用完整性约束。

2、找出借书超过5本的读者,输出借书卡号及所借图书册数。

3、查询借阅了"水浒"一书的读者，输出姓名及班级。

4、查询过期未还图书，输出借阅者（卡号）、书号及还书日期。

5、查询书名包括"网络"关键词的图书，输出书号、书名、作者。

6、查询现有图书中价格最高的图书，输出书名及作者。

7、查询当前借了"计算方法"但没有借"计算方法习题集"的读者，输出其借书卡号，并按卡号降
序排序输出。

8、将"C01"班同学所借图书的还期都延长一周。

9、从BOOKS表中删除当前无人借阅的图书记录。

10、如果经常按书名查询图书信息，请建立合适的索引。

11、在BORROW表上建立一个触发器，完成如下功能：如果读者借阅的书名是"数据库
技术及应用"，就将该读者的借阅记录保存在BORROW_SAVE表中（注ORROW_SAVE表
结构同BORROW表）。

12、建立一个视图，显示"力01"班学生的借书信息（只要求显示姓名和书名）。

13、查询当前同时借有"计算方法"和"组合数学"两本书的读者，输出其借书卡号，并按
卡号升序排序输出。

14、假定在建BOOKS表时没有定义主码，写出为BOOKS表追加定义主码的语句。

15、对CARD表做如下修改：
a. 将NAME最大列宽增加到10个字符（假定原为6个字符）。
b. 为该表增加1列NAME（系名），可变长，最大20个字符。

要求实现如下15个处理：
1、写出建立BORROW表的SQL语句，要求定义主码完整性约束和引用完整性约束。

create table borrow
(cno char(15) not null,
bno char(20) not null,
rdate date not null,
primary key(cno,bno)，
constraint fk_cbw foreign key(cno) references card(cno),
constraint fk_bb foreign key(bno) references books(bno));

delimiter //
create trigger tr1
after insert on borrow
for each row
begin
update books set quantity=quantity-1
where bno=new.bno;
end; //

delimiter ;

delimiter //
create trigger tr2
after delete on borrow
for each row
begin
update books set quantity=quantity+1
where bno=old.bno;
end; //

delimiter ;

2、找出借书超过5本的读者,输出借书卡号及所借图书册数。

select cno,count(bno) as 借书数 from borrow
group by cno
having count(bno)>5;

3、查询借阅了"水浒"一书的读者，输出姓名及班级。

select card.cno,name,class ,bname from borrow,card,books
where card.cno=borrow.cno and books.bno=borrow.bno
and bname='水浒';

4、查询过期未还图书，输出借阅者（卡号）、书号及还书日期。

select card.cno,bno,rdate from card inner join borrow
on card.cno=borrow.cno
where curdate()>rdate;

5、查询书名包括"网络"关键词的图书，输出书号、书名、作者。

select bno,bname,author from books
where bname like '%网络%';

6、查询现有图书中价格最高的图书，输出书名及作者。

select bname,author from books
order by price desc
limit 1;

7、查询当前借了"计算方法"但没有借"计算方法习题集"的读者，输出其借书卡号，并按卡号降序排序输出。

select distinct cno from books inner join borrow
on borrow.bno=books.bno
where bname='计算方法' and
cno not in(select cno from borrow
where bno in(select bno from books where bname='计算方法习题集'))order by cno desc
;

8、将"C01"班同学所借图书的还期都延长一周。

update borrow set rdate=date_add(rdate,interval 1 week);

9、从BOOKS表中删除当前无人借阅的图书记录。

delee from books
where not exists(select bno from borrow where bno=books.bno);

10、如果经常按书名查询图书信息，请建立合适的索引。

alter table books add unique(bname);

create table borrow_save like borrow;

delimiter //

create trigger tr23
after insert on borrow
for each row
begin
insert into borrow_save
select distinct new.cno,new.bno,new.rdate from borrow
where new.bno=(select bno from books where bname='数据库技术及应用');

end; //

12、建立一个视图，显示"01"班学生的借书信息（只要求显示姓名和书名）。

create vuew vborrow as select name,bname from card ,borrow,books
where card.cno=borrow.cno and borrow.bno=books.bno and class='01';

13、查询当前同时借有"计算方法"和"组合数学"两本书的读者，输出其借书卡号，并按
卡号升序排序输出。

select cno from card,borrow,books
where card.cno=borrow.cno and borrow.bno=books.bno
and (bname='计算方法' or bname='组合数学')
group by cno
having count(*)=2 order by cno asc;

14、假定在建BOOKS表时没有定义主码，写出为BOOKS表追加定义主码的语句。

alter table books add primary key(bno);

15、对CARD表做如下修改：
a. 将NAME最大列宽增加到10个字符（假定原为6个字符）。
b. 为该表增加1列NAME（系名），可变长，最大20个字符。

alter table card modify name char(10) not null;

alter table card add xname varchar (20);

一、数组的组织和整理--数据预处理(R语言)

1.缺失值的表达方式: NA NaN Inf -lnf

2.发现缺失值

summary(),is.na(), complete.case()

3.缺失值是否有业务含义

4.把不合理数据编码为缺失值

5.加载包:library(VIM)

6.查看数据:str(sleep)

7.summary(sleep)

8.发现缺失值

(I)属性级别

is.na(sleep$Sleep)

(II)is.na(NaN)

(III)

> is.na(NaN)
[1] TRUE
> is.na(-Inf)
[1] FALSE
> is.na(Inf)
[1] FALSE
> is.infinite(Inf)
[1] TRUE

(IV)

sleep$Sleep[is.na(sleep$Sleep)]

(V)

sleep$Sleep[!(is.na(sleep$Sleep))]

(VI)记录级别

complete.cases(sleep)

(VII)

> sleep.cleaned=sleep[complete.cases(sleep),]
> View(sleep.cleaned)
> sellp.na=sleep[!(complete.cases(sleep)),]
> View(sleep.cleaned)
> sum(is.na(sleep$Sleep))
[1] 4
> mean(is.na(sleep$Sleep))
[1] 0.06451613

2.完全随机缺失

Missing Completely At Random,MCAR

随机缺失

Missing At　Random,MAR

非随机缺失

NMAR

3.查看包的帮助文档

> help(package='VIM')

4.分析函数

na.rm=T

5.行删除

na.omit

6.配对删除

典型函数cor(data,use="pairwise.complete.obs")

2016年2月10日

最后一定要 shift+ctrl+Enter

1.LOOKUP函数:

数据可视化-基础绘图体系(R)

图形设备 pdf png jpeg bmp win.metafile postscript dev.off

高水平绘图函数 plot pairs hist

低水平绘图函数 points lines text abline legend title axis

2017年2月11日

日期时间、字符串的处理

字符串

nchar() length()

paste() outer()

substr() strsplit()

Sub() gsub() grep() regexpr() grepexpr()

dfdd

数据集成

merge

pylr::join

透视表分组

改成分秒格式

输入数组

2017年2月12日

1.最近邻分类法基于距离的比较，本质上赋予每个属性相等的权重。因此，当数据存在噪声或不相关属性时，它们的准确率可能受到影响。

2.最近邻分类法在对检验元组分类时可能非常慢。

3.粗糙集理论可以用于分类，发现不准确数据或噪声数据内的结构联系。它用于离散值属性。因此，连续值属性必须在使用前离散化。

4.粗糙集理论基于给定训练数据内部的等价类的建立。形成一个等价类的所有元组是不加区分的;也就是说，对于描述数据的属性，这些样本是等价的。

5.半监督分类使用有类标号的数据和无类标号的数据构建分类器。

6.自我训练是半监督分类的最简单形式。

7.协同训练是半监督分类的另一种形式，其中两个或多个分类器互教互学。理想地，每个学习器都对每个元组使用一个不同的、理想的独立特征集。

8.主动学习是一种迭代的监督学习，适合数据丰富但类标号稀缺或获取昂贵的情况。学习算法是主动的，因为它可能有目的地向用户询问类标号。通常，这种方法用于学习概念的元组数远小于典型的监督学习所需要的数量。

9.高维数据聚类方法可以分成两类:子空间聚类方法和维归约方法。

10.如何从高维数据中发现子空间簇？大致可以划分成三个主要类别:子空间搜索方法、基于相关性的簇聚类方法和双聚类方法。

11.聚类的最优化目标包含两部分:优化聚类质量和最小化违反约束的罚。总体目标函数是聚类质量得分和罚得分的组合。

12.基于密度的离群点检测方法的基本假设是:非离群点对象周围的密度与其领域周围的密度类似，而离群点对象周围的密度显著不同于其领域周围的密度。根据这个假定，基于密度的离群点检测方法使用对象和其近邻的相对密度指示对象是离群点的程度。

13.朴素贝叶斯分类法假定一个属性值在给定类上的影响独立于其他属性的值。

2017年02年13日

1.VBE

开发工具选项卡调用:选项----自定义功能区

VBE就是VBA的编辑窗口，所有的VBA操作都在VBE里完成

ALT+F11快捷键打开VBE

填充颜色代码: Selection.Interior.Color = 8883

Sub 宏4()
' ActiveCell.FormulaR1C1 = "数据分析"
Selection = "数据分析"
Selection.Font.Size = 20
Selection.Font.Bold = True
End Sub

注意:ActiveCell 是当前活动的单元格，Selection则是选择的单元格

4.数据类型

类型	值范围
Byte/字节型	0 - 255
Integer（%）/整数型	-32,768 - 32,767
Long（&）/长整型	-2,147,483,648 - 2,147,483,648
Single（！）/单精度浮点型	-3.402823E+38 ~ -1.401298E-45 为负值 1.401298E-45 ~ 3.402823E+38 为正值/
Double（#）/双精度浮点型	-1.79769313486232e+308 ~ -4.94065645841247E-324 为负值 4.94065645841247E-324 ~ 1.79769313486232e+308 为正值
Currency（@）/货币类型	-922,337,203,685,477.5808 to 922,337,203,685,477.5807
Decimal/小数型	+/- 79,228,162,514,264,337,593,543,950,335 if no decimal is use +/- 7.9228162514264337593543950335 (28 decimal places).

类型	值范围
String(fixed length) （$）/字符串型	1 ~ 65,400 字符
String(variable length) （$）/字符串型	0 ~ 20亿个字符
Date/日期类型	1月 1, 100 到12月 31, 9999(2016-04-01)
Boolean/布尔类型	True 或False
Object/对象类型 worksheet workbook range	任何嵌入对象
Variant(numeric)/变体型	任何Double值一样大
Variant(text) )/变体型（arr）	同为可变长度的字符串

1.大数据在国际标准上是4v,也就是数量、多样、价值、速度。

(1)数量就是数据体量特别大。

(2)多样就是数据的种类和类型非常多，它包括网络日志、视频、图片、地理位置、信息、统计等等调查，这些全包括。

(3)深入挖掘，就能产生出商业价值，换言之就是价值密度低，商业价值高。

(4)处理速度快。

2.大数据三原则

(1)首先大数据它不是随机样本，而是全体数据。

(2)大数据不是精确性的，而是混杂性。

(3)大数据彼此不是因果关系，而是相关关系。

3.数据特征化

(1)数据特征化是目标类数据的一般特征或特征的汇总。通常，通过查询来收集对应于用户指定类的数据。

(2)数据特征化的输出可以用多种形式提供，例如饼图、条图、曲线、多维数据立方体和包括交叉表在内的多维表。结果描述也可以用广义关系或规则(称做特征规则)形式提供。

4.数据区分是将目标类数据对象的一般特性与一个或多个对比类对象的一般特征进行比较。目标类和对比类可以由用户制定,而对应的数据对象可以通过数据库查询检索。

5.频繁模式是在数据中频繁出现的模式。存在多种类型的频繁模式，包括频繁项集、频繁子序列(又称序列模式)和频繁子结构。

6.频繁项集挖掘是频繁模式挖掘的基础。

7.离群点数据分析称做离群点分析或异常挖掘。

8.数据质量涉及许多因素,包括准确性、完整性、一致性、时效性、可信性和可解释性。

9.考察数据预处理的主要步骤,即数据清理、数据集成、数据规约和数据变换。

(1)数据清理例程通过填写缺失的值，光华噪声数据，识别或删除离群点，并解决不一致性来”清理”数据。

10.主成分分析(principal components analysis)或PCA(又称Karhunen-Loeve或K-L方法)搜索k个最能代表数据的n维正交向量，其中k<=n,这样，原数据投影到一个小得多的空间上，导致维规约。与属性子集选择通过保留属性集的一个子集来减少属性集的大小不同，PCA通过创建一个替换的、较小的变量”集合”属性的基本要素。原数据可以投影到该较小的集合中。PCA常常能够揭示先前未曾察觉的联系，并因此允许不寻常的结果。

11.PCA可以用于有序和无序的属性，并且可以处理稀疏和倾斜数据。多于二维的多维数据可以通过将问题归约为二维问题来处理。主成分可以用做多元回归和聚类分析的输入。与小波变换相比，PCA能够更好地处理稀疏数据，而小波变换更适合高维数据。

12.数据仓库是一个面向主题的、集成的、时变的、非易失的数据集合，支持管理者的决策过程。

13.通常，数据仓库采用三层体系结构:

(1)底层是仓库数据库服务器，它几乎总是一个关系数据库系统。

(2)中间层是OLAP服务器，其典型的实现使用(i)关系OLAP(ROLAP)模型(即扩充的关系DBMS,它将多维数据上的操作映射为标准的关系操作),或使用(ii)多维OLAP(MOLAP)模型(即专门的服务器,它直接实现多维数据和操作)。

(3)顶层是前端客户端，它包括查询和报告工具、分析工具和或数据挖掘工具(例如,趋势分析，预测等)。

14.多维数据分析的核心是有效地计算多维集合上的聚集。

15.置信区间是一个以给定的高概率涵盖正真总体值估计的值域。

16.至少有4种方法可以把OLAP风格的分析与数据挖掘技术融合在一起。

(1)使用立方体空间为数据挖掘定义数据空间。

(2)使用OLAP查询为挖掘产生特征和目标。

(3)使用数据挖掘模型作为多步挖掘过程的构建。

(4)使用数据立方体计算技术加快重复模型的构建。

17.一般而言，关联规则的挖掘是一个两步的过程:

(1)找出所有的频繁项集:根据定义，这些项集的每一个频繁出现的次数至少与预定义的最小支持计算min_sup一样。

(2)由频繁项集产生强关联规则:根据定义,这些规则必须满足最小支持度和最小置信度。

18.基于模式的多样性,模式挖掘可以使用如下标准进行分类:

(1)基本模式:频繁模式可能有多种形式，包括简单的频繁模式、闭幕式和极大模式。

(2)基于模式所涉及的抽象层:模式或关联规则可能具有处于高、低，或多个抽象层的项。

(3)基于规则或模式所涉及的维数:如果关联规则或模式中的项或属性只涉及一个维，则它是单维关联规则/模式。

19.数据挖掘研究的一般线路图

20.根据约束如何与模式挖掘过程配合，模式剪纸约束可以分为五类：(1)反单调的;(2)单调的;(3)简洁的;(4)可转变的(5)不可转变的

21.模式融合合并大模式的小的子模式，而不是用单个项增量地扩展模式，因此，该方法有一个优点，绕过中型模式，沿着通往可能的巨型模式的路径前进。

22.分类是一种最重要的数据分析形式，它提取刻画重要数据类的模型。这种模型称为分类器，预测分类的(离散的、无序的)类标号。

23.数据分类是一个两阶段过程，包括学习阶段(构建分类模型)和分类阶段(使用模型预测给定数据的类标号)。

24.聚类是一个把数据对象集划分成多个组或簇的过程，使得簇内的对象具有很高的相似性，但与其他簇中的对象很不相似。

25.数据仓库是一个从多个数据源收集的信息存储库，存放在一致的模式下，并且通常驻留在单个站点上。数据仓库通常通过数据清理、数据变换、数据集成、数据装入和定期数据刷新来构造。

(1)通常，数据仓库用称作数据立方体的多维数据结构建模。

26.数据特征化是目标类数据的一般特征或特征的汇总。通常，通过查询来收集对应于用户指定类的数据。

27.数据区分是将目标类数据对象的一般特征与一个或多个对比类对象的一般特征进行比较。目标类和对对比类可以由用户指定，而对应的数据可以通过数据库查询检索。

28.分类预测类别(离散的，无序的)标号，而回归建立连续值函数模型。也就是说，回归用来预测缺失的或难以获得的数值数据值，而不是(离散的)类标号。

29.离群点数据分析称作离群点分析或异常挖掘。

30.作为一个应用驱动的领域，数据挖掘吸纳了诸如统计学、机器学习、模式识别、数据库和数据仓库、信息检索、可视化、算法、高性能计算和许多应用领域的大量技术。

31.有效性、可伸缩性、性能、优化以及实时运行能力是驱动许多数据挖掘新算法开发的关键标准。

32.给定基本防踢，方体的物化有三种选择:

(1)不物化(2)完全物化(3)部分物化

33.聚类、决策树和相关分析可以用于数据离散

33.数据仓库提供联机分析处理(OLAP)工具，用于各种粒度的多维数据的交互分析，有利于有效的数据泛化和数据挖掘。OLAP工具通常使用数据立方体和多维数据模型，对汇总数据提供灵活的访问。

34.大规模数据集下的一些重要的特征抽取类型，它们包括以下两种

(1)频繁项集(2)相似项.

35.序列是事件的有序列表。根据事

件的特征，序列数据可以分层三类：(1)时间序列数据(2)符号序列数据(3)生物学序列法

36.序列分类方法可以分成三类:(1)基于特征的分类，它们把序列转换成特征向量，然后使用传统的分类方法;(2)基于序列距离的分类，其中度量序列之间相似性的距离函数决定分类的质量(3)基于模型的分类。

37．可视化数据挖掘包括数据可视化、数据挖掘结果可视化、数据挖掘过程可视化和交互的可视数据挖掘。

Sub 消息框弹出()
MsgBox "学习VBA"
MsgBox 12345
MsgBox prompt:="学习VBA", Buttons:=vbYesNo, _
Title:="数据分析"
MsgBox "学习VBA", vbYesNo, "数据分析"
MsgBox 56 * 2
End Sub

Sub 消息框弹出()
Const pi = 3.1415
'Dim a As Integer
a = 200
Debug.Print pi * a

End Sub

Sub 打开工作薄()
Dim wkb As Workbook
Set wkb = Workbooks.Open("C:\Users\lx\Desktop\新建 Microsoft Excel 工作表.xlsm")
End Sub

'----------------------------------------------------
'单元格对象在VBA中一个非常基础，同时也很重要的。
'它的表达方式也是非常的多样化。
'----------------------------------------------------
'Range 对象
'代表某一单元格、某一行、某一列、某一选定区域（该区域可包含一个或若干连续单元格区域），或者某一三维区域。

'Range ("文本型装单元格地址")

'range的常见写法
Sub rng()
Range("a1").Select         '单元格
Range("a:a").Select        '列
Range("1:3").Select       '行
Range("a1:b10").Select     '相邻区域
Range("a1:d7,c4:e8").Select  '不相个邻区域
End Sub

Sub range引用区域且有变量()
Dim i
i = 1
Range("a1:c" & i).Select '引用单元格是区域且有变量
Cells(i, "c").Select '引用的是单个单元格且有变量
[a1:a10].Select '引用的是区域或单元格且无变量
End Sub

2017年2月14日

表1、员工表employee的结构：

属性含义	属性名	数据类型
员工编号	employeeno	char(8)
员工姓名	employeename	varchar(10)
性别	sex	char(1)
出生日期	birthday	datetime
住址	address	varchar(50)
电话	telephone	varchar(20)
雇佣日期	hiredate	datetime
所属部门	department	varchar(30)
职务	headship	varchar(10)
薪水	salary	decimal(8,2)

2、客户表customer：

属性含义	属性名	数据类型
客户号	customerNo	char(9)
客户名称	customerName	varchar(40)
客户住址	address	varchar(40)
客户电话	telephone	varchar(20)
邮政编码	zip	char(6)

3、商品基本信息表product:

属性含义	属性名	数据类型
商品编号	productNo	char(9)
商品名称	productName	varchar(40)
商品类型	productClass	varchar(20)
商品定价	productProce	decimal(8,2)

4、订单主表OrderMaster：

属性含义	属性名	数据类型
订单编号	orderNo	char(12)
客户号	customerNo	char(9)
员工编号	employeeNo	char(8)
订单日期	orderDate	datetime
订单金额	orderSum	decimal(8,2)
发票号码	invoiceNo	char(10)

5、订单明细表OrderDetail：

属性含义	属性名	数据类型
订单编号	orderNo	char(12)
商品编号	productNo	char(9)
销售数量	quantity	int
成交单价	price	decimal(8,2)

一、单表查询

（1）查询全部职工的基本信息

（2）查询所有职工的部门、职工号、姓名和薪水

（3）查询全体职工的姓名、年龄、所属部门，并用汉语显示表头信息

（4）查询1973年出生且为职员的员工信息

（5）查询业务科或财务科的职工姓名、性别和所在部门，仅显示前面5位职工

（6）查询薪水为2000或4000的职工编号、姓名、所在部门和薪水

（7）查询薪水在3000~4000的职工姓名和薪水

（8）查询薪水不在3000~4000的职工姓名和薪水

（9）查询所有姓张的职工姓名、所属部门和性别

（10）查询所有姓张且全名为三个汉字的职工姓名

（11）查询既不在业务科也不在财务科的职工姓名、性别和所在部门

（12）查询1991年被雇佣的职工号、姓名、性别、电话号码、出生日期以及年龄

（13）查询6月出生的员工编码、姓名、出生日期，并按出生日期的降序输出

（14）查询职工工资最高的前10%的职工编号、职工姓名和工资

（15）查询每个业务员的订单数量

（16）统计在业务科工作且在1973年或1967年出生的员工人数和平均工资

（17）统计每种商品的销售数量和金额，并按销售金额的升序排序输出

（18）查询订单中至少包含3种（含3种）以上商品的订单编号及订购次数，且订购的商品数量在3件（含3件）以上。

二、表链接

（1）查询住址在上海的员工所做的订单，结果输出员工编号、姓名、订单编号、客户编号和订单日期，并按客户编号排序输出。

（2）查找订购了“32M DRAM”的商品的客户编号、客户名称、订单编号、订货数量和订货金额，并按客户编号排序输出。

（3）查询与“张晓梅”在同一部门工作的员工姓名、所属部门、性别和出生日期，并按所属部门排序输出

（4）查询1973年出生的员工所订购产品的订单，输出结果位员工编号、姓名、所属部门、订单编号、客户名称、订单日期，按员工编号排序输出。

（5）统计订单中每个商品具有销售数量大于4的订单的订单个数，要求显示商品编号、商品名称、订单数

（6）查询每个客户订购商品的具体订单信息，输出结果为客户编号、客户名称、商品编号、商品名称、数量、单价和金额

（7）查询“52倍速光驱”的销售情况，要求显示相应的销售员的姓名、性别、销售日期、销售数量和金额。

三、嵌套查询

（1）查询员工“张小娟”所做的订单信息

（2）查询没有订购商品的且在北京地区的客户编号、客户名称和邮政编码，并按邮政编码降序排序

（3）查询订购了“32M DRAM”商品的订单编号、订货数量和订货单价

（4）查询与员工编号E2008005在同一部门的员工编号、姓名、性别、所属部门

（5）查询即订购了P20050001商品，又订购了P20070002商品的客户编号、订单编号和订单金额。

（6）查询没有订购“52倍速光驱”或“17寸显示器”的客户编号、客户名称。

（7）查找至少有2次销售记录的业务员名单和销售日期

（8）查找销售金额最大的客户名称和总订单金额

（9）查找销售总额少于5000元的销售员编号、姓名和销售额

（10）查找至少订购了3种商品的客户编号、客户名称、商品编号、商品名称、数量和金额

（11）查找同时订购了商品编号为“P20070002”和商品编号为“20070001”的商品的客户

编号、客户姓名、商品编号、商品名称和销售数量，按客户编号排序输出

四、存在量词运算

（1）查询订购了“键盘”商品的客户姓名、订货数量和订货日期

（2）查询没有订购“键盘”商品的客户名称

（3）查询至少销售了5种商品的销售员编号、姓名、商品名称、数量及相应的单价，并按销售员编号排序输出

（4）查询没有订购商品的客户编号和客户名称

（5）查询订购了所有已列商品的客户名称

（6）查询至少包含了“世界技术开发公司”所订购的商品的客户编号、客户名称、商品编号、商品名称、数量和金额。

一、单表查询
1、select * from employee;

2、
select employeeno,employeename,department,salary from employee;

3、
select employeename as 姓名,year(now())-year(birthday) as 年龄,
department 所属部门 from employee;

4、
select * from employee
where year(birthday)='1973' and headship='职员';

5、
select employeename,sex,department from employee
where department regexp '财务科|业务科'
limit 0,5;

6、
select employeeno,employeename,department,salary
from employee
where salary='2000' or salary='4000';

7、
select employeename,salary from employee
where salary between 2000 and 4000;
8、
select employeename,salary from employee
where salary not between 2000 and 4000;

9、
select employeename,department,sex from employee
where employeename like '张%';

10、
select employeename from employee
where employeename like '张__';

如果上个不准用这个(个别电脑)：
select employeename from employee
where employeename like '张%' and char_length(employeename)=char_length('张')*3;

11、
select employeename,sex,department from employee
where department not regexp '财务科|业务科';

12、
select employeeno,employeename,sex,telephone,
birthday,year(now())-year(birthday) as age from employee
where year(hiredate)='1991';

13、
select employeeno,employeename,birthday
from employee
where month(birthday)=6
order by birthday desc;

14、

delimiter //

create procedure pro2(in x int)
begin
create table employee1 as select * from employee order by salary desc;
alter table employee1 add num int unique auto_increment;
select employeeno,employeename,salary,num from employee1
where num<=round((select count(*) from employee1)*x/100);
drop table employee1;
end;
//

delimiter ;

15、select employeeno,count(distinct orderno) from ordermaster
group by employeeno;

16、
select count(*),avg(salary) from employee
where year(birthday) in (1973,1967) and department='业务科';

17、
select orderdetail.productno,productname,quantity,quantity*price as 销售金额
from orderdetail left join product on product.productno=orderdetail.productno
group by orderdetail.productno
order by 销售金额 ;

18、
select productno,group_concat(orderno) 订单编号,
count(distinct orderno) as 订购次数 from orderdetail
where orderno in(
select orderno from orderdetail
group by orderno
having count(productno)>=3)
and quantity>=3
group by productno;

二、链接查询
1、
select employee.employeeno,employeename,orderno,
customerno,orderdate from employee left join ordermaster
on employee.employeeno=ordermaster.employeeno
where address regexp '上海'
order by customerno ;

2、
方法1： select p1.productno,productname,c1.customerno,
customername,o1.orderno,quantity,
quantity*price as 销售金额 from customer as c1,product p1,ordermaster o1,orderdetail o2
where c1.customerno=o1.customerno and
o1.orderno=o2.orderno and
p1.productno=o2.productno
and productname='32m dram'
order by c1.customerno ;

方法2：
select p1.productno,productname,c1.customerno,
customername,o1.orderno,quantity,
quantity*price as 销售金额 from customer as c1 inner join ordermaster o1 on c1.customerno=o1.customerno inner join orderdetail o2 on o1.orderno=o2.orderno
inner join product p1 on p1.productno=o2.productno
where productname='32m dram'
order by c1.customerno ;

3、查询与“张晓梅”在同一部门工作的员工姓名、所属部门、性别和出生日期，并按所属部门排序输出

select distinct e1.employeename,e1.department,e1.sex,e1.birthday
from employee e1 inner join employee e2 on e1.department=e2.department
where e2.employeename='张晓梅'
order by birthday;

4、
select e1.employeeno,employeename,department,o1.orderno,customername,
orderdate
from employee as e1,customer as c1,ordermaster as o1
where e1.employeeno=o1.employeeno and
c1.customerno=o1.customerno and
year(birthday)='1973'
order by e1.employeeno;

5、
select o1.productno,productname,
count(distinct orderno) as 订单数 from orderdetail as o1,product as p1
where o1.productno=p1.productno
and quantity>4
group by o1.productno;

6、
select c1.customerno,customername,p1.productno,productname,
quantity,price,price*quantity 金额
from customer c1,product p1,ordermaster o1,orderdetail o2
where c1.customerno=o1.customerno and
p1.productno=o2.productno and
o1.orderno=o2.orderno;

7、
select employeename,sex,orderdate,quantity,quantity*price
from employee e1,ordermaster o1,orderdetail o2
where e1.employeeno=o1.employeeno and
o1.orderno=o2.orderno and
o2.productno=(select productno from product where productname='52倍速光驱');

三：嵌套查询
1、
select * from ordermaster
where employeeno=(select employeeno from employee where employeename='张晓娟');

2、
select customerno,customername,zip from customer
where customerno not in
(select distinct customerno from ordermaster )
order by zip ;

3、
select productno, orderno,quantity,price from orderdetail
where productno =(select productno from product where productname='32M DRAM');

4、
select employeeno,employeename,sex,department from employee
where department=(select department from employee where employeeno='E2008005');

5、
select customerno,orderno,ordersum from ordermaster
where customerno in(
select customerno from ordermaster
where orderno in (select orderno from orderdetail where productno='P20050001') )and customerno in(
select customerno from ordermaster
where orderno in (select orderno from orderdetail where productno='P20070002'))
;

6、
select customerno,customername from customer
where customerno not in(
select customerno from ordermaster
where orderno in(select orderno from orderdetail
where productno in(select productno from product
where productname in('52倍速光驱','17寸显示器'))));

7、
select employeeno,orderno,orderdate from ordermaster
where employeeno in(select employeeno from ordermaster
group by employeeno
having count(orderno)>=2);
8、
select f1.customerno ,c1.customername,max(销售额) as 总订单金额
from ( select customerno,sum(ordersum)as 销售额 from ordermaster
group by customerno) as f1 inner join customer as c1
on f1.customerno=c1.customerno;
9、
select f1.employeeno,employeename,销售额 from
(select employeeno,sum(ordersum) as 销售额 from ordermaster
group by employeeno
having 销售额<5000) as f1 inner join employee
on f1.employeeno=employee.employeeno;

10、
select c1.customerno,customername,p1.productno,productname,
quantity,quantity*price 金额 from customer c1,product p1,
ordermaster o1,orderdetail o2
where c1.customerno in(select o1.customerno from ordermaster o1 inner join orderdetail o2 on o1.orderno=o2.orderno
group by o1.customerno
having count(productno)>=3)
and c1.customerno=o1.customerno and
p1.productno=o2.productno and
o1.orderno=o2.orderno;

11、

select c1.customerno,customername,p1.productno,productname,quantity
from ordermaster o1,product p1,orderdetail o2,customer c1
where c1.customerno in(
select customerno from ordermaster
where orderno in (select orderno from orderdetail where productno='P20070001') )and c1.customerno in(
select customerno from ordermaster
where orderno in (select orderno from orderdetail where productno='P20070002'))
and c1.customerno=o1.customerno and
p1.productno=o2.productno and
o1.orderno=o2.orderno and p1.productno in('P20070001','P20070002')
order by c1.customerno;

四、
1、
select customername,productname,quantity,orderdate from customer c1,
ordermaster o1,orderdetail o2,product p1
where exists(select productno from product
where productname='键盘' and productno=o2.productno) and
c1.customerno=o1.customerno and
o1.orderno=o2.orderno and p1.productno=o2.productno;
2、
select customername from customer
where not exists(
select c1.customerno, customername,productname,quantity,orderdate from customer c1,
ordermaster o1,orderdetail o2,product p1
where exists(select productno from product
where productname='键盘' and o2.productno=productno) and
c1.customerno=o1.customerno and
o1.orderno=o2.orderno and p1.productno=o2.productno
and c1.customerno=customer.customerno);

3、
select * from
(select e1.employeeno,employeename,productname,quantity,price from
employee e1,product p1,orderdetail o2,ordermaster o1
where exists(select employeeno from ordermaster or1,orderdetail o2
where or1.orderno=o2.orderno
group by or1.employeeno
having count(productno)>=5 and employeeno=e1.employeeno)
and e1.employeeno=o1.employeeno and
p1.productno=o2.productno and
o1.orderno=o2.orderno) as f1
order by employeeno;

4、
select customerno,customername from customer
where not exists (select customerno from ordermaster
where customerno=customer.customerno);

5、
select customername from customer
where exists(
select customerno,count(distinct productno) from ordermaster o1,orderdetail o2
where o1.orderno=o2.orderno
group by customerno
having count(distinct productno)=(select count(distinct productno) from product))
and customerno=customer.customerno;

6、
例1：查询“世界技术开发公司”所订购的商品的客户编号、客户名称、商品编号、商品名称、数量和金额。
select o1.customerNo,customerName,o2.productNo,p1.productName,
o2.quantity,price*quantity from customer c1,ordermaster o1,
orderdetail o2,product p1
where o1.customerno=c1.customerno and o2.orderno=o1.orderno
and p1.productNo=o2.productNo AND
exists(
select distinct productno from ordermaster o1 inner join orderdetail o2
on o1.orderno=o2.orderNo
where exists
( select customerno from customer
where customername='世界技术开发公司' AND
customerno=o1.customerNo) and
productno=o2.productno);

例2：查询至少包含了“世界技术开发公司”所订购的商品的客户所订购商品的客户编号、客户名称、商品编号、商品名称、数量和金额。

select o1.customerNo,customerName,o2.productNo,p1.productName,
o2.quantity,price*quantity from customer c1,ordermaster o1,
orderdetail o2,product p1
where o1.customerno=c1.customerno and o2.orderno=o1.orderno
and p1.productNo=o2.productNo AND
exists(
select distinct productno from ordermaster o1 inner join orderdetail o2
on o1.orderno=o2.orderNo
where exists
( select customerno from customer
where customername='世界技术开发公司' AND
customerno=o1.customerNo) and
productno=o2.productno)
group by customerNo
having count(*)>=(select count(distinct productno) from ordermaster o1 inner join orderdetail o2
on o1.orderno=o2.orderNo
where exists
( select customerno from customer
where customername='世界技术开发公司' AND
customerno=o1.customerNo));

1.代码编写规则

'1.VBE常见的设置
'A.工具选项设置
'B.注释
'C.代码的运行
'D.代码帮助(F1)

'2.代码的编写规则
'A.子过程
'B.函数过程

'3.代码换行（空格+下划线+换行）

Sub aa()
MsgBox prompt:="学习VBA", Buttons:=vbYesNo + vbInformation, Title:="vba"
MsgBox 56 * 9
MsgBox _
"学习VBA"
End Sub

2.工作薄

Sub 当前与活动工作薄区别实例()
MsgBox ThisWorkbook.Name & " --------" & ActiveWorkbook.Name
End Sub

Sub 打开工作薄()
Dim wkb As Workbook
Set wkb = Workbooks.Open("C:\Users\lx\Desktop\新建 Microsoft Excel 工作表.xlsm")
End Sub

3.工作表示表的b表示方法

'在workbook对象中，有一个SHEETS集合,其成员是worksheet对象或chart对象。
'worksheets仅指的是工作表，而sheets包含图表，工作表，宏表等等
'VBA中，经常在工作表之间转换或者对不同工作表中的单元格区域进行操作.
'通常有下面几种方法:
Sub 直接使用工作表名称法()
MsgBox Worksheets("我的工作表").Name
MsgBox Sheets("我的图表").Name
End Sub

Sub 索引号表示法()
MsgBox Worksheets(1).Name
End Sub

Sub 工作表代码索引号表示法()
MsgBox Sheets(1).Name
End Sub

Sub 直接取工作代码法()
MsgBox Sheet1.Name
End Sub

Sub 活动工作表()
MsgBox ActiveSheet.Name
End Sub

'注意：当工作簿包括工作表、宏表、图表等时，
' 使用索引号引用工作表如Sheets(1)与
' WorkSheets(1)引用的可能不是同一个表。

Sub worksheetss()
MsgBox Worksheets(1).Name
MsgBox Sheets(1).Name
End Sub

Sub Beee()
Cells(2, 2).Select
Range("b2").Select
[B2].Select

End Sub

Sub F6()
Range("F6,F6").Select
[B2].Offset(4, 4).Select
[B2].Cells(5, 6).Select
End Sub
Sub B2F6()
Range(Cells(2, 2), Cells(6, 6)).Value = 10
Range("b2:f6").Select
Range("b2", "f6").Select

[b2:f6].Select
Range("b2").Resize(5, 6).Select
[b2:f6] = 1
Sheet1.Range("b2:f6").Copy Sheet2.[a1]

End Sub

Sub 动态变量()
Dim a%, b%
a = Application.CountA(Range("b:b"))
b = Application.CountA(Range("2:2"))
Range("f" & b + 1).Select
Range(Cells(2, 2), Cells(a + 1, b + 1)).Select
[B2].Cells(a, b).Select

End Sub

注意:Sub 名称切记不用用B2等，这些容易与单元格计算容易混淆[B2].Cells(a,b).Select 运行不了。

CountA() 计算非空单元格

5.创建和删除工作表

Sub 批量新建工作表()
Dim i%, sth As Worksheet
For i = 1 To 12
Set sth = Sheets.Add
sth.Move after:=Sheets(Sheets.Count)
sth.Name = i & "月"
Next
End Sub

'删除工作表
Sub 删除sheet()
On Error Resume Next
Application.DisplayAlerts = False
Dim i%
For i = 1 To 12
Sheets(i & "月").Delete
Next
Application.DisplayAlerts = True
End Sub

'在操作EXCEL时，经常会弹出提示，如删除工作表时会提示，合并单元格时会提示，
'如果在VBA中想屏它，就在开始加上Application.DisplayAlerts = False，如果需要在结尾处要恢复显示 Application.DisplayAlerts = True

6.赋值和移动工作表

'表达式.Move(Before, After)
'表达式.copy(Before, After)

Sub 移动()
Sheets(1).Move , Sheets(3)
End Sub

Sub 复制()
Sheet1.Copy Sheets(Sheets.Count)
End Sub

7.Range的用法

'Range ("文本型装单元格地址")

'range的常见写法
Sub rng()
Range("a1").Select '单元格
Range("a:a").Select '列
Range("1:3").Select '行
Range("a1:b10").Select '相邻区域
Range("a1:d7,c4:e8").Select '不相个邻区域
End Sub

2017年2月15日

1.cells单元格引用法
'写法：cells(行号,列号)
Sub cells基本写法()
Cells(3, 4).Select '行列号均为数字
Cells(2, "c").Select '行为数字，列为列标字母
Cells.Select '全选
End Sub

'cells可以像range一样可以参照前面的单元格位置
Sub 参照写法()
Range("b3:f11").Cells(2, 2).Select
Range("b3:f11").Cells(6).Select '从左到右，从上到下
Range("b3:f11")(6).Select '与上一句相等
End Sub
'注意：
'1.cells中的数字一样支持正数，负数，0值，小数(四舍六入五单双)
'2.cells不能像range一样可以引用一个区域，只能引用一个单元格

'cells还可以嵌套在range中
Sub 嵌套()
Range(Range("b1"), Range("f11")).Select '这种嵌套方法写变量比较麻烦
Range(Cells(3, 2), Cells(11, 6)).Select '这种嵌套方法写变量比较方便
End Sub

Sub 动态引用行列区域()
Dim a%, b%
a = Application.CountA(Range("a:a"))
b = Application.CountA(Range("1:1"))
Range(Cells(1, 1), Cells(a, b)).Select
End Sub

2.单元格简写

'除了前面讲的range\cells单元格区域的表示方法还，还是一种简单的写法
'写法: [单元格地址] '注意：中括号中的单元格地址并不需要双引号("")
Sub 单元格简写()
[a3].Select ' 单元格引用
[b2:c6].Select '单元格区域引用
[a3,b2:c6,b8:d12].Select '多区域引用
[a:a].Select '整列引用
[1:1].Select '整行引用
End Sub

'单元格简写的也支持引用子集
Sub 子集引用()
[b2:c6].Item(3).Select
Range("b2:c6")(3).Select
[b2:c6].Cells(4).Select
End Sub

Sub 动态区域的引用()
a = Application.CountA([a:a])
b = Application.CountA([1:1])
Range(Range("a1"), Range(Chr(64 + b) & a)).Select '利用chr函数，让字母形式的列号也支持变量
End Sub

Sub chr函数字符循环()
For i = 1 To 65535
Cells(i, 1) = i
Cells(i, 2) = Chr(i)
Next
End Sub

3.三种单元格引用小结

Sub range引用区域且有变量()
Dim i
i = 1
Range("a1:c" & i).Select '引用单元格是区域且有变量
Cells(i, "c").Select '引用的是单个单元格且有变量
[a1:19].Select '引用的是区域或单元格且无变量
End Sub

4.行列引用

'行列引用
Sub 列引用()
Columns(1).Select
Columns("b").Select
Columns("c:e").Select
End Sub

Sub 行引用()
Rows(1).Select
Rows("2").Select
Rows("3:4").Select
End Sub

Sub range行列表式法()
Range("1:1").Select
Range("2:4").Select
Range("a:a").Select
Range("b:d").Select
End Sub
Sub 简写法()
[a:a].Select
[b:d].Select
[1:1].Select
[2:4].Select
End Sub

Sub 全选()
Rows.Select '选择所有行
Columns.Select '选择所有列
Cells.Select '选择所单元格
i = Rows.Count
j = Columns.Count
k = Cells.Count
End Sub
Sub 动态引用使用区域()
a = Application.CountA(Columns(1))
b = Application.CountA(Rows(1))
Range("a1", Cells(a, b)).Select
End Sub

6.Range.Row 与Range.Column属性

'-------------------------------------------------
'Range.Row 属性
'返回区域中第一个子区域的第一行的行号

'Range.Column 属性
'返回指定区域中第一块中的第一列的列号
'-------------------------------------------------

Sub test()
i = Range("a3:b9").Range("a5").Row
j = Range("a3:b9").Row
i = Range("b3:d9").Range("a5").Column
j = Range("b3:d9").Column
End Sub

Sub row应用()
For Each rw In Rows("1:13")
If rw.Row Mod 2 = 0 Then
rw.RowHeight = 5
End If
Next rw
End Sub

2017年2月16日

1.属性

'VBA属性: 指对象所具有的特性
'人的属性：姓名，年龄，身份证号，住址….

Sub 属性()
Debug.Print Sheet1.Name
Debug.Print Sheet1.Range("a1").Value
End Sub

'VBA对象属性的赋值

Sub 属性赋值()
Sheet2.Name = "改变自己"
Sheet2.Range("a1") = "学习VBA"
End Sub

Sub 常见属性()
MsgBox Application.Name
MsgBox ThisWorkbook.Path
MsgBox Worksheets(1).Name
MsgBox ActiveCell.Height
MsgBox ActiveCell.Value
End Sub

2.活动单元格

'活动单元格：activecell,工作表中活动单元格只有一个
Sub activecells()
a = ActiveCell.Address '取得活动单元格地址
Cells(2, 3).Activate '激活指定单元格
End Sub

'selection光标所选区域
Sub 光标所选区域()
Selection = 2
End Sub
'Range.CurrentRegion 属性
'返回一个 Range 对象，该对象表示当前区域。
Sub 当前区域()
[a1].CurrentRegion.Select
[f8].CurrentRegion.Select
End Sub

'usedrange与currentregion
'如果表中只有一个区域，两者最后的结果是一样的
'只是表达方式不一样
Sub u与c()
Sheet3.UsedRange.Select
[a1].CurrentRegion.Select
End Sub

Sub currentregion应用()
Rows(8).Clear
a = [b2].CurrentRegion.Address
b = [b5].CurrentRegion.Address
c = [b2].CurrentRegion.Count + 1
Set c = Range("b8", Cells(8, c))
c.FormulaArray = "=" & a & "+" & b
End Sub

3.Range.offset属性

'Range.Offset 属性
'返回 Range 对象，它代表位于指定单元格区域的一定的偏移量位置上的区域。
'表达式.Offset(偏移行, 偏移列)
'表达式一个代表 Range 对象的变量。
'偏移行列的数字可以是：正数，负数，零值

Sub test()
[a1].Offset(1, 2).Select '行列都偏移
[a1].Offset(2).Select '只偏移行
[a1].Offset(, 2).Select '只偏移列

'如果offset前面的range对象是一个区域，则偏移后也结果尺寸不变
[a1:d1].Offset(1, 2).Select
[a1:d1].Offset(2).Select
[a1:d1].Offset(, 2).Select
End Sub

4.Range.Resize属性

'Range.Resize 属性
'调整指定区域的大小。返回 Range 对象，该对象代表调整后的区域。
'语法
'表达式.Resize(行数, 列数)
'表达式一个返回 Range 对象的表达式。
Sub test()
[a1].Resize(2, 3).Select
[a1].Resize(2).Select
[a1].Resize(, 3).Select
End Sub

Sub 保存()
Dim i%, j%, k%
i = [a1].CurrentRegion.Rows.Count - 1
j = [a1].CurrentRegion.Columns.Count
k = Application.CountA(Sheet2.Columns(1))
[a2].Resize(i, j).Copy Sheet2.[a1].Offset(k)
End Sub

5.方向键(Range.End)

'====================================================================================
'1.从录制一个宏开始。操作：(ctrl+向上键、ctrl+向下键、ctrl+向左键、ctrl+向右键)
'2.看代码

'Range.End 属性
'返回一个 Range 对象，该对象代表包含源区域的区域尾端的单元格。
'等同于按键 (End+向上键、End+向下键、End+向左键、End+向右键)

'语法
'表达式.End (Direction)
'表达式一个代表 Range 对象的变量。
'方向写法值
'向上 xlUp - 4162
'向下 xlDown - 4121
'向左 xlToLeft - 4159
'向右 xlToRight - 4161
'========================================================================================
'←为1，→为2，↑为3，↓为4

Sub test()
i = Cells(Rows.Count, 3).End(xlUp).Row
j = Cells(Rows.Count, 3).End(xlUp).Address
Range("a1", j).Select
Range("a1", Cells(i, 3)).Select
Range("a1:c" & i).Select
Range("a1", "c" & i).Select
End Sub

6.单元格的值

'单元格的值表示方法
Sub 单元格值表示()
a = [a1].Value '实际是什么，就是什么
b = [a1].Text '看到是什么，就是什么
c = [a1]
End Sub

'注意：一个单元格可以省略value，多单元格区域不能省略
Sub 多区域赋值()
Range("e1:e4") = Range("d1:d4").Value
End Sub

'单元格地址与引用
Sub 地址与引用()
Set rng = [b2:f2]
[a9] = rng.Address(1, 1) '绝对引用
[b9] = rng.Address(0, 0) '相对引用
[c9] = rng.Address(1, 0) '混合引用
[d9] = rng.Address(0, 1) '混合引用
End Sub
'总结：1代表固定，0代表不固定,默认是绝对引用

Sub 地址引用实例()
'将表三成绩中为空的单元格标为未考
Dim rng As Range, rn$
On Error Resume Next
For Each rng In Sheet3.Range("b2:d10")
If rng = "" Then rn = rn & rng.Address & ","
Next
Range(Left(rn, Len(rn) - 1)) = "未考"
End Sub

'思考题:用上题的思路，将成绩中的"未考"去掉为空?

注:""与“ ”的区别

7.方法

'方法：实际上就是对对象的操作，它是一种动作，一种行为。

Sub 选择方法()
Range("a1:a10").Select
'注：如果单元格区域前没有写工作表名，则默认为活动工作表
End Sub

Sub 复制方法()
Sheet1.Range("a1:a10") = 1 '将1写入表一的a1:a10区域
Sheet1.Range("a1:a10").Copy Sheet2.Range("a1") '将表一的a1:a10区域的值复制到表2的a1
End Sub

Sub 删除方法()
Sheets(3).Delete
End Sub

8.触发事件

'下面是可以由用户添加的支持工作薄的事件
'Private Sub Workbook_AddinUninstall()
'Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Private Sub Workbook_BeforePrint(Cancel As Boolean)
'Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Private Sub Workbook_Deactivate()
'Private Sub Workbook_NewSheet(ByVal Sh As Object)
'Private Sub Workbook_Open()
'Private Sub Workbook_SheetActivate(ByVal Sh As Object)
'Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
'Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
'Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
'Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
'Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
'Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
'Private Sub Workbook_WindowActivate(ByVal Wn As Window)
'Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
'Private Sub Workbook_WindowResize(ByVal Wn As Window)

Private Sub Workbook_Activate()

End Sub

Private Sub Workbook_NewSheet(ByVal Sh As Object)
MsgBox "新建工作表"
End Sub

Private Sub Workbook_Open()
MsgBox "欢迎"
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

End Sub

注:名称也就是触发事件，触发事件就是vba自带的

9.动态选单元格

Sub 实例1动态选单元格或区域()
Dim i%
i = Application.CountA(Range("c:c")) '找到c列中已使用的最后一个单元格位置
Range("c" & i).Select '选择C列最后一格
Range("a1", "c" & i).Select '选择A1到C列的最后一格（方法一）
Range("a1:c" & i).Select '选择A1到C列的最后一格（方法二）
'小结：动态单元格区域的定位，可以应用到单据的保存等实际工作中
j = Application.ActiveSheet.Name 'Application对象是Excel对象模型中最高层级的对象，代表Excel应用程序自身，也包含组成工作簿的许多部分，包括工作簿、工作表、单元格集合以及它们包含的数据
End Sub

'除了上一课节讲的range基本表示方法外，还有更多的变化写法

'1.range("地址区域").range("地址区域")
Sub 序号表示法()
Range("b2:d4").Range("b2").Select '相对引用的写法
'参照前一个range的左上单元格
End Sub

'2.range地址区域中支持变量
Sub range的变量支持()
Dim a%
a = 3
Range("a" & a).Select
Range("c3:e5")(2).Select
End Sub

10.读出声音

Sub 读出声音()
Application.Speech.Speak Now
[a2:b5].Speak , True
End Sub

11.常量与变量

'1.常量：常量是定义了之后就不做变化了。
' 常量定义格式：Const 常量名= 常量表达式
Sub 常量()
Const pi = 3.145926
End Sub

'2.变量:在定义之后还能再次赋值
'变量定义格式：Dim 变量 As 变量类型
Sub 变量()
Dim a As Integer
a = 100
End Sub

'3.常量变量应用
Sub 应用()
Const pi = 3.1415926
Dim a As Integer
a = 200
Debug.Print pi * a
End Sub

'3.A允许使用未定义的变量，默认是变体变量(Variant)
'B.变量的强制声明：Option Explicit

'4.变量名的命名规则
'以字母开头
'不能用保留字
'不超过 255 个字符。
'同一范围内必须是唯一

12.数据类型

13.IF条件判断

'VBA中的IF条件判断语句，就像函数中的IF一样
'IF可单条件，也可多条件。

Sub IF条件判断之单条件1()
Dim a%, b%
a = 2
b = 2
If a = b Then MsgBox "相等"
End Sub

Sub IF条件判断之单条件2()
Dim a%, b%
a = 2
b = 2
If a = b Then
MsgBox "相等"
End If
End Sub

Sub IF条件判断之单条件3()
Dim a%, b%
a = 2
b = 2
If a = b Then
MsgBox "相等"
Else
MsgBox "不相等"
End If
End Sub
'Go to 就是去到哪儿的意思。Go to 语句是将程序转到指定的标签的语句位置，然后继续往下执行
Sub goto语句()
Dim a%, b%
a = 3
b = 2
If a = b Then
MsgBox "相等"
Else
GoTo xy '为go to 语句设置的标签，必须以英文状态下的冒号结尾
End If
xy: MsgBox "不相等"
End Sub
Sub IF条件判断之多条件等级划分()
If Sheet1.Range("b1") >= 90 Then
Sheet1.Range("b2") = "优秀"
ElseIf Sheet1.Range("b1") >= 80 Then
Sheet1.Range("b2") = "良好"
ElseIf Sheet1.Range("b1") >= 60 Then
Sheet1.Range("b2") = "中等"
Else
Sheet1.Range("b2") = "较差"
End If
End Sub

'VBA中的IIF函数与工作表函数（IF）的语法结一致
Sub IIF函数应用()
Cells(2, 3) = IIf(Cells(1, 2) > 80, "优秀", "不优秀")
End Sub

Sub IF条件判断之多条件等级划分11()
If Sheet1.Range("b1") < 60 Then
Sheet1.Range("b2") = "较差"
ElseIf Sheet1.Range("b1") < 80 Then
Sheet1.Range("b2") = "中等"
ElseIf Sheet1.Range("b1") < 90 Then
Sheet1.Range("b2") = "良好"
Else
Sheet1.Range("b2") = "优秀"
End If
End Sub
Sub IF条件判断之多条件等级划分1()
If Sheet1.Range("b1") > 100 Then
Sheet1.Range("b2") = "溢出"
ElseIf Sheet1.Range("b1") >= 90 Then
Sheet1.Range("b2") = "优秀"
ElseIf Sheet1.Range("b1") >= 80 Then
Sheet1.Range("b2") = "良好"
ElseIf Sheet1.Range("b1") >= 60 Then
Sheet1.Range("b2") = "中等"
Else
Sheet1.Range("b2") = "较差"
End If
End Sub

Sub IF条件判断之多条件等级划分2()
If Range("b1") < 60 Then
Range("b2") = "较差"
ElseIf Range("b1") >= 60 And Range("b1") < 80 Then
Range("b2") = "中等"
ElseIf Sheet1.Range("b1") >= 80 And Range("b1") < 90 Then
Range("b2") = "良好"
ElseIf Sheet1.Range("b1") >= 90 And Range("b1") <= 100 Then
Range("b2") = "优秀"
Else
Range("b2") = "溢出"
End If
End Sub

14.select case

'select case语句：根据表达式的值来决定执行几组语句中的其中之一。
'vba if语句为判断语句。根据条件的值，可使用 If...Then...Else 语句运行指定的语句或一个语句块。If...Then...Else 语句可根据需要嵌套多级。
'
'　　但是，为了代码的可读性，建议使用其余的语句，比如 Select Case 语句而不使用多层嵌套的vba中的 If...Then...Else 语句。

Sub select单条件判断()
i = 2
Select Case i
Case Is > 0 ' Is 是关键字
MsgBox "正数"
Case Else
MsgBox "负数"
End Select
End Sub

Sub select多条件判断()
Select Case Sheet1.[b1].Value
Case 90 To 100
Sheet1.[b2] = "优秀"
Case 80 To 89
Sheet1.[b2] = "良好"
Case 60 To 79
Sheet1.[b2] = "中等"
Case 0 To 59
Sheet1.[b2] = "较差"
Case Else
Sheet1.[b2] = "溢出"
End Select
End Sub

15.for each ...each

'当需要处理集合成员时，一般会用for each..next,实际上就是处理对象

Sub foreachnext循环1()
Dim rng As Range
For Each rng In Sheet1.Range("a2:a10") '取a2:a10中的每个单元格
If rng = "A1" Then rng.Interior.ColorIndex = 3
Next
End Sub
Sub for循环()
Dim i%
For i = 2 To 10
If Cells(i, 1) = "A1" Then
Cells(i, 1).Interior.ColorIndex = 3
End If
Next
End Sub

Sub foreachnext循环2()
Dim wsh As Worksheet, n As Byte, m As String
For Each wsh In Worksheets '取当前工作表集合中的每个成员
n = n + 1
Sheet1.Cells(n, 3) = wsh.Name
Next
End Sub

16.for next 循环

'FOR...NEXT也是循环语句，for next有一个内置计数器
Sub fornext循环1()
Dim i%, j%
For i = 1 To 100 Step 2
j = j + i
Next
MsgBox j
End Sub

Sub fornext循环2()
Dim i!, j!
For i = 100 To 1 Step -2
j = j + i
Next
MsgBox j
End Sub

Sub fornext循环()
Dim rng!
For rng = 2 To 16
Sheet1.Cells(rng, 4) = Sheet1.Cells(rng, 2) * Sheet1.Cells(rng, 3)
Next
End Sub

'可以将一个 For...Next 循环放置在另一个 For...Next 循环中，组成嵌套循环。
' For I = 1 To 10
' For J = 1 To 10
' For K = 1 To 10
' ...
' Next K
' Next J
' Next I

Sub 理解计数变量的运行()

For i = 1 To 2
For j = 1 To 3
k = "外层的第" & i & "次的，内层的第" & j & "次"
Next j
Next i
End Sub

17.do...loop

'----------------------------------------------------------

'do ...loop 循环语句，直到满足某个条件

'----------------------------------------------------------

Sub test()
Dim a%
Do
a = a + 1
If a > 10 Then
MsgBox a & "终于大于10"
Exit Do
End If
Loop
End Sub

Sub 基本示例()
Dim rs%
rs = 1
Do
rs = rs + 1
If rs > 10 Then
Exit Sub
Else
If Cells(rs, 2) >= 90 Then Cells(rs, 3) = "√"
End If
Loop
End Sub

18.while until 与Do的使用

'---------------------------------------------------------------
'while与until不但可以放在DO后面，也可以放在LOOP后面
'事实上有时在循环的最后一行进行判断，更具有意义
'---------------------------------------------------------------
Sub doloop的前面判断循环()
Dim pss$
Do Until pss = "123" 'until放在前面需要做两次判断
pss = InputBox("请输入密码")
Loop
End Sub

Sub doloop的最后判断循环()
Dim pss$
Do
pss = InputBox("请输入密码")
Loop Until pss = "123"
End Sub

'如果没有判断正确，永远无法退出循环
'如果三次没有正确录入，就退出循环
Sub doloop的最后判断循环2()
Dim pss$, i%
Do
i = i + 1
If i > 3 Then Exit Do
pss = InputBox("请输入密码，第" & i & "次输入")
Loop Until pss = "123"
End Sub

'用Do…Loop循环要注意的几点:
'1. While与Until是放在Do后面还是Loop后面，取决于是先判断再循环，还是先循环再判断前者则在Do后面。，后者则在Loop后面。
'2. 可以在Do...Loop中的任何位置放置任意个数的 Exit Do 语句，随时跳出 Do...Loop 循环。
'3. Do...Loop + If...Then + Exit Do 通常结合使用.
'4. 如果 Exit Do 使用在嵌套的 Do...Loop 语句中，则 Exit Do 会将控制权转移到 Exit Do 所在位置的外层循环。

2017年2月17日

1.OTS构建在阿里云飞天分布式系统之上的NoSQL数据库服务，提供海量结构化数据的存储和实时访问。

2.数组的概念

' 1.数组概念
' 数组就是一个列表或者一组数据表.
'
' 2.数组位置
' 数组存储在内存中.
'
' 3.数组特点
' a.读写速度快（从内存读取数据要比从硬盘读取快）
' b.无法永远保存(内存只是暂存空间)

' 4.数组分类
' a.一般分为：常量数组，静态数组，动态数组
' b.如按维度为：1维，2维，3维......60 维
'
'

Sub test2()
Dim arr1(3)
Dim arr2(1 To 3)
Dim arr3(1 To 3, 1 To 2)
Dim arr4(3, 2)
End Sub

3.多维数组

' 数组最多有60维，但在excel中一般最到2维
' 1.excel中的一行或一列可以转换1维数组
' 2.excel中的多行多列可以转换成2维数组
'

Sub test1()
arr1 = [{"A","B","C","D"}] '横向逗号
arr2 = Application.WorksheetFunction.Transpose([{1;2;3;4}]) '竖向分号
End Sub

Sub test2()
arr3 = [{"张",1;"王",2;"陈",3;"李",4;"林",5}]
arr4 = Range("i1:j5")
End Sub

Sub test4()

4.数组写入数据

Sub 向数组中指定的位置写入数据()
Dim arr(1 To 9)
arr(3) = "我"
arr(5) = "是"
arr(7) = "谁"
End Sub

Sub 循环向数组中写入数据()
Dim arr(1 To 4)
For Each Rng In [a1:a4]
n = n + 1
arr(n) = Rng
Next
End Sub

Sub 常量数组数据写入一般数组()
Dim arr() '声明时括号内没有赋值，形成动态数组
arr = Array("V", "B", "A", 9)
End Sub

arr = Array(1, 2, 3, 4)
arr1 = Array(Array("a", "b"), Array(1, 2, 3))
End Sub

Sub 单元格区域数据批量写入数组()
arr = Application.Transpose([a1:a4]) '注意：当向数组中批量写入多行，结果就是二维数组
arr1 = Application.Transpose(Application.Transpose([a7:d7]))
End Sub

5.数组取数

'怎样在数组中取数
Sub 取数组中指定位置的元素()
arr = [a2:a5]
'MsgBox arr(2, 1)
'MsgBox arr(4, 1)
i = UBound(arr, 2) '上标
j = LBound(arr, 2) '下标
End Sub

Sub 方法1循环取数()
arr = [a2:a9]
[b1] = arr(2, 1)
For i = 1 To 8
Cells(i, 3) = arr(i, 1)
Next
End Sub
Sub 方法2一次性取数()
arr = [a2:a5]
Range("d1:d" & 4) = arr
End Sub
Sub 用transpose函数转置()
arr = [a2:a5]
arr1 = Application.Transpose(arr)
[a7:d7] = arr1
[a8:c8] = arr1
[a9:e9] = arr1
'注意左右两边尺寸的对应
End Sub

6.数组

Sub 数组汇总() '求平均分
arr = [b2:b9]
For Each num In arr
n = n + 1 '计数
lj = lj + num '累加求和
Next
MsgBox "平均分为：" & lj / n
End Sub

Sub 动态区域数组汇总() '求平均分
arr = Range([b2], Cells(Rows.Count, 2).End(xlUp))
For Each num In arr
n = n + 1
lj = lj + num
Next
MsgBox "平均分为：" & lj / n
End Sub

Sub 动态区域数组汇总分拆() '求平均分
Dim m$
m = Cells(Rows.Count, 2).End(xlUp).Address
arr = Range([b2], m)
For Each num In arr
n = n + 1
lj = lj + num
Next
MsgBox "平均分为：" & lj / n
End Sub

7.数组中的函数

'在数组中求和，平均，最大，最小，极大，极小值等
Sub test()
arr = [b2:c9]
MsgBox WorksheetFunction.Sum(arr)
MsgBox WorksheetFunction.Average(arr)
MsgBox WorksheetFunction.Max(arr)
MsgBox WorksheetFunction.Min(arr)
MsgBox WorksheetFunction.Small(arr, 2)
MsgBox WorksheetFunction.Large(arr, 2)
End Sub

Sub test2()
Dim arr1(1 To 16)
arr = [b2:c9]
For Each a In arr
If a >= 80 Then
n = n + 1
arr1(n) = a
End If
Next
Stop
MsgBox WorksheetFunction.Average(arr1)
End Sub

Sub foreach循环()
Dim rng As Range
For Each rng In [b2:c9]
If rng >= 80 Then
n = n + 1 '计数
lj = lj + rng.Value '累加求和
End If
Next
MsgBox lj / n
End Sub

Sub for循环()
Dim a%
For a = 2 To 9
If Cells(a, 2) >= 80 And Cells(a, 3) >= 80 Then
Cells(a, 1).Interior.Color = 65535
End If
Next
End Sub
Sub 数组查找期中和期末都满足大于等于80()
Dim arr3
arr3 = [b2:c9]
b = UBound(arr3)
For a = 1 To b
If arr3(a, 1) >= 80 And arr3(a, 2) >= 80 Then
Cells(a + 1, 1).Interior.Color = 65535
End If
Next
End Sub

Sub 条件平均循环()
Dim rng As Range
For Each rng In [b2:c9]
If rng >= 80 Then m = m + 1: n = n + rng
Next
[A18] = n / m
End Sub

Sub 条件平均数组()

arr = Range("b2", Cells(Rows.Count, 3).End(xlUp))
For Each rng In arr
If rng >= 80 Then m = m + 1: n = n + rng
Next
[A19] = n / m
End Sub

'比比哪个程序的运行速度更快

Sub 方法一()
t = Timer
Set Rng = Cells(Rows.Count, 1).End(xlUp)
arr = Range([a1], Rng)
For Each a In Range([a1], Rng)
If a >= 90 Then n = n + 1: Cells(n, 3) = a
Next
MsgBox Format(Timer - t, "0.0000")
End Sub

Sub 方法二()
t = Timer
Dim arr1(1 To 5000, 1 To 1)
Set Rng = Cells(Rows.Count, 1).End(xlUp)
arr = Range([a1], Rng) '数组写入
For Each a In arr
If a >= 90 Then
n = n + 1
arr1(n, 1) = a '将arr数组中的数据有条件的将数据写入arr1数组
End If
Next
[d1].Resize(n) = arr1
MsgBox Format(Timer - t, "0.0000")
End Sub

Sub 数组()
Dim arr1(1 To 5000)
arr = Range("a2", Cells(Rows.Count, 1).End(xlUp))
For Each a In arr
If a >= 90 Then
m = m + 1
arr1(m) = a
End If
Next
Range("d2").Resize(m) = Application.Transpose(arr1)
'Range("d2:d" & m + 1) = Application.Transpose(arr1)
End Sub

9.函数

Sub function用法()
Cells(12, 1) = Application.CountIf([a1:a10], ">9")
Cells(12, 2) = Application.Count([a1:a10])
End Sub

10.what-if模拟分析方法是模拟影响结果的可变因素而测算出最后的结果

2017年2月18日

1.单变量求解

3.mysql 的服务器服务的启动和停止

4.mysql修改密码

一、数据库架构：

服务器-> 多个数据库->多张数据表(多个行和列)

二、查看当前所有存在的数据库

show databases;

1、创建测试数据库test_db，

create database 数据库名;

create database test_db;

create database if not exists zoo;

2、查看创建好的数据库test_db的定义
show create database 数据库名;

show create database test;

3、删除测试数据库test_db，

drop database 数据名
drop database test_db;

drop database if exists zoo;
4、选择数据库：

use 数据库名;

6.规划求解

7.模拟运算表

8.char 定长 varchar 变长

9.模拟运算表(双重变量)

10.

创建员工表tb_emp1。
首先创建数据库test_db，SQL语句如下：

create database test_db;
选择创建表的数据库，SQL语句如下：

use test_db;

create table 表名
(字段名数据类型字段属性，
.....);

create table tb_emp1
(id int(11) ,
name varchar(25),
depId int(11),
salary float);

查看数据表是否创建成功，SQL语句如下：

show tables;

查看表格的结构：
describle 表名
desc 表名

desc tb_emp1;

主键:primary key 对字段具有非空和唯一的约束

create table tb_emp2
(id int(11) not null primary key,
name varchar(25) not null,
deptId int(11) ,
salary float );

create table tb_emp3
( id int(11) not null,
name varchar(25),
depId int(11) ,
salary float,
primary key(id));

3、定义数据表tb_emp4，假设表中间没有主键id，为了唯一确定一个员工，可以把name、deptId联合起来做为主键，
SQL语句如下：
+--------+-------------+------+-----
| Field | Type | Null | Key |
+--------+-------------+------+-----+
| name | varchar(25) | NO | PRI |
| deptId | int(11) | NO | PRI |
| salary | float | YES | |
+--------+-------------+------+-----+

create table tb_emp4
(name varchar(25) not null,
depId int(11) not null,
salary float ,
primary key(name,depId));

4、定义数据表tb_emp5，并在tb_emp5表上创建外键约束。

create table tb_dept1
( id int(11) not null primary key,
name varchar(22) not null,
location varchar(50));

MUL 不是外键的标志，而是索引的标志。
普通索引:key|index

外键: foreign key
constraint 外键名 foreign key(字段名) references 表名(字段名)
foreign key（字段名） references 表名(字段名）

create table tb_emp5
( id int(11) not null primary key,
name varchar(22),
deptId int(11),
salary float,
constraint fk_ed foreign key(deptId) references tb_dept1(id));

6、定义数据表tb_dept2，指定部门的名称唯一，SQL语句如下：

唯一键: unique key

create table tb_dept2
( id int(11) not null unique key,
name varchar(22) unique key,
location varchar(50));

可以最后添加唯一键:
unique key 唯一键名(唯一键)

7、定义数据表tb_dept3，指定部门的名称唯一，SQL语句如下：

create table tb_dept3
(id int(11) not null primary key,
name varchar(22),
location varchar(50),
unique key unique_key_name(name));

8、定义数据表tb_emp7，指定员工的部门编号默认为1111，SQL语句如下：
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(25) | NO | | NULL | |
| deptId | int(11) | YES | | 1111 | |
| salary | float | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
默认值 default

create table tb_emp7
(id int(11) not null primary key,
name varchar(25) not null,
deptId int(11) default 1111,
salary float );

9、定义数据表tb_emp8，指定员工的编号自动递增，SQL语句如下：
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(25) | NO | | NULL | |
| deptId | int(11) | YES | | NULL | |
| salary | float | YES | | NULL | |
+--------+-------------+------+-----+---------+----------------+

自动:auto_increment

注意:自动auto_increment 只能在具有主键或唯一键的字段上添加

create table tb_emp8
(id int(11) not null primary key auto_increment,
name varchar(25) not null,
deptId int(11) ,
salary float);

10、分别使用DESCRIBE和DESC查看表tb_dept1和表tb_emp1的表结构。
查看tb_dept1表结构，SQL语句如下：

desc tb_dept1;
describe tb_dept1;

11、将数据表tb_dept2改名为tb_deptment2。再将表名tb_deptment2改为tb_dept2；

alter table 表名 rename 新表名

alter table tb_dept2 rename tb_deptment2;

alter table tb_deptment2 rename tb_dept2;

12、将数据表tb_dept1中name字段的数据类型由VARCHAR(22)修改成VARCHAR(30)。

修改属性:modify 可以修改字段名后面
modify 不能修改主键，但是可以增加主键，不能减主键

alter table tb_dept1 modify name varchar(30) not null;

注意:modify 重新修改的属性要全面
13、将数据表tb_dept1中的location字段名称改为loc，数据类型保持不变，

修改字段名 change
alter table 表名 change 字段名新字段名数据类型字段属性

alter table tb_dept1 change location loc varchar(50) not null;

14、将数据表tb_dept1中的loc字段名称改为location，同时将数据类型变为VARCHAR(60)，

alter table tb_dept1 change loc location varchar(60) not null;

15、在数据表tb_dept1中添加一个没有完整性约束的INT类型的字段managerId（部门经理编号）

altr table 表名 add 字段名　数据类型　字段属性　first|alter 字段名

alter table tb_dept1 add managerid int;

16、在数据表tb_dept1中添加一个不能为空的VARCHAR(12)类型的字段column1，SQL语句如下：

alter table tb_dept1 add column1 varchar(12) not null;

17、在数据表tb_dept1中添加一个INT类型的字段column2，SQL语句如下：

alter table tb_dept1 add column2 int first;

18、删除数据表tb_dept1表中的column2字段。

alter table 表名 drop 字段名
alter table tb_dept1 drop column2;
19、将数据表tb_dept1中的column1字段插入到location字段后面，SQL语句如下：

alter table tb_dept1 modify column1 varchar(12) not null after location;

20、删除主键;

alter table tb_emp7 drop primary key;

21.添加主键
alter table tb_emp7 add primary key(id);

22.删除唯一键
index 唯一键名字
alter table tb_dept2 drop index name;

23.添加唯一键
alter table ta_dept2 add unique key(name);

11.数据库保存笔记

开启文件 tee

最后结束文件 notee;

2017年2月19日

1.方案

方案(假设分析工具):是一组命令的组成部分。预测工作表模型的输出结果。同时还可以在工作表中创建并保存不同的数值组，然后切换到任意新方案以查看不同的结果。

2.数据有效性

数据有效性:限制其数据能录入的有效范围。

数据有效性"序列":是数据有效性中非常常用和重要的功

序列:可以在单元格中自定义下拉列表的内容

create table person
(id int(10) unsigned not null primary key auto_increment,
name char(40) not null default '',
age int(11) not null default 0,
info char(50) );

insert into 表名(字段名)
value (记录1)，(记录2)....;

insert into person(id,name,age,info)
value(1,'green',21,'lawyer'),
(2,'suse' ,22,'dancer'),
(3,'tom',22,'musician'),
(4,'mike',20,'teacher');

insert into person
value(1,'green',21,'lawyer'),
(2,'suse' ,22,'dancer'),
(3,'tom',22,'musician'),
(4,'mike',20,'teacher');

insert into person(name,age,info)
value('green',21,'lawyer'),
('suse' ,22,'dancer'),
('tom',22,'musician'),
('mike',20,'teacher');

查询表格信息：

select * from person;

create table person_old
(id int(10) unsigned not null primary key auto_increment,
name char(40) not null ,
age int(11) not null default 0,
info char(50) );

4、向表格person_old中插入数据

+----+------+-----+---------+
| id | name | age | info |
+----+------+-----+---------+
| 11 | l | 20 | student |
| 12 | m | 30 | police |
+----+------+-----+---------+

insert into person_old
value(11,'l',20,'student'),
(12,'m',30,'police');

5、将另一张表转存到一张表中

insert into person(id,name,age,info)
select id,name,age,info from person_old;

insert into person(id,name,age,info)
select * from person_old;

注意:没有value这个单词

删除数据：

delete from 表名 where 条件
delete from person where id>10;

创建表格person1，表结构和person相同:

create table person1 like person;//表结构一样，但是表没有数据 like

按照person表信息查询的结果创建表格person2：

create table person2 as select * from person;//有数据，表结构有可能不同 as

注意:like 与as的区别

6，更新数据
对数据表person中id为11的age改为15，name改为lining；

update 表名 set 字段名= 新数据.... where 条件;

update person set age=15,name='linling' where id=11;

在person表中，更新age值为19-22的记录，将info字段值都改成student；

update person set info='student' where age between 19 and 22;

3,删除数据
delete from tale_name[where condition] (condtion条件）
删除数据表person中id为11的记录

delete from person where id=11;
4,在person表中，删除age值为19-22的记录

delete from person where age between 19 and 22;

select 字段名
from　表名
where 条件
group by (分组)
having 条件
order　by (排序)
limit m,n
union

表名->条件->分组->条件->字段名-union -> 排序->limit
from ->where-> group by ->having —>union->order by ->limit

1、从fruits表中检索所有字段的数据，SQL语句如下：

select * from fruits;

2、查询fruits表中f_name列所有水果名称，SQL语句如下：

select f_name from fruits;

3、例如，从fruits表中获取f_name和f_price两列，SQL语句如下：

select f_name,f_price from fruits;

4、查询价格为10.2元的水果的名称，SQL语句如下：
select f_name,f_price from fruits where f_price=10.2;

5、查找名称为“apple”的水果的价格，SQL语句如下：

select f_name,f_price from fruits where f_name="apple";

6、查询价格小于10的水果的名称，SQL语句如下：

select f_name,f_price from fruits where f_price<10;

7、s_id为101和102的记录，SQL语句如下：

select * from fruits where s_id in(101,102); //in （不是区间，而是集合）

select * from fruits where s_id= 101 or s_id=102;

8、查询所有s_id不等于101也不等于102的记录，SQL语句如下：

select * from fruits where s_id not in (101,102); //not in （集合）不在集合内

select *from fruits where s_id <>101 and s_id != 102;

不等于 <> 或者！=

9、查询价格在2.00元到10.20元之间的水果名称和价格，SQL语句如下：
select f_name,f_price from fruits where f_price between 2.00 and 10.2;

select f_name,f_price from fruits where f_price>=2.00 and f_price<=10.20;
10、查询价格在2.00元到10.20元之外的水果名称和价格，SQL语句如下：

select f_name,f_price from fruits where f_price not between 2.00 and 10.2;

create table fruits_new as select * from fruits;//创建fruits_new 这张表，并有值

select * from fruits where f_price<10 union select * from fruits_new wher
e s_id in(101,103);//union 除重

mysql> select * from fruits where f_price<10 union all select * from fruits_new
where s_id in(101,103);//union all 不去重

去重：distinct
12、查询fruits表中的s_id有哪些：

select distinct s_id from fruits;

EXISTS exists 存在
13、查询fruits表中是否存在s_id=107的供应商，如果存在，则查询fruits表中的记录，SQL语句如下

select * from fruits
where exists(select * from fruits where s_id=107 );

14、查询fruits表中是否存在s_id=107的供应商，如果存在，则查询fruits表中的f_price大于10.20的记录，SQL语句
如下：

select * from fruits where exists (select * from fruits where s_id=107) and f_price> 10.20

15、查询fruits表中是否存在s_id=107的供应商，如果不存在则查询fruits表中的记录，SQL语句如下

select * from fruits where not exists (select * from fruits where s_id=107) and f_price> 10.20

16、ORDER BY 字段名 asc（升序）|desc（降序）:
查询数据表fruits中水果的信息，并按照价格进行升序排列,SQL语句为

select * from fruits order by f_price asc;

17、查询数据表fruits中水果的信息，首先按照s_id升序，在按照f_price进行降序排列

select *from fruits order by s_id asc,f_price desc;

LIMIT limit(偏移量，几行数据)
18、查询数据表fruits中前5行的信息

select * from fruits limit 0,5;
select * from fruits limit 5;

19、查询数据表fruits中第3行到第6行的信息

select *from fruits limit 2,4;//从0 开始偏移量

函数：不能直接在where后面用
最大值: select max(字段名) from 表名 where 条件
最小值: select min(字段名) from 表名 where 条件
求和值: select sum(字段名) from 表名 where 条件
平均值: select avg(字段名) from 表名 where 条件
计数: select count(字段名) from 表名 where 条件

GROUP BY order by 分组
20、查询数据表fruits中每个供应商各自水果的平均价格

select s_id, avg(f_price) from fruits group by s_id;

HAVING
21、查询每个供应商各自水果的平均价格大于5元的记录；

select s_id ,avg(f_price) from fruits group by s_id having avg(f_price)>5;

22、查找f_name所有以’b’字母开头的水果，SQL语句如下：

like 通配符:%,单个字符_

select * from fruits where f_name like 'b%';
23、在fruits表中，查询f_name中包含字母'g’的记录，SQL语句如下：
select * from fruits where f_name like '%g%';

24、查询以’b’开头，并以’y’结尾的水果的名称，SQL语句如下：

select * from fruits where f_name like 'b%y';
25、在fruits表中，查询以字母’y’结尾，且’y’前面只有4个字母的记录，SQL语句如下：

select * from fruits where f_name like '____y';

26、在fruits表中，f_name中包含‘b’或者‘x'的记录，SQL语句如下：

select * from fruits where f_name like '%b%' or f_name like '%x%';

正则表达式：regexp

以b为开头

select * from fruits where f_name regexp '^b';

以y为结尾

select * from fruits where f_name regexp 'y$';

包含b或x（|两边可写单个字符，也可写字符串）

select * from fruits where f_name regexp 'b|x';

包含b或x,只能匹配单个字符

select * from fruits where f_name regexp '[bx]';

匹配字符集中除去a到x的其他字符。

select * from fruits where f_name regexp '[^a-x]';
不包括xyz
select * from fruits where f_name not regexp '[xyz]';

匹配包含y且y前面最少四个字符.

select * from fruits where f_name regexp '....y';
.*:*前面的字符连续出现n个，n包含0； *={0,} like中 %=.*

ab* a ab abb abbb abbb....
ba连续出现最少两次

select * from fruits where f_name regexp '(ba){2,}';
//ba只出现两次

select * from fruits where f_name regexp '(ba){2,}'and f_name not regexp '(ba){3,}';

(ba)+:+表示符号之前的字符连续出现1次以上；{1,}

mysql 中的中文配置文件 my.ini

配置之后，服务断开之后重启。如果还不可以，需要进行修改数据库utf8

mysql> show create database company;
+----------+------------------------------------------------------------------+
| Database | Create Database |
+----------+------------------------------------------------------------------+
| company | CREATE DATABASE `company` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter database company default character set utf8;
Query OK, 1 row affected (0.04 sec)

多表查询：

create table suppliers
(s_id int(11) not null primary key auto_increment,
s_name char(50) not null,
s_city char(50),
s_zip char(10),
s_call char(50) not null);

4、向数据表suppliers中插入需要演示的数据，SQL语句如下：
+------+----------------+-----------+--------+--------+
| s_id | s_name | s_city | s_zip | s_call |
+------+----------------+-----------+--------+--------+
| 101 | FastFruit Inc. | Tianjin | 300000 | 48075 |
| 102 | LT Supplies | Chongqing | 400000 | 44333 |
| 103 | ACME | Shanghai | 200000 | 90046 |
| 104 | FNK Inc. | Zhongshan | 528437 | 11111 |
| 105 | Good Set | Taiyuan | 030000 | 22222 |
| 106 | Just Eat Ours | Beijing | 010 | 45678 |
| 107 | DK Inc. | Zhengzhou | 450000 | 33332 |
+------+----------------+-----------+--------+--------+
insert into suppliers
values(101,'FastFruit Inc','Tianjin','300000','48075'),
(102,'LT Supplies','Chongqing','400000','44333'),
(103,'ACME','Shanghai','200000','90046'),
(104,'FNK Inc','Zhongshan','528437','11111'),
(105,'Good Set','Taiyuan','030000','22222'),
(106,'Just Eat Ours','Beijing','010','45678'),
(107,'DK Inc','Zhengzhou','450000','33332');

5、在fruits表和suppliers表之间使用内连接查询。
查询之前，查看两个表的结构：

select * from fruits,suppliers
where fruits.s_id=suppliers.s_id;

连接查询：
select 字段名 from 表1 inner|left|right join 表2
on 表1.字段名=表2.字段名 inner|left|right join 表3 on 条件
where 条件;

内连接:
select * from fruits inner join suppliers
on fruits.s_id=suppliers.s_id;

左连接：
select * from fruits left join suppliers
on fruits.s_id=suppliers.s_id;

右连接:

select * from fruits right join suppliers
on fruits.s_id=suppliers.s_id;

6、在fruits表和suppliers表之间，使用INNER JOIN语法进行内连接查询，SQL语句如下：

7、在fruits表和suppliers表之间，使用left join语法进行左连接查询，SQL语句如下：

8、在fruits表和suppliers表之间，使用right join语法进行右连接查询，SQL语句如下：

取别名：as

select f_id as fruit_id,s_id,f_name as fruit_name ,f_price from fruits;

select f_id,f1.s_id as fs_id,f_name,s_name from fruits as f1 inner join suppliers as s1
on f1.s_id=s1.s_id
where f1.s_id in (101,103);

判断语句：case

select f_id,s_id,f_name,f_price,case
when f_price<5 then '5元以内'
when f_price between 5 and 10 then '5-10元'
else '10元以上'
end as 价格区间
from fruits;

子查询

9、使用内连接查询供应f_id= ‘a1’的水果供应商提供的水果种类，SQL语句如下：

select s_id from fruits where f_id='a1';

select f_id,s_id,f_name from fruits
where s_id=101;

select f_id,s_id,f_name from fruits
where s_id=(select s_id from fruits
where f_id='a1');

select f_id,fruits.s_id fs_id,f_name,f_price,suppliers.* from fruits inner join suppliers
on fruits.s_id=suppliers.s_id;

select f_id,s_id,f_name from
(select f_id,fruits.s_id fs_id,f_name,f_price,suppliers.* from fruits inner join suppliers
on fruits.s_id=suppliers.s_id) as f1
where s_id=(select s_id from fruits
where f_id='a1');

11、分别向两个表中插入数据：

insert into tb1
values(1),(3),(5),(7),(9),(13),(17);

insert into tb2
values(2),(4),(6),(8),(10),(12);

12、返回tb2表的所有num2列，然后将tb1中的num1的值与之进行比较，只要大于num2的其中一个值，其为符合查询条件的结果。

select num2 from tb2;

select num1 from tb1
where num1>any(
select num2 from tb2);

select num1 from tb1
where num1>(
select min(num2) from tb2);

13、返回tb1表中比tb2表num2 列所有值都大的值，SQL语句如下：

select num1 from tb1
where num1>all(
select num2 from tb2);

select num1 from tb1
where num1>(
select max(num2) from tb2);

2017年2月20日

1.多个下拉列表的切换

2.数据分析能做什么？活动复盘、项目立项、专题报告、项目优化、市场预测、改进技术、帮助研发。

3.应用实例:医疗行业、能源行业、通信行业、电商行业

4.数据的来源：

(1)外部数据：社会人口数据宏观经济数据新闻国情数据市场调研数据

(2)内部数据: 用户行为数据服务端日志数据 CRM&交易数据

5.方法论是从宏观角度出发，从管理和业务的角度提出的分析框架，知道我们接下来具体分析的方法。方法是微观的概念，是指我们在我们在具体分析过程中使用的方法:

SWOT分析法:从优势(Strength)、劣势(Weakness)、机遇(Opportunity)、威胁(Threat)四个方面分析内外环境，适用于宏观分析。

5W2H分析法:从Why、When、Where、What、Who、How、How much 7个常见的维度分析问题。

PEST分析法:从政治(Politics)、经济(Economy)、社会(Society)、技术(Technology)四个方面分析内外环境，适

用于宏观分析。

6.用户购买行为分析:why what who when where How 、How much

11.

12.

13.什么时数据分析:它就是基于商业目的，有目的地进行收集、整理、加工和分析数据，提炼有价值信息的一个过程。

2017年2月21日

1.跨工作表的序列引用

2017年2月22日

1.双关键字限制

注意:A45与B45= 中A与B要绝对引用，因为列号不变

2017年2月23日

1.用户行为理论：

用户使用行为是指用户为获取、使用物品或服务所采用的各种行为，一般按照以下过程:对产品有一个认知、熟悉的过程，然后试用，再决定是否继续消费使用，最后成为忠实用户。

认识->熟悉->使用->使用->忠诚

2.AARRR理论:增长黑客的海盗法则，精益创业的重要框架，从获取(Acquisition)、激活(Activition)、留存(Retention)、变现(Revenue)和推荐(Referral)5个环节增长。互联网企业可以重点关注AARRR方法论，对于精益化运营、业务增长的问题，这个方法论非常契合。

3.AARRR理论漏斗图

4.数据分析方法论

5.对比分析法:

(1)对比分析法是将两个或两个以上的数据进行比较，分析其中的差异，从而揭示这些食物所代表的发展变化情况和规律性。

6.对比分析法:

(1)指标的口径范围、计算方法计量单位一致。

(2)对比的对象要有可比性

(3)对比的指标类型必须一致

7.杜邦分析法

(1)利用各主要财务指标间的内在联系，对企业财务状态及经济效益进行综合分析评价的方法。

8.矩阵关联分析法:

9.具体分析方法:

10.数据分析报告:

(1)数据分析报告是根据数据分析原理和方法，运用数据来反映、研究和分析事物现状、问题、原因、本质和规律，并得出结论，提出解决问题的一种分析应用文体。

分析报告种类:

(1)日常数据通报:月度数据报告、日报表

(2)专题分析报告:用户流失分析、提升用户消费分析

(3)综合分析报告:企业运营报告、世界人口发展报告

数据分析报告结构:

分析背景->分析目的->分析思路->正文->结论与建议->附录

11.什么是B2C?

B2C是Business-to-Customer的缩写，而其中文简称为"商对客"。"商对客"是电子商务的一种模式，也就是通常说的商业零售，直接面向消费者销售产品和服务。

12.天猫的6大优势:

13.天猫的4大特色

14.天猫的店铺类型:旗舰店、专卖店、专营店

15.

16.免费推广:

免费推广是指不花钱的情况下，对产品或店铺作出的推广行为。

免费推广:标题优化、宝贝上下架调整、产品属性的完整、主图的点击率

17.付费推广：

付费推广是指通过工具或方法，获得流量，从而提高店内的流量和销量。

付费推广:直通车、钻石展位、淘宝客

18.直通车

(1)直通车:按单次点击收费的引流工具，适合单品爆款打造。

19.钻石展位

(1)钻石展位:按千次展现收费的引流工具，适合大型活动或品牌推广。

(2)

20.淘宝客:

(1)淘宝客:按成交结果收费的引流工具，适合日常推广。

(2)

21.

22.数据分析第三方工具:江湖策，生意参谋，数据魔方

23.会员分析的目的:提高复购率、提高客单价、合理维护客户关系、恰当推广商品。

24.千牛卖家工作台是由阿里巴巴集团官方出品，由PC版本和手机版本，淘宝卖家，天猫商家均可使用。千牛工作台包含卖家工作台、消息中心、阿里旺旺、量子恒道、订单管理、商品管理等主要功能。

25.生意参谋:

26.后台导航-店铺左侧栏

27.

28.流量的本质(个性化搜索)

2018年2月24日

1.产品分类

严格意义上来说，爆款分二种、一种是利润爆款，一种是引流爆款。利润爆款也叫小爆款，引流爆款叫大爆款，从成本上来说，引流爆款往往会利润较低！

定价策略

（1）常规款：常规款为店铺的主营产品。此类产品多以成本导向为定价方法进行销售。区别于流量款有价格优势，区别于活动款有折扣优势，区别于利润款，毛利率并不是很高。

（2）流量款：流量款多为引流产品，除有自身的产品优势外，其性价比也很占优。对于同类同质产品能在价格的引导上做出很好的示范，起到吸引店铺流量的作用。

（3）利润款：顾名思义，利润款为产品本身具有核心优势或产品类别同质同类竞争少，所以在利润的追求上会过多。

（4）活动款：活动款的定价需要留有一定的折扣空间，刺激消费者购买，往往作为促销使用。

（5）爆款：爆款的定价策略为区间浮动，根据爆款的预热期，产品价格偏低，追求性价比；到爆款的发展期，产品价格保持优势；到爆款的成熟期，价格可以略微上涨；到爆款的衰退期，低价引流为其他爆款做预热，或将爆款转为利润款，榨干该爆款的剩余价值。

（6）边缘款：此类产品销售形势较低迷，没有爆款或相对爆款的潜质，不具备其他定价策略的核心特点，往往被卖家和消费者忽视。

3.三个钻展的三个阶段

5.定向推广

(1)定义:

(2)定向原理

6.直通车

8.淘宝客

如意投的特点是:系统智能精确投放流量可控渠道精确

10.
CRM其实是指所有我们与客户接触点的关系管理。

CRM的目的是为了满足客户需求;维护好喝客户之间的关系。

客户关系管理本质:对客户进行细分根据客户价值以及特征进行差异化营销。

11.

12.Excel 冻结

2017年2月25日

1.网店核心思维图

数据化流量精准获取

网店运营靠流量，大多数工作都是围绕流量展开，很多电商人会把流量看作店铺的生命线，但是很容易走入一味砸钱买流量的误区。流量问题应该客观的看待，流量应该围绕着我们日常运营，运营是有机会的，而流量也应该有规划，每个点击背后都是一个个活生生的顾客，不同的流量入口有着其固有的基因，每个关键词的背后也代表着一定的市场需求，本章节主要分析，如何用数据化的方式精准定位自己的目标顾客，降低引流成本，从而占领主搜阵地。

一、数据制定流量规划

我们明确做电商是为了做生意，而非做流量，不管是从长期来说，还是从短期来说，获取流量的最终目的还是为了成交，既然一切围绕生意，而生意应该有所计划的，那么对于流量我们就应该有所规划。

1、流量的需求

（1）销量的目标决定流量的数量需求

公司无论大小，都会做年度规划，哪怕没有年度规划，老板心中也有一年的计划，而年度规划中很重要的一块就是销售目标规划。

首先，我们看一下销售额公式：

销售额=流量*转化率*客单价

这样，可以得出来：

流量=销售额/（转化率*客单价）

在定位及产品品类不发生太大变化的情况下，大多数店铺转化率和客单价，一定时间内都比较稳定，所以我们就可以根据销售目标来推倒出来流量的需求。

但是，要注意几点：一、不同的流量入口，转化率及客单价会有所差异，其中活动流量所占的比重最大，可以单独考虑。二、销量目标、转化率目标、客单价目标也应该根据类目节奏和自己的综合情况进行微调。

图1全年流量需求表

当然，制定目标的过程也是需要和相应团队一起来制定的，目标的不同决定行为的不同，当然这个涉及到团队管理的内容，这里不做讨论，只是需要强调的是，既然目标制定了，我们应该让团队的工作围绕目标去展开，而流量的目标应该是负责运营推广的同事去实现。

（2）转化的需求决定流量的质量需求

做电商，不仅仅有流量的需求，流量的目的是为了转化，这里就涉及到一个流量质量的问题，即流量与店铺产品定位的匹配程度。不同的流量，在一样的产品、一样的视觉及一样的客服水平面前，也会有不同的表现，转化率、客单价、以及产品的销售结构都有所不同。

流量质量需要怎么把控呢？这里给大家介绍一个流量质量金字塔原理：流量应该是分层的，顶部的流量相对比较少，与产品定位匹配性高，转化率高，也就是我们所说的精准流量；中部的流量精准性良好，转化率一般，数量也比顶部的流量大；而最下面的流量则是属于比较宽泛的流量，不太精准，转化率比较低，同时流量也比较大。

我们在获取流量的时候应该从顶部开始获取，对于不同入口的流量获取的方法也不同：一、自然搜索及直通车流量。自然搜索是根据关键词来获取流量的，而直通车流量本质上也属于搜索流量，只是我们可以通过出价以及一些其他的推广技巧提高排名。通过关键词获取流量的精准性，取决于关键词的精准性，比如，对于一个卖12岁男童春秋牛仔裤的卖家来说，“牛仔裤”，“童装牛仔裤”、“男童牛仔裤”、“男大童牛仔裤”、“男大童牛仔裤春秋”，这一系列关键词的精准性就越来越精准，相对来说转化率也较高。二、钻石展位。钻展是通过向被定向的消费者展现广告，吸引消费者点击，而产生的流量。定向的准确与否，直接影响了钻展的流量精准性，同时影响钻展流量的精准程度的还有钻展素材的设计等。三、淘宝客。淘宝客的流量，如果淘宝客是通过“爱淘宝”搜索进来的流量，也是属于搜索流量，精准性取决于站外搜索的关键词的精准性。如果是通过自己招募的淘宝客进来的流量，则取决于淘宝客的平台与所营产品的匹配程度。四、活动流量。活动流量的精准性取决于活动平台上浏览的顾客与所营产品的匹配程度。

图2流量金字塔原理

2、流量的基因

（1）顾客购物习惯的改变

消费者消费过程，由“购物”变成了“逛街”。在80年代，那时候还没有超市，我们到商店购物，是进不了商店柜台的，顾客需要买什么，然后老板就会拿什么，这时候顾客平均停留时间极短，没有访问深度，更谈不上关联销售；到90年代以后，超市开始出现，顾客可以进入店内任意选购，这时候顾客平均停留时间开始得到增长，访问深度也开始加深，很多超市为了让顾客逛更多的东西，开始规划顾客行走路线；而宜家则把这方面做到了极致，在店内布置不同的主题，迷宫式布局，让顾客流连忘返，还不觉得累，顾客的访问深度及停留时间得到了极大的增长。

（2）顾客购物路径的改变

顾客的这种消费行为改变，在网路上依然存在，淘宝平台上，通过搜索成交的比例逐年在降低，而通过主题性活动等形式成交的比例逐步上升。这说明更多的顾客喜欢在愉悦的浏览中购物。如果说搜索流量带着理性购物的基因，更多的是一种刚性的需求，而主题性活动的流量则带着感性购物的基因，更多的是一种冲动型消费。

不同的流量有着其特定的基因，哪怕就是每一个关键词背后也是蕴含着不同的消费群里，比如说，“婴儿推车欧式”代表着一种对品质的需要，“韩版童装”代表着一种对于潮流款式的追求，钻展也是如此，不同的位置，不同的定向，顾客群体都不一样，在做流量规划的同时，更应该注意流量的基因是否与产品定位相匹配。

3、流量规划表

在了解了各个入口的流量基因，熟悉各个入口的流量转化率、客单价及流量成本以后，我们可以把每年的流量目标按月来划分，这样就是每个月的流量规划表，在制定好每月流量规划表以后，需要分解实施，同时进行监控，确保最终流量目标的达成。

二、数据把控推广节奏

大多数类目，市场的需求在一年当中会有变化，那么我们的推广节奏也应该随着市场的变化而改变，导致市场需求变化的因素有产品本身的特性，也有平台的促销节奏，同时也跟传统的节日相关，了解全年的营销节奏，需要推广在旺季来临之前把自己的产品推到搜索排名的前几名，从而在旺季来临的时候获取大量流量。

1、产品销售节奏。

（1） 类目销售节奏。推广节奏的安排首先需要考虑本类目的销售节奏，具体可以在数据魔方è行业分析è整体情况中查看，需要注意的是查询时间需要选择1年。

图3类目销售节奏

（2） 子类目销售节奏。光分析类目营销节奏还不够，我们还需要看每个子类目的销售节奏。可以在行业选择的时候，选择子类目查看，同样时间也需要选择1年。

图4类目销售节奏

（3） 单品生命周期。涉及到单品打造的时候，还需要考虑单品的生命周期，我们可以在淘宝指数，根本本产品的主搜关键词进行查询。例：男童长袖T恤。

图5单品生命周期

在了解所经营产品的销售节奏以后，就需要针对性的进行推广投入、老顾客营销、组织促销活动，在进入快速增长的时候大量投入，而进入成熟期以后，可以根据每个波段进行阶段性投入。

2、全年促销节点

做营销还讲究师出有名，我们所有的活动必须要有主题，可以是传统节日、重要的文体赛事、突发的热门话题等等，我们可以对全年的相关主题进行总结。

而不同的主题适合的活动也不一样，有些是可以用来塑造品牌，有些是可以进行大规模促销，促销也需要避免频率太高，不同的类目可以进行自行选择。

3、平台营销安排

每年淘内的平台也会有自己的营销节奏安排，如传统的“双十一”、“双十二”、“66大促”、“99大促”等，同时类目也会有一些日常的营销安排，不同类目会有所不同，具体可以参考类目帮派。

如果了解了平台的营销节奏，我们就可以把店内营销活动和平台的营销活动结合，灵活借力；同时我们还需要了解各活动的要求，争取能够参与进去。

4、全年营销计划表

最后我们可以制定店铺的全年营销计划表，针对不同时间点的热销类目，结合当时的热点时事或者即日，整合平台的资源，提高店铺的营销活动效果。

图6全年营销节奏

三、数据提高展现量

1、排名与流量

按照PV计算，前三页占有所有PV的84%，其中第一页65%，第二页11%，第三页7%，而从点击即IPV的角度，前三页占有所有IPV的86%，其中第一页占68%，第二页占12%，第三页占6%，从成交金额来看第一页更是占了所有成交金额的88%，占成交笔数的87%。这说明了占领排名不管对于流量获取来说，还是对于最终成交来说，都显得特别重要。

2、上下架时间优化

（1）上下架时间的概念及影响。淘宝的排名中有一个模型叫做时间模型，天猫和淘宝中的所有宝贝上架后第7天都会有下再上架的过程，在淘宝搜索中，下架之前的一段时间内，在综合排序里宝贝会得到一定的加权，竞争的宝贝数越多，得到的加权时间就越短。

（2）一天中的顾客访问高峰。在淘宝平台上，不同的时间点访问的人数，搜索的人数都不同，不同类目的顾客访问的时间也有所差别，具体我们可以通过数据魔方è行业分析è卖家信息分析，中查看查询。

图7 PC来访分析

图8无线来访分析

（3）根据顾客来访高峰来安排上下架。可以看出来，PC的访问时间与无线的访问时间有所差别，无线更集中在晚上，为了获得一个较好的排名，我们会把主推的宝贝放到流量较大的时间段，那么如果店铺产品更适合于顾客在PC上浏览，则可以多根据PC端上下架时间进行安排，如果店铺产品属于单价比较低的快速决策类产品，即顾客购买所要花的决策时间比较短的话，可以多安排在晚上20：00到23：00上下架。

有一种情况需要注意，其实安排上下架的时候就类似于田忌赛马，有些时候我们宝贝的竞争力不强，而行业竞争又很激烈，这些宝贝其实还是建议安排在竞争较小的时间段上下架。

一般情况下（具体可以用一些软件来查看类目的竞争情况），早10点的竞争较为激烈，但是早10点的流量跟一天当中下午及晚上的流量相差不大，所以安排上下架的时候也需要重点考虑。

如果处于爆款竞争的类目，建议卖家监控竞争对手的上下架时间，来针对性的安排自己店铺宝贝的上下架时间。

（4）上下架需要考虑的同店打散原则。搜索中有个同店打散原则，即除了豆腐块（人气及综合排序的前三名为商城位置，成为豆腐块），每个搜索结果页面（综合和人气）出现的同店产品最多不会超过2个。那么这是时候就遇到了问题，如果一个店铺内同类产品，被统一关键词覆盖，都在同一时间段上下架，那么即使所有宝贝的人气都很高，也只能在统一页面出现2个，其他产品都会被挤到后面去，所以为了得到最大量的曝光，我们可以在安排上下架时间的时候，同类产品分开，店内最热销的款上下架时间的当天，甚至可以不安排同类产品。

（5）通过上下架考虑避开强劲竞争对手。我们甚至有办法通过一些软件查询同类热销产品的上下架时间，为了避开强劲竞争对手，我们可以把自己产品与竞争对手产品错开时间进行上下架。

理解了以上的原理，现提供一个工具帮助读者安排自己产品的上下架时间，从而有效的提高展现量。

第一步：把店内产品根据不同的类别进行分类，把被同一类关键词覆盖的产品放到同一类别里：

图9店铺产品梳理表

第二步：根据顾客来访高峰，把产品的上下架时间安排在不同的时间段。

图10上下架时间计划表

小技巧：（1）在调整上下架时间的时候，适宜把时间往本身的上下架时间往后移，而避免往前移，举个例子，如果一款产品是周一早上11点15分下架，那么我们调整的时候可以调整到周一11点15分以后，而不要调整到周一的11点15分以前，以避免损失一次下架时间之前的曝光。（2）按照访问高峰安排上下架同时面临的一个挑战就是，那个时间段竞争会比较激烈，如果一个新开的店铺，可以在次高峰时间段安排上下架，避开高峰，能得到一个更长时间的搜索加权。

3、橱窗推荐优化

橱窗推荐（又名卖家热推），即每位商家根据店铺实际经营情况，将店铺内最有竞争力的宝贝通过设置成橱窗推荐的方式（在“我是卖家”—“宝贝管理”—“橱窗推荐”中设置），在淘宝搜索排序中，其他条件相同的情况下，橱窗推荐宝贝将获得优先展示机会。橱窗推荐规则仅应用在淘宝搜索排序下，不影响天猫搜索排序。

（1）集市店新橱窗获得规则

规则维度	规则内容
信用等级	星级卖家奖励10个橱窗位；钻级卖家奖励20个橱窗位；冠级卖家奖励30个橱窗位。
开店时间	开店时间少于90天内，奖励10个橱窗位；开店时间满1年奖励2个橱窗位；开店时间满2年奖励5个橱窗位；开店时间满3年奖励10个橱窗位。
消保	缴纳消保保证金的，奖励5个橱窗位
店铺周成交额（不同类目有所差异）	周成交额是指上周的周成交额，计算周期为周四0点至周三23:59:59；根据你的信用等级，店铺上周支付宝周成交额：达到XX金额时，奖励30个橱窗位；达到XX金额时，奖励45个橱窗位
金牌卖家	金牌卖家奖励：5个橱窗位
违规扣分	一般违规扣分（A类扣分）满12分及以上，扣除5个橱窗位；严重违规扣分（B类扣分）满12分及以上，扣除10个橱窗位；出售假冒商品被违规扣分（C类扣分）满24分及以上，扣除20个橱窗位。

（2）天猫店橱窗获得规则

2013年4月18日生效的《天猫橱窗推荐规则》根据天猫商家每个月（以自然月为计算单位）的销售金额，分设七个层级，每个层级有不同的橱窗推荐位数量。

月销售金额：指上个自然月成交并且已成功的交易。

月销售金额（M）	橱窗推荐位（个）
M<1.5万	60
1.5万=<M<10万	100
10万=<M<30万	200
30万=<M<100万	300
100万=<M<300万	500
300万=<M<500万	1000
M>= 500万	2000

（3）利用橱窗推荐提高展现量

橱窗推荐会影响搜索结果的综合排序和人气排序，会有非常高的加分，为了将流量集中到我们的优势宝贝，一般我们会设置10-15个橱窗位（根据宝贝数的多少和橱窗位的多少会有所调整）推荐临近下架时间的宝贝，其他的橱窗推荐位推荐店内最热销或者最想主推的宝贝。

4、关键词的匹配原则

标题关键词的优化是搜索优化的重要组成部分，这是很多卖家最感兴趣的工作，却又觉得难以下手，这里只是从提高展现量的角度跟大家分享几个要点，重要提示，一下只是搜索的一些小技巧和知识，具体实施需要结合所经营的类目进行融会贯通：

（1）尽量的覆盖更多的搜索关键词。我们分析淘词的TOP500词就会发现，对于大多数类目来说，TOP500词都是有为数不多的字进行各种各样的排列组合，而只要标题中覆盖了消费者搜索的关键词，只要不存在分词上的问题，那么理论上都有可能被搜索到，所以我们可以在优化标题的时候考虑到尽量多的覆盖搜索关键词。

（2）利用完全匹配提高可拆分关键词的关键词匹配分。在人气排序中，关键词的匹配分是人气的一个组成部分，什么叫完全匹配，举个例子，比如在搜索“男童牛仔裤”这个关键词，这个关键词在搜索过程中会被拆成“男童牛仔裤”这样的几个词根，在童装类目中，只要包含“男童牛仔裤”这几个词根的，不管排列顺序如何，都可以被检索到，但是前后顺序一致，并紧密排列的关键词会得到最高的关键词匹配分。如果我们想提高单个的关键词展现量，可以采用完全匹配的技巧。

（3）合理利用长尾词，避开强劲竞争对手。每个关键词背后都是因为有特定的人群来搜索，其实每个关键词就对应一个市场，关键词的竞争度有强有弱，具体我们可以在数据魔方è全网关键词查询，输入宝贝的主推关键词，分析查询结果。

我们以淘宝平台上搜索量最大的连衣裙为例，可以看到下图，仔细分析，我们发现根据搜索指数排序，和当前宝贝数排序并不相同，这样我们就可以用搜索指数除以当前宝贝数，得出单个关键词的竞争激烈程度。再参照转化率可以兼顾转化效果来挑选竞争蓝海市场，我们参照直通车点击单价，可以挑选在直通车推广中相对竞争较弱的词。

我们可以看到“早秋新款套装连衣裙”、“森系连衣裙”等关键词竞争热度都小于周围的其他关键词，如果我们宝贝跟这些关键属性比较相符的话，我们可以把它们添加到我们的宝贝标题中来。

图11淘词分析

1、从fruits表中检索所有字段的数据，SQL语句如下：

2、查询fruits表中f_name列所有水果名称，SQL语句如下：

3、从fruits表中获取f_name和f_price两列，SQL语句如下：

4、查询价格为10.2元的水果的名称，SQL语句如下：

5、查找名称为“apple”的水果的价格，SQL语句如下：

6、在fruits表中查询s_id = 101或者102，且f_price大于5，并且f_name=‘apple’的水果价格和名称，SQL语句如下：

explain
select f_name,f_price from fruits
where s_id in (101,102) and f_price>5 and f_name='apple';

(s_id=101 or s_id=102) and f_price>5

describe fruits; explain

7、在suppliers表中查询s_city等于“Tianjin”的供应商s_id，然后在fruits表中查询所有该供应商提供的水果的种类，SQL语句如下：

select s_id,f_name from fruits
where s_id in (select s_id from suppliers where s_city='Tianjin');

exists:

select s_id,f_name from fruits
where exists (select s_id from suppliers where s_city='Tianjin' and
s_id=fruits.s_id
)

连接：
select fruits.s_id,s_name,fruits.f_name,s_city,f1.f_id from fruits
right join suppliers on fruits.s_id=suppliers.s_id
where s_city='Tianjin';

select fruits.s_id,s_name,f_name,s_city from fruits,suppliers,fruits f1
where fruits.s_id=suppliers.s_id and s_city='Tianjin' ;

8、在suppliers表中查询s_city等于“Tianjin”的供应商s_id，然后在fruits表中查询所有非该供应商提供的水果的种类，SQL语句如下：

select s_id,f_name from fruits
where s_id not in(select s_id from suppliers
where s_city='Tianjin');

9、查询fruits表，为f_name取别名fruit_name，f_price取别名fruit_price，为fruits表取别名f1，查询表中f_price < 8的水果的名称，SQL语句如下：

select f_name as fruit_name,f_price fruit_price from
fruits f1
where f_price<8;

10、在fruits表中，查找f_name字段中包含字母’o’或者’t’的记录，SQL语句如下：

select * from fruits
where f_name regexp 'o|t'; '[ot]'

where f_name like '%o%' or f_name like '%t%';

11、在fruits表中字段名为s_id上建立外键连接到suppliers表s_id上，SQL语句为：

alter table fruits add constraint fd_ed foreign key(s_id)
references suppliers(s_id);

12、删除fruits表中的外键,SQL语句为：

alter table fruits drop foreign key fd_ed;

13、查询fruits表中的信息，并将结果首先按照s_id升序排列，在按照水果名称降序排列：

select * from fruits
order by s_id asc,f_name desc;

14、修改fruits表中s_id的字段名，将字段名改为fs_id，其余信息不变，SQL语句为：

alter table fruits change s_id fs_id int(11) not null;

15、输出fruits表中第3行到第7行的数据，SQL语句为：

select * from fruits
limit 2,5;

16、查询fruits表中供应商信息及各供应商提供水果的平均价格，SQL语句为：

select fs_id,avg(f_price) from fruits
group by fs_id;

17、查询fruits表中每个供应商供应的水果大于4.5元的记录，SQL语句为：

select * from fruits
where f_price>4.5
group by fs_id;

18、查询fruits表中供应商的信息，SQL语句如下：

select distinct fs_id from fruits;

19、查询fruits表中有多少个供应商，SQL语句如下：

select count(distinct fs_id) from fruits;

20、查询fruits表中每个供应商所供应水果价格最高的记录；

select fs_id,max(f_price) from fruits
group by fs_id;

select fs_id,f_name,f_price from fruits
where (fs_id,f_price) in (
select fs_id,max(f_price) from fruits
group by fs_id);

21、在fruits表中添加一字段名为num的字段，数据类型为int，默认值为3；（该字段为销售量的意思）

alter table fruits add num int default 3;

22、查询fruits表中供应商总销售额前三名的信息及销售额；

select fs_id,sum(f_price*num) from fruits
group by fs_id
order by sum(f_price*num) desc
limit 3;

23、查询fruits表的信息，并添加‘单价状况’的信息，如果单价低于5元，则标明‘低于5元’，如果单价高于10元，则表明’高于10元’，其他的情况，请表明‘5-10元’，SQL语句为：

select *,case
when f_price<5 then '低于5元'
when f_price between 5 and 10 then '5-10'
else '高于10元'
end as 单价状况
from fruits;

24、怎样查询fruits表中fs_id和f_name信息重复的记录；

select fs_id,f_name ,count(fs_id) from fruits
group by fs_id,f_name
having count(fs_id)>1;

4.总结

主键：primary key 对字段具有非空和唯一的约束; 索引
非空：not null 字段中的值不能为空;
唯一键: unique 字段中的值不能出现重复数据；索引
索引: index | key 加快该字段中数据的查询
外键：foreign key 让一张表中一字段的数据对另一张表中对应字段的数据取值范围进行约束,保证数据一致性；
默认值：default 设定默认值
自增：auto_increment 只能在具有主键或唯一键的字段上添加;
注释：comment

alter table 表名 modify 字段名数据类型　not null|default| auto_increment|comment;

alter table 表名 change 字段名字段名数据类型　not null| default|auto_increment|comment;

alter table 表名 add 字段名数据类型字段属性 first|after 字段名;
alter table 表名 drop 字段名;

范式：
第一范式：数据表的单个字段中变量只能有一个。
例：岗位：销售部经理 —>部门：销售部职位：经理.
第二范式：满足第一范式的条件下，表中字段名的数据由一个字段主键决定；
例：一个订单多个商品：
(订单号，商品号）->商品名称，商品价格
订单表：订单号、客户号、订单时间
中间表：订单号、商品号、销售量
商品表：商品号、商品名称、商品价格
第三范式：满足第二范式的条件下，表中字段与主键是直接联系，而非间接关联：
例子：员工表：员工号、员工名、部门号、部门名..

员工表：员工号、员工名、部门号...
部门表：部门号、部门名称....

从MySQL中导出数据：

mysqldump.exe

1、将某数据库中的所有表导出来：

mysqldump -u 用户名 -p 数据库名> 路径文件名
C:\Users\lx>mysqldump -u root -p company>C:\aa\hongqianjin.sql
Enter password: ******

C:\Users\lx>

注意:cmd 中运行，不能语句结束之后不能用;结尾

不能再数据库中运行命令
没有数据库的信息，只有表的信息

2、如何以库为单位把数据导出来;
mysqldump -u root -p -B company>C:\aa\hongqianji1.sql

3、如何导出所有的库：

mysqldump -u root -p -A>C:\aa\allhongqianjin.sql

4、如何导出特定库中特定表的数据：

mysqldump -u root -p company fruits>C:\aa\fruitshongqianjin.sql

将数据导入到数据库中

mysql.exe

5、如何以库为单位导入sql文件：

cmd:
mysql -u root -p<C:\aa\hongqianji1.sql

数据库mysql内部:
source C:/aa/hongqianji1.sql

6、对于表级的备份文件：

mysql -u root -p company<c:/aa/fruitshongqianjin.sql

source c:/aa/fruitshongqianjin.sql

7、只导出数据结构，不导出数据

mysqldump -u root -p -d company >C:\aa\fruitshongqianjin1.sql

8、MySQL中用into outfile导出fruits的csv或txt文件只导出数据，没有字段名

SELECT * INTO OUTFILE 文件路径 FROM 表名 +Where 条件

在mysql内部使用:
select * into outfile 'c:/aa/fruits2.csv'
fields terminated by ','
optionally enclosed by '"'
lines terminated by '\r\n'
from fruits;

select * into outfile 'C:/bb/fruits3.txt'
fields terminated by ','
optionally enclosed by '"'
lines terminated by '\r\n'
from fruits;

9、导出为txt的文件：

select * into outfile 'c:/users/fruits.txt'
fields terminated by','
optionally enclosed by '"'
lines terminated by '\r\n'
from fruits;
where fs_id between 101 and 109;

10、将csv或txt文件导入到数据库中

Load data infile 文件路径 into table 数据表

load data infile 'C:/bb/fruits3.txt' into table fruits
character set gbk
fields terminated by ','
optionally enclosed by '"'
escaped by '"'
lines terminated by '\r\n';

Load data infile 'c:/aa/fruits2.csv' into table fruits
character set gbk
fields terminated by ','
optionally enclosed by '"'
escaped by '"'
lines terminated by '\r\n';

导出csv和txt文件时，格式要定义好，
fields terminated定义字段终止用什么来区分，我们用的是“，”来区分的，optionally enclosed by’”’表示把数据文件中的字符
串加双引号“ " ”来封闭，escaped by用来规定转义字符。
，lines terminated 表示换行。

1、绝对值ABS:求2，-3.3和-33的绝对值，输入语句如下：

select abs(2),abs(-3.3),abs(-33);

2、求余数MOD(X,Y):对MOD(31,8)，MOD(234, 10)，MOD(45.5,6)进行求余运算，输入语句如下：

select mod(31,8),mod(234,10),mod(45.5,6);

3、返回最小整数CEILING:使用CEILING函数返回最小整数，输入语句如下：

select ceiling(45.34),ceiling(-45.34);
4、使用ROUND(x)函数对操作数进行四舍五入操作，输入语句如下：

select round(45.34),round(45.56,1),round(45.56,0),round(45.56,-1);
5、使用CHAR_LENGTH函数计算字符串字符个数，输入语句如下：

select char_length('football'),length('football');

6、使用CONCAT函数连接字符串，输入语句如下：group_concat()

select concat('my','sql');

select fs_id,group_concat(f_name) from fruits group by fs_id;

7、使用LEFT函数返回字符串中左边的字符，输入语句如下：
select left('football',4) ;

8、使用RIGHT函数返回字符串中右边的字符，输入语句如下：

select right('football',4);

9、使用MID()函数获取指定位置处的子字符串，输入语句如下：

select mid('football',4) f1 ,
mid('football',-4)f2 ,
mid('football',5,3) f3,
mid('football',-5,3) f4;

10、使用LOCATE，POSITION，INSTR函数查找字符串中指定子字符串的开始位置，输入语如下：

select locate('ball','football'),position('ball' in 'football'),instr('football','ball');

11、使用FIND_IN_SET()函数返回子字符串在字符串列表中的位置，输入语句如下：

select find_in_set('ba','ball,my,sql,ba,bae');

12、使用ifnull()函数对null空值进行判断，如果为空，输出第二个值。

select ifnull(null,2),ifnull(4,2);

13、使用日期函数current_date()获取系统当前日期，输入语句如下：

select current_date(),curdate();

14、使用时间函数current_time()获取系统当前时间，输入语句如下：

select current_time(),curtime();
15、使用日期时间函数current_timestamp(),localtime(),now(),sysdate()获取当前系统日期和时间，输入语句如下：

select current_timestamp(),localtime(),now(),sysdate();

16、使用MONTH()函数返回指定日期中的月份，输入语句如下：

select date(now()),year(now()),month(now());

17、使用MONTHNAME()函数返回指定日期中的月份的名称，输入语句如下：

select monthname(now());

18、查询两个日期相差几天datediff（时间1，时间2);
select datediff('2017-02-25','1989-03-26');

19、对时间进行更新可使用date_add(date,interval expr type)和date_sub()函数;

select date_add('2017-02-25',interval 5 day) f1,
date_add('2017-02-25',interval 5 week) f2,
date_add('2017-02-25',interval 5 month) f3;

10.影响无线搜索的十大权重

11.

查看是否是utf8:

show variables like '%cha%';

12.

create table tb_1
(s_id char(8) not null ,
f_name char(20) not null,
f_price float(8,2) not null,
f_no int(4) not null,
primary key(s_id,f_name));

insert into tb_1
values('001','apple',3.5,3),
('001','banana',3.5,2),
('001','orange',4.5,4),
('002','apple',3.3,5),
('002','banana',3.8,0),
('002','orange',4.7,3);

5、按照供应商来查询表中水果的总销售额：

select s_id,f_name,sum(f_price*f_no) as 销售额 from tb_1 group by s_id,f_name with rollup;

存储过程：

查询fruits信息的存储过程；

select * from fruits
where fs_id=102;

delimiter //

create procedure 过程名(参数)
begin
SQL 语句;
end;//

create procedure pro1(in x int)
begin
select * from fruits
where fs_id=x;
end;//

调用存储过程：

call 存储过程名(参数);

call pro1(105);

参数：in｜out|inout 参数名数据类型

create procedure pro2( in pro_in int)
begin
select pro_in;
set pro_in=3;
select pro_in;
end;//

set @x=1;
select @x,@y;
call pro2(@x)// 1 3
select @x// 1

create procedure pro3( out pro_in int)
begin
select pro_in;
set pro_in=3;
select pro_in;
end;//

set @x=1;
select @x,@y;
call pro2(@x)// null 3
select @x// 3

定义变量：
declare 变量名数据类型初始值

create procedure pro4( in x int)
begin
case
when x=1 then select 1;
when x=2 then select 2;
else select 3;
end case;
end;//

if：
create procedure pro5(in x int)
begin
if x=1 then select 1;
elseif x=2 then select 2;
else select 3;
end if;
end;//

while：
1+2+3+....=5050

create procedure pro6(out z int,in y int)
begin
declare i int default 1;
declare s int default 0;
while i<=y do
set s=s+i;
set i=i+1;
end while;
set z=s;
end;//

call pro7(@z，50）

drop procedure pro5;//删除存储过程

查询有哪些存储过程
show procedure status;

13.

索引：
设有N条随机记录，不用索引，平均查找N/2次。
如果使用索引后呢？

btree(二叉树)索引
log以2为底N的对数次

hash(哈希)索引：1次（理论上）

建立数据表books_1,在pubdate上建立普通索引，数据如下

SQL语句如下：

二，在已经存在的表上创建索引；

alter table fruits add index(f_name) ;

create index if_name on fruits(f_name);

三，删除索引

alter table fruits drop index f_name;

drop index if_name on fruits;

视图：
视图是由查询结果形成的一张虚拟表，表格数据的更新，视图显示的数据也会同时更新，但是，视图的增删改同样会修改表中的数据（只有一一对应的时候才可以修改）。

create (algorithm=merge|temptable|undefined) view 视图名
as select 字段名 from 表名
where 条件;

merge|undefined:

create view vfruits as select * from fruits
order by f_price desc;

select * from vfruits
group by fs_id ;

select * from fruits
group by fs_id
order by f_price desc;

temptable:

create algorithm=temptable view vfruits1 as select * from fruits
order by f_price desc;

select * from vfruits1
group by fs_id ;

select * from (select * from fruits
order by f_price desc ) as vfruits
group by fs_id;

2017年2月26日

1.出库的限制

create table tt1
(pro_id char(10) not null primary key ,
pro_name char(10) not null,
num int(10) not null);

在商品表tt1中插入数据：

create table ts1
(order_id char(10) not null primary key ,
pro_id char(10) not null,
num int(10) );

创建触发器：

delimiter //
create trigger触发器名
after|before insert|update|delete on 表名
for each row
begin
SQL操作语句(触发器发生之后的操作);
end;//

1、客户下订单，订单表中插入数据：('1001','1112',4),创建触发器，要求商品表自动减去相应的商品库存：

delimiter //
create trigger tr1
after insert on ts1
for each row
begin
update tt1 set num=num-new.num
where pro_id=new.pro_id;
end;//

show triggers;
drop trigger 触发器名;

2、客户取消订单，取消订单'1001',创建触发器，要求商品表自动添加相应的商品库存：

create trigger tr2
after delete on ts1
for each row
begin
update tt1 set num=num+old.num
where pro_id=old.pro_id;
end;//

delete from ts1 where order_id='1001';//

3、客户修改订单，将订单'1001'的数量改为8，要求商品表的库存自动进行相应的调整.

create trigger tr3
after update on ts1
for each row
begin
update tt1 set num=num+old.num
where pro_id=old.pro_id;
update tt1 set num=new-new.num
where pro_id=new.pro_id;
end;//

update ts1 set num=8 where order_id=1001;
4、客户下订单，限购要求：每种商品最多只能购买5件，创建存储过程，自动调整下单num和商品表相应的库存.

create trigger tr4
before insert on ts1
for each row
begin
if new.num>5 then
set new.num=5;
end if;
update tt1 set num=num-new.num
where pro_id=new.pro_id;
end;//

insert into ts1 value('1003','1112',10);//

3.数据结构表查询

mysql> select * from columns where table_schema='company';
+---------------+--------------+------------+-------------+------------------+---------------
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | COLUMN_DEFAULT
+---------------+--------------+------------+-------------+------------------+---------------
| def | company | dept | d_no | 1 | NULL
| def | company | dept | d_name | 2 | NULL
| def | company | dept | d_location | 3 | NULL
| def | company | fruits | f_id | 1 | NULL
| def | company | fruits | fs_id | 2 | NULL
| def | company | fruits | f_name | 3 | NULL
| def | company | fruits | f_price | 4 | NULL
| def | company | fruits | num | 5 | 3
| def | company | offices | officecode | 1 | NULL
| def | company | offices | city | 2 | NULL
| def | company | offices | ads | 3 | NULL
| def | company | person | id | 1 | NULL
| def | company | person | name | 2 |
| def | company | person | age | 3 | 0
| def | company | person | info | 4 | NULL
| def | company | suppliers | s_id | 1 | NULL
| def | company | suppliers | s_name | 2 | NULL
| def | company | suppliers | s_city | 3 | NULL
| def | company | suppliers | s_zip | 4 | NULL
| def | company | suppliers | s_call | 5 | NULL
| def | company | tb_1 | s_id | 1 | NULL
| def | company | tb_1 | f_name | 2 | NULL
| def | company | tb_1 | f_price | 3 | NULL
| def | company | tb_1 | f_no | 4 | NULL
+---------------+--------------+------------+-------------+------------------+---------------
24 rows in set (0.01 sec)

4.上库存不能大于库存上限

使用mysql数据库，use mysql
使用mysql中的表中的user信息，有那些用户

1、添加用户：
create user '用户名'@'服务器名' identified by '密码'
create user 'hongqianjin1'@'localhost' identified by'123456';

登录只能用cmd

C:\Users\lx>mysql -h localhost -u root -p
Enter password: ******

C:\Users\lx>mysql -h 127.0.0.1 -u root -p
Enter password: ******

C:\Users\lx>mysql -u hongqianjin1 -p
Enter password: ******

查看那些用户
mysql> select * from user;

2、赋予用户权限：

grant 权限 on 数据库.数据表 to '用户名'@'服务器名'(identified by '密码');
grant select on company.* to 'hongqianjin1'@'localhost';

grant select,insert on *.* to 'lisi'@'localhost'identified by '123456';

全部权限:

all privileges

查看权限：
show grants for 'hongqianjin1'@'localhost';

3、取消用户权：
revoke 权限 on 数据库.数据表 from '用户名'@'服务器名';
revoke select on company.* from 'hongqianjin'@'localhost';

4、刷新权限：

flush privileges;

5、删除用户：

drop user '用户名' @ '服务器名';
drop user 'hongqianjin' @'localhost';

6、修改密码：

update user set password=password('密码')where user='用户名'and host='服务器名';

update user set password=password('123')where user='hongqianjin1' and host='localhost';

password('123')中password是加密,去掉则不加密。

修改密码之后，赋予权限的人需要刷新一下权限

1、添加用户：
create user '用户名'@'服务器名' identified by '密码'

create mysql.user 'zhangsan'@'localhost' identified by '12345';

2、赋予用户权限：

grant 权限 on 数据库.数据表 to '用户名'@'服务器名' (identified by '密码'); *.*

grant select on company.* to 'zhangsan'@'localhost';

grant select,insert on *.* to 'lisi'@'localhost' identified by '1234';

全部权限: all privileges

查看权限：

show grants for 'zhangsan'@'localhost';

3、取消用户权：
revoke 权限 on 数据库.数据表 from ’用户名'@'服务器名';

revoke select on company.* from 'zhangsan'@'localhost';

4、刷新权限：

flush privileges;

5、删除用户：
drop user ‘用户名'@'服务器名';

drop user 'zhangsan'@'localhost';

6、修改密码：

update user set password=password('密码')
where user='用户名' and host='服务器名';

update user set password=password('123')
where user='zhangsan';

忘记root的密码：

C:\Users\Administrator>net stop mysql
MySQL 服务正在停止.
MySQL 服务已成功停止。

C:\Users\Administrator>mysqld -n --skip-grant-tables

打开客户端无需密码直接登陆；
mysql> use mysql;
Database changed
mysql> update user set password=password('880218')
-> where user='root';
Query OK, 0 rows affected (0.05 sec)
Rows matched: 1 Changed: 0 Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.09 sec)

数据库：
查看有哪些数据库： show databases;
创建数据库: create database 数据库名;
创建数据库过程: show create database 数据库名;
删除数据库: drop database 数据库名;
选择数据库: use 数据库名;

数据表:
查看有哪些表： show tables;
创建数据表: create table 表名
(字段名数据类型字段属性,
....
);
数据类型: 数值型：整型：int 浮点型： float,double,decimal
字段属性: 主键: primary key 对字段具有非空和唯一的约束
唯一键: unique
非空: not null
自增: auto_increment
默认值:default
注释; comment
外键: foreign key
普通索引:key|index
数据表结构调整：
alter table 表名 add|drop|change|rename|modify 。。。
查看表格结构：
describe|desc 表名;
创建和表1结构相同的表：
create table 表2 like 表1;
把查询结果保存一张表中：
create table 表2 as select * from 表1 where条件;

数据的更新:

插入数据:
insert into 表名(字段名)
values(数据1),(数据2)...;

insert into 表1(字段名)
select 字段名 from 表2
where 条件;

修改数据:
update 表名 set 字段名=数据 where 条件;

删除数据:

delete from 表名
where 条件;

查询数据:
select 字段名 from 表名
where 条件 (不能直接使用sum(),max(),count()等聚合函数）
group by 字段名
having 条件
order by 字段名 asc|desc
limit m,n

连接查询：
select 字段名 from 表1 inner|left|right join 表2
on 表1.字段名=表2.字段名 inner join 表3 on 条件
where 条件;

嵌套查询:
select * from (select * from fruits) as f1
where s_id in( select * from suppliers);

去重：distinct
合并查询结果：union(去重) ,union all(不去重)
存在: exists
where s_id in(102,103);

范围:between ...and
字符串匹配; (not) like 通配符:%, 单个字符:_
正则表达式; (not) regexp ....

存储过程：
delimiter 符号

create procedure 过程名(in|out|inout 参数名　数据类型)
begin
declare 变量名数据类型默认值
SQL语句;
end;

触发器：

create trigger 触发器名
after|before insert|update|delete on table_name
for each row
begin
SQL语句;
end ;