mysql内链接链接条件怎么去重 mysql内连接语法

MYSQL基本语法二

I、表连接查询
语法：SELECT 列名 FROM 表1 连接方式 表2 ON 连接条件。
II、内连接查询（INNER JOIN ON）
#查询所有有部门的员工信息,显示部门名称(不包括没有部门的员工) SQL标准
SELECT * FROM t_employees
INNER JOIN t_departments
ON t_employees.DEPARTMENT_ID = t_departments.DEPARTMENT_ID;

#MYSQL标准
SELECT EMPLOYEE_ID,FIRST_NAME,DEPARTMENT_NAME FROM
t_employees,t_departments
WHERE t_employees.DEPARTMENT_ID = t_departments.DEPARTMENT_ID;

#1.两张表连接查询，要有关联条件。但是关联条件的列重复了。需要明确查询的是哪个表的列
#2.表名比较长,表名多次重复出现。容易混淆.可以给别名
SELECT EMPLOYEE_ID,FIRST_NAME,d.DEPARTMENT_ID,DEPARTMENT_NAME FROM t_employees AS e
INNER JOIN t_departments AS d
ON e.DEPARTMENT_ID = d.DEPARTMENT_ID;

III、内连接查询
#查询所有岗位的员工信息，显示岗位名称
SELECT EMPLOYEE_ID,FIRST_NAME,JOB_TITLE
FROM t_employees AS e
INNER JOIN t_jobs AS j
ON e.JOB_ID = j.JOB_ID;

IV、三表连接查询
#查询所有员工工号、名字、部门名称、部门所在城市的名称
SELECT EMPLOYEE_ID,FIRST_NAME,DEPARTMENT_NAME,CITY
FROM t_employees AS e
INNER JOIN t_departments AS d
ON e.DEPARTMENT_ID = d.DEPARTMENT_ID INNER JOIN t_locations AS l
ON d.LOCATION_ID = l.LOCATION_ID;

V、多表连接查询
#查询所有员工工号、名字、部门名称、部门城市名称、所在城市的国家
SELECT EMPLOYEE_ID,FIRST_NAME,DEPARTMENT_NAME,CITY,COUNTRY_NAME
FROM t_employees AS e
INNER JOIN t_departments AS d
ON e.DEPARTMENT_ID = d.DEPARTMENT_ID INNER JOIN t_locations AS l
ON d.LOCATION_ID = l.LOCATION_ID INNER JOIN t_countries AS c
ON l.COUNTRY_ID = c.COUNTRY_ID;

VI、左外连接查询(LEFT JOIN ON)
#查询所有员工信息，以及对应的部门名称（没有部门的员工，也在查询结果中，但是部门名称以NULL填充）
SELECT EMPLOYEE_ID,FIRST_NAME,DEPARTMENT_NAME
FROM t_employees AS e
LEFT JOIN t_departments AS d
ON e.DEPARTMENT_ID = d.DEPARTMENT_ID;

• 注意：左外连接，是以左表为主表，依次向右表匹配，匹配到，则返回正确结果
• 匹配不到，则返回NULL值，填充显示

VII、#查询所有部门信息，以及部门中的员工信息
#查询所有部门信息，以及部门中的员工信息
#(没有员工的部门，也在查询结果中，员工信息以NULL填充)
SELECT EMPLOYEE_ID,FIRST_NAME,DEPARTMENT_NAME
FROM t_employees AS e
RIGHT JOIN t_departments AS d
ON e.DEPARTMENT_ID = d.DEPARTMENT_ID;

• 注意：右外连接，是以右表为主表，依次向左匹配，匹配到，返回正确结果
• 匹配不到，则返回NULL填充

二、DML操作（增、删、改）

I、新增（INSERT）
INSERT INTO 表名(列1，列2，列3…)VALUES(值1，值2，值3…)

II、添加一条信息
#添加一条员工信息
INSERT INTO t_employees
(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID)
VALUES(‘209’,‘Ya’,‘Suo’,‘[e-mail]’,‘515.123.6666’,‘2010-03-18’,‘Center’,900,NULL,‘123’,‘50’)

