常用sql语句

• 创建语句

<code class="language-sql hljs  has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: "Source Code Pro", monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;"><span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">CREATE</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">table</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">if</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">not</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">exists</span> b(id <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">INTEGER</span>  <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">PRIMARY</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">KEY</span> AUTOINCREMENT,waijian <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">int</span> ,<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">FOREIGN</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">KEY</span> (waijian) <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">REFERENCES</span> a(id))
</span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li></ul>

上面会创建一个 b表（当次表不存在的时候）

1. id 为主键 并且自增长 
2. waijian为外键来自b表的 id列作为参考

• 查询语句

  • 模糊查询:

    • SELECT * FROM aa WHERE name LIKE (‘王_’)

  • 精准查询

    • SELECT * FROM aa WHERE age >= 10 and age <12
    • 分组查询 
      
      • SELECT * FROM aa GROUP BY name HAVING age >= 10

  • 分页查询

    • SELECT * FROM aa LIMIT 1,2

  • 降序查询

    • SELECT * FROM aa ORDER BY age DESC
  • 升序查询(默认) 
    
    • SELECT * FROM aa ORDER BY age ASC

• 删除语句

  • DELETE FROM aa WHERE age = 1

• 修改语句

  • updata UPDATE aa set name =’去死吧’

• 插入语句

  • 列全部插入 
    
    • INSERT into aa VALUES(‘王五’,12)
    • 选择列部插入
    • INSERT into aa (name) VALUES(‘王五’)

---

通配符: 
* [abc]包含abc其中一个 
* [1-10]1到10之间 
* [a-z]a到z其中一个字符 

* %一到多个字符 

* _一个字符