Mysql 基础整理
2018-11-21 本文已影响0人
AnnaJIAN
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';