#多行添加，在值列表外边追加,再写一个值列表
,(‘208’,‘Ya’,‘Suo’,‘[e-mail]’,‘515.123.6666’,‘2010-03-18’,‘Center’,900,NULL,‘123’,‘50’);

#添加一条城市信息
INSERT INTO t_countries(COUNTRY_ID,COUNTRY_NAME)VALUES(‘AL’,‘阿尔巴尼亚’);
SELECT * FROM t_departments;

#添加一条部门信息
INSERT INTO t_departments(DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID)
VALUES(‘280’,‘Teach’,‘111’,‘1500’)

注意:表名后的列名列表以及VALUES里的值列表要一一对应（个数、顺序、类型）

III、修改（UPDATE）
UPDATE表名 SET 列名1 = 新值1，列名2 =新值2…WHERE条件

IV、修改一条信息

#修改员工编号为208的员工名字为TOM Jackson
UPDATE t_employees
SET FIRST_NAME='TOM', LAST_NAME = 'Jackson'
WHERE EMPLOYEE_ID = '208';

• 注意：SET后跟着多个列 = 值，绝大多- 数情况下，都要加WHERE条件，指定修改的目标，否则为整表更新

V、删除
DELETE ]()FROM 表名 WHERE 条件

删除一条信息
#删除一条员工，编号为207的
DELETE FROM t_employees
WHERE EMPLOYEE_ID = ‘207’

注意：删除时，如若不加WHERE条件，删除的是整张表的数据。结构不变

VI、清空(TRUNCATE)
清空(TRUNCATE)

清空整张表
#清空t2整张表
TRUNCATE TABLE t2;

注意：TRUNCATE与DELETE不加WHERE删除整张表数据不同：

DELETE仅仅删除数据，结构不变。

TRUNCATE是把整张表销毁，再按照原表的格式、结构创建一张新表

三、库表操作

I、数据库创建（CREATE）
CREATE DATABASE 库名；

II、创建数据库
#创建默认字符集的数据库
CREATE DATABASE MYDB1;

#创建指定字符集的数据库
CREATE DATABASE MYDB1 CHARACTER SET UTF8;

III、修改数据库
ALTER DATABASE 库名 操作

IV、 修改数据库的字符集
#修改mydb1的字符集给gbk
ALTER DATABASE MYDB1 CHARACTER SET GBK;

V、删除数据库
DROP DATABASE 库名

VI、删除数据库
#删除mydb1数据库
DROP DATABASE MYDB1;

VII、 数据类型
MySQL大致可以分为三类：数值、日期/时间、字符串(字符)类型。对于我们建表，约束列的类型有很大的帮助

VIII、 数值类型

类型	大小	范围（有符号）	范围（无符号）	用途
INT	4字节	(-2147483648,2147483647)	(0,4294967295)	整数值
DOUBLE	8字节	(-1.797E+308,-2.22E-308)	(0,2.22E-308,1.797E+308)	双精度浮点值
DOUBLE(M,D)	8字节，M表示长度，D表示小数位数	同上，受M和D的约束。DOUBLE(5,2)-999.99-999.99	同上，受M和D的约束	双精度浮点值
DECIMAL(M,D)	保存精确值	依赖M和D。	依赖M和D	小数值

日期类型

类型	大小	范围	格式	用途
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	混合日期时间值

字符串类型

类型	大小	用途
CHAR	0-255字符	定长字符串 CHAR(10) 10个字符
VARCHAR	0-65535	可变长字符串 VARCHAR(10) 10个字符
BLOB(binary large object)	0-65535	二进制形式的长文本数据
TEXT	0-65535	长文本

数据表的创建（CREATE）
CREATE TABLE 表名(]()

列名 数据类型 [约束],

列名 数据类型 [约束],

…

列名 数据类型 [约束] //最后一列的创建，末尾不需要加逗号

)[charset=utf8]](); //根据需要指定表的字符编码集

创建表
#创建科目表
#科目编号、科目名称、科目学时
#Subject
CREATE TABLE Subject(
subjectId INT,
subjectName VARCHAR(20),
subjectHours INT
)CHARSET=utf8;

