Mysql基础——表操作

**

一、Mysql的表数据类型

1. 整型——INT(显示宽度)
其实是显示宽度，因为整型的显示宽度，对数值大小无影响，只是当设置了zerofill的时候，在显示的时候补0而已。
2. 浮点型与定点型
数据类型(M,D)
M：精度，数据的总长度；
D：标度，小数点后的长度；
这种类型MySQL里有3种如：Float(6,2)、Double(6,2)、Decimal(6,2)；
其区别在于：
当不指定精度时，Float、Double默认会保存实际精度，而Decimal默认是整数；
当标度不够时，都会四舍五入，但Decimal会警告信息。
3. 日期与时间类型

4. 字符串类型
4.1 CHAR与VARCHAR
其定义方式为：
字符串类型(M)
这个M是长度的意思，插入该列的字符串长度将不允许超过M所指定的长度。另外，由于MySQL在建库时就指定了字符集，因此就不存在nchar、nvarchar、ntext这种数据类型了。
char最长可取255；
varchar最长可取65535；
4.2 TEXT
TEXT分为4种，与SQLServer不同：
类型允许的长度存储空间
TINYTEXT0~255字节值的长度+2字节
TEXT0~65535字节值的长度+2字节
MEDIUMTEXT0~167772150字节值的长度+3字节
LONGTEXT0~4294967295字节值的长度+4字节
4.3 ENUM类型
ENUM类型(枚举类型)，与C#的概念一样，在定义时指定取值范围。
属性名ENUM(‘值1’,‘值2’,‘值3’…‘值n’)
ENUM有NOT NULL属性，其默认值为取值列表的第一个元素；
ENUM无NOT NULL，则ENUM类型将允许插入NULL，并且NULL为默认值；
CREATE TABLE Test4(Sex ENUM(‘男’,‘女’)); INSERT INTO Test4 VALUES(‘男’); INSERT INTO Test4 VALUES(‘爷’);–这行报错SELECT * FROM Test4;
4.4 SET类型
在创建表时，就指定SET类型的取值范围。
属性名 SET(‘值1’,‘值2’,‘值3’…,‘值n’)
它与ENUM的区别在什么地方呢？
基本上就是多选的ENUM。
4.5 二进制类型
二进制类型是在数据库中存储二进制数据的数据类型。二进制类型包括BINARY、VARBINARY、BIT、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB。
类型取值范围
BINARY(M)字节数为M，允许长度为0~M的定长二进制字符串
VARBINARY(M)允许长度为0~M的变长二进制字符串，字节数为值的长度加1
BIT(M)M位二进制数据，M最大值为64
TINYBLOB可变长二进制数据，最多255个字节
BLOB可变长二进制数据，最多2的16次方-1个字节
MEDIUMBLOB可变长二进制数据，最多2的24次方-1个字节
LONGBLOB可变长二进制数据，最多2的32次方-1个字节
4.6 BINARY和VARBINARY
两者唯一的差别在于BINARY当长度不够时会补\0。
4.7 BIT类型
BIT类型与SQLServer里的就差别大了。其定义方式为：
BIT(M)
其中"M"指定了该二进制的最大字节长度为M，M的最大值为64。如BIT(4)就是数据类型为BIT类型，长度为4。其能够存储的值为0-15。因为变成二进制后，15的值为1111。
在查询BIT类型的数据时，要用BIN(字段名+0)来将值转换为二进制显示。
CREATE TABLE Test6(BB BIT(4));INSERT INTO Test6 VALUES(12);SELECT BIN(BB+0) FROM Test6;
上面的结果以二进制显示，最大的长度为4。
4.8 BLOB类型
BLOB类型是一种特殊的二进制类型。BLOB可以存储数据量很大的二进制数据，如图片，视频等。BLOB类型包括TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们之间的区别也只是最大长度不同。

**

二、数据定义DDL

用于定义数据库的三级结构，包括外模式、概念模式、内模式及其相互之间的映像，定义数据的完整性、安全控制等约束
DDL不需要commit.
CREATE
ALTER
DROP
TRUNCATE
COMMENT
RENAME

三、数据操纵DML

