当前位置：Gxlcms > 数据库问题 > MySQL的初次见面礼基础实战篇

MySQL的初次见面礼基础实战篇

时间：2021-07-01 10:21:17 帮助过：21人阅读

| Tables_in_webshop | +-------------------+ | items | | orderdetail | | orders | | user | +-------------------+

商品表结构items(简化版)：

+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int(11)     | NO   | PRI | NULL    | auto_increment |
| name       | varchar(32) | NO   |     | NULL    |                |
| price      | float(10,1) | NO   |     | NULL    |                |
| detail     | text        | YES  |     | NULL    |                |
| pic        | varchar(64) | YES  |     | NULL    |                |
| createtime | datetime    | NO   |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+

订单详情表结构orderdetail(简化版):

+-----------+---------+------+-----+---------+----------------+
| Field     | Type    | Null | Key | Default | Extra          |
+-----------+---------+------+-----+---------+----------------+
| id        | int(11) | NO   | PRI | NULL    | auto_increment |
| orders_id | int(11) | NO   | MUL | NULL    |                |
| items_id  | int(11) | NO   | MUL | NULL    |                |
| items_num | int(11) | YES  |     | NULL    |                |
+-----------+---------+------+-----+---------+----------------+

订单表结构orders(简化版)：

+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| user_id    | int(11)      | NO   | MUL | NULL    |                |
| number     | varchar(32)  | NO   |     | NULL    |                |
| createtime | datetime     | NO   |     | NULL    |                |
| note       | varchar(100) | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

用户表结构user(简化版):

+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(11)      | NO   | PRI | NULL    | auto_increment |
| username | varchar(32)  | NO   |     | NULL    |                |
| birthday | date         | YES  |     | NULL    |                |
| sex      | char(1)      | YES  |     | NULL    |                |
| address  | varchar(256) | YES  |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+

它们间的关系如下图，即一个订单详情orderdetail可以有多个商品items，但只能属于一个订单orders，而一个订单orders也只能属于一个user用户，一个user用户可以拥有多个orders订单。

技术分享

这里我们使用mysql提供的命令行窗口监视器对mysql数据库进行各项操作。

数据库与表的创建以及SQL增删改查

数据库和表的创建与删除

通过以下命令链接上mysql监听器：

mysql -u root -p
********(输入密码)

我们可以通过以下语句来创建数据库和删除数据库并查看当前有哪些数据库(大小不敏感)

#创建数据库
CREATE DATABASE 数据库名称
#删除数据库
DROP DATABASE 数据库名称
#查看当前所有数据库
SHOW DATABASES

先查看当前有哪些数据库：

mysql> show databases; <-----------查看当前数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |          
+--------------------+
5 rows in set (0.00 sec)

其中information_schema、performance_schema、mysql、sys 都是mysql自动创建的数据库，如下给出这几库的简单信息：

  • information_schema数据库又称为信息架构，数据表保存了MySQL服务器所有数据库的信息。如数据库名，数据库的表，表栏的数据类型与访问权限等。

  • performance_schema数据库主要用于收集数据库服务器性能参数，以便优化mysql数据库性能。

  • mysql数据库是存储着已MySQL运行相关的基本信息等数据管理的数据库。

  • sys 数据库是mysql5.7增加的，通过这个库可以快速的了解系统的元数据信息
    这个库可以方便DBA发现数据库的很多信息，提供解决性能瓶颈的信息。

而test数据库则是一个测试数据库可有可无。ok，就此打住，现在通过创建和删除一个名为debug的数据库来演示数据库的创建语句。

mysql> create database debug; <------创建数据库
Query OK, 1 row affected (0.01 sec) <------代表执行成功
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| debug              |<------已被创建的数据库
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
6 rows in set (0.00 sec)

使用DROP DATABASE语句删除数据库：

mysql> drop database debug ; <------删除数据库
Query OK, 0 rows affected (0.02 sec)

mysql> show databases;       <------重新查看数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

表的创建与删除

表的操作语句如下：

#创建表
CREATE TABLE 表名 (字段名1 数据类型 [其他可选项],
                  字段名2 数据类型 [其他可选项],
                  ......                    )
#删除表
DROP TABLE 表名

#显示表的数据结构
DESC 表名