INSERT INTO subject(subjectid,subjectname,subjecthours)
VALUES(1,‘Java’,10);
INSERT INTO subject(subjectid,subjectname,subjecthours)
VALUES(2,‘HTML5’,20);
INSERT INTO subject(subjectid,subjectname,subjecthours)
VALUES(3,‘BIGDATA’,5);

• 问题：在已创建的表中新增数据时，可不可以新增两行相同列值的数据？
• 有什么弊端？表中的数据不唯一

三、约束
I、实体完整性约束
表中一行数据代表一个实体(entity)，实体完整性约束是标识每一行数据不重复、实体唯一。

主键约束
PRIMARY KEY]() 唯一，标识表中的一行数据，此列的值不可重复，且不能为NULL

#创建表中，选择适合做主键的列，添加主键约束
CREATE TABLE Student(
stuid INT PRIMARY KEY,#标识每一个学生的编号是唯一的，不能为NULL
stuName VARCHAR(20),
phone VARCHAR(11)
)CHARSET=utf8;

II、唯一约束

UNIQUE 唯一，标识表中的一行数据，不可重复，可以为NULL

#表中的手机号列，添加唯一约束！不能重复，但是可以为NULL
CREATE TABLE Student(
	stuid INT PRIMARY KEY,#标识每一个学生的编号是唯一的，不能为NULL
	stuName VARCHAR(20),
	phone VARCHAR(11) UNIQUE
)CHARSET=utf8;

III、自动增长列

AUTO_INCREMENT 自动增长，给主键数值列添加自动增长。从1开始，每次加1。不能单独使用，和主键搭配

#为表中的主键列添加自动增长，避免ID重复，也容易忘记
CREATE TABLE Student(
	stuid INT PRIMARY KEY AUTO_INCREMENT,#会从1开始，根据添加数据的顺序依次+1
	stuName VARCHAR(20),
	phone VARCHAR(11) UNIQUE
)CHARSET=utf8;

IV、 域完整性约束
限制列的每一个单元格的数据正确性

非空约束
#加了NOT NULL的约束列，必须有值
CREATE TABLE emp(
id INT PRIMARY KEY AUTO_INCREMENT,
empName VARCHAR(20)NOT NULL,#约束名字这一列必须有值
address VARCHAR(50) NOT NULL
)CHARSET=utf8;
INSERT INTO emp(empName,address) VALUES(null,‘北京市海淀区’);#error,课程名称必须有值

V、默认值约束

DEFAULT 为列赋予默认值，当新增的数据不指定值时，可以书写DEFAULT，以定义好的默认值进行填充

#默认值约束，如果没有指定值，填充DEFAULT，默认值。
CREATE TABLE emp(
	id INT PRIMARY KEY AUTO_INCREMENT,
	empName VARCHAR(20)NOT NULL,#约束名字这一列必须有值
	address VARCHAR(50) NOT NULL,
	sex CHAR(1) DEFAULT '女'
)CHARSET=utf8;

VI、引用完整性约束

语法：CONSTRAINT 引用名 FOREIGN KEY (列名) REFERENCES 被引用表名(列名)

详解：FOREIGN KEY 引用外部表的某个列的值，新增数据时，约束此列的值必须是被引用表中存在的值

#专业表
CREATE TABLE Speciality(
	id INT PRIMARY KEY AUTO_INCREMENT,
	SpecialName VARCHAR(20) UNIQUE NOT NULL#唯一，且不能为空
)CHARSET=utf8;
#课程表
CREATE TABLE `subject`(
	subjectid INT PRIMARY KEY AUTO_INCREMENT,
	subjecname VARCHAR(20) UNIQUE NOT NULL,
	subjecthours INT DEFAULT 20,
	specialid INT NOT NULL,
	CONSTRAINT fk_subject_specialid
	 FOREIGN KEY(specialid)
	  REFERENCES Speciality(id)
)CHARSET=utf8;
SELECT * FROM SUBJECT;

#存在引用关系的表。要先添加被引用的表数据(主键表).再添加引用表的数据(外键表)
INSERT INTO Speciality (SpecialName) VALUES('Java');
INSERT INTO Speciality (SpecialName) VALUES('HTML5');

