Day02_数据库基础02

最新推荐文章于 2026-06-23 22:50:02 发布

原创最新推荐文章于 2026-06-23 22:50:02 发布 · 159 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#mysql #数据挖掘

本文详细介绍SQL中表结构的创建、修改与数据操作，包括字段类型、表操作语法、数据查询、排序、分组、聚合函数及分页查询等核心内容。

一、alter操作

1 语法

#1.修改表名
语法规则：ALTER TABLE old_table_name RENAME [TO] new_table_name

#2.修改字段的数据类型
语法规则：ALTER TABLE table_name MODIFY 字段名 数据类型
修改完成之后可以查看DESC table_name检验结果

#3.修改字段名
语法规则：ALTER TABLE table_name CHANGE 旧字段名 新字段名 数据类型

#4.添加字段
语法规则：ALTER TABLE table_name ADD 新字段名 数据类型 [约束条件] [FIRST|AFTER 已经存在的字段名]

#5.删除字段
语法规则：ALTER TABLE table_name DROP 字段名

#6.修改字段的排列位置
语法规则：ALTER TABLE table_name MODIFY 字段1 数据类型 FIRST|AFTER 字段2
	first: 设置成第一个
	after 字段2： 在指定字段2的后面
	
#7.删除表的外键约束
语法规则：ALTER TABLE table_name DROP FOREIGN KEY 外键约束名

#8.删除数据表
#删除没有被关联的表
语法规则：DROP TABLE [IF EXISTS] 表1，表2...
#删除被其他表关联的的表
直接删除会出现错误的，操作： 先解除关联 再进行删除

2 常用数据类型

1.数字数据类型 
- INT - 正常大小的整数，可以带符号。如果是有符号的，它允许的范围是从-2147483648到2147483647。如果是无符号，允许的范围是从0到4294967295。 可以指定多达11位的宽度。
- TINYINT - 一个非常小的整数，可以带符号。如果是有符号，它允许的范围是从-128到127。如果是无符号，允许的范围是从0到255，可以指定多达4位数的宽度。
- SMALLINT - 一个小的整数，可以带符号。如果有符号，允许范围为-32768至32767。如果无符号，允许的范围是从0到65535，可以指定最多5位的宽度。
- MEDIUMINT - 一个中等大小的整数，可以带符号。如果有符号，允许范围为-8388608至8388607。 如果无符号，允许的范围是从0到16777215，可以指定最多9位的宽度。
- BIGINT - 一个大的整数，可以带符号。如果有符号，允许范围为-9223372036854775808到9223372036854775807。如果无符号，允许的范围是从0到18446744073709551615. 可以指定最多20位的宽度。
- FLOAT(M,D) - 不能使用无符号的浮点数字。可以定义显示长度(M)和小数位数(D)。这不是必需的，并且默认为10,2。其中2是小数的位数，10是数字(包括小数)的总数。小数精度可以到24个浮点。
- DOUBLE(M,D) - 不能使用无符号的双精度浮点数。可以定义显示长度(M)和小数位数(D)。 这不是必需的，默认为16,4，其中4是小数的位数。小数精度可以达到53位的DOUBLE。 REAL是DOUBLE同义词。
- DECIMAL(M,D) - 非压缩浮点数不能是无符号的。在解包小数，每个小数对应于一个字节。定义显示长度(M)和小数(D)的数量是必需的。 NUMERIC是DECIMAL的同义词。[decimal]

2.日期和时间类型
- DATE - 以YYYY-MM-DD格式的日期，在1000-01-01和9999-12-31之间。 例如，1973年12月30日将被存储为1973-12-30。
- DATETIME - 日期和时间组合以YYYY-MM-DD HH:MM:SS格式，在1000-01-01 00:00:00 到9999-12-31 23:59:59之间。例如，1973年12月30日下午3:30，会被存储为1973-12-30 15:30:00。
- TIMESTAMP - 1970年1月1日午夜之间的时间戳，到1973的某个时候。这看起来像前面的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。

