数据库概述及单表操作

数据库概述:

• 什么是数据库: 存放数据和管理的仓库

  • 数据库就是一个文件系统,只不过我们需要通过命令(SQL)来操作这个文件系统

  • 数据库(Database)是按照数据结构来组织、存储和管理数据的建立在计算机存储设备上的仓库。

  • 数据库是长期储存在计算机内、有组织的、可共享的数据集合。数据库中的数据指的是以一定的数据模型组织、描述和储存在一起、具有尽可能小的冗余度、较高的数据独立性和易扩展性的特点并可在一定范围内为多个用户共享。​

常见的数据库:

关系型数据库:

mysql : 小型.开源免费,大多数中小型互联网公司都在使用这个,sun公司收购了 (集群),5.5以前的都是免费的,6.X之后的版本都是收费的

mariadb: 开源免费数据, 和mysql基本上一模一样,但是它是免费的,并且在不停的更新,是mysql开源版本的一个分支

Oracle : 中大型的数据库,服务收费的,安全稳定. 证券,银行,保险公司, Oracle收购了sun公司, mysql6.x之后就开始收费了

SQL Server : 大学里面教学使用比较多, 政府官网, 图形化界面做的很好 c#

DB2 : IBM公司, 主要运用在银行系统, 服务器软硬件解决方案

sybase : powerdesinger 数据建模工具

SQLite : 运用在嵌入式系统中的小型数据库,android

查看数据库排名网站: https://db-engines.com/en/ranking

​

关系型数据库: E-R关系图

      E:Entity实体

      R:relation关系

      主要是用来存储实体与实体之间的关系:

      例如用户和订单之间的关系,订单和商品之间的关系,学生和班级. 学生和课程 老师和学生等

MYSQL的安装和卸载

• 卸载

  • 在控制面板中删除mysql

  • 要将安装路径下的: C:\Program Files\MySQL 文件夹全部删除

  • 要将C:\ProgramData\MySQL 文件夹删除

  • 注意:如果删除完之后,最好到注册表查看是否还有所残留,如果都删除完,建议电脑重启后安装

  • 注册表进入方式 window + r-->运行窗口输入 regedit --> ctrl+f 搜索MySQL

• 安装

  • 字符集必须是设置为UTF8

  • include bin dirctory in windows path 前面的勾必须勾上

  • 密码必须得自己记住

MYSQL的SQL语句

​         SQL: Structrued Query Langauage 结构化的查询语言

​         DDL: 数据定义语言 : 定义数据库或者表结构. alter(修改) create(创建) drop(删除) truncate(截断)

​         DML: 数据操纵语言: 用来操作表中数据: insert(插入) update(更新) delete(删除)

        ​ DQL: 数据查询语言: 用来查询数据 select(选择) from(从哪里查) where(哪一条,判断条件)

​         DCL: 数据控制语言: 用来控制用户权限 grant(授权) revoke(取消授权)

DBA

        数据库管理员（Database Administrator，简称DBA），是从事管理和维护数据库管理系统(DBMS)的相关工作        人员的统称，属于运维工程师的一个分支，主要负责业务数据库从设计、测试到部署交付的全生命周期管理。

        登录数据库: 

            方式一: mysql -u root -p 按回车后提示后输入密码

            方式二:mysql -uroot -p自己的密码

        完整格式:

            mysql -h主机地址 -u用户名 -p密码

            连接本机: mysql -hlocalhost -uroot -p123456

            注意:不写-hlocalhost代表默认访问本机数据库服务器

            -u username 用户名

            -p password密码

数据库的CRUD的操作

CRUD是指在做计算处理时的增加(Create)、读取查询(Retrieve)、更新(Update)和删除(Delete)几个单词的首字母简写。主要被用在描述软件系统中数据库或者持久层的基本操作功能。

创建数据库

create database 数据库的名字 ---用的非常多的方式create database day08_1;create database 数据库的名字 character set 字符集; -- 了解create database day08_2 character set gbk;create database 数据库的名字 character set 字符集 collate 校对规则; -- 了解​create database day08_3 character set utf8 collate utf8_bin;

查看数据库

