[MySQL]3.简单实操

1 MySQL数据类型

在 MySQL 中，有三种主要的类型：文本、数字和日期/时间类型。

1. Text类型

   数据类型	描述
   CHAR(size)	保存固定长度的字符串（可包含字母、数字以及特殊字符）。在括号中指定字符串的长度。最多 255 个字符。
   VARCHAR(size)	保存可变长度的字符串（可包含字母、数字以及特殊字符）。在括号中指定字符串的最大长度。最多 255 个字符。注释：如果值的长度大于 255，则被转换为 TEXT 类型。
   TINYTEXT	存放最大长度为 255 个字符的字符串。
   TEXT	存放最大长度为 65,535 个字符的字符串。
   BLOB	用于 BLOBs (Binary Large OBjects)。存放最多 65,535 字节的数据。
   MEDIUMTEXT	存放最大长度为 16,777,215 个字符的字符串。
   MEDIUMBLOB	用于 BLOBs (Binary Large OBjects)。存放最多 16,777,215 字节的数据。
   LONGTEXT	存放最大长度为 4,294,967,295 个字符的字符串。
   LONGBLOB	用于 BLOBs (Binary Large OBjects)。存放最多 4,294,967,295 字节的数据。
   ENUM(x,y,z,etc.)	允许你输入可能值的列表。可以在 ENUM 列表中列出最大 65535 个值。如果列表中不存在插入的值，则插入空值。 注释：这些值是按照你输入的顺序存储的。可以按照此格式输入可能的值：ENUM(‘X’,‘Y’,‘Z’)
   SET	与 ENUM 类似，SET 最多只能包含 64 个列表项，不过 SET 可存储一个以上的值。

2. Number类型

   数据类型	描述
   TINYINT(size)	-128 到 127 常规。0 到 255 无符号*。在括号中规定最大位数。
   SMALLINT(size)	-32768 到 32767 常规。0 到 65535 无符号*。在括号中规定最大位数。
   MEDIUMINT(size)	-8388608 到 8388607 普通。0 to 16777215 无符号*。在括号中规定最大位数。
   INT(size)	-2147483648 到 2147483647 常规。0 到 4294967295 无符号*。在括号中规定最大位数。
   BIGINT(size)	-9223372036854775808 到 9223372036854775807 常规。0 到 18446744073709551615 无符号*。在括号中规定最大位数。
   FLOAT(size,d)	带有浮动小数点的小数字。在括号中规定最大位数。在 d 参数中规定小数点右侧的最大位数。
   DOUBLE(size,d)	带有浮动小数点的大数字。在括号中规定最大位数。在 d 参数中规定小数点右侧的最大位数。
   DECIMAL(size,d)	作为字符串存储的 DOUBLE 类型，允许固定的小数点。

   这些整数类型拥有额外的选项 UNSIGNED。通常，整数可以是负数或正数。如果添加 UNSIGNED 属性，那么范围将从 0 开始，而不是某个负数。

3. Date类型

   数据类型	描述
   DATE()	日期。格式：YYYY-MM-DD。 注释：支持的范围是从 ‘1000-01-01’ 到 ‘9999-12-31’
   DATETIME()	*日期和时间的组合。格式：YYYY-MM-DD HH:MM:SS。 注释：支持的范围是从 ‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’
   TIMESTAMP()	*时间戳。TIMESTAMP 值使用 Unix 纪元(‘1970-01-01 00:00:00’ UTC) 至今的描述来存储。格式：YYYY-MM-DD HH:MM:SS。 注释：支持的范围是从 ‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-09 03:14:07’ UTC
   TIME()	时间。格式：HH:MM:SS 。 注释：支持的范围是从 ‘-838:59:59’ 到 ‘838:59:59’
   YEAR()	2 位或 4 位格式的年。 注释：4 位格式所允许的值：1901 到 2155。2 位格式所允许的值：70 到 69，表示从 1970 到 2069。

   即便 DATETIME 和 TIMESTAMP 返回相同的格式，它们的工作方式很不同。在 INSERT 或 UPDATE 查询中，TIMESTAMP 自动把自身设置为当前的日期和时间。TIMESTAMP 也接受不同的格式，比如 YYYYMMDDHHMMSS、YYMMDDHHMMSS、YYYYMMDD 或 YYMMDD。

2 用SQL语句创建表