3.字符串类型
虽然数字和日期类型比较有意思，但存储大多数数据都可能是字符串格式。 下面列出了在MySQL中常见的字符串数据类型。
- CHAR(M) - 固定长度的字符串是以长度为1到255之间个字符长度(例如：CHAR(5))，存储右空格填充到指定的长度。 限定长度不是必需的，它会默认为1。
- VARCHAR(M) - 可变长度的字符串是以长度为1到255之间字符数(高版本的MySQL超过255); 例如： VARCHAR(25). 创建VARCHAR类型字段时，必须定义长度。 [varchar]
- BLOB or TEXT - 字段的最大长度是65535个字符。 BLOB是“二进制大对象”，并用来存储大的二进制数据，如图像或其他类型的文件。定义为TEXT文本字段还持有大量的数据; 两者之间的区别是，排序和比较上存储的数据，BLOB大小写敏感，而TEXT字段不区分大小写。不用指定BLOB或TEXT的长度。
- TINYBLOB 或 TINYTEXT - BLOB或TEXT列用255个字符的最大长度。不指定TINYBLOB或TINYTEXT的长度。
- MEDIUMBLOB or 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)才能用来填充这个字段。

注意：主要了解 char 和 varchar 的区别

char(M)是固定长度的字符串，在定义时指定字符串列长。当保存数据时如果长度不够在右侧填充空格以达到指定的长度。M 表示列的长度，M 的取值范围是0-255个字符 name char(20)

varchar(M)是长度可变的字符串，M 表示最大的列长度。M 的取值范围是0-65535。varchar的最大实际长度是由最长的行的大小和使用的字符集确定的，而实际占用的空间为字符串的实际长度+1

name varchar(20)

主要使用的数据类型：
数字型数据类型：int float
日期类：date datetime
字符串：varchar(num) text【长字符串】

3 需求：创建一个员工表

#切换数据库
mysql> use mydb1			
Database changed
#查看当前正在使用的数据库
mysql> select database();	
+------------+
| database() |
+------------+
| mydb1      |
+------------+
1 row in set (0.00 sec)

#在当前数据库下创建新的表
mysql> create table worker(			
 -> id int(11) primary key,
 -> name varchar(20),
 -> gender varchar(10),
 -> brithday date,
 -> entry_date date,
 -> job varchar(20),
 -> salary double,
 -> resume blob
 -> );
Query OK, 0 rows affected (0.02 sec)

#显示当前数据库中的所有表
mysql> show tables;			
+-----------------+
| Tables_in_mydb1 |
+-----------------+
| worker          |
+-----------------+
1 row in set (0.00 sec)

#显示指定表中的所有字段
mysql> desc worker;			
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id         | int(11)     | YES  |     | NULL    |       |
| name       | varchar(20) | YES  |     | NULL    |       |
| gender     | varchar(10) | YES  |     | NULL    |       |
| brithday   | date        | YES  |     | NULL    |       |
| entry_date | date        | YES  |     | NULL    |       |
| job        | varchar(20) | YES  |     | NULL    |       |
| salary     | double      | YES  |     | NULL    |       |
| resume     | blob        | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
8 rows in set (0.01 sec)

#增加字段image
mysql> alter table worker add image blob;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc worker;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id         | int(11)     | YES  |     | NULL    |       |
| name       | varchar(20) | YES  |     | NULL    |       |
| gender     | varchar(10) | YES  |     | NULL    |       |
| brithday   | date        | YES  |     | NULL    |       |
| entry_date | date        | YES  |     | NULL    |       |
| job        | varchar(20) | YES  |     | NULL    |       |
| salary     | double      | YES  |     | NULL    |       |
| resume     | blob        | YES  |     | NULL    |       |
| image      | blob        | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
9 rows in set (0.00 sec)

