数据库

mysql-5.5.27-winx64.msi

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

Mysql数据库默认的编码是latin1等价于iso-8859-1，修改为utf-8

在这里插入图片描述

在这里插入图片描述

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

在这里插入图片描述

 

 

insert，delete，update，select（插入、删除、修改、检索） 数据类型 int varchar double numeric(5,2)

1.创建数据库，数据库名称：cgb2022并确定字符集

create database cgb2022 DEFAULT CHARACTER SET utf8;

2.删除名称是cgb2022的数据库

drop database cgb2022

3.查看所有数据库

show databases

4.使用数据库

use cgb2022

5.创建tb_door表，有id,door_name,tel字段

create table tb_door(
id int primary key auto_increment,
door_name varchar(100),
tel varchar(50)
);

6.添加列      NUMERIC(7,2)   字段类型，double类型有7位数，2位小数

alter table tb_door add column 字段名 NUMERIC(7,2)

7.插入记录

insert into tb_door values(null,'永和大王1店',666);

 

replace into users(id, name, age) VALUES(123, '赵本山', 50), (134,'Mary',15);
REPLACE也可以使用SET语句

8.查询tb_door表中的所有记录

SELECT * FROM tb_door;

9.修改tb_door表中id为1的记录

update tb_door set tel=555 where id=1;

10.删除tb_door表中id为2的数据

Delete from tb_door where id=2;

 

修改数据库

alter  database  db1    charset    gbk；

删除数据库

drop database if exists  db1;

改表名

rename table 原名 to  新名

添加字段

alter  table  表名  add column   字段  类型   位置

                                                                    add     column    （字段1    数据类型，字段2    数据类型，字段3    数据类型）                  

删除字段

alter  table  表名     drop     column    字段

 

修改一个字段的名称 

alter  table  表名  change    老字段    新字段   类型

alter  table  表名  modify   字段   类型

 

删除表里所有数据，保留表

truncate  tb_door

11.将tb_door表记录按照tel排序

Select * from tb_door order by tel desc;

12.查询tb_door表中的总记录数

Select count(*) from tb_door;

 

 waitfor语句

--例 等待1 小时2 分零3 秒后才执行SELECT 语句
waitfor delay ’01:02:03’

select * from employee

13.主键约束与自增

13.1主键约束

主键约束：如果为一个列添加了主键约束，那么这个列就是主键，主键的特点是唯一且不能为空。通常情况下，每张表都会有主键。

添加主键约束，例如将id设置为主键：

主键自增策略** **当主键为数值类型时，为了方便维护，可以设置主键自增策略（auto_increment），设置了主键自增策略后，数据库会在表中保存一个AUTO_INCREMENT变量值，初始值为1，当需要id值，不需要我们指定值，由数据库负责从AUTO_INCREMENT获取一个id值，作为主键值插入到表中。而且每次用完AUTO_INCREMENT值，都会自增1. AUTO_INCREMENT=1

create table abc(
id int primary key auto_increment
);
insert into abc values(null);
insert into abc values(null);
insert into abc values(null);
select * from abc;

13.2非空约束

非空约束：如果为一个列添加了非空约束，那么这个列的值就不能为空，但可以重复。

添加非空约束，例如为password添加非空约束：

create table user(
id int primary key auto_increment,
password varchar(50) not null
);
show tables;
insert into user values(null,null);//不符合非空约束
insert into user values(null,123;);//OK
​

13.3唯一约束

唯一约束：如果为一个列添加了唯一约束，那么这个列的值就必须是唯一的（即不能重复），但可以为空。

添加唯一约束，例如为username添加唯一约束及非空约束：

create table test(
id int primary key auto_increment,
username varchar(50) unique--唯一约束
);
show tables;
insert into test values(null,'lisi');
insert into test values(null,'lisi');--username的值要唯一,重复会报错的
select * from test;


排除哪个
Not in（哪个）
查询空数据
is null

14.基础函数

14.1数据转小写 lower(字段)

