CGB2105第二阶段-day01数据库函数应用01

目录

 1、数据库概念

1.1什么是数据库

2.MySQL数据库

2.1MySQL服务端的安装：

2.2MySQL客户端1：DOS窗口

2.3MySQL客户端2：可视化工具

4.数据库的结构

5.SQL语句

5.1定义

5.2SQL语句的分类--面试题

6.数据库的常见操作

7.表的常用操作

8.表记录(内容)的常用操作

9.navicat--数据库客户端工具

9.1操作数据库

9.2操作表

9.3操作记录(内容)

9.4写SQL

10数据类型

10.1命名规则

10.2字符类型

10.3数字类型

10.4日期

10.5图片

11.字段约束--表记录的约束

11.1主键约束(primary key)

11.2非空约束(not null)

11.3唯一约束(unique)

12.准备数据

12.1部门表dept

12.2员工表

13.基础函数

lower全传小写，upper全传大写

length求长度

substr截取字符串

concat拼接字符串

replace替换

ifnull 对null元素的替换

round四舍五入、ceil向上取整、floor向下取整

now & curtime & curdate 查询当前的年月日、时分秒

 now &year & month & day & hour & minute & second

转义字符 '

---

 1、数据库概念

1.1什么是数据库

1.存储并管理数据的仓库

2.常见的数据库分为：

1）关系型数据库(数据之间有关系)：Oracle(付费)、MySQL(免费)、SQLServer、Access

2）非关系型数据库(数据之间没关系)：MongoDB、Redis、Solr、ElasticSearch、Hive、HBase

3.关系型和非关系型

两者没有矛盾，它们各有特点，根据业务情况互补才是真谛。

但现在都在去Oracle化，阿里就已经全面排斥Oracle。

很多传统项目的还是围绕关系型数据库的居多，学习关系型数据库，目前最流行的关系型数据库是MySQL。

4.关系型数据库

关系型数据库有特定的组织方式，其以行和列的形式存储数据；

用户通过查询来检索数据库中的数据；

一个关系型数据库就是由二维表及其之间的关系组成的一个数据集合。

2.MySQL数据库

1. mysql服务端，它来处理具体数据维护，保存磁盘
2. mysql客户端，CRUD新增，修改，删除，查询

2.1MySQL服务端的安装：

一路next，注意：端口号默认3306，字符集改为utf-8，账号和密码都是root

注意：配置完，mysql开始执行，最后一步出错有时仍可以使用，使用SQLyog工具测试，如不行，再执行安装程序，选择remove，删除，然后重新安装。同时注意必须是管理员权限

2.2MySQL客户端1：DOS窗口

方式1：win+R输入cmd，打开DOS窗口，输入：mysql -uroot -proot

方式2：打开开始菜单，打开MariaDB，打开Mariadb Mysql client，输入密码root

2.3MySQL客户端2：可视化工具

安装的软件

4.数据库的结构

数据库就是一个个的表，表里都有字段名和字段值

5.SQL语句

5.1定义

结构化查询语言(Structured Query Language)，是一种特殊目的的编程语言，是一种数据库查询和抽象设计语言，用于增删改查(CRUD)关系数据库系统，也是数据库脚本文件的扩展名。

SQL 是1986年10 月由美国国家标准局（ANSI）通过的数据库语言美国标准，接着，国际标准化组织（ISO）颁布了SQL正式国际标准

注意：SQL不区分大小写

5.2SQL语句的分类--面试题

1）DML(Data Manipulation Language):数据操作语言，是对数据的操作，

CRUD(新增Create、查询Retrieve、修改Update、删除Delete);

2）DDL(Data Definition Language):数据定义语言，create,show,use,truncate,drop,alter;

3）DCL(Data Control Language)：数据控制语言，分配具体操作数据的权限;

4）DQL(Data Query Language)：数据查询语言，主要是对数据的查询;

面试题: 数据库三大范式

1. 确保每列保持原子性(不可分割性)
2. 确保表中的每列都和主键相关
3. 确保每列都和主键列直接相关,而不是间接相关

5.3 truncated、delete和drop的异同点

1）相同点

truncate和不带where子句的delete, 以及drop都会删除表内的数据。

2）不同点:

