MySQL 基础 (二)- 表操作
0.引言
本文介绍MySQL中有关表操作的基础知识。
1.MySQL表数据类型
1.1 数值类型
类型 | 大小 | 范围(有符号) | 范围(无符号) | 用途 |
---|---|---|---|---|
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的值 | 小数值 |
1.2 日期和时间类型
类型 | 大小(字节) | 范围 | 格式 | 用途 |
---|---|---|---|---|
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 | 混合日期和时间值,时间戳 |
1.3 字符类型
类型 | 大小 | 用途 |
---|---|---|
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. 用SQL语句创建表
通用语法
CREATE TABLE table_name(column_name,column_type);
#创建含有数据类型为column_type的column_name这一列的数据表table_name;
设定
列类型:见前文数据类型;
大小:数据类型分为定长和变长数据类型,变长数据类型可以控制大小,如VARCHAR(n),n可取1-65535;
约束:
NOT NULL:在操作数据库时如果输入该字段的数据为NULL ,就会报错;
AUTO_INCREMENT:定义列为自增的属性,一般用于主键,数值会自动加1;
主键:
PRIMARY KEY:定义列为主键,可以使用多列来定义主键,列间以逗号分隔;
ENGINE:设置数据库所用引擎;
CHARSET:设置使用编码。
CREATE TABLE IF NOT EXISTS `human`(
`human_id` INT UNSIGNED AUTO_INCREMENT,
`human_essay` VARCHAR(100) NOT NULL,
`human_name` VARCHAR(40) NOT NULL,
`birth_date` DATE,
PRIMARY KEY ( `human_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
3. 用SQL语句向表中添加数据
INSERT INTO table_name ( field1, field2,...fieldN )
VALUES
( value1, value2,...valueN );
#向table_name表中添加一行数据,新建一行;
INSERT INTO table_name
VALUES
( value1, value2,...valueN );
#所有列都要添加数据时可以不指定列名。
4. 用SQL语句删除表
DROP TABLE table_name;
TRUNCATE TABLE table_name;
DELETE FROM table_name WHERE condition;
- DROP 删除表全部数据和表结构,立刻释放磁盘空间,不管是 Innodb 和 MyISAM;
- TRUNCATE 删除表全部数据,保留表结构,立刻释放磁盘空间 ,不管是 Innodb 和 MyISAM
- DELETE:
不加WHERE条件时,删除表全部数据,表结构不变,对于 MyISAM 会立刻释放磁盘空间,InnoDB 不会释放磁盘空间 ;
加WHERE条件时,带条件的删除,表结构不变,不管是 innodb 还是 MyISAM 都不会释放磁盘空间。
5. 用SQL语句修改表
5.1 UPDATE和DELETE
更新数据
UPDATE table_name
SET column=1
WHERE column=2;
--修改table_name表中column列的值,原来为2的行的值修改为1;
--若没有WHERE子句,则修改column的所有值为1;
--删除某列中的值,可以用column=NULL。
删除数据
DELETE FROM table1
WHERE condition;
--删除符合condition条件的表table1中的所有行的数据;
--WHERE语句没有时,删除表中所有行的数据;
--以列为单位删除数据可以用UPDATE语句。
5.2 ALTER TABLE
ALTER TABLE table_name
ADD column_name column_type;
--新建列
ALTER TABLE table_name
DROP column_name;
--删除列
ALTER TABLE table_name
MODIFY column_name new_type NOT NULL DEFAULT default_vlaue;
--修改列数据类型并设置默认值,不设默认值默认为NULL
ALTER TABLE table_name
CHANGE column_name new_column_name new_column_type;
--修改列名和数据类型
ALTER TABLE table_name
RENAME AS new_table_name;
--修改表名
6.实例
6.1 超过5名学生的课
要求:创建如下所示的courses 表 ,有: student (学生) 和 class (课程)。 例如,表:
student | class |
---|---|
A | Math |
B | English |
C | Math |
D | Biology |
E | Math |
F | Computer |
G | Math |
H | Math |
I | Math |
A | Math |
代码:
#创建表
CREATE TABLE IF NOT EXISTS courses(
student varchar(5) NOT NULL,
class varchar(10) NOT NULL
);
#导入数据
INSERT INTO courses VALUES("A","Math");
INSERT INTO courses VALUES("B","English");
INSERT INTO courses VALUES("C","Math");
INSERT INTO courses VALUES("D","Biology");
INSERT INTO courses VALUES("E","Math");
INSERT INTO courses VALUES("F","Computer");
INSERT INTO courses VALUES("G","Math");
INSERT INTO courses VALUES("H","Math");
结果:
要求:编写一个 SQL 查询,列出所有超过或等于5名学生的课。 应该输出:
class |
---|
Math |
Note: 学生在每个课中不应被重复计算。
代码:
SELECT class FROM courses GROUP BY class HAVING COUNT(DISTINCT student) >= 5;
结果:
6.2 交换工资
要求: 创建一个 salary表,如下所示,有m=男性 和 f=女性的值 。
例如:
id | name | sex | salary |
---|---|---|---|
1 | A | m | 2500 |
2 | B | f | 1500 |
3 | C | m | 5500 |
4 | D | f | 500 |
代码:
#创建数据库
CREATE TABLE IF NOT EXISTS salary (
id INT NOT NULL,
NAME VARCHAR (5) NOT NULL,
sex VARCHAR (5) NOT NULL,
salary INT NOT NULL,
PRIMARY KEY(id)
);
#插入数据
INSERT INTO salary(id,name,sex,salary)
VALUES
(1,'A','m',2500),
(2,'B','f',1500),
(3,'C','m',5500),
(4,'D','f',500);
结果:
要求:交换所有的 f 和 m 值(例如,将所有 f 值更改为 m,反之亦然)。要求使用一个更新查询,并且没有中间临时表。 运行你所编写的查询语句之后,将会得到以下表:
id | name | sex | salary |
---|---|---|---|
1 | A | f | 2500 |
2 | B | m | 1500 |
3 | C | f | 5500 |
4 | D | m | 500 |
代码:
UPDATE salary
SET sex=CASE sex
WHEN 'f' THEN 'm'
WHEN 'm' THEN 'f'
END;
SELECT * FROM salary;
结果: