数据库SQL语句

一、数据库

1.数据库概念

1.数据库服务器

安装好mysql数据库软件后，这个服务器，支撑数据的存储。

2.数据库

一个项目对应一个数据库，数据库服务器下面可以有很多个数据库

3.数据表

一个数据库中可以有很多个表

4.字段和数据

一个表中可以有很多个字段和数据

2.数据库命令

在终端中通过命令操作数据库（win+R）

2.1创建和查看数据库和表

#连接数据库服务器的命令
mysql -uroot -p
输入密码：******
#退出命令
exit;
#查看服务器下有多少个数据库
show databases;
#新建数据库
create database 库名;
#删除数据库   慎用！
drop database 库名;
#进入数据库
use database 库名;
#查看当前数据库下有多少表
show tables;
#创建表
#格式1
create table 表名(字段1 数据类型(),字段2 数据类型(),....);
#格式2
create table 表名（
id int,
name varchar(32),
age int);
#查看表结构
desc 表名;
#删除表   慎用！
drop table 表名;
#查看创建数据库和创建表时候的信息
show create database 库名;
show create table 表名;

2.2修改表的字段 alter

varchar 和 char 的区别：

varchar和char都是用来存放字符串类型的数据

char(32) 定长 存入数据时，不论存入数据多长，都开辟32长度

varchar(32) 变长 存入数据是，随着存入数据的长短，开辟对应长度

数据类型 text 文本 字符串 不限制大小

数据类型 decimal(8,2) 小数 最大长度8位，小数部分占2位

#删除某个字段
alter table 表名 drop 字段名;
#添加某个字段
alter table 表名 add 字段名 数据类型;
#在指定的字段后面添加一个字段
alter table 表名 add 字段名 数据类型 after 字段名;
#修改某个字段的数据类型 
alter table 表名 modify 字段名 修改后的数据类型;
#修改字段和数据类型
alter table 表名 change 字段名 修改后的字段名  修改后的数据类型;
#添加一个字段
alter table 表名 add 字段名 数据类型;

2.3添加数据到表 insert into

#插入一条数据到表中
#格式1
insert into 表名 values (值1,值2,...);
#格式2  开发多用 
insert into 表名(字段名1,字段名2,...)values(值1,值2,...);
#通过指定的字段名，插入指定的数据
insert into 表名(字段1,字段2,...)values(值1,值2,...);
#一次性插入多条数据
insert into 表名(字段1,字段2,...)values
(值1,值2,...),
(值1,值2,...),
(值1,值2,...));

2.4删除数据【慎】 delete

#删除固定字段的数据
delete from 表名 where 字段名 = 值;
#删除多条数据
delete from 表名 where 字段名 in (值1,值2,值3,...);

2.5修改数据【慎】 update

#修改单个字段数据
update 表名 set 字段名 = 新值 where 字段名 = 值;
#修改多个字段数据
update 表名 set 字段名 = 新值,字段名=新值,...where 字段名=值;

2.6事务操作

事务操作可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行。

事务用来管理insert，update，delete语句。

#如何保证多个sql语句同时执行或者同时不执行
#开启事务，默认是关闭的 将sql语句自动提交关闭！！！
set autocommit = 0;
#回滚
rollback;
#全部执行
commit;

set autocommit = 0 ;
sql语句1;
sql语句2;
commit;(提交)或者rollback;(回滚);

2.7查询 select【重点】

查询所有数据

#查询表中所有字段   *通配符，代表的是所有
select * from 表名
#查询某个字段的数据
select 字段名1,字段名2,... from 表名;
#按照字段查询时，可以对此字段起别名  查看是就是别名
select 字段名1 as 别名1, 字段名2 as 别名2,...form 表名;

查询该条件下的数据

若需要有条件的从表中选取数据，可将where子句添加到select语句。

下面运算符可在where子句中使用：

操作符	描述
=	等于
<>	不等于
>	大于
<	小于
>=	大于等于
<=	小于等于
BETWEEN	在某个范围内
LIKE	搜索某种模式

注释：在某些版本的 SQL 中，操作符 <> 可以写为 !=。

#查询id=6的数据
select * from 表名 where id = 6;
#查询年龄在10~30岁之间的数据
select * from 表名 where age between 10 and 30;
#like 模糊查询
select * from 表名 where name like "%苏%";

逻辑运算符

and && or ||

#查询性别为1且年龄为29的数据
select * from 表名 where gender =1 and age = 29 ;
#查询年龄小于30或者性别为0的数据
select * from 表名 where age<30 or gender = 0;

2.7.1 order by 排序

对查询结果排序 order by 一定要写在 where 后面

select 字段 from 表 order by 字段 asc;默认是升序  asc可以省略
select 字段 from 表 order by 字段 desc;
#年龄大于20的 然后薪资倒序排
select * from 表名 where age>20 order by salary desc;
#先按照薪资倒序排，如果薪资一样，再按照年龄升序排
select * from 表名 order by salary desc,age asc;

2.7.2 limit 限制输出

limit 数字； //限制前几个数据输出

limit 数字1,数字2; //数字1 偏移量 数字2 数据的个数

limit 必须在where 和order by 的后面

#前三个数据
select * from 表名 limit 3;
#性别为1，按照薪资升序排序，限制输出3个数据
select * from 表名 where gender=1 order by salary limit 3;

