第一章 初涉MySQL
1.1 MySQL 概述
- MySQL
MySQL由瑞典MySQL AB公司开发 , 目前属于Oracle公司
MySQL是一个开源的关系型数据库管理系统
MySQL分为社区版和企业版
1.2 MySQL 登录与退出
- mysql参数
- 登录命令 :
mysql -uroot -p -P3306 -h127.0.0.1 - 退出命令 :
exitquit\q
1.3 MySQL 提示符
- MySQL提示符
显示服务器版本 :select version()
显示当前日期 :select now()
显示当前用户 :select user()
显示当前数据库 :select database()
1.4 MySQL 语句规范及常用命令
- MySQL语句的规范
关键字和函数名称全部大写
数据库名称 , 表名称 , 字段名称全部小写
SQL语句必须以分号结尾 - 常用命令
查看数据库 :show databases;
创建数据库 :create database dbname;
如果数据库不存在则创建 :create database if not exists dbname;
删除数据库 :drop database dbname;
如果数据库不存在则创建 , 同时设置字符类型为utf8 :
create database if not exists t1 character set utf8;
如果数据库存在则直接修改字符类型为utf8 :
alter database dbname character set = utf8;
第二章 数据类型与操作数据表
2.1 MySQL 数据类型
- 数据类型决定了数据的存储格式
- 整型
TINYINT: 1字节
SMALLINT: 2字节
INT: 4字节
BIGINT: 8字节 - 浮点型 :
FLOATDOUBLE - 日期时间型
DATE: 3字节
TIMESTAMP: 4字节 (时间戳) - 字符型
CHAR(M): M个字节 , 其中 0 < = M < = 255 0 <= M <= 255 0<=M<=255
这是定长类型 , 若定义变量没达到定长的长度则用空格补齐
VARCHAR(M): L+1个字节 , 其中 L < M , 0 < = M < = 65535 L < M , 0 <= M <= 65535 L<M,0<=M<=65535
这是变长类型 , 存多长就是多长
TEXT: L+2个字节 , 其中 L < 2 16 L < 2^{16} L<216
2.2 MySQL 创建数据表
- 数据表是数据库最重要的成分之一 , 是其他对象的基础
- 打开数据库 :
use dbname - 创建数据表 :
create table [if not exists] table_name(
column_name data_type,
)create table tb1( username varchar(20), age int unsigned, salary float(7,2) unsigned );
2.3 MySQL 查看数据表
- 查看数据表 :
show tables [from dbname];
可以查看当前数据库下的数据表 , 也可以查看其它数据库的数据表 , 查看其它数据库的表时 , 当前数据库的位置不变mysql> select database(); +------------+ | database() | +------------+ | t1 | +------------+ 1 row in set (0.00 sec) mysql> show tables; +--------------+ | Tables_in_t1 | +--------------+ | tb1 | +--------------+ 1 row in set (0.00 sec) mysql> show tables from mysql; +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | ............................. ............................. ............................. | user | +---------------------------+ 31 rows in set (0.01 sec) mysql> select database(); +------------+ | database() | +------------+ | t1 | +------------+ 1 row in set (0.00 sec)
2.4 MySQL 查看数据表结构
- 查看数据表结构 :
show columns from tbname;mysql> show columns from tb1; +----------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------------+------+-----+---------+-------+ | username | varchar(20) | YES | | NULL | | | age | int(10) unsigned | YES | | NULL | | | salary | float(7,2) unsigned | YES | | NULL | | +----------+---------------------+------+-----+---------+-------+ 3 rows in set (0.04 sec)
2.5 MySQL 数据表中记录的插入与查找
- 向数据表中插入数据
insert into tbname [(col_name,...)] values(val,...)
插入数据时 , 可以将字段全部赋值 , 或者赋值指定的字段mysql> insert into tb1 values('tom',25,2525.25); Query OK, 1 row affected (0.02 sec) mysql> insert into tb1 values('amy',25); ERROR 1136 (21S01): Column count doesn't match value count at row 1 mysql> insert into tb1(username,age) values('amy',25); Query OK, 1 row affected (0.01 sec) - 查找记录
select expr,... from tbname;
显示全部字段的记录 , 没有赋值的为空NULLmysql> select * from tb1; +----------+------+---------+ | username | age | salary | +----------+------+---------+ | tom | 25 | 2525.25 | | amy | 25 | NULL | +----------+------+---------+ 2 rows in set (0.01 sec)
第三章 约束
3.1 MySQL 约束简介
- 约束
约束保证数据的完整性与一致性
约束分为表级约束与列级约束
约束类型包括 :
非空(NOT NULL)
主键(PRIMARY KEY)
唯一(UNIQUE KEY)
默认(DEFAULT)
外键(FOREIGN KEY) - 表级约束与列级约束
表级约束 : 对多个数据列建立的约束
列级约束 : 对一个数据列建立的约束
列级约束可以在列定义时声明 , 也可以在定义列后声明
表级约束只能在列定义后声明
3.2 MySQL 空值与非空
-
空值与非空
NULLNOT NULLmysql> create table tb2( -> username varchar(20) not null, -> age int unsigned null -> ); Query OK, 0 rows affected (0.03 sec) mysql> show columns from tb2; +----------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+------------------+------+-----+---------+-------+ | username | varchar(20) | NO | | NULL | | | age | int(10) unsigned | YES | | NULL | | +----------+------------------+------+-----+---------+-------+ 2 rows in set (0.01 sec) mysql> insert into tb2 values('tom',NULL); Query OK, 1 row affected (0.01 sec) mysql> select * from tb2; +----------+------+ | username | age | +----------+------+ | tom | NULL | +----------+------+ 1 row in set (0.00 sec) mysql> insert into tb2 values(NULL,11); ERROR 1048 (23000): Column 'username' cannot be null
3.3 MySQL 主键约束
-
主键约束
每张表只有一个主键 , 保证记录的唯一性 , 主键自动为NOT NULL
primary keymysql> create table tb4( -> id int unsigned primary key, -> username varchar(30) not null -> ); Query OK, 0 rows affected (0.02 sec) mysql> show colums from tb4; 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 'colums from tb4' at line 1 mysql> show columns from tb4; +----------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+------------------+------+-----+---------+-------+ | id | int(10) unsigned | NO | PRI | NULL | | | username | varchar(30) | NO | | NULL | | +----------+------------------+------+-----+---------+-------+ 2 rows in set (0.01 sec) mysql> insert into tb4 values(1,'tom'); Query OK, 1 row affected (0.01 sec) mysql> insert into tb4 values(3,'mike'); Query OK, 1 row affected (0.00 sec) mysql> select * from tb4; +----+----------+ | id | username | +----+----------+ | 1 | tom | | 3 | mike | +----+----------+ 2 rows in set (0.00 sec) mysql> insert into tb4 values(3,'rose'); ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'
3.4 MySQL 自动编号
-
自动编号
且必须与主键组合使用 , 默认起始值为1 , 增量为1
auto_incrementmysql> create table tb3( -> id int unsigned auto_increment primary key, -> username varchar(30) not null -> ); Query OK, 0 rows affected (0.03 sec) mysql> show columns from tb3; +----------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | username | varchar(30) | NO | | NULL | | +----------+------------------+------+-----+---------+----------------+ 2 rows in set (0.02 sec) mysql> insert tb3(username) values('tom'); Query OK, 1 row affected (0.01 sec) mysql> insert tb3(username) values('john'); Query OK, 1 row affected (0.00 sec) mysql> insert tb3(username) values('rose'); Query OK, 1 row affected (0.01 sec) mysql> insert tb3(username) values('amy'); Query OK, 1 row affected (0.00 sec) mysql> select * from tb3; +----+----------+ | id | username | +----+----------+ | 1 | tom | | 2 | john | | 3 | rose | | 4 | amy | +----+----------+ 4 rows in set (0.01 sec)
3.5 MySQL 唯一约束
- 唯一约束
唯一约束可以保证记录的唯一性 , 可以为NULL , 在一张数据表中可以存在多个
unique keymysql> create table tb5( -> id int unsigned auto_increment primary key, -> username varchar(30) not null unique key, -> age int unsigned -> ); Query OK, 0 rows affected (0.05 sec) mysql> show columns from tb5; +----------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | username | varchar(30) | NO | UNI | NULL | | | age | int(10) unsigned | YES | | NULL | | +----------+------------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) mysql> insert into tb5(username,age) values('tony',23); Query OK, 1 row affected (0.01 sec) mysql> insert into tb5(username,age) values('tony',25); ERROR 1062 (23000): Duplicate entry 'tony' for key 'username'
3.6 MySQL 默认约束
- 默认约束
当插入记录时 , 如果没有明确为记录赋值 , 则插入默认值
defaultmysql> create table tb6( -> id int unsigned auto_increment primary key, -> username varchar(30) not null unique key, -> sex enum('1','2','3') default'3' -> ); Query OK, 0 rows affected (0.03 sec) mysql> show columns from tb6; +----------+-------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | username | varchar(30) | NO | UNI | NULL | | | sex | enum('1','2','3') | YES | | 3 | | +----------+-------------------+------+-----+---------+----------------+ 3 rows in set (0.01 sec) mysql> insert into tb6(username) values('tony'); Query OK, 1 row affected (0.01 sec) mysql> select * from tb6; +----+----------+------+ | id | username | sex | +----+----------+------+ | 1 | tony | 3 | +----+----------+------+ 1 row in set (0.00 sec)
3.7 MySQL 外键约束
-
外键约束
foreign key -
外键约束的要求
父表与字表必须使用相同的存储引擎 , 禁止使用临时表
数据表的存储引擎只能为InnoDB
外键列和参照列必须有相似的数据类型 . 其中数字的长度或是否有符号位必须相同 , 字符的长度可以不同
外键列和参照列必须创建索引 . 如果外键列不存在索引 , mysql自动创建mysql> create table provinces( -> id int unsigned primary key auto_increment, -> pname varchar(20) not null -> ); Query OK, 0 rows affected (0.03 sec) mysql> show create table provinces; +-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | provinces | CREATE TABLE `provinces` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `pname` varchar(20) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec) mysql> create table users( -> id int unsigned primary key auto_increment, -> username varchar(10) not null, -> pid bigint, -> foreign key (pid) references provinces (id) -> ); ERROR 1215 (HY000): Cannot add foreign key constraint mysql> create table users( -> id int unsigned primary key auto_increment, -> username varchar(10) not null, -> pid int unsigned, -> foreign key (pid) references provinces (id) -> ); Query OK, 0 rows affected (0.03 sec) # 显示索引 mysql> show indexes from provinces; +-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | provinces | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | +-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 1 row in set (0.01 sec) # 网格的形式显示索引 mysql> show indexes from provinces\G; *************************** 1. row *************************** Table: provinces 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: 1 row in set (0.00 sec) ERROR: No query specified mysql> show indexes from users\G; *************************** 1. row *************************** Table: users 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: users 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: 2 rows in set (0.00 sec) ERROR: No query specified mysql> show create table users; +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | users | CREATE TABLE `users` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `username` varchar(10) NOT NULL, `pid` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`id`), KEY `pid` (`pid`), CONSTRAINT `users_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `provinces` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec) -
外键约束的参照操作
在创建了外键约束以后 , 表更新 , 子表是否也进行相应的操作
cascade: 从父表删除或更新且自动删除或更新子表中匹配的行
set null: 从父表删除或更新行 , 并设置子表中的外键列为null , 如果使用该选项 , 必须保证子表列没有指定not null
restrict: 拒绝对父表的删除或更新操作
no action: 标准SQL关键字 , 在MySQL中与restrict相同
实例 - cascademysql> create table users1( -> id int unsigned primary key auto_increment, -> username varchar(10) not null, -> pid int unsigned, -> foreign key (pid) references provinces (id) on delete cascade -> ); Query OK, 0 rows affected (0.03 sec) mysql> show create table users1; +--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | users1 | CREATE TABLE `users1` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `username` varchar(10) NOT NULL, `pid` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`id`), KEY `pid` (`pid`), CONSTRAINT `users1_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `provinces` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec) mysql> insert into provinces(pname) values('A'); Query OK, 1 row affected (0.01 sec) mysql> insert into provinces(pname) values('B'); Query OK, 1 row affected (0.00 sec) mysql> insert into provinces(pname) values('C'); Query OK, 1 row affected (0.01 sec) mysql> select * from provinces; +----+-------+ | id | pname | +----+-------+ | 1 | A | | 2 | B | | 3 | C | +----+-------+ 3 rows in set (0.00 sec) mysql> insert into users1(username,pid) values('tom',3); Query OK, 1 row affected (0.00 sec) mysql> insert into users1(username,pid) values('amy',1); Query OK, 1 row affected (0.01 sec) mysql> insert into users1(username,pid) values('mike',3); Query OK, 1 row affected (0.01 sec) mysql> select * from users1; +----+----------+------+ | id | username | pid | +----+----------+------+ | 1 | tom | 3 | | 2 | amy | 1 | | 3 | mike | 3 | +----+----------+------+ 3 rows in set (0.00 sec) mysql> delete from provinces where id=3; Query OK, 1 row affected (0.02 sec) mysql> select * from provinces; +----+-------+ | id | pname | +----+-------+ | 1 | A | | 2 | B | +----+-------+ 2 rows in set (0.00 sec) mysql> select * from users1; +----+----------+------+ | id | username | pid | +----+----------+------+ | 2 | amy | 1 | +----+----------+------+ 1 row in set (0.00 sec)
第四章 修改数据表
4.1 MySQL 添加 / 删除列
-
添加单列
alter table tbname add col_name col_definition [first | after col_name]
若没有为添加列设置first或after属性 , 则默认添加到最后一列mysql> show columns from users1; +----------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | username | varchar(10) | NO | | NULL | | | pid | int(10) unsigned | YES | MUL | NULL | | +----------+------------------+------+-----+---------+----------------+ 3 rows in set (0.01 sec) mysql> alter table users1 add age int unsigned not null default 10; Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show columns from users1; +----------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | username | varchar(10) | NO | | NULL | | | pid | int(10) unsigned | YES | MUL | NULL | | | age | int(10) unsigned | NO | | 10 | | +----------+------------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) mysql> alter table users1 add password varchar(30) not null after username; Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show columns from users1; +----------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | username | varchar(10) | NO | | NULL | | | password | varchar(30) | NO | | NULL | | | pid | int(10) unsigned | YES | MUL | NULL | | | age | int(10) unsigned | NO | | 10 | | +----------+------------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec) mysql> alter table users1 add truename varchar(20) not null first; Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show columns from users1; +----------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+------------------+------+-----+---------+----------------+ | truename | varchar(20) | NO | | NULL | | | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | username | varchar(10) | NO | | NULL | | | password | varchar(30) | NO | | NULL | | | pid | int(10) unsigned | YES | MUL | NULL | | | age | int(10) unsigned | NO | | 10 | | +----------+------------------+------+-----+---------+----------------+ 6 rows in set (0.00 sec) -
添加多列
alter table tbname add (col_name col_definition,....) -
删除列
alter table tbname drop col_namemysql> show columns from users1; +----------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+------------------+------+-----+---------+----------------+ | truename | varchar(20) | NO | | NULL | | | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | username | varchar(10) | NO | | NULL | | | password | varchar(30) | NO | | NULL | | | pid | int(10) unsigned | YES | MUL | NULL | | | age | int(10) unsigned | NO | | 10 | | +----------+------------------+------+-----+---------+----------------+ 6 rows in set (0.00 sec) mysql> alter table users1 drop truename; Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show columns from users1; +----------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | username | varchar(10) | NO | | NULL | | | password | varchar(30) | NO | | NULL | | | pid | int(10) unsigned | YES | MUL | NULL | | | age | int(10) unsigned | NO | | 10 | | +----------+------------------+------+-----+---------+----------------+ mysql> alter table users1 drop password,drop age; Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show columns from users1; +----------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | username | varchar(10) | NO | | NULL | | | pid | int(10) unsigned | YES | MUL | NULL | | +----------+------------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) -
注意 : 若想同时删除一列并添加一列 , 可以用逗号分隔开
例如 :
alter table users1 drop password,add age;
4.2 MySQL 添加约束
-
添加主键约束
alter table tbname add [constraint [symbol]] primary key [index_type] (index_col_name,...)mysql> create table users2( -> username varchar(10) not null, -> pid int unsigned -> ); Query OK, 0 rows affected (0.03 sec) mysql> alter table users2 add id int unsigned; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show columns from users2; +----------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+------------------+------+-----+---------+-------+ | username | varchar(10) | NO | | NULL | | | pid | int(10) unsigned | YES | | NULL | | | id | int(10) unsigned | YES | | NULL | | +----------+------------------+------+-----+---------+-------+ 3 rows in set (0.01 sec) mysql> alter table users2 add constraint pk_users2_id primary key (id); Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show columns from users2; +----------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+------------------+------+-----+---------+-------+ | username | varchar(10) | NO | | NULL | | | pid | int(10) unsigned | YES | | NULL | | | id | int(10) unsigned | NO | PRI | NULL | | +----------+------------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) -
添加唯一约束
alter table tbname add [constraint [symbol]] unique [index | key] [index_name] [index_type] (index_col_name,...)mysql> alter table users2 add unique (username); 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 | int(10) unsigned | YES | | NULL | | | id | int(10) unsigned | NO | PRI | NULL | | +----------+------------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) -
添加外键约束
alter table tbname add [constraint [symbol]] foreign key [index_name] (index_col_name,...) reference_definitionmysql> alter table users2 add foreign key (pid) references provinces (id); Query OK, 0 rows affected (0.03 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` int(10) unsigned DEFAULT NULL, `id` int(10) unsigned NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `username` (`username`), KEY `pid` (`pid`), CONSTRAINT `users2_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `provinces` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) -
添加 / 删除默认约束
alter table tbname alter col_name {set default literal | drop default}mysql> show columns from users2; +----------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+------------------+------+-----+---------+-------+ | username | varchar(10) | NO | UNI | NULL | | | pid | int(10) unsigned | YES | MUL | NULL | | | id | int(10) unsigned | NO | PRI | NULL | | | age | int(10) unsigned | NO | | NULL | | +----------+------------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> alter table users2 alter age set default 15; Query OK, 0 rows affected (0.03 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 | int(10) unsigned | YES | MUL | NULL | | | id | int(10) unsigned | NO | PRI | NULL | | | age | int(10) unsigned | NO | | 15 | | +----------+------------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> alter table users2 alter age drop default; 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 | int(10) unsigned | YES | MUL | NULL | | | id | int(10) unsigned | NO | PRI | NULL | | | age | int(10) unsigned | NO | | NULL | | +----------+------------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
4.3 MySQL 删除约束
-
删除主键约束
alter table tbname drop primary keymysql> show columns from users2; +----------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+------------------+------+-----+---------+-------+ | username | varchar(10) | NO | UNI | NULL | | | pid | int(10) unsigned | YES | MUL | NULL | | | id | int(10) unsigned | NO | PRI | NULL | | | age | int(10) unsigned | NO | | NULL | | +----------+------------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> alter table users2 drop primary key; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show columns from users2; +----------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+------------------+------+-----+---------+-------+ | username | varchar(10) | NO | PRI | NULL | | | pid | int(10) unsigned | YES | MUL | NULL | | | id | int(10) unsigned | NO | | NULL | | | age | int(10) unsigned | NO | | NULL | | +----------+------------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) -
删除唯一约束
alter table tbname drop [index | key] index_namemysql> show columns from users2; +----------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+------------------+------+-----+---------+-------+ | username | varchar(10) | NO | PRI | NULL | | | pid | int(10) unsigned | YES | MUL | NULL | | | id | int(10) unsigned | NO | | NULL | | | age | int(10) unsigned | NO | | NULL | | +----------+------------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> show indexes from users2\G; *************************** 1. row *************************** Table: users2 Non_unique: 0 Key_name: username Seq_in_index: 1 Column_name: username 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: 2 rows in set (0.00 sec) mysql> alter table users2 drop index username; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show columns from users2; +----------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+------------------+------+-----+---------+-------+ | username | varchar(10) | NO | | NULL | | | pid | int(10) unsigned | YES | MUL | NULL | | | id | int(10) unsigned | NO | | NULL | | | age | int(10) unsigned | NO | | NULL | | +----------+------------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> show indexes from users2\G; *************************** 1. 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: 1 row in set (0.00 sec) -
删除外键约束
alter table tbname drop foreign key fk_symbolmysql> show create table users2; +--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | users2 | CREATE TABLE `users2` ( `username` varchar(10) NOT NULL, `pid` int(10) unsigned DEFAULT NULL, `id` int(10) unsigned NOT NULL, `age` int(10) unsigned NOT NULL, KEY `pid` (`pid`), CONSTRAINT `users2_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `provinces` (`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.03 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` int(10) unsigned DEFAULT NULL, `id` int(10) unsigned NOT NULL, `age` int(10) unsigned NOT NULL, KEY `pid` (`pid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) # 删除索引 mysql> alter table users2 drop index pid; Query OK, 0 rows affected (0.02 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` int(10) unsigned DEFAULT NULL, `id` int(10) unsigned NOT NULL, `age` int(10) unsigned NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
4.4 MySQL 修改列定义和更名数据表
-
修改列定义
alter table tbname modify col_name col_definition [first | after col_name]mysql> show create table users2; +--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | users2 | CREATE TABLE `users2` ( `username` varchar(10) NOT NULL, `pid` int(10) unsigned DEFAULT NULL, `id` int(10) unsigned NOT NULL, `age` int(10) unsigned NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> alter table users2 modify id smallint unsigned not null first; Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show columns from users2; +----------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------------------+------+-----+---------+-------+ | id | smallint(5) unsigned | NO | | NULL | | | username | varchar(10) | NO | | NULL | | | pid | int(10) unsigned | YES | | NULL | | | age | int(10) unsigned | NO | | NULL | | +----------+----------------------+------+-----+---------+-------+ 4 rows in set (0.01 sec) mysql> alter table users2 modify id tinyint unsigned not null first; 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 | +----------+---------------------+------+-----+---------+-------+ | id | tinyint(3) unsigned | NO | | NULL | | | username | varchar(10) | NO | | NULL | | | pid | int(10) unsigned | YES | | NULL | | | age | int(10) unsigned | NO | | NULL | | +----------+---------------------+------+-----+---------+-------+ 4 rows in set (0.01 sec) -
修改数据表名称
alter table tablename rename [to | as] newname
rename table tablename to newname [, tablename2 to newname2]...mysql> alter table users2 rename users3; Query OK, 0 rows affected (0.01 sec) mysql> show tables; +--------------+ | Tables_in_t1 | +--------------+ | provinces | | tb1 | | tb2 | | tb3 | | tb4 | | tb5 | | tb6 | | users | | users1 | | users3 | +--------------+ 10 rows in set (0.00 sec) mysql> rename table users3 to users2; Query OK, 0 rows affected (0.00 sec) mysql> show tables; +--------------+ | Tables_in_t1 | +--------------+ | provinces | | tb1 | | tb2 | | tb3 | | tb4 | | tb5 | | tb6 | | users | | users1 | | users2 | +--------------+ 10 rows in set (0.00 sec)
4.5 总结
- 约束
按照功能划分 : not null , primary key , unique key , default , foreign key
按照数据列数目划分 : 表级约束 , 列级约束 - 修改数据表
针对字段操作 : 添加/删除字段 , 修改列定义 , 修改列名称
针对约束操作 : 添加/删除各种约束
针对数据表操作 : 数据表更名(两种方法)
第五章 操作数据表中的记录
5.1 MySQL 插入记录
-
插入insert
insert into tablename [(col_name,...)] {values | value} ({expr | default},...), (),...mysql> create table user ( -> id smallint unsigned primary key auto_increment, -> username varchar(20) not null, -> password varchar(20) not null, -> age tinyint unsigned not null default 10, -> sex boolean -> ); Query OK, 0 rows affected (0.05 sec) mysql> insert into user values(null,'tom','123',25,1); Query OK, 1 row affected (0.01 sec) mysql> select * from user; +----+----------+----------+-----+------+ | id | username | password | age | sex | +----+----------+----------+-----+------+ | 1 | tom | 123 | 25 | 1 | +----+----------+----------+-----+------+ 1 row in set (0.00 sec) mysql> insert into user values(null,'mike','456',25,1); Query OK, 1 row affected (0.00 sec) mysql> select * from user; +----+----------+----------+-----+------+ | id | username | password | age | sex | +----+----------+----------+-----+------+ | 1 | tom | 123 | 25 | 1 | | 2 | mike | 456 | 25 | 1 | +----+----------+----------+-----+------+ 2 rows in set (0.01 sec) mysql> insert into user values(null,'mike','456',25); ERROR 1136 (21S01): Column count doesn't match value count at row 1 mysql> insert into user values(null,'mike','456',3*7,1); Query OK, 1 row affected (0.00 sec) mysql> select * from user; +----+----------+----------+-----+------+ | id | username | password | age | sex | +----+----------+----------+-----+------+ | 1 | tom | 123 | 25 | 1 | | 2 | mike | 456 | 25 | 1 | | 3 | mike | 456 | 21 | 1 | +----+----------+----------+-----+------+ 3 rows in set (0.00 sec) mysql> show columns from user; +----------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+----------------------+------+-----+---------+----------------+ | id | smallint(5) unsigned | NO | PRI | NULL | auto_increment | | username | varchar(20) | NO | | NULL | | | password | varchar(20) | NO | | NULL | | | age | tinyint(3) unsigned | NO | | 10 | | | sex | tinyint(1) | YES | | NULL | | +----------+----------------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec) mysql> insert into user values(null,'mike','456',default,1); Query OK, 1 row affected (0.00 sec) mysql> show columns from user; +----------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+----------------------+------+-----+---------+----------------+ | id | smallint(5) unsigned | NO | PRI | NULL | auto_increment | | username | varchar(20) | NO | | NULL | | | password | varchar(20) | NO | | NULL | | | age | tinyint(3) unsigned | NO | | 10 | | | sex | tinyint(1) | YES | | NULL | | +----------+----------------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec) mysql> select * from user; +----+----------+----------+-----+------+ | id | username | password | age | sex | +----+----------+----------+-----+------+ | 1 | tom | 123 | 25 | 1 | | 2 | mike | 456 | 25 | 1 | | 3 | mike | 456 | 21 | 1 | | 4 | mike | 456 | 10 | 1 | +----+----------+----------+-----+------+ 4 rows in set (0.00 sec) mysql> insert into user values(null,'mike','456',28,1),(null,'sara','789',default,0); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from user; +----+----------+----------+-----+------+ | id | username | password | age | sex | +----+----------+----------+-----+------+ | 1 | tom | 123 | 25 | 1 | | 2 | mike | 456 | 25 | 1 | | 3 | mike | 456 | 21 | 1 | | 4 | mike | 456 | 10 | 1 | | 5 | mike | 456 | 28 | 1 | | 6 | sara | 789 | 10 | 0 | +----+----------+----------+-----+------+ 6 rows in set (0.00 sec) -
插入insert set
与第一中插入方法不同的是 , 这种可以使用子查询
insert into tablename set col_name = {expr | default},...mysql> insert into user set username='ben',password='901'; Query OK, 1 row affected (0.01 sec) mysql> select * from user; +----+----------+----------+-----+------+ | id | username | password | age | sex | +----+----------+----------+-----+------+ | 1 | tom | 123 | 25 | 1 | | 2 | mike | 456 | 25 | 1 | | 3 | mike | 456 | 21 | 1 | | 4 | mike | 456 | 10 | 1 | | 11 | mike | 456 | 28 | 1 | | 12 | sara | 789 | 10 | 0 | | 13 | ben | 901 | 10 | NULL | +----+----------+----------+-----+------+ 7 rows in set (0.00 sec) -
插入select
insert into tablename [(col_name, ....)] select ....mysql> create table test( -> id tinyint unsigned primary key auto_increment, -> username varchar(20) -> ); Query OK, 0 rows affected (0.05 sec) mysql> select * from test; Empty set (0.01 sec) mysql> insert into test(username) select username from user where age>=20; Query OK, 5 rows affected (0.02 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> select * from test; +----+----------+ | id | username | +----+----------+ | 1 | tom | | 2 | mike | | 3 | mike | | 4 | mike | | 5 | mike | +----+----------+ 5 rows in set (0.00 sec)
5.2 MySQL 单表操作记录
-
更新记录update
update [low_priority] [ignore] table_reference set col_name1 = {expr1 | default} [,col_name2 = {expr2 | default}]... [where where_condition]mysql> select * from user; +----+----------+----------+-----+------+ | id | username | password | age | sex | +----+----------+----------+-----+------+ | 1 | tom | 123 | 25 | 1 | | 2 | mike | 456 | 25 | 1 | | 3 | mike | 456 | 21 | 1 | | 4 | mike | 456 | 10 | 1 | | 11 | mike | 456 | 28 | 1 | | 12 | sara | 789 | 10 | 0 | | 13 | ben | 901 | 10 | NULL | +----+----------+----------+-----+------+ 7 rows in set (0.00 sec) mysql> update user set age=age+5; Query OK, 7 rows affected (0.01 sec) Rows matched: 7 Changed: 7 Warnings: 0 mysql> select * from user; +----+----------+----------+-----+------+ | id | username | password | age | sex | +----+----------+----------+-----+------+ | 1 | tom | 123 | 30 | 1 | | 2 | mike | 456 | 30 | 1 | | 3 | mike | 456 | 26 | 1 | | 4 | mike | 456 | 15 | 1 | | 11 | mike | 456 | 33 | 1 | | 12 | sara | 789 | 15 | 0 | | 13 | ben | 901 | 15 | NULL | +----+----------+----------+-----+------+ 7 rows in set (0.00 sec) mysql> update user set age=age-id,sex=0; Query OK, 7 rows affected (0.01 sec) Rows matched: 7 Changed: 7 Warnings: 0 mysql> select * from user; +----+----------+----------+-----+------+ | id | username | password | age | sex | +----+----------+----------+-----+------+ | 1 | tom | 123 | 29 | 0 | | 2 | mike | 456 | 28 | 0 | | 3 | mike | 456 | 23 | 0 | | 4 | mike | 456 | 11 | 0 | | 11 | mike | 456 | 22 | 0 | | 12 | sara | 789 | 3 | 0 | | 13 | ben | 901 | 2 | 0 | +----+----------+----------+-----+------+ 7 rows in set (0.00 sec) mysql> update user set age=age+10 where id%2=0; Query OK, 3 rows affected (0.00 sec) Rows matched: 3 Changed: 3 Warnings: 0 mysql> select * from user; +----+----------+----------+-----+------+ | id | username | password | age | sex | +----+----------+----------+-----+------+ | 1 | tom | 123 | 29 | 0 | | 2 | mike | 456 | 38 | 0 | | 3 | mike | 456 | 23 | 0 | | 4 | mike | 456 | 21 | 0 | | 11 | mike | 456 | 22 | 0 | | 12 | sara | 789 | 13 | 0 | | 13 | ben | 901 | 2 | 0 | +----+----------+----------+-----+------+ 7 rows in set (0.00 sec) -
删除记录delete
delete from tablename [where where_condition]mysql> delete from user where id=13; Query OK, 1 row affected (0.01 sec) mysql> select * from user; +----+----------+----------+-----+------+ | id | username | password | age | sex | +----+----------+----------+-----+------+ | 1 | tom | 123 | 29 | 0 | | 2 | mike | 456 | 38 | 0 | | 3 | mike | 456 | 23 | 0 | | 4 | mike | 456 | 21 | 0 | | 11 | mike | 456 | 22 | 0 | | 12 | sara | 789 | 13 | 0 | +----+----------+----------+-----+------+ 6 rows in set (0.00 sec) mysql> insert into user values(null,'john','109',default,0); Query OK, 1 row affected (0.01 sec) mysql> select * from user; +----+----------+----------+-----+------+ | id | username | password | age | sex | +----+----------+----------+-----+------+ | 1 | tom | 123 | 29 | 0 | | 2 | mike | 456 | 38 | 0 | | 3 | mike | 456 | 23 | 0 | | 4 | mike | 456 | 21 | 0 | | 11 | mike | 456 | 22 | 0 | | 12 | sara | 789 | 13 | 0 | | 14 | john | 109 | 10 | 0 | +----+----------+----------+-----+------+ 7 rows in set (0.00 sec)
5.3 MySQL 查询表达式解析
-
查找记录
-
sel_expr :
每个表达式表示想要的一列 , 必须至少有一个
多个列之间以英文逗号分隔
(*)表示所有列 , tablename.*可以表示命名表的所有列
查询表达式可以使用 [as] alias_name为其赋予别名
别名可用于group by , order by和having的子句mysql> select id,age from user; +----+-----+ | id | age | +----+-----+ | 1 | 29 | | 2 | 38 | | 3 | 23 | | 4 | 21 | | 11 | 22 | | 12 | 13 | | 14 | 10 | +----+-----+ 7 rows in set (0.00 sec) mysql> select username,id from user; +----------+----+ | username | id | +----------+----+ | tom | 1 | | mike | 2 | | mike | 3 | | mike | 4 | | mike | 11 | | sara | 12 | | john | 14 | +----------+----+ 7 rows in set (0.00 sec) mysql> select id as userId,username as uname from user; +--------+-------+ | userId | uname | +--------+-------+ | 1 | tom | | 2 | mike | | 3 | mike | | 4 | mike | | 11 | mike | | 12 | sara | | 14 | john | +--------+-------+ 7 rows in set (0.01 sec) -
where语句进行条件查询
在where语句中可以使用MySQL支持的函数或运算符 -
group by语句对查询结果分组
对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的 , 查出来的列必须在group by后面出现，否则就会报错，或者这个字段出现在聚合函数里面
[group by {col_name | position} [asc | desc],...]mysql> select * from user; +----+----------+----------+-----+------+ | id | username | password | age | sex | +----+----------+----------+-----+------+ | 1 | tom | 123 | 29 | 0 | | 2 | mike | 456 | 38 | 0 | | 3 | mike | 456 | 23 | 0 | | 4 | mike | 456 | 21 | 0 | | 11 | mike | 456 | 22 | 0 | | 12 | sara | 789 | 13 | 0 | | 14 | john | 109 | 10 | 0 | +----+----------+----------+-----+------+ 7 rows in set (0.04 sec) mysql> select sex from user group by sex; +------+ | sex | +------+ | 0 | +------+ 1 row in set (0.02 sec) mysql> select * from user group by sex; ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 't1.user.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by mysql> select sex from user group by 1; +------+ | sex | +------+ | 0 | +------+ 1 row in set (0.00 sec)
注意:1代表select语句中字段的位置是第一个
-
having语句设置分组条件
在采用having语句进行分组条件指定的时候 , 要保证分组条件要么为一个聚合函数 , 要么保证分组条件的字段必须出现在select中 , 否则出现错误 , 同时若having中出现非聚合字段 , group by中叶要对其进行分组mysql> select sex from user group by 1 having age>35; ERROR 1054 (42S22): Unknown column 'age' in 'having clause' mysql> select sex,age from user group by 1 having age>35; ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 't1.user.age' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by mysql> select sex,age from user group by 1,2 having age > 35; +------+-----+ | sex | age | +------+-----+ | 0 | 38 | +------+-----+ 1 row in set (0.00 sec) mysql> select sex from user group by 1 having count(id) >= 1; +------+ | sex | +------+ | 0 | +------+ 1 row in set (0.00 sec) -
order by语句对查询结果排序
[order by {col_name | expr | position} [asc | desc],...]mysql> select * from user order by id desc; +----+----------+----------+-----+------+ | id | username | password | age | sex | +----+----------+----------+-----+------+ | 14 | john | 109 | 10 | 0 | | 12 | sara | 789 | 13 | 0 | | 11 | mike | 456 | 22 | 0 | | 4 | mike | 456 | 21 | 0 | | 3 | mike | 456 | 23 | 0 | | 2 | mike | 456 | 38 | 0 | | 1 | tom | 123 | 29 | 0 | +----+----------+----------+-----+------+ 7 rows in set (0.00 sec) mysql> select * from user order by age; +----+----------+----------+-----+------+ | id | username | password | age | sex | +----+----------+----------+-----+------+ | 14 | john | 109 | 10 | 0 | | 12 | sara | 789 | 13 | 0 | | 4 | mike | 456 | 21 | 0 | | 11 | mike | 456 | 22 | 0 | | 3 | mike | 456 | 23 | 0 | | 1 | tom | 123 | 29 | 0 | | 2 | mike | 456 | 38 | 0 | +----+----------+----------+-----+------+ 7 rows in set (0.00 sec) -
limit语句限制查询数量
[limit {[offset,] row_count | row_count offset offset}]mysql> select * from user limit 2; +----+----------+----------+-----+------+ | id | username | password | age | sex | +----+----------+----------+-----+------+ | 1 | tom | 123 | 29 | 0 | | 2 | mike | 456 | 38 | 0 | +----+----------+----------+-----+------+ 2 rows in set (0.00 sec) mysql> select * from user limit 2,2; +----+----------+----------+-----+------+ | id | username | password | age | sex | +----+----------+----------+-----+------+ | 3 | mike | 456 | 23 | 0 | | 4 | mike | 456 | 21 | 0 | +----+----------+----------+-----+------+ 2 rows in set (0.00 sec) mysql> select * from user order by id desc; +----+----------+----------+-----+------+ | id | username | password | age | sex | +----+----------+----------+-----+------+ | 14 | john | 109 | 10 | 0 | | 12 | sara | 789 | 13 | 0 | | 11 | mike | 456 | 22 | 0 | | 4 | mike | 456 | 21 | 0 | | 3 | mike | 456 | 23 | 0 | | 2 | mike | 456 | 38 | 0 | | 1 | tom | 123 | 29 | 0 | +----+----------+----------+-----+------+ 7 rows in set (0.00 sec) mysql> select * from user order by id desc limit 2,2; +----+----------+----------+-----+------+ | id | username | password | age | sex | +----+----------+----------+-----+------+ | 11 | mike | 456 | 22 | 0 | | 4 | mike | 456 | 21 | 0 | +----+----------+----------+-----+------+ 2 rows in set (0.00 sec)
第六章 子查询与连接
6.1 MySQL 子查询简介
- 子查询(Subquery) : 出现在其他sql语句中的select语句
- 例如 :
SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2)
SELECT * FROM t1 …称为Outer Query[外查询](或者Outer Statement)
SELECT column1 FROM t2 称为Sub Query[子查询]
所以，我们说子查询是嵌套在外查询内部 , 而事实上它有可能在子查询内部再嵌套子查询,子查询必须出现在圆括号之间
6.2 MySQL 由比较运算符引发的子查询
-
比较运算符
< , > , = , >= , <= , <> , != , <=> -
语法结构 :
operand comparison_operator subquery/* 求所有电脑产品的平均价格 */ mysql> select avg(goods_price) from tdb_goods; +------------------+ | avg(goods_price) | +------------------+ | 5391.3043478 | +------------------+ 1 row in set (0.00 sec) /* 求所有电脑产品的平均价格,并且保留两位小数 */ mysql> select round(avg(goods_price),2) from tdb_goods; +---------------------------+ | round(avg(goods_price),2) | +---------------------------+ | 5391.30 | +---------------------------+ 1 row in set (0.01 sec) /* 查询所有价格大于平均价格的商品 */ mysql> select goods_id,goods_name,goods_price from tdb_goods where goods_price >= 5391.30; +----------+-----------------------------------------+-------------+ | goods_id | goods_name | goods_price | +----------+-----------------------------------------+-------------+ | 3 | G150TH 15.6英寸游戏本 | 8499.000 | | 7 | SVP13226SCB 13.3英寸触控超极本 | 7999.000 | | 13 | iMac ME086CH/A 21.5英寸一体电脑 | 9188.000 | | 17 | Mac Pro MD878CH/A 专业级台式电脑 | 28888.000 | | 18 | HMZ-T3W 头戴显示设备 | 6999.000 | | 20 | X3250 M4机架式服务器 2583i14 | 6888.000 | | 21 | HMZ-T3W 头戴显示设备 | 6999.000 | +----------+-----------------------------------------+-------------+ 7 rows in set (0.01 sec) /* 查询所有价格大于平均价格的商品 */ mysql> select goods_id,goods_name,goods_price from tdb_goods where goods_price >= (select round(avg(goods_price),2) from tdb_goods); +----------+-----------------------------------------+-------------+ | goods_id | goods_name | goods_price | +----------+-----------------------------------------+-------------+ | 3 | G150TH 15.6英寸游戏本 | 8499.000 | | 7 | SVP13226SCB 13.3英寸触控超极本 | 7999.000 | | 13 | iMac ME086CH/A 21.5英寸一体电脑 | 9188.000 | | 17 | Mac Pro MD878CH/A 专业级台式电脑 | 28888.000 | | 18 | HMZ-T3W 头戴显示设备 | 6999.000 | | 20 | X3250 M4机架式服务器 2583i14 | 6888.000 | | 21 | HMZ-T3W 头戴显示设备 | 6999.000 | +----------+-----------------------------------------+-------------+ 7 rows in set (0.01 sec) -
用any , some , or修饰的比较运算符
/* 查询类型为“超记本”的商品价格 */ mysql> select goods_price from tdb_goods where goods_cate = '超级本'; +-------------+ | goods_price | +-------------+ | 4999.000 | | 4299.000 | | 7999.000 | +-------------+ 3 rows in set (0.00 sec) /* 查询价格大于"超级本"价格的商品 */ 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 | +----------+-----------------------------------------+-------------+ 10 rows in set (0.00 sec) mysql> select goods_id,goods_name,goods_price from tdb_goods where goods_price > all (select goods_price from tdb_goods where goods_cate='超级本'); +----------+-----------------------------------------+-------------+ | goods_id | goods_name | goods_price | +----------+-----------------------------------------+-------------+ | 3 | G150TH 15.6英寸游戏本 | 8499.000 | | 13 | iMac ME086CH/A 21.5英寸一体电脑 | 9188.000 | | 17 | Mac Pro MD878CH/A 专业级台式电脑 | 28888.000 | +----------+-----------------------------------------+-------------+ 3 rows in set (0.01 sec) 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 | +----------+---------------------------------------+-------------+ 3 rows in set (0.01 sec)
6.3 MySQL 由[not] in / exists引发的子查询
- 使用[not] in的子查询
语法结构 : operand comparison_operator [not] in (subquery)
与=any运算符等价 ; !=all , <>all运算符与not in等价 - 使用[not] exists的子查询
若子查询返回任何行, exists返回true , 否则返回false
6.4 MySQL 使用insert - select插入记录
-
将查询结果写入数据表
insert into tablename [(col_name,...)] select ....mysql> create table if not exists tdb_goods_cates( -> cate_id smallint unsigned primary key auto_increment, -> cate_name varchar(40) not null -> ); Query OK, 0 rows affected (0.03 sec) /* 查询tdb_goods表的所有记录，并且按"类别"分组 */ mysql> select goods_cate from tdb_goods group by goods_cate; +---------------------+ | goods_cate | +---------------------+ | 台式机 | | 平板电脑 | | 服务器/工作站 | | 游戏本 | | 笔记本 | | 笔记本配件 | | 超级本 | +---------------------+ 7 rows in set (0.00 sec) /* 将分组结果写入到tdb_goods_cates数据表 */ mysql> insert into tdb_goods_cates(cate_name) select goods_cate from tdb_goods group by goods_cate; Query OK, 7 rows affected (0.00 sec) Records: 7 Duplicates: 0 Warnings: 0 mysql> select * from tdb_goods_cates; +---------+---------------------+ | cate_id | cate_name | +---------+---------------------+ | 1 | 台式机 | | 2 | 平板电脑 | | 3 | 服务器/工作站 | | 4 | 游戏本 | | 5 | 笔记本 | | 6 | 笔记本配件 | | 7 | 超级本 | +---------+---------------------+ 7 rows in set (0.00 sec)
6.5 MySQL 多表更新
-
update table_reference set col_name1 = {expr1 | default} [,col_name2 = {expr2 | default}]... [where where_condition] -
语法结构
-
连接类型
mysql> select * from tdb_goods_cates; +---------+---------------------+ | cate_id | cate_name | +---------+---------------------+ | 1 | 台式机 | | 2 | 平板电脑 | | 3 | 服务器/工作站 | | 4 | 游戏本 | | 5 | 笔记本 | | 6 | 笔记本配件 | | 7 | 超级本 | +---------+---------------------+ 7 rows in set (0.00 sec) /* 通过tdb_goods_cates数据表来更新tdb_goods表 */ mysql> update tdb_goods inner join tdb_goods_cates on goods_cate = cate_name -> set goods_cate = cate_id; Query OK, 23 rows affected (0.02 sec) Rows matched: 23 Changed: 23 Warnings: 0 mysql> select * from tdb_goods\G; *************************** 1. row *************************** goods_id: 1 goods_name: R510VC 15.6英寸笔记本 goods_cate: 5 brand_name: 华硕 goods_price: 3399.000 is_show: 1 is_saleoff: 0 ... ... *************************** 23. row *************************** goods_id: 23 goods_name: 玄龙精英版 笔记本散热器 goods_cate: 6 brand_name: 九州风神 goods_price: 0.000 is_show: 1 is_saleoff: 0 23 rows in set (0.00 sec) ERROR: No query specified -
多表更新简单实现
创建数据表的同时将数据写入到数据表
create table [if not exists] tablename [(create_definition,...)] select_statementmysql> select brand_name from tdb_goods group by brand_name; +--------------+ | brand_name | +--------------+ | IBM | | 九州风神 | | 华硕 | | 宏碁 | | 惠普 | | 戴尔 | | 索尼 | | 联想 | | 苹果 | | 雷神 | +--------------+ 10 rows in set (0.00 sec) /* 通过CREATE...SELECT来创建数据表并且同时写入记录 */ 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, 10 rows affected (0.03 sec) Records: 10 Duplicates: 0 Warnings: 0 mysql> show tables; +------------------+ | Tables_in_t1 | +------------------+ | tdb_goods | | tdb_goods_brands | | tdb_goods_cates | | user | +------------------+ 4 rows in set (0.00 sec) /* 通过tdb_goods_brands数据表来更新tdb_goods数据表 */ mysql> update tdb_goods as g inner join tdb_goods_brands as b on g.brand_name=b.brand_name -> set g.brand_name = b.brand_id; Query OK, 23 rows affected (0.01 sec) Rows matched: 23 Changed: 23 Warnings: 0 mysql> select * from tdb_goods\G; *************************** 1. row *************************** goods_id: 1 goods_name: R510VC 15.6英寸笔记本 goods_cate: 5 brand_name: 3 goods_price: 3399.000 is_show: 1 is_saleoff: 0 ... ... *************************** 23. row *************************** goods_id: 23 goods_name: 玄龙精英版 笔记本散热器 goods_cate: 6 brand_name: 2 goods_price: 0.000 is_show: 1 is_saleoff: 0 23 rows in set (0.00 sec) ERROR: No query specified -
在更新数据表时 , 同时更新数据表结构
/* 通过ALTER TABLE语句修改数据表结构 */ 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, 23 rows affected (0.04 sec) Records: 23 Duplicates: 0 Warnings: 0 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 | | +-------------+------------------------+------+-----+---------+----------------+ 7 rows in set (0.00 sec)
6.6 MySQL 连接的语法结构
- MySQL中的select , 多表更新 , 多表删除都支持join操作
- 语法结构
- 数据表参照
6.7 MySQL 内连接inner join
-
连接类型
- inner join 内连接
在MySQL中 , join , cross join , inner join是等价的 - left [outer] join 左外连接
- right [outer] join 右外连接
- inner join 内连接
-
使用关键字 on 来设定连接条件 , 也可以使用 where 来代替
通常使用 on 来设定连接条件 , 用 where 关键字进行结果集记录的过滤 -
内连接
显示表 A 与表 B 符合连接条件的记录
/* 查询所有商品的详细信息(通过内连接实现) */ 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 | 玄龙精英版 笔记本散热器 | 笔记本配件 | +----------+--------------------------------------------------------------------------------------+---------------------+ 23 rows in set (0.05 sec)
6.8 MySQL 外连接 outer join
-
左外连接 left [outer] join
显示表 A 全部记录以及表 B 符合连接条件的记录
/* 查询所有商品的详细信息(通过左外连接实现) */ 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; +----------+--------------------------------------------------------------------------------------+---------------------+ | 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 | 玄龙精英版 笔记本散热器 | 笔记本配件 | | 24 | LaserJet Pro P1606dn 黑白激光打印机 | NULL | +----------+--------------------------------------------------------------------------------------+---------------------+ 24 rows in set (0.00 sec) -
右外连接
显示表 B 全部记录以及表 A 符合连接条件的记录
/* 查询所有商品的详细信息(通过右外连接实现) */ mysql> select goods_id,goods_name,cate_name from tdb_goods right 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 | 玄龙精英版 笔记本散热器 | 笔记本配件 | | NULL | NULL | 路由器 | | NULL | NULL | 交换机 | | NULL | NULL | 网卡 | +----------+--------------------------------------------------------------------------------------+---------------------+ 26 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; +----------+--------------------------------------------------------------------------------------+---------------------+--------------+-------------+ | goods_id | goods_name | cate_name | brand_name | goods_price | +----------+--------------------------------------------------------------------------------------+---------------------+--------------+-------------+ | 1 | R510VC 15.6英寸笔记本 | 笔记本 | 华硕 | 3399.000 | | 2 | Y400N 14.0英寸笔记本电脑 | 笔记本 | 联想 | 4899.000 | | 3 | G150TH 15.6英寸游戏本 | 游戏本 | 雷神 | 8499.000 | | 4 | X550CC 15.6英寸笔记本 | 笔记本 | 华硕 | 2799.000 | | 5 | X240(20ALA0EYCD) 12.5英寸超极本 | 超级本 | 联想 | 4999.000 | | 6 | U330P 13.3英寸超极本 | 超级本 | 联想 | 4299.000 | | 7 | SVP13226SCB 13.3英寸触控超极本 | 超级本 | 索尼 | 7999.000 | | 8 | iPad mini MD531CH/A 7.9英寸平板电脑 | 平板电脑 | 苹果 | 1998.000 | | 9 | iPad Air MD788CH/A 9.7英寸平板电脑 （16G WiFi版） | 平板电脑 | 苹果 | 3388.000 | | 10 | iPad mini ME279CH/A 配备 Retina 显示屏 7.9英寸平板电脑 （16G WiFi版） | 平板电脑 | 苹果 | 2788.000 | | 11 | IdeaCentre C340 20英寸一体电脑 | 台式机 | 联想 | 3499.000 | | 12 | Vostro 3800-R1206 台式电脑 | 台式机 | 戴尔 | 2899.000 | | 13 | iMac ME086CH/A 21.5英寸一体电脑 | 台式机 | 苹果 | 9188.000 | | 14 | AT7-7414LP 台式电脑 （i5-3450四核 4G 500G 2G独显 DVD 键鼠 Linux ） | 台式机 | 宏碁 | 3699.000 | | 15 | Z220SFF F4F06PA工作站 | 服务器/工作站 | 惠普 | 4288.000 | | 16 | PowerEdge T110 II服务器 | 服务器/工作站 | 戴尔 | 5388.000 | | 17 | Mac Pro MD878CH/A 专业级台式电脑 | 服务器/工作站 | 苹果 | 28888.000 | | 18 | HMZ-T3W 头戴显示设备 | 笔记本配件 | 索尼 | 6999.000 | | 19 | 商务双肩背包 | 笔记本配件 | 索尼 | 99.000 | | 20 | X3250 M4机架式服务器 2583i14 | 服务器/工作站 | IBM | 6888.000 | | 21 | HMZ-T3W 头戴显示设备 | 笔记本配件 | 索尼 | 6999.000 | | 22 | 商务双肩背包 | 笔记本配件 | 索尼 | 99.000 | | 23 | 玄龙精英版 笔记本散热器 | 笔记本配件 | 九州风神 | 0.000 | +----------+--------------------------------------------------------------------------------------+---------------------+--------------+-------------+ 23 rows in set (0.01 sec)
6.9 MySQL 关于连接的说明
- 外连接
- 内连接
6.10 MySQL 无限级分类表设计
-
自身连接
同一个表对自身进行连接/* 查找所有分类及其父类 */ 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 | 主机 | 电脑配件 | +---------+-----------------+-----------------+ 15 rows in set (0.00 sec)/* 查找所有分类及其子类 */ mysql> select p.type_id,p.type_name,s.type_name from tdb_goods_types as p left join tdb_goods_types s on s.parent_id = p.type_id; +---------+-----------------+--------------+ | type_id | type_name | type_name | +---------+-----------------+--------------+ | 1 | 家用电器 | 大家电 | | 1 | 家用电器 | 生活电器 | | 3 | 大家电 | 平板电视 | | 3 | 大家电 | 空调 | | 4 | 生活电器 | 电风扇 | | 4 | 生活电器 | 饮水机 | | 2 | 电脑、办公 | 电脑整机 | | 2 | 电脑、办公 | 电脑配件 | | 9 | 电脑整机 | 笔记本 | | 9 | 电脑整机 | 超级本 | | 9 | 电脑整机 | 游戏本 | | 10 | 电脑配件 | CPU | | 10 | 电脑配件 | 主机 | | 5 | 平板电视 | NULL | | 6 | 空调 | NULL | | 7 | 电风扇 | NULL | | 8 | 饮水机 | NULL | | 11 | 笔记本 | NULL | | 12 | 超级本 | NULL | | 13 | 游戏本 | NULL | | 14 | CPU | NULL | | 15 | 主机 | NULL | +---------+-----------------+--------------+ 22 rows in set (0.00 sec)/* 查找所有分类及其子类的数目 */ mysql> select p.type_id,p.type_name,count(s.type_name) as 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 | +---------+-----------------+---------------+ 15 rows in set (0.00 sec) -
此处可能会因为group by出现错误 , 改正方法
mysql> set sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
6.11 MySQL 多表删除
-
删除
delete tablename[.*] [, tablename[.*] ]... from table_references where conditionmysql> 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版） | | 24 | 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 | 商务双肩背包 | | 23 | 玄龙精英版 笔记本散热器 | +----------+--------------------------------------------------------------------------------------+ 22 rows in set (0.01 sec) /* 查找重复记录*/ 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.00 sec) /* 删除tdb_goods中goods_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.01 sec) mysql> select * from tdb_goods\G; *************************** 1. row *************************** goods_id: 1 goods_name: R510VC 15.6英寸笔记本 cate_id: 5 brand_id: 3 goods_price: 3399.000 is_show: 1 is_saleoff: 0 ...... *************************** 22. row *************************** goods_id: 24 goods_name: LaserJet Pro P1606dn 黑白激光打印机 cate_id: 12 brand_id: 4 goods_price: 1849.000 is_show: 1 is_saleoff: 0 22 rows in set (0.00 sec) ERROR: No query specified
第七章 运算符和函数
7.1 MySQL 字符函数
-
字符函数
-
每个函数按照上图顺序进行了举例展示
mysql> select * from test; +------------+-----------+ | first_name | last_name | +------------+-----------+ | A | B | | C | D | | tom% | 123 | | NULL | 11 | +------------+-----------+ 4 rows in set (0.00 sec) mysql> select concat(first_name,last_name) as full_name from test; +-----------+ | full_name | +-----------+ | AB | | CD | | tom%123 | | NULL11 | +-----------+ 4 rows in set (0.01 sec) 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(12344.98,1); +--------------------+ | format(12344.98,1) | +--------------------+ | 12,345.0 | +--------------------+ 1 row in set (0.00 sec) mysql> select lower('mySQL'); +----------------+ | lower('mySQL') | +----------------+ | mysql | +----------------+ 1 row in set (0.01 sec) mysql> select upper('mySQL'); +----------------+ | upper('mySQL') | +----------------+ | MYSQL | +----------------+ 1 row in set (0.00 sec) /* 从左向右取字符 */ mysql> select left('mysql',2); +----------------------+ | left('mysql',2) | +----------------------+ | my | +----------------------+ 1 row in set (0.00 sec) /* 从右向左取字符 */ mysql> select right('mysql',1); +-----------------------+ | right('mysql',1) | +-----------------------+ | l | +-----------------------+ 1 row in set (0.00 sec) mysql> select length('mysql'); +-----------------+ | length('mysql') | +-----------------+ | 5 | +-----------------+ 1 row in set (0.01 sec) mysql> select ltrim(' mysql '); +---------------------+ | ltrim(' mysql ') | +---------------------+ | mysql | +---------------------+ 1 row in set (0.00 sec) mysql> select 'mysql'; +-------+ | mysql | +-------+ | mysql | +-------+ 1 row in set (0.00 sec) mysql> select rtrim(' mysql '); +---------------------+ | rtrim(' mysql ') | +---------------------+ | mysql | +---------------------+ 1 row in set (0.00 sec) /* 删除前导问号 */ mysql> select trim(leading '?' from '??mysql???'); +-------------------------------------+ | trim(leading '?' from '??mysql???') | +-------------------------------------+ | mysql??? | +-------------------------------------+ 1 row in set (0.00 sec) /* 删除后续问号 */ mysql> select trim(trailing '?' from '??mysql???'); +--------------------------------------+ | trim(trailing '?' from '??mysql???') | +--------------------------------------+ | ??mysql | +--------------------------------------+ 1 row in set (0.00 sec) /* 删除前导和后续问号，这里注意不能删除字符间的问号*/ mysql> select trim(both '?' from '??mysql???'); +----------------------------------+ | trim(both '?' from '??mysql???') | +----------------------------------+ | mysql | +----------------------------------+ 1 row in set (0.00 sec) /* 可以用空格代替前导和后续的问号，代替字符与被代替字符可以是一对多，多对一 */ mysql> select replace('??my??sql???','?',''); +--------------------------------+ | replace('??my??sql???','?','') | +--------------------------------+ | mysql | +--------------------------------+ 1 row in set (0.00 sec) mysql> select substring('mysql',1,2); +------------------------+ | substring('mysql',1,2) | +------------------------+ | my | +------------------------+ 1 row in set (0.00 sec) mysql> select substring('mysql',3); +----------------------+ | substring('mysql',3) | +----------------------+ | sql | +----------------------+ 1 row in set (0.00 sec) /* 倒数取字符 */ mysql> select substring('mysql',-1); +-----------------------+ | substring('mysql',-1) | +-----------------------+ | l | +-----------------------+ 1 row in set (0.01 sec) /* 这里的 1 代表 true */ mysql> select 'mysql' like 'm%'; +-------------------+ | 'mysql' like 'm%' | +-------------------+ | 1 | +-------------------+ 1 row in set (0.01 sec) mysql> select * from test where first_name like '%o%'; +------------+-----------+ | first_name | last_name | +------------+-----------+ | tom% | 123 | +------------+-----------+ 1 row in set (0.00 sec) /* 此时的第一个和第三个%代表任意字符，中间%代表特定匹配字符，但是并不能识别出各个%的意义，所以数据都被查询出来 */ mysql> select * from test where first_name like '%%%'; +------------+-----------+ | first_name | last_name | +------------+-----------+ | A | B | | C | D | | tom% | 123 | | NULL | 11 | +------------+-----------+ 4 rows in set (0.00 sec) mysql> select * from test where first_name like '%1%%' escape '1'; +------------+-----------+ | first_name | last_name | +------------+-----------+ | tom% | 123 | +------------+-----------+ 1 row in set (0.00 sec)
7.2 MySQL 数值运算符和函数
-
数值运算符函数
-
每个函数按照上图顺序进行了举例展示
mysql> select ceil(3.01); +------------+ | ceil(3.01) | +------------+ | 4 | +------------+ 1 row in set (0.01 sec) mysql> select floor(3.99); +-------------+ | floor(3.99) | +-------------+ | 3 | +-------------+ 1 row in set (0.00 sec) mysql> select 3 div 4; +---------+ | 3 div 4 | +---------+ | 0 | +---------+ 1 row in set (0.00 sec) mysql> select 5 mod 3; +---------+ | 5 mod 3 | +---------+ | 2 | +---------+ 1 row in set (0.00 sec) mysql> select 5.3 mod 3; +-----------+ | 5.3 mod 3 | +-----------+ | 2.3 | +-----------+ 1 row in set (0.00 sec) mysql> select power(3,4); +------------+ | power(3,4) | +------------+ | 81 | +------------+ 1 row in set (0.01 sec) mysql> select round(3.652,1); +----------------+ | round(3.652,1) | +----------------+ | 3.7 | +----------------+ 1 row in set (0.00 sec) mysql> select round(3.652,0); +----------------+ | round(3.652,0) | +----------------+ | 4 | +----------------+ 1 row in set (0.00 sec) mysql> select truncate(125.89,1); +--------------------+ | truncate(125.89,1) | +--------------------+ | 125.8 | +--------------------+ 1 row in set (0.00 sec) mysql> select truncate(125.89,0); +--------------------+ | truncate(125.89,0) | +--------------------+ | 125 | +--------------------+ 1 row in set (0.00 sec) mysql> select truncate(125.89,-1); +---------------------+ | truncate(125.89,-1) | +---------------------+ | 120 | +---------------------+ 1 row in set (0.00 sec)
7.3 MySQL 比较运算符和函数
-
比较运算符函数
-
每个函数按照上图顺序进行了举例展示
mysql> select 15 between 1 and 22; +---------------------+ | 15 between 1 and 22 | +---------------------+ | 1 | +---------------------+ 1 row in set (0.00 sec) mysql> select 15 between 19 and 22; +----------------------+ | 15 between 19 and 22 | +----------------------+ | 0 | +----------------------+ 1 row in set (0.00 sec) mysql> select 10 in(5,10,15); +----------------+ | 10 in(5,10,15) | +----------------+ | 1 | +----------------+ 1 row in set (0.00 sec) mysql> select NULL is NULL; +--------------+ | NULL is NULL | +--------------+ | 1 | +--------------+ 1 row in set (0.00 sec) mysql> select '' is NULL; +------------+ | '' is NULL | +------------+ | 0 | +------------+ 1 row in set (0.00 sec) mysql> select 0 is NULL; +-----------+ | 0 is NULL | +-----------+ | 0 | +-----------+ 1 row in set (0.00 sec) mysql> select * from test where first_name is NULL; +------------+-----------+ | first_name | last_name | +------------+-----------+ | NULL | 11 | +------------+-----------+ 1 row in set (0.00 sec)
7.4 MySQL 日期时间函数
-
日期时间函数
-
每个函数按照上图顺序进行了举例展示
mysql> select now(); +---------------------+ | now() | +---------------------+ | 2019-08-25 10:40:33 | +---------------------+ 1 row in set (0.01 sec) mysql> select curdate(); +------------+ | curdate() | +------------+ | 2019-08-25 | +------------+ 1 row in set (0.00 sec) mysql> select curtime(); +-----------+ | curtime() | +-----------+ | 10:40:57 | +-----------+ 1 row in set (0.00 sec) mysql> select date_add('2019-3-12',interval 365 day); +----------------------------------------+ | date_add('2019-3-12',interval 365 day) | +----------------------------------------+ | 2020-03-11 | +----------------------------------------+ 1 row in set (0.01 sec) mysql> select date_add('2019-3-12',interval -365 day); +-----------------------------------------+ | date_add('2019-3-12',interval -365 day) | +-----------------------------------------+ | 2018-03-12 | +-----------------------------------------+ 1 row in set (0.01 sec) mysql> select date_add('2019-3-12',interval 3 week); +---------------------------------------+ | date_add('2019-3-12',interval 3 week) | +---------------------------------------+ | 2019-04-02 | +---------------------------------------+ 1 row in set (0.00 sec) mysql> select date_add('2019-3-12',interval 3 year); +---------------------------------------+ | date_add('2019-3-12',interval 3 year) | +---------------------------------------+ | 2022-03-12 | +---------------------------------------+ 1 row in set (0.00 sec) mysql> select datediff('2019-3-12','2017-3-12'); +-----------------------------------+ | datediff('2019-3-12','2017-3-12') | +-----------------------------------+ | 730 | +-----------------------------------+ 1 row in set (0.00 sec) mysql> select date_format('2019-3-12','%m/%d//%y'); +--------------------------------------+ | date_format('2019-3-12','%m/%d//%y') | +--------------------------------------+ | 03/12//19 | +--------------------------------------+ 1 row in set (0.00 sec)
7.5 MySQL 信息函数
-
信息函数
-
每个函数按照上图顺序进行了举例展示
mysql> select connection_id(); +-----------------+ | connection_id() | +-----------------+ | 15 | +-----------------+ 1 row in set (0.00 sec) mysql> select database(); +------------+ | database() | +------------+ | t1 | +------------+ 1 row in set (0.00 sec) mysql> desc test; +------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | first_name | varchar(20) | YES | | NULL | | | last_name | varchar(10) | YES | | NULL | | +------------+-------------+------+-----+---------+-------+ 2 rows in set (0.01 sec) mysql> alter table test add id smallint unsigned -> key auto_increment first; Query OK, 0 rows affected (0.08 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc test; +------------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+----------------------+------+-----+---------+----------------+ | id | smallint(5) unsigned | NO | PRI | NULL | auto_increment | | first_name | varchar(20) | YES | | NULL | | | last_name | varchar(10) | YES | | NULL | | +------------+----------------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) mysql> insert into test(first_name,last_name) values('1','2'); Query OK, 1 row affected (0.01 sec) mysql> select last_insert_id(); +------------------+ | last_insert_id() | +------------------+ | 5 | +------------------+ 1 row in set (0.01 sec) mysql> insert into test(first_name,last_name) values('1','2'),('3','4'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select last_insert_id(); +------------------+ | last_insert_id() | +------------------+ | 6 | +------------------+ 1 row in set (0.00 sec) mysql> select user(); +----------------+ | user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec) mysql> select version(); +-----------+ | version() | +-----------+ | 5.7.23 | +-----------+ 1 row in set (0.00 sec)
7.6 MySQL 聚合函数
-
聚合函数
-
每个函数按照上图顺序进行了举例展示
mysql> select avg(goods_price) as avg_price from tdb_goods; +--------------+ | avg_price | +--------------+ | 5397.7727273 | +--------------+ 1 row in set (0.00 sec) mysql> select count(goods_id) as counts from tdb_goods; +--------+ | counts | +--------+ | 22 | +--------+ 1 row in set (0.00 sec) mysql> select min(goods_price) as min from tdb_goods; +--------+ | min | +--------+ | 0.000 | +--------+ 1 row in set (0.01 sec) mysql> select max(goods_price) as max from tdb_goods; +-----------+ | max | +-----------+ | 28888.000 | +-----------+ 1 row in set (0.00 sec) mysql> select sum(goods_price) as sum from tdb_goods; +------------+ | sum | +------------+ | 118751.000 | +------------+ 1 row in set (0.00 sec)
7.7 MySQL 加密函数
-
加密函数
-
每个函数按照上图顺序进行了举例展示
mysql> select md5('admin'); +----------------------------------+ | md5('admin') | +----------------------------------+ | 21232f297a57a5a743894a0e4a801fc3 | +----------------------------------+ 1 row in set (0.01 sec) mysql> select password('admin'); +-------------------------------------------+ | password('admin') | +-------------------------------------------+ | *4ACFE3202A5FF5CF467898FC58AAB1D615029441 | +-------------------------------------------+ 1 row in set, 1 warning (0.00 sec)
本文章参考课程链接
https://www.imooc.com/video/1802
3500
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
