MySQL学习 || 任务3 表操作与表联结

0、关于navicat的一点问题（mysql服务自启动问题）

每次启动navicat建立连接或打开历史连接时都会报错2003，得去命令行一步一步手动开启mysql服务才行。

解决方法：
去网上搜索发现mysql服务可以开机自启，于是尝试设置，发现服务里面mysql已经为自动启动，百思不得其解，同时发现还有一个mysql80服务，它应该是以前安装mysql的遗留，它的指向路径也是以前那个，且他也是自启动，可能是这两个mysql服务都想自启动，发生冲突，都启动不了，于是将mysql80禁止，再次重启电脑，发现mysql服务已自启，navicat也可以正常使用了。

1、表操作

MySQL表数据类型

常见数据类型



详见 http://www.runoob.com/mysql/mysql-data-types.html

用SQL语句创建表（注意列类型 、大小、主键、约束，参照）

例子：

用SQL语句向表中添加数据

INSERT INTO 语句有两种方法。

第一种方法明确了列名称和待插入值（指定列名）：
INSERT INTO table_name (column1, column2, column3, …)
VALUES (value1, value2, value3, …);

如果要对表格的所有列变量都插入值，则不需要写明列名称（不指定列名）：
INSERT INTO table_name
VALUES (value1, value2, value3, …);

注意：字符的话要加单引号。
如：

  INSERT INTO World
  VALUES('Afghanistan','Asia',652230,25500100,20343000);

用SQL语句删除表

另外还有：

DELETE FROM table_name WHERE ……;

TRUNCATE TABLE table_name;

区别：
drop直接删掉表；
truncate删除的是表中的数据，再插入数据时自增长的数据id又重新从1开始；
delete删除表中数据，可以在后面添加where字句，实现部分删除。

用SQL语句修改表

修改表名
    alter table  oldname rename newname;
    
修改表中数据
 	UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值；
 
删除某一行
    delete from table_name where some_column=some_value
        
添加某一列：
    ALTER TABLE table_name
    ADD column_name datatype;
    
删除某一列：  
    ALTER TABLE table_name   
    DROP COLUMN column_name;            \\COLUMN可省略
    
修改列名
	alter table table_name
	change column oldname newname [other description];    \\COLUMN可省略

修改列数据类型：
    ALTER TABLE table_name
    modify COLUMN column_name newtype;      \\COLUMN可省略

作业

作业1

创建表

create table courses(
student char(2) ,
class char(20)
);

添加数据

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');

查询：
注意学生在每个课中不应该被重复计算，所以加distinct student

select class from courses
group by class
HAVING COUNT(distinct student, class)>=5;     

作业2

创建表：

create table salary(
id char(4),
name char(10),
sea char(2),
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','5500');
 insert into salary values('4','D','f','500');

更新和查询语句 （用到case when 函数）

update salary set sea=(
case sea
when 'f'then 'm'
when 'm'then 'f'
else 'other'
end );
select *from salary；

case when 函数用法

https://www.cnblogs.com/hongmaju/p/4459046.html

2、表联结（连接查询）

MySQL别名

在下面的情况下，使用别名很有用：

在查询中涉及超过一个表
在查询中使用了函数
列名称很长或者可读性差
需要把两个列或者多个列结合在一起

语法
本名 AS 别名
可以给列名、表名等起别名

例：
表的 SQL 别名语法
SELECT column_name(s)
FROM table_name AS alias_name;

列的 SQL 别名语法
SELECT column_name AS alias_name
FROM table_name;

内连接（INNER JOIN）

等值连接

概述：指使用等号"="比较两个表的连接列的值，相当于两表执行笛卡尔后，取两表连结列值相等的记录。

语法：

SELECT 列
FROM 表1 INNER JOIN 表2
ON 表1.列 = 表2.列

示例：

SELECT A., B.
FROM student_info A inner join student_score B
ON A.student_id = B.student_id

非等值连接

概述：指使用大于号">“或小于号”<"比较两个表的连接列的值，相当于两表执行笛卡尔后，取一个表大于或小于另一个表的连结列值的记录。

语法：

SELECT 列
FROM 表1 INNER JOIN 表2
ON 表1.列 <> 表2.列

示例：

SELECT A., B.
FROM student_info A inner join student_score B
ON A.student_id > B.student_id

---

---

内连接的其他写法：逗号代替join，where代替on

SELECT * FROM employee INNER JOIN department
ON employee.DepartmentID = department.DepartmentID

等价于:

SELECT * FROM employee，department
WHERE employee.DepartmentID =department.DepartmentID

嵌套循环连接算法基本思想

自然连接（natural join）

SELECT A., B. FROM student_info A natural join student_score B

外连接

左外连接（LEFT OUTER JOIN）

概述：指将左表的所有记录与右表符合条件的记录，返回的结果除内连接的结果，还有左表不符合条件的记录，并在右表相应列中填NULL。

示例：

SELECT A., B.
FROM student_info A left join student_score B
ON A.student_id = B.student_id

右外连接（RIGHT OUTER JOIN）

概述：与左外连接相反，指将右表的所有记录与左表符合条件的记录，返回的结果除内连接的结果，还有右表不符合条件的记录，并在左表相应列中填NULL。

示例：

SELECT A., B.
FROM student_info A right join student_score B
ON A.student_id = B.student_id

CROSS JOIN(交叉连接 笛卡尔积)

概述：CROSS JOIN又称为笛卡尔乘积，实际上是把两个表乘起来。

示例：

SELECT
* FROM
表1
CROSS JOIN
表2;

自连接

概述：指用表的别名实现表自身的连接。

示例：

SELECT B.* FROM student_score AS A, student_score AS B
WHERE A.student_id = B.student_id AND B.student_score > 80

UNION 操作符

概述：
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。（将多个表的某列下所有值各输出一次）
请注意，UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。

样例：

SELECT column_name FROM table_name1
UNION
SELECT column_name FROM table_name2

注释：默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。

作业

作业1

代码：

--创建表
create table person
(
personid int primary key,
FirstName varchar(20),
LastName  varchar(20)
);

CREATE table Address(
	AddressId INT  Primary Key,
	PersonId INT ,
	City VARCHAR(20) ,
	State VARCHAR(20) );

--插入数据
INSERT INTO Person VALUES(1,'A','B');
INSERT INTO Person VALUES(2,'C','D');
INSERT INTO Person VALUES(3,'E','F');

INSERT INTO Address VALUES(22,2,'C2','S2');
INSERT INTO Address VALUES(11,1,'C1','S1');
INSERT INTO Address VALUES(33,3,'C3','S3');

--查询语句
select FirstName,LastName,City,State 
from person,Address
where person.personid=Address.personid

作业2

--创建表格
CREATE table emial
(
	Id int primary key ,
	Email VARCHAR(50)
 );

--插入数据
INSERT INTO email VALUES(1,'a@b.com');
INSERT INTO email VALUES(2,'c@d.com');
INSERT INTO email VALUES(3,'a@b.com');

--查询语句
delete e1 from email AS e1 ,email AS e2
where e1.Email =e2.Email and e1.Id > e2.Id;
SELECT Id,Email FROM email;