INSERT INTO `subject`(subjecname,subjecthours,specialid)
VALUES('JavaSE',10,1);
INSERT INTO `subject`(subjecname,subjecthours,specialid)
VALUES('JavaScript',20,2);
INSERT INTO `subject`(subjecname,subjecthours,specialid)
VALUES('BIGDATA',20,3);#error 约束：主键表不存在3.所以外键表不能插入3

• 注意：两张表存在引用关系时，执行删除操作需要注意，先删除从表(引用表、外键表)，再删除主表(被引用表、主键表)

VII、 约束创建整合

创建带有约束的表

创建Grade表

列名	数据类型	约束	说明
GradeId	INT	主键、自动增长	班级编号
GradeName	VARCHAR(20)	唯一、非空	班级名称

#创建Grade表
CREATE TABLE Grade(
	GradeId INT PRIMARY KEY AUTO_INCREMENT,
	GradeName VARCHAR(20) UNIQUE NOT NULL
)CHARSET=utf8;

SELECT * FROM grade;
INSERT INTO Grade(GradeName) VALUES('NZ2001');
INSERT INTO Grade(GradeName) VALUES('NZ2002');
INSERT INTO Grade(GradeName) VALUES('NZ2003');

VIII、 创建Student表

列名	数据类型	约束	说明
student_id	VARCHAR(50)	主键	学号
student_name	VARCHAR(50)	非空	姓名
sex	CHAR(2)	默认值。男	性别
borndate	DATE	非空	生日
phone	VARCHAR(11)	无	电话
GradeId	INT	非空，外键约束：引用班级表的GradeId	班级编号

#创建Student表
CREATE TABLE Student(
	student_id VARCHAR(50) PRIMARY KEY,
	student_name VARCHAR(50) NOT NULL,
	sex CHAR(2) DEFAULT '男',
	borndate DATE NOT NULL,
	phone VARCHAR(11),
	GradeId INT NOT NULL,
	CONSTRAINT fk_student_gradeId FOREIGN KEY(GradeId) REFERENCES Grade(GradeId)
)CHARSET=utf8;

SELECT * FROM student;
INSERT INTO student(student_id,student_name,sex,borndate,phone,GradeId)
VALUES('S1001','唐三',DEFAULT,'2001-06-01',NULL,2);
INSERT INTO student(student_id,student_name,sex,borndate,phone,GradeId)
VALUES('S1002','小武',DEFAULT,'1999-06-01',NULL,3);

• 注意：在创建有关系关联表时，要先创建主表（主键），再创建从表（外键表）

数据表的修改（ALTER）
语法：ALTER TABLE 表名 修改操作;

向现有表中添加列

#向现有表中添加列
ALTER TABLE Student ADD image BLOB;
#ADD 新列名 数据类型 [约束]

修改表中的列

ALTER TABLE student MODIFY phone VARCHAR(14) NOT NULL

• 注意：修改表中的某列时，需要写全列的名字、数据类型、约束

删除表中的列

ALTER TABLE student DROP image;

改变列名

ALTER TABLE student CHANGE borndate birthday DATE NOT NULL;

• 注意：改变列名时，在给定新列名的同时，要指定列的数据类型和约束

修改表名

ALTER TABLE student RENAME stu;

删除表(DROP)

DROP TABLE 表名

删除学生表

DROP TABLE stu

事务

---

模拟转账

生活中转账是转账方扣钱，收钱方账户价钱。用数据库操作来模拟现实转账。

模拟账户转钱

#1账号转钱给2账户1000元
#1账户扣钱
UPDATE account SET money = money - 1000 WHERE id = 1;

#2账户加钱
UPDATE account SET money = money + 1000 WHERE id = 2;

模拟转账错误

#1账号转钱给2账户1000元
#1账户扣钱
UPDATE account SET money = money - 1000 WHERE id = 1;
#断电、异常、出错

#2账户加钱
UPDATE account SET money = money + 1000 WHERE id = 2;

• 上述代码在减钱操作过程中出现了异常或语句出错，会发现，减钱仍旧成功，而加钱失败了
• 每条SQL语句都是一个独立的操作！任何一个操作执行完对数据库是永久性的影响