Mysql入门

2020-05-12 15:37:23 浏览数 (1)

MYSQL 数据库

数据库介绍

===========

数据库，就是能够存储和管理“大量数据”的一种软件系统的统称。

主流数据库

主流数据库包括：MS SQL Server， Oracle，DB2，Informix， Sybase 等。

他们都是被称为“关系数据库”的一种遵循 sql 标准的软件产品。

MySQL 数据库概览

MySQL 数据库的基本结构如下所示：

其中，实际表的数据和结构如下所示：

对应的几个名词（单词）为：

DBMS： 数据库（管理）系统，是我们“安装”而得到的。

DB，DataBase： 数据库，一个数据库系统中可以存放多个数据库。

通常一个项目（网站）使用一个数据库来存储其中的数据。

table：表，一个数据库中可以存放多个表。

row：行，指一行数据，一个表中可以有很多行。

record：记录，也是指一行数据。

column：列，指一列数据，一个表可以有若干列。

field：字段（列名），指数据表中的一列的名称（类似表头），一个表可以有若干字段。

关系数据库

关系数据库是指基于关系模型而设计的数据库系统。

所谓关系，其实就是指一个二维表（table）（有行有列）。

一行有多个数据，就表示这多个数据是具有内在关系的（同属一个“实体”）。

比如，上述两个表，可以用“E-R”图（实体-关系图）表示如下：

开启/关闭数据库服务

通过系统服务
通过命令行方式

在管理员模式下运行 cmd，执行如下命令：

net start mysql

net stop mysql

客户端连接数据库服务器

数据库就在那里！连，还是不连，就看你了！

任何连接或访问数据库的“软件/工具”，都可以称为“客户端”。

命令行连接数据库

连接（进入）数据库命令：

mysql -h 主机地址 -u 用户名 -p

特别注意：cmd 中登录后，请立即使用“set names gbk;” 语句来设定连接编码。

表示当前连接到数据库的“客户端”的字符编码是 gbk（固定的，不可更改）。

退出数据库命令：

quit;

或

exit;

或

q;

navicat 软件连接数据库

安装它，然后打开软件：

phpmyadmin“网站”连接数据库

安装（配置）该站点：

  1. hosts 文件中设定域名解析： www.myadmin69.com
  2. 拷贝网站文件到指定目录： bj-php-69/myadmin/
  3. httpd-vhost.conf 文件中设定站点：

<VirtualHost *:80>

…….

</VirtualHost>

数据库操作

==========

查看所有数据库

语句形式：

show databases;

一个错误提示：

创建新数据库

语句形式：

create database 数据库名 [charset 字符集名称][collate 校对规则名];

字符集名类似这些： utf8, gbk, gb2312, big5, ascii 等。推荐用 utf8。

校对规则名：通常都不用写，而是使用所设定字符集的默认校对规则。

校对规则的含义：

就是一个字符集中的每个字符的“排序规则”。

对于英文，很简单，就是按英文单词的字母顺序。

对于中文，或其他一些亚洲语言，就会面临问题：两个字的顺序，到底谁先谁后（谁大谁小）呢？

比如：“传”和“智”，有这样的可能排序方式：

按拼音：“传”在前（更小），“智”在后（更大）；

按笔顺（横竖撇捺折）：“智”在前（更小），“传”在后（更大）；

按编码：肯定一个大一个小（具体未知）；

查看可用的字符集：

show charset;

查看可用的校对规则：

show collation;

查看数据库创建信息

语句形式：

show create database 数据库名;

结果其实就是能看到指定数据的完整创建语句（含默认值的选项，比如 charset， collate）。

删除现有数据库

语句形式：

drop database 数据库名;

修改现有数据库

修改数据库，其实只是修改数据库的字符编码或校对规则。

其实一般都不需要修改。

语句形式：

alter database 数据库名 charset 新的字符集名称 collate 新的校对规则名 ；

选择（使用）某个数据库

一个项目中，具体进行有关数据操作（增删改查）之前，都需要先“选择/进入”该数据库。

语句形式：

use 数据库名;

总结有关数据库的常规操作：

创建数据库：

create database 数据库名 charset 编码名（推荐 utf8）；

显示所有数据库：

show databases ；

显示某个数据库的创建语句：

show create database 数据库名；

删除数据库：

drop database 数据库名；

修改某个数据库（的字符集和排序规则）

alter database 数据库名 charset 新字符集名称 collate 新校对规则名；

使用（进入/选择）数据库：

use 数据库名；

数据表操作

==========

“数据库”只是一个外壳，除了有个数据库名称和字符集设定，基本就没有别的信息了。

数据表才是存储（装载）数据的具体“容器”。

我们需要创建不同的表来存储不同的数据。

创建数据表初步

语句形式：

create table 数据表名 （字段 1， 字段 2， … ）[charset=字符集][engine=表类型];

其中：

字段的形式为： 字段名 字段类型 [字段属性…]

字符集包括：utf，gbk，gb2312，big5 等等，默认是数据库的字符集，可以不写。

表类型包括：InnoDB, MyIsam，BDB，等，默认是 InnoDB，可以不写。

查看所有数据表

语句形式：

show tables；

查看数据表结构

语句形式：

desc 表名；

