五分钟带你了解表的基本操作

刘刘同学Y

已于 2023-08-10 09:30:52 修改

阅读量1.1k

点赞数

文章标签：数据库

于 2023-08-09 21:19:03 首次发布

本文链接：https://blog.csdn.net/qq_69850554/article/details/132174320

版权

表的创建

CREATE TABLE
简单语法：
    CREATE TABLE 表名（
    列名 列类型，
    列名 列类型
    ）;

功能：在当前数据库中创建一张表

查看表

SHOW TABLES[FROM 数据库名][LIKE wild];

{DESCRIBE|DESC}表名[列名];
# or
show columns from 表名称;

删除表

DROP TABLE

语法：
	DROP TABLE [IF EXISTS] 表名；

功能：删除指定的表

修改表

修改列类型
	ALTER TABLE 表名 MODIFY 列名 列类型;
	
增加列
	ALTER TABLE 表名 ADD 列名 列类型;
	
删除列
	ALTER TABLE 表名 DROP 列名;
	
列改名
	ALTER TABLE 表名 CHANGE 旧列名 新列名 列类型;
更改表名
	ALTER TABLE 表名 RENAME 新表名;
	RENAME TABLE 表名 TO 新表名;

复制表

复制一个表结构的实现方法有两种

方法一：在create table语句的末尾添加like子句，可以将源表的表结构复制到新表中，语法格式如下。
create table 新表名 like 源表

方法二：在create table语句的末尾添加一个select语句，可以实现表结构的复制，甚至可以将源表的表
记录拷贝到新表中。下面的语法格式将源表的表结构以及源表的所有记录拷贝到新表中。
create table 新表名 select * from 源表


方法三：如果已经存在一张机构一致的表，复制数据
insert into 表 select * from 原表;

表的约束

NOT NULL非空
UNIQUE Key唯一键
PRIMARY KEY主键
FOREIGN KEY外键
CHECK检查
默认值约束

约束作为数据库对象，存放在系统表中，也有自己的名字
创建约束的时机
    在建表的同时创建
    建表后创建（修改表）
可定义列级或表级约束
有单列约束和多列约束

定义约束的语法

列级约束：在定义列的同时定义约束
	语法：列定义 约束类型,
	
表级约束：在定义了所有列之后定义的约束
	语法：
    列定义
    [CONSTRAINT 约束名] 约束类型(列名)

约束名的取名规则
	推荐采用：表名_列名_约束类型简介
约束可以在创建表时就定义，也可以在创建完后再添加

语法：
	alter table 表名 add constraint 约束名 约束类型(要约束的列名)

示例：

1、非空约束(NOT NULL)
列级约束，只能使用列级约束语法定义。
确保字段值不允许为空
只能在字段级定义

CREATE TABLE tb_student(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(18) NOT NULL
)

NULL
所有数据类型的值都可以是NULL。
空字符串不等于NULL。
0也不等于NULL。


2、唯一约束
唯一性约束条件确保所在的字段或者字段组合不出现重复值
唯一性约束条件的字段允许出现多个NULL
同一张表内可建多个唯一约束
唯一约束可由多列组合而成
建唯一约束时MySQL会为之建立对应的索引。
如果不给唯一约束起名，该唯一约束默认与列名相同。

CREATE TABLE tb_student(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(18) UNIQUE NOT NULL
)


3、主键约束
主键从功能上看相当于非空且唯一
一个表中只允许一个主键
主键是表中唯一确定一行数据的字段
删除表的约束
自动增长和默认值
存储引擎
主键字段可以是单字段或者是多字段的组合
当建立主键约束时，MySQL为主键创建对应的索引
主键约束名总为PRIMARY。

CREATE TABLE tb_student(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(18)
)

4、外键约束
外键是构建于一个表的两个字段或者两个表的两个字段之间的关系
外键确保了相关的两个字段的两个关系：
子(从)表外键列的值必须在主表参照列值的范围内，或者为空（也可以加非空约束，强制不允许为空）。
当主表的记录被子表参照时，主表记录不允许被删除。
外键参照的只能是主表主键或者唯一键，保证子表记录可以准确定位到被参照的记录。
	
格式FOREIGN KEY (外键列名)REFERENCES 主表(参照列)

CREATE TABLE tb_dept(
    dept_id INT PRIMARY KEY,
    NAME VARCHAR(18),
    description VARCHAR(255)
);

CREATE TABLE tb_employee(
    employee_id INT PRIMARY KEY,
    NAME VARCHAR(18),
    gender VARCHAR(10),
    dept_id INT REFERENCES tb_dept(dept_id),
    address VARCHAR(255)
);


# 检查约束

# 注意检查约束在8.0之前，MySQL默认但不会强制的遵循check约束（写不报错，但是不生效，需要通触发器完成）
# 8之后就开始正式支持这个约束了。
create table t3(
    id int, 
    age int check(age > 18),
    gender char(1) check(gender in ('M','F'))
);


