阿里云网站空间,有哪些网站是响应式的,wordpress插件漏洞扫描,在线图片编辑器图片编辑SQL与关系数据库基本操作 文章目录 第一节 SQL概述一、SQL的发展二、SQL的特点三、SQL的组成 第二节 MySQL预备知识一、MySQL使用基础二、MySQL中的SQL1、常量#xff08;1#xff09;字符串常量#xff08;2#xff09;数值常量#xff08;3#xff09;十六进制常量1字符串常量2数值常量3十六进制常量4日期时间常量5位字段值了解6布尔值7NULL值 2、变量3、运算符4、表达式5、内置函数 第三节 数据定义一、数据库模式定义1、创建数据库2、选择数据库3、修改数据库4、删除数据库5、查看数据库 二、表定义1、创建表2、添加字段3、修改字段4、删除字段5、重命名表6、删除表7、查看表8、查看表结构 三、索引定义1、索引的创建2、索引的查看3、索引的删除 第四节 数据更新一、插入数据二、删除数据三、修改数据 第五节 数据查询一、SELECT语句二、列的选择与指定1、选择指定的列2、定义并使用列的别名3、替换查询结果集中的数据4、计算列值5、聚合函数 三、FROM子句与多表连接查询1、交叉连接2、内连接3、外连接1左连接2右连接 四、WHERE子句与条件查询五、GROUP BY子句与分组数据六、HAVING子句七、ORDER BY子句八、LIMIT子句 第六节 视图一、创建视图二、删除视图三、修改视图定义四、查看视图定义五、更新视图数据六、查询视图数据 附上mydata.sql 第一节 SQL概述
一、SQL的发展
结构化查询语言Structured Query Language简称SQL是一种介于关系代数和关系演算之间的语言。由于具有语言简洁、方便实用、功能齐全等优点很快得到推广和应用。随着关系数据库的流行SQL语言最终发展成为关系数据库的标准语言和数据库领域中一个主流语言。SQL已经被ANSI美国国家标准化组织确定为数据库系统的工业标准它是数据库系统的通用语言。利用SQL用户可以用几乎相同的语句在不同的数据库系统上执行同样的操作。MySQL利用SQL对数据库进行操作此外SQL语言不区分大小写。
二、SQL的特点
1、SQL不是某个特定数据库供应商专有的语言。几乎所有重要的关系数据库管理系统都支持SQL所以掌握SQL可以帮助用户与几乎所有的关系数据库进行交互。 2、SQL简单易学。它的语句全都是由具有很强描述性的英语单词所组成而且这些单词的数目不多。
3、SQL尽管看上去很简单但它实际上是一种强有力的语言灵活使用其语言元素可以进行非常复杂和高级的数据库操作。
三、SQL的组成
1、数据定义语言Data Definition LanguageDDL
2、数据操纵语言Data Manipulation LanguageDML
3、数据控制语言Data Control LanguageDCL
4、嵌入式和动态SQL规则
5、SQL调用和会话规则
第二节 MySQL预备知识
一、MySQL使用基础
1、LAMPLinuxApache MySQLPHP/Perl/Python即使用Linux作为操作系统Apache作为Web服务器MySQL作为数据库管理系统PHP、Perl或Python语言作为服务器端脚本解释器。
2、WAMPWindowsApacheMySQLPHP/Perl/Python即使用Windows系统Apache作为Web服务器MySQL作为数据库管理系统PHP、Perl或Python语言作为服务器端脚本解释器。
二、MySQL中的SQL 图4.1 MySQL中的SQL对关系数据库模式的支持 1、常量
1字符串常量
字符串是指用单引号或双引号括起来的字符序列分为ASCII字符串常量一个字节和Unicode字符串常量两个字节。
ASCII例如‘hello’, ‘How are you!’
Unicode字符串常量与ASCII字符串常量相似但它前面有一个N标志例如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 Nhello;
-------
| hello |
-------
| hello |
-------
1 row in set (0.04 sec)mysql select Nhello;
1054 - Unknown column N in field list -- 使用双引号会报错
mysql 2数值常量
数值常量可以分为整数常量和浮点数常量。
整数常量即不带小数点的十进制数例如18942145345234-2147483648。
浮点数常量是使用小数点的数值常量例如5.26-1.39101.5E50.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十六进制常量
MySQL支持十六进制值。一个十六进制值通常指定为一个字符串常量每对十六进制数字被转换为一个字符其最前面有一个大写字母“X”或小写字母“x”。在引号中只可以使用数字“0”到“9”及字母“a”到“f”或“A”到“F”。例如X’41’表示大写字母A。x’4D7953514C’表示字符串MySQL。
mysql select X41;
-------
| X41 |
-------
| A |
-------
1 row in set (0.02 sec)mysql select X4D7953514C;
---------------
| X4D7953514C |
---------------
| MySQL |
---------------
1 row in set (0.03 sec)mysql 十六进制数值不区分大小写其前缀“X”或“x”可以被“0x”取代而且不用引号。及X’41’可以替换为0x41。
414*16^11*16^065 注意“0x”中x一定要小写。
十六进制值的默认类型是字符串。如果向要确保该值作为数字处理可以使用CAST…AS UNSIGNED。
4日期时间常量
日期时间常量用单引号将表示日期时间的字符串括起来构成。
日期型常量包括年、月、日数据类型为DATE表示为1999-06-17这样的值。
时间型常量包括小时数、分钟数、秒数及微秒数数据类型为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’符号写位字段值。value是一个用0和1写成的二进制值。直接显示b’value’的值可能是一系列特殊的符号。
例如b’0’显示位空白b’1’显示位一个图案。
mysql select b1;
------
| b1 |
------
| |
------
1 row in set (0.04 sec)mysql select b111;
--------
| b111 |
--------
| |
--------
1 row in set (0.04 sec)mysql 6布尔值
布尔值只包含两个可能的值TRUE和FALSE。FALSE的数字值为“0”TRUE的数字值为“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 7NULL值
NULL值可适用于各种列类型它通常用来表示“没有值”、“无数据”等意义并且不同于数字类型的“0”或字符串类型的空字符串。
mysql select NULL;
------
| NULL |
------
| NULL |
------
1 row in set (0.02 sec)mysql 2、变量
用户可以在表达式中使用自己定义的变量这样的变量叫作用户变量。
用户可以先在用户变量中保存值然后在以后引用它这样可以将值从一个语句传递到另一个语句在使用用户变量前必须定义和初始化。如果使用没有初始化的变量它的值为NULL。
用户变量前加****用来区分列名。
必须在系统变量前加两个。
mysql set a1;
Query OK, 0 rows affected (0.00 sec)mysql select 1;
------
| 1 |
------
| NULL |
------
1 row in set (0.03 sec)mysql select a1;
------
| a1 |
------
| 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、运算符
1算术运算符在两个表达式上执行数学运算这两个表达式可以是任何数字数据类型。算术运算符有加、-减、*乘、/除和%求模5种运算。
mysql select a;
----
| a |
----
| 1 |
----
1 row in set (0.01 sec)mysql select a5;
------
| a5 |
------
| 6 |
------
1 row in set (0.02 sec)-- 加减乘除都一样重点来看一下求模mysql select 7%3;
-----
| 7%3 |
-----
| 1 |
-----
1 row in set (0.04 sec)mysql 2位运算符有位与、|位或、^位异或、~位取反、位右移、位左移。
mysql select 51;
-----
| 51 |
-----
| 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 51;
------
| 51 |
------
| 2 |
------
1 row in set (0.06 sec)mysql select 51;
------
| 51 |
------
| 10 |
------
1 row in set (0.07 sec)mysql 3比较运算符又称关系运算符用于比较两个表达式的值其运算结果为逻辑值可以为三种之一1真、0假及NULL不能确定
运算符含义运算符含义等于小于等于大于、!不等于小于相等或都等于空大于等于
mysql select 51;
-----
| 51 |
-----
| 1 |
-----
1 row in set (0.02 sec)mysql select 51;
-----
| 51 |
-----
| 0 |
-----
1 row in set (0.03 sec)mysql select 51;
-----
| 51 |
-----
| 0 |
-----
1 row in set (0.04 sec)mysql select 5!1;
------
| 5!1 |
------
| 1 |
------
1 row in set (0.03 sec)mysql select 51;
------
| 51 |
------
| 1 |
------
1 row in set (0.04 sec)mysql 4逻辑运算符
运算符运算规则运算符运算规则NOT或!逻辑非OR或||逻辑或AND或逻辑与XOR逻辑异或
mysql select not (51);
------------
| not (51) |
------------
| 0 |
------------
1 row in set (0.02 sec)mysql select 51 68;
--------------
| 51 68 |
--------------
| 1 |
--------------
1 row in set (0.03 sec)mysql select 51 and 68;
---------------
| 51 and 68 |
---------------
| 1 |
---------------
1 row in set (0.04 sec)mysql select 51 and 68;
--------------
| 51 and 68 |
--------------
| 0 |
--------------
1 row in set (0.06 sec)mysql select 51 or 68;
-------------
| 51 or 68 |
-------------
| 1 |
-------------
1 row in set (0.06 sec)mysql select 51 xor 68;
--------------
| 51 xor 68 |
--------------
| 1 |
--------------
1 row in set (0.06 sec)mysql 4、表达式
表达式就是常量、变量、列名、复杂计算、运算符和函数的组合。一个表达式通常可以得到一个值。与常量和变量一样表达式的值也具有某种数据类型可能的数据类型有字符类型、数值类型、日期时间类型。这样根据表达式的值的类型表达式可分为字符型表达式、数值型表达式和日期表达式。
mysql select 55.1;
-------
| 55.1 |
-------
| 10.1 |
-------
1 row in set (0.02 sec)mysql select 5A;
-------
| 5A |
-------
| 5 |
-------
1 row in set (0.02 sec)mysql select bA;
---------
| bA |
---------
| 0 |
---------
1 row in set (0.03 sec)mysql select 2019-09-081;
----------------
| 2019-09-081 |
----------------
| 2020 |
----------------
1 row in set (0.06 sec)mysql select 2019-09-08 12:00:001;
-------------------------
| 2019-09-08 12:00:001 |
-------------------------
| 2020 |
-------------------------
1 row in set (0.04 sec)mysql 5、内置函数
例如
函数类型示例数学函数ABS()、SORT()聚合函数COUNT()字符串函数ASCI()、CHAR()日期和时间函数NOW()、YEAR()加密函数ENCODE()、ENCRYPT()控制流程函数IF()、IFNULL()格式化函数FORMAT()类型转换函数CAST()系统信息函数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(X41 as unsigned);
-------------------------
| cast(X41 as unsigned) |
-------------------------
| 65 |
-------------------------
1 row in set (0.02 sec)mysql select user();
----------------
| user() |
----------------
| rootlocalhost |
----------------
1 row in set (0.03 sec)mysql select version();
-----------
| version() |
-----------
| 5.7.26 |
-----------
1 row in set (0.04 sec)mysql 第三节 数据定义
一、数据库模式定义
1、创建数据库
语法格式
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_nme [DEFAULT] CHARACTER SET charset_name | [DEFAULT] COLLATE collation_name说明语句中“[ ]”内为可选项。
db_name数据库名
IF NO EXISTS在建数据库前进行判断只有该数据库目前尚不存在时才执行CREATE DATABASE操作。
DEFAULT指定默认值
CHARACTER SET指定数据库字符集Charset
charset_name字符集名称。
COLLATE指定字符集的校对规则collation_name为校对规则名。
mysql create database mydata;
Query OK, 1 row affected (0.01 sec)mysql create database mydata;
1007 - Cant create database mydata; database exists
mysql create database IF NOT EXISTS mydata;
Query OK, 1 row affected (0.00 sec)mysql 2、选择数据库
USE db_name;mysql use mydata;
Database changed
mysql 3、修改数据库
语法格式
ALTER {DATABASE | SCHEMA} [db_name] alter_specification [,alter_specification] ...其中alter_specification[DEFAULT] CHARACTER SET charset_name | [DEFAULT] COLLATE collation_name
4、删除数据库
语法格式
DROP DATABASE [IF EXISTS] db_namedb_name是要删除的数据库名。可以使用IF EXISTS子句以避免删除不存在的数据库时出现MySQL错误信息。
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、查看数据库
SHOW [DATABASE|SCHEMAS]mysql show databases;
--------------------
| Database |
--------------------
| information_schema |
| mysql |
| performance_schema |
| sys |
--------------------
4 rows in set (0.02 sec)mysql 二、表定义 #mermaid-svg-74UVFzQa4bCiWN4m {font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:16px;fill:#333;}#mermaid-svg-74UVFzQa4bCiWN4m .error-icon{fill:#552222;}#mermaid-svg-74UVFzQa4bCiWN4m .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-74UVFzQa4bCiWN4m .edge-thickness-normal{stroke-width:2px;}#mermaid-svg-74UVFzQa4bCiWN4m .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-74UVFzQa4bCiWN4m .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-74UVFzQa4bCiWN4m .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-74UVFzQa4bCiWN4m .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-74UVFzQa4bCiWN4m .marker{fill:#333333;stroke:#333333;}#mermaid-svg-74UVFzQa4bCiWN4m .marker.cross{stroke:#333333;}#mermaid-svg-74UVFzQa4bCiWN4m svg{font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-74UVFzQa4bCiWN4m .label{font-family:"trebuchet ms",verdana,arial,sans-serif;color:#333;}#mermaid-svg-74UVFzQa4bCiWN4m .cluster-label text{fill:#333;}#mermaid-svg-74UVFzQa4bCiWN4m .cluster-label span{color:#333;}#mermaid-svg-74UVFzQa4bCiWN4m .label text,#mermaid-svg-74UVFzQa4bCiWN4m span{fill:#333;color:#333;}#mermaid-svg-74UVFzQa4bCiWN4m .node rect,#mermaid-svg-74UVFzQa4bCiWN4m .node circle,#mermaid-svg-74UVFzQa4bCiWN4m .node ellipse,#mermaid-svg-74UVFzQa4bCiWN4m .node polygon,#mermaid-svg-74UVFzQa4bCiWN4m .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-74UVFzQa4bCiWN4m .node .label{text-align:center;}#mermaid-svg-74UVFzQa4bCiWN4m .node.clickable{cursor:pointer;}#mermaid-svg-74UVFzQa4bCiWN4m .arrowheadPath{fill:#333333;}#mermaid-svg-74UVFzQa4bCiWN4m .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-74UVFzQa4bCiWN4m .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-74UVFzQa4bCiWN4m .edgeLabel{background-color:#e8e8e8;text-align:center;}#mermaid-svg-74UVFzQa4bCiWN4m .edgeLabel rect{opacity:0.5;background-color:#e8e8e8;fill:#e8e8e8;}#mermaid-svg-74UVFzQa4bCiWN4m .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-74UVFzQa4bCiWN4m .cluster text{fill:#333;}#mermaid-svg-74UVFzQa4bCiWN4m .cluster span{color:#333;}#mermaid-svg-74UVFzQa4bCiWN4m div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-74UVFzQa4bCiWN4m :root{--mermaid-font-family:"trebuchet ms",verdana,arial,sans-serif;} 数值类型 整数类型 浮点类型 INT FLOAT DOUBLE DECIMAL 日期和时间类型 日期类型 日期时间类型 时间戳类型 DATE YYYY-MM-DD YEAR DATETIME TIME TIMESTAMP 字符串类型 固定长度类型 可变长度类型 文本类型 CHAR 最大长度255 VARCHAR 最大长度65535 TEXT 1、创建表
CREATE TABLE [IF NOT EXISTS] tbl_name(字段名1 数据类型[列级完整性约束条件][默认值][字段名2 数据类型[列级完整性约束条件][默认值]][......][表级完整性约束])[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);进行创建mysql 2、添加字段
ALTER TABLE tb_name ADD [COLUMN] 新字段名 数据类型 [约束条件] [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] 原字段 新字段名 数据类型 [约束条件]ALTER TABLE tb_name ALTER [COLUMN] 字段名 {SET|DROP} DEFUALT;ALTER TABLE tb_name MODIFY [COLUMN] 字段名 数据类型 [约束条件] [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、查看表结构
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 三、索引定义
可以理解成目录在查找时有目录查找速度会加快。 #mermaid-svg-a3Fbj7ip8FpWnIRd {font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:16px;fill:#333;}#mermaid-svg-a3Fbj7ip8FpWnIRd .error-icon{fill:#552222;}#mermaid-svg-a3Fbj7ip8FpWnIRd .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-a3Fbj7ip8FpWnIRd .edge-thickness-normal{stroke-width:2px;}#mermaid-svg-a3Fbj7ip8FpWnIRd .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-a3Fbj7ip8FpWnIRd .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-a3Fbj7ip8FpWnIRd .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-a3Fbj7ip8FpWnIRd .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-a3Fbj7ip8FpWnIRd .marker{fill:#333333;stroke:#333333;}#mermaid-svg-a3Fbj7ip8FpWnIRd .marker.cross{stroke:#333333;}#mermaid-svg-a3Fbj7ip8FpWnIRd svg{font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-a3Fbj7ip8FpWnIRd .label{font-family:"trebuchet ms",verdana,arial,sans-serif;color:#333;}#mermaid-svg-a3Fbj7ip8FpWnIRd .cluster-label text{fill:#333;}#mermaid-svg-a3Fbj7ip8FpWnIRd .cluster-label span{color:#333;}#mermaid-svg-a3Fbj7ip8FpWnIRd .label text,#mermaid-svg-a3Fbj7ip8FpWnIRd span{fill:#333;color:#333;}#mermaid-svg-a3Fbj7ip8FpWnIRd .node rect,#mermaid-svg-a3Fbj7ip8FpWnIRd .node circle,#mermaid-svg-a3Fbj7ip8FpWnIRd .node ellipse,#mermaid-svg-a3Fbj7ip8FpWnIRd .node polygon,#mermaid-svg-a3Fbj7ip8FpWnIRd .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-a3Fbj7ip8FpWnIRd .node .label{text-align:center;}#mermaid-svg-a3Fbj7ip8FpWnIRd .node.clickable{cursor:pointer;}#mermaid-svg-a3Fbj7ip8FpWnIRd .arrowheadPath{fill:#333333;}#mermaid-svg-a3Fbj7ip8FpWnIRd .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-a3Fbj7ip8FpWnIRd .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-a3Fbj7ip8FpWnIRd .edgeLabel{background-color:#e8e8e8;text-align:center;}#mermaid-svg-a3Fbj7ip8FpWnIRd .edgeLabel rect{opacity:0.5;background-color:#e8e8e8;fill:#e8e8e8;}#mermaid-svg-a3Fbj7ip8FpWnIRd .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-a3Fbj7ip8FpWnIRd .cluster text{fill:#333;}#mermaid-svg-a3Fbj7ip8FpWnIRd .cluster span{color:#333;}#mermaid-svg-a3Fbj7ip8FpWnIRd div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-a3Fbj7ip8FpWnIRd :root{--mermaid-font-family:"trebuchet ms",verdana,arial,sans-serif;} 索引 用途划分 普通索引 INDEX 唯一索引 UNIQUE 主键 PRIMARY KEY 全文索引 FULLTEXT 引擎MyISAM 聚簇索引 引擎InnoDB 列级索引 单列索引 组合索引 1、索引的创建
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 -- 通过创建表时创建索引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_namemysql 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 第四节 数据更新
一、插入数据
-- 重点
INSERT语法格式
INSERT INTO tb_namecolumn_list
VALUESvalue_list1[,(value_list2)]
从数据查询中插入数据
INSERT INTO tb_namel(column_listl)
SELECT (column_list2)
FROM tb_name2 WHEREcondition
注意column_list1与clolumn_list2必须个数相同且数据类型一一对应使用INSERT...SET语句插入部分列值数据
INSERT [INTO] tbl-name
SET col_name{expr | DEFAULT},***使用INSERT...SELECT语句插入子查询数据
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- Sno002,- Sage19,- Sbirthday2002-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 二、删除数据
DELETE FROM tb_name [WHERE CONDITION]; 删除所以记录可以使用
TRUNCATE [TABLE] tb_name 注意TRUNCATE是删除整个表重新建一个表在删除全部数据时候效率高于DELETEDELETE是逐条删除。TRUNCATE无法恢复AUTO_INCREMENT计数器重置。
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 sage18;
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- (语文),- (数学),- (英语),- (政治);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0mysql select * from course;
-----------
| id | cname |
-----------
| 1 | 语文 |
| 2 | 数学 |
| 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-- 然后新增两条记录发现id是从5、6开始的
mysql insert into course (cname) values- (语文),- (数学);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0mysql select * from course;
-----------
| id | cname |
-----------
| 5 | 语文 |
| 6 | 数学 |
-----------
2 rows in set (0.04 sec)-- 用truncate计数器重置mysql truncate course;
Query OK, 0 rows affected (0.00 sec)mysql select * from course;
Empty setmysql insert into course (cname) values- (语文),- (数学);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0mysql select * from course;
-----------
| id | cname |
-----------
| 1 | 语文 |
| 2 | 数学 |
-----------
2 rows in set (0.04 sec)mysql 三、修改数据
UPDATE tbl_name SET col_name1expr1 [, col_name2expr2 ...][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 sage20 where Sno004;
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 第五节 数据查询
一、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返回的列或表达式是FROM从中检索数据的表仅在从表选择数据时使用WHERE行级过滤否GROUP BY分组说明仅在从表选择数据时使用HAVING组级说明否ORDER BY输出排序顺序否LIMIT要检索的行数否
二、列的选择与指定
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 学号,- sage as 年龄,- sbirthday as 出生日期- from student;
---------------------------------
| 学号 | 年龄 | 出生日期 |
---------------------------------
| 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、替换查询结果集中的数据
CASE
WHEN 条件1 THEN 表达式1WHEN 条件2 THEN 表达式2...
ELSE 表达式
END[AS] column_alias4、计算列值
使用SELECT语句对列进行查询时在结果集中可以输出对列值计算后的值其具体使用方法是将SELECT语句的语法项“select_expr”指定为对应列参与计算的表达式。
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、聚合函数
表4.3 MySQL中常用聚合函数表 函数名说明COUNT求组中项数返回INT类型整数MAX求最大值MIN求最小值SUM返回表达式中所有值的和AVG求组中值的平均值STD或STDDEV返回给定表达式中所有值得标准值VARLANCE返回给定表达式中所有值得方差GROUP_CONCAT返回由属于一组的列值连接组合而成的结果BIT_AND逻辑或BIR_OR逻辑与BIT_XOR逻辑异或
-- 销售数量超过150的条数
mysql select count(*) from rec where num150;
----------
| 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版本之后对group by进行了优化。默认启动改进之后的版本启动了ONLY_FULL_GROUP_BY模式。即ONLY_FULL_GROUP_BY是MySQL数据库提供的一个sql_mode通过这个sql_mode来保证SQL语句“分组求最值”合法性的检查。这种模式采用了与Oracle、DB2等数据库的处理方式。即不允许select target list中出现语义不明确的列。
-- 解决方式只要有聚合函数sum()count()max()avg()等函数就需要用到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、内连接
FROM table1 [INNER] JOIN table2 ON 连接条件等值连接
FROM table1, [table2] ......
WHERE table1.字段 比较运算符 table2.字段-- 准备数据↓↓↓↓↓
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,产品1),- (002,产品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 | 产品1 |
| 2 | 002 | 产品2 |
-----------------
2 rows in set (0.04 sec)-- 准备数据完成↑↑↑↑↑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.gnumrec.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.gnumrec.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.gnumrec.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.gnumrec.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)-- 这个时候发现004没有也就是说左连接的时候在右里面有和它对应的就到一起否则直接剔除mysql select * from gong right join rec on gong.gnumrec.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比较运算表达式确定范围DETWEEN AND, NOT BETWEEN AND确定集合IN, NOT IN字符匹配LIKE, NOT LIKE空值IS NULL, IS NOT NULL多重条件AND, OR
mysql select * from rec where num200;
--------------------------
| 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,产品3),(004,产品4);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0-- 查卖出去的mysql select * from pro;
-----------------
| id | pnum | pname |
-----------------
| 1 | 001 | 产品1 |
| 2 | 002 | 产品2 |
| 3 | 003 | 产品3 |
| 4 | 004 | 产品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 | 产品1 |
| 2 | 002 | 产品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 | 产品3 |
| 4 | 004 | 产品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-- 我们在这个表中找姓“赵”的都有谁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 gnum007;
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[但必须只有一列])比较运算符
WHERE 列名 比较运算符 (值或者子SELECT[但必须只有一值])EXIST查询
WHERE EXIST (子SELECT)五、GROUP BY子句与分组数据
语法
[GROUP BY 字段列表][HAVING条件表达式]含义按照某一列的值相同的分成一组进行聚合计算
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子句分组数据之外还可以使用HAVING子句来过滤分组即在结果集中规定包含哪些分组和排除哪些分组。
-- 销售额超过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 [位置偏移量,] 行数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 第六节 视图
视图是从一个或多个表或视图导出的表。视图是数据库的用户使用数据库的观点。例如对于一个学校其学生的情况存于数据库的一个或多个表中而作为学校的不同职能部门所关心的学生数据的内容是不同的。视图是一张虚表。视图一经定义以后就可以像表一样被查询、修改、删除和更新。
一、创建视图
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.gnumrec.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_namemysql show create view v_rec_gong;
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| View | Create View | character_set_client | collation_connection |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| v_rec_gong | CREATE ALGORITHMUNDEFINED DEFINERrootlocalhost 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 五、更新视图数据
要通过视图更新基本表数据必须保证视图是可更新视图即可以在INSERTUPDATE或DELETE等语句当中使用它们。对于可更新的视图在视图中的行和基表中的行之间必须具有一对一的关系。还有一些特定的其他结构这类结构会使得视图不可更新。
强烈不建议更新视图更新视图风险性非常大更新不好的话影响到基本表要用基本表取更新视图。
六、查询视图数据
视图一经定义后就可以如同查询数据库中的真实表一样对视图进行数据查询检索这也是对视图使用最多的一种操作。
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, 数学);-- ----------------------------
-- 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.gnumrec.gno ;SET FOREIGN_KEY_CHECKS 1; 文章转载自： http://www.morning.lkgqb.cn.gov.cn.lkgqb.cn http://www.morning.khdw.cn.gov.cn.khdw.cn http://www.morning.prysb.cn.gov.cn.prysb.cn http://www.morning.hmmnb.cn.gov.cn.hmmnb.cn http://www.morning.nnpfz.cn.gov.cn.nnpfz.cn http://www.morning.jwskq.cn.gov.cn.jwskq.cn http://www.morning.rdsst.cn.gov.cn.rdsst.cn http://www.morning.tmfm.cn.gov.cn.tmfm.cn http://www.morning.tdttz.cn.gov.cn.tdttz.cn http://www.morning.kdlzz.cn.gov.cn.kdlzz.cn http://www.morning.nkdmd.cn.gov.cn.nkdmd.cn http://www.morning.qhfdl.cn.gov.cn.qhfdl.cn http://www.morning.ychrn.cn.gov.cn.ychrn.cn http://www.morning.brwnd.cn.gov.cn.brwnd.cn http://www.morning.xysxj.com.gov.cn.xysxj.com http://www.morning.ljsxg.cn.gov.cn.ljsxg.cn http://www.morning.zfkxj.cn.gov.cn.zfkxj.cn http://www.morning.dshkp.cn.gov.cn.dshkp.cn http://www.morning.pqwrg.cn.gov.cn.pqwrg.cn http://www.morning.gbxxh.cn.gov.cn.gbxxh.cn http://www.morning.sjsks.cn.gov.cn.sjsks.cn http://www.morning.ldcrh.cn.gov.cn.ldcrh.cn http://www.morning.zmbzl.cn.gov.cn.zmbzl.cn http://www.morning.nwrzf.cn.gov.cn.nwrzf.cn http://www.morning.ktnt.cn.gov.cn.ktnt.cn http://www.morning.qmbpy.cn.gov.cn.qmbpy.cn http://www.morning.ncfky.cn.gov.cn.ncfky.cn http://www.morning.jqswf.cn.gov.cn.jqswf.cn http://www.morning.fxjnn.cn.gov.cn.fxjnn.cn http://www.morning.swwpl.cn.gov.cn.swwpl.cn http://www.morning.prprj.cn.gov.cn.prprj.cn http://www.morning.zqcdl.cn.gov.cn.zqcdl.cn http://www.morning.hqjtp.cn.gov.cn.hqjtp.cn http://www.morning.amlutsp.cn.gov.cn.amlutsp.cn http://www.morning.xqxrm.cn.gov.cn.xqxrm.cn http://www.morning.drhnj.cn.gov.cn.drhnj.cn http://www.morning.sffwz.cn.gov.cn.sffwz.cn http://www.morning.rlbfp.cn.gov.cn.rlbfp.cn http://www.morning.rxlk.cn.gov.cn.rxlk.cn http://www.morning.zdydj.cn.gov.cn.zdydj.cn http://www.morning.zyrcf.cn.gov.cn.zyrcf.cn http://www.morning.npmcf.cn.gov.cn.npmcf.cn http://www.morning.rxnr.cn.gov.cn.rxnr.cn http://www.morning.osshjj.cn.gov.cn.osshjj.cn http://www.morning.svrud.cn.gov.cn.svrud.cn http://www.morning.yfmwg.cn.gov.cn.yfmwg.cn http://www.morning.slnz.cn.gov.cn.slnz.cn http://www.morning.drcnf.cn.gov.cn.drcnf.cn http://www.morning.fbzdn.cn.gov.cn.fbzdn.cn http://www.morning.mcmpq.cn.gov.cn.mcmpq.cn http://www.morning.lyldhg.cn.gov.cn.lyldhg.cn http://www.morning.mcpdn.cn.gov.cn.mcpdn.cn http://www.morning.xczyj.cn.gov.cn.xczyj.cn http://www.morning.jrpmf.cn.gov.cn.jrpmf.cn http://www.morning.bpmz.cn.gov.cn.bpmz.cn http://www.morning.trhrk.cn.gov.cn.trhrk.cn http://www.morning.rmpfh.cn.gov.cn.rmpfh.cn http://www.morning.brkc.cn.gov.cn.brkc.cn http://www.morning.zfcfk.cn.gov.cn.zfcfk.cn http://www.morning.wdxr.cn.gov.cn.wdxr.cn http://www.morning.kgslc.cn.gov.cn.kgslc.cn http://www.morning.dljujia.com.gov.cn.dljujia.com http://www.morning.dxpqd.cn.gov.cn.dxpqd.cn http://www.morning.zcqtr.cn.gov.cn.zcqtr.cn http://www.morning.nwmwp.cn.gov.cn.nwmwp.cn http://www.morning.sxbgc.cn.gov.cn.sxbgc.cn http://www.morning.rrcrs.cn.gov.cn.rrcrs.cn http://www.morning.hqjtp.cn.gov.cn.hqjtp.cn http://www.morning.wjlkz.cn.gov.cn.wjlkz.cn http://www.morning.rbsxf.cn.gov.cn.rbsxf.cn http://www.morning.zxznh.cn.gov.cn.zxznh.cn http://www.morning.lhgkr.cn.gov.cn.lhgkr.cn http://www.morning.tpps.cn.gov.cn.tpps.cn http://www.morning.xsymm.cn.gov.cn.xsymm.cn http://www.morning.ydzly.cn.gov.cn.ydzly.cn http://www.morning.wnqbf.cn.gov.cn.wnqbf.cn http://www.morning.jfqpc.cn.gov.cn.jfqpc.cn http://www.morning.mlcnh.cn.gov.cn.mlcnh.cn http://www.morning.tturfsoc.com.gov.cn.tturfsoc.com http://www.morning.wtrjq.cn.gov.cn.wtrjq.cn