#修改job的长度为60
mysql> alter table worker modify job varchar(60);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc worker;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id         | int(11)     | YES  |     | NULL    |       |
| name       | varchar(20) | YES  |     | NULL    |       |
| gender     | varchar(10) | YES  |     | NULL    |       |
| brithday   | date        | YES  |     | NULL    |       |
| entry_date | date        | YES  |     | NULL    |       |
| job        | varchar(60) | YES  |     | NULL    |       |
| salary     | double      | YES  |     | NULL    |       |
| resume     | blob        | YES  |     | NULL    |       |
| image      | blob        | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
9 rows in set (0.00 sec)

#删除image字段
mysql> alter table worker drop image;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc worker;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id         | int(11)     | YES  |     | NULL    |       |
| name       | varchar(20) | YES  |     | NULL    |       |
| gender     | varchar(10) | YES  |     | NULL    |       |
| brithday   | date        | YES  |     | NULL    |       |
| entry_date | date        | YES  |     | NULL    |       |
| job        | varchar(60) | YES  |     | NULL    |       |
| salary     | double      | YES  |     | NULL    |       |
| resume     | blob        | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

#对表名重新命名
#方式一
mysql> rename table worker to user;
Query OK, 0 rows affected (0.00 sec)
#方式二
mysql> alter table  worker rename to user;
Query OK, 0 rows affected (0.00 sec)

#查看表的信息
mysql> desc user;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id         | int(11)     | YES  |     | NULL    |       |
| name       | varchar(20) | YES  |     | NULL    |       |
| gender     | varchar(10) | YES  |     | NULL    |       |
| brithday   | date        | YES  |     | NULL    |       |
| entry_date | date        | YES  |     | NULL    |       |
| job        | varchar(60) | YES  |     | NULL    |       |
| salary     | double      | YES  |     | NULL    |       |
| resume     | blob        | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
8 rows in set (0.00 sec)


#查看创建表的详细信息
mysql> show create table user;
| Table | Create Table                                                                                                   
| user  | CREATE TABLE `user` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`gender` varchar(10) DEFAULT NULL,
`brithday` date DEFAULT NULL,
`entry_date` date DEFAULT NULL,
`job` varchar(60) DEFAULT NULL,
`salary` double DEFAULT NULL,
`resume` blob
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

1 row in set (0.00 sec)


#修改表的字符集为gbk
mysql> alter table user character set gbk;	
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table user;			#查看信息，已经改为gbk

| Table | Create Table                                       
| user  | CREATE TABLE `user` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) CHARACTER SET latin1 DEFAULT NULL,
`gender` varchar(10) CHARACTER SET latin1 DEFAULT NULL,
`brithday` date DEFAULT NULL,
`entry_date` date DEFAULT NULL,
`job` varchar(60) CHARACTER SET latin1 DEFAULT NULL,
`salary` double DEFAULT NULL,
`resume` blob
) ENGINE=InnoDB DEFAULT CHARSET=gbk |

1 row in set (0.00 sec)

#将列名name修改为username
mysql> alter table user change name username varchar(100);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| id         | int(11)      | YES  |     | NULL    |       |
| username   | varchar(100) | YES  |     | NULL    |       |
| gender     | varchar(10)  | YES  |     | NULL    |       |
| brithday   | date         | YES  |     | NULL    |       |
| entry_date | date         | YES  |     | NULL    |       |
| job        | varchar(60)  | YES  |     | NULL    |       |
| salary     | double       | YES  |     | NULL    |       |
| resume     | blob         | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

二、字段控制查询

2.1 as用法

as: 起别名，用法：select 字段 as 别名
distinct: 去除重复记录

演示：

#1.去除重复记录
mysql> select id from student;
+------+
| id   |
+------+
| 1    |
| 2    |
| 3    |
| 4    |
| 5    |
| 1    |
+------+
6 rows in set (0.00 sec)

