02 . Mysql基础操作及增删改查
SQL简介
SQL（Structured Query Language 即结构化查询语言）
SQL语言主要用于存取数据、查询数据、更新数据和管理关系数据库系统，SQL语言由IBM开发。
SQL语句四大分类
SQL语言分为3种类型：
DDL语句 数据库定义语言(Create，Alter，Drop，DECLARE)
DML语句 数据库操纵语言(Select，Delete，Update，Insert)
DCL语句 数据库控制语言(GRANT，REVOKE，COMMIT，ROLLBACK)
DQL语句 数据库查询语言(select)
DML（data manipulation language）：
就象它的名字一样，这4条命令是用来对数据库里的数据进行操作的语言
DDL（data definition language）：
主要是用在定义或改变表的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建表时使用
DCL（Data Control Language）：
用来设置或更改数据库用户或角色权限的语句，默认状态下，只有sysadmin,dbcreator,db_owner或db_securityadmin等人员才有权力执行
SQL语句的注意事项
# 1. 每条SQL语句结束时要以;做为结束符，也可以使用\G以文本模式输出.(除了use命令)
# 2. SQL语句的关键字不区分大小写(除了库名字和表名字)
# 3. 当你需要补全大部分库和表名使用\#
# 4. 当你不需要一条语句输出的结果以\c结束
# 5. 当你想要退出数据库时，可以使用\q或者是exit，ctrl+d
# 6. 我们可以在命令行执行sql语句,要通过mysql -e参数
# mysql -e "show databases \G" 显示到shell上
# 7. 如果需要获取SQL语句的帮助可以用help命令
# 如:help create
# 如果需要进一步获取帮助,可以继续使用help命令
# 如:help create database
MySQL的库操作
系统自带数据库
# 系统数据库
information_schema:
# 虚拟库，存放在内存中，主要存储了系统中的一些数据库对象信息，如用户表信息，列信息，权限信息，字符信息等
performance_schema:
# 主要存储数据库服务器的性能参数
mysql:
# 授权库，主要存储系统用户的权限信息
test:
# 公共库，任何人都能访问，Mysql数据库自动创建的测试数据库
sys:
# 系统的元数据信息，方便DBA发现数据库的很多信息，为解决性能瓶颈体用了巨大帮助
常见库操作
语法
# create database 数据库名 character set = 'utf8' collate=utf8_general_ci;
# 数据库命名规则：
# 1 > 可以由字母，数字，下划线，@，#，$
# 2 > 区分大小写
# 3 > 唯一性
# 4 > 不能使用关键字如create select
# 5 > 不能单独使用数字
# 6 > 最长128位
# 使用数据库
use 数据库名字;
# 查看当前库
select database();
# OR
status;
# 删除数据库
drop database database_name;
关于字符集
# 数据表里存放什么样的数据就选取相应的字符集: 简体中文的字符集是gb2312和gbk： utf8是国际通用字符集
show character set; # 查看Mysql支持的字符集
show collation; # 查看字符集的排序规则，utfs-generic支持大多数的排序规则
create database db character set = 'utf8mb4' collate=utf8_general_ci;
# 创建数据库设置字符编码和字符集的排序规则
show create database db\G; # 查看db数据库字符编码
alter database db character set = latin1 # 修改数据库的字符编码
数据库存储引擎
位于数据库底层，属于底层组件，DBMS通过存储引擎进行增删改查操作，不同的存储引擎有不同的功能。支持不同的索引，不同的存储方式等等，属于mysql核心
查看mysql所有的存储引擎
mysql> show engines\G;
*************************** 1. row ***************************
Engine: InnoDB # 引擎名
Support: DEFAULT # mysql是否支持该种引擎
Comment: Supports transactions, row-level locking, and foreign keys
# mysql对这种引擎的描述
Transactions: YES # 是否支持事务
XA: YES # 是否支持分布式事务
Savepoints: YES # 是否支持事务的保存点
*************************** 2. row ***************************
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 3. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 4. row ***************************
Engine: BLACKHOLE
Support: YES
Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
XA: NO
Savepoints: NO
*************************** 5. row ***************************
Engine: MyISAM
Support: YES
Comment: MyISAM storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 6. row ***************************
Engine: CSV
Support: YES
Comment: CSV storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 7. row ***************************
Engine: ARCHIVE
Support: YES
Comment: Archive storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 8. row ***************************
Engine: PERFORMANCE_SCHEMA
Support: YES
Comment: Performance Schema
Transactions: NO
XA: NO
Savepoints: NO
*************************** 9. row ***************************
Engine: FEDERATED
Support: NO
Comment: Federated MySQL storage engine
Transactions: NULL
XA: NULL
Savepoints: NULL
9 rows in set (0.00 sec)
存储引擎
属于数据存储的格式，不同的存储引擎功能不相同，占用空间不相同，读取性能不相同，每一个表格对应一个存储引擎
MyISAM
# MyISAM存储引擎的特点
（1）在5.5版本之前默认存储引擎，在5.5及之后被lnnoDB代替
（2）读取速度快，占用资源少，不支持事务，支持全文索引，不支持外键
（3）读写相互阻塞
（4）只能缓存索引不能缓存数据
# MyISAM适用场景
（1） 不支持事务的业务
（2） 适合于读取数据较多的业务
（3） 并发量低。数据修改少的业务
（4） 硬件较差的服务器
InnoDB
# InnoDB特点
(1) 支持事务，属于事务类型的首选存储引擎，还支持**行级别锁定**（每次锁定的是一行数据的锁机制）和外键，为mysql5.5版本默认存储引擎
(2) 具有提交、回滚和崩溃回复能力的事务安全的存储引擎，可以处理高并发数据量，性能高
(3) 具有较高缓存性能，支持缓存索引和数据
(4) 使用InnoDB会在数据目录下生成一个10MB大小的名为ibdata1的自动扩展文件和两个5MB大小的名为ib_logfile0和ib_logfile1的日志文件
ibdata1 文件存放使用innodb存储引擎表中的数据和索引信息
# InnoDB适用场景
(1) 需要支持事务的业务，高并发的业务 例如银行转账
(2) 数据更新较为频繁的业务 例如微博
(3) 数据一致性要求较高的业务 例如充值，转账
Memory
# (1) Memory存储引擎将会把表中的数据存放在内存中，提供快速查询和快速引用
# (2) Memory支持hash和btree索引，不支持blob和text列的创建
# (3) 当不需要Memory表中的数据时，直接清空表中数据可以直接释放内存，或直接删除表
Mysql常见的数据类型
在Mysql数据库管理系统中，可以通过存储引擎来决定表的类型，同时,Mysql数据库管理系统也提供了数据类型决定表存储数据的类型，数值类型越大对于内存的消耗越多，合适的数据类型在一定程度上能增加数据库的效率.
Mysql数据库管理系统提供的数据类型
# 1. 整数类型: TINYINT SMALLINT MEDIUMINT INT BIGINT
# 2. 浮点数类型: FLOAT 单精度浮点数值（7个有效位） DOUBLE双精度（15个）
# 3. 字符串类型: CHAR VARCHAR
# 4. 时间和日期类型: DATE TIME DATETIME TIMESTAMP YEAR
# 5. 枚举类型: ENUM
#6. 集合类型: SET
数据类型的常用场景
整数类型
用于存储用户的年龄、游戏的Level、经验值等。
分为有符号（默认）和无符号
浮点数类型
用于存储用户的身高、体重、薪水等.
浮点数类型可以使用名称后加(M,D)的方式表示，表示一共显示M位数字（整数位+小数位），其中D表示小数点后面的位数，M和D又称为精度和标度。
时间和日期类型
用于存储用户的注册时间，在线时长，文章的发布时间，员工的入职时间等
date类型
使用连字号为分隔符分隔开 (显示年月日)
time类型
应该使用冒号作为分隔符且需要加上单引号，或者是直接使用具体的数字(显示时分秒)
year类型
关于年的时间有个分界线是1970年,把在0069范围内的值转换为20002069范围内,而把7099范围内的值转换到19701999之内，为了避免发生这种现象最好就是写全了.
datetime和timestamp类型
都用于存储包含当前日期和时间的时间戳
如果字段没有值或者是空值时timestamp会使用当前的系统时间作为他的时间戳，而datetime只显示出NULL。
当已有字段更新时，datetime类型所对应的时间不会发生变化，timestamp类型所对应的时间会更新为最新时间。
字符串类型
用于存储用户的姓名、爱好、发布的文章等
# 1. char：定长字符串，需要大小修饰符来指定要存储值的长度，修饰符的范围是0-255
# 2. varchar：变长字符串，需要大小修饰符来指定要存储值的长度，修饰符的范围是0-65535
ENUM类型即枚举类型
集合类型SET
# 1. 枚举类型：一般用于性别，是女或是男两者选一个，不包含的不能选
# 2. set类型：可以多选，比如爱好中可以有一个或者多个，但不能超出范围，一个set最多包含64项。
MySQL的表操作
Mysql表的基本管理
MySQL 的表存放了实际的数据。
表大体可以分为表头和表体， 一张表只有一个表头，也就是一行数据，
这行数据的每一列的值成为字段。表体可以有多行数据组成，每一行代表了一个要表现的实际对象。
每个字段代表了一条数据都有那些属性，比如存一个学生的信息，需要有姓名、年龄、学号等。
对于这些属性，你会发现它们是不同类型的数据，比如 姓名 是普通的文本，就是字符串，而年龄就是数字，也就是整型。
为了保证这些数据存放到数据库时，不会出现类型的错误而导致以后影响使用，在创建表时，必须对每个字段指定要存放数据的数据类型。
学生表
| id | name | age | phone |
|---|---|---|---|
| 1 | youmen | 18 | 13143234323 |
| 2 | flying | 19 | 12823486853 |
| 3 | zhou | 20 | 13123345568 |
| 4 | jian | 21 | 15723427563 |
Mysql表操作
用在定义或改变表(TABLE)的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用。
DDL不需要commit(DDL都是隐式提交，不能对DDL语句使用ROLLBACK命令)
# CREATE 创建表,在数据库创建对象
# ALTER 修改表,修改数据库结构(扩展自修)
# DROP 删除表,从数据库中删除对象
# TRUNCATE 删除表中所有行（无法回退）
# COMMENT 注释（为数据字典添加备注）
# RENAME 重命名表(扩展自修)
查看数据库所有表
mysql> show tables;
+------------------+
| Tables_in_youmen |
+------------------+
| student |
| student2 |
+------------------+
2 rows in set (0.00 sec)
查看表结构
desc 表名;
# OR
desc 库名.表名;
清空表的内容，表本身还在
delete from 表名 /*自增列的值继续递增，可以加 where 子句*/
truncate table 表名 /*自增列的值重新从 1 开始*/
/*删除表中的所有数据，自增列的值继续递增*/
delete from tb1;
/*删除表中的某些数据，被删除的数据的自增列的值将不会再次出现，自增列的值继续递增*/
delete from tb1 where id < 20;
删除表
mysql> drop table 表名，表名;
mysql> drop table IF EXISTS student2; # 这个表存在就删除，不存在也不会报错，可以删除脚本使用
# 删除字段/表
delete FROM 表名 [where condtion];
mysql> alter table student2 drop id;
mysql> delete from student2;
mysql> delete from mysql.user where authentication_string=''; # 出于安全考虑将表中没有密码的删掉
mysql> delete from student2 where id=2 and name='flying'; # 匹配到id=2并且name=flying的删掉此类数据
创建表
create table 表名(
字段名1 类型[(宽度)约束条件]
字段名2 类型[(宽度)约束条件]
...
)[存储引擎 字符集];
# 说明: 在同一张表中，字段名和类型是必须有的，且字段名是不能相同；
# 中括号里面的宽度和约束条件可选,整数类型不需要指定宽度,其他的浮点型和定点型等需要指定宽度,也就是约束条件。
create table 表名(
列名 类型 是否可以为空,
列名 类型 是否可以为空
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
# 创建表时可以单独指定其使用的存储引擎和默认字符编码.
# ENGING=InnoDB DEFAULT CHARSET=utf8
# 默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值
create table tb1(
id int not null,
age int not null default 2
)
# 是否可空，null表示空，并不是字符串类型
not null # 不可空，在添加数据时必须给值
null # 可空(这是非主键的默认属性)
主键
# 外键，一个特殊的索引，用于关联两个表，只能是指定内容
# 语法:
# 字段名 数据类型 primary key
create table tb1(
id int primary key,
age int null
)
# OR
create table tb1(
id int not null,
age int not null,
primary key(id,age)
)
# 注意
# 1. 外键不允许是空值，如果主键使用单个列，则他的值必须唯一，如果是多种，则其组合必须唯一.
# 外键是基于字段建立的，一旦建立外键之后，建立的外键的表称之为子表，被关联的表为父表.
# [constraint 约束名] foreign key （添加外键的字段） references 父表名 （父表主键列）
# 语法： 注意父表中所关联的字段必须是主键
# constraint 约束名 foreign key （建立外键的字段） references 父表名 （父表主键列）
Example1
create table student(
nid int auto_increment primary key,
name varchar(12) not null,
age int not null,
phone char(11)
);
create table student2(
id int auto_increment primary key,
class_id int,
foreign key(class_id) REFERENCES student(nid)
);
# 建立外键的规则
# 1. 父子表中建立外键的字段数据类型需要一致
# 2. 关联父表时，父表的字段需要为父表
# 3. 如果父表为联合主键需要从第一个字段开始关联
# 4. 书写问题
# 5. 存储引擎 只有innodb才支持外键，其他不行，否则外键建立不成功
# 建立有外键的父子表中不允许使用truncate table 只能使用delete进行删除数据
# 父子表写入数据时，如果想给子表中的外键写入数据，需要保证写入的数据在父表的主键列拥有该数据才能进行添加是否添加失败，用来保证数据的一致性
# 外键在进行建立的过程中需要重新写一行进行添加，不能跟在数据类型的后面进行建立
自增
# 自增，如果为某列设置自增列，插入数据时无需设置此列的值，默认将自增（表中只能有一个自增列）
create table tb1(
id int auto_increment primary key,
age int not null
)
show variables like '%auto_increment_%';
auto_increment_increment | 1 # 每次按照指定的数量自增
auto_increment_offset | 1 # 自增量的初始量
set auto_increment_increment=2;
创建表定义一对多关系
create table student(
id1 int auto_increment primary key,
name varchar(12) not null,
age int not null,
phone char(11)
);
create table student2(
id int auto_increment primary key,
class_id int,
foreign key(class_id) REFERENCES student(id1)
);
修改表结构
修改表名
alter table student1 rename student2;
# 使用to 的话可以进行库与库之间的表迁移
alter table tb10 rename to test1.tb10;
修改字段数据类型
alter table tb10 modify name int(10);
alter table student modify column age varchar(10);
修改列名和类型
alter table student change age ages int(20);
添加列
alter table 表名 add 列名 类型
# 示例：
alter table stadent add gender Enum("男", "女");
alter table stadent add hobby set("girl","car","yacht");
update student set hobby = "girl,car";
删除列
alter table 表名 drop column 列名
添加主键
alter table 表名 add primary key(列名);
alter table students add id int not null auto_increment, add primary key (id);
删除主键
alter table 表名 drop primary key;
# 删除主键属性，保留原值和列
alter table 表名 modify 列名 int, drop primary key;
添加一个列的唯一属性
alter table student2 modify column email varchar(32) unique;
修改自增列
# 修改自增属性的列，必须具备主键的属性。
alter table 表名 modify column id int AUTO_INCREMENT;
外键的添加和删除
# 向从表对一个字段增加外键属性时，从表中的这个字段必须已经存在，且不能有数据。
# 添加外键：
alter table 从表 add constraint 外键名称（形如：FK_从表_主表） foreign key 从表(从表的列名) references 主表(主键列名);
# 删除外键：
alter table 表名 drop foreign key 外键名称
# 注意
# 从表的外键列的属性必须主表的主键列名属性一致
默认值的修改和删除
# 修改默认值：
ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
# 删除默认值：
ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;
插入表数据
DML(数据操纵语言)
DML是用来对数据库里的数据进行操作的语言，DML需要commit.
当使用mysql客户端操作数据库时，这些语句都会自动提交，不会执行commit
# 表school.student1
字段 字段 字段 字段
id name sex age
1 tom male 23 记录
2 jack male 21 记录
3 alice female 19 记录
# 创建表
create table student1
(id int,name varchar(50),sex enum('male','female'),age int);
# 注意:
# 1. 先看表的结构再去插值，插值的时候数字不需要用引号，字符串必须要用双引号或者是单引号引起来；
# 2. 按"行"插入，"字段"和“值”之间一一对应
# 3. 值的形式: 数字直接写,字符串和时间加单引号，但如果是函数值,则不能加引号
# 4. auto_increment,timestamp等字段无需插入
# 语法: insert into 表名(字段1，字段2...) values(字段值列表...)
# 顺序插入多个记录，不用指定字段名,此处指的顺序并非是id数字，而是插入的值和前面的字段是对应的；
mysql> insert into student1 values
(1,'tom','male',23),
(2,'jack','male',21),
(3,'alice','female',19);
# 1.插入完整数据（顺序插入）
# 语法一: insert into 表名（字段1，字段2，字段3..字段n） values(值1，值2，值3);
# 语法二: insert into 表名 values (值1，值2，值3..值n);
# 2.指定字段插入数据
# 语法一: insert into 表名（字段1，字段2） values (值1，值2);
# 3.插入多条记录使用逗号隔开
# 语法一: insert into 表名 values(值1，值2，值3),(值1，值2，值3..值n),(值1，值2，值3 );
# 4.插入查询结果
# 语法一: insert into 表1（字段1，字段2，字段3...字段n）
# select (字段1，字段2，字段3) FROM 表2 WHERE ...;
Mysql数据库查询
查看表
mysql> show table;
# 查看库中已有的表
# 查看表结构
mysql> desc student1;
mysql> show create table student1;
# 查询表中指定字段的值
mysql> select id,name,sex from student1\G;
mysql> select * from student1;
# 查询表中所有字段的值，因为还没有插入字段，所以查询到数据都是空的
# 聚合查询
# 查询一个表中有多少条记录,可以使用SQL内置的count()函数查询~~~~
select count(*) from students;
实例表创建
# 创建employee 数据表：
create table employee
( e_no int(11) not null unique,
e_name varchar(50) not null,
e_gender char(2),
dept_no int(11) not null,
e_job varchar(50) not null,
e_salary int(11) not null,
hiredate date not null,primary key (dept_no,e_no));
# 插入数据
insert into employee values
(1001,'SMITH','m',20,'CLERK',800,'2005/11/12'),
(1002,'ALLEN','f',30,'SALESMAN',1600,'2003/5/12'),
(1003,'WARD','f',30,'SALESMAN',1250,'2003/5/12'),
(1004,'JONES','m',20,'MANAGER',2975,'1998/5/18'),
(1005,'MARTIN','m',30,'SALESMAN',1250,'2001/6/12'),
(1006,'BLAKE','f',30,'MANAGER',2850,'1997/2/15'),
(1007,'CLARK','m',10,'MANAGER',2450,'2002/9/12'),
(1008,'SCOTT','m',20,'ANALYSE',3000,'2003/5/12'),
(1009,'KING','f',10,'PRESIDENT',5000,'1995/1/1'),
(1010,'TURNER','f',30,'SALESMAN',1500,'1997/10/12'),
(1011,'ADAMS','m',20,'CLERK',1100,'1999/10/5'),
(1012,'JAMES','f',30,'CLERK',950,'2008/6/15');
# 创建dept 数据表：
create table dept
(d_no int(11) primary key not null unique auto_increment,
d_name varchar(50) not null,
d_location varchar(100));
# 插入数据
insert into dept values
(10,'ACCOUNTING','ShangHai'),
(20,'RESEARCH','BeiJing'),
(30,'SALES','ShenZhen'),
(40,'OPERATIONS','FuJian');
单表查询
# 1. 在employee表中，查询所有记录的e_no，e_name和e_salary字段值
mysql>select e_no,e_name,e_salary from employee;
+------+--------+----------+
| e_no | e_name | e_salary |
+------+--------+----------+
| 1007 | CLARK | 2450 |
| 1009 | KING | 5000 |
| 1001 | SMITH | 800 |
| 1004 | JONES | 2975 |
| 1008 | SCOTT | 3000 |
| 1011 | ADAMS | 1100 |
| 1002 | ALLEN | 1600 |
| 1003 | WARD | 1250 |
| 1005 | MARTIN | 1250 |
| 1006 | BLAKE | 2850 |
| 1010 | TURNER | 1500 |
| 1012 | JAMES | 950 |
+------+--------+----------+
# 2、在employee表中，查询dept_no等于10和20的所有记录
mysql> select * from employee where dept_no =10 or dept_no=20;
+------+--------+----------+---------+-----------+----------+------------+
| e_no | e_name | e_gender | dept_no | e_job | e_salary | hiredate |
+------+--------+----------+---------+-----------+----------+------------+
| 1007 | CLARK | m | 10 | MANAGER | 2450 | 2002-09-12 |
| 1009 | KING | f | 10 | PRESIDENT | 5000 | 1995-01-01 |
| 1001 | SMITH | m | 20 | CLERK | 800 | 2005-11-12 |
| 1004 | JONES | m | 20 | MANAGER | 2975 | 1998-05-18 |
| 1008 | SCOTT | m | 20 | ANALYSE | 3000 | 2003-05-12 |
| 1011 | ADAMS | m | 20 | CLERK | 1100 | 1999-10-05 |
+------+--------+----------+---------+-----------+----------+------------+
# 3、在employee表中，查询工资范围在800~2500之间的员工信息
select * from employee where e_salary between 800 and 2500;
+------+--------+----------+---------+----------+----------+------------+
| e_no | e_name | e_gender | dept_no | e_job | e_salary | hiredate |
+------+--------+----------+---------+----------+----------+------------+
| 1007 | CLARK | m | 10 | MANAGER | 2450 | 2002-09-12 |
| 1001 | SMITH | m | 20 | CLERK | 800 | 2005-11-12 |
| 1011 | ADAMS | m | 20 | CLERK | 1100 | 1999-10-05 |
| 1002 | ALLEN | f | 30 | SALESMAN | 1600 | 2003-05-12 |
| 1003 | WARD | f | 30 | SALESMAN | 1250 | 2003-05-12 |
| 1005 | MARTIN | m | 30 | SALESMAN | 1250 | 2001-06-12 |
| 1010 | TURNER | f | 30 | SALESMAN | 1500 | 1997-10-12 |
| 1012 | JAMES | f | 30 | CLERK | 950 | 2008-06-15 |
+------+--------+----------+---------+----------+----------+------------+
# 4、 在employee表中，查询部门编号为20的部门中的员工信息
select * from employee where dept_no=20;
+------+--------+----------+---------+---------+----------+------------+
| e_no | e_name | e_gender | dept_no | e_job | e_salary | hiredate |
+------+--------+----------+---------+---------+----------+------------+
| 1001 | SMITH | m | 20 | CLERK | 800 | 2005-11-12 |
| 1004 | JONES | m | 20 | MANAGER | 2975 | 1998-05-18 |
| 1008 | SCOTT | m | 20 | ANALYSE | 3000 | 2003-05-12 |
| 1011 | ADAMS | m | 20 | CLERK | 1100 | 1999-10-05 |
+------+--------+----------+---------+---------+----------+------------+
# 5、在employee表中，查询每个部门最高工资的员工信息
select * from employee where e_salary in (select max(e_salary) from employee group by dept_no);
+------+--------+----------+---------+-----------+----------+------------+
| e_no | e_name | e_gender | dept_no | e_job | e_salary | hiredate |
+------+--------+----------+---------+-----------+----------+------------+
| 1009 | KING | f | 10 | PRESIDENT | 5000 | 1995-01-01 |
| 1008 | SCOTT | m | 20 | ANALYSE | 3000 | 2003-05-12 |
| 1006 | BLAKE | f | 30 | MANAGER | 2850 | 1997-02-15 |
+------+--------+----------+---------+-----------+----------+------------+
多表条件查询
# 1、查询员BLAKE所在部门和部门所在地
select d_name,d_location from dept where d_no in (select dept_no from employee WHERE e_name='BLAKE');
+--------+------------+
| d_name | d_location |
+--------+------------+
| SALES | ShenZhen |
+--------+------------+
# 2、使用连接查询、查询所有员工的部门和部门信息
select dept_no,d_name,d_location from employee,dept where employee.dept_no=dept.d_no;
+---------+------------+------------+
| dept_no | d_name | d_location |
+---------+------------+------------+
| 10 | ACCOUNTING | ShangHai |
| 10 | ACCOUNTING | ShangHai |
| 20 | RESEARCH | BeiJing |
| 20 | RESEARCH | BeiJing |
| 20 | RESEARCH | BeiJing |
| 20 | RESEARCH | BeiJing |
| 30 | SALES | ShenZhen |
| 30 | SALES | ShenZhen |
| 30 | SALES | ShenZhen |
| 30 | SALES | ShenZhen |
| 30 | SALES | ShenZhen |
| 30 | SALES | ShenZhen |
+---------+------------+------------+
# 3、在employee表中，计算每个部门各多少名员工
select dept_no,count(e_name)as peoples from employee group by dept_no ;
+---------+---------+
| dept_no | peoples |
+---------+---------+
| 10 | 2 |
| 20 | 4 |
| 30 | 6 |
+---------+---------+
# 4、在employee表中，计算不同类型职工的总工资数
select e_job,sum(e_salary)as total from employee group by e_job ;
+-----------+-------+
| e_job | total |
+-----------+-------+
| ANALYSE | 3000 |
| CLERK | 2850 |
| MANAGER | 8275 |
| PRESIDENT | 5000 |
| SALESMAN | 5600 |
+-----------+-------+
# 5、在employee表中，计算不同部门的平均工资
select dept_no,avg(e_salary)as total from employee group by dept_no ;
+---------+-----------+
| dept_no | total |
+---------+-----------+
| 10 | 3725.0000 |
| 20 | 1968.7500 |
| 30 | 1566.6667 |
+---------+-----------+
# 6、在employee表中，查询工资低于1500的员工信息
select * from employee where e_salary<1500;
+------+--------+----------+---------+----------+----------+------------+
| e_no | e_name | e_gender | dept_no | e_job | e_salary | hiredate |
+------+--------+----------+---------+----------+----------+------------+
| 1001 | SMITH | m | 20 | CLERK | 800 | 2005-11-12 |
| 1011 | ADAMS | m | 20 | CLERK | 1100 | 1999-10-05 |
| 1003 | WARD | f | 30 | SALESMAN | 1250 | 2003-05-12 |
| 1005 | MARTIN | m | 30 | SALESMAN | 1250 | 2001-06-12 |
| 1012 | JAMES | f | 30 | CLERK | 950 | 2008-06-15 |
+------+--------+----------+---------+----------+----------+------------+
# 7、在employee表中，将查询记录先按照部门编号由高到低排列，在按员工工资由高到低排列
select * from employee order by dept_no,e_salary desc ;
+------+--------+----------+---------+-----------+----------+------------+
| e_no | e_name | e_gender | dept_no | e_job | e_salary | hiredate |
+------+--------+----------+---------+-----------+----------+------------+
| 1009 | KING | f | 10 | PRESIDENT | 5000 | 1995-01-01 |
| 1007 | CLARK | m | 10 | MANAGER | 2450 | 2002-09-12 |
| 1008 | SCOTT | m | 20 | ANALYSE | 3000 | 2003-05-12 |
| 1004 | JONES | m | 20 | MANAGER | 2975 | 1998-05-18 |
| 1011 | ADAMS | m | 20 | CLERK | 1100 | 1999-10-05 |
| 1001 | SMITH | m | 20 | CLERK | 800 | 2005-11-12 |
| 1006 | BLAKE | f | 30 | MANAGER | 2850 | 1997-02-15 |
| 1002 | ALLEN | f | 30 | SALESMAN | 1600 | 2003-05-12 |
| 1010 | TURNER | f | 30 | SALESMAN | 1500 | 1997-10-12 |
| 1003 | WARD | f | 30 | SALESMAN | 1250 | 2003-05-12 |
| 1005 | MARTIN | m | 30 | SALESMAN | 1250 | 2001-06-12 |
| 1012 | JAMES | f | 30 | CLERK | 950 | 2008-06-15 |
+------+--------+----------+---------+-----------+----------+------------+
# 8、在employee表中，查询员工姓名以字母A或S开头的员工信息
select * from employee WHERE e_name like 'A%' OR e_name LIKE 'S%';
+------+--------+----------+---------+----------+----------+------------+
| e_no | e_name | e_gender | dept_no | e_job | e_salary | hiredate |
+------+--------+----------+---------+----------+----------+------------+
| 1001 | SMITH | m | 20 | CLERK | 800 | 2005-11-12 |
| 1008 | SCOTT | m | 20 | ANALYSE | 3000 | 2003-05-12 |
| 1011 | ADAMS | m | 20 | CLERK | 1100 | 1999-10-05 |
| 1002 | ALLEN | f | 30 | SALESMAN | 1600 | 2003-05-12 |
+------+--------+----------+---------+----------+----------+------------+
# 9、在employee表中，查询到目前为止，工龄大于等于10年的员工信息
select * from employee where (year(now())-year(hireDate))>= 10;a
或者
select * from employee where year(now())-year(hiredate) >=10;
或者
select * from employee where adddate(hiredate,interval 10 year)>=2009;
select * from employee where adddate(hiredate,interval 10 year)<=date(now());
+------+--------+----------+---------+-----------+----------+------------+
| e_no | e_name | e_gender | dept_no | e_job | e_salary | hiredate |
+------+--------+----------+---------+-----------+----------+------------+
| 1007 | CLARK | m | 10 | MANAGER | 2450 | 2002-09-12 |
| 1009 | KING | f | 10 | PRESIDENT | 5000 | 1995-01-01 |
| 1001 | SMITH | m | 20 | CLERK | 800 | 2005-11-12 |
| 1004 | JONES | m | 20 | MANAGER | 2975 | 1998-05-18 |
| 1008 | SCOTT | m | 20 | ANALYSE | 3000 | 2003-05-12 |
| 1011 | ADAMS | m | 20 | CLERK | 1100 | 1999-10-05 |
| 1002 | ALLEN | f | 30 | SALESMAN | 1600 | 2003-05-12 |
| 1003 | WARD | f | 30 | SALESMAN | 1250 | 2003-05-12 |
| 1005 | MARTIN | m | 30 | SALESMAN | 1250 | 2001-06-12 |
| 1006 | BLAKE | f | 30 | MANAGER | 2850 | 1997-02-15 |
| 1010 | TURNER | f | 30 | SALESMAN | 1500 | 1997-10-12 |
| 1012 | JAMES | f | 30 | CLERK | 950 | 2008-06-15 |
+------+--------+----------+---------+-----------+----------+------------+
# 10、计算所有女员工龄（F）
select e_gender,e_name, year(now())-year(hiredate) as gongling from employee where e_gender='f';
+----------+--------+----------+
| e_gender | e_name | gongling |
+----------+--------+----------+
| f | KING | 25 |
| f | ALLEN | 17 |
| f | WARD | 17 |
| f | BLAKE | 23 |
| f | TURNER | 23 |
| f | JAMES | 12 |
+----------+--------+----------+
# 11、使用LIMIT查询从第3条记录开始到第6条记录
select * from employee limit 2,4;
# 12、查询销售人员（SALSEMAN）的最低工资
select min(e_salary) as zuidigongzi from employee group by e_job having
+------+--------+----------+---------+---------+----------+------------+
| e_no | e_name | e_gender | dept_no | e_job | e_salary | hiredate |
+------+--------+----------+---------+---------+----------+------------+
| 1001 | SMITH | m | 20 | CLERK | 800 | 2005-11-12 |
| 1004 | JONES | m | 20 | MANAGER | 2975 | 1998-05-18 |
| 1008 | SCOTT | m | 20 | ANALYSE | 3000 | 2003-05-12 |
| 1011 | ADAMS | m | 20 | CLERK | 1100 | 1999-10-05 |
+------+--------+----------+---------+---------+----------+------------+
# 13、查询名字以字母N或者S结尾的记录
select e_name from employee where e_name like '%N' or e_name like '%S';
+--------+
| e_name |
+--------+
| JONES |
| ADAMS |
| ALLEN |
| MARTIN |
| JAMES |
+--------+
# 14、查询在BeiJing工作的源的姓名和职务
select e_name,e_job from employee where dept_no in (select d_no from dept where d_location='BEIJING');
+--------+---------+
| e_name | e_job |
+--------+---------+
| SMITH | CLERK |
| JONES | MANAGER |
| SCOTT | ANALYSE |
| ADAMS | CLERK |
+--------+---------+
# 15、使用左连接方式查询employee和dept表
select * from employee left join dept on employee.e_no = dept.d_no;
+------+--------+----------+---------+-----------+----------+------------+------+--------+------------+
| e_no | e_name | e_gender | dept_no | e_job | e_salary | hiredate | d_no | d_name | d_location |
+------+--------+----------+---------+-----------+----------+------------+------+--------+------------+
| 1007 | CLARK | m | 10 | MANAGER | 2450 | 2002-09-12 | NULL | NULL | NULL |
| 1009 | KING | f | 10 | PRESIDENT | 5000 | 1995-01-01 | NULL | NULL | NULL |
| 1001 | SMITH | m | 20 | CLERK | 800 | 2005-11-12 | NULL | NULL | NULL |
| 1004 | JONES | m | 20 | MANAGER | 2975 | 1998-05-18 | NULL | NULL | NULL |
| 1008 | SCOTT | m | 20 | ANALYSE | 3000 | 2003-05-12 | NULL | NULL | NULL |
| 1011 | ADAMS | m | 20 | CLERK | 1100 | 1999-10-05 | NULL | NULL | NULL |
| 1002 | ALLEN | f | 30 | SALESMAN | 1600 | 2003-05-12 | NULL | NULL | NULL |
| 1003 | WARD | f | 30 | SALESMAN | 1250 | 2003-05-12 | NULL | NULL | NULL |
| 1005 | MARTIN | m | 30 | SALESMAN | 1250 | 2001-06-12 | NULL | NULL | NULL |
| 1006 | BLAKE | f | 30 | MANAGER | 2850 | 1997-02-15 | NULL | NULL | NULL |
| 1010 | TURNER | f | 30 | SALESMAN | 1500 | 1997-10-12 | NULL | NULL | NULL |
| 1012 | JAMES | f | 30 | CLERK | 950 | 2008-06-15 | NULL | NULL | NULL |
+------+--------+----------+---------+-----------+----------+------------+-----
# 16、使用LIKE查询员工姓名中包括字母a的记录
select * from employee where e_name like '%a%';
+------+--------+----------+---------+----------+----------+------------+
| e_no | e_name | e_gender | dept_no | e_job | e_salary | hiredate |
+------+--------+----------+---------+----------+----------+------------+
| 1007 | CLARK | m | 10 | MANAGER | 2450 | 2002-09-12 |
| 1011 | ADAMS | m | 20 | CLERK | 1100 | 1999-10-05 |
| 1002 | ALLEN | f | 30 | SALESMAN | 1600 | 2003-05-12 |
| 1003 | WARD | f | 30 | SALESMAN | 1250 | 2003-05-12 |
| 1005 | MARTIN | m | 30 | SALESMAN | 1250 | 2001-06-12 |
| 1006 | BLAKE | f | 30 | MANAGER | 2850 | 1997-02-15 |
| 1012 | JAMES | f | 30 | CLERK | 950 | 2008-06-15 |
+------+--------+----------+---------+----------+----------+------------+
更新数据update
# 语法: update 表名 SET 字段1=值1，字段2=值2... WHERE CONDITION;
# 表的修饰符
# not null 标识该字段不能为空
# default 为该字段设置默认值
mysql> update student2 set name='flying' where id=2; # 匹配id=2的哪一行，并将name字段换成flying；
mysql> update student2 set name='flying' where d=3 and sex='female'; # 此处and也可以是or
# 添加字段
mysql> alter table student2 add id int(3) not null default 22, add gender enum('M','WW');
1、单条件查询：where
select 字段 from 表名 where 条件判断;
select 字段 from 表名 where 条件判断 and 条件判断;
select 字段 from 表名 where 条件判断(between A and B); --> 范围
eg： select name from haha where id between 0 and 2;
+------+
| name |
+------+
| ljt |
+------+
1 row in set (0.00 sec)
SELECT name, salary FROM employee5 WHERE salary NOT IN (4000,5000,6000,9000) ;
SELECT name,job_description from employee5 where job_description is null;(字段为空)
SELECT * FROM employee5 WHERE name LIKE 'al%'; %代表多个字符
SELECT * FROM employee5 WHERE name LIKE 'al___'; --> "_"下划线代表一个字符
正则：' = ' 精确匹配；'like' 模糊查询； ' regexp '正则查询
select * from student where register_date > '2020-02-13';
# 使用主键来作为WHERE子句的条件查询是非常快速的.
2、排序：order by 字段(一般此字段都是数字，排个成绩啥的)
单列：默认升序，在末尾加DESC则变为降序；
mysql> select id from haha order by id DESC;
+----+
| id |
+----+
| 4 |
| 3 |
| 2 |
| 1 |
+----+
4 rows in set (0.00 sec)
多列：先按第一条件，在第一条件框架中再按第二条件
eg： mysql> select id from haha order by money,id;
内外连接区别
内连接只能显示出两表中符合条件的字段
外连接可以显示主表的全部字段，未能匹配到副表的显示null
内连接：SELECT 字段列表 FROM 表1 ,表2 WHERE 表1.字段 = 表2.字段;
eg:select info.name,info.age,info.dep_num,department.dep_num from info,department where info.dep__num = department.dep_num;
