基础概念
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 |
+------+---------+