Mysql 基础整理

Mysql 常用命令

基本命令

mysql -hhost -uusername -ppassword -Pport;

show databases;

use database;

show tables;

desc table;

show columns;

show create table;

select * from table limit 1;

select f1, f2 from table order by f1;

select f1, f2 from table order by f1 desc;

select f1 from table where f1 = 'val1';

select f1 from table where f1 like '%val%';

select f1, count(*) as count from table group by f1;

select concat(f1, f2) as nf from table order by f1;

select distinct f1 from table;

update table set f1 = 'val1' where f2 = 'val2';

insert into table (f1, f2) values (val1, val2);

delete from table where f1 = 'val1';

drop table tablexxx;

alter table tablexxx add col char(10);

alter table tablexxx drop column col;

alter table tablexxx add foreign key (fk) refrences table2 (f1);

alter table tablexxx add index_name (tablexxx_f1); 

select test_a.name from test_b left join test_a on test_b.id = test_a.test_b_id;

select test_a.name from test_b right join test_a on test_b.id = test_a.test_b_id;

select test_a.name from test_b inner join test_a on test_b.id = test_a.test_b_id;

select test_a.name from test_b left outer join test_a on test_b.id = test_a.test_b_id;

业务中常用

#插入多条
insert into table (f1, f2) values (val11, val12), (val21, val22);

#降低插入的优先级low_priority
insert low_priority into table(f1, f2) values(val1, val2);

#从另外一个表查询数据插入
insert into table1 (f1, f2) select (f3, f4) from table2;

#先按f1排序再按f2排序, 默认order by f1 a-z，f2 a-z;
select f1, f2, f3 from table order by f1, f2;

#先把结果集按f1降序排列，再按f2排
select f1, f2, f3 from table order by f1 desc, f2;

#查询出拼表中列的结果
select table1_f1, table1_f2, table2_f2 from table1, table2 where
table1.table1_f1 = table2.table2_f1 order by table1_f1, table1_f2;

#用having对分组进行过滤，where只能对行进行过滤，不能对分组
#进行过滤查询出订单价格总计大于50的订单号和总计列表
select order_id, sum(n*item_price) as order_total from table group 
by order_id having sum(n*item_price) >= 50 order by order_total;

#罗列出生产某个产品的供应商的所有产品
select prod_id, prod_name from products where vendor_id = (select vendor_id from products where prod_name = 'xxx');

#罗列出生产某个产品的供应商的所有产品, 自查询
select prod_id, prod_name from products as p1, products as p2 where p1.vendor_id = p2.vendor_id and p2.prod_name = 'xxx'

#罗列出客户表中所有的客户的订单总量
select cust_name, cust_id, (select count(*) from orders where 
orders.cust_id = customers.cust_id) as num_ord from customers
order by cust_name;

#罗列出客户表中所有的客户的订单总量; 笛卡尔积: 没有联结条件的表的返回的
#结果叫笛卡尔积, LEFT OUTER JOIN 可以返回笛卡尔积。
select customers.cust_name, customers.cust_id, count(orders.order_num) as num_ord from customers left outer join orders on customers.cust_id = orders.cust_id group by customers.cust_id;

####mysql中没有outer join，left join和left outer join一样，都会出
#现主表上的笛卡尔积。如果A表有3条记录，B表有4条记录，以下语句将会打印出4条记录。
select test_a.name from test_b left join test_a on test_b.id = 
test_a.test_b_id;

#join等价于inner join内连接，是返回两个表中都有的符合条件的行。
#left join左连接，是返回左表中所有的行及右表中符合条件的行。
#right join右连接，是返回右表中所有的行及左表中符合条件的行。
#full join全连接，是返回左表中所有的行及右表中所有的行，并按条件连接。
#通常情况下，left join比inner join返回的行数多。
#inner join 执行效率比left join/right join 高。inner join会自动选择数量级小的表作为驱动表。

#罗列出2中查询条件的结果
select vendor_id, prod_id, prod_price from products where 
prod_price <=5 or vendor_id in (1001, 1002);

#罗列出2中查询条件的结果, 使用UNION
select vendor_id, prod_id, prod_price from products where 
prod_price <=5 union
select vendor_id, prod_id, prod_price from products where 
vendor_id in (1001, 1002);

业务中偶尔用到

#显示广泛的服务器状态
show status;

#显示授予用户
show grants;

#显示所有活动进程
show processlist;

#正则查询f1包含a或b开头空格A字符串括号包含4个数字结尾的字符
select f1,f2 from table where f1 regexp '^[a|b] A*\([1-9]{4}\)$' order by f1;

#两个表关联更新
update user_score as us LEFT JOIN t_athlete_score as tas
on us.user_id=tas.pk_id
SET us.birth=tas.f_birth;

#查看mysql索引整理
show index from table;

需要注意的地方

#查询行1起始的2行。mysql第一行offset是0，以下2条语句等价。
select field1,field2 from table limit 1, 2;
select field1,field2 from table limit 2 offset 1;

#返回没有记录的f1：is null 未知，不等于0，在匹配过滤和不匹配
#过滤中都不返回结果
select f1 from table where f1 is null;

#BETWEEN , 包含A和B
select f1, f2 from table between A and B;

#UNION 
(select a xxx) union (select b order by xxx)
union all 表示罗列出重复的结果集。union会自动把重复的结果集去掉。
union 必须在最后一条select语句中使用order by，不允许用一种结果排序
又用另外一种结果排序。

#delete truncate drop
delete 不删除表
truncate 删除表并重新创建表，速度比delete更快
drop table xxx 直接删除表

常用函数

Trim(), Upper(), Length(), Now(), Date(), Time(), Year()，DateDiff()

#打印今年
select year(now());

select avg(f1) as avg_f1 from table;

select count(*) from table where f1='val1';

#统计非null的f1的条数
select count(f1) as total_f1, max(f1) as max_f1, min(f1) as min_f1, 
avg(f1) as avg_f1 from table;

常用数据类型

int, tinynit, bigint, unsigned, char, varchar, text, decimal, double, 
float, boolean
date, datetime, timestamp, smallint, mediumint, blob

创建表

create table if not exists tablexxx ();

创建用户

'username'@'%' 表示可以远程连接。
PRIVILEGES ON *.* 表示所有的库所有的表都有权限。

> mysql> CREATE USER 'foo'@'localhost' IDENTIFIED WITH mysql_native_password BY 'bar';
> mysql> GRANT ALL PRIVILEGES ON database_name.* TO 'foo'@'localhost';

CREATE USER 'drupal'@'%';
GRANT ALL PRIVILEGES ON *.* TO 'drupal'@'%' IDENTIFIED BY 'drupal';

删除用户

DROP USER 'jeffrey'@'localhost';