MySQL 基础 （二）- 表操作

一. MySQL表数据类型
大致分为3类：数值、日期/时间、字符串类型。
二. 用SQL语句创建表
CREATE TABLE 表名称
(
列名称1 数据类型,
列名称2 数据类型,
列名称3 数据类型,
…
)

设置主键
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (Id_P)
)

三、 用SQL语句向表中添加数据
INSERT INTO 语句用于向表格中插入新的行。
INSERT INTO 表名称 VALUES (值1, 值2,…)

我们也可以指定所要插入数据的列：
INSERT INTO table_name (列1, 列2,…) VALUES (值1, 值2,…)

四、 用SQL语句删除表
drop table 表名称
truncate table 表名称
delete from 表名称 where 列名称 = 值

drop，truncate，delete区别

1、drop (删除表)：删除内容和定义，释放空间。简单来说就是把整个表去掉.以后要新增数据是不可能的,除非新增一个表。

   drop语句将删除表的结构被依赖的约束（constrain),触发器（trigger)索引（index);依赖于该表的存储过程/函数将被保留，但其状态会变为：invalid。

2、truncate (清空表中的数据)：删除内容、释放空间但不删除定义(保留表的数据结构)。与drop不同的是,只是清空表数据而已。

   注意:truncate 不能删除行数据,要删就要把表清空。

3、delete (删除表中的数据)：delete 语句用于删除表中的行。delete语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存

   truncate与不带where的delete ：只删除数据，而不删除表的结构（定义）

4、执行速度，一般来说: drop> truncate > delete。
五、 用SQL语句修改表
修改列名
ALTER TABLE 表名 CHANGE 列名 新列名 列类型
修改表中数据
UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
UPDATE Person SET FirstName = ‘Fred’ WHERE LastName = ‘Wilson’
删除行
DELETE FROM 表名称 WHERE 列名称 = 值
DELETE FROM Person WHERE LastName = ‘Wilson’
删除所有行
可以在不删除表的情况下删除所有的行。这意味着表的结构、属性和索引都是完整的：
DELETE FROM table_name
或者：
DELETE * FROM table_name
删除列
alter table tableName drop column columnName
新建列
alter table tableName add columnName varchar(30)
新建行
INSERT INTO 表名称 VALUES (值1, 值2,…)

作业：
项目三：超过5名学生的课（难度：简单）
创建表
create table courses(
student varchar(10)
class varchar(10)
) ;

INSERT INTO courses (student, class) VALUES
(‘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(*)>=5;
结果为项目四：交换工资（难度：简单）
创建一个 salary表，如下所示，有m=男性 和 f=女性的值 。
create table salary (
id NOT NULL AUTO_INCREMENT PRIMARY KEY
name varchar(10)
sex varchar(10)
salary int(10)
)
插入数据
INSERT INTO salary (name, sex, salary) VALUES
(‘A’, ‘m’, 2500),
(‘B’, ‘f’, 1500),
(‘C’, ‘m’, 5500),
(‘D’, ‘f’, 500);
结果为：

交换所有的 f 和 m 值(例如，将所有 f 值更改为 m，反之亦然)。要求使用一个更新查询，并且没有中间临时表。
代码为：
SELECT id,name,
(case sex
when ‘f’ THEN ‘m’
ELSE ‘f’
END ) as sex,salary
FROM salary
结果为：