MYSQL ナレッジポイントの要約と共有

この記事では主に MYSQL の知識ポイントの概要を共有し、mysql データベースをより良く習得して使用するのに役立つことを願っています。

データベースの概要

データベース: データベース、大容量のストレージと効率的なアクセスを備え、データを永続的に保存するために使用されるソフトウェア。
データベース ソフトウェアの種類:

<span style="font-size: 14px;">(1)网状数据库<br>(2)树形/层次型数据库<br>(3)关系型数据库(Relational DB)<br> (4)非关系型数据库(NoSQL)<br></span>

RDBMS (RDB Management System) の展開構造:
(1) サーバー側: データの保存/管理を担当します。データはバイナリ形式で保存され、人間が直接見ることはできません。銀行本店の産業および商業データベース サーバーとして
(2) クライアント: サーバーに接続し、追加、削除、変更、クエリ命令をサーバーに送信する責任を負います (ATM マシンなど)

RDBMS の論理構造サーバー側データ:
Server=>Database=> ;Table=>Row=>Column

mysqld、httpd、ftpd、sshd、smbd....
デーモン: elf、guardian、daemon、elfプログラム、サーバープログラム

MySQLシステム 使用

(1) サーバー側: MySQLサーバーソフトウェアをダウンロードしてインストール

<span style="font-size: 14px;">mysql.com<br>mariadb.org<br>xampp.org = Apache+MySQL+PHP<br></span>

(2) サーバー側: MySQLサーバーソフトウェアを起動

<span style="font-size: 14px;">c:/xampp/mysql/bin/mysqld.exe<br>保证3306端口被打开<br></span>

= ============= ==============
(3) クライアント: MySQL クライアント ソフトウェアをダウンロードしてインストールします

<span style="font-size: 14px;">c:/xampp/mysql/bin/mysql.exe<br>作用相当于银行的ATM终端客户机<br></span>

(4) クライアント:データベース サーバーにログインするためのユーザー名とパスワードを入力します

<span style="font-size: 14px;">mysql.exe   -uroot   -p       不能加分号！<br>mysql   -uroot              不能加分号！<br></span>

MySQL サーバーでよく使用される管理コマンドに移動します

ヒント: すべての管理コマンドはセミコロンで終わる必要があります。使用して終了する以外は！
(1) 終了します。
(2) データベースを表示します。
(3) 指定したデータベースを入力/使用します。現在のデータベースにどのようなテーブルがあるのか​​
(5)desc テーブル名; 指定したテーブルの列を記述します (テーブルヘッダーを記述します)

よく使われる SQL コマンド

SQL: 構造化クエリ言語、構造化クエリ言語、はプログラミング言語です。これは最初に IBM によって提案され、その後リレーショナル データベース業界の国際標準として ISO によって採用され、SQL87、SQL92、SQL99 などの複数のバージョンが現在では主要なデータベース メーカーによってサポートされています。

SQL文の実行方法: (1) 対話モード: 1行入力して1行実行…データの一時的な閲覧に適しています。 Mysql -uroot Enter
(2) スクリプト モード: 実行する複数のコマンドをテキスト ファイルに記述し、一度に実行するためにサーバーに送信します。複数のステートメントをバッチで繰り返し実行するのに適しています。 Mysql -uroot < d:/xx.sql

SQL 言語構文を入力します: (1) すべての SQL ステートメントはセミコロンで終わる必要があります。
(2)SQL ステートメントでは大文字と小文字が区別されません。慣例により、システムで事前定義されたキーワードはすべて大文字で、キーワード以外のキーワードは小文字で表記されます。
(3) SQL ステートメントでは単一行コメント (#...) と複数行コメント (/
.../) を使用できます

DROP DATABASE IF EXISTS ライブラリ名;CREATE DATABASE ライブラリ名CHARSET=UTF8;
USE ライブラリ名;
CREATE TABLE テーブル名 (列名の型, 列名の型, ....);
INSERT INTO テーブル名 VALUES (値, 値, ....);
SELECT * FROMテーブル名;

データベースの文字化け問題の理由

: コンピューターは各文字に一意の番号を割り当てます。文字を格納するときのエンコード方式と、文字を取り出すときのエンコード方式が異なると文字化けが発生します。

