MySQL（增删改查）

MySQL服务的启动和停止

方式一：计算机——右击管理——服务
方式二：通过管理员身份运行

​	net start 服务名（启动服务）
​	net stop 服务名（停止服务）

MySQL服务的登录和退出

方式一：通过mysql自带的客户端
只限于root用户

方式二：通过windows自带的客户端
登录：
mysql 【-h主机名 -P端口号 】-u用户名 -p密码

退出：
exit或ctrl+C

MySQL的常见命令

1.查看当前所有的数据库
show databases;
2.打开指定的库
use 库名
3.查看当前库的所有表
show tables;
4.查看其它库的所有表
show tables from 库名;
5.创建表
create table 表名(

	列名 列类型,
	列名 列类型，
	。。。
);
6.查看表结构
desc 表名;
7.查看服务器的版本
方式一：登录到mysql服务端
select version();
方式二：没有登录到mysql服务端
mysql --version
或
mysql --V

MySQL的语法规范

1. 不区分大小写,但建议关键字大写，表名、列名小写
2. 每条命令最好用分号结尾
3. 每条命令根据需要，可以进行缩进 或换行
4. 注释

​		单行注释：#注释文字
​		单行注释：-- 注释文字
​		多行注释：/* 注释文字  */

SQL的语言分类

​	DQL（Data Query Language）：数据查询语言
​		select 
​	DML(Data Manipulate Language):数据操作语言
​		insert 、update、delete
​	DDL（Data Define Languge）：数据定义语言
​		create、drop、alter
​	TCL（Transaction Control Language）：事务控制语言
​		commit、rollback
	DCL（Data Control Language）：
       是数据库控制功能。是用来设置或更改数据库用户或角色权限的语句，包括（grant,deny,revoke等）语句。在默认状态下，只有sysadmin,dbcreator,db_owner或db_securityadmin等人员才有权力执行DCL

DQL语言(查询)

进阶1：基础查询

​ 语法：

​	SELECT 要查询的东西
​	【FROM 表名】;

类似于Java中 :System.out.println(要打印的东西);
特点：
①通过select查询完的结果 ，是一个虚拟的表格，不是真实存在
② 要查询的东西 可以是常量值、可以是表达式、可以是字段、可以是函数

案例

起别名

#方式一：使用as
SELECT 100%98 AS 结果;
SELECT last_name AS 姓,first_name AS 名 FROM employees;

#方式二：使用空格
SELECT last_name 姓,first_name 名 FROM employees;

去重

SELECT DISTINCT department_id FROM employees;

进阶2：条件查询

​ 条件查询：根据条件过滤原始表的数据，查询到想要的数据
​ 语法：

​	select 
​		要查询的字段|表达式|常量值|函数
​	from 
​		表
​	where 
​		条件 ;

分类：
一、条件表达式

示例：salary>10000
条件运算符：
< >= <= = != <>

二、逻辑表达式

示例：salary>10000 && salary<20000

逻辑运算符：

and（&&）:两个条件如果同时成立，结果为true，否则为false
or(||)：两个条件只要有一个成立，结果为true，否则为false
not(!)：如果条件成立，则not后为false，否则为true

三、模糊查询
特点：

①一般和通配符搭配使用
	通配符：
	% 任意多个字符,包含0个字符
	_ 任意单个字符

示例：last_name like ‘a%’

案例

#逻辑表达式
#案例2：查询部门编号不是在90到110之间，或者工资高于15000的员工信息
SELECT
	*
FROM
	employees
WHERE
	NOT(department_id>=90 AND  department_id<=110) OR salary>15000;

#模糊查询
#案例1：查询员工名中包含字符a的员工信息
select 
	*
from
	employees
where
	last_name like '%a%';#abc

进阶3：排序查询

语法：

select
	要查询的东西
from
	表
where 
	条件
order by 排序的字段|表达式|函数|别名 【asc|desc】

特点：

1、asc代表的是升序，可以省略
desc代表的是降序

