Mysql数据库

Skyme2023/7/8大约 11 分钟

虽然学过Mysql但是很久远的事情了,所以现在回来再学下

MySQL三层结构

SQL语句分类:

DDL：数据定义语句
DML：数据操作语句
DQL：数据查询语句
DCL：数据控制语句[比如用户权限,grant,revoke]

Java操作数据库

案例:

创建数据库

create database [if not existes] db_name

CHARACTER SET ：指定数据库采用的字符集
COLLATE：指定数据库字符集的校对规则(常用utf8_bin[区分大小写],utf8_general_ci[不区分大小写],注意默认是utf8_general_ci)

删除数据库:

drop database db_name;

创建数据库:

SELECT * FROM t1 WHERE `name`='tom'

查询数据库

查看前面创建数据库的定义信息:

show create database db_skyme

CREATE DATABASE `db_skyme` /*!40100 DEFAULT CHARACTER SET utf8 */

为了规避关键字,可以使用反引号解决！

CREATE DATABASE 'CREATE';

备份恢复数据库

备份数据库（在DOS执行)

mysqldump -u 用户名 -p -B 数据库1 数据库2 > 文件名.sql

恢复数据库（进入客户端执行)
```
Source 文件名.sql
```

备份数据库的表

mysqldump -u 用户名 -p密码 数据库 表1 表2 > d:\\文件名.sql

创建表

CREATE TABLE `user` (id INT ,`name` VARCHAR(255),`password` VARCHAR(32),`birthday` DATE) CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB;

数据类型

数值类型

整型：

tinyint[1个字节] 2的7次方-1,最高位符号减去
smallint[2个字节]
mediumint[3个字节]
int[4个字节]
bigint[8个字节]

小数类型

float [单精度 4个字节]，如果描述长度的时候25-53那就是8个字节
double [双精度 8个字节]
decimal[M,D] [大小不确定]:M是位数的总数,D是小数点后面的位数,M最大65,D最大是30，默认为0，M默认是10

字符串类型

char[0-255]字符:char(4)是定长(固定的大小),也就是说你插入了'aa'，也会按照4个字符的空间来分配（空格自动补齐)
varchar[0-65532字节]:varchar(4)是变长,'aa'实际占用空间大小不是四个字符,而是按照实际占用空间来分配(varchar本身还要用1-3个字节来记录存放内容长度)
区别char和varchar的区别:char效率比varchar效率高,char的空间利用率可达到100%，varchar不行
txt[0-65535]
longtext[0-2^32-1]

二进制数据存储

blob [0-65535]
longblob [0-2^32-1]

日期类型

date [日期:年月日]
time [时间:时分秒]
datetime [年月日时分秒 YYYY-MM-DD HH:mm:ss]
timestamp [时间戳]
year [存放年]

约束

对字段的一些约束
非空not null

唯一 unique

主键 primary key

默认 default

外键 foreign key

不带符号 unsigned

check 不支持check约束,但是写上去不会报错

建表

create table user (
uid int primary key auto_increment comment '用户主键',username varchar(50) not null unique comment '账号',password char(32) not null comment '账号',
 salt char(6) comment '盐',email varchar(50) unique comment '邮箱',
  telPhone varchar(20) unique comment '手机号',
    nickname varchar(20) comment '昵称',
    birthday date comment '生日',
    sex char(2) not null default '男' comment '性别'
)
insert  into user values(1,"张三","123456","skyme","412399240@qq.com","19140309154","skyme",now(),"男")
create table message(
mid int primary key auto_increment comment '消息id',conent varchar(2000) comment '消息内容', uid int,foreign key(uid) references user(uid),
sendtime datetime not null

)auto_increment=1000
insert into message values (0,"消息",1,now())

条件

条件主要存在于where后面部分
判断相同 =
判断不同 != < >

在指定范围之间 between start and end 包含 start 和and
不在指定范围之间 not between start and end 不包含start 和end
在指定集合之中 in (3,5,6,7)
不在指定集合之中 not int (3,5,6,7)
判断字段是否为空 is null
判断字段不为 is not null
模糊查询 like 格式 like '...' %代表任意多个字符 _代表一个字符

