SQL 笔记整理 8_9

数据库:
数据库：存储数据的仓库
数据库管理系统:操作数据库的一个工具

关系型数据库的一些概念数据库表：存储数据的一个单位字段：字段是数据库表中存储一类数据的一个单位元组：数据库中所有的数据数据冗余:在数据库表中，多余的数据失去数据完整性:如果某一列是唯一性数据，但是数据库中出现多个此数据主键:具有唯一标注一条数据记录的列 ，此列没有任何业务意义，唯一的作用     就是能代表这条数据 ，主键绝对不能为空

数据库的结构化查询语言:
数据定义语言(DDL):
create(创建表、视图…)、alter(修改表、字段…)、drop(删除表、视图)

数据操作语言：    增加(insert)、删除(delete)、更新(update)、查询(select)数据控制语言(DCL):     分配(grant)、回收(revoke)、拒绝(deny)

MySql操作：
mysql是使用数据库来操作表的，和sql servver类似，不同于oracle，oracle使用
用户进行管理表

 在MySql里，如果改变数据库使用： use  数据库名 ;

数据库的操作：
创建数据库：
create database dbname ;

删除数据库表：    drop database dbname ;

数据库表的管理:
MySql的数据类型：

创建数据库表:create  table  emp (empid  int  primary key,ename varchar(60),sex char(4),age int ,salary double(8,2),createdate date)alter table tableName add constraint T_PK primary key (id)查看数据表：    DESC tablename；查看表的详细结构:    show create table tableName \G  "G 代表格式化"插入数据： insert into  tableName（列1 ，列2...） values(v1,v2,......)修改数据：  update tableName set { colName1=v1,colName2=v2,......}删除数据：  delete  from table  where  condition;

数据库查询:
查询表的全部行和列
select * from T_emp;

SELECT * FROM t_emp ;

SELECT ename,salary from t_emp;

SELECT ename as ‘姓名’, salary as ‘薪资’ from t_emp;

SELECT age from t_emp;

SELECT DISTINCT(age) from t_emp;

SELECT * FROM t_emp WHERE empid < 3

SELECT * FROM t_emp LIMIT 4 ,2

SELECT ename ,salary ,salary * 2 FROM t_emp;

SELECT * from t_emp where ename = ‘lucy’ ;

SELECT * from t_emp where sex = ‘男’;

SELECT * from t_emp where salary <> 3000

SELECT * from t_emp where sex =’男’ and salary > 5000 and depart = ‘人事部’；

SELECT * FROM t_emp where age BETWEEN 20 and 22 ;

SELECT * FROM t_emp where age >= 20 and age <= 22 ;

SELECT * from t_emp where age in (19,20,25,30)

SELECT * from t_emp where ename LIKE ‘%L%’ ;
–查询姓张的

SELECT * from t_emp where ename like ‘张%’

SELECT * from t_emp where ename like ‘张_’

SELECT * from t_emp where ename like ‘李%’

SELECT * from t_emp where ename like ‘ABO[1-9]’

SELECT * from t_emp where ename not like ‘李%’

SELECT * FROM t_emp where depart is null;

SELECT * FROM t_emp order by salary desc ,age asc

–查询数据表中共有多少条数据 、所有员工一年要发多少钱 ，每月员工平均薪资、最高工资、最低工资

SELECT
COUNT(empid) as ‘总数聚量’ ,
sum(salary) * 12 as ‘总钱数’,
avg(salary) as ‘平均薪资’,
max(salary) as ‘最高’,
min(salary) as ‘最低’ ,
avg(age)
FROM
t_emp
where depart = ‘销售部’

–每个部门的平均工资
SELECT depart,avg(salary) as avgSalary from t_emp
GROUP BY depart HAVING avgSalary > 5000

– 张三给李四转账500；
– 事务
– 1.原子性
– 2.一致性
– 3.隔离性
– 4.持久性

– 表示关闭默认的提交方式，数据库管理员\
set autocommit=1;
update bank set balance=balance+500 where name=’李四’;
update bank set balance=balance+500 where name=’张三’;
ROLLBACK;
start TRANSACTION;
update bank set balance=balance+500 where name=’李四’;
update bank set balance=balance+500 where name=’张三’;
ROLLBACK
update bank set balance=balance+500 where name=’张三’;

select * from bank;
begin TRANSACTION

– 函数 可以把他理解为方法
– select NOW();

– 常用的mysql函数

– 对比java中的方法来使用

