sql基础
mysql启动命令
注:要以管理员身份打开cmd,否则命令无效
net stop/start 服务名
我的是mysql80
mysql登入命令
-h 接主机名
-P(大写与密码区分)接端口号
-u 接用户
-p 接密码(p与密码之间不能用空格)
mysql -uroot -p1234或mysql -uroot -p
mysql常用命令
1.查看当前所有的数据库
show databases;
2.打开指定的库
use 库名
3.查看当前库的所有表
show tables;
4.查看其它库的所有表
show tables from 库名;
5.创建表
create table 表名(
列名 列类型,
列名 列类型,
…
);
6.查看表结构
desc 表名;
7.查看当前所在的库
select database();
8.查询表里面的数据
select * from 表名
9.查看mysql版本
select version();
如果未登入mysql客户端
mysql --version
mysql -V
mysql的语法规范
1.不区分大小写,但建议关键字大小写,表名、列名小写
2.每条命令最好用分号结尾
3.每条命令根据需要,可以进行缩进或换行
4.注释
单行注释:#注释文字
单行注释:-- 注释文字
多行注释:/* 注释文字 */
DQL(Data Query Language)
基础查询
语法:
select 查询列表 from 表名
特点:
1、查询列表可以是:表中的字段、常量值、表达式、函数
2、查询的结果是一个虚拟的表格
1.查询表中的单个字段
select last_name from employees;
2.查询表中的多个字段
select last_name,salary,email from employees;
3.查询表中的所有字段
select * from employees;
4.查询常量值
select 100;
5查询表达式
select 100%98;
6.查询函数
select version();
7.起别名
1、便于理解
2、如果要查询的字段有重名的情况,使用别名可以区分开来
方式一:使用as
select 100%98 as 结果;
select last_name as 姓,first_name as 名 from employees;
方式二:使用空格
select last_name 姓,first_name 名 from employees;
8.去重
select distinct department_id from employees;
9.+号的作用
mysql中的+号
只有一个功能:运算符
select 100+90;两个操作数都为数值型,则做加法运算
select ‘123’+90;其中一方为字符型,试图将字符型数值转换成数值型
如果转换失败,则将字符型数值转换成0
如果转换成功,则继续做加法运算
select null+0 只要其中一方为null,则结果为null
条件查询
select
查询列表
from
表名
where
筛选条件;
分类:
一、按条件表达式筛选
条件运算符:> < = !=或<> >= <=
二、逻辑表达式筛选
作用:用于连接条件表达式
逻辑运算符:
&& || !
and or not
三、模糊查询
like
between and
in
is null或not null
select * from employees where salary>12000;
select last_name,salary,commission_pct from employees where salary>=10000 and salary <=20000;
like
一般和通配符搭配使用
通配符:
%任意多个字符,包含0个字符
_任意个字符
select * from employees where last_name like '%a%';
select * from employees where last_name like '__n_l%';
特殊情况:字段中有_
使用转义字符
select * from employees where last_name like '_\_%';
select * from employees where last_name like '_$_%' escape '$';#(推荐使用)
between and
包含临界值
select * from employees where employee_id >=100 and employee_id <=120;
select * from employees where employee_id between 100 and 120;
in
列表的值类型必须一致或兼容
不支持通配符
select last_name,job_id from employees where job_id ='it_prot' or job_id ='ad_vp' or job_id ='ad_pres';
select last_name,job_id from employees where job_id in ('it_prot','ad_vp' , 'ad_pres');
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 <=> null;
安全等于 <=>
不能使用=null
is null :仅仅可以判断null值
<=> :既可以判断null值,又可以判断普通的数值,可读性差
排序查询
语法
select 查询列表
from 表
where 筛选条件
order by 排序列表[asc|desc]
特点:
1、asc代表的是升序,desc代表的是降序,如果不写,默认降序
2、order by 子句中可以支持单个字段、多个字段、表达式、函数、别名
3、order by 子句一般是放在查询语句的最后面,limit 子句除外
select * from employees order by salary desc ;#降序
select * from employees order by salary asc;#升序,可以省略不写
按表达式排序
select * ,salary*12*(1+ifnull(commission_pct,0)) 年薪 from employees order by
salary*12*(1+ifnull(commission_pct,0));
按别名排序
select * ,salary*12*(1+ifnull(commission_pct,0)) 年薪 from employees order by 年薪;
按字节长度排序
select length(last_name) 字节长度,last_name,salary from employees order by
length(last_name) desc;
常见函数
功能:类似于java的方法,将一组逻辑语句封装在方法体中,对外暴露方法名
好处:1、隐藏了实现细节 2、提高代码的重用性
调用:select 函数名(实参列表) [from 表];
特点:函数名、函数功能
分类:
1、单行函数
如 concat、length、ifnull等
2、分组函数
功能:做统计使用
单行函数
一、字符函数
length 字节长度
select length(‘john’);
select length('是否哈里斯发货')
注:在utf8编码下,中文占三个字节,所以一个中文的长度是3
concat 拼凑字符串
select concat(last_name,'_',first_name) 姓名 from employees;
upper、lower 大小写
select upper('jhon');
select lower('hoHn');
select concat(upper(last_name),lower(first_name))from employees ;
substr、substring
注意:索引从1开始
截取指定索引后面所有的字符
select substr('fjasfjalj',3) as out_put;
截取从指定索引处指定字符长度的字符
select substr('fjasfjalj',1,3)as out_put;
案例,姓名中首字符大写,其它字符小写然后用_拼接,显示出来
select concat(upper(substr(last_name,1,1)) ,'_',substr(last_name,2)) out_put from employees;
instr
返回字串第一次出现的索引,如果找不到返回0
select instr('123456','34');
trim
去掉前面和后面的空格
select trim(' afsdjfla ') as out_put;
去掉前面和后面的指定字符
select trim('a' from 'aaaaaaaaaaaaaaaaaa张aaaaaaaaaaa翠山aaaaaaaaaaaaa') as
out_put ;
select trim('aa' from 'aaaaaaaaaaaaaaaaa张aaaaaaaaaaa翠山
aaaaaaaaaaaaa') as
out_put ;
lpad
用指定的字符实现左填充指定长度
select lpad('殷素素',10,'*') as out_put;
select lpad('殷素素',2,'*') as out_put;
rpad
用指定的字符实现右填充指定长度
select rpad('殷素素',10,'ab') as out_put;
replace
select replace ('周芷诺周芷诺周芷诺张无忌爱上了周芷诺','周芷诺','赵敏') out_put;
二、数学函数
round 四舍五入
select round(1.55);
如果是复数,先取绝对值,再取整
select round(-1.55);
保留指定位小数
select round(1.567,2);
ceil
向上取整,返回>=改参数的最小整数
select ceil (1.02);
select ceil (-1.02);
floor
向下取整,返回<=改参数的最小整数
select floor(-9.99);
truncate 截断
select truncate(1.65,1);
#mod 取余
mod(a,b)=a-(a/b)*b
select mod(10,-3);
select 10%3;
三、日期函数
now 返回当前系统日期+时间
select now();
curdate 返回当前系统日期,不包含时间
select curdate();
curtime 返回当前时间,不包含日期
select curtime();
**datediff **获取间隔时间
select datediff(now(),'1995-1-1');
获取指定的部分,年、月、日、小时、分钟、秒
select year (now()) 年;
select year (2021-08-30) 年;
select month(now()) 月;
select monthname(now())月;
类推
str_to_date
将字符通过指定的格式转换成日期
select str_to_date('2021-08-30 17:16:23','%Y-%c-%d') as out_put;
select str_to_date('4-2 2021','%d-%m %Y') as out_put;
date_format
将日期转换成字符
select date_format(now(),'%y年%m月%d日') as out_put;
四、其它函数
select version();#当前版本
select database();#当前打开的数据库
select user();#当前的用户
五、流程控制函数
if 函数
select if(10<5,'大','小');
select last_name,commission_pct,if(commission_pct is null,'没奖金','有奖金')
备注 from employees;
case 函数
使用一、
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
...
else 要显示的值n或语句
end
- 案例:查询员工的工资,要求
- 部门号=30,显示的工资位1.1倍
- 部门号=40,显示的工资为1.2倍
- 部门号=50,显示的工资为1.3倍
- 其它部门,显示为原工资
select 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 新工资
from employees;
使用二、
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 as 工资级别
from employees;
多行函数(分组函数)
功能:用于统计使用,称为聚合函数或统计函数
分类:sum 求和、avg 平均值、min 最小值、count 计算个数
特点:
1、sum、avg一般用于处理数值型
max、min、count可以处理任何类型
2、以上分组函数都忽略null值
select sum(salary) 和,
avg(salary) 平均,
min(salary) 最低,
max(salary) 最高,
count(salary) 个数
from employees;
支持的类型
max和min支持字符、日期、数字…
select max(last_name),min(last_name)from employees;
count支持任意数据类型
结果是该字段不为null的个数
以下函数忽略null值
select
sum(commission_pct),
avg(commission_pct),
sum(commission_pct)/count(commission_pct),
count(commission_pct)
from employees;
和distinct搭配
select
sum(distinct salary),sum(salary),
count(distinct salary),count(salary)
from employees;
count函数详细介绍
select count(*) from employees;
#里面也可以加常量值,效果一样
select count("fsadfj") from employees;
用于统计行数
效率:
myisam存储引擎下,count(*)的效率高
innodb存储引擎下,count(*)和count(1)的效率差不多
和分组函数一同查询有限制
select avg(salary),employee_id from employees;
employee_id的查询结果没有任何的意义
分组查询
语法
select 分组函数,列(要求出现在group by 后面)
from 表
[where 筛选条件]
group by分组列表
[order by 子句]
[having 筛选条件]
注意:查询列表比较特殊,要求分组函数和group by后出现的字段
1、分组查询的筛选条件分为两类
数据源 | 位置 | 关键字 | |
---|---|---|---|
分组前筛选 | 原始表 | group by子句的前面 | where |
分组后筛选 | 分组后的结果集 | group by子句的后面 | having |
分组函数做条件肯定是放在having子句中
能用分组前筛选的,就优先考虑分组前筛选
2、group by 子句支持单个字段、多个字段分组(多个字段之间用逗号隔开没有顺序要求)
3、添加排序(排序放在整个分组查询的最后)
查询不同工种的最高工资
select max(salary), job_id from employees group by job_id ;
查询哪个部门的员工个数>2
select count(*),department_id from employees where count(*)>2
group by department_id;
会报错,where 不能这么使用,count(*)不属于employees中的字段
select count(*),department_id from employees group by department_id
having count(*)>2 ;
查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
select
max(salary),
job_id
from
employees
where
commission_pct is not null
group by
job_id
having
max(salary)>12000;
commission_pct 字段属于employees
多个字段分组
select
avg(salary),
job_id,
department_id
from
employees
group by
job_id,
department_id
order by
avg(salary) desc ;
连接查询
含义:又称多表查询,当查询的字段来自于多个表时,就会用到连接查询
笛卡尔乘积现象:表1 有m行,表2有n行,结果=m*n行
发生原因:没有有效的连接条件
如何避免:添加有效的连接条件
分类:
按年代分类:
sql92标准:仅仅支持内连接
sql99标准【推荐】:支持内连接+外连接(左外和右外)+交叉连接
按功能分类:
内连接:
等值连接
非等值连接
自连接
外连接:
左外连接
右外连接
全外连接
交叉连接
一、sql 92标准
1、等值连接
select
name,
boyName
from
boys,
beauty
where
beauty.boyfriend_id = boys.id
查询员工名、工种号、工种名
注意:如果为表起了别名,则查询的字段就不能使用原来的表名去限定
select
last_name,
e.job_id,
job_title
from
employees e,
jobs j
where
e.job_id = j.job_id
查询每个城市的部门个数
select
count(*) 个数 ,
l.city
from
departments d ,
locations l
where
d.location_id = l.location_id
group by
l.city ;
查询每个工种的工种名和员工的个数,并且按员工个数降序
select
job_title 工种 ,
count(*) 个数
from
employees e ,
jobs j
where
e.job_id = j.job_id
group by
job_title
order by 个数 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 ;
2、非等值连接
查询员工的工资和工资级别
select
last_name,
salary,
grade_level
from
employees e ,
job_grades jg
where
salary between jg.lowest_sal and jg.highest_sal ;
3、自连接
查询员工和上级的名称
select
e.employee_id ,
e.last_name,
e2.employee_id ,
e2.last_name
from
employees e,
employees e2
where
e.manager_id = e2.employee_id ;
二、sql 99
语法
select 查询列表
from 表1 别名 【连接类型】
join 表2 别名
on 连接条件
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序条件】
内连接:inner
外连接:
左外:left 【outer】
右外:right 【outer】
全外:full 【outer】
交叉连接:cross
内连接
select 查询列表
from 表1 别名 【连接类型】
inner join 表2 别名
on 连接条件
等值连接
select
last_name,
department_name
from
employees e
inner join departments d
on
e.department_id = d.department_id;
三表连接
查询员工名、部门名、工种名、并按部门名排序
注:连接条件放一起也可以
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;
非等值连接
select
last_name,
salary ,
grade_level
from
employees e
inner join job_grades jg
on
salary between jg.lowest_sal and jg.highest_sal
自连接
select
e.employee_id 员工id,
e.last_name 员工,
e1.employee_id 上级id,
e1.last_name 上级
from
employees e
inner join employees e1
on
e.manager_id = e1.employee_id ;
外连接
应用场景:用于查询一个表中有,另一个表没有的记录
如果从表中有和它匹配的,则显示匹配的值
如果从表中没有和它匹配的值,则显示null
外连接查询结果=内连接结果+主表中有而从表没有的记录
左外连接
select
b.name,
bo.*
from
beauty b
left outer join boys bo
on
b.boyfriend_id = bo.id
where
bo.id is null;
查询没有员工的部门
select
d.department_name
from
departments d
left join employees e
on
d.department_id = e.department_id
where e.employee_id is null;
右外连接
select
d.department_name
from
employees e
right join departments d
on
d.department_id = e.department_id
where e.employee_id is null;
全外连接(mysql不支持)
mysql不支持
结果=内连接的结果+表1中有单表2中没有的+表2中有但表1中没有的
交叉连接
迪卡尔乘积
子查询
出现在其它语句中的select语句中,称为子查询或内查询
外部的查询语句,称为主查询或外查询
分类:
按子查询出现的位置:
select 后面:仅仅支持标量子查询
from 后面 :支持表子查询
where 或 having 后面:标量子查询、列子查询、行子查询
exists 后面(相关子查询):表子查询
按结果集的行列数不同:
标量子查询(结果集只有一行一列)
列子查询(结果集只有一列多行)
行子查询(结果有一行多列)
表子查询(结果集一般为多行多列)
where或having后面
1、标量子查询(单行子查询)
2、表子查询(多行子查询)
3、行子查询
特点:
1、子查询放在小括号内
2、子查询一般放在条件的右侧
3、标量子查询,一般搭配着单行操作符使用> < >= = <>
列子查询:一般搭配着多行操作符使用
in 、any/some、all
any:任意一个
all:全部
标量查询
select
*
from
employees e
where
salary >(
select
salary
from
employees e
where
last_name = "abel"
);
返回job_id与141号员工相同,salary比143号员工多的员工 姓名,job_id和工资
select
last_name ,job_id,salary
from
employees e
where
job_id =(
select
job_id
from
employees
where
employee_id = 141)
and salary > (
select
salary
from
employees
where
employee_id = 143);
放在having后面
select
min(salary) ,
department_id
from
employees
group by
department_id
having
min(salary)
>(
select
min(salary)
from
employees e
where
department_id = 50)
列子查询
返回location_id是1400或1700的部门中的所有员工姓名
select
last_name
from
employees e
where
department_id in(
select
distinct department_id
from
departments d
where
location_id in (1400, 1700));
返回其它部门中比job_id为’it_prog’部门任一工资低的员工的员工号、姓名、job_id 以及salary
select
last_name,
employee_id ,
job_id ,
salary
from
employees
where
salary <salary any(
select
distinct salary
from
employees
where
job_id = 'it_prog');
行子查询
查询员工编号最小并且工资最高的员工信息
select
*
from
employees e
where
employee_id =(
select
min(employee_id)
from employees)
and salary =(
select
max(salary)
from
employees);
或者
select
*
from
employees
where
(employee_id,
salary)=(
select
min(employee_id),
max(salary)
from employees);
select后面
仅支持标量子查询
查询每个部门的员工个数
select
d.*,
(
select
count(*)
from
employees e
where
e.department_id = d.department_id)
from
departments d ;
查询员工号=102的部门名
select(select
department_name
from
departments d
inner join employees e
on
d.department_id = e.department_id
where
e.employee_id = 102);
from后面
每个部门的平均工资的工资等级
select
ag_dep.*,
jg.grade_level
from
(
select
avg(salary) ag ,
department_id
from
employees e
group by
department_id) ag_dep
inner join job_grades jg
on
ag_dep.ag between lowest_sal and highest_sal ;
exists后面
语法
exists(完整的查询语句)
结果为1或0
select exists (select employee_id from employees);
有值为1
查询有员工的部门名
select
department_name
from
departments d
where
exists (
select
*
from
employees e
where
d.department_id = e.department_id
);
分业查询
一、应用场景
当要查询的条目数太多,一页显示不全,需要分页提交sql请求
二、语法
select 查询列表
from 表
limit 【offset】,size;
注意:
offset代表的是起始的条目索引,默认为0
size代表的是显示的条目数
公式:
假如要显示的页数为page,每一页条目数为size
select 查询列表
from 表
limit (page-1)*size,size;
select * from employees e limit 5;
或
select * from employees e limit 0,5;
联合查询
union 联合 合并:将多条查询语句的结果合并成一个结果
语法
查询语句1
union
查询语句2
union
…
应用场景:
要查询的结果来自于多个表,且多个表没有直接的连接关系,但查询的信息一致
特点:
1.要求多条查询语句的查询列表是一致的
2.要求多条查询语句的查询的每一列的类型和循序最好一致
查询部门编号>90或邮箱包含a的员工信息
select * from employees e where email like '%a%' or department_id >90;
或者
select * from employees where email like '%a%' union select * from employees
where department_id >90;
可以查询有相同字段的两张表
DML(Data Manipulation Language)
数据操作语言:
插入:insert
修改:update
删除:delete
插入
方式一
语法
insert into 表名(列名,...)
values(值1,...);
插入的值的类型要与列的类型一致或兼容
insert into beauty(id,name,sex,borndate,phone,photo,boyfriend_id)
values(13,'唐艺昕','女','1990-4-23','18988888888',null,2);
或
insert into beauty values(13,'唐艺昕','女','1990-4-23','18988888888',null,2);
列和值可以调换顺序,但是列和值要对应上
方式二
insert into table_name
set 列名=值,...
insert into beauty set id=19,name='刘涛',phone ='999';
方式一支持插入多行,方式二不支持
方式一支持子查询,方式二不支持
insert into beauty(id,name,sex,phone)
values(14,'jack','男','412341'),
(13,'唐艺昕','女','1990-4-23'),
(14,'jack','男','412341');
insert into beauty(id,name,phone)
select 26,'宋茜','141234'
修改
truncate talbe 表名(清空所有内容)
修改单表
update 表名
set 列=新值,列=新值,...
where 筛选条件
update beauty
set name = 'jack1'
where name = 'jack';
修改多表的记录
语法
sql92
update 表1 别名,表2 别名
set 列=值
where 连接条件 and 筛选条件
sql99
update 表1 别名,
inner |left|right join 表2 别名
on 连接条件
set 列=值,...
where 筛选条件
update boys bo
inner join beauty b on bo.id =b.boyfriend_id
set b.phone ='114'
where bo.boyName ='张无忌';
删除
方式一 单表的删除
delete from 表名 where 筛选条件
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 筛选条件;
delete b
from beauty b
inner join boys bo on b.boyfriend_id =bo.id
where bo.boyName ="张无忌";
delete b,bo
from beauty b
inner join boys bo on b.boyfriend_id =bo.id
where bo.boyName ="黄晓明";
DDL
库的管理
创建、修改、删除
1、创建
create database 库名;
增加容错性
create database if not exists books;
2、修改
①修改库名
rename database books to 新库名
不稳定,不建议修改库名,新版本无法使用
②更改字符集
alter database books character set gbk;
3、删除
drop database books;
drop database if exists books;
表的管理
1、创建表
语法
create table 表名(
列名 列的类型【(长度)约束】,
列名 列的类型【(长度)约束】,
...
)
create table book(
id int,#编号
bName varchar(20),#图书馆
price double,#价格
authorId int,#作者编号
publishDate datetime#出版日期
)
create table author(
id int,
au_name varchar(20),
nation varchar(10)
)
desc author;
2、修改
语法
alter table 表名 add|drop|modify|change column 【列名】【约束】
①修改列名
alter table book change column publishdate pubDate datetime;
②修改列的类型或约束
alter table book modify column pubdate timestamp;
③添加新列
alter table author add column annual double;
④删除列
alter table author drop column annual;
⑤修改表名
alter table author rename to book_author
3、删除
drop if exists author;
4、表的复制
insert into author values
(1,'村上春树','日本'),
(2,'莫言','中国'),
(3,'冯唐','中国'),
(4,'金庸','中国');
①复制表的结构
create table copy like author;
②复制表的结构+数据
create table copy2 select * from author;
③复制结构和部分数据
create table copy3 select * from author where nation ="中国";
④复制部分结构
create table copy4 select id,au_name from author where 0;
数据类型
数据类型
数值型:
整型
小数:
定点数
浮点数
字符型:
较短的文本:char、varchar
较长的文本:text、blob(较长的二进制数据)
日期型:
整型
分类:
tinyint、smallint、mediumint、int/integer、bright
1 2 3 4 8 字节
默认为有符号
create table tab_int(t1 int,#有符号
t2 int unsigned);#无符号
特点:
①如果不设置无符号或者有符号,默认是有符号,如果想设置无符号,需要添加unsigned关键字
②如果插入的数值超出了整型的范围,会报 out of rang异常,报错
③如果不设置长度,会有默认的长度
长度代表了显示的最大宽度,如果不够会用0在左边填充,但必须搭配zerofill使用!
使用zerofill会自动变成无符号型
create table tab_int1 (
t1 int(7) zerofill,
t2 int(7)
);
insert into tab_int1 values(123,123);
小数
浮点型
float(M,D)
double(M,D)
定点型
dec(M,D)
decimal(M,D)
M代表数字长度,D代表小数位数
create table tab_float(
f1 float(5,2),
f2 double(5,2),
f3 decimal(5,2)
);
insert into tab_float values (123.45,123.45,123.45);
insert into tab_float values (123.45,123.45,123.456);
insert into tab_float values (1234.45,1234.45,1234.45);#超出范围,报错
省略(m,d)
create table tab_float1(
f1 float,
f2 double,
f3 decimal
);
类型的选择
所选择的类型越简单越好,能保存数值的情况下类型越小越好(占用空间小)
字符型
写法 | M的意思 | 特点 | |||
---|---|---|---|---|---|
char | char(M) | 最大的字符数,可以省略,默认为1 | 固定长度的字符 | 空间的耗费比较大 | 效率高 |
varchar | varchar(M) | 最大的字符数,不可以省略 | 可变长度的字符 | 比较省空间 | 效率低 |
enum
不区分大小写
create table tab_char(
c1 enum('a','b','c')
)
insert into tab_char values ('a');
insert into tab_char values ('b');
insert into tab_char values ('c');
insert into tab_char values ('m');#插入失败
insert into tab_char values ('A');
set
不区分大小写
create table tab_set(
s1 set('a','b','c','d')
);
insert into tab_set values ('a');
insert into tab_set values ('a,b');
insert into tab_set values ('a,c,d');
insert into tab_set values ('a,d,d');
日期型
date 只保存日期
time 只保存时间
year 只保存年
字节 | 范围 | 时区等的影响 | |
---|---|---|---|
datetime | 8 | 1000-9999 | 不受 |
timestamp | 4 | 1970-2038 | 守 |
create table tab_date(
t1 datetime,
t2 timestamp
);
insert into tab_date values(now(),now());
select * from tab_date;
改变时区
set time_zone='+9:00'
常见的约束
含义:一种限制,用于限制表中的数据,为了保证表中的数据的准确和可靠性
分类:六大约束
not
create table 表名(
字段名 字段类型 约束
)
not null | 非空,用于保证该字段的值不能为空,比如姓名、学号等 |
---|---|
default | 默认,用于保证该字段有默认值。比如性别 |
primary key | 主键,用于保证该字段的值具有唯一性,并且非空。比如学号 |
unique | 唯一,用于保证该字段的值具有唯一性,可以为空。比如座位号 |
check | 检查约束(mysql中不支持) |
foreign key reference | 外键,用于限制两个表的关系,用于保证字段的值必须来自于主表的关联列的值。在从表中添加外键的约束,用于引用主表中某列的值,学生表的专业编号,员工表的部门标号,员工表的工种编号 |
主键和唯一的区别
保证唯一性 | 是否允许为空 | 一个表可以有多少个 | 是否允许组合 | |
---|---|---|---|---|
主键 | √ | × | 至多有一个 | √,不推荐 |
唯一 | √ | √ | 可以有多个 | √,不推荐 |
外键
1、要求在从表设置外键关系
2、从表的外键列的类型和主表的关联列的类型要求一致或兼容,名称无要求
3、主表的关联列必须是一个key(一般是主键或唯一)
添加约束的时机:
1、创建表时
2、修改表时
约束的添加分类
列级约束:
六大约束语法上都支持,但外键约束没有效果
表级约束:
除了非空,其它都支持
create table 表名(
字段名 字段类型 列级约束,
字段名 字段类型 ,
表级约束
)
一、创建表时添加约束
①添加列级约束
检查和外键都没效果
create database students;
use students;
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 stuinfo1(
id int,
stuname varchar(20),
gender char(1),
seat int,
age int,
majorid int,
constraint pk primary key(id),#主键
constraint ug unique(seat),
constraint ck check(gender='男' or gender='女'),
constraint fk_stuinfo_major foreign key(majorid) references major(id)#外键
)
constraint pk可以省略pk是取的名字
通用的写法
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,
constraint fk_stuinfo_major foreign key(majorid) references major(id)#外键
);
二、修改表时添加约束类型
drop table if exists stuinfo;
create table stuinfo (
id int,
stuname varchar(20),
gender char(1),
seat int,
age int,
majorid int
);
①添加非空约束
alter table stuinfo modify column stuname varchar(20) not null;
②添加默认约束
alter table stuinfo modify column age int default 18;
③添加主键
#列级约束
alter table stuinfo modify column id int primary key;
#表级约束
alter table stinfo add primary key(id);
④添加唯一
alter table stuinfo modify column seat int unique;
三、删除约束
①删除非空约束
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 majorid;
标识列
又称为自增长列
含义:可以不用手动的插入值,系统提供默认的序列值
create table tab_identity(
id int primary key auto_increment,
name varchar(20)
);
多次执行
insert into tab_identity values(null,'join');
set auto_increment_increment=3;
更改步长
特点
表示列必须和key搭配
一个表至多有一个标识列
标识列的类型只能是数值型
修改表时设置标识列
alter table tab_identity modify column id int primary key auto_increment;
修改表时修改标识列
alter table tab_identity modify column id int;
TCL(Transaction Control Language)
事务控制语言
事务:
一个或一组sql语句组成一个执行单元,这个执行单元要么全部执行,要么全部不执行
事务的ACID(acid)属性
-
原子性(Atomicity) 原子性是指事务是一个不可分割的工作单位,事务中的操作要么 都发生,要么都不发生。
-
一致性(Consistency) 事务必须使数据库从一个一致性状态变换到另外一个一致性状态 。
-
隔离性(Isolation) 事务的隔离性是指一个事务的执行不能被其他事务干扰,即一个 事务内部的操作及使用的数据对并发的其他事务是隔离的,并发 执行的各个事务之间不能互相干扰。
-
持久性(Durability) 持久性是指一个事务一旦被提交,它对数据库中数据的改变就是 永久性的,接下来的其他操作和数据库故障不应该对其有任何影 响
事物的创建
隐式事务:事物没有明显的开启和结束的标记
比如insert、update、delete语句
delete from 表 where id=1;
显式事务:事务具有明显的开启和结束的标志
前提:必须设置自动提交功能为禁用
set autocommit=0;
步骤1:开启事物
set autocommit=0;
start transaction;可选的
步骤2:编写事务中的sql语句(select insert update delete)
语句1;
语句2;
…
步骤三:结束事务
commit;提交事务
rollback;回滚事务
#开启事务
set autocommit=0;
#编写一组事务的语句
update account set balance=1000 where username='a';
update account set balance=1500 where username='b';
#结束事务
commit;
回滚
#开启事务
SET autocommit=0;
#编写一组事务的语句
UPDATE account SET balance=800 WHERE username='a';
UPDATE account SET balance=800 WHERE username='b';
#结束事务
ROLLBACK
不执行,数据不变
delete和truncate在事务中的区别
set autocommit=0;
start transaction;
delete from account;
rollback;
set autocommit=0;
start transaction;
truncate table account;
rollback;
delete支持回滚
truncate 不支持回滚,数据会被删除
视图
含义:虚拟表,和普通表一样使用
msyql5.1版本出现的新特性,是通过表动态生成的数据
只保存了sql逻辑,不保存sql语句
特点:
重用sql语句
简化复杂的sql操作,不必知道它的查询细节
保护数据,提高安全性
一、视图的创建
create view 视图名
as
查询语句
#创建
create view myv1
as
select last_name,department_name,job_title
from employees e
join departments d on e.department_id =d.department_id
join jobs j on j.job_id=e.job_id ;
#使用
select * from myv1 where last_name like '%a%';
二、视图的修改
方式一:
没有就创建,有就替代
create or replace view 视图名
as
查询语句;
create or replace view myv1
as
select last_name,department_name,job_title
from employees e
join departments d on e.department_id =d.department_id
join jobs j on j.job_id=e.job_id ;
方式二
alter view 视图名
as
查询语句;
三、删除视图
drop view 视图名,视图名...;
四、查看视图
desc myv1;
show create view myv1;
五、视图的更新
会更改原表
insert into myv1 values(...);
update myv1 set last_name='' where
变量
系统变量:
全局变量
会话变量
自定义变量:
用户变量
局部变量
一、系统变量
说明: 变量由系统提供,不是用户定义,属于服务器层面
使用语法:
1、查看所有的系统变量
show global variables;#全局变量
show session variables;#会话变量 session可以省略
2、查看满足条件的部分系统变量
show global|session variables like '%char%'
3、查看指定的某个系统变量的值
select @@global|session.系统变量名;
4、为某个系统变量赋值
方式一
set global | session 系统变量名=值;
方式二
set @@global|session.系统变量名=值;
注:
如果是全局级别,则需要加globa,如果是会话级别,则需要加session,如果不写,则默认session
作用域:
全局变量:服务器每次启动将为所有的全局变量赋初始值,针对于所有的会话(连接)有效,但不能跨重启
会话变量:只对当前会话和连接有效
二、自定义变量
变量是用户自定义的,不是由系统提供的
使用步骤
声明
赋值
使用(查看、比较、运算)
1、用户变量
作用域:针对当前会话和连接有效,同于会话变量的作用域
赋值的操作符为: =或:=
①声明并初始化
set @用户变量名=值;
set @用户变量名:=值;
select @用户变量名:=值;
②赋值(更新用户变量的值)
方式一:通过set或select
set @用户变量名=值;
set @用户变量名:=值;
select @用户变量名:=值;
方式二:通过select into
select 字段 into 变量名 from 表
③使用
select @用户变量名
2、局部变量
作用域:仅仅在定义它的begin end 中有效
应用在begin end中的第一句话
①声明
declare 变量名 类型;
declare 变量名 类型 defalut 值;
②赋值(更新用户变量的值)
方式一:通过set或select
set 局部变量名=值;
set 局部变量名:=值;
select @局部变量名:=值;
方式二:通过select into
select 字段 into 局部变量名 from 表
③使用
select 局部变量名
存储过程和函数
存储过程
含义:一组预先编译好的sql语句的集合,理解成批处理语句
1、提高代码的重用性
2、简化操作
3、减少了编译次数并且减少了数据库服务器的连接次数,提高了效率
一、创建过程
create procedure 存储姓名(参数列表)
begin
方法体(一组合法的sql语句)
end
注意:
1、参数列表包含三部分
参数模式 参数名 参数类型
举例:
in stuname varchar(20)
参数模式:
in:该参数可以作为输入,也就是该参数需要调用方传入值
out:该参数可以作为输出,也就是该参数可以作为返回值
inout:该参数即可以作为输出,也就是该参数既需要传入值,又可以返回值
2、如果存储过程仅仅只有一句话,begin end可以省略
存储过程中的每条语句的结尾必须加分号。
存储过程的结尾可以使用delimiter重新设置
语法:
delimiter 结束标记
例如
delimiter $
二、调用
call 存储过程名(实参列表);
1、空参列表
插入到admin表中五条记录
delimiter $
create procedure myp1()
begin
insert into admin(username,`password`)
values('john1','0000'),('lily','00000'),('rose','00000'),('jack','00000'),('tom','00000');
end $
要在cmd中执行
调用
call myp1()$
2、创建带in模式参数的存储过程
create procedure myp2(in beautyname varchar(20) )
begin
select *
from boys bo
right join beauty b on bo.id=b.boyfriend_id
where b.name=beautyname;
end $
调用
call myp2('王语嫣') $
创建存储过程实现判断用户是否登入成功
create procedure myp3(in username varchar(20),in PASSWORD varchar(20))
begin
declare result int default 0;#声明并初始化
select count(*)into result#赋值
from admin a
where a.username=username
and a.password=PASSWORD;
select if(result>0,'success','fail');
end $
调用
call myp3('张飞','8888')$
3、创建带out模式的存储过程
create procedure myp4(in beautyName varchar(20),out boyName varchar(20))
begin
select bo.boyname into boyname
from boys bo
inner join beauty b on bo.id=b.boyfriend_id
where b.name=beautyName;
end $
调用
call myp4('王语嫣',@bName)$
select @bName $
4、创建带inout模式参数的存储过程
传入a和b两个值,最终a和b都翻倍并返回
create procedure myp5(inout a int,inout b int)
begin
set a=a*2;
set b=b*2;
end $
set @a=1 $
set @b=2 $
call myp5(@a,@b) $
select @a,@b $
二、删除存储过程
drop procedure pl;
三、查看存储过程的信息
show create procedure myp1;
函数
存储过程:可以有0个返回,也可以有多个返回,适合做批量插入、批量更新
函数:有且仅有一个返回,适合做处理数据后返回一个结果
一、创建语法
create function 函数名(参数列表) returns 返回类型
begin
函数体
end
注意
函数体:肯定会有return语句,如果没有会报错
如果return语句没有放在函数体的最后也不报错,但不建议
return 值;
函数体中仅有一句话,则可以省略begin end
使用 delimiter 语句设置结束标记
delimiter s;
create function myf1() returns int
begin
declare c int default 0;
select
count(*)
into
c
from
employees;
return c;
end $
如果无法创建参考文章https://blog.csdn.net/GT_Stone/article/details/84499118
这里我们如下设置
SET GLOBAL log_bin_trust_function_creators = 1;
调用
select myf1()$
二、查看函数
show create function myf1;
三、删除函数
drop function myf3;
流程控制结构
顺序结构:程序从上往下依次执行
分支结构:程序从两条或多条路径中选择一条去执行
循环结构:程序在满足一定条件的基础上,重复执行一段代码
一、分支结构
1、if函数
功能:实现简单的分支
语法:
select if(表达式1,表达式2,表达式3)
执行顺序
如果表达式1成立,则if函数返回表达式2的值,否则返回表达式3的值
应用:任何地方
2、case
参考流程控制函数
3、if结构
功能:实现多重分支
语法
if 条件1 then 语句1;
elseif 条件2 then 语句2;
...
end if;
应用在begin end中
delimiter $
create function test_if(score int)returns char
begin
if score>=90 and score<=100 then return 'A';
elseif score>80 then return 'B';
elseif score>60 then return 'C';
else return 'D';
end if;
end $
select test_if(80);
二、循环结构
分类:
while、loop、repeat
循环控制:
iterate类似于continue 继续
leave类似于 break 跳出
1、while
语法:
while 循环条件 do
循环体;
end while 【标签】;
2、loop
语法
loop
循环体
end loop 【标签】;
可以用来模拟简单的死循环
3、repeat
语法:
【标签:】repeat
循环体;
until 结束循环的条件;
end repeat 【标签】;
批量插入,根据次数插入到admin表中多条记录
delimiter $
create procedure pro_while1(in insertCount int)
begin
declare i int default 1;
while i<= insertCount Do
insert into admin(username,`password`)values (concat('Rose',i),'666');
set i=i+1;
if i>=20 then leave a;
end while a;
end $
call pro_while1(10)
into boyname
from boys bo
inner join beauty b on bo.id=b.boyfriend_id
where b.name=beautyName;
end $
调用
```sql
call myp4('王语嫣',@bName)$
select @bName $
4、创建带inout模式参数的存储过程
传入a和b两个值,最终a和b都翻倍并返回
create procedure myp5(inout a int,inout b int)
begin
set a=a*2;
set b=b*2;
end $
set @a=1 $
set @b=2 $
call myp5(@a,@b) $
select @a,@b $
二、删除存储过程
drop procedure pl;
三、查看存储过程的信息
show create procedure myp1;
函数
存储过程:可以有0个返回,也可以有多个返回,适合做批量插入、批量更新
函数:有且仅有一个返回,适合做处理数据后返回一个结果
一、创建语法
create function 函数名(参数列表) returns 返回类型
begin
函数体
end
注意
函数体:肯定会有return语句,如果没有会报错
如果return语句没有放在函数体的最后也不报错,但不建议
return 值;
函数体中仅有一句话,则可以省略begin end
使用 delimiter 语句设置结束标记
delimiter s;
create function myf1() returns int
begin
declare c int default 0;
select
count(*)
into
c
from
employees;
return c;
end $
如果无法创建参考文章https://blog.csdn.net/GT_Stone/article/details/84499118
这里我们如下设置
SET GLOBAL log_bin_trust_function_creators = 1;
调用
select myf1()$
二、查看函数
show create function myf1;
三、删除函数
drop function myf3;
流程控制结构
顺序结构:程序从上往下依次执行
分支结构:程序从两条或多条路径中选择一条去执行
循环结构:程序在满足一定条件的基础上,重复执行一段代码
一、分支结构
1、if函数
功能:实现简单的分支
语法:
select if(表达式1,表达式2,表达式3)
执行顺序
如果表达式1成立,则if函数返回表达式2的值,否则返回表达式3的值
应用:任何地方
2、case
参考流程控制函数
3、if结构
功能:实现多重分支
语法
if 条件1 then 语句1;
elseif 条件2 then 语句2;
...
end if;
应用在begin end中
delimiter $
create function test_if(score int)returns char
begin
if score>=90 and score<=100 then return 'A';
elseif score>80 then return 'B';
elseif score>60 then return 'C';
else return 'D';
end if;
end $
select test_if(80);
二、循环结构
分类:
while、loop、repeat
循环控制:
iterate类似于continue 继续
leave类似于 break 跳出
1、while
语法:
while 循环条件 do
循环体;
end while 【标签】;
2、loop
语法
loop
循环体
end loop 【标签】;
可以用来模拟简单的死循环
3、repeat
语法:
【标签:】repeat
循环体;
until 结束循环的条件;
end repeat 【标签】;
批量插入,根据次数插入到admin表中多条记录
delimiter $
create procedure pro_while1(in insertCount int)
begin
declare i int default 1;
while i<= insertCount Do
insert into admin(username,`password`)values (concat('Rose',i),'666');
set i=i+1;
if i>=20 then leave a;
end while a;
end $
call pro_while1(10)
测试所用的数据
以下数据都来自尚硅谷
/*
SQLyog Ultimate v10.00 Beta1
MySQL - 5.5.15 : Database - myemployees
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;
/*!40101 SET SQL_MODE=''*/;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`myemployees` /*!40100 DEFAULT CHARACTER SET gb2312 */;
USE `myemployees`;
/*Table structure for table `departments` */
DROP TABLE IF EXISTS `departments`;
CREATE TABLE `departments` (
`department_id` int(4) NOT NULL AUTO_INCREMENT,
`department_name` varchar(3) DEFAULT NULL,
`manager_id` int(6) DEFAULT NULL,
`location_id` int(4) DEFAULT NULL,
PRIMARY KEY (`department_id`),
KEY `loc_id_fk` (`location_id`),
CONSTRAINT `loc_id_fk` FOREIGN KEY (`location_id`) REFERENCES `locations` (`location_id`)
) ENGINE=InnoDB AUTO_INCREMENT=271 DEFAULT CHARSET=gb2312;
/*Data for the table `departments` */
insert into `departments`(`department_id`,`department_name`,`manager_id`,`location_id`) values (10,'Adm',200,1700),(20,'Mar',201,1800),(30,'Pur',114,1700),(40,'Hum',203,2400),(50,'Shi',121,1500),(60,'IT',103,1400),(70,'Pub',204,2700),(80,'Sal',145,2500),(90,'Exe',100,1700),(100,'Fin',108,1700),(110,'Acc',205,1700),(120,'Tre',NULL,1700),(130,'Cor',NULL,1700),(140,'Con',NULL,1700),(150,'Sha',NULL,1700),(160,'Ben',NULL,1700),(170,'Man',NULL,1700),(180,'Con',NULL,1700),(190,'Con',NULL,1700),(200,'Ope',NULL,1700),(210,'IT ',NULL,1700),(220,'NOC',NULL,1700),(230,'IT ',NULL,1700),(240,'Gov',NULL,1700),(250,'Ret',NULL,1700),(260,'Rec',NULL,1700),(270,'Pay',NULL,1700);
/*Table structure for table `employees` */
DROP TABLE IF EXISTS `employees`;
CREATE TABLE `employees` (
`employee_id` int(6) NOT NULL AUTO_INCREMENT,
`first_name` varchar(20) DEFAULT NULL,
`last_name` varchar(25) DEFAULT NULL,
`email` varchar(25) DEFAULT NULL,
`phone_number` varchar(20) DEFAULT NULL,
`job_id` varchar(10) DEFAULT NULL,
`salary` double(10,2) DEFAULT NULL,
`commission_pct` double(4,2) DEFAULT NULL,
`manager_id` int(6) DEFAULT NULL,
`department_id` int(4) DEFAULT NULL,
`hiredate` datetime DEFAULT NULL,
PRIMARY KEY (`employee_id`),
KEY `dept_id_fk` (`department_id`),
KEY `job_id_fk` (`job_id`),
CONSTRAINT `dept_id_fk` FOREIGN KEY (`department_id`) REFERENCES `departments` (`department_id`),
CONSTRAINT `job_id_fk` FOREIGN KEY (`job_id`) REFERENCES `jobs` (`job_id`)
) ENGINE=InnoDB AUTO_INCREMENT=207 DEFAULT CHARSET=gb2312;
/*Data for the table `employees` */
insert into `employees`(`employee_id`,`first_name`,`last_name`,`email`,`phone_number`,`job_id`,`salary`,`commission_pct`,`manager_id`,`department_id`,`hiredate`) values (100,'Steven','K_ing','SKING','515.123.4567','AD_PRES',24000.00,NULL,NULL,90,'1992-04-03 00:00:00'),(101,'Neena','Kochhar','NKOCHHAR','515.123.4568','AD_VP',17000.00,NULL,100,90,'1992-04-03 00:00:00'),(102,'Lex','De Haan','LDEHAAN','515.123.4569','AD_VP',17000.00,NULL,100,90,'1992-04-03 00:00:00'),(103,'Alexander','Hunold','AHUNOLD','590.423.4567','IT_PROG',9000.00,NULL,102,60,'1992-04-03 00:00:00'),(104,'Bruce','Ernst','BERNST','590.423.4568','IT_PROG',6000.00,NULL,103,60,'1992-04-03 00:00:00'),(105,'David','Austin','DAUSTIN','590.423.4569','IT_PROG',4800.00,NULL,103,60,'1998-03-03 00:00:00'),(106,'Valli','Pataballa','VPATABAL','590.423.4560','IT_PROG',4800.00,NULL,103,60,'1998-03-03 00:00:00'),(107,'Diana','Lorentz','DLORENTZ','590.423.5567','IT_PROG',4200.00,NULL,103,60,'1998-03-03 00:00:00'),(108,'Nancy','Greenberg','NGREENBE','515.124.4569','FI_MGR',12000.00,NULL,101,100,'1998-03-03 00:00:00'),(109,'Daniel','Faviet','DFAVIET','515.124.4169','FI_ACCOUNT',9000.00,NULL,108,100,'1998-03-03 00:00:00'),(110,'John','Chen','JCHEN','515.124.4269','FI_ACCOUNT',8200.00,NULL,108,100,'2000-09-09 00:00:00'),(111,'Ismael','Sciarra','ISCIARRA','515.124.4369','FI_ACCOUNT',7700.00,NULL,108,100,'2000-09-09 00:00:00'),(112,'Jose Manuel','Urman','JMURMAN','515.124.4469','FI_ACCOUNT',7800.00,NULL,108,100,'2000-09-09 00:00:00'),(113,'Luis','Popp','LPOPP','515.124.4567','FI_ACCOUNT',6900.00,NULL,108,100,'2000-09-09 00:00:00'),(114,'Den','Raphaely','DRAPHEAL','515.127.4561','PU_MAN',11000.00,NULL,100,30,'2000-09-09 00:00:00'),(115,'Alexander','Khoo','AKHOO','515.127.4562','PU_CLERK',3100.00,NULL,114,30,'2000-09-09 00:00:00'),(116,'Shelli','Baida','SBAIDA','515.127.4563','PU_CLERK',2900.00,NULL,114,30,'2000-09-09 00:00:00'),(117,'Sigal','Tobias','STOBIAS','515.127.4564','PU_CLERK',2800.00,NULL,114,30,'2000-09-09 00:00:00'),(118,'Guy','Himuro','GHIMURO','515.127.4565','PU_CLERK',2600.00,NULL,114,30,'2000-09-09 00:00:00'),(119,'Karen','Colmenares','KCOLMENA','515.127.4566','PU_CLERK',2500.00,NULL,114,30,'2000-09-09 00:00:00'),(120,'Matthew','Weiss','MWEISS','650.123.1234','ST_MAN',8000.00,NULL,100,50,'2004-02-06 00:00:00'),(121,'Adam','Fripp','AFRIPP','650.123.2234','ST_MAN',8200.00,NULL,100,50,'2004-02-06 00:00:00'),(122,'Payam','Kaufling','PKAUFLIN','650.123.3234','ST_MAN',7900.00,NULL,100,50,'2004-02-06 00:00:00'),(123,'Shanta','Vollman','SVOLLMAN','650.123.4234','ST_MAN',6500.00,NULL,100,50,'2004-02-06 00:00:00'),(124,'Kevin','Mourgos','KMOURGOS','650.123.5234','ST_MAN',5800.00,NULL,100,50,'2004-02-06 00:00:00'),(125,'Julia','Nayer','JNAYER','650.124.1214','ST_CLERK',3200.00,NULL,120,50,'2004-02-06 00:00:00'),(126,'Irene','Mikkilineni','IMIKKILI','650.124.1224','ST_CLERK',2700.00,NULL,120,50,'2004-02-06 00:00:00'),(127,'James','Landry','JLANDRY','650.124.1334','ST_CLERK',2400.00,NULL,120,50,'2004-02-06 00:00:00'),(128,'Steven','Markle','SMARKLE','650.124.1434','ST_CLERK',2200.00,NULL,120,50,'2004-02-06 00:00:00'),(129,'Laura','Bissot','LBISSOT','650.124.5234','ST_CLERK',3300.00,NULL,121,50,'2004-02-06 00:00:00'),(130,'Mozhe','Atkinson','MATKINSO','650.124.6234','ST_CLERK',2800.00,NULL,121,50,'2004-02-06 00:00:00'),(131,'James','Marlow','JAMRLOW','650.124.7234','ST_CLERK',2500.00,NULL,121,50,'2004-02-06 00:00:00'),(132,'TJ','Olson','TJOLSON','650.124.8234','ST_CLERK',2100.00,NULL,121,50,'2004-02-06 00:00:00'),(133,'Jason','Mallin','JMALLIN','650.127.1934','ST_CLERK',3300.00,NULL,122,50,'2004-02-06 00:00:00'),(134,'Michael','Rogers','MROGERS','650.127.1834','ST_CLERK',2900.00,NULL,122,50,'2002-12-23 00:00:00'),(135,'Ki','Gee','KGEE','650.127.1734','ST_CLERK',2400.00,NULL,122,50,'2002-12-23 00:00:00'),(136,'Hazel','Philtanker','HPHILTAN','650.127.1634','ST_CLERK',2200.00,NULL,122,50,'2002-12-23 00:00:00'),(137,'Renske','Ladwig','RLADWIG','650.121.1234','ST_CLERK',3600.00,NULL,123,50,'2002-12-23 00:00:00'),(138,'Stephen','Stiles','SSTILES','650.121.2034','ST_CLERK',3200.00,NULL,123,50,'2002-12-23 00:00:00'),(139,'John','Seo','JSEO','650.121.2019','ST_CLERK',2700.00,NULL,123,50,'2002-12-23 00:00:00'),(140,'Joshua','Patel','JPATEL','650.121.1834','ST_CLERK',2500.00,NULL,123,50,'2002-12-23 00:00:00'),(141,'Trenna','Rajs','TRAJS','650.121.8009','ST_CLERK',3500.00,NULL,124,50,'2002-12-23 00:00:00'),(142,'Curtis','Davies','CDAVIES','650.121.2994','ST_CLERK',3100.00,NULL,124,50,'2002-12-23 00:00:00'),(143,'Randall','Matos','RMATOS','650.121.2874','ST_CLERK',2600.00,NULL,124,50,'2002-12-23 00:00:00'),(144,'Peter','Vargas','PVARGAS','650.121.2004','ST_CLERK',2500.00,NULL,124,50,'2002-12-23 00:00:00'),(145,'John','Russell','JRUSSEL','011.44.1344.429268','SA_MAN',14000.00,0.40,100,80,'2002-12-23 00:00:00'),(146,'Karen','Partners','KPARTNER','011.44.1344.467268','SA_MAN',13500.00,0.30,100,80,'2002-12-23 00:00:00'),(147,'Alberto','Errazuriz','AERRAZUR','011.44.1344.429278','SA_MAN',12000.00,0.30,100,80,'2002-12-23 00:00:00'),(148,'Gerald','Cambrault','GCAMBRAU','011.44.1344.619268','SA_MAN',11000.00,0.30,100,80,'2002-12-23 00:00:00'),(149,'Eleni','Zlotkey','EZLOTKEY','011.44.1344.429018','SA_MAN',10500.00,0.20,100,80,'2002-12-23 00:00:00'),(150,'Peter','Tucker','PTUCKER','011.44.1344.129268','SA_REP',10000.00,0.30,145,80,'2014-03-05 00:00:00'),(151,'David','Bernstein','DBERNSTE','011.44.1344.345268','SA_REP',9500.00,0.25,145,80,'2014-03-05 00:00:00'),(152,'Peter','Hall','PHALL','011.44.1344.478968','SA_REP',9000.00,0.25,145,80,'2014-03-05 00:00:00'),(153,'Christopher','Olsen','COLSEN','011.44.1344.498718','SA_REP',8000.00,0.20,145,80,'2014-03-05 00:00:00'),(154,'Nanette','Cambrault','NCAMBRAU','011.44.1344.987668','SA_REP',7500.00,0.20,145,80,'2014-03-05 00:00:00'),(155,'Oliver','Tuvault','OTUVAULT','011.44.1344.486508','SA_REP',7000.00,0.15,145,80,'2014-03-05 00:00:00'),(156,'Janette','K_ing','JKING','011.44.1345.429268','SA_REP',10000.00,0.35,146,80,'2014-03-05 00:00:00'),(157,'Patrick','Sully','PSULLY','011.44.1345.929268','SA_REP',9500.00,0.35,146,80,'2014-03-05 00:00:00'),(158,'Allan','McEwen','AMCEWEN','011.44.1345.829268','SA_REP',9000.00,0.35,146,80,'2014-03-05 00:00:00'),(159,'Lindsey','Smith','LSMITH','011.44.1345.729268','SA_REP',8000.00,0.30,146,80,'2014-03-05 00:00:00'),(160,'Louise','Doran','LDORAN','011.44.1345.629268','SA_REP',7500.00,0.30,146,80,'2014-03-05 00:00:00'),(161,'Sarath','Sewall','SSEWALL','011.44.1345.529268','SA_REP',7000.00,0.25,146,80,'2014-03-05 00:00:00'),(162,'Clara','Vishney','CVISHNEY','011.44.1346.129268','SA_REP',10500.00,0.25,147,80,'2014-03-05 00:00:00'),(163,'Danielle','Greene','DGREENE','011.44.1346.229268','SA_REP',9500.00,0.15,147,80,'2014-03-05 00:00:00'),(164,'Mattea','Marvins','MMARVINS','011.44.1346.329268','SA_REP',7200.00,0.10,147,80,'2014-03-05 00:00:00'),(165,'David','Lee','DLEE','011.44.1346.529268','SA_REP',6800.00,0.10,147,80,'2014-03-05 00:00:00'),(166,'Sundar','Ande','SANDE','011.44.1346.629268','SA_REP',6400.00,0.10,147,80,'2014-03-05 00:00:00'),(167,'Amit','Banda','ABANDA','011.44.1346.729268','SA_REP',6200.00,0.10,147,80,'2014-03-05 00:00:00'),(168,'Lisa','Ozer','LOZER','011.44.1343.929268','SA_REP',11500.00,0.25,148,80,'2014-03-05 00:00:00'),(169,'Harrison','Bloom','HBLOOM','011.44.1343.829268','SA_REP',10000.00,0.20,148,80,'2014-03-05 00:00:00'),(170,'Tayler','Fox','TFOX','011.44.1343.729268','SA_REP',9600.00,0.20,148,80,'2014-03-05 00:00:00'),(171,'William','Smith','WSMITH','011.44.1343.629268','SA_REP',7400.00,0.15,148,80,'2014-03-05 00:00:00'),(172,'Elizabeth','Bates','EBATES','011.44.1343.529268','SA_REP',7300.00,0.15,148,80,'2014-03-05 00:00:00'),(173,'Sundita','Kumar','SKUMAR','011.44.1343.329268','SA_REP',6100.00,0.10,148,80,'2014-03-05 00:00:00'),(174,'Ellen','Abel','EABEL','011.44.1644.429267','SA_REP',11000.00,0.30,149,80,'2014-03-05 00:00:00'),(175,'Alyssa','Hutton','AHUTTON','011.44.1644.429266','SA_REP',8800.00,0.25,149,80,'2014-03-05 00:00:00'),(176,'Jonathon','Taylor','JTAYLOR','011.44.1644.429265','SA_REP',8600.00,0.20,149,80,'2014-03-05 00:00:00'),(177,'Jack','Livingston','JLIVINGS','011.44.1644.429264','SA_REP',8400.00,0.20,149,80,'2014-03-05 00:00:00'),(178,'Kimberely','Grant','KGRANT','011.44.1644.429263','SA_REP',7000.00,0.15,149,NULL,'2014-03-05 00:00:00'),(179,'Charles','Johnson','CJOHNSON','011.44.1644.429262','SA_REP',6200.00,0.10,149,80,'2014-03-05 00:00:00'),(180,'Winston','Taylor','WTAYLOR','650.507.9876','SH_CLERK',3200.00,NULL,120,50,'2014-03-05 00:00:00'),(181,'Jean','Fleaur','JFLEAUR','650.507.9877','SH_CLERK',3100.00,NULL,120,50,'2014-03-05 00:00:00'),(182,'Martha','Sullivan','MSULLIVA','650.507.9878','SH_CLERK',2500.00,NULL,120,50,'2014-03-05 00:00:00'),(183,'Girard','Geoni','GGEONI','650.507.9879','SH_CLERK',2800.00,NULL,120,50,'2014-03-05 00:00:00'),(184,'Nandita','Sarchand','NSARCHAN','650.509.1876','SH_CLERK',4200.00,NULL,121,50,'2014-03-05 00:00:00'),(185,'Alexis','Bull','ABULL','650.509.2876','SH_CLERK',4100.00,NULL,121,50,'2014-03-05 00:00:00'),(186,'Julia','Dellinger','JDELLING','650.509.3876','SH_CLERK',3400.00,NULL,121,50,'2014-03-05 00:00:00'),(187,'Anthony','Cabrio','ACABRIO','650.509.4876','SH_CLERK',3000.00,NULL,121,50,'2014-03-05 00:00:00'),(188,'Kelly','Chung','KCHUNG','650.505.1876','SH_CLERK',3800.00,NULL,122,50,'2014-03-05 00:00:00'),(189,'Jennifer','Dilly','JDILLY','650.505.2876','SH_CLERK',3600.00,NULL,122,50,'2014-03-05 00:00:00'),(190,'Timothy','Gates','TGATES','650.505.3876','SH_CLERK',2900.00,NULL,122,50,'2014-03-05 00:00:00'),(191,'Randall','Perkins','RPERKINS','650.505.4876','SH_CLERK',2500.00,NULL,122,50,'2014-03-05 00:00:00'),(192,'Sarah','Bell','SBELL','650.501.1876','SH_CLERK',4000.00,NULL,123,50,'2014-03-05 00:00:00'),(193,'Britney','Everett','BEVERETT','650.501.2876','SH_CLERK',3900.00,NULL,123,50,'2014-03-05 00:00:00'),(194,'Samuel','McCain','SMCCAIN','650.501.3876','SH_CLERK',3200.00,NULL,123,50,'2014-03-05 00:00:00'),(195,'Vance','Jones','VJONES','650.501.4876','SH_CLERK',2800.00,NULL,123,50,'2014-03-05 00:00:00'),(196,'Alana','Walsh','AWALSH','650.507.9811','SH_CLERK',3100.00,NULL,124,50,'2014-03-05 00:00:00'),(197,'Kevin','Feeney','KFEENEY','650.507.9822','SH_CLERK',3000.00,NULL,124,50,'2014-03-05 00:00:00'),(198,'Donald','OConnell','DOCONNEL','650.507.9833','SH_CLERK',2600.00,NULL,124,50,'2014-03-05 00:00:00'),(199,'Douglas','Grant','DGRANT','650.507.9844','SH_CLERK',2600.00,NULL,124,50,'2014-03-05 00:00:00'),(200,'Jennifer','Whalen','JWHALEN','515.123.4444','AD_ASST',4400.00,NULL,101,10,'2016-03-03 00:00:00'),(201,'Michael','Hartstein','MHARTSTE','515.123.5555','MK_MAN',13000.00,NULL,100,20,'2016-03-03 00:00:00'),(202,'Pat','Fay','PFAY','603.123.6666','MK_REP',6000.00,NULL,201,20,'2016-03-03 00:00:00'),(203,'Susan','Mavris','SMAVRIS','515.123.7777','HR_REP',6500.00,NULL,101,40,'2016-03-03 00:00:00'),(204,'Hermann','Baer','HBAER','515.123.8888','PR_REP',10000.00,NULL,101,70,'2016-03-03 00:00:00'),(205,'Shelley','Higgins','SHIGGINS','515.123.8080','AC_MGR',12000.00,NULL,101,110,'2016-03-03 00:00:00'),(206,'William','Gietz','WGIETZ','515.123.8181','AC_ACCOUNT',8300.00,NULL,205,110,'2016-03-03 00:00:00');
/*Table structure for table `jobs` */
DROP TABLE IF EXISTS `jobs`;
CREATE TABLE `jobs` (
`job_id` varchar(10) NOT NULL,
`job_title` varchar(35) DEFAULT NULL,
`min_salary` int(6) DEFAULT NULL,
`max_salary` int(6) DEFAULT NULL,
PRIMARY KEY (`job_id`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;
/*Data for the table `jobs` */
insert into `jobs`(`job_id`,`job_title`,`min_salary`,`max_salary`) values ('AC_ACCOUNT','Public Accountant',4200,9000),('AC_MGR','Accounting Manager',8200,16000),('AD_ASST','Administration Assistant',3000,6000),('AD_PRES','President',20000,40000),('AD_VP','Administration Vice President',15000,30000),('FI_ACCOUNT','Accountant',4200,9000),('FI_MGR','Finance Manager',8200,16000),('HR_REP','Human Resources Representative',4000,9000),('IT_PROG','Programmer',4000,10000),('MK_MAN','Marketing Manager',9000,15000),('MK_REP','Marketing Representative',4000,9000),('PR_REP','Public Relations Representative',4500,10500),('PU_CLERK','Purchasing Clerk',2500,5500),('PU_MAN','Purchasing Manager',8000,15000),('SA_MAN','Sales Manager',10000,20000),('SA_REP','Sales Representative',6000,12000),('SH_CLERK','Shipping Clerk',2500,5500),('ST_CLERK','Stock Clerk',2000,5000),('ST_MAN','Stock Manager',5500,8500);
/*Table structure for table `locations` */
DROP TABLE IF EXISTS `locations`;
CREATE TABLE `locations` (
`location_id` int(11) NOT NULL AUTO_INCREMENT,
`street_address` varchar(40) DEFAULT NULL,
`postal_code` varchar(12) DEFAULT NULL,
`city` varchar(30) DEFAULT NULL,
`state_province` varchar(25) DEFAULT NULL,
`country_id` varchar(2) DEFAULT NULL,
PRIMARY KEY (`location_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3201 DEFAULT CHARSET=gb2312;
/*Data for the table `locations` */
insert into `locations`(`location_id`,`street_address`,`postal_code`,`city`,`state_province`,`country_id`) values (1000,'1297 Via Cola di Rie','00989','Roma',NULL,'IT'),(1100,'93091 Calle della Testa','10934','Venice',NULL,'IT'),(1200,'2017 Shinjuku-ku','1689','Tokyo','Tokyo Prefecture','JP'),(1300,'9450 Kamiya-cho','6823','Hiroshima',NULL,'JP'),(1400,'2014 Jabberwocky Rd','26192','Southlake','Texas','US'),(1500,'2011 Interiors Blvd','99236','South San Francisco','California','US'),(1600,'2007 Zagora St','50090','South Brunswick','New Jersey','US'),(1700,'2004 Charade Rd','98199','Seattle','Washington','US'),(1800,'147 Spadina Ave','M5V 2L7','Toronto','Ontario','CA'),(1900,'6092 Boxwood St','YSW 9T2','Whitehorse','Yukon','CA'),(2000,'40-5-12 Laogianggen','190518','Beijing',NULL,'CN'),(2100,'1298 Vileparle (E)','490231','Bombay','Maharashtra','IN'),(2200,'12-98 Victoria Street','2901','Sydney','New South Wales','AU'),(2300,'198 Clementi North','540198','Singapore',NULL,'SG'),(2400,'8204 Arthur St',NULL,'London',NULL,'UK'),(2500,'Magdalen Centre, The Oxford Science Park','OX9 9ZB','Oxford','Oxford','UK'),(2600,'9702 Chester Road','09629850293','Stretford','Manchester','UK'),(2700,'Schwanthalerstr. 7031','80925','Munich','Bavaria','DE'),(2800,'Rua Frei Caneca 1360 ','01307-002','Sao Paulo','Sao Paulo','BR'),(2900,'20 Rue des Corps-Saints','1730','Geneva','Geneve','CH'),(3000,'Murtenstrasse 921','3095','Bern','BE','CH'),(3100,'Pieter Breughelstraat 837','3029SK','Utrecht','Utrecht','NL'),(3200,'Mariano Escobedo 9991','11932','Mexico City','Distrito Federal,','MX');
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
CREATE TABLE job_grades
(grade_level VARCHAR(3),
lowest_sal int,
highest_sal int);
INSERT INTO job_grades
VALUES ('A', 1000, 2999);
INSERT INTO job_grades
VALUES ('B', 3000, 5999);
INSERT INTO job_grades
VALUES('C', 6000, 9999);
INSERT INTO job_grades
VALUES('D', 10000, 14999);
INSERT INTO job_grades
VALUES('E', 15000, 24999);
INSERT INTO job_grades
VALUES('F', 25000, 40000);
/*
SQLyog Ultimate v10.00 Beta1
MySQL - 5.7.18-log : Database - girls
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;
/*!40101 SET SQL_MODE=''*/;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`girls` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `girls`;
/*Table structure for table `admin` */
DROP TABLE IF EXISTS `admin`;
CREATE TABLE `admin` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(10) NOT NULL,
`password` varchar(10) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
/*Data for the table `admin` */
insert into `admin`(`id`,`username`,`password`) values (1,'john','8888'),(2,'lyt','6666');
/*Table structure for table `beauty` */
DROP TABLE IF EXISTS `beauty`;
CREATE TABLE `beauty` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`sex` char(1) DEFAULT '女',
`borndate` datetime DEFAULT '1987-01-01 00:00:00',
`phone` varchar(11) NOT NULL,
`photo` blob,
`boyfriend_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;
/*Data for the table `beauty` */
insert into `beauty`(`id`,`name`,`sex`,`borndate`,`phone`,`photo`,`boyfriend_id`) values (1,'柳岩','女','1988-02-03 00:00:00','18209876577',NULL,8),(2,'苍老师','女','1987-12-30 00:00:00','18219876577',NULL,9),(3,'Angelababy','女','1989-02-03 00:00:00','18209876567',NULL,3),(4,'热巴','女','1993-02-03 00:00:00','18209876579',NULL,2),(5,'周冬雨','女','1992-02-03 00:00:00','18209179577',NULL,9),(6,'周芷若','女','1988-02-03 00:00:00','18209876577',NULL,1),(7,'岳灵珊','女','1987-12-30 00:00:00','18219876577',NULL,9),(8,'小昭','女','1989-02-03 00:00:00','18209876567',NULL,1),(9,'双儿','女','1993-02-03 00:00:00','18209876579',NULL,9),(10,'王语嫣','女','1992-02-03 00:00:00','18209179577',NULL,4),(11,'夏雪','女','1993-02-03 00:00:00','18209876579',NULL,9),(12,'赵敏','女','1992-02-03 00:00:00','18209179577',NULL,1);
/*Table structure for table `boys` */
DROP TABLE IF EXISTS `boys`;
CREATE TABLE `boys` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`boyName` varchar(20) DEFAULT NULL,
`userCP` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
/*Data for the table `boys` */
insert into `boys`(`id`,`boyName`,`userCP`) values (1,'张无忌',100),(2,'鹿晗',800),(3,'黄晓明',50),(4,'段誉',300);
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;