MySQL(慕课网笔记)

来源：互联网发布：网络人头像编辑：程序博客网时间：2024/06/03 20:01

MySQL

开源的关系型数据库管理系统

第一章安装

MSI安装
在官网下载的网址
下载之后一路next安装。安装路径默认到了c:\Program Files\MySQL，这时MySQL Server 5.7\bin目录下没有data文件夹。
在环境变量的path中配置一下mysql的环境（例如我的C:\Program Files\MySQL\MySQL Server 5.7\bin;）。
接着需要修改下mysql的配置文件my-default

# basedir = C:\Program Files\MySQL\MySQL Server 5.7# datadir = C:\Program Files\MySQL\MySQL Server 5.7\data

下一步cmd里配置。
以管理员身份运行cmd。进入bin文件夹后：
1. 运行mysqld –initialize 命令，这时mysql Server5.7下会自动生成一个data文件夹。
2. 输入mysqld -install
3. net start mysql启动mysql服务。如果下载MySQL5.7版本的，在windows服务上Mysql的名字默认是MySQL57，因此在cmd运行 net start/stop mysql 是无效的，必须改成 net start/stop mysql57才行。
下一次想要开启，直接以管理员身份进入再输入第三步就行。

MySQL登录

登录：
–本地连接
mysql –uroot -p
–远程连接
mysql –uwgb –hXXX.XXX.XXX.XXX –p
可以输入mysql参数
退出：mysql > exit;
mysql > quit;
mysql > \q;
修改MySQL提示符：
提示符修改为了localhost

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

常用命令：
显示服务器版本
SELECT VERSION();
当前日期时间
SELECT NOW();
当前用户
SELECT USER();
SET NAMES gbk;//只影响客户端的显示数据，并不影响真实的数据表中的数据

语句规范：
关键字与函数名称全部大写
数据库名称、表名称、字段名称全部小写
SQL语句必须以分号结尾

操作数据库