#查看数据库中所有的表
SHOW TABLES

创建表的最基本的3点是：

表的名称
表字段名称
每个字段的数据类型

现在利用上述的SQL操作语言，先创建一个名称webshop的数据库，并使用USE 关键字选择该数据库，然后创建前面 items 、orderdetail、orders、user 四张表，ok，先创建数据库，操作如下：

mysql> create database webshop; <------创建webshop数据库
Query OK, 1 row affected (0.00 sec)

mysql> use webshop <------使用use关键字选择webshop数据库
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

创建 items 表，语句如下:

CREATE TABLE `items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL COMMENT ‘商品名称‘,
  `price` float(10,1) NOT NULL COMMENT ‘商品定价‘,
  `detail` text COMMENT ‘商品描述‘,
  `pic` varchar(64) DEFAULT NULL COMMENT ‘商品图片‘,
  `createtime` datetime NOT NULL COMMENT ‘生产日期‘,
  PRIMARY KEY (`id`)  <---------------------------指明items的唯一主键字段
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

其中id是唯一主键，使用关键字 PRIMARY KEY 进行指定，并且不能为空，因此使用 NOT NULL 标识非空，而 AUTO_INCREMENT 选项代表该id为自动增长从1开始。在其他列中如name中还使用到了 COMMENT 来标识name的含义。每个列中使用到诸如int(11)、varchar(32)、float(10,1)、text、datetime 等数据类型对每个字段的数据存储类型进行标明（关于数据类型后面会说明）。在表创建的结尾，使用 ENGINE=InnoDB 来说明该items表在mysql数据库中使用的引擎为InnoDB（mysql数据库中提供多种数据库引擎供选择，而InnoDB是具备事务功能的引擎，后面还能见到它，这里暂且打住），通过 CHARSET=utf8 指定该表的字符集，到此创建表的语句就完成了。接着创建其他3张表：

#user表创建语句
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(32) NOT NULL COMMENT ‘用户名称‘,
  `birthday` date DEFAULT NULL COMMENT ‘生日‘,
  `sex` char(1) DEFAULT NULL COMMENT ‘性别‘,
  `address` varchar(256) DEFAULT NULL COMMENT ‘地址‘,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=utf8;


#订单表orders创建语句
CREATE TABLE `orders` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL COMMENT ‘下单用户id‘,
  `number` varchar(32) NOT NULL COMMENT ‘订单号‘,
  `createtime` datetime NOT NULL COMMENT ‘创建订单时间‘,
  `note` varchar(100) DEFAULT NULL COMMENT ‘备注‘,
  PRIMARY KEY (`id`),
  CONSTRAINT `FK_orders_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;


#订单详情表orderdetail创建语句
CREATE TABLE `orderdetail` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `orders_id` int(11) NOT NULL COMMENT ‘订单id‘,
  `items_id` int(11) NOT NULL COMMENT ‘商品id‘,
  `items_num` int(11) DEFAULT NULL COMMENT ‘商品购买数量‘,
  PRIMARY KEY (`id`),
  <--------创建外键约束----------->
  CONSTRAINT `FK_orderdetail_1` FOREIGN KEY (`orders_id`) REFERENCES `orders` (`id`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_orderdetail_2` FOREIGN KEY (`items_id`) REFERENCES `items` (`id`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION

外键的约束

在订单详情表orderdetail表的创建语句中使用到如下语句：

  CONSTRAINT `FK_orderdetail_1` FOREIGN KEY (`orders_id`) REFERENCES `orders` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_orderdetail_2` FOREIGN KEY (`items_id`) REFERENCES `items` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION

其中orders_id 和 items_id 分别的订单表orders和商品表items的主键，像这种属于其他表主键又存在于orderdetail表中的字段，称之为orderdetail的外键字段，使用外键的好处是可以使得两张表存在关联，保证数据的一致性和实现一些级联操作；如每次购物时必须存在相对应的items_id商品数据才能创建订单详情的数据，因为没有商品也没有所谓的订单详情了，而每次可能会购买多种商品，而每种商品也将生成不同订单详情，而客户的购买行为属一次购买，因此订单详情汇聚成一个整体的订单（orders_id），也就是说一个订单详情只能属于一个订单，而一个订单可以拥有多个订单详情。在MySQL中，InnoDB引擎类型的表支持了外键约束，而外键的使用条件如下：
1.两个表必须使用InnoDB引擎
2.外键列必须建立了索引(关于索引后面分析，主键创建时会自动创建索引)，MySQL 4.1.2以后的版本在建立外键时会自动创建索引
3.外键关系的两个表的列必须是数据类型相似，也就是可以相互转换类型的列，比如int和tinyint可以，但int和char则不可以；
外键的定义语法：

[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, …)
REFERENCES tbl_name (index_col_name, …)
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]

