潜心静气

2023-07-26 17:20 已编辑兰州交通大学 Java 发布于甘肃

关注

Mysql基础

1.使用idea工具写sql语句

alt

如何使用最小化的语句执行sql语句：

alt

2.sql简介

SQL：Structure Query Language。（结构化查询语言）

SQL92 92年提出的SQL的国际化标准

SQL99 99年提出的SQL标准,多加了连接等标准

(SQL-2003)标准,主要提出了窗口函数

3.数据库的操作

（1）创建数据库

create database 数据库名;

#可以设置表的编码，utf8mb4可以存储一些表情包等
create database 数据库名 character set utf8mb4;

（2）删除数据库

drop database 数据库名;

（3）使用数据库及切换数据库

use 数据库名;

（4）展示当前使用的数据库

select database();

alt

（5）展示数据库服务器中的所有数据库

show databases;

alt

（6）展示数据库的创建信息

show create database test;

alt

4.数据库表的操作

（1）创建表的语法

create table 表名(
    字段1 字段类型,
    字段2 字段类型,
    ...
    字段n 字段类型
  );

#样例：（#表名尽量使用大写，表名的规范）
create table JD_USER(
	id int,
	name VARCHAR(20),
	age int,
	sex VARCHAR(20),
	height int 
);

例二：
create table JD_USER(
        `id` bigint(20) NOT NULL AUTO_INCREMENT,
        `name` varchar(255) NOT NULL,
        `age` int(11) NOT NULL,
        PRIMARY KEY (`id`)
);

（2）常用的数据类型

  int：整型
  double：浮点型，例如double(5,2)表示最多5位，其中必须有2位小数，即最大值为999.99；
  bigint:一种整数数据类型，可以存储范围从-9223372036854775808到9223372036854775807的整数值，占用8个字节的存储空间，比较适合于存储大整数值。
  char：固定长度字符串类型；    char(10)     'aaa       '  占10位
  varchar：可变长度字符串类型； varchar(10)  'aaa'  占3为
  text：字符串类型；
  blob：字节类型；
  date：日期类型，格式为：yyyy-MM-dd；
  time：时间类型，格式为：hh:mm:ss
  timestamp：时间戳类型 yyyy-MM-dd hh:mm:ss  会自动赋值
  datetime:日期时间类型 yyyy-MM-dd hh:mm:ss

（3）表的描述

相当于注释

create table JD_USER(
	id int comment "学号",
	name VARCHAR(20) comment "姓名",
	age int comment "年龄",
	sex VARCHAR(20) comment "性别",
	height int comment "身高"
) comment "学生表";

（4）表的删除

#删除表
drop table JD_USER;

#**推荐写法**
drop table if exists JD_USER;

（5）查看表格的创建细节

show create table JD_USER;

alt

（6）查看表的结构和字段信息

desc JD_USER;

alt

desc table JD_USER;

alt

（7）表的插入

#插入语句
#这种写法必须要把创建表的属性全部写全
insert into JD_USER values(1,"张三",18,"男",175);

#插入表中某几个属性的值
insert into JD_USER(id,name,age) values(2,"张月华",20);

insert into JD_USER(id,name,age,sex,height) values(3,"张天择",17,"男",169),(4,"张莹岚",19,"女",156),(5,"张三丰",20,"男",178);

insert into JD_USER(id,name,age,sex,height) values(3,"李四",17,"男",169),(4,"李华",19,"女",156),(5,"王五",20,"男",178);

（8）查询语句（模糊查询）

select * from JD_USER;

alt

条件查询就是在查询时给出WHERE子句，在WHERE子句中可以使用如下运算符及关键字：
=、!=、<>、<、<=、>、>=；
BETWEEN…AND；
IN(set)；
IS NULL；  
AND；
OR；
NOT；

#查询名字为张三
select * from JD_USER where name like "张三";

alt

