[MySQL_3]表操作

[MySQL_3]表操作 and 表联结

[一].操作

【一】.1.MySQL表数据类型

• 大致分为三类——数值、日期/时间和字符串类型

1.1 数值类型

1.2 日期/时间

1.3字符串类型

【一】.2.用SQL语句创建表

语句解释

• 有两种创建表的方式

  1. 多数 DBMS 都具有交互式创建和管理数据库表的工具；

  2. 表也可以直接用 SQL 语句操纵。

• 使用SQL的 create table

2.1.设定列类型 、大小、约束

create table test
(
    prod_id char(10) not null,
    prod_name char(254) not null
 );   

• NULL 为默认设置

2.2.设定主键

• 主键是其值唯一标识表中每一行的列。只有不允许NULL值的列可作为主键

  create table test
  (
      prod_id char(10) not null,
      prod_name char(254) not null,
      primary key(prod_id)
   );   
  

【一】.3.用SQL语句向表中添加数据

3.1.语句解释

• INSERT 用来将行插入（或添加）到数据库表

  1. 插入完整的行；

  2. 插入行的一部分；

  3. 插入某些查询的结果

     INSERT INTO test
     (
     	prod_id,
     	prod_name
     )
     SELECT test_2, 
      	prod_id,
     	prod_name
     FROM ***;
     

     • 合并

3.2.多种添加方式（指定列名；不指定列名）

1. 不指定列名

   INSERT INTO test
   VALUES
   (
   	'1',
   	'czc'
   )
   

2. 指定列名

   INSERT INTO test
   (
   	prod_id,
   	prod_name
   )
   VALUES
   (
   	'1',
   	'czc'
   )
   

• VALUES 必须以其指定的次序匹配指定的列名，不一定按各列出现在表中的实际次序
• 其优点是——即使表的结构改变，这条INSERT 语句仍然能正确工作。

【一】.4.用SQL语句删除表

• 从一个表中删除（去掉）数据

	用法	说明	MyISAM（是否释放磁盘空间）	Innodb
drop	drop table table_name	删除表全部数据和表结构	Y	Y
truncate	truncate table table_name	删除表全部数据，保留表结构	Y	Y
delete	delete from table_name	删除表全部数据，表结构不变	Y	N
	delete from table_name where where ***	带条件的删除，表结构不变	N	N

• MyISAM——$默认表类型$，它是基于传统的ISAM类型，ISAM是Indexed Sequential Access Method (有索引的顺序访问方法) 的缩写，它是存储记录和文件的标准方法。不是事务安全的，而且不支持外键，如果执行大量的select，insert MyISAM比较适合
• InnoDB——支持事务安全的引擎，支持外键、行锁、事务是他的最大特点。如果有大量的update和insert，建议使用InnoDB，特别是针对多个并发和QPS较高的情况。

【一】.5.用SQL语句修改表

	说明
修改表名	alter table 旧表名 rename 新表名;
修改列名	alter talbe 表名 change column 旧列名 新列名 新数据类型;
修改表中数据类型	alter talble 表名 modify 属性名 数据类型;
修改表中数据	update 表名 set 字段名=‘新内容’ + where条件 ;
删除行	delete from 表名 where 行字段名=字段值;
删除列	alter table 表名 drop column 列名;
新建列	alter table 表名 add column 列名 数据类型;
新建行	insert into 表名（字段名1，字段名2） values（值1，值2）;

【一】.6.作业