由DBMS提供，用于让用户或程序员使用，实现对数据库中数据的操作。
DML分成交互型DML和嵌入型DML两类。
依据语言的级别，DML又可分成过程性DML和非过程性DML两种。
需要commit.
SELECT
INSERT
UPDATE
DELETE
MERGE
CALL
EXPLAIN PLAN
LOCK TABLE

四、数据库控制语言DCL

GRANT 授权
REVOKE 取消授权

五、事务控制语言TCL

SAVEPOINT 设置保存点
ROLLBACK 回滚
SET TRANSACTION
**

六、表联结

参考(https://www.jb51.net/article/75456.htm)
**

实践作业：

1、创建如下所示的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 VARCHAR (30) NOT NULL,
	class VARCHAR (20) NOT NULL
);
插入数据：
INSERT INTO courses
VALUES
	('A', 'Math'),
	('A', 'Math'),
	('B', 'English'),
	('C', 'Math'),
	('D', 'Bio'),
	('E', 'Math'),
	('F', 'Comp'),
	('G', 'Math'),
	('H', 'Math'),
	('I', 'Math');
	运行语句：
	SELECT
	class
FROM
	courses
GROUP BY
	class
HAVING
	COUNT(*) >= 5;

**2、交换工资
创建一个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 auto_increment PRIMARY KEY,
	NAME VARCHAR (30) NOT NULL,
	sex VARCHAR (1) NOT NULL,
	salary DOUBLE NOT NULL
);

插入语句：
INSERT INTO salary (NAME, sex, salary)
VALUES
	("A", "m", 2500);

INSERT INTO salary (NAME, sex, salary)
VALUES
	("B", "f", 1500);

INSERT INTO salary (NAME, sex, salary)
VALUES
	("C", "m", 5500);

INSERT INTO salary (NAME, sex, salary)
VALUES
	("D", "f", 500);
	
运行语句：
SET sex = CASE
WHEN sex = 'f' THEN
	'm'
ELSE
	'f'
END;

3、组合两张表
在数据库中创建表1和表2，并插入三行数据
表1：Person

PersonId	FirstName	LastName
1	a	b
2	c	d
3	a	c

PersonId是上表主键
表2：Address

AddressId	PersonId	City	State
1	1	北京	A
2	4	上海	B
3	2	广州	C

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

创建表
CREATE TABLE Person (
	PersonId INT NOT NULL PRIMARY KEY,
	FirstName VARCHAR (30),
	LastName VARCHAR (30)
);
CREATE TABLE Address (
	AddressId INT NOT NULL PRIMARY KEY,
	PersonId INT NOT NULL,
	City VARCHAR (100),
	State VARCHAR (100)
);

插入数据：
INSERT INTO Person
VALUES
	('1', 'a', 'b');
INSERT INTO Person
VALUES
	('2', 'c', 'd');
INSERT INTO Person
VALUES
	('3', 'a', 'c');
INSERT INTO Address
VALUES
	('1', '1', '北京', 'A');
INSERT INTO Address
VALUES
	('2', '4', '上海', 'B');
INSERT INTO Address
VALUES
	('3', '2', '深圳', 'C');
	
运行语句：
SELECT
	FirstName,
	LastName,
	City,
	State
FROM
	Person p
LEFT JOIN Address a ON p.PersonId = a.PersonId;

4、删除重复的邮箱
编写一个SQL查询，来删除email表中所有重复的电子邮箱，重复的电子邮箱里只保留id最小的那个。

id	email
1	a@b.com
2	c@d.com
3	a@b.com

id 是这个表的主键。
例如，在运行你的查询语句之后，上面的Person应返回以下几行：

id	email
1	a@b.com
2	c@d.com

创建表：
CREATE TABLE email (
	id INT NOT NULL PRIMARY KEY,
	email VARCHAR (50) NOT NULL
);

插入数据：
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
FROM
	email
WHERE
	id NOT IN (
		SELECT
			temp.id
		FROM
			(
				SELECT
					MIN(id) AS id
				FROM
					email
				GROUP BY
					Email
			) temp
	);
	先找到重复的email，然后min（id）找到最小的，最后删除掉id不在查询出来的这个里面的其他数据。