MySQL学习（二）

最新推荐文章于 2024-09-04 12:26:28 发布

番茄大圣

最新推荐文章于 2024-09-04 12:26:28 发布

阅读量186

点赞数

分类专栏： MySQL 文章标签： mysql 入门学习任务

本文链接：https://blog.csdn.net/tomatomas/article/details/89007442

版权

MySQL 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

基础概念

MySQL数据类型

MySQL支持3种数据类型，分别是数值类型、日期和时间类型以及字符串类型。

数值类型

所有数值类型都包含有符号和无符号两种，取值范围不同。如TINYINT，的取值范围为[-128,127]，而TINYINT UNSIGNED的取值范围为[0,255]，其他类型雷同。所有支持的数值类型如下：

类型	大小byte
TINYINT	1
SMALLINT	2
MEDIUMINT	3
INT / INTEGER	4
BIGINT	8
FLOAT	4
DOUBLE	8
DECIMAL(M,D)	取决于M/D值

其中DECIMAL类型用于存储精确的数值。

日期和时间类型

类型	大小byte	格式
DATE	3	YYYY-MM-DD
TIME	3	HH:MM:SS
YEAR	1	YYYY
DATETIME	8	YYYY-MM-DD HH:MM:SS
TIMESTAMP	4	YYYYMMDD HHMMSS

字符串类型

类型	大小byte	用途
CHAR	0-255	定长字符串
VARCHAR	0-65535	变长字符串
TINYBLOB	0-255	不超过 255 个字符的二进制字符串
TINYTEXT	0-255	短文本字符串
BLOB	0-65535	二进制形式的长文本数据
TEXT	0-65535	长文本数据
MEDIUMBLOB	0-16777	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16777	中等长度文本数据
LONGBLOB	0-4294967295	二进制形式的极大文本数据
LONGTEXT	0-4294967295	极大文本数据

创建表

创建表需要3个基本元素，表名、表字段名和字段类型：

CREATE TABLE table_name (column_name column_type);

下面是例子：