实例对照：

 CONSTRAINT `FK_orderdetail_1` FOREIGN KEY (`orders_id`) REFERENCES `orders` (`id`) 
 ON DELETE NO ACTION ON UPDATE NO ACTION <-----默认行为，可以不写

注意该语法可以在 CREATE TABLE 和 ALTER TABLE 时使用，CONSTRAINT symbol，指明了约束标识符，在SQL排错时可能有不错的表现，如果不指明CONSTRAINT symbol，MYSQL会自动生成一个名字。两表间的更新删除时数据的同步可以使用ON DELETE、ON UPDATE 来表明相互间删除和更新事件触发后的影响，可设参数以下参数，假设主表是orders，从表是orderdetail。

  • RESTRICT、NO ACTION(默认行为)
    删除：从表数据记录不存在时，主表数据记录才可以删除，如当从表orderdetail的数据被删除后主表的orders的数据才能被删除，否则无法删除。删除从表数据，主表数据不变
    更新：从表记录数据不存在时，主表数据才可以更新。当更新从表数据，主表数据不变

  • CASCADE（级联）
    删除：删除主表数据时自动删除从表数据。删除从表数据，主表数据不变
    更新：更新主表数据时自动更新从表数据。更新从表数据，主表数据不变

  • SET NULL
    删除：删除主表数据时自动更新从表对于数据值为NULL。删除从表数据，主表数据不变
    更新：更新主表数据时自动更新从表数据值为NULL。更新从表数据数据，主表不变

到此，4张表都创建完成，我们使用show tables 语句来查看数据库中的表：

mysql> show tables;
+-------------------+
| Tables_in_webshop |
+-------------------+
| items             |
| orderdetail       |
| orders            |
| orders-dely       |
| user              |
+-------------------+
5 rows in set (0.01 sec)

其中orders-dely表是多余的，使用drop table 表名 语句将其删除：

mysql> drop table `orders-dely` ; <-------删除orders-dely表
Query OK, 0 rows affected (0.01 sec)

mysql> show tables; <------再次查看数据库中的表
+-------------------+
| Tables_in_webshop |
+-------------------+
| items             |
| orderdetail       |
| orders            |
| user              |
+-------------------+
4 rows in set (0.00 sec)

数据类型

MySQL使用所有标准的ANSI SQL数字数据类型，下面将列出常见的数据类型及其说明

数字数据类型

  • TINYINT - 一个非常小的整数，可以带符号。如果是有符号，它允许的范围是从-128到127。如果是无符号，允许的范围是从0到255，占1个字节。。

  • SMALLINT - 一个小的整数，可以带符号。如果有符号，允许范围为-32768至32767。如果无符号，允许的范围是从0到65535，占2个字节。

  • MEDIUMINT - 一个中等大小的整数，可以带符号。如果有符号，允许范围为-8388608至8388607。 如果无符号，允许的范围是从0到16777215，占3个字节。。

  • INT - 正常大小的整数，可以带符号。如果是有符号的，它允许的范围是从-2147483648到2147483647。如果是无符号，允许的范围是从0到4294967295，占4个字节。

  • BIGINT - 一个大的整数，可以带符号。如果有符号，允许范围为-9223372036854775808到9223372036854775807。如果无符号，允许的范围是从0到18446744073709551615. 占8个字节。

  • FLOAT(M,D) - 不能使用无符号的浮点数字。可以定义显示长度(M)和小数位数(D)。这不是必需的，并且默认为10,2。其中2是小数的位数，10是数字(包括小数)的总数。占4个字节。

  • DOUBLE(M,D) - 不能使用无符号的双精度浮点数。可以定义显示长度(M)和小数位数(D)。 这不是必需的，默认为16,4，其中4是小数的位数，占8个字节。

  • DECIMAL(M,D) - 非压缩浮点数不能是无符号的。在解包小数，每个小数对应于一个字节。定义显示长度(M)和小数(D)的数量是必需的。 NUMERIC是DECIMAL的同义词。