-- 查看数据库定义的语句: 查询的是数据库创建的语句show create database 数据库的名字--查看所有数据库show databases;

修改数据库的操作

--修改数据的字符集alter database 数据库的名字  character set 字符集

删除数据库

drop database 数据库名字;

其他数据库操作命令

--切换数据库(选中数据库)use 数据库名字--当前选中是哪个数据库,当前正在使用哪个数据库select database();

DBA 数据库管理员

表的CRUD操作

创建表

语法:    create table 表名(列名 列的类型 [列的约束], 列名 列的类型 [列的约束]);列名:也称为字段名[] 代表的可有可无​    

create table 表名(列名1 列的类型 [列的约束],列名2 列的类型 [列的约束]);    

列的类型java   mysqlbyte  TinyIntshort  SMALLINTint   intfloat   floatdouble  doubleString   char(长度)/varchar(长度)                    char(10): 固定长度字符 输入hello    没有填满用空格补齐10个                    char(5):haha存储的时候不足会补齐空格                    varchar(10):可变长度字符 要存储hello  只占5个字符,在数据库存储的时候就存储5个,不会以空格补齐                            都不能超过最大长度                            可以存放10个汉字 ,10个字符                    char 和varchar:区别主要体现在存储字符时候,如果是char存储类型,不足的字符会以空格存储,varchar则不会​boolean             booleanDate                date : YYYY-MM-DD 年/月/日                    time : hh:mm:ss 时:分:秒                    datetime: YYYY-MM-DD hh:mm:ss  默认值是null 年/月/日 时/分/秒                    timestamp:YYYY-MM-DD hh:mm:ss  默认值是当前的时间, 最大取值是2038年.时间戳                                        text: 大文本类型                    blob: 二进制大对象类型  大电影 java-存放和读取                    创建员工(employee)表: 员工编号empno,员工名称ename,工资sal,部门编号deptno create table employee(   empno int,   ename varchar(20),   sal double,   deptno int );​列的约束    主键约束: primary key不能重复,不能为空  通常用在编号ID    唯一约束: unique 唯一约束: 不能重复,但是可以为空    非空约束: not null 不能为空create table employee2(    empnot int primary key auto_increment, -- ID为主键 并且唯一不能重复 也不能为空 自动增长    ename varchar(20) unique,    sal double,     deptno int not null);​​auto_increment 自动增长属性,通常是加载编号上的

查看表

--  查看所有的表    show tables;

--  查看表的创建过程,建表语句    show create table 表名

--  查看表结构    desc table

修改表

添加列(add),修改列(modify),修改列名(change),删除列(drop),修改表名(rename),修改表的字符集

添加列(add)  alter table 表名 add 列名 列名类型

--添加性别alter table employee add gender varchar(2);

​修改列(modify) alter table 表名 modify 列名 列的类型

-- 重定义列的类型alter table employee modify gender varchar(4); ​

修改列名(change)  alter table 表名 change 旧的列名 新列名 列的类型

-- 将gender 改成 sex alter table employee change gender sex varchar(4);​

删除列(drop) alter table 表名 drop 列名;alter table employee drop sex;​

修改表名(rename) rename table 旧表名 to 新表名rename table employee to emp;​

修改表的字符集 alter table 表名 character set 字符集alter table emp character set 'utf8';

删除表

drop table 表名drop table emp;

Sql完成对表中数据的CRUD的操作

插入数据

--创建一张表-- 学生表 : 学号, 姓名,性别create table stu(stuno int primary key auto_increment, sname varchar(20), gender varchar(2));auto_increment:自动增长,插入一条记录则增加1

​​​--指定列名插入:insert into 表名(列名1,列名2) values(值1,值2)注意: 列名的数量和值的数量要一一对应方式一:

insert into 表名(字段名1,字段名2) values(值1,值2);insert into stu(sname,gender) values('zs','ma');

insert into stu(sname,gender) values('ls','nv');

方式二:insert into 表名 values(值1,值2,值3)注意:如果不写列名,那么在插入数据的时候,要根据表的所有字段一一对应插入;

insert into stu values(3,'ww','nv');insert into stu values('zl','na');  

