建表語(yǔ)句
set names utf8mb4;
use f_algo_platform;
CREATE TABLE `result_table` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主鍵id',
`prec` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '準(zhǔn)確率',
`acc` DOUBLE NOT NULL DEFAULT 0 COMMENT '精確率'，
`model_path` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '模型存儲(chǔ)地址',
PRIMARY KEY (`id`),
UNIQUE KEY `uniq_task_id` (`task_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT = '算法結(jié)果表';
DQL查詢語(yǔ)句
show databases ;
show tables ;
use myemployees;
select * from employees;
select * from departments;
select * from locations;
select * from jobs;
show columns from employees;
desc employees;
#進(jìn)階1：基礎(chǔ)查詢
/*
語(yǔ)法:select 查詢列表 from 表名;
特點(diǎn):
1.可以查詢表中的字段，常量值，表達(dá)式，函數(shù)
2.查詢的結(jié)果是一個(gè)虛擬表格
*/
#1.查詢表中單個(gè)字段
select last_name from employees;
#2.查詢表中的多個(gè)字段
select last_name,email from employees;
select
employee_id,
first_name,
last_name,
phone_number
from employees;
#使用著重號(hào)
select
`employee_id`,
`first_name`,
`last_name`,
`phone_number`
from employees;
#3.查詢表中所有的字段
select * from employees;
#4.查詢常量值
select 100 from employees;
select 'john';
#5.查詢表達(dá)式
select 100%98;
#6.查詢函數(shù)
select version();
#7.為字段取別名
#方法一
select 100%98 as 結(jié)果;
select last_name as 姓 from employees;
#方法二
select last_name 姓 from employees;
#案例 查詢salary顯示結(jié)果為output
select salary as 'out put' from employees;
#8.去重
select department_id from employees;
select distinct department_id from employees;
#9.+號(hào)的作用-當(dāng)作運(yùn)算符
#案例：查詢員工的姓名鏈接成一個(gè)字段，合并顯示為姓名
select last_name+first_name '姓名'from employees; #沒有結(jié)果
select 100+90;
select '100'+88; #會(huì)將字符串轉(zhuǎn)換成為數(shù)值型，轉(zhuǎn)換成功就繼續(xù)做加法運(yùn)算，
select '哈哈'+98; #如果轉(zhuǎn)換失敗就將字符轉(zhuǎn)換為0,
select null+98; #如果有一個(gè)為null。則結(jié)果為null
#10.字符串的拼接
select concat(last_name,first_name) '姓名' from employees;
#11.ifnull函數(shù)
select ifnull(commission_pct,0) '獎(jiǎng)金率' from employees;
# 進(jìn)階二 條件查詢
/*
語(yǔ)法：
select
查詢條件
from
表名
where
篩選條件;
分類:
一:按條件表達(dá)式篩選
條件運(yùn)算符： <,>,=,!=(<>)又小又大，<=,>=
二，按邏輯表達(dá)式篩選
邏輯表達(dá)式：and,or,not
三，模糊查詢
like，between and,in,is null
*/
#1.按條件表達(dá)式進(jìn)行篩選
select * from employees where salary>12000;
select last_name,department_id from employees where department_id!=90;
select last_name,department_id from employees where department_id<>90;
#2.按邏輯表達(dá)式篩選，將多條件鏈接在一起
select last_name,salary,commission_pct from employees where salary>10000 and salary<20000;
select * from employees where department_id<90 or department_id>110 or salary>15000;
select * from employees where not(department_id>=90 and department_id<=110) or salary>15000;
#3.like模糊查詢
/*
一般和通配符一起搭配使用
通配符：
% 任意個(gè)字符，包含0個(gè)字符
_ 任意單個(gè)字符
*/
select * from employees where last_name like '%a%';
select last_name,salary from employees where last_name like '__n_l%';
select first_name,last_name,salary from employees where last_name like '_\_%'; #使用轉(zhuǎn)義字符
select first_name,last_name,salary from employees where last_name like '_a_%' escape 'a'; #將a指明為轉(zhuǎn)義字符
#4.between and
/*
可以提高語(yǔ)句的簡(jiǎn)潔度，
between and 是閉區(qū)間
between與and之間值不能顛倒順序
*/
select first_name,last_name,department_id from employees where department_id between 0 and 100;
#5.in
/*
用于判讀值是否為in中的值
特點(diǎn)：
使用in提高語(yǔ)句的簡(jiǎn)潔度
in類表的值類型必須一致或兼容
不支持通配符
*/
select * from employees where job_id in ('IT_PROG','AD_VP','AD_PRES');
#4.is null
/*
=或<,>不能夠判斷null
*/
select first_name,last_name,commission_pct from employees where commission_pct is null ;
select first_name,last_name,commission_pct from employees where commission_pct is not null ;
#5.安全等于
/*
<=> 可以用來判斷null，也可以用來判斷一般類型的數(shù)值
is null與<=>的區(qū)別
is null 只能判斷null
<=> 可以判斷null與一般值
= 只能判斷一般值
*/
select first_name,last_name,commission_pct from employees where commission_pct <=> null ;
select first_name,last_name,commission_pct from employees where commission_pct <=> 0.2 ;
# 進(jìn)階三,排序查詢
/*
select * from 表名 where 查詢條件 order by 排序列表(asc|desc)
特點(diǎn)：asc升序，desc降序
order by 子句中可以支持單個(gè)字段，多個(gè)字段，表達(dá)式，函數(shù)，別名
order by 字句放在查詢語(yǔ)句的最后面，只有l(wèi)imit放在order by后面
*/
select * from employees order by salary;
select * from employees order by salary desc;
select * from employees order by salary asc;
select * from employees where department_id >= 90 order by hiredate asc;
select salary*12*(1+ifnull(commission_pct,0)) 年薪 from employees order by 年薪 desc;
select last_name,salary from employees order by length(last_name) desc,salary;
select last_name,salary,employee_id from employees order by salary,employee_id desc;
select last_name,department_id,salary*12*(1+(ifnull(commission_pct,0))) 年薪 from employees order by 年薪 desc,last_name;
select last_name,salary from employees where not (salary between 8000 and 17000) order by salary desc ;
select * from employees where email like '%e%' order by length(email) desc,department_id asc;
#進(jìn)階四 常見函數(shù)
/*
功能：類似于java中的方法，將一組邏輯語(yǔ)句封裝在方法體中，對(duì)外暴露方法名
好處：
1.掩藏實(shí)現(xiàn)細(xì)節(jié)，
2.提高代碼的重用性
調(diào)用：
select 函數(shù)名(實(shí)參列表) from 表名;
特點(diǎn):
函數(shù)名
函數(shù)功能
分類：
單行函數(shù)：處理功能(字符函數(shù))
concat，length，ifnull
分組函數(shù)：統(tǒng)計(jì)使用，(統(tǒng)計(jì)函數(shù)，聚合函數(shù))
*/
#字符函數(shù)
#1.length()獲取參數(shù)值的字節(jié)數(shù)
select length('join');
select length('張三豐');
#2.concat()拼接字符串
select concat(last_name,'-',first_name) 姓名 from employees;
#3.upper,lower
select upper(last_name) from employees;
select lower(first_name) from employees;
select concat(upper(last_name),'_',lower(first_name)) from employees;
#4.substr,substring截取字符串
select substr('去哪兒網(wǎng)度假部門',5,2);
select substr(last_name,1,2) ,last_name from employees;
select concat(upper(substr(last_name,1,1)),lower(substr(last_name,2))) from employees;
#5.instr返回字串在字符串中的索引
select instr('去哪兒網(wǎng)','網(wǎng)') as output;
select instr('去哪兒網(wǎng)','xie') as output;
#6.trim 去除空格，只取出首尾的空格
select trim(' aa bb ') as output;
select trim('a' from 'aaaaaaaaaaaaaaa你好aaaaa') as output;
#7.lpad ，rpad 用指定的字符填充字符串至指定的長(zhǎng)度
select lpad('因叔叔',10,'*') as output;
# replace替換
select replace('張無忌愛上了周至若','周至若','趙敏') as output;
#數(shù)學(xué)函數(shù)
#1.round 四舍五入,取整數(shù)
select round(1.45);
select round(1.5678,2); #小數(shù)點(diǎn)后取兩位
#2.ceil 向上取整
select ceil(1.002);
#3,floor向下取整
select floor(1.999);
#truncate 截?cái)?select truncate(1.9999,1);
#mod取余
select mod(10,3);
select mod(10,-3);
#日期函數(shù)
#1.now()返回當(dāng)前的系統(tǒng)時(shí)間
select now();
#2.curdate()返回當(dāng)前系統(tǒng)的日期，不包括時(shí)間
select curdate();
#3,curtime()返回當(dāng)前系統(tǒng)的時(shí)間，不包括日期
select curtime();
#4,獲取指定的時(shí)，分，秒
select YEAR(2020-11-11);
#5,str_to_date
select str_to_date('9-13-1999','%m-%d-%Y');
select date_format('1999-6-12','%Y-%m-%d');
# 其他函數(shù)
select version();
select database();
select user();
#流程控制函數(shù)
#1.if函數(shù) s實(shí)現(xiàn)if else的效果
select if(10>5,'大','小');
select last_name,if(commission_pct is null,'you','wu') from employees;
#2.case函數(shù)的使用
#使用一
/*
switch (常量或表達(dá)式){
case 常量1:語(yǔ)句1;break;
case 常量2:語(yǔ)句2;break;
}
mysql中的case
case 要判斷的字段
when 常量1 then 要顯示的語(yǔ)句;
when 常量2 then 要顯示的語(yǔ)句;
....
else 要顯示的語(yǔ)句
end
*/
select salary 原始工資,department_id,
case department_id
when 50 then salary*2
when 60 then salary*1.2
when 70 then salary*1.3
else salary
end 新工資
from employees;
#使用二
/*
case
when 條件1 then 值1
when 條件2 then 值2
when 條件3 then 值3
else 要顯示的語(yǔ)句
end
*/
select salary,
case
when salary > 10000 then 'A'
end
from employees;
select now();
select employee_id,last_name,salary,salary*1.2 from employees;
select length(last_name),substr(last_name,1,1) 首字符,last_name from employees order by 首字符;
select concat(last_name,'earns',salary,'monthly but wants',salary*3 ) from employees;
select job_id,
case job_id
when 'AD_PRES' then 'A'
when 'AD_VP' then 'B'
when 'IT_PROG' then 'C'
end
from employees;
#分組函數(shù)
/*
主要用于統(tǒng)計(jì)，又稱為聚合函數(shù)或統(tǒng)計(jì)函數(shù)
分類
sum求和
avg平均值
max 最大值
min 最小值
count 計(jì)算個(gè)數(shù)
*/
#1,簡(jiǎn)單使用
select sum(salary) 工資總和 from employees;
select round(avg(salary)) 平均值,min(salary) 最小值,max(salary) 最大值 ,count(salary) 總數(shù) from employees;
#2,參數(shù)類型支持
/*
sum數(shù)值型，null不參與運(yùn)算
avg數(shù)值型，null不參與運(yùn)算
max數(shù)值型，字符型，null不參與運(yùn)算
min數(shù)值型，字符型，null不參與運(yùn)算
count 非null，null不計(jì)數(shù)
*/
select sum(last_name),avg(last_name) from employees;
select max(hiredate),min(hiredate)from employees;
#3,可以和distinct搭配使用，去重
select sum(distinct(salary)) from employees;
select count(distinct salary) from employees;
#4,count函數(shù)的詳細(xì)介紹
/*
innodb count(*)與count(1)效率差不多，比count(字段)要高一些
myisam count(*)效率最高，直接返回計(jì)數(shù)器
一般使用count(*)統(tǒng)計(jì)行數(shù)
*/
select count(salary) from employees;
select count(*) from employees;
select count(1) from employees; #相當(dāng)于在表中加了一列常量
#5,和分組函數(shù)一同查詢的字段有限制，同分組查詢的字段一般要求是group by之后的字段
select avg(salary),employee_id from employees; #錯(cuò)誤
select datediff(now(),'1995-09-15');
select count(*) from employees where department_id=90;
#進(jìn)階五，分組查詢
/*
語(yǔ)法
select 分組函數(shù)，列(要求出現(xiàn)在group by的后面)
from 表
where 篩選條件
group by 分組列表
order by 字句
注意：查詢列表比較特殊，要求是分組函數(shù)與group by后出現(xiàn)的字段
特點(diǎn)：
1,分組查詢中的篩選條件分為兩類
分組前篩選：原始表
分組后篩選：分組后的結(jié)果集
分組函數(shù)做條件，放在having函數(shù)中
*/
select department_id,avg(salary) from employees group by department_id;
select max(salary),job_id from employees group by job_id;
select count(*),location_id from departments group by location_id;
#添加篩選條件,分組前的篩選
select avg(salary) ,department_id from employees where email like '%a%' group by department_id;
select max(salary),manager_id from employees where commission_pct is not null group by manager_id;
#添加復(fù)雜的篩選條件
#select department_id,count(*) '員工數(shù)' from where '員工數(shù)'>2 employees group by department_id;
#根據(jù)已查詢的結(jié)果進(jìn)行再次查詢分組
/*
利用having在查詢的結(jié)果后面篩選
*/
select count(*) ,department_id from employees group by department_id having count(*)>2;
select job_id,max(salary) from employees where commission_pct is not null group by job_id having max(salary)>200;
select min(salary) 最低工資,manager_id from employees where manager_id>102 group by manager_id having 最低工資>5000 order by manager_id;
#gruopby 按表達(dá)式進(jìn)行分組
select count(*),length(last_name) from employees group by length(last_name) having count(*) >5 order by length(last_name);
#按多個(gè)字段進(jìn)行分組
select department_id,job_id from employees group by department_id, job_id order by department_id ;
select department_id,job_id,avg(salary) from employees group by department_id,job_id;
select max(salary)-min(salary) difference from employees;
select min(salary),manager_id from employees where manager_id is not null group by manager_id having min(salary)>6000;
select department_id,count(*),avg(salary)from employees group by department_id order by avg(salary);
show databases ;
use girls;
show tables;
select * from beauty;
select * from boys;
#進(jìn)階六 鏈接查詢
/*
多表鏈接，當(dāng)查詢的字段涉及到多個(gè)表
笛卡爾乘積現(xiàn)象：表一有m行，表二有n行，結(jié)果一共有mXn項(xiàng)
如何有效的避免：添加有效的鏈接條件
分類：
按年代分類
sql92標(biāo)準(zhǔn)
sql99標(biāo)準(zhǔn)
按功能進(jìn)行分類
內(nèi)鏈接
--等值鏈接
--非等值鏈接
--自鏈接
外鏈接
--左外鏈接
--右外鏈接
--全外鏈接
交叉鏈接
*/
#發(fā)生笛卡爾乘積現(xiàn)象
select name,boyName from beauty,boys;
select name ,boyName from beauty,boys where beauty.boyfriend_id=boys.id;
#1,等值鏈接
select boyName ,name from boys,beauty where beauty.boyfriend_id=boys.id;
use myemployees;
select * from departments;
#查詢員工名和對(duì)應(yīng)的部門名
select last_name ,department_name from employees e,departments d where e.department_id=d.department_id;
#查詢員工號(hào)，工種號(hào)，工種名
#注意，其別名之后不能夠再使用表明
select * from jobs;
select e.last_name,e.job_id,j.job_title from employees e,jobs j where e.job_id=j.job_id;
#可以加篩選
select last_name,department_name,commission_pct from employees e,departments d where e.department_id=d.department_id and e.commission_pct is not null;
#查詢城市名中第二個(gè)字符為o的部門名和城市名
select * from departments;
select d.department_name,l.city from departments d,locations l where d.location_id=l.location_id and l.city like '_o%';
#加分組
#查詢每一個(gè)城市的部門個(gè)數(shù)
select count(*) 部門數(shù),city from departments d,locations l where d.location_id=l.location_id group by l.city;
#查詢有獎(jiǎng)金的每個(gè)部門的部門名和部門的領(lǐng)導(dǎo)編號(hào)和該部門的最低工資
select
d.department_name,min(salary)
from departments d,employees e
where
d.department_id=e.department_id and e.commission_pct is not null
group by d.department_name;
#加排序
select job_title,count(*) from jobs j,employees e where j.job_id=e.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;
#2,非等值鏈接
select last_name,salary,grade_level from employees e,job_grades j where e.salary>j.lowest_sal and e.salary <j.highest_sal;
#自鏈接,自己鏈接自己
select e1.last_name,e2.last_name from employees e1,employees e2 where e1.manager_id=e2.employee_id;
#3,sql99語(yǔ)法
/*
內(nèi)鏈接 inner
外鏈接
--左外 left[outer]
--右外 right[outer]
--全外 full[outer]
交叉鏈接 cross
語(yǔ)法
select 查詢列表
from 表1 別名 鏈接類型
join 表2 別名 on 鏈接條件
where 篩選條件
*/
#1.內(nèi)鏈接
/*
select 查詢列表
from 表1 別名 inner
join 表2 別名
*/
select last_name,department_name from employees e inner join departments d on e.department_id=d.department_id;
select last_name,job_title from employees e inner join jobs j on e.job_id = j.job_id where last_name like '%e%';
select city,count(*) from departments d inner join locations l on d.location_id = l.location_id group by d.location_id having count(*)>3;
select department_name,count(*) 員工數(shù) from departments d inner join employees e on d.department_id = e.department_id group by e.department_id having count(*)>3 order by count(*)desc;
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 e.job_id = j.job_id order by department_name desc;
#非等值鏈接
select last_name,salary,grade_level from employees e inner join job_grades j on e.salary>j.lowest_sal and e.salary<j.highest_sal;
select last_name,salary,grade_level from employees e inner join job_grades j on e.salary between j.lowest_sal and j.highest_sal;
select e.last_name,e.employee_id,m.last_name,m.employee_id from employees e inner join employees m on e.manager_id=m.employee_id;
#2.外鏈接
use girls;
select * from beauty;
select be.*,bo.* from beauty be left outer join boys bo on be.boyfriend_id=bo.id;
/*
left join 左邊的是主表
right join 右邊是主表
*/
use myemployees;
select d.*,e.* from departments d left join employees e on d.department_id = e.department_id where employee_id is null ;
#全外鏈接
use girls;
#交叉鏈接=笛卡爾乘積
select b.*,bo.* from boys b cross join beauty bo;
select be.*,b.*from beauty be left join boys b on be.boyfriend_id=b.id where be.id>3;
use myemployees;
select city,d.* from departments d right join locations l on d.location_id = l.location_id where d.department_id is null;
select last_name,department_name from departments d left join employees e on d.department_id = e.department_id where d.department_name='SAL' or d.department_name='IT' and last_name != null;
#進(jìn)階七 子查詢
/*
含義：出現(xiàn)在其他語(yǔ)句中的select語(yǔ)句，稱為子查詢或內(nèi)查詢
外部的查詢語(yǔ)句，稱為主查詢或外查詢
分類：
--按子查詢出現(xiàn)的位置分類
-----select后面，標(biāo)量子查詢
-----from后面，表子查詢
-----where或having后面，標(biāo)量子查詢(單行)，列子查詢(多行)，行子查詢，
-----exist后面，表子查詢
--按照功能不同，結(jié)果集的行列數(shù)不同
-----標(biāo)量子查詢(結(jié)果集只有一行一列)
-----列子查詢(結(jié)果集只有一列多行)
-----行子查詢(結(jié)果集只有一行多列)
-----表子查詢(結(jié)果集有多行多列)
*/
#一，where,having后面
/*
特點(diǎn)：子查詢都會(huì)放在小括號(hào)內(nèi)
子查詢一般放在條件右側(cè)
標(biāo)量子查詢，一般搭配著單行操作符（>,<,+,>=,<=,<>）使用
列子查詢，一般搭配著多行操作符使用，in/any/some/all
子查詢優(yōu)先與主查詢，主查詢用到了子查詢的結(jié)果
*/
#1.標(biāo)量子查詢
select
last_name,salary
from
employees
where
salary>=(select salary from employees where last_name='Abel')
order by
last_name;
select
last_name,job_id,salary
from employees
where job_id=(
select job_id
from employees
where employee_id=141)
and salary> (
select salary
from employees
where employee_id=143
);
select last_name,job_id,salary
from employees
where salary = (
select min(salary)
from employees
);
select department_id,min(salary)
from employees
group by department_id
having min(salary)>(
select min(salary)
from employees
where department_id=50
);
#2.列子查詢(多行子查詢)
/*
返回多行
使用多行比較符
IN/NOT IN 等于列表中的任意一個(gè)
ANY/SOME 和子查詢返回的某一個(gè)值比較
ALL 和子查詢返回的所有值比較
*/
select last_name,department_id from employees where department_id in (select distinct department_id from departments where location_id in (1400,1700));
select last_name,employee_id,job_id,salary from employees where salary <any(select distinct salary from employees where job_id='IT_PROG') and job_id!='IT_PROG';
#3.行子查詢
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) 個(gè)數(shù) from departments d;
select (select department_name from departments d where d.department_id=e.department_id)from employees e where employee_id=102;
#三，放在from后面
select j.grade_level,avgsalary.* from (select avg(salary) 平均工資,department_id from employees group by department_id) avgsalary,job_grades j where avgsalary.平均工資>j.lowest_sal and avgsalary.平均工資<j.highest_sal;
#四，放在exist后面
#，exist的使用,判斷查詢是否有值
select exists(select employee_id from employees);
select department_name from departments d where exists(select * from employees e where e.department_id=d.department_id);
# 進(jìn)階八，分頁(yè)查詢
/*
應(yīng)用場(chǎng)景：當(dāng)要顯示的數(shù)據(jù)，一頁(yè)現(xiàn)實(shí)不全，需要分頁(yè)提交sql請(qǐng)求
語(yǔ)法：select 查詢列表
from 表
join type join 表2
on
where
group by
having
order by
limit offset，size;
offset:要顯示的條目的起始索引
size：要顯示的條目個(gè)數(shù)
特點(diǎn)：用在查詢語(yǔ)句的最后
公式：要顯示的頁(yè)數(shù)page，每條的條目數(shù)size
page :（page-1）* size
size : size
*/
select * from employees limit 0,5;
select * from employees limit 10,15;
select * from employees where commission_pct is not null order by salary desc limit 10;
#進(jìn)階九，聯(lián)合查詢union聯(lián)合查詢
/*
union聯(lián)合，將多條語(yǔ)句的查詢語(yǔ)句合并成一個(gè)結(jié)果
語(yǔ)法：查詢語(yǔ)句1 union 查詢語(yǔ)句2;
使用場(chǎng)景：
注意事項(xiàng)：多表中的結(jié)果列數(shù)要相同
查詢的列類型要對(duì)應(yīng)一致
會(huì)將結(jié)果自動(dòng)的去重
關(guān)閉去重 union all
*/
#查詢部門編號(hào)>90或者郵箱包含a的員工的信息
select * from employees where email like '%a%'
union
select * from employees where department_id>90;
DML語(yǔ)言
/*
數(shù)據(jù)的操作語(yǔ)言
插入：
修改：
刪除：
*/
#一，插入語(yǔ)句
/*
語(yǔ)法：
insert into 表名 (列名,...) values (值);
特點(diǎn)：插入的值的類型要與列的類型一致或兼容
*/
#1.插入beauty
use girls;
insert into beauty (id,name,sex,borndate,phone,photo,boyfriend_id) values (13,'唐藝昕','女','1990-4-23','18988888888',null,2);
select * from beauty;
delete from beauty where id=16;
#2.可以為null的列是如何插入值的，不可以為null的列必須有值
#方式二，可以為空的列，可以不用寫
insert into beauty (id,name,sex,borndate,phone,boyfriend_id) values (14,'金星','女','1990-4-23','13822222222',9);
#3.列的順序是否可以顛倒
insert into beauty (name,sex,id,phone) values ('將昕','女',16,'1345678903');
#4.列數(shù)與值的個(gè)數(shù)必須一致
#5.可以省略列名，默認(rèn)所有列，而且列的順序與表中列的順序一致
insert into beauty values (18,'張飛','男',null,'12345678901',null,null);
#方式二
/*
insert into 表名 set 列名=值，列名=值,...
*/
insert into beauty set id=19,name='劉濤',phone='34567654839';
/*
兩種方式的區(qū)別
1.方式一，支持一次插入多行,方式二不支持
2.方式一支持子查詢，方式二不支持
*/
insert into beauty (id,name,sex,borndate,phone,photo,boyfriend_id)
values (20,'唐藝昕','女','1990-4-23','18988888888',null,2),
(21,'唐藝昕','女','1990-4-23','18988888888',null,2),
(22,'唐藝昕','女','1990-4-23','18988888888',null,2),
(23,'唐藝昕','女','1990-4-23','18988888888',null,2);
insert into beauty (id,name,phone) select 26,'宋茜','12345678901';
#二。修改語(yǔ)句
/*
語(yǔ)法：
1.修改單表中的記錄
2.修改多表中的記錄
語(yǔ)法
update 表明 set 列=新值，列=新值 where 篩選條件
*/
#1.修改單表的記錄
update beauty set phone='13899888899' where name like '唐%';
select * from beauty;
#2.修改boys
update boys set boyName='張飛',userCP=20 where id=2;
#2.修改多表的記錄
/*
sql92語(yǔ)法：
update 表1,別名，表2 別名
set 列=值
where 鏈接條件
and 篩選條件
sql99
update 表1 別名
inner、left、right join 表1 別名
on 條件
set 列=值
where 篩選條件
*/
#2.修改多表的記錄
update boys b
inner join beauty be
on b.id=be.boyfriend_id
set be.phone=11111111111
where b.boyName='張無忌';
select * from beauty;
update beauty be left join boys b on be.boyfriend_id=b.id set be.boyfriend_id=2 where b.id is null;
#刪除語(yǔ)句
/*
語(yǔ)法：
方式一
delete from 表名 where 篩選條件
1,實(shí)現(xiàn)單表的刪除
2,實(shí)現(xiàn)多表的刪除
方式二
truancate
truncate table 表名
*/
#方式一
#單表的刪除
delete from beauty where phone like '%9';
select * from beauty;
#多表的刪除
delete be from beauty be inner join boys b on be.boyfriend_id=b.id where b.boyName='張無忌';
delete be,b from beauty be inner join boys b on be.boyfriend_id=b.id where be.name='Angelababy';
#方式二 truncate清空數(shù)據(jù)
truncate table boys;
#delete 與truncate的區(qū)別
/*
delete 可以加where條件，truncate不能加
truncate刪除，效率更高
如果刪除表中的數(shù)據(jù)有自增長(zhǎng)列，如果使用delete刪除，新插入的數(shù)據(jù)，從斷點(diǎn)處進(jìn)行增長(zhǎng)，如果使用truncate則從1開始
truncate刪除沒有返回值，delete刪除有返回值
truncate刪除不能回滾，delete刪除可以回滾
*/
use myemployees;
CREATE TABLE my_employees(
Id INT(10),
First_name VARCHAR(10),
Last_name VARCHAR(10),
Userid VARCHAR(10),
Salary DOUBLE(10,2)
);
CREATE TABLE users(
id INT,
userid VARCHAR(10),
department_id INT
);
#2. 顯示表my_employees的結(jié)構(gòu)
DESC my_employees;
#方式一：
INSERT INTO my_employees
VALUES(1,'patel','Ralph','Rpatel',895),
(2,'Dancs','Betty','Bdancs',860),
(3,'Biri','Ben','Bbiri',1100),
(4,'Newman','Chad','Cnewman',750),
(5,'Ropeburn','Audrey','Aropebur',1550);
INSERT INTO users
VALUES(4,'Rpatel',10),
(5,'Bdancs',10);
update my_employees set Last_name='drelxer' where id=3;
update my_employees set Salary =1000 where Salary<900;
delete my,u from my_employees my inner join users u on my.Userid = u.userid where u.userid='Bbiri';
select * from my_employees;
DDL語(yǔ)言
/*
庫(kù)的管理
創(chuàng)建
修改
刪除
表的管理
創(chuàng)建 create
修改 alter
刪除 drop
*/
#一，庫(kù)的管理
/*
1.庫(kù)的創(chuàng)建
語(yǔ)法
create database 庫(kù)名;
*/
#if not exists
create database if not exists books;
show databases ;
#庫(kù)的修改
/*
修改庫(kù)名，可能有數(shù)據(jù)的丟失
可以修改庫(kù)的字符集
alter database books charachter set gbk;
*/
alter database books character set gbk;
#庫(kù)的刪除
drop database if exists books;
#表的管理
/*
表的創(chuàng)建
*/
#1.表的創(chuàng)建
/*
語(yǔ)法
create table 表名(
列名 列的類型(長(zhǎng)度) 約束，
列名 列的類型(長(zhǎng)度) 約束，
列名 列的類型(長(zhǎng)度) 約束，
...
列名 列的類型(長(zhǎng)度) 約束，
)
*/
create database if not exists books;
create table book(
id int, #編號(hào)
bName varchar(20), #s書名
price double,
authorId int,
publishDate datetime #出版日期
);
desc book;
create table author(
id int,
authorName varchar(20),
nation varchar(10)
);
desc author;
#2.表的修改
/*
修改列名
修改列的類型與約束
添加列
刪除列
修改列名
alter table 表名 drop|add|modify|change column 列名 類型 約束;
*/
#修改列名
alter table book change column publishDate pubDate DATETIME;
desc book;
#修改列的類型
alter table book modify column pubDate timestamp;
desc book;
#添加新列
alter table author add column annual double;
#刪除列
alter table author drop column annual;
#修改表名
alter table author rename to bookAuthor;
alter table bookAuthor rename to author;
#3表的刪除
drop table if exists book;
show tables ;
#通用的寫法
drop database if exists 舊庫(kù)名;
drop table if exists 舊表名;
#表的復(fù)制
insert into author
(id,authorName,nation)
values
(1,'村上春樹','日本'),
(2,'莫言','中國(guó)'),
(3,'風(fēng)談','中國(guó)'),
(4,'金庸','中國(guó)');
#1.僅僅復(fù)制表的結(jié)構(gòu)
create table cpoyAuthor like author;
select * from copyAuthor2;
#2,復(fù)制表的結(jié)構(gòu)家數(shù)據(jù)
create table copyAuthor2 select * from author;
#3.只復(fù)制部分?jǐn)?shù)據(jù)
create table capyAuthor3 select id,authorName from author where nation='中國(guó)';
#4.僅僅復(fù)制某些字段
create table copyAuthor4 select id,authorName from author where 1=2;
create database if not exists test;
use test;
create table dept1(
id int(7),
name varchar(20)
);
#2,將表departments中的數(shù)據(jù)插入的dept1中
create table dept2 select department_id,department_name from myemployees.departments;
create table emp5(
id int(7),
first_name varchar(20),
last_name varchar(20),
dep_id int(7)
);
alter table emp5 modify column last_name varchar(50);
create table employees2 like myemployees.my_employees;
drop table if exists emp5;
alter table employees2 rename to emp5;
alter table emp5 add column test_column varchar(20);
alter table test.emp5 drop column test_column;
select * from emp5;
/*
數(shù)據(jù)表的類型
數(shù)值型：
--整型
Tinyint 1字節(jié) -128～127 0～255 所保存的范圍不同
Smallint 2字節(jié) -32768～32767 0～65503
mediumint 3字節(jié)
int ，integer 4字節(jié)
bigint 8字節(jié)
--浮點(diǎn)型
-------定點(diǎn)數(shù)
-------浮點(diǎn)數(shù)
--字符型
-------較短的文本 char varchar
-------較長(zhǎng)的文本 text blob
--日期
*/
#1。整型，如何設(shè)置無符號(hào)與有符號(hào) unsigned
/*
特點(diǎn);整型默認(rèn)為有符號(hào)，使用無符號(hào)需要加上unsigned
超出范圍，不會(huì)插入
如果不設(shè)置長(zhǎng)度，或有默認(rèn)的長(zhǎng)度,默認(rèn)長(zhǎng)度只影響顯示效果
如果要使用0填充，需要使用zerofill
*/
create table tabInt(
t1 int(9) zerofill,
t2 int(9) unsigned
);
select * from tabInt ;
insert into tabInt (t1) values (12);
insert into tabInt (t1) values (-12);
insert into tabInt (t2) values (-1);
insert into tabInt (t2) values (1);
drop table if exists tabInt;
insert into tabInt values (3563333333,333333567);
#3.小數(shù)
/*
浮點(diǎn)型
float(m,d) 4字節(jié)
double(m,d) 8字節(jié)
定點(diǎn)型 字節(jié)
decimal(m,d)
dec(m,d)
特點(diǎn)：
m，d可以省略，decimal默認(rèn)(10,0)
m,總的位數(shù) d，小數(shù)點(diǎn)后的位數(shù)
整數(shù)部分的長(zhǎng)度=m-d
m，超過總的位數(shù)會(huì)報(bào)錯(cuò)，d超過小數(shù)會(huì)截取
定點(diǎn)型與浮點(diǎn)型的精度不同，如果需要的精度較高，使用定點(diǎn)型
float的占用空間較小，優(yōu)先于double
越簡(jiǎn)單越好，越小越好
*/
create table tab_float(
f1 float(5,2),
f2 double(5,2),
f3 decimal(5,2)
);
select * from tab_float;
insert into tab_float values(123.45,123.56,123.67);
insert into tab_float values(1234.456,123.56,123.67);
insert into tab_float values(123.456,123.567,123.678);
insert into tab_float values(123.4,123.56,123.67);
#三，字符型
/*
較短的文本char，varchar
每個(gè)字符占3個(gè)字節(jié)
較長(zhǎng)的文本text blob(較大的二進(jìn)制)
cahr(M)
varchar(M)
m保存最多的字符數(shù)
char與varchar的區(qū)別，
char固定長(zhǎng)度的字符，varchar不固定的長(zhǎng)度
char默認(rèn)為1,varchar中的m不可以省略
*/
/*
enum型
*/
create table tab_char(
c1 enum('a','b','c','d')
);
/*
大寫會(huì)自動(dòng)轉(zhuǎn)小寫
*/
select * from tab_char;
insert into tab_char (c1) values ('a');
insert into tab_char (c1) values ('e');
insert into tab_char (c1) values ('A');
/*
set 可以選擇多個(gè)插入
超出范圍會(huì)報(bào)錯(cuò)
*/
create table tab_set(
c1 set('a','b','c')
);
insert into tab_set (c1) values ('a');
insert into tab_set (c1) values ('a,b');
insert into tab_set (c1) values ('a,e');
/*
日期類型 最小值
date 4字節(jié) 1000-01-01
datetime 8字節(jié) 1000-01-01 00：00：00
timestamp 4字節(jié)
time 3字節(jié)
year 1字節(jié) 1901-2155
datetime與timestamp的區(qū)別
datatime不受時(shí)區(qū)影響
timestamp 受時(shí)區(qū)影響
*/
create table tab_date(
t1 datetime,
t2 timestamp
);
insert into tab_date values (now(),now());
insert into tab_date values ('2020-02-04','2020-02-04');
select * from tab_date;
show variables like 'time_zone';
set time_zone ='+9:00'
#約束
/*
常見約束
含義：一種限制，用于限制表中的數(shù)據(jù)，為了保證表中數(shù)據(jù)的準(zhǔn)確性和可靠性
六大約束：
-- not null非空約束，用于保證該字段必須填寫，如姓名，學(xué)號(hào)
-- default 默認(rèn)約束，用于保存該字段有默認(rèn)值，
-- primary key 主鍵約束，用于保證該字段值具有唯一性，并且非空
-- unique 唯一約束，用于保證該字段的值具有唯一性，可以為空
-- check 檢查約束，mysql中不支持
-- foreign key 外間約束嗎，用于保證該字段值，必須來源與主表的關(guān)聯(lián)列的值
在從表中添加外健約束，用于引用主表中某列的值
添加約束在創(chuàng)建表時(shí)進(jìn)行添加
修改表時(shí)，數(shù)據(jù)還沒插入，可以進(jìn)行修改
約束的添加分類
列級(jí)約束
表級(jí)約束
create table 表名(
列名 類型 列級(jí)約束(六大約束語(yǔ)法上都支持，但外健約束沒有效果 not null,default)，
表級(jí)約束(除了非空，默認(rèn)，其他都支持)
);
*/
#1,創(chuàng)建表添加越約束
#1.添加列級(jí)約束
create database if not exists students;
use students;
drop table if exists stuinfo;
create table stuinfo(
id int primary key, #主鍵
stuNmae varchar(20) not null,
gender char(1),
searnum int unique ,
age int default 18
);
desc stuinfo;
select * from stuinfo;
#查看表中的索引，主鍵，外健，唯一約束會(huì)創(chuàng)建索引
show index from stuinfo;
drop table if exists major;
create table major(
id int primary key ,
majorNmae varchar(20)
)default character set utf8;
#添加表級(jí)約束
/*
語(yǔ)法：在各個(gè)字段的最下面，
constraint 約束名 約束類型 (字段名)
可以不使用constraint 約束名這個(gè)部分
何時(shí)使用
通用的寫法：
*/
drop table if exists stuinfo;
create table stuinfo(
id int,
stuName varchar(20),
gender char(1),
seat int,
age int,
majorid int,
constraint pk primary key(id),#主鍵
constraint uq unique (seat), #唯一鍵
constraint fk_stuinfo_major foreign key(majorid) references major(id)
);
show index from stuinfo;
create table stuinfo(
id int,
stuName varchar(20),
gender char(1),
seat int,
age int,
majorid int,
primary key(id),#主鍵
unique (seat), #唯一鍵
foreign key(majorid) references major(id)
);
create table if not exists stuinfo(
id int primary key ,
stuName varchar(20) not null ,
gender char(1),
seat int unique ,
age int default 18,
majorid int,
constraint fk_stuinfo_major foreign key(majorid) references major(id)
) default character set utf8;
/*
主鍵和唯一的對(duì)比
保證唯一性 是否可以為空 一個(gè)表中可以有多少個(gè) 是否允許組合
primary key 是 否 一個(gè)或者沒有 是
unique 是 是 可以有多個(gè)或者沒有 否
*/
select * from stuinfo;
insert into stuinfo (id,stuName,seat) values (1,'tom',null),
(2,'john',null);
/*
聯(lián)合主鍵，多列組成一個(gè)主鍵
只要有一個(gè)相同就不同
*/
create table if not exists stuinfo(
id int,
stuName varchar(20) not null unique default '張三',
gender char(1),
seat int unique ,
age int default 18,
majorid int,
constraint pk primary key (id,stuName),
constraint fk_stuinfo_major foreign key(majorid) references major(id)
);
insert into stuinfo (id,stuName,seat) values (1,'tom',null),
(2,'tom',null);
/*
外健的特點(diǎn)
在從表的上建立外健約束，
從表與主表的類型和主表的關(guān)聯(lián)列的類型要求一致或兼容，名稱無要求
插入數(shù)據(jù)時(shí)，先插入主表，再插入從表
刪除數(shù)據(jù)時(shí)，先刪除從表，在刪除主表
*/
#修改表時(shí)，添加約束
/*
添加列級(jí)約束
alter table 表名 modify column 字段名 字段類型 新約束;
添加表級(jí)約束
alter table 表名 add constraint 表級(jí)約束
*/
drop table if exists stuinfo;
desc stuinfo;
create table if not exists stuinfo(
id int,
stuName varchar(20),
gender char(1),
seat int ,
age int ,
majorid int
);
use students;
#1.添加非空約束
alter table stuinfo modify column stuName varchar(20) not null ;
#2.刪除非空約束
alter table stuinfo modify column stuName varchar(20) null;
#3.添加默認(rèn)約束
alter table stuinfo modify column age int default 18;
#4.添加主鍵
#列級(jí)約束
alter table stuinfo modify column id int primary key ;
#表級(jí)約束
alter table stuinfo add constraint primary key (id);
#5.添加唯一
use students;
alter table stuinfo modify column seat int unique ;
alter table students.stuinfo add constraint unique (seat);
#6.添加外健
alter table students.stuinfo add foreign key (majorid) references major(id);
alter table students.stuinfo add constraint fk foreign key (majorid) references major(id);
#三，修改表時(shí)刪除約束
#1.刪除非空約束
alter table stuinfo modify column stuName varchar(20) null;
#2.刪除默認(rèn)約束
alter table stuinfo modify column age int ;
#3.刪除主鍵
alter table stuinfo drop primary key ;
#4.刪除唯一約束
alter table stuinfo drop index fk;
#5.刪除外健約束
alter table stuinfo drop foreign key fk;
show index from stuinfo;
desc stuinfo;
#標(biāo)識(shí)列
/*
又稱為自增加列
可以不用手動(dòng)的插入值，系統(tǒng)提供默認(rèn)的序列值
特點(diǎn)：
標(biāo)識(shí)頁(yè)必須和主鍵搭配嗎？不一定，但必須和鍵搭配
一個(gè)表中可以有多少個(gè)標(biāo)識(shí)列 只能一個(gè)自增長(zhǎng)列
標(biāo)識(shí)的類型，只能是數(shù)字類型
*/
#一，創(chuàng)建表時(shí)設(shè)置標(biāo)識(shí)列
drop table if exists tab_identity;
create table tab_identity(
id int ,
name varchar(20)
);
select * from tab_identity;
insert into tab_identity values (1,'john');
insert into tab_identity values (2,'tom');
insert into tab_identity (name) values ('wede');
insert into tab_identity values (20,'james');
#1.不從一開始
show VARIABLES like '%auto_increment%';
#修改表時(shí)設(shè)置標(biāo)識(shí)列
alter table tab_identity modify column id int primary key auto_increment;
############################# 事物 ################################
/*
事物的創(chuàng)建
隱式的事物：事物沒有明顯的開啟和結(jié)束的標(biāo)記
比如insert，update，delete等單條sql語(yǔ)句
顯示事物：必須先設(shè)置自動(dòng)提交功能為禁用 set autocommit=0;
開啟事物的語(yǔ)句
sql語(yǔ)句1;
sql語(yǔ)句2;
提交事物的語(yǔ)句
*/
show variables like 'autocommit';
/*
步驟1：開啟事物
set autocommit=0;
start transcation;可選的
步驟2：編寫事物中的語(yǔ)句
步驟3 ：結(jié)束事物
commit;提交事物
rollback;回滾事物
*/
#事物的演示
drop table if exists account;
create table account(
id bigint unsigned comment 'id',
username varchar(20) comment '用戶姓名',
balance decimal(40, 20) comment '存款'
)character set utf8;
alter table account default character set utf8;
alter table account modify column id bigint unsigned primary key auto_increment;
insert into account (username,balance) values ('aa',1000),('趙敏',1000);
select * from account;
#開啟事物
set autocommit =0;
update account set balance=300 where username='張無忌';
start transaction ;
update account set balance=500 where username='張無忌';
update account set balance=1500 where username='趙敏';
#commit;
rollback ;
#事物沒有隔離性
/*
臟讀：對(duì)于兩個(gè)事物t1,t2.t1讀取了已經(jīng)被t2更新，但是還沒有提交的字段，若t2回滾，t1讀取的數(shù)據(jù)就是無效的
不可重復(fù)讀：對(duì)于兩個(gè)事物t1,t2.t1讀取了一個(gè)字段，然后t2個(gè)更新了該字段，t1再讀取同一個(gè)字段，值就不同了
幻讀:對(duì)于兩個(gè)事物t1,t2.t1讀取了一些字段，然后t2在表中插入了一些新的數(shù)據(jù)，如果t1再次讀取同一個(gè)表，就會(huì)多出幾行
可以設(shè)置隔離級(jí)別，避免臟讀等問題
mysql支持的隔離級(jí)別
read uncommitted
read committed
*/
#查看隔離級(jí)別
select @@tx_isolation;
set session transaction isolation level read uncommitted ; #無法避免臟讀，幻讀，不可重復(fù)讀
set session transaction isolation level read committed ; #可避免臟讀，無法避免幻讀，不可重復(fù)讀
set session transaction isolation level repeatable read; #可避免臟讀，不可重復(fù)讀,無法避免幻讀
set session transaction isolation level serializable; #可避免臟讀，不可重復(fù)讀,幻讀
#savepoint 結(jié)點(diǎn)名 設(shè)置回滾點(diǎn)
use students;
select * from account;
set autocommit =0;
start transaction ;
delete from account where id=5;
savepoint a;
delete from account where id =6;
rollback to a;
#視圖，虛擬的表
/*
和普通的表一樣的，mysql5.1出現(xiàn)的新特性，通過表動(dòng)態(tài)生成的數(shù)據(jù)
*/
desc students.stuinfo;
select * from students.stuinfo;
select * from major;
insert into
students.stuinfo
values
(1,'張飛','男',111,18,1),
(2,'劉備','男',112,18,2),
(3,'關(guān)羽','男',113,18,1),
(4,'趙云','男',114,18,2),
(5,'黃蓋','男',115,18,1);
set character set utf8;
show variables like '%char%';
set character_set_database=utf8;
set character_set_server =utf8;
set character_set_connection =utf8;
set character_set_filesystem =utf8;
insert into major (id,majorNmae) values (1,'語(yǔ)文'),(2,'數(shù)學(xué)');
select s.stuName,majorNmae from stuinfo s inner join major m on s.majorid=m.id where s.stuName like '張%';
create view v1 as select s.stuName,majorNmae from stuinfo s inner join major m on s.majorid=m.id where s.stuName like '張%';
select * from v1 where stuName='張飛';
#一，創(chuàng)建視圖
/*
語(yǔ)法：
create view 視圖名
as 復(fù)雜查詢語(yǔ)句
*/
use myemployees;
create view
myv1
as select
e.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 e.job_id = j.job_id;
#二，使用視圖
select * from myv1;
select last_name,department_name,job_title from myv1 where last_name like '%a%';
drop view avgsalary;
create view avgsalary as select avg(salary) avs,department_id from employees group by department_id;
select avs, j.grade_level from avgsalary a inner join job_grades j on a.avs>j.lowest_sal and a.avs<j.highest_sal ;
select avs,department_id from avgsalary order by avs desc limit 1;
create view myv3 as select avs,department_id from avgsalary order by avs desc limit 1;
select avs,department_name from avgsalary a inner join departments d on a.department_id = d.department_id order by a.avs desc limit 1;
select avs,department_name from myv3 m inner join departments d on m.department_id = d.department_id;
#三，視圖的修改
/*
方式一：
create or replace view 視圖名 查詢語(yǔ)句
方式二:
alter view 視圖名 as 查詢語(yǔ)句
*/
select * from myv3;
create or replace view myv3 as select avg(salary) avs,job_id from employees group by job_id;
#四，刪除視圖
/*
drop view 視圖名1，視圖名2
*/
#五，查看視圖
desc myv3;
show create view myemployees.myv3;
drop view emp_v1;
create view emp_v1 as select last_name,salary,email from employees where phone_number like '011%';
select * from emp_v1;
create or replace view emp_v2
as select max(salary),department_name
from employees e inner join departments d on e.department_id = d.department_id
group by e.department_id having max(salary) >12000;
select * from emp_v2;
#六，視圖的更新
create or replace view myv1 as select last_name,email,salary*12*(1+ifnull(commission_pct,0)) asalary from employees;
create or replace view myv1 as select last_name,email from employees;
#1.插入數(shù)據(jù)
/*
在視圖中插入數(shù)據(jù)，會(huì)在原始的表中插入數(shù)據(jù)
*/
select * from myv1;
insert into myv1 values ('張飛','ZF@qq.com');
#2.修改
update myv1 set last_name='張無忌' where last_name='張飛';
#3.刪除
delete from myv1 where last_name='張無忌';
#可以為視圖添加只讀權(quán)限
/*
具備一下關(guān)鍵詞的視圖是不允許更新
distinct,group by, having,union或則union all;
*/
create or replace view myv1 as select max(salary) m,department_id from employees group by department_id;
update myv1 set m=9000 where department_id=10;
#常量視圖
create or replace view myv2 as select 'john' name;
update myv2 set name='jack';
#select 中包含子查詢
create or replace view myv3 as select (select max(salary) from employees) '最高工資';
update myv3 set 最高工資=100000;
#join語(yǔ)句不能更新
#from 后是一個(gè)不能更新的視圖
/*
視圖 create table 不占用實(shí)際物理空間 增刪該查
表 create view 沒有占有實(shí)際的物理空間，只是保存了實(shí)際的物理邏輯 增刪該查，一般只做查詢
delete與truncate在事物使用時(shí)的區(qū)別
delete可以回滾，
truncate不可以回滾
*/
#演示delete
create table if not exists bookType(
id int auto_increment,
type varchar(20) not null ,
primary key (id)
);
use students;
create table book(
bid int primary key ,
bname varchar(20) unique not null ,
price float default 10,
btype_id int ,
constraint foreign key fk_book_booktype(btype_id) references bookType(id)
);
insert into bookType values(1,'aaaa');
set autocommit =0;
insert into book (bid, bname,price, btype_id) values (1,'it',12.34,1);
create view bookview as select bname,type from book b inner join bookType bT on b.btype_id = bT.id where b.price>1000;
#create or replace view bookview as
#alter view bookview as select
#級(jí)聯(lián)更新或級(jí)聯(lián)刪除
drop table if exists major;
create table major(
id int primary key ,
majorName varchar(20)
)default character set utf8;
show index from major;
insert into major values (1,'java'),(2,'h5'),(3,'data');
drop table if exists stuinfo;
desc stuinfo;
create table if not exists stuinfo(
id int,
stuName varchar(20),
gender char(1),
seat int ,
age int ,
majorid int
)default character set =utf8;
show index from stuinfo;
#添加外健
alter table stuinfo add constraint fk_stu_major foreign key(majorid) references major(id);
insert into
stuinfo
values
(1,'john1','女',null,null,1),
(2,'john2','女',null,null,2),
(3,'john3','女',null,null,1),
(4,'john4','女',null,null,2),
(5,'john5','女',null,null,3),
(6,'john6','女',null,null,1);
#刪除專業(yè)表的3號(hào)專業(yè)
/*
由于存在外健存在不能刪除
*/
use students;
#方式一：級(jí)聯(lián)刪除,刪除主表會(huì)將從表進(jìn)行刪除
show create table students.stuinfo;
alter table stuinfo drop foreign key fk_stu_major;
alter table stuinfo add constraint fk_stu_major foreign key(majorid) references students.major(id) on delete cascade ;
select * from students.major;
select * from students.stuinfo;
delete from students.major where id=4;
#方式二：級(jí)聯(lián)置空
alter table stuinfo drop foreign key fk_stu_major;
alter table stuinfo add constraint fk_stu_major foreign key(majorid) references students.major(id) on delete set null;
select * from students.major;
select * from students.stuinfo;
delete from major where id =2;
#變量
/*
系統(tǒng)變量:變量由系統(tǒng)提供，不是由用戶定義，屬于服務(wù)器層面
1>>使用語(yǔ)法：
1,查看所有的系統(tǒng)變量
show global variables ;查看全局變量
show session variables ;查看會(huì)話變量
2.查看滿足條件的部分系統(tǒng)變量
show global|session variables like '%char%';
3.查看制定某個(gè)系統(tǒng)變量的值
select @@global|session.系統(tǒng)變量名
4.為某一個(gè)系統(tǒng)變量賦值
方式一：
set global|session 系統(tǒng)變量名=值
方式二：
set global|session.系統(tǒng)變量名=值;
2>>會(huì)話變量
作用域：僅僅針對(duì)當(dāng)前的鏈接
1.查看會(huì)話變量
show session variables
show variables
2.查看部分會(huì)話變量
show session variables like '%char%'
3.查看制定的某一個(gè)會(huì)話變量
select @@tx_isolation;
4.為某一個(gè)會(huì)話變量賦值
set @@tx_isolation='read-uncommitted';
set session tx_isolation = 'read-committed'
二，自定義變量
用戶自定義的變量
使用步驟：
申明
賦值
使用
1,用戶變量：
作用域：針對(duì)當(dāng)前會(huì)話（鏈接）有效，同于會(huì)話變量的作用域
#1,聲明并初始化 =或者:=
set @用戶變量名=值;
set @用戶變量名:=值;
select @用戶變量名:=值;
#2.賦值或者更新用戶變零的值
方式一:通過set或select
set @用戶變量名=值;
set @用戶變量名:=值;
select @用戶變量名:=值;
方式二： select into
select 字段 into 變量名 from 表;
#.3查看用戶變量
select @用戶變量名
--局部變量：
作用域:僅僅在定義他的begin end中有效
只能放在begin，end中的第一句話
#1,聲明
declare 變量名 類型
declare 變量名 類型 default 值;
#2，賦值
方式一:通過set或select
set 局部變量名=值;
set 局部變量名:=值;
select @局部變量名:=值;
方式二： select into
select 字段 into 局部變量名 from 表;
#3.使用
select 局部變量名;
作用域 定義與使用的位置 語(yǔ)法
用戶變量 當(dāng)前會(huì)話 會(huì)話中的任何位置 必須加@符號(hào)，不用設(shè)定類型
局部變量 begin end 只能在begin end中，且為第一句話 一般不用加@符號(hào)，需要限定
*/
set @name=100;
select count(*) into @count from students.major;
select @count ;
set @m=1;
set @n=2;
set @sum = @m+@n;
select @sum;
declare m int default 1;
declare n int default 2;
declare sum int;
set sum =n+m;
select sum;
#儲(chǔ)存過程和函數(shù)
/*
存儲(chǔ)過程與函數(shù)，類似于java中的方法
方法的好處：
1.提高代碼的重用性
2.簡(jiǎn)化操作
3.減少了編譯此時(shí)并且減少了和數(shù)據(jù)庫(kù)服務(wù)器的鏈接次數(shù)，提高了效率
#存儲(chǔ)過程：
預(yù)先編譯好的sql語(yǔ)句的集合，可以理解成批處理語(yǔ)句
#一，創(chuàng)建語(yǔ)法
create procedure 存儲(chǔ)過程名(參數(shù)類表)
begin
存儲(chǔ)過程體
end
注意，
1.參數(shù)列表包含三部分
參數(shù)模式 參數(shù)名 參數(shù)類型
IN stuname varchar(20)
參數(shù)模式：
in 該參數(shù)可以作為輸入，也就是說需要調(diào)用法傳入值
out 該參數(shù)可以作為輸出，該參數(shù)可以作為返回值
inout
2.如果存儲(chǔ)過程體僅僅只有一句話，begin end 可以省略
存儲(chǔ)過程體中每條sql語(yǔ)句的結(jié)尾必須加分號(hào)
存儲(chǔ)過程的結(jié)尾可以使用delimiter重新設(shè)置
語(yǔ)法
delimiter 結(jié)束標(biāo)記
案例：
delimiter $
二，調(diào)用過程
call 存儲(chǔ)過程名(實(shí)參列表);
*/
#1.空參列表
use girls;
drop table admin;
create table admin(
id bigint unsigned auto_increment,
username varchar(20) not null,
password varchar(20) not null,
primary key (id)
)default character set = utf8;
insert into
admin (id,username,password)
values
(1,'join',8888),
(2,'lyt',6666);
select * from admin;
delimiter $
create procedure myp1()
begin
insert into admin(username,password) values ('john2',0000),('jack',0000),('tom1',0000);
end $
call myp1()$
#in模式的參數(shù)的存儲(chǔ)過程
create procedure myp2(in name varchar(20))
begin
select bo.* from boys bo right join beauty b on b.boyfriend_id=bo.id where b.name=name;
end $
call myp2('柳巖') $
create procedure myp3(in username varchar(20),in password varchar(20))
begin
declare result varchar(20) default '';
select count(*) into result from admin where admin.username=username and admin.password=password;
select result;
end $
create procedure myp5(in username varchar(20),in password varchar(20))
begin
declare result int;
select count(*) into result from admin where admin.username=username and admin.password=password;
select if(result>0,'成功','失敗');
end $
call myp5('張飛',0000) $
#創(chuàng)建帶out模式的存儲(chǔ)過程
create procedure myp7(in girlname 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=girlname;
end $
select * from beauty;
select * from boys;
set @name='' $
call myp7('柳巖',@name) $
select @name;
call myp7('柳巖',@name1) $
select @name1;
#4.帶inout模式的存儲(chǔ)模式
create procedure myp8(inout a int,inout b int)
begin
set a=a*2;
set b=b*2;
end $
set @a=10 $
set @b=20 $
call myp8(@a,@b) $
select @a,@b;
create procedure test1(in user varchar(20),in psas varchar(20))
begin
insert into admin (admin.username,admin.password) values (username,password);
end $
call test1('aaa','0000');
select * from admin;
create procedure test2(in id int,out name varchar(20),out phone varchar(20))
begin
select b.name,b.phone into name,phone from beauty b where b.id=id;
end $
#存儲(chǔ)過程的刪除
drop procedure test1;
#查看存儲(chǔ)過程的信息
show create procedure myp2;
create procedure test3(in mydate datetime,out strdate varchar(50))
begin
select date_format(mydate,'%Y年%m月%d日') into strdate;
end $
call test3(now(),@str);
select @str;
#函數(shù)
/*
與存儲(chǔ)過程的差異：
存儲(chǔ)過程可以有0個(gè)返回，也可以有多個(gè)返回
函數(shù)必須有一個(gè)返回 處理數(shù)據(jù)后，返回一個(gè)結(jié)果
*/
#一，函數(shù)的創(chuàng)建語(yǔ)法
/*
create function 函數(shù)名(參數(shù)列表) returns 返回類型
begin
函數(shù)體;
end
注意：
1.函數(shù)體：
肯定有return語(yǔ)句，如果沒有會(huì)報(bào)錯(cuò)，
如果return語(yǔ)句沒有放在函數(shù)體的最后也不會(huì)報(bào)錯(cuò)，但不建議
return 值;
2.使用delimiter語(yǔ)句設(shè)置結(jié)束標(biāo)記
*/
#調(diào)用語(yǔ)法
/*
select 函數(shù)名(參數(shù)列表)
*/
#無參無返回
use myemployees;
create function myf1() returns int
begin
declare c int default 0;
select count(*) into c from employees;
return c;
end $
select myf1()$
#有參有返回
create function myf2(empNmae varchar(20)) returns double
begin
declare s double default 0;
set @sal=0;
select salary into s from employees where last_name=empNmae;
return s;
end $
select * from employees;
select myf2('Kochhar');
create function myf3(deptname varchar(20)) returns double
begin
declare sal double default 0;
select avg(salary) into sal from employees e inner join departments d on e.department_id=d.department_id where department_name=deptname;
return sal;
end $
select * from departments;
select myf3('IT');
#三，查看函數(shù)
show create function myf3;
#四，刪除函數(shù)
drop function myf3;
create function myf4(num1 float,num2 float) returns float
begin
declare sum float;
set sum= num1+num2;
return sum;
end $
select myf4(1.3,1.4);
#流程控制結(jié)構(gòu)
/*
順序結(jié)構(gòu)：程序從上往下依次執(zhí)行
分支結(jié)構(gòu):程序從兩條或多條中選擇一條去執(zhí)行
循環(huán)結(jié)構(gòu)：程序在滿足一定條件下，重復(fù)執(zhí)行一段代碼
#一，分支結(jié)構(gòu)
#1.if函數(shù)
功能：實(shí)現(xiàn)簡(jiǎn)單的雙分支
語(yǔ)法：
if(表達(dá)式1,表達(dá)式2,表達(dá)式3)
如果表達(dá)式1成立，返回表達(dá)式2,不成立返回表達(dá)式3
#2.case函數(shù)
情況一：使用switch語(yǔ)句，一般用于實(shí)現(xiàn)等值判斷
語(yǔ)法：
case 變量|表達(dá)式|字段
when 要判斷的值 then 返回的值1或語(yǔ)句;
when 要判斷的值 then 返回的值2或語(yǔ)句;
when 要判斷的值 then 返回的值3或語(yǔ)句;
。。。
else 要返回的值;
end case;
情況二：類似與多重if語(yǔ)句，一般用于實(shí)現(xiàn)區(qū)間判斷
語(yǔ)法：
case
when 要判斷的條件 then 返回的值1
when 要判斷的條件 then 返回的值2
when 要判斷的條件 then 返回的值3
。。。
else 要返回的值
end
特點(diǎn)：
既可以作為表達(dá)式。嵌套在其他語(yǔ)句中使用，可以放在任何地方
*/
create procedure myf5(in score int)
begin
case
when score>=90 and score<=100 then select 'A';
when score>=80 and score<=90 then select 'b';
when score>=70 and score<=80 then select 'c';
when score>=60 and score<=70 then select 'd';
else select 'E';
end case ;
end $
call myf5(77);
/*
if結(jié)構(gòu)
語(yǔ)法：
if (條件1) then 語(yǔ)句1;
elseif 條件2 then 語(yǔ)句2;
elseif 條件2 then 語(yǔ)句2;
end if;
應(yīng)用在begin end語(yǔ)句中
*/
create function myf6 (score int) returns char
begin
if score>=90 and score<=100 then return 'a';
elseif score>=80 and score<=90 then return 'b';
elseif score>=70 and score<=80 then return 'c';
end if;
end $
select myf6(77);
#循環(huán)結(jié)構(gòu)
/*
分類while,loop,repeat
循環(huán)控制：
iterate 類似于continue
leave 類似于break
1。while的語(yǔ)法
while 循環(huán)條件 do
循環(huán)體;
end while
2.loop
標(biāo)簽:loop
循環(huán)體
end loop [標(biāo)簽]
3.repeat
語(yǔ)法:
標(biāo)簽:repeat
循環(huán)體;
until 結(jié)束條件
end repeat[標(biāo)簽]
*/
use girls;
create procedure pro_while1(in insertCount int)
begin
declare i int default 1;
while (i<=insertCount) do
insert into admin(username,password) values ('rose','6666');
set i=i+1;
end while ;
end $
drop procedure pro_while1;
call pro_while1(10)$
select * from admin;
create procedure pro_while2(in insertCount int)
begin
declare i int default 1;
a:while (i<=insertCount) do
insert into admin(username,password) values (concat('rose',i),'5555');
if i>=20 then leave a;
end if;
set i=i+1;
end while a;
end $
call pro_while2(111);