1.truncate和 delete只删除数据不删除表的结构(定义)
drop语句将删除表的结构被依赖的约束(constrain),触发器(trigger),索引(index); 依赖于该表的存储过程/函数将保留,但是变为invalid状态。
2.delete语句是dml,这个操作会放到rollback segement中,事务提交之后才生效;如果有相应的trigger,执行的时候将被触发，
truncate,drop是ddl, 操作立即生效,原数据不放到rollback segment中,不能回滚. 操作不触发trigger。
3.delete语句不影响表所占用的extent, 高水线(high watermark)保持原位置不动 ，
drop语句将表所占用的空间全部释放， 
truncate 语句缺省情况下见空间释放到 minextents个 extent,除非使用reuse storage; truncate会将高水线复位(回到最开始)。
4.删除速度：drop>truncate>delete
5.安全性：小心使用drop 和truncate,尤其没有备份的时候.否则哭都来不及
使用上,想删除部分数据行用delete,注意带上where子句. 回滚段要足够大。
6.各自使用场合：
想删除表,当然用drop
想保留表而将所有数据删除. 如果和事务无关,用truncate即可. 如果和事务有关,或者想触发trigger,还是用delete.
如果是整理表内部的碎片,可以用truncate跟上reuse stroage,再重新导入/插入数据

6.数据库的常见操作

1）建(增)库：create database+库名 DEFAULT CHARACTER SET utf8;

注意：库名必须用英文，见名知意，库名不能重复
MariaDB [(none)]> create database cgb222 DEFAULT CHARACTER SET utf8;

建库时必须添加默认字符集utf-8，也可以是小写的default character set utf8；

目的是为了控制编码集，防止乱码

2）删库：drop database+库名；

3）查库：show databases;

数据库的操作

建库	create database+库名 default character set utf8；
删库	drop database+库名；
查库	show databases;

7.表的常用操作

1)必须先使用数据库：use+库名；

2)建表：

create table+表名(字段名1  字段类型(字段长度)，字段2  字段类型(字段长度)，字段名3... );

格式：

create table tb_door(id int(3), door_name varchar(10)，tel varchar(11));

注意：字段名就是列名

其中字段类型：

String-->varchar

int-->int

double-->double

3)查表：show tables;

4)删表：drop table+表名;

5)改表：alter table+表名 add column 字段名 字段类型(字段长度)；

#表新增字段及备注
alter table 表名 add column 字段名 字段类型(字段长度) COMMENT '备注';#表批量新增字段及备注
alter table 表名 add
(`字段名` int(11) DEFAULT NULL COMMENT '注释',
`字段名` varchar(255) DEFAULT NULL COMMENT '注释');#修改表的字段类型、备注
格式：ALTER TABLE 表名 MODIFY 字段名 新数据类型 COMMENT '备注';
例子：ALTER TABLE ACT_AAA MODIFY gender char(32) COMMENT '下班';#修改表的字段名称、类型、长度、备注
格式：ALTER TABLE 表名 CHANGE 旧字段名 新字段名 数据类型 （长度） COMMENT '备注';
例子：ALTER TABLE ACT_AAA CHANGE names gender integer(2) COMMENT '备注';#修改字段据类型、长度、默认值 、注释
格式：ALTER TABLE table_name MODIFY COLUMN 字段名 新数据类型 新类型长度 新默认值 '新注释';
例子：ALTER TABLE ACT_AAA MODIFY COLUMN school decimal (15,2) DEFAULT NULL COMMENT '注释111';#修改表注释
格式：ALTER TABLE 表名 COMMENT '备注';
实例：ALTER TABLE ACT_AAA COMMENT '学生表看不出来';

6)描表：desc+表名；--展示表里具体内容

表的操作

必须先使用库	use+库名；
建表	create table+表名(字段名1  字段类型(字段长度)，字段2  字段类型(字段长度)，字段名3... );
查表	show tables；
删表	drop table+表名;
改表	alter table+表名 add column 列名 字段类型(字段长度)；
描表	desc+表名；--展示表里具体内容

8.表记录(内容)的常用操作

1）新增：insert into+表名 values(字段名1的值，字段2的值，字段3的值)；

注意：字段的值如果是varchar类型需要用单引号(‘’)或者双引号(" ")包裹

2）查询表里所有列内容：select * from+表名；

MariaDB [cgb2105]> select * from tb_door;
+------+-----------+-------------+----------+
| id   | door_name | tel         | addr     |
+------+-----------+-------------+----------+
|  123 | B栋       | 13211703121 | 银泰中心 |
|  456 | A栋       | 13211703122 | 银泰中心 |
+------+-----------+-------------+----------+

注意：查询表中指定列的数据内容：select+字段名1，字段名2+from+表名；

MariaDB [cgb2105]> select id,tel from tb_door;
+------+-------------+
| id   | tel         |
+------+-------------+
|  123 | 13211703121 |
|  456 | 13211703122 |
+------+-------------+

