You辉编程_MySql数据-CSDN博客

本文链接：https://blog.csdn.net/weixin_53781798/article/details/126724061
一、数据库的基本操作

1.显示所有数据库
show databases;

2.创建数据库
create database + 数据库名字;

3.删除数据库
drop database + 数据库名字;

4.使用数据库
use + 数据库名 


二、数据库表的基本操作

1.创建表
create table 表名(
	字段名 数据类型 [约束条件]
);

约束条件
primary key 主键
foreign key 外键--->constraint 外键名 foreign key 外键字段 references 关联的表(`id`)
not null 不能为空
unique 唯一
auto_increment 自增
default 默认

eg:
create table t_bookType(
	id int primary key auto_increment,
	bookTypeName varchar(20),
	bookTypeDesc varchar(200)
);

插入数据
insert into t_bookeType(id,bookType,bookTypeDesc) values(1,"java编程","张三");

create table t_book(
	id int primary key auto_increment,
	bookName varchar(20),
	author varchar(10),
	price decimal(6,2),
	bookTypeId int,
	constraint `fk` foreign key (`bookTypeId`) references `t_bookType`(`id`)
);

2.查看表
-desc t_book; 查看表结构
-show create table; 查看创建表的语句

3.修改表
alter table 旧表名 rename 新表明--->修改表名
eg: alter table t_book rename r_book1;

alter table 表名 change 旧字段 新字段 新数据类型(不变就写原来的)--->修改字段
eg: alter table t_book change bookName bookName1 varchar(20);

alter table 表名 add 字段名1 数据类型 [约束条件] [first|after 字段名2]--->增加字段
eg: alter table t_book add testFile int after author;  在author字段之后添加testFile字段  

alter table 表名 drop 字段名--->删除字段
eg: alter table t_book drop testFile

3.删除表
drop table 表名

三、查询数据

(一)单表查询
1.查询所有字段
select 字段1，字段2，字段3... from 表名
select * from 表名

2.查询指定字段
select 所要查的字段 from 表名

3.where条件查询
select 字段1，字段2，字段3... from 表名 where 条件表达式

4.in 包含查询(包含哪些)
select * from 表名 where  字段名 in (包含的元素)
eg: select * from student where age in (18,22,25);--->查询年龄18，22，25的学生
补：not in 表示表示不包含

5.between and范围查询
select * from 表名 where age between 21 and 24;
补：not between and 表示不在该范围

6.like模糊查询
select * from student where name like '张三%';--->任意
select * from student where name like '张三_';--->剩余占一个字符
select * from student where name like '%张三%';--->包含

7.控制查询
slect * from student where ssex is null;--->查性别为空的字段
补：is not null 查询不为空的字段

8.and 多条件查询
select * from student where gradeName='大一' and age = 20;

9.or 多条件查询
select * from student where gradeName='大一' or age = 20;

10.distinct 去重复查询
select distinct gradeName from student

11.对查询结果排序
select * from student  order by age asc;--->按年龄升序(默认)
补：desc 降序 

12.group by 分组查询(归类)
注：group by单独使用没有意义；
		一般结合聚合函数一起使用；
		如：与 group_count()函数、having(限制输出结果)、with rollup(最后加入一个总和行即总计)
select 年级字段,group_concat(name) from student group by 年级字段--->根据年级分组，并显示同一年级的所有学生
select 年级字段,count(name) from student group by 年级字段--->查询每组年级有多少学生
select 年级字段,count(name) from student group by 年级字段 having count(name)>3--->查询人数大于3的年级
select 年级字段,count(name) from student group by 年级字段 with rollup--->在最后一行加入总计

13.limit分页查询
select * from student limit (0,5)--->第一页的5条数据
select * from student limit (5,5)--->第二页的5条数据

14.使用聚合函数查询
(1)count()函数记录总条数与group by 一起使用
select name,count(*) from 成绩表名 group by name--->查询学生选的课程有几门,按姓名分组

(2)sun()函数用来求和与group by 一起使用
select name,sum(score) from 成绩表名 group by name--->查询每个学的总成绩，按姓名分组