创建数据库
CREATE {DATDABASE | SCHEMA} [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset_name
查看当前服务器下的数据表列表
SHOW {DATABASE | SCHEMA} [LIKE ‘pattern’ | WHERE expr]
修改数据库
ALTER {DATABASE | SCHEMA} [db_name] [DEFAULT] CHARACTER SET [=] charset_name
删除数据库
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

第2章数据类型与操作数据表

是指列、存储过程参数、表达式和局部变量的数据特征，它决定了数据的存储形式。

数据表操作

数据表是数据库的组成部分，是其他对象的基础。

打开数据库
USE数据库名称
创建数据表
CREATE TABLE [IF NOT EXISTS] table_name(
column_name data_type,
…
)
查看数据表
SHOW TABLES [FROM db_name] [LIKE ‘pattern’ | WHERE expr]
查看数据表结构
SHOW COLUMNS FROM tbl_name
插入记录INSERT
INSERT [INTO] tbl_name [(col_name,…)] VALUES(val,…)
所有字段都要赋值
- 查找记录SELECT
  SELECT expr,… FROM tb1_name
  记录的空值与非空：
  NULL，字段值可以为空
  NOT NULL，字段值禁止为空
  AUTO_INCREMENT
  自动编号，且必须与主键组合使用，默认起始为1（整数或小数为0的浮点）
  PRIMARY KEY
  每张数据表只能存在一个主键，保证记录的唯一性，主键自动为NOT NULL
  UNIQUE KEY
  唯一约束，保证记录的唯一性。字段可以为空值NULL，每张数据表可以存在多个唯一约束
  DEFAULT
  插入记录时，如果没有明确为字段赋值，则自动赋予默认值。

第3章约束和修改数据表

约束保证数据的完整性和一致性
约束分为表级约束和列级约束
约束类型：
NOT NULL
PRIMARY KEY
UNIQUE KEY
DEFAULT
FOREIGN KEY
保持数据一致性、完整性；实现一对一或一对多关系
外键约束的要求：
父表和子表必需使用相同的存储引擎，而且禁止使用临时表。（子表参照的表是父表）
数据表的存储引擎只能为InnoDB
外键列和参照列必须具有相似的数据类型。其中数字的长度或是否有符号位必须相同；而字符的长度则可以不同。
外键列和参照列（例子中的id）必须创建索引。如果参照列不存在索引，Mysql将自动创建索引。（外键列不存在，不会自动。。）
编辑数据表的默认存储引擎
Mysql配置文件
default-storage-engine=INNODB

省份ID是外键

mysql> CREATE TABLE pro(    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,    -> pname VARCHAR(20) NOT NULL);Query OK, 0 rows affected (0.16 sec)mysql> CREATE TABLE users(    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,    -> username VARCHAR(10) NOT NULL,    -> pid SMALLINT UNSIGNED,    -> FOREIGN KEY(pid) REFERENCES pro(id)    -> );

查看索引：
SHOW INDEXES FROM pro;
SHOW INDEXES FROM pro \G;网格形式
外键约束的参照操作
1. CASCADE:从父表删除或更新且自动删除或更新子表中匹配的行
2. SET NULL:从父表删除或更新行，并设置子表中的外键列为NULL。如果使用该选项，必须保证子表列没有指定NOT NULL
3. RESTRICT：拒绝对父表的删除或更新操作
4. NO ACTION标准SQL的关键字，在mysql中与RESTRICT一样
必须现在父表中插入记录，再子表。

mysql> CREATE TABLE users1(    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,    -> username VARCHAR(10) NOT NULL,    -> pid SMALLINT UNSIGNED,    -> FOREIGN KEY(pid) REFERENCES pro(id) ON DELETE CASCADE    -> );mysql> INSERT pro (pname )VALUES('A');Query OK, 1 row affected (0.03 sec)mysql> INSERT pro (pname )VALUES('B');Query OK, 1 row affected (0.02 sec)mysql> INSERT pro (pname )VALUES('C');Query OK, 1 row affected (0.02 sec)mysql> SELECT * FROM pro;+----+-------+| id | pname |+----+-------+|  1 | A     ||  2 | B     ||  3 | C     |mysql> INSERT users1 (username,pid )VALUES('tom', 3);Query OK, 1 row affected (0.02 sec)mysql> INSERT users1 (username,pid )VALUES('jih', 7);ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`users1`, CONSTRAINT `users1_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `pro` (`id`) ON DELETE CASCADE)mysql> INSERT users1 (username,pid )VALUES('tyom',1);Query OK, 1 row affected (0.03 sec)mysql> INSERT users1 (username,pid )VALUES('rosm',3);Query OK, 1 row affected (0.02 sec)mysql> SELECT * FROM users1;+----+----------+------+| id | username | pid  |+----+----------+------+|  1 | tom      |    3 ||  3 | tyom     |    1 ||  4 | rosm     |    3 |+----+----------+------+3 rows in set (0.00 sec)mysql> DELETE FROM pro WHERE id = 3;Query OK, 1 row affected (0.10 sec)mysql> SELECT * FROM pro;+----+-------+| id | pname |+----+-------+|  1 | A     ||  2 | B     |+----+-------+2 rows in set (0.00 sec)mysql> SELECT * FROM users1;+----+----------+------+| id | username | pid  |+----+----------+------+|  3 | tyom     |    1 |+----+----------+------+1 row in set (0.00 sec)

对一个数据列建立的约束，列级约束（列定义时和列定义后声明）
多个数据列建立的约束，是表级约束，只能列定义后声明。

修改数据表

添加、删除字段
添加单列
ALTER TABLE tb1_name ADD [COLUMN] col_name column_definition [FIRST|AFTER col_name]
添加多列
ALTER TABLE tb1_name ADD [COLUMN] (col_name column_definition,…)
删除列：
ALTER TABLE tb1_name DROP [COLUMN] (col_name column_definition,…)

mysql> ALTER TABLE users1 ADD passqord  VARCHAR(32) NOT NULL AFTER username;Query OK, 0 rows affected (0.37 sec)mysql> SHOW COLUMNS FROM users1;+----------+----------------------+------+-----+---------+----------------+| Field    | Type                 | Null | Key | Default | Extra          |+----------+----------------------+------+-----+---------+----------------+| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment || username | varchar(10)          | NO   |     | NULL    |                || passqord | varchar(32)          | NO   |     | NULL    |                || pid      | smallint(5) unsigned | YES  | MUL | NULL    |                || age      | tinyint(3) unsigned  | NO   |     | 10      |                |+----------+----------------------+------+-----+---------+----------------+

添加主键约束（只能1个）
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name)
添加唯一约束
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [index_type] (index_col_name,..)
添加/删除默认约束
ALTER TABLE tbl_name ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEAFULT}

mysql> SHOW COLUMNs FROM users2;+----------+----------------------+------+-----+---------+-------+| Field    | Type                 | Null | Key | Default | Extra |+----------+----------------------+------+-----+---------+-------+| username | varchar(10)          | NO   |     | NULL    |       || pid      | smallint(5) unsigned | YES  |     | NULL    |       || id       | smallint(5) unsigned | YES  |     | NULL    |       |+----------+----------------------+------+-----+---------+-------+3 rows in set (0.00 sec)mysql> ALTER TABLE users2 ADD CONSTRAINT PK_users2_id PRIMARY KEY(id);mysql> SHOW COLUMNs FROM users2;+----------+----------------------+------+-----+---------+-------+| Field    | Type                 | Null | Key | Default | Extra |+----------+----------------------+------+-----+---------+-------+| username | varchar(10)          | NO   |     | NULL    |       || pid      | smallint(5) unsigned | YES  |     | NULL    |       || id       | smallint(5) unsigned | NO   | PRI | NULL    |       |+----------+----------------------+------+-----+---------+-------+mysql> ALTER TABLE users2 ADD UNIQUE (username);mysql> show create table users2;+--------+-----------------------------------------------------------------------------------------------------------------+| Table  | Create Table                                                                                       |+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+| users2 | CREATE TABLE `users2` (  `username` varchar(10) NOT NULL,  `pid` smallint(5) unsigned DEFAULT NULL,  `id` smallint(5) unsigned NOT NULL,  PRIMARY KEY (`id`),  UNIQUE KEY `username` (`username`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 |+--------+-----------------------------------------------------------------+1 row in set (0.00 sec)

mysql> ALTER TABLE users2 ALTER age SET DEFAULT 15;Query OK, 0 rows affected (0.02 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> SHOW COLUMNS FROM users2;+----------+----------------------+------+-----+---------+-------+| Field    | Type                 | Null | Key | Default | Extra |+----------+----------------------+------+-----+---------+-------+| username | varchar(10)          | NO   | UNI | NULL    |       || pid      | smallint(5) unsigned | YES  | MUL | NULL    |       || id       | smallint(5) unsigned | NO   | PRI | NULL    |       || age      | tinyint(3) unsigned  | NO   |     | 15      |       |+----------+----------------------+------+-----+---------+-------+

删除主键约束
ALTER TABLE users2 DROP PRIMARY KEY;
删除唯一约束
ALTER TABLE users2 DROP {INDEX|KEY} index_name;

mysql> SHOW INDEXEs FrOM users2 \G;*************************** 1. row ***************************        Table: users2   Non_unique: 0     Key_name: PRIMARY Seq_in_index: 1  Column_name: id    Collation: A  Cardinality: 0     Sub_part: NULL       Packed: NULL         Null:   Index_type: BTREE      Comment:Index_comment:*************************** 2. row ***************************        Table: users2   Non_unique: 1     Key_name: pid Seq_in_index: 1  Column_name: pid    Collation: A  Cardinality: 0     Sub_part: NULL       Packed: NULL         Null: YES   Index_type: BTREE      Comment:Index_comment:

删除外键约束

显示当前约束：

mysql> show create table users2;+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table  | Create Table                                             |+--------+----------------------------------------------------------------------------------------------------------------+| users2 | CREATE TABLE `users2` (  `username` varchar(10) NOT NULL,  `pid` smallint(5) unsigned DEFAULT NULL,  `id` smallint(5) unsigned NOT NULL,  `age` tinyint(3) unsigned NOT NULL DEFAULT '15',  PRIMARY KEY (`id`),  KEY `pid` (`pid`),  CONSTRAINT `users2_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `pro` (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 |+--------+---------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)mysql> ALTER TABLE users2 DROP FOREIGN KEY users2_ibfk_1;Query OK, 0 rows affected (0.07 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> show create table users2;+--------+------------------------------------------------------------------------------------------------------------------------------------------+| Table  | Create Table                         +--------+--------------------------------------| users2 | CREATE TABLE `users2` (  `username` varchar(10) NOT NULL,  `pid` smallint(5) unsigned DEFAULT NULL,  `id` smallint(5) unsigned NOT NULL,  `age` tinyint(3) unsigned NOT NULL DEFAULT '15',  PRIMARY KEY (`id`),  KEY `pid` (`pid`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 |+--------+-------------------------------------------------------------------------------------+1 row in set (0.00 sec)

再删除pid上的索引：
mysql> ALTER TABLE users2 DROP index pid;

| users2 | CREATE TABLE `users2` (  `username` varchar(10) NOT NULL,  `pid` smallint(5) unsigned DEFAULT NULL,  `id` smallint(5) unsigned NOT NULL,  `age` tinyint(3) unsigned NOT NULL DEFAULT '15',  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

修改列定义
让id字段放在最前面：
ALTER TABLE users2 MODIFY id SMALLINT UNSIGNED NOT NULL FIRST;
修改数据类型
ALTER TABLE users2 MODIFY id TINYINT UNSIGNED NOT NULL ;
修改列名称
ALTER TABLE users2 CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER] col_name
同时修改类型、名称
ALTER TABLE users2 CHANGE pid p_id TINYINT UNSIGNED NOT NULL;
![][18]
总结：
![][19]

第4章操作数据表中的记录

1. 插入

第一种：

mysql> CREATE TABLE users4(    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,    -> username VARCHAR(20) not null,    -> age TINYINT UNSIGNED NOT NULL DEFAULT 10,    -> sex boolean);Query OK, 0 rows affected (0.17 sec)mysql> INSERT users4 VALUES(NULL, 'johnm','789', 25,1 );Query OK, 1 row affected (0.03 sec)mysql> SELECT * FROM users4;+----+----------+----------+-----+------+| id | username | password | age | sex  |+----+----------+----------+-----+------+|  1 | tim      |          |  25 |    1 ||  2 | johnm    | 789      |  25 |    1 |+----+----------+----------+-----+------+

将主键设置为NULL，自动升序
或：
INSERT users4 VALUES(default, ‘scyda’,’789’, 25,1 );
INSERT users4 VALUES(default, ‘scyda’,’789’, 5 * 5,1 );
如果字段为DEFAULT，可以不赋值，直接以DEFAULT代替
INSERT users4 VALUES(NULL, ‘Rose’, md5(‘123’),DEFAULT, 0);md5为123的哈希值
第二种：
与第一种区别是可以使用子查询（subQuery）
INSERT [INTO] tbl_name SET col_name ={expr | DEFAULT},…
mysql> INSERT users4 SET username =’Ben’,password=’456’;
Query OK, 1 row affected (0.05 sec)
第三种：
INSERT [INTO] tbl_name [(col_name,…)] SELECT …
此方法可以将查询结果插入到指定数据表

2. 更新记录

mysql> UPDATE users4 SET age = age + 5;所有年龄加上5

mysql> UPDATE users4 SET age = age - id, sex = 0 WHERE id % 2 = 0;Query OK, 2 rows affected (0.04 sec)Rows matched: 2  Changed: 2  Warnings: 0mysql> SELECT * FROM users4;+----+----------+----------+-----+------+| id | username | password | age | sex  |+----+----------+----------+-----+------+|  1 | tim      |          |  30 |    1 ||  2 | johnm    | 789      |  28 |    0 ||  3 | scyda    | 789      |  30 |    1 ||  6 | tom      | 123      |  24 |    0 ||  7 | Ben      | 456      |  15 | NULL |+----+----------+----------+-----+------+

3. 删除记录

DELETE FROM tbl_name [WHERE where_condition]

mysql> DELETE FROM users4 WHERE id = 7;Query OK, 1 row affected (0.01 sec)mysql> SELECT * FROM users4;+----+----------+----------+-----+------+| id | username | password | age | sex  |+----+----------+----------+-----+------+|  1 | tim      |          |  30 |    1 ||  2 | johnm    | 789      |  28 |    0 ||  3 | scyda    | 789      |  30 |    1 ||  6 | tom      | 123      |  24 |    0 |+----+----------+----------+-----+------+

删掉了id7，此时再插入，新id为8。

4. SELECT

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} [ADC|DESC],…]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
]
查询表达式：
每一个表达式表示想要的一列，必须有至少一个。
多个列之间以英文逗号分；是所有列，tbl_name.命名表的所有列；可使用[AS]ailas_name为其赋予别名；别名可用于GROUP BY,ORDER BY或HAVING子句。

mysql> SELECT 3 + 5;+-------+| 3 + 5 |+-------+|     8 |+-------+mysql> SELECT id,username FROM users4;//id和username顺序不要求+----+----------+| id | username |+----+----------+|  1 | tim      ||  2 | johnm    ||  3 | scyda    ||  6 | tom      ||  8 | 111      |+----+----------+mysql> SELECT users4.* FROM users4;mysql> SELECT id AS userId,username AS uname FROM users4;+--------+-------+| userId | uname |+--------+-------+|      1 | tim   ||      2 | johnm ||      3 | scyda ||      6 | tom   ||      8 | 111   |+--------+-------+mysql> SELECT id username FROM users4;//别名+----------+| username |+----------+|        1 ||        2 ||        3 ||        6 ||        8 |+----------+

5. WHERE
未指定WHERE子句，则显示所有记录；在WHERE表达式中，可以使用Mysql支持的函数或运算符。
6. GROUP BY
[GROUP BY {col_name |position} [ASC|DESC],..]

mysql> SELECT sex FROM users4 GROUP BY sex;+------+| sex  |+------+| NULL ||    0 ||    1 |mysql> SELECT sex FROM users4 GROUP BY 1;

7. HAVING
分组条件

mysql> SELECT sex FROM users4 GROUP BY 1 HAVING count(id) >= 2;+------+| sex  |+------+|    0 ||    1 |

8. ORDER BY
对查询结果进行排序

mysql> SELECT * FROM users4 ORDER BY id DESC;+----+----------+----------+-----+------+| id | username | password | age | sex  |+----+----------+----------+-----+------+|  8 | 111      | 22       |  22 | NULL ||  6 | tom      | 123      |  24 |    0 ||  3 | scyda    | 789      |  30 |    1 ||  2 | johnm    | 789      |  28 |    0 ||  1 | tim      |          |  30 |    1 |+----+----------+----------+-----+------+// 先按照年龄排，若年龄相同，id降序排mysql> SELECT * FROM users4 ORDER BY age;+----+----------+----------+-----+------+| id | username | password | age | sex  |+----+----------+----------+-----+------+|  8 | 111      | 22       |  22 | NULL ||  6 | tom      | 123      |  24 |    0 ||  2 | johnm    | 789      |  28 |    0 ||  1 | tim      |          |  30 |    1 ||  3 | scyda    | 789      |  30 |    1 |+----+----------+----------+-----+------+mysql> SELECT * FROM users4 ORDER BY age,id DESC;+----+----------+----------+-----+------+| id | username | password | age | sex  |+----+----------+----------+-----+------+|  8 | 111      | 22       |  22 | NULL ||  6 | tom      | 123      |  24 |    0 ||  2 | johnm    | 789      |  28 |    0 ||  3 | scyda    | 789      |  30 |    1 ||  1 | tim      |          |  30 |    1 |+----+----------+----------+-----+------+

8. LIMIT
限制查询结果返回的数量
[LIMIT {[offset,] row_count | row_count OFFSET offset}]

mysql> SELECT * FROM users4;+----+----------+----------+-----+------+| id | username | password | age | sex  |+----+----------+----------+-----+------+|  1 | tim      |          |  30 |    1 ||  2 | johnm    | 789      |  28 |    0 ||  3 | scyda    | 789      |  30 |    1 ||  6 | tom      | 123      |  24 |    0 ||  8 | 111      | 22       |  22 | NULL |+----+----------+----------+-----+------+mysql> SELECT * FROM users4 LIMIT 2;+----+----------+----------+-----+------+| id | username | password | age | sex  |+----+----------+----------+-----+------+|  1 | tim      |          |  30 |    1 ||  2 | johnm    | 789      |  28 |    0 |+----+----------+----------+-----+------+mysql> SELECT * FROM users4 LIMIT 3,2; // 偏移量+----+----------+----------+-----+------+| id | username | password | age | sex  |+----+----------+----------+-----+------+|  6 | tom      | 123      |  24 |    0 ||  8 | 111      | 22       |  22 | NULL |+----+----------+----------+-----+------+mysql> SELECT * FROM users4 ORDER BY id DESC LIMIT 2,2;+----+----------+----------+-----+------+| id | username | password | age | sex  |+----+----------+----------+-----+------+|  3 | scyda    | 789      |  30 |    1 ||  2 | johnm    | 789      |  28 |    0 |+----+----------+----------+-----+------+

mysql> CREATE TABLE test (    -> id TINYINT UNSIGNED PRIMARY KEY  AUTO_INCREMENT,    -> username VARCHAR(20)    -> );// 将users4的写入testmysql> INSERT test(username) SELECT username FROM users4 WHERE age >= 25;Query OK, 3 rows affected (0.03 sec)Records: 3  Duplicates: 0  Warnings: 0mysql> SELECT * FROM test;+----+----------+| id | username |+----+----------+|  1 | tim      ||  2 | johnm    ||  3 | scyda    |

LIMIT偏移量公式：当前页码减一乘以每页所显示的记录数

第5章子查询与连接

子查询

子查询指嵌套在查询内部，且必须始终出现在圆括号内。子查询可以包含多个关键字或条件，如DISTINCT,GROUP BY,ORDER BY, LIMIT，函数等
子查询的外层查询可以是SELECT,INSERT,UPDATE,SET或DO。
例句：
SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);
其中，SELECT * FROM t1 …称为Outer Query[外查询](或者Outer Statement),
SELECT column1 FROM t2 称为Sub Query[子查询]。
所以，我们说子查询是嵌套在外查询内部。而事实上它有可能在子查询内部再嵌套子查询。
子查询必须出现在圆括号之间。子查询可以返回标量、一行、一列或子查询。
行级子查询：
SELECT * FROM t1 WHERE (col1,col2) = (SELECT col3, col4 FROM t2 WHERE id = 10);
SELECT * FROM t1 WHERE ROW(col1,col2) = (SELECT col3, col4 FROM t2 WHERE id = 10);
行级子查询的返回结果最多为一行。
优化子查询

