【MySQL】3. MySQL基础 - 表操作

最新推荐文章于 2024-05-04 23:16:16 发布

Harry-L

最新推荐文章于 2024-05-04 23:16:16 发布

阅读量210

点赞数 2

分类专栏： MySQL

本文链接：https://blog.csdn.net/l75326747/article/details/88051085

版权

MySQL 专栏收录该内容

6 篇文章 1 订阅

订阅专栏

1. MySQL数据类型

MySQL中定义数据字段的类型对你数据库的优化是非常重要的。

MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型

数值类型

类型	大小	范围（有符号 signed）	范围（无符号 unsigned）	用途
TINYINT	1 字节	(-128，127)	(0，255)	小整数值
SMALLINT	2 字节	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 字节	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 字节	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 字节	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 字节	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度浮点数值
DOUBLE	8 字节	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

日期和时间类型

每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。

类型	大小 (字节)	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	'-838:59:59'/'838:59:59'	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:00/2038 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日凌晨 03:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

字符串类型

类型	大小	用途
CHAR	0-255字节	定长字符串
VARCHAR	0-65535 字节	变长字符串
TINYBLOB	0-255字节	不超过 255 个字符的二进制字符串
TINYTEXT	0-255字节	短文本字符串
BLOB	0-65 535字节	二进制形式的长文本数据
TEXT	0-65 535字节	长文本数据
MEDIUMBLOB	0-16 777 215字节	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215字节	中等长度文本数据
LONGBLOB	0-4 294 967 295字节	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295字节	极大文本数据

2. 创建表 CREATE TABLE

创建表时需要给出的信息：表名、表字段名、定义每个表字段

通用语法

CREATE TABLE table_name (column_name column_type);

实例：

