一、MYSQL启动方式
- 计算机管理,手动配置
- 命令行使用命令,管理员模式下,net start mysql服务名 / net stop mysql服务名
二、MYSQL登录与退出
- 菜单-mysql自带客户端 mysql command client;仅限root用户
- 命令行下,mysql -h localhost -P 3306 -u root -p //前提是配置了环境变量
h->host 指主机
P->port 端口号
u->user 用户
p->password 密码
命令行退出使用 exit
三、MYSQL的常见命令
- show databases; //展示数据库
- use database_name //切换到具体的数据库
- desc table_name; //查看表结构
- show tables; //展示当前数据库下所有表
- show tables from database_name; //展示指定数据库下所有表
- select version(); --已经进入到mysql数据库使用 mysql --version/ mysql -V -命令行下使用
//查看数据库版本
四、MYSQL的语定规范
- 不区分大小写,但建议关键字大写,列名小写,表名,列名小写
- 每条命令最好用分号结尾
- 每条命令根据需要,可以进行缩进 Or 换行
- 注释
-
- 单行注释:#注释文字
- 单行注释:-- 注释文字
- 多行注释:/* 注释文字*/
五、DQL语言(数据查询)
- 基础查询
-
- 语法:select 查询列表 from 表名;
- 查询列表可以是:表中的字段、常量值、表达式、函数
- 查询的结果是一个虚拟的表格
※查询表中的单个字段
select last_name from employees;
※查询表中的多个字段
select last_name,salary,email from employees;
※查询表中的所有字段
select * from employees;
※查询常量值
select 100;
※查询表达式
select 100*98;
※查询函数
select VERSION();
※起另名
①便于理解
②如果要查询的字段有重名的情况,使用别名可以区分开
方式一:使用as
select 100%98 as 结果
方式二:使用空格
select last_name 名,first_name 名 from employees;
※去重
select DISTINCT(department_id) from employees;
※ +号使用-运算符
select 100+90; 两个操作数都为数值型,则做加法运算
select '123'+90; 只要其中一方为字符型,试图将字符型数值转换成数值型
如果转换成功,则继续做加法运算
如果转换失败,则将字符型数值转换成0
select null+10; 只要其中一方为null,则结果肯定为null
※ concat连接两个字段
select concat(last_name,first_name) from employees;;
- 条件查询
-
- 语法:select 查询列表 from 表名 where 筛选条件
- 按条件表达式筛选
-
-
- 条件运算符:> < = != <>
-
-
- 按逻辑表达式筛选
-
-
- 逻辑运算符 and or not
-
-
- 模糊查询
-
-
- like
- between and
- in
- is null
-
-
- 实例
-
-
- 查询工资>12000的员工信息
-
-
-
-
- select * from employees where salary >12000
-
-
-
-
- 查询部门部门编号不等于90的员工名和部门编号
-
-
-
-
- select last_name,department_id from employment where department_id<>90
-
-
-
-
- 查询工资z在10000到20000之间的员工名、工资以及奖金
-
-
-
-
- select last_name,salary,commission_pac from employees where salary >=10000 and salary <=20000
-
-
-
-
- 查询部门编号不是在90到110之间,或者工资高于15000的员工信息
-
-
-
-
- select * from employees where department_id<90 or department_id>110 or salary>15000;
- select * from employees not(department_id>=90 and department_id<=110) or salary>15000
-
-
-
- like
-
-
- 查询员工名中包含字符a的员工信息
-
-
-
-
- select * from employees where list_name like '%a%'
-
-
-
-
- 查询员工名中第三个字符为e,第五个字符为a的员工名和工资
-
-
-
-
- select * from employees where list_name like '__e_a%';
-
-
-
-
- 查询员工名中第二个字符为_的员工名
-
-
-
-
- select * from employees where list_name like '_\_%'
- select * from employees where list_name like '_$_%' ESCAPE '$'
-
-
-
- between and
-
-
- 使用between and 可以提高语句的简洁度
- 包含临界值
- 两个临界值不要调换顺序
- 查询员工编号在100到120之间的员工信息
-
-
-
-
- select * from employees where employee_id between 100 and 120;
- select * from employees where employee_id >=100 and employee_id <=120;
-
-
-
- in
-
-
- 查询员工的工种编号是IR_PROG、AD_VP、AD_PRES中的一个员工名和工种编号
-
-
-
-
- select * from employees where job_id in('IR_PROG','AD_VP','AD_PRES')
- select * from employees where job_id=IR_PROG or job_id=AD_VP or job_id=AD_VP
-
-
-
- is null / is not null
-
-
- 查询没有奖金的员工名和奖金率
-
-
-
-
- select last_name,commission_pct from employees where commission_pct IS NULL
-
-
-
-
- 查询有奖金的员工名和奖金率
-
-
-
-
- select last_name,commission_pct from employees where commission_pct IS NOT NULL
-
-
-
- 安全等于 <=>
-
-
- IS NULL :仅仅可以判断NULL值,可读性较高,建议使用
- <=> : 既可以判断NULL值,又可以判断普通的数据,可读性较低
-
-
- 【补充】concat函数
-
-
- 功能:拼接字符
-
-
-
-
- select concat(字符1,字符2,字符3,...
-
-
-
- 【补充】ifnull函数
-
-
- 功能:判断某字段或表达式是否为null,如果是,则返回1,否则返回0
-
- 排序查询
-
- 语法:select 查询列表 from 表名 [where 筛选条件] order by 排序列表 [asc | desc]
- asc 升序 、desc 降序;如果不写,默认为升序
- order by子句中可以支持单个字段、多个字段、表达式、函数、别名
- order by子句一般是放在查询语句的最后面,limit子句除外
- 执行顺序①from表 ②筛选条件 ③查询列表 ④order by排序
- 实例1 : 查询员工信息,要求工资从高到低排序
-
-
- select * from employees order by salary desc
-
-
- 实例2:查询部门编号>=90的员工信息,按入职时间的先后进行排序【添加筛选条件】
-
-
- select * from employees where department_id>=90 order by hiredate asc;
-
-
- 实例3 :按年薪的高低显示员工的信息和年薪【按表达式排序】
-
-
- select *,salary*12*(1+IFNULL(commission_pct,0)) as '年薪' from employees order by 年薪 desc
-
-
- 实例4:按姓名的长度显示员工的姓名和工资【按函数排序】
-
-
- select LENGTH(last_name) 字节长度 ,last_name,salary from employees order by LENGTH(last_name) desc;
-
-
- 实例5:查询员工信息,要求先按工资升序,再按员工编号降序【按多个字段排序】
-
-
- select * from employees order by salary asc ,employee_id desc;
-
-
- 实例6:查询员工的姓名和部门号和年薪,按年薪降序,按姓名升序
-
-
- select last_name,department_id,salary*12*(1+IFNULL(commission_pat,0)) '年薪' from employees order by 年薪 desc , last_name asc
-
-
- 实例7:选择工资不在8000到17000的员工的姓名和工资,按工资降序
-
-
- select last_name,salary from employees where salary not between 8000 and 17000 order by salary desc
-
-
- 实例8:查询邮箱中包含e的员工信息,并先按邮箱的字节数降序,再按部门号升序
-
-
- select * from employees where email like '%e%' order by length(email) desc ,department_id asc
-
- 常见函数
-
- 功能:类似于Java的方法,将一组逻辑语句封装在方法体中,对外暴露方法名
- 调用:select 函数名(实参列表) 【from 表】
- 分类:
-
-
- 单行函数 如:concat、length、ifnull等
- 分组函数
- 功能:做统计使用,又称为统计函数、聚合函数、组函数
-
-
- 字符函数
-
-
- length:获取参数值的字节个数
-
-
-
-
- select length('john')
- select length('张三haha')
-
-
-
-
- concat:拉接字符串
-
-
-
-
- select concat(last_name,'_',first_name) from employees;
-
-
-
-
- upper(转为大写)、lower(转为小写)
-
-
-
-
- select upper('john') => JOHN
- select lower('joHn') =>john
- 例:将姓变大写,名变小写,然后拼接 select concat(upper(last_name),lower(first_name))
-
-
-
-
- substr、substring 截取字符
-
-
-
-
- 注意索引从1开始
- 例1:select substr('我是小菜鸡',3) ->小菜鸡 #截取从指定索引处后面所有字段
- 例2:select substr('我是小菜鸡',1,4) ->我是小菜 #截取从指定索引处指定字符长度的字符
-
-
-
-
- 综合实例:姓名中首字符大写,其他字符小写然后用_拼接,显示出来
-
-
-
-
- select concat(upper(substr(last_name,1,1)),'_',lower(substr(last_name,2))) as '姓名' from employees;
-
-
-
-
- instr 返回子串第一次出现的索引,如果找不到返回0
-
-
-
-
- select instr('我是小菜鸡','小菜鸡') as out_put -> 3
-
-
-
-
- trim去除字符两侧空格 or 去除指定字符
-
-
-
-
- select trim(' 小菜鸡 ') as out_put -> 小菜鸡
- select trim('a' from 'aaaaaaa小菜鸡aaaaaaaa') as out_put ->小菜鸡
-
-
-
-
- lpad 用指定的字符实现左填充指定长度
-
-
-
-
- select lpad('小菜鸡',5,'*') as out_put -> **小菜鸡
-
-
-
-
- rpad 用指定的字符实现右填充指定长度
-
-
-
-
- select lpad('小菜鸡',5,'*') as out_put -> 小菜鸡**
-
-
-
-
- replace 替换 格式:replace(操作对象,将要被替换对象,预期要替换的对象)
-
-
-
-
- select replace('小白是小菜鸡','小白','小黄') as out_put -> 小黄是小菜鸡
-
-
-
- 数学函数
-
-
- round 四舍五入
-
-
-
-
- select round(-1.55) -> -2 #取整
- select round(1.567,2) ->-1.57 #保留2位小数
-
-
-
-
- ceil 向上取整,返回>=该参数的最小整数
-
-
-
-
- select ceil(1.002) -> 2
- select ceil(-9.99) -> -9
-
-
-
-
- floor 向下取整,返回<=该参数的最大的整数
-
-
-
-
- select floor(-9.99) ->-10
- select floor(9.99) -> 9
-
-
-
-
- truncate 截断
-
-
-
-
- select truncate(1.699999,1) -> 1.6
-
-
-
-
- mod取余,mod(a,b)=> a-a/b*b
-
-
-
-
- select mod(10,-3) -> -1
-
-
-
- 日期函数
-
-
- now返回当前系统日期+时间
-
-
-
-
- select now()
-
-
-
-
- curdate 返回当前系统日期,不包含时间
-
-
-
-
- select curdate()
-
-
-
-
- curtime 返回当前时间,不包含日期
-
-
-
-
- select curtime()
-
-
-
-
- 获取指定的部分,年、月、日、小时、分钟、秒
-
-
-
-
- select year(now()) 年
- select year('2021-01-01') 月
- select monthname(now()) 月
-
-
-
-
- str_to_date 将日期格式的字符转换成指定格式的日期
-
-
-
-
- select str_to_date('9-13-1999','%m-%d-%Y') -> 1999-09-13
-
-
-
-
- date_format 将日期转换成字符
-
-
-
-
- select date_format('2028/6/6','%Y年%m月%d日') -> 2028年06月06日
-
-
-
- 流程控制函数
-
-
- if函数:if else的效果
-
-
-
-
- select IF(表达式,True的返回值,False的返回值)
-
-
-
-
-
-
- 例 select (10<5,'大','小')
-
-
-
-
-
- case函数的使用一:switch case的效果
-
-
-
-
- 语法:case 要判断的字段或表达式
-
-
-
-
-
-
- when 常量值1 then 要显示的值1或语句1
- when 常量值2 then 要显示的值2或语句2
- ....
- else 要显示的值n或语句n
- end
- 实例:
- 查询员工的工资,要求:部门号=30,显示的工资为1.1倍;部门号=40,显示的工资为1.2倍;部门号=50,显示的工资为1.3倍;其他部门,显示的工资为原工资
- select salary as origin_salary,department_id
- case department_id
- when 30 then salary*1.1
- when 40 then salary*1.2
- when 50 then salary*1.3
- else salary
- end as new_salary
- from employees
-
-
-
-
-
- case函数的使用二:if else if 的效果
-
-
-
-
- 语法:case
-
-
-
-
-
-
- when 条件1 then 要显示的值1或语句1
- when 条件2 then 要显示的值2或语句2
- ...
- else 要显示的值n或语句n
- end
- 实例:查询员工的工资的情况,如果工资>20000,显示A级别
- 如果工资>15000,显示B级别
- 如果工资>10000,显示C级别
- 否则,显示D级别
- 实例:
- select salary,
- case
- when salary >20000 then 'A'
- when salary >15000 then 'B'
- when salary >10000 then 'C'
- else 'D'
- end
-
-
-
-
- 分组函数,用于统计使用,又称为聚合函数或统计函数或组函数
-
-
- sum求和
- avg平均值
- max最大值
- min最小值
- count计算个数
-
-
- 分组查询
-
-
- 语法:select 分组函数,列(要求出现在group by的后面)
- from表
- [where 筛选条件]
- group by 分组的列表
- [order by 子句]
-
- 连接查询
-
- 含义:又称多表查询,当查询的字段来自于多个表时,就会用到连接查询
- 笛卡尔乘积现象:表1 有M行,表2有N行,结果=M*N行
-
-
- 发生原因:没有有效的条件
- 如何避免:添加有效的连接条件
-
-
- 按功能分类:
-
-
- 内连接sql92语法:
-
-
-
-
- 等值连接
-
-
-
-
-
-
- 案例1:查询女神名和对应的男神名
-
-
-
-
-
-
-
-
- select
name
,boyName from boys,beauty where beauty.boyfriend_id=boys.id
- select
-
-
-
-
-
-
-
-
- 案例2:查询员工名和对应的部门名
-
-
-
-
-
-
-
-
- select last_name,department_name from employees,departments where employees.department_id=departments.department_id
-
-
-
-
-
-
-
-
- 总结:
-
-
-
-
-
-
-
-
- 多表等值连接的结果为多表的交集部分
- N表连接,至少需要n-1个连接条件
- 多表的顺序没有要求
- 为表名取别名
- 可以搭配子句使用,比如:分组,最大值,最小值
-
-
-
-
-
-
-
- 非等值连接
-
-
-
-
-
-
- 实例1:查询员工的工资和工资级别
-
-
-
-
-
-
-
-
- select salary,grade_level from employees,job_grades g where salary between g.lowest_sal and g.highest_sal
-
-
-
-
-
-
-
- 自连接
-
-
-
-
-
-
- 把一个表当成两个表使用
-
-
-
-
-
- 外连接
-
-
-
-
- 应用场景:用于查询一个表中有,另一个表没有的记录
- 特点:
-
-
-
-
-
-
- 外连接的查询结果为主表中的所有记录;
- 如果从表中有和它匹配的,则显示匹配的值
- 如果从表中没有和它匹配的,则显示null
- 外连接查询结果=内连接结果+主表中有而从表没有的记录
- 左外连接,left join 左边的是主表
- 右外连接,right join右边的是主表
- 左外和右外交换两个表的顺序,可以实现同样的效果
- 全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的
-
-
-
-
-
- 语法:select 查询列表 from 表1 别名 【连接类型】 join 表2 别名 on 连接条件 【where 筛选条件】【group by 分组】【having 筛选条件】【order by 排序列表】
-
-
-
-
- 左外连接left join
- 右外连接right join
- 全外连接Full Join
-
-
-
-
- 交叉连接Cross Join
- 内连接sql99语法
-
-
-
-
- select 查询列表 from 表1 别名 inner join 表2 别名 on 连接条件
-
-
-
- sql92 VS sql99
-
-
- 功能:sql99支持的较多
- 可读性:sql99实现连接条件和筛选条件的分离,可读性较高
-
- 子查询
-
- 含义:出现在其他语句中的select语句,称为子查询或内查询;外部的查询语句,称为主查询;
- 分类1:按子查询出现的位置
-
-
- select 后面
-
-
-
-
- 仅仅支持标量子查询
-
-
-
-
-
-
- 查询每个部门的员工个数
-
-
-
-
-
-
-
-
- select * d.*,(select count(*) from employees e where e.department_id=d.department_id) '个数' from departments d;
-
-
-
-
-
-
- from后面
-
-
-
-
- 支持表子查询
-
-
-
-
-
-
- 查询每个部门的平均工资的工资等级
-
-
-
-
-
-
-
-
- select ag_dep.*,g.grade_level from(select AVG(salary) ag ,department_id from employees group by department_id) ag_dep inner join job_grades g on ag_dep.ag between lowest_sal and highest_sal;
-
-
-
-
-
-
- where 或 having后面(重点)
-
-
-
-
- 标量子查询
-
-
-
-
-
-
- 谁的工资比Abel高?
-
-
-
-
-
-
-
-
- 查询Abel的工资
-
-
-
-
-
-
-
-
-
-
- selelct salary from employees where last_name = 'Abel'
-
-
-
-
-
-
-
-
-
-
- 查询员工的信息,满足salary>①结果
-
-
-
-
-
-
-
-
-
-
- select * from employees where salary >(selelct salary from employees where last_name = 'Abel')
-
-
-
-
-
-
-
-
-
- 返回job_id与141员工相同,salary比143号员工多的员工姓名,job_id和工资
-
-
-
-
-
-
-
-
- 查询员工号=141的job_id
-
-
-
-
-
-
-
-
-
-
- select job_id from employees where employee_id=141
-
-
-
-
-
-
-
-
-
-
- 查询salary>143号员工的工资
-
-
-
-
-
-
-
-
-
-
- select salary from employees where job_id=143
-
-
-
-
-
-
-
-
-
-
- 组合①和②的条件
-
-
-
-
-
-
-
-
-
-
- select * from employees where jod_id =(select job_id from employees where employee_id=141) and salary > (select salary from employees where job_id=143)
-
-
-
-
-
-
-
-
-
- 返回公司工资最少的员工的last_name,job_id和salary
-
-
-
-
-
-
-
-
- 查询公司最低工资
-
-
-
-
-
-
-
-
-
-
- select min(salary) from employees
-
-
-
-
-
-
-
-
-
-
- 查询last_name,job_id,salary,要求salary=①
-
-
-
-
-
-
-
-
-
-
- select last_name,job_id,salary where salary = ( select min(salary) from employees )
-
-
-
-
-
-
-
-
- 列子查询
-
-
-
-
-
-
- 查询location_id是1400或1700的部门编号
-
-
-
-
-
-
-
-
- select department_id from departments where location_id in(1400,1700)
-
-
-
-
-
-
-
- 行子查询
-
-
-
-
-
-
- 查询员工编号最小并且工资最高的员工信息
-
-
-
-
-
-
-
-
- select * from employees where (employees_id,salary)=(select MIN(employee_id),MAX(salary) from employees)
-
-
-
-
-
-
- exists后面(相关子查询)--判断是否存在
-
-
-
-
- 表子查询
-
-
-
-
-
-
- select exists(select employee_id from employees);
-
-
-
-
- 分类2:按结果集的行列数不同
-
-
- 标量子查询(结果集只有一行一列)
- 列子查询(结果集只有一列多行)
- 行子查询(结果集有一行多列)
- 表子查询(结果集一般为多行多列)
-
- 分布查询(重点)
-
- 应用场景:当要显示的数据,一页显示不全,需要分页提交sql请求
- 语法:select 查询列表 from 表 [join_type join 表2 on 连接条件 where 筛选条件 group by 分组字段 having 分组后的筛选 order by 排序的字段]
- 特点:
-
-
- offset要显示条目的起始索引(起始索引从0开始)
- size 要显示条目个数
- limit 语句放在查询语句的最后
- 公式:要展示的页数 page,每页的条目数size -> select 查询列表 from 表 limit (page-1)*size ,size
-
-
-
-
- size=10
- 页数,起始索引
-
-
-
-
-
-
- 1 0
- 2 10
- 3 20
-
-
-
- union联合查询
-
- 将多条查询语句的结果合并成一个结果
- 语法:查询语句1 union 查询语句2 ..........
- 应用场景:要查询的结果来源于多个表,且多个表没有直接的连接关系,但查询的信息一致
- 案例:查询中国用户中男性的信息以及外国用户中男性的用户信息
-
-
- select id ,cname,csex from t_ca where csex='男' union select t_id,tName,tGender from t_ua where tGender ='male'
-
-
- 特点:
-
-
- 要求多条查询语句的查询列数是一致的
- 要求多条查询语句的查询的每一列的类型和顺序最好一致
- union关键字默认去重,如果使用union all可以包含重复项
-
六、DML语言(数据操作)
- 插入语句
-
- 语法1:insert into 表名(列名,.....) values(与列名对应值,.......)
- 语法2:insert into 表名 set 列名=值,列名=值,......
- 插入值类型要与列类型一致或兼容
- 不可以为null的值必须插入值,可以为null的值直接插入null
- 列的顺序可以调换
- 列数和值的个数必须一致
- 可以省略列名,默认所有列,而且列的顺序和表中列的顺序一致
- 修改语句
-
- 修改单表记录
-
-
- 语法:update 表名 set 列名=新值 ,........where 筛选条件
-
-
- 修改多表记录
-
-
- sql92语法 update 表1 别名,表2 别名 set 列=值,... where 连接条件 and 筛选条件;
- sql99语法 update 表1 别名 inner |left |right join 表2 别名 on 连接条件 set 列=值,... where 筛选条件;
-
- 删除语句
-
- 语法1
-
-
- 单表删除
-
-
-
-
- delete from 表名 where 筛选条件
-
-
-
-
-
-
- 案例:删除手机号以9结尾的女神信息
-
-
-
-
-
-
-
-
- delete from beauty where phone like '%9' ;
-
-
-
-
-
-
- 多表删除
-
-
-
-
- sql92语法:delete 别名 from 表1 别名,表2 别名 where 连接条件 and 筛选条件
- sql99语法:delete 表1的别名,表2的别名 from 表1 别名 inner| left |right join 表2 别名 on 连接条件 where 筛选条件
- 案例1:删除张无忌的女朋友信息
-
-
-
-
-
-
- delete b from beauty b inner join boys bo on b.boyfriend_id = bo.id where bo.boyName='张无忌';
-
-
-
-
- 语法2:truncate table 表名
-
-
- 案例:将魅力值>100的男神信息删除
-
-
-
-
- truncate table boys;
-
-
-
- delete PK truncate[重点]
-
-
- delete 可以加where 条件,truncate不能加
- truncate 删除,效率稍微高一些
- 假如要删除的表中有自增长列,如果用delete删除后,再插入数据,自增长列的值从断点开始,而truncate删除后,再插入数据,自增长列的值从1开始
- truncate删除没有返回值,delete有返回值
- truncate删除不能回滚,delete可以回滚
-
七、DDL语言(数据定义)
- 库的管理
-
- 库的创建
-
-
- 语法:create database [if not exists]库名;
-
-
-
-
- 创建库books
-
-
-
-
-
-
- create database if not exists books;
-
-
-
-
-
-
- 库的修改(一般不修改)
- 更改库的字符集
-
-
-
-
-
-
- alter database books character set gbk;
-
-
-
-
-
-
- 库的删除
-
-
-
-
-
-
- drop database if exists books;
-
-
-
- 表的管理
-
- 表的创建
-
-
- 语法:create table 表名(列名 列的类型【(长度) 约束,...........】)
-
-
-
-
- 案例1:创建表Book
-
-
-
-
-
-
- create table book(
-
-
-
-
-
-
-
-
- id INT ,#编号
- bName VARCHAR(20),#书名
- price DOUBLE,#价格
- authorId INT,#作者日期
- publishDate DATETIME #出版日期);
-
-
-
-
-
-
-
- 案例2:创建表author
-
-
-
-
-
-
- create table author(
-
-
-
-
-
-
-
-
- id INT,
- author_name VARCHAR(20),
- nation VARCHAR(10);
-
-
-
-
- 表的修改
-
- 修改列名
-
-
- ALTER TABLE 表名 CHANGE COLUMN 旧列名 新列名 列属性
-
-
- 修改列的类型或约束
-
-
- ALTER TABLE 表名 MODIFY COLUMN 列名 新属性
-
-
- 添加新列
-
-
- ALTER TABLE 表名 ADD COLUMN 列名 新属性
-
-
- 删除列
-
-
- ALTER TABLE 表名 DROP COLUMN 列名
-
-
- 修改表名
-
-
- ALTER TABLE 旧表名 RENAME TO 新表名
-
- 表的删除
-
- DROP TABLE 【IF EXISTS】表名
- 表的复制
-
- 仅仅复制表的结构
-
-
- CREATE TABLE 表名 LIKE 目标表名
-
-
- 复制表的结果+数据
-
-
- CREATE TABLE 表名 select * from 目标表名
-
-
- 只复制部分数据
-
-
- CREATE TABLE 表名 select id,au_name from 目标表名 where 条件
-
-
- 仅仅复制某些字段
-
-
- CREATE TABLE 表名 select id,au_name from 目标表名 where 1=2
-
- 常见数据类型
-
- 数值型
-
-
- 整形
-
-
-
-
- Tinyint 字节:1
- Smallint 字节:2
- Mediumint 字节:3
- int/integer 字节:4
- bigInt 字节:8
- 特点:
-
-
-
-
-
-
- 如果不设置无符号还是有符号,默认是有符号,如果想设置无符号,需要添加unsiigned关键字
- 如果插入的数值超出了整形的范围,会报out of range异常,并且插入临界值
- 如果不设置长度,会有默认的长度,长度代表了显示的最大宽度,如果不够会用0在左边填充,但必须搭配zerofill使用
-
-
-
-
-
-
- 实例1,如何设置无符号和有符号
-
-
-
-
-
-
- int
- int unsigned
-
-
-
-
-
- 小数
-
-
-
-
- 定点数
-
-
-
-
-
-
- dec(M,D)
- decimal(M,D)
-
-
-
-
-
-
- 浮点数
-
-
-
-
-
-
- float(M,D) 字节4
- double(M,D) 字节8
-
-
-
-
-
-
- 特点:
-
-
-
-
-
-
- M和D
-
-
-
-
-
-
-
-
- M:整数部位+小数部位
- D:小数部位
- 如果超过范围,则插入临界值
-
-
-
-
-
-
-
-
- M和D可以省略
-
-
-
-
-
-
-
-
- 如果是decimal,则M默认为10,D默认为0
- 如果是float和double,则会根据插入的数值的精度来决定精度
-
-
-
-
-
-
-
-
- 定点型的精确度较高,如果要求插入数值的精度较高,如货币
-
-
-
-
- 字符型
-
-
- 较短的文本:char、varchar
-
-
-
-
- char,固定长度,效率高,最多字符数(0~255)
- varchar,可变长度,效率低,最多字符数(0~65535)
-
-
-
-
- 较长的文本:text、blob(保存较大的二进制)
-
-
- 日期
-
-
- date xxxx-xx-xx
- datetime xxxx-xx-xx xx:xx:xx
- timestamp
- time
- year
-
- 常见约束
-
- 含义:一种限制,用于限制表的数据,为了保证表中的数据准确性和可靠性
- 分类:六大约束
-
-
- NOT NULL :非空,用于保证该字段的值不能为空;e.g. 姓名、学号等
- DEFAULT :默认,用于保证该字段有默认值;e.g. 性别
- PRIMARY KEY :主键,用于保证该字段的值具有唯一性,并且非空;e.g. 学号、员工编号等
- UNIQUE:唯一,用于保证该字段的值具有唯一性,可以为空;,e.g. 座位号
- CHECK:检查约束【mysql中不支持】
- FOREIGN KEY:外键,用于限制两个表的关系,用于保证该字段的值必须来自于主表的关联列的值;在从表添加外键约束,用于引用主表中的某列的值
- 添加约束的时机:
-
-
-
-
- 创建表时
- 修改表时
-
-
-
-
- 约束的添加分类:
-
-
-
-
- 列级约束
-
-
-
-
-
-
- 六大约束语法上都支持,但外键约束没有效果
-
-
-
-
-
-
- 表级约束
-
-
-
-
-
-
- 除了非空、默认,其他都支持
-
-
-
-
- 创建表时添加约束
-
-
- 语法:直接在字段名和类型后面追加 [约束类型] 即可;只支持:默认、非空、主键、唯一
- create table stuInfo(
-
id INT PRIMARY KEY,#主键
stuName VARCHAR(20) NOT NULL,#非空
gender CHAR(1) CHECK(gender='男' OR gender ='女'),#检查
seat INT UNIQUE,#唯一
age INT DEFAULT 18,#默认约束
majorId INT REFERENCES major(id)# 外键
);
create table major(
id INT PRIMARY KEY,
majorName VARCHAR(20)
);
-
- 添加表级约束
-
-
- create table if not exists stuinfo (
-
id INT,
stuname VARCHAR(20),
seat INT,
age INT,
majorid INT,
CONSTRAINT pk PRAMARY KEY (id),#主键
CONSTRAINT uq UNIQUE(seat), #唯一键
CONSTRAINT ck CHECK(gender='男' OR gender ='女'),#检查
CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERANCES major #外键
);
-
- 通用的写法,除外键外,其他约束使用[列级约束]
-
-
- create table if not exists stuinfo (
-
id INT PRIMARY KEY,
stuname VARCHAR(20) NOT NULL,
sex CHAR(1),
age INT DEFAULT 18,
seat INT UNIQUE,
majorid INT,
CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERANCES major #外键
)
-
- 主键和唯一的大对比
保证唯一性 | 是否允许为空 | 一个表中可以有多少个 | 是否可以组合 | |
主键 | √ | × | 至多有1个 | √,但不推荐 |
唯一 | √ | √ | 可以有多个 | √,但不推荐 |
-
- 外键
-
-
- 要求在从表设置外键关系
- 从表的外键列的类型和主表的关联列的类型要求一致或兼容,名称无要求
- 主表的关联列必须是一个key(一般是主键或唯一)
- 插入数据时,先插入主表,再插入从表;删除数据时,先删除从表,再删除主表
-
-
- 修改表时添加约束
-
-
- 创建表信息
-
create table if not exists stuinfo(
id INT,
stuname VARCHAR(20),
gender CHAR(1),
seat INT,
age INT,
majorId INT
)
-
-
- 添加非空约束
-
-
-
-
- ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHER(20) NOT NULL
-
-
-
-
- 添加默认约束
-
-
-
-
- ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;
-
-
-
-
- 添加主键
-
-
-
-
- 列级约束
-
-
-
-
-
-
- ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY
-
-
-
-
-
-
- 表级约束
-
-
-
-
-
-
- ALTER TABLE stuinfo ADD PRIMARY KEY (id)
-
-
-
-
-
- 添加唯一
-
-
-
-
- 列级约束
-
-
-
-
-
-
- ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE
-
-
-
-
-
-
- 表级约束
-
-
-
-
-
-
- ALTER TABLE stuinfo ADD UNIQUE (seat)
-
-
-
-
-
- 添加外键
-
-
-
-
- ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY (majorId) REFERANCES major(id)
-
-
-
- 删除非空约束
-
-
- ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL
-
-
- 删除默认约束
-
-
- ALTER TABLE stuinfo MODIFY COLUMN age INT
-
-
- 删除主键
-
-
- ALTER TABLE stuinfo DROP PRIMARY KEY
-
-
- 删除唯一
-
-
- ALTER TABLE stuinfo DROP INDEX seat
-
-
- 删除外键
-
-
- ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major
-
- 标识列(又称为自增长列)
-
- 含义:可以不用手动的插入值,系统提供默认的序列值
- 使用auto_increment关键字
- 特点
-
-
- 标识列必须和主键搭配吗?不一定,但要求是一个key
- 一个表可以有几个标识列?至多一个!
- 标识列的类型只能是数值型
- 标识列可以通过set auto_increment_increment=3;设置步长,可以通过手动插入值,设置起始值
-
-
- 修改表时设置标识列
-
-
- AlTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;
-
-
- 修改表时删除标识列
-
-
- AlTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY;
-
- 级联
-
- 级联删除
-
-
- 添加外键约束时,加上 " ON DELETE CASECADE"
-
-
- 级联置空
-
-
- 添加外键约束时,加上 " ON DELETE SET NULL"
-
八、TCL语言(事务控制语言)
Transaction Control Language
- 解释:事务由单独单元的一个或多个SQL语句组成,在这个单元中,每个MySQL语句是相互依赖的。而整个单独单元作为一个不可分割的整体,如果单元中某条SQL语句一旦执行失败或产生错误,整个单元将会回滚。所有受到影响的数据将返回到事务开始以前的状态;如果单元中的所有SQL均成功,则事务被顺利执行。
- 事务的特性(ACID)
-
- 原子性:一个事务不可再分割,要么都执行要么都不执行
- 一致性:一个事务执行会使数据从一个一致状态切换到另外一个一致状态
- 隔离性:一个事务的执行不受其他事务的干扰
- 持久性:一个事务一旦提交,则会永久的改变数据库的数据
- 事务的创建
-
- 隐式事务:事务没有显示的开启和结束的标记;e.g. insert、update、delete语句
- 显示事务:事务具有显示的开启和结束标记
-
-
- 前提:必须先设置自动提交功能为禁用
- 步骤1:开启事务
-
-
-
-
- set autocommit=0;
- start tranction(可选)
-
-
-
-
- 步骤2:编写事务中的sql语句(select insert update delete)
- 步骤3:结束事务
-
-
-
-
- commit;提交事务
- rollback;回滚事务
-
-
-
-
- 演示事务的使用步骤
-
-
-
-
- 开始事务
-
-
-
-
-
-
- set autocommit=0;
- start transaction;
-
-
-
-
-
-
- 编写一组事务语句
-
-
-
-
-
-
- update account set banlance=500 where username='name1';
- update account set banlance=1000 where username='name2';
-
-
-
-
-
-
- 结束事务
-
-
-
-
-
-
- commit / rollback
-
-
-
-
- delete 和 truncate 在事务使用时的区别
-
-
- delete支持事务回滚,truncate不支持
-
-
- 数据库的隔离级别
-
-
- 对于同时运行的多个事务,当这些事务访问[数据库中相同的数据]时,如果没有采取必要的隔离机制,就会导致各种并发问题:
-
-
-
-
- 脏读:对于两个事务T1,T2,T1读取了已经被T2更新但还[没有被提交]的字段。之后,若T2回滚,T1读取的内容就是临时且无效的
- 不可重复读:对于两个事务T1,T2,T1读取了一个字段,然后T2更新了该字段.之后,T1再次读取同个字段,值就不同了
- 幻读:对于两个事务T1,T2,T1从一个表中读取了一个字段,然后T2在该表中[插入]了一些新的行。之后,如果T1再次读同一个表,就会多出几行
-
-
-
-
- 数据库事务的隔离性:数据库系统必须具有隔离并发运行各个事务的能力,使它们不付相互影响,避免各种并发问题;一个事务与其他事务隔离的程度称为隔离级别,数据库规定了多种事务隔离级别,不同隔离级别对应不同的干扰程度,隔离级别越高,数据一致性就越好,但并发性越弱
-
-
- 回滚点演示
-
-
- SET autocommit =0;
-
START TRANSACTION;
DELETE FROM account WHERE id=25;
SAVEPOINT a;#设置保存点
DELETE FROM account WHERE id=28;
ROLLBACK TO a; #回滚到保存点
-
- 数据库提供的4种事务隔离级别
隔离 级别 | 描述 |
READ UNCOMMITTED 读未提交数据 | 允许事务读取未被其他事务提交的变更,脏读,不可重复读和幻读都会出现 |
READ COMMITED 读已提交数据 | 只允许事务读取已经被其它事务提交的变更,可以避免脏读,但不可重复读、幻读问题仍可能出现 |
REPEATABLE READ 可重复读 | 确保事务可以多次从一个字段中读取相同的值,在这个事务持续期间,禁止其他事务对这个字段进行更新,可以避免脏读和不可重复读,但幻读的问题仍然存在 |
SERIALIZABLE 串行化 | 确保事务可从一个表中读取相同的行,在这个事务持续期间,禁止其他事务对该表执行插入,更新和删除操作,所有并发问题都可以避免,但性能十分低下 |
-
- 查看当前的隔离级别:select @@transaction_isolation(v8.0版本)
- 设置当前myql连接的隔离级别
-
-
- set transaction isolation level read committed;
-
-
- 设置数据库系统的全局的隔离级别
-
-
- set global transaction isolation level read committed
-
-
- mysql中默认,第三个隔离级别 ,repeatable read
- oracle中默认第二个隔离级别 read committed
事务的隔离级别 | 脏读 | 不可重复读 | 幻读 |
read uncommitted | √ | √ | √ |
read committed | × | √ | √ |
repeatable read | × | × | √ |
serializable | × | × | × |
九、视图
- 含义
-
- MySQL从5.0.1版本开始提供视图功能。一种虚拟存在的表,行和列的数据来自定义视图的查询中使用的表,并且是在使用视图时动态生成的,只保存了sql逻辑,不保存查询结果。
- 应用场景
-
- 多个地方用到同样的查询结果
- 该查询结果使用的sql语句比较复杂
- 创建视图
-
- 语法:
-
-
- create view 视图名
-
as
查询语句;
-
- 案例
-
-
- 查询姓名中包含a字符的员工名、部门名和工种信息
-
-
-
-
- 创建
-
-
-
-
-
-
- CREATE VIEW myv1
-
-
-
AS
SELECT last_name ,department_name,job_title
FROM employees e
JOIN departments d ON e.department_id = d.d.department_id
JOIN jobs j ON j.job_id =e.job_id;
-
-
-
- 使用
-
-
-
-
-
-
- SELECT * FROM myv1 WHERE last_name LIKE '%a%'
-
-
-
-
-
- 视图的好处
-
-
-
-
- 重用sql语句
- 简化复杂的sql操作,不必知道它的查询细节
- 保护数据,提高安全性
-
-
-
- 修改视图
-
-
- 方法一:create or replace view 视图名
-
as
查询语句;
-
-
- 方法二:alter view 视图名
-
as
查询语句;
-
- 删除视图
-
-
- 语法:DROP VIEW 视图名,视图名.........;
-
-
- 查看视图
-
-
- 方法一:desc 视图名
- 方法二:show create view 视图名
-
十、变量
- 系统变量:变量由系统提供,不是用户定义,属于服务器层面
-
- 语法(如果不写global,默认是会话变量)
-
-
- 查看所有的系统变量(全局变量、会话变量)
-
-
-
-
- show global / 【session】 variables;
-
-
-
-
- 查看满足条件的部分系统变量
-
-
-
-
- show global / 【session】variables like '%char%'
-
-
-
-
- 查看指定的某个系统变量的值
-
-
-
-
- select @@global/session.系统变量名
-
-
-
-
- 为某个系统变量赋值
-
-
-
-
- set global / 【session】系统变量名 = 值
- set @@global / 【session】. 系统变量名 = 值
-
-
-
-
- 作用域
-
-
-
-
- 全局:服务器每次启动将为所有的全局变量赋初始值,针对所有的会话(连接)有效,但不能跨重启
- 会话:仅仅针对当前会话(连接)有效
-
-
- 自定义变量:变量是用户自定义的,不是由系统的
-
- 使用步骤:声明-->赋值-->使用(查看、比较、运算)
- 作用域:针对当前会话(连接)有效,同于会话变量的作用域;应用在任何地方,也就最begin、end里面或begin、end外面
- 语法:
-
-
- 声明并初始化
-
-
-
-
- SET @用户变量名=值;
- SET @用户变量名:=值;
- select @用户变量名:=值;
-
-
-
-
- 赋值(更新用户变量的值)
-
-
-
-
- SET @用户变量名=值;
-
-
-
-
- 查看变量值
-
-
-
-
- select @用户变量名
-
-
- 局部变量
-
- 作用域:仅仅在定义它的begin、end中有效;应用在begin、end中的第一句话
- 声明
-
-
- DECLARE 变量名 类型;
- DECLARE 变量名 类型 DEFAULT 值;
-
-
- 赋值
-
-
- SET 局部变量名=值
- SET 局部变量名:=值
- SELECT 局部变量名:=值
- SET 更新值 INTO 局部变量名
-
-
- 查询
-
-
- SELECT 局部变量名
-
- 对比用户变量和局部变量
变量类型 | 作用域 | 定义和使用的位置 | 语法 |
用户变量 | 当前会话 | 会话中的任何地方 | 必须加@符号,不用限定类型 |
局部变量 | begin、end中 | 只能在begin、end中,且为第一句话 | 一般不用加@符号,需要限制类型 |
- 案例
-
- 用户变量
- SET @m=1;
SET @n=2;
SET @sum=@m+@n;
SELECT @sum;
十一、存储过程和函数
- 存储过程:一组预先编译好的SQL语句的集合,理解成批处理语句
- 创建语法
-
- CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
存储过程体(一组合法的SQL语句)
END;
-
- 注意1:
-
-
- 参数列表包含三部分:参数模式、参数名、参数类型
- 实例
-
-
-
-
- IN stuname VARCHAR(20)
-
-
-
- 参数模式
-
-
- IN:该参数可以作为输入,也就是该参数需要调用方传入值
- OUT:该参数可以作为输出,也就是该参数可以作为返回值
- INTOUT:该参数既可以作为输入又可以作为输出,也就是该参数需要传入值,又可以返回值
-
-
- 注意2
-
-
- 如果存储过程体仅仅只有一句话,BEGIN END 可以省略
- 存储过程体中的每条SQL语句的结尾要求必须加分号
- 使用delimiter语句作为设置结束标记
-
- 调用语法:CALL 存储过程名(实参列表)
- 实例一(空参列表):插入到admin表中五条记录
-
- CREATE PROCEDURE myp1()
BEGIN
INSERT INTO admin('username','password')
VALUES
('john',0000),
('john',0000),
('john',0000),
('john',0000),
('john',0000);
END;
CALL myp1();
- 实例二(带IN模式参数):创建存储过程实现,根据女神名,查询对应的男神信息
-
- CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))
BEGIN
SELECT bo.*
FROM boys bo
RIGHT JOIN beauty b ON bo.id=b.boyfriend_id
WHERE b.name=beautyName;
END;
CALL myp2('张飞');
- 实例三(带OUT模式参数):创建存储过程实现,根据女神名,返回对应男神名和魅力值
-
- CREATE PROCEDURE myp3(IN beautyName VARCHAR(20),out boyName VARCHAR(20),OUT userCP INT)
BEGIN
SELECT bo.boyName,bo.userCP INTO boyName,userCP
FROM boys bo
RIGHT JOIN beauty b ON bo.id=b.boyfriend_id
WHERE b.name=beautyName;
END;
CALL myp3('张冰',@bName,@usercp);
select @bName,@usercp
- 删除存储过程
-
- DROP PROCEDURE 存储过程名;
- 查看存储过程的信息
-
- SHOW CREATE PROCEDURE 存储过程名;
- 函数:一组预先编译好的SQL语句的集合,理解成批处理语句
-
- 与存储过程的区别
-
-
- 存储过程:可以有0个返回,也可以有多个返回
- 函数:有且仅有1个返回,适合做处理后返回一个结果
-
-
- 创建语法
-
-
- CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
-
BEGIN
函数体
RETURN xxxxx;
END;
-
-
- 注意
-
-
-
-
- 函数体:肯定会有return语句,如果没有会报错
- 如果return语句没有放在函数体的最后也不会报错,但不建议
- 如果方法体仅仅只有一句话,BEGIN END 可以省略
- 使用delimiter语句作为设置结束标记
-
-
-
- 调用语句
-
-
- select 函数名(参数列表)
-
-
- 查看函数
-
-
- SHOW CREATE FUNCTION 函数名
-
-
- 删除函数
-
-
- DROP FUNCTION 函数名
-
十二、流程控制结构
- 分支结构
-
- if函数
-
-
- 功能:实现简单的双分支
- 语法:IF(表达式1,表达式2,表达式3)
-
-
-
-
- 执行顺序:如果表达式1成立,则IF函数返回表达式2的值,否则返回表达式3的值
- 应用:任何地方
-
-
-
- case结构
-
-
- 功能1:类似于java中的switch语句,一般用于实现的等值判断
-
-
-
-
- 语法:CASE | 变量 |表达式 |字段
-
-
WHEN 要判断的值 THEN 返回的值1 或 【语句1】
WHEN 要判断的值 THEN 返回的值2 或 【语句2】
. . .
ELSE 要返回的值n 或 语句n
END
-
-
- 功能2:类似于java中的多重IF语句,一般用于实现区间判断
-
-
-
-
- 语法:CASE
-
-
WHEN 要判断的条件1 THEN 返回的值1 或 【语句1】
WHEN 要判断的条件2 THEN 返回的值2 或 【语句1】
. . .
ELSE 要返回的值 或 语句n
END
-
-
- 特点
-
-
-
-
- 可以作为表达式,嵌套在其他语句中使用,可以放在任何地方,BEGIN END中 或 BEGIN END外面
- 可以作为独立的语句去使用,只能放在BEGIN END中
- 如果WHEN中的值或条件成立,则执行对应的THEN后面的语句,并且结束CASE
- 如果都不满足,则执行ELSE中的语句或值
- ELSE可以省略,如果省略了,并且所有WHEN条件都不满足,则返回NULL
-
-
-
-
- 案例:创建存储过程,根据传入的成绩,来显示等级,比如传入的成绩90-100,显示A;80-90,显示B;60-80,显示C;否则显示D.
-
-
-
-
- CASE PROCEDURE test_case(IN score INT)
-
-
BEGIN
CASE
WHEN SCORE >=90 AND SCORE <=100 THEN SELECT 'A';
WHEN SCORE >=80 THEN SELECT 'B';
WHEN SCORE >=60 THEN SELECT 'C';
ELSE SELECT 'D'
END CASE;
END
CALL test_case('95')
-
- IF 结构
-
-
- 功能:实现多重分支
- 语法:IF 条件1 THEN 语句1
-
ELSEIF 条件2 THEN 语句2
...
[ELSE 语句n]
END IF
-
-
- 应用场合:应用于BEGIN END中
-
-
- 案例:根据传入的成绩,来返回等级,比如传入的成绩90-100,返回A;80-90,返回B;60-80,返回C;否则返回D.
-
-
- CASE FUNCTION test_if( score INT) RETURNS CHAR
-
BEGIN
IF SCORE >=90 AND SCORE <=100 RETURN 'A';
ELSEIF SCORE >=80 THEN RETURN 'B';
ELSEIF SCORE >=60 THEN RETURN' 'C';
ELSE RETURN 'D'
END IF;
END;
CALL test_if('95')
- 循环结构
-
- 分类:while 、loop 、repeat
- 循环控制
-
-
- iterate 类似于 continue ,继续,结束本次循环,继续下一次
- leave 类似于 break,跳出,结束当前所有的循环
-
-
- while循环
-
-
- 语法
-
-
-
-
- 【标签】: while 循环条件 do
-
-
循环体
end while 【标签】;
-
- loop循环(可以用来模拟简单的死循环)
-
-
- 语法
-
-
-
-
- 【标签】: loop
-
-
循环体
end loop【标签】;
-
- repeat循环
-
-
- 语法
-
-
-
-
- 【标签】: repeat
-
-
循环体
until 结束循环的条件
end repeat【标签】;
-
- 实例1:批量插入,根据次数插入到admin表中多条记录
-
-
- CREATE PROCEDURE pro_while1(IN insertCount INT)
-
BEGIN
DECLARE i INT DEAULT 1;
WHILE i<i=nsertCount DO
INSERT INTO admin(username,'password') VALUES(CONCAT('test',i),'666')
SET i=i+1;
END WHILE
END
-
- 实例2:批量插入,根据次数插入到admin表中多条记录,如果次数>20则暂停
-
-
- CREATE PROCEDURE pro_while2(IN insertCount INT)
-
BEGIN
DECLARE i INT DEAULT 1;
a: WHILE i<i=nsertCount DO
INSERT INTO admin(username,'password') VALUES(CONCAT('test',i),'666');
IF i>=20 THEN LEASE a;
END IF;
SET i=i+1;
END WHILE a;
END $