使用比较运算符的子查询

mysql> SELECT AVG(goods_price) FROM tdb_goods;+------------------+| AVG(goods_price) |+------------------+|     5636.3636364 |+------------------+// 保留小数点后两位mysql> SELECT ROUND(AVG(goods_price),2) FROM tdb_goods;+---------------------------+| ROUND(AVG(goods_price),2) |+---------------------------+|                   5636.36 |+---------------------------+mysql> select goods_id,goods_name,goods_price from tdb_goods WHERE goods_price >= 5636;

运用子查询：

mysql> select goods_id,goods_name,goods_price from tdb_goods WHERE goods_price >= (SELECT ROUND(AVG(goods_price), 2) FROM tdb_goods);mysql> select goods_price from tdb_goods where goods_cate='超级本';+-------------+| goods_price |+-------------+|    4999.000 ||    4299.000 ||    7999.000 |+-------------+mysql> select * from tdb_goods where goods_cate = '超级本';+----------+---------------------------------+------------+------------+-------------+---------+------------+| goods_id | goods_name                      | goods_cate | brand_name | goods_price | is_show | is_saleoff |+----------+---------------------------------+------------+------------+-------------+---------+------------+|        5 | X240(20ALA0EYCD) 12.5英寸超极本 | 超级本     | 联想       |    4999.000 |       1 |          0 ||        6 | U330P 13.3英寸超极本            | 超级本     | 联想       |    4299.000 |       1 |          0 ||        7 | SVP13226SCB 13.3英寸触控超极本  | 超级本     | 索尼       |    7999.000 |       1 |          0 |+----------+---------------------------------+------------+------------+-------------+---------+------------+