mysql> select distinct id from student;
+------+
| id   |
+------+
| 1    |
| 2    |
| 3    |
| 4    |
| 5    |
+------+
5 rows in set (0.01 sec)

#2.给列名起别名
mysql> select name,gender  from student;
+----------+--------+
| name     | gender |
+----------+--------+
| aaaa     | female |
| bbbbbbbb | male   |
| cc       | male   |
| ddd      | female |
| eee      | female |
| ffff     | male   |
+----------+--------+
6 rows in set (0.00 sec)


mysql> select name as 姓名,gender as 性别  from student;
+----------+--------+
| 姓名     | 性别   |
+----------+--------+
| aaaa     | female |
| bbbbbbbb | male   |
| cc       | male   |
| ddd      | female |
| eee      | female |
| ffff     | male   |
+----------+--------+
6 rows in set (0.00 sec)

mysql> select name  姓名1,gender  性别1  from student;
+----------+---------+
| 姓名1    | 性别1   |
+----------+---------+
| aaaa     | female  |
| bbbbbbbb | male    |
| cc       | male    |
| ddd      | female  |
| eee      | female  |
| ffff     | male    |
+----------+---------+
6 rows in set (0.00 sec)

2.2 order by

order by:指定数据返回的顺序
asc：ascending,升序
desc: descending，降序

用法：select from 表 order by xxx

演示：

#1.查询所有的记录，按照年龄升序排序
mysql> select * from student order by age asc;
+------+----------+------+--------+
| id   | name     | age  | gender |
+------+----------+------+--------+
| 3    | cc       |   15 | male   |
| 4    | ddd      |   16 | female |
| 1    | aaaa     |   19 | female |
| 2    | bbbbbbbb |   20 | male   |
| 5    | eee      |   20 | female |
| 1    | ffff     |   30 | male   |
+------+----------+------+--------+
6 rows in set (0.00 sec)

#2.查询所有学生记录，按照年龄降序排序，如果年龄相等，则按照编号进行升序排序 
mysql> select * from student order by age desc,id asc; 
+------+----------+------+--------+ 
| id   | name     | age  | gender | 
+------+----------+------+--------+ 
| 1    | ffff     |   30 | male   | 
| 2    | bbbbbbbb |   20 | male   | 
| 5    | eee      |   20 | female | 
| 1    | aaaa     |   19 | female | 
| 4    | ddd      |   16 | female | 
| 3    | cc       |   15 | male   | 
+------+----------+------+--------+ 
6 rows in set (0.00 sec)

2.3 聚合函数

聚合函数主要用来做纵向运算

2.3.1 count():统计指定列不为null的记录行数

#1.查询年龄大于20的人数
mysql> select count(*)  from student where age>20;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

2.3.2 sum(): 计算指定列的数值和

#1.查询所有学生的年龄和
mysql> select sum(age) from student;
+----------+
| sum(age) |
+----------+
|      120 |
+----------+
1 row in set (0.01 sec)

#2.查询所有学生的年龄和，以及所有学生的编号和
mysql> select sum(age),sum(id) from student;
+----------+---------+
| sum(age) | sum(id) |
+----------+---------+
|      120 |      16 |
+----------+---------+
1 row in set (0.00 sec)

2.3.3 求指定列中的最大值和最小值

max()
min()

#求最大年龄和最小年龄
mysql> select max(age),min(age) from student;
+----------+----------+
| max(age) | min(age) |
+----------+----------+
|       30 |       15 |
+----------+----------+
1 row in set (0.00 sec)

2.3.4 求平均值

avg()

#查询所有学生的平均年龄
mysql> select avg(age) from student;
+----------+
| avg(age) |
+----------+
|  20.0000 |
+----------+
1 row in set (0.00 sec)

总结：

查询关键字的书写顺序：select 聚合函数 from where order by

2.4 分组查询

group by：分组查询
having：有…,表示条件，类似于where的用法

