中文亚洲精品无码_熟女乱子伦免费_人人超碰人人爱国产_亚洲熟妇女综合网

當(dāng)前位置： 首頁 > news >正文

企業(yè)網(wǎng)站中( )是第一位的?；葜莅俣萻eo哪家好

news 2025/7/10 4:17:35
企業(yè)網(wǎng)站中( )是第一位的。,惠州百度seo哪家好,微商城網(wǎng)站建設(shè)咨詢,珠海市官網(wǎng)網(wǎng)站建設(shè)價(jià)格SQL與關(guān)系數(shù)據(jù)庫基本操作 文章目錄 第一節(jié) SQL概述一、SQL的發(fā)展二、SQL的特點(diǎn)三、SQL的組成 第二節(jié) MySQL預(yù)備知識(shí)一、MySQL使用基礎(chǔ)二、MySQL中的SQL1、常量（1）字符串常量（2）數(shù)值常量（3）十六進(jìn)制常量&…
SQL與關(guān)系數(shù)據(jù)庫基本操作

文章目錄

  • 第一節(jié) SQL概述
    • 一、SQL的發(fā)展
    • 二、SQL的特點(diǎn)
    • 三、SQL的組成
  • 第二節(jié) MySQL預(yù)備知識(shí)
    • 一、MySQL使用基礎(chǔ)
    • 二、MySQL中的SQL
      • 1、常量
        • （1）字符串常量
        • （2）數(shù)值常量
        • （3）十六進(jìn)制常量
        • （4）日期時(shí)間常量
        • （5）位字段值（了解）
        • （6）布爾值
        • （7）NULL值
      • 2、變量
      • 3、運(yùn)算符
      • 4、表達(dá)式
      • 5、內(nèi)置函數(shù)
  • 第三節(jié) 數(shù)據(jù)定義
    • 一、數(shù)據(jù)庫模式定義
      • 1、創(chuàng)建數(shù)據(jù)庫
      • 2、選擇數(shù)據(jù)庫
      • 3、修改數(shù)據(jù)庫
      • 4、刪除數(shù)據(jù)庫
      • 5、查看數(shù)據(jù)庫
    • 二、表定義
      • 1、創(chuàng)建表
      • 2、添加字段
      • 3、修改字段
      • 4、刪除字段
      • 5、重命名表
      • 6、刪除表
      • 7、查看表
      • 8、查看表結(jié)構(gòu)
    • 三、索引定義
      • 1、索引的創(chuàng)建
      • 2、索引的查看
      • 3、索引的刪除
  • 第四節(jié) 數(shù)據(jù)更新
    • 一、插入數(shù)據(jù)
    • 二、刪除數(shù)據(jù)
    • 三、修改數(shù)據(jù)
  • 第五節(jié) 數(shù)據(jù)查詢
    • 一、SELECT語句
    • 二、列的選擇與指定
      • 1、選擇指定的列
      • 2、定義并使用列的別名
      • 3、替換查詢結(jié)果集中的數(shù)據(jù)
      • 4、計(jì)算列值
      • 5、聚合函數(shù)
    • 三、FROM子句與多表連接查詢
      • 1、交叉連接
      • 2、內(nèi)連接
      • 3、外連接
        • （1）左連接
        • （2）右連接
    • 四、WHERE子句與條件查詢
    • 五、GROUP BY子句與分組數(shù)據(jù)
    • 六、HAVING子句
    • 七、ORDER BY子句
    • 八、LIMIT子句
  • 第六節(jié) 視圖
    • 一、創(chuàng)建視圖
    • 二、刪除視圖
    • 三、修改視圖定義
    • 四、查看視圖定義
    • 五、更新視圖數(shù)據(jù)
    • 六、查詢視圖數(shù)據(jù)
  • 附上`mydata.sql`

第一節(jié) SQL概述

一、SQL的發(fā)展

? 結(jié)構(gòu)化查詢語言（Structured Query Language，簡稱SQL）是一種介于關(guān)系代數(shù)和關(guān)系演算之間的語言。由于具有語言簡潔、方便實(shí)用、功能齊全等優(yōu)點(diǎn)，很快得到推廣和應(yīng)用。隨著關(guān)系數(shù)據(jù)庫的流行，SQL語言最終發(fā)展成為關(guān)系數(shù)據(jù)庫的標(biāo)準(zhǔn)語言和數(shù)據(jù)庫領(lǐng)域中一個(gè)主流語言。SQL已經(jīng)被ANSI（美國國家標(biāo)準(zhǔn)化組織）確定為數(shù)據(jù)庫系統(tǒng)的工業(yè)標(biāo)準(zhǔn)，它是數(shù)據(jù)庫系統(tǒng)的通用語言。利用SQL，用戶可以用幾乎相同的語句在不同的數(shù)據(jù)庫系統(tǒng)上執(zhí)行同樣的操作。MySQL利用SQL對(duì)數(shù)據(jù)庫進(jìn)行操作，此外SQL語言不區(qū)分大小寫。

二、SQL的特點(diǎn)

? 1、SQL不是某個(gè)特定數(shù)據(jù)庫供應(yīng)商專有的語言。幾乎所有重要的關(guān)系數(shù)據(jù)庫管理系統(tǒng)都支持SQL，所以掌握SQL可以幫助用戶與幾乎所有的關(guān)系數(shù)據(jù)庫進(jìn)行交互。
? 2、SQL簡單易學(xué)。它的語句全都是由具有很強(qiáng)描述性的英語單詞所組成，而且這些單詞的數(shù)目不多。

? 3、SQL盡管看上去很簡單，但它實(shí)際上是一種強(qiáng)有力的語言，靈活使用其語言元素，可以進(jìn)行非常復(fù)雜和高級(jí)的數(shù)據(jù)庫操作。

三、SQL的組成

? 1、數(shù)據(jù)定義語言（Data Definition Language，DDL）

? 2、數(shù)據(jù)操縱語言（Data Manipulation Language，DML）

? 3、數(shù)據(jù)控制語言（Data Control Language，DCL）

? 4、嵌入式和動(dòng)態(tài)SQL規(guī)則

? 5、SQL調(diào)用和會(huì)話規(guī)則

第二節(jié) MySQL預(yù)備知識(shí)

一、MySQL使用基礎(chǔ)

? 1、LAMP（Linux+Apache+ MySQL+PHP/Perl/Python），即使用Linux作為操作系統(tǒng)，Apache作為Web服務(wù)器，MySQL作為數(shù)據(jù)庫管理系統(tǒng)，PHP、Perl或Python語言作為服務(wù)器端腳本解釋器。

? 2、WAMP（Windows+Apache+MySQL+PHP/Perl/Python），即使用Windows系統(tǒng)，Apache作為Web服務(wù)器，MySQL作為數(shù)據(jù)庫管理系統(tǒng)，PHP、Perl或Python語言作為服務(wù)器端腳本解釋器。

二、MySQL中的SQL

在這里插入圖片描述

圖4.1 MySQL中的SQL對(duì)關(guān)系數(shù)據(jù)庫模式的支持

1、常量

（1）字符串常量

? 字符串是指用單引號(hào)或雙引號(hào)括起來的字符序列，分為ASCII字符串常量（一個(gè)字節(jié)）和Unicode字符串常量（兩個(gè)字節(jié)）。

? ASCII例如：‘hello’, ‘How are you!’

? Unicode字符串常量與ASCII字符串常量相似，但它前面有一個(gè)N標(biāo)志，例如：N’hello’。

mysql> select 'hello';
+-------+
| hello |
+-------+
| hello |
+-------+
1 row in set (0.02 sec)mysql> select "hello";
+-------+
| hello |
+-------+
| hello |
+-------+
1 row in set (0.03 sec)mysql> select N'hello';
+-------+
| hello |
+-------+
| hello |
+-------+
1 row in set (0.04 sec)mysql> select N"hello";
1054 - Unknown column 'N' in 'field list'     -- 使用雙引號(hào)會(huì)報(bào)錯(cuò)
mysql>
（2）數(shù)值常量

? 數(shù)值常量可以分為整數(shù)常量和浮點(diǎn)數(shù)常量。

? 整數(shù)常量即不帶小數(shù)點(diǎn)的十進(jìn)制數(shù)，例如：1894，2，+145345234，-2147483648。

? 浮點(diǎn)數(shù)常量是使用小數(shù)點(diǎn)的數(shù)值常量，例如：5.26，-1.39，101.5E5，0.5E-2。

mysql> select 12345.6789;
+------------+
| 12345.6789 |
+------------+
| 12345.6789 |
+------------+
1 row in set (0.04 sec)mysql> select 12345;
+-------+
| 12345 |
+-------+
| 12345 |
+-------+
1 row in set (0.04 sec)mysql> select 12345e-3;
+----------+
| 12345e-3 |
+----------+
|   12.345 |
+----------+
1 row in set (0.04 sec)mysql>
（3）十六進(jìn)制常量

? MySQL支持十六進(jìn)制值。一個(gè)十六進(jìn)制值通常指定為一個(gè)字符串常量，每對(duì)十六進(jìn)制數(shù)字被轉(zhuǎn)換為一個(gè)字符，其最前面有一個(gè)大寫字母“X”或小寫字母“x”。在引號(hào)中只可以使用數(shù)字“0”到“9”及字母“a”到“f”或“A”到“F”。例如：X’41’表示大寫字母A。x’4D7953514C’表示字符串MySQL。

mysql> select X'41';
+-------+
| X'41' |
+-------+
| A     |
+-------+
1 row in set (0.02 sec)mysql> select X'4D7953514C';
+---------------+
| X'4D7953514C' |
+---------------+
| MySQL         |
+---------------+
1 row in set (0.03 sec)mysql>