<span style="font-size: 14px;">a <=> 97<br>   b <=> 98<br>....<br></span></p>
<p>文字エンコーディング スキーム/文字セット: レンダリングする必要がある各文字に一意の数値エンコーディングを割り当てます。世界中で一般的に使用されている文字セットがいくつかあります: <span style="font-size: 14px;"> (1) ASCII 文字セット: すべての英語文字 (128) のみをエンコードします <br> (2) GB2312/GBK: 一般的に使用される英語文字と中国語簡体字の両方をエンコードします コード (40,000 以上) ) <br> (3) BIG5: 一般的に使用される英語文字と繁体字中国語文字がエンコードされます <br> (4) Unicode 文字セット: 一般的に使用される英語文字、簡体字中国語文字、繁体字中国語文字、日本語、および韓国語 ..主流の言語で一般的な記号。すべてエンコードされており、特定のストレージは UTF-8/UTF-16/UTF-32 の 3 つのストレージ ソリューションに分割されています<br></span></p>
<p> 文字化けの問題を解決する方法 - 「3 つの場所を統一」するため:<span style="font-size: 14px;">( 1) .sql ファイルのストレージ エンコーディング<br>(2) mysql.exe が mysqld.exe に接続するために使用するエンコーディング<br>(3) mysqld.exe にデータを保存するために使用されるエンコーディング<br></span></p>
<h2 id="span-style-font-size-px-MySQL服务器端数据的逻辑结构-span"><span style="font-size: 14px;">MySQL服务器端数据的逻辑结构：</span></h2>
<p><span style="font-size: 14px;">SERVER=>DATABASE=>TABLE=>ROW=>COLUMN<br>如何连接到数据库服务器：<br>  交互模式：mysql.exe  -uroot  -p<br>  脚本模式：mysql.exe  -uroot  -p  <  d:/2.sql</span></p><h2 id="span-style-font-size-px-常用的SQL语句-span"><span style="font-size: 14px;">常用的SQL语句：</span></h2><p><span style="font-size: 14px;">(1)增：INSERT INTO 表 VALUES(值, 值, ...);<br/>  (2)删：DELETE  FROM  表;<br/>  (3)改：UPDATE 表 SET 列=值, ...,列=值 ;<br/>  (4)查：SELECT  *  FROM 表;</span></p><p><span style="font-size: 14px;">DELETE FROM laptop;   #删除所有的记录行<br/>DELETE FROM laptop WHERE  lid=10;   #删除满足条件的记录行</span></p><p><span style="font-size: 14px;">UPDATE laptop <br/>SET  price=&#39;3000&#39;,pic=&#39;img/50.jpg&#39;,isOnsale=&#39;否&#39;; #更新所有的记录行<br/>UPDATE laptop <br/>SET  price=&#39;3000&#39;,pic=&#39;img/50.jpg&#39;,isOnsale=&#39;否&#39;<br/>WHERE  lid=31;    #更新满足条件的行</span></p><h2 id="span-style-font-size-px-MySQL中的列类型-span"><span style="font-size: 14px;">MySQL中的列类型</span></h2><p><span style="font-size: 14px;">(1)数值类型   —— 可用引号括起来也可以不用</span></p><pre class="brush:php;toolbar:false"><span style="font-size: 14px;">整数数值类型：   student(  age TINYINT  )<br/>  TINYINT：微整数，占1字节，-128~127<br/>  SMALLINT：小整数，占2字节，-32768~32767<br/>  INT：整数，占4个字节, -2147483648~2147483647<br/>  BIGINT：大整数，占8个字节，.....<br/>小数数值类型：  product( price DECIMAL(7, 4) )<br/>  FLOAT(M,D)：单精度浮点型，占4字节，3.4E38，计算时可能产生四舍五入<br/>  DOUBLE(M,D)：双精度浮点型，占8字节 1.8E30，计算时可能产生四舍五入<br/>  DECIMAL(M,D)：定点小数，不会产生精度舍入<br/>布尔数值类型：   product( isOnsale BOOL)<br/>  BOOL，布尔/真假类型只能取值为TRUE/FALSE。注意：<br/>MySQL数据库中没有真正意义上的布尔类型，TRUE等同于1，FALSE等同于0<br/></span>

(2)日期时间类型 —— 必须用引号括起来

<span style="font-size: 14px;">DATE：日期类型，形如&#39;2017-5-10&#39;<br/>TIME：时间类型，形如&#39;22:08:5&#39;   <br/>DATETIME：日期/时间类型，形如&#39;2017-10-25 22:8:5&#39;<br/></span>