不像指定的格式 not like
多条件的关系
and 并且
or 或者

在写增删改查的条件顺序的时候,条件的顺序是有讲究,一般建议筛选大量数据的在前面,少量数据的在后面

修改表

添加列:

alter table tablename add (column datatype [DEFAULT expr][,column datatype]....)

修改列:

alter  tablename modify (column datatype [DEFAULT expr][,column datatype]....)

删除列:

alter table tablename drop (column)

修改列名

alter table tablename change xxx xxx  varchar(?)

查看表结构:

desc 表名; --可以查看表的列

修改表名:

Rename table 表名 to 新表名

修改表字符集:

alter table 表名 character set 字符集

Insert

insert into table_name (column,column···) values (value,value···)

希望指定某个列的默认值,创建表的时候

NOT NULL DEAFULT value

Update

如果不写where则是对表中所有记录修改
UPDATE语法可以用新值更新原有表行的各列
SET子句指示要修改的哪些列和要给予哪些值
如果要修改多个字段,可以通过 set 字段1 =值1,字段2 =值2 ....

update employee set salary =3000
insert into employee VALUES(200,'老妖怪','1990-11-11','2000-11-11 11:11:11','捶背的',5000,'哈哈哈','哦哦');
update employee set salary = salary+1000 WHERE `name` = '老妖怪';

DELETE

如果不带where则是删除全部数据
Delete语句不能删除某一列的值,可以使用update

SELECT

语法使用

创建表

CREATE TABLE student(
id INT NOT NULL DEFAULT 1, NAME VARCHAR(20) NOT NULL DEFAULT '', chinese FLOAT NOT NULL DEFAULT 0.0, english FLOAT NOT NULL DEFAULT 0.0, math FLOAT NOT NULL DEFAULT 0.0
);

INSERT INTO student(id,NAME,chinese,english,math) VALUES(1,'韩顺平',89,78,90);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(2,'张飞',67,98,56);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(3,'宋江',87,78,77);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(4,'关羽',88,98,90);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(5,'赵云',82,84,67);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(6,'欧阳锋',55,85,45);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(7,'黄蓉',75,65,30);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(8,'韩信',45,65,99);

SELECT * FROM student;//一般不建议用*,查询所有建议写完所有的字段，因为*中间会多一个过程

查询表中所有学生的信息

SELECT * FROM student;
``` 如上图
2. 查询表中所有学生的姓名和对应的英语成绩
```sql
select name,english from student;

3. 过滤表中的重复数据distinct

select DISTINCT english from student;

4. 要查询的记录,每个字段都相同,才会去重

别名和列运算

SELECT `name` AS `名字`,(chinese+english+math) AS `总分` FROM student ;

运算符

SELECT `name` AS `名字`,(chinese+english+math) AS `总分` FROM student ;
SELECT * FROM student where math >60 and id >4 ;
SELECT * FROM student where english > chinese;
SELECT * FROM student where (english+chinese+math)>200 and math<chinese and `name` LIKE '韩%';

%是0到多的意思

使用order by 子句排序查询结果

asc升序默认, desc 降序

order by 子句应该位于select语句的结尾

合计/统计函数

cout返回行的总数：

cout(*) 返回满足条件的记录的行数
cout(列) 统计满足条件的某列有多少个,但是会排除为Null的情况

Sum -合计函数

Sum函数返回满足where条件的行的和一般使用在数值列

Avg返回满足where条件的一列的平均值

max/min 返回满足where条件的一列的最大/最小值

分组统计

使用group by 子句对列进行分组
使用having 子句对分组后的结果进行过滤

限制结果条数

limit [start,]length
从start开始 查询length个数据,如果start没写默认是从0开始

函数

字符串函数

CHARSET(str):返回字串字符集

select charset(ename) from emop;

CONCAT(string):连接字串,将多个列拼接成一列

select concat(ename,'job is',job) from empl;

LEFT,RIGHT(String,length)
LENGTH(string) string长度(按字节)
REPLACE(str,search_str,replace_str)