(3)avg()函数用来求平均数与group by 一起使用
select name,avg(score) from 成绩表名 group by name--->查询每个学的平均分，按姓名分组

(4)max()函数用来求最大值与group by 一起使用
select name,max(score) from 成绩表名 group by name--->查询每个学生的最高分，按姓名分组

(5)min()函数用来求最小值与group by 一起使用
select name,min(score) from 成绩表名 group by name--->查询每个学生的最低分，按姓名分组

(二)连接查询
1.内连接(最常用) as 关键字用来起别名的
select tb.bookName,tb.author,tby.bookTypeName from t_book as tb,t_bookType as tby where tb.bookTypeId=tby.id;

2.外连接
(1)左连接，查出第一张表所有数据，第二张只查询匹配字段
select tb.bookName,tb.author,tby.bookTypeName  from t_book as tb left join t_bookType as tby on tb.bookTypeId=tby.id;

(2)右连接
select tb.bookName,tb.author,tby.bookTypeName  from t_book as tb right join t_bookType as tby on tb.bookTypeId=tby.id;

3.多条件查询
select tb.bookName,tb.author,tby.bookTypeName from t_book as tb,t_bookType as tby where tb.bookTypeId=tby.id and tb.price>33;

(三)子查询 
1.in 关键字的子查询
select * from t_book where bookTypeId in (select id form t_bookType);

2.比较运算符的子查询
select * from t_book where price>=(select price from t_price priceLevel=1);

3.exists 关键字的子查询,只有子查询为true，才会进行外层查询
select * from t_book where exists (select * from t_bookType);

4.any关键字的子查询,满足任意一个条件即可
select * from t_book where price>= any(select price from t_price);

5.all关键字的子查询,需要满足所有条件
select * from t_book where price>= all(select price from t_price);

(四)合并查询
1.union 关键字会去除合并表的重复部分
select id form t_book union select id form t_bookType;

2.union all 关键字不去重
select id form t_book union all select id form t_bookType;

四、插入、更新和删除数据
1.插入数据
(1)给表所有字段插入数据
insert into 表名 values(值1，值2，值n);

(2)给表指定字段插入数据
insert into 表名(字段1，字段2，字段n) values (值1，值2，值n);

(3)同时插入多条数据
insert into 表名(字段1，字段2，字段n)
values 
(值1，值2，值n)
(值11，值21，值nn)
...

2.更新数据
update 表名 set 需要修改的字段名="修改的内容"，需要修改的字段名2="修改的内容2"... where id = 1;

3.删除表中数据
delete from 表名 where id=6;

五、索引
1.索引概述
索引是由数据库表中一列或多列组成，类似于图书的目录。

2.索引优缺点
优：增加了查询的速度
缺：创建和维护需要增加时间

3.索引的分类
(1)普通索引，可以创建在任何数据类型中。
(2)唯一性索引，使用unique参数可以设置，在创建唯一索引时，限制该索引的值是唯一的。
(3)全文索引，使用fulltext参数可以设置，只能创建在char,varchar,text类型的字段上，MySQL默认引擎不支持。
(4)单列索引，给单个字段创建索引。
(5)多列索引，在表的多个字段上创建索引。
(6)空间索引，使用spatial参数可以设置空间索引。

4.索引创建的方式
(1)创建表时创建索引
create table 表名(
	id int,
  username varchar(20),
	password varchar(20),
	index (username)--->创建单列索引
);

create table 表名(
	id int,
  username varchar(20),
	password varchar(20),
	unique index (username)--->创建唯一索引
);

create table 表名(
	id int,
  username varchar(20),
	password varchar(20),
	unique index index_username(username)--->给索引起别名
);

create table 表名(
	id int,
  username varchar(20),
	password varchar(20),
	index index_username_password(username,password)--->创建多列索引
);

(2)在已存在的表上创建索引
create index index_username on 表名(username);--->普通索引
create unique index index_username on 表名(username);--->唯一性索引
create index index_username_password on 表名(username,passwrod);--->多列索引