CREATE TABLE IF NOT EXISTS `runoob_tbl`(
   `runoob_id` INT UNSIGNED AUTO_INCREMENT,
   `runoob_title` VARCHAR(100) NOT NULL,
   `runoob_author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

其中IF NOT EXISTS是可选的，加上是为了避免报异常。NOT NULL则是表字段值的约束，用户插入数据的时候该字段必须有值。最后的PRIMARY KEY ( runoob_id )语句则是给表设置主键，AUTO_INCREMENT通常和主键一起用，该表字段不需要设置值，每加入一条数据会自动加1。

添加数据

向数据库表中添加数据的sql格式为：

INSERT INTO table_name ( field1, field2,...fieldN )
						VALUES
						( value1, value2,...valueN );

如果第一个括号中的字段包括了表的所有字段且顺序一致，则第一个括号可以省去，写为：

INSERT INTO table_name VALUES ( value1, value2,...valueN );

删除表

sql删除表的语句为：

DROP TABLE table_name ;

修改表

修改表数据的sql格式为：

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

如

UPDATE runoob_tbl SET runoob_title='学习 C++' WHERE runoob_id=3;

删除行数据的sql格式为：

DELETE FROM table_name [WHERE Clause]

如：

DELETE FROM runoob_tbl WHERE runoob_id=3;

修改表列(字段)的sql格式为：

//修改列名（字段名）
ALTER TABLE table_name CHANGE old_column_name new_column_name column_value_tyme;
//删除列
ALTER TABLE table_name DROP column_name;
//新增列
ALTER TABLE table_name ADD column_name column_value_type;

如：

ALTER TABLE testalter_tbl CHANGE i j BIGINT;
ALTER TABLE testalter_tbl  DROP i;
ALTER TABLE testalter_tbl ADD i INT;

别名

摘自：https://www.cnblogs.com/liliyang/p/9765889.html。
MySQL可以为表和字段起别名，sql格式为：

//表别名
table_name [AS] table_alias
//列别名
SELECT [column_1 | expression] [AS] descriptive_name
	FROM table_name;

如：

SELECT
customerName,
COUNT(o.orderNumber) [as] total	#列别名
FROM
customers [as] c	INNER JOIN orders [as] o #表别名

表连接

表连接有3种类型：INNER JOIN、LEFT JOIN与RIGHT JOIN和CROSS JOIN。INNER JOIN可以省略INNER写为JOIN。

INNER JOIN

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;

即将表runoob_tbl中的runoob_id值和表tcount_tbl中的runoob_count值对应并列出，对应的标准是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;

LEFT/RIGHT JOIN

LEFT/RIGHT JOIN语句的使用与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;


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

区别是LEFT JOIN语句会读取左边的数据表 runoob_tbl 的所有选取的字段数据，即便在右侧表 tcount_tbl中没有对应的 runoob_author 字段值。
而RIGHT JOIN则相反。

CROSS JOIN

CROSS JOIN会得到两个表的笛卡尔积，即假设表a有4条记录，表b有5条记录，则CROSS JOIN之后会得到4x5=20条记录。
如对于表salary：

+----+------+------+--------+
| id | name | sex  | salary |
+----+------+------+--------+
|  1 | A    | f    |   2500 |
|  2 | B    | m    |   1500 |
|  3 | C    | f    |   5500 |
|  4 | D    | m    |    500 |
+----+------+------+--------+

执行：

SELECT a.id,b.name FROM salary a CROSS JOIN salary b;

得到：

+----+------+
| id | name |
+----+------+
|  1 | A    |
|  2 | B    |
|  3 | C    |
|  4 | D    |
|  1 | A    |
|  2 | B    |
|  3 | C    |
|  4 | D    |
|  1 | A    |
|  2 | B    |
|  3 | C    |
|  4 | D    |
|  1 | A    |
|  2 | B    |
|  3 | C    |
|  4 | D    |
+----+------+

自连接

自连接的本质是将一张表当成两张表中，如上面的例子。

UNION

UNION即联合查询，其sql格式为：

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

其中：

expression1, expression2, … expression_n: 要检索的列。
tables: 要检索的数据表。
WHERE conditions: 可选，检索条件。
DISTINCT: 可选，删除结果集中重复的数据。默认情况下UNION 操作符已经删除了重复数据，所以 DISTINCT 修饰符对结果没啥影响。
ALL: 可选，返回所有结果集，包含重复数据。

其功能是列出两张表的查询结果，如：

SELECT country FROM Websites
UNION ALL #加上ALL显示所有数据，不加ALL自动过滤重复数据
SELECT country FROM apps
ORDER BY country;

作业

任务3

在下面的表中查询报名学生数大于或等于5的课程：

+---------+----------+
| student | class    |
+---------+----------+
| A       | Math     |
| B       | English  |
| C       | Math     |
| D       | Biology  |
| E       | Math     |
| F       | Computer |
| G       | Math     |
| H       | Math     |
| I       | Math     |
| A       | Math     |
+---------+----------+

由于其中有一对重复数据，所以得先过滤，最终的SQL语句应该为：

SELECT class FROM (SELECT DISTINCT student,class FROM courses) AS temp GROUP BY class HAVING COUNT(*) >= 5;

最终输出为：

+-------+
| class |
+-------+
| Math  |
+-------+

任务4

在下表中，交换所有的m值和f值：

| id | name | sex | salary |
|----|------|-----|--------|
| 1  | A    | m   | 2500   |
| 2  | B    | f   | 1500   |
| 3  | C    | m   | 5500   |
| 4  | D    | f   | 500    |

sql语句应该为：

UPDATE salary a JOIN salary b 
			ON (a.sex='m' AND b.sex='f') 
			OR (a.sex='f' AND b.sex='m')
			SET a.sex=b.sex, b.sex=a.sex;

执行结果为：

+----+------+------+--------+
| id | name | sex  | salary |
+----+------+------+--------+
|  1 | A    | f    |   2500 |
|  2 | B    | m    |   1500 |
|  3 | C    | f    |   5500 |
|  4 | D    | m    |    500 |
+----+------+------+--------+

任务5

对于表person：

+----------+-----------+----------+
| PersonId | FirstName | LastName |
+----------+-----------+----------+
|        1 | a         | A        |
|        2 | b         | B        |
|        3 | c         | C        |
+----------+-----------+----------+

和表address：

+-----------+----------+------+------------+
| AddressId | PersonId | City | State      |
+-----------+----------+------+------------+
|         1 |        1 | USA  | California |
+-----------+----------+------+------------+

列出person的信息，无论 person 是否有地址信息，都需要基于上述两表提供 person 的以下信息：FirstName, LastName, City, State。
执行语句应该为：

SELECT a.FirstName,a.LastName,b.City,b.State
FROM person a 
LEFT JOIN address b 
ON a.PersonId=b.PersonId;

执行结果：

+-----------+----------+------+------------+
| FirstName | LastName | City | State      |
+-----------+----------+------+------------+
| a         | A        | USA  | California |
| b         | B        | NULL | NULL       |
| c         | C        | NULL | NULL       |
+-----------+----------+------+------------+

任务6

对于表email：

+----+---------+
| Id | Email   |
+----+---------+
| 1  | a@b.com |
| 2  | c@b.com |
| 3  | a@b.com |
+----+---------+

编写一个 SQL 查询，来删除 email 表中所有重复的电子邮箱，重复的邮箱里只保留 Id 最小的那个。
执行语句应该为：

DELETE FROM email 
WHERE Id IN 
(
	SELECT MAX(Id) 
	FROM email 
	GROUP BY Email 
	HAVING COUNT(*) > 1
);

执行后email表为：

+------+---------+
| Id   | Email   |
+------+---------+
|    1 | a@b.com |
|    2 | c@b.com |
+------+---------+

番茄大圣

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
打赏
0
评论
MySQL学习（二）

任务3在下面的表中查询报名学生数大于或等于5的课程：±--------±---------+| student | class |±--------±---------+| A | Math || B | English || C | Math || D | Biology || E | Mat...
复制链接

扫一扫