(3)字符串类型 —— 必须用引号括起来 emp(resume ...)

<span style="font-size: 14px;">CHAR(M)：定长字符串，比VARCHAR操作速度更快，M不能超过255<br/>VARCHAR(M)：变长字符串，比CHAR更能节约空间，M不能超过65535<br/>TEXT(M)：大型变长字符串，M不能超过2G<br/>ename CHAR(11)    ename VARCHAR(11)<br/></span>

a a000 a0
ab ab00 ab0
abc abc0 abc0
abcd abcd abcd
abcde abcd abcd
一二三四 一二三四 一二三四
一二三四五 一二三四 一二三四

true 真 ture x
false 假 flase x

MySQL中的列约束

Constraint：约束，数据库中某列上的数据往往必须符合某种规范，如编号不能重复、年龄必须在一定范围、密码有长度限制、员工所在部门必须真的存在......类似的限制/规范就称为“列约束”
(1)主键约束 —— PRIMARY KEY

<span style="font-size: 14px;"> 声明为主键的列上，不能出现重复值，也不能出现NULL值，所有的记录会自动按照主键列上值由小到大排序 —— 因此一个表中至多只能有一个主键列。<br/></span>

(2)非空约束 —— NOT NULL

<span style="font-size: 14px;">声明为非空的列，不能出现NULL，但可以出现重复值。<br/></span>

(3)唯一约束 —— UNIQUE

<span style="font-size: 14px;">声明为唯一约束的列，不能出现重复的值，但可以出现NULL，且允许多个NULL出现（两个NULL值是不等的）<br/></span>

(4)检查约束 —— CHECK

<span style="font-size: 14px;">检查约束可以检查新插入的数据是否满足指定的条件，如：<br/>student( age INT CHECK(age>=18  AND  age<=60)  )<br>MySQL不支持此约束！<br></span></p>
<p><span style="font-size: 14px;">(5)默认值约束 —— DEFAULT</span></p>
<pre class="brush:php;toolbar:false"><span style="font-size: 14px;">student(sid INT, sex CHAR(1) DEFAULT '男' );  <br>使用默认值的方式<br>1)INSERT INTO student VALUES(10, DEFAULT);<br> 2)INSERT INTO student(sid) VALUES(20);<br></span>

(6)外键约束 —— FOREIGN KEY...REFERENCES

<span style="font-size: 14px;">   外键列上可以出现NULL，也可以有重复值，但是必须保证“<br>所有出现的值在另一个表的主键列上存在”——外键列上的值“参考了”另一个表上的主键值。<br></span>

面试题：数据库中主键约束 和 唯一且非空组合 约束有何区别？
PRIMARY KEY：是表中记录的排序依据，故一个表至多有一个
UNIQUE NOT NULL：不会排序，故一个表可以有多个

程序中的NULL/空值的含义：表示应该有一个这样的数据，但是暂时还没有确定值是什么，如新员工的部门编号(尚未确定)、尚未确定的部门经理、尚未发到手的年终奖

项目中如何存储日期时间数据

大体有三种方式
 (1)VARCHAR存储：不足：不便于比较大小，格式不灵活
 (2)DATE/TIME/DATETIME存储：不足：不便于实现国际化，不同的编程语言支持程度不同
 (3)BIGINT存储：表示距离计算机元年的毫秒值，任何编程语言都可以把大数字转换为日期时间

中国：2017-10-25
美国：10-25-2017
欧洲：25/10/2017
i18n：internationalization，国际化，实现了国际化的项目应该对中国人显示中国人的习惯格式，对美国人显示美国人的习惯格式.......
计算机中如何存储日期时间：一个很大的数字，表示目标日期距离“计算机元年(1970-1-1 0:0:0 GMT)”经过了多少毫秒:
数字    代表的时间
0    1970-1-1 0:0:0
1000    1970-1-1 0:0:1
-1000    1969-12-31 23:59:59
1000*60    1970-1-1 0:1:0
10006060    1970-1-1 1:0:0
10006060*24    1970-1-2 0:0:0
1000606024365    1971-1-1 0:0:0

MySQL中使用自增列

id INT PRIMARY KEY AUTO_INCREMENT
 自增列：只能用于整数列，且必须是主键列。自增列无需手工赋值，会自动采用1/2/3....数列，在当前最大值基础上+1。
 注意：SQL标准中没有此关键字，它是MySQL所专有的！