SELECT 'ABC',LOWER('ABC') from dept; --数据转小写

14.2 数据转大写 upper(字段)

select upper(dname) from dept --数据转大写

14.3数据长度 length

select length(dname) from dept --数据的长度

14.4截取 substr(1,2,3) 1.字段 2.开始位置 3.截取长度

SELECT dname,SUBSTR(dname,1,3) FROM dept; --截取[1,3]

14.5拼接 concat(1,2) 1.字段 2.在后面拼接的东西

select dname,concat(dname,'123') X from dept --拼接数据

14.6替换 replace(1,2,3) 1.字段 2.要替换的东西 3.替换成的东西

select dname,replace(dname,'a','666') X from dept --把a字符替换成666

14.7判断 ifnull(1,2) 1.字段 2.替代的数

select ifnull(comm,10) comm from dept2 #判断,如果comm是null,用10替换

 

15.1 round & ceil & floor

四舍五入 round(1,2) 1.字段 2.保留几位数

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

–直接四舍五入取整

select comm,round(comm) from emp

–四舍五入并保留一位小数

select comm,round(comm,1) from emp

–ceil向上取整,floor向下取整

select comm,ceil(comm) ,floor(comm) from emp

 

15.2 now

按照 ’年-月-日 时：分：秒’ 的格式返回    created_at字段名

DATE_FORMAT(created_at,'%Y-%m-%d %H:%i:%s') 

 

select now() -- 年与日 时分秒

select curdate() --年与日

select curtime() --时分秒

15.3 year & month & day

当前的时间 now() 年月日 时分秒 select now() 年月日 select curdate() 时分秒 select curtime() 时 hour() 分 minute() 秒 second() 年 year() 月 month() 日 day()

 

–hour()时 minute()分 second()秒

select now(),hour(now()),minute(now()),second(now()) from emp ;

–year()年 month()月 day()日

select now(),year(now()),month(now()),day(now()) from emp ;

 

使用 DATE_ADD() 增加时间

 

语法

SELECT DATE_ADD(date, INTERVAL expr type)
FROM table_name

其中：date 指日期字段名，为起始日期

​ expr 是希望添加的时间间隔的数值（expr 是一个字符串，对于负值的间隔，可以以 ”-“ 开头）

​ type 是具体的数据类型，表示加上时间间隔的单位（可以是 MICROSECOND , SECOND , MINUTE , HOUR , DAY , WEEK , MONTH , QUARTER , YEAR 等）

 

使用 DATE_SUB() 减时间

语法

SELECT DATE_SUB(date, INTERVAL expr type)
FROM table_name

其中：date 指日期字段名，为起始日期

​ expr 是希望添加的时间间隔的数值（expr 是一个字符串，对于负值的间隔，可以以 ”-“ 开头）

​ type 是具体的数据类型，表示加上时间间隔的单位（可以是 MICROSECOND , SECOND , MINUTE , HOUR , DAY , WEEK , MONTH , QUARTER , YEAR 等）

 

使用时间函数 DATEDIFF() 和 TIMESTAMPDIFF() 计算日期差

DATEDIFF() 的用法。



如果我们要查询课程表 courses 所有日期和指定日期天数时间差：




我们可以使用下面的 SQL 语句：


SELECT DATEDIFF(created_at,'2018-01-13') AS date_diff FROM courses;



select TIMESTAMPDIFF(month,created_at,'2020-04-22')     所有日期与指定日期的月数差


 


DATEDIFF() 常用的日期差，在 MySQL 中默认只能计算天数差。




示例代码




DATEDIFF() 用法：




DATEDIFF（时间1，时间2）


 


SELECT DATEDIFF(时间1,时间2) AS date_diff FROM courses;


 




15.4 distinct


distinct->使用distinct关键字，去除重复的记录行


SELECT DISTINCT loc FROM dept;


15.5 where


and 并且关系 or 或者关系


select * from emp where ename='tony' and deptno=2 --相当于两个条件的&关系 


