MySQL 实用笔记

笔记

1. where被用于前一步生产的表中
   select a,b,c from table where x = 1 where条件的变量x必须在表中存在。

2. having是针对查询的结果集做操作
   select a, b, c as x having x = 1 having后的变量x可以是表中的列，也可以是别名，having是对查询结果集进行再筛选。

3. sum()在某种程度上可以替换count()
select count(*)查询的是绝对的行数，就算某行全为NULL也计算在内；
   select count(列名)查询的是该列不为NULL的所有行的行数；
   InnoDB的表，用count(*)会一行行数，效率很低；
   count(a<22)，a<22返回值要么为1，要么为0，即每次count()都会按一行来数，达不到根据条件数行数的目的；
   如果想计算a<22的行数，可以用sum(a<22)，即满足a<22的返回1，不满足返回 0，进行累加。

4. group by是针对where查询的结果集做操作
   group by有多少个类别，查询结果就有多少行数据；
   group by a,b,c时，则select查询的列，只能在a,b,c里选择，才满足语义；

5. order by是针对最终结果集进行排序
   order by放在where/group by/having后面， 升序:ASC(默认)，降序为DESC；
   多列排序用,分隔排序属性。

6. limit N [, offset M]或limit M, N: 偏移量M后，取出N条记录
   offset偏移量如果不写, 则从0开始取出N条记录。

7. join on where
on是生成临时表时使用的条件，where是对临时表再进行过滤，所以应先join on再where;
   单纯的join不附带on条件时，表达为 笛卡尔积；
   select * from tableA, tableB, tableC等同于select * from tableA join tableB join tableC。

笔记部分内容摘录于：https://www.cnblogs.com/chiangchou/p/mysql-2.html

命令列表

1. 数据库相关

# 新建数据库
create database if not exists test_db;

# 选择数据库
use database test_db;

# 删除数据库
drop database if exists test_db;

2. 表相关

# 创建表
create table if not exists user (
  id int(11) not null primary key auto_increment,
  name varchar(32) not null,
  age int(11) not null, 
  phone varchar(32) default null,
  city varchar(32) default null,
  unique name (name),
  unique idx_name_phone(name, phone)
) engine=InnoDB default charset=utf8;

# 查看索引
show index from user;

# 创建索引
alter table user add key city (city);
alter table user add unique key city (city);

# 删除索引
alter table user drop index city;

# 修改列顺序
alter table user modify city varchar(32) first;
alter table user modify city varchar(32) after phone;

# 查看表结构
desc user；

# 删除表
drop table if exists user;

# 查寻表的创建时间和最近一次更新时间
select concat(table_schema, ".", table_name) as table_name, create_time, update_time from information_schema.tables where table_schema = "test" and table_name = "user";

# 查寻表的列名和列顺序
select concat(table_schema, ".", table_name) as table_name, column_name, ordinal_position as column_index from information_schema.columns where table_schema = "test" and table_name = "user";

3. 记录相关

# 单条插入
insert into user (name, age, city) values ("rain", 22, "A");

# 多条插入
insert into user (name, age, city) values ("xiaoming", 11, "A"), ("xiaobai", 12, "B"), ("xiaoqi", 13, "C");

# 更新记录
update user set phone = "152211111111" where name = "rain";

# 查询
select * from user;
select * from user limit 1, 2;
select * from user limit 2 offset 1;
select * from user where name = "rain";
select * from user where not isnull(phone);
select city, count(*) as count from user group by city having city = "B";

# 删除
delete from user where id = 2;

# 清空表，不会进入事务，没有日志记录，难以恢复。
truncate user;

# 有则更新，无则插入
insert into user (name, age, city) values 
  ("xiaoming", 21, "A"), 
  ("xiaobai", 22, "B"),
  ("xiaochu", 13, "C") 
on duplicate key update 
  name = values(name),
  age = values(age),
  city = values(city);

# 有则删除插入，无则插入
replace into user (name, age, city) values ("xiaoming", 11, "A"), ("xiaobai", 12, "B"), ("xiaoqi", 13, "C");