日期和时间类型

  • DATE - 以YYYY-MM-DD格式的日期，在1000-01-01和9999-12-31之间。 例如，1999年10月30日将被存储为1999-10-30。

  • DATETIME - 日期和时间组合以YYYY-MM-DD HH:MM:SS格式，在1000-01-01 00:00:00 到9999-12-31 23:59:59之间。例如，1999年10月30日下午3:30，会被存储为1999-10-30 15:30:00。

  • TIMESTAMP - 1970年1月1日午夜之间的时间戳，到2037的某个时候。这看起来像前面的DATETIME格式，无需只是数字之间的连字符; 1973年12月30日下午3点30分将被存储为19731230153000(YYYYMMDDHHMMSS)。

  • TIME - 存储时间在HH:MM:SS格式。

  • YEAR(M) - 以2位或4位数字格式来存储年份。如果长度指定为2(例如YEAR(2))，年份就可以为1970至2069(70?69)。如果长度指定为4，年份范围是1901-2155，默认长度为4。

字符串类型

  • CHAR(M) - 固定长度的字符串是以长度为1到255之间个字符长度(例如：CHAR(5))，存储右空格填充到指定的长度。 限定长度不是必需的，它会默认为1。

  • VARCHAR(M) - 可变长度的字符串是以长度为1到255之间字符数(高版本的MySQL超过255); 例如： VARCHAR(25). 创建VARCHAR类型字段时，必须定义长度。

  • BLOB 或 TEXT - 字段的最大长度是65535个字符。 BLOB是“二进制大对象”，并用来存储大的二进制数据，如图像或其他类型的文件。定义为TEXT文本字段还持有大量的数据; 两者之间的区别是，排序和比较上存储的数据，BLOB大小写敏感，而TEXT字段不区分大小写。不用指定BLOB或TEXT的长度。

  • TINYBLOB 或 TINYTEXT - BLOB或TEXT列用255个字符的最大长度。不指定TINYBLOB或TINYTEXT的长度。

  • MEDIUMBLOB 或 MEDIUMTEXT - BLOB或TEXT列具有16777215字符的最大长度。不指定MEDIUMBLOB或MEDIUMTEXT的长度。

  • LONGBLOB 或 LONGTEXT - BLOB或TEXT列具有4294967295字符的最大长度。不指定LONGBLOB或LONGTEXT的长度。

  • ENUM - 枚举，这是一个奇特的术语列表。当定义一个ENUM，要创建它的值的列表，这些是必须用于选择的项(也可以是NULL)。例如，如果想要字段包含“A”或“B”或“C”，那么可以定义为ENUM为 ENUM(“A”，“B”，“C”)也只有这些值(或NULL)才能用来填充这个字段。

表的增删改查操作

增删改

有上述的表结构就可以对表进行增删改查的操作，其语句法结构如下：

#insert 插入操作
INSERT INTO 表名(列名1,列名2,...) VALUES (数据1,数据2...);

#update 更新操作
UPDATE 表名 SET 列名1=值1,列名2=值2,...WHERE 条件表达式；

#delete 删除操作
DELETE FROM 表名 WHERE 条件表达式

#select 查询操作
SELECT 列名1，列名2，... FROM 表名 [条件表达式]

接着使用上述语句对user进行增删改查的操作，首先查询一下user表有哪些用户数据，通过select 语句进行查询：

mysql> select * from user; <---- select查询语句
+----+-----------+------------+------+--------------------+
| id | username  | birthday   | sex  | address            |
+----+-----------+------------+------+--------------------+
|  1 | 王五      | NULL       | 2    | NULL               |
|  2 | 张曹宇    | 1990-08-05 | 1    | 广东省汕头市       |
| 10 | 张三      | 1999-06-06 | 1    | 北京市朝阳区       |
| 16 | 任在明    | 1996-12-01 | 1    | 广东省广州市       |
| 22 | 陈小明    | 1995-05-10 | 1    | 广东省深圳市       |
| 24 | 任传海    | 1992-03-08 | 1    | 海南三亚           |
+----+-----------+------------+------+--------------------+
9 rows in set (0.00 sec)

