Mysql数据库——01基本操作

目录

数据库操作

表操作

数据库的增删改查

别名运算符和排序

运算符

比较运算符 

逻辑运算符

排序

数据库三范式 

---

数据库操作

数据库作为数据表存储的基本单位，即一个数据库存储一张和多张表，同一个数据库中数据表可以关联查询。一般的，在实际开发中每个项目都会使用独立的一个数据库，一个项目也不建议连接多个数据库。

CREATE DATABASE mydb

当再次执行时会报错，数据库 mydb 已经存在（exists）

错误代码： 1007
Can't create database 'mydb'; database exists

使用命令查看所有的数据库

SHOW DATABASES

删除数据库

DROP DATABASE mydb;

使用数据库，方便在数据库中创建表

USE mydb;

表操作

数据库中每列数据的标识叫字段，表操作一般就是针对字段的操作。创建表时一般会有一个或多个字段，它们之间用逗号隔开。数据库中最常用的两种字段类型为 int 和 char 分别用于存储整数和字符串类型的数据，char 类型后需要指定存储的字符个数。

CREATE TABLE mytb (
    id INT,
    name CHAR(5)
);

创建完表后可以使用 desc 表名查看表结构

mysql> desc mytb;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
| name  | char(5) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

字段的增删改操作，下面 SQL 演示如何操作字段

ALTER TABLE mytb  DROP id;  删除字段
ALTER TABLE mytb  ADD ids INT;  增加字段

ALTER TABLE mytb  ADD id INT FIRST;  添加一个字段在最前面
ALTER TABLE mytb  ADD sex INT AFTER name; 增加一个字段在 name 后

ALTER TABLE mytb MODIFY name CHAR(10);修改 name 字段的长度为 10
ALTER TABLE mytb MODIFY ids BIGINT NOT NULL DEFAULT 100;修改 ids 字段的属性为 long 不为空默认值为 100

修改表名为 mytable

ALTER TABLE mytb RENAME TO mytable;

刪除表 mytable

DROP TABLE mytable

注意：

删除表有 drop [drɒp]，truncate [trʌŋˈkeɪt]，delete [dɪˈliːt] 三种操作，他们的区别如下：

drop：删除数据和表结构，释放空间。

drop table user;

truncate：保留表结构，删除数据，释放空间。

truncate table user;

delete：保留表结构，删除数据，不释放空间。

delete from user;

三者的执行速度，一般来说：drop > truncate > delete

释放空间可以体现在：

1.通过 delete 删除的行数据是不释放空间的，如果表 id 是递增式的话，那么表数据的 id 就可能不是连续的；而通过 truncate 删除数据是释放空间的，如果表 id 是递增式的话，新增数据的 id 又是从头开始，而不是在已删数据的最大 id 值上递增。

2.使用 delete 删除数据时，mysql 并没有把数据文件删除，而是将数据文件的标识位删除，没有整理文件，因此不会彻底释放空间。被删除的数据将会被保存在一个链接清单中，当有新数据写入的时候，mysql 会利用这些已删除的空间再写入。即，删除操作会带来一些数据碎片，正是这些碎片在占用硬盘空间。

数据库的增删改查

数据库的每条数据通常称为一条记录，记录的常用操作通常就是增删改查，往数据表中插入记录使用如下语法：

INSERT INTO table [(字段名 [, 字段名...])]
VALUES		(value [, value...]);

注：

• 插入的数据应与字段的数据类型相同。 
• 数据的大小应在列的规定范围内，例如：不能将一个长度为 80 的字符串加入到长度为 40 的列中。 
• 在 values 中列出的数据位置必须与被加入的列的排列位置相对应。 字符和日期型数据应包含在单引号中。 
• 插入空值，不指定或 insert into table value(null) 即可插入空值。

示例：

INSERT INTO `emp` (`name`,`sal`) VALUES ('张三',10000.5);

修改记录

修改数据表中记录使用如下语法：

UPDATE table_name SET 字段名1=expr1 [, 字段名2=expr2 ...] [WHERE where_definition]

注意：

• UPDATE 语法可以用新值更新原有表行中的各列。 
• SET 子句指示要修改哪些列和要给予哪些值。 
• WHERE 子句指定应更新哪些行。如没有 WHERE 子句，则更新所有的行。

示例：

UPDATE emp SET `name` ='李四'
UPDATE emp SET `name`='王二' WHERE `id` = 1;
UPDATE emp SET `name`='王二',`sal`=16.5 WHERE `id` = 1;
UPDATE emp SET `name`='王二',`sal`=16.5 WHERE `id` IN(1,3,5);

 注意：如果在数据修改时，该表的全部字段相同，修改命令发送时会导致多条数据被修改。同时 SQLYog 会弹窗。所以为了避免这种问题数据库各大厂商提出数据库三大范式作为约束。

