暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / MySQL查询

MySQL查询

brevity分享 2021-08-22
269
MySQL的数据类型有：bigint、binary、bit、blob、bool、boolean、char、date、datetime、decimal、double、enum、float、int、json、longblob、longtext、mediumblob、mediumint、mediumtext、numeric、real、set、smallint、text、time、timestamp、tinyint、tinyblob、tinytext、varbinary、varchar、year。
条件查询：select  查询列表  from  表名  where  筛选条件; 。数据库中真实的执行顺序并不是SQL的书写顺序，比如条件查询的执行顺序是：第一步先执行from 表名，找到库中对应的表；第二步执行where 筛选条件，判断条件是否满足；第三步执行select 查询列表，查询出列表字段。

按条件表达式查询(>、<、=、!=、<>、>=、<=)：

    SELECT * FROM employees WHERE salary >= 20000;
    SELECT last_name,department_id FROM employees WHERE department_id <> 30;

    按逻辑运算符查询(&&、||、!或and、or、not)：

      SELECT last_name,first_name,salary,commission_pct FROM employees WHERE salary >= 2000 AND salary <= 3000;
      SELECT * FROM employees WHERE NOT(department_id >= 30 AND department_id <= 50) OR salary >20000;

      模糊查询(like、between and、in、is null、is not null)：

        SELECT last_name,first_name FROM employees WHERE last_name LIKE '%H%';
        SELECT last_name,salary FROM employees WHERE last_name LIKE '___H_a%';
        SELECT last_name FROM employees WHERE last_name LIKE '_\_%';
        SELECT last_name FROM employees WHERE last_name LIKE '_#_%' ESCAPE '#';
        SELECT * FROM employees WHERE employee_id BETWEEN 120 AND 150;
        SELECT last_name,employee_id,job_id FROM employees WHERE job_id IN('AD_VP','MK_MAN','ST_MAN');
        SELECT last_name,first_name,commission_pct FROM employees WHERE commission_pct IS NULL;
        SELECT last_name,first_name,commission_pct FROM employees WHERE commission_pct IS NOT NULL;
        模糊查询中%与_表示通配符，%代表任意多个字符，_代表一个字符。

        MySQL中安全等于可以判断null以及普通类型，示例如下：

          SELECT last_name,first_name,commission_pct FROM employees WHERE commission_pct <=> NULL;
          SELECT last_name,salary FROM employees WHERE salary <=> 3300;
          但是不建议使用<=>，因为可读性比较差。

          排序查询：select 查询列表 from 表名 【where 筛选条件】order by 排序列表(ASC、DESC);  默认为升序ASC，可以省略。

            SELECT * FROM employees ORDER BY salary DESC;
            SELECT * FROM employees WHERE department_id >= 100 ORDER BY hiredate;
            SELECT *,salary * 12 * (1+ IFNULL (commission_pct, 0)) AS 年薪 FROM employees ORDER BY salary * 12 * (1+ IFNULL (commission_pct, 0)) DESC;
            SELECT *,salary * 12 * (1+ IFNULL (commission_pct, 0)) AS 年薪 FROM employees ORDER BY 年薪 DESC;
            SELECT LENGTH(last_name) 长度,last_name,salary FROM employees ORDER BY 长度 DESC;
            SELECT * FROM employees ORDER BY salary ASC,employee_id DESC;

            mysql
            文章转载自brevity分享，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

            评论