limit 分页以后是要做分页的
1 2 3 4 5 6 7 8 9 10 11
每页显示3条数据 一共有4页
前端会提供一个值 第几页
第一页： 1 2 3 limit 0, 3;
第二页: 4 5 6 limit 3, 3;
第三页: 7 8 9 limit 6, 3;
第四页: 10 11 limit 9, 3;

Java中有一个变量 叫 int pageNo = 1
每页显示的数据 int pageCount = 3;
limit (pageNo - 1) * pageCount, pageCount;

2.7.3 查询相关的sql内置方法

max(); 求一个字段中数据的最大值

min(); 求一个字段中数据的最小值

avg(); 求一个字段中数据的平均值

sum(); 求一个字段中所有数据的和

count(*); 统计当前表中数据的个数

select max(字段名) from 表名;

#找出年龄最大的那条数据   嵌套sql  一个sql的结果当成另一个sql的条件
select * from person where age = (select max(age) from  person );

2.7.4 group by 分组统计

group by 语句用于结合count函数，根据一个或多个列 对结果集进行分组

一般使用group by的时候 需要对分组之后的数据进行筛选 使用having 不能where

#对性别进行分组
select gender from person group by gender;
#对年龄进行分组后统计每组个数
select gender count(*) from person group by gender;
#按照性别分组，然后统计，最后要性别个数大于2的
select gender count(*) from person group by dender having count(*)>2;
#找出年龄大于20的，然后分组统计，统计大于2的性别类别
select gender count(*）from person where age>20 group by gender having count(*)>2;

3.数据约束

在创建表时，一定要对字段进行约束，这样插入数据时，才更加合理，有约束性。

数据约束在创建字段时就可以开始添加了。

3.1默认值 default

如果给上一个字段默认值 在插入数据时，没有给当前字段设置值，就会给当前字段一个默认值

create table person(
id int ,
 name varchar(32),
    country varchar(32) default "默认值"
);

3.2非空 not null

not null 如果给一个字段加上非空，在添加数据时，必须给当前字段赋值，否则就会报错。

create table person(
id int not null,
    name varchar(32)
);

3.3唯一 unique

unique 设置字段的唯一性，添加数据的时候，如果添加数据重复的值话会报错。

create table person(
id int not null,
name varchar(32) unique not null
);

3.4主键 primary key

主键是 唯一 和 非空的结合

主键是唯一的并且是非空的，每张表都有且只有一个主键字段，而且主键字段必须和业务逻辑无关。

create table person(
id int primary key,
name varchar(32)
);

3.5自增长 auto_increment

我们通常希望在每次插入新记录时，自动地创建主键字段的值

被设置为自增长的字段如果被删除数据，不会影响其自动创建的顺序

create table person(
id int primary auto_increment,
    name varchar(32)
);

4.时间字段的设置

类型	格式	用途	默认值
date（出现频率偏高）	YYYY-MM-DD	2023-04-08	无
time	HH:MM:SS	16:45:03	无
year	YYYY	2023	无
datetime	YYYY-MM-DD hh:mm:ss		无
timestamp（出现频率偏高）	YYYY-MM-DD hh:mm:ss	2023-04-08 16:47:08	current_timestamp

create table person(
id int primary key auto_increment,
    name varchar(32) not null,
    createTime timestamp default current_timestamp
);

5.外键约束

增删改和外键约束有关

# constraint 约束的意思
# fk_emp_dept 外键的名字  可以随意起名
# foreign key(dept_id) 外键 使用本表中的一个字段作为外键去和别的表产生关系
# references 关联，引用的意思。去关联主表里面的某一个字段
#（外键所在的表叫副表）
cerete table emp(
	constraint fk_emp_dept foreign key(dept_id)
    references dept(dept_id)
);

1.级联操作

加上外键约束后，

增加时：先看主表，然后增加副表

删除时：先删除副表，再删除主表

修改时：先修改副表，再修改主表

可以选择级联操作！

级联修改和级联删除！！！

create table emp(
	constraint fk_emo_dept foreign key(dept_id)
	references dept(dept_id)
	on delete cascade#级联删除
    on update cascade#级联修改
);

6.联表查询【重点】

1.常规的联表查询

select e.emp_name as "员工名",d.dept_name as "部门名"#查询什么 可以起别名
from emp e,dept d #从哪里查询 同时可以对表起别名
where e.dept_id = d.dept_id and e.emp_name 
="erbei" #表之间连接的字段  查询的条件  

2.内连接

inner join 与 jion是相同的

select #字段名 
from #表1名
inner jion #关联 表2名
on 字段名 = 字段名 #表之间的连接字段
where #查询条件

3.外连接【开发少用】

1.左外连接

left join 关键字会从左表返回所有的行，即使在右表中没有匹配的行。

select * 
from dept d
left outer join emp e
on d.dept_id = e.dept_id

2.右外连接

right join 关键字会从右表哪里返回所有的行，即使在左表中没有匹配的行。

select * 
from emp e
right outer join dept d
on e.dept_id = d.dept_id;

7.多表联查

1.一对多或者多对一查询

select teacher.t_name,student.s_name
from teacher,student
where teacher.t_id = student.teacher_id 
and teacher.t_name = "老邢";

2.多对多查询

#三表查询
select stu.s_name,course.c_name
from stu,stu_course,course
where stu.s_id = stu_course.s_id 
and stu_course.c_id = course.c_id
and stu.s_name = "司马懿";