? 十六進(jìn)制數(shù)值不區(qū)分大小寫，其前綴“X”或“x”可以被“0x”取代而且不用引號(hào)。及X’41’可以替換為0x41。

41=4*16^1+1*16^0=65

? 注意：“0x”中x一定要小寫。

? 十六進(jìn)制值的默認(rèn)類型是字符串。如果向要確保該值作為數(shù)字處理，可以使用CAST（…AS UNSIGNED）。

（4）日期時(shí)間常量

? 日期時(shí)間常量：用單引號(hào)將表示日期時(shí)間的字符串括起來構(gòu)成。

? 日期型常量包括年、月、日，數(shù)據(jù)類型為DATE，表示為"1999-06-17"這樣的值。

? 時(shí)間型常量包括小時(shí)數(shù)、分鐘數(shù)、秒數(shù)及微秒數(shù)，數(shù)據(jù)類型為TIME，表示為“12:30:43.00013”這樣的值。

mysql> select '2023-10-04';
+------------+
| 2023-10-04 |
+------------+
| 2023-10-04 |
+------------+
1 row in set (0.02 sec)mysql> select '2023-10-04 11:08:30';
+---------------------+
| 2023-10-04 11:08:30 |
+---------------------+
| 2023-10-04 11:08:30 |
+---------------------+
1 row in set (0.03 sec)mysql>
（5）位字段值（了解）

? 可以使用b’value’符號(hào)寫位字段值。value是一個(gè)用0和1寫成的二進(jìn)制值。直接顯示b’value’的值可能是一系列特殊的符號(hào)。

? 例如：b’0’顯示位空白，b’1’顯示位一個(gè)圖案。

mysql> select b'1';
+------+
| b'1' |
+------+
|     |
+------+
1 row in set (0.04 sec)mysql> select b'111';
+--------+
| b'111' |
+--------+
|       |
+--------+
1 row in set (0.04 sec)mysql>
（6）布爾值

? 布爾值只包含兩個(gè)可能的值：TRUE和FALSE。FALSE的數(shù)字值為“0”，TRUE的數(shù)字值為“1”。

mysql> select true;
+------+
| TRUE |
+------+
|    1 |
+------+
1 row in set (0.03 sec)mysql> select 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.04 sec)mysql> select false;
+-------+
| FALSE |
+-------+
|     0 |
+-------+
1 row in set (0.03 sec)mysql>
（7）NULL值

? NULL值可適用于各種列類型，它通常用來表示“沒有值”、“無數(shù)據(jù)”等意義，并且不同于數(shù)字類型的“0”或字符串類型的空字符串。

mysql> select NULL;
+------+
| NULL |
+------+
| NULL |
+------+
1 row in set (0.02 sec)mysql>

2、變量

? 用戶可以在表達(dá)式中使用自己定義的變量，這樣的變量叫作用戶變量。

? 用戶可以先在用戶變量中保存值，然后在以后引用它這樣可以將值從一個(gè)語句傳遞到另一個(gè)語句，在使用用戶變量前必須定義和初始化。如果使用沒有初始化的變量，它的值為NULL。

? 用戶變量前加**@**用來區(qū)分列名。

? 必須在系統(tǒng)變量前加兩個(gè)@。

mysql> set @a=1;
Query OK, 0 rows affected (0.00 sec)mysql> select @1;
+------+
| @1   |
+------+
| NULL |
+------+
1 row in set (0.03 sec)mysql> select @a+1;
+------+
| @a+1 |
+------+
|    2 |
+------+
1 row in set (0.04 sec)mysql> set @b;
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
mysql> select @b;
+------+
| @b   |
+------+
| NULL |
+------+
1 row in set (0.05 sec)mysql>

3、運(yùn)算符

? （1）算術(shù)運(yùn)算符在兩個(gè)表達(dá)式上執(zhí)行數(shù)學(xué)運(yùn)算，這兩個(gè)表達(dá)式可以是任何數(shù)字?jǐn)?shù)據(jù)類型。算術(shù)運(yùn)算符有：+（加）、-（減）、*（乘）、/（除）和%（求模）5種運(yùn)算。

mysql> select @a;
+----+
| @a |
+----+
|  1 |
+----+
1 row in set (0.01 sec)mysql> select @a+5;
+------+
| @a+5 |
+------+
|    6 |
+------+
1 row in set (0.02 sec)-- 加減乘除都一樣，重點(diǎn)來看一下求模mysql> select 7%3;
+-----+
| 7%3 |
+-----+
|   1 |
+-----+
1 row in set (0.04 sec)mysql>

? （2）位運(yùn)算符有：&（位與）、|（位或）、^（位異或）、~（位取反）、>>（位右移）、<<（位左移）。

mysql> select 5&1;
+-----+
| 5&1 |
+-----+
|   1 |
+-----+
1 row in set (0.02 sec)mysql> select 5|1;
+-----+
| 5|1 |
+-----+
|   5 |
+-----+
1 row in set (0.03 sec)mysql> select ~1;
+----------------------+
| ~1                   |
+----------------------+
| 18446744073709551614 |
+----------------------+
1 row in set (0.03 sec)mysql> select 5^1;
+-----+
| 5^1 |
+-----+
|   4 |
+-----+
1 row in set (0.05 sec)mysql> select 5>>1;
+------+
| 5>>1 |
+------+
|    2 |
+------+
1 row in set (0.06 sec)mysql> select 5<<1;
+------+
| 5<<1 |
+------+
|   10 |
+------+
1 row in set (0.07 sec)mysql>

? （3）比較運(yùn)算符（又稱關(guān)系運(yùn)算符），用于比較兩個(gè)表達(dá)式的值，其運(yùn)算結(jié)果為邏輯值，可以為三種之一1（真）、0（假）及NULL（不能確定）

運(yùn)算符含義運(yùn)算符含義
=等于<=小于等于
>大于<>、!=不等于
<小于<=>相等或都等于空
>=大于等于
mysql> select 5>1;
+-----+
| 5>1 |
+-----+
|   1 |
+-----+
1 row in set (0.02 sec)mysql> select 5<1;
+-----+
| 5<1 |
+-----+
|   0 |
+-----+
1 row in set (0.03 sec)mysql> select 5=1;
+-----+
| 5=1 |
+-----+
|   0 |
+-----+
1 row in set (0.04 sec)mysql> select 5!=1;
+------+
| 5!=1 |
+------+
|    1 |
+------+
1 row in set (0.03 sec)mysql> select 5>=1;
+------+
| 5>=1 |
+------+
|    1 |
+------+
1 row in set (0.04 sec)mysql>

? （4）邏輯運(yùn)算符

運(yùn)算符運(yùn)算規(guī)則運(yùn)算符運(yùn)算規(guī)則
NOT或!邏輯非OR或||邏輯或
AND或&&邏輯與XOR邏輯異或
mysql> select not (5>=1);
+------------+
| not (5>=1) |
+------------+
|          0 |
+------------+
1 row in set (0.02 sec)mysql> select 5>=1 && 6<=8;
+--------------+
| 5>=1 && 6<=8 |
+--------------+
|            1 |
+--------------+
1 row in set (0.03 sec)mysql> select 5>=1 and 6<=8;
+---------------+
| 5>=1 and 6<=8 |
+---------------+
|             1 |
+---------------+
1 row in set (0.04 sec)mysql> select 5>=1 and 6>8;
+--------------+
| 5>=1 and 6>8 |
+--------------+
|            0 |
+--------------+
1 row in set (0.06 sec)mysql> select 5>=1 or 6>8;
+-------------+
| 5>=1 or 6>8 |
+-------------+
|           1 |
+-------------+
1 row in set (0.06 sec)mysql> select 5>=1 xor 6>8;
+--------------+
| 5>=1 xor 6>8 |
+--------------+
|            1 |
+--------------+
1 row in set (0.06 sec)mysql>

4、表達(dá)式

? 表達(dá)式就是常量、變量、列名、復(fù)雜計(jì)算、運(yùn)算符和函數(shù)的組合。一個(gè)表達(dá)式通?？梢缘玫揭粋€(gè)值。與常量和變量一樣，表達(dá)式的值也具有某種數(shù)據(jù)類型，可能的數(shù)據(jù)類型有字符類型、數(shù)值類型、日期時(shí)間類型。這樣，根據(jù)表達(dá)式的值的類型，表達(dá)式可分為字符型表達(dá)式、數(shù)值型表達(dá)式和日期表達(dá)式。

mysql> select 5+5.1;
+-------+
| 5+5.1 |
+-------+
| 10.1  |
+-------+
1 row in set (0.02 sec)mysql> select 5+'A';
+-------+
| 5+'A' |
+-------+
|     5 |
+-------+
1 row in set (0.02 sec)mysql> select 'b'+'A';
+---------+
| 'b'+'A' |
+---------+
|       0 |
+---------+
1 row in set (0.03 sec)mysql> select '2019-09-08'+1;
+----------------+
| '2019-09-08'+1 |
+----------------+
|           2020 |
+----------------+
1 row in set (0.06 sec)mysql> select '2019-09-08 12:00:00'+1;
+-------------------------+
| '2019-09-08 12:00:00'+1 |
+-------------------------+
|                    2020 |
+-------------------------+
1 row in set (0.04 sec)mysql>

5、內(nèi)置函數(shù)

例如：