CREATE TABLE IF NOT EXISTS `runoob_tbl`(
   `runoob_id` INT UNSIGNED AUTO_INCREMENT,
   `runoob_title` VARCHAR(100) NOT NULL,
   `runoob_author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL，在操作数据库时如果输入该字段的数据为NULL ，就会报错。
AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。
PRIMARY KEY关键字用于定义列为主键。您可以使用多列来定义主键，列间以逗号分隔。
ENGINE 设置存储引擎，CHARSET 设置编码。

如果已经有一个表 table，希望创建的新表与 table 有同样的结构，可以用以下语法复制：

CREATE TABLE table_copy LIKE table;

3. 添加数据 INSERT INTO

通用语法

-- 插入单行;
INSERT INTO table_name(field1, field2,...fieldN)
VALUES (value1, value2,...valueN);
-- 插入多行;
INSERT INTO table_name(field1, field2,...fieldN)
VALUES (value1, value2,...valueN),
       (value1, value2,...valueN),
       ...;

注意：

如果数据是字符型，必须使用单引号或双引号。
不必为自动递增列(例如上文提到的runoob_id列)指定值，因为MySQL会自动为自动递增列生成值。

如果要为表中所有列指定值，则可以忽略 INSERT 语句中的列名：

-- 插入单行;
INSERT INTO table_name
VALUES (value1, value2,...valueN);
-- 插入多行;
INSERT INTO table_name
VALUES (value1, value2,...valueN),
       (value1, value2,...valueN),
       ...;

使用 SELECT 子句进行插入

INSERT INTO table_1
SELECT c1, c2, FROM table_2;

4. 删除数据

删除部分数据 DELETE

DELETE FROM table_name
WHERE condition;

如果指定 where 子句则删除满足记录的行，如果不指定，则删除表中的所有行。
除了删除表中的数据之外， DELETE 语句返回删除的行数
如果需要限制删除的行数，则使用 LIMIT 子句，一般与 ORDER BY 共用：
```
DELETE FROM table_name
ORDER BY c1, c2, ...
LIMIT row_count;
```

删除全表数据 TRUNCATE

TRUNCATE TABLE table_name;

TRUNCATE TABLE语句允许您删除表中的所有数据。
语句后面不需要跟 WHERE 子句，一般比 DELETE 语句跟高效。
如果表中有自增列，则会将此重置为零

删除表 DROP

DROP TABLE table_name ;

删除表及表中的所有数据

三者区别

delete语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存，以便进行进行回滚操作。
执行速度一般来说：drop>truncate>delete
delete语句是数据库操作语言(dml)，这个操作会放到 rollback segement 中，事务提交之后才生效；如果有相应的 trigger，执行的时候将被触发。
runcate、drop 是数据库定义语言(ddl)，操作立即生效，原数据不放到 rollback segment 中，不能回滚，操作不触发trigger。
truncate语句执行以后，id标识列还是按顺序排列，保持连续；而delete语句执行后，ID标识列不连续

5. 修改表结构 ALTER

ALTER TABLE 语句可用来添加列，删除列，更改列的数据类型，添加主键，重命名表等等。以下说明了ALTER TABLE语句语法：

ALTER TABLE table_name action1[,action2,…]

修改列名及类型 CHANGE、MODIFY

使用 CHANGE 关键字修改表名与类型：

ALTER TABLE tasks
CHANGE task_id task_id_new INT(11) NOT NULL AUTO_INCREMENT;

CHANGE 关键字之后，紧跟着的是你要修改的字段名，然后指定新字段名及类型

如果不需要更改列名，也可以使用 MODIFY 关键字：

ALTER TABLE tasks
MODIFY task_id INT(11) NOT NULL AUTO_INCREMENT;

新建列 ADD

ALTER TABLE tasks 
ADD col_name DECIMAL(2,1) NULL
AFTER col_name1;

若不指定位置，则默认插入到最后一列。若要插入在第一列，可以使用 FIRST 关键字

删除列 DROP

ALTER TABLE tasks 
DROP col_name;

重命名表名

ALTER TABLE tasks
RENAME TO tacks2;

6. 修改数据 UPDATE

通用语法

UPDATE table_name SET field1=new_value1, field2=new_value2
[WHERE Clause]

你可以同时更新一个或多个字段。
你可以在 WHERE 子句中指定任何条件，不指定 WHERE 子句将进行全表更新。
你可以在一个单独表中同时更新数据。

实例

1. 超过5名学生的课：

创建如下所示的 courses 表；编写一个SQL查询，列出所有超过或等于5名学生的课，学生在每个课中不应被重复计算。

表内容：
+---------+------------+
| student | class      |
+---------+------------+
| A       | Math       |
| B       | English    |
| C       | Math       |
| D       | Biology    |
| E       | Math       |
| F       | Computer   |
| G       | Math       |
| H       | Math       |
| I       | Math       |
| A       | Math       |
+---------+------------+
超过或等于5名学生的课：
+---------+
| class   |
+---------+
| Math    |
+---------+

代码：

-- 创建表
CREATE TABLE IF NOT EXISTS courses (
	student CHAR (10),
	class CHAR (20)
);

-- 插入数据
INSERT INTO courses
VALUES
	("A", "Math"),
	("B", "English"),
	("C", "Math"),
	("D", "Biology"),
	("E", "Math"),
	("F", "Computer"),
	("G", "Math"),
	("H", "Math"),
	("I", "Math"),
	("A", "Math")
;

-- 查询语句
SELECT class FROM courses GROUP BY class HAVING COUNT(DISTINCT student) >= 5;

2. 交换工资

创建一个 salary表，如下所示；交换所有的 f 和 m 值(例如，将所有 f 值更改为 m，反之亦然)。要求使用一个更新查询，并且没有中间临时表。

原始表格：
+----+------+------+--------+
| id | name | sex  | salary |
+----+------+------+--------+
|  1 | A    | m    |   2500 |
|  2 | B    | f    |   1500 |
|  3 | C    | m    |   5500 |
|  4 | D    | f    |    500 |
+----+------+------+--------+
交换后：
+----+------+------+--------+
| id | name | sex  | salary |
+----+------+------+--------+
|  1 | A    | f    |   2500 |
|  2 | B    | m    |   1500 |
|  3 | C    | f    |   5500 |
|  4 | D    | m    |    500 |
+----+------+------+--------+

代码：

-- 创建表
CREATE TABLE
IF NOT EXISTS salary (
	`id` INT UNSIGNED AUTO_INCREMENT,
	`name` CHAR (10),
	`sex` CHAR (5),
	`salary` INT,
	PRIMARY KEY (id)
);

-- 插入数据
INSERT INTO salary (`name`, `sex`, `salary`)
VALUES
	("A", "m", 2500),
	("B", "f", 1500),
	("C", "m", 5500),
	("D", "f", 500);

-- 交换 f 和 m 的值
UPDATE salary
SET sex = (CASE sex WHEN "f" THEN "m" ELSE "f" END);