-
数据库数据类型
- char(n) 定长字符串 最长255个字符
当插入的值小于指定长度时,会用空格填充剩余的长度(会浪费空间) - varchar(n) 变长字符串 最长65535个字节 一般超过255个字节,会转换成text类型
- 大文本(长文本) 最长65535个字节 一般超过255个字节,会使用text类型
text也分多种，其中bigtext存储数据的长度约为4GB。 - 日期类型 date:年月日
time:时分秒
datetime:年月日时分秒
timestamp:时间戳(实际存储的是一个时间毫秒值) 与datetime存储日期格式相同
datetime与timestamp区别是:
+ timestamp最大表示2038年，而datetime范围是1000~9999
+ timestamp在插入数、修改数据时，可以自动更新成系统当前时间
- char(n) 定长字符串 最长255个字符
-
数据库相关操作 (不区分大小写)
- 连接数据库:
语法: mysql -uroot -p (mysql -uroot -p -h172.0.0.1 -p3306) - 显示数据库:
语法:show databases; - 进入数据库:
语法:use 数据库名; // 进入数据库后，才能操作库中的表和表记录 - 查看已进入的库:
语法:SELECT DATABASE(); - 查看所有的表:
语法:show tables; - 查看指定的表:
语法:desc 表名 - 删除数据库:
语法:DROP DATABASE 库名;
DROP database if exists mydb1; 判断是否存在 存在则删除 - 创建一个数据库库，指定编码为utf8
语法:CREATE DATABASE 库名 CHARSET 编码;
create database mydb1 charset utf8;
- 连接数据库:
-
表相关操作
-
创建表（编号[数值类型]、姓名、性别、出生年月、考试成绩[浮点型]）
语法: CREATE TABLE 表名(列名 数据类型,列名 数据类型,列名 数据类型,列名 数据类型);
create table stu(id int,name varchar(50),gender varchar(10),birthday date,score double); -
添加数据
语法:insert into stu(ID,name,gender,birthday,score) value (1,‘张三’,‘男’,‘1999-10-3’,99); -
查询数据
语法:select * from stu; -
设置主键
关键字:primary key 放到数据类型后面 -
更新数据
update emp set sal=sal+1000 where name=“张三” 修改张三的薪资增加1000 -
删除数据
delete -
模糊查询
语法 select name from emp where name like ‘%张%’ //%表示一个或多个,也可以为0,%张表示以张结尾 张%表示以张开头 -
计数查询(聚合函数)
select count(id) from emp 查询有多少个id值 遇到null不会计入计数
select count() from emp 统计所有记录数
select max(sal) from emp 查询某个字段的最高值
select min(sal) from emp 查询某个字段的最低值
select sum(sal) from emp 查询某个字段的和
select avg(sal) from emp 查询某个字段的平均值
select dept,count() from emp group by dept 按部门分组并统计
select dept,max(sal) from emp group by dept 每个部门的最高薪资
select name,birthday from emp where birthday between ‘1993-1-1’ and ‘1995-12-31’
select name,birthday from emp where year(birthday) between 1993 and 1995; //查询生日在某个范围之间的数据
select * from emp where month(birthday)=month(now()) //查询本月过生日的员工
select name,concat(sal,’(元)’) from emp//连接字符串 -
排序查询
关键字 order by 列名 ASC(升序,默认可以省略)|DESC(降序)
select * from emp order by sal desc //按照薪资进行降序排序
select * from emp order by bonus desc,sal desc //先按照奖金降序排列,奖金一样的再按照薪资排序
//每一列都需要指定排列方式,否则默认升序 -
分页查询
select * from emp limit 0,3 //3条分一页 从第一条开始
select * from emp limit 3,3 // 从(3+1)条开始
select * from emp order by sal desc limit 0,3 //查询薪资最高的三个人 -
多表查询
select * from dept,emp //查询出来的是笛卡尔集 也就是dept*emp的结果,并不是我们所要的,所以用where来剔除错误的数据
select * from dept,emp where dept.id=emp.dept_id; //这样就剔除了错误数据 所属部门id对应部门编号
左外链接查询(没有员工的部门,员工列显示null,也就是左边表要全部显示)
select * from dept left join emp on dept.id=emp.dept_id;
右外链接查询(没有部门的员工,部门列显示null,也就是右边表要全部显示)
select * from dept right join emp on dept.id=emp.dept_id;
全外链接查询(mysql不支持全外链接查询)
select * from dept full join emp on dept.id=emp.dept_id;
换种方式实现(联合查询,两个查询的列名和列数要求一致):
select * from dept left join emp on dept.id=emp.dept_id union(去重) select * from dept right join emp on dept.id=emp.dept_id;
union all(不去重)
select * from dept union select id,name from emp; -
嵌套查询
select name,sal from emp where sal>(select sal from emp where name=‘张良’);//查询工资高于张良的员工
select name,job from emp where job >(select job from emp where name=‘张飞’);//查询张飞的同事
select name,sal,dept.name from fept,emp where emp.dept_id=det.id;
select emp.name,sal,dept.name from dept,emp where emp.dept_id=dept.id and sal>(select max(sal) from emp where dept_id=30);
//查询
select emp.name,sal,dept -
多表查询
select emp.name,dept.name from emp,dept where dept.id=emp.dept_id and dept.name=‘销售部’; //查询在销售部的员工 -
自查询
(自查询)列出所有员工及其直接上级，显示员工姓名、上级编号，上级姓名
/* 将emp表既看作员工表, 也看做上级表 e1(员工表) e2(上级表)
查询的列: e1.name, e2.id, e2.name
查询的表: emp e1,emp e2
连接条件: e1.topid=e2.id
*/
select e1.name,e2.id,e2.name as 上级 from emp e1 left join emp e2 on e1.topid=e2.id;
select e1.name, e2.id, e2.name from emp e1,emp e2 where e1.topid=e2.id;列出最低薪资大于1500的各种职位，显示职位和该职位最低薪资
SELECT job,min(sal) from emp GROUP BY job having min(sal)>1500列出在每个部门就职的员工数量、平均工资。显示部门编号、员工数量，平均薪资。
select emp.dept_id 部门ID,COUNT(emp.id) 员工数量,AVG(sal) 平均薪资 from emp left join dept on emp.dept_id=dept.id GROUP BY dept_id
select emp.dept_id 部门ID,COUNT(emp.id) 员工数量,AVG(sal) 平均薪资 from emp group by dept_id查出至少有一个员工的部门，显示部门编号、部门名称、部门位置、部门人数。
select emp.dept_id 部门编号,dept.name 部门名称,loc 地址,COUNT(emp.id) 人数 FROM emp left join dept on emp.dept_id=dept.id GROUP BY dept_id列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。
/*
查询的列:e1.id,e1.name,d.name
查询的表:emp e1(员工表),dept d(部门表),emp e2(上级表)
连接条件:e1.dept_id=d.id and e1.top_id=e2.id
筛选条件e1.hdate<e2.hdate
*/
select e1.id,e1.name,d.name from emp e1,dept d,emp e2 where e1.dept_id=d.id and e1.top_id=e2.id and e1.hdate<e2.hdate列出每个部门薪资最高的员工信息，显示部门编号、员工姓名、薪资
–求出每个部门的最高薪资,显示部门编号和最高薪资
select dept_id,max(sal) from emp from group by dept_id–将上面的查询结果作为一张表和emp表进行连接查询
select e.dept_id,t.max_sal,e.name from emp e,(select dept_id,max(sal) from emp from group by dept_id) t where e,sal=t.max_sal and e.dept_id=t.dept_id;–查询没有部门的员工的最高信息,显示部门编号,最高薪资,员工姓名
-
-
where和having的区别
相同点:都用于对表中的记录进行筛选
不同点:where是在分组之前对表中的记录进行筛选过滤.where中不能使用列别名(但可以使用表别名),也不能使用聚合函数.
having是在分组之后,对表中的记录进行筛选,having中可以使用列别名 表别名 聚合函数
通常情况,having可以替代where,当分组前要筛选,分组后要筛选,就不能用having -
字段设置相关
关键字:primary key(设置主键) 不能为空,不能重复 放到数据类型后面
auto_increment 如果主键是数值类型,为了避免因为主键重复导致插入失败,可以设置主键
自动增长(主键自增策略),设置之后,主键不用指定,指定为null即可
1.当需要id时,获取变量的值作为id插入,用完后变量会自增1
2.设置之后,也可以为id赋值,只要不冲突即可,插入成功之后自动增长的值
会和插入的值比较,若插入的值较大,会将插入的值加1后赋值给自动增长的值
not null(非空约束) 可以重复 不能为空
unique(唯一约束) 不能重复 可以为空
foreign key(本表的列名) references 表名(列名) (创建表时创建外键约束)
外键的添加:
一对一:任意一方添加另外一方的主键作为外键
一对多(多对一):在多的一方添加外键,保存一的一方的主键作为外键
多对多:不能在任意一方添加外键.此时可以创建一张第三方的表来保存关系 -
数据库的备份与恢复
-
备份:
mysqldump -uroot -p db40 >D:/db40.sql //备份单个数据库
mysqldump -uroot -p --all-database > d:/all.sql //备份所有数据库 -
恢复:
mysql -u用户名 -p 数据库的名字 < 备份文件的位置 //在cmd窗口中(未登录的状态下)
mysql -uroot -p db60 < d:/db40.sql //将d:/db40.sql文件中的数据恢复到db60库中(需要先创建好数据库,且未登录)
source d:/db40.sql //登录状态下执行sql文件
-
-
数据库内容的修改
* 修改表_新增列
语法:ALTER TABLE tabname ADD col_name datatype [DEFAULT expr][,ADD col_name datatype…];
举例:alter table stu add score double;
-
修改表_修改列
语法:ALTER TABLE tabname MODIFY (col_name datatype [DEFAULT expr][,MODIFY col_name datatype]…);
举例:alter table stu modify id int primary key; //修改id列，将id设置为主键
alter table stu modify id int auto_increment; //修改id列，将id主键设置为自动增长 -
修改表_删除列
语法:ALTER TABLE tabname DROP [COLUMN] col_name;
举例:alter table stu drop score; //删除stu表中的score列
-
SQL语句的书写顺序:
select * | 列名 – 确定要查询的列有哪些
from 表名 – 确定查询哪张表
where 条件 – 通过筛选过滤，剔除不符合条件的记录
group by 分组的列 – 指定根据哪一列进行分组
having 条件 – 通过条件对分组后的数据进行筛选过滤
order by 排序的列 – 指定根据哪一列进行排序
limit (countPage-1)*rowCount, rowCount – 指定返回第几页记录以及每页显示多少条 -
SQL语句的执行顺序:
from 表名 – 确定查询哪张表
where 条件 – 通过筛选过滤，剔除不符合条件的记录
select * | 列名 列别名 – 确定要查询的列有哪些，
group by 分组的列 – 指定根据哪一列进行分组
having 条件 – 通过条件对分组后的数据进行筛选过滤
order by 排序的列 – 指定根据哪一列进行排序
limit (countPage-1)*rowCount, rowCount
补充:
数据库的事务(事务内的数据都执行成功,才会将改动提交到数据库,如果中间出了问题,则会撤销(回滚)操作)
打开事务:start transaction
//接下来执行操作
关闭事务:1.回滚:rollback 撤销之前的操作(在事务提交之前,操作显示的数据是日志里的操作,只有提交事务之后才会将修改结果放到数据库中)
2.提交:commit 提交(数据提交到数据库内持久保存)
多个事务对相同的数据同时进行操作，这叫做事务并发。
在事务并发时，如果没有采取必要的隔离措施，可能会导致各种并发问题，破坏数据的完整性等。这些问题中，其中有三类是读问题，分别是：脏读、不可重复读、幻读。
-
(1)脏读（dirty read）：读到另一个事务的未提交更新数据，即读取到了脏数据；
例如：A给B转账100元但未提交事务，在B查询后，A做了回滚操作，那么B查询到了A未提交的数据，就称之为脏读。 -
(2)不可重复读（unrepeatable read）：对同一记录的两次读取不一致，因为另一事务对该记录做了修改（是针对修改操作）
-
例如：在事务1中，前后两次查询A账户的金额，在两次查询之间，另一事物2对A账户的金额做了修改，此种情况可能会导致事务1中，前后两次查询的结果不一致。这就是不可重复度
-
(3)幻读（虚读）（phantom read）：对同一张表的两次查询不一致，因为另一事务插入了一条记录(是针对插入或删除操作)；
事务隔离级别
事务隔离级别分四个等级，在相同数据环境下，对数据执行相同的操作，设置不同的隔离级别，可能导致不同的结果。不同事务隔离级别能够解决的数据并发问题的能力也是不同的。
set tx_isolation=‘read-uncommitted’;
1、READ UNCOMMITTED（读未提交数据）
安全级别最低, 可能出现任何事务并发问题(比如脏读、不可以重复读、幻读等)
性能最好（不使用!!）
2、READ COMMITTED（读已提交数据）（Oracle默认）
防止脏读，没有处理不可重复读，也没有处理幻读；
性能比REPEATABLE READ好
3、REPEATABLE READ（可重复读）（MySQL默认）
防止脏读和不可重复读，不能处理幻读问题；
性能比SERIALIZABLE好
4、SERIALIZABLE（串行化）
不会出现任何并发问题，因为它是对同一数据的访问是串行的，非并发访问的；
性能最差；
MySQL的默认隔离级别为REPEATABLE READ，即可以防止脏读和不可重复读
扫一扫
295
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
