mysql必知必会

– 检索单个列
– select prod_name from products;
– 检索多个列
– select prod_name,prod_price,prod_id from products;
– 检索所有列
– seleCT * FROM products;
– 检索不同的行(去掉重复值)
– select distinct prod_price from products;
– 限制结果
– select prod_name from products limit 5;
– 从第5行开始选取4列
– select prod_name from products limit 5,4;
– 排序数据(默认升序ASC)
– select prod_name from products order by prod_name;
– 指定排序方向(降序)
– select prod_name,prod_price,prod_id from products order by prod_price desc;
– 找出最高值
– select prod_price from products order by prod_price desc limit 1;
– where 子句(检查单个值)
– select prod_price, prod_name from products where prod_price = ‘2.50’;
– 检查多个值
– select prod_price, prod_name from products where prod_price >10.00;
– 不匹配检查
– select prod_price, prod_name from products where prod_price <> 10.00;
– 中间值检查
– select prod_price, prod_name from products where prod_price between 10 and 30.00;
– 空值检查
– select prod_price, prod_name from products where prod_price is null；
– and 操作符
– select prod_price, prod_name,prod_id from products where vend_id = 1003 and prod_price < 50.00;
– or操作符
– select prod_price, prod_name from products where vend_id = 1002 or vend_id = 1003;
– 计算次序
– select prod_price, prod_name from products where (vend_id = 1002 or vend_id = 1003) AND prod_price >= 10;
– in 操作符
– select prod_price, prod_name from products where vend_id in (1002,1003) order by prod_name;
– not 操作符(not in / not between / not exists)
– select prod_price, prod_name from products where vend_id not in (1002,1003) order by prod_name;
– %通配符
– select prod_price, prod_name from products where prod_name like ‘jet%’;
– _通配符（匹配单个字符）
– select prod_price, prod_name from products where prod_name like ‘% ton anvil’;
– 基本通配符匹配(REGEXP后面的东西做正则表达式处理)
– select prod_price, prod_name from products where prod_name regexp ‘1000’ order by prod_name;
– .是正则表达式特殊字符，表示匹配任意一个字符
– select prod_price, prod_name from products where prod_name regexp ‘.000’ order by prod_name;
– 进行or匹配
– select prod_name,prod_price from products where prod_name regexp ‘1000|2000’ order by prod_name;
– select prod_name,prod_price from products where prod_name regexp ‘[123] Ton’ order by prod_name;
– 已知字段增加信息使用更新数据
– update products set vend_id = 1004 where prod_id = ‘SING’;