其中 * 号代表查询出该表的所有字段，当然也向下面那样一个个字段列举出来：

select id, username, birthday, sex, address from user;

现在通过下面语句向user表插入一条数据：

insert into user (id, username, birthday, sex, address)
          values(‘3‘,‘新数据‘,‘1909-12-12‘,‘1‘,‘常年在外‘);

#当确定插入表中所有列时可以省略列名称     
insert into user values(‘3‘,‘新数据‘,‘1909-12-12‘,‘1‘,‘常年在外‘);

结果如下：

#插入新数据
mysql> insert into user (id, username, birthday, sex, address) values(‘3‘,‘新数据‘,‘1909-12-12‘,1,‘常年在外‘);
Query OK, 1 row affected (0.00 sec)

mysql> select * from user;
+----+-----------+------------+------+--------------------+
| id | username  | birthday   | sex  | address            |
+----+-----------+------------+------+--------------------+
|  1 | 王五      | NULL       | 2    | NULL               |
|  2 | 张曹宇    | 1990-08-05 | 1    | 广东省汕头市       |
|  3 | 新数据    | 1909-12-12 | 1    | 常年在外    <--------新插入的数据
| 10 | 张三      | 1999-06-06 | 1    | 北京市朝阳区       |
| 16 | 任在明    | 1996-12-01 | 1    | 广东省广州市       |
| 22 | 陈小明    | 1995-05-10 | 1    | 广东省深圳市       |
| 24 | 任传海    | 1992-03-08 | 1    | 海南三亚           |
+----+-----------+------------+------+--------------------+
10 rows in set (0.00 sec)

使用更新操作，更新ID为3的记录：

update user set username=‘大王让我来巡山‘,address=‘北京朝阳‘ where id=3;

执行过程如下：

mysql> update user set username=‘大王让我来巡山‘,address=‘北京朝阳‘ where id=3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user;
+----+-----------------------+------------+------+--------------------+
| id | username              | birthday   | sex  | address            |
+----+-----------------------+------------+------+--------------------+
|  1 | 王五                  | NULL       | 2    | NULL               |
|  2 | 张曹宇                | 1990-08-05 | 1    | 广东省汕头市       |
|  3 | 大王让我来巡山         | 1909-12-12 | 1    | 北京朝阳           |
| 10 | 张三                  | 1999-06-06 | 1    | 北京市朝阳区       |
| 16 | 任在明                | 1996-12-01 | 1    | 广东省广州市       |
| 22 | 陈小明                | 1995-05-10 | 1    | 广东省深圳市       |
| 24 | 任传海                | 1992-03-08 | 1    | 海南三亚           |   
+----+-----------------------+------------+------+--------------------+
10 rows in set (0.01 sec)

显然id为3的数据已更新，接着使用delete语句删除id为3和33的记录，执行如下：

mysql> delete from user where id = 3 and id=33;
Query OK, 0 rows affected (0.00 sec) <-----删除成功

mysql> select * from user;
+----+-----------------------+------------+------+--------------------+
| id | username              | birthday   | sex  | address            |
+----+-----------------------+------------+------+--------------------+
|  1 | 王五                  | NULL       | 2    | NULL               |
|  2 | 张曹宇                | 1990-08-05 | 1    | 广东省汕头市       |
|  3 | 大王让我来巡山         | 1909-12-12 | 1    | 北京朝阳           |
| 10 | 张三                  | 1999-06-06 | 1    | 北京市朝阳区       |
| 16 | 任在明                | 1996-12-01 | 1    | 广东省广州市       |
| 22 | 陈小明                | 1995-05-10 | 1    | 广东省深圳市       |
| 24 | 任传海                | 1992-03-08 | 1    | 海南三亚         | 
+----+-----------------------+------------+------+--------------------+
10 rows in set (0.00 sec)

变化多端的查询

对于增删改都比较简单，这里我们主要来细说一下查询，因为查询可以更加不同条件组合来获取不同的查询结果，这点还是比较有意思的。

过滤条件 - WHERE