所谓数据表的结构，其实就是一个表的每个字段的具体信息。

在来一个：

查看数据表的创建语句

语句形式：

show create table 表名；

（在 phpmyadmin 的界面执行的结果）

删除数据表

语句形式：

drop table 表名；

有关表的基本操作的总结：

建表：

create table 表名 (

字段名 字段类型 [字段属性],

字段名 字段类型 [字段属性],

……

) [ charset=编码名称][engine=表类型名称]；

表类型名称可用的也就几个，比如： InnoDB（默认的）, MyIsam, BDB, memory,

显示所有表：

show tables;

显示某个表的创建语句：

show create table 表名；

显示某个表的结构：

desc 表名；

删除表：

drop table 表名；

修改数据表

修改数据表主要是修改表名，添加字段，修改字段，删除字段，修改表的字符集；

添加字段：

语句形式：

alter table 表名 add 字段名 字段类型 [字段属性…][after 某字段名 或first]；

after 某字段名：意思是，新加的字段，放在该现有字段的后面；

first：表示新加的字段放在第一位（最前面）

修改字段：

语句形式：

alter table 表名 change 旧字段名 新字段名 字段类型 [字段属性…]；

如果不修改字段名，而只修改字段的其他信息，则可以使用：

alter table 表名 modify 要修改的字段名 字段类型 [字段属性…]；

删除字段：

语句形式：

alter table 表名 drop 要删除的字段名；

修改表名：

语句形式：

alter table 表名 rename 新的表名；

修改字符集：

语句形式：

alter table 表名 charset=新的字符集；

修改表语句的总结：
添加一个字段：

alter table 表名 add 一个字段的信息；

一个字段的信息的意思是：字段名 字段类型 [字段属性]

改掉一个字段：

alter table 表名 change 旧的字段名 新的字段信息；

修改一个字段：

alter table 表名 modify 要修改的字段名 新的类型 [新的属性]

删除字段：

alter table 表名 drop 要删除的字段名；

修改表名：

alter table 表名 rename 新的表名；

修改字符集：

alter table 表名 charset=新的字符集；

数据操作初步

============

数据都是存储在数据表中。

数据的基本操作有 4 种：增（插入 insert），删（删除 delete），改（修改 update），查（查询 select）。

即所谓的 CRUD 操作： Create（创建）, Retrieve（获取）, Update（更新）, Delete（删除）。

插入数据

语句形式：

