MySQL补充
mysql的工作流程
- MySQL架构总共四层，在上图中以虚线作为划分。
- 首先，最上层的服务并不是MySQL独有的，大多数给予网络的客户端/服务器的工具或者服务都有类似的架构。比如：连接处理、授权认证、安全等。
- 第二层的架构包括大多数的MySQL的核心服务。包括：查询解析、分析、优化、缓存以及所有的内置函数（例如：日期、时间、数学和加密函数）。同时，所有的跨存储引擎的功能都在这一层实现：存储过程、触发器、视图等。
- 第三层包含了存储引擎。存储引擎负责MySQL中数据的存储和提取。服务器通过API和存储引擎进行通信。这些接口屏蔽了不同存储引擎之间的差异，使得这些差异对上层的查询过程透明化。存储引擎API包含十几个底层函数，用于执行“开始一个事务”等操作。但存储引擎一般不会去解析SQL（InnoDB会解析外键定义，因为其本身没有实现该功能），不同存储引擎之间也不会相互通信，而只是简单的响应上层的服务器请求。
- 第四层包含了文件系统，所有的表结构和数据以及用户操作的日志最终还是以文件的形式存储在硬盘上。
表操作
什么是存储引擎
- mysql5.6支持的存储引擎包括InnoDB、MyISAM、MEMORY、CSV、BLACKHOLE、FEDERATED、MRG_MYISAM、ARCHIVE、PERFORMANCE_SCHEMA。其中NDB和InnoDB提供事务安全表，其他存储引擎都是非事务安全表。
- MySQL中的数据用各种不同的技术存储在文件（或者内存）中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。
- 例如，如果你在研究大量的临时数据，你也许需要使用内存存储引擎。内存存储引擎能够在内存中存储所有的表格数据。又或者，你也许需要一个支持事务处理的数据库(以确保事务处理不成功时数据的回退能力)。
- 这些不同的技术以及配套的相关功能在MySQL中被称作存储引擎(也称作表类型)。
- MySQL默认配置了许多不同的存储引擎，可以预先设置或者在MySQL服务器中启用。你可以选择适用于服务器、数据库和表格的存储引擎，以便在选择如何存储你的信息、如何检索这些信息以及你需要你的数据结合什么性能和功能的时候为你提供最大的灵活性。
- 选择如何存储和检索你的数据的这种灵活性是MySQL为什么如此受欢迎的主要原因。其它数据库系统(包括大多数商业选择)仅支持一种类型的数据存储。
- 遗憾的是，其它类型的数据库解决方案采取的“一个尺码满足一切需求”的方式意味着你要么就牺牲一些性能，要么你就用几个小时甚至几天的时间详细调整你的数据库。使用MySQL，我们仅需要修改我们使用的存储引擎就可以了
常用存储引擎及适用场景
InnoDB
用于事务处理应用程序，支持外键和行级锁。如果应用对事物的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包括很多更新和删除操作，那么InnoDB存储引擎是比较合适的。InnoDB除了有效的降低由删除和更新导致的锁定，还可以确保事务的完整提交和回滚，对于类似计费系统或者财务系统等对数据准确要求性比较高的系统都是合适的选择。
MyISAM
如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不高，那么可以选择这个存储引擎。
Memory
将所有的数据保存在内存中，在需要快速定位记录和其他类似数据的环境下，可以提供极快的访问。Memory的缺陷是对表的大小有限制，虽然数据库因为异常终止的话数据可以正常恢复，但是一旦数据库关闭，存储在内存中的数据都会丢失。
InnoDB MySql 5.6 版本默认的存储引擎。InnoDB 是一个事务安全的存储引擎，它具备提交、回滚以及崩溃恢复的功能以保护用户数据。InnoDB 的行级别锁定以及 Oracle 风格的一致性无锁读提升了它的多用户并发数以及性能。InnoDB 将用户数据存储在聚集索引中以减少基于主键的普通查询所带来的 I/O 开销。为了保证数据的完整性，InnoDB 还支持外键约束。 MyISAM MyISAM既不支持事务、也不支持外键、其优势是访问速度快，但是表级别的锁定限制了它在读写负载方面的性能，因此它经常应用于只读或者以读为主的数据场景。 Memory 在内存中存储所有数据，应用于对非关键数据由快速查找的场景。Memory类型的表访问数据非常快，因为它的数据是存放在内存中的，并且默认使用HASH索引，但是一旦服务关闭，表中的数据就会丢失 BLACKHOLE 黑洞存储引擎，类似于 Unix 的 /dev/null，Archive 只接收但却并不保存数据。对这种引擎的表的查询常常返回一个空集。这种表可以应用于 DML 语句需要发送到从服务器，但主服务器并不会保留这种数据的备份的主从配置中。 CSV 它的表真的是以逗号分隔的文本文件。CSV 表允许你以 CSV 格式导入导出数据，以相同的读和写的格式和脚本和应用交互数据。由于 CSV 表没有索引，你最好是在普通操作中将数据放在 InnoDB 表里，只有在导入或导出阶段使用一下 CSV 表。 NDB (又名 NDBCLUSTER)——这种集群数据引擎尤其适合于需要最高程度的正常运行时间和可用性的应用。注意：NDB 存储引擎在标准 MySql 5.6 版本里并不被支持。目前能够支持 MySql 集群的版本有：基于 MySql 5.1 的 MySQL Cluster NDB 7.1；基于 MySql 5.5 的 MySQL Cluster NDB 7.2；基于 MySql 5.6 的 MySQL Cluster NDB 7.3。同样基于 MySql 5.6 的 MySQL Cluster NDB 7.4 目前正处于研发阶段。 Merge 允许 MySql DBA 或开发者将一系列相同的 MyISAM 表进行分组，并把它们作为一个对象进行引用。适用于超大规模数据场景，如数据仓库。 Federated 提供了从多个物理机上联接不同的 MySql 服务器来创建一个逻辑数据库的能力。适用于分布式或者数据市场的场景。 Example 这种存储引擎用以保存阐明如何开始写新的存储引擎的 MySql 源码的例子。它主要针对于有兴趣的开发人员。这种存储引擎就是一个啥事也不做的 "存根"。你可以使用这种引擎创建表，但是你无法向其保存任何数据，也无法从它们检索任何索引。
查看当前的默认存储引擎: mysql> show variables like "default_storage_engine"; 查询当前数据库支持的存储引擎 mysql> show engines \G; 在建表时指定: mysql> create table ai(id bigint(12),name varchar(200)) ENGINE=MyISAM; mysql> create table country(id int(4),cname varchar(50)) ENGINE=InnoDB; 也可以使用alter table语句，修改一个已经存在的表的存储引擎。 mysql> alter table ai engine = innodb; 在配置文件中指定: #my.ini文件 [mysqld] default-storage-engine=INNODB
#进入mysql客户端 $mysql mysql> select user(); #查看当前用户 mysql> exit # 也可以用\q quit退出 # 默认用户登陆之后并没有实际操作的权限 # 需要使用管理员root用户登陆 $ mysql -uroot -p # mysql5.6默认是没有密码的 #遇到password直接按回车键 mysql> set password = password('root'); # 给当前数据库设置密码 # 创建账号 mysql> create user 'eva'@'192.168.10.%' IDENTIFIED BY '123';# 指示网段 mysql> create user 'eva'@'192.168.10.5' # 指示某机器可以连接 mysql> create user 'eva'@'%' #指示所有机器都可以连接 mysql> show grants for 'eva'@'192.168.10.5';查看某个用户的权限 # 远程登陆 $ mysql -uroot -p123 -h 192.168.10.3 # 给账号授权 mysql> grant all on *.* to 'eva'@'%'; mysql> flush privileges; # 刷新使授权立即生效 # 创建账号并授权 mysql> grant all on *.* to 'eva'@'%' identified by '123' ############################################################################# 1. 操作文件夹（库） 增：create database db1 charset utf8; 查：show databases; 改：alter database db1 charset latin1; 删除: drop database db1; 2. 操作文件（表） 先切换到文件夹下：use db1 增：create table t1(id int,name char); 查：show tables; 改：alter table t1 modify name char(3); alter table t1 change name name1 char(2); 删：drop table t1; 3. 操作文件中的内容（记录） 增：insert into t1 values(1,'egon1'),(2,'egon2'),(3,'egon3'); 查：select * from t1; 改：update t1 set name='sb' where id=2; 删：delete from t1 where id=1; 清空表： delete from t1; #如果有自增id，新增的数据，仍然是以删除前的最后一样作为起始。 truncate table t1;数据量大，删除速度比上一条快，且直接从零开始， *auto_increment 表示：自增 *primary key 表示：约束（不能重复且不能为空）；加速查找
mysql> create database staff; Query OK, 1 row affected (0.00 sec) mysql> use staff; Database changed mysql> create table staff_info (id int,name varchar(50),age int(3),sex enum('male','female'),phone bigint(11),job varchar(11)); Query OK, 0 rows affected (0.02 sec) mysql> show tables; +-----------------+ | Tables_in_staff | +-----------------+ | staff_info | +-----------------+ 1 row in set (0.00 sec) mysql> desc staff_info; +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(50) | YES | | NULL | | | age | int(3) | YES | | NULL | | | sex | enum('male','female') | YES | | NULL | | | phone | bigint(11) | YES | | NULL | | | job | varchar(11) | YES | | NULL | | +-------+-----------------------+------+-----+---------+-------+ 6 rows in set (0.00 sec) mysql> select id,name,sex from staff_info; Empty set (0.00 sec) mysql> select * from staff_info; Empty set (0.00 sec)
mysql> insert into staff_info (id,name,age,sex,phone,job) values (1,'Alex',83,'female',13651054608,'IT'); Query OK, 1 row affected (0.00 sec) mysql> insert into staff_info values (2,'Egon',26,'male',13304320533,'Teacher'); Query OK, 1 row affected (0.00 sec) mysql> insert into staff_info values (3,'nezha',25,'male',13332353222,'IT'),(4,'boss_jin',40,'male',13332353333,'IT'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from staff_info; +------+----------+------+--------+-------------+---------+ | id | name | age | sex | phone | job | +------+----------+------+--------+-------------+---------+ | 1 | Alex | 83 | female | 13651054608 | IT | | 2 | Egon | 26 | male | 13304320533 | Teacher | | 3 | nezha | 25 | male | 13332353222 | IT | | 4 | boss_jin | 40 | male | 13332353333 | IT | +------+----------+------+--------+-------------+---------+ 4 rows in set (0.00 sec)
mysql> describe staff_info; +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(50) | YES | | NULL | | | age | int(3) | YES | | NULL | | | sex | enum('male','female') | YES | | NULL | | | phone | bigint(11) | YES | | NULL | | | job | varchar(11) | YES | | NULL | | +-------+-----------------------+------+-----+---------+-------+ 6 rows in set (0.00 sec) mysql> desc staff_info; +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(50) | YES | | NULL | | | age | int(3) | YES | | NULL | | | sex | enum('male','female') | YES | | NULL | | | phone | bigint(11) | YES | | NULL | | | job | varchar(11) | YES | | NULL | | +-------+-----------------------+------+-----+---------+-------+ 6 rows in set (0.00 sec) mysql> show create table staff_info\G; *************************** 1. row *************************** Table: staff_info Create Table: CREATE TABLE `staff_info` ( `id` int(11) DEFAULT NULL, `name` varchar(50) DEFAULT NULL, `age` int(3) DEFAULT NULL, `sex` enum('male','female') DEFAULT NULL, `phone` bigint(11) DEFAULT NULL, `job` varchar(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.01 sec) ERROR: No query specified
mysql支持的数据类型
- 数值类型
- 日期时间类型
- 字符串类型
- ENUM和SET类型
# 创建表一个是默认宽度的int，一个是指定宽度的int(5) mysql> create table t1 (id1 int,id2 int(5)); Query OK, 0 rows affected (0.02 sec) # 像t1中插入数据1，1 mysql> insert into t1 values (1,1); Query OK, 1 row affected (0.01 sec) # 可以看出结果上并没有异常 mysql> select * from t1; +------+------+ | id1 | id2 | +------+------+ | 1 | 1 | +------+------+ 1 row in set (0.00 sec) # 那么当我们插入了比宽度更大的值，会不会发生报错呢？ mysql> insert into t1 values (111111,111111); Query OK, 1 row affected (0.00 sec) # 答案是否定的，id2仍然显示了正确的数值，没有受到宽度限制的影响 mysql> select * from t1; +------------+--------+ | id1 | id2 | +------------+--------+ | 0000000001 | 00001 | | 0000111111 | 111111 | +------------+--------+ 2 rows in set (0.00 sec) # 修改id1字段 给字段添加一个unsigned表示无符号 mysql> alter table t1 modify id1 int unsigned; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc t1; +-------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+-------+ | id1 | int(10) unsigned | YES | | NULL | | | id2 | int(5) | YES | | NULL | | +-------+------------------+------+-----+---------+-------+ 2 rows in set (0.01 sec) # 当给id1添加的数据大于214748364时，可以顺利插入 mysql> insert into t1 values (2147483648,2147483647); Query OK, 1 row affected (0.00 sec) # 当给id2添加的数据大于214748364时，会报错 mysql> insert into t1 values (2147483647,2147483648); ERROR 1264 (22003): Out of range value for column 'id2' at row 1
# 创建表的三个字段分别为float，double和decimal参数表示一共显示5位，小数部分占2位 mysql> create table t2 (id1 float(5,2),id2 double(5,2),id3 decimal(5,2)); Query OK, 0 rows affected (0.02 sec) # 向表中插入1.23，结果正常 mysql> insert into t2 values (1.23,1.23,1.23); Query OK, 1 row affected (0.00 sec) mysql> select * from t2; +------+------+------+ | id1 | id2 | id3 | +------+------+------+ | 1.23 | 1.23 | 1.23 | +------+------+------+ 1 row in set (0.00 sec) # 向表中插入1.234，会发现4都被截断了 mysql> insert into t2 values (1.234,1.234,1.234); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> select * from t2; +------+------+------+ | id1 | id2 | id3 | +------+------+------+ | 1.23 | 1.23 | 1.23 | | 1.23 | 1.23 | 1.23 | +------+------+------+ 2 rows in set (0.00 sec) # 向表中插入1.235发现数据虽然被截断，但是遵循了四舍五入的规则 mysql> insert into t2 values (1.235,1.235,1.235); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> select * from t2; +------+------+------+ | id1 | id2 | id3 | +------+------+------+ | 1.23 | 1.23 | 1.23 | | 1.23 | 1.23 | 1.23 | | 1.24 | 1.24 | 1.24 | +------+------+------+ 3 rows in set (0.00 sec) # 建新表去掉参数约束 mysql> create table t3 (id1 float,id2 double,id3 decimal); Query OK, 0 rows affected (0.02 sec) # 分别插入1.234 mysql> insert into t3 values (1.234,1.234,1.234); Query OK, 1 row affected, 1 warning (0.00 sec) # 发现decimal默认值是(10,0)的整数 mysql> select * from t3; +-------+-------+------+ | id1 | id2 | id3 | +-------+-------+------+ | 1.234 | 1.234 | 1 | +-------+-------+------+ 1 row in set (0.00 sec) # 当对小数位没有约束的时候，输入超长的小数，会发现float和double的区别 mysql> insert into t3 values (1.2355555555555555555,1.2355555555555555555,1.2355555555555555555555); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> select * from t3; +---------+--------------------+------+ | id1 | id2 | id3 | +---------+--------------------+------+ | 1.234 | 1.234 | 1 | | 1.23556 | 1.2355555555555555 | 1 | +---------+--------------------+------+ 2 rows in set (0.00 sec)
mysql> create table t4 (d date,t time,dt datetime); Query OK, 0 rows affected (0.02 sec) mysql> desc t4; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | d | date | YES | | NULL | | | t | time | YES | | NULL | | | dt | datetime | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 3 rows in set (0.01 sec) mysql> insert into t4 values (now(),now(),now()); Query OK, 1 row affected, 1 warning (0.01 sec) mysql> select * from t4; +------------+----------+---------------------+ | d | t | dt | +------------+----------+---------------------+ | 2018-09-21 | 14:51:51 | 2018-09-21 14:51:51 | +------------+----------+---------------------+ 1 row in set (0.00 sec) mysql> insert into t4 values (null,null,null); Query OK, 1 row affected (0.01 sec) mysql> select * from t4; +------------+----------+---------------------+ | d | t | dt | +------------+----------+---------------------+ | 2018-09-21 | 14:51:51 | 2018-09-21 14:51:51 | | NULL | NULL | NULL | +------------+----------+---------------------+ 2 rows in set (0.00 sec)
mysql> create table t5 (id1 timestamp); Query OK, 0 rows affected (0.02 sec) mysql> desc t5; +-------+-----------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------+------+-----+-------------------+-----------------------------+ | id1 | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | +-------+-----------+------+-----+-------------------+-----------------------------+ 1 row in set (0.00 sec) # 插入数据null，会自动插入当前时间的时间 mysql> insert into t5 values (null); Query OK, 1 row affected (0.00 sec) mysql> select * from t5; +---------------------+ | id1 | +---------------------+ | 2018-09-21 14:56:50 | +---------------------+ 1 row in set (0.00 sec) #添加一列 默认值是'0000-00-00 00:00:00' mysql> alter table t5 add id2 timestamp; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table t5 \G; *************************** 1. row *************************** Table: t5 Create Table: CREATE TABLE `t5` ( `id1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `id2` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) ERROR: No query specified # 手动修改新的列默认值为当前时间 mysql> alter table t5 modify id2 timestamp default current_timestamp; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table t5 \G; *************************** 1. row *************************** Table: t5 Create Table: CREATE TABLE `t5` ( `id1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `id2` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) ERROR: No query specified mysql> insert into t5 values (null,null); Query OK, 1 row affected (0.01 sec) mysql> select * from t5; +---------------------+---------------------+ | id1 | id2 | +---------------------+---------------------+ | 2018-09-21 14:56:50 | 0000-00-00 00:00:00 | | 2018-09-21 14:59:31 | 2018-09-21 14:59:31 | +---------------------+---------------------+ 2 rows in set (0.00 sec)
mysql> create table t6 (t1 timestamp); Query OK, 0 rows affected (0.02 sec) mysql> desc t6; +-------+-----------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------+------+-----+-------------------+-----------------------------+ | t1 | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | +-------+-----------+------+-----+-------------------+-----------------------------+ 1 row in set (0.01 sec) mysql> insert into t6 values (19700101080001); Query OK, 1 row affected (0.00 sec) mysql> select * from t6; +---------------------+ | t1 | +---------------------+ | 1970-01-01 08:00:01 | +---------------------+ 1 row in set (0.00 sec) # timestamp时间的下限是19700101080001 mysql> insert into t6 values (19700101080000); ERROR 1292 (22007): Incorrect datetime value: '19700101080000' for column 't1' at row 1 mysql> insert into t6 values ('2038-01-19 11:14:07'); Query OK, 1 row affected (0.00 sec) # timestamp时间的上限是2038-01-19 11:14:07 mysql> insert into t6 values ('2038-01-19 11:14:08'); ERROR 1292 (22007): Incorrect datetime value: '2038-01-19 11:14:08' for column 't1' at row 1 mysql>
mysql> create table t7 (y year); Query OK, 0 rows affected (0.02 sec) mysql> insert into t7 values (2018); Query OK, 1 row affected (0.00 sec) mysql> select * from t7; +------+ | y | +------+ | 2018 | +------+ 1 row in set (0.00 sec)
mysql> create table t8 (dt datetime); Query OK, 0 rows affected (0.01 sec) mysql> insert into t8 values ('2018-9-26 12:20:10'); Query OK, 1 row affected (0.01 sec) mysql> insert into t8 values ('2018/9/26 12+20+10'); Query OK, 1 row affected (0.00 sec) mysql> insert into t8 values ('20180926122010'); Query OK, 1 row affected (0.00 sec) mysql> insert into t8 values (20180926122010); Query OK, 1 row affected (0.00 sec) mysql> select * from t8; +---------------------+ | dt | +---------------------+ | 2018-09-26 12:20:10 | | 2018-09-26 12:20:10 | | 2018-09-26 12:20:10 | | 2018-09-26 12:20:10 | +---------------------+ 4 rows in set (0.00 sec)
mysql> create table t9 (v varchar(4),c char(4)); Query OK, 0 rows affected (0.01 sec) mysql> insert into t9 values ('ab ','ab '); Query OK, 1 row affected (0.00 sec) # 在检索的时候char数据类型会去掉空格 mysql> select * from t9; +------+------+ | v | c | +------+------+ | ab | ab | +------+------+ 1 row in set (0.00 sec) # 来看看对查询结果计算的长度 mysql> select length(v),length(c) from t9; +-----------+-----------+ | length(v) | length(c) | +-----------+-----------+ | 4 | 2 | +-----------+-----------+ 1 row in set (0.00 sec) # 给结果拼上一个加号会更清楚 mysql> select concat(v,'+'),concat(c,'+') from t9; +---------------+---------------+ | concat(v,'+') | concat(c,'+') | +---------------+---------------+ | ab + | ab+ | +---------------+---------------+ 1 row in set (0.00 sec) # 当存储的长度超出定义的长度，会截断 mysql> insert into t9 values ('abcd ','abcd '); Query OK, 1 row affected, 1 warning (0.01 sec) mysql> select * from t9; +------+------+ | v | c | +------+------+ | ab | ab | | abcd | abcd | +------+------+ 2 rows in set (0.00 sec)
mysql> create table t10 (name char(20),gender enum('female','male')); Query OK, 0 rows affected (0.01 sec) # 选择enum('female','male')中的一项作为gender的值，可以正常插入 mysql> insert into t10 values ('nezha','male'); Query OK, 1 row affected (0.00 sec) # 不能同时插入'male,female'两个值，也不能插入不属于'male,female'的值 mysql> insert into t10 values ('nezha','male,female'); ERROR 1265 (01000): Data truncated for column 'gender' at row 1 mysql> create table t11 (name char(20),hobby set('抽烟','喝酒','烫头','翻车')); Query OK, 0 rows affected (0.01 sec) # 可以任意选择set('抽烟','喝酒','烫头','翻车')中的项，并自带去重功能 mysql> insert into t11 values ('yuan','烫头,喝酒,烫头'); Query OK, 1 row affected (0.01 sec) mysql> select * from t11; +------+---------------+ | name | hobby | +------+---------------+ | yuan | 喝酒,烫头 | +------+---------------+ 1 row in set (0.00 sec) # 不能选择不属于set('抽烟','喝酒','烫头','翻车')中的项， mysql> insert into t11 values ('alex','烫头,翻车,看妹子'); ERROR 1265 (01000): Data truncated for column 'hobby' at row 1
mysql表的完整性约束
- unsigned表示无符号
- NOT NULL ：非空约束，指定某列不能为空；
- UNIQUE : 唯一约束，指定某列或者几列组合不能重复
- PRIMARY KEY ：主键，指定该列的值可以唯一地标识该列记录
- FOREIGN KEY ：外键，指定该行记录从属于主表中的一条记录，主要用于参照完整性
mysql> create table t12 (id int not null); Query OK, 0 rows affected (0.02 sec) mysql> select * from t12; Empty set (0.00 sec) mysql> desc t12; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | +-------+---------+------+-----+---------+-------+ 1 row in set (0.00 sec) #不能向id列插入空元素。 mysql> insert into t12 values (null); ERROR 1048 (23000): Column 'id' cannot be null mysql> insert into t12 values (1); Query OK, 1 row affected (0.01 sec)
mysql> create table t13 (id1 int not null,id2 int not null default 222); Query OK, 0 rows affected (0.01 sec) mysql> desc t13; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id1 | int(11) | NO | | NULL | | | id2 | int(11) | NO | | 222 | | +-------+---------+------+-----+---------+-------+ 2 rows in set (0.01 sec) # 只向id1字段添加值，会发现id2字段会使用默认值填充 mysql> insert into t13 (id1) values (111); Query OK, 1 row affected (0.00 sec) mysql> select * from t13; +-----+-----+ | id1 | id2 | +-----+-----+ | 111 | 222 | +-----+-----+ 1 row in set (0.00 sec) # id1字段不能为空，所以不能单独向id2字段填充值； mysql> insert into t13 (id2) values (223); ERROR 1364 (HY000): Field 'id1' doesn't have a default value # 向id1，id2中分别填充数据，id2的填充数据会覆盖默认值 mysql> insert into t13 (id1,id2) values (112,223); Query OK, 1 row affected (0.00 sec) mysql> select * from t13; +-----+-----+ | id1 | id2 | +-----+-----+ | 111 | 222 | | 112 | 223 | +-----+-----+ 2 rows in set (0.00 sec)
设置严格模式： 不支持对not null字段插入null值 不支持对自增长字段插入”值 不支持text字段有默认值 直接在mysql中生效(重启失效): mysql>set sql_mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"; 配置文件添加(永久失效)： sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
方法一： create table department1( id int, name varchar(20) unique, comment varchar(100) ); 方法二： create table department2( id int, name varchar(20), comment varchar(100), unique(name) ); mysql> insert into department1 values(1,'IT','技术'); Query OK, 1 row affected (0.00 sec) mysql> insert into department1 values(1,'IT','技术'); ERROR 1062 (23000): Duplicate entry 'IT' for key 'name'
mysql> create table t1(id int not null unique); Query OK, 0 rows affected (0.02 sec) mysql> desc t1; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | +-------+---------+------+-----+---------+-------+ 1 row in set (0.00 sec)
create table service( id int primary key auto_increment, name varchar(20), host varchar(15) not null, port int not null, unique(host,port) #联合唯一 ); mysql> insert into service values -> (1,'nginx','192.168.0.10',80), -> (2,'haproxy','192.168.0.20',80), -> (3,'mysql','192.168.0.30',3306) -> ; Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> insert into service(name,host,port) values('nginx','192.168.0.10',80); ERROR 1062 (23000): Duplicate entry '192.168.0.10-80' for key 'host'
============单列做主键=============== #方法一：not null+unique create table department1( id int not null unique, #主键 name varchar(20) not null unique, comment varchar(100) ); mysql> desc department1; +---------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(20) | NO | UNI | NULL | | | comment | varchar(100) | YES | | NULL | | +---------+--------------+------+-----+---------+-------+ rows in set (0.01 sec) #方法二：在某一个字段后用primary key create table department2( id int primary key, #主键 name varchar(20), comment varchar(100) ); mysql> desc department2; +---------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(20) | YES | | NULL | | | comment | varchar(100) | YES | | NULL | | +---------+--------------+------+-----+---------+-------+ rows in set (0.00 sec) #方法三：在所有字段后单独定义primary key create table department3( id int, name varchar(20), comment varchar(100), primary key(id); #创建主键并为其命名pk_name mysql> desc department3; +---------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(20) | YES | | NULL | | | comment | varchar(100) | YES | | NULL | | +---------+--------------+------+-----+---------+-------+ rows in set (0.01 sec) # 方法四：给已经建成的表添加主键约束 mysql> create table department4( -> id int, -> name varchar(20), -> comment varchar(100)); Query OK, 0 rows affected (0.01 sec) mysql> desc department4; +---------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | comment | varchar(100) | YES | | NULL | | +---------+--------------+------+-----+---------+-------+ 3 rows in set (0.01 sec) mysql> alter table department4 modify id int primary key; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc department4; +---------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(20) | YES | | NULL | | | comment | varchar(100) | YES | | NULL | | +---------+--------------+------+-----+---------+-------+ 3 rows in set (0.01 sec)
==================多列做主键================ create table service( ip varchar(15), port char(5), service_name varchar(10) not null, primary key(ip,port) ); mysql> desc service; +--------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+-------+ | ip | varchar(15) | NO | PRI | NULL | | | port | char(5) | NO | PRI | NULL | | | service_name | varchar(10) | NO | | NULL | | +--------------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> insert into service values -> ('172.16.45.10','3306','mysqld'), -> ('172.16.45.11','3306','mariadb') -> ; Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> insert into service values ('172.16.45.10','3306','nginx'); ERROR 1062 (23000): Duplicate entry '172.16.45.10-3306' for key 'PRIMARY'
#不指定id，则自动增长 create table student( id int primary key auto_increment, name varchar(20), sex enum('male','female') default 'male' ); mysql> desc student; +-------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | | sex | enum('male','female') | YES | | male | | +-------+-----------------------+------+-----+---------+----------------+ mysql> insert into student(name) values -> ('egon'), -> ('alex') -> ; mysql> select * from student; +----+------+------+ | id | name | sex | +----+------+------+ | 1 | egon | male | | 2 | alex | male | +----+------+------+ #也可以指定id mysql> insert into student values(4,'asb','female'); Query OK, 1 row affected (0.00 sec) mysql> insert into student values(7,'wsb','female'); Query OK, 1 row affected (0.00 sec) mysql> select * from student; +----+------+--------+ | id | name | sex | +----+------+--------+ | 1 | egon | male | | 2 | alex | male | | 4 | asb | female | | 7 | wsb | female | +----+------+--------+ #对于自增的字段，在用delete删除后，再插入值，该字段仍按照删除前的位置继续增长 mysql> delete from student; Query OK, 4 rows affected (0.00 sec) mysql> select * from student; Empty set (0.00 sec) mysql> insert into student(name) values('ysb'); mysql> select * from student; +----+------+------+ | id | name | sex | +----+------+------+ | 8 | ysb | male | +----+------+------+ #应该用truncate清空表，比起delete一条一条地删除记录，truncate是直接清空表，在删除大表时用它 mysql> truncate student; Query OK, 0 rows affected (0.01 sec) mysql> insert into student(name) values('egon'); Query OK, 1 row affected (0.01 sec) mysql> select * from student; +----+------+------+ | id | name | sex | +----+------+------+ | 1 | egon | male | +----+------+------+ row in set (0.00 sec)
#在创建完表后，修改自增字段的起始值 mysql> create table student( -> id int primary key auto_increment, -> name varchar(20), -> sex enum('male','female') default 'male' -> ); mysql> alter table student auto_increment=3; mysql> show create table student; ....... ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 mysql> insert into student(name) values('egon'); Query OK, 1 row affected (0.01 sec) mysql> select * from student; +----+------+------+ | id | name | sex | +----+------+------+ | 3 | egon | male | +----+------+------+ row in set (0.00 sec) mysql> show create table student; ....... ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 #也可以创建表时指定auto_increment的初始值，注意初始值的设置为表选项，应该放到括号外 create table student( id int primary key auto_increment, name varchar(20), sex enum('male','female') default 'male' )auto_increment=3; #设置步长 sqlserver：自增步长 基于表级别 create table t1（ id int。。。 ）engine=innodb,auto_increment=2 步长=2 default charset=utf8 mysql自增的步长： show session variables like 'auto_inc%'; #基于会话级别 set session auth_increment_increment=2 #修改会话级别的步长 #基于全局级别的 set global auth_increment_increment=2 #修改全局级别的步长（所有会话都生效） #！！！注意了注意了注意了！！！ If the value of auto_increment_offset is greater than that of auto_increment_increment, the value of auto_increment_offset is ignored. 翻译：如果auto_increment_offset的值大于auto_increment_increment的值，则auto_increment_offset的值会被忽略 ，这相当于第一步步子就迈大了，扯着了蛋 比如：设置auto_increment_offset=3，auto_increment_increment=2 mysql> set global auto_increment_increment=5; Query OK, 0 rows affected (0.00 sec) mysql> set global auto_increment_offset=3; Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'auto_incre%'; #需要退出重新登录 +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | auto_increment_increment | 1 | | auto_increment_offset | 1 | +--------------------------+-------+ create table student( id int primary key auto_increment, name varchar(20), sex enum('male','female') default 'male' ); mysql> insert into student(name) values('egon1'),('egon2'),('egon3'); mysql> select * from student; +----+-------+------+ | id | name | sex | +----+-------+------+ | 3 | egon1 | male | | 8 | egon2 | male | | 13 | egon3 | male | +----+-------+------+ 步长:auto_increment_increment,起始偏移量:auto_increment_offset
mysql> create table departments (dep_id int(4),dep_name varchar(11)); Query OK, 0 rows affected (0.02 sec) mysql> desc departments; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | dep_id | int(4) | YES | | NULL | | | dep_name | varchar(11) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) # 创建外键不成功 mysql> create table staff_info (s_id int,name varchar(20),dep_id int,foreign key(dep_id) references departments(dep_id)); ERROR 1215 (HY000): Cannot add foreign key # 设置dep_id非空，仍然不能成功创建外键 mysql> alter table departments modify dep_id int(4) not null; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc departments; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | dep_id | int(4) | NO | | NULL | | | dep_name | varchar(11) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> create table staff_info (s_id int,name varchar(20),dep_id int,foreign key(dep_id) references departments(dep_id)); ERROR 1215 (HY000): Cannot add foreign key constraint # 当设置字段为unique唯一字段时，设置该字段为外键成功 mysql> alter table departments modify dep_id int(4) unique; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc departments; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | dep_id | int(4) | YES | UNI | NULL | | | dep_name | varchar(11) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ 2 rows in set (0.01 sec) mysql> create table staff_info (s_id int,name varchar(20),dep_id int,foreign key(dep_id) references departments(dep_id)); Query OK, 0 rows affected (0.02 sec)
#表类型必须是innodb存储引擎，且被关联的字段，即references指定的另外一个表的字段，必须保证唯一 create table department( id int primary key, name varchar(20) not null )engine=innodb; #dpt_id外键，关联父表（department主键id），同步更新，同步删除 create table employee( id int primary key, name varchar(20) not null, dpt_id int, foreign key(dpt_id) references department(id) on delete cascade # 级连删除 on update cascade # 级连更新 )engine=innodb; #先往父表department中插入记录 insert into department values (1,'教质部'), (2,'技术部'), (3,'人力资源部'); #再往子表employee中插入记录 insert into employee values (1,'yuan',1), (2,'nezha',2), (3,'egon',2), (4,'alex',2), (5,'wusir',3), (6,'李沁洋',3), (7,'皮卡丘',3), (8,'程咬金',3), (9,'程咬银',3) ; #删父表department，子表employee中对应的记录跟着删 mysql> delete from department where id=2; Query OK, 1 row affected (0.00 sec) mysql> select * from employee; +----+-----------+--------+ | id | name | dpt_id | +----+-----------+--------+ | 1 | yuan | 1 | | 5 | wusir | 3 | | 6 | 李沁洋 | 3 | | 7 | 皮卡丘 | 3 | | 8 | 程咬金 | 3 | | 9 | 程咬银 | 3 | +----+-----------+--------+ 6 rows in set (0.00 sec) #更新父表department，子表employee中对应的记录跟着改 mysql> update department set id=2 where id=3; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from employee; +----+-----------+--------+ | id | name | dpt_id | +----+-----------+--------+ | 1 | yuan | 1 | | 5 | wusir | 2 | | 6 | 李沁洋 | 2 | | 7 | 皮卡丘 | 2 | | 8 | 程咬金 | 2 | | 9 | 程咬银 | 2 | +----+-----------+--------+ 6 rows in set (0.00 sec)
. cascade方式 在父表上update/delete记录时，同步update/delete掉子表的匹配记录 . set null方式 在父表上update/delete记录时，将子表上匹配记录的列设为null 要注意子表的外键列不能为not null . No action方式 如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作 . Restrict方式 同no action, 都是立即检查外键约束 . Set default方式 父表有变更时,子表将外键列设置成一个默认的值 但Innodb不能识别
修改表结构
语法： 1. 修改表名 ALTER TABLE 表名 RENAME 新表名; 2. 增加字段 ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…], ADD 字段名 数据类型 [完整性约束条件…]; 3. 删除字段 ALTER TABLE 表名 DROP 字段名; 4. 修改字段 ALTER TABLE 表名 MODIFY 字段名 数据类型 [完整性约束条件…]; ALTER TABLE 表名 CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…]; ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…]; 5.修改字段排列顺序/在增加的时候指定字段位置 ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…] FIRST; ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名; ALTER TABLE 表名 CHANGE 字段名 旧字段名 新字段名 新数据类型 [完整性约束条件…] FIRST; ALTER TABLE 表名 MODIFY 字段名 数据类型 [完整性约束条件…] AFTER 字段名; 6.删除表：DROP TABLE 表名;
create table t(id int unique,name char(10) not null); #去掉null约束 alter table t modify name char(10) null; # 添加null约束 alter table t modify name char(10) not null; # 去掉unique约束 alter table t drop index id; # 添加unique约束 alter table t modify id int unique; alter处理null和unique约束
1、首先创建一个数据表table_test： create table table_test( `id` varchar(100) NOT NULL, `name` varchar(100) NOT NULL, PRIMARY KEY (`name`) ); 2、如果发现主键设置错了,应该是id是主键,但如今表里已经有好多数据了,不能删除表再重建了，仅仅能在这基础上改动表结构。 先删除主键 alter table table_test drop primary key; 然后再增加主键 alter table table_test add primary key(id); 注:在增加主键之前,必须先把反复的id删除掉。
为表添加外键（了解）
mysql> desc staff_info; +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(50) | YES | | NULL | | | age | int(3) | YES | | NULL | | | sex | enum('male','female') | YES | | NULL | | | phone | bigint(11) | YES | | NULL | | | job | varchar(11) | YES | | NULL | | +-------+-----------------------+------+-----+---------+-------+ 6 rows in set (0.00 sec) # 表重命名 mysql> alter table staff_info rename staff; Query OK, 0 rows affected (0.00 sec) mysql> desc staff; +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(50) | YES | | NULL | | | age | int(3) | YES | | NULL | | | sex | enum('male','female') | YES | | NULL | | | phone | bigint(11) | YES | | NULL | | | job | varchar(11) | YES | | NULL | | +-------+-----------------------+------+-----+---------+-------+ 6 rows in set (0.00 sec) # 删除sex列 mysql> alter table staff drop sex; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc staff; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(50) | YES | | NULL | | | age | int(3) | YES | | NULL | | | phone | bigint(11) | YES | | NULL | | | job | varchar(11) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 5 rows in set (0.01 sec) # 添加列 mysql> alter table staff add sex enum('male','female'); Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 # 修改id的宽度 mysql> alter table staff modify id int(4); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc staff; +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(4) | YES | | NULL | | | name | varchar(50) | YES | | NULL | | | age | int(3) | YES | | NULL | | | phone | bigint(11) | YES | | NULL | | | job | varchar(11) | YES | | NULL | | | sex | enum('male','female') | YES | | NULL | | +-------+-----------------------+------+-----+---------+-------+ 6 rows in set (0.01 sec) # 修改name列的字段名 mysql> alter table staff change name sname varchar(20); Query OK, 4 rows affected (0.03 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> desc staff; +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(4) | YES | | NULL | | | sname | varchar(20) | YES | | NULL | | | age | int(3) | YES | | NULL | | | phone | bigint(11) | YES | | NULL | | | job | varchar(11) | YES | | NULL | | | sex | enum('male','female') | YES | | NULL | | +-------+-----------------------+------+-----+---------+-------+ 6 rows in set (0.00 sec) # 修改sex列的位置 mysql> alter table staff modify sex enum('male','female') after sname; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc staff; +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(4) | YES | | NULL | | | sname | varchar(20) | YES | | NULL | | | sex | enum('male','female') | YES | | NULL | | | age | int(3) | YES | | NULL | | | phone | bigint(11) | YES | | NULL | | | job | varchar(11) | YES | | NULL | | +-------+-----------------------+------+-----+---------+-------+ 6 rows in set (0.00 sec) # 创建自增id主键 mysql> alter table staff modify id int(4) primary key auto_increment; Query OK, 4 rows affected (0.02 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> desc staff; +-------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+----------------+ | id | int(4) | NO | PRI | NULL | auto_increment | | sname | varchar(20) | YES | | NULL | | | sex | enum('male','female') | YES | | NULL | | | age | int(3) | YES | | NULL | | | phone | bigint(11) | YES | | NULL | | | job | varchar(11) | YES | | NULL | | +-------+-----------------------+------+-----+---------+----------------+ 6 rows in set (0.00 sec) # 删除主键，可以看到删除一个自增主键会报错 mysql> alter table staff drop primary key; ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key # 需要先去掉主键的自增约束，然后再删除主键约束 mysql> alter table staff modify id int(11); Query OK, 4 rows affected (0.02 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> desc staff; +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | 0 | | | sname | varchar(20) | YES | | NULL | | | sex | enum('male','female') | YES | | NULL | | | age | int(3) | YES | | NULL | | | phone | bigint(11) | YES | | NULL | | | job | varchar(11) | YES | | NULL | | +-------+-----------------------+------+-----+---------+-------+ 6 rows in set (0.01 sec) mysql> alter table staff drop primary key; Query OK, 4 rows affected (0.06 sec) Records: 4 Duplicates: 0 Warnings: 0 # 添加联合主键 mysql> alter table staff add primary key (sname,age); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 # 删除主键 mysql> alter table staff drop primary key; Query OK, 4 rows affected (0.02 sec) Records: 4 Duplicates: 0 Warnings: 0 # 创建主键id mysql> alter table staff add primary key (id); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc staff; +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | 0 | | | sname | varchar(20) | NO | | | | | sex | enum('male','female') | YES | | NULL | | | age | int(3) | NO | | 0 | | | phone | bigint(11) | YES | | NULL | | | job | varchar(11) | YES | | NULL | | +-------+-----------------------+------+-----+---------+-------+ 6 rows in set (0.00 sec) # 为主键添加自增属性 mysql> alter table staff modify id int(4) auto_increment; Query OK, 4 rows affected (0.02 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> desc staff; +-------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+----------------+ | id | int(4) | NO | PRI | NULL | auto_increment | | sname | varchar(20) | NO | | | | | sex | enum('male','female') | YES | | NULL | | | age | int(3) | NO | | 0 | | | phone | bigint(11) | YES | | NULL | | | job | varchar(11) | YES | | NULL | | +-------+-----------------------+------+-----+---------+----------------+ 6 rows in set (0.00 sec)
=====================多对一===================== create table press( id int primary key auto_increment, name varchar(20) ); create table book( id int primary key auto_increment, name varchar(20), press_id int not null, foreign key(press_id) references press(id) on delete cascade on update cascade ); insert into press(name) values ('北京工业地雷出版社'), ('人民音乐不好听出版社'), ('知识产权没有用出版社') ; insert into book(name,press_id) values ('九阳神功',1), ('九阴真经',2), ('九阴白骨爪',2), ('独孤九剑',3), ('降龙十巴掌',2), ('葵花宝典',3) ; =====================多对多===================== create table author( id int primary key auto_increment, name varchar(20) ); #这张表就存放作者表与书表的关系，即查询二者的关系查这表就可以了 create table author2book( id int not null unique auto_increment, author_id int not null, book_id int not null, constraint fk_author foreign key(author_id) references author(id) on delete cascade on update cascade, constraint fk_book foreign key(book_id) references book(id) on delete cascade on update cascade, primary key(author_id,book_id) ); #插入四个作者，id依次排开 insert into author(name) values('egon'),('alex'),('yuanhao'),('wpq'); #每个作者与自己的代表作如下 egon: 九阳神功 九阴真经 九阴白骨爪 独孤九剑 降龙十巴掌 葵花宝典 alex: 九阳神功 葵花宝典 yuanhao: 独孤九剑 降龙十巴掌 葵花宝典 wpq: 九阳神功 insert into author2book(author_id,book_id) values (1,1), (1,2), (1,3), (1,4), (1,5), (1,6), (2,1), (2,6), (3,4), (3,5), (3,6), (4,1) ; =====================一对一===================== create table customer( id int primary key auto_increment, name varchar(20) not null, qq varchar(10) not null, phone char(16) not null ); create table student( id int primary key auto_increment, class_name varchar(20) not null, customer_id int unique, #该字段一定要是唯一的 foreign key(customer_id) references customer(id) #外键的字段一定要保证unique on delete cascade on update cascade ); #增加客户 insert into customer(name,qq,phone) values ('韩蕾','31811231',13811341220), ('杨澜','123123123',15213146809), ('翁惠天','283818181',1867141331), ('杨宗河','283818181',1851143312), ('袁承明','888818181',1861243314), ('袁清','112312312',18811431230) insert into student(class_name,customer_id) values ('脱产1班',3), ('周末1期',4), ('周末1期',5) ;
记录操作
单表操作
建表和数据准备
SELECT DISTINCT 字段1,字段2... FROM 表名 WHERE 条件 GROUP BY field HAVING 筛选 ORDER BY field LIMIT 限制条数 关键字执行的优先级： 1.找到表:from 2.拿着where指定的约束条件，去文件/表中取出一条条记录 3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组 4.执行select（去重） 5.将分组的结果进行having过滤 6.将结果按条件排序：order by 7.限制结果的显示条数
#简单查询 SELECT id,emp_name,sex,age,hire_date,post,post_comment,salary,office,depart_id FROM employee; SELECT * FROM employee; SELECT emp_name,salary FROM employee; #避免重复DISTINCT SELECT DISTINCT post FROM employee; #通过四则运算查询 SELECT emp_name, salary*12 FROM employee; SELECT emp_name, salary*12 AS Annual_salary FROM employee; SELECT emp_name, salary*12 Annual_salary FROM employee; #定义显示格式 CONCAT() 函数用于连接字符串 SELECT CONCAT('姓名: ',emp_name,' 年薪: ', salary*12) AS Annual_salary FROM employee; CONCAT_WS() 第一个参数为分隔符 SELECT CONCAT_WS(':',emp_name,salary*12) AS Annual_salary FROM employee; 结合CASE语句： SELECT ( CASE WHEN emp_name = 'jingliyang' THEN emp_name WHEN emp_name = 'alex' THEN CONCAT(emp_name,'_BIGSB') ELSE concat(emp_name, 'SB') END ) as new_name FROM employee; 1 查出所有员工的名字，薪资,格式为 <名字:egon> <薪资:3000> 2 查出所有的岗位（去掉重复） 3 查出所有员工名字，以及他们的年薪,年薪的字段名为annual_year select concat('<名字:',emp_name,'> ','<薪资:',salary,'>') from employee; select distinct depart_id from employee; select emp_name,salary*12 annual_salary from employee;
where字句中可以使用： 1. 比较运算符：> < >= <= <> != 2. between 80 and 100 值在80到100之间 3. in(80,90,100) 值是80或90或100 4. like 'e%' 通配符可以是%或_， %表示任意多字符 _表示一个字符 5. 逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not #1:单条件查询 SELECT emp_name FROM employee WHERE post='sale'; #2:多条件查询 SELECT emp_name,salary FROM employee WHERE post='teacher' AND salary>10000; #3:关键字BETWEEN AND SELECT emp_name,salary FROM employee WHERE salary BETWEEN 10000 AND 20000; SELECT emp_name,salary FROM employee WHERE salary NOT BETWEEN 10000 AND 20000; #4:关键字IS NULL(判断某个字段是否为NULL不能用等号，需要用IS) SELECT emp_name,post_comment FROM employee WHERE post_comment IS NULL; SELECT emp_name,post_comment FROM employee WHERE post_comment IS NOT NULL; SELECT emp_name,post_comment FROM employee WHERE post_comment=''; 注意''是空字符串，不是null ps： 执行 update employee set post_comment='' where id=2; 再用上条查看，就会有结果了 #5:关键字IN集合查询 SELECT emp_name,salary FROM employee WHERE salary=3000 OR salary=3500 OR salary=4000 OR salary=9000 ; SELECT emp_name,salary FROM employee WHERE salary IN (3000,3500,4000,9000) ; SELECT emp_name,salary FROM employee WHERE salary NOT IN (3000,3500,4000,9000) ; #6:关键字LIKE模糊查询 通配符’%’ SELECT * FROM employee WHERE emp_name LIKE 'eg%'; 通配符’_’ SELECT * FROM employee WHERE emp_name LIKE 'al__'; 1. 查看岗位是teacher的员工姓名、年龄 2. 查看岗位是teacher且年龄大于30岁的员工姓名、年龄 3. 查看岗位是teacher且薪资在9000-10000范围内的员工姓名、年龄、薪资 4. 查看岗位描述不为NULL的员工信息 5. 查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资 6. 查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资 7. 查看岗位是teacher且名字是jin开头的员工姓名、年薪 select emp_name,age from employee where post = 'teacher'; select emp_name,age from employee where post='teacher' and age > 30; select emp_name,age,salary from employee where post='teacher' and salary between 9000 and 10000; select * from employee where post_comment is not null; select emp_name,age,salary from employee where post='teacher' and salary in (10000,9000,30000); select emp_name,age,salary from employee where post='teacher' and salary not in (10000,9000,30000); select emp_name,salary*12 from employee where post='teacher' and emp_name like 'jin%';
单独使用GROUP BY关键字分组 SELECT post FROM employee GROUP BY post; 注意：我们按照post字段分组，那么select查询的字段只能是post，想要获取组内的其他相关信息，需要借助函数 GROUP BY关键字和GROUP_CONCAT()函数一起使用 SELECT post,GROUP_CONCAT(emp_name) FROM employee GROUP BY post;#按照岗位分组，并查看组内成员名 SELECT post,GROUP_CONCAT(emp_name) as emp_members FROM employee GROUP BY post; GROUP BY与聚合函数一起使用 select post,count(id) as count from employee group by post;#按照岗位分组，并查看每个组有多少人 强调： 如果我们用unique的字段作为分组的依据，则每一条记录自成一组，这种分组没有意义 多条记录之间的某个字段值相同，该字段通常用来作为分组的依据
#强调：聚合函数聚合的是组的内容，若是没有分组，则默认一组 示例： SELECT COUNT(*) FROM employee; SELECT COUNT(*) FROM employee WHERE depart_id=1; SELECT MAX(salary) FROM employee; SELECT MIN(salary) FROM employee; SELECT AVG(salary) FROM employee; SELECT SUM(salary) FROM employee; SELECT SUM(salary) FROM employee WHERE depart_id=3; 1. 查询岗位名以及岗位包含的所有员工名字 2. 查询岗位名以及各岗位内包含的员工个数 3. 查询公司内男员工和女员工的个数 4. 查询岗位名以及各岗位的平均薪资 5. 查询岗位名以及各岗位的最高薪资 6. 查询岗位名以及各岗位的最低薪资 7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资 #题1：分组 mysql> select post,group_concat(emp_name) from employee group by post; +-----------------------------------------+---------------------------------------------------------+ | post | group_concat(emp_name) | +-----------------------------------------+---------------------------------------------------------+ | operation | 张野,程咬金,程咬银,程咬铜,程咬铁 | | sale | 歪歪,丫丫,丁丁,星星,格格 | | teacher | alex,wupeiqi,yuanhao,liwenzhou,jingliyang,jinxin,成龙 | | 老男孩驻沙河办事处外交大使 | egon | +-----------------------------------------+---------------------------------------------------------+ #题目2： mysql> select post,count(id) from employee group by post; +-----------------------------------------+-----------+ | post | count(id) | +-----------------------------------------+-----------+ | operation | 5 | | sale | 5 | | teacher | 7 | | 老男孩驻沙河办事处外交大使 | 1 | +-----------------------------------------+-----------+ #题目3： mysql> select sex,count(id) from employee group by sex; +--------+-----------+ | sex | count(id) | +--------+-----------+ | male | 10 | | female | 8 | +--------+-----------+ #题目4： mysql> select post,avg(salary) from employee group by post; +-----------------------------------------+---------------+ | post | avg(salary) | +-----------------------------------------+---------------+ | operation | 16800.026000 | | sale | 2600.294000 | | teacher | 151842.901429 | | 老男孩驻沙河办事处外交大使 | 7300.330000 | +-----------------------------------------+---------------+ #题目5 mysql> select post,max(salary) from employee group by post; +-----------------------------------------+-------------+ | post | max(salary) | +-----------------------------------------+-------------+ | operation | 20000.00 | | sale | 4000.33 | | teacher | 1000000.31 | | 老男孩驻沙河办事处外交大使 | 7300.33 | +-----------------------------------------+-------------+ #题目6 mysql> select post,min(salary) from employee group by post; +-----------------------------------------+-------------+ | post | min(salary) | +-----------------------------------------+-------------+ | operation | 10000.13 | | sale | 1000.37 | | teacher | 2100.00 | | 老男孩驻沙河办事处外交大使 | 7300.33 | +-----------------------------------------+-------------+ #题目7 mysql> select sex,avg(salary) from employee group by sex; +--------+---------------+ | sex | avg(salary) | +--------+---------------+ | male | 110920.077000 | | female | 7250.183750 | +--------+---------------+
按单列排序 SELECT * FROM employee ORDER BY salary; SELECT * FROM employee ORDER BY salary ASC; SELECT * FROM employee ORDER BY salary DESC; 按多列排序:先按照age排序，如果年纪相同，则按照薪资排序 SELECT * from employee ORDER BY age, salary DESC; 1. 查询所有员工信息，先按照age升序排序，如果age相同则按照hire_date降序排序 2. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列 3. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列 #题目1 mysql> select * from employee ORDER BY age asc,hire_date desc; #题目2 mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) asc; +-----------+---------------+ | post | avg(salary) | +-----------+---------------+ | operation | 16800.026000 | | teacher | 151842.901429 | +-----------+---------------+ #题目3 mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) desc; +-----------+---------------+ | post | avg(salary) | +-----------+---------------+ | teacher | 151842.901429 | | operation | 16800.026000 | +-----------+---------------+
示例： SELECT * FROM employee ORDER BY salary DESC LIMIT 3; #默认初始位置为0 SELECT * FROM employee ORDER BY salary DESC LIMIT 0,5; #从第0开始，即先查询出第一条，然后包含这一条在内往后查5条 SELECT * FROM employee ORDER BY salary DESC LIMIT 5,5; #从第5开始，即先查询出第6条，然后包含这一条在内往后查5条 1. 分页显示，每页5条 mysql> select * from employee limit 0,5; +----+-----------+------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ | id | emp_name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+-----------+------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ | 1 | egon | male | 18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL | 7300.33 | 401 | 1 | | 2 | alex | male | 78 | 2015-03-02 | teacher | | 1000000.31 | 401 | 1 | | 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 | | 4 | yuanhao | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 | | 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 | +----+-----------+------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ rows in set (0.00 sec) mysql> select * from employee limit 5,5; +----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+ | id | emp_name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+ | 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 | | 7 | jinxin | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 | | 8 | 成龙 | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 | | 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 | | 10 | 丫丫 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 | +----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+ rows in set (0.00 sec) mysql> select * from employee limit 10,5; +----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+ | id | emp_name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+ | 11 | 丁丁 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 | | 12 | 星星 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 | | 13 | 格格 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 | | 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 | | 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 | +----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+ rows in set (0.00 sec)
SELECT * FROM employee WHERE emp_name REGEXP '^ale'; SELECT * FROM employee WHERE emp_name REGEXP 'on$'; SELECT * FROM employee WHERE emp_name REGEXP 'm{2}'; 小结：对字符串匹配的方式 WHERE emp_name = 'egon'; WHERE emp_name LIKE 'yua%'; WHERE emp_name REGEXP 'on$'; 查看所有员工中名字是jin开头，n或者g结果的员工信息 select * from employee where emp_name regexp '^jin.*[gn]$';
多表操作
#重点：外链接语法
SELECT 字段列表
FROM 表1 INNER|LEFT|RIGHT JOIN 表2
ON 表1.字段 = 表2.字段;
建表与数据准备
mysql> select * from employee,department; +----+------------+--------+------+--------+------+--------------+ | id | name | sex | age | dep_id | id | name | +----+------------+--------+------+--------+------+--------------+ | 1 | egon | male | 18 | 200 | 200 | 技术 | | 1 | egon | male | 18 | 200 | 201 | 人力资源 | | 1 | egon | male | 18 | 200 | 202 | 销售 | | 1 | egon | male | 18 | 200 | 203 | 运营 | | 2 | alex | female | 48 | 201 | 200 | 技术 | | 2 | alex | female | 48 | 201 | 201 | 人力资源 | | 2 | alex | female | 48 | 201 | 202 | 销售 | | 2 | alex | female | 48 | 201 | 203 | 运营 | | 3 | wupeiqi | male | 38 | 201 | 200 | 技术 | | 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 | | 3 | wupeiqi | male | 38 | 201 | 202 | 销售 | | 3 | wupeiqi | male | 38 | 201 | 203 | 运营 | | 4 | yuanhao | female | 28 | 202 | 200 | 技术 | | 4 | yuanhao | female | 28 | 202 | 201 | 人力资源 | | 4 | yuanhao | female | 28 | 202 | 202 | 销售 | | 4 | yuanhao | female | 28 | 202 | 203 | 运营 | | 5 | liwenzhou | male | 18 | 200 | 200 | 技术 | | 5 | liwenzhou | male | 18 | 200 | 201 | 人力资源 | | 5 | liwenzhou | male | 18 | 200 | 202 | 销售 | | 5 | liwenzhou | male | 18 | 200 | 203 | 运营 | | 6 | jingliyang | female | 18 | 204 | 200 | 技术 | | 6 | jingliyang | female | 18 | 204 | 201 | 人力资源 | | 6 | jingliyang | female | 18 | 204 | 202 | 销售 | | 6 | jingliyang | female | 18 | 204 | 203 | 运营 | +----+------------+--------+------+--------+------+--------------+
#找两张表共有的部分，相当于利用条件从笛卡尔积结果中筛选出了正确的结果 #department没有204这个部门，因而employee表中关于204这条员工信息没有匹配出来 mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee inner join department on employee.dep_id=department.id; +----+-----------+------+--------+--------------+ | id | name | age | sex | name | +----+-----------+------+--------+--------------+ | 1 | egon | 18 | male | 技术 | | 2 | alex | 48 | female | 人力资源 | | 3 | wupeiqi | 38 | male | 人力资源 | | 4 | yuanhao | 28 | female | 销售 | | 5 | liwenzhou | 18 | male | 技术 | +----+-----------+------+--------+--------------+ #上述sql等同于 mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee,department where employee.dep_id=department.id; #示例1：以内连接的方式查询employee和department表，并且employee表中的age字段值必须大于25,即找出年龄大于25岁的员工以及员工所在的部门 select employee.name,department.name from employee inner join department on employee.dep_id = department.id where age > 25; #示例2：以内连接的方式查询employee和department表，并且以age字段的升序方式显示 select employee.id,employee.name,employee.age,department.name from employee,department where employee.dep_id = department.id and age > 25 order by age asc;
#以左表为准，即找出所有员工信息，当然包括没有部门的员工 #本质就是：在内连接的基础上增加左边有右边没有的结果 mysql> select employee.id,employee.name,department.name as depart_name from employee left join department on employee.dep_id=department.id; +----+------------+--------------+ | id | name | depart_name | +----+------------+--------------+ | 1 | egon | 技术 | | 5 | liwenzhou | 技术 | | 2 | alex | 人力资源 | | 3 | wupeiqi | 人力资源 | | 4 | yuanhao | 销售 | | 6 | jingliyang | NULL | +----+------------+--------------+
#以右表为准，即找出所有部门信息，包括没有员工的部门 #本质就是：在内连接的基础上增加右边有左边没有的结果 mysql> select employee.id,employee.name,department.name as depart_name from employee right join department on employee.dep_id=department.id; +------+-----------+--------------+ | id | name | depart_name | +------+-----------+--------------+ | 1 | egon | 技术 | | 2 | alex | 人力资源 | | 3 | wupeiqi | 人力资源 | | 4 | yuanhao | 销售 | | 5 | liwenzhou | 技术 | | NULL | NULL | 运营 | +------+-----------+--------------+
全外连接：在内连接的基础上增加左边有右边没有的和右边有左边没有的结果 #注意：mysql不支持全外连接 full JOIN #强调：mysql可以使用此种方式间接实现全外连接 select * from employee left join department on employee.dep_id = department.id union select * from employee right join department on employee.dep_id = department.id ; #查看结果 +------+------------+--------+------+--------+------+--------------+ | id | name | sex | age | dep_id | id | name | +------+------------+--------+------+--------+------+--------------+ | 1 | egon | male | 18 | 200 | 200 | 技术 | | 5 | liwenzhou | male | 18 | 200 | 200 | 技术 | | 2 | alex | female | 48 | 201 | 201 | 人力资源 | | 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 | | 4 | yuanhao | female | 28 | 202 | 202 | 销售 | | 6 | jingliyang | female | 18 | 204 | NULL | NULL | | NULL | NULL | NULL | NULL | NULL | 203 | 运营 | +------+------------+--------+------+--------+------+--------------+ #注意 union与union all的区别：union会去掉相同的纪录
子查询
#1：子查询是将一个查询语句嵌套在另一个查询语句中。 #2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。 #3：子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字 #4：还可以包含比较运算符：= 、 !=、> 、<等
###################带IN关键字的子查询################### #查询平均年龄在25岁以上的部门名 select id,name from department where id in (select dep_id from employee group by dep_id having avg(age) > 25); #查看技术部员工姓名 select name from employee where dep_id in (select id from department where name='技术'); #查看不足1人的部门名(子查询得到的是有人的部门id) select name from department where id not in (select distinct dep_id from employee); ###################带比较运算符的子查询################### #比较运算符：=、!=、>、>=、<、<=、<> #查询大于所有人平均年龄的员工名与年龄 mysql> select name,age from emp where age > (select avg(age) from emp); +---------+------+ | name | age | +---------+------+ | alex | 48 | | wupeiqi | 38 | +---------+------+ 2 rows in set (0.00 sec) #查询大于部门内平均年龄的员工名、年龄 select t1.name,t1.age from emp t1 inner join (select dep_id,avg(age) avg_age from emp group by dep_id) t2 on t1.dep_id = t2.dep_id where t1.age > t2.avg_age; ###################带EXISTS关键字的子查询################### EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录。 而是返回一个真假值。True或False 当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询 #department表中存在dept_id=203，Ture mysql> select * from employee -> where exists -> (select id from department where id=200); +----+------------+--------+------+--------+ | id | name | sex | age | dep_id | +----+------------+--------+------+--------+ | 1 | egon | male | 18 | 200 | | 2 | alex | female | 48 | 201 | | 3 | wupeiqi | male | 38 | 201 | | 4 | yuanhao | female | 28 | 202 | | 5 | liwenzhou | male | 18 | 200 | | 6 | jingliyang | female | 18 | 204 | +----+------------+--------+------+--------+ #department表中存在dept_id=205，False mysql> select * from employee -> where exists -> (select id from department where id=204); Empty set (0.00 sec) #创建表 create table emp( id int not null unique auto_increment, name varchar(20) not null, sex enum('male','female') not null default 'male', #大部分是男的 age int(3) unsigned not null default 28, hire_date date not null, post varchar(50), post_comment varchar(100), salary double(15,2), office int, #一个部门一个屋子 depart_id int ); #查看表结构 mysql> desc employee; +--------------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+-----------------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | NO | | NULL | | | sex | enum('male','female') | NO | | male | | | age | int(3) unsigned | NO | | 28 | | | hire_date | date | NO | | NULL | | | post | varchar(50) | YES | | NULL | | | post_comment | varchar(100) | YES | | NULL | | | salary | double(15,2) | YES | | NULL | | | office | int(11) | YES | | NULL | | | depart_id | int(11) | YES | | NULL | | +--------------+-----------------------+------+-----+---------+----------------+ #插入记录 #三个部门：教学，销售，运营 insert into employee(name,sex,age,hire_date,post,salary,office,depart_id) values ('egon','male',18,'20170301','老男孩驻沙河办事处外交大使',7300.33,401,1), #以下是教学部 ('alex','male',78,'20150302','teacher',1000000.31,401,1), ('wupeiqi','male',81,'20130305','teacher',8300,401,1), ('yuanhao','male',73,'20140701','teacher',3500,401,1), ('liwenzhou','male',28,'20121101','teacher',2100,401,1), ('jingliyang','female',18,'20110211','teacher',9000,401,1), ('jinxin','male',18,'19000301','teacher',30000,401,1), ('成龙','male',48,'20101111','teacher',10000,401,1), ('歪歪','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门 ('丫丫','female',38,'20101101','sale',2000.35,402,2), ('丁丁','female',18,'20110312','sale',1000.37,402,2), ('星星','female',18,'20160513','sale',3000.29,402,2), ('格格','female',28,'20170127','sale',4000.33,402,2), ('张野','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门 ('程咬金','male',18,'19970312','operation',20000,403,3), ('程咬银','female',18,'20130311','operation',19000,403,3), ('程咬铜','male',18,'20150411','operation',18000,403,3), ('程咬铁','female',18,'20140512','operation',17000,403,3) ; #ps：如果在windows系统中，插入中文字符，select的结果为空白，可以将所有字符编码统一设置成gbk ############################ SELECT * FROM emp AS t1 INNER JOIN ( SELECT post, max(hire_date) max_date FROM emp GROUP BY post ) AS t2 ON t1.post = t2.post WHERE t1.hire_date = t2.max_date; ############################ mysql> select (select t2.name from emp as t2 where t2.post=t1.post order by hire_date desc limit 1) from emp as t1 group by post; +---------------------------------------------------------------------------------------+ | (select t2.name from emp as t2 where t2.post=t1.post order by hire_date desc limit 1) | +---------------------------------------------------------------------------------------+ | 张野 | | 格格 | | alex | | egon | +---------------------------------------------------------------------------------------+ rows in set (0.00 sec) mysql> select (select t2.id from emp as t2 where t2.post=t1.post order by hire_date desc limit 1) from emp as t1 group by post; +-------------------------------------------------------------------------------------+ | (select t2.id from emp as t2 where t2.post=t1.post order by hire_date desc limit 1) | +-------------------------------------------------------------------------------------+ | 14 | | 13 | | 2 | | 1 | +-------------------------------------------------------------------------------------+ rows in set (0.00 sec) #正确答案 mysql> select t3.name,t3.post,t3.hire_date from emp as t3 where id in (select (select id from emp as t2 where t2.post=t1.post order by hire_date desc limit 1) from emp as t1 group by post); +--------+-----------------------------------------+------------+ | name | post | hire_date | +--------+-----------------------------------------+------------+ | egon | 老男孩驻沙河办事处外交大使 | 2017-03-01 | | alex | teacher | 2015-03-02 | | 格格 | sale | 2017-01-27 | | 张野 | operation | 2016-03-11 | +--------+-----------------------------------------+------------+ rows in set (0.00 sec)
库操作
#语法： # mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.sql #示例： #单库备份 mysqldump -uroot -p123 db1 > db1.sql mysqldump -uroot -p123 db1 table1 table2 > db1-table1-table2.sql #多库备份 mysqldump -uroot -p123 --databases db1 db2 mysql db3 > db1_db2_mysql_db3.sql #备份所有库 mysqldump -uroot -p123 --all-databases > all.sql #方法一： [root@egon backup]# mysql -uroot -p123 < /backup/all.sql #方法二： mysql> use db1; mysql> SET SQL_LOG_BIN=0; #关闭二进制日志，只对当前session生效 mysql> source /root/db1.sql #准备表、记录 mysql> create database db1; mysql> use db1; mysql> source /root/init.sql
begin; # 开启事务 select * from emp where id = 1 for update; # 查询id值，for update添加行锁； update emp set salary=10000 where id = 1; # 完成更新 commit; # 提交事务