若子查询返回多个结果，可以用以下修饰：
用ANY SOME ALL修饰的比较运算符
operand comparison_operator ANY(subquery)
operand comparison_operator SOME(subquery)
operand comparison_operator ALL(subquery)

运算符和关键字 ANY SOME ALL ‘>’ >= 最小值最小值最大值 < <= 最大值最大值最小值 = 任意值任意值 < > != 任意值

mysql> select goods_id,goods_name,goods_price from tdb_goods WHERE goods_price > ANY(select goods_price from tdb_goods where goods_cate = '超级本');+----------+----------------------------------+-------------+| goods_id | goods_name                       | goods_price |+----------+----------------------------------+-------------+|        2 | Y400N 14.0英寸笔记本电脑         |    4899.000 ||        3 | G150TH 15.6英寸游戏本            |    8499.000 ||        5 | X240(20ALA0EYCD) 12.5英寸超极本  |    4999.000 ||        7 | SVP13226SCB 13.3英寸触控超极本   |    7999.000 ||       13 | iMac ME086CH/A 21.5英寸一体电脑  |    9188.000 ||       16 | PowerEdge T110 II服务器          |    5388.000 ||       17 | Mac Pro MD878CH/A 专业级台式电脑 |   28888.000 ||       18 |  HMZ-T3W 头戴显示设备            |    6999.000 ||       20 | X3250 M4机架式服务器 2583i14     |    6888.000 ||       21 |  HMZ-T3W 头戴显示设备            |    6999.000 |+----------+----------------------------------+---------mysql> select goods_id,goods_name,goods_price from tdb_goods WHERE goods_price = ANY(select goods_price from tdb_goods where goods_cate = '超级本');+----------+---------------------------------+-------------+| goods_id | goods_name                      | goods_price |+----------+---------------------------------+-------------+|        5 | X240(20ALA0EYCD) 12.5英寸超极本 |    4999.000 ||        6 | U330P 13.3英寸超极本            |    4299.000 ||        7 | SVP13226SCB 13.3英寸触控超极本  |    7999.000 |+----------+---------------------------------+-------------+

使用【NOT】 IN的子查询

=ANY运算符与IN等效
！=ALL或<>ALL与NOT IN等效
除去超级本的3个，剩下所有19个：
mysql> select goods_id,goods_name,goods_price from tdb_goods WHERE goods_price != ALL(select goods_price from tdb_goods where goods_cate = ‘超级本’);

使用【NOT】 EXISTS的子查询

若子查询返回任何行，EXISTS将返回为true，否则为false

多表更新

意思是参照另外的表来更新本表的参数。
创建一个表

mysql> CREATE TABLE IF NOT EXISTS tdb_goods_cates(    -> cate_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,    -> cate_name VARCHAR(40) NOT NULL);mysql> SELECT goods_cate from tdb_goods GROUP BY goods_cate;+---------------+| goods_cate    |+---------------+| 台式机        || 平板电脑      || 服务器/工作站 || 游戏本        || 笔记本        || 笔记本配件    || 超级本        |+---------------+

将查询结果写入数据表：
INSERT [INTO] tbl_name [(col_name,..)] SELECT…

mysql> insert tdb_goods_cates(cate_name) select goods_cate FROM tdb_goods GROUP BY goods_cate;Query OK, 7 rows affected (0.02 sec)mysql> select * from tdb_goods_cates;+---------+---------------+| cate_id | cate_name     |+---------+---------------+|       1 | 台式机        ||       2 | 平板电脑      ||       3 | 服务器/工作站 ||       4 | 游戏本        ||       5 | 笔记本        ||       6 | 笔记本配件    ||       7 | 超级本        |+---------+---------------+

使用外键,多表更新
连接类型：
INNER JOIN，内连接
在MYsql中，JOIN,CROSS JOIN和INNER JOIN等价
LEFT[OUTER] JOIN,左外连接
RIGHT[OUTER] JOIN右外连接
mysql> UPDATE tdb_goods INNER JOIN tdb_goods_cates ON goods_cate = cate_name
-> SET goods_cate = cate_id;

一步进行多表更新

创建数据表同时将查询结果写入到数据表
CREATE TABLE
[(create_difinition)]
select_statement

mysql> select brand_name from tdb_goods group by brand_name;+------------+| brand_name |+------------+| IBM        || 华硕       || 宏碁       || 惠普       || 戴尔       || 索尼       || 联想       || 苹果       || 雷神       |+------------+// 在创建时一步：mysql> CREATE TABLE tdb_goods_brands(    -> brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,    -> brand_name VARCHAR(40) NOT NULL)    -> select brand_name from tdb_goods group by brand_name;Query OK, 9 rows affected (0.28 sec)mysql> show tables;+------------------+| Tables_in_goods  |+------------------+| tdb_goods        || tdb_goods_brands || tdb_goods_cates  |+------------------+3 rows in set (0.00 sec)mysql> select * from tdb_goods_brands \G;*************************** 1. row ***************************  brand_id: 1brand_name: IBM*************************** 2. row ***************************  brand_id: 2brand_name: 华硕*************************** 3. row ***************************  brand_id: 3brand_name: 宏碁*************************** 4. row ***************************  brand_id: 4brand_name: 惠普*************************** 5. row ***************************  brand_id: 5brand_name: 戴尔*************************** 6. row ***************************  brand_id: 6brand_name: 索尼*************************** 7. row ***************************  brand_id: 7brand_name: 联想*************************** 8. row ***************************  brand_id: 8brand_name: 苹果*************************** 9. row ***************************  brand_id: 9brand_name: 雷神9 rows in set (0.00 sec)

参照brand表来更新tdb-goods表（id）。
两个表都有brand_name，这样是错的

mysql> update tdb_goods inner join tdb_goods_brands on brand_name = brand_name    -> set brand_name = brand_id;//起别名ERROR 1052 (23000): Column 'brand_name' in field list is ambiguousmysql> update tdb_goods AS a inner join tdb_goods_brands AS b on a.brand_name = b.brand_name    -> set a.brand_name = brand_id;Query OK, 22 rows affected (0.09 sec)mysql> select * from tdb_goods \G;*************************** 1. row ***************************  goods_id: 1 goods_name: R510VC 15.6英寸笔记本 goods_cate: 笔记本 brand_name: 2goods_price: 3399.000    is_show: 1 is_saleoff: 0

此时在show columns from tdb_goods;里，brand_name和goods_cate还是
VARCHAR(40)类型，修改后再查看表结构

mysql> ALTER TABLE tdb_goods    -> CHANGE goods_cate cate_id SMALLINT UNSIGNED NOT NULL,    -> CHANGE brand_name brand_id SMALLINT UNSIGNED NOT NULL;Query OK, 22 rows affected (0.68 sec)mysql> show columns from tdb_goods;+-------------+------------------------+------+-----+---------+----------------+| Field       | Type                   | Null | Key | Default | Extra          |+-------------+------------------------+------+-----+---------+----------------+| goods_id    | smallint(5) unsigned   | NO   | PRI | NULL    | auto_increment || goods_name  | varchar(150)           | NO   |     | NULL    |                || cate_id     | smallint(5) unsigned   | NO   |     | NULL    |                || brand_id    | smallint(5) unsigned   | NO   |     | NULL    |                || goods_price | decimal(15,3) unsigned | NO   |     | 0.000   |                || is_show     | tinyint(1)             | NO   |     | 1       |                || is_saleoff  | tinyint(1)             | NO   |     | 0       |                |+-------------+------------------------+------+-----+---------+----------------+mysql> select * from tdb_goods \G;*************************** 1. row ***************************   goods_id: 1 goods_name: R510VC 15.6英寸笔记本    cate_id: 5   brand_id: 2goods_price: 3399.000    is_show: 1 is_saleoff: 0