CREATE TABLE table_name (
column_name1 data_type PRIMARY KEY,
column_name2 data_type NOT NULL,
column_name2 data_type UNIQUE,
column_name3 data_type DEFAULT value,
...

约束

• NOT NULL - 指示某列不能存储 NULL 值。
• UNIQUE - 保证某列的每行必须有唯一的值。
• PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
• FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
• CHECK - 保证列中的值符合指定的条件。
• DEFAULT - 规定没有给列赋值时的默认值。

3 用SQL语句向表中添加数据

--指定列名
INSERT INTO table_name (column_name1, column_name3)
	VALUES(value1, value3);
	
--不指定列名
INSERT INTO table_name VALUES(value1, value2, value3);

最好采用指定列名的方法。

4 用SQL语句删除表

1. DELETE
   DELETE FROM table_name WHERE conditions;
2. DROP
   DROP TABLE table_name;
3. TRUNCATE
   TRUNCATE TABLE table_name;

不同方式的区别

1. 完全删除表格， 用DROP;
2. 保留表格，但要删除所有记录时， 用TRUNCATE;
3. 要删除部分记录时， 用DELETE。

5 用SQL语句修改表

修改列名

ALTER TABLE 
	table_name 
CHANGE 
	column_name new_column_name date_type;

修改表中数据

UPDATE 
	table_name
SET 
	column_name = new_value 
WHERE
	condition, ...;

删除行

DELETE 
FROM 
	table_name
WHERE 
	condition, ...;

删除列

ALTER TABLE 
	table_name
DROP COLUMN
	column_name

新建列

ALTER TABLE 
	table_name 
ADD COLUMN 
	column_name data_type
AFTER
	 certain_column;

6 作业

练习三：超过5名学生的课（难度：简单）

创建如下所示的courses 表 ，有: student (学生) 和 class (课程)。

1. 建表，表名courses。再填充records。

   CREATE TABLE courses (
   Student VARCHAR(50),
   Class VARCHAR(50)
   );
   
   INSERT INTO courses (Stduent, Class) VALUES('A', 'Math'), 
   ('B', 'English'), ('C', 'Math'), ('D', 'Biology'), ('E', 'Math'), ('F', 'Computer'),
   ('G', 'Math'), ('H', 'Math'), ('I', 'Math'), ('J', 'Math');
   

   
2. 查找大于等于5名学生的课程

   SELECT 
   	Class
   FROM
   	courses
   GROUP BY
   	Class
   HAVING
   	COUNT(Class) >= 5;
   

   

练习四：反转性别（难度：简单）

1. 建表，表名salary。再填充records。

   CREATE TABLE salary (
   id INT NOT NULL,
   name VARCHAR(50),
   sex VARCHAR(1),
   salary double
   );
   
   INSERT INTO TABLE salary (id, name, sex, salary) VALUES (1, 'A', 'm, '2500'),
   (2, 'B', 'f', '1500'), (3, 'C', 'm', '5500'), (4, 'D', 'f', '500');
   

   
2. 反转性别

   UPDATE
   	salary
   SET
   	sex=IF(sex='f', 'm', 'f');
   

   

练习五：组合两张表 （难度：简单）

1. 建表， 表名person。插入records。

   CREATE TABLE person (
   PersonId int,
   FirstName VARCHAR(50),
   LastName VARCHAR(50)
   );
   
   INSERT INTO person (PersonId, FirstName, LastName) 
   VALUES(1, 'An', 'Li'), (2, 'Pi', 'Ti'), (3, 'Kou', 'Bi');
   

   
2. 建表， 表名Address。插入records。

   CREATE TABLE Address (
   AddressId INT,
   PersonId INT,
   City VARCHAR(100);
   State VARCHAR(100)
   );
   
   INSERT INTO Address (AddressId, PersonId, City, State)
   VALUES(1, 1, 'AA', 'A'), （2, 2, 'BB', 'B'), (3, 3, 'CC', 'C'), (4, 4, 'DD', 'D');
   

   
3. 查找内容：无论 person 是否有地址信息，都需要基于上述两表提供 person 的以下信息：FirstName, LastName, City, State

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

   

练习六：删除重复的邮箱（难度：简单）

1. 建表， 表名email。插入records。

   CREATE TABLE email (
   Id INT,
   Email VARCHAR(100)
   );
   
   INSERT INTO email (Id, Email) VALUES(1, 'a@b.com'),
   (2, 'c@d.com'）, (3, 'a@b.com');
   

   
2. 删除重复的邮箱

   DELETE
   	E1
   FROM
   	email E1, email E2
   Where
   	E1.Email = E2.Email AND E1.Id > E2.Id
   
   SELECT
   	*
   FROM
   	email