select ename,replace(job,`manager`,`经理`) from emp;

时间日期函数

加密函数和系统函数

查询用户

可以查看登录到Mysql的有哪些用户以及IP

select USER() FROM DUAL;

查询当前使用的数据库名称

SELECT DATABASE();

MD5(STR) 为字符串算出一个MD5 32的字符串加密

处理过后都是32位的

SELECT MD5('skyme') FROM DUAL;

PASSWORD(str) 加密函数 MYSQL用户密码通过PASSWORD来加密的

流程控制函数

IF(expr1,expr2,expr3) 如果expr1为True,则返回expr2,否则返回expr3
IFNULL(expr1,expr2) 如果expr1不为空NULL，则返回expr1,否则返回expr2
SELECT CASE WHEN TRUE THEN XXX WHEN FALSE THEN XXX ELSE XXX END

SELECT加强

表查询加强

表示0到多个任意字符 _:表示单个字符
如何显示第三个字符大写o的所有员工的姓名和工资

SELECT ename,sal from emp WHERE ename Like '___O%'

如何显示没有上级的雇员

SELECT * FROM WHERE mgr is NULL;

查询表结构:

DECS tablename

按照部门号升序而雇员的工资降序排列 , 显示雇员信息

SELECT * FROM emp

ORDER BY deptno ASC , sal DESC;

多表查询

笛卡尔积

一般不使用

select * from 表1,表2

首先会将两张表相乘，然后再在其中筛出数据

连接查询

join

左连接(左外连接) left join

SELECT * FROM kl_user u LEFT JOIN kl_goods g on u.user_id=g.goods_host;

数据量不是特别大可以使用

右连接(右外连接)

一般情况下有外键的表是基准表

内连接

inner join
SELECT u.nickname, g.goods_name FROM kl_user u INNER JOIN kl_goods g on u.user_id=g.goods_host;

和左右连接的区别是只要没有匹配的数据都不要,也就是说内连接不出现Null的情况

子查询

一个sql中出现了两个及以上的关键字就可以称为子查询, union方式除外

where型子查询

select 字段 from 表名 where 字段=(查询出来的一个)

select * from weibo where sendTime > any(也可以用all) (select sendTime from weibo where uid=1)

from型子查询

select 字段... from (select 字段... from 表名 where 条件) 别名 where 条件

联合查询

把两次查询的结果合并到一起,要求两次查询的结构的列数量和类型一致，否则无法合并

SELECT * FROM `kl_user` WHERE login_date < '2023-06-04 19:40:26' UNION SELECT * FROM kl_user WHERE login_date > '2023-08-11 16:43:09'

在联合的时候union会自动去重 union all不会去重

事务transaction

事务四个特性 ACID

原子性：要么全部成功,要么全部失败

一致性：事务执行前后,数据的总体状态保持一致

隔离性: 事务在并发的时候,应该互不影响

数据本身提供了4种隔离级别：串行化,可重复度(mysql),可读已提交(oracle),可读未提交

持久性: 事务成功执行后,对数据库的改变是持久的

begin 开启事务
commit 提交事务
rollback 回滚事务

其他的常见对象

视图 view

视图可以理解依次查询的临时结果被保存在数据库中,以后可以单独的使用这个临时结果,所以视图再使用的时候可以当做表来使用

视图无法执行增删改操作，但创建视图所使用的表的数据发生改变,视图中的数据就会随之改变

存储过程 procedure

存储过程是数据库比较重要的功能,5.0后支持

可以提升数据的处理速度，灵活性也可以提升

create procedure 存储过程的名字 (in|out|inout 参数名 参数类型[,in|out|inout 参数名 参数类型]....)
过程体

in是无法修改的参数
out 可以修改
inout可修改可使用

create procedure prod (out s int)
begin
	select count(*) into s from kl_user;
end
CALL prod(@s);
select @s;

索引index

索引的目的在于提高查询效率,可以类比字典,

create index 可对表增加索引或unique索引
create index index_name on table_name (column_list)
create unique index_name on table_name (column_list)