-- 错误:ERROR 1136 (21S01): Column count doesn't match value count at row 1insert into stu values(1,'tq','na'); 

--错误:主键重复 ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'insert into stu values(4,'tq','nan'); 

--错误:字符超出长度 ERROR 1406 (22001): Data too long for column 'gender' at row 1insert into stu values(4,'tq','男'); 

-- 错误:ERROR 1366 (HY000): Incorrect string value: '\xC4\xD0' for column 'gender' at row 1insert into stu(stuno,sname) values(1,'zs');insert into stu(stuno,sname) values(null,'lisi');​

--不指定列名插入:必须按照顺序,把表中所有的列的数据都填上,如果不填必须填nullinsert into stu values(null,'wangwu','nv');

删除记录

delete from 表名 [ where 条件 ]​--删除 stuno = 1的学生信息delete from stu where stuno=1;​

--全部删除  , 逐条删除数据delete from stu; ​delete删除表数据和truncate删除表数据区别?区别一:

使用delete这种方式删除,自增id会继续自增下去,不会重置为0开始;truncate 表: 直接整个先删除再重新创建表,主键id直接从0开始

区别二:truncate属于DDL: 数据定义语言delete属于DML:数据操纵语言

区别三:在事务使用中,delete删除表数据可以恢复,但是truncate不可以;

更新表记录

update 表名 set 列名=列的值,列名2=列的值2  [where 条件]

需求:修改6号胡八的删除结果为1update stu set isDelete=1 where stuno=6;

查询记录

数据准备

--准备一张商品分类表(分类ID,分类名称,分类描述)create table category(cid int primary key auto_increment, cname varchar(10) not null,    cdesc varchar(100));​

--准备一张商品表(商品编号,商品名称,商品价格,商品描述,商品分类编号)create table product( pid int primary key auto_increment,pname varchar(10) not null,price double,pdesc varchar(20), cno int);

--数据的准备insert into category values(null,'手机数码','黑马生产的小手机');

insert into category values(null,'鞋靴箱包','传智生产的包包');

insert into category values(null,'香烟酒水','黄鹤楼,茅台');

insert into category values(null,'馋嘴零食','卫龙辣条,周黑鸭');​

insert into product values(null,'锤子',2999,'可以砸榴莲的手机',1);

insert into product values(null,'小米',1599,'为发烧而生',1);

insert into product values(null,'李宁',99,'不走寻常路',2);

insert into product values(null,'耐克',399,'just do it',2);

insert into product values(null,'黄鹤楼',20,'饭后一根烟,胜做活神仙',3);

insert into product values(null,'卫龙辣条',5,'卫龙辣条加料不加价',4);

insert into product values(null,'老干妈辣椒酱',9,'永远不变的味道',4);

insert into product values(null,'老干娘辣椒酱',19,'永远不变的味道',4);​

insert into product values(null,'黄鹤楼',null,'饭后一根烟,胜做活神仙',3);

简单查询

查询语句的基本结构:   select 列名,列名... from 表名​ 只是查询出结果,不会改变数据库里面原有的内容--简单查询:

---查询所有商品信息:select * from product;select * from category;

---查询商品名称和商品价格:select pname,price from product;

别名查询

表别名语法 : select 列名1,列名2.....from 表名 as 别名;

列别名语法: select 列名1 as 列别名1,列名2 as 列别名2 .... from 表名

注意: as可以省略不写

    --表别名: 多表查询里面    select * from product as p;     

select p.pname,p.price from product  p;     

select pname,price from product  p; ​    

--列别名:     select pname as 商品名称,price as 商品价格 from product;    

select pname 商品名称,price 商品价格 from product;​

---去掉重复的值 distinct    

--查询所有商品的名称    select pname from product;    

--去除重复的商品名称    select distinct pname from product;    select distinct * from product; 

-- 要求每一列都相同的才去除重复    select distinct pname,price,pdesc,cno from product;   

-- 多列去除重复的, 必须是每一列都一样才算重复 

运算查询

运算符 + - * /

-- 查询商品折后价select pname as 商品名称,price * 0.75 as 折后价 from product;

条件查询/逻辑查询/比较查询/模糊查询