insert into 表名 (字段名 1， 字段名 2， … ) values (数据 1， 数据 2， … ）；

说明：

  1. 字段名和数据是“一一对应”的，包括：数量一致，顺序一致，类型匹配。
  2. 对于要写入的数据，字符串和时间日期类型，要用单引号引起来。
  3. 可以省略“字段列表”部分，此时就需要给出跟字段数量一样多的数据，类似这样：
    1. insert into 表名 values (数据 1， 数据 2， … ）；
查询数据

语句形式：

select 字段名 1， 字段名 2， … from 表名 [ where 条件]；

说明：

  1. select 后的字段列表用于设定要从表中取出的哪些字段的值。
  2. select 后可以只使用一个“*”号表示取出该表中所有字段的值。
  3. where 条件表示取出的数据所应满足的条件，比如：
    1. where id < 10 表示取出那些字段 id 的值小于 10 的行。
    2. where age > 60 表示取出那些字段 age 的值大于 60 的行
  4. where 条件可以不写，就取出所有行的数据。
删除数据

语句形式：

delete from 表名 [where 条件]；

说明：

  1. 删除数据指的是删除表的某些行，比如原来有 10 行，可以将其中的 3 行删除，则剩下 7 行。
  2. where 条件表示删除数据所应满足的条件，含义跟 select 中的一样。
  3. where 条件可以不写，如果不写，则会删除所有数据——通常都不会这么用。
修改数据

语句形式：

update 表名 set 字段名 1 = 新值 1， 字段名 2=新值 2， … [where 条件]；

说明：

  1. 修改数据指的是修改表的某些行的某些字段。
  2. where 条件表示修改数据所应满足的条件，含义跟 select 中的一样。
  3. where 条件可以不写，如果不写，则会修改所有数据——通常都不会这么用。

总结：

系统级操作：

服务器的启停

mysql 服务，通过系统服务来操作，或:

net start mysql

net stop mysql

登录系统：

mysql -hlocalhost -uroot -p

退出：

quit

exit

客户端：

cmd 方式：

phpmyadmin 方式：

navicat 方式：

库操作：
建库：

create database 数据库名 charset utf8;

删除/显示/选择/数据库：

drop database 数据库名；

show databases;

show create database 数据库名；

use 数据库名;

表操作：
建表：

create table 表名 (

字段 1 类型 [属性],

字段 2 类型 [属性],

……

)

charset = 编码 engine =表类型名称

表类型有：InnoDB, MyIsam, BDB, Memory

结构

desc 表名；

查看表创建信息

show create table 表名；

查看全部表

show tables;

删除表

drop table 表名；

修改表

alter table 修改表有如下可修改项：

alter table 表名 add 新的字段；

alter table 表名 change 改掉字段；

alter table 表名 modify 修改字段；

alter table 表名 rename 新的表名；

alter table 表名 charset = 新的字符集名；

数据操作：
增：

insert into (字段列表) values (值列表);

删：

delete from 表名 where 条件；

改：

update 表名 set 字段名 = 新的值，….. where 条件

查：

select 字段列表 from 表名 where 条件。

》》》day2

MySQL 数据类型

数据类型（列类型）总览

可见，mysql 中的数据类型，总体分 3 大类：

数字型：

时间型：

字符型：

其中，在 sql 语句中，数字型数据不用单引号引起来，而时间型和字符型数据需要用单引号引起来。

整型

整型数据类型包括：

tinyint ：微整型

smallint ：小整型

mediumint ：中整型

int ：整型

bigint ：大整型

这些不同大小范围的整型信息如下表所示：

类型

所占空间 (字节)

带符号

无符号

最小值

最大值

最小值

最大值

tinyint

1

-128

127

0

255

smallint

2

-32768

32767

0

65535

mediumint

3

-8388608

8388607

0

16777215

int

4

-2147483648

2147483647

0

4294967295

bigint

8

-9223372036854775808

9223372036854775807

0

18446744073709551615

默认整数类型是带符号的，即可以有正负值，比如：

create table zhengxing1(num1 int, num2 tinyint);

此时，num1 和 num2 中都可以存储负数（但都不能超出范围）

不带符号的整数类型设置形式如下：

create table zhengxing2(num1 int unsigned, num2 tinyint unsigned);

小数型

小数类型分为浮点小数和定点小数。

浮点小数

浮点小数是“不精确的小数”，包括 float 和 double。

float：

占用 4 字节存储空间，可称为“单精度浮点数”，约 7 位有效数字。

double：

占用 8 字节存储空间，可称为“双精度浮点数”，约 17 位有效数字。

定点小数

浮点小数是“精确的小数”——它通过内部技巧，突破了“有些小数无法用二进制精确表示”的局限。

其设定方式通常是这样的： decimal(M, D);

其中 M 表示该小数的总的有效位数（最大 65），D 表示该小数的小数点后的位数。

演示：

定义三个字段分别为 float、double 和 decimal 类型，并都插入数字“123456789.123456789123456789”，显示结果。

日期时间型

日期时间类型包括如下几种：

date 类型：

表示日期，格式类似这样：’0000-00-00’

time 类型：

表示时间，格式类似这样：’00:00:00’

datetime 类型：

表示日期时间，格式类似这样：’0000-00-00 00:00:00’

timestamp 类型：

表示“时间戳”，其实就是一个整数数字，该数字是从是“时间起点”到现在为止的“秒数”。

“时间起点”是：1970-1-1 0:0:0

timestamp 类型的字段，无需插入数据，而是会自动取得当前的日期时间（表示当前时刻）。

而且，此类型字段会在数据被更新时，也同样自动取得当前的日期时间（表示修改的时刻）。

year 类型：

表示年份，格式为：’0000’

示例：

创建一个表，设定 5 个字段分别为上述类型，并插入相应的数据值后查看结果。

字符串型

字符串类型常用的包括：char， varchar， text， enum， set，分述如下：

定长字符 char 和变长字符 carchar
  • 定长字符类型 char：

适用于存储的字符长度为固定长度的字符，比如中国邮政编码，中国身份证号码，手机号码等。

设定形式：

字段名称 char(长度)

其特点是：

  1. 存储的字符长度固定，最长可设定为 255 个字符。
  2. 如果实际写入的字符不足设定长度，内部会自动用空格填充到设定的长度。
  3. 相对 varchar 类型，其存取速度更快。
  • 变长字符类型 varchar：

适用于存储字符长度经常不确定的字符，比如姓名，用户名，标题，内容，等大多数场合的字符。

设定形式：

字段名称 varchar(长度)

其特点是：

  1. 存储的字符长度是写入的实际长度，但不超过设定的长度。最长可设定为 65532（字节）。
    1. 注：由于其最长的限制是字节数，因此存储中文和英文的实际字符个数是不同的；
    2. 英文：一个字符占一个字节；
    3. 中文（gbk 编码）：一个字符占 2 个字节；
    4. 中文（utf8 编码）：一个字符占 3 个字节；
  2. 如果实际写入的字符不足设定的长度，就按实际的长度存储。
  3. 相对于 char 字符串，其存取速度相对更慢。
text 长文本类型

适用于存储“较长的文本内容”，比如文章内容。最长可存储 65535 个字符。

如果还需要存储更长的文本，可以使用 mediumtext（1600 万左右）或 longtext（40 亿左右）。

设定形式：

字段名称 text

text 类型的字段不能设置默认值。

enum 和 set 类型

enum 类型和 set 类型都是用于存储“有给定值的可选字符”，比如类似表单中的单选，多选，下拉列表。

  • enum 类型（单选类型/枚举类型）：

enum 类型通常用于存储表单中的“单选项”的值。

设定形式：

enum(‘选项值 1’, ‘选项值 2’, ‘选项值 3’, ….)

这些选项值都对应了相应的“索引值”，类似索引数组的下标，但是从 1 开始的。

即这些选项的索引值分别为：1， 2， 3， 4， …..

enum 类型最多可设定 65535 个选项。

示例：

create table tab1 (id int, edu(‘大学’, ‘中学’, ‘小学’, ‘其他’ ) );

insert into tab1 (id, edu) values (1, ‘大学’);

或：

insert into tab1 (id, edu) values (1, 2); //表示中学

  • set 类型（多选类型）：

set 类型通常用于存储表单中的“多选项”的值。

设定形式：

set(‘选项值 1’, ‘选项值 2’, ‘选项值 3’, ….)

这些选项值都对应了相应的“索引值”，其索引值从 1 开始，并“依次翻倍”。

即这些选项的索引值分别为：1， 2， 4， 8， 16， ….. （其实就是 2 的 n 次方）

enum 类型最多可设定 64 个选项值。

示例：

create table tab2 (aihao(‘篮球’, ‘排球’, ‘足球’, ‘中国足球’ ) ); #对应索引值为 1,2,4,8

insert into tab2( aihao ) values ( ‘篮球’ );

或：

insert into tab2 ( aihao ) values ( ‘篮球,排球’ );

或：

insert into tab2 ( aihao ) values ( ‘篮球,足球,排球’ );

或：

insert into tab2 ( aihao ) values ( 2 ); //表示排球(2)

或：

insert into tab2 ( aihao ) values ( 3); //表示“篮球,排球”(1 2)

或：

insert into tab2 ( aihao ) values ( 7); //表示“篮球,排球,足球”(1 2 4)

列属性

列属性是指定义或创建一个列的时候，可以给列额外增加的“附加特性”。

形式如下：

create table 表名 (列名 列类型 [列属性…] )；

说明：

  1. 一个列可以有多个列属性；
  2. 多个列属性空格隔开就行；

列属性包括以下这些：

  • null，not null
    • 设定为空，或非空，表明该列数据是否可为空值（null）。
  • default
    • 用于设定列默认值（不给值或给空值 null，就会自动使用该值）。
    • 使用形式：default 默认值 。
  • primary key
    • 用于设定主键。
    • 主键就是一个表中数据的“关键值”，通过该关键值就可以找到该特定的数据行。
    • 一个表的主键值不能重复（相等），比如文章表中的文章编号 id，比如用户表中的用户名。
    • 主键字段必须有值（不能为空）。
    • 一个表只能有一个主键（但一个主键可以是 1 个字段或 2 个以上的字段联合构成）
  • auto_increment
    • 用于设定一个整数字段的值是“自增长的”，通常用于一个表中的数据行的编号（比如文章编号）。
    • 默认情况下自增长值从 1 开始。
    • 一个表只能设定一个字段为自增长特性。
  • unique key
    • 用于设定“唯一键”的特性。
    • 唯一键表示一个表中的某字段的值是“唯一的”，“不重复的”。
    • 唯一键有点类似 primay key，但其值可以为空（null）。
    • 一个表可以有多个唯一键。
  • comment
    • 用于设定字段的说明性内容，类似注释，但又不是注释（属于有效的代码）。
    • 使用形式： comment ‘文字内容’

演示：

创建一个表，并用上以上所有字段属性。字段可包括：id, kecheng, keshi, intro.

实体与实体的关系

基本概念

实体（Entity）：

指现实中具体存在的可指称的“某物”。

一个表中的一行数据实际就是指对某物的描述性数据，所以一行数据就是一个实体。

有时实体也指整个表（因为表是由多个实体构成的）。

实体间关系（relationship）：

是指不同实体数据之间的关系，很多时候就是指表和表之间的关系。

实体间关系有：一对一关系，一对多关系，多对多关系。

图示如下：

一对一关系

表示一个表跟另一个表之间的数据之间一对一的关系。图示如下：

现实案例：

学校表：id，校名， 地址， 校长 id

校长表：id, 姓名， 年龄， 学历

此时，学校表和校长表就是一对一的关系：

一个学校只能有一个校长，一个校长只能负责一个学校。

一对多关系

表示一个表跟另一个表之间的数据之间是一对多的关系。图示如下：

现实案例：

学校表：id，校名， 地址， 校长 id

班级表：id, 班级名称， 教室号， 所属学校 id

此时，学校表和班级表就是一对多的关系：

一个学校可以有多个班级，一个班级只能属于一个学校。

多对多关系

表示一个表跟另一个表之间的数据之间是多对多的关系。图示如下：

现实案例：

课程表：id，课程名称， 课时数， 学分数

学生表：id, 姓名， 性别， 年龄， 所属班级 id

此时，课程表和班级表就是多对多的关系：

一个课程可以被多个学生学习，

一个学生也可以学多个课程。

这种情况，通常还需要建立一个“中间表”，以记录所有学生各自选修了哪些课程，如下所示：

学生 id

课程 id

选修时间

1

1

2017-8-9

1

3

2017-8-9

1

4

2017-9-1

2

1

2017-8-4

2

2

2017-8-4

2

3

2017-9-1

3

3

2018-6-9

…

…

…

》》》day3

进阶

高级查询

高级查询语法概述

一个查询语句的完整形式如下所示：

select 子句

[from 子句]

[where 子句]

[group by 子句]

[having 子句]

[order by 子句]

[limit 子句]

；

可见，select 语句还是比较复杂的——其实是 mysql 中最复杂的语句。

总体说明：

  1. 以上中括号中的任一项都可以省略，但如果不省略，就应该按该顺序出现。
  2. 通常，from 后的子句都需要有 from 子句， having 子句需要有 group by 子句。
  3. 这些子句的“执行顺序”，也是按此顺序进行的。
查询结果数据及 select 选项
查询“固定数据”

例：

select 1;

select 2, ‘abc’;

select 3, now();

select 中可以进行计算

例：

select 1 2;

select 3 4*5, 6 round(6.7) ; #其中 round( )为系统函数

查询出的数据字段可以使用别名

例：

select 1 as d1, 2 3 as d2;

select user_name as un, user_pass as pwd from users;

使用 distinct 消除查询结果重复行

语法形式：

select disctinct 字段 1， 字段 2， … from 表名；

where 子句

语法形式：

select …. from 表名 where 查询条件；

说明：

  1. 查询条件，类似 php 语言中的判断条件，也就是说，where 相当于 if。
  2. 查询条件的作用是：针对 from 子句的表“进行一行一行筛选”，以筛选出符合条件的行。
  3. 查询条件中，可以使用很多的运算符，包括：算术运算符，比较运算符，逻辑运算符，等等。

示例：

where id > 10; //比较运算符

where age - 18 >= 0; //算术运算符，比较运算符

where id < 20 and age >= 18; //比较运算符，逻辑运算符

where year % 4 = 0 and year % 100 != 0 || year % 400 = 0;

//算术运算符，比较运算符，逻辑运算符

mysql 运算符
算术运算符

- * / %

比较运算符：

相等： =

不相等： <> 或 !=

大于： >

大于等于： >=

小于： <

小于等于： <=

逻辑运算符：

逻辑与： && 或 and

逻辑或： || 或 or

逻辑非： ! 或 not

其他特殊运算符

like 模糊查找运算符：

用于判断某个字符型字段的值是否包含给定的字符。

语法形式：

xxx 字段 like ‘%关键字%’

其中：%表示“任意个数的任意字符”。

还可以使用“_”（下杠），表示“任意一个字符”。

如果不使用“%”或“_”，则 like 相当于等于（=）。比如：

xxx 字段 like ‘关键字’

相当于：

xxx 字段 = ‘关键字’

between 范围限定运算符：

用于判断某个字段的值是否在给定的两个数据范围之间。

语法形式：

xxx 字段 between 值 1 and 值 2

其含义相当于： xxx 字段 >= 值 1 and xxx 字段 <= 值 2

in 运算符：

用于判断某个字段的值是否在给出的若干个“可选值”范围。

语法形式：

xxx 字段 in （值 1， 值 2， …… ）

其含义是：该字段的值等于所列出的任意一个值，就算满足条件，比如：

籍贯 in (‘北京’，‘山东’，‘河北’， ‘江西’); //则某人籍贯为上述 4 个之一就 ok。

is 运算符：

用于判断一个字段中的是“是否存在”（即有没有），只有两个写法，如下所示：

where content is null; //不能写成： content = null;

where content is not null; //不能写成： content != null;

group by 子句

语法形式：

group by 字段 1， 字段 2， …. ；

含义：

表示对所取得的数据，以所给定的字段来进行分组。

最后的结果就是将数据分成了若干组，每组作为一个“整体”成为一行数据。

示例：

对于如下原始数据：

对其按“品牌”进行分组：

结果为：

特别注意：

分组查询的结果，要理解为，将“若干行原始数据”，分成了若干组，结果是每组为一行数据。

即：一行数据就代表“一组”这个集合概念，而不再是单个概念。

因此：一行中出现的信息，应该是“组的信息”，而不是“个体信息”。

于是，对于分组查询（group by），select 中出现的信息，通常就只有两种情况的信息了：

  1. 分组本身的字段信息；
  2. 一组的综合统计信息，主要包括：
    1. 计数值： count(字段)， 表示求出一组中原始数据的行数；
    2. 最大值： max(字段)，表示求出一组中该字段的最大值；
    3. 最小值： min(字段)，表示求出一组中该字段的最小值；
    4. 平均值： avg(字段)，表示求出一组中该字段的平均值；
    5. 总和值： sum(字段)，表示求出一组中该字段的累加和；

示例 1：

查询出各个品牌的产品的平均价。

示例 2：

查询出各个产地的产品数量、平均价，最高价，最低价。

示例 3：

查询出产品表中的产品总数。

示例 4：

查询出产品表中联想品牌的产品总数。

having 子句

语法形式：

having 筛选条件

含义：

having 的含义跟 where 的含义一样，只是 having 是用于对 group by 分组的结果进行的条件筛选。

示例：

查询出品牌平均价超过 5000 的所有品牌的平均价，最高价，以及产品的数量。

order by 子句

语法形式：

order by 字段 1 [asc 或 desc]， 字段 2 [asc 或 desc]， ….

含义：

对前面所取得的数据按给定的字段进行排序。

排序方式有： 正序 asc， 倒序 desc，如果省略不写，就是 asc

示例 1：

对所有产品按价格从高到低进行排序；

示例 2：

对所有品牌的平均价按从高到低的顺序进行排序，并列出品牌名和平均价。

limit 子句

语法形式：

limit 起始行号，行数

说明：

  1. limit 表示对前面所取得的数据再进行数量上的筛选：取得从某行开始的多少行。
  2. 行号就是前面所取得数据的“自然顺序号”，从 0 开始算起——注意不是 id，或任何其他实际数据。
  3. 起始行号可以省略，此时 limit 后只用一个数字，表示从第 0 行开始取出多少行。
  4. limit 子句通常用在“翻页”功能上，用于找出“第 n 页”的数据，其公式为： limit (n - 1) * pageSize, pageSize; 其中 pageSize 表示每页显示的条数。

示例 1：

取出商品表中价格最高的 3 个商品，并按倒序排列出来。

高级插入

同时插入多行记录

语句形式：

insert into 表名(字段 1，字段 2，…) values (值 1，值 2，… ), (值 1， 值 2，… ), ……；

插入查询的结果数据

语句形式：

insert into 表名(字段 1，字段 2，…) select (xx1, xx2, … ) … ;

要求：

  1. 插入语句的字段个数，跟 select 语句的字段个数相等；
  2. 插入语句的字段类型，跟 select 语句的字段类型相符；
set 语法插入数据

语句形式：

insert into 表名 set 字段 1=值 1， 字段 2=值 2，…. ；

蠕虫复制

所谓蠕虫复制，就是针对一个表的数据，进行快速的复制并插入到所需要的表中，以期在短时间内具备“大量数据”，以用于测试或其他特殊场合，比如：

  1. 将一个表的大量数据，复制到另一个表中；
  2. 将一个表的数据复制到本身表中以产生大量数据；
插入时主键冲突的解决办法

所谓主键冲突是指，当插入一条记录的时候，如果插入的记录的主键值，在现有的数据中已经存在，则此时，因为主键不能重复，因此就产生了“主键冲突”。

那如果出现主键冲突，该怎么办呢？

  • 办法 1：忽略

——终止插入，数据不改变。

其语法为：

insert ignore into 表名 ( 字段…. ) values (值…..)；

  • 办法 2：替换

——删除原纪录，插入新纪录。

其语法为：

replace into into 表名 ( 字段…. ) values (值…..)；

说明：此 replace 的用法跟 insert 一样，也可以插入新纪录，只是如果新纪录出现主键冲突，就会删除原纪录后，再插入该新纪录。

  • 办法 3：更新

——设置为去更新原有数据（而并不插入）。

语法为：

insert into 表名 ( 字段…. ) values (值…..) on duplicate key update XX 字段=新的值；

高级删除

按指定顺序删除指定数量的数据

语法形式：

delete from 表名 where … [order by 字段名, …][limit 数量n]；

说明：

  1. order by 用于设定删除数据时的删除顺序，跟 select 语句中的 order by 子句道理一样。
  2. limit 用于设定删除数据时要删除的行数，即删除的数据可能少于条件筛选出来的数据。
trancate 清空

语法形式：

trancate 表名；

说明：

表示清空指定表中的所有数据并将表恢复到“初始状态”（就类似刚刚创建一样）。

对比：

无条件删除： delete from 表名；

结果：删除了指定表中的所有数据——但表仍然会被纪录为“已使用过”。

差别：主要是对于”auto_increment”的字段，会保留使用过的最大值，而 trancate 后的表，自增长的序号会完全重新开始（就像新表一样）。

高级更新

语法形式：

update 表名 set 字段名 1=字段值 1， … where … [order by 字段名, …][limit 数量n]；

说明：

  1. order by 用于设定更新数据时的更新顺序，跟 select 语句中的 order by 子句道理一样。
  2. limit 用于设定更新数据时要更新的行数，即更新的数据量可能少于条件筛选出来的数据量。

联合（union）查询

联合查询概念

含义：

联合查询是指将 2 个或 2 个以上的字段数量相同的查询结果，“纵向堆叠”后合并为一个结果。

图示如下：

select id, f1, f2 from join1

union

select id2, c1, c2 from join2;

联合查询语法

语法形式：

select 查询 1

union [all 或 distinct]

select 查询 2

union [all 或 distinct]

select 查询 3

……

[order by 字段 [asc 或 desc] ]

[limit 起始行号，数量] ；

说明：

  1. 所有单个查询结果应该具有相等的列数。
  2. 所有单个查询的列类型应该具有一致性（即每个查询的第 n 列的数据类型一致）。
  3. 单个查询的列名可以不同，但最终的列名是第一个查询的列名（可以使用别名）。
  4. union 可以带 all 或 distinct 参数，如果省略就是 distinct，即默认已经消除重复行了。
  5. 最后的 order by 或 limit 是对整个联合之后的结果数据进行排序或数量限定。
  6. order by 子句中的排序字段应该使用第一个查询中的字段名，如果有别名就必须使用别名。

示例：

》》》day4

连接（join）查询

连接（join）查询是将两个查询的结果以“横向对接”的方式合并起来的结果。

对比：联合查询 是将两个查询的结果以“纵向堆叠”的方式合并起来的结果。

连接查询概述

连接查询，是将两个查询(或表)的每一行，以“两两横向对接”的方式，所得到的所有行的结果。

即一个表中的某行，跟另一个表中的某行，进行“横向对接”，而得到一个新行。

如下图所示：

则他们对接（连接）之后的结果类似这样：

可见，假设：

表 1 有 n1 行，m1 列；

表 2 有 n2 行，m2 列；

则表 1 和表 2“连接”之后，就会有：

n1*n2 行；

m1 m2 列。

连接查询基本形式如下：

select … from 表 1 [连接方式] join 表 2 [on 连接条件] where … ；

可见，连接查询只是作为 from 子句的“数据源”。

或者说，连接查询是扩大了数据源，从原来的一个表作为数据源，扩大为多个表作为数据源。

连接查询包括以下这些不同形式：

交叉连接，内连接，外连接（分：左外连接，右外连接）。

交叉连接（cross join）

语法形式：

from 表 1 [cross] join 表 2

说明：

  1. 交叉连接其实可以认为是连接查询的“完全版本”，即所有行都无条件地都连接起来了。
  2. 关键字“cross”可以省略；
  3. 交叉连接又称为“笛卡尔积”，通常应用价值不大。

演示：

内连接（inner join）

语法形式：

from 表 1 [inner] join 表 2 on 连接条件

说明：

  1. 内连接其实是交叉连接的基础上，再通过 on 条件而筛选出来的部分数据。
  2. 关键字“inner”可以省略，但建议写上。
  3. 内连接是应用最广泛的一种连接查询，其本质是根据条件筛选出“有意义的数据”。

演示：

外连接

外连接分为左外连接和右外连接。

左外连接（left join）：

语法形式：

from 表 1 left [outer] join 表 2 on 连接条件

说明：

  1. 左外连接其实是保证左边表的数据都能够取出的一种连接。
  2. 左外连接其实是在内连接的基础上，再加上左边表中所有不能满足条件的数据
  3. 关键字“outer”可以省略。

演示：

右外连接（right join）：

语法形式：

from 表 1 right [outer] join 表 2 on 连接条件

说明：

  1. 右外连接其实是保证右边表的数据都能够取出的一种连接。
  2. 右外连接其实是在内连接的基础上，再加上右边表中所有不能满足条件的数据。
  3. 关键字“outer”可以省略。

演示：

自连接

自连接不是一种新的连接形式，而只是一个表“自己跟自己连接”，这怎么做到呢？

语法形式：

from 表名 as a [连接形式] join 表名 as b on a.xx 字段 1=b.xx 字段名

说明：

  1. 自连接其实还是两个表连接，只是将一个表用不同的别名，当做两个表。
  2. 自连接适用于一个表中的某个字段的值“来源于”当前表的另一个字段的情况。

示例：

地区表如下所示：

id area_name parent_id

  1. 北京市 0
  2. 河北省 0
  3. 山东省 0
  4. 石家庄 4
  5. 保定 4
  6. 衡水 4
  7. 济南 3
  8. 青岛 3
  9. 烟台 3

… …

要求查询每个城市及其所在省份。

子查询（subquery）

子查询的概念

子查询就是指一个“正常查询语句”中的某个部分（比如 select 部分，from 部分， where 部分）又出现了查询的一种查询形式，比如：

select * from XX 表名 where price >= (一个子查询语句)；

此时，子查询所在上“上层查询”，就被称为主查询。

也可以这么说：子查询是为主查询的某个部分提供某种数据的查询。

上一条语句中，括号中的子查询语句如果查出的是一个“某个数值”（比如 3000），则其就相当于：

select * from XX 表名 where price >=3000；

标量子查询
  • 含义：

标量子查询就是指子查询的结果是“单个值”（一行一列）的查询。

  • 使用：

标量子查询通常用在 where 子句中，作为主查询的一个条件判断的数据。

本质上，标量子查询的结果，就可以直接当做“一个值”来使用。

示例：

找出产品表中价格大于平均价的所有产品。

列子查询
  • 含义：

列子查询查出的结果为“一列数据”，类似这样：

select pinpai from product where chandi = ‘北京’;

结果为：

  • 使用：

列子查询通常用在 where 子句的 in 运算符中，代替 in 运算符中的“字面值”列表数据。

比如：

select * from product where chandi in ( ‘北京’, ‘深圳’,’天津’ )

如果 in 中的数据并不方便一个一个列出，但可以通过一个查询得到，就可以使用查询来实现：

select * from product where chandi in ( select chandi from product price > 4000 );

示例：

查出出产贵重商品（价格超过 4000 即为贵重商品）的那些产地的所有商品。

行子查询
  • 含义：

行子查询查出的结果是一个行（有时也可以是多行），类似这样：

select distinct pinpai, chandi from product where pinpai=’联想’ and chandi = ‘北京’;

结果为：

  • 使用：

行子查询的结果通常跟“行构造符”一起，在 where 条件子句中做为条件数据，类似这样：

where (字段 1, 字段 2 ) = （行子查询）

或

where row(字段 1, 字段 2 ) = （行子查询） //含义跟上一行是一样的，即 row 可以省略

示例：

找出跟单价最高的商品同品牌同产地的所有商品。

SELECT * FROM `product`

where row(pinpai, chandi)=

(select pinpai,chandi from product where price = ( select max(price) from product) )

表子查询
  • 含义：

当一个子查询查出的结果是“多行多列”的时候，就是表子查询。

表子查询的结果相当于一个表，可以直接当做一个表来使用。

  • 使用：

表子查询通常用在主查询的 from 子句中，作为一个“数据源”。

注意：

此时需要给该子查询设置一个别名，类似这样：

from (select … 子查询 ) as tab1

示例：

查出商品价格大于 4000 的所有商品的数量和均价

有关子查询的特定关键字

in 关键字

in 关键字在子查询中主要用在列子查询中代替人为手工罗列出来的多个“字面值”数据。

举例：

找出联想品牌的商品都有哪些类别。

any 关键字

any 关键字用在比较操作操符的后面，表示查询结果的多个数据中的任一个满足该比较操作符就算满足。

举例：

找出在北京生产的但价格比在深圳生产的贵的商品。

all 关键字

all 关键字用在比较操作操符的后面，表示查询结果的多个数据中的所有都满足该比较操作符才算满足。

举例：

找出在北京生产的但价格比在深圳生产所有商品都贵的商品。

exists 子查询
  • 形式：

where exists ( 任何子查询 )

  • 含义：

该子查询如果“有数据”， 则该 exists()的结果为“true”， 即相当于 where true （恒真）

该子查询如果“没有数据”，则该 exists()的结果为“false”，即相当于 where false（恒假）

  • 说明：
  1. 此子查询语句通常需要用到主查询语句中的字段作为查询条件。
  • 示例：
  1. 查询商品分类名称中带“电”字的所有商品；
  2. 查询联想品牌的产品都有哪些分类；

数据管理

数据备份

数据备份就是指将一个数据库中的数据，转存为一个或多个文件的过程。

备份整个数据库

  • 命令形式：

mysqldump.exe -h 主机地址 -u 用户名 -p 密码 数据库名 > 备份文件名(含路径)

  • 说明：
  1. 跟登录 mysql 类似，密码可以不写，则随后会提示输入
  2. 该语句是 mysql/bin 中的一个命令，不是 sql 语句（即不应该登录 mysql 后使用）
  • 示例：

备份单个表

  • 命令形式：

mysqldump.exe -h 主机地址 -u 用户名 -p 密码 数据库名 表名 > 备份文件名(含路径)

  • 说明：
  1. 跟登录 mysql 类似，密码可以不写，则随后会提示输入
  2. 该语句是 mysql/bin 中的一个命令，不是 sql 语句（即不应该登录 mysql 后再去使用）
  • 示例：

数据还原（数据恢复）

数据还原（恢复）是指将一个之前备份过的数据文件，恢复（还原）到某个数据库的过程。

还原其实不分整个库还是单个表，都是一样的。

  • 命令形式：

mysql.exe -h 主机地址 -u 用户名 -p 密码 目标数据库名 < 想要还原的备份文件名(含路径)

  • 示例：

用户管理

用户管理主要包括两方面的工作：

用户账号的管理，包括：创建，删除，改密

用户权限的管理，包括：授予权限，取消权限

查看用户

mysql 数据库管理系统中有个数据库叫做“mysql”，绝对不能删除！

其中有个表“user”，就是存储了当前数据库系统中的所有用户信息。

初始时只有一个用户：root 。

查看：

use mysql;

select * from user;

创建用户

语法形式：

create user ‘用户名’[@’允许登录的地址’] identified by ‘密码’;

说明：

  1. 创建用户之后，数据库 mysql 中的 user 表中就会多一个用户。
  2. ‘允许登录的地址’，就是允许登录的客户端的 ip 地址，或
    1. ”localhost”表示只能本地登录；
    2. “%”表示任何位置都可以登录；
    3. 该部分可以省略，如果省略，默认就是”%”；
    4. 后续涉及到用户的操作，都是这个格式。

示例：

create user ‘user1’ identified by ‘123’;

create user ‘user2’@’localhost’ identified by ‘123’;

create user ‘user3’@’192.168.1.103’ identified by ‘123’;

create user ‘user4’@’%’ identified by ‘123’;

删除用户

语法形式：

drop user 用户[@’允许登录的地址’]；

删除用户后，数据库 mysql 中的 user 表中就会少一个用户。

修改/设置用户密码

语法形式：

set password for 用户[@’允许登录的地址’] = password(‘密码’);

授予用户权限

语法形式：

grant 操作 1，操作 2，…. on *.*或数据库名.* 或 数据库名.表名 to 用户[@’允许登录的地址’]；

说明：

  1. “操作”其实就是权限名，是一个“特定词”，比如：delete, insert, update, select, create, 等等。
    1. 其中，还可以用“all”，表示“所有权限”（除了 grant 权限）。
  2. on 后表示对“什么东西”来设定该权限，大致意思是对什么库的什么表，其中：
    1. *.* ：表示所有库的所有表；
    2. 数据库名.* ：表示该指定数据库的所有表；
    3. 数据库名.表名：表示该指定数据库的该指定表；

示例：

grant select, insert on test.* to user1@’%’;

  • mysql 中的所有操作（权限），有如下所示：

取消用户授权

语法形式：

revoke 操作 1，操作 2，…. on *.*或数据库名.* 或 数据库名.表名 from 用户[@’允许的地址’]；

ide 云数据库SQLServer 数据库 数据备份 sql

0 人点赞