MySQL学习笔记

来源：互联网发布：linux怎么查看log文件编辑：程序博客网时间：2024/06/05 15:19

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

登录与退出MySQL

root@ubuntu:/etc/mysql# mysql -v    # 查看mysql版本       mysql  Ver 14.14 Distrib 5.5.35, for debian-linux-gnu (i686) using readline 6.2root@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 330Server version: 5.5.35-0ubuntu0.12.04.2 (Ubuntu)mysql> \q|exit|quit # 退出mysql

修改mysql提示符

连接mysql时通过--prompt参数指定
连接上客户端的时候通过prompt命令指定

MySQL提示符如下表：

参数描述 \D 完整日期 \d 当前数据库 \h 服务器名 \u 当前用户

以上的提示符还能连用，例如：

mysql> prompt \u@\h \d> # 用户名@主机名 数据库名PROMPT set to '\u@\h \d>'root@localhost demo>

MySQL常用命令

SET NAMES gbk; -- 客户端以GBK的编码形式呈现数据

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清屏

MySQL语句规范

关键字和函数名称全部大写；
数据库名、表名、字段名称全部小写；
SQL语句必须以分号结束。

操作数据库

创建数据库

-- 说明：花括号表示必选项，竖线表示多选一，方括号表示可选CREATE {DATABASE|SCHEMA} [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] character_name

mysql> mysql> CREATE DATABASE t1;Query OK, 1 row affected (0.00 sec)mysql>

我们可以使用source命令运行sql脚本。登录mysql数据库。

source 数据库脚本名

查看当前服务器中的数据库列表

SHOW {DATABASES | SCHEMAS} [LIKE 'pattern' | WHERE expr]

正确安装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>

修改数据库

ALTER {DATABASE | SCHEMA} [db_name] [DEFAULT] CHARACTER SET [=] character_name

这里写图片描述

删除数据库

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

数据类型和操作数据表

数据类型决定了数据的存储格式，代表了不同的信息类型。

1.整型
这里写图片描述
2.浮点型

项目开发中以float居多
3.日期型

项目开发中该类型用得并不多，因为涉及到时区的问题，多用数字表示。
4.字符型

VARCHAR,TEXT,DATE,TIME,ENUM等类型的数据也需要单引号修饰，而INT,FLOAT,DOUBLE等则不需要。
使用某个数据库

mysql> USE test;            # 使用数据库Database changedmysql> SELECT DATABASE();   #查看当前使用的数据库+------------+| DATABASE() |+------------+| test       |+------------+1 row in set (0.00 sec)mysql>

创建表

CREATE TABLE [IF NOT EXISTS] table_name(    column_name data_type,    ……)

创建用户表tb_user

CREATE TABLE tb_user(    username VARCHAR(20),       -- 【用户名】    age TINYINT UNSIGNED,       -- 【年龄】     无符号微整数（0~255）    salary FLOAT(8,2) UNSIGNED  -- 【工资】     一共有8位，小数部分有2位);

查看表

SHOW TABLES [FROM da_name] [LIKE 'pattern'|WHERE expr]

例如：

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>

查看表结构

SHOW COLUMNS FROM tb_name # 或者DESC tb_name

例如：
这里写图片描述
重命名表

RENAME TABLE 原名 TO 新名;ALTER TABLE 原名 RENAME 新名;ALTER TABLE 原名 RENAME TO 新名;

插入记录

INSERT [INTO] tb_name [(col_name,...)] VALUES(val,...)

例如：

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 1mysql> mysql> INSERT tb_user(username,age) VALUES('Kitty',18); # 为指定字段赋值Query OK, 1 row affected (0.03 sec)

记录的查询

SELECT expr,... FROM tb_name

例如：

mysql> SELECT * FROM tb_user; # 这里的*表示的是字段的过滤+----------+------+---------+| username | age  | salary  |+----------+------+---------+| Tom      |   22 | 3658.32 || Kitty    |   18 |    NULL |+----------+------+---------+2 rows in set (0.00 sec)

空值与非空
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>

这里写图片描述
默认约束-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');              # 没有指定性别时，默认是3Query 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)

约束和修改表

外键约束和唯一约束

约束保证了数据的完整性和一致性。
约束分为表级约束和列级约束。
约束类型包括
- 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)# 向父表（省份表）中插入记录mysql> INSERT provinces(pname) VALUES('A');Query OK, 1 row affected (0.01 sec)mysql> INSERT provinces(pname) VALUES('B');Query OK, 1 row affected (0.00 sec)mysql> INSERT provinces(pname) VALUES('C');Query OK, 1 row affected (0.00 sec)mysql> SELECT * FROM provinces;+----+-------+| id | pname |+----+-------+|  1 | A     ||  2 | B     ||  3 | C     |+----+-------+3 rows in set (0.07 sec)mysql>

这里写图片描述
删除父表（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,...)

这里写图片描述

删除列

-- 删除列 ALTER TABLE tb_name DROP [COLUMN] col_name

这里写图片描述

删除列的同时可以新增列，同理新增列的同时可以删除列，操作之间以逗号分隔。

添加约束

-- 添加主键约束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}

例如：为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

例：
删除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]

例：
将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]...

例：
将users表更名为user：
这里写图片描述

在实际的开发中应该尽量避免数据列和表的更名——可能导致某些视图或者存储过程无法工作！

记录的操作

插入记录——INSERT

-- 如果省略了列名，表示所有的字段都要赋值；插入语句可以一次性插入多条记录INSERT [INTO] tb_name [(col_name,...)] {VALUES|VALUE} ({expr|DEFAULT},...),(...),...

首先创建一张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);

向user表中插入记录：

INSERT user VALUES(NULL,'A','111',11,1);            -- id字段为了保持自增可以使用NULL或者DEFAULTINSERT 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)

INSERT SET-SELECT

-- INSERT SET方式。可以使用子查询(SubQuery)，只能一次插入一条记录；用得较少INSERT [INTO] tb_name SET col_name={expr|DEFAULT},...-- INSERT SELECT方式（将查询结果写入到表）INSERT [INTO] tb_name [(col_name,...)] SELECT ...

使用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.建立一张test表：

CREATE TABLE test(    id TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,     username VARCHAR(20));

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 |+----+----------+----------------------------------+-----+------+

现在需要将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: 0mysql> SELECT * FROM test;+----+----------+| id | username |+----+----------+|  1 | A        ||  2 | D        ||  3 | E        ||  4 | G        |+----+----------+4 rows in set (0.00 sec)

单表记录更新——UPDATE

-- 如果省略了WHERE条件所有的记录将全部更新！UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1={expr1|DEFAULT} [,col_name2={expr2|DEFAULT}] ... [WHERE where_condition]

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;                      -- 让表中的年龄字段都在各自的基础上加5Query OK, 8 rows affected (0.11 sec)Rows matched: 8  Changed: 8  Warnings: 0SELECT * 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，性别都变成0Query OK, 8 rows affected (0.00 sec)Rows matched: 8  Changed: 8  Warnings: 0SELECT * 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: 0SELECT * 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)

单表删除记录

-- 注意：如果省略WHERE条件将删除数据表中的全部记录！DELETE FROM tb_name [WHERE where_condition]

例：

DELETE FROM user WHERE id = 2;  -- 删除id为2的记录，删除之后该id不会重用DELETE FROM user;               -- 删除全表记录

查询表达式解析

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}]]

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>

查询表达式

每一个表达式表示想要的一列，至少要有一个。
多个列之间以逗号分隔。
星号（*）表示所有列。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)

使用WHERE语句进行条件查询

WHERE语句的作用是对记录进行过滤，如果没有指定WHERE子句，则显示所有记录。
在WHERE表达式中，可以使用MySQL支持的函数或者运算符。

使用GROUP BY对结果集进行分组

[GROUP BY {col_name | position} [ASC|DESC], ... ]

例：

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)

使用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)

使用ORDER BY对结果集进行排序

[ORDER BY {col_name | expr | position} [ASC | DESC], ... ]

例：

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)

使用LIMIT语句限制结果集的数量

-- OFFSET常用于PHP中的分页（当前页面-1）* 每页显示的记录数[LIMIT {[offset,] row_count | row_count OFFSET offset}]

子查询与连接

数据准备

--  创建商品表  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);

子查询必须嵌套在循环内部，其必须出现在圆括号内。
子查询可以包含多个关键字或者条件，如：DISTINCT、GROUP BY、ORDER BY、LIMIT，函数等。
子查询的外层查询可以是：SELECT 、INSERT、 UPDATE 、SET、或者DO。【这里的查询并不是指查找，是所有SQL语句的统称】。

子查询的返回值：
子查询可以返回标量、一行、一列或者子查询。

使用比较运算符的子查询

= ,>,>= , < ,<=,<> !=,<=>，常用ANY,SOME,ALL来修饰，语法：

operand comparison_operator subqueryoperand comparision_operator ANY (subquery)operand comparision_operator SOME (subquery)operand comparision_operator ALL (subquery)

这里写图片描述
例：

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>

使用[NOT]IN的子查询

语法

