mysql综合

插入数据

全列插入(一一对应)
INSERT INTO employee VALUES (2,'李四','男'),(3,'造钱','男')
部分插入
INSERT INTO 表名(列名，列名) VALUES (值),(值)
多条插入
INSERT INTO 表名(列名，列名) VALUES (值,值,值),(值,值,值)
删除数据(指定)
DELETE FROM employee WHERE name='李四';
删除全部
DELETE FROM employee;
TRUNCATE TABLE employee;
区别：DELETE:可指定删除，也可全部删除(不删表,只删数据)删除可找回
TRUNCATE:只删全部(先完全删除表，再创建新表)删除不能找回

检索查看(架构)

SELECT * FROM employee \G;(所有)
SELECT 指定列名 FROM employee;(单个)
SELETE DISTINCT 指定列名 FROM employee;(去重)
使用完全限定的表名()
SELETE 表.列 FROM dbname.tablename;

将一个表的数据复制到另一个表

INSERT INTO copy_employee(id,name,gender) SELECT id,name,gender FROM employee;
数据更新
UPDATE employee SET name='周七' WHERE id=2;
UPDATE employee SET name='胡八'，salary=6000 WHERE name='孙一';
UPDATE employee SET salary+1000 where name='蟹二'
UPDATE employee SET sum=math+yuwen+english WHERE id; 更新所有总分

查询语句:WHERE

SELECT * FROM employee WHERE id=1(name='yi'); 等于 SELECT id,name FROM employee WHERE id=1(name='yi');
SELECT * FROM employee WHERE id!=1(name!='yi'); 不等于
SELECT * FROM employee WHERE id <> 1; 不等
SELECT * FROM employee WHERE id < 2(id >= 2);
SELTCT * FROM employee WHERE id BETWEEN 2 AND 5;
包含2,5
检查空值
SELECT * FROM employee WHERE hire_date IS NULL(IS NOT NULL);
AND
SELECT * FROM employee WHERE gender='男' AND salary > 2000;
OR
SELECT * FROM employee WHERE gender='男' OR salary > 2000;
IN 范围
SELECT * FROM employee WHERE id IN(1,3); 取1,3 NOT IN (取反)
AND OR 一块用
SELECT * FROM employee WHERE (id >= 2 OR gemder='男') AND salary > 8000;
优先AND
SELECT * FROM employee WHERE id >= 2 OR gemder='男 AND salary > 8000;
模糊查询
%:任意字符，出现任意次数
:任意字符，有且只有一个李% 俩位汉字以上
SELECT * FROM employee WHERE name LIKE '%a%'; 包含a的
SELECT * FROM emplpyee WHERE name LIKE '%a_'; 前面无数个，后面一个

. 除\n 之外任意字符
\d 数字0-9
\D 非数字 ->^0-9
\w 单词字符(a-z,A_Z,0-9,_)
\W 非单词字符
\s 空白字符
\S 非空白字符

^ 以...开头
$ 结尾

*号之前的字符，出现任意次

+号之前的字符，至少一次
？之前的字符，0-1次

非贪婪：尽可能少匹配
str = 'asdfghsdfg'
'*'贪婪到结尾结束
'??'非贪婪 asd +? *?

| 或
() 分组匹配
re.complice()
python使用正则re模块
import re
从头开始匹配,单次匹配，匹配到结果立即返回,如果没有符合规则的返回NONE
re.match()
在全文中匹配，单次匹配，含有符合正则规则的字串，返回结果，反之返回NONE
re.search()
在全文中多次匹配，返回所有
re.findall
替换
re.sub
patter:正则complice对象
repl:替换对象
string:原始字符串
字符串分割，返回列表
re.split()
作用和findall类似，斗返回符合正则规则的字符串，不同的是，返回可迭代对象，可for循环 .group()
re.finditer
分组

在mysql中使用正则(REGEXP)
SELECT * FROM 表名 WHERE 列名 REGEXP
排序 order by
升序:ASC(默认)
降序:DESC
SELECT salary FROM employee ORDER BY salary 方向
升序
SELECT NAME,yuwen,math,english FROM result ORDER BY wuli ASC;
物理升序，语文升序
SELECT NAME,yuwen,math,english FROM result ORDER BY wuli ASC,yuwen ASC;

限定查询 LIMIT