in（1,2,3） ->字段=1或2或3


select name, sal from emp where sal not in(1400,1600,1800);


15.6 like


like ->like 'l%' --以l开头的


select * from emp where ename like '%a' --以a结束的 


15.7 null


null ->where mgr is not null --过滤字段值不为空的


select * from emp where mgr is not null --过滤字段值不为空的


15.8 between and


between and->在那两个数中间


select * from emp where sal between 3000 and 10000--等效


15.9 limit


limit ->limit 0,3 --从第一条开始,展示3条记录--前三条


select * from emp limit 0,3 --从第一条开始,展示3条记录--前三条


15.9 order by


order by->order by sal desc #降序
​
SELECT * FROM emp order by sal #默认升序 
​
SELECT * FROM emp order by sal desc #降序


 


16.聚合函数 


16.1 count(个数) 


select count(*) from emp --底层优化了


16.2 max / min 


select min(sal) min,max(sal) max from emp --最小值最大值 


16.3 sum（和） / avg（平均）


select count(*) from emp --总记录数 
​
select sum(sal) from emp --求和
​
 select avg(sal) from emp --平均数


 


聚合函数与非聚合函数同时出现，必须分组，where不能跟聚合函数，having


as别名 sum（sal） as a


17 分组 group


用于对查询的结果进行分组统计


group by表示分组, having 子句类似where过滤返回的结果


17.1 group by


#每个部门每个岗位的最高薪资和平均薪资，结果中的非聚合列必须出现在分组中，否则业务意义不对


SELECT deptno,MAX(sal),AVG(sal) FROM emp
​
GROUP BY deptno #按照deptno分组
​
SELECT job,MAX(sal),AVG(sal) FROM emp
​
GROUP BY job #按照job分组
​
SELECT deptno,job,MAX(sal),AVG(sal) FROM emp
​
GROUP BY deptno,job #deptno和job都满足的


 


17.2 having


#平均工资小于8000的部门


select deptno, AVG(sal) from emp
group by deptno #按部门分组
having AVG(sal)<8000 #查询条件,类似where,但是group by只能配合having
​
#deptno出现的次数
SELECT deptno,COUNT(deptno) FROM emp
​
GROUP BY deptno #按deptno分组
​
HAVING COUNT(deptno)>1 #次数多的


 


18.查看索引


 show index from dept;


18.1创建普通索引


#create index 索引名字 on 表名(字段名); #创建索引

create index loc_index on dept(loc); #创建索引


18.2创建唯一索引


18.2.1 创建唯一索引--索引列的值必须唯一


CREATE UNIQUE INDEX 索引名 ON 表名(字段名)
CREATE UNIQUE INDEX bindex ON dept(loc)


 


18.2.2 创建复合索引


#如果您希望索引不止一个列，您可以在括号中列出这些列的名称，用逗号隔开：


CREATE INDEX 索引名 ON 表名 (字段1, 字段2)
CREATE INDEX PIndex ON Persons (LastName, FirstName)


 


19 删除索引


alter table dept drop index fuhe_index


19.1 最左特性


explain

select * from dept where loc='二区' #使用了loc索引

explain

select * from dept where dname='研发部'#使用了dname索引

explain

select * from dept where dname='研发部' and loc='二区' #使用了dname索引


 


20.多表联查 join


 把两个表的数据都拼接起来：SELECT * FROM dept,emp ​ 三种连接 join 内连接 inner join/左（外）连接 left join/右（外）连接 right join ​ SELECT d.dname,e.ename,e.job


FROM emp e INNER JOIN dept d
 
ON e.deptno=d.deptno  //条件一
 
WHERE d.dname='research' //条件二


#表明和哪张表的哪个字段有关系 #foreign key(本表的主键) references 关联表(主键) FOREIGN KEY(user_id) REFERENCES tb_user(id)


 


 


 


 


 


 






            

            
posted @ 
2021-10-13 22:17 
身在江湖 
阅读(51) 
评论(0) 
编辑 
收藏 
举报