【版权申明】未经博主同意,谢绝转载!(请尊重原创,博主保留追究权) http://blog.csdn.net/javazejian/article/details/61614366 出自【zejian 的博客】
本篇将以最简单的方式呈现并演绎 mysql 数据库的必知必会的知识点,通过本篇博文您将会对 mysql 从起点到终点的较为全面的认识,关于 mysql 的知识,将分两篇来记录,即 MySQL 的基础实战篇和 MySQL 的进阶实战篇,以下是本篇的主要知识点。
本篇不会讲解如何去安装 mysql,也没有这样的必要,网上这方面的资料随处可见,因此这里的环境搭建主要的我们可能在后面会使用到的数据库和表结构,本篇将采用电商网站最常见的四张表, 如下:
- +-------------------+| 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 数据库进行各项操作。
通过以下命令链接上 mysql 监听器:
- mysql -u root -p********(输入密码)
我们可以通过以下语句来创建数据库和删除数据库并查看当前有哪些数据库 (大小不敏感)
- #创建数据库CREATE DATABASE数据库名称
- #删除数据库DROP DATABASE数据库名称
- #查看当前所有数据库SHOWDATABASES
先查看当前有哪些数据库:
- mysql>showdatabases;<-----------查看当前数据库+--------------------+| Database |
- +--------------------+| information_schema |
- | mysql |
- | performance_schema |
- | sys |
- | test |
- +--------------------+5 rows inset(0.00sec)
其中 information_schema、performance_schema、mysql、sys 都是 mysql 自动创建的数据库,如下给出这几库的简单信息:
而 test 数据库则是一个测试数据库可有可无。ok,就此打住,现在通过创建和删除一个名为 debug 的数据库来演示数据库的创建语句。
- mysql>create databasedebug;<------创建数据库Query OK, 1 row affected (0.01 sec) <------代表执行成功mysql>showdatabases;+--------------------+| Database |
- +--------------------+| information_schema |
- | debug |<------已被创建的数据库| mysql |
- | performance_schema |
- | sys |
- | test |
- +--------------------+6 rows inset(0.00sec)
使用 DROP DATABASE 语句删除数据库:
- mysql>drop databasedebug ;<------删除数据库Query OK, 0 rows affected (0.02 sec)
- mysql>showdatabases;<------重新查看数据库+--------------------+| Database |
- +--------------------+| information_schema |
- | mysql |
- | performance_schema |
- | sys |
- | test |
- +--------------------+5 rows inset(0.00sec)
表的操作语句如下:
- #创建表CREATE TABLE表名 (字段名1数据类型 [其他可选项],
- 字段名2数据类型 [其他可选项],
- ...... )
- #删除表DROP TABLE表名
- #显示表的数据结构DESC表名
- #查看数据库中所有的表SHOWTABLES
创建表的最基本的 3 点是:
- 表的名称
- 表字段名称
- 每个字段的数据类型
现在利用上述的 SQL 操作语言,先创建一个名称 webshop 的数据库,并使用 USE 关键字选择该数据库,然后创建前面 items 、orderdetail、orders、user 四张表,ok,先创建数据库,操作如下:
- mysql>create databasewebshop;<------创建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 NULLAUTO_INCREMENT,`name` varchar(32)NOT NULLCOMMENT'商品名称',`price` float(10,1)NOT NULLCOMMENT'商品定价',`detail`text COMMENT'商品描述',`pic` varchar(64)DEFAULT NULLCOMMENT'商品图片',`createtime`datetimeNOT NULLCOMMENT'生产日期',PRIMARY KEY(`id`) <---------------------------指明items的唯一主键字段
- ) ENGINE=InnoDBDEFAULTCHARSET=utf8;
其中 id 是唯一主键,使用关键字
进行指定,并且不能为空,因此使用
- PRIMARY KEY
标识非空,而
- NOT NULL
选项代表该 id 为自动增长从 1 开始。在其他列中如 name 中还使用到了
- AUTO_INCREMENT
来标识 name 的含义。每个列中使用到诸如
- COMMENT
等数据类型对每个字段的数据存储类型进行标明(关于数据类型后面会说明)。在表创建的结尾,使用
- int(11)、varchar(32)、float(10,1)、text、datetime
来说明该 items 表在 mysql 数据库中使用的引擎为 InnoDB(mysql 数据库中提供多种数据库引擎供选择,而 InnoDB 是具备事务功能的引擎,后面还能见到它,这里暂且打住),通过
- ENGINE=InnoDB
指定该表的字符集,到此创建表的语句就完成了。接着创建其他 3 张表:
- CHARSET=utf8
- #user表创建语句CREATE TABLE `user`(`id` int(11)NOT NULLAUTO_INCREMENT,`username` varchar(32)NOT NULLCOMMENT'用户名称',`birthday` date DEFAULT NULLCOMMENT'生日',`sex` char(1)DEFAULT NULLCOMMENT'性别',`address` varchar(256)DEFAULT NULLCOMMENT'地址',PRIMARY KEY(`id`)
- ) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULTCHARSET=utf8;#订单表orders创建语句CREATE TABLE `orders`(`id` int(11)NOT NULLAUTO_INCREMENT,`user_id` int(11)NOT NULLCOMMENT'下单用户id',`number` varchar(32)NOT NULLCOMMENT'订单号',`createtime`datetimeNOT NULLCOMMENT'创建订单时间',`note` varchar(100)DEFAULT NULLCOMMENT'备注',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 DEFAULTCHARSET=utf8;#订单详情表orderdetail创建语句CREATE TABLE `orderdetail`(`id` int(11)NOT NULLAUTO_INCREMENT,`orders_id` int(11)NOT NULLCOMMENT'订单id',`items_id` int(11)NOT NULLCOMMENT'商品id',`items_num` int(11)DEFAULT NULLCOMMENT'商品购买数量',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`) ONDELETE 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
分别的订单表 orders 和商品表 items 的主键,像这种属于其他表主键又存在于 orderdetail 表中的字段,称之为 orderdetail 的外键字段,使用外键的好处是可以使得两张表存在关联,保证数据的一致性和实现一些级联操作;如每次购物时必须存在相对应的 items_id 商品数据才能创建订单详情的数据,因为没有商品也没有所谓的订单详情了,而每次可能会购买多种商品,而每种商品也将生成不同订单详情,而客户的购买行为属一次购买,因此订单详情汇聚成一个整体的订单(orders_id),也就是说一个订单详情只能属于一个订单,而一个订单可以拥有多个订单详情。在 MySQL 中,InnoDB 引擎类型的表支持了外键约束,而外键的使用条件如下: 1. 两个表必须使用 InnoDB 引擎 2. 外键列必须建立了索引 (关于索引后面分析,主键创建时会自动创建索引),MySQL 4.1.2 以后的版本在建立外键时会自动创建索引 3. 外键关系的两个表的列必须是数据类型相似,也就是可以相互转换类型的列,比如 int 和 tinyint 可以,但 int 和 char 则不可以; 外键的定义语法:
- items_id
- [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`)
- ONDELETE NO ACTION ON UPDATE NO ACTION<-----默认行为,可以不写
注意该语法可以在 CREATE TABLE 和 ALTER TABLE 时使用,
,指明了约束标识符,在 SQL 排错时可能有不错的表现,如果不指明
- CONSTRAINT symbol
,MYSQL 会自动生成一个名字。两表间的更新删除时数据的同步可以使用 ON DELETE、ON UPDATE 来表明相互间删除和更新事件触发后的影响,可设参数以下参数,假设主表是 orders,从表是 orderdetail。
- CONSTRAINT symbol
到此,4 张表都创建完成,我们使用
语句来查看数据库中的表:
- show tables
- mysql>showtables;+-------------------+| Tables_in_webshop |
- +-------------------+| items |
- | orderdetail |
- | orders |
- | orders-dely |
- | user |
- +-------------------+5 rows inset(0.01sec)
其中 orders-dely 表是多余的,使用
语句将其删除:
- drop table 表名
- mysql>drop table `orders-dely`;<-------删除orders-dely表Query OK, 0 rows affected (0.01 sec)
- mysql>showtables;<------再次查看数据库中的表+-------------------+| Tables_in_webshop |
- +-------------------+| items |
- | orderdetail |
- | orders |
- | user |
- +-------------------+4 rows inset(0.00sec)
MySQL 使用所有标准的 ANSI SQL 数字数据类型,下面将列出常见的数据类型及其说明
有上述的表结构就可以对表进行增删改查的操作,其语句法结构如下:
- #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 inset(0.00sec)
其中 * 号代表查询出该表的所有字段,当然也向下面那样一个个字段列举出来:
- 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 inset(0.00sec)
使用更新操作,更新 ID 为 3 的记录:
- update user set username = '大王让我来巡山',
- address = '北京朝阳'where id = 3;
执行过程如下:
- mysql>update user setusername='大王让我来巡山',address='北京朝阳' whereid=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 inset(0.01sec)
显然 id 为 3 的数据已更新,接着使用 delete 语句删除 id 为 3 和 33 的记录,执行如下:
- mysql>delete from user whereid =3 andid=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 inset(0.00sec)
对于增删改都比较简单,这里我们主要来细说一下查询,因为查询可以更加不同条件组合来获取不同的查询结果,这点还是比较有意思的。
先来看看条件查询,前面我们更新和删除时都使用到了条件语句,使用 where 子句指明要删除记录的 id 是哪个,也就是指明删除的符合具体条件的行数据,同样的条件也适合 select 语句,通过 where 子句查询,可以过滤不是期望的数据,下面通过一个例子来演示,查询 id 大于 15 的所有数据,执行语句如下:
- mysql>select*from user whereid >15;+----+-----------+------------+------+--------------------+| id | username | birthday | sex | address |
- +----+-----------+------------+------+--------------------+| 16 | 任在明 | 1996-12-01 | 1 | 广东省广州市 |
- | 22 | 陈小明 | 1995-05-10 | 1 | 广东省深圳市 |
- | 24 | 任传海 | 1992-03-08 | 1 | 海南三亚 |
- +----+-----------+------------+------+--------------------+6 rows inset(0.00sec)
其中
属于比较运算符 ,如上面 id 大于 15 的所有数据将会被检索出来,而 id 小于 15 的就被过滤掉了,当然在 where 子句中还可使用其他运算符,如下
- >
运算符 | 描述 | 演示 |
---|---|---|
|
相等 |
|
|
大于 |
|
|
小于 |
|
|
大于等于 |
|
|
小于等于 |
|
|
不相等 |
|
|
为 NULL(不为 NULL) |
|
|
模糊查询,指向模糊查询目标 |
|
|
(不包含) 包含在指定范围内 |
|
|
包含在指定范围值内 |
|
NULL 条件有点需要注意,在使用 NULL 条件检索时不能使用
号,必须使用 is 指明查询条件的值为空,当然如果是 not null 那就是非空数据了,如:
- =
- #查询生日为空的用户
- mysql>select*from user wherebirthdayis NULL;+----+----------+----------+------+---------+| id | username | birthday | sex | address |
- +----+----------+----------+------+---------+| 1 | 王五 | NULL | 2 | NULL |
- +----+----------+----------+------+---------+1 row inset(0.00sec)
关键字 like 主要用于模糊查询,如下查询名称为'任'开头的用户:
- mysql>selectusernamefrom user whereusernamelike '任%';+-----------+| username |
- +-----------+| 任在明 |
- | 任传海 |
- +-----------+2 rows inset(0.00sec)
其中
称为外卡符号,代表 0 个以上的字符。如上述的【
- %
】代表以任开头的所有字符串。如果是 【
- 任%
】则代表以任结尾的所有字符串,而【
- %任
】代表所有含有任字的字符串。当然除了
- %任%
是外卡符号,还有
- %
下划线也是外卡符号,,代表一个字符,也就是说条件为
- '_'
时 只有【任良】是符合条件而【任其阿】则是不符合条件。同理加上 NOT 则取反的意思。
- 任_
BETWEEN 是让我们可以运用一个范围 (range) 内抓出数据库中的值。执行语句如下:
- #查询id在1到15之间的用户(包括1和15)
- mysql>select*from user whereid between1 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 inset(0.00sec)
表示某一组指明的数据,在括弧内可以有一或多个值,而不同值之间由逗点分开。值可以是数目或是文字。如下语句:
- #查询id为1,2,3的用户
- mysql>select*from user whereidin(1,2,3);+----+-----------------------+------------+------+--------------------+| id | username | birthday | sex | address |
- +----+-----------------------+------------+------+--------------------+| 1 | 王五 | NULL | 2 | NULL |
- | 2 | 张曹宇 | 1990-08-05 | 1 | 广东省汕头市 |
- | 3 | 大王让我来巡山 | 1909-12-12 | 1 | 北京朝阳 |
- +----+-----------------------+------------+------+--------------------+4 rows inset(0.00sec)
当需要在查询中使用多个条件组合时,可以使用 AND 或者 OR ,其中指明两个条件必须成立,而 OR 则需要一个条件成立即可,如下语句使用 AND 进行条件组合查询:
- #查询生日为空并且sex=1的用户
- mysql>select*from user wherebirthdayis null andsex =1;+----+----------+----------+------+---------+| id | username | birthday | sex | address |
- +----+----------+----------+------+---------+| 1 | 王五 | NULL | 1 | NULL |
- +----+----------+----------+------+---------+
来源: http://blog.csdn.net/javazejian/article/details/61614366