先来看看条件查询，前面我们更新和删除时都使用到了条件语句，使用where子句指明要删除记录的id是哪个，也就是指明删除的符合具体条件的行数据，同样的条件也适合select语句，通过where子句查询，可以过滤不是期望的数据，下面通过一个例子来演示，查询id大于15的所有数据，执行语句如下：

mysql> select * from user where id > 15 ;
+----+-----------+------------+------+--------------------+
| id | username  | birthday   | sex  | address            |
+----+-----------+------------+------+--------------------+
| 16 | 任在明    | 1996-12-01 | 1    | 广东省广州市        |
| 22 | 陈小明    | 1995-05-10 | 1    | 广东省深圳市        |
| 24 | 任传海    | 1992-03-08 | 1    | 海南三亚           |
+----+-----------+------------+------+--------------------+
6 rows in set (0.00 sec)

其中 > 属于比较运算符 ，如上面id大于15的所有数据将会被检索出来，而id小于15的就被过滤掉了，当然在where子句中还可使用其他运算符，如下

运算符 描述 演示
= 相等 id = 15
> 大于 id > 15
< 小于 id < 15
>= 大于等于 id >= 15
<= 小于等于 id <= 15
<> 不相等 id <> 15
IS [NOT] NULL 为NULL(不为NULL) address is NULL
[NOT] LIKE 模糊查询，指向模糊查询目标 address LIKE ‘北京%‘
[NOT] BETWEEN (不包含)包含在指定范围内 id BETWEEN 1 AND 15
[NOT] IN 包含在指定范围值内 id in (1,2,15,20)

?

NULL 操作符

NULL条件有点需要注意，在使用NULL条件检索时不能使用=号，必须使用 is 指明查询条件的值为空，当然如果是not null 那就是非空数据了，如：

#查询生日为空的用户
mysql> select * from user where birthday is NULL;
+----+----------+----------+------+---------+
| id | username | birthday | sex  | address |
+----+----------+----------+------+---------+
|  1 | 王五     | NULL     | 2    | NULL    |
+----+----------+----------+------+---------+
1 row in set (0.00 sec)
LIKE 操作符

关键字like主要用于模糊查询，如下查询名称为‘任’开头的用户：

mysql> select username from user where username like ‘任%‘;
+-----------+
| username  |
+-----------+
| 任在明    |
| 任传海    |
+-----------+
2 rows in set (0.00 sec)

其中% 称为外卡符号，代表0个以上的字符。如上述的【任%】代表以任开头的所有字符串。如果是 【%任】则代表以任结尾的所有字符串，而【%任%】代表所有含有任字的字符串。当然除了% 是外卡符号，还有‘_’下划线也是外卡符号，，代表一个字符，也就是说条件为任_ 时 只有【任良】是符合条件而【任其阿】则是不符合条件。同理加上NOT 则取反的意思。

BETWEEN 操作符

BETWEEN 是让我们可以运用一个范围 (range) 内抓出数据库中的值。执行语句如下：

#查询id在1到15之间的用户(包括1和15)
mysql> select * from user where id between 1 and 15 ;
+----+-----------------------+------------+------+--------------------+
| id | username              | birthday   | sex  | address            |
+----+-----------------------+------------+------+--------------------+
|  1 | 王五                  | NULL       | 2    | NULL               |
|  2 | 张曹宇                 | 1990-08-05 | 1    | 广东省汕头市        |
|  3 | 大王让我来巡山          | 1909-12-12 | 1    | 北京朝阳            |
| 10 | 张三                   | 1999-06-06 | 1    | 北京市朝阳区        |
+----+-----------------------+------------+------+--------------------+
4 rows in set (0.00 sec)
IN 操作符

表示某一组指明的数据，在括弧内可以有一或多个值，而不同值之间由逗点分开。值可以是数目或是文字。如下语句：

#查询id为1，2，3的用户
mysql> select * from user where id in (1,2,3);
+----+-----------------------+------------+------+--------------------+
| id | username              | birthday   | sex  | address            |
+----+-----------------------+------------+------+--------------------+
|  1 | 王五                   | NULL       | 2    | NULL               |
|  2 | 张曹宇                 | 1990-08-05 | 1    | 广东省汕头市         |
|  3 | 大王让我来巡山           | 1909-12-12 | 1    | 北京朝阳            |
+----+-----------------------+------------+------+--------------------+
4 rows in set (0.00 sec)
多条件组合AND 和 OR 操作符

当需要在查询中使用多个条件组合时，可以使用AND 或者 OR ，其中指明两个条件必须成立，而OR则需要一个条件成立即可，如下语句使用AND进行条件组合查询：

#查询生日为空并且sex=1的用户
mysql> select * from user where birthday is null and sex = 1;
+----+----------+----------+------+---------+
| id | username | birthday | sex  | address |
+----+----------+----------+------+---------+
|  1 | 王五     | NULL     | 1    | NULL    |
+----+----------+----------+------+---------+
1 row in set (0.00 sec)

使用OR进行条件组合查询的结果如下：

#查询生日为空或者sex=1的用户
mysql>  select * from user where birthday is null or sex = 1;
+----+-----------------------+------------+------+--------------------+
| id | username              | birthday   | sex  | address            |
+----+-----------------------+------------+------+--------------------+
|  1 | 王五                   | NULL       | 1    | NULL               |
|  2 | 张曹宇                 | 1990-08-05 | 1    | 广东省汕头市       |
|  3 | 大王让我来巡山          | 1909-12-12 | 1    | 北京朝阳           |
| 10 | 张三                  | 1999-06-06 | 1    | 北京市朝阳区       |
| 16 | 任在明                | 1996-12-01 | 1    | 广东省广州市       |
| 22 | 陈小明                | 1995-05-10 | 1    | 广东省深圳市       |
| 24 | 任传海                | 1992-03-08 | 1    | 海南三亚           |
+----+-----------------------+------------+------+--------------------+
7 rows in set (0.00 sec)

由此看出AND和OR的区别确实很明显，而且在where条件语句中可包含任意数目的AND和OR操作符，因此我们在语句查询时对于AND和OR的使用以及计算顺序要特别注意，否则将会得到非预期的查询结果。

对查询结果排序 ORDER BY

有时我们希望查询出的数据按照一定的规律排序，此时ORDER BY就是很好的帮手了，如想让查询出来的数据按生日排序：

#默认按升序，ASC 升序（可省略） ， DESC 降序
mysql> select username,birthday  from user where id > 15 order by birthday ASC;
+-----------+------------+
| username  | birthday   |
+-----------+------------+
| 任传海    | 1992-03-08 |
| 陈小明    | 1995-05-10 |
| 任在明    | 1996-12-01 |
+-----------+------------+
6 rows in set (0.00 sec)

如果有多个排序条件则用逗号隔开，第一个条件的优先级总是高于第二个条件，如下：

#先按id排序再按生日排序
mysql> select id ,username,birthday  from user where id > 15 order by id ,birthday;
+----+-----------+------------+
| id | username  | birthday   |
+----+-----------+------------+
| 16 | 任在明    | 1996-12-01 |
| 22 | 陈小明    | 1995-05-10 |
| 24 | 任传海    | 1992-03-08 |
+----+-----------+------------+
6 rows in set (0.00 sec)
获取指定行数的数据-LIMIT

通过LIMIT可以获取到指定行数的记录。比如想获取前3条数据

#获取前3条数据
mysql> select * from user limit 0 , 3;
+----+-----------------------+------------+------+--------------------+
| id | username              | birthday   | sex  | address            |
+----+-----------------------+------------+------+--------------------+
|  1 | 王五                   | NULL       | 1    | NULL               |
|  2 | 张曹宇                 | 1990-08-05 | 1    | 广东省汕头市        |
|  3 | 大王让我来巡山          | 1909-12-12 | 1    | 北京朝阳            |
+----+-----------------------+------------+------+--------------------+

其中limit 0 ，3 ，第一个参数代表从第0个(也就是第一行数据)开始获取，第二个参数3，表示获取的条数。如下从第1个数据开始获取，结果就不一样了。

mysql> mysql> select * from user limit 1 , 3;
+----+-----------------------+------------+------+--------------------+
| id | username              | birthday   | sex  | address            |
+----+-----------------------+------------+------+--------------------+
|  2 | 张曹宇                 | 1990-08-05 | 1    | 广东省汕头市         |
|  3 | 大王让我来巡山          | 1909-12-12 | 1    | 北京朝阳            |
| 10 | 张三                   | 1999-06-06 | 1    | 北京市朝阳区        |
+----+-----------------------+------------+------+--------------------+
数据分组-GROUP BY 与 过滤分组 - HAVING

