数据库
简介
数据库软件应该为数据库管理系统,数据库是通过数据库管理系统创建和操作的。
数据库学习的重点三个方面:存储、维护和管理数据的集合。
数据库管理系统:指一种操作和管理数据库的大型软件,用于建立、使用和维护数据库,对数据库进行统一管理和控制,以保证数据库的安全性和完整性。用户通过数据库管理系统访问数据库中的数据。
数据库三大范式
第一范式:无重复的列。
第二范式:属性完全依赖于主键。[消除部分子函数依赖] 要求数据库表中的每个实例或行必须可以被唯一的区分。
第三范式:属性不依赖于其它非主属性。[消除传递依赖] 要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。
注:关系实质上是一张二维表,其中每一行是一个元组,每一列是一个属性。
SQL语言
SQL语句分类
DDL:数据定义语言,用来定义数据库对象:库、表、列等
DML:数据操作语言,用来定义数据库记录(数据)增删改
DCL:数据控制语言,用来定义访问权限和安全级别。
DQL:数据查询语言,用来查询记录(数据)查询
注意:SQL语句以;结尾
mysql中的关键字不区分大小写
DDL操作数据库
创建
//create database 数据库名;
create database mydb1;
//create database 数据库名 character set 编码方式;
create database mydb2 character set GBK;
//create database 数据库名 set 编码方式 collate 排序规则;
create database mydb3 character set GBK collate gbk_chinese_ci;
查看数据库
//查询所有数据库
show database;
查看前面创建的mydb1数据库的定义信息
//show create database 数据库名;
show create database mydb2;
修改数据库
alter database 数据库名 character set 编码方式;
查看服务器中的数据库,并把mydb2的字符集修改为utf8
alter database mydb2 character set utf8;
删除数据库
//drop database 数据库名;
drop database mydb3;
其它语句
//查看当前使用的数据库
select database();
//切换数据库: use 数据库名
use mydb2;
DDL操作表
create table语句用于创建新表
语法:
create table 表名(
列名1 数据类型 [约束],
列名2 数据类型 [约束],
列名3 数据类型 [约束]
)
说明表名,列名是自定义,多列之间使用逗号间隔,最后一列的逗号不能写
[约束]表示可有可无
案例:
create table Book(
id int,
name varchar(5),
num varchar(10)
)
常用数据类型 | 格式 |
---|---|
int(整型) | / |
double(浮点型) | 例如double(5,2)表示最多两位,其中必须有2位小数,最大值默认为999.99 默认支持四舍五入 |
char(固定长度字符串类型) | char(10) ‘aaa ’ 占10位 |
varchar(可变长度字符串类型) | varchar(10) ‘aaa’ 占3位 |
text(字符串类型) | 比如简介信息 |
blob(字节类型) | 保存文件信息(视频,音频,图片) |
date(日期类型) | yyyy-MM-dd |
time(时间类型) | hh:mm:ss |
timestam(时间戳类型) | yyyy-MM-dd hh:mm:ss 会自动赋值 |
datetime(日记事件类型) | yyyy-MM-dd hh:mm:ss |
其它表操作
drop table 表名
drop table table_name;
//当前数据库中的所有表
show tables;
//查看表的字段信息
desc 表名;
例:
desc book;
//增加列:在上面图书表的基本上增加一个image列
alter table 表名 add 新列名 新的数据类型;
alter table book add image blob;
//修改num列,使其长度为60
alter table 表名 change 旧列名 新列名 新的数据类型;
alter table book change num Num varchar(60);
//列名修改为booknamealter table book change name bookname varchar(100);
//删除image列,一次只能删除一列
alter table 表名 drop 列名;
alter table book drop image;
//修改表名,表名改为books
alter table 旧表名 rename 新表名;
alter table book rename books;
//修改表的字符集为GBK
alter table 表名 character set 编码方式;
alter table books character set GBK;
DML操作
DML是对表中的数据进行增、删、改的操作。
注:不要与DDL混淆。
主要包括:insert、update、delete
小知识:在mysql中,字符串类型和日期类型都要用单引号括起来。
空值:null
插入操作:insert
//插入操作
insert into 表名(列名) values(数据值);
insert into student(studentname,stuage,stusex,birthday) values('张三',18,'a','2020-1-1');
注:多列和多个列值之间使用逗号隔开;列名要和列值一一对应;非数值的列值两侧需要加单引号
当给所有的列添加数据的时候可以将列名省略,此时列值的顺序按照数据表中列的顺序执行
//同时添加多行
insert into 表名(列名) values(第一行数据),(第二行数据),(),()....;
注意:列名与列值的类型、个数、顺序要一一对应,参数值不要超过列定义的长度,如果插入值为空,要使用null,插入的日期和字符一样,都是用引号括起来。
sql中的运算符:
1.算数运算符:+ - * / %
2.赋值运算符:=
注:赋值方向 从右往左赋值
3.逻辑运算符:
and(并且),or(或者),not(取非)
作用:用于连接多个条件时使用
4.关系运算符:>,<,>=,<=,!=,=,<>(不等于)
补:查询所有数据:select * from 表名;
更改操作: update
update 表名 set 列名1 = 列值1,列名2 = 列值2 .... where 列名 = 值;
删除操作:delete
delete from 表名 where 列名 = 值;
//使用truncate删除表中记录
truncate table student;
delete删除表中的数据,表结构还在,删除后的数据可以找回
truncate删除是把表直接drop掉,然后再创建一个同样的新表,删除的数据不能找回,执行速度比delete快。
DCL
1.创建用户
语法:create user 用户名@指定ip identified by 密码;
create user test123@localhost identified by 'test123';
语法:create user 用户名@客户端ip identified by 密码;指定IP才能登录
create user test456@10.4.10.18 identified by 'test456';
语法:create user 用户名@'%' identified by 密码;任意IP均可登录
create user test7@'%' identified by 'test7';
2.用户授权
grant 权限1,权限2.....,权限n on
数据库名.* to 用户名@IP;给指定用户授予指定数据库指定权限;
grant select,insert,update,delete,create on chaoshi.* to 'test456'@'127.0.0.1';
grant all on .to 用户名@IP;给指定用户授予所有数据库所有权限
grant all on *.* to 'test456'@'127.0.0.1';
3.用户权限查询
show grants for 用户名@IP;show grants for 'root'@'%';
4.撤销用户权限
revoke 权限1,权限2,.......,权限n on 数据库名.* from 用户名@IP;revoke select on *.* from 'root'@'%';
5.删除用户
drop user 用户名@IP;drop user test123@localhost;
DQL数据查询
DQL数据查询语言(重要)数据库执行DQL语句不会对数据进行改变,而是让数据库发送结果集给客户端。查询返回的结果集是一张虚拟表查询关键字:select
语法:select 列名 from 表名 where broup by having order by * 表示所有列; select 要查询的列名称 from 表名称 where 限定条件 /*行条件*/ group by grouping_columns /*对结果分组*/ having condition /*分组后的行条件*/ order by sorting_columns /*对结果进行分组*/ limit offset_start,row_count; /*结果限定*/
1.简单查询
//查询所有列
select * from stu;
//查询指定列
select sid,sname,age from stu;
2.条件查询
条件查询就是在查询时给出WHERE子句,在WHERE子句中可以使用如下运算符及关键字: =、!=、<>、<、<=、>、>=; BETWEEN…AND; IN(set); IS NULL; AND;OR; NOT;
范围查询:列名 in (列值1,列值2)
列名 between 开始值 and 结束值 注意:开始值<结束值;包含临界值的
3.模糊查询
当想查询姓名中包含a字母的学生时就需要使用模糊查询了。模糊查询需要使用关键字like。
语法: 列名 like '表达式'; //表达式必须是字符串
通配符:
_(下划线):任意一个字符
%:任意0-n个字符,'张%'
4.字段控制查询
//创建雇员表
CREATE TABLE emp2( empno INT, ename VARCHAR(50), job VARCHAR(50), mgr INT, hiredate DATE, sal DECIMAL(7,2), comm decimal(7,2), deptno INT ) ;
添加数据
INSERT INTO emp2 values(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20); INSERT INTO emp2 values(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30); INSERT INTO emp2 values(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30); INSERT INTO emp2 values(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20); INSERT INTO emp2 values(7654,'MARTIN','SALESMAN',7698,'1981-09- 28',1250,1400,30);
INSERT INTO emp2 values(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30); INSERT INTO emp2 values(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10); INSERT INTO emp2 values(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20); INSERT INTO emp2 values(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
1.去除重复记录
去除重复记录(两行或两行以上记录中系列的上的数据都相同)。想去除重复记录,就需要使用distinct
select distinct 列名 from 表名;
2.查看雇员的月薪与佣金之和
因为sal和comm两列的类型都是数值类型,所以可以做加运算。如果sal或comm中有一个字段不 是数值类型,那么会出错。
SELECT *,sal+comm FROM emp;
comm列有很多记录的值为NULL,因为任何东西与NULL相加结果还是NULL,所以结算结果可能会出 现NULL。下面使用了把NULL转换成数值0的函数IFNULL:
SELECT *,sal+IFNULL(comm,0) FROM emp;
3.给列名添加别名
在上面查询中出现列名为sal+IFNULL(comm,0),这很不美观,现在我们给这一列给出一个别名,为
total:
SELECT *, sal+IFNULL(comm,0) AS total FROM emp;
给列起别名时,是可以省略AS关键字的:
SELECT *,sal+IFNULL(comm,0) total FROM emp;
5.排序
语法:order by 列名 asc(升序)/desc(降序)
1.按升序排序
select * from 表名 order by 列名 asc;
或者:
select * from 表名 order by 列名;
2.按降序排列
select * from 表名 order by 列名 desc;
3.查询所有雇员,按xx列降序排序,如果月薪相同时,按xx列升序排序
多列排序:当前面的列的值相同的时候,才会按照后面的列值进行排序
select * from emp order by 列名 desc,列名 desc;
6.聚合函数
聚合函数是用来做纵向运算的函数:
COUNT(列名):统计指定列不为NULL的记录行数;
MAX(列名):计算指定列的最大值,如果指定列是字符串类型,那么使用字符串排序运算;
MIN(列名):计算指定列的最小值,如果指定列是字符串类型,那么使用字符串排序运算;
SUM(列名):计算指定列的数值和,如果指定列类型不是数值类型,那么计算结果为0;
AVG(列名):计算指定列的平均值,如果指定列类型不是数值类型,那么计算结果为0;
1.count 当需要纵向统计时可以使用count()
select count(*) as 列名 from 表名;
注意:count()函数只统计一个列中非null的行数
2.sum和avg 当需要总想求和时使用sum()函数
select sum(列名) from 表名;
3.max和min 查询最高值和最低值
select max(列名),min(列名) from 表名;
7.分组查询
当需要分组查询时需要使用group by子句。
注意:如果查询语句中有分组操作,则select后面能添加的只能是聚合函数和被分组的列名
having子句
having与where的区别
having是在分组后对数据进行过滤,where是在分组前对数据进行过滤
having后面可以使用分组函数(统计函数),where后面不可以使用分组函数
where是对分组前记录的条件,如果某行记录没有满足where子句的条件,那么这行记录不会参加分组,而having是对分组后数据的约束
having出现时必须有group by
8.Limit
limit用来限定查询结果的起始行,以及总行数。
limit开始下标,显示条数;//开始下标从0开始
limit显示条数;//表示默认从0开始获取数据
1.查询5行记录,起始行从0开始
select * from 表名 limit 0,5;
2.查询10行记录,起始行从3开始
select * from 表名 limit 3,10;
8.1分页查询
如果一页记录为10条,希望查看第3页记录应该怎么查?
第一页记录起始行为0,一共查询10行; limit 0,10
第一页记录起始行为10,一共查询10行; limit 10,10
第一页记录起始行为20,一共查询10行; limit 20,10
pageIndex 页码值 pageSize 每页显示条数
limit (pageindex-1) * pagesize,pagesize;
查询语句书写顺序:select from where group by having order by limit
查询语句执行顺序:from where group by having select order by limit