站内链接:

Table Handle

Alter

add a new column at last: alter table table1 add set1 timestamp not null default current_timestamp;

add a new column after spcial column: alter table table1 add str1 varchar(20) not null default ‘’ after set1;

Change type: alter table table1 modify str1 varchar(50) not null default ‘mylove’;

Change name: alter table table1 change oldstr1 newstr1 varchar(50) not null default ‘mylove’;

Delete a column: alter table table1 drop conlumn str1;

Rename Table name: alter table oldt1 rename newt1

Create

Command: create table <表名> (<字段名 1> <类型 1> [,..<字段名 n> <类型 n>]);

Example:

1
2
3
4
5
6
7
create table radcheck(
  id int(4) not null primary key auto_increment,
  name char(20) not null,
  value char(12) not null default '',
  loginenable int(4) not null default 0,
  oldtime timestamp not null default now()
);

Drop

1
2
use database-name;
drop table table-name;

Information

Description: desc table-name;

Create table: show create table table-name;

Record Handle

Insert

Syntax: insert [into]

[column] values (, …)

Example:

1
2
3
4
5
6
7
8
9
# 直接插入新的数据
insert into department_user(corp_id, department_id, user_id) values ('ding001', '1', '10001');
# 使用其他表数据来完成插入操作
insert into department_user(corp_id, department_id, user_id) select corp_id, department_id, user_id from corp
# 批量
select corp_id, department_id, user_id into department_user from corp
# union, 多行插入, 并非合并多列, 数据也并非一定从其他表中获取
insert into department_user(corp_id, department_id, user_id)
    select 'corp001', 'depart001', 'user001' union select 'corp002', 'depart002', 'user002';

Delete

Syntax: delete from

[where ]
Example:

1
2
3
4
# 删除某一条
delete from corp where corp_id='123'
# truncate 整个表格
truncate table <table-name>

Update

Syntax: update

set <column=column-value, …> [where ]

Function: 如果没有 condition, 则默认更新该表的所有记录
Example:

1
2
# 更新某一个条件下的所有记录
update corp set name='郑' where corp_id='123'

Select

Syntax:

1
2
select <column-name, ...> from <table> [where <condition>] [order by <order-column> [ASC or Desc]]
select <column-name1 as name1> from <table> [where <condition>]

Example:

1
2
3
4
5
6
# 所有行和列
select * from corp;
# 条件查询
select * from corp where corp_id='123'
# 模糊查询
select * from corp where name like '%郑%';

Query

limit

limit 和\G 连用: 限定输出并以一种格式化的形式输出所有的信息，用于 mysql 命令格式化输出：select * from ossweb_kvinfo limit 3 \G;

limit: 限定返回的 record 数目个数

offset, 必须和 limit 连用: select username from ossweb_kvinfo limit 3 offset 4;

group by

所谓分组或聚合,即将一个”数据集”划分成若干个”小区域”, 针对”小区域”进行数据处理, 其语法有:

  • group by 列名
  • group by 列名 desc

一旦使用 group by 语法时, 其对查询列是有要求的, 在 mysql 默认开启only_full_group特性之后其基本语法格式如下:

1
2
3
4
5
6
7
8
select "分组中的列(重要)", 聚合函数(列) from tableName group by "分组列"

-- 正确用法
select a, b, c from table_name group by a, b, c;
select a, b, max(c) from table_name group by a, b;

-- 错误用法: 报错only full group
select a, b, c from table_name group by a, b;

实际例子如下:

1
2
3
4
-- 获取每一个dispatch_id的分组数目统计信息
SELECT count(*), dispatch_id FROM DB1.topology group by dispatch_id;
-- 对(dispatch_id, group_id)进行聚合分组
SELECT count(*), dispatch_id, group_id FROM DB1.topology group by dispatch_id, group_id;

另外, 请注意, 对于非 1 对 1 的两个聚合条件, 比如如下数据库输出信息:

1
2
3
4
5
6
7
8
# insert into school values(1, 'bifeng', 'five', 'grade6', 1);
# insert into school values(2, 'zhansan', 'six', 'grade7', 1);
# insert into school values(3, 'lisi', 'seven', 'grade7', 1);

id      name        class   grade       tid
1       bifeng      five    grade6      1
2       zhangsan    six     grade7      1
3       lisi        seven   grade7      1

此时如果执行如下查找命令: SELECT any_value(class), grade, tid FROM school group by grade, tid, 此时会造成 seven 班级信息丢失, 所以在这种聚合条件非 1 对 1 的情况下谨慎使用. 类似的 python sqlalchemy 例子如下:

1
2
3
4
from sqlalchemy import func
values = db.session.query(
    Topology.dispatch_id, func.count('*').label('count')
).group_by(Topology.dispatch_id).all()

having

where 子句, 对查询结果进行分组前, 过滤掉数据, having 子句–筛选满足条件的数据, 在分组之后过滤数据, 可以包含聚合函数. 实例如下:

1
select * from department_user group by corp_id having count(corp_id) > 1;

Distinct

关键字：distinct

命令：select distinct * from table;

Order by

排序, 注意, 必须放在 offset/limit 前面: select * from item order by item_id;

Between

在某一个范围内对数据进行查询过滤, 与

Example: select * from department_user where department_id between 18 and 1000;

Like

Function: 模糊查询

Example: select * from department_user where corp_id like ‘ding%’;

In

Function: 列举集合查询

Example: select * from department_user where corp_id in (‘ding123’, ‘ding1234’);

Join

见笔记 多表查询

参考: