任务三 MySQL 基础 (二)- 表操作 任务时间 请于3月1日22:00前完成,在本文章评论处打卡。逾期尚未打卡的会被清退。
学习内容
1. MySQL表数据类型
2. 用SQL语句创建表 语句解释 设定列类型 、大小、约束 设定主键
3. 用SQL语句向表中添加数据 语句解释 多种添加方式(指定列名;不指定列名)
4. 用SQL语句删除表 语句解释 DELETE DROP TRUNCATE 不同方式的区别
5. 用SQL语句修改表 修改列名 修改表中数据 删除行 删除列 新建列 新建行 作业 项
正文:
1. MySQL支持多种类型,大致可以分为三类:数值、日期/时间和字符串(字符)类型。
MySQL支持所有标准SQL数值数据类型。
这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC),以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。
类型 | 大小 | 范围(有符号) | 范围(无符号) | 用途 |
---|---|---|---|---|
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的值 | 小数值 |
表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。
每个时间类型有一个有效值范围和一个"零"值,当指定不合法的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、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。
类型 | 大小 | 用途 |
---|---|---|
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.
创建MySQL数据表需要以下信息:
- 表名
- 表字段名
- 定义每个表字段
语法
以下为创建MySQL数据表的SQL通用语法:
CREATE TABLE table_name (column_name column_type);
以下例子中我们将在 RUNOOB 数据库中创建数据表runoob_tbl:
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 设置编码。
3. 向表中插入数据
语法
以下为向MySQL数据表插入数据通用的 INSERT INTO SQL语法:
INSERT INTO table_name ( field1, field2,...fieldN ) VALUES ( value1, value2,...valueN );
如果数据是字符型,必须使用单引号或者双引号,如:"value"。
不指定列名的插入按默认顺序排列:
INSERT INTO Customers
VALUES('1000000006',
'Toy Land',
'123 Any Street',
'New York',
'NY',
'11111',
'USA',
NULL,
NULL);
指定列名的插入如下,这会使得插入操作更不易出错,但也有操作繁琐的代价:
INSERT INTO Customers(cust_id,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country,
cust_contact,
cust_email)
VALUES('1000000006',
'Toy Land',
'123 Any Street',
'New York',
'NY',
'11111',
'USA',
NULL,
NULL);
4.
删除表(删除整个表而不是其内容)非常简单,使用 DROP TABLE语句 即可:
DROP TABLE CustCopy;
删除表内数据,用 delete。格式为:
delete from 表名 where 删除条件;
实例:删除学生表内姓名为张三的记录。
delete from student where T_name = "张三";
清除表内数据,保存表结构,用 truncate。格式为:
truncate table 表名;
实例:清除学生表内的所有数据。
truncate table student;
删除表用 drop,就是啥都没了。格式为:
drop table 表名;
实例:删除学生表。
drop table student;
drop, truncate 和delete的区别:
1、当你不再需要该表时, 用 drop;
2、当你仍要保留该表,但要删除所有记录时, 用 truncate;
3、当你要删除部分记录时, 用 delete。
5.
ALTER TABLE 语句
ALTER TABLE 语句用于在已有的表中添加、删除或修改列。
SQL ALTER TABLE 语法
如需在表中添加列,请使用下面的语法:
ALTER TABLE table_name
ADD column_name datatype
如需删除表中的列,请使用下面的语法(请注意,某些数据库系统不允许这种在数据库表中删除列的方式):
ALTER TABLE table_name
DROP COLUMN column_name
要改变表中列的数据类型,请使用下面的语法:
My SQL / Oracle:
ALTER TABLE table_name
MODIFY COLUMN column_name datatype
更新(修改)表中的数据,可以使用 UPDATE语句。有两种使用 UPDATE 的方式:
更新表中的特定行;
更新表中的所有行。
基本的 UPDATE语句 由三部分组成,分别是:
要更新的表;
列名和它们的新值;
确定要更新哪些行的过滤条件。举一个简单例子。客户 1000000005现在有了电子邮件地址,因此他的 记录需要更新,语句如下:
UPDATE Customers
SET cust_email = 'kim@thetoystore.com'
WHERE cust_id = '1000000005';
UPDATE语句总是以要更新的表名开始。
更新多个列的语法稍有不同:
UPDATE Customers
SET cust_contact = 'Sam Roberts',
cust_email = 'sam@toyland.com'
WHERE cust_id = '1000000006';
在更新多个列时,只需要使用一条 SET命令,每个“列=值”对之间用逗号分隔(最后一列之后不用逗号)。
新建行:
insert into column(字段1,字段2...)values (字段1,字段2....) ;
delete from table where conditions
作业:
1.
项目三:超过5名学生的课(难度:简单) 创建如下所示的courses 表 ,有: student (学生) 和 class (课程)。 编写一个 SQL 查询,列出所有超过或等于5名学生的课。Note: 学生在每个课中不应被重复计算。
创建表如下:
create table courses(
student varchar(1) not null,
class varchar(255) 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(student,class) values ('F','Computer');
insert into courses(student,class) values ('G','Math');
insert into courses(student,class) values ('H','Math');
insert into courses(student,class) values ('I','Math');
insert into courses(student,class) values ('A','Math');
查询语句如下:
SELECT class
FROM courses
GROUP BY class
HAVING count(DISTINCT student)>=5;
返回结果如下:
项目四:交换工资(难度:简单) 创建一个 salary表,如下所示,有m=男性 和 f=女性的值 。 交换所有的 f 和 m 值(例如,将所有 f 值更改为 m,反之亦然)。要求使用一个更新查询,并且没有中间临时表。
创建表如下:
create table salary(
id int not null primary key,
name varchar(1) not null,
sex varchar(1) not null,
salary int
);
insert into salary values('1', 'A', 'm', '2500');
insert into salary values('2', 'B', 'f', '1500');
insert into salary values('3', 'C', 'm', '2500');
insert into salary values('4', 'D', 'f', '500');
修改语句如下:
UPDATE salary
SET sex=
CASE
WHEN sex='f' THEN 'm'
ELSE 'f'
END;