operand comparison_operator [NOT] IN (subquery)

= 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]

例：
查看以上的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));

我们需要向上面的表中插入记录。

INSERT tdb_goods_cates(cate_name)  -- 将分组信息写入商品分类表SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;

表的参照关系——表的连接

table_reference {[INNER|CROSS] JOIN {LEFT|RIGHT} [OUTER] JOIN} table_reference ON condition_expr

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的记录

修改表

修改列（表结构的修改）

# 增加列ALTER TABLE 表名 ADD COLUMN 列名 数据类型 约束;或者ALTER TABLE 表名 ADD 列名 数据类型 约束;# 删除列ALTER TABLE 表名 DROP COLUMN 列名;或ALTER TABLE 表名 DROP 列名;# 对原有的列进行修改ALTER TABLE 表名字 CHANGE 原列名 新列名 数据类型 约束;# 修改某一列的数据类型ALTER TABLE 表名字 MODIFY 列名字 新数据类型;

新增加的列默认是在最后，如果想要在第一列加入列，请参考以下的写法：

ALTER TABLE employee ADD test INT(10) DEFAULT 11 FIRST;

索引

索引是一种与表有关的结构，它的作用相当于书的目录，可以根据目录中的页码快速找到所需的内容。当表中有大量记录时，若要对表进行查询，没有索引的情况是全表搜索：将所有记录一一取出，和查询条件进行一一对比，然后返回满足条件的记录。这样做会消耗大量数据库系统时间，并造成大量磁盘I/O操作。而如果在表中已建立索引，在索引中找到符合查询条件的索引值，通过索引值就可以快速找到表中的数据，可以大大加快查询速度。索引的建立有以下的2种方式：

# 建立索引ALTER TABLE 表名字 ADD INDEX 索引名 (列名);或者CREATE INDEX 索引名 ON 表名字 (列名);# 查看索引SHOW INDEX FROM 表名字

例如：

ALTER TABLE employee ADD INDEX idx_id (id);  #在employee表的id列上建立名为idx_id的索引CREATE INDEX idx_name ON employee (name);   #在employee表的name列上建立名为idx_name的索引

在执行SQL语句的时候会自动判断WHERE子句中是否有可用的索引。

视图

视图是从一个或多个表中导出来的表，是一种虚拟存在的表。它就像一个窗口，通过这个窗口可以看到系统专门提供的数据，这样，用户可以不用看到整个数据库中的数据，而只关心对自己有用的数据。

数据库中只存放了视图的定义，而没有存放视图中的数据，这些数据存放在原来的表中；

使用视图查询数据时，数据库系统会从原来的表中取出对应的数据；

视图中的数据依赖于原来表中的数据，一旦表中数据发生改变，显示在视图中的数据也会发生改变；

在使用视图的时候，可以把它当作一张表。

CREATE VIEW 视图名(列a,列b,列c) AS SELECT 列1,列2,列3 FROM 表名字;

可见创建视图的语句，后半句是一个SELECT查询语句，所以视图也可以建立在多张表上，只需在SELECT语句中使用子查询或连接查询。
例如：

mysql> CREATE VIEW v_emp(v_name,v_age,v_phone) AS SELECT name,age,phone FROM eQuery 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)

导入

导入操作，可以把一个文件里的数据保存进一张表。导入语句格式为：

LOAD DATA INFILE '文件路径' INTO TABLE 表名字;

例如：这是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: 0mysql> 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)

导出

导出与导入是相反的过程，是把数据库某个表中的数据保存到一个文件之中。导出语句基本格式为：

SELECT 列1，列2 INTO OUTFILE '文件路径和文件名' FROM 表名字;

例：把整个employee表的数据导出到 /tmp 目录下，导出文件命名为 out.txt 具体语句为：

SELECT * INTO OUTFILE '/tmp/out.txt' FROM employee;

这里写图片描述

备份

备份与导出的区别：导出的文件只是保存数据库中的数据；而备份，则是把数据库的结构，包括数据、约束、索引、视图等全部另存为一个文件。
mysqldump是MySQL用于备份数据库的实用程序。它主要产生一个SQL脚本文件，其中包含从头重新创建数据库所必需的命令CREATE TABLE INSERT等。

mysqldump -u root 数据库名>备份文件名;   #备份整个数据库mysqldump -u root 数据库名 表名字>备份文件名;  #备份整个表

例如：备份数据库mysql_shiyan：mysqldump -u root mysql_shiyan > bak.sql;

恢复

我们可以使用source 脚本名的方式恢复数据库，也可以向数据库重定向备份的脚本。
这里写图片描述

0 0