Mysql基础-（1）

”明月如霜，好风如水，清景无限 “

参考的视频：MOSH 大叔
关于数据库的安装部分，比较推荐5.7，具体可公众号回复：sql.

Windows上msi文件安装会比较舒服。

壹

生成基础的表格（供往后练习）

注意把生成数据库代码中的utf8mb4替换为utf8，utf8mb4_0990_ai_ci替换为utf8_general_ci

也就是运行，SQL Course Materials/create-databases.sql文件。

                     贰  

下面是一些基础语句的介绍。

• USE------------------选择数据库

• SELECT------------------确定是哪些列，（*代表所有列）

• AS------------------常用于取别名，也就是列名

• AND OR NOT------------------逻辑 与或非

• LIKE------------------弱化版正则，建议用正则啊

• REGEXP-----------------能够使用，语法样式：匹配时大都配合WHERE关键字。

eg.

WHERE first_name REGEXP '^[a-f]$m'

正则部分和爬虫一样。记住是在匹配处你想要的字符串就行

• IS NULL------------------判断空

• BETWEEN x AND y------------------( > x AND <y)和大于x小于y效果差不多

• USING------------------当连接的表PK(primary key)名字相同时，建议使用

• WHERE-----------------接条件判断，理解成if语句

• ORDER BY-----------------排序，默认是升序，DESC放在后面

eg.

ORDER BY   customer_id  DESC

• UNION -----------------将筛选出来的行行数据合在一起。（多个select）

• CREATE-----------------新建，表，数据库都行

eg.

CREATE TABLE name AS
SELECT

叁

• 内连接（INNER JOIN）

• 外连接(OUTER JOIN)

• 自然连接(NATURAL JOIN)

不常用，计算机自己判断。（不可控）

• 交叉连接(CROSS JOIN)

（类似于N*N）

插入

INSERT INTO customers 
VALUE  (132,34,546)
## 也可以ustomers后面指明列名

                     肆  

一些代码小案例

###关于JION
USE sql_store;

SELECT 
  orders.order_date,
  orders.order_id,
    customers.first_name,
    shippers.name AS shippers,
    order_statuses.name AS statuses
FROM orders
JOIN customers
  #ON orders.customer_id=customers.customer_id
    USING (customer_id)
LEFT JOIN shippers
  #ON orders.shipper_id=shippers.shipper_id
    USING (shipper_id)
JOIN order_statuses
  ON orders.status=order_statuses.order_status_id
# order by status

### 关于union
USE sql_store;

SELECT 
    customer_id,
        first_name,
        points,
       "倔强青铜" AS type    
#    "Bronze" AS type
FROM customers
WHERE points<1000
UNION
SELECT 
    customer_id,
        first_name,
        points,
       "秩序白银" AS type    
#    "Bronze" AS type
FROM customers
WHERE points>1000 AND points<2000
UNION
SELECT 
    customer_id,
        first_name,
        points,
       "荣耀黄金" AS type    
#    "Bronze" AS type
FROM customers
WHERE points BETWEEN 2000 AND 3000
UNION
SELECT 
    customer_id,
        first_name,
        points,
       "至尊铂金" AS type    
#    "Bronze" AS type
FROM customers
WHERE points >3000

###创建新表CREATE TABLE
###USING 关键字对于PK名一样的内连接
USE sql_invoicing;

CREATE TABLE invoice_archived AS
SELECT
####报错，非8.0+,无法直接用别名
   invoices.invoice_id,
     invoices.number,
     clients.name AS client,
     invoices.invoice_total,
     invoices.payment_total,
     invoices.invoice_date,
     invoices.payment_date,
     invoices.due_date
FROM invoices  
JOIN clients
  USING (client_id)
WHERE payment_date IS NOT NULL
# ORDER BY client

• 结果

对应代码，分别是join,union,create.

END

作者：不爱跑马的影迷不是好程序猿

   喜欢的话请关注点赞👇 👇👇 👇                     

壹句： 月寒日暖，来煎人寿