mysql操作

mysql操作


命令行操作
show engines;//查询数据库的引擎
show variables like 'table_type';//查询表类型


create table() type InnoDB;//创建表，设置表的存储引擎
create table() engine InnoDB;//创建表，设置表的存储引擎
truncate tbname;//清空表
insert into 表名() values();//插入数据


SELECT [distinct] * FROM 表名 WHERE [GROUP BY , HAVING , ORDER BY , LIMIT count];
SELECT a [AS] A,b [AS] B FROM 表名;


空值不能用=查询
SELECT * FROM 表名 WHERE id=null;//错
SELECT * FROM 表名 WHERE id is null;//对
SELECT * FROM 表名 WHERE id is not null;//对
SELECT * FROM 表名 WHERE id <=> null;//对  <=>和=作用一样，但可用于空值比较


SELECT * FROM products WHERE id BETWEEN 10 AND 20;和SELECT * FROM products WHERE id <= 20 && id >= 10;等价
SELECT * FROM products WHERE id NOT BETWEEN 10 AND 20;和SELECT * FROM products WHERE id > 20 && id < 10; 等价
SELECT * FROM products WHERE id IN(5,10,15,20,25);//查询出所有id等于5,10,15,20,25的记录
UPDATE products SET num=10 WHERE id IN(5,10);


LIKE   包含
NOT LIKE  不包含
_(任意一个字符)和%(0个或多个任意字符) 两个通配符号
SELECT * FROM products WHERE name LIKE "%java%";
SELECT * FROM products WHERE name LIKE "_java_";
SELECT * FROM products WHERE name NOT LIKE "%java%";
SELECT * FROM products WHERE name NOT LIKE "_java_";


正则 REGEXP RLIKE
SELECT * FROM products WHERE name REGEXP '^j';//以j开头的记录
SELECT * FROM products WHERE name REGEXP 's$';//以s结尾的记录


多表查询(连接查询)
SELECT c.name,p.name FROM cats as c,products as p WHERE c.id = p.pid; //两个表多表查询
SELECT a.id aid ,a.name aname,b.id bid ,b.name bname FROM cats a,cats b WHERE b.pid = a.id;//单表多表查询


嵌套查询(子查询)
SELECT *  FROM products WHERE cid IN(SELECT id FROM cats WHERE name like 'j%');


排序 ORDER BY 字段 [ASC正序|DESC倒序]
SELECT * FROM products WHERE id < 10 ORDER BY id [ASC|DESC];


LIMIT count 限制条数
SELECT * FROM products WHERE id < 10 ORDER BY id LIMIT 5;
SELECT * FROM products WHERE id>14 ORDER BY id ASC LIMIT 1;//获取14的下一条记录
SELECT * FROM products WHERE id < 14 ORDER BY id DESC LIMIT 1;//获取14的上一条数据


GROUP BY  分组
函数：count()
sum()
avg()
max()
min()
SELECT cid,count(*),sum(price),avg(price),max(price),min(price) FROM products GROUP BY cid HAVING avg(price) > 50;