函數(shù)類型示例
數(shù)學(xué)函數(shù)ABS()、SORT()
聚合函數(shù)COUNT()
字符串函數(shù)ASCI()、CHAR()
日期和時(shí)間函數(shù)NOW()、YEAR()
加密函數(shù)ENCODE()、ENCRYPT()
控制流程函數(shù)IF()、IFNULL()
格式化函數(shù)FORMAT()
類型轉(zhuǎn)換函數(shù)CAST()
系統(tǒng)信息函數(shù)USER()、VERSION()
mysql> select abs(-10);
+----------+
| abs(-10) |
+----------+
|       10 |
+----------+
1 row in set (0.02 sec)mysql> select sqrt(16);
+----------+
| sqrt(16) |
+----------+
|        4 |
+----------+
1 row in set (0.03 sec)mysql> select ascii('A');
+------------+
| ascii('A') |
+------------+
|         65 |
+------------+
1 row in set (0.03 sec)mysql> select char(65);
+----------+
| char(65) |
+----------+
| A        |
+----------+
1 row in set (0.05 sec)mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2023-10-04 11:41:17 |
+---------------------+
1 row in set (0.02 sec)mysql> select year(now());
+-------------+
| year(now()) |
+-------------+
|        2023 |
+-------------+
1 row in set (0.02 sec)mysql> select month(now());
+--------------+
| month(now()) |
+--------------+
|           10 |
+--------------+
1 row in set (0.03 sec)mysql> select day(now());
+------------+
| day(now()) |
+------------+
|          4 |
+------------+
1 row in set (0.04 sec)mysql> select cast(X'41' as unsigned);
+-------------------------+
| cast(X'41' as unsigned) |
+-------------------------+
|                      65 |
+-------------------------+
1 row in set (0.02 sec)mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.03 sec)mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.26    |
+-----------+
1 row in set (0.04 sec)mysql>

第三節(jié) 數(shù)據(jù)定義

一、數(shù)據(jù)庫模式定義

1、創(chuàng)建數(shù)據(jù)庫

? 語法格式：

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_nme [DEFAULT] CHARACTER SET charset_name | [DEFAULT] COLLATE collation_name

說明：語句中“[ ]”內(nèi)為可選項(xiàng)。

db_name：數(shù)據(jù)庫名

IF NO EXISTS：在建數(shù)據(jù)庫前進(jìn)行判斷，只有該數(shù)據(jù)庫目前尚不存在時(shí)才執(zhí)行CREATE DATABASE操作。

DEFAULT：指定默認(rèn)值

CHARACTER SET：指定數(shù)據(jù)庫字符集（Charset）

charset_name：字符集名稱。

COLLATE：指定字符集的校對(duì)規(guī)則，collation_name為校對(duì)規(guī)則名。

mysql> create database mydata;
Query OK, 1 row affected (0.01 sec)mysql> create database mydata;
1007 - Can't create database 'mydata'; database exists
mysql> create database IF NOT EXISTS mydata;
Query OK, 1 row affected (0.00 sec)mysql>

2、選擇數(shù)據(jù)庫

USE db_name;

mysql> use mydata;
Database changed
mysql>

3、修改數(shù)據(jù)庫

? 語法格式：

ALTER {DATABASE | SCHEMA} [db_name] alter_specification [,alter_specification] ...

其中alter_specification：[DEFAULT] CHARACTER SET charset_name | [DEFAULT] COLLATE collation_name

4、刪除數(shù)據(jù)庫

? 語法格式：

DROP DATABASE [IF EXISTS] db_name

db_name是要?jiǎng)h除的數(shù)據(jù)庫名?？梢允褂肐F EXISTS子句以避免刪除不存在的數(shù)據(jù)庫時(shí)出現(xiàn)MySQL錯(cuò)誤信息。

mysql> drop database mydata;
Query OK, 0 rows affected (0.01 sec)mysql> drop database mydata;
1049 - Unknown database 'mydata'
mysql> drop database IF EXISTS mydata;
Query OK, 0 rows affected (0.00 sec)mysql>

5、查看數(shù)據(jù)庫

SHOW [DATABASE|SCHEMAS]

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.02 sec)mysql>

二、表定義
數(shù)值類型
整數(shù)類型
浮點(diǎn)類型
INT
FLOAT DOUBLE DECIMAL
日期和時(shí)間類型
日期類型
日期時(shí)間類型
時(shí)間戳類型
DATE YYYY-MM-DD YEAR
DATETIME TIME
TIMESTAMP
字符串類型
固定長度類型
可變長度類型
文本類型
CHAR 最大長度255
VARCHAR 最大長度65535
TEXT

1、創(chuàng)建表

CREATE TABLE [IF NOT EXISTS] tbl_name(字段名1 數(shù)據(jù)類型[列級(jí)完整性約束條件][默認(rèn)值][，字段名2 數(shù)據(jù)類型[列級(jí)完整性約束條件][默認(rèn)值]][，......][表級(jí)完整性約束])[ENGINE=引擎類型];

mysql> create database mydata;
Query OK, 1 row affected (0.00 sec)mysql> use mydata;
Database changed
mysql> create table student(-> Sno char(11) NOT NULL,-> Sname char(10) NOT NULL,-> Sage int-> );
Query OK, 0 rows affected (0.02 sec)-- 或者使用create table student(Sno char(11) NOT NULL,Sname char(10) NOT NULL,Sage int);進(jìn)行創(chuàng)建mysql>

2、添加字段

ALTER TABLE tb_name ADD [COLUMN] 新字段名 數(shù)據(jù)類型 [約束條件] [FIRST | AFTER 已有字段名]

mysql> alter table student add column Sbirthday datetime;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql>

3、修改字段

ALTER TABLE tb_name CHANGE [COLUMN] 原字段 新字段名 數(shù)據(jù)類型 [約束條件]ALTER TABLE tb_name ALTER [COLUMN] 字段名 {SET|DROP} DEFUALT;ALTER TABLE tb_name MODIFY [COLUMN] 字段名 數(shù)據(jù)類型 [約束條件] [FIRST | AFTER 已有字段名]

mysql> alter table student change column Sname Sname char(200);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql>

4、刪除字段

ALTER TABLE tb_name DROP [COLUMN] 字段名;

mysql> alter table student drop column Sname;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql>

5、重命名表

ALTER TABLE 原表名 RENAME [TO] 新表名 RENAME TABLE 原表名1 TO 新表名1 [，原表名2 TO 新表名2]......;

mysql> show tables;
+------------------+
| Tables_in_mydata |
+------------------+
| student          |
| teacher          |
+------------------+
2 rows in set (0.03 sec)mysql> alter table teacher rename to my_teacher;
Query OK, 0 rows affected (0.00 sec)mysql> show tables;
+------------------+
| Tables_in_mydata |
+------------------+
| my_teacher       |
| student          |
+------------------+
2 rows in set (0.05 sec)-- 修改方法2mysql> rename table my_teacher to teacher;
Query OK, 0 rows affected (0.00 sec)mysql> show tables;
+------------------+
| Tables_in_mydata |
+------------------+
| student          |
| teacher          |
+------------------+
2 rows in set (0.04 sec)mysql>

6、刪除表

DROP TABLE [IF EXISTS] 表1 [，表2]......;

mysql> drop table teacher;
Query OK, 0 rows affected (0.00 sec)mysql>

7、查看表

SHOW TABLES[{FROM|IN} db_name];

mysql> show tables;
+------------------+
| Tables_in_mydata |
+------------------+
| student          |
+------------------+
1 row in set (0.07 sec)mysql> show tables in mysql;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event                     |
| func                      |
| general_log               |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| server_cost               |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
31 rows in set (0.10 sec)mysql>

8、查看表結(jié)構(gòu)

SHOW COLUMNS {FROM|IN} tb_name [{FROM|IN} db_name];

mysql> show columns in student;
+-----------+----------+------+-----+---------+-------+
| Field     | Type     | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| Sno       | char(11) | NO   |     | NULL    |       |
| Sage      | int(11)  | YES  |     | NULL    |       |
| Sbirthday | datetime | YES  |     | NULL    |       |
+-----------+----------+------+-----+---------+-------+
3 rows in set (0.04 sec)mysql> desc student;
+-----------+----------+------+-----+---------+-------+
| Field     | Type     | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| Sno       | char(11) | NO   |     | NULL    |       |
| Sage      | int(11)  | YES  |     | NULL    |       |
| Sbirthday | datetime | YES  |     | NULL    |       |
+-----------+----------+------+-----+---------+-------+
3 rows in set (0.05 sec)mysql> show columns in db in mysql;
+-----------------------+---------------+------+-----+---------+-------+
| Field                 | Type          | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host                  | char(60)      | NO   | PRI |         |       |
| Db                    | char(64)      | NO   | PRI |         |       |
| User                  | char(32)      | NO   | PRI |         |       |
| Select_priv           | enum('N','Y') | NO   |     | N       |       |
| Insert_priv           | enum('N','Y') | NO   |     | N       |       |
| Update_priv           | enum('N','Y') | NO   |     | N       |       |
| Delete_priv           | enum('N','Y') | NO   |     | N       |       |
| Create_priv           | enum('N','Y') | NO   |     | N       |       |
| Drop_priv             | enum('N','Y') | NO   |     | N       |       |
| Grant_priv            | enum('N','Y') | NO   |     | N       |       |
| References_priv       | enum('N','Y') | NO   |     | N       |       |
| Index_priv            | enum('N','Y') | NO   |     | N       |       |
| Alter_priv            | enum('N','Y') | NO   |     | N       |       |
| Create_tmp_table_priv | enum('N','Y') | NO   |     | N       |       |
| Lock_tables_priv      | enum('N','Y') | NO   |     | N       |       |
| Create_view_priv      | enum('N','Y') | NO   |     | N       |       |
| Show_view_priv        | enum('N','Y') | NO   |     | N       |       |
| Create_routine_priv   | enum('N','Y') | NO   |     | N       |       |
| Alter_routine_priv    | enum('N','Y') | NO   |     | N       |       |
| Execute_priv          | enum('N','Y') | NO   |     | N       |       |
| Event_priv            | enum('N','Y') | NO   |     | N       |       |
| Trigger_priv          | enum('N','Y') | NO   |     | N       |       |
+-----------------------+---------------+------+-----+---------+-------+
22 rows in set (0.15 sec)mysql>