• 【3】项目三：超过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 |

  ±--------±-----------+

  编写一个 SQL 查询，列出所有超过或等于5名学生的课。

  应该输出:

  ±--------+

  | class |

  ±--------+

  | Math |

  ±--------+

  Note:

  学生在每个课中不应被重复计算。

  #创建表
  CREATE TABLE courses
  (
  	student CHAR(4) NOT NULL,
  	class VARCHAR(50) 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');
  INSERT INTO courses VALUES('I','Math');
  INSERT INTO courses VALUES('A','Math');
  
  # 结果
  SELECT class from courses
  GROUP BY class 
  HAVING count(*)>=5;
  

• 【4】项目四：交换工资（难度：简单）

  创建一个 salary表，如下所示，有m=男性 和 f=女性的值 。

  例如:

  | id | name | sex | salary |

  |----|------|-----|--------|

  | 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 |

# 创建表
CREATE TABLE salary
(
	id INT NOT NULL,
	name VARCHAR(4) NOT NULL,
	sex VARCHAR(4) NOT NULL,
	salary INT NOT null,
	PRIMARY KEY(id)
);
# 插入数据
INSERT INTO salary VALUES(1,'A','m',2500);
INSERT INTO salary VALUES(2,'B','f',1500);
INSERT INTO salary VALUES(3,'C','m',5500);
INSERT INTO salary VALUES(4,'D','f',500);

#结果
# 方法一 利用变量进行交换；
UPDATE salary SET sex='a' WHERE sex='m';
UPDATE salary SET sex='m' WHERE sex='f';
UPDATE salary SET sex='f' WHERE sex='a';
# 方法二 直接交换；
UPDATE salary SET sex=(CASE sex when 'm' then f else 'm' end);

CASE…END判断语句：case函数只返回第一个符合条件的值，剩下的case部分将会被自动忽略

case sex
	when 'm' then '男'
	when 'f' then '女'
	else '其他' end

【一】.7.参考

1. 一周MySQL集训day3：MySQL 基础 （二）- 表操作
2. mysql数据类型

【二】.表联结

【二】.1.MySQL别名

1.1使用别名的好处

• 缩短SQL语句
• 允许在一条select语句中多次使用相同的表

1.2INNER JOIN

• 获取两个表中字段匹配关系的记录

SELECT a.runoob_id, a.runoob_author, b.runoob_count 
FROM runoob_tbl a INNER JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
# 等价于
SELECT a.runoob_id, a.runoob_author, b.runoob_count 
FROM runoob_tbl a, tcount_tbl b WHERE a.runoob_author = b.runoob_author;

输出

直观表示：

1.3.LEFT JOIN

SELECT a.runoob_id, a.runoob_author, b.runoob_count 
FROM runoob_tbl a LEFT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;

读取左边的数据表 runoob_tbl 的所有选取的字段数据

直观表示：

• RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录

参考——Mysql 连接的使用

CROSS JOIN

自连接

UNION

以上几种方式的区别和联系

【二】.2.作业

• 【5】项目五：组合两张表 （难度：简单）

  在数据库中创建表1和表2，并各插入三行数据（自己造）

  表1: Person

  ±------------±--------+

  | 列名 | 类型 |

  ±------------±--------+

  | PersonId | int |

  | FirstName | varchar |

  | LastName | varchar |

  ±------------±--------+

  PersonId 是上表主键

  表2: Address

  ±------------±--------+

  | 列名 | 类型 |

  ±------------±--------+

  | AddressId | int |

  | PersonId | int |

  | City | varchar |

  | State | varchar |

  ±------------±--------+

  AddressId 是上表主键

  编写一个 SQL 查询，满足条件：无论 person 是否有地址信息，都需要基于上述两表提供 person 的以下信息：FirstName, LastName, City, State

# 创建表 preson
CREATE TABLE Person(
	PersonID INT,
	FirstName VARCHAR(10),
	LastName VARCHAR(10),
	PRIMARY KEY(PersonID)
);
#创建表 address
CREATE TABLE Address(
AddressId INT PRIMARY KEY,
PersonId INT,
City VARCHAR(10),
State VARCHAR(10)
);
# 插入数据
INSERT INTO Person VALUE('1','wang','xiaoming');
INSERT INTO Person VALUE('2','cheng','xiaohong');
INSERT INTO Person VALUE('3','hai','xiaoqing');
# 插入数据
INSERT INTO Address VALUE('10','1','chengdu','china');
INSERT INTO Address VALUE('11','2','jingment','china');
INSERT INTO Address VALUE('12','4','chongqing','china');

结果

SELECT Person.FirstName,Person.LastName,Address.City,Address.State
FROM Person LEFT OUTER JOIN Address
ON Person.PersonId=Address.PersonId;