mysql记录操作

1. 使用insert实现数据插入:
   1. insert into class values(1,‘1年1班‘),(2,‘1年2班‘),(3,‘2年1班‘);
   2. insert into stu(name, cid) values(‘a‘,1),(‘b‘,2);
   3. insert into  stu2 (id,name,sex) select id , name, sex from stu where conditions;
2. update更新数据
   1. update mysql.user set passwd=passwd( ‘123‘ ) where user =‘root‘ and host = ‘localhost‘
3. delete删除数据
   1. delete from mysql.user where passwd = ‘‘;
4. 单表查询:
   1. select distinct 字段1, 字段2 from 表明 where 条件 ; group by field ; having 筛选 ; order by field  ; limit 限制
   2. select concat(‘<名字:‘,emp_name, ‘>   <薪资:‘, salary,‘>‘ ) as target from employee;
   3.  select distinct post from employee;
   4. select emp_name, salary*12 as annual_salary from employee;
   5. where 约束条件
      1. select * from employee where post = ‘sale‘
      2.  select * from employee where post = ‘teacher‘ and salary > 10000;
      3. select * from employee where salary between 8000 and 10000;
      4. select * from employee where post_comment is not null; 注意判断是否为空要用is 或者 is not
      5. select * from employee where salary in (3000,3500,4000,17000);
      6.  select * from employee where emp_name like ‘eg%‘;  注意: 通配符 % 代表后边可以有多个字符
      7.  select * from employee where emp_name like ‘eg_‘;    注意: 通配符 _ 只能匹配一个字符
   6. group by 分组
      1. select post from employee group by post;
      2. select post, group_concat(emp_name) from employee group by post;
      3. select post, count(id) from employee group by post;
   7. 聚合函数: 聚合函数聚合的是分组的内容, 如果没有分组,则默认为一组
      1. select count(*) from employee;
      2. select post,count(*) from employee group by post;
      3. max, min, avg, sum,count
   8. having过滤: having与where不一样的地方在于 执行顺序 where > group by > having
      1. select post, group_concat(emp_name), count(id) as counts from employee group by post having counts < 2;
      2.  select post, avg(salary) as avg_salary from employee group by post having avg_salary > 10000;
   9. order by 查询排序 :
      1. select * from employee order by salary desc,age desc; 注意: 默认是升序排列, 加desc将序排列
   10. limit限制查询数目:
       1. select * from employee limit 3;
       2. select * from employee limit 3, 3;
   11. 使用正则表达式查询:
5. 多表查询:
   1. 联表查询:
      1. 　　笛卡尔积: select * from employee, department
      2. 　　内连接:  select * from employee inner join department on employee.dep_id = department.id;
      3. 　　左外连接: select * from employee left join department on employee.dep_id = department.id;
      4. 　　右外连接: select * from employee right join department on employee.dep_id = department.id;
   2. 子查询: 将一个查询语句嵌套在另一个查询语句中,内层的查询结果作为外层的查询条件
      
      1. 关键字 in查询
         1. 　　select name from department where id in (select dep_id from employee group by dep_id having avg(age) > 25);
         2. 　　查询不足一人的部门名字: select name from department where id not in (select dep_id from department group by dep_id having count(id) >= 1);
      2. 带比较运算符的查询: = , != , > , < , >=, <=, <>
         
         1.  select name, age from employee where age > (select avg(age) from employee);
         2. select * from employee t1 inner join (select dep_id, avg(age) as avage from employee group by dep_id) t2 on t1.dep_id = t2.dep_id where age > avage;
      3. 带有exists关键字的子查询:
         1. select * from employee where exists ( select id from employ where id = ‘205‘ )
            1. exists语句不返回查询结果,而是返回一个真假值,值为真,执行外面的sql语句, 值为假, 返回None