1.简单查询 —— 只查询特定的列
 示例：查询出所有员工的姓名、工资、和编号

<span style="font-size: 14px;">SELECT ename, salary, eid  FROM emp;<br></span>

练习：查询出所有的员工姓名、性别、生日、姓名

<span style="font-size: 14px;">SELECT ename, sex, birthday, ename FROM emp;<br></span>

2.简单查询 —— 查询所有的列
 示例：查询员工的所有信息
 SELECT  *  FROM  emp;

3.简单查询 —— 给列取别名
 示例：查询出员工姓名，所在部门编号，要求列名用中文呈现

<span style="font-size: 14px;"> SELECT  ename AS 姓名, deptId `部门 编号`  FROM emp;<br></span>

注意：给列取别名用AS关键字，且可以省略；别名中若有空格，需要用括起来。

4.简单查询 —— 只显示不同的记录
 示例：显示出哪些部门编号下有员工

<span style="font-size: 14px;">SELECT  DISTINCT deptId <br>FROM emp;<br></span>

说明： distinct：不同的
 练习：查询出公司中有哪些性别的员工

<span style="font-size: 14px;">SELECT  DISTINCT  sex    FROM emp;<br></span>

5.简单查询 —— 在查询时执行计算
 示例：计算2/3的商

<span style="font-size: 14px;">SELECT  2/3;<br></span>

示例：查询出每个员工的姓名及其年薪

<span style="font-size: 14px;">SELECT  ename, salary,  salary*12<br>FROM emp;<br></span>

6.简单查询 —— 查询结果集的排序
 示例：查询出所有员工信息，按工资由小到大排列

<span style="font-size: 14px;">SELECT  * <br>FROM emp<br>ORDER  BY  salary ;    #ASC，ascendant 升序<br></span>

示例：查询出所有员工信息，按工资由大到小排列

<span style="font-size: 14px;">SELECT  * <br>FROM emp<br>ORDER  BY  salary  DESC;     #descendant 降序<br></span>

7.简单查询 —— 条件查询
 示例：查询出编号为5的员工所有信息

<span style="font-size: 14px;">SELECT  *  FROM emp<br>WHERE  eid=5 ;<br></span>

8.简单查询 —— 模糊条件查询
 示例：查询出姓名中包含字母E的员工所有信息

<span style="font-size: 14px;">SELECT  *  FROM  emp<br>WHERE  ename  LIKE  '%E%';<br>#WHERE  ename='%E%'; #错误写法！<br></span>

SQL通配符：  下面两个通配符必须与LIKE组合应用
%   匹配任意多个任意字符    
_    匹配一个任意字符

9.简单查询 —— 分页查询
 分页查询：若数据库中的满足条件的记录行数太多，一般会采取“一页一页”的方式逐步展示给用户。
 不同的数据库实现分页查询语法各不相同，MySQL中的分页查询时最简单的！形如：

<span style="font-size: 14px;">SELECT  ...<br>FROM ...<br>WHERE  ...<br>ORDER BY ...<br>LIMIT  start, count ;<br></span>

start：从哪一行开始读取数据，数据库中的第一行记录称为第0行
 count：一次最多可以读取的行数
 假设：每一页最多呈现6条记录(称为“页面大小”pageSize)
 第1页： SELECT ....  LIMIT  0, 6 ;
 第2页： SELECT ....  LIMIT  6, 6 ;
 第3页： SELECT ....  LIMIT  12, 6 ;
 第4页： SELECT ....  LIMIT  18, 6 ;
 ....
 第n页： SELECT ....  LIMIT  (n-1)*pageSize,  pageSize ;

复杂查询 —— 聚合/分组查询

函数：一个可以接收若干数据，加以处理，输出特定数据的功能体 —— 饺子机
MySQL提供的函数： COUNT()、SUM()、AVG()、MAX()、MIN() ——  聚合函数
示例：查询出所有员工的总数量

<span style="font-size: 14px;">SELECT  COUNT(eid) AS 编号数量  FROM emp;  #15<br>SELECT  COUNT(deptId)  FROM emp;            #14<br>SELECT  COUNT(*)  FROM emp;                #15<br></span>

示例：查询出每个部门的编号以及该部门的员工数量(先分组再聚合计算)

<span style="font-size: 14px;">SELECT  deptId, COUNT(*) FROM  emp<br>GROUP  BY  deptId;<br></span>