#查找一个字段为空时不用==，而是使用is null/is not null 
select * from JD_USER where name is not null and height is null;

alt

#模糊查询
#注意%和_的区别，%是匹配0个字符或任意多个字符，_是匹配1个字符。

#查询第一个名字中第一个字母为"张"的
select * from JD_USER where name like "张%";

alt

select * from JD_USER where name like "张_";

alt

#查询姓名中第二个字为"三"的数据
select * from JD_USER where name like "_三%";

alt

（9）更新语句

#更新语句
update JD_USER set age=age+2 where name="张三";

alt

update JD_USER set age=age+2 where name like "_三%";

alt

（10）表的删除

#删除表（delete和truncate）
#delete 和truncate的区别
#此语句要注意会删除数据库中所有的数据
delete from JD_USER;  
#删除表中姓名中第二个字为"三"的数据
delete from JD_USER where name like "_三%";

（11）去掉重复记录

#去掉重复记录，去除下面
select distinct * from JD_USER;
select distinct id from JD_USER;

去重前： alt

去重后：

alt

（12）concat函数与字段拼接

#一行输出，上面是两行输出，用到了连接字符的函数concat
select concat(name,'__',age) from JD_USER;

alt

#列出所有的id,age,height的和
select id+age+height from JD_USER;

alt

#上述语句运行后发现有个问题，当null和数字相加的时候等于null，这时候就需要将该字段的null替换成0
select id+age+ifnull(height,0) from JD_USER;

alt

（13）分组查询

#分组查询
①先指定要分组的列，select 列名。
②确定分组的名称， group by 列名
select sex from JD_USER group by sex;
③指定要查询的数据，但一般是聚合函数
select sex,count(id) from JD_USER group by sex;
④显示列一般需要聚合计算的列和分组的列，其他的列不要写，容易误导结果
select id,sex,count(id) from JD_USER group by sex;

alt

（14）having

#having:分组之后的结果在进行查询
select sex,count(id) userCount from JD_USER group by sex having userCount>4;

alt

（15）limit

#limit:该限制可以用在分页
select * from JD_USER limit 1,5;
#上面的语句表示从1开始，去除5条记录

alt

分页查询：

  查询语句书写顺序：select – from- where- group by- having- order by-limit
  查询语句执行顺序：from - where -group by - having - select - order by-limit

（16）聚合函数

#聚合函数sum,max,min,count,avg,他是纵向运算，一列从上往下计算

（17）排序

①升序（默认为升序）

select * from JD_USER order by age ASC ;
select * from JD_USER order by age ;

alt

②降序

select * from JD_USER order by age desc ;

alt

③多个字段排序

#查看所有学生信息表，按年龄进行升序排列，如果年龄相同，则按照升高进行降序排列。
select * from JD_USER order by age ASC ,height desc ;

alt

（18）其他的操作

查看服务器中的数据库，并把mydb2的字符集修改为utf8;
alter database mydb2 character set utf8;

#当前数据库中的所有表
SHOW TABLES;


#查看表的字段信息
DESC employee;

#在上面员工表的基本上增加一个image列。
ALTER TABLE employee ADD image blob;

# 修改job列，使其长度为60。
ALTER TABLE employee MODIFY job varchar(60);

#删除image列,一次只能删一列。
ALTER TABLE employee DROP image;

#表名改为user。
RENAME TABLE employee TO user;


#查看表格的创建细节
SHOW CREATE TABLE user;

#修改表的字符集为gbk
ALTER TABLE user CHARACTER SET gbk;

#列名name修改为username
ALTER TABLE user CHANGE name username varchar(100);

#删除表
DROP TABLE user ;

5.数据的完整性

5.1 主键约束

注：每个表中要有一个主键。
特点：数据唯一，且不能为null,不能修改,不能有业务性(不要让客户知道),不要用自增长类型

主键约束的三种创建方式：

第一种方式：
CREATE TABLE student(
id int primary key,
name varchar(50)
);


