任务三MySQL 基础 (二)- 表操作
一、学习内容
1、MySQL表数据类型
http://www.runoob.com/mysql/mysql-data-types.html
MySQL支持多种类型,大致可以分为三类:数值、日期/时间和字符串(字符)类型。
- 数值类型
- 日期和时间类型
- 字符串类型
2、用SQL语句创建表
表格(table)用来存储数据,表格是一种结构化文件。表格的行被称为记录,表格列被称为字段。表格字段属性:字段名称、字段数据类型、字段长度、字段约束。
2.1 创建表
创建表的时候,表中有字段,每一个字段有:
* 字段名
* 字段数据类型
* 字段长度限制
* 字段约束
创建表的语法:
create table tableName(
columnName dataType(length),
………………..
columnName dataType(length)
);
如:建立学生信息表,字段包括:学号、姓名、性别、出生日期、email、班级标识。
create table t_student(
student_id int(10),
student_name varchar(20),
sex char(2),
birthday date,
email varchar(30),
classes_id int(3)
)
2.2 创建表加入约束
常见的约束:
1)非空约束,not null,针对某个字段设置其值不为空,如学生姓名不为空.
2)唯一约束,unique,它可以使某个字段的值不能重复,如email不能重复
create table t_student(
student_id int(10),
student_name varchar(20) not null,
sex char(2),
birthday date,
email varchar(30) unique,
classes_id int(3),
constraint fk_id foreign key(classes_id) references t_classes(classes_id)
);
3)主键约束,primary key,主键可以标识记录的唯一性,主键分为单一主键和复合(联合)主键,单一主键是由一个字段构成的,复合(联合)主键是由多个字段构成的。
列级约束:
表级约束:
复合约束:
d)外键约束,foreign key
外键主要是维护表之间的关系的,主要是为了保证参照完整性,如果表中的某个字段为外键字段,那么该字段的值必须来源于参照的表的主键。
外键名是fk_id,clases_id外键,参考列为t_classes表的classes_id列。
3、用SQL语句向表中添加数据
insert语法格式:
字段和值必须一一对应,个数必须相同,数据类型必须一致。
INSERT INTO tablename(columname1,columname2,columname3)
VALUES(value1,value2,value3);
多种添加方式(指定列名和不指定列名)
--指定列名
INSERT INTO employee(id,name,phone) VALUES(01,'Tom',110110110);
--不指定列名
INSERT INTO employee VALUES(02,'Jack',119119119);
--指定列名
INSERT INTO employee(id,name) VALUES(03,'Rose');
最好采用指定列名的方法,若省略表示将表中的所有字段全部加上,省略之后程序不健壮。
4、用SQL语句删除表
- delete删除表内数据。
delete from 表名 where 删除条件;
- 清除表内数据,保存表结构,用 truncate。
truncate table 表名;
- 删除表用 drop,什么都没有了。
drop table 表名;
1、当你不再需要该表时, 用 drop;
2、当你仍要保留该表,但要删除所有记录时, 用 truncate;
3、当你要删除部分记录时, 用 delete。
5、 用SQL语句修改表
- 对一列的修改
1)增加一列(新建列)
ALTER TABLE 表名字 ADD COLUMN 列名字 数据类型 约束;
或:
ALTER TABLE 表名字 ADD 列名字 数据类型 约束;
新增加的列,被默认放置在这张表的最右边。如果要把增加的列插入在指定位置,则需要在语句的最后使用AFTER关键词(“AFTER 列1” 表示新增的列被放置在 “列1” 的后面)。如果想放在第一列的位置,则使用 **FIRST **关键词。
2)删除一列(删除列)
ALTER TABLE 表名字 DROP COLUMN 列名字;
或: ALTER TABLE 表名字 DROP 列名字;
3)重命名一列
ALTER TABLE 表名字 CHANGE 原列名 新列名 数据类型 约束;
重命名语句后面的数据类型不能省略,否则重命名失败。修改数据类型可能会导致数据丢失,所以要慎重使用。
4)改变数据类型
要修改一列的数据类型,除了使用刚才的 CHANGE 语句外,还可以用这样的 MODIFY 语句:
ALTER TABLE 表名字 MODIFY 列名字 新数据类型;
- 对表的内容修改
1)修改表中的某个值
UPDATE 表名字 SET 列1=值1,列2=值2 WHERE 条件;
如把 TOM的age改为21,salary改为3000。
UPDATE employee SET age=21,salary=3000 WHERE name='Tom';
2)删除一行记录(删除行)
DELETE FROM 表名字 WHERE 条件;
3)新建行
就是插入了,INSERT
二、作业
1、项目三:超过5名学生的课(难度:简单)
创建表
CREATE TABLE courses(
student VARCHAR(6),
class VARCHAR(10)
);
插入值
INSERT INTO courses(student,class)
VALUES('A','Math');
INSERT INTO courses(student,class)
VALUES('B','English');
INSERT INTO courses(student,class)
VALUES('C','Math');
INSERT INTO courses(student,class)
VALUES('D','Biology');
INSERT INTO courses(student,class)
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');
+---------+----------+
| student | class |
+---------+----------+
| A | Math |
| B | English |
| C | Math |
| D | Biology |
| E | Math |
| F | Computer |
| G | Math |
| H | Math |
| I | Math |
| A | Math |
+---------+----------+
编写一个 SQL 查询,列出所有超过或等于5名学生的课。学生在每个课中不应被重复计算。
select class from courses group by class
having
count(distinct student) >= 5; --distinct有重复记录去重
+-------+
| class |
+-------+
| Math |
+-------+
2、项目四:交换工资(难度:简单)
创建一个 salary表,有m=男性 和 f=女性的值 。交换所有的 f 和 m 值(例如,将所有 f 值更改为 m,反之亦然)。要求使用一个更新查询,并且没有中间临时表。
创建表并插入值
CREATE TABLE salary(
id int(2) primary key,
name VARCHAR(6),
sex CHAR(2),
salary VARCHAR(10)
);
INSERT INTO salary(id,name,sex,salary)
VALUES(1,'A','m',2500);
INSERT INTO salary(id,name,sex,salary)
VALUES(2,'B','f',1500);
INSERT INTO salary(id,name,sex,salary)
VALUES(3,'C','m',5500);
INSERT INTO salary(id,name,sex,salary)
VALUES(4,'D','f',500);
+----+------+------+--------+
| id | name | sex | salary |
+----