mysql 整理

mysql 二维表格

一基本概念

数据以表的形式出现

每行为一条记录

每列为记录名称所对应的数据域（field)

许多行和列组成一张单表（Table)

若干单表组成数据库（Database)

事务性：关系型数据库强调ACID规则 原子性　Atomicity,一致性　Consistency,
隔离性　Isolation,持久性　Durability

读写性能，因为强调数据库数据的一致性，降低了读写性能，高并发，海量数据处理性能下降

外键，其他表的主键

4类语言

数据定义语言Data Definition Language

用于数据库，表，视图的建立和删除
DDL包括：CREATE,ALTER,DROP等

CREATE　DATABASE　数据库名　　
DROP　　DATABASE　数据库名
use 数据库名

数据操作语言Data Manipulation Language

用于添加，删除，和修改数据表中的记录
DML包括：INSERT，DELETE，UPDATE

数据库控制语言Data Control Language

用于数据库对象的权限管理和事务管理
DCL包括 ：COMMIT，ROLLBACK，GRANT

select user，host from user 查看个用户可以登录的地址

数据查询语言Data Query Language

用于查询数据库的基本功能
DQL包括：SELECT



select * from employee where sex = ‘男‘
select * from employee where salary > 10000
select * from employee where salery between 10000 and 20000
select * from emplpyee where salery sxe = '男’ and salery > 20000


select * from employee where id = 1 or id = 2 or id = 3 等价
select * from employee where id in(1,2,3)
select * from employee where name like '李%‘

MySql常用函数

select length(“qlf”)
select abs(-10)
select count() from employee
select count() from employee where sex=‘男’
select sum(salary) from employee
select avg(salery) from emploee
select user()
select md5(“qlf”)

查询结果的排序和分页

select * from employee order by salary
select * from employee order by sex, salary desc

select * from employee limit 0,5
select * from employee limit 5,5

GROUP BY 和 HAVING 的使用

select sex , count() from employee group by sex
select dept , count() from employee group by dept
select dept, max(salary) from employee group by dept

select dept, count() from employee group by dept having count()<5
select dept, max(salary) from emplyee group by dept having max(salary)>=10000

distinct 的使用

表连接

内链接
select A.stu_no, A.name, B.course,B.score
from student A
join score B on(A.stu_no = b.stu_no); 等价
select A.stu_no, A.name, B.course,B.score
from student A, score B
where A.stuo=B.stuo;

左链接
select A.stu_no, A.name, B.course,B.score
from student A
left score B on(A.stu_no = b.stu_no)

笛卡尔积（无意义）
select A.stu_no, A.name, B.course,B.score
from student A, score B

子查询

in

查学生的选修课



没有选修
select A.*
from student A
where not exists(slect * from score b where A.stu_no=B.stu_no)