MYsql 之单标查询.

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了MYsql 之单标查询.，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含2463字，纯文字阅读大概需要4分钟。

内容图文

http://www.cnblogs.com/wangfengming/articles/8064956.html

3.数据操作

    1.增
        INSERT into t2(name,age) VALUES('小三',11); -- 指定字段插入

        INSERT into t2 VALUES('凤',13,2.5); -- 整表字段插入

        INSERT into t2 VALUES('凤',13,2.5),('凤',13,2.5),('凤',13,2.5),('凤',13,2.5),('凤',13,2.5);
        --插入多条
        insert into t1 SELECT id,name from t2; -- 复制表数据
    2.删
        delete from t2 where age = 1113;
    3.改
        update t2 set name ='祝小凤' , salary = 100 where age =13;
    4.查
        select * from T1;
    
        1.简单查询
            -- 查询所有
            select * from person; 
            --查询指定字段
            select name,age FROM person;
            -- 别名+字段运算
            select p.name,p.salary,p.salary+p.salary*0.1 as 'sum' from person as p
            -- 去重复查询
            select DISTINCT salary,name from person; 
        
        2.条件查询
            -- 逻辑运算符 < > <= >= != <> =
            -- is null ,is not null
            -- and  OR ()

            SELECT * from person where salary >5000 or ( age <=30 AND NAME ='');
        
        3.区间查询
            SELECT * FROM person where salary >=5000 and salary<=10000;
            -- 推荐使用 :
            --ps:前后包含
            
            SELECT * FROM person where salary between 5000 and 10000;
        
        4.集合查询 in not in
            
            SELECT * FROM person where age = 20 or age = 23 or age =30 ;

            SELECT * FROM person where age not in(20,23,30);
            
            
        5.模糊查询 like
            SELECT * FROM person where name LIKE '%月'; -- 以什么结尾

            SELECT * FROM person where name LIKE '月%'; -- 以什么开头

            SELECT * FROM person where name LIKE '%月%'; -- 包含

            SELECT * FROM person where name LIKE '_l%'; -- "_"表示占位符
            
            
        6.排序 
            select * from person ORDER BY salary ASC,age desc;

            -- 强制中文[排序
            select * FROM person ORDER BY CONVERT(name USING GBK) ;
    
        7.聚合函数
            select MAX(salary) from person;
            select MIN(salary) from person;
            select AVG(salary) from person;
            select SUM(salary) from person;
            select COUNT(*) from person;

        8.分组查询   GROUP BY  HAVING
            select count(id),dept_id,avg(salary) from person GROUP BY dept_id HAVING avg(salary) >=5000 ;
             where 与 having区别:
            #执行优先级从高到低：where > group by > having 
            #1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。
            #2. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数
        
        9.分页查询 LIMIT
            SELECT * FROM person LIMIT 2,2

        10.正则表达式
            SELECT * FROM person where name REGEXP '^a';
            
            SELECT * FROM person where name REGEXP 'n$';

            SELECT * FROM person where name REGEXP '.a';

            SELECT * FROM person where name REGEXP '[a,e,n]';

            SELECT * FROM person where name REGEXP '[^alex]';

            SELECT * FROM person where name REGEXP 'a|e';

            SELECT * FROM person where name REGEXP '^w.*i$';


        11. SQL 语句关键字的执行顺序
            
            执行顺序: FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY ->limit　　

内容总结

以上是互联网集市为您收集整理的MYsql 之单标查询.全部内容，希望文章能够帮你解决MYsql 之单标查询.所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端