第二种方式：可以创建联合主键
CREATE TABLE example (
id INT,
name VARCHAR(50),
date DATE,
PRIMARY KEY (id, name)
);
在这个例子中，id和name两个字段同时成为主键。这意味着，不允许重复的id和name组合将是表格中的唯一标识，而且这两个字段将自动成为索引。

需要注意的是，联合主键中每个列的值都必须是唯一的，如果有重复的组合，相关的INSERT或UPDATE语句将失败。

第三种方式：
CREATE TABLE student(
id int,
name varchar(50)
);
ALTER TABLE student  ADD  PRIMARY  KEY (id);

5.2 唯一约束

特点：数据不能重复，所有记录中字段的值不能重复出现

create table student(
    id int primary key ,
    num bigint unique comment '学号',
    name varchar(20)
);

insert into student(id,num,name) value (1,15674566,'张三');
insert into student(id,num,name) value (2,15674566,'李四');

会出现以下错误： alt

唯一约束和主键约束的区别

唯一约束与主键约束相似的是它们都可以确保列的唯一性。不同的是，唯一约束在一个表中可有多个，并且设置唯一约束的列允许有空值，但是只能有一个空值。而主键约束在一个表中只能有一个，且不允许有空值。比如，在用户信息表中，为了避免表中用户名重名，可以把用户名设置为唯一约束。

5.3 主动增长列

sqlserver数据库 (identity) oracle数据库( sequence)

给主键添加自动增长的数值，列只能是整数类型

create table student(
    id int primary key auto_increment,
    num bigint unique comment '学号',
    name varchar(20)
);

insert into student(num,name) values (1231,'王五'),(1232,'李华');

查询表：

alt

注意：现在我们删除id=2的数据，然后在插入一条数据，主键id就会从3开始，不是从删除的2开始。

delete from student where id=2;

insert into student( num, name) value (1245,'王麻子');

alt

这是什么原因导致的了？

使用 innodb 引擎的情况下。当 MySQL 版本为 8.0 之前时，自增 id 会丢失，下次自增会取表中最大 id + 1。即新增的数据 id 为 7。当 MySQL 版本为 8.0 及之后时，自增 id 不会丢失，仍从删除数据 id 后算起。即新增的数据 id 为 9。

使用 myisam 引擎的情况下。不管 MySQL 版本为多少，自增的 id 都不会丢失。

可以通过查看表的状态查看什么引擎。

show table status ;

alt

原文链接：https://blog.csdn.net/weixin_44061521/article/details/123718973

解决办法：

#接上述，将id=3的数据删除。
delete from student where id=3;

#进行对主键的设置
ALTER TABLE student AUTO_INCREMENT = 1;

#重新插入数据
insert into student( num, name) value (1245,'王麻子');

alt

详细参考链接： https://blog.csdn.net/qq_53039581/article/details/128593602

5.4 非空约束

CREATE TABLE student(
Id int pirmary key,
Name varchar(50) not null,
Sex varchar(10)
);


INSERT INTO student values(1,’tom’,null);

5.5 默认约束

CREATE TABLE student(
Id int primary key,
Name varchar(50) not null,
Sex varchar(10) default '男'
);


insert into student1 values(1,'tom','女');
insert into student1 values(2,'jerry',default);

5.6 外键约束（FOREIGN KEY）

#第一种添加方式：

# 学生表,一般也叫做主表
CREATE TABLE student(
sid int primary key,
name varchar(50) not null,
sex varchar(10) default ‘男’
);


#成绩表,它是以学生表为主,所以也叫做从表
create table score(
  id int primary key,
  score int,
  sid int , -- 外键列的数据类型一定要与主键的类型一致
  foreign key (sid) references student(sid)
);

#第二种添加方式：

ALTER TABLE score1 ADD CONSTRAINT fk_stu_score FOREIGN KEY(sid) REFERENCES stu(id);