三、索引定義

? 可以理解成目錄，在查找時(shí)有目錄查找速度會(huì)加快。
在這里插入圖片描述
索引
用途劃分
普通索引 INDEX
唯一索引 UNIQUE
主鍵 PRIMARY KEY
全文索引 FULLTEXT 引擎MyISAM
聚簇索引 引擎InnoDB
列級(jí)索引
單列索引
組合索引

1、索引的創(chuàng)建

CREATE TABLE tbl_name[col_name data_type][CONSTRAINT index_name][UNIQUE][INDEX|KEY][index_name](index_col_name[length])[ASC|DESC]-- ↓常用↓
CREATE INDEX
CREATE [UNIQUE] INDEX index_name ON tbl_name(col_name[(length)][ASC|DESC],......)
-- ↑常用↑ALTER TABLE
ALTER TABLE tbl_name ADD[UNIQUE|DULLTEXT][INDEX|KEY][index_name](col_name[length][ASC|DESC],......)

mysql> desc student;
+-----------+----------+------+-----+---------+-------+
| Field     | Type     | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| Sno       | char(11) | NO   |     | NULL    |       |
| Sage      | int(11)  | YES  |     | NULL    |       |
| Sbirthday | datetime | YES  |     | NULL    |       |
+-----------+----------+------+-----+---------+-------+
3 rows in set (0.04 sec)mysql> create index index_sage on student (Sage asc);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> desc student;
+-----------+----------+------+-----+---------+-------+
| Field     | Type     | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| Sno       | char(11) | NO   |     | NULL    |       |
| Sage      | int(11)  | YES  | MUL | NULL    |       |
| Sbirthday | datetime | YES  |     | NULL    |       |
+-----------+----------+------+-----+---------+-------+
3 rows in set (0.06 sec)mysql> 

-- 通過創(chuàng)建表時(shí)創(chuàng)建索引mysql> create table course (-> id int not null auto_increment,-> cname char(50) not null,-> primary key(id),-> index(cname)-> );
Query OK, 0 rows affected (0.01 sec)mysql> show tables;
+------------------+
| Tables_in_mydata |
+------------------+
| course           |
| student          |
+------------------+
2 rows in set (0.03 sec)mysql> desc course;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(11)  | NO   | PRI | NULL    | auto_increment |
| cname | char(50) | NO   | MUL | NULL    |                |
+-------+----------+------+-----+---------+----------------+
2 rows in set (0.06 sec)mysql> 

mysql> desc student;
+-----------+----------+------+-----+---------+-------+
| Field     | Type     | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| Sno       | char(11) | NO   |     | NULL    |       |
| Sage      | int(11)  | YES  | MUL | NULL    |       |
| Sbirthday | datetime | YES  |     | NULL    |       |
+-----------+----------+------+-----+---------+-------+
3 rows in set (0.03 sec)mysql> alter table student add index index_sbirth(Sbirthday);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> desc student;
+-----------+----------+------+-----+---------+-------+
| Field     | Type     | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| Sno       | char(11) | NO   |     | NULL    |       |
| Sage      | int(11)  | YES  | MUL | NULL    |       |
| Sbirthday | datetime | YES  | MUL | NULL    |       |
+-----------+----------+------+-----+---------+-------+
3 rows in set (0.05 sec)mysql>

2、索引的查看

SHOW {INDEX|INDEXS|KEYS} {FROM|IN} tbl_name [{FROM|IN} db_name]

mysql> show index in student;
+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          1 | index_sage   |            1 | Sage        | A         | NULL        | NULL     | NULL   | YES  | BTREE      |         |               |
| student |          1 | index_sbirth |            1 | Sbirthday   | A         | NULL        | NULL     | NULL   | YES  | BTREE      |         |               |
+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.04 sec)mysql>

在這里插入圖片描述

3、索引的刪除

DROP INDEX index_name ON tbl_nameALTER TABLE tbl_name DROP INDEX index_name

mysql> drop index index_sage on student;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> show index in student;
+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          1 | index_sbirth |            1 | Sbirthday   | A         | NULL        | NULL     | NULL   | YES  | BTREE      |         |               |
+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.03 sec)mysql> alter table student drop index index_sbirth;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> show index in student;
Empty setmysql>

在這里插入圖片描述

第四節(jié) 數(shù)據(jù)更新

一、插入數(shù)據(jù)

-- 重點(diǎn)
INSERT語法格式
INSERT INTO tb_name（column_list）
VALUES（value_list1）[,(value_list2)]，
從數(shù)據(jù)查詢中插入數(shù)據(jù)
INSERT INTO tb_namel(column_listl)
SELECT (column_list2)
FROM tb_name2 WHERE（condition）
注意：column_list1與clolumn_list2必須個(gè)數(shù)相同且數(shù)據(jù)類型一一對(duì)應(yīng)

使用INSERT...SET語句插入部分列值數(shù)據(jù)
INSERT [INTO] tbl-name
SET col_name={expr | DEFAULT},***使用INSERT...SELECT語句插入子查詢數(shù)據(jù)
INSERT[INTO]tbl_name[(col name，**-)]
SELECT ...

mysql> desc student;
+-----------+----------+------+-----+---------+-------+
| Field     | Type     | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| Sno       | char(11) | NO   |     | NULL    |       |
| Sage      | int(11)  | YES  |     | NULL    |       |
| Sbirthday | datetime | YES  |     | NULL    |       |
+-----------+----------+------+-----+---------+-------+
3 rows in set (0.04 sec)mysql> insert into student (Sno,Sage,Sbirthday) values (-> '001',18,'2002-09-08 07:06:05'-> );
Query OK, 1 row affected (0.01 sec)mysql> select * from student;
+-----+------+---------------------+
| Sno | Sage | Sbirthday           |
+-----+------+---------------------+
| 001 |   18 | 2002-09-08 07:06:05 |
+-----+------+---------------------+
1 row in set (0.05 sec)mysql> 

mysql> insert student set-> Sno='002',-> Sage=19,-> Sbirthday='2002-12-12 11:11:11'-> ;
Query OK, 1 row affected (0.01 sec)mysql> select * from student;
+-----+------+---------------------+
| Sno | Sage | Sbirthday           |
+-----+------+---------------------+
| 001 |   18 | 2002-09-08 07:06:05 |
| 002 |   19 | 2002-12-12 11:11:11 |
+-----+------+---------------------+
2 rows in set (0.04 sec)mysql>

二、刪除數(shù)據(jù)

DELETE FROM tb_name [WHERE <CONDITION>];

? 刪除所以記錄可以使用

TRUNCATE [TABLE] tb_name

? 注意：TRUNCATE是刪除整個(gè)表，重新建一個(gè)表，在刪除全部數(shù)據(jù)時(shí)候效率高于DELETE，DELETE是逐條刪除。TRUNCATE無法恢復(fù)，AUTO_INCREMENT計(jì)數(shù)器重置。

mysql> insert into student (Sno,Sage,Sbirthday) values ('003',18,'2003-11-09 21:38:21');
Query OK, 1 row affected (0.00 sec)mysql> insert into student (Sno,Sage,Sbirthday) values ('004',17,'2002-11-09 04:31:11');
Query OK, 1 row affected (0.00 sec)mysql> select * from student;
+-----+------+---------------------+
| Sno | Sage | Sbirthday           |
+-----+------+---------------------+
| 001 |   18 | 2002-09-08 07:06:05 |
| 002 |   19 | 2002-12-12 11:11:11 |
| 003 |   18 | 2003-11-09 21:38:21 |
| 004 |   17 | 2002-11-09 04:31:11 |
+-----+------+---------------------+
4 rows in set (0.05 sec)mysql> delete from student where sage=18;
Query OK, 2 rows affected (0.00 sec)mysql> select * from student;
+-----+------+---------------------+
| Sno | Sage | Sbirthday           |
+-----+------+---------------------+
| 002 |   19 | 2002-12-12 11:11:11 |
| 004 |   17 | 2002-11-09 04:31:11 |
+-----+------+---------------------+
2 rows in set (0.03 sec)-- ==============================================================mysql> show tables;
+------------------+
| Tables_in_mydata |
+------------------+
| course           |
| student          |
+------------------+
2 rows in set (0.02 sec)mysql> desc course;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(11)  | NO   | PRI | NULL    | auto_increment |
| cname | char(50) | NO   | MUL | NULL    |                |
+-------+----------+------+-----+---------+----------------+
2 rows in set (0.05 sec)mysql> insert into course (cname) values-> ('語文'),-> ('數(shù)學(xué)'),-> ('英語'),-> ('政治');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0mysql> select * from course;
+----+-------+
| id | cname |
+----+-------+
|  1 | 語文  |
|  2 | 數(shù)學(xué)  |
|  3 | 英語  |
|  4 | 政治  |
+----+-------+
4 rows in set (0.04 sec)mysql> delete from course;
Query OK, 4 rows affected (0.00 sec)mysql> select * from course;
Empty set-- 然后新增兩條記錄，發(fā)現(xiàn)id是從5、6開始的
mysql> insert into course (cname) values-> ('語文'),-> ('數(shù)學(xué)');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0mysql> select * from course;
+----+-------+
| id | cname |
+----+-------+
|  5 | 語文  |
|  6 | 數(shù)學(xué)  |
+----+-------+
2 rows in set (0.04 sec)-- 用truncate計(jì)數(shù)器重置mysql> truncate course;
Query OK, 0 rows affected (0.00 sec)mysql> select * from course;
Empty setmysql> insert into course (cname) values-> ('語文'),-> ('數(shù)學(xué)');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0mysql> select * from course;
+----+-------+
| id | cname |
+----+-------+
|  1 | 語文  |
|  2 | 數(shù)學(xué)  |
+----+-------+
2 rows in set (0.04 sec)mysql>