有时候可能需要依据某个字段进行查询结果分组，这时GROUP BY就显得很有用了，比如在user 表中我们依据sex字段进行分组,统计两种性别分别有多少人，先查看没有分组的数据：

mysql> select * from user ;
+----+-----------------------+------------+------+--------------------+
| id | username              | birthday   | sex  | address            |
+----+-----------------------+------------+------+--------------------+
|  1 | 王五                  | NULL        | 1    | NULL               |
|  2 | 张曹宇                | 1990-08-05  | 1    | 广东省汕头市         |
|  3 | 大王让我来巡山          | 1909-12-12 | 1    | 北京朝阳             |
| 10 | 张三                  | 1999-06-06 | 0    | 北京市朝阳区          |
| 16 | 任在明                | 1996-12-01 | 1    | 广东省广州市          |
| 22 | 陈小明                | 1995-05-10 | 0    | 广东省深圳市          |
| 24 | 任传海                | 1992-03-08 | 1    | 海南三亚             |
+----+-----------------------+------------+------+--------------------+
7 rows in set (0.00 sec)

分组数据如下，其count()是统计函数，可以统计某个字段的数量,执行结果如下：

#根据sex统计分组数量
mysql> select sex ,count(id) from user group by sex;
+------+-----------+
| sex  | count(id) |
+------+-----------+
| 0    |         2 |
| 1    |         5 |
+------+-----------+
2 rows in set (0.01 sec)

有点要注意的是如果列中具有NULL值，则NULL将作为一个分组返回，如果列中有多个NULL值，它们将分为一组，GROUP BY 子句必须出现在WHERE子句之后，ORDER BY 语句之前。如下添加两条sex为null的数据。

insert into user values(11,‘aaa‘,NULL,NULL,NULL);
insert into user values(12,‘bbb‘,NULL,NULL,NULL);

mysql> select sex ,count(id) from user group by sex;
+------+-----------+
| sex  | count(id) |
+------+-----------+
| NULL |         2 |<------ 以NULL进行分组
| 0    |         2 |
| 1    |         5 |
+------+-----------+
3 rows in set (0.00 sec)

除了能使用group by 分组数据外，我们还可以对分组的数据进行过滤，从而指定包括哪些分组，排除哪些分组，比如根据sex字段进行user查询分组时想排除sex字段值为null的数据，此时需要指明一个条件进行过滤，可能我们已想到where 子语句，遗憾的是where并不能对数据进行分组过滤，因为where更多地是进行行数据的过滤而不是分组数据的过滤，实际上where并没有分组的概念。幸运的是，mysql提供另外的子语句having，having与where有点类似，只不过where是进行行数据过滤，而having是进行组数据过滤,其演示过程如下：

#使用 having 排除sex为null的分组
mysql> select sex ,count(id) from user group by sex having sex is not null;
+------+-----------+
| sex  | count(id) |
+------+-----------+
| 0    |         2 |
| 1    |         5 |
+------+-----------+
2 rows in set (0.01 sec)

记住，where是进行行数据的过滤，也就是说在分组前where已过滤了数据，而having是进行分组数据的过滤，即在分组后才进行数据过滤，接着来看一个where 和 having 结合使用的例子，比如还是根据sex进行分组查询并排除sex为null的分组，同时希望排除id小于10的数据，其sql编写并执行如下：

#先查看所有数据
mysql> select id ,username, sex from user;
+----+-----------------------+------+
| id | username              | sex  |
+----+-----------------------+------+
|  1 | 王五                  | 1    |
|  2 | 张曹宇                | 1    |
|  3 | 大王让我来巡山          | 1    |
| 10 | 张三                  | 0    |
| 11 | aaa                  | NULL |
| 12 | bbb                  | NULL |
| 16 | 任在明                | 1    |
| 22 | 陈小明                | 0    |
| 24 | 任传海                | 1    |
+----+-----------------------+------+
9 rows in set (0.00 sec)

#同时使用where 和 having 进

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！