注意：分组查询的结果集中只能包含两种列：
(1)分组条件列
(2)其他列的聚合函数
SELECT  deptId, COUNT(ename), ename FROM  emp
GROUP  BY  deptId;   #错误写法

复杂查询 —— 子查询

子查询：在一条语句(增删改查)中又嵌入了一条SELECT语句
 示例：查询出“研发部”所有员工的信息
 步骤1：到部门表查询出研发部对应的部门编号，如10

<span style="font-size: 14px;">SELECT  did  FROM  dept <br>WHERE  dname='研发部';<br></span>

步骤2：到员工表查询部门编号为10的员工信息

<span style="font-size: 14px;">SELECT  *   FROM  emp<br>WHERE  deptId=10;<br></span>

综合两条语句：

<span style="font-size: 14px;">SELECT  *   FROM  emp<br>WHERE  deptId=( <br>    SELECT  did  FROM  dept <br>    WHERE  dname='研发部'<br>);   #父查询中需要的条件数据由子查询提供<br></span>

复杂查询 —— 跨表/多表查询

示例：查询出每个员工的姓名及其所在部门的名称

<span style="font-size: 14px;">SELECT  ename, dname<br>FROM  emp, dept;      #错误！得到了“笛卡尔积”<br> <br>SELECT  ename, dname<br>FROM  emp, dept<br>WHERE  deptId=did;  #跨表查询必须防止“笛卡尔积”<br>注意：上述语法是SQL-92标准中的跨表查询语法。缺陷：<br></span>

若某个中的记录在对方表中无对应项，则总结果无法显示这样的记录。
如deptId为NULL的员工、没有员工的部门都无法显示。

<span style="font-size: 14px;">SQL-99中的跨表查询语法分为四种：<br>(1)内连接查询  INNER JOIN  查询结果与SQL-92标准一样！<br>    SELECT  ename,  dname<br>    FROM  emp   INNER JOIN  dept<br>    ON  deptId=did;  #两个表的拼接条件用ON声明<br>  (2)左外连接查询  LEFT [OUTER] JOIN<br>    SELECT  ename,  dname<br>    FROM  emp   LEFT  OUTER  JOIN  dept<br>    ON  deptId=did;  #显示“左侧”表中的所有记录！<br>  (3)右外连接查询  RIGHT OUTER JOIN<br>    SELECT  ename,  dname<br>    FROM  emp   RIGHT  OUTER  JOIN  dept<br>    ON  deptId=did;  #显示“右侧”表中的所有记录！<br>  (4)全连接查询 FULL JOIN<br>    注意：MySQL不支持全连接！<br></span>

MySQL中解决“不支持全连接”的方法 —— 结果集的合并：

<span style="font-size: 14px;">(SELECT  ename  FROM  emp_cn)<br>UNION<br>(SELECT  ename  FROM  emp_us);  #合并相同的记录<br> ------------------------------------------------<br>(SELECT  ename  FROM  emp_cn)<br>UNION  ALL<br>(SELECT  ename  FROM  emp_us);  #不合并相同的记录<br></span>

SQL语句的分类：

DDL: Data Define Language，数据定义语言——定义列

<span style="font-size: 14px;"> CREATE / DROP / ALTER / TRUNCATE<br></span>

DML: Data Manipulate Language，数据操作语言——操作行

<span style="font-size: 14px;"> INSERT / DELETE / UPDATE<br></span>

DQL: Data Query Language，数据查询语言——不影响数据

<span style="font-size: 14px;"> SELECT<br></span>

DCL: Data Control Language，数据控制语言——控制权限

<span style="font-size: 14px;"> GRANT / REVOKE<br></span>

小知识：mysqli_query($conn, $sql)的返回值类型：
(1)DML: 增删改，执行失败返回false，成功返回true
(2)DQL: 查，执行失败返回false，成功返回查询结果集对象，可能有0/1/N行数据；
从其中获取一行数据可以使用：
 $row=mysqli_fetch_row($result);抓取一个索引数组或null
 $row=mysqli_fetch_assoc($result);抓取一个关联数组或null
从其中获取所有记录行可以使用：
 $rowList=mysqli_fetch_all($result, MYSQLI_ASSOC);抓取一个二维数组，每一行呈现为一个关联数组

相关推荐：

mysql知识点总结_MySQL

mysql建议连接及查询数据表PHP代码

mysql连接数据库并测试实例分享

以上がMYSQL ナレッジポイントの要約と共有の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。