Mysql入门学习(4)

mysql必知必会学习笔记

1. 查看表结构

    (1) SELECT COLUMNS tableName 
    (2) DESC tableName 是语句(1)的快捷方式

2. 查看创建表的语句

    SHOW CREATE TABLE tableName; 

3. distinct 检索值不同的行

    SELECT DISTINCT venid ,prod_name FROM products;
    注意: 当前检索论文两列，只要这两列数据都相同时，才会去重复

4. 限制检索的行数

    SELECT  *  FROM products LIMIT 4;// 仅取四行
    SELECT  *  FROM products LIMIT 4 OFFSET 3 ;// 从第三行往后去四行（行数不足，有多少返回多少）
    SELECT  *  FROM products LIMIT 3,4 ;// 同上   

5. 对检索的结果进行排序

    // 根据prod_name 字段对检索的结果进行升序/降序排序 。
    SELECT prod_id,prod_name ,prod_price FROM products ORDER BY prod_name ASC/DESC;
    // 默认是升序的，不需要指定升序
    SELECT prod_id,prod_name ,prod_price FROM products ORDER BY prod_name ;
    // 根据多个列进行排序 按照排序声明的先后顺序先根据prod_price进行排序,如果prod_pricr相等，再根据prod_name排序,默认都是升序
    SELECT prod_id,prod_name ,prod_price FROM products ORDER BY prod_price,prod_name ;
    // 指定prod_price 以降序排序，prod_name升序排序
    SELECT prod_id,prod_name ,prod_price FROM products ORDER BY prod_price DESC ,prod_name ;

6. 条件组合查询

    // and 和 or 优先是and 所以必须加括号
    SELECT prod_id,prod_name ,prod_price FROM products WHERE prod_price >=10 and (vend_id=1003 OR vend_id=1002);

7. NOT IN

    SELECT prod_id,prod_name ,prod_price FROM products WHERE prod_price NOT IN (10,19)

8. 通配符过滤

    (1) % 表示在%出现的位置可以有任意个字符
    SELECT prod_id,prod_name ,prod_price FROM products WHERE prod_name LIKE '%anvil%'
    (2) _ 功能同% 但是_只能匹配一个字符
    SELECT prod_id,prod_name ,prod_price FROM products WHERE prod_name LIKE '_ anvil'

9. 正则表达式进行搜索

10. 拼接字段 concat()

    SELECT CONCAT(vend_name,'(',vend_country,')') FROM vendors ORDER BY vend_name

11. 文本处理;

    (1)去掉字符串中的空格
    // 数据右侧的空格删除 
    SELECT RTRIM(vend_name)  FROM vendors ORDER BY vend_name
    // 数据左侧的空格删除
    SELECT LTRIM(vend_name)  FROM vendors ORDER BY vend_name;
    // 数据左右侧的空格删除
    SELECT TRIM(vend_name)  FROM vendors ORDER BY vend_name
    (2) 转换大小写 
     SELECT UPPER(vend_name)  FROM vendors ORDER BY vend_name;
     SELECT LOWER(vend_name)  FROM vendors ORDER BY vend_name;
    (3) 长度
    SELECT LENGTH(vend_name)  FROM vendors ORDER BY vend_name;
    (4) 返回字符串两边的字符
    SELECT LEFT(vend_name,1)  FROM vendors ;
    SELECT RIGHT(vend_name,1)  FROM vendors ;
    (5) 查找子串
    SELECT LOCATE(vend_name,'Anvils ') FROM vendors ;

12. 日期和时间处理函数

    SELECT cust_id,order_num ,DATE(order_date)from orders  ;
    SELECT cust_id,order_num ,TIME(order_date)from orders  ;
    SELECT cust_id,order_num ,YEAR(order_date)from orders  ;
    SELECT cust_id,order_num ,HOUR(order_date)from orders  ;

13. HAVING 和 WHERE 的差别

    WHERE 在数据分组前进行过滤， HAVING 在数据分组后进行过滤。

14. 连接表

    (1) 等值联结
    SELECT vend_name,prod_name,prod_price FROM vendors,products where vendors.vend_id = products.vend_id ;
    (2) 笛卡尔积 (vendors 表内的每一条数据都和 products下的每一条都结合)
    SELECT vend_name,prod_name,prod_price FROM vendors,products where vendors.vend_id = products.vend_id ;
    (3) 内联
    SELECT vend_name,prod_name,prod_price FROM vendors INNER JOIN products ON vendors.vend_id = products.vend_id;
    (4) 自联结 
    SELECT p1.prod_name ,p1.prod_price from products AS p1 ,products AS p2 WHERE p1.vend_id=p2.vend_id AND p2.prod_id='DTNTR';
    (5) 左联 查询左边表内所有的行 在右边表中的情况
    SELECT  customers.cust_id , orders.order_num FROM customers LEFT  OUTER JOIN orders ON customers.cust_id = orders.cust_id;
    (6) 右联 查询右边表内所有行在左边表中的情况
    SELECT  customers.cust_id , orders.order_num FROM customers RIGHT OUTER JOIN orders ON customers.cust_id = orders.cust_id;
    (7) 带聚集的联结
    SELECT customers.cust_name,COUNT(orders.order_num) from customers INNER JOIN orders on customers.cust_id = orders.cust_id GROUP BY orders.cust_id;

15. 组合查询

    // 组合多条select语句
    SELECT  vend_id,prod_id,prod_price FROM products WHERE prod_price<=5 UNION SELECT  vend_id,prod_id,prod_price FROM products WHERE vend_id IN (1005,1002);