2、order by子句可以支持 单个字段、别名、表达式、函数、多个字段

3、order by子句在查询语句的最后面，除了limit子句

案例

#3、按表达式排序
#案例：查询员工信息 按年薪降序

SELECT *,salary*12*(1+IFNULL(commission_pct,0))
FROM employees
ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;

#5、按函数排序
#案例：查询员工名，并且按名字的长度降序

SELECT LENGTH(last_name),last_name 
FROM employees
ORDER BY LENGTH(last_name) DESC;

#6、按多个字段排序
#案例：查询员工信息，要求先按工资降序，再按employee_id升序

SELECT *
FROM employees
ORDER BY salary DESC,employee_id ASC;

进阶4：常见函数

​ 一、单行函数
​ 1、字符函数
​ concat拼接
​ substr截取子串
​ upper转换成大写
​ lower转换成小写
​ trim去前后指定的空格和字符
​ ltrim去左边空格
​ rtrim去右边空格
​ replace替换
​ lpad左填充
​ rpad右填充
​ instr返回子串第一次出现的索引
​ length 获取字节个数
​
​ 2、数学函数
​ round 四舍五入
​ rand 随机数
​ floor向下取整
​ ceil向上取整
​ mod取余
​ truncate截断
​ 3、日期函数
​ now当前系统日期+时间
​ curdate当前系统日期
​ curtime当前系统时间
​ str_to_date 将字符转换成日期
​ date_format将日期转换成字符
​ 4、流程控制函数
​ if 处理双分支
​ case语句 处理多分支
​ 情况1：处理等值判断
​ 情况2：处理条件判断
​
​ 5、其他函数
​ version版本
​ database当前库
​ user当前连接用户

二、分组函数

sum 求和
max 最大值
min 最小值
avg 平均值
count 计数

特点：

1、以上五个分组函数都忽略null值，除了count()
2、sum和avg一般用于处理数值型
max、min、count可以处理任何数据类型
3、都可以搭配distinct使用，用于统计去重后的结果
4、count的参数可以支持：
字段、、常量值，一般放1

 建议使用 count(*)

进阶5：分组查询

​ 语法：

​	select 查询的字段，分组函数
​	from 表
​	group by 分组的字段

特点：

1、可以按单个字段分组
2、和分组函数一同查询的字段最好是分组后的字段
3、分组筛选
		针对的表	位置			关键字
分组前筛选：	原始表		group by的前面		where
分组后筛选：	分组后的结果集	group by的后面		having

4、可以按多个字段分组，字段之间用逗号隔开
5、可以支持排序
6、having后可以支持别名

案例

#案例2：每个工种有奖金的员工的最高工资>12000的工种编号和最高工资

SELECT job_id,MAX(salary)
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary)>12000;

#5.按多个字段分组
#案例：查询每个工种每个部门的最低工资,并按最低工资降序

SELECT MIN(salary),job_id,department_id
FROM employees
GROUP BY department_id,job_id
ORDER BY MIN(salary) DESC;

#4.查询所有部门的编号，员工数量和工资平均值,并按平均工资降序
SELECT department_id,COUNT(*),AVG(salary) a
FROM employees
GROUP BY department_id
ORDER BY a DESC;

进阶6：多表连接查询

笛卡尔乘积：如果连接条件省略或无效则会出现
解决办法：添加上连接条件

一、传统模式下的连接 ：等值连接——非等值连接

1.等值连接的结果 = 多个表的交集
2.n表连接，至少需要n-1个连接条件
3.多个表不分主次，没有顺序要求
4.一般为表起别名，提高阅读性和性能

二、sql99语法：通过join关键字实现连接

含义：1999年推出的sql语法
支持：
等值连接、非等值连接 （内连接）
外连接
交叉连接

语法：

select 字段，...
from 表1
【inner|left outer|right outer|cross】join 表2 on  连接条件
【inner|left outer|right outer|cross】join 表3 on  连接条件
【where 筛选条件】
【group by 分组字段】
【having 分组后的筛选条件】
【order by 排序的字段或表达式】

