0116-06-侯舒舒(作业-sql)

2020 01 16

• 选择数据库
  • use test
• 返回数据库列表
  • show DATABASES
• 获取数据库表的列表
  • show TABLES
• 显示表列
  • show COLUMNS FROM customers
• 检索单个列
  • SELECT prod_name
    FROM products
• 检索多个列
  • SELECT prod_name,prod_id,prod_price
    FROM products
• 检查所有列
  • SELECT *
    FROM products
• 检索值
  • SELECT vend_id
    FROM products
• 检索值 只返回不用的值 DISTINCT关键字
  • SELECT DISTINCT vend_id
    FROM products
• 检索列限制结果LIMIT 5不多于5行
  • SELECT prod_name
    FROM products
    LIMIT 5
• 而LIMIT 5,5指的是返回从行5开始的5行，第一个数为开始位置，第二个数为要检索的行数。
  • SELECT prod_name
    FROM products
    LIMIT 5,5
• 完全限定表名，同时制定表名和列字
  • SELECT products.prod_name
    FROM products
• 排序数据
  • SELECT prod_name
    FROM products
    ORDER BY prod_name
• 按多列排序
  • SELECT prod_id,prod_price,prod_name
    FROM products
    ORDER BY prod_price,prod_name
• 指定排序方向
  • SELECT prod_id,prod_price,prod_name
    FROM products
    ORDER BY prod_price DESC
• 使用组合找到最贵的
  • SELECT prod_price
    FROM products
    ORDER BY prod_price DESC
    LIMIT 1
• 过滤语句WHERE
  • SELECT prod_name,prod_price
    FROM products
    WHERE prod_price=2.50
• 检查单个值
  • SELECT prod_name,prod_price
    FROM products
    WHERE prod_name='fuses'
• 不匹配检查
  • SELECT vend_id,prod_name
    FROM products
    WHERE vend_id <> 1003
• 范围值检查 关键字BETWEEN
  • SELECT prod_name,prod_price
    FROM products
    WHERE prod_price BETWEEN 5 AND 10
• 空值检查
  • SELECT prod_name
    FROM products
    WHERE prod_price IS NULL
• and操作符
  • SELECT prod_id,prod_price,prod_name
    ROM products
    WHERE vend_id=1003 AND prod_price<=10
• or操作符
  • SELECT prod_name,prod_price
    FROM products
    WHERE vend_id = 1002 OR vend_id=1003
• 计算次序，在计算or前 优先计算and
  • SELECT prod_name,prod_price
    FROM products
    WHERE vend_id = 1002 OR vend_id=1003 AND prod_price>=10
• in操作符
  • SELECT prod_name,prod_price
    FROM products
    WHERE vend_id IN (1002,1003)
    ORDER BY prod_name
• not操作符
  • SELECT prod_name,prod_price
    FROM products
    WHERE vend_id NOT IN (1002,1003)
    ORDER BY prod_name
• 百分号通配符 使用通配符 必须使用LIKE
  • SELECT prod_id,prod_name
    FROM products
    WHERE prod_name LIKE 'jet%'
• 下划线通配符
  • SELECT prod_id,prod_name
    FROM products
    WHERE prod_name LIKE '_ ton anvil'
• 基本字符匹配 REGEXP后跟的作为正则表达式
  • SELECT prod_name
    FROM products
    WHERE prod_name REGEXP '1000'
    ORDER BY prod_name
• 进行or匹配
  • SELECT prod_name
    FROM products
    WHERE prod_name REGEXP '1000|2000'
    ORDER BY prod_name
• 匹配几个字符之一
  • SELECT prod_name
    FROM products
    WHERE prod_name REGEXP '[123] Ton'
    ORDER BY prod_name
• 匹配范围
  • SELECT prod_name
    FROM products
    WHERE prod_name REGEXP '[1-5] Ton'
    ORDER BY prod_name
• 匹配特殊字符
  • SELECT vend_name
    FROM vendors
    WHERE vend_name REGEXP '.'
    ORDER BY vend_name
• 匹配多个实列
  • SELECT prod_name
    FROM products
    WHERE prod_name REGEXP '\([0-9] sticks?\)'
    ORDER BY prod_name
• 定位符^
  • SELECT prod_name
    FROM products
    WHERE prod_name REGEXP '^([0-9\.])'
    ORDER BY prod_name