目录
数据库操作
创建数据库
1 |
CREATE DATABASE IF NOT EXISTS 数据库名 DEFAULT CHARSET utf8 COLLATE utf8_general_ci; |
创建数据库,该命令的作用:
1. 如果数据库不存在则创建,存在则不创建。
2. 创建RUNOOB数据库,并设定编码集为utf8
删除数据库
1 |
drop database 数据库名 |
使用数据库
1 |
use 数据库名 |
表操作
创建表
创建MySQL数据表需要以下信息:
表名
表字段名
定义每个表字段
语法
以下为创建MySQL数据表的SQL通用语法:
1 |
create table 表名 (表字段名 字段类型); |
以下例子中我们将在 RUNOOB 数据库中创建数据表runoob_tbl:
1 2 3 4 5 6 7 |
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 设置编码。
反引号是为了区分 MySql 关键字与普通字符而引入的符号,一般的,表名与字段名都使用反引号。
删除数据表
语法
以下为删除MySQL数据表的通用语法:
1 |
DROP TABLE table_name ; |
删除没有被关联的普通表:直接上面的SQL语句就行了
删除被其他表关联的父表:
方法一:先删除子表,在删除父表
方法二:删除父表的外键约束(上面有介绍),再删该表
修改数据表
1)修改表名
表名可以在一个数据库中唯一的确定一张表。
格式:
1 |
ALTER TABLE 旧表名 RENAME 新表名; |
示例:
1 2 3 4 5 |
mysql> ALTER TABLE student RENAME student4; Query OK, 0 rows affected (0.11 sec) mysql> DESCRIBE student; ERROR 1146 (42S02): Table 'example.student' doesn't exist |
由上面可以看出,改名后的表已经不存在了。
2)修改字段的数据类型
格式:
1 |
ALTER TABLE 表名 MODIFY 属性名 数据类型; |
示例:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
mysql> DESCRIBE student1; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.08 sec) mysql> ALTER TABLE student1 MODIFY name varchar(30); Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESCRIBE student1; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(30) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.01 sec) |
3)修改字段名:
格式:
1 |
ALTER TABLE 表名 CHANGE 旧属性名 新属性名 新数据类型; |
示例:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
mysql> DESCRIBE student1; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(30) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> ALTER TABLE student1 CHANGE name stu_name varchar(40); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESCRIBE student1; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | stu_name | varchar(40) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) |
这里我修改的字段名的同时也修改了数据类型了,如果你不想修改数据类型的话就按照原来的写就行了。
4)增加字段
格式:
1 |
ALTER TABLE 表名 ADD 属性名1 数据类型 [完整性约束条件] [FIRST | AFTER 属性名2]; |
其中,“属性名1”参数指需要增加的字段的名称;“FIRST”参数是可选参数,其作用是将新增字段设置为表的第一个字段;“AFTER”参数也是可选的参数,其作用是将新增字段添加到“属性名2”后面;“属性名2”当然就是指表中已经有的字段
示例:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
mysql> DESCRIBE student1; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | stu_name | varchar(40) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> ALTER TABLE student1 ADD teacher_name varchar(20) NOT NULL AFTER id; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESCRIBE student1; +--------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | teacher_name | varchar(20) | NO | | NULL | | | stu_name | varchar(40) | YES | | NULL | | +--------------+-------------+------+-----+---------+-------+ 3 rows in set (0.01 sec) |
5)删除字段
格式:
1 |
ALTER TABLE 表名 DROP 属性名; |
示例:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
mysql> DESCRIBE student1; +--------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | teacher_name | varchar(20) | NO | | NULL | | | stu_name | varchar(40) | YES | | NULL | | +--------------+-------------+------+-----+---------+-------+ 3 rows in set (0.01 sec) mysql> ALTER TABLE student1 DROP teacher_name; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESCRIBE student1; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | stu_name | varchar(40) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) |
6)更改表的存储引擎
格式:
1 |
ALTER TABLE 表名 ENGINE = 存储引擎名; |
示例:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
mysql> SHOW CREATE TABLE student2; +----------+---------------------------------------- ---------------------------------------------------- ---------------------------------------------------- | Table | Create Table +----------+---------------------------------------- ---------------------------------------------------- ---------------------------------------------------- | student2 | CREATE TABLE `student2` ( `id` int(11) NOT NULL DEFAULT '0', `stu_id` int(11) NOT NULL DEFAULT '0', `name` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`,`stu_id`) ) ENGINE=InnoDB DEFAULT CHARSET=gb2312 | +----------+---------------------------------------- ---------------------------------------------------- ---------------------------------------------------- 1 row in set (0.05 sec) mysql> ALTER TABLE student2 ENGINE = MYISAM; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SHOW CREATE TABLE student2; +----------+---------------------------------------- ---------------------------------------------------- ---------------------------------------------------- | Table | Create Table +----------+---------------------------------------- ---------------------------------------------------- ---------------------------------------------------- | student2 | CREATE TABLE `student2` ( `id` int(11) NOT NULL DEFAULT '0', `stu_id` int(11) NOT NULL DEFAULT '0', `name` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`,`stu_id`) ) ENGINE=MyISAM DEFAULT CHARSET=gb2312 | +----------+---------------------------------------- ---------------------------------------------------- ---------------------------------------------------- 1 row in set (0.00 sec) |
7)删除表的外键约束
格式:
1 |
ALTER TABLE 表名 DROP FOREIGN KEY 外键别名; |
示例:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
mysql> SHOW CREATE TABLE teacher; +---------+------------------------------------------------ ----------------------------------------------------------- ----------------------------------------------------------- --------------------------------------------------+ | Table | Create Table | +---------+------------------------------------------------ ----------------------------------------------------------- ----------------------------------------------------------- --------------------------------------------------+ | teacher | CREATE TABLE `teacher` ( `id` int(11) NOT NULL, `stu_id` int(11) DEFAULT NULL, `name` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`), KEY `STUID` (`stu_id`), CONSTRAINT `STUID` FOREIGN KEY (`stu_id`) REFERENCES `stu ) ENGINE=InnoDB DEFAULT CHARSET=gb2312 | +---------+------------------------------------------------ ----------------------------------------------------------- ----------------------------------------------------------- --------------------------------------------------+ 1 row in set (0.08 sec) mysql> ALTER TABLE teacher DROP FOREIGN KEY STUID; Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SHOW CREATE TABLE teacher; +---------+------------------------------------------------ ----------------------------------------------------------- --------------------------------------------------------+ | Table | Create Table | +---------+------------------------------------------------ ----------------------------------------------------------- --------------------------------------------------------+ | teacher | CREATE TABLE `teacher` ( `id` int(11) NOT NULL, `stu_id` int(11) DEFAULT NULL, `name` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`), KEY `STUID` (`stu_id`) ) ENGINE=InnoDB DEFAULT CHARSET=gb2312 | +---------+------------------------------------------------ ----------------------------------------------------------- --------------------------------------------------------+ 1 row in set (0.00 sec) |
表数据操作
插入数据
1 |
INSERT INTO 表名 ( 字段1, 字段2,...字段N ) VALUES ( 值1, 值2,...值N ); |
INSERT 插入多条数据
1 |
INSERT INTO table_name (field1, field2,...fieldN) VALUES (valueA1,valueA2,...valueAN),(valueB1,valueB2,...valueBN),(valueC1,valueC2,...valueCN)......; |
查询数据
1 |
SELECT column_name,column_name FROM table_name [WHERE Clause] [OFFSET M ][LIMIT N] |
查询语句中你可以使用一个或者多个表,表之间使用逗号(,)分割,并使用WHERE语句来设定查询条件。
SELECT 命令可以读取一条或者多条记录。
你可以使用星号(*)来代替其他字段,SELECT语句会返回表的所有字段数据
你可以使用 WHERE 语句来包含任何条件。
你可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。
你可以使用 LIMIT 属性来设定返回的记录数。
WHERE 子句查询
1 |
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 表中的字段值来读取指定的数据。
以下为操作符列表,可用于 WHERE 子句中。
下表中实例假定 A 为 10, B 为 20
操作符 | 描述 | 实例 |
---|---|---|
= | 等号,检测两个值是否相等,如果相等返回true | (A = B) 返回false。 |
<>, != | 不等于,检测两个值是否相等,如果不相等返回true | (A != B) 返回 true。 |
> | 大于号,检测左边的值是否大于右边的值, 如果左边的值大于右边的值返回true | (A > B) 返回false。 |
< | 小于号,检测左边的值是否小于右边的值, 如果左边的值小于右边的值返回true | (A < B) 返回 true。 |
>= | 大于等于号,检测左边的值是否大于或等于右边的值, 如果左边的值大于或等于右边的值返回true | (A >= B) 返回false。 |
<= | 小于等于号,检测左边的值是否小于于或等于右边的值, 如果左边的值小于或等于右边的值返回true | (A <= B) 返回 true。 |
如果我们想再 MySQL 数据表中读取指定的数据,WHERE 子句是非常有用的。
使用主键来作为 WHERE 子句的条件查询是非常快速的。
如果给定的条件在表中没有任何匹配的记录,那么查询不会返回任何数据。
UPDATE 查询(数据更新)
1 |
UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause] |
- 你可以同时更新一个或多个字段。
- 你可以在 WHERE 子句中指定任何条件。
- 你可以在一个单独表中同时更新数据。
当你需要更新数据表中指定行的数据时 WHERE 子句是非常有用的。
update 语句可用来修改表中的数据, 简单来说基本的使用形式为:
update 表名称 set 列名称=新值 where 更新条件;
以下是在表 students 中的实例:
将 id 为 5 的手机号改为默认的 – : update students settel=default where id=5;
将所有人的年龄增加 1: update students set age=age+1;
将手机号为 13288097888 的姓名改为 “小明”, 年龄改为 19: update students setname=”小明”, age=19 wheretel=”13288097888″;
当我们需要将字段中的特定字符串批量修改为其他字符串时,可已使用以下操作:
UPDATE table_name SET field=REPLACE(field, ‘old-string’, ‘new-string’)
[WHERE Clause]
实例:
以下实例将更新 runoob_id 为 3 的runoob_title 字段值的 “C++” 替换为 “Python”:
UPDATE runoob_tbl SET runoob_title = REPLACE(runoob_title, ‘C++’, ‘Python’) where
runoob_id = 3;
DELETE 语句(删除数据)
1 |
DELETE FROM table_name [WHERE Clause] |
- 如果没有指定 WHERE 子句,MySQL 表中的所有记录将被删除。
- 你可以在 WHERE 子句中指定任何条件
- 您可以在单个表中一次性删除记录。
当你想删除数据表中指定的记录时 WHERE 子句是非常有用的。
delete from 表名称 where 删除条件;
以下是在表 students 中的实例:
删除 id 为 3 的行: delete from students where id=3;
删除所有年龄小于 21 岁的数据: delete from students where age<20;
删除表中的所有数据: delete from students;
LIKE 子句
我们知道在 MySQL 中使用 SQL SELECT 命令来读取数据, 同时我们可以在 SELECT 语句中使用 WHERE 子句来获取指定的记录。
WHERE 子句中可以使用等号 = 来设定获取数据的条件,如 “runoob_author = ‘RUNOOB.COM'”。
但是有时候我们需要获取 runoob_author 字段含有 “COM” 字符的所有记录,这时我们就需要在 WHERE 子句中使用 SQL LIKE 子句。
SQL LIKE 子句中使用百分号 %字符来表示任意字符,类似于UNIX或正则表达式中的星号 *。
如果没有使用百分号 %, LIKE 子句与等号 = 的效果是一样的。
语法
以下是 SQL SELECT 语句使用 LIKE 子句从数据表中读取数据的通用语法:
1 2 3 |
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_tbl 表中获取 runoob_author 字段中以 COM 为结尾的的所有记录:
1 2 3 4 5 6 7 8 9 10 |
mysql> use RUNOOB; Database changed mysql> SELECT * from runoob_tbl WHERE runoob_author LIKE '%COM'; +-----------+---------------+---------------+-----------------+ | runoob_id | runoob_title | runoob_author | submission_date | +-----------+---------------+---------------+-----------------+ | 3 | 学习 Java | RUNOOB.COM | 2015-05-01 | | 4 | 学习 Python | RUNOOB.COM | 2016-03-06 | +-----------+---------------+---------------+-----------------+ 2 rows in set (0.01 sec) |