3）删除表里所有内容：delete from+表名;

注意：删表后使用select * from+表名；查看一下

-- 没有别名时候的语法：
DELETE  from t_user where tdate='2020-05-29';-- 有别名时候的语法：
DELETE t from t_user t where t.tdate='2020-05-29';

4）修改表里的内容：update+表名+set+字段名 = 字段的新值；

修改tb_door表中id为1的记录

update tb_door set tel=555 where id=1;

注意：格式里有等于号(=)

5）查询表的总记录数

select count * from+表名；

表内容的操作

新增表内容	insert into 表名 (字段1,字段2,字段3) values(字段名1的值，字段2的值，字段3的值)；
查询表里所有列内容	select * from+表名；--查询高效
查询表中指定列的数据内容	select+字段名1，字段名2+from+表名；
删除表里所有内容：	delete from+表名;
修改表里的内容	update+表名+set+字段名 = 字段的新值 where  字段名=字段值；(等号必带)

6）表中某字段值替换REPLACE(字段名,',替换的目标值','替换后的值')

#要求：把APPLICATION_PROVINCES字段中的“江苏”去掉（因为前面有逗号，所以sql也要作为替换值加上，同时如果所有数据都要修改的话，可把where条件去掉）
UPDATE vic_sample_strategy SET APPLICATION_PROVINCES=REPLACE(APPLICATION_PROVINCES,',江苏','') WHERE SAMPLE_ID =11768；

9.navicat--数据库客户端工具

9.1操作数据库

打开navicat工具-->新建连接-->MySQL：

9.2操作表

9.3操作记录(内容)

9.4写SQL

10数据类型

10.1命名规则

1. 字段名必须以字母开头，尽量不使用拼音
2. 长度不能超过30个字符(不同数据库，不同版本会不同)
3. 不能使用SQL保留字，如where，order，group
4. 只能使用字符a-z、A-Z、0~9、$等
5. Oracle习惯全大写：USER_NAME，mysql习惯全小写：user_name
6. 多个单词用下划线隔开，而非java语言的驼峰规则，如：user_name

10.2字符类型

1. char长度固定，不足使用空格填充，最多容纳2000个字符，char(11)存储abc，占11位。查询速度极快但浪费空间
2. varchar变长字符串，最多容纳4000个字符，varchar(11)存储abc，只占3位。查询稍慢，但节省空间。Oracle为varchar2
3. 大文本: 大量文字（不推荐使用，尽量使用varchar替代）
4. 以utf8编码计算的话，一个汉字在u8下占3个字节
5. 注：不同数据库版本长度限制可能会有不同

10.3数字类型

1. tinyint,int整数类型
2. float,double小数类型
3. numeric(5,2) decimal(5,2)—也可以表示小数,表示总共5位,其中可以有两位小数
4. decimal和numeric表示精确的整数数字

10.4日期

1. date 包含年月日
2. time时分秒
3. datetime包含年月日和时分秒
4. timestamp时间戳，不是日期，而是从1970年1月1日到指定日期的毫秒数

10.5图片

1. blob 二进制数据，可以存放图片、声音，容量4g。
2. 其缺点非常明显，数据库庞大，备份缓慢，这些内容去备份多份价值不大。同时数据库迁移时过大，迁移时间过久。
3. 目前主流都不会直接存储这样的数据，而只存储其访问路径，文件则存放在磁盘上。

11.字段约束--表记录的约束

11.1主键约束(primary key)

1)作用：非空且唯一，通常每张表都会有主键。

2)当主键为数字类型时，为了方便维护，可以设置主键自增策略(auto_increment)，初始值为1。

3)主键约束和主键约束自增格式：

#没有约束，id的值可以重复，也可以为null
CREATE TABLE a(id int)
#主键约束，id的值不能重复，也不能是null
CREATE TABLE b(id int PRIMARY KEY)
#主键约束自动递增，id的值不能重复，不能是null，而且自动递增
CREATE TABLE c(id int PRIMARY KEY auto_increment)

11.2非空约束(not null)

1）顾名思义，列表(字段名)的值不能为空

2）非空约束格式：

#不用非空约束：字段的值可以为空
CREATE TABLE d(name varchar(10))
#非空约束，字段的值不能为空
CREATE TABLE e(name varchar(10) not NULL)

11.3唯一约束(unique)

1）列表的值在整列中都是唯一，不能重复的，但可以为空

2）唯一约束的格式：

#不用唯一约束：字段的值可以重复
CREATE TABLE f(name varchar(10))
#唯一约束：字段的值不能重复
CREATE TABLE g(name varchar(10) UNIQUE)