好处：语句上，连接条件和筛选条件实现了分离，简洁明了！

左连接

#一、查询编号>3的女神的男朋友信息，如果有则列出详细，如果没有，用null填充

SELECT b.id,b.name,bo.*
FROM beauty b
LEFT OUTER JOIN boys bo
ON b.`boyfriend_id` = bo.`id`
WHERE b.`id`>3;

右连接
​#二、查询哪个城市没有部门

SELECT city
FROM departments d
RIGHT OUTER JOIN locations l
ON d.location_id=l.location_id
WHERE d.department_id IS NULL;

三、自连接

案例：查询员工名和直接上级的名称

sql99

SELECT e.last_name,m.last_name
FROM employees e
JOIN employees m ON e.`manager_id`=m.`employee_id`;

sql92

SELECT e.last_name,m.last_name
FROM employees e,employees m 
WHERE e.`manager_id`=m.`employee_id`;

左连接和有连接的区别

左连接：是已左边表中的数据为基准，若左表有数据右表没有数据，则显示左表中的数据右表中的数据显示为空。（左边有的，右边没有的为null）
左边的表是全部数据，右边的只有符合条件的才有数据；

右联接：是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。（左边没有的，右边有的为null ）
右连接 右边的表数据全，左边的符合条件的有数据；

内连接和外连接的区别

内连接是只有符合条件的才显示；

MySql不支持全连接FUll JOIN，不过可以通过联合UNION来模拟。
两个表数据都有

案例

第一张表
id：1，2，3
第二张
id：1，2，4

全
都有

内
id:1，2

进阶7：子查询

含义：

一条查询语句中又嵌套了另一条完整的select语句，其中被嵌套的select语句，称为子查询或内查询在外面的查询语句，称为主查询或外查询

特点：

1、子查询都放在小括号内
2、子查询可以放在from后面、select后面、where后面、having后面，但一般放在条件的右侧
3、子查询优先于主查询执行，主查询使用了子查询的执行结果
4、子查询根据查询结果的行数不同分为以下两类：
① 单行子查询
	结果集只有一行
	一般搭配单行操作符使用：> < = <> >= <= 
	非法使用子查询的情况：
	a、子查询的结果为一组值
	b、子查询的结果为空
	
② 多行子查询
	结果集有多行
	一般搭配多行操作符使用：any、all、in、not in
	in： 属于子查询结果中的任意一个就行
	any和all往往可以用其他查询代替

案例

#③ 在②基础上筛选，满足min(salary)>①
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
	SELECT  MIN(salary)
	FROM employees
	WHERE department_id = 50
);

#③查询部门信息
SELECT *
FROM departments
WHERE department_id=(
	SELECT department_id
	FROM employees
	GROUP BY department_id
	ORDER BY AVG(salary) 
	LIMIT 1
);

进阶8：分页查询

应用场景：实际的web项目中需要根据用户的需求提交对应的分页查询的sql语句
语法：

select 字段|表达式,...
from 表
【where 条件】
【group by 分组字段】
【having 条件】
【order by 排序的字段】
limit 【起始的条目索引，】条目数;

特点：

1.起始条目索引从0开始

2.limit子句放在查询语句的最后

3.公式：select * from  表 limit （page-1）*sizePerPage,sizePerPage
假如:
每页显示条目数sizePerPage
要显示的页数 page

案例

#案例2：查询第11条——第25条
SELECT * FROM  employees LIMIT 10,15;

select * from table limit 2,1;
//跳过2条取出1条数据，limit后面是从第2条开始读，读取1条信息，即读取第3条数据


select * from table limit 2 offset 1;
//从第1条（不包括）数据开始取出2条数据，limit后面跟的是2条数据，offset后面是从第1条开始读取，即读取第2,3条

进阶9：联合查询

引入：
union 联合、合并

语法：