三、修改數(shù)據(jù)

UPDATE tbl_name SET col_name1=expr1 [, col_name2=expr2 ...][WHERE where_definition]

mysql> select * from student;
+-----+------+---------------------+
| Sno | Sage | Sbirthday           |
+-----+------+---------------------+
| 002 |   19 | 2002-12-12 11:11:11 |
| 004 |   17 | 2002-11-09 04:31:11 |
+-----+------+---------------------+
2 rows in set (0.02 sec)mysql> update student set sage=20 where Sno='004';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from student;
+-----+------+---------------------+
| Sno | Sage | Sbirthday           |
+-----+------+---------------------+
| 002 |   19 | 2002-12-12 11:11:11 |
| 004 |   20 | 2002-11-09 04:31:11 |
+-----+------+---------------------+
2 rows in set (0.04 sec)mysql>

第五節(jié) 數(shù)據(jù)查詢

一、SELECT語句

SELECT [ALL | DISTINCT | DISTINCTROW] select_expr, ... [FROM table_reference [,table_reference]...][WHERE  where_definition]     /*WHERE子句*/[GROUP BY[col_name | expr | position）[ASC | DESC], ...[WITH ROLLUP]]     /*GROUP BY子句*/
[HAVING where_definition]     /*HAVING子句*/
[ORDER BY{col_name | expr | position}[ASC | DESC], ...]     /*ORDER BY子句*/
[LIMIT{[offset,]row_count | row_count OFFSET offset}]     /*LIMIT子句*/
表4.2 SELECT語句中各子句的使用次序及說明
子句說明是否必須使用
SELECT返回的列或表達(dá)式是
FROM從中檢索數(shù)據(jù)的表僅在從表選擇數(shù)據(jù)時(shí)使用
WHERE行級(jí)過濾否
GROUP BY分組說明僅在從表選擇數(shù)據(jù)時(shí)使用
HAVING組級(jí)說明否
ORDER BY輸出排序順序否
LIMIT要檢索的行數(shù)否

二、列的選擇與指定

1、選擇指定的列

SELECT classNo, department, className From tb_class;-- 選擇所有列
SELECT * FORM tb_class;

mysql> desc student;
+-----------+----------+------+-----+---------+-------+
| Field     | Type     | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| Sno       | char(11) | NO   |     | NULL    |       |
| Sage      | int(11)  | YES  |     | NULL    |       |
| Sbirthday | datetime | YES  |     | NULL    |       |
+-----------+----------+------+-----+---------+-------+
3 rows in set (0.05 sec)mysql> select Sbirthday from student;
+---------------------+
| Sbirthday           |
+---------------------+
| 2002-12-12 11:11:11 |
| 2002-11-09 04:31:11 |
+---------------------+
2 rows in set (0.04 sec)mysql> select * from student;
+-----+------+---------------------+
| Sno | Sage | Sbirthday           |
+-----+------+---------------------+
| 002 |   19 | 2002-12-12 11:11:11 |
| 004 |   20 | 2002-11-09 04:31:11 |
+-----+------+---------------------+
2 rows in set (0.06 sec)mysql>

2、定義并使用列的別名

SELECT studentName as 姓名, sex as 性別, YEAR (NOW())-YEAR(birthday) as 年齡 from tb_student;

mysql> select-> sno as 學(xué)號(hào),-> sage as 年齡,-> sbirthday as 出生日期-> from student;
+------+------+---------------------+
| 學(xué)號(hào) | 年齡 | 出生日期            |
+------+------+---------------------+
| 002  |   19 | 2002-12-12 11:11:11 |
| 004  |   20 | 2002-11-09 04:31:11 |
+------+------+---------------------+
2 rows in set (0.05 sec)mysql>

3、替換查詢結(jié)果集中的數(shù)據(jù)

CASE
WHEN 條件1 THEN 表達(dá)式1WHEN 條件2 THEN 表達(dá)式2...
ELSE 表達(dá)式
END[AS] column_alias

4、計(jì)算列值

? 使用SELECT語句對(duì)列進(jìn)行查詢時(shí)，在結(jié)果集中可以輸出對(duì)列值計(jì)算后的值，其具體使用方法是將SELECT語句的語法項(xiàng)“select_expr”指定為對(duì)應(yīng)列參與計(jì)算的表達(dá)式。

mysql> select * from rec;
+----+-----+-----+-------+-----+
| id | Gno | Pno | price | num |
+----+-----+-----+-------+-----+
|  1 | 001 | 001 |   1.2 | 300 |
|  2 | 001 | 002 |   1.5 | 200 |
|  3 | 002 | 001 |   1.2 | 170 |
|  4 | 002 | 002 |   1.5 | 130 |
|  5 | 003 | 001 |   1.2 | 110 |
|  6 | 003 | 002 |   1.5 |  60 |
+----+-----+-----+-------+-----+
6 rows in set (0.04 sec)mysql> select price*num as 銷售額,gno,pno from rec;
+--------------------+-----+-----+
| 銷售額             | gno | pno |
+--------------------+-----+-----+
| 360.00001430511475 | 001 | 001 |
|                300 | 001 | 002 |
|  204.0000081062317 | 002 | 001 |
|                195 | 002 | 002 |
| 132.00000524520874 | 003 | 001 |
|                 90 | 003 | 002 |
+--------------------+-----+-----+
6 rows in set (0.05 sec)mysql>

5、聚合函數(shù)

表4.3 MySQL中常用聚合函數(shù)表
函數(shù)名說明
COUNT求組中項(xiàng)數(shù)，返回INT類型整數(shù)
MAX求最大值
MIN求最小值
SUM返回表達(dá)式中所有值的和
AVG求組中值的平均值
STD或STDDEV返回給定表達(dá)式中所有值得標(biāo)準(zhǔn)值
VARLANCE返回給定表達(dá)式中所有值得方差
GROUP_CONCAT返回由屬于一組的列值連接組合而成的結(jié)果
BIT_AND邏輯或
BIR_OR邏輯與
BIT_XOR邏輯異或
-- 銷售數(shù)量超過150的條數(shù)
mysql> select count(*) from rec where num>150;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.03 sec)-- 取某一列的最大值
mysql> select max(num*price),gno from rec;
+--------------------+-----+
| max(num*price)     | gno |
+--------------------+-----+
| 360.00001430511475 | 001 |
+--------------------+-----+
1 row in set (0.02 sec)-- 在MySQL5.7版本之后對(duì)group by進(jìn)行了優(yōu)化。默認(rèn)啟動(dòng)改進(jìn)之后的版本啟動(dòng)了ONLY_FULL_GROUP_BY模式。即ONLY_FULL_GROUP_BY是MySQL數(shù)據(jù)庫提供的一個(gè)sql_mode，通過這個(gè)sql_mode來保證SQL語句“分組求最值”合法性的檢查。這種模式采用了與Oracle、DB2等數(shù)據(jù)庫的處理方式。即不允許select target list中出現(xiàn)語義不明確的列。
-- 解決方式：只要有聚合函數(shù)sum()，count()，max()，avg()等函數(shù)就需要用到group by
-- mysql> select version();
-- +-----------+
-- | version() |
-- +-----------+
-- | 5.5.29    |
-- +-----------+
-- 1 row in set (0.06 sec)mysql> select min(num*price),gno from rec;
+----------------+-----+
| min(num*price) | gno |
+----------------+-----+
|             90 | 001 |
+----------------+-----+
1 row in set (0.02 sec)mysql> select sum(num*price) as 總銷售額 from rec;
+--------------------+
| 總銷售額           |
+--------------------+
| 1281.0000276565552 |
+--------------------+
1 row in set (0.04 sec)mysql> select avg(num*price) as 平均銷售額 from rec;
+--------------------+
| 平均銷售額         |
+--------------------+
| 213.50000460942587 |
+--------------------+
1 row in set (0.05 sec)mysql>

三、FROM子句與多表連接查詢

1、交叉連接

2、內(nèi)連接

FROM table1 [INNER] JOIN table2 ON 連接條件等值連接
FROM table1, [table2] ......
WHERE table1.字段 <比較運(yùn)算符> table2.字段