(3)用alter table语句创建索引
alter table 表名 add index index_username(username);--->普通索引
alter table 表名 add unique index index_username(username);--->唯一性索引
alter table 表名 add index index_username_password(username,password);--->多列索引

5.删除索引
drop index 索引名 on 表名

六、视图
1.视图概述：是一张虚拟的表，是从数据库中一个或多个表中导出来的表。

2.视图的作用：使操作简单化、增强数据的安全性、提高表的逻辑独立性。

3.视图的创建
(1)在单表上创建视图
create view 视图名 as select * from t_book;--->给所用字段加视图
select * from 视图民;--->查出来其实就是一张表

create view v2 as select bookName,price from t_book;--->给指定字段加视图
create view v2(b,p) as select bookName,price from t_book;--->b是bookName的别名，p是price的别名
select * from v2;

(2)在多表上创建视图
create view v3 as select bookName,bookType from t_book,t_bookType where t_book.bookTypeId=t_bookType.id;

4.视图的查看
desc v3;--->查看视图的结构
show table start like 'v3';--->查看视图的状态
show create view v3;--->查看视图的详情信息

5.修改视图
(1)create or replace view语句修改视图
create or replace view v3(bookName,price) as select bookName,price from t_book;--->修改为只有bookName,price两个字段

(2)alter 语句修改视图
alter view v3 as select * from t_book;--->还原所有字段

6.更新视图(和表的操作一样)
(1)insert 出入数据
insert into v3(null,'java编程思想',99,'zhangsan',1);

(2)update 更新数据
update v3 set bookName='java编程思想2',price=120 where id=1;

(3) delete 删除数据
delete v3 where id=1;

7.删除视图
drop view if exists v3;

七、触发器
1.触发器的概述
是由事件触发某个操作，这些事件包括：insert,update,delete语句，当数据库系统执行这些事件时，
就会激活触发器执行相应的操作。

2.触发器的创建和使用
(1)创建只有一个执行语句的触发器
create trigger 出发器名 before|after 出发事件
on 表名 for each 执行语句

eg:添加一条记录时做数量+1的操作
create trigger trigger_book after insert 
on t_book for each row 
update t_bookType set bookNum=bookNum+1 
where new.bookTypeId=t_bookType.id

-当添加一条记录，触发器激活，数量增加1
insert into t_book values(null,'js编程','lisi',1);

(2)创建多个执行语句的触发器
create trigger 触发器名 before | after 出发事件
on 表名 for each row 
begin 
执行语句列表
end 

补：DELIMITER：分割符
DELIMITER |
create trigger trigger_book2 after delete
on t_book from each row 
bigint 
update t_bookType set bookNum=bookNum-1 where old.bookTypeId=t_bookType.id;
insert into t_log values(null,NOW(),'在book表删除了一条数据');
delete from t_test where old.bookTypeId=t_test.id;
end 
|
DELIMITER;

delet from t_book where id=1;

3.查看触发器
show triggers 

4.删除触发器
drop trigger 触发器名

八、MySQL常用函数
1.日期和事件函数
(1)curdate() 返回当前日期
(2)curtime() 返回当前时间
(3)month() 返回月份

eg:select curdate(),curtime(),month(brithday) from t_user;

2.字符串函数
(1)char_length(s) 计算字符串s的字符数
(2)upper(s) 把所有字母变成大写字母
(3)lower(s) 把所有字母变成小写字母

eg:select username,char_length(username),upper(username),lower(username) from t_user;

3.数学函数
(1)abs(x) 求绝对值
(2)sqrt(x) 求平方根
(3)mod(x,y) 求余

eg:select num,abs(num) from t_user;

4.加密函数
(1)password(str) 对密码进行加密 不可解密
(2)md5(str) 普通加密 不可解密
(3)encode(str,pwd_str) 加密函数，结果是一个二进制
(4)decode(crypt_str,pwd_str) 解密函数

eg:insert into t_user values(null,'2000-10-12','张三',1,password('123456'));