MySQL目录结构
配置my.ini
MySQL5.7的my.ini位于\ProgramData\MySQL\MySQL Server 5.7目录下(可能有的版本的my.ini就在安装目录下),该该目录下还有一个data目录存放我们的创建的数据库。
打开my.ini文件[client]
用于配置客户端,主要就是3306端口。[mysqld]
配置的是服务端。
特别要注意一点:编码方式是utf8而不是utf-8。
在Linux系统中该配置文件在/etc/mysql/my.cnf。
启动、关闭重启MySQL
service mysql start|stop|restart
- 1
![](http://static.blog.csdn.net/images/save_snippets.png)
- 1
登录与退出MySQL
root@ubuntu:/etc/mysql# mysql -v # 查看mysql版本
mysql Ver 14.14 Distrib 5.5.35, for debian-linux-gnu (i686) using readline 6.2
root@ubuntu:/etc/mysql# mysql -uroot -p123456 -P3306 -h192.168.132.128 # -u用户名 -p密码(为了安全可以不写,会提示输入) -P端口 -h主机名
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 330
Server version: 5.5.35-0ubuntu0.12.04.2 (Ubuntu)
mysql> \q|exit|quit # 退出mysql
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
![](http://static.blog.csdn.net/images/save_snippets.png)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
修改mysql提示符
- 连接mysql时通过
--prompt
参数指定
- 连接上客户端的时候通过
prompt
命令指定
MySQL提示符如下表:
参数 | 描述 |
---|---|
\D | 完整日期 |
\d | 当前数据库 |
\h | 服务器名 |
\u | 当前用户 |
以上的提示符还能连用,例如:
mysql> prompt \u@\h \d> # 用户名@主机名 数据库名
PROMPT set to '\u@\h \d>'
root@localhost demo>
- 1
- 2
- 3
![](http://static.blog.csdn.net/images/save_snippets.png)
- 1
- 2
- 3
MySQL常用命令
SET NAMES gbk; -- 客户端以GBK的编码形式呈现数据
- 1
![](http://static.blog.csdn.net/images/save_snippets.png)
- 1
mysql> SELECT VERSION(); # 显示mysql版本,相当于登录数据库之前输入mysql -V
+-------------------------+
| VERSION() |
+-------------------------+
| 5.5.35-0ubuntu0.12.04.2 |
+-------------------------+
1 row in set (0.00 sec)
mysql> SELECT USER(); # 显示当前用户
+----------------+
| USER() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
mysql> SELECT NOW(); # 显示系统时间
+---------------------+
| NOW() |
+---------------------+
| 2015-04-05 21:02:31 |
+---------------------+
1 row in set (0.00 sec)
mysql> # 在Linux的mysql中也可以使用Ctrl+L清屏
- 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
![](http://static.blog.csdn.net/images/save_snippets.png)
- 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
MySQL语句规范
- 关键字和函数名称全部大写;
- 数据库名、表名、字段名称全部小写;
- SQL语句必须以分号结束。
操作数据库
创建数据库
-- 说明:花括号表示必选项,竖线表示多选一,方括号表示可选
CREATE {DATABASE|SCHEMA} [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] character_name
- 1
- 2
![](http://static.blog.csdn.net/images/save_snippets.png)
- 1
- 2
mysql>
mysql> CREATE DATABASE t1;
Query OK, 1 row affected (0.00 sec)
mysql>
- 1
- 2
- 3
- 4
- 5
![](http://static.blog.csdn.net/images/save_snippets.png)
- 1
- 2
- 3
- 4
- 5
我们可以使用source
命令运行sql脚本。登录mysql数据库。
source 数据库脚本名
- 1
![](http://static.blog.csdn.net/images/save_snippets.png)
- 1
查看当前服务器中的数据库列表
SHOW {DATABASES | SCHEMAS} [LIKE 'pattern' | WHERE expr]
- 1
![](http://static.blog.csdn.net/images/save_snippets.png)
- 1
正确安装mysql后有4个数据库:
mysql> SHOW CREATE DATABASE t1; # 查看创建数据库使用的命令
+----------+-------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------+
| t1 | CREATE DATABASE `t1` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> CREATE DATABASE IF NOT EXISTS t2 CHARACTER SET GBK; # 使用gbk编码方式创建数据库
Query OK, 1 row affected (0.00 sec)
mysql> SHOW CREATE DATABASE t2;
+----------+------------------------------------------------------------+
| Database | Create Database |
+----------+------------------------------------------------------------+
| t2 | CREATE DATABASE `t2` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
![](http://static.blog.csdn.net/images/save_snippets.png)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
修改数据库
ALTER {DATABASE | SCHEMA} [db_name] [DEFAULT] CHARACTER SET [=] character_name
- 1
![](http://static.blog.csdn.net/images/save_snippets.png)
- 1
删除数据库
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
- 1
![](http://static.blog.csdn.net/images/save_snippets.png)
- 1
数据类型和操作数据表
数据类型决定了数据的存储格式,代表了不同的信息类型。
1.整型
2.浮点型
项目开发中以float居多
3.日期型
项目开发中该类型用得并不多,因为涉及到时区的问题,多用数字表示。
4.字符型
VARCHAR,TEXT,DATE,TIME,ENUM等类型的数据也需要单引号修饰,而INT,FLOAT,DOUBLE等则不需要。
使用某个数据库
mysql> USE test; # 使用数据库
Database changed
mysql> SELECT DATABASE(); #查看当前使用的数据库
+------------+
| DATABASE() |
+------------+
| test |
+------------+
1 row in set (0.00 sec)
mysql>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
![](http://static.blog.csdn.net/images/save_snippets.png)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
创建表
CREATE TABLE [IF NOT EXISTS] table_name(
column_name data_type,
……
)
- 1
- 2
- 3
- 4
![](http://static.blog.csdn.net/images/save_snippets.png)
- 1
- 2
- 3
- 4
创建用户表tb_user
CREATE TABLE tb_user(
username VARCHAR(20), -- 【用户名】
age TINYINT UNSIGNED, -- 【年龄】 无符号微整数(0~255)
salary FLOAT(8,2) UNSIGNED -- 【工资】 一共有8位,小数部分有2位
);
- 1
- 2
- 3
- 4
- 5
![](http://static.blog.csdn.net/images/save_snippets.png)
- 1
- 2
- 3
- 4
- 5
查看表
SHOW TABLES [FROM da_name] [LIKE 'pattern'|WHERE expr]
- 1
![](http://static.blog.csdn.net/images/save_snippets.png)
- 1
例如:
mysql> CREATE TABLE tb_user( # 创建表
-> username VARCHAR(20),
-> age TINYINT UNSIGNED,
-> salary FLOAT(8,2) UNSIGNED
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW TABLES; # 查看表
+----------------+
| Tables_in_test |
+----------------+
| tb_user |
+----------------+
1 row in set (0.00 sec)
mysql>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
![](http://static.blog.csdn.net/images/save_snippets.png)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
查看表结构
SHOW COLUMNS FROM tb_name # 或者
DESC tb_name
- 1
- 2
![](http://static.blog.csdn.net/images/save_snippets.png)
- 1
- 2
例如:
重命名表
RENAME TABLE 原名 TO 新名;
ALTER TABLE 原名 RENAME 新名;
ALTER TABLE 原名 RENAME TO 新名;
- 1
- 2
- 3
![](http://static.blog.csdn.net/images/save_snippets.png)
- 1
- 2
- 3
插入记录
INSERT [INTO] tb_name [(col_name,...)] VALUES(val,...)
- 1
![](http://static.blog.csdn.net/images/save_snippets.png)
- 1
例如:
mysql> INSERT tb_user VALUES('Tom',22,3658.32); # 当我们省略字段名的时候所有的字段都要赋值!
Query OK, 1 row affected (0.03 sec)
mysql> INSERT tb_user VALUES('Tom',22);
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql>
mysql> INSERT tb_user(username,age) VALUES('Kitty',18); # 为指定字段赋值
Query OK, 1 row affected (0.03 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
![](http://static.blog.csdn.net/images/save_snippets.png)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
记录的查询
SELECT expr,... FROM tb_name
- 1
![](http://static.blog.csdn.net/images/save_snippets.png)
- 1
例如:
mysql> SELECT * FROM tb_user; # 这里的*表示的是字段的过滤
+----------+------+---------+
| username | age | salary |
+----------+------+---------+
| Tom | 22 | 3658.32 |
| Kitty | 18 | NULL |
+----------+------+---------+
2 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
![](http://static.blog.csdn.net/images/save_snippets.png)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
空值与非空
NULL允许空值(默认),NOT NULL禁止非空。例如:
字段的自动编号
AUTO_INCREMENT
该字段要么是整数,要么小数位数为0,且必须和主键组合使用,默认情况下初始值为1,增量为1.——保证记录的唯一性。
主键约束(PRIMARY KEY):
每张表只能有一个主键,能够保证记录的唯一性,主键自动为NOT NULL
,并自动创建索引。
主键我们可以写成KEY或者PRIMARY KEY。
唯一约束——UNIQUE KEY
- 唯一约束也可以保证记录的唯一性
- 唯一约束的字段可以为NULL
- 一张表可以有多个唯一约束
mysql> CREATE TABLE tb_user(
->id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
->username VARCHAR(20) NOT NULL UNIQUE KEY, age TINYINT UNSIGNED
->);
Query OK, 0 rows affected (0.08 sec)
mysql> DESC tb_user;
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | UNI | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
+----------+----------------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
mysql>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
![](http://static.blog.csdn.net/images/save_snippets.png)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
默认约束-DEFAULT
当插入记录时,如果没有明确为字段赋值,则自动赋予默认值。
mysql> CREATE TABLE tb_user(
-> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, -- 主键约束,自动编号
-> username VARCHAR(20) NOT NULL UNIQUE KEY, -- 唯一约束
-> sex ENUM('1','2','3') DEFAULT '3' -- 性别是枚举值,默认是3(保密)
-> );
Query OK, 0 rows affected (0.12 sec)
mysql> DESC tb_user;
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | UNI | NULL | |
| sex | enum('1','2','3') | YES | | 3 | |
+----------+----------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> INSERT tb_user(username) VALUES('Tom'); # 没有指定性别时,默认是3
Query OK, 1 row affected (0.08 sec)
mysql> INSERT tb_user(username,sex) VALUES('Kitty','2'); # 指定性别
Query OK, 1 row affected (0.08 sec)
mysql> SELECT * FROM tb_user;
+----+----------+------+
| id | username | sex |
+----+----------+------+
| 1 | Tom | 3 |
| 3 | Kitty | 2 |
+----+----------+------+
2 rows in set (0.00 sec)
- 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
![](http://static.blog.csdn.net/images/save_snippets.png)
- 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
约束和修改表
外键约束和唯一约束
- 约束保证了数据的完整性和一致性。
- 约束分为表级约束和列级约束。
- 约束类型包括
- NOT NULL(非空约束)
- PRIMARY KEY(主键约束)
- UNIQUE KEY(唯一约束)
- DEFAULT(默认约束)
- FOREIGN KEY(外键约束)
外键约束:保证数据的完整性、一致性,实现表的一对一或者一对多关系。
外键约束的要求
- 父表(子表所参照的表)和子表(具有外键列的表)使用相同的存储引擎,禁止使用临时表。
- 表的存储引擎只能是InnoDB。
- 外键列(加过FOREIGN关键字的列)和参照列(外键列所参照的列)必须具有相似的数据类型。其中数字的长度或者是否有符号位必须相同;而字符的长度可以不同。
- 外键列和参照列必须建立索引。如果外键列不存在索引,MySQL将自动创建索引。
编辑表的存储引擎
MySQL配置文件default-storage-engine=INNODB
现在我们要创建一张users表,users表中有省份,只需要在user表中保存省份id就行了,这样两张表就联系起来了!目前的子表就是users
以上的命令中我们并没有显式创建索引,但是有了外键约束,MySQL会自动创建索引。
外键约束的参照操作
CASCADE
:从父表删除或更新且自动删除或更新子表中匹配的行。SET NULL
:从父表删除或更新行,并设置子表中的外键列为NULL
。如果使用该项,必须保证子表列没有指定NOT NULL
。RESTRICT
:拒绝对父表删除或更新操作。NO ACTION
:标准SQL关键字,在MYSQL中与RESTRICT
相同。
重新建立users表:
# 建表
mysql> CREATE TABLE users(
-> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> username VARCHAR(10) NOT NULL,
-> pid SMALLINT UNSIGNED,
-> FOREIGN KEY (pid) REFERENCES provinces(id) ON DELETE CASCADE -- 在父表中删除或者更新记录,同时更新子表中的相应行
-> );
Query OK, 0 rows affected (0.01 sec)
- 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
![](http://static.blog.csdn.net/images/save_snippets.png)
- 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
删除父表(province表中的记录后)
在实际的开发中我们很少使用物理的外键约束,因为它只支持InnoDB这一种引擎。所谓逻辑的外键约束指的是我们在定义2张表的时候确定两张表的关系而不去使用FOREIGN KEY
这个关键字。
表级约束和列级约束
- 对一个数据列建立的约束,称为列级约束。
- 对多个数据列建立的约束,称为表级约束。
- 列级约束既可以在列定义时声明,也可以在列定义后声明。
- 表级约束只能在列定义后声明。
- 在实际的开发中经常使用的是列级约束。
修改数据表
增加列
-- 添加单列
ALTER TABLE tb_name ADD [COLUMN] col_name column_definition [FIRST|AFTER col_name]
-- 添加多列,不能指定列的位置关系
ALTER TABLE tb_name ADD [COLUMN] (col_name col_definition,...)
- 1
- 2
- 3
- 4
- 5
![](http://static.blog.csdn.net/images/save_snippets.png)
- 1
- 2
- 3
- 4
- 5
删除列
-- 删除列
ALTER TABLE tb_name DROP [COLUMN] col_name
- 1
- 2
![](http://static.blog.csdn.net/images/save_snippets.png)
- 1
- 2
删除列的同时可以新增列,同理新增列的同时可以删除列,操作之间以逗号分隔。
添加约束
-- 添加主键约束
ALTER TABLE tb_name ADD [CONSTRAINT[symbol]] PRIMARY KEY [index_type] (index_col_name,...)
-- 添加唯一约束
ALTER TABLE tb_name ADD [CONSTRAINT[symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,...)
-- 添加外键约束
ALTER TABLE tb_name ADD [CONSTRAINT[symbol]] FOREIGN KEY [index_name] [index_type] (index_col_name,...) reference_definiton
-- 添加/删除默认约束
ALTER TABLE tb_name ALTER [CONSTRAINT] col_name {SET DEFAULT literal|DROP DEFAULT}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
![](http://static.blog.csdn.net/images/save_snippets.png)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
例如:为users表的id字段添加主键:
为username字段添加唯一约束:
为users表添加外键:
为age字段添加默认值:
删除age字段的默认值:
删除约束
-- 删除主键约束
ALTER TABLE tb_name DROP PRIMARY KEY
-- 删除唯一约束(首先要知道索引的名字SHOW INDEXES FROM tb_name\G命令)
ALTER TABLE tb_name DROP {INDEX|KEY} inedx_name
-- 删除外键约束(需要知道外键约束的名称:SHOW CREATE TABLE tb_name命令)
ALTER TABLE tb_name DROP FOREIGN KEY fk_symbol
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
![](http://static.blog.csdn.net/images/save_snippets.png)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
例:
删除users表的id字段的主键约束:
删除users表的username字段的唯一约束:
删除users表的pid字段的外键约束:
修改列定义和列名称
-- 修改列定义(由大类型改为小类型的时候可能会丢失数据)
ALTER TABLE tb_name MODIFY [COLUMN] col_name column_definition [FIRST|AFTER col_name]
-- 修改列名称
ALTER TABLE tb_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name]
- 1
- 2
- 3
- 4
- 5
![](http://static.blog.csdn.net/images/save_snippets.png)
- 1
- 2
- 3
- 4
- 5
例:
将users表的id字段变为第一列:
将users表的pid字段的名称和类型同时修改:
修改表名
-- 方法一
ALTER TABLE tb_name RENAME [TO|AS] new_tb_name
-- 方法二,可以为多张数据表更名
RENAME TABLE tb_name TO new_tb_name [,tb_name2 TO new_tb_name2]...
- 1
- 2
- 3
- 4
- 5
![](http://static.blog.csdn.net/images/save_snippets.png)
- 1
- 2
- 3
- 4
- 5
例:
将users表更名为user:
在实际的开发中应该尽量避免数据列和表的更名——可能导致某些视图或者存储过程无法工作!
记录的操作
插入记录——INSERT
-- 如果省略了列名,表示所有的字段都要赋值;插入语句可以一次性插入多条记录
INSERT [INTO] tb_name [(col_name,...)] {VALUES|VALUE} ({expr|DEFAULT},...),(...),...
- 1
- 2
![](http://static.blog.csdn.net/images/save_snippets.png)
- 1
- 2
首先创建一张user表:
CREATE TABLE user(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
password VARCHAR(32) NOT NULL,
age TINYINT UNSIGNED NOT NULL DEFAULT 10,
sex BOOLEAN
);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
![](http://static.blog.csdn.net/images/save_snippets.png)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
向user表中插入记录:
INSERT user VALUES(NULL,'A','111',11,1); -- id字段为了保持自增可以使用NULL或者DEFAULT
INSERT user VALUE(DEFAULT,'B','222',31,2);
INSERT user(username,password) VALUES ('C','333'); -- 指定插入的字段
INSERT user VALUES(8,'D','444',36,2); -- 人为指定id,该id必须不存在,后面记录的编号以此为基准
INSERT user VALUES(NULL,'G','555',DEFAULT,2); -- 保持age字段的默认值
-- 同时插入多条记录(中间用逗号分隔),列可以是函数或者表达式,md5()是MySQL内置函数
INSERT user VALUES(NULL,'E',md5('666'),3*5-6,2),(NULL,'F','777',18,2);
SELECT * FROM user; -- 运行结果
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 1 | A | 111 | 11 | 1 |
| 2 | B | 222 | 31 | 2 |
| 3 | C | 333 | 10 | NULL |
| 8 | D | 444 | 36 | 2 |
| 9 | G | 555 | 10 | 2 |
| 10 | E | fae0b27c451c728867a567e8c1bb4e53 | 9 | 2 |
| 11 | F | 777 | 18 | 2 |
+----+----------+----------------------------------+-----+------+
7 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
![](http://static.blog.csdn.net/images/save_snippets.png)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
INSERT SET-SELECT
-- INSERT SET方式。可以使用子查询(SubQuery),只能一次插入一条记录;用得较少
INSERT [INTO] tb_name SET col_name={expr|DEFAULT},...
-- INSERT SELECT方式(将查询结果写入到表)
INSERT [INTO] tb_name [(col_name,...)] SELECT ...
- 1
- 2
- 3
- 4
- 5
![](http://static.blog.csdn.net/images/save_snippets.png)
- 1
- 2
- 3
- 4
- 5
使用INSERT-SET 方式向表中插入记录:
INSERT user SET username='G',password=md5('888');
SELECT * FROM user;
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 1 | A | 111 | 11 | 1 |
| 2 | B | 222 | 31 | 2 |
| 3 | C | 333 | 10 | NULL |
| 8 | D | 444 | 36 | 2 |
| 9 | G | 555 | 10 | 2 |
| 10 | E | fae0b27c451c728867a567e8c1bb4e53 | 9 | 2 |
| 11 | F | 777 | 18 | 2 |
| 12 | G | 0a113ef6b61820daa5611c870ed8d5ee | 10 | NULL |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
![](http://static.blog.csdn.net/images/save_snippets.png)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
有子查询的插入操作
1.建立一张test表:
CREATE TABLE test(
id TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20)
);
- 1
- 2
- 3
- 4
![](http://static.blog.csdn.net/images/save_snippets.png)
- 1
- 2
- 3
- 4
user表和test表分别如下:
mysql> SELECT * FROM test;
Empty set (0.00 sec)
mysql> SELECT * FROM user;
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 1 | A | 111 | 15 | 0 |
| 3 | C | 333 | 12 | 0 |
| 8 | D | 444 | 43 | 1 |
| 9 | G | 555 | 6 | 0 |
| 10 | E | fae0b27c451c728867a567e8c1bb4e53 | 14 | 1 |
| 11 | F | 777 | 12 | 0 |
| 12 | G | 0a113ef6b61820daa5611c870ed8d5ee | 13 | 1 |
+----+----------+----------------------------------+-----+------+
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
![](http://static.blog.csdn.net/images/save_snippets.png)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
现在需要将user表中年龄大于12的用户插入到test表:
mysql> INSERT test(username) SELECT username FROM user WHERE age > 12;
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM test;
+----+----------+
| id | username |
+----+----------+
| 1 | A |
| 2 | D |
| 3 | E |
| 4 | G |
+----+----------+
4 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
![](http://static.blog.csdn.net/images/save_snippets.png)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
单表记录更新——UPDATE
-- 如果省略了WHERE条件所有的记录将全部更新!
UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1={expr1|DEFAULT} [,col_name2={expr2|DEFAULT}] ... [WHERE where_condition]
- 1
- 2
![](http://static.blog.csdn.net/images/save_snippets.png)
- 1
- 2
SELECT * FROM user;
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 1 | A | 111 | 11 | 1 |
| 2 | B | 222 | 31 | 2 |
| 3 | C | 333 | 10 | NULL |
| 8 | D | 444 | 36 | 2 |
| 9 | G | 555 | 10 | 2 |
| 10 | E | fae0b27c451c728867a567e8c1bb4e53 | 9 | 2 |
| 11 | F | 777 | 18 | 2 |
| 12 | G | 0a113ef6b61820daa5611c870ed8d5ee | 10 | NULL |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)
UPDATE user SET age = age + 5; -- 让表中的年龄字段都在各自的基础上加5
Query OK, 8 rows affected (0.11 sec)
Rows matched: 8 Changed: 8 Warnings: 0
SELECT * FROM user;
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 1 | A | 111 | 16 | 1 |
| 2 | B | 222 | 36 | 2 |
| 3 | C | 333 | 15 | NULL |
| 8 | D | 444 | 41 | 2 |
| 9 | G | 555 | 15 | 2 |
| 10 | E | fae0b27c451c728867a567e8c1bb4e53 | 14 | 2 |
| 11 | F | 777 | 23 | 2 |
| 12 | G | 0a113ef6b61820daa5611c870ed8d5ee | 15 | NULL |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.01 sec)
UPDATE user SET age = age - id,sex = 0; -- 将年龄字段改为各自的年龄减去id,性别都变成0
Query OK, 8 rows affected (0.00 sec)
Rows matched: 8 Changed: 8 Warnings: 0
SELECT * FROM user;
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 1 | A | 111 | 15 | 0 |
| 2 | B | 222 | 34 | 0 |
| 3 | C | 333 | 12 | 0 |
| 8 | D | 444 | 33 | 0 |
| 9 | G | 555 | 6 | 0 |
| 10 | E | fae0b27c451c728867a567e8c1bb4e53 | 4 | 0 |
| 11 | F | 777 | 12 | 0 |
| 12 | G | 0a113ef6b61820daa5611c870ed8d5ee | 3 | 0 |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)
UPDATE user SET age = age + 10 WHERE id % 2 = 0; -- 让所有的id为偶数的用户年龄在原来的基础上加10(注意是一个等号)
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0
SELECT * FROM user;
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 1 | A | 111 | 15 | 0 |
| 2 | B | 222 | 44 | 0 |
| 3 | C | 333 | 12 | 0 |
| 8 | D | 444 | 43 | 0 |
| 9 | G | 555 | 6 | 0 |
| 10 | E | fae0b27c451c728867a567e8c1bb4e53 | 14 | 0 |
| 11 | F | 777 | 12 | 0 |
| 12 | G | 0a113ef6b61820daa5611c870ed8d5ee | 13 | 0 |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)
- 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
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
![](http://static.blog.csdn.net/images/save_snippets.png)
- 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
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
单表删除记录
-- 注意:如果省略WHERE条件将删除数据表中的全部记录!
DELETE FROM tb_name [WHERE where_condition]
- 1
- 2
![](http://static.blog.csdn.net/images/save_snippets.png)
- 1
- 2
例:
DELETE FROM user WHERE id = 2; -- 删除id为2的记录,删除之后该id不会重用
DELETE FROM user; -- 删除全表记录
- 1
- 2
![](http://static.blog.csdn.net/images/save_snippets.png)
- 1
- 2
查询表达式解析
SELECT select_expr [,select_expr ...]
[
FROM table_references
[WHERE where_condition]
[GROUP BY {col_name | position} [ASC|DESC], ...]
[HAVING where_condition]
[ORDER BY {col_name | expr | position} [ASC|DESC], ...]
[LIMIT {[offest,] row_count | row_count OFFEST offest}]
]
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
![](http://static.blog.csdn.net/images/save_snippets.png)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
SELECT语句最基本要有SELECT关键字和查询表达式,例如:
mysql> SELECT 9.9 + 1.1;
+-----------+
| 9.9 + 1.1 |
+-----------+
| 11.0 |
+-----------+
1 row in set (0.00 sec)
mysql> SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2015-04-08 18:56:56 |
+---------------------+
1 row in set (0.09 sec)
mysql>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
![](http://static.blog.csdn.net/images/save_snippets.png)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
查询表达式
- 每一个表达式表示想要的一列,至少要有一个。
- 多个列之间以逗号分隔。
- 星号(
*
)表示所有列。tb_name.*表示命名表的所有列。 - 查询表达式可以使用[AS] alias_name为其赋予别名。
- 别名可以用于GROUP BY,ORDER BY或者HAVING子句。
例:
mysql> SHOW COLUMNS FROM user;
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | | NULL | |
| password | varchar(32) | NO | | NULL | |
| age | tinyint(3) unsigned | NO | | 10 | |
| sex | tinyint(1) | YES | | NULL | |
+----------+----------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> SELECT password,username FROM user; -- 查询表达式的列的顺序决定结果集的顺序
mysql> SELECT user.password,user.username FROM user;-- 该语句与上面的等效,使用多表连接的时候可能两张表的字段相同
+----------------------------------+----------+
| password | username |
+----------------------------------+----------+
| 111 | A |
| 333 | C |
| 444 | D |
| 555 | G |
| fae0b27c451c728867a567e8c1bb4e53 | E |
| 777 | F |
| 0a113ef6b61820daa5611c870ed8d5ee | G |
+----------------------------------+----------+
7 rows in set (0.00 sec)
mysql> SELECT id AS user_id,username AS user_name FROM user;-- 使用别名(影响结果集的列名)
+---------+-----------+
| user_id | user_name |
+---------+-----------+
| 1 | A |
| 3 | C |
| 8 | D |
| 9 | G |
| 10 | E |
| 11 | F |
| 12 | G |
+---------+-----------+
7 rows in set (0.00 sec)
mysql> SELECT id username FROM user; -- 别名的使用可以省略AS关键字,下面的username就作为了id的别名
+----------+
| username | -- 强烈建议使用别名的时候加上AS关键字,上面的语句很容易让人抽风
+----------+
| 1 |
| 3 |
| 8 |
| 9 |
| 10 |
| 11 |
| 12 |
+----------+
7 rows in set (0.00 sec)
- 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
- 52
- 53
- 54
![](http://static.blog.csdn.net/images/save_snippets.png)
- 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
- 52
- 53
- 54
使用WHERE语句进行条件查询
- WHERE语句的作用是对记录进行过滤,如果没有指定WHERE子句,则显示所有记录。
- 在WHERE表达式中,可以使用MySQL支持的函数或者运算符。
使用GROUP BY对结果集进行分组
[GROUP BY {col_name | position} [ASC|DESC], ... ]
- 1
![](http://static.blog.csdn.net/images/save_snippets.png)
- 1
例:
mysql> SELECT * FROM user;
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 1 | A | 111 | 15 | 0 |
| 3 | C | 333 | 12 | 0 |
| 8 | D | 444 | 43 | 1 |
| 9 | G | 555 | 6 | 0 |
| 10 | E | fae0b27c451c728867a567e8c1bb4e53 | 14 | 1 |
| 11 | F | 777 | 12 | 0 |
| 12 | G | 0a113ef6b61820daa5611c870ed8d5ee | 13 | 1 |
+----+----------+----------------------------------+-----+------+
7 rows in set (0.00 sec)
mysql> SELECT sex FROM user GROUP BY sex; -- 结果集按照性别分组
+------+
| sex |
+------+
| 0 |
| 1 |
+------+
2 rows in set (0.13 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
![](http://static.blog.csdn.net/images/save_snippets.png)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
使用HAVING语句设置分组条件
注意:HAVING子句后面要么是聚合函数,要么HAVING后面的字段出现在查找字段内。
mysql> SELECT * FROM user;
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 1 | A | 111 | 15 | 0 |
| 3 | C | 333 | 12 | 0 |
| 8 | D | 444 | 43 | 1 |
| 9 | G | 555 | 6 | 0 |
| 10 | E | fae0b27c451c728867a567e8c1bb4e53 | 14 | 1 |
| 11 | F | 777 | 12 | 0 |
| 12 | G | 0a113ef6b61820daa5611c870ed8d5ee | 13 | 1 |
+----+----------+----------------------------------+-----+------+
7 rows in set (0.00 sec)
mysql> SELECT sex FROM user GROUP BY sex HAVING count(id) > 3; -- HAVING后面跟的是聚合函数
+------+
| sex |
+------+
| 0 |
+------+
1 row in set (0.10 sec)
mysql> SELECT sex,age FROM user GROUP BY sex HAVING age > 15; -- HAVING后面出现的字段在查询字段中
+------+-----+
| sex | age |
+------+-----+
| 1 | 43 |
+------+-----+
1 row in set (0.00 sec)
- 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
![](http://static.blog.csdn.net/images/save_snippets.png)
- 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
使用ORDER BY对结果集进行排序
[ORDER BY {col_name | expr | position} [ASC | DESC], ... ]
- 1
![](http://static.blog.csdn.net/images/save_snippets.png)
- 1
例:
mysql> SELECT * FROM user; -- 默认是按照id升序排列
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 1 | A | 111 | 15 | 0 |
| 3 | C | 333 | 12 | 0 |
| 8 | D | 444 | 43 | 1 |
| 9 | G | 555 | 6 | 0 |
| 10 | E | fae0b27c451c728867a567e8c1bb4e53 | 14 | 1 |
| 11 | F | 777 | 12 | 0 |
| 12 | G | 0a113ef6b61820daa5611c870ed8d5ee | 13 | 1 |
+----+----------+----------------------------------+-----+------+
7 rows in set (0.00 sec)
mysql> SELECT * FROM user ORDER BY id DESC; -- 按照id降序排列
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 12 | G | 0a113ef6b61820daa5611c870ed8d5ee | 13 | 1 |
| 11 | F | 777 | 12 | 0 |
| 10 | E | fae0b27c451c728867a567e8c1bb4e53 | 14 | 1 |
| 9 | G | 555 | 6 | 0 |
| 8 | D | 444 | 43 | 1 |
| 3 | C | 333 | 12 | 0 |
| 1 | A | 111 | 15 | 0 |
+----+----------+----------------------------------+-----+------+
7 rows in set (0.00 sec)
mysql> SELECT * FROM user ORDER BY age ASC,id DESC; -- 按照年龄升序、id降序。权重年龄大于id
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 9 | G | 555 | 6 | 0 |
| 11 | F | 777 | 12 | 0 |
| 3 | C | 333 | 12 | 0 |
| 12 | G | 0a113ef6b61820daa5611c870ed8d5ee | 13 | 1 |
| 10 | E | fae0b27c451c728867a567e8c1bb4e53 | 14 | 1 |
| 1 | A | 111 | 15 | 0 |
| 8 | D | 444 | 43 | 1 |
+----+----------+----------------------------------+-----+------+
7 rows in set (0.00 sec)
- 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
![](http://static.blog.csdn.net/images/save_snippets.png)
- 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
使用LIMIT语句限制结果集的数量
-- OFFSET常用于PHP中的分页(当前页面-1)* 每页显示的记录数
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
- 1
- 2
![](http://static.blog.csdn.net/images/save_snippets.png)
- 1
- 2
子查询与连接
数据准备
-- 创建商品表
CREATE TABLE IF NOT EXISTS tdb_goods(
goods_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
goods_name VARCHAR(150) NOT NULL,
goods_cate VARCHAR(40) NOT NULL,
brand_name VARCHAR(40) NOT NULL,
goods_price DECIMAL(15,3) UNSIGNED NOT NULL DEFAULT 0,
is_show BOOLEAN NOT NULL DEFAULT 1,
is_saleoff BOOLEAN NOT NULL DEFAULT 0
);
-- 向商品表中插入22条记录
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('R510VC 15.6英寸笔记本','笔记本','华硕','3399',DEFAULT,DEFAULT) ENGINE=InnoDB DEFAULT CHARSET=utf8;;
-- 省略
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('商务双肩背包','笔记本配件','索尼','99',DEFAULT,DEFAULT);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
![](http://static.blog.csdn.net/images/save_snippets.png)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 子查询必须嵌套在循环内部,其必须出现在圆括号内。
- 子查询可以包含多个关键字或者条件,如:DISTINCT、GROUP BY、ORDER BY、LIMIT,函数等。
- 子查询的外层查询可以是:SELECT 、INSERT、 UPDATE 、SET、 或者DO。【这里的查询并不是指查找,是所有SQL语句的统称】。
子查询的返回值:
子查询可以返回标量、一行、一列或者子查询。
使用比较运算符的子查询
= ,>,>= , < ,<=,<> !=,<=>,常用ANY
,SOME
,ALL
来修饰,语法:
operand comparison_operator subquery
operand comparision_operator ANY (subquery)
operand comparision_operator SOME (subquery)
operand comparision_operator ALL (subquery)
- 1
- 2
- 3
- 4
- 5
![](http://static.blog.csdn.net/images/save_snippets.png)
- 1
- 2
- 3
- 4
- 5
例:
mysql> SELECT AVG(goods_price) FROM tdb_goods; -- 查询所有商品价格的平均值
+------------------+
| AVG(goods_price) |
+------------------+
| 5636.3636364 |
+------------------+
1 row in set (0.00 sec)
mysql> SELECT ROUND(AVG(goods_price),2) FROM tdb_goods; -- 查询商品价格平均值四舍五入保留2位小数
+---------------------------+
| ROUND(AVG(goods_price),2) |
+---------------------------+
| 5636.36 |
+---------------------------+
1 row in set (0.00 sec)
mysql> SELECT goods_id,goods_name,goods_price FROM tdb_goods -- 查看商品价格大于等于平均价格的商品id,名称和价格
-> WHERE goods_price >= ( SELECT ROUND(AVG(goods_price),2) FROM tdb_goods ); -- 子查询
+----------+-----------------------------------------+-------------+
| goods_id | goods_name | goods_price |
+----------+-----------------------------------------+-------------+
| 3 | G150TH 15.6英寸游戏本 | 8499.000 |
| 7 | SVP13226SCB 13.3英寸触控超极本 | 7999.000 |
| 13 | iMac ME086CH/A 21.5英寸一体电脑 | 9188.000 |
| 17 | Mac Pro MD878CH/A 专业级台式电脑 | 28888.000 |
| 18 | HMZ-T3W 头戴显示设备 | 6999.000 |
| 20 | X3250 M4机架式服务器 2583i14 | 6888.000 |
| 21 | HMZ-T3W 头戴显示设备 | 6999.000 |
+----------+-----------------------------------------+-------------+
7 rows in set (0.00 sec)
mysql> SELECT goods_id,goods_price FROM tdb_goods -- 查询商品价格大于【所有】超极本的商品的id,价格
-> WHERE goods_price > ALL ( SELECT goods_price FROM tdb_goods WHERE goods_cate = '超级本' );
+----------+-------------+
| goods_id | goods_price |
+----------+-------------+
| 3 | 8499.000 |
| 13 | 9188.000 |
| 17 | 28888.000 |
+----------+-------------+
3 rows in set (0.00 sec)
mysql>
- 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
![](http://static.blog.csdn.net/images/save_snippets.png)
- 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
使用[NOT]IN的子查询
语法
operand comparison_operator [NOT] IN (subquery)
- 1
![](http://static.blog.csdn.net/images/save_snippets.png)
- 1
- = ANY 运算符与IN等效
- != ALL 或者<>ALL和NOT IN等效
使用[NOT]EXISTS的子查询
如果子查询返回任何行,EXISTS将返回true;否则返回false。
使用INSERT-SELECT插入记录和多表更新
-- 插入记录
INSERT [INTO] tb_name[(col_name, ... )] SELECT ...
-- 多表更新
UPDATE table_references SET col_name1 = {expr1|DEFAULT} [,col_name2 = {expr2|DEFAULT}] [WHERE where_condition]
- 1
- 2
- 3
- 4
- 5
![](http://static.blog.csdn.net/images/save_snippets.png)
- 1
- 2
- 3
- 4
- 5
例:
查看以上的tdb_goods表我们发现有很多的重复,例如:
为此,我们需要另外创建一张商品分类表tdb_goods_cates
。
-- 商品分类表
CREATE TABLE IF NOT EXISTS tdb_goods_cates(
cate_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
cate_name VARCHAR(40)
);
- 1
- 2
- 3
- 4
- 5
- 6
![](http://static.blog.csdn.net/images/save_snippets.png)
- 1
- 2
- 3
- 4
- 5
- 6
我们需要向上面的表中插入记录。
INSERT tdb_goods_cates(cate_name) -- 将分组信息写入商品分类表
SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;
- 1
- 2
![](http://static.blog.csdn.net/images/save_snippets.png)
- 1
- 2
表的参照关系——表的连接
table_reference {[INNER|CROSS] JOIN {LEFT|RIGHT} [OUTER] JOIN} table_reference ON condition_expr
- 1
![](http://static.blog.csdn.net/images/save_snippets.png)
- 1
- INNER JOIN,内连接。在MYSQL中,JOIN,CROSS JOIN和INNER JOIN等价。
- LEFT [OUTER] JOIN,左外连接。
- RIGHT [OUTER] JOIN,右外连接 。
UPDATE tdb_goods INNER JOIN tdb_goods_cates
ON goods_cate = cate_name
SET goods_cate = cate_id; -- 参照tdb_goods_cates表更新tdb_goods的记录
- 1
- 2
- 3
![](http://static.blog.csdn.net/images/save_snippets.png)
- 1
- 2
- 3
修改表
修改列(表结构的修改)
# 增加列
ALTER TABLE 表名 ADD COLUMN 列名 数据类型 约束;
或者
ALTER TABLE 表名 ADD 列名 数据类型 约束;
# 删除列
ALTER TABLE 表名 DROP COLUMN 列名; 或
ALTER TABLE 表名 DROP 列名; # 对原有的列进行修改
ALTER TABLE 表名字 CHANGE 原列名 新列名 数据类型 约束; # 修改某一列的数据类型
ALTER TABLE 表名字 MODIFY 列名字 新数据类型;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
![](http://static.blog.csdn.net/images/save_snippets.png)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
新增加的列默认是在最后,如果想要在第一列加入列,请参考以下的写法:
ALTER TABLE employee ADD test INT(10) DEFAULT 11 FIRST;
- 1
![](http://static.blog.csdn.net/images/save_snippets.png)
- 1
索引
索引是一种与表有关的结构,它的作用相当于书的目录,可以根据目录中的页码快速找到所需的内容。 当表中有大量记录时,若要对表进行查询,没有索引的情况是全表搜索:将所有记录一一取出,和查询条件进行一一对比,然后返回满足条件的记录。这样做会消耗大量数据库系统时间,并造成大量磁盘I/O操作。 而如果在表中已建立索引,在索引中找到符合查询条件的索引值,通过索引值就可以快速找到表中的数据,可以大大加快查询速度。索引的建立有以下的2种方式:
# 建立索引
ALTER TABLE 表名字 ADD INDEX 索引名 (列名);
或者
CREATE INDEX 索引名 ON 表名字 (列名);
# 查看索引
SHOW INDEX FROM 表名字
- 1
- 2
- 3
- 4
- 5
- 6
- 7
![](http://static.blog.csdn.net/images/save_snippets.png)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
例如:
ALTER TABLE employee ADD INDEX idx_id (id); #在employee表的id列上建立名为idx_id的索引
CREATE INDEX idx_name ON employee (name); #在employee表的name列上建立名为idx_name的索引
- 1
- 2
- 3
![](http://static.blog.csdn.net/images/save_snippets.png)
- 1
- 2
- 3
在执行SQL语句的时候会自动判断WHERE子句中是否有可用的索引。
视图
视图是从一个或多个表中导出来的表,是一种虚拟存在的表。它就像一个窗口,通过这个窗口可以看到系统专门提供的数据,这样,用户可以不用看到整个数据库中的数据,而只关心对自己有用的数据。
- 数据库中只存放了视图的定义,而没有存放视图中的数据,这些数据存放在原来的表中;
- 使用视图查询数据时,数据库系统会从原来的表中取出对应的数据;
- 视图中的数据依赖于原来表中的数据,一旦表中数据发生改变,显示在视图中的数据也会发生改变;
- 在使用视图的时候,可以把它当作一张表。
CREATE VIEW 视图名(列a,列b,列c) AS SELECT 列1,列2,列3 FROM 表名字;
- 1
![](http://static.blog.csdn.net/images/save_snippets.png)
- 1
可见创建视图的语句,后半句是一个SELECT查询语句,所以视图也可以建立在多张表上,只需在SELECT语句中使用子查询或连接查询。
例如:
mysql> CREATE VIEW v_emp(v_name,v_age,v_phone) AS SELECT name,age,phone FROM
e
Query OK, 0 rows affected (0.04 sec)
mysql> SELECT * FROM v_emp;
+--------+-------+---------+
| v_name | v_age | v_phone |
+--------+-------+---------+
| Tom | 26 | 119119 |
| Jack | 24 | 120120 |
| Jobs | NULL | 19283 |
| Tony | NULL | 102938 |
| Rose | 22 | 114114 |
+--------+-------+---------+
5 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
![](http://static.blog.csdn.net/images/save_snippets.png)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
导入
导入操作,可以把一个文件里的数据保存进一张表。导入语句格式为:
LOAD DATA INFILE '文件路径' INTO TABLE 表名字;
- 1
![](http://static.blog.csdn.net/images/save_snippets.png)
- 1
例如:这是in.txt
mysql> SELECT * FROM employee; -- 导入数据之前
+----+------+------+--------+--------+--------+
| id | name | age | salary | phone | in_dpt |
+----+------+------+--------+--------+--------+
| 1 | Tom | 26 | 2500 | 119119 | dpt4 |
| 2 | Jack | 24 | 2500 | 120120 | dpt2 |
| 3 | Jobs | NULL | 3600 | 19283 | dpt2 |
| 4 | Tony | NULL | 3400 | 102938 | dpt3 |
| 5 | Rose | 22 | 2800 | 114114 | dpt3 |
+----+------+------+--------+--------+--------+
5 rows in set (0.00 sec)
mysql> LOAD DATA INFILE '/tmp/SQL6/in.txt' INTO TABLE employee; -- 导入数据
Query OK, 7 rows affected (0.02 sec)
Records: 7 Deleted: 0 Skipped: 0 Warnings: 0
mysql> SELECT * FROM employee; -- 导入数据
+----+------+------+--------+--------+--------+
| id | name | age | salary | phone | in_dpt |
+----+------+------+--------+--------+--------+
| 1 | Tom | 26 | 2500 | 119119 | dpt4 |
| 2 | Jack | 24 | 2500 | 120120 | dpt2 |
| 3 | Jobs | NULL | 3600 | 19283 | dpt2 |
| 4 | Tony | NULL | 3400 | 102938 | dpt3 |
| 5 | Rose | 22 | 2800 | 114114 | dpt3 |
| 6 | Alex | 26 | 3000 | 123456 | dpt1 |
| 7 | Ken | 27 | 3500 | 654321 | dpt1 |
| 8 | Rick | 24 | 3500 | 987654 | dpt3 |
| 9 | Joe | 31 | 3600 | 100129 | dpt2 |
| 10 | Mike | 23 | 3400 | 110110 | dpt1 |
| 11 | Jim | 35 | 3000 | 100861 | dpt4 |
| 12 | Mary | 21 | 3000 | 100101 | dpt2 |
+----+------+------+--------+--------+--------+
12 rows in set (0.00 sec)
- 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
![](http://static.blog.csdn.net/images/save_snippets.png)
- 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
导出
导出与导入是相反的过程,是把数据库某个表中的数据保存到一个文件之中。导出语句基本格式为:
SELECT 列1,列2 INTO OUTFILE '文件路径和文件名' FROM 表名字;
- 1
![](http://static.blog.csdn.net/images/save_snippets.png)
- 1
例:把整个employee表的数据导出到 /tmp 目录下,导出文件命名为 out.txt 具体语句为:
SELECT * INTO OUTFILE '/tmp/out.txt' FROM employee;
- 1
![](http://static.blog.csdn.net/images/save_snippets.png)
- 1
备份
备份与导出的区别:导出的文件只是保存数据库中的数据;而备份,则是把数据库的结构,包括数据、约束、索引、视图等全部另存为一个文件。
mysqldump是MySQL用于备份数据库的实用程序。它主要产生一个SQL脚本文件,其中包含从头重新创建数据库所必需的命令CREATE TABLE INSERT等。
mysqldump -u root 数据库名>备份文件名; #备份整个数据库
mysqldump -u root 数据库名 表名字>备份文件名; #备份整个表
- 1
- 2
- 3
![](http://static.blog.csdn.net/images/save_snippets.png)
- 1
- 2
- 3
例如:备份数据库mysql_shiyan:mysqldump -u root mysql_shiyan > bak.sql;
恢复
我们可以使用source 脚本名
的方式恢复数据库,也可以向数据库重定向备份的脚本。