• 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  

                  

                

                  



                  

                  

                



                

                
  • 相关阅读:
    远程访问linux环境安装图形界面问题解决汇总
    如何通过SecureCRT FTP上传下载文件
    X-Frame-Options 响应头
    Openresty 学习笔记(四)lualocks包管理器安装使用
    PHP7 学习笔记(十六)Yaconf 一个高性能的配置管理扩展
    网络工具(一)frp 供内网穿透服务的工具
    博客园自定义样式参考
    Redis学习笔记(二)解析dump.rdb文件工具之redis-rdb-tools
    Docker 从入门到放弃(四)Docker+Jenkins_自动化持续集成
    PHP7 学习笔记(十五)Repository 模式实现业务逻辑和数据访问的分离
  • 原文地址:https://www.cnblogs.com/zhangjian0092/p/11687497.html
Copyright © 2020-2023  润新知