mysql基础

select expr from tal_name
where 条件
group by 分组 having 子句对分组结果进行二次筛选
order by 排序 asc | desc
limit 限制显示条数

in (); 在范围内
between x and y 在两者之间

where price >= 10 and price <=30 or price<=50;

where price not between 30 and 50;

where name like 'da%'

where name like 'da_'

select max(price) from goods;

select type_id, max(price) from goods group by type_id;

select id, max(price) from goods group by id;

select id, count(*) from goods group by id;

select goods_id,goods_name,goods_price - market_price from goods;

select id, name, m_price-shop_price as s from goods having s>100;

select id, name from goods where m_price - shop_price >100;

order by price
order by price desc 降序
order by price asc
order by rand()

select id, name, price from goods order by price desc limit 3,3;

http://dev.mysql.com/downloads/

Microsoft.NET4.0

= any  运算符 = in等效
!=any 或 <> 运算符 与 not in 等效

select goods_type_id, good_type_name from category where exists (select * from goods where goods.goods_type_id = category.goods_type_id);

SELECT n1,n2,n3 FROM tb1 INNER JOIN tb2 ON tb1.n1= tb2.n2; 

SELECT n1,n2,n3 FROM tb1 LEFT JOIN tb2 ON tb1.n1= tb2.n2 

SELECT n1,n2,n3 FROM tb1 RIGHT JOIN tb2 ON tb1.n1= tb2.n2 

父表：p
type_id type_name
子表：s
type_id type_name parent_id

select s.type_id, s.type_name, p.type_name from tdb_goods_types as s left join tdb_goos_types as p on s.parent_id = p.type_id

select p.type_id, p.type_name, s.type_name from tdb_goods_types as p left join tdb_goos_types as s on p.type_id = s.parent_id;

abs() 绝对值
bin() 二进制
avg(col_name);
count(col_name);
min(col_name);
max(col_name);
sum(col_name);
concat();
concat_ws();
strcmp();

not null 非空约束
primary key 主键约束
unique key 唯一约束
default 默认约束
foreign key 外键约束

create table address(
id smallint unsigned primary key,
name varchar(20) not null
);

create table teacher(
t_id tinyint unsigend primary key,
t_name varchar(20) not null,
t_add varchar(20) not null,
foreign key (t_add) references address(name) on update cascade
);

show indexes from address;

创建索引
create index address_name on address(name);

create table teacher(
t_id tinyint unsigned primary key,
t_name varchar(20) not null,
t_add varchar(20) not null,
foreign key (t_add) references address (name) on update cascade
);

show create table teacher;