– 字符串：
– String ,LENGTH(str),SUBSTR(str,pos,len),CONCAT(str1,str2,…),TRIM([remstr FROM] str)
– LOWER(),REPLACE(str,from_str,to_str),LEFT(str,len),RIGHT(str,len)
select LENGTH(‘abcde’);
select substr(‘abcde’,2,3);
select CONCAT(‘a’,’b’,’c’);
select LENGTH(trim(’ abc ‘));
select LOWER(‘ABCDE’);
select UPPER(‘abcde’);
– 例如找’张三’ 数据库中类型’张 三’;
select REPLACE(‘张 三’,’ ‘,”);
select LOCATE(‘b’,’abcde’);
– 数学：Math,RAND(),POW(X,Y),ROUND(X,D),FLOOR(X),CEILING(X)
select RAND();
select pow(3,4);
– 向下取整
– er(entity -relation) 图
– 范式来设计
– 第一范式
每一列必须是不可再分割的最小的元素（原子性）
张三 中国（国家） 江苏省（省） 苏州市（市） 工业园区（区） 【仁爱路1号】
李四 中国江苏省苏州市工业园区 【仁爱路2号】
– 第二范式
首先要满足第一范式，除了主键列之外所有的列都和主键有关
– 第三范式
首先要满足第二范式，要和主键有直接关系，而不是间接地

select * from j_score;
– 成绩编号，学员编号，学员姓名，现在的住址，出生年月，性别，科目编号，科目名称，教师编号，教师名称，成绩
– 如果按照上述的步骤创建表，会造成数据大量重复
– 成绩编号，学员编号，学员姓名，科目编号，科目名称，成绩
emp 编号，姓名，岗位id
job 岗位id,岗位名称,薪资
– mysql:64g

– 一般你只需要满足前两个

SELECT FLOOR(5.6);
– 向上取整
select ceiling(5.6);
– 四舍五入
select ROUND(5.456,2);

– 日期：统计中经常使用日期函数
– NOW(),SYSDATE(),DATEDIFF(expr1,expr2),DATE_ADD(date,INTERVAL expr unit),DATE_FORMAT(date,format)
– 借贷
select DATEDIFF(‘2017-8-7’,’2017-8-10’);
select DATE_ADD(‘2017-8-7’,INTERVAL -7 DAY);
– 转换函数
– CAST(expr AS type)
select CAST(sname AS UNSIGNED) from j_student;
– CONVERT(expr,type)
select CONVERT(‘12345’,UNSIGNED);

– 自定义函数
create function fn_test
RETURNS double
BEGIN
#Routine body goes here…
DECLARE avg_score double;
set avg_score=(select avg(score) from j_score where sno=var_sno);
return avg_score;
END
select avg(score) from j_score where sno=1;

行转列 #in和exsits not in 和not exsits #in如果要查询的子表内容较多，则不提倡用in，而改用exsits来替换 #但是如果查询的子表数量不多，则可以使用in来进行查询

select * from j_student A where sno not in( select sno from j_student B);
select * from j_student where sno=1 or sno=2 ……
select * from j_student s1 where not EXISTS (select 1 from j_student where s1.sno=sno)
EXISTS true;

not in 和not exsits not EXISTS比not in效率要高 #in查询的数据不要超过1w条

– 存储过程|函数
– public void say(参数)
– {
– 变量，控制流语句
– }
create PROCEDURE proc_名称()
BEGIN
– 如何定义变量
declare 变量名 变量类型 DEFAULT 默认值;
– 如何给变量赋值
set 变量名=变量值;
– select 赋值
select 列名 into 变量名 from 表名 where 条件
– 逻辑控制语句
if 条件 THEN
满足if要干的事情
ELSEIF 条件 THEN
满足elseif要干的事情
ELSE
满足else要干的事情
end if;
– CASE
case 变量名
when 条件 then 执行内容
when 条件 then 执行内容
when 条件 then 执行内容
else 执行内容
end case;

– 循环
– WHILE
while 条件 DO

end while;

– REPEAT
repeat
内容

UNTIL 条件
end repeat

– LOOP:游标
lp:LOOP

if 条件 THEN
LEAVE lp;
END if;

end loop;

end;

– 如何调用
call 存储过程的名字()
– 删除存储过程
drop PROCEDURE if EXISTS proc_test;

create PROCEDURE proc_test()
BEGIN
declare var_sname varchar(20) DEFAULT ‘张三’;
– set var_sname=’李四’;
select sname into var_sname from j_student where sno=1;
– select var_sname;
if var_sname=’张三’ THEN
select ‘找到了张三’;
ELSEIF var_sname=’李四’ THEN
select ‘找到了李四’;
ELSE
select ‘找到了王丽’;
end if;
end;

call proc_test()

– 循环删除100条数据
drop PROCEDURE if EXISTS proc_test1;
create PROCEDURE proc_test1()
BEGIN
DECLARE var_index int;
set var_index=600;
– while var_index<=356 DO
– delete from j_student where sno=var_index;
– select var_index;
– set var_index=var_index+1;
– end while;
– REPEAT
– delete from j_student where sno=var_index;
– set var_index=var_index+1;
– UNTIL var_index>=600
– end repeat;
lp:LOOP
delete from j_student where sno=var_index;
set var_index=var_index+1;
if var_index>=700 THEN
LEAVE lp;
END if;
end loop;
end;
call proc_test1();