-- 準(zhǔn)備數(shù)據(jù)↓↓↓↓↓
mysql> create table gong(-> id int NOT NULL primary key AUTO_INCREMENT,-> gnum char(11) NOT NULL,-> gname varbinary(255) NOT NULL-> );
Query OK, 0 rows affected (0.00 sec)mysql> insert into gong (gnum,gname) values-> ('001','張三'),-> ('002','李四'),-> ('003','王五');
Query OK, 3 rows affected (2.04 sec)
Records: 3  Duplicates: 0  Warnings: 0mysql> insert into pro (pnum,pname) values-> ('001','產(chǎn)品1'),-> ('002','產(chǎn)品2');
Query OK, 2 rows affected (2.04 sec)
Records: 2  Duplicates: 0  Warnings: 0mysql> select * from gong;
+----+------+-------+
| id | gnum | gname |
+----+------+-------+
|  2 | 001  | 張三  |
|  3 | 002  | 李四  |
|  4 | 003  | 王五  |
+----+------+-------+
3 rows in set (0.03 sec)mysql> select * from pro;
+----+------+-------+
| id | pnum | pname |
+----+------+-------+
|  1 | 001  | 產(chǎn)品1 |
|  2 | 002  | 產(chǎn)品2 |
+----+------+-------+
2 rows in set (0.04 sec)-- 準(zhǔn)備數(shù)據(jù)完成↑↑↑↑↑mysql> select * from gong,rec;
+----+------+-------+----+-----+-----+-------+-----+
| id | gnum | gname | id | Gno | Pno | price | num |
+----+------+-------+----+-----+-----+-------+-----+
|  2 | 001  | 張三  |  1 | 001 | 001 |   1.2 | 300 |
|  3 | 002  | 李四  |  1 | 001 | 001 |   1.2 | 300 |
|  4 | 003  | 王五  |  1 | 001 | 001 |   1.2 | 300 |
|  2 | 001  | 張三  |  2 | 001 | 002 |   1.5 | 200 |
|  3 | 002  | 李四  |  2 | 001 | 002 |   1.5 | 200 |
|  4 | 003  | 王五  |  2 | 001 | 002 |   1.5 | 200 |
|  2 | 001  | 張三  |  3 | 002 | 001 |   1.2 | 170 |
|  3 | 002  | 李四  |  3 | 002 | 001 |   1.2 | 170 |
|  4 | 003  | 王五  |  3 | 002 | 001 |   1.2 | 170 |
|  2 | 001  | 張三  |  4 | 002 | 002 |   1.5 | 130 |
|  3 | 002  | 李四  |  4 | 002 | 002 |   1.5 | 130 |
|  4 | 003  | 王五  |  4 | 002 | 002 |   1.5 | 130 |
|  2 | 001  | 張三  |  5 | 003 | 001 |   1.2 | 110 |
|  3 | 002  | 李四  |  5 | 003 | 001 |   1.2 | 110 |
|  4 | 003  | 王五  |  5 | 003 | 001 |   1.2 | 110 |
|  2 | 001  | 張三  |  6 | 003 | 002 |   1.5 |  60 |
|  3 | 002  | 李四  |  6 | 003 | 002 |   1.5 |  60 |
|  4 | 003  | 王五  |  6 | 003 | 002 |   1.5 |  60 |
+----+------+-------+----+-----+-----+-------+-----+
18 rows in set (0.10 sec)mysql> select * from gong join rec on gong.gnum=rec.gno;
+----+------+-------+----+-----+-----+-------+-----+
| id | gnum | gname | id | Gno | Pno | price | num |
+----+------+-------+----+-----+-----+-------+-----+
|  2 | 001  | 張三  |  1 | 001 | 001 |   1.2 | 300 |
|  2 | 001  | 張三  |  2 | 001 | 002 |   1.5 | 200 |
|  3 | 002  | 李四  |  3 | 002 | 001 |   1.2 | 170 |
|  3 | 002  | 李四  |  4 | 002 | 002 |   1.5 | 130 |
|  4 | 003  | 王五  |  5 | 003 | 001 |   1.2 | 110 |
|  4 | 003  | 王五  |  6 | 003 | 002 |   1.5 |  60 |
+----+------+-------+----+-----+-----+-------+-----+
6 rows in set (0.05 sec)mysql> select * from gong,rec where gong.gnum=rec.gno;
+----+------+-------+----+-----+-----+-------+-----+
| id | gnum | gname | id | Gno | Pno | price | num |
+----+------+-------+----+-----+-----+-------+-----+
|  2 | 001  | 張三  |  1 | 001 | 001 |   1.2 | 300 |
|  2 | 001  | 張三  |  2 | 001 | 002 |   1.5 | 200 |
|  3 | 002  | 李四  |  3 | 002 | 001 |   1.2 | 170 |
|  3 | 002  | 李四  |  4 | 002 | 002 |   1.5 | 130 |
|  4 | 003  | 王五  |  5 | 003 | 001 |   1.2 | 110 |
|  4 | 003  | 王五  |  6 | 003 | 002 |   1.5 |  60 |
+----+------+-------+----+-----+-----+-------+-----+
6 rows in set (0.10 sec)mysql>

3、外連接

（1）左連接
FROM table1 [LEFT] JOIN table2 ON 連接條件
（2）右連接
FROM table1 [RIGHT] JOIN table2 ON 連接條件

mysql> select * from gong left join rec on gong.gnum=rec.gno;
+----+------+-------+----+-----+-----+-------+-----+
| id | gnum | gname | id | Gno | Pno | price | num |
+----+------+-------+----+-----+-----+-------+-----+
|  2 | 001  | 張三  |  1 | 001 | 001 |   1.2 | 300 |
|  2 | 001  | 張三  |  2 | 001 | 002 |   1.5 | 200 |
|  3 | 002  | 李四  |  3 | 002 | 001 |   1.2 | 170 |
|  3 | 002  | 李四  |  4 | 002 | 002 |   1.5 | 130 |
|  4 | 003  | 王五  |  5 | 003 | 001 |   1.2 | 110 |
|  4 | 003  | 王五  |  6 | 003 | 002 |   1.5 |  60 |
+----+------+-------+----+-----+-----+-------+-----+
6 rows in set (0.04 sec)-- 我再加一條記錄004mysql> insert into rec values (7,'003','001',1.2,600);
Query OK, 1 row affected (2.03 sec)mysql> select * from gong left join rec on gong.gnum=rec.gno;
+----+------+-------+----+-----+-----+-------+-----+
| id | gnum | gname | id | Gno | Pno | price | num |
+----+------+-------+----+-----+-----+-------+-----+
|  2 | 001  | 張三  |  1 | 001 | 001 |   1.2 | 300 |
|  2 | 001  | 張三  |  2 | 001 | 002 |   1.5 | 200 |
|  3 | 002  | 李四  |  3 | 002 | 001 |   1.2 | 170 |
|  3 | 002  | 李四  |  4 | 002 | 002 |   1.5 | 130 |
|  4 | 003  | 王五  |  5 | 003 | 001 |   1.2 | 110 |
|  4 | 003  | 王五  |  6 | 003 | 002 |   1.5 |  60 |
|  4 | 003  | 王五  |  7 | 003 | 001 |   1.2 | 600 |
+----+------+-------+----+-----+-----+-------+-----+
7 rows in set (0.08 sec)-- 這個(gè)時(shí)候發(fā)現(xiàn)004沒有，也就是說左連接的時(shí)候，在右里面有和它對(duì)應(yīng)的就到一起，否則直接剔除mysql> select * from gong right join rec on gong.gnum=rec.gno;
+----+------+-------+----+-----+-----+-------+-----+
| id | gnum | gname | id | Gno | Pno | price | num |
+----+------+-------+----+-----+-----+-------+-----+
|  2 | 001  | 張三  |  1 | 001 | 001 |   1.2 | 300 |
|  2 | 001  | 張三  |  2 | 001 | 002 |   1.5 | 200 |
|  3 | 002  | 李四  |  3 | 002 | 001 |   1.2 | 170 |
|  3 | 002  | 李四  |  4 | 002 | 002 |   1.5 | 130 |
|  4 | 003  | 王五  |  5 | 003 | 001 |   1.2 | 110 |
|  4 | 003  | 王五  |  6 | 003 | 002 |   1.5 |  60 |
|NULL| NULL | NULL |  7 | 004 | 001 |   1.2 | 600 |
+----+------+-------+----+-----+-----+-------+-----+
7 rows in set (0.09 sec)mysql>

四、WHERE子句與條件查詢