限定返回前两条 offset:偏移量,从哪条开始返回，不包含这条：0,2
num:返回多少行从头开始
SELECT * FROM result LIMIT num;
SELECT * FROM result LIMIT offset,num;
2后两条
SELECT * FROM result LIMIT 2,2; 第2条开始，不包含第2条
组合(不论升降,指名方向)
第一
SELECT NAME,yuwen FROM result ORDER BY yuwen DESC LIMIT 1;
倒数第一
SELECT NAME,yuwen FROM result ORDER BY yuwen ASC LIMIT 1;
中文首字母顺序
SELECT NAME FROM result ORDER BY CONVERT(name USING gbk)

mysql常见聚合函数
MAX():最大
MIN():最小
AVG():平均
COUNT():统计某列行数
SUM():求和

SELECT MAX(yuwen,math,wuli) FROM result;
改名
SELECT MAX(yuwen) as max_a,MAX(math) as max_b,MAX(wuli) as max_c FROM result;

平均
SELECT AVG(yuwen) as 别名 FROM result;
统计某列行数
SELECT COUNT(1) as 别名 FROM result;
和
SELECT SUM(math) as 别名 FROM result;
综合使用
SELECT COUNT(1) as 别名，SUM(列名) as 别名,AVG(列名) as 别名,COUNT(列名) as 别名;

共用

分组 GROUP BY 可和聚合参数同使用
根据班级分组，统计语文平均成绩,，和班级总人数
SELECT class,COUNT(1) FROM result GROUP BY class;
SELECT class,,COUNT(1),AVG(yuwen) FROM result GROUP BY class,;
根据班级和性别分组，统计语文平均成绩，和班级总人数，返回同一分组下学生姓名
SELECT class,gender,count(1),AVG(yuwen),group_concat(name) FROM result GROUP BY class,gender;

GROUP BY和ROLLUP共同使用
会在最后一行1添加一条记录，记录当前列里所有记录和
SELECT class,COUNT(1) as 别名 FROM result GROUP BY class Wq3ITH ROLLUP;
HAVING：过滤分组同样是过滤，和WHERE 相似 WHERE：过滤表中记录 HAVING过滤组 WHERE过滤行
根据班级分组，统计班级人数，返回总人数大于等于2的班级名和学生人数
SELECT class,COUNT(1) as 别名 FROM result GROUP BY class HAVING COUNT(1)>=2;

GROUP BY 和 ORDER BY 共用
SELECT class,gender,count(1) from result group by class,gender having gender='男' and class='一年级';
根据班级，性别分组，统计总人数，根据总人数降序
SELECT class,gender,count(1) as 别名 from result group by class,gender ORDER BY COUNT(1) DESC;

GROUP BY ORDER BY HAVING 共用
根据班级和性别分组，统计班级总人数，过滤出分组人数大于1
SELECT class,gender,COUNT(1) as 别名 FROM grade(表名) DROUP BY class,gender HAVING COUNT(1)>1 ORDER BY COUNT(1) DESC;
根据班级和语文成绩分组，统计每个分组的总的记录，过滤出分组总记录大于2的组，根据分组的总行数倒序排列，限定返回一条
SELECT class,yuwen,COUNT(1) FROM result GROUP BY class,yuwen,HAVING COUNT(1)>=2 ORDER BY COUNT(1) DESC LIMIT 1;
按年龄分组，返回每个年龄分组下对应的员工姓名和薪资
select age,group_concat(name,salary) from group by salary;
员工平均年龄，总员工数量，每月支出，最大，最小
select avg(age),count(1),sum(salary),max(salary),min(salary) from employee;
按月薪分组，每个分组下总人数，总人数大于2的分组返回
select salary,count(1) from employee group by salary having count(1)>2;

SELECT 要检索的列，或表达式
FROM 表名
WHERE 条
GROUP BY 分组(根据哪些列分组)
HAVING 条件(过滤组)
ORDER BY 排序
LIMIT 要检索的行数

select 列
from 表名
where 条件，过滤行
group by 根据列分组
haaving 条键
order by 列，方向
limit 要返回行数
原表中薪资加减乘除
select salary+1000 from employee;
select salary*1000 from employee;
select salary/1000 from employee;

条件判断函数