删除记录

删除数据表的数据使用如下语法：

delete from table_name [WHERE where_definition]

注意：

• 如果不使用 where 子句，将删除表中所有数据。 
• 使用 delete 语句仅删除记录，不删除表本身。
• 如要删除表，使用 drop table 语句。 

示例：

delete from `emp` where id = 1;

简单查询

查询数据表数据一般使用如下语法：

SELECT [DISTINCT] *|{字段名1, 字段名2, 字段名3..} FROM table;

参数：

• Select 指定查询哪些列的数据。 
• * 号代表查询所有列，在实际开发中很少用 *，它会增加查询的负担。 
• From 指定查询哪张表。 
• DISTINCT [dɪˈstɪŋkt] 可选，指显示结果时，是否剔除重复数据 

示例：

SELECT * FROM `emp`;
SELECT `name`,`sal` FROM `emp`;
SELECT DISTINCT `name`,`sal` FROM `emp`;

别名运算符和排序

在查询过程中，使用原字段的名称有时候不太方便，MySQL 提供别名处理这样的情况。

SELECT 字段名 AS 别名 FROM 表名;

AS 可以省略

示例：

SELECT `name` 姓名,`sal` 薪水 FROM `emp`;

注意：别名处可以不使用单引号，但是为了不和数据库的一些关键字冲突建议所有表名、字段名都加上数据库专用单引号（一般位于 tab 键上方）。

运算符

在 WHERE 子句中经常使用相关的运算符，常见运算符有比较运算符、逻辑运算符等。

比较运算符 

>   <   <=   >=   =    <>/!=	大于、小于、大于(小于)等于、不等于
BETWEEN  ...AND...	        显示在某一区间的值(含头含尾)
IN(set)	                        显示在 in 列表中的值，例：in(100,200)
LIKE ‘%张_’	                模糊查询
IS NULL	                        判断是否为空（is not null）

LIKE 语句中，% 代表零个或多个任意字符，_ 代表一个字符。

示例：

name LIKE ‘_a%'    表示第二个字符为 a 的人
name like '张%'     姓张的所有人
name like '_伟'     所有姓名为两个字且第二个字为 “伟”
name like '%商%'    姓名中包含 “商”字
name like '%友'     姓名以“友”结尾

逻辑运算符

and	                        多个条件同时成立
or	                        多个条件任一成立
not	                        不成立，例：where not(salary>100);

示例：

SELECT `id`, `name`,`sal` FROM `emp` WHERE id > 1;
SELECT `id`, `name`,`sal` FROM `emp` WHERE id BETWEEN 1 AND 3;
SELECT `id`, `name`,`sal` FROM `emp` WHERE `name` IS NULL;
SELECT `id`, `name`,`sal` FROM `emp` WHERE `name` LIKE '%王%' AND id = 1;

排序

排序的列即可是表中的列名，也可以是 SELECT 语句后指定的列名。 

ASC 升序、DESC 降序 

ORDER BY 子句应位于 SELECT 语句的结尾。 

案例：

SELECT `id`, `name`,`sal` FROM `emp` WHERE `id` > 0 ORDER BY `name` ASC ;
SELECT * FROM `mytabe` ORDER BY `sex` DESC,`id` DESC

数据库三范式 

第一范式（1NF）： 字段具有原子性，不可再分。所有关系型数据库系统都满足第一范式

数据库表中的字段都是单一属性的，不可再分。例如，姓名字段，其中的姓和名必须作为一个整体，无法区分哪部分是姓，哪部分是名，如果要区分出姓和名，必须设计成两个独立的字段。 

第二范式（2NF）： 第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。 

要求数据库表中的每个实例或行必须可以被惟一地区分。通常需要为表加上一个列，以存储各个实例的唯一标识。这个唯一属性列被称为主关键字或主键。 实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。简而言 之，第二范式就是非主属性非部分依赖于主关键字。 

第三范式（3NF）： 满足第三范式（3NF）必须先满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。 所以第三范式具有如下特征： 1，每一列只有一个值 2，每一行都能区分。 3，每一个表都不包含其他表已经包含的非主关键字信息。 例如，帖子表中只能出现发帖人的 id，而不能出现发帖人的 id，还同时出现发帖人姓名，否则，只要出现同一发帖人 id 的所有记录，它们中的姓名部分都必须严格保持一致，这就是数据冗余。