– 分别在tdb_goods_cates和tdb_goods_brands表插入记录：
INSERT tdb_goods_cates(cate_name) VALUES(‘路由器’),(‘交换机’),(‘网卡’);
INSERT tdb_goods_brands(brand_name) VALUES(‘海尔’),(‘清华同方’),(‘神舟’);
– 在tdb_goods数据表写入任意记录
INSERT tdb_goods(goods_name,cate_id,brand_id,goods_price) VALUES(’ LaserJet Pro P1606dn 黑白激光打印机’,’12’,’4’,’1849’);
这里有错，cate_id没有12：

mysql> select * from tdb_goods_cates;+---------+---------------+| cate_id | cate_name     |+---------+---------------+|       1 | 台式机        ||       2 | 平板电脑      ||       3 | 服务器/工作站 ||       4 | 游戏本        ||       5 | 笔记本        ||       6 | 笔记本配件    ||       7 | 超级本        ||       8 | 路由器        ||       9 | 交换机        ||      10 | 网卡          |+---------+---------------+

连接

Mysql在SELECT语句、多表更新、多表删除语句中支持JOIN操作。
table_reference {[INNER|CROSS] JOIN | {LEFT|RIGHT} [OUTER] JOIN}
table_reference ON conditional_expr
(连接时为区分两个同名字段，可以加上别名)数据表可以使用tbl_name AS alias_name或tbl_name alias_name赋予别名
table_subquery可以作为子查询使用在FROM子句中，这样的子查询必须为其赋予别名
1. 内连接（显示左表和右表符合条件的记录）
JOIN ,CROSS JOIN, INNER JOIN等价
2. 左外连接（显示左表的全部记录和右表符合连接条件的记录）
LEFT [OUTER] JOIN
3. 右外连接
RIGHT [OUTER] JOIN
使用关键字ON来设定连接条件，也可以使用WHERE来代替（一般用来进行结果集记录的过滤）

mysql> select goods_id, goods_name,cate_name from tdb_goods inner join tdb_goods_cates    -> on tdb_goods.cate_id = tdb_goods_cates.cate_id;+----------+------------------------------------------------------------------------+---------------+| goods_id | goods_name                                                             | cate_name     |+----------+------------------------------------------------------------------------+---------------+|        1 | R510VC 15.6英寸笔记本                                                  | 笔记本        ||        2 | Y400N 14.0英寸笔记本电脑                                               | 笔记本        ||        3 | G150TH 15.6英寸游戏本                                                  | 游戏本        ||        4 | X550CC 15.6英寸笔记本                                                  | 笔记本        ||        5 | X240(20ALA0EYCD) 12.5英寸超极本                                        | 超级本        ||        6 | U330P 13.3英寸超极本                                                   | 超级本        ||        7 | SVP13226SCB 13.3英寸触控超极本                                         | 超级本        ||        8 | iPad mini MD531CH/A 7.9英寸平板电脑                                    | 平板电脑      ||        9 | iPad Air MD788CH/A 9.7英寸平板电脑 （16G WiFi版）                      | 平板电脑      ||       10 |  iPad mini ME279CH/A 配备 Retina 显示屏 7.9英寸平板电脑 （16G WiFi版） | 平板电脑      ||       11 | IdeaCentre C340 20英寸一体电脑                                         | 台式机        ||       12 | Vostro 3800-R1206 台式电脑                                             | 台式机        ||       13 | iMac ME086CH/A 21.5英寸一体电脑                                        | 台式机        ||       14 | AT7-7414LP 台式电脑 （i5-3450四核 4G 500G 2G独显 DVD 键鼠 Linux ）     | 台式机        ||       15 | Z220SFF F4F06PA工作站                                                  | 服务器/工作站 ||       16 | PowerEdge T110 II服务器                                                | 服务器/工作站 ||       17 | Mac Pro MD878CH/A 专业级台式电脑                                       | 服务器/工作站 ||       18 |  HMZ-T3W 头戴显示设备                                                  | 笔记本配件    ||       19 | 商务双肩背包                                                           | 笔记本配件    ||       20 | X3250 M4机架式服务器 2583i14                                           | 服务器/工作站 ||       21 |  HMZ-T3W 头戴显示设备                                                  | 笔记本配件    ||       22 | 商务双肩背包                                                           | 笔记本配件    |

因为刚加入的23号不符合连接条件，所以只有22个。
左外：
mysql> select goods_id, goods_name,cate_name from tdb_goods left join tdb_goods_cates
-> on tdb_goods.cate_id = tdb_goods_cates.cate_id;
有这一条：
23 | LaserJet Pro P1606dn 黑白激光打印机 | NULL |
+———-+————-
右外：
新增的23号商品不符合右表，所以不存在，仅存在插入到右表的
***************** 23. row *****************
goods_id: NULL
goods_name: NULL
cate_name: 路由器
***************** 24. row *****************
goods_id: NULL
goods_name: NULL
cate_name: 交换机
***************** 25. row *****************
goods_id: NULL
goods_name: NULL
cate_name: 网卡
25 rows in set (0.00 sec)

多表连接

