目录
5.3 is null / is not null 查询条件:
1.SQL语句的分类
DQL(数据查询语言):查询语句,凡是select语句都是DQL。
DML(数据操作语言):insert delete update,对表当中的数据进行增删改。
DDL(数据定义语言):create drop alter,对表结构的增改。
TCL(事务控制语言):commit提交事务,rollback回滚事务。
DCL(数据控制语言):grant授权、revoke撤销权限等。
2.MySQL数据类型
2.1数值类型:
备注:double(5,2) 表示总共5位,小数点后2位;
2.2日期和时间类型:
备注:如果使用TIMESTAMP类型,则如果不赋值或者赋值为NULL,则默认保存系统当前时间。
2.3字符串类型:
备注:varchar(20) 表示最大20个字符;
3.MySQL常见基础命令
3.1远程连接MySQL:
mysql -h127.0.0.1 -uroot -p
3.2建库:
create database [if not exists] 数据库名 [character set 字符集名];
[if not exists]:判断是否存在,防止重复创建,可以省略不写;
[character set 字符集名]:指定字符集,可以省略不写;
3.3显示所有数据库:
show databases;
3.4查询创建数据库时使用的语法:
show create database 数据库名;
3.5修改字符集:
alter database 数据库名 character set 字符集名;
3.6删除数据库:
drop database 数据库名 [if exists] 数据库名;
[if exists]:判断是否存在,防止重复创建,可以省略不写;
3.7查询当前正在使用的数据库名称:
select database();
3.8使用数据库:
use 数据库名;
4.简单的查询
4.1单个字段查询:
select field1 from tableName;
4.2多个字段查询:
select field1, field2, field3, ... from tableName;
4.3全部字段查询:
select * from tableName;
4.4查询的字段参与数学运算:
select field1*20 from tableName;
4.5查询结果起别名:
select field1*20 as result from tableName;
备注:
1.SQL语句以‘;’结束;
2.SQL语句不区分大小写;
3.SQL语句中别名as可以不写;
4.标准SQL语句中的字符串用英文单引号括起来;
5.条件查询
5.1运算符查询条件:
select * from student where age = 18; --查询age = 18的学生信息
select * from student where age > 18; --查询age > 18的学生信息
select * from student where age < 18; --查询age < 18的学生信息
select * from student where age <> 18; --查询age <> 18的学生信息
select * from student where age >= 18; --查询age >= 18的学生信息
select * from student where age <= 18; --查询age <= 18的学生信息
5.2 between...and...查询条件:
select * from student where age between 18 and 22; --查询age 为 [18, 22] 的学生信息
备注:between...and...还可以用于字符串
select * from student where name between ‘A’ and ‘C’; --查询name为 [‘A’, ‘C’)的学生信息
5.3 is null / is not null 查询条件:
select * from student where age is null; --查询年龄为空的学生信息
select * from student where age is not null; --查询年龄不为空的学生信息
备注:空值在数据库中不是一个值,不能用等号衡量,只能用is null 或者is not null
5.4逻辑运算符查询条件:
select * from student where age >= 18 and age <= 20;
--查询age范围在18到20岁的学生信息
select * from student where age >= 18 or age <= 12;
--查询age大于18或者小于12岁的学生信息
5.5集合查询条件:
select * from student where age in (18,19,20,25);
--查询age等于18,19,20,25的学生信息
select * from student where age not in (18,19,20,25);
--查询age不等于18,19,20,25的学生信息
5.6 like 模糊查询:
select * from student where name like ‘%o%’;
--查询name中包含字母o的学生信息
select * from student where name like ‘_o%’;
--查询name中,第二个字母为o的学生信息
select * from student where name like ‘__o%’;
--查询name中,第三个字母为o的学生信息
select * from student where name like ‘%\_o%’;
--查询name中,第三个字母为o的学生信息
备注:%通配所有字符,_通配单个字符,\转译
6.排序查询
6.1单一字段排序:
select * from student order by age;
--查询学生信息表,按照年龄字段排序,默认升序
6.2手动指定排序顺序:
select * from student order by age asc;
--查询学生信息表,按照年龄字段升序排序
select * from student order by age desc;
--查询学生信息表,按照年龄字段降序排序
6.3多个字段排序:
select * from student order by age asc, name desc;
--查询学生信息表,按照年龄字段升序排序、姓名降序排序
6.4使用字段的位置来排序:
select * from student order by 1;
--查询学生信息表,按照student表中第一个字段升序排序
7.分组查询
7.1计数查询count:
select count(*) from student;
--查询学生信息表,统计一共有多少个学生
7.2 求和查询sum:
select sum(score) from student;
--查询学生信息表,查询学生得分总和
7.3 平均数查询avg:
select avg(score) from student;
--查询学生信息表,查询学生的平均成绩
7.4最大值查询max:
select max(score) from student;
--查询学生信息表,查询学生的最高得分
7.5 最小值查询min:
select min(score) from student;
--查询学生信息表,查询学生的最低得分
7.6 组合聚合函数:
select count(*), sum(score), avg(score), max(score), min(score) from student;
--查询学生信息表,组合使用分组函数
备注:
(1)分组函数不可直接使用在where子句中;
(2)分组函数会自动忽略null;
(3)count(*)表示统计所有数据的条数;
count(filed1)表示统计filed1字段不为空 的数据的条数;
7.7 group by 查询:
select max(score), subject from student group by subject;
--查询学生信息表,按照学科分组并查询每个学科的最好成绩
7.8多个字段联合分组查询:
select max(score), subject, sex from student group by subject, sex;
--查询学生信息表,按照学科、性别分组并查询每个学科+性别的最好成绩
7.9 having查询:
select max(score), subject from student group by subject having max(score) > 90;
--查询学生信息表,按照学科分组并查询每个学科的最好成绩以及最好成绩超过90分的学科
备注:
(1)当SQL语句有group by时,那么select后面只能跟分组函数和参加分组的字段
8.去重查询
select distinct subject from student;
--去重查询学生信息表中的学科
备注:
distinct关键字只能出现在所有字段的最前方;
9.连接查询
9.1内连接之等值连接:
含义:等值连接最大的特点是条件是等量关系
SQL92:
select e.ename, d.dname from emp e, dept d where e.deptno = d.deptno
SQL99:
select e.ename, d.dname from emp e [inner] join dept d on e.deptno = d.deptno
9.2内连接之非等值连接:
含义:非等值连接最大的特点是条件是非等量关系
select e.ename, e.sal, s.grade from emp e [inner] join salgrade s on e.sal between s.losal and s.hisal;
9.3内连接之自连接:
含义:自连接最大的特点是一张表看做两张表,自己连接自己
select a.ename, b.ename from emp a [inner] join emp b on a.mgr = b.empno;
9.4外连接之左连接:
select a.ename, b.ename from emp a left [outer] join emp b on a.mgr = b.empno;
备注:左连接,以左侧的表为主表,右侧表为辅表;主表的数据会被百分百查出,辅表中和主表相关的数据会被查出,其他的数据不会被查出
9.5外连接之右连接:
select a.ename, b.ename from emp b right [outer] join emp a on a.mgr = b.empno;
备注:右连接,以右侧的表为主表,左侧表为辅表;主表的数据会被百分百查出,辅表中和主表相关的数据会被查出,其他的数据不会被查出
9.6三张表查询:
select e.ename, d.dname , s.grade from emp e join dept d
on e.deptno = d.deptno
join salgrade s
on e.sal between s.losal and s.hisal;
10.子查询
10.1 where语句后嵌套子查询
select * from student where age > (select avg(age)from student);
--查出年龄大于平均年龄的学生信息
10.2 from 语句后嵌套子查询
select t.*, s.grade from (select deptno, avg(sal) as avgsal from emp group by deptno) t join salgrade s on t.avgsal between s.losal and s.hisal;
10.3 select 语句后嵌套子查询
select e.ename, (select d.dname from dept d where e.deptno = d.deptno) as dname from emp e;
11. union的用法
select ename from emp
union
select dname from dept;
备注:使用union时,前后两个语句查询字段的数量必须一致
12. limit的用法
limit startindex, length --> 起始位置,查询的个数
select * from emp order by sal desc limit 3,6
备注:limit在SQL执行中,最后执行
13.表结构操作
13.1新建一张表
create table t_student(
no bigint,
name varchar(255),
sex char(1),
classno varchar(255),
birth char(1)
);
13.2向表中插入数据(insert)
方式一(插入全量的字段和值):insert into t_student(name,sex,classno,birth,no) values('lisi','1','gaosan1ban','1950-10-12',2);
方式二(只插入值):insert into t_student values (1,'jack','','gaosan2ban');
方式三(批量插入):
insert into t_student
(no ,name,sex,classno ,birth)
values
(3,'rose',1''gaosi2ban','1952-12-14'),
(4,laotie','1',gaosi2ban','1955-12-14');
13.3修改表中的数据(update)
update dept1 set loc = 'SHANGHAI',dname ='RENSHIBU' where deptno = 10;
13.4删除数据(delete)
delete from student where a>=18;
13.5删除数据量大的表(表截断-truncate)
truncate table student;
13.6表结构修改(alter)
(1)添加列:ALTER TABLE student ADD age INT;
(2)删除列:ALTER TABLE student DROP COLUMN age;
(3)修改列数据类型:ALTER TABLE student MODIFY age VARCHAR(50);
(4)修改列名:ALTER TABLE table_name CHANGE old_column new_column column_definition;
(5)修改表的名称:RENAME TABLE student TO user;
14.约束
14.1非空约束(not null):
create table t_user(
id int,
username varchar (255) not null
password varchar(255)
);
14.2唯一约束(unique):
唯一约束修饰的字段具有唯一性,不能重复。但可以为NULL。
create table t_user (
id int,
username varchar(255) unique
);
14.3主键约束(primary key):
被添加主键约束的字段,不能重复,不能为null,具有唯一性。
create table t_user(
id int primary key,
username varchar(255),
email varchar(255)
);
主键自增:
create table t_user(
id int primary key auto increment,
username varchar(255)
);
14.4外键约束(foreign key):
子表中的外键值可以为null。
--父表
create table t_class (
cno int,
cname varchar(255),
primary key(cno)
);
--子表,子表中的classno字段的值必须存在于父表中,不然无法被添加
create table t_student(
sno int,
sname varchar(255),
classno int,
foreign key(classno) references t_class (cno)
);
15.存储引擎
15.1存储引擎的含义:
存储引擎即表的存储方式,存储引擎只是在MySQL中的说法,在Oracle中叫存储方式。
15.2 MySQL中常见的存储引擎:
1.MYISAM:
--不支持事务
--使用三个文件表示每个表:
a.格式文件 - 存储表结构的定义(mytable.frm)
b.数据文件 - 存储表行的内容(mytable.MYD)
c.索引文件 - 存储表上索引(mytable.MYI)
--灵活的AUTO_INCREMENT字段处理
--可被转换为压缩、只读表来节省空间
2.InnoDB
--InnoDB 存储引整是 MySQL 的缺省引整。
--它管理的表具有下列主要特征:
a.每个InnoDB 表在数据库目录中以.frm 格式文件表示
b.InnoDB 表空间 tablespace 被用于存储表的内容
c.提供一组用来记录事务性活动的日志文件
d.用 COMMIT(提交1、SAVEPOINT 及 ROLLBACK(回滚)支持事务处理
e.提供全 ACID 兼容
f.在 MySQL服务器崩溃后提供自动恢复
g.多版本(MVCC)和行级锁定
h.支持外键及引用的完整性,包括级联删除和更新
3.MEMORY
--不支持事务,无法回滚
--不支持CLOG和BLOG数据
--数据存储在内存中,数据容易丢失
--查询速度快
16.事务
16.1事务的含义:
一个事务是一个完整的业务逻辑单元,不可再分。
16.2事务的原理:
1.开启事务机制(开始)
2.执行insert语句-->insert....(这个执行成功之后,把这个执行记录到数据库的操作历史当中,并不会向文件中保存条数据,不会真正的修改硬盘上的数据。)
3.执行update语句--> update...(这个执行也是记录一下历史操作,不会真正的修改硬盘上的数据。)
4.执行delete语句--> delete...(这个执行也是记录一下历史操作[记录到缓存]不会真正的修改硬盘上的数据。)
5.提交事务或者回滚事务(结束)
6.删除历史记录。
16.3事务的特性:
A:原子性:事务是最小的工作单元,不可再分。
C:一致性:事务必须保证多条DML语句同时成功或者同时失败。
I: 隔离性:事务A和事务B之间具有隔离性。
D:持久性:最终数据必须持久化到硬盘文件中,书屋才算成功的结束。
16.4事务的隔离性:
第一级别:读未提交(read uncommitted),对方事务还未提交,我们当前事务可以读取到对方未提交的数据;读未提交存在脏读 (Dirty Read)现象:表示读到了脏的数据。
第二级别:读已提交(read committed),对方事务提交之后的数据我方可以读取到。读已提交存在的问题是:不可重复读。
第三级别:可重复读(repeatable read),这种隔离级别解决了,不可重复度的问题。这种隔离级别存在的问题是:读取到的数据是幻象。
第四级别:序列化读,解决了所有问题。存在的问题:效率低,需要事务排队。
17.索引
17.1索引的概念:
索引就相当于一本书的目录,通过目录可以快速的找到对应的资源。
17.2创建&删除索引:
创建索引对象:
create index 素引名称 on 表名(字段名);
create index emp sal index on emp(sal) ;
删除索引对象:
drop index 索引名称;
17.3添加索引的场景:
a.数据量庞大
b.该字段很少的DML操作
c.该字段经常出现在where子句中
备注:主键和具有unique约束的字段自动会添加索引,所以建议根据主键检索
17.4索引的分类:
单一索引:给单个字段添加索引
复合索引:给多个字段联合起来添加一个索引
主键索引:主键上会自动添加索引
唯一索引:有unique约束的字段上会自动添加索引
18.视图
18.1视图的概念:
站在不同的角度去看到数据。 (同一张表的数据,通过不同的角度去看待)
18.2视图的语法:
创建视图:create view myview as select empno, ename from emp;
删除视图:drop view myview;
18.3视图的作用:
视图可以隐藏表的实现细节。保密级别较高的系统,数据库只对外提供相关的视图,java程序员只对视图对象进行CRUD。
19.MySQL导入导出
19.1导出:
在windows的dos命令窗口中执行:
mysqldump bjpowernode>D:\bjpowernode.sql -uroot -p333
19.2导入:
create database bjpowernode;--创建数据库
use bjpowernode;--使用数据库
source D:\bjpowernode.sql--导入
20.数据库设计三范式
20.1范式的概念:
设计表的依据。按照这个三范式设计的表不会出现数据冗余。
20.2三范式的介绍:
第一范式:任何一张表都应该有主键,并且每一个字段原子性不可再分。
第二范式:建立在第一范式的基础之上,所有非主键字段完全依赖主键,不能产生部分依赖。
第三范式:建立在第二范式的基础之上,所有非主键字段直接依赖主键,不能产生传递依赖。