一、数据库的基本操作
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'));
You辉编程_MySql数据
于 2022-09-06 14:09:14 首次发布