select 要显示的列名 from 从哪张表里面去查询数据 [where 条件:查询的是哪条记录]

• 语法格式 : select distinct [*] from 表名 [where 条件]

• 关系/比较运算符: > >= = < <= != <>

• 逻辑运算: and , or , not

• 其他运算符:

  is null 判断是否是空

  is not null 判断是否不为空in (集合) 在某个范围内 between..and.. 在某个区间内 ,包含两端 闭区间 [10,100] (10,100)

-- 查询商品价格>60元的所有商品信息select * from product where price > 60;

-- 查询商品cno为4,价格为9的商品select * from product where cno=4 and price=9;​

-- where 后的条件写法    --查询商品价格不等于99的所有商品    

select * from product where price != 99;    

select * from product where price <> 99;      

--查询出商品价格不为null的所有商品    

select * from product where price != null; --错误     

select * from product where price is not null;​    

-- 查询商品价格在10 到 100之间 price >=10 and price <=100;    

select * from product where  price >=10 and price <=100;    

select * from product where price between 10 and 100; -- 正确书写方式    

注意:    select * from product where price between 100 and 10; 

-- 错误书写    -- 查询出商品价格 小于35 或者商品价格 大于900    

select * from product where price <35 or price >900;

​​-- like: 模糊查询          %  匹配多个字符        _  匹配单个字符​    

-- 查询出名字中带有辣的所有商品:      select * from product where pname like '%辣%';            

-- 查询出名字第三个字是辣字的所有商品信息    select * from product where pname like '__辣_'; -- 占位    

select * from product where pname like '__辣%';  ​    

-- in 判断值是否在某个集合/范围          

--查询出商品分类ID在 1,3,4里面的所有商品    

select * from product where cno in (1,3,4);    

select * from product where cno in (7,8,9); 

排序查询

--排序查询: order by 关键字/列名语法:select distinct [*] from 表名 where 列名 条件 order by 列名 asc/desc;

升序: asc   ascend  默认是升序降序: desc  descend. 

查询所有商品,按照价格进行排序select * from product order by price asc;​

查询所有的商品,按价格进行降序排序 (asc-升序  desc-降序);select * from product order by price desc;

查询名称有 辣 的商品,按价格降序排序. 查询出所有 辣的商品select * from product where pname like '%辣%'

排序select * from product where pname like '%辣%' order by price desc;

聚合函数查询

常见聚合函数:

​ sum() 求和

​ avg()求平均值

​ count()统计个数

​ max()最大值

​ min()最小值

--1.获得所有商品价格的总和: select sum(price) from product;

.获得所有商品的平均价格: select avg(price) as 平均价 from product;

获得所有商品的个数: select count(pid) from product;

select count(*) from product;select count(1) from product; 

只是代表一个常量,SQL语句优化​--注意: where 条件后面不能接聚合函数 having 后面可以加​-- 找出商品 > 平均价格的商品

select * from product where price > avg(price); 

-- 错误: where后面不能接聚合函数 ERROR 1111 (HY000): Invalid use of group function

分组查询

--分组: group by

select 分组的条件,分组之后的操作 from 表名 group by 分组条件 having 条件过滤;

注意:聚合函数可以书写在having后面,但是不能书写在where 条件后面

--1.根据cno字段分组,分组后统计商品的个数select cno as 类别,count(1) 总数 from product group by cno;​

--分组之后的条件过滤: having--2.根据cno分组,分组统计每组商品的平均价格 并且商品平均价格 > 60select cno 类别,avg(price) 平均价格 from product group by cno having avg(price) > 60;

 -- 注意:聚合函数可以书写在having后面,但是不能书写在where 条件后面select cno 类别,avg(price) 平均价格 from product group by cno;select pname from product group by cno having avg(price) > 60;

SQL语句编写顺序及查询顺序

SQL编写顺序

​ select ... From .. where .. group by ... having ....order by

​

SQL执行顺序

F..W..G..H..S..O from ... where .. group by ... having ...select ...order by 1,先from去查找表 2,根据where的条件过滤 3,where条件过滤完之后再group by分组 4,having是对分组的数据进行过滤 5,执行select查询 6,最后order by排