SQL
1.SQL分类
- DDL:数据定义语言 如create,drop,alter
- DCL: 数据控制语言 如 grant.if
- DML: 数据操纵语言 如insert,update,delete
- DQL:数据查询语言 如select
2.SQL对数据库的操作
2.1创建数据库
语法: create datebase 数据库名称 [charater set 字符集 collate 字符集校对规则];
create database db1; 默认字符集
create database db2 character set utf8; 默认校对规则
create database db3 character set utf8 collate utf8_bin;
2.2查看数据库
语法:
- show databases;查看所有数据库
- show create database 数据库名称; 查看数据库定义信息
show databases;
show create database db2;
2.3修改数据库
语法: alter database 数据库名称 character set 字符集 collate 字符集校对规则
alter database db2 character set utf8;(不写,默认校对规则)
2.4删除数据库
语法: drop database 数据库名称
drop database db1;
2.5其它数据库操作
切换使用的数据库: use 数据库名称(要切换的)
use db2;
查看当前使用的数据库: select database();
select database();
3.SQL对数据库表的操作
3.1创建表
语法: create table 表名称(字段名称 字段类型(长度)约束,字段名称 字段类型(长度)约束…);
字段类型:一个实体对应一个表，一个实体属性对应一个表的字段
java与MySQL数据类型对应(方便记忆):
- java中的类型 byte short int long float boolean char String Date File
- MySQL中的类型 tinyint smallint int bigint float double bit char varchar date/time/datetime/timestamp BLOB/TEXT
char与varchar的区别:
- char代表固定长度的字符或字符串 (定义char(8),向这个字段存入字符串hello，那么数据库使用三个空格将其补全)。
- varchar代表可变长度的字符串(定义雷系 varchar(8)，向这个字段存入字符串hello，那么存入到数据库的就是hello)。
datetime与timestam的区别:
- datetime就是既有日期又有时间的日期类型，如果没有向这个字段中存值，数据库使用null存入到数据库中
- timestamp也是既有日期又有时间的日期类型，如果没有向这个字段中存值，数据库使用当前的系统时间存入到数据库
BLOB/TEXT:存一些音频，二进制，文本文件，使用较少。一般直接通过流存入磁盘，在数据库中记录其所在位置。
约束:
- 约束作用: 保证数据的完整性
- 单表约束分类:
- 主键约束:primary key 主键约束默认就是唯一，非空的
- 唯一约束:unique
- 非空约束: not null
- 建表语句:
create table userinfo(
id int primary key auto_increment,
username varchar(20) unique,
password varchar(20) not null,
age int,
address varchar(50)
);
3.2删除表
语法: drop table 表名;
drop table userinfo;
3.3修改表
- 添加列
alter table表名 add 列名 类型(长度) 约束;
alter table userinfo add image varchar(20) not null
- 修改列的类型，长度和约束
alter table 表名 modify 列名 类型(长度) 约束;
alter table userinfo modify qq varchar(20) not null;
- 删除列
alter table 表名 drop 列名;
alter table userinfo drop qq;
- 修改列名称
alter table 表名 change 旧列名 新列名 类型(长度) 约束;
alter table userinfo change age nian int;
- 修改表名称
rename table 表名 to 新的表名;
rename table userinfo to user;
- 修改表的字符集
alter table 表名 character set 字符集;
alter table user character set gbk;
3.4查询表
- 查看某个数据库下的所有表
语法:show tables;
show tables;
- 查看某个表的结构信息
语法: desc 表名
desc userinfo;
4.对数据表记录的基本操作
4.1SQL添加表的记录
语法:
- 向表中插入某些列: insert into 表名 (列1，列2，列3…)values(值1，值2，值3…);
- 向表中插入所以列: insert into 表名 values(值1，值2，值3…);
注意事项:
- 值的类型与数据库中表的类型一致
- 值的顺序与数据库中表的顺序一致
- 值的最大长度不能超过列设置最大长度
- 值的类型是字符串或者是日期类型，使用单引号引起来
insert into user(id,username,password)values(null,'李华','123');
insert into user values(null,'小明','1234','10','2002-9-27');
4.2SQL修改表的记录
语法:
- update 表名 set 列名=值，列名=值 [where 条件];
注意事项:
- 值的类型与列的类型一致
- 值的最大长度不能超过列设置的最大长度
- 字符串类型何日期类型添加单引号
//修改某一列所有值
update user set password = 123456;
//按条件修改数据
update user set password = 'abc' where username='李华';
//按条件修改多个列
update user set password = 'xyz',age=99 where username='小明';
4.3SQL删除表的记录
语法:
- delete from 表名[where 条件];
注意事项:
- 删除表的记录，指的是删除表中的一行记录
- 删除如果没有条件，默认是删除表中的所有记录
//删除某一条记录
delete from user where id = 1;
//删除表中所有记录
delete from user;
删除记录
- delete from user:删除所有记录，是DML语句，逐条删除记录。事务可以作用在DML语句上
- truncate table user:删除所有记录，属于DDL语句，将表删除，然后创建一个结构一样的表。事务不能控制。
4.4SQL查看表的记录
4.41基本查询
语法
- select [distinct]*|列名 from 表 [条件];
//1.查询所有记录
select * from exam;
//2.查询所有的姓名和数学成绩
select name,math from exam;
//3.查询英语成绩，不显示重复值
select distinct english from exam;
//4.总成绩查询
select name,english+math+chinese from exam;
//5.别名查询
select name,english+math+chinese as sum from exam;
4.42条件查询
语法:使用where子句
- <,>,>=,<=,<>,=
- like模糊查询
- in: 范围查询
- 条件关联:and,or,not
like模糊查询:
在like子句中可以使用_或者%作为占位符，_只能代表一个字符，而%可以代表任意个字符。
- like ‘李_’ :名字必须两个字，而且姓李
- like ‘李%’ : 名字中姓李的学生，李字后可以是1个或任意个字符
- like ‘%四’ ：名字中以四结尾的。
- like ‘%王%’ :只要名字中包含这个字的
- 要检索信息中有_的: like ‘%_%’; 若不想使用\，可以用escape自定义逃逸字符
示例
select * from exam where name='李四';
select * from exam where name = '李四' and english>90;
select * from exam where name like '李%';
select * from exam where english in (69,89,75);
4.43排序查询
语法:
- 使用order by(asc(升序)/desc(降序))
//按语文成绩升序查询
select * from exam order by chinese;
//按语文成绩降序查询
select * from exam order by chinese desc;
//按语文成绩降序查询，如果语文成绩相同，则按英语降序
select * from exam order by chinese desc,english desc;
//查询姓李的学生信息，按英语成绩降序
select * from exam where name like '李%' order by english desc;
4.44分组统计查询
聚合函数的使用:
- sum();
- count();
- max();
- min();
- avg();
//获取所有学生的英语成绩总和
select sum(english) from exam;
//获取所有学生的英语成绩和数学成绩
select sum(english),sum(math) from exam;
//查询李姓学生的英语成绩总和
select sum(english) from exam where name like '李%';
//查询所有学生各科成绩总和
select sum(english+math+chinese)from exam;
select sum(english)+sum(math)+sum(chinese) from exam;
//当成绩都不为null时结果相同,有null时，前者不变，因为null+任何数都为null。
//使用ifnull聚合函数
select sum(ifnull(english,0)+math+chinese)from exam;//将null作为0处理
//获得学生总人数
select count(*) from exam;
//获得数学最高分
select max(math) from exam;
//语文最低分
select min(chinese) from exam;
//英语平均分
select avg(english) from exam;
group by:
//查询每个商品的销量
select product,count(*) from orderitem group by product;
//查询每个商品的交易额
select product,sum(price) from orderitem group by product;
//按商品名称统计，统计每类商品花费的总金额在5000元以上的商品，并且按照总金额升序排序。
select product ,sum(price) from orderitem group by product having sum(price) > 5000 order by sum(price)asc;
where的子句后面不能跟着聚合函数。如果现在使用带有聚合函数的条件过滤需要使用having关键字.
4.5小结
语法顺序:
S(select)…F(from)…W(where)…G(group by)…H(having)…O(order by);
5.重置mysql的root密码
重置密码的步骤:
- 停止mysql的服务。
操作:windows+r ->输入service.msc ->找到mysql服务->停止mysql服务
(你的服务名称可能不为mysql,可能为MySQLXX,停止即可)
- 启动mysql
操作: windows+r->输入cmd->输入mysqld --skip-grant-tables(跳过密码认证服务)
(这一步一定要以管理员的身份进行，否则无效。成功后相当于已经运行了一个mysql服务，不要关，进行下一步.)
- 重新打开一个cmd
操作:在cmd中输入mysql -u root -p ->回车->不输入密码，回车->进入了mysql
- 修改密码
操作:输入use mysql;->update user set password = password('新的密码') where user = 'root';
(这里需要注意的一点是，mysql5.7以上的版本中，password字段被替换成了authentication_string)
此时密码就已经修改成功了
- 结束mysqld进程
操作:把两个cmd窗口关闭->ctrl+alt+delete进入任务管理器->点详细信息->找到并结束mysqld进程
- 重新启动mysql服务
操作:把步骤1的最后一部改为启动即可。
操作:然后输入mysql -u root -p 回车->输入新密码->成功登录即可
6.数据库的备份与还原
6.1数据库的备份
//以管理员身份打开cmd
第一步:mysqldump -u root -p 数据名 >G:/文件名
第二步: 输入数据库密码
6.2数据库的还原
第一种还原方式:
- 进入mysql，创建数据库
- 退出数据库 输入命令:mysql -u root -p 数据库名< G:/文件名(文件路径)
第二种还原方式:
- 在数据库服务器内部创建数据库
- 切换到该数据库使用source命令还原 (source G:/web_test.sql)
7.多表设计
7.1外键约束
约束的作用: 约束是用来保证数据的完整性。
- 单表约束
- 主键约束
- 唯一约束
- 非空约束
- 多表约束
- 外键约束: 用来保证数据完整性(多表之间).
创建一个部门表
create table dept(
did int primary key auto_increment,
dname varchar(20)
);
insert into dept values(null,'市场部');
insert into dept values(null,'人事部');
insert into dept values(null,'教研部');
创建一个员工表
create table employee(
eid int primary key auto_increment,
ename varchar(20),
salary double,
birthday date,
sex varchar(10),
dno int
);
insert into employee values(null,'张三',8000,'1990-09-01','男',3);
insert into employee values(null,'李四',9000,'1989-09-27','男',1);
insert into employee values(null,'王五',6000,'1989-06-07','男',2);
insert into employee values(null,'赵六',10000,'1999-09-27','男',3);
insert into employee values(null,'孙七',10000,'1989-09-27','男',1);
insert into employee values(null,'田八',9000,'1989-09-27','男',1);
执行两个操作:
- 向员工表中插入一条记录(没有部门)
insert into employee values(null,'老王',7800,'2001-09-27','男',null);
- 删除一个人事部
delete from dept where did = 2;
显然，这两张表代表了存在关系的两个实体，上述两个操作都应该不发生!
添加外键:
alter table employee add foreign key (dno) references dept (did);
alter table employee modify dno int not null; //修改外键不允许为空
7.2表与表之间的关系
1.一对多的关系
一对多的建表原则: 在多的一方创建外键指向一的一方的主键
2.多对多的关系
多对多建表原则: 需要创建第三张表(中间表),在中间表中至少两个字段分别作为外键，指向多对多双方表的主键
3.一对一的关系
唯一外键对应方式: 假设一对一是一个一对多关系，需要在多的一方创建外键指向一的一方的主键，将外键设置为唯一(unique)
主键对应方式: 主键一一对应
8.多表查询
8.2连接查询
交叉连接:查询到的是两个表的笛卡尔积。
- 语法: select * from 表1 cross join 表2;
- select * from 表1，表2;
select * from class cross join student;
select * from class,student;
结果一样
内连接: inner join
- 显示内连接 select * from 表1 inner join 表2 on 关键条件;
- 隐式内连接 select * from 表1，表2 where 关联条件
//显示
SELECT * FROM class c INNER JOIN studnet s ON c.cid = s.cno;
//隐式
SELECT * FROM class c,studnet s WHERE c.cid = s.cno;
外连接: outer join
- 左外连接 select * from 表1 left outer join 表2 on 关联条件
- 右外连接 select * from 表1 right outer join 表2 on 关联条件
SELECT * FROM class c LEFT JOIN studnet s on c.cid = s.cno; ##展示出左边表的所有信息
SELECT *FROM class c RIGHT JOIN studnet s on c.cid =s.cno; ##展示出右边表的所有信息
内连接与外连接的区别:
8.3子查询
一个查询语句条件需要依赖另一个查询语句的结果。
- 带in的子查询
查询学生生日在 1994-01-01之后的班级记录
SELECT * FROM class WHERE cid IN(SELECT cno FROM studnet WHERE birthday > 1994-01-01);
- 带exists的子查询
SELECT * FROM class WHERE EXISTS(SELECT cno FROM studnet WHERE birthday > 1994-01-01);
(字句为true则执行前面的语句)
- 带any的子查询
SELECT * FROM class WHERE cid > ANY (SELECT cno FROM studnet );
- 带all的子查询
SELECT * FROM class WHERE cid > ALL(SELECT cno FROM studnet);
8.4多表查询的练习
//查询班级名称，和班级总人数
SELECT c.cname,count(*) from class c,studnet s WHERE c.cid = s.cno GROUP BY c.cname;
//查询学生所选总课程的平均成绩
SELECT s.sname,avg(c.score) from studnet s,stu_cour c WHERE s.sid = c.sno GROUP BY s.sname;
//查询学生的姓名和学生的选课总数,显示选课超过2门的学生姓名
SELECT any_value(s.sid) id, s.sname,count(*) FROM studnet s,stu_cour sc WHERE s.sid =sc.sno GROUP BY s.sname HAVING count(*) > 2;
//查询平均成绩大于80分的学生的总数
SELECT count(*) FROM studnet s WHERE s.sid IN (select sc.sno FROM stu_cour sc GROUP BY sc.sno HAVING avg(sc.score)>80);
//查看大于01班任何一个学生平均成绩的学生信息
SELECT s.sname,avg(sc.score) FROM stu_cour sc ,studnet s WHERE s.sid = sc.sno GROUP BY s.sname HAVING avg(sc.score) > ANY(SELECT avg(sc.score) FROM studnet s,stu_cour sc,class c WHERE s.sid = sc.sno AND s.cno =c.cid AND c.cname = '01班' GROUP BY s.sname);
记录一点: select 的属性 必须出现在group by中 否则会报错(在mysql5.7mysql模式sql_mode=only_full_group_by).
解决: 可以在想查询的字段前加any_value,比如 any_value(id).
具体深层次原因我也还没弄懂!
9.事务
9.1事务的基础介绍
事务:指的是逻辑上的一组操作，组成这组操作的各个逻辑单元，要么全部成功，要么全都失败。
开启事务
start transaction;
提交事务
commit;
回滚事务
rollback;
注意:事务一旦提交或回滚便会结束.
9.2事务的四大特性
- 原子性: 事务的不可分割，组成事务的各个逻辑单元不可分割.
- 一致性: 事务执行的前后，数据完整性保持一致
- 隔离性: 事务执行不应该受到其他事务的干扰
- 持久性: 事务一旦结束后，数据就持久化到数据库中
9.3事务隔离级别
隔离性: 一个事务的执行，不应该受到其它事务的干扰。如果不考虑隔离性(一个事务执行受到其他的事务干扰)，引发一些安全问题， 主要体现在读取数据上:
- 赃读: 一个事务读到了另一个事务未提交的数据，导致查询结果不一致
- 不可重复读: 一个事务读到了另一个事务已经提交的update的数据，导致多次查询结果不一致
- 虚读/幻读: 一个事务读到了另一个事务已经提交的insert的数据，导致多此查询结果不一致
设置事务的隔离级别:
- read uncommitted: 赃读，不可重复读，虚读都有可能发生
- read committed: 避免赃读。但是不可重复读和虚读都有可能发生
- repeatable read: 避免赃读和不可重复读，但是虚度有可能发生 (mysql默认的事务隔离级别)
- serializable: 避免赃读，不可重复读，虚读。事务不允许出现并发，A事务需要等待B事务(A设置为这一级别) 执行完成后，才会执行A事务
安全级别: 低 —> 高 效率: 高 ---- > 低
设置事务隔离级别
set session transaction isolation level 隔离级别
查看当前隔离级别
select @@tx_ioslation;
- 持久性: 事务一旦结束后，数据就持久化到数据库中
9.3事务隔离级别
隔离性: 一个事务的执行，不应该受到其它事务的干扰。如果不考虑隔离性(一个事务执行受到其他的事务干扰)，引发一些安全问题， 主要体现在读取数据上:
- 赃读: 一个事务读到了另一个事务未提交的数据，导致查询结果不一致
- 不可重复读: 一个事务读到了另一个事务已经提交的update的数据，导致多次查询结果不一致
- 虚读/幻读: 一个事务读到了另一个事务已经提交的insert的数据，导致多此查询结果不一致
设置事务的隔离级别:
- read uncommitted: 赃读，不可重复读，虚读都有可能发生
- read committed: 避免赃读。但是不可重复读和虚读都有可能发生
- repeatable read: 避免赃读和不可重复读，但是虚度有可能发生 (mysql默认的事务隔离级别)
- serializable: 避免赃读，不可重复读，虚读。事务不允许出现并发，A事务需要等待B事务(A设置为这一级别) 执行完成后，才会执行A事务
安全级别: 低 —> 高 效率: 高 ---- > 低
设置事务隔离级别
set session transaction isolation level 隔离级别
查看当前隔离级别
select @@tx_ioslation;
扫一扫
4610
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