mysql> select goods_id,goods_name,cate_name,brand_name, goods_price from tdb_goods AS g  inner join    -> tdb_goods_cates AS c on g.cate_id = c.cate_id    -> inner join tdb_goods_brands AS b on g.brand_id = b.brand_id \G*************************** 1. row ***************************   goods_id: 1 goods_name: R510VC 15.6英寸笔记本  cate_name: 笔记本 brand_name: 华硕goods_price: 3399.000*************************** 2. row ***************************   goods_id: 2 goods_name: Y400N 14.0英寸笔记本电脑  cate_name: 笔记本 brand_name: 联想goods_price: 4899.000*************************** 3. row ***************************   goods_id: 3 goods_name: G150TH 15.6英寸游戏本  cate_name: 游戏本 brand_name: 雷神goods_price: 8499.000*************************** 4. row ***************************   goods_id: 4 goods_name: X550CC 15.6英寸笔记本  cate_name: 笔记本 brand_name: 华硕goods_price: 2799.000*************************** 5. row ***************************   goods_id: 5 goods_name: X240(20ALA0EYCD) 12.5英寸超极本  cate_name: 超级本 brand_name: 联想goods_price: 4999.000*************************** 6. row ***************************   goods_id: 6 goods_name: U330P 13.3英寸超极本  cate_name: 超级本 brand_name: 联想goods_price: 4299.000*************************** 7. row ***************************   goods_id: 7 goods_name: SVP13226SCB 13.3英寸触控超极本  cate_name: 超级本 brand_name: 索尼goods_price: 7999.000*************************** 8. row ***************************   goods_id: 8 goods_name: iPad mini MD531CH/A 7.9英寸平板电脑  cate_name: 平板电脑 brand_name: 苹果goods_price: 1998.000*************************** 9. row ***************************   goods_id: 9 goods_name: iPad Air MD788CH/A 9.7英寸平板电脑 （16G WiFi版）  cate_name: 平板电脑 brand_name: 苹果goods_price: 3388.000*************************** 10. row ***************************   goods_id: 10 goods_name:  iPad mini ME279CH/A 配备 Retina 显示屏 7.9英寸平板电脑 （16G WiFi版）  cate_name: 平板电脑 brand_name: 苹果goods_price: 2788.000*************************** 11. row ***************************   goods_id: 11 goods_name: IdeaCentre C340 20英寸一体电脑  cate_name: 台式机 brand_name: 联想goods_price: 3499.000*************************** 12. row ***************************   goods_id: 12 goods_name: Vostro 3800-R1206 台式电脑  cate_name: 台式机 brand_name: 戴尔goods_price: 2899.000*************************** 13. row ***************************   goods_id: 13 goods_name: iMac ME086CH/A 21.5英寸一体电脑  cate_name: 台式机 brand_name: 苹果goods_price: 9188.000*************************** 14. row ***************************   goods_id: 14 goods_name: AT7-7414LP 台式电脑 （i5-3450四核 4G 500G 2G独显 DVD 键鼠 Linux ）  cate_name: 台式机 brand_name: 宏碁goods_price: 3699.000*************************** 15. row ***************************   goods_id: 15 goods_name: Z220SFF F4F06PA工作站  cate_name: 服务器/工作站 brand_name: 惠普goods_price: 4288.000*************************** 16. row ***************************   goods_id: 16 goods_name: PowerEdge T110 II服务器  cate_name: 服务器/工作站 brand_name: 戴尔goods_price: 5388.000*************************** 17. row ***************************   goods_id: 17 goods_name: Mac Pro MD878CH/A 专业级台式电脑  cate_name: 服务器/工作站 brand_name: 苹果goods_price: 28888.000*************************** 18. row ***************************   goods_id: 18 goods_name:  HMZ-T3W 头戴显示设备  cate_name: 笔记本配件 brand_name: 索尼goods_price: 6999.000*************************** 19. row ***************************   goods_id: 19 goods_name: 商务双肩背包  cate_name: 笔记本配件 brand_name: 索尼goods_price: 99.000*************************** 20. row ***************************   goods_id: 20 goods_name: X3250 M4机架式服务器 2583i14  cate_name: 服务器/工作站 brand_name: IBMgoods_price: 6888.000*************************** 21. row ***************************   goods_id: 21 goods_name:  HMZ-T3W 头戴显示设备  cate_name: 笔记本配件 brand_name: 索尼goods_price: 6999.000*************************** 22. row ***************************   goods_id: 22 goods_name: 商务双肩背包  cate_name: 笔记本配件 brand_name: 索尼goods_price: 99.00022 rows in set (0.00 sec)

关于连接的几点：
外连接：
A LEFT JOIN B join_condition
数据表B的结果集依赖数据表A（A中有的记录在B表中才显示出来，否则不能显示）
数据表A的结果集根据左连接条件依赖所有数据表（B表除外）
左外连接条件决定如何检索数据表B（在没有指定WHERE条件的情况下），即
如果数据表A的某条记录符合WHERE条件，但在B表不存在符合连接条件的记录，将生成一个所有列为空（NULL）的额外的B行
若使用内连接查找的记录在连接数据表中不存在，并且在WHERE子句中尝试以下操作：col_name IS NULL时，如果col_name被定义为NOT NULL，mysql将在找到符合连接条件的记录后停止搜索更多的行。

无限分类的数据表设计：
CREATE TABLE tdb_goods_types(
type_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
type_name VARCHAR(20) NOT NULL,
parent_id SMALLINT UNSIGNED NOT NULL DEFAULT 0
);
家用电器和电脑、办公是顶级分类，parent_id为0。

mysql> select * from tdb_goods_types;+---------+------------+-----------+| type_id | type_name  | parent_id |+---------+------------+-----------+|       1 | 家用电器   |         0 ||       2 | 电脑、办公 |         0 ||       3 | 大家电     |         1 ||       4 | 生活电器   |         1 ||       5 | 平板电视   |         3 ||       6 | 空调       |         3 ||       7 | 电风扇     |         4 ||       8 | 饮水机     |         4 ||       9 | 电脑整机   |         2 ||      10 | 电脑配件   |         2 ||      11 | 笔记本     |         9 ||      12 | 超级本     |         9 ||      13 | 游戏本     |         9 ||      14 | CPU        |        10 ||      15 | 主机       |        10 |+---------+------------+-----------+

自身连接
同一个数据表对其自身进行连接。左边是子表son，右边是父表

mysql> SELECT s.type_id,s.type_name,p.type_name from tdb_goods_types as s    -> left join tdb_goods_types as p    -> on s.parent_id = p.type_id;+---------+------------+------------+| type_id | type_name  | type_name  |+---------+------------+------------+|       1 | 家用电器   | NULL       ||       2 | 电脑、办公 | NULL       ||       3 | 大家电     | 家用电器   ||       4 | 生活电器   | 家用电器   ||       5 | 平板电视   | 大家电     ||       6 | 空调       | 大家电     ||       7 | 电风扇     | 生活电器   ||       8 | 饮水机     | 生活电器   ||       9 | 电脑整机   | 电脑、办公 ||      10 | 电脑配件   | 电脑、办公 ||      11 | 笔记本     | 电脑整机   ||      12 | 超级本     | 电脑整机   ||      13 | 游戏本     | 电脑整机   ||      14 | CPU        | 电脑配件   ||      15 | 主机       | 电脑配件   |+---------+------------+------------+

反过来：

mysql> select p.type_id,p.type_name,s.type_name from tdb_goods_types as p left join    -> tdb_goods_types as s on s.parent_id = p.type_id;

父类下子类的数目：

mysql> select p.type_id,p.type_name,count(s.type_name) child_count from tdb_goods_types as p left join    -> tdb_goods_types as s on s.parent_id = p.type_id group by p.type_name order by p.type_id;+---------+------------+-------------+| type_id | type_name  | child_count |+---------+------------+-------------+|       1 | 家用电器   |           2 ||       2 | 电脑、办公 |           2 ||       3 | 大家电     |           2 ||       4 | 生活电器   |           2 ||       5 | 平板电视   |           0 ||       6 | 空调       |           0 ||       7 | 电风扇     |           0 ||       8 | 饮水机     |           0 ||       9 | 电脑整机   |           3 ||      10 | 电脑配件   |           2 ||      11 | 笔记本     |           0 ||      12 | 超级本     |           0 ||      13 | 游戏本     |           0 ||      14 | CPU        |           0 ||      15 | 主机       |           0 |+---------+------------+-------------+

多表删除
select * from tdb_goods中有重复的记录。
一共23个，这里显示21条记录（2个重复的）

mysql> select goods_id,goods_name from tdb_goods group by goods_name;+----------+------------------------------------------------------------------------+| goods_id | goods_name                                                             |+----------+------------------------------------------------------------------------+|       18 |  HMZ-T3W 头戴显示设备                                                  ||       10 |  iPad mini ME279CH/A 配备 Retina 显示屏 7.9英寸平板电脑 （16G WiFi版） ||       23 |  LaserJet Pro P1606dn 黑白激光打印机                                   ||       14 | AT7-7414LP 台式电脑 （i5-3450四核 4G 500G 2G独显 DVD 键鼠 Linux ）     ||        3 | G150TH 15.6英寸游戏本                                                  ||       11 | IdeaCentre C340 20英寸一体电脑                                         ||       13 | iMac ME086CH/A 21.5英寸一体电脑                                        ||        9 | iPad Air MD788CH/A 9.7英寸平板电脑 （16G WiFi版）                      ||        8 | iPad mini MD531CH/A 7.9英寸平板电脑                                    ||       17 | Mac Pro MD878CH/A 专业级台式电脑                                       ||       16 | PowerEdge T110 II服务器                                                ||        1 | R510VC 15.6英寸笔记本                                                  ||        7 | SVP13226SCB 13.3英寸触控超极本                                         ||        6 | U330P 13.3英寸超极本                                                   ||       12 | Vostro 3800-R1206 台式电脑                                             ||        5 | X240(20ALA0EYCD) 12.5英寸超极本                                        ||       20 | X3250 M4机架式服务器 2583i14                                           ||        4 | X550CC 15.6英寸笔记本                                                  ||        2 | Y400N 14.0英寸笔记本电脑                                               ||       15 | Z220SFF F4F06PA工作站                                                  ||       19 | 商务双肩背包                                                           |mysql> select goods_id,goods_name from tdb_goods group by goods_name having count(goods_name) >= 2;+----------+-----------------------+| goods_id | goods_name            |+----------+-----------------------+|       18 |  HMZ-T3W 头戴显示设备 ||       19 | 商务双肩背包          |+----------+-----------------------+2 rows in set (0.02 sec)

