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
插入记录时,如果没有明确为字段赋值,则自动赋予默认值。
- 查找记录SELECT
第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: 0
mysql> 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: 0
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`)
) 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: 0
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 |
| 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的写入test
mysql> INSERT test(username) SELECT username FROM users4 WHERE age >= 25;
Query OK, 3 rows affected (0.03 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> 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: 1
brand_name: IBM
*************************** 2. row ***************************
brand_id: 2
brand_name: 华硕
*************************** 3. row ***************************
brand_id: 3
brand_name: 宏碁
*************************** 4. row ***************************
brand_id: 4
brand_name: 惠普
*************************** 5. row ***************************
brand_id: 5
brand_name: 戴尔
*************************** 6. row ***************************
brand_id: 6
brand_name: 索尼
*************************** 7. row ***************************
brand_id: 7
brand_name: 联想
*************************** 8. row ***************************
brand_id: 8
brand_name: 苹果
*************************** 9. row ***************************
brand_id: 9
brand_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 ambiguous
mysql> 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: 2
goods_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: 2
goods_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: IBM
goods_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.000
22 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: 4
goods_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 changed
mysql> 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表示true
mysql> 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-12
SELECT DATE_ADD('2014-3-12',INTERVAL -3 WEEK);
SELECT DATE_DIFF('2013-3-12','2014-3-12');
-365
SELECT DATE_FORMAT('2013-3-2','%m/%d/%Y'); // 默认前导0
03/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 4
mysql> 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: 0
mysql> 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;