【Mysql】数据操作1

 

增   INSERT t1(key1,key2,key3) VALUE(value1,value2,value3);

       INSERT t1(key1,key2,key3) SELECT * FROM t2 WHERE 1;

 

改   UPDATE t1 SET key1=new_value WHERE 1;

 

删   DELETE FROM t1 WHERE 1;

 

查  单表查:

执行顺序:  FROM > WHERE>GROUP BY >HAVING>SELECT>DISTINCT>ORDERBY>LIMIT>SELECT

语法顺序: SELECT [DISTINCT]<name> FROM tablename[WHERE]<condition where> [GROUP BY]<name> HAVING <condition having> ORDER BY <name> LIMIT<int>

SELECT 显示选项

SELECT * FROM db1.t1;     

SELECT DISTINCT key1 FROM db1.t1; //去重

SELECT key1,key2 FROM db1.t1;

SELECT key1 AS k1, key2 AS k2 FROM db1.t1  //字段别名

SELECT CONCAT(key1, “:’, key2) AS union_key FROM t1   //print(key1,’:’, key2) 

SELECT CONCAT_WS(key1, key2) AS union_key FROM t1   //’:’.join(key1, key2) 

 

聚合 可结合分组使用,不分组情况下整表为一组

SELECT MAX(key1) FROM db1.t1

SELECT MIN(key1) FROM db1.t1

SELECT SUM(key1) FROM db1.t1

SELECT AVG(key1) FROM db1.t1

SELECT COUNT(key1) FROM db1.t1

 

FROM 指定表

SELECT * FROM db1.t1;   

SELECT * FROM t1;   

 

WHERE 条件, 分组前的过滤

SELECT * FROM db1.t1 WHERE key1>1;   //(>  <  =  !=  >=  <=)

SELECT * FROM db1.t1 WHERE key1>1 AND key2<1;   // AND OR NOT

SELECT * FROM db1.t1 WHERE key1 BETWEEN x AND y;  

SELECT * FROM db1.t1 WHERE key1 IN(x, y, z);

SELECT * FROM db1.t1 WHERE LIKE “key%” ;  //模糊匹配, % 代表任意数量字符, _ 代表一个字符

SELECT * FROM db1.t1 WHERE REGEXP “^key” ;  //正则匹配

 

GROUP BY condition

SELECT post FROM t1 GROUP BY post;  //以员工职位为分组,显示出职位名

SELECT post, GROUP_CONCAT(name) FROM t1 GROUP BY post;  //分组显示各组所有员工名

 

HAVING 分组后的过滤

//员工数量小于2的部门及其员工名

SELECT post,GROUP_CONCAT(name),COUNT(id) FROM t1 GROUP BY post HAVING COUNT(id)<2;

//重名员工的名字及数量

SELECT name,COUNT(id) FROM t1 GROUP BY name HAVING COUNT(id)>1;

 

ORDER BY 排序, 不排序情况下,默认按ID升序排列

SELECT * FROM t1 ORDER BY age asc;  //按年龄升序排列

SELECT * FROM t1 ORDER BY age desc;  //按年龄降序排列

SELECT * FROM t1 ORDER BY age asc, id desc;  //按年龄升序排列,第二优先按id降序排

 

LIMIT    限制条数

SELECT * FROM t1 LIMIT 3  //只显示3条

SELECT name,salary FROM t1 ORDER BY salary desc LIMIT 1  //只显示工资最高的1条记录