上面显示的是要删除的两项。删除t1，且保留id小的项

mysql> delete t1 from tdb_goods as t1 left join (select goods_id,goods_name from tdb_goods group by goods_name having count(goods_name) >= 2) as t2 on t1.goods_name = t2.goods_name where t1.goods_id > t2.goods_id;Query OK, 2 rows affected (0.16 sec)

查看,发现21和22被删除了，21的位置是23

mysql> select * from tdb_goods \G*************************** 21. row ***************************   goods_id: 23 goods_name:  LaserJet Pro P1606dn 黑白激光打印机    cate_id: 12   brand_id: 4goods_price: 1849.000    is_show: 1 is_saleoff: 0

第六章内置函数库

字符函数
数值运算符与函数
比较运算符与函数
日期时间函数
信息函数
聚合函数

1. 字符函数

函数名称 CONCAT() 字符连接 CONCAT_WS() 使用指定的分隔符进行字符连接 FORMAT() 数字格式化 LOWER() 转换成小写字母 UPPER() 转换成大写字母 LEFT() 获取左侧字符 RIGHT() 获取右侧字符 LENGTH() 获取字符串长度 RIGHT() 获取右侧字符 LTRIM() 删除前导空格 RTRIM() 删除后续空格 TRIM() 删除前导和后续 SUBSTRING() 字符串截取 [NOT]LIKE() 模式匹配 REPLACE() 字符串替换

前导字符是第一个字符之前的空格
后续是最后一个字符之后的空格

mysql> use test1;Database changedmysql> select concat('test1', '-','imooc');+------------------------------+| concat('test1', '-','imooc') |+------------------------------+| test1-imooc                  |+------------------------------+mysql> select concat(first_name,last_name) as fullname from test;+----------+| fullname |+----------+| AB       || cd       || tom123   || NULL     |+----------+mysql> select concat_ws('|','A','B','C');+----------------------------+| concat_ws('|','A','B','C') |+----------------------------+| A|B|C                      |+----------------------------+1 row in set (0.00 sec)mysql> select format(12345.75,1);+--------------------+| format(12345.75,1) |+--------------------+| 12,345.8           |+--------------------+mysql> select lower('Mysql');+----------------+| lower('Mysql') |+----------------+| mysql          |+----------------+mysql> select lower (left('Mysql',2));+-------------------------+| lower (left('Mysql',2)) |+-------------------------+| my                      |+-------------------------+mysql> SELECT length('mysql ');// 包含空格mysql> select length('  mysql    ');+-----------------------+| length('  mysql    ') |+-----------------------+|                    11 |+-----------------------+1 row in set (0.00 sec)mysql> select length(ltrim('  mysql    '));+------------------------------+| length(ltrim('  mysql    ')) |+------------------------------+|                            9 |+------------------------------+//删除字符串中前导的?字符mysql> select trim(leading'?' from '??mysql');+---------------------------------+| trim(leading'?' from '??mysql') |+---------------------------------+| mysql                           |+---------------------------------+mysql> select trim(trailing'?' from '??mysql???');+-------------------------------------+| trim(trailing'?' from '??mysql???') |+-------------------------------------+| ??mysql                             |+-------------------------------------+//前后都删mysql> select trim(both '?' from '??mysql???');mysql> select replace ('??my??sql???','?','');+---------------------------------+| replace ('??my??sql???','?','') |+---------------------------------+| mysql                           |+---------------------------------+mysql> select substring('mysql',1,2);+------------------------+| substring('mysql',1,2) |+------------------------+| my                     |+------------------------+mysql> select substring('mysql',1);//截到结尾// -1从最后一位开始截取mysql> select substring('mysql',-1);+-----------------------+| substring('mysql',-1) |+-----------------------+| l                     |+-----------------------+

从第一位开始截两个，数据库中编号从1开始
%代表任意字符
_代表任意一个字符

// 1表示truemysql> select 'mysql' like 'm%';+-------------------+| 'mysql' like 'm%' |+-------------------+|                 1 |+-------------------+mysql> select * from test;+------------+-----------+| first_name | last_name |+------------+-----------+| A          | B         || c          | d         || NULL       | 11        || tom%       | 123       |mysql> select * from test where first_name like '%o%';+------------+-----------+| first_name | last_name |+------------+-----------+| tom%       | 123       |+------------+-----------+// 第一个和最后一个%是通配符，这里把第2个也当作通配符了mysql> select * from test where first_name like '%%%';+------------+-----------+| first_name | last_name |+------------+-----------+| A          | B         || c          | d         || tom%       | 123       |+------------+-----------+3 rows in set (0.00 sec)mysql> select * from test where first_name like '%1%%' escape '1';+------------+-----------+| first_name | last_name |+------------+-----------+| tom%       | 123       |+------------+-----------+

2. 数值运算符

mysql> select 3 + 4;+-------+| 3 + 4 |+-------+|     7 |+-------+

名称描述 CEIL() 进一取整 DIV 整数除法 FLOOR() 舍一取整 MOD 取余数（取模） POWER() 幂运算 ROUND() 四舍五入 TRUNCATE() 数字截取

mysql> select ceil(3.01); // 4
mysql> select 3/4; // 0.7500
mysql> select 3 div 4; // 0
mysql> select 5 % 3; // 等价于5 mod 3，为2
mysql> select power(3,3); //27
mysql> select round(3.652,2); // 保留小数点2位，3,65
mysql> select round(3.652,1); //3.7
mysql> select round(3.652,0); //4
mysql> select truncate(125.89,0);//125
mysql> select truncate(125.89,-1);//120把那一位整个去掉

3. 比较运算符与函数

名称描述 [NOT]BETWEEN…AND.. 【不】在范围之内 [NOT]IN() 【不】在列出值范围内 IS [NOT] NULL 【不】为空

mysql> select 15 between 1 and 20;// 输出为1
mysql> select 10 in (5,10,15); // 1
mysql> select null is null; // 1
mysql> select ” is null;//空字符串不为null
// 用法：查哪个用户的first_name为空

mysql> select * from test where first_name is null;+------------+-----------+| first_name | last_name |+------------+-----------+| NULL       | 11        |+------------+-----------+mysql> select * from test where first_name is not null;

4. 日期时间函数

函数名称 NOW() 当前日期和时间 CURDATE() 当前日期 CURTIME() 当前时间 DATE_ADD() 日期变化 DATEDIFF() 日期差值 DATE_FORMAT() 日期格式化

SELECT DATE_ADD('2014-3-12',INTERVAL 365 DAY);2015-03-12SELECT DATE_ADD('2014-3-12',INTERVAL -3 WEEK);SELECT DATE_DIFF('2013-3-12','2014-3-12');-365SELECT DATE_FORMAT('2013-3-2','%m/%d/%Y'); // 默认前导003/02/2014

5. 信息函数

函数名称 CONNECTION_ID() 连接ID DATABASE() 当前数据库 LAST_INSERT_ID() 最后插入记录 USER() 当前用户 VERSION() 版本信息

// 查看当前mysql> DESC table1;+-------------+-------------+------+-----+---------+-------+| Field       | Type        | Null | Key | Default | Extra |+-------------+-------------+------+-----+---------+-------+| first_name  | varchar(20) | YES  |     | NULL    |       || second_name | varchar(20) | YES  |     | NULL    |       |+-------------+-------------+------+-----+---------+-------+mysql> alter table table1 ADD id SMALLINT UNSIGNED    -> KEY AUTO_INCREMENT FIRST; id          | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment || first_name  | varchar(20)          | YES  |     | NULL    |                || second_name | varchar(20)          | YES  |     | NULL    |mysql> insert table1(first_name,second_name) values('11','22');// 得到目前新写入的id号：mysql> select last_insert_id();+------------------+| last_insert_id() |+------------------+|                5 |+------------------+// last_insert_id()，若继续insert两条，只返回第一条写入的id：6

