4.MYSQL数据表操作
数据类型
创建数据表
以下为创建MySQL数据表的SQL通用语法:
CREATE TABLE table_name (column_name column_type);
以下例子中我们将在 RUNOOB 数据库中创建数据表runoob_tbl:
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;
实例解析:
-
如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL, 在操作数据库时如果输入该字段的数据为NULL ,就会报错。
-
AUTO_INCREMENT定义列为自增的属性,一般用于主键,数值会自动加1。
-
PRIMARY KEY关键字用于定义列为主键。 您可以使用多列来定义主键,列间以逗号分隔。
-
ENGINE 设置存储引擎,CHARSET 设置编码。
删除数据表
DROP TABLE table_name ;
插入数据
INSERT INTO table_name ( field1, field2,...fieldN )
VALUES
( value1, value2,...valueN );
查询数据
以下为在MySQL数据库中查询数据通用的 SELECT 语法:
SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M]
1.WHERE子句
SELECT field1, field2,...fieldN FROM table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....
- 查询语句中你可以使用一个或者多个表,表之间使用逗号**,** 分割,并使用WHERE语句来设定查询条件。
- 你可以在 WHERE 子句中指定任何条件。
- 你可以使用 AND 或者 OR 指定一个或多个条件。
- WHERE 子句也可以运用于 SQL 的 DELETE 或者 UPDATE 命令。
- WHERE 子句类似于程序语言中的 if 条件,根据 MySQL 表中的字段值来读取指定的数据。
(以字母顺序显示介于 "Adams"(包括)和 "Carter"(不包括)之间的人)
SELECT * FROM Persons
WHERE LastName
BETWEEN 'Adams' AND 'Carter'
2.LIKE子句
SELECT field1, field2,...fieldN
FROM table_name
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'
- 你可以在 WHERE 子句中指定任何条件。
- 你可以在 WHERE 子句中使用LIKE子句。
- 你可以使用LIKE子句代替等号 =。
- LIKE 通常与 % 一同使用,类似于一个元字符的搜索。
- 你可以使用 AND 或者 OR 指定一个或多个条件。
- 你可以在 DELETE 或 UPDATE 命令中使用 WHERE…LIKE 子句来指定条件。
(获取 runoob_author 字段中以 COM 为结尾的的所有记录)
SELECT * from runoob_tbl WHERE runoob_author LIKE '%COM'
在 WHERE LIKE 的条件查询中,SQL 提供了四种匹配方式。
- %:表示任意 0 个或多个字符。可匹配任意类型和长度的字符,有些情况下若是中文,请使用两个百分号(%%)表示。
- _:表示任意单个字符。匹配单个任意字符,它常用来限制表达式的字符长度语句。
- []:表示括号内所列字符中的一个(类似正则表达式)。指定一个字符、字符串或范围,要求所匹配对象为它们中的任一个。
- [^] :表示不在括号所列之内的单个字符。其取值和 [] 相同,但它要求所匹配对象为指定字符以外的任一个字符。
- 查询内容包含通配符时,由于通配符的缘故,导致我们查询特殊字符 “%”、“_”、“[” 的语句无法正常实现,而把特殊字符用 “[ ]” 括起便可正常查询。
3.UNION操作符
MySQL UNION 操作符语法格式:
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
SELECT country FROM apps
ORDER BY country;
4.UNION ALL
操作与UNION一样,但重复的值也会被返回。
SELECT country FROM Websites
UNION ALL
SELECT country FROM apps
ORDER BY country;
5.JOIN语句
INNER JOIN(内连接,或等值连接):
获取两个表中字段匹配关系的记录。
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName
LEFT JOIN(左连接):
获取左表所有记录,即使右表没有对应匹配的记录。
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
LEFT JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName
RIGHT JOIN(右连接):
与 LEFT JOIN 相反,用于获取右表所有记录,即使左表没有对应匹配的记录。
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
RIGHT JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName
FULL JOUN(全连接):
返回左表右表所有行。
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
FULL JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName
6.正则表达式
模式 | 描述 |
---|---|
^ | 匹配输入字符串的开始位置。如果设置了 RegExp 对象的 Multiline 属性,^ 也匹配 ‘\n’ 或 ‘\r’ 之后的位置。 |
$ | 匹配输入字符串的结束位置。如果设置了RegExp 对象的 Multiline 属性,$ 也匹配 ‘\n’ 或 ‘\r’ 之前的位置。 |
. | 匹配除 “\n” 之外的任何单个字符。要匹配包括 ‘\n’ 在内的任何字符,请使用像 ‘[.\n]’ 的模式。 |
[…] | 字符集合。匹配所包含的任意一个字符。例如, ‘[abc]’ 可以匹配 “plain” 中的 ‘a’。 |
[^…] | 负值字符集合。匹配未包含的任意字符。例如, ‘[^abc]’ 可以匹配 “plain” 中的’p’。 |
p1|p2|p3 | 匹配 p1 或 p2 或 p3。例如,‘z|food’ 能匹配 “z” 或 “food”。’(z|f)ood’ 则匹配 “zood” 或 “food”。 |
* | 匹配前面的子表达式零次或多次。例如,zo* 能匹配 “z” 以及 “zoo”。* 等价于{0,}。 |
+ | 匹配前面的子表达式一次或多次。例如,‘zo+’ 能匹配 “zo” 以及 “zoo”,但不能匹配 “z”。+ 等价于 {1,}。 |
{n} | n 是一个非负整数。匹配确定的 n 次。例如,‘o{2}’ 不能匹配 “Bob” 中的 ‘o’,但是能匹配 “food” 中的两个 o。 |
{n,m} | m 和 n 均为非负整数,其中n <= m。最少匹配 n 次且最多匹配 m 次。 |
了解以上的正则需求后,我们就可以根据自己的需求来编写带有正则表达式的SQL语句。以下我们将列出几个小实例(表名:person_tbl )来加深我们的理解:
查找name字段中以’st’为开头的所有数据:
mysql> SELECT name FROM person_tbl WHERE name REGEXP '^st';
查找name字段中以’ok’为结尾的所有数据:
mysql> SELECT name FROM person_tbl WHERE name REGEXP 'ok$';
查找name字段中包含’mar’字符串的所有数据:
mysql> SELECT name FROM person_tbl WHERE name REGEXP 'mar';
查找name字段中以元音字符开头或以’ok’字符串结尾的所有数据:
mysql> SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]|ok$';
更新数据
UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]
- 你可以同时更新一个或多个字段。
- 你可以在 WHERE 子句中指定任何条件。
- 你可以在一个单独表中同时更新数据。
UPDATE Person SET FirstName = 'Fred' WHERE LastName = 'Wilson'
UPDATE Person SET Address = 'Zhongshan 23', City = 'Nanjing'
WHERE LastName = 'Wilson'
删除数据
DELETE FROM table_name [WHERE Clause]
- 如果没有指定 WHERE 子句,MySQL 表中的所有记录将被删除。
- 你可以在 WHERE 子句中指定任何条件
- 您可以在单个表中一次性删除记录。
DELETE FROM Person WHERE LastName = 'Wilson'
DELETE * FROM table_name
数据排序
以下是 SQL SELECT 语句使用 ORDER BY 子句将查询数据排序后再返回数据:
SELECT field1, field2,...fieldN FROM table_name1, table_name2...
ORDER BY field1 [ASC [DESC][默认 ASC]], [field2...] [ASC [DESC][默认 ASC]]
- 你可以使用任何字段来作为排序的条件,从而返回排序后的查询结果。
- 你可以设定多个字段来排序。
- 你可以使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认情况下,它是按升序排列。
- 你可以添加 WHERE…LIKE 子句来设置条件。
SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC
SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC, OrderNumber ASC
如果字符集采用的是 utf8(万国码),需要先对字段进行转码然后排序:
SELECT *
FROM runoob_tbl
ORDER BY CONVERT(runoob_title using gbk);
数据分组
SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;
GROUP BY 语句根据一个或多个列对结果集进行分组
mysql> SELECT name, COUNT(*) FROM employee_tbl GROUP BY name;
+--------+----------+
| name | COUNT(*) |
+--------+----------+
| 小丽 | 1 |
| 小明 | 3 |
| 小王 | 2 |
+--------+----------+
WITH ROLLUP
WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计(SUM,AVG,COUNT…)。
例如我们将以上的数据表按名字进行分组,再统计每个人登录的次数:
mysql> SELECT name, SUM(singin) as singin_count FROM employee_tbl GROUP BY name WITH ROLLUP;
+--------+--------------+
| name | singin_count |
+--------+--------------+
| 小丽 | 2 |
| 小明 | 7 |
| 小王 | 7 |
| NULL | 16 |
+--------+--------------+
其中记录 NULL 表示所有人的登录次数。
我们可以使用 coalesce 来设置一个可以取代 NUll 的名称,coalesce 语法:
select coalesce(a,b,c);
参数说明:如果a == null,则选择b;如果b == null,则选择c;如果a != null,则选择a;如果a,b,c 都为null ,则返回为null(没意义)。
以下实例中如果名字为空我们使用总数代替:
mysql> SELECT coalesce(name, '总数'), SUM(singin) as singin_count FROM employee_tbl GROUP BY name WITH ROLLUP;
+--------------------------+--------------+
| coalesce(name, '总数') | singin_count |
+--------------------------+--------------+
| 小丽 | 2 |
| 小明 | 7 |
| 小王 | 7 |
| 总数 | 16 |
+--------------------------+--------------+
NULL 值处理
我们已经知道 MySQL 使用 SQL SELECT 命令及 WHERE 子句来读取数据表中的数据,但是当提供的查询条件字段为 NULL 时,该命令可能就无法正常工作。
为了处理这种情况,MySQL提供了三大运算符:
- IS NULL: 当列的值是 NULL,此运算符返回 true。
- IS NOT NULL: 当列的值不为 NULL, 运算符返回 true。
- <=>: 比较操作符(不同于 = 运算符),当比较的的两个值相等或者都为 NULL 时返回 true。
比如有这么一个表
mysql> SELECT * from runoob_test_tbl;
+---------------+--------------+
| runoob_author | runoob_count |
+---------------+--------------+
| RUNOOB | 20 |
| 菜鸟教程 | NULL |
| Google | NULL |
| FK | 20 |
+---------------+--------------+
以下实例中你可以看到 = 和 != 运算符是不起作用的:
mysql> SELECT * FROM runoob_test_tbl WHERE runoob_count = NULL;
Empty set (0.00 sec)
mysql> SELECT * FROM runoob_test_tbl WHERE runoob_count != NULL;
Empty set (0.01 sec)
查找数据表中 runoob_test_tbl 列是否为 NULL,必须使用 IS NULL 和 IS NOT NULL,如下实例:
mysql> SELECT * FROM runoob_test_tbl WHERE runoob_count IS NULL;
+---------------+--------------+
| runoob_author | runoob_count |
+---------------+--------------+
| 菜鸟教程 | NULL |
| Google | NULL |
+---------------+--------------+
2 rows in set (0.01 sec)
mysql> SELECT * from runoob_test_tbl WHERE runoob_count IS NOT NULL;
+---------------+--------------+
| runoob_author | runoob_count |
+---------------+--------------+
| RUNOOB | 20 |
| FK | 20 |
+---------------+--------------+
2 rows in set (0.01 sec)
修改数据表
修改表名
将数据表 testalter_tbl 重命名为 alter_tbl:
mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl;
删除、添加或修改字段(列)
如下命令使用了 ALTER 命令及 DROP 子句来删除表的 i 字段:
mysql> ALTER TABLE testalter_tbl DROP i;
如果数据表中只剩余一个字段则无法使用DROP来删除字段。
MySQL 中使用 ADD 子句来向数据表中添加列,如下实例在表 testalter_tbl 中添加 i 字段,并定义数据类型:
mysql> ALTER TABLE testalter_tbl ADD i INT;
执行以上命令后,i 字段会自动添加到数据表字段的末尾。
如果你需要指定新增字段的位置,可以使用MySQL提供的关键字 FIRST (设定位第一列), AFTER 字段名(设定位于某个字段之后)。
尝试以下 ALTER TABLE 语句, 在执行成功后,使用 SHOW COLUMNS 查看表结构的变化:
ALTER TABLE testalter_tbl DROP i;
ALTER TABLE testalter_tbl ADD i INT FIRST;
ALTER TABLE testalter_tbl DROP i;
ALTER TABLE testalter_tbl ADD i INT AFTER c;
修改字段(列)类型及名称
如果需要修改字段类型及名称, 你可以在ALTER命令中使用 MODIFY 或 CHANGE 子句 。
例如,把字段 c 的类型从 CHAR(1) 改为 CHAR(10),可以执行以下命令:
mysql> ALTER TABLE testalter_tbl MODIFY c CHAR(10);
使用 CHANGE 子句, 语法有很大的不同。 在 CHANGE 关键字之后,紧跟着的是你要修改的字段名,然后指定新字段名及类型。尝试如下实例:
mysql> ALTER TABLE testalter_tbl CHANGE i j BIGINT;
mysql> ALTER TABLE testalter_tbl CHANGE j j INT;
ALTER TABLE 对 Null 值和默认值的影响
当你修改字段时,你可以指定是否包含值或者是否设置默认值。
以下实例,指定字段 j 为 NOT NULL 且默认值为100 。
mysql> ALTER TABLE testalter_tbl
-> MODIFY j BIGINT NOT NULL DEFAULT 100;
如果你不设置默认值,MySQL会自动设置该字段默认为 NULL。
修改字段(列)默认值
你可以使用 ALTER 来修改字段的默认值,尝试以下实例:
mysql> ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
mysql> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c | char(1) | YES | | NULL | |
| i | int(11) | YES | | 1000 | |
+-------+---------+------+-----+---------+-------+
你也可以使用 ALTER 命令及 DROP子句来删除字段的默认值,如下实例:
mysql> ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;
mysql> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c | char(1) | YES | | NULL | |
| i | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
修改数据表类型(引擎)
mysql> ALTER TABLE testalter_tbl ENGINE = MYISAM;
删除外键约束
keyName是外键别名
alter table tableName drop foreign key keyName;