三个库 :student+girls+myemployees
链接:https://pan.baidu.com/s/1R2Aoh-nLSDKh9vfj0AG4XA
提取码:jl9b
阿茄的数据库学习笔记,将持续更新。。。。。。。。
基础部分知识已经完结,有机会再适当添加补充修改哈。
欢迎大家留言交流讨论蛤
文章目录
MySQL初识
MySQL服务的启动和停止
启动:net start mysql5.5
停止:net stop mysql5.5
MySQl服务的登入和退出
登入:mysql -h localhost -P 3306 -u root -p
退出:exit
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
MySQL常用命令
显示所有数据:show databases;
进入数据库中的某一个库(比如test库):use test;
显示所在库的表:show tables;
显示其他库的表:show tables from test;
查看自己在哪一个库:select database();
显示表的具体结构(列):desc 表名;(不用show)= SHOW COLUMNS FROM 表名;
查看表的所有数据:select * from 表名;
DQL语言
进阶一:基础查询
select 查询列表(表中字段、常量值、表达式、函数) from 表名;
USE myemployees
SELECT first_name,last_name FROM employees;
SELECT * FROM employees
SELECT 100;
SELECT 100*90;
/*+号的特殊用法*/
SELECT '100'+90;#190
SELECT NULL+90;#NULL
SELECT 'chat'+90;#90
SELECT 'chat'+'char';#0
/*起别名*/
SELECT last_name 姓,first_name AS 名 FROM employees;
SELECT salary "out put" FROM employees;#有空格加引号
/*去重*/
SELECT department_id FROM employees;#有重覆
SELECT DISTINCT department_id FROM employees;
/*拼接*/
SELECT CONCAT(last_name,' ',first_name) AS "姓 名" FROM employees;
进阶二:条件查询
select 查询列表(表中字段、常量值、表达式、函数) from 表名 where 筛选条件;
分类:
一、按条件运算符筛选: >,<,=,!=(<>),<=,>=
二、按逻辑运算符筛选:&&(and) ||(or) !(not)
三、模糊查询:like, between and, in, is null
模糊查询:
LIKE:配合通配符使用;
BETWEEN AND:包含等号;顺序不能颠倒;
IN:括号里面内容不支持通配符;
IS NULL:=不能判断NULL;<=>可以判断NULL和其他(=升级版:安全等于)
IS NOT NULL:判断是否非空;
IS NULL VS. <=> VS. =(三者比较):
IS NULL:只能判断是否为空;
<=>:可以判断是否为空和普通数值;
=:只能判断普通数值;USE `myemployees`
/*条件表达式*/
SELECT * FROM employees WHERE department_id=90;
SELECT last_name,department_id FROM employees WHERE department_id=90;
/*逻辑表达式*/
#查询部门编号不是90-100之间,或者工资高于30000的员工信息
SELECT * FROM employees WHERE NOT(department_id >=90 AND department_id<=100) OR salary>30000;
/*模糊查询*/
/*LIKE:匹配*/
#查询员工名中包含a字符的员工信息
SELECT last_name FROM employees WHERE last_name LIKE '%a%';#包含a
SELECT last_name FROM employees WHERE last_name LIKE 'a%';#以a开头
SELECT last_name FROM employees WHERE last_name LIKE '%a';#以a结尾
SELECT last_name FROM employees WHERE last_name LIKE '_a_a%';
SELECT last_name FROM employees WHERE last_name LIKE '_\_%';#转义
SELECT last_name FROM employees WHERE last_name LIKE '_&_%' ESCAPE '&';#自定义转义
/*BETWEEN AND*/
SELECT * FROM employees WHERE salary>=10000 AND salary<=30000;
SELECT * FROM employees WHERE salary BETWEEN 10000 AND 30000;#10000~30000
/*IN:属于*/
#查询工种编号为‘SH_CLERK’或‘AD_VP’的员工信息
SELECT * FROM employees WHERE job_id = 'SH_CLERK' OR job_id='AD_VP';
SELECT * FROM employees WHERE job_id IN('SH_CLERK','AD_VP');
/*IS NULL*/
#查询没有奖金的员工姓名和工资
#SELECT CONCAT(last_name,' ',first_name) AS '姓 名', salary FROM employees WHERE commission_pct= null;#错误写法
SELECT CONCAT(last_name,' ',first_name) AS '姓 名', salary FROM employees WHERE commission_pct <=> NULL;
SELECT CONCAT(last_name,' ',first_name) AS '姓 名', salary FROM employees WHERE commission_pct IS NULL;
/*IS NOT NULL*/
#查询有奖金的员工姓名和工资
SELECT CONCAT(last_name,' ',first_name) AS '姓 名', salary FROM employees WHERE commission_pct IS NOT NULL;
#查询员工号为176的员工的姓名,部门编号和年薪
SELECT
CONCAT(last_name,' ',first_name) AS '姓 名',
department_id,
salary*12*(1+IFNULL(commission_pct,0)) AS '年 薪'
FROM
employees
WHERE
employee_id = 176;
面试案例:
SELECT * FROM employees;#(1)
SELECT * FROM employees WHERE commission_pct LIKE '%%' AND last_name LIKE '%%';#like不能匹配null,所以结果和(1)不一样
SELECT * FROM employees WHERE commission_pct LIKE '%%' OR last_name LIKE '%%';#last_name没有null,所以结果和(1)一样
进阶三:排序查询
select 查询列表
from 表
where 条件
order by 排序的字段|表达式|函数|别名 (asc/desc)(不写默认升序);
#查询部门编号>=90的员工信息,并按照薪水降序排序
SELECT * FROM employees WHERE department_id>=90 ORDER BY salary DESC;
#先按工资降序排,再按员工编号降序排:支持多字段排序
SELECT * FROM employees WHERE department_id>=90 ORDER BY salary DESC, employee_id DESC;
进阶四:常用函数
单行函数
字符函数
- length:获取字节个数(汉字占用三个字节)(只有length以字节为尺度)
- concat: 拼接字符串
- substr:截取子串
#与java中不同,需要留意
SELECT SUBSTR('fatcat',2) AS out_put;#actcat:索引从1开始而不是0
SELECT SUBSTR('fatcat',1,4) AS out_put;#fatc:第二位表示截取长度而不是结束索引
- instr:返回子串第一次出现的索引,找不到返回0
SELECT INSTR('fatcat','cat') AS out_put;#4
- upper:转换成大写
- lower:转换成小写
- trim:去前后指定的空格和字符
#只去前后(不去中间)指定字符,不指定指的空格
SELECT TRIM('k' FROM 'kkkkfatkkkcatkkkk') AS out_put;#fatkkkcat
- ltrim:去左边空格(只能去空格不能指定)
- rtrim:去右边空格(只能去空格不能指定)
- replace:替换
#全部替换
SELECT REPLACE('catfatcatcat','cat','dog') AS out_put;#dogfatdogdog
- lpad:左填充
- rpad:右填充
#中间是最终总长度而不是需要填充的长度
#无论左填充还是右填充,长度不足是均是从左到右截取
SELECT LPAD('fatcat',8,'k') AS out_put;#kkfatcat
SELECT LPAD('fatcat',3,'k') AS out_put;#fat
SELECT RPAD('fatcat',8,'k') AS out_put;#fatcatkk
SELECT RPAD('fatcat',3,'k') AS out_put;#fat
数学函数
- round:四舍五入
SELECT ROUND(-1.54) AS out_put;#-2
SELECT ROUND(-1.547,2) AS out_put;#-1.55:表示小数点后保留两位
- rand:随机数([0,1)
#用rand产生[i,j]的随机数:floor(i+rand()*(j-i+1))
- floor:向下取整
- ceil:向上取整
SELECT CEIL(-1.1) AS out_put;#-1
SELECT FLOOR(-9.9) AS out_put;#-10
- mod:取余
#符号和被除数一致(a-a/b*b)
SELECT MOD(-10,3) AS out_put;#-1
SELECT MOD(10,-3) AS out_put;#1
- truncate:截断
SELECT TRUNCATE(-9.987,2) AS out_put;#-9.98:小数点后保留两位,直接截断,不用四舍五入
日期函数
- now:当前系统日期+时间
- curdate:当前系统日期
- curtime:当前系统时间
- year: 获取年(同理:month,day,hour,minute,second)
- str_to_date:将字符转换成日期
- date_format:将日期转换成字符
- datediff:求两个日期的天数差
流程控制函数(挺有意思的)
- if:类似三元运算符
if(条件表达式,表达式1,表达式2):如果条件表达式成立,返回表达式1,否则返回表达式2
SELECT IF(10>5,'large','small');#large
- case:
case 情况1
case 变量或表达式或字段
when 常量1 then 值1
when 常量2 then 值2
...
else 值n
end
#根据部门编号就算加权工资
SELECT salary,department_id,
CASE department_id
WHEN 50 THEN salary*2
WHEN 80 THEN salary*3
WHEN 100 THEN salary*4
ELSE salary
END AS 'wighted salary'
FROM employees
ORDER BY department_id
case 情况2
case
when 条件1 then 值1
when 条件2 then 值2
...
else 值n
end
#根据工资显示级别
SELECT last_name,salary,
CASE
WHEN salary>=20000 THEN 'A'
WHEN salary>=10000 THEN 'B'
WHEN salary>=5000 THEN 'C'
ELSE 'D'
END AS '级别'
FROM employees
ORDER BY 级别
其他函数
- version(): 版本
- database(): 当前库
- user(): 当前连接用户
分组函数(统计函数)
- max:最大值
- min:最小值
- sum:和
- avg:平均值
- count:计算个数
特点:
1、以上五个分组函数都忽略null值,除了count(*)
2、sum和avg一般用于处理数值型,max、min、count可以处理任何数据类型
3、都可以搭配distinct使用,用于统计去重后的结果:select sum(distinct salary) from employees;
4、count的参数可以支持:字段、*、常量值,一般放1,建议使用 count(*)
5、count(1)和count(*):统计结果集的行数
6、效率上:MyISAM存储引擎,count(*)最高;InnoDB存储引擎,count(*)和count(1)效率>count(字段)
7、和分组函数一同查询的字段,要求是group by后出现的字段
进阶五:分组查询
语法:
select 分组函数/group by后面的字段
from 表
(where 分组前筛选条件)
group by 分组的字段(即,按该字段分组)
(having 分组后筛选条件)
(order by 子句)
特点:
1、可以按单个字段分组
2、和分组函数一同查询的字段最好是分组后的字段
3、分组筛选
分组前筛选:原始表,group by的前面,关键字where
分组后筛选:分组后的结果集,group by的后面,关键字having
4、可以按多个字段分组,字段之间用逗号隔开
5、可以支持排序
6、having后可以支持别名
/*单字段分组查询*/
#下述两种写法效果一样(都是按照部门分组输出,不重复)
SELECT department_id
FROM employees
GROUP BY department_id;
SELECT DISTINCT department_id
FROM employees;
#查询每个领导手下有奖金的员工的最高工资
SELECT MAX(salary),manager_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;
#查询没有奖金的员工所属的部门的最低工资
SELECT MIN(salary),department_id
FROM employees
WHERE commission_pct IS NULL
GROUP BY department_id;
#(上面案例相当于先筛选后分组,下面这个案例需要先分组计算出相应结果后再筛选)
#查询哪个部门的员工数大于7
#首先得先查出各个部门的员工数,然后判断是否大于7
#引入新关键字having(用于分组后的筛选)
SELECT COUNT(*), department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>7;
#查询公种编号和员工最高工资(要求该工种下有奖金的员工的工资最高工资高于12000)
SELECT MAX(salary),job_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary)>12000;
#查询领导ID(要求ID>102且手下员工的最低工资高于5000)
SELECT manager_id
FROM employees
WHERE manager_id>102
GROUP BY manager_id
HAVING MIN(salary)>5000;
/*多字段分组查询*/
#查询每个部门每个工种的平均工资
SELECT AVG(salary),department_id,job_id
FROM employees
GROUP BY department_id,job_id;
进阶六:多表连接查询
笛卡尔乘积现象:如果连接条件省略或无效则会出现全部匹配的情况(表1为m行,表2为n行,输出结果为m*n行)。
解决办法:添加上连接条件。
分类:
按标准分类:
sql92(在mysql中仅支持内连接)
sql99(在mysql中不支持全外连接)
按功能分类:
内连接:等值连接、非等值连接、自连接
外连接:左外连接、右外连接、全外连接
交叉连接
思想:多张表的匹配,没有加连接条件情况下就是每一行匹配,加上连接条件相当与加了约束后匹配。
内连接
(表的交集)
sql92标准等值连接:
#查询员工名和对应的部门名
SELECT CONCAT(last_name,' ',first_name) fullname, department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id;
#查询员工名及其对应的工种号和工种名(为表起别名)
SELECT last_name,e.job_id,job_title
FROM employees AS e,jobs AS j
WHERE e.job_id = j.job_id;
#查询每个工种的工种名和员工的个数,并按员工个数降序排序
SELECT job_title, COUNT(*)
FROM employees e ,jobs j
WHERE e.job_id = j.job_id
GROUP BY job_title
ORDER BY COUNT(*) DESC;
#三表连接:查询员工名、部门名和所在城市
SELECT last_name,department_name,city
FROM employees e,departments d,locations l
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id
AND city LIKE 's%'
ORDER BY LENGTH(city);
sql92标准非等值连接:
#查询员工的工资和工资级别
SELECT salary, grade_level
FROM employees e, job_grades g
WHERE salary BETWEEN g.lowest_sal AND g.highest_sal
ORDER BY grade_level;
sql92标准自连接:
#利用给同一张表其不同的别名实现区分
#查询员工名和其领导的名字
SELECT e.employee_id,e.last_name, m.employee_id 对应领导ID, m.last_name 对应领导名
FROM employees e,employees m
WHERE e.manager_id = m.employee_id;
sql99语法结构:
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 排序的字段或表达式】
好处:语句上,连接条件和筛选条件实现了分离,简洁明了!
sql99标准等值连接:
#查询部门个数大于3的城市名和相应的部门个数
#sql92
SELECT city,COUNT(*)
FROM locations l,departments d
WHERE l.location_id = d.location_id
GROUP BY city
HAVING COUNT(*)>3;
#sql99
SELECT city,COUNT(*)
FROM locations l
INNER JOIN departments d
ON l.location_id = d.location_id
GROUP BY city
HAVING COUNT(*)>3;
#查询员工名、部门名、工种名、并按部门名降序排序(三表连接)
#sql92
SELECT last_name,department_name,job_title
FROM employees e,departments d,jobs j
WHERE e.department_id = d.department_id
AND j.job_id = e.job_id
ORDER BY department_name DESC;
#sql99
SELECT last_name,department_name,job_title
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id
INNER JOIN jobs j
ON j.job_id = e.job_id
ORDER BY department_name DESC;
sql99标准非等值连接:
#查询员工的工资和工资级别
SELECT salary, grade_level
FROM employees e
INNER JOIN job_grades g
ON salary BETWEEN g.lowest_sal AND g.highest_sal
ORDER BY grade_level;
sql99标准自连接:
#利用给同一张表其不同的别名实现区分
#查询员工名和其领导的名字
SELECT e.employee_id,e.last_name, m.employee_id 对应领导ID, m.last_name 对应领导名
FROM employees e
INNER JOIN employees m
ON e.manager_id = m.employee_id;
外连接
(查询一张表中有,另一张表中没有的记录)
特点:
1、外连接会显示主表的所有记录
如果从表中有和它匹配的,则显示匹配的值;
如果从表中没有和它匹配的,则显示null;
外连接查询=内连接结果+主表中有但从表中没有的记录
2、左外连接:left join左边是主表
右外连接:right join右边是主表
3、左外和右外交换两个表顺序,结果一致;
sql99左外和右外连接:(查询主表中有,从表中没有的记录)
#查询那个部门没有员工
SELECT department_name,d.department_id,e.employee_id
FROM departments d
LEFT JOIN employees e
ON d.department_id = e.department_id
WHERE e.employee_id IS NULL;
SELECT department_name,d.department_id,e.employee_id
FROM employees e
RIGHT JOIN departments d
ON d.department_id = e.department_id
WHERE e.employee_id IS NULL;
sql99全外连接:(内连接+表一中有表二没有记录+表二中有表一没有记录)
mysql不支持。
交叉连接
害,就是笛卡尔乘积的叫法糖而已,用cross join表征。
进阶七:子查询(内查询)
含义:
一条查询语句中又嵌套了另一条完整的select语句,其中被嵌套的select语句,称为子查询或内查询;
在外面的查询语句,称为主查询或外查询;
分类:
1、按出现位置
select后面:
仅仅支持标量子查询
from后面:
表子查询
where/having后面:(重点)
标量子查询
列子查询
行子查询
exists后面:
标量子查询
列子查询
行子查询
表子查询
2、按结果集的行列
标量子查询(单行子查询):结果集为一行一列
列子查询(多行子查询):结果集为多行一列
行子查询:结果集一般为一行多列(也可以多行多列)
表子查询:结果集一般为多行多列
特点:
1、子查询都放在小括号内
2、子查询可以放在from后面、select后面、where后面、having后面,但一般放在条件的右侧
3、子查询优先于主查询执行,主查询使用了子查询的执行结果
4、子查询根据查询结果的行数不同分为以下两类:
① 单行子查询
结果集只有一行
一般搭配单行操作符使用:> < = <> >= <=
非法使用子查询的情况:
a、子查询的结果为一组值
b、子查询的结果为空
② 多行子查询
结果集有多行
一般搭配多行操作符使用:any、all、in、not in
in: 属于子查询结果中的任意一个就行
any和all往往可以用其他查询代替
where/having后面:
#查询谁的工资比高 Greenberg高
#首先查出Greenberg的工资
#再查比他高的员工信息
SELECT *
FROM employees
WHERE salary>(
SELECT salary
FROM employees
WHERE last_name = 'Greenberg'
);
#返回job_id和141号员工相同,salary比143号员工多的员工信息
SELECT *
FROM employees
WHERE job_id=(
SELECT job_id
FROM employees
WHERE employee_id = 141
)
AND salary>(
SELECT salary
FROM employees
WHERE employee_id = 143
);
#返回公司中工资最少的员工的last_name,job_id,salary
SELECT last_name,job_id,salary
FROM employees
WHERE salary=(
SELECT MIN(salary)
FROM employees
);
#查询最低工资大于50号部门最低工资的部门id及其最低工资
SELECT department_id,MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
SELECT MIN(salary)
FROM employees
WHERE department_id = 50
);
select后面:
#查询每个部门的员工个数
SELECT department_id,(
SELECT COUNT(*)
FROM employees e
WHERE e.department_id = d.department_id
) AS 个数
FROM departments d
SELECT department_id,COUNT(*)
FROM employees
GROUP BY department_id;
from后面:
#查询每个部门的平均工资的工资级别
SELECT ag_dep.*, jg.grade_level
FROM job_grades jg
INNER JOIN(
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
)ag_dep
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal
ORDER BY grade_level;
exit后面:(相关子查询)
#查询有员工名和部门名
SELECT department_name
FROM departments d
WHERE EXISTS(#(是否有值的意思)
SELECT *
FROM employees e
WHERE d.department_id = e.department_id
);
练习:
#查询各个部门中比部门平均工资高的员工的信息
SELECT *
FROM employees e
INNER JOIN (
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
) AS avg_dep
ON e.department_id = avg_dep.department_id
WHERE salary>ag;
SELECT AVG(salary), department_id
FROM employees
GROUP BY department_id
#查询和姓名中包含u的员工在同一个部门的员工的员工号和姓名
SELECT employee_id,last_name
FROM employees
WHERE department_id IN (
SELECT DISTINCT department_id
FROM employees
WHERE last_name LIKE '%u%'
);
#查询在部门的location_id为1700的部门工作的员工的员工号
SELECT employee_id
FROM employees
WHERE department_id IN (
SELECT DISTINCT department_id
FROM departments
WHERE location_id = 1700
);
#查询领导为King的员工的姓名和工资
SELECT last_name, salary
FROM employees
WHERE manager_id IN (
SELECT employee_id
FROM employees
WHERE last_name = 'K_ing'
);
#查询工资最高的员工的姓名,要求姓名整合成一列
SELECT CONCAT(last_name,' ',first_name) 姓名,salary
FROM employees
WHERE salary = (
SELECT MAX(salary)
FROM employees
);
#查询和K_ing 相同部门的员工姓和工资
SELECT last_name,salary
FROM employees
WHERE department_id IN (
SELECT department_id
FROM employees
WHERE last_name = 'K_ing'
);
#查询工资比公司平均工资高的员工的员工号和姓名
SELECT employee_id, last_name
FROM employees
WHERE salary>(
SELECT AVG(salary)
FROM employees
);
进阶八:分页查询
语法:
select 字段|表达式,...
from 表
【join type】 join 表
【on】 连接条件
【where 条件】
【group by 分组字段】
【having 条件】
【order by 排序的字段】
limit 【offset,】size
特点:
limit 语句放在查询语句的最后
offset 表示显示条目的起始索引(从0开始)(可缺省)
size 表示显示的条目的个数
公式:
假如要显示的页数为page,每一页条目数为size
select 查询列表
from 表
limit (page-1)*size,size;
#查询前五条员工的信息
SELECT *
FROM employees
LIMIT 0,5;
#缺省offset,默认从第一页即0索引开始
SELECT *
FROM employees
LIMIT 5;
#查询第11-25员工信息
SELECT *
FROM employees
LIMIT 10,15;
查询语句的执行顺序:
select 查询列表 ⑦
from 表1 别名 ①
连接类型 join 表2 ②
on 连接条件 ③
where 筛选 ④
group by 分组列表 ⑤
having 筛选 ⑥
order by排序列表 ⑧
limit 起始条目索引,条目数; ⑨
进阶八:分页查询
语法:
select 字段|表达式,...
from 表
【join type】 join 表
【on】 连接条件
【where 条件】
【group by 分组字段】
【having 条件】
【order by 排序的字段】
limit 【offset,】size
特点:
limit 语句放在查询语句的最后
offset 表示显示条目的起始索引(从0开始)(可缺省)
size 表示显示的条目的个数
公式:
假如要显示的页数为page,每一页条目数为size
select 查询列表
from 表
limit (page-1)*size,size;
#查询前五条员工的信息
SELECT *
FROM employees
LIMIT 0,5;
#缺省offset,默认从第一页即0索引开始
SELECT *
FROM employees
LIMIT 5;
#查询第11-25员工信息
SELECT *
FROM employees
LIMIT 10,15;
查询语句的执行顺序:
select 查询列表 ⑦
from 表1 别名 ①
连接类型 join 表2 ②
on 连接条件 ③
where 筛选 ④
group by 分组列表 ⑤
having 筛选 ⑥
order by排序列表 ⑧
limit 起始条目索引,条目数; ⑨
DML语言
插入
语法:
方式一:
insert into 表名(字段名,...)
values(值1,...);
方式二:
insert into 表名
set 字段名=值,...
特点:
1、字段类型和值类型一致或兼容,而且一一对应
2、可以为空的字段,可以不用插入值,或用null填充
3、不可以为空的字段,必须插入值
4、字段个数和值的个数必须一致
5、字段可以省略,但默认所有字段,并且顺序和表中的存储顺序一致
方式一:values()
支持多行插入;
支持子查询;
#插入新部门信息
INSERT INTO departments(department_id)#nullable类型字段可以不写
VALUES(12),(13),(14);#多行插入
#支持后接子查询,常用于查询后批量插入
INSERT INTO departments(department_id)#nullable类型字段可以不写
SELECT 18; #select后面可以是从其他表查询的数据,这种方式可以做到批量插入
INSERT INTO departments(department_id,department_name)
VALUES(12,'Tencent');#报错:因为department_name的类型varchar(3),而我插入和7个字符,超过3
方式二:set
不支持多行插入;
不支持子查询;
#插入新部门信息
INSERT INTO departments
SET department_id = 14,department_name= 'cns';
修改
语法:
修改单表语法:
update 表名
set 字段=新值,字段=新值
where 筛选条件
修改多表语法:
sql92:
update 表1 别名1,表2 别名2
set 字段=新值,字段=新值
where 连接条件
and 筛选条件
sql99:
update 表1 别名1,表2 别名2
inner|left|right join 表2
on 连接条件
set 字段=新值,字段=新值
where 筛选条件
#修改id为103的员工的工资为12000
UPDATE employees
SET salary = 12000,email = NULL
WHERE employee_id = 103;
#修改location_id为1800的部门的员工的工资为0
UPDATE employees e
INNER JOIN departments d
ON e.department_id = d.department_id
SET e.salary = 0
WHERE d.location_id =1800;
删除
方式一:delete
一、删除单表的记录
语法:
delete from 表名 【where 筛选条件】【limit 条目数】
二、级联删除
sql92语法:
delete 表1的别名,表2的别名 #(只删除表1只写表1别名,表12全删,两个都写)
from 表1 别名,表2 别名
where 连接条件
and 筛选条件
sql99语法:
delete 别名1,别名2
from 表1 别名
inner|left|right join 表2 别名
on 连接条件
where 筛选条件
方式二:truncate
语法:(清空操作,删库跑路)
truncate table 表名
delete:
#单表删除
#删除名字里含有hh的员工的信息
DELETE
FROM employees
WHERE last_name LIKE '%hh%';
#多表删除
#删除department_name = 'Pur' 的所有员工信息
DELETE e
FROM employees e
INNER JOIN departments d
ON d.department_id = e.department_id
WHERE d.department_name = 'Pur'
#删除department_name = 'Pur' 的部门信心及其的所有员工信息
DELETE e,d
FROM employees e
INNER JOIN departments d
ON d.department_id = e.department_id
WHERE d.department_name = 'Pur'
truncate:
#删库跑路
TRUNCATE TABLE employees;
两种方式的区别【面试题】:
1.truncate删除后,如果再插入,标识列从1开始
delete删除后,如果再插入,标识列从断点开始
2.delete可以添加筛选条件
truncate不可以添加筛选条件
3.truncate效率较高
4.truncate没有返回值
delete可以返回受影响的行数
5.truncate不可以回滚
delete可以回滚
DDL语言
库表的创建、修改和删除
库的管理:
创建库:
create database 【if not exits】 库名;
删除库:(原来这才是最纯正的删库跑路)
drop database 【if exits】 库名;
表的管理:
创建表:creat
CREATE TABLE IF NOT EXISTS stuinfo(
stuId INT,
stuName VARCHAR(20),
gender CHAR,
bornDate DATETIME
);
DESC studentinfo;
修改表: alter
语法:ALTER TABLE 表名 ADD|MODIFY|DROP|CHANGE COLUMN 字段名 【字段类型】;
①修改字段名
ALTER TABLE studentinfo CHANGE COLUMN gender sex CHAR;
②修改表名
ALTER TABLE stuinfo RENAME [TO] studentinfo;
③修改字段类型和列级约束
ALTER TABLE studentinfo MODIFY COLUMN borndate DATE ;
④添加字段
ALTER TABLE studentinfo ADD COLUMN email VARCHAR(20) first;
⑤删除字段
ALTER TABLE studentinfo DROP COLUMN email;
删除表: delete
DROP TABLE [IF EXISTS] studentinfo;
复制表:
①仅仅复制表的结构
CREATE TABLE stu LIKE stuinfo;
②复制表的部分结构
CREATE TABLE stu3
SELECT gender,stuId
FROM stuinfo
WHERE 1=2;
③复制表的结构+数据
CREATE TABLE stu1
SELECT * FROM stuinfo;
④复制表的结构+部分数据
CREATE TABLE stu2
SELECT stuId FROM stuinfo
WHERE gener = 'female';
数据类型
数值型:
1、整型
tinyint、smallint、mediumint、int/integer、bigint
1 2 3 4 8字节
特点:
①都可以设置无符号和有符号,默认有符号,通过unsigned设置无符号
②如果超出了范围,会报out or range异常,插入临界值
③长度可以不指定,默认会有一个长度
长度代表显示的最大宽度,如果不够则左边用0填充,但需要搭配zerofill,并且默认变为无符号整型
2、浮点型
定点数:
dec(M,D) = decimal(M,D)
默认(10,0)。
浮点数:
float(M,D) 4
double(M,D) 8
(M,D):M表示整数+小数总长度;D表示小数长度。
特点:
①M代表整数部位+小数部位的个数,D代表小数部位
②如果超出范围,则报out or range异常,并且插入临界值
③M和D都可以省略,但对于定点数,M默认为10,D默认为0
④如果精度要求较高,则优先考虑使用定点数
字符型:
常见类型:char、varchar、binary、varbinary、enum、set、text、blob
char:固定长度的字符,写法为char(M),最大字符数长度不能超过M,其中M可以省略,默认为1字符
varchar:可变长度的字符,写法为varchar(M),最大长度不能超过M,其中M不可以省略
日期型:
year年 1字节
date日期 4
time时间 3
datetime 日期+时间 8
timestamp 日期+时间 4 比较容易受时区、语法模式、版本的影响,更能反映当前时区的真实时间
约束条件
约束条件:
一、常见的约束
NOT NULL:非空,该字段的值必填
UNIQUE:唯一,该字段的值不可重复
DEFAULT:默认,该字段的值不用手动插入有默认值
CHECK:检查,mysql不支持
PRIMARY KEY:主键,该字段的值不可重复并且非空 unique + not null
FOREIGN KEY:外键,该字段的值必须来自另外的表的关联字段的值
二、主键和唯一
区别:
1、一个表至多有一个主键,但可以有多个唯一
2、主键不允许为空,唯一可以为空
相同点:
1、都具有唯一性
2、都支持组合键,但不推荐
三、外键:
1、用于限制两个表的关系,从表的字段值引用了主表的某字段值
2、外键列和主表的被引用列要求类型一致,意义一样,名称无要求
3、主表的被引用列要求是一个key(主键、外键、唯一键或者自定义key,一般就是主键)
4、插入数据,先插入主表
5、删除数据,先删除从表
可以通过以下两种方式来删除主表的记录
#方式一:级联删除
ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE CASCADE;
#方式二:级联置空
ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE SET NULL;
创建表时添加约束:
create table 表名(
字段名 字段类型 列级约束,
字段名 字段类型,
表记约束
)
约束的添加分类:
列级约束:都支持,但是外键约束没有效果
表级约束:除了非空、默认外,都支持
列级约束和表级约束的写法:
create table 表名(
字段名 字段类型 not null,#非空
字段名 字段类型 primary key,#主键
字段名 字段类型 unique,#唯一
字段名 字段类型 default 值,#默认
【constraint 约束名(随便起)】 foreign key(字段名) references 主表(被引用字段名)
# 表级约束写法: 【constraint 约束名(随便起)】 primary key(字段名)
)
注意:
列级约束可以在一个字段上追加多个,中间用空格隔开,没有顺序要求
修改表时添加或删除约束:
1、非空
添加非空
alter table 表名 modify column 字段名 字段类型 not null;
删除非空
alter table 表名 modify column 字段名 字段类型 ;
2、默认
添加默认
alter table 表名 modify column 字段名 字段类型 default 值;
删除默认
alter table 表名 modify column 字段名 字段类型 ;
3、主键
添加主键
alter table 表名 add【constraint 约束名】 primary key(字段名);
删除主键
alter table 表名 drop primary key;#可以不写名,因为只有唯一一个
4、唯一
添加唯一
alter table 表名 add【constraint 约束名】 unique(字段名);
删除唯一
alter table 表名 drop index 索引名; #不是unique而是index
5、外键
添加外键
alter table 表名 add【constraint 约束名】 foreign key(字段名) references 主表(被引用列);
删除外键
alter table 表名 drop foreign key 约束名;
标识列(自增长列)
特点:
1、不用手动插入值,可以自动提供序列值,默认从1开始,步长为1:auto_increment(放的位置跟列级约束条件的位置一样)
如果要更改起始值:手动插入值
如果要更改步长:更改系统变量
set auto_increment_increment=值;
2、一个表至多有一个自增长列
3、自增长列只能支持数值型
4、自增长列必须为一个key
一、创建表时设置自增长列
create table 表(
字段名 字段类型 约束 auto_increment
);
二、修改表时设置自增长列
alter table 表 modify column 字段名 字段类型 约束(key) auto_increment
三、修改表时删除自增长列
alter table 表 modify column 字段名 字段类型 约束(key)
TCL
存储引擎
一、概念:mysql中的数据用各种不同的技术存储在文件(内存)中。
二、通过 show engines:来查看mysql支持的存储引擎。
三、mysql中支持最多的存储引擎有:innodb(支持事务)、myisam、memory等。
事务
一、含义
事务:一条或多条sql语句组成一个执行单位,一组sql语句要么都执行要么都不执行
二、特点(ACID)
A 原子性:一个事务是不可再分割的整体,要么都执行要么都不执行
C 一致性:一个事务可以使数据从一个一致状态切换到另外一个一致的状态
I 隔离性:一个事务不受其他事务的干扰,多个事务互相隔离的(需要设置隔离级别,隔离级别不同隔离成都也不一样)
D 持久性:一个事务一旦提交了,则永久的持久化到本地
三、分类与写法
隐式事务:
事务没有明显的开启和结束的标记,比如:select、insert、update、delete语句;
显式事务:
事务有明显的开启和结束的标记
1、需要手动设置自动提交功能为禁用(不然系统会默认一句一句提交为事务)
2、写法:
set autocommit = 0;(设置自动提交禁用)
start transcation;(开启事务的语句,可缺省)
编写事务中的sql语句(为这四种select、insert、update、delete(DDL语言没有事务之说)),如:
update employees set salary = 0 where employee_id = 102;
update employees set commission_pct = 0 where employee_id = 102;
commit/rollback(提交事务/回滚事务);(结束事务的语句)
事务存在提交和回滚两种状态:
1、commit:提交事务,会将磁盘缓存中的数据写入磁盘的数据库中,一般数据库是自动提交,因此修改以后数据库就会发生变化。(抢购到了并提交付款)
2、rollback:数据回滚,将事务中已经执行成功的部分回到最初的状态。(抢购到了但没有提交付款并取消了订单)
四、事务的使用步骤
显式事务:
1、开启事务
set autocommit=0;
start transaction;#可以省略
2、编写一组逻辑sql语句
注意:sql语句支持的是insert、update、delete
设置回滚点:
savepoint 回滚点名;
3、结束事务
提交:commit;
回滚:rollback;
回滚到指定的地方:rollback to 回滚点名;
五、delete和truncate在事务中的使用区别
1、delete
set autocommit = 0;
start transaction;
delete from 表名;
rollback;
2、truncate
set autocommit = 0;
start transaction;
truncate from 表名;#(不支持回滚,还是删除了)
rollback;
六、并发事务
1、事务的并发问题是如何发生的?
多个事务 同时 操作 同一个数据库的相同数据时。
2、并发问题都有哪些?
脏读:一个事务读取了其他事务还没有提交的数据,读到的是其他事务“更新”的数据
不可重复读:一个事务多次读取,结果不一样
幻读:一个事务读取了其他事务还没有提交的数据,只是读到的是 其他事务“插入”的数据
3、如何解决并发问题
通过设置隔离级别来解决并发问题
4、隔离级别
脏读 不可重复读 幻读
read uncommitted:读未提交 × × ×
read committed:读已提交 √ × ×
repeatable read:可重复读 √ √ ×
serializable:串行化 √ √ √
5、写法
select @@tx_isolation;(查看当前隔离级别)
set transaction isolation level read commit;(设置当前mysql连接的隔离级别)
其他
视图
一、含义
mysql5.1版本出现的新特性,本身是一个虚拟表,它的数据来自于表,通过执行时动态生成(往通俗讲,就是包装一些sql语句用来虚拟存储筛选后的新虚拟表)。
好处:
1、简化sql语句,提高了sql的重用性
2、保护基表的数据,保密字段不被访问,提高了安全性
3、可以直接映射废弃旧表到新建的表,更加灵活
二、创建
create view 视图名
as
查询语句;
三、修改
方式一:
create or replace view 视图名
as
查询语句;
方式二:
alter view 视图名
as
查询语句
四、删除
drop view 视图1,视图2,...;
五、查看
desc 视图名;
show create view 视图名;
六、使用(视图里的操作能修改原表的数据,也说明了视图只保存逻辑没有数据,所有数据哥还是来自原始表)
1.插入
insert
2.修改
update
3.删除
delete
4.查看
select
注意:视图一般用于查询的,而不是更新的,所以具备以下特点的视图都不允许更新
(通俗来讲,如果视图表的每一行和原始表一一对应的则可以修改,如果是由原始表多行操作或多表操作得到的,那么就不能修改)
(1)包含分组函数、group by、distinct、having、union、
(2)join
(3)常量视图
(4)where后的子查询用到了from中的表
(5)用到了不可更新的视图
七、视图和表的对比
关键字 是否占用物理空间 使用
视图 view 占用较小,只保存sql逻辑 一般用于查询
表 table 保存实际的数据 增删改查
变量
系统变量
一、查看
说明:变量由系统提供的,不用自定义
语法:
1、查看系统变量
show 【global|session】variables like ''; 如果没有显式声明,默认是session
2、查看指定的系统变量的值
select @@【global|session】.变量名; 如果没有显式声明,默认是session
3、为系统变量赋值
方式一:
set 【global|session】 变量名=值; 如果没有显式声明,默认是session
方式二:
set @@global.变量名=值;
set @@变量名=值;
二、分类
1、全局变量
服务器层面上的,必须拥有super权限才能为系统变量赋值,作用域为整个服务器,也就是针对于所有连接(会话)有效
2、会话变量
服务器为每一个连接的客户端都提供了系统变量,作用域为当前的连接(会话)
自定义变量
一、用户变量
1、作用域:针对于当前连接(会话)生效,和会话变量作用域一致
2、位置:begin end里面,也可以放在外面
3、使用:
(1)声明并赋值:
set @变量名=值;
set @变量名:=值;
select @变量名:=值;
(2)更新值:
方式一:
set @变量名=值;
set @变量名:=值;
select @变量名:=值;
方式二:
select xx into @变量名 from 表;
(3)使用:
select @变量名;
二、局部变量
1、作用域:仅仅在定义它的begin end中有效
2、位置:只能放在begin end中,而且只能放在第一句
3、使用:
(1)声明:
declare 变量名 类型 【default 值】;
(2)赋值或更新
方式一:
set 变量名=值;
set 变量名:=值;
select @变量名:=值;
方式二:
select xx into 变量名 from 表;
(3)使用
select 变量名;
三、用户变量和局部变量对比
作用域 定义和使用的位置 语法
用户变量 当前会话 会话中的任何地方 需要加@符号,不限定类型
局部变量 BEGIN END 只在BEGIN END中的第一句 需要特定声明,一般不需要@符号,需要限定类型
存储过程和函数
存储过程
一、含义:一组经过预先编译的sql语句的集合
好处:
1、提高了sql语句的重用性,减少了开发程序员的压力
2、提高了效率
3、减少了传输次数
二、分类
1、无返回无参
2、仅仅带in类型,无返回有参
3、仅仅带out类型,有返回无参
4、既带in又带out,有返回有参
5、带inout,有返回有参
注意:in、out、inout都可以在一个存储过程中带多个
三、创建存储过程
语法:
delimiter $ #设置结束标志
CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...)
BEGIN
sql语句1;
sql语句2;
END $ #结束标记
注意:
1、需要设置新的结束标记
delimiter 新的结束标记
示例:
delimiter $ #(不要写成 delimiter $;)
2、存储过程体中可以有多条sql语句,如果仅仅一条sql语句,则可以省略begin end
3、参数模式:
in: 表示该参数可以作为输入,即该参数需要调用方传入值;
inout: 表示该参数可以作为输出,即该参数可以作为返回值;
inout: 表示该参数既可以作为输出也可以输入,即该参数既需要传入值,又可以作为返回值;
四、调用存储过程
call 存储过程名(实参列表) $ #结尾不用;而用新设置的结束标志
五、使用举例
#创建存储过程,实现用户是否登入成功
DELIMITER $
CREATE PROCEDURE myp1 (IN username VARCHAR(20), IN password1 VARCHAR(20))
BEGIN
DECLARE result INT DEFAULT 0;
SELECT COUNT(*) INTO result
FROM admin
WHERE admin.username = username
AND admin.password = password1;
SELECT IF(result > 0,'yes','no' );
END $
CALL myp1('zhang','123985') $
#创建存储过程,根据员工编号返回其所在部门名
DELIMITER $
CREATE PROCEDURE myp2 (IN employee_id INT(6), OUT department_name VARCHAR(3))
BEGIN
SELECT d.department_name INTO department_name
FROM departments d
INNER JOIN employees e
ON e.employee_id = d.department_id
WHERE e.employee_id = employee_id;
END $
#set @dname $
CALL myp2(102,@dname) $
六、存储过程的删除
drop procedure myp2;
drop procedure myp2,mpy1; #报错,存储过程的删除只能一个一个删除
七、存储过程信息的查看
desc myp2; #报错,只能查看表和视图
show create procedure myp2;
注意:
存储过程可以修改部分属性,但是逻辑语句没法修改,只能删除再建。
函数
一、创建函数
学过的函数:LENGTH、SUBSTR、CONCAT等
语法:
DELIMITER $
CREATE FUNCTION 函数名(参数名 参数类型,...) RETURNS 返回类型
BEGIN
函数体:需要放return 语句
END $
二、调用函数
SELECT 函数名(实参列表)
三、查看函数
show create function myf2;
四、删除函数
drop function myf2;
注意:函数可以修改部分属性,但是逻辑语句没法修改,只能删除再建。
五、函数和存储过程的区别
关键字 调用语法 返回值 应用场景
函数 FUNCTION SELECT 函数() 只能是一个 一般用于查询结果为一个值并返回时
存储过程 PROCEDURE CALL 存储过程() 可以有0个或多个 一般用于更新
Q:表,视图,存储过程,函数的区别?
流程控制结构
一、分类
顺序结构、分支结构、循环结构
分支结构
一、if函数
功能:
实现简单双分支
语法:
if(条件,值1,值2)
位置:
可以作为表达式放在任何位置
二、case结构
功能:
实现多分支
语法1:
case 表达式或字段
when 值1 then 语句1;
when 值2 then 语句2;
...
else 语句n;
end case;
语法2:
case
when 条件1 then 语句1;
when 条件2 then 语句2;
...
else 语句n;
end case;
位置:
可以放在任何位置,
作为表达式嵌套在其他语句中,可以放在begin end 外面或者begin end 中;
作为独立的语句使用时,只能放在begin end 里面;(上述加分号的写法结构就是作为独立的语句)
注意:
这里的case结构与之前学的有点不一样,需要加分号,因为是一个语句而不仅仅是个值。
三、if结构
功能:
实现多分支
语法:
if 条件1 then 语句1;
elseif 条件2 then 语句2;
...
else 语句n;
end if;
位置:
只能放在begin end中
循环结构
位置:
只能放在begin end中
特点:
都能实现循环结构
对比:
这三种循环都可以省略名称,但如果循环中添加了循环控制语句(leave或iterate)则必须添加名称
loop 一般用于实现简单的死循环
while 先判断后执行
repeat 先执行后判断,无条件至少执行一次
1、while
语法:
【名称:】while 循环条件 do
循环体
end while 【名称】;
2、loop
语法:
【名称:】loop
循环体
end loop 【名称】;
3、repeat
语法:
【名称:】repeat
循环体
until 结束条件
end repeat 【名称】;
循环控制语句:
leave:类似于break,用于跳出所在的循环
iterate:类似于continue,用于结束本次循环,继续下一次
Quiet and Quick! Salute !