6. 聚合函数

函数名称 AVG() 平均值 COUNT() 计数 MAX() 最大值 MIN() 最小值 SUM() 求和

select * from tdb_goods LIMIT 1; // 只看一条mysql> select ROUND(avg(goods_price),2) as avg_price from tdb_goods;+-----------+| avg_price |+-----------+|   5845.10 |+-----------+// 有多少条记录mysql> select count(goods_id) as counts from tdb_goods;+--------+| counts |+--------+|     20 |+--------+mysql> select max(goods_price) as counts from tdb_goods;

7. 加密函数

函数名称 MD5() 信息摘要算法 PASSWORD() 密码算法

mysql> SELECT MD5('admin')    -> ;+----------------------------------+| MD5('admin')                     |+----------------------------------+| 21232f297a57a5a743894a0e4a801fc3 |+----------------------------------+1 row in set (0.08 sec)// password用来修改密码mysql> SET PASSWORD = PASSWORD('123');

如果为web页面做准备，推荐MD5

第七章自定义函数

用户自定义函数(UDF)是一种对mysql扩展的途径，其用法与内置函数相同

自定义函数必要条件:参数、返回值

CREATE FUNCTION function_name RETURNS {STRING|INTEGER|REAL|DECIMAL} rourine_body

mysql> select date_format(now(), '%y年%m月 %h点');+-------------------------------------+| date_format(now(), '%y年%m月 %h点') |+-------------------------------------+| 16年12月 09点                       |+-------------------------------------+mysql> create function f1() returns VARCHAR(30)    -> return date_format(now(),'%Y年%m月%d日 %h点：%i分：%s秒');Query OK, 0 rows affected (0.11 sec)mysql> select f1();+---------------------------------+| f1()                            |+---------------------------------+| 2016年12月14日 09点：21分：17秒 |+---------------------------------+// 带参mysql> create function f2(num1 SMALLINT UNSIGNED, num2 SMALLINT UNSIGNED)    -> returns float(10,2) unsigned    -> return (num1 + num2)/2;Query OK, 0 rows affected (0.03 sec)mysql> select f2(10,15);+-----------+| f2(10,15) |+-----------+|     12.50 |+-----------+1 row in set (0.03 sec)//删函数DROP function adduser;// 所有命令都通过//结束mysql> delimiter //mysql> create function adduser(username VARCHAR(20))    -> returns INT UNSIGNED    -> return    -> insert test(username) values(username);    -> last_insert_id();    -> //ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'test(username) values(username);last_insert_id()' at line 4mysql> create function adduser(username VARCHAR(20))    -> returns INT UNSIGNED    -> begin    -> insert test(username) values(username);    -> return last_insert_id();    -> end    -> //Query OK, 0 rows affected (0.02 sec)mysql> select adduser('rose');    -> //+-----------------+| adduser('rose') |+-----------------+|               4 |+-----------------+1 row in set (0.08 sec)

存储过程

SQL命令——>Mysql引擎——>(分析)语法正确——>可识别命令——>（执行）执行结果——>（返回）客户端

存储过程是SQL语句和控制语句的预编译集合，以一个名称存储并作为一个单元处理
存储过程存储在数据库内，可以接收参数，可以存在多个返回值

增强SQL语句的功能和灵活性
实现较快的执行速度
减少网络流量
创建存储过程：
CREATE [DEFINER = {user | CURRENT_USER}]
PROCEDURE sp_name ([proc_parameter[,…]])
[characteristic…] routine_body

proc_parameter:
[IN| OUT |INOUT] parameter_name type
参数：
IN，表示该参数的值必须在调用存储过程时指定
OUT，表示该参数的值可以被存储过程改变，并且可以返回
INOUT，表示该参数的值调用时指定，并可以被改变和返回
特性：
COMMENT:注释
CONTAINS SQL:包含SQL语句，但不包含读或写数据的语句
NO SQL：不包含SQL语句
READS SQL DATA：包含读数据的语句
MODIFIES SQL DATA：包含写数据的语句
SQL SECURITY{DEFINER|INVOKER}指明谁有权限来执行

过程体由合法的SQL语句构成（记录的增删改查和多表连接，不能创建数据库或数据表）
复合结构：BEGIN..END
可包含声明，循环，控制结构

调用存储过程：
CALL sp_name([parameter[,…]])
CALL sp_name[()]

mysql> CREATE procedure sp1() select version();Query OK, 0 rows affected (0.14 sec)mysql> call sp1;+------------+| version()  |+------------+| 5.7.16-log |

IN:

mysql> desc test;+------------+-------------+------+-----+---------+-------+| Field      | Type        | Null | Key | Default | Extra |+------------+-------------+------+-----+---------+-------+| first_name | varchar(20) | YES  |     | NULL    |       || last_name  | varchar(20) | YES  |     | NULL    |       |+------------+-------------+------+-----+---------+-------+mysql> create procedure removeUserId(IN id int unsigned)    -> begin    -> delete from test where id = id;    -> end    -> //Query OK, 0 rows affected (0.00 sec)mysql> call removeUserId(3);    -> //Query OK, 4 rows affected (0.07 sec)mysql> select * from test//Empty set (0.00 sec)

空集是因为where id = id,所以参数不能和数据表的字段相同

存储过程不能修改过程体，只能先删除

mysql> create procedure removeUserId(in p_id INT UNSIGNED)    -> begin    -> delete from test where id = p_id;    -> end//Query OK, 0 rows affected (0.00 sec)mysql> select * from test//+----+------------+-----------+| id | first_name | last_name |+----+------------+-----------+|  1 | A          | 1         ||  2 | b          | 1         ||  3 | c          | 123       |+----+------------+-----------+mysql> call removeUserId(3);//Query OK, 1 row affected (0.10 sec)mysql> select * from test//+----+------------+-----------+| id | first_name | last_name |+----+------------+-----------+|  1 | A          | 1         ||  2 | b          | 1         |+----+------------+-----------+2 rows in set (0.00 sec)mysql> create procedure removeAndReturnName(in p_id int unsigned, out countNum  int unsigned)    -> begin    -> delete from test where id =p_id;    -> select count(id) from test into countNum;    -> end//Query OK, 0 rows affected (0.00 sec)mysql> call removeAndReturnName(2, @nums);    -> //Query OK, 1 row affected (0.09 sec)mysql> select @nums//+-------+| @nums |+-------+|     1 |+-------+

带有@的是用户变量

一次插入两条记录，row_count()表更新的记录总数

mysql> insert test (username) values('c'),('d');    -> select row_count();//Query OK, 2 rows affected (0.09 sec)Records: 2  Duplicates: 0  Warnings: 0+-------------+| row_count() |+-------------+|           2 |+-------------+1 row in set (0.10 sec)mysql> update test set username = concat(username, '--a') where id <= 2;    -> //Query OK, 2 rows affected (0.08 sec)Rows matched: 2  Changed: 2  Warnings: 0mysql> select row_count();//+-------------+| row_count() |+-------------+|           2 |+-------------+1 row in set (0.00 sec)

根据年龄删除，然后返回删除的记录数和剩下的记录数

mysql> create procedure removeUserByAgeAndReturnInfos( in p_age smallint unsigned,out deleteNums int unsigned, out userCount smallint unsigned)    -> begin    -> delete from users where age = p_age;    -> select row_count() into deleteNums;    -> select count(id) from users into userCount;    -> end //Query OK, 0 rows affected (0.00 sec)// 调用call  removeUserByAgeAndReturnInfos(23, @a,@b);// 查看返回结果select @a,@b;

参考链接

0 0