MySql 必知必会 总结

read_mysql必知必会

查询select

//查询全部列
select * from tablename;
//查询个别列
select colname, colname from tablename;
//查询不同的值
select DISTINCT id from tablename;
//限制显示的行数
select name from tablename limit 5;
select top 5 name from tablename;
(oracle) select name from tablename where rownum <= 5;
//限制显示的行数，从指定的起点
select name from tablename limit 5 offset 5;
//order by 子句 去一个列或多个列的名字，据此对输出进行排序。
select name from tablename order by name;
// order by 子句对多个列排序（从左至右的顺序）
select name, age from tablename order by name, age;
// order by 按照列位置排序（上面时列名排序）
select name, age from tablename order by 2, 3
//指定排序的方向
select name, age from users order by name, age DESC;
// where 子句 过滤数据
select name, age from tablename where age = 18;
// where
 = 等于
 <> != 不等于
 ! 不小于
 !> 不大于
 between
 is null
select name, age from tablename where age between 10 and 100;
// and 优先级 高于 or
select name, age from tablename where age = 12 AND name = '蒲';
select name, age from users where age = 12 or name = '蒲' and id = 5;
select name, age from users where (age = 12 or name = '蒲') and id = 4;
// in 范围
select name, age from users where id in(1,2,3) order by name;
// not 否定后面的条件
select name,age from users where not age = 12;
// like 通配符操作
select name, age from users where name LIKE '%月%';
select name, age from users where name LIKE '_月%';
% 可以匹配任何东西，但是不能匹配null;
- 可以匹配单个字符
// 算术计算 +-*/
select id, name, age, age+year as newage from users;
select 3*2;
select Trim('  abc');
select Now();
// 部分字符串函数
select num from orders where YEAR(order_date) = 2012;
ABS
COS
EXP
PI()
SQRT();

聚集函数

行数、某行的和、列的最大值、最小值、平均值

AVG() count() max() min() sum();
SELECT avg(age) as avg_age from users;
//包括null 速度慢 type：all
select count(*) from users;  distinct 不能使用（distinct必须使用列名）
//不包括null type：all
select count(name) from users;
//包括null type：indax
select count(1) from users;
1.任何情况下select count(*) from table 最优选择
2.减少select count(*) from table where condition = ? 这样的查询
3.杜绝select count(colunm) from table
select AVG(DISTINCT year) as avg_year from users;
select count(*) as nums_age,min(age) as min_age,max(age) as max_age,avg(age) as avg_agefrom users;

数据分组

select name, count(*) as num_user from users GROUP BY name;
select name, count(*) as num_user from users GROUP BY name HAVING num_user >= 2;
select name, count(*) as num_user from users GROUP BY name HAVING count(*) >= 2;
select name, count(*) as num_user from users where age = 12 GROUP BY name HAVING count(*) >= 2;
select name, count(*) as num_name FROM users GROUP BY name having count(*) > 1 ORDER BY num_name desc;

子查询

select name from users where id in (
    select id from users order by id
) limit 5;

join

select name,users.age,year,money from users, money where users.id = money.id;

内联结

select users.name, money.name from users inner join money on users.id = money.id; 
select U.name, M.money from users as U inner join money as M on U.id = M.id; 

自连接 : select 语句不只一次 引用相同的表

select name, age from users where year = (select year from users where year = 2);
select name, age from users where year IN (select year from users where year = 2);
//上面的例子使用LEFT OUTER JOIN从FROM子句左边的表（Customers表）中选择所有行。为了从右边的表中选择所有行，需要使用RIGHT OUTER JOIN
select users.id, name, money from users left OUTER join money on users.id = money.id; 
select users.id, name, money from users right OUTER join money on users.id = money.id; 

插入数据

insert into tablename(id1,id2) values('2','1');
insert into tablename1(id1,id2) select id1, id2 from tablename2;
//复制整个表
select * into table1 from table2;
create table tablename1 as select * from table2;

更新数据

update table1 set id = 2 where id = 1;
//更新表数据
alter table vendors add vend_phone char(20);

删除数据

delete from table1 where id = 2;
//删除表
drop table tablename;

事务

begin transaction
---
commit transaction

delect from orders;
rollback

外键

alter table users add constraint primary key(id);
//唯一约束
add constraint check(gender like '[mf]');

索引

创建索引：create index inx_name on users(name);