查詢條件操作符
比較=，<>，!=，<，<=，>，>=，!<，!> NOT+比較運(yùn)算表達(dá)式
確定范圍DETWEEN AND, NOT BETWEEN AND
確定集合IN, NOT IN
字符匹配LIKE, NOT LIKE
空值IS NULL, IS NOT NULL
多重條件AND, OR
mysql> select * from rec where num>200;
+----+-----+-----+-------+-----+
| id | Gno | Pno | price | num |
+----+-----+-----+-------+-----+
|  1 | 001 | 001 |   1.2 | 300 |
|  7 | 003 | 001 |   1.2 | 600 |
+----+-----+-----+-------+-----+
2 rows in set (2.08 sec)mysql> select * from rec where num <= 200;
+----+-----+-----+-------+-----+
| id | Gno | Pno | price | num |
+----+-----+-----+-------+-----+
|  2 | 001 | 002 |   1.5 | 200 |
|  3 | 002 | 001 |   1.2 | 170 |
|  4 | 002 | 002 |   1.5 | 130 |
|  5 | 003 | 001 |   1.2 | 110 |
|  6 | 003 | 002 |   1.5 |  60 |
+----+-----+-----+-------+-----+
5 rows in set (0.05 sec)mysql> select * from rec where num between 150 and 300;
+----+-----+-----+-------+-----+
| id | Gno | Pno | price | num |
+----+-----+-----+-------+-----+
|  1 | 001 | 001 |   1.2 | 300 |
|  2 | 001 | 002 |   1.5 | 200 |
|  3 | 002 | 001 |   1.2 | 170 |
+----+-----+-----+-------+-----+
3 rows in set (0.05 sec)mysql> insert into pro (pnum,pname) values ('003','產(chǎn)品3'),('004','產(chǎn)品4');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0-- 查賣出去的mysql> select * from pro;
+----+------+-------+
| id | pnum | pname |
+----+------+-------+
|  1 | 001  | 產(chǎn)品1 |
|  2 | 002  | 產(chǎn)品2 |
|  3 | 003  | 產(chǎn)品3 |
|  4 | 004  | 產(chǎn)品4 |
+----+------+-------+
4 rows in set (0.08 sec)mysql> select * from pro where pnum in (select distinct pno from rec);
+----+------+-------+
| id | pnum | pname |
+----+------+-------+
|  1 | 001  | 產(chǎn)品1 |
|  2 | 002  | 產(chǎn)品2 |
+----+------+-------+
2 rows in set (0.10 sec)mysql> select * from pro where pnum not in (select distinct pno from rec);
+----+------+-------+
| id | pnum | pname |
+----+------+-------+
|  3 | 003  | 產(chǎn)品3 |
|  4 | 004  | 產(chǎn)品4 |
+----+------+-------+
2 rows in set (0.04 sec)mysql> insert into gong (gnum,gname) values ('004','趙云'),('005','趙雅芝'),('006','趙曼億');
Query OK, 3 rows affected (2.02 sec)
Records: 3  Duplicates: 0  Warnings: 0-- 我們在這個(gè)表中找姓“趙”的都有誰mysql> select * from gong where gname like '趙%';
+----+------+--------+
| id | gnum | gname  |
+----+------+--------+
|  5 | 004  | 趙云   |
|  6 | 005  | 趙雅芝 |
|  7 | 006  | 趙曼億 |
+----+------+--------+
3 rows in set (0.04 sec)mysql> insert into gong (gnum) values ('007');
Query OK, 1 row affected (0.00 sec)mysql> select * from gong;
+----+------+--------+
| id | gnum | gname  |
+----+------+--------+
|  2 | 001  | 張三   |
|  3 | 002  | 李四   |
|  4 | 003  | 王五   |
|  5 | 004  | 趙云   |
|  6 | 005  | 趙雅芝 |
|  7 | 006  | 趙曼億 |
| 10 | 007  | NULL   |
+----+------+--------+
7 rows in set (0.10 sec)mysql> select * from gong where gname is null;
+----+------+--------+
| id | gnum | gname  |
+----+------+--------+
| 10 | 007  | NULL   |
+----+------+--------+
1 rows in set (0.10 sec)mysql> update gong set gname='張飛' where gnum=007;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from gong;
+----+------+--------+
| id | gnum | gname  |
+----+------+--------+
|  2 | 001  | 張三   |
|  3 | 002  | 李四   |
|  4 | 003  | 王五   |
|  5 | 004  | 趙云   |
|  6 | 005  | 趙雅芝 |
|  7 | 006  | 趙曼億 |
| 10 | 007  | 張飛   |
+----+------+--------+
7 rows in set (0.06 sec)-- 姓“張”的并且賣出貨的有誰mysql> select * from gong where gname like '張%' and gnum in (select distinct gnum from rec);
+----+------+-------+
| id | gnum | gname |
+----+------+-------+
|  2 | 001  | 張三  |
+----+------+-------+
1 rows in set (0.04 sec)mysql>

子查詢

IN語法：

WHERE 列名 IN (值列表或者子SELECT[但必須只有一列])

比較運(yùn)算符：

WHERE 列名 <比較運(yùn)算符> (值或者子SELECT[但必須只有一值])

EXIST查詢

WHERE EXIST (子SELECT)

五、GROUP BY子句與分組數(shù)據(jù)

語法：

[GROUP BY 字段列表][HAVING<條件表達(dá)式>]

含義：按照某一列的值相同的分成一組，進(jìn)行聚合計(jì)算

mysql> select (price*num) as 銷售額,rec.*from rec;
+--------------------+----+-----+-----+-------+-----+
| 銷售額             | id | Gno | Pno | price | num |
+--------------------+----+-----+-----+-------+-----+
| 360.00001430511475 |  1 | 001 | 001 |   1.2 | 300 |
|                300 |  2 | 001 | 002 |   1.5 | 200 |
|  204.0000081062317 |  3 | 002 | 001 |   1.2 | 170 |
|                195 |  4 | 002 | 002 |   1.5 | 130 |
| 132.00000524520874 |  5 | 003 | 001 |   1.2 | 110 |
|                 90 |  6 | 003 | 002 |   1.5 |  60 |
|  720.0000286102295 |  7 | 003 | 001 |   1.2 | 600 |
+--------------------+----+-----+-----+-------+-----+
7 rows in set (2.08 sec)mysql> select sum(price*num) as 銷售額,rec.* from rec group by gno;
+-------------------+----+-----+-----+-------+-----+
| 銷售額            | id | Gno | Pno | price | num |
+-------------------+----+-----+-----+-------+-----+
| 660.0000143051147 |  1 | 001 | 001 |   1.2 | 300 |
| 399.0000081062317 |  3 | 002 | 001 |   1.2 | 170 |
| 942.0000338554382 |  5 | 003 | 001 |   1.2 | 110 |
+-------------------+----+-----+-----+-------+-----+
3 rows in set (0.05 sec)mysql>

六、HAVING子句

? 在SELECT語句中，除了能使用GROUP BY子句分組數(shù)據(jù)之外，還可以使用HAVING子句來過濾分組，即在結(jié)果集中規(guī)定包含哪些分組和排除哪些分組。

-- 銷售額超過400
mysql> select sum(price*num) as 銷售額,rec.* from rec group by gno having 銷售額>400;
+-------------------+----+-----+-----+-------+-----+
| 銷售額            | id | Gno | Pno | price | num |
+-------------------+----+-----+-----+-------+-----+
| 660.0000143051147 |  1 | 001 | 001 |   1.2 | 300 |
| 942.0000338554382 |  5 | 003 | 001 |   1.2 | 110 |
+-------------------+----+-----+-----+-------+-----+
2 rows in set (0.05 sec)mysql>

七、ORDER BY子句

語法：

[ORDER BY 字段1 [ASC|DESC][,字段2[ASC|DESC]]......

-- 排序
mysql> select sum(price*num) as 銷售額,rec.* from rec group by gno having 銷售額>400 order by gno desc;
+-------------------+----+-----+-----+-------+-----+
| 銷售額            | id | Gno | Pno | price | num |
+-------------------+----+-----+-----+-------+-----+
| 942.0000338554382 |  5 | 003 | 001 |   1.2 | 110 |
| 660.0000143051147 |  1 | 001 | 001 |   1.2 | 300 |
+-------------------+----+-----+-----+-------+-----+
2 rows in set (0.04 sec)mysql>

八、LIMIT子句

LIMIT [位置偏移量,] 行數(shù)

mysql> select sum(price*num) as 銷售額,rec.* from rec group by gno;
+-------------------+----+-----+-----+-------+-----+
| 銷售額            | id | Gno | Pno | price | num |
+-------------------+----+-----+-----+-------+-----+
| 660.0000143051147 |  1 | 001 | 001 |   1.2 | 300 |
| 399.0000081062317 |  3 | 002 | 001 |   1.2 | 170 |
| 942.0000338554382 |  5 | 003 | 001 |   1.2 | 110 |
+-------------------+----+-----+-----+-------+-----+
3 rows in set (0.04 sec)mysql> select sum(price*num) as 銷售額,rec.* from rec group by gno limit 0,3;
+-------------------+----+-----+-----+-------+-----+
| 銷售額            | id | Gno | Pno | price | num |
+-------------------+----+-----+-----+-------+-----+
| 660.0000143051147 |  1 | 001 | 001 |   1.2 | 300 |
| 399.0000081062317 |  3 | 002 | 001 |   1.2 | 170 |
| 942.0000338554382 |  5 | 003 | 001 |   1.2 | 110 |
+-------------------+----+-----+-----+-------+-----+
3 rows in set (0.06 sec)mysql> select sum(price*num) as 銷售額,rec.* from rec group by gno limit 1,3;
+-------------------+----+-----+-----+-------+-----+
| 銷售額            | id | Gno | Pno | price | num |
+-------------------+----+-----+-----+-------+-----+
| 399.0000081062317 |  3 | 002 | 001 |   1.2 | 170 |
| 942.0000338554382 |  5 | 003 | 001 |   1.2 | 110 |
+-------------------+----+-----+-----+-------+-----+
2 rows in set (0.08 sec)mysql>

第六節(jié) 視圖

? 視圖是從一個(gè)或多個(gè)表（或視圖）導(dǎo)出的表。視圖是數(shù)據(jù)庫的用戶使用數(shù)據(jù)庫的觀點(diǎn)。例如，對(duì)于一個(gè)學(xué)校，其學(xué)生的情況存于數(shù)據(jù)庫的一個(gè)或多個(gè)表中，而作為學(xué)校的不同職能部門，所關(guān)心的學(xué)生數(shù)據(jù)的內(nèi)容是不同的。視圖是一張?zhí)摫?/strong>。視圖一經(jīng)定義以后，就可以像表一樣被查詢、修改、刪除和更新。

一、創(chuàng)建視圖

CREATE [OR REPLACE] [ALGORITHM={UNDEFINED | MERGE | TEMPTABLE}]VIEW view_name [(column_list)]AS select_statement[WITH [CASCADED | LOCAL] CHECK OPTION]

mysql> create OR REPLACE view v_gname as select gname from gong;
Query OK, 0 rows affected (2.02 sec)mysql> create OR REPLACE view v_rec_gong as select gname,rec.* from gong,rec where gong.gnum=rec.gno;
Query OK, 0 rows affected (0.00 sec)mysql>

二、刪除視圖

DROP VIEW [IF EXISTS]view_name [,view_name]...

mysql> drop view IF EXISTS v_gname;
Query OK, 0 rows affected (2.04 sec)mysql>

三、修改視圖定義

語法格式：

ALTER VIEW view_name [(column_list)]AS select_statement[WITH [CASCADED | LOCAL] CHECK OPTION]

四、查看視圖定義

語法格式：

SHOW CREATE VIEW view_name