select 字段|常量|表达式|函数 【from 表】 【where 条件】 union 【all】
select 字段|常量|表达式|函数 【from 表】 【where 条件】 union 【all】
select 字段|常量|表达式|函数 【from 表】 【where 条件】 union  【all】
.....
select 字段|常量|表达式|函数 【from 表】 【where 条件】

特点：

1、多条查询语句的查询的列数必须是一致的
2、多条查询语句的查询的列的类型几乎相同
3、union代表去重，union all代表不去重

案例

#引入的案例：查询部门编号>90或邮箱包含a的员工信息

SELECT * FROM employees WHERE email LIKE '%a%' OR department_id>90;;

SELECT * FROM employees  WHERE email LIKE '%a%'
UNION
SELECT * FROM employees  WHERE department_id>90;


#案例：查询中国用户中男性的信息以及外国用户中年男性的用户信息

SELECT id,cname FROM t_ca WHERE csex='男'
UNION ALL
SELECT t_id,tname FROM t_ua WHERE tGender='male';

DML语言

插入

语法：

insert into 表名(字段名，...)
values(值1，...);

特点：

1、字段类型和值类型一致或兼容，而且一一对应
2、可以为空的字段，可以不用插入值，或用null填充
3、不可以为空的字段，必须插入值
4、字段个数和值的个数必须一致
5、字段可以省略，但默认所有字段，并且顺序和表中的存储顺序一致

案例

#4.列数和值的个数必须一致
INSERT INTO beauty(NAME,sex,id,phone)
VALUES('关晓彤','女',17,'110');

#5.可以省略列名，默认所有列，而且列的顺序和表中列的顺序一致
INSERT INTO beauty
VALUES(18,'张飞','男',NULL,'119',NULL,NULL);

#方式二：
/*

语法：
insert into 表名
set 列名=值,列名=值,...
*/


INSERT INTO beauty
SET id=19,NAME='刘涛',phone='999';


#两种方式大pk ★


#1、方式一支持插入多行,方式二不支持

INSERT INTO beauty
VALUES(23,'唐艺昕1','女','1990-4-23','1898888888',NULL,2)
,(24,'唐艺昕2','女','1990-4-23','1898888888',NULL,2)
,(25,'唐艺昕3','女','1990-4-23','1898888888',NULL,2);

#2、方式一支持子查询，方式二不支持

INSERT INTO beauty(id,NAME,phone)
SELECT 26,'宋茜','11809866';

INSERT INTO beauty(id,NAME,phone)
SELECT id,boyname,'1234567'
FROM boys WHERE id<3;

修改

修改单表语法：

update 表名 set 字段=新值,字段=新值
【where 条件】

修改多表语法：

update 表1 别名1,表2 别名2
set 字段=新值，字段=新值
where 连接条件
and 筛选条件

删除

方式1：delete语句

单表的删除： ★

delete from 表名 【where 筛选条件】

多表的删除：

delete 别名1，别名2
from 表1 别名1，表2 别名2
where 连接条件
and 筛选条件;

方式2：truncate语句

truncate table 表名

两种方式的区别【面试题】

#1.truncate不能加where条件，而delete可以加where条件

#2.truncate的效率高一丢丢

#3.truncate 删除带自增长的列的表后，如果再插入数据，数据从1开始
#delete 删除带自增长列的表后，如果再插入数据，数据从上一次的断点处开始

#4.truncate删除不能回滚，delete删除可以回滚

案例

#2.多表的删除

#案例：删除张无忌的女朋友的信息

DELETE b
FROM beauty b
INNER JOIN boys bo ON b.`boyfriend_id` = bo.`id`
WHERE bo.`boyName`='张无忌';

#方式二：truncate语句

#案例：将魅力值>100的男神信息删除
TRUNCATE TABLE boys ;

注意

聚合函数无法用在WHERE中
如

错误案例

SELECT MIN(salary),manager_id
FROM employees
WHERE manager_id IS NOT NULL and MIN(salary)>=6000
GROUP BY manager_id;

正确写法

SELECT MIN(salary),manager_id
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING MIN(salary)>=6000;