# 默认值
可以使用default关键字设置每一个字段的默认值。
-- 创建一张user表
CREATE TABLE `test`.`user`(  
  `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `name` VARCHAR(225) COMMENT '姓名',
  `sex` TINYINT(1) DEFAULT 1 COMMENT '性别 1男 0女',
  PRIMARY KEY (`id`)
) ENGINE=INNODB CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

删除表的约束：

删除NOT NULL约束
	alter table 表名 modify 列名 类型;

删除UNIQUE约束
	alter table 表名 drop index 惟一约束名;

删除PRIMARY KEY约束
	alter table 表名 drop primary key;

删除FOREIGN KEY约束
	alter table 表名 drop foreign key 外键名;

自动增长和默认值：

auto_increment ：自动增长
	为新的行产生唯一的标识
	一个表只能优一个auto_increment，且该属性必须为主键的一部分。auto_increment的属性可以是任何整数类型


default : 默认值

例题：

单表查询
素材：表名：worker-- 表中字段均为中文，比如部门号工资职工号参加工作等
CREATE TABLE `worker` (
`部门号` int(11) NOT NULL,
`职工号` int(11) NOT NULL,
`工作时间` date NOT NULL,
`工资` float(8,2) NOT NULL,
`政治面貌` varchar(10) NOT NULL DEFAULT '群众',
`姓名` varchar(20) NOT NULL,
`出生日期` date NOT NULL,
PRIMARY KEY (`职工号`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
INSERT INTO `worker` (`部门号`, `职工号`, `工作时间`, `工资`, `政治面貌`, `姓名`, `出生
日期`) VALUES (101, 1001, '2015-5-4', 3500.00, '群众', '张三', '1990-7-1');
INSERT INTO `worker` (`部门号`, `职工号`, `工作时间`, `工资`, `政治面貌`, `姓名`, `出生
日期`) VALUES (101, 1002, '2017-2-6', 3200.00, '团员', '李四', '1997-2-8');
INSERT INTO `worker` (`部门号`, `职工号`, `工作时间`, `工资`, `政治面貌`, `姓名`, `出生
日期`) VALUES (102, 1003, '2011-1-4', 8500.00, '党员', '王亮', '1983-6-8');
INSERT INTO `worker` (`部门号`, `职工号`, `工作时间`, `工资`, `政治面貌`, `姓名`, `出生
日期`) VALUES (102, 1004, '2016-10-10', 5500.00, '群众', '赵六', '1994-9-5');
INSERT INTO `worker` (`部门号`, `职工号`, `工作时间`, `工资`, `政治面貌`, `姓名`, `出生
日期`) VALUES (102, 1005, '2014-4-1', 4800.00, '党员', '钱七', '1992-12-30');
INSERT INTO `worker` (`部门号`, `职工号`, `工作时间`, `工资`, `政治面貌`, `姓名`, `出生
日期`) VALUES (102, 1006, '2017-5-5', 4500.00, '党员', '孙八', '1996-9-2');

1、显示所有职工的基本信息。

mysql> SELECT *FROM worker;
+-----------+-----------+--------------+---------+--------------+--------+--------------+
| 部门号    | 职工号    | 工作时间     | 工资    | 政治面貌     | 姓名   | 出生日期     |
+-----------+-----------+--------------+---------+--------------+--------+--------------+
|       101 |      1001 | 2015-05-04   | 3500.00 | 群众         | 张三   | 1990-07-01   |
|       101 |      1002 | 2017-02-06   | 3200.00 | 团员         | 李四   | 1997-02-08   |
|       102 |      1003 | 2011-01-04   | 8500.00 | 党员         | 王亮   | 1983-06-08   |
|       102 |      1004 | 2016-10-10   | 5500.00 | 群众         | 赵六   | 1994-09-05   |
|       102 |      1005 | 2014-04-01   | 4800.00 | 党员         | 钱七   | 1992-12-30   |
|       102 |      1006 | 2017-05-05   | 4500.00 | 党员         | 孙八   | 1996-09-02   |
+-----------+-----------+--------------+---------+--------------+--------+--------------+
6 rows in set (0.00 sec)

2、查询所有职工所属部门的部门号，不显示重复的部门号。

mysql> SELECT DISTINCT `部门号` FROM `worker`;
+-----------+
| 部门号    |
+-----------+
|       101 |
|       102 |
+-----------+
2 rows in set (0.00 sec)

3、求出所有职工的人数。

mysql> SELECT COUNT(*) AS 总人数 FROM `worker`;
+-----------+
| 总人数    |
+-----------+
|         6 |
+-----------+
1 row in set (0.00 sec)

4、列出最高工资和最低工资。

mysql> SELECT MAX(`工资`) AS 最高工资, MIN(`工资`) AS 最低工资 FROM `worker`;
+--------------+--------------+
| 最高工资     | 最低工资     |
+--------------+--------------+
|      8500.00 |      3200.00 |
+--------------+--------------+
1 row in set (0.00 sec)


mysql> SELECT `工资` 
    -> FROM `worker`
    -> WHERE `工资` = (SELECT MAX(`工资`) FROM `worker`)
    ->    OR `工资` = (SELECT MIN(`工资`) FROM `worker`);
+---------+
| 工资    |
+---------+
| 3200.00 |
| 8500.00 |
+---------+
2 rows in set (0.00 sec)

5、列出职工的平均工资和总工资。

mysql> SELECT AVG(`工资`) AS 平均工资, SUM(`工资`) AS 总工资 FROM `worker`;
+--------------+-----------+
| 平均工资     | 总工资    |
+--------------+-----------+
|  5000.000000 |  30000.00 |
+--------------+-----------+
1 row in set (0.01 sec)

6、创建一个只有职工号、姓名和参加工作的新表，名为工作日期表。

mysql> CREATE TABLE `工作日期表` SELECT `职工号`,`姓名`,`工作时间` FROM worker;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC `工作日期表`;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| 职工号       | int         | NO   |     | NULL    |       |
| 姓名         | varchar(20) | NO   |     | NULL    |       |
| 工作时间     | date        | NO   |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

7、显示所有女职工的年龄。

8、列出所有姓刘的职工的职工号、姓名和出生日期。

mysql> SELECT `职工号`, `姓名`, `出生日期`
    -> FROM `worker`
    -> WHERE `姓名` LIKE '刘%';
Empty set (0.00 sec)

9、列出1960年以前出生的职工的姓名、参加工作日期。

mysql> SELECT `姓名`, `工作时间`
    -> FROM `worker`
    -> WHERE `出生日期` < '1960-01-01';
Empty set (0.00 sec)

10、列出工资在1000－2000之间的所有职工姓名。

mysql> SELECT `姓名`
    -> FROM `worker`
    -> WHERE `工资` >= 1000 AND `工资` <= 2000;
Empty set (0.09 sec)

11、列出所有陈姓和李姓的职工姓名。

mysql> SELECT `姓名` 
    -> FROM `worker` 
    ->  WHERE `姓名` LIKE "陈%" OR `姓名` LIKE "李%";
+--------+
| 姓名   |
+--------+
| 李四   |
+--------+
1 row in set (0.00 sec)

12、列出所有部门号为2和3的职工号、姓名、党员否。

mysql> SELECT 职工号, 姓名, CASE WHEN 政治面貌='党员' THEN '是' ELSE '否' END AS 党员否 FROM worker WHERE 部门号 IN (2, 3);
Empty set (0.00 sec)

13、将职工表worker中的职工按出生的先后顺序排序。

mysql> SELECT * FROM worker ORDER BY `出生日期`;
+-----------+-----------+--------------+---------+--------------+--------+--------------+
| 部门号    | 职工号    | 工作时间     | 工资    | 政治面貌     | 姓名   | 出生日期     |
+-----------+-----------+--------------+---------+--------------+--------+--------------+
|       102 |      1003 | 2011-01-04   | 8500.00 | 党员         | 王亮   | 1983-06-08   |
|       101 |      1001 | 2015-05-04   | 3500.00 | 群众         | 张三   | 1990-07-01   |
|       102 |      1005 | 2014-04-01   | 4800.00 | 党员         | 钱七   | 1992-12-30   |
|       102 |      1004 | 2016-10-10   | 5500.00 | 群众         | 赵六   | 1994-09-05   |
|       102 |      1006 | 2017-05-05   | 4500.00 | 党员         | 孙八   | 1996-09-02   |
|       101 |      1002 | 2017-02-06   | 3200.00 | 团员         | 李四   | 1997-02-08   |
+-----------+-----------+--------------+---------+--------------+--------+--------------+
6 rows in set (0.01 sec)

14、显示工资最高的前3名职工的职工号和姓名。

mysql> SELECT 职工号, 姓名 FROM worker ORDER BY 工资 DESC LIMIT 3;
+-----------+--------+
| 职工号    | 姓名   |
+-----------+--------+
|      1003 | 王亮   |
|      1004 | 赵六   |
|      1005 | 钱七   |
+-----------+--------+
3 rows in set (0.00 sec)

15、求出各部门党员的人数。

mysql> SELECT 部门号, COUNT(*) AS 党员人数 FROM worker WHERE 政治面貌='党员' GROUP BY 部门号;
+-----------+--------------+
| 部门号    | 党员人数     |
+-----------+--------------+
|       102 |            3 |
+-----------+--------------+
1 row in set (0.00 sec)

16、统计各部门的工资和平均工资

mysql> SELECT 部门号, SUM(工资) AS 总工资, AVG(工资) AS 平均工资 FROM worker GROUP BY 部门号;
+-----------+-----------+--------------+
| 部门号    | 总工资    | 平均工资     |
+-----------+-----------+--------------+
|       101 |   6700.00 |  3350.000000 |
|       102 |  23300.00 |  5825.000000 |
+-----------+-----------+--------------+
2 rows in set (0.00 sec)

17、列出总人数大于4的部门号和总人数。

mysql> SELECT 部门号, COUNT(*) AS 总人数 FROM worker GROUP BY 部门号 HAVING COUNT(*) > 4;
Empty set (0.00 sec)

刘刘同学Y

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
五分钟带你了解表的基本操作

功能：在当前数据库中创建一张表。
复制链接

扫一扫

五分钟带你了解表的基本操作

表的创建

查看表

删除表

修改表

复制表

表的约束

例题：

“相关推荐”对你有帮助么？