#在当前数据库下创建新的表
mysql> create table emp(			
 -> empno int primary key,
 -> enname varchar(20),
 -> job varchar(20),
 -> mgr int,
 -> hiredate date,
 -> sal double,
 -> comm double,
 -> deptno int
 -> );

#1.查询各个部门的人数
mysql> select count(*) from emp group by deptno;
+----------+
| count(*) |
+----------+
|        2 |
|        2 |
|        4 |
+----------+
3 rows in set (0.00 sec)

#2.查询每个部门的部门编号和每个部门的工资和
mysql> select deptno,sum(sal) from emp group by deptno;
+--------+----------+
| deptno | sum(sal) |
+--------+----------+
|     10 |  7450.00 |
|     20 |  3800.00 |
|     30 |  8675.00 |
+--------+----------+
3 rows in set (0.00 sec)


#3.查询每个部门的部门编号和每个部门的人数
mysql> select deptno,count(*) from emp group by deptno;
+--------+----------+
| deptno | count(*) |
+--------+----------+
|     10 |        2 |
|     20 |        2 |
|     30 |        4 |
+--------+----------+
3 rows in set (0.00 sec)


#4.查询每个部门的部门编号和每个部门工资大于1500的人数
mysql> select deptno,count(*) from emp where sal>1500 group by deptno;
+--------+----------+
| deptno | count(*) |
+--------+----------+
|     10 |        2 |
|     20 |        1 |
|     30 |        3 |
+--------+----------+
3 rows in set (0.01 sec)


#5.查询工资总和大于7000的部门编号以及工资和
mysql> select deptno,sum(sal) from emp group by deptno having sum(sal)>7000;
+--------+----------+
| deptno | sum(sal) |
+--------+----------+
|     10 |  7450.00 |
|     30 |  8675.00 |
+--------+----------+
2 rows in set (0.00 sec)

总结：
having和where的区别
a.二者都表示对数据执行条件
b.having是在分组之后对数据进行过滤
where是在分组之前对数据进行过滤
c.having后面可以使用聚合函数
where后面不可以使用聚合函数

#查询工资大于1500，工资总和大于6000的部门编号和工资和
mysql> select deptno,sum(sal) from emp where sal>1500 group by deptno having sum(sal)>6000;
+--------+----------+
| deptno | sum(sal) |
+--------+----------+
|     10 |  7450.00 |
|     30 |  7425.00 |
+--------+----------+
2 rows in set (0.00 sec)

2.5 分页查询

limit：用来限定查询的起始行，以及总行数

#1.查询4行记录，起始行从0开始
mysql> select * from emp limit 0,4;
+-------+--------+----------+------+------------+---------+---------+--------+
| empno | enname | job      | mgr  | hiredate   | sal     | comm    | deptno |
+-------+--------+----------+------+------------+---------+---------+--------+
|  7369 | smith  | clark    | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | allen  | salesman | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7566 | jones  | managen  | 7839 | 1981-04-02 | 2975.00 |    NULL |     30 |
|  7654 | martin | salesman | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
+-------+--------+----------+------+------------+---------+---------+--------+
4 rows in set (0.00 sec)

mysql> select * from emp limit 2,3;
+-------+--------+----------+------+------------+---------+---------+--------+
| empno | enname | job      | mgr  | hiredate   | sal     | comm    | deptno |
+-------+--------+----------+------+------------+---------+---------+--------+
|  7566 | jones  | managen  | 7839 | 1981-04-02 | 2975.00 |    NULL |     30 |
|  7654 | martin | salesman | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | blake  | manager  | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
+-------+--------+----------+------+------------+---------+---------+--------+
3 rows in set (0.01 sec)

总结：

mysql> select deptno,sum(sal) from emp where sal>1500 group by deptno having sum(sal)>5000 order by sum(sal) asc limit 4;

查询语句书写顺序：select----》from—》where—》group by-----》having-----》order by----->limit
查询语句执行顺序：from----》where-----》group by----》having----》order by ----》select-----》limit