if(条件,v1,v1):满足返回v1,否则v2
ifnull(manage,'123'):是返回123,否则返回manage

case when 条件 then 结果1 else 结果2 end from 表;
例:年龄大于30返回yes,否则返回no
case when age>30 then 'yes' else 'no' end
select case when age>30 then 'yes' else 'no' end as if age from newemployee;
字符串处理函数
数值处理函数
时间日期处理函数
加密函数
password():mysql 数据库用户信息表中密码就使用此函数
md5:加密函数
insert into uer(name,pw) values ('yi',)
创建表:default , not null , primary key , auto_increment 约束
数据完整性:
保证用户输入的数据存储到数据库中是正确的

实体完整型:针对行的限制
实体完整性约束:
主键:唯一性，值非空，一个表里只一个主键，一个主键可由多个列构成
唯一:列对应的值具有唯一性，值可以为null
自增:一般为主键(int类型)设置自增，设置自增后再次插入数据会在原来基础加1
域完整性都对应约束
针对列的限制
从数据类型限制:数值类型，字符串，时间类型
约束限制:
NOT NULL:往数据库插入数据时，该列不为null
default:设置默认值，当没有为该列设置数据，会显示默认值
参照完整性:表与表之间约束
外键:引用另一个表中的一列或多列，被引用的列应该具有主键约束或唯一性约束
外键用于建立和加强加两个表数据之间的链接

                    07day

mysql 函数
字符串函数拼接，替换
数字函数最大，最小，平均，求余
时间日期函数处理当前时间，返回时间属于哪天

条件判断函数
ifnull(列，value)
if(条件，v1,v2)
case 条件 then value1 else value2 end from employee
加密函数 md5() password()
完整性目的:让输入数据库中的记录是正确的
实体完整形(行约束)
主键:primary key
唯一:unique

域完整性值:
数据类型约束：not null
default

参照完整性
外键:foreign key

自增:auto_increment
alter table 表名 auto_increment=10;
自增步长改为10
set session auto_increment_increment = 10;
偏移5
set session auto_increment_offset = 5
查看步长，偏移量:
show session variables like 'auto_incre%';
自增列自增规律
1行 100+5
2行 101+5
n行 10*(n-1)+5
设置全局自增步长：
set global auto_increment_increment = 10;
设置全局自增偏移量：
set global auto_increment_offset = 5

三范式

1NF:列不可再分
2NF:每个表需有一个主键（主键可由单个列，多个列构成）
所有非主键列，需完全依赖主键，不能部分依赖
3NF:非主键列需直接依赖主键，不能传递依赖

E-R模型:
一对一：身份证
夫妻
用户密码
一对多：一个班级多个学生
多对多：一个老师教多班级，一个班级多个老师教
学生可以选多门课，一门课多个学生选

多表查询
笛卡尔集现象
消除笛卡尔集，只返回满足条件的结果
99写法：隐式内连接
select * from studnets,class where studnets.cls_id=class.cls_id;

链接查询：

内连接:查询出两表共同有的数据
select * from student INNER JOIN student ON student.cls_id=class.cls_id
外连接：左连接:同样查询两表中的数据，以左边表为参照，先把左边表中数据查询出来，
通过字段关联另一个表中数据，如果能关联到结果则展示，否则null填充
select * from class LEFT JOIN students ON class.cls_id=students.cls_id;
右连接同样查询两表中的数据，以右边表为参照，先把右边表中数据查询出来，
通过字段关联另一表中数据，如果能关联到结果则展示，否则null填充
select * from class RIGHT JOIN students ON class.cls_id=students.cls_id;
right 右边表为依据，左边有Null
可添加条件
select stu_name,cls_name,teacher,score FROM class
INNER JOIN students ON class.cls_id = student.cls_id
INNER JOIN score ON students.stu_id = score.stu_id
WHERE scorec.score>60 and teacher='胡老师';
自然连接
select * from class NATURAL JOIN students;

ALTER TABLE students ADD CONSTRAINT f-CS FOREIGN KEY(cls_id) REFERENCES CLASS(cls_id);
references class 依赖主键class
ALTER TABLE students DROP FOREIGN KEY F_CS; 删除外键
ALTER TABLE students ADD CONSTRAINT F_CS FOREIGN KEY(cls_id) REFERENCES class(cls_id)
ON UPDATE CASCRED;