12.准备数据

12.1部门表dept

字段名称	数据类型	是否为空	备注
deptno	int		部门编号，PK主键
dname	varchar(20)	Y	部门名称
loc	varchar(13)	Y	部门所在地点

MySQL:

CREATE TABLE dept(
deptno int primary key auto_increment ,
dname VARCHAR(20),
loc VARCHAR(13)
);INSERT INTO dept VALUES(null,'accounting','一区');
INSERT INTO dept VALUES(null,'research','二区');
INSERT INTO dept VALUES(null,'operations','二区');

12.2员工表

员工表emp

字段名称	数据类型	是否为空	备注
empno	int		员工编号，PK主键
ename	varchar	Y	员工名称
job	varchar	Y	职位
mgr	int	Y	上级编号
hiredate	datetime	Y	入职时间
sal	double	Y	月工资
comm	NUMERIC(8,2)	Y	奖金
deptno	int	Y	所属部门 FK外键

MySQL：

CREATE TABLE emp(
empno int primary key auto_increment,
ename VARCHAR(10),
job VARCHAR(10),
mgr int,
hiredate DATE,
sal double,
comm NUMERIC(7,2),
deptno int
);INSERT INTO emp VALUES(100,'jack','副总',NULL,'2002-05-1',90000,NULL,1);INSERT INTO emp VALUES(200,'tony','总监',100,'2015-02-02',10000,2000,2);INSERT INTO emp VALUES(300,'hana','经理',200,'2017-02-02',8000,1000,2);INSERT INTO emp VALUES(400,'leo','员工',300,'2019-02-22',3000,200.12,2);INSERT INTO emp VALUES(500,'liu','员工',300,'2019-03-19',3500,200.58,2);

13.基础函数

lower全传小写，upper全传大写

#lower全传小写
SELECT dname FROM dept
SELECT dname,lower(dname) FROM dept
#upper全传大写
select dname from dept
select dname,upper(dname) from dept

length求长度

length求长度，底层用了utf-8编码表，一个字母/数字占一个字节，一个汉字占3个字节
SELECT dname,LENGTH(dname),loc FROM dept
SELECT LENGTH(dname) FROM dept

substr截取字符串

#substr截取字符串
SELECT dname,SUBSTR(dname,2) FROM dept #从第二个字符开始往后截取完
SELECT dname,SUBSTR(dname,2,5) FROM dept #从第二个字符开始，截取出来5个

concat拼接字符串

#concat拼接字符串
SELECT dname,CONCAT(dname,'hello',123) FROM dept #在原来的值后面拼接hello
SELECT dname,CONCAT(dname,'hello',123,0123) FROM dept #数字0拼接后不显示

replace替换

#replace替换
SELECT dname,REPLACE(dname,'a','666') FROM dept #指定字符替换成指定字符
SELECT dname,REPLACE(dname,'acc','666') FROM dept#替换字段值中的部分内容
UPDATE vic_template_class SET CLASS_NAME_SEQ = REPLACE(CLASS_NAME_SEQ,'>','>') WHERE CLASS_ID = 1269

ifnull 对null元素的替换

#ifnull如果是null就替换成xxx，对null元素的替换
SELECT comm,IFNULL(comm,0) FROM emp
SELECT comm,IFNULL(comm,0),mgr,IFNULL(mgr,0) FROM emp

round四舍五入、ceil向上取整、floor向下取整

#round四舍五入、ceil向上取整、floor向下取整
SELECT comm,ROUND(comm) FROM emp #四舍五入
SELECT comm,ROUND(comm,1) FROM emp #保留一位小数,后面数字四舍五入
SELECT comm,CEIL(comm) FROM emp  #只要有小数就进一位
SELECT comm,FLOOR(comm) FROM emp  #小数全部舍去

now & curtime & curdate 查询当前的年月日、时分秒

#now & curtime & curdate 查询当前的年月日、时分秒
SELECT now() #查询当前时间的年月日时分秒
SELECT curdate()
SELECT curtime()

 now &year & month & day & hour & minute & second

# now &year & month & day & hour & minute & second
SELECT now(),YEAR('1990-1-1') #获取指定年月日中的年份
SELECT now(),YEAR(now()),MONTH(now()),DAY(now())#获取当前年月日
SELECT now(),HOUR(now()),MINUTE(now()),SECOND(now())#获取当前时分秒

转义字符 '

#转义字符，SQL中包含着一些特殊字符，需要转义\--反斜杠
SELECT 'xi\'an'
SELECT "xi'an"