mysql> show create view v_rec_gong;
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View       | Create View                                                                                                                                                                                                                                                                                              | character_set_client | collation_connection |
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| v_rec_gong | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_rec_gong` AS select `gong`.`gname` AS `gname`,`rec`.`id` AS `id`,`rec`.`Gno` AS `Gno`,`rec`.`Pno` AS `Pno`,`rec`.`price` AS `price`,`rec`.`num` AS `num` from (`gong` join `rec`) where (`gong`.`gnum` = `rec`.`Gno`) | utf8mb4              | utf8mb4_general_ci   |
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.05 sec)mysql>

在這里插入圖片描述

五、更新視圖數(shù)據(jù)

? 要通過視圖更新基本表數(shù)據(jù)，必須保證視圖是可更新視圖，即可以在INSERT，UPDATE或DELETE等語句當(dāng)中使用它們。對(duì)于可更新的視圖，在視圖中的行和基表中的行之間必須具有一對(duì)一的關(guān)系。還有一些特定的其他結(jié)構(gòu)，這類結(jié)構(gòu)會(huì)使得視圖不可更新。

? 強(qiáng)烈不建議更新視圖，更新視圖風(fēng)險(xiǎn)性非常大，更新不好的話影響到基本表，要用基本表取更新視圖。

六、查詢視圖數(shù)據(jù)

? 視圖一經(jīng)定義后，就可以如同查詢數(shù)據(jù)庫中的真實(shí)表一樣，對(duì)視圖進(jìn)行數(shù)據(jù)查詢檢索，這也是對(duì)視圖使用最多的一種操作。

mysql> create OR REPLACE view v_game as select gnum,gname from gong;
Query OK, 0 rows affected (0.00 sec)mysql> select * from v_game;
+------+--------+
| gnum | gname  |
+------+--------+
| 001  | 張三   |
| 002  | 李四   |
| 003  | 王五   |
| 004  | 趙云   |
| 005  | 趙雅芝 |
| 006  | 趙曼億 |
| 007  | 張飛   |
+------+--------+
7 rows in set (0.11 sec)mysql> select * from v_game where gname like '趙%';
+------+--------+
| gnum | gname  |
+------+--------+
| 004  | 趙云   |
| 005  | 趙雅芝 |
| 006  | 趙曼億 |
+------+--------+
3 rows in set (0.04 sec)mysql>

附上mydata.sql

/*Navicat Premium Data TransferSource Server         : 00Source Server Type    : MySQLSource Server Version : 50529Source Host           : localhost:3306Source Schema         : mydataTarget Server Type    : MySQLTarget Server Version : 50529File Encoding         : 65001Date: 04/10/2023 18:27:33
*/create database IF NOT EXISTS mydata;
USE mydata;SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course`  (`id` int(11) NOT NULL AUTO_INCREMENT,`cname` char(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,PRIMARY KEY (`id`) USING BTREE,INDEX `cname`(`cname`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = FIXED;-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES (1, '語文');
INSERT INTO `course` VALUES (2, '數(shù)學(xué)');-- ----------------------------
-- Table structure for gong
-- ----------------------------
DROP TABLE IF EXISTS `gong`;
CREATE TABLE `gong`  (`id` int(11) NOT NULL AUTO_INCREMENT,`gnum` char(11) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,`gname` varbinary(255) NOT NULL,PRIMARY KEY (`id`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 11 CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Dynamic;-- ----------------------------
-- Records of gong
-- ----------------------------
INSERT INTO `gong` VALUES (2, '001', 0xE5BCA0E4B889);
INSERT INTO `gong` VALUES (3, '002', 0xE69D8EE59B9B);
INSERT INTO `gong` VALUES (4, '003', 0xE78E8BE4BA94);
INSERT INTO `gong` VALUES (5, '004', 0xE8B5B5E4BA91);
INSERT INTO `gong` VALUES (6, '005', 0xE8B5B5E99B85E88A9D);
INSERT INTO `gong` VALUES (7, '006', 0xE8B5B5E69BBCE4BABF);
INSERT INTO `gong` VALUES (10, '007', 0xE5BCA0E9A39E);-- ----------------------------
-- Table structure for pro
-- ----------------------------
DROP TABLE IF EXISTS `pro`;
CREATE TABLE `pro`  (`id` int(11) NOT NULL AUTO_INCREMENT,`pnum` char(11) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,`pname` varbinary(255) NOT NULL,PRIMARY KEY (`id`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Dynamic;-- ----------------------------
-- Records of pro
-- ----------------------------
INSERT INTO `pro` VALUES (1, '001', 0xE4BAA7E5938131);
INSERT INTO `pro` VALUES (2, '002', 0xE4BAA7E5938132);
INSERT INTO `pro` VALUES (3, '003', 0xE4BAA7E5938133);
INSERT INTO `pro` VALUES (4, '004', 0xE4BAA7E5938134);-- ----------------------------
-- Table structure for rec
-- ----------------------------
DROP TABLE IF EXISTS `rec`;
CREATE TABLE `rec`  (`id` int(11) NOT NULL,`Gno` varchar(11) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL,`Pno` varchar(11) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL,`price` float NULL DEFAULT NULL,`num` int(11) NULL DEFAULT NULL,PRIMARY KEY (`id`) USING BTREE
) ENGINE = MyISAM CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = DYNAMIC;-- ----------------------------
-- Records of rec
-- ----------------------------
INSERT INTO `rec` VALUES (1, '001', '001', 1.2, 300);
INSERT INTO `rec` VALUES (2, '001', '002', 1.5, 200);
INSERT INTO `rec` VALUES (3, '002', '001', 1.2, 170);
INSERT INTO `rec` VALUES (4, '002', '002', 1.5, 130);
INSERT INTO `rec` VALUES (5, '003', '001', 1.2, 110);
INSERT INTO `rec` VALUES (6, '003', '002', 1.5, 60);
INSERT INTO `rec` VALUES (7, '003', '001', 1.2, 600);-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (`Sno` char(11) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,`Sage` int(11) NULL DEFAULT NULL,`Sbirthday` datetime NULL DEFAULT NULL
) ENGINE = MyISAM CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = FIXED;-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('002', 19, '2002-12-12 11:11:11');
INSERT INTO `student` VALUES ('004', 20, '2002-11-09 04:31:11');-- ----------------------------
-- View structure for v_game
-- ----------------------------
DROP VIEW IF EXISTS `v_game`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `v_game` AS select gnum,gname from gong ;-- ----------------------------
-- View structure for v_rec_gong
-- ----------------------------
DROP VIEW IF EXISTS `v_rec_gong`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `v_rec_gong` AS select gname,rec.* from gong,rec where gong.gnum=rec.gno ;SET FOREIGN_KEY_CHECKS = 1;
http://www.risenshineclean.com/news/7996.html

相關(guān)文章：

  • 如何快速找到做網(wǎng)站的客戶站長素材網(wǎng)
  • 用wordpress做外貿(mào)網(wǎng)站b站推廣網(wǎng)站2024年
  • 企業(yè)對(duì)電子商務(wù)網(wǎng)站的建設(shè)網(wǎng)頁設(shè)計(jì)制作網(wǎng)站代碼
  • 做動(dòng)圖的網(wǎng)站在哪里推廣自己的產(chǎn)品
  • 網(wǎng)站搭建素材百度總部電話
  • 網(wǎng)站建設(shè)叫什么軟件網(wǎng)絡(luò)營銷方式有哪些
  • 境外公司在國內(nèi)建網(wǎng)站黑馬it培訓(xùn)班出來現(xiàn)狀
  • 音樂網(wǎng)站開發(fā)畢業(yè)論文創(chuàng)建網(wǎng)站需要多少資金
  • 網(wǎng)站建設(shè)初步認(rèn)識(shí)的實(shí)訓(xùn)體會(huì)行業(yè)網(wǎng)站有哪些平臺(tái)
  • 中山制作網(wǎng)站的公司西安網(wǎng)站推廣慧創(chuàng)科技
  • 互聯(lián)網(wǎng)下的網(wǎng)絡(luò)營銷前端seo是什么意思
  • 網(wǎng)站建設(shè)營銷方案整站外包優(yōu)化公司
  • 泉州北京網(wǎng)站建設(shè)如何制作app軟件
  • phpcms學(xué)校網(wǎng)站模板如何制作微信小程序店鋪
  • wordpress 社交分享肇慶seo排名外包
  • 網(wǎng)站倒計(jì)時(shí)代碼資源企業(yè)網(wǎng)站排名優(yōu)化價(jià)格
  • html制作網(wǎng)站的步驟網(wǎng)絡(luò)服務(wù)包括
  • 企業(yè)域名是什么網(wǎng)站seo關(guān)鍵詞設(shè)置
  • 網(wǎng)站設(shè)計(jì)營銷網(wǎng)站出租三級(jí)域名費(fèi)用
  • 做視頻網(wǎng)站視頻的軟件企業(yè)營銷培訓(xùn)課程
  • 女性時(shí)尚網(wǎng)站源碼客戶關(guān)系管理
  • 有沒有免費(fèi)的微網(wǎng)站視頻營銷模式有哪些
  • 昭通網(wǎng)站建設(shè)如何提高網(wǎng)站排名的方法
  • 二手站網(wǎng)站怎做優(yōu)化課程體系
  • 招聘 負(fù)責(zé)網(wǎng)站開發(fā)網(wǎng)絡(luò)營銷有什么方式
  • 網(wǎng)站做cdn百度網(wǎng)頁版入口
  • 網(wǎng)站信息發(fā)布制度建設(shè)seo網(wǎng)站優(yōu)化排名
  • 建網(wǎng)站哪便宜百度網(wǎng)站提交入口網(wǎng)址
  • 網(wǎng)頁跟網(wǎng)站的區(qū)別百度seo2022
  • 開發(fā)app的注意事項(xiàng)seo代理計(jì)費(fèi)系統(tǒng)