一、数据库管理
1.创建数据库
create database A;
2.查询当前连接下的所有数据库
show database;
3.使用数据库
use A;
4.删除数据库
删除数据的方式有三种：delete < truncate < drop
（1）delete from table_name where xxx;
有条件的删除并不会真的把数据删除，可以通过事务回滚机制找回rollback
（2）truncate table table_name;
执行后立即生效，无法找回，执行前请确认是否买好机票跑路！
（3）drop table table_name;
与truncate一样，shan库跑路
二、表的管理
1.建表
create table student（
Id int,
Name varchar(20),
Grade int
）;（1）mysql常用数据类型：
整型：int
浮点型：float
字符型：varchar/char
日期型：date
（2）建表通常伴随着约束
主键约束：primary key ，主键指的是主关键字
非空约束：not null ，如果某个字段设置了该约束，那么该字段不能取null值
唯一约束：uniqe ，如果某个字段设置了该约束，那么该字段禁止更新为重复的值
主 外 键约束：如果某个字段，在一张表里是主键，然后它出现在在另一张表中，那么就称之为另一张表的外键
--主外键关联：
create table student(
id int primary key,
name varchar(20)
);
insert into student(id,name) values(1,'su');
--关联的表，数学成绩表
create table math_grade(
id int primary key,
grade int,
constraint fk_mg_id foreign key(id) references student(id)
);
insert into math_grade(id,grade) values(1,99);
insert into math_grade(id,grade) values(2,99);2.查看表结构
desc student;
查看数据库中所有表
show tables;
3.表的修改
修改表名：alter table student rename to student2;
修改字段名：alter table student2 change name username varchar(20);
添加字段：alter table student2 add age int;
4.增删改查
auto_increment：约束的一种，表示自动增长，如果在表中插入行的时候，没有给该字段赋值，那么会自动产生一个唯一的标识。
create table student(
id int primary key auto_increment,
name varchar(20) not null,
grade int,
gender varchar(5）
);
（1）一次插入一条数据
insert into sutdent(name,grade,gender)
values('zhangsan',88,'man');
一次插入多条数据
insert into student(name,grade,gender)
values('su',77,'female'),
('xue',66,'male'),
('feng','female');
（2）数据查询操作
简单查询
--查询学生表所有字段信息
select * from student;
--查询学生表里的学号和姓名
select no,name from student;条件查询
--查询张三的全部信息
select * from student where name='张三';
--查询年龄大于20岁的学生信息
select * from student where age>20;
--查询张三和李四的信息
select * from student where name='张三' and name='李四';
--查询张三或者李四
select * from student where name='张三' or name='李四';
--查询除了张三以外的学生信息
select * from student where not name='张三';
--查询既不是张三也不是李四的学生
select * from student where not (name='张三' or name='李四');
/*
模糊查询常用的通配符：
%：表示0,1或者多个字符
_：表示1个字符
in：在。。。里面
not in：不在。。。里面
is null：是空值
is not null：不是空值
between...and...：范围查询
*/
--查询张姓的学生信息
select * from student where name like '张%';
select * from student where name like '张_';
--查询籍贯是北京、山东和上海的学生信息
select * from student where jg in('北京','山东','上海');
--查询除了北京、山东和上海的学生信息
select * from student where jg not in('北京','山东','上海');
--查询没有成绩的学生学号
select no from grade where score is null;
--查询18到21岁的学生信息
select * from student where age between 18 and 21;（3）排序显示
--按成绩排序
select * from grade order by score asc --升序
select * from grade order by score desc --降序
--将学生表的所有信息按年龄从大到小排序，如果年龄相同，按学号从小到大排序
select * from student order by age desc,no asc;
（4）聚合函数查询
聚合函数一般用于分组统计
/*
常见的聚合函数有：
count():统计个数
avg()：求平均值
sum()：求和
max()：求最大值
min()：求最小值
*/
--查询学生表里有多少个学生
select count(*) from student;
--查询001号学生的平均成绩、最大成绩、最小成绩、成绩总分
select avg(score),max(score),min(score),sum(score) from grade where no='001';
（5）分组查询
--查询每个学生的总成绩
select no,sum(score) from grade group by no;
--查询有两门及以上课程不及格的学生的学号
select no
from grade
where score<60
group by no
having count(*)>1;
（6）数据的修改
如果不跟条件，所有的记录都会被修改，如果有条件，只有满足条件的 记录会被修改。
--将成绩表里所有人的成绩减少2分
update grade set score=score-2;
--将成绩表里001的成绩减少2分
update grade set score=score-2 where no='001';
--将张三的成绩更新为89，籍贯更新为广东
update student set score=89,jg='广东' where name='张三';
（7）表中数据的删除
delete from table_name [where]
如果不跟条件，删除表里的所有数据（保留表结构），如果跟条件，删除满足条件的记录。
--删除某一个数据
update student set score=null where name='ee';
--删除某一列数据(保留表头)
alter table student drop score;
--删除某一列数据（不保留表头）
update student set score =null;（8）多表查询
--查询选修了java课程全部学生的总成绩
--1.在课程表里根据java课程找到对应的课程号
select kch from kcb where kcm='java';
--2.在成绩表里根据课程号找到对应的成绩
select cj from cjb where kch=(select kch from kcb where kcm='java');
--3.使用sum函数求和
select sum(cj) from cjb where kch=(select kch from kcb where kcm='java');
--查询1班叫张三的同学的java的成绩
--1.确定1班张三对应的学号
select xh from student where bj='1班' and name='张三';
--2.确定java对应的课程号
select kch from cjb where kcm='java';
--3.在成绩表里查询java成绩
select cj from cjb
where xh=(select xh from student where bj='1班' and name='张三';)
and kch=(select kch from cjb where kcm='java');（9）关联查询
将多张表连接成一张大的表，然后从表中查询特定的数据
--查询张三同学的各科成绩，要求显示的字段有姓名、课程号和成绩
select student.xm,cjb.kch,cjb.cj
from student,cjb
where student.no=cjb.no and name='张三';
內联接：多表关联的一种，只返回满足关联条件的结果集。
外联接：
左外联接：多表关联的一种，指的是除了返回满足关联条件的结果集，还会把左边的那张表完整地展示出来，右边那张表里不满足关联条件的字段位置补空值（null）。
右外联接：多表关联的一种，指的是除了返回满足关联条件的结果集，还会把右边的那张表完整地展示出来，左边那张表里不满足关联条件的字段位置补空值（null）。
全外联接：多表关联的一种，指的是除了返回满足关联条件的结果集，还会把两边的表完整地展示出来，不满足关联条件的字段位置补空值（null）。
--查询所有学生的课程和成绩，要求显示学号、姓名、课程号、成绩（没有成绩记录的学员信息也要查询出来）
select student.no,student.name,cjb.kch,cjb.cj
from student,cjb
where student.no=cjb.no;
--以上方法，没有成绩的学生不能查询出来
--左外联接
select student.no,student.name,cjb.kch,cjb,cj
from student left join cjb on student.no=cjb.no;
--右外联接
select student.no,student.name,cjb.kch,cjb,cj
from cjb right join student on student.no=cjb.no;
--全外联接：通常使用full join来实现，但是mysql不支持full join，在oracle中支持full join，mysql通过union集合操作
select student.no,student.name,cjb.kch,cjb,cj
from student left join cjb on student.no=cjb.no
union
select student.no,student.name,cjb.kch,cjb,cj
from cjb right join student on student.no=cjb.no;
--去重查询：使用distinct关键字
--查询学生表中学生来自的省份（重复的省份只显示一次）
select distinct jg from student;（10）正则表达式查询
正则表达式（regular expression）描述了一种字符串匹配的模式，有点类似于like，但是比like强大。
^：代表开头——^aaa：代表以aaa开头的字符
$：代表结尾——c$：代表以c结尾的字符
[]：代表一个范围，在里面任取其一——[abc]：代表此处要么是a要么是b要么是c
--查询名字以L开头的记录
select * from info where name regexp '^l'; --此写法不区分大小写
select * from info where name regexp binary '^l' --此写法区分大小写
--查询名字以C结尾的记录
select * from info where name regexp 'c$';
--查询名字中包含c或e或o的记录
select * from info where name regexp '[ceo]';
三、MySQL的高级特性
1.事务（transaction）
（1）什么是事务？
例如：a要给b转账100元，本质是对账户的余额字段做更新操作
--需求：要求这两个sql语句要么同时成功，要么同时失败，否则将引发纠纷
--这里的转账就是事务，事务指的是一组sql语句，只允许他们同时成功或者同时失败
update xx set banlance=banlance-100 where countname='a';
update xx set banlance=banlance+100 where countname='b';
（2）事物的4个特性
- 原子性——一个事务中的所有操作，要么全部成功要么全部失败，不会出现中间状态，将这一组sql语句看成一个整体，不可分割。
- 隔离性——一个事务在其操作期间，别的事务不可以对其进行干扰。
- 永久性——一个事务处理结束后，对数据的修改是永久的，即便是系统发生了故障也不会丢失。
- 一致性——在事务开始之前和事物结束之后，数据库的完整性没有被破坏。
（3）事物的代码实现
mysql手动开启事务：start transaction;
事务提交：commit;
事务回滚：rollback; --没有提交的事务允许回滚
（4）事物的示例
--数据准备
create table account(
id int auto_increment primary key,
name varchar(50) not null,
money float
);
insert into account(name,money) values('a',1000);
select * from account;
--如果开启了事务，但是没有提交，你所做的数据修改只保存在客户端，没有修改到服务器
start transaction;
update account set money=money-100 where name='a';
update account set money=money+100 where name='b';
rollback;
--mysql的默认设置，事物都是自动提交的，即执行mysql语句后会马上commit，可以使用start transaction显式的开启一个事务，或者执行set autocommit=0来禁止自动提交
commit;
--在没有提交之前如果想撤销修改，可以使用rollback，对于已经提交的，无法rollback
2.存储过程
（1）需求：比如遇到一些sql语句需要反复调用，每次都要去编写比较耗费精力，好的做法是：定义一串代码，取个名字，将经常使用的sql语句放进去，以后需要使用这个sql语句，可以通过名字对他进行调用。
存储过程：将常用的sql语句存放起来，方便以后重复调用。
（2）如何编写存储过程
delimiter //
create procedure 存储过程名字([形式参数])
begin
常用的sql语句;
end //
delimiter;
/*
delimiter // --将默认的sql的分号分隔改成//分隔
delimiter; --将//分隔还原成;分隔
*/
--存储过程的调用格式
call 存储过程名字([实际参数])（3）示例1
--数据准备：
create table student(
id int primary key auto_increment,
name varchar(20) not null,
grade float,
gender char(2)
);
--数据插入：
insert into student(name,grade,gender)
values('jack',60,'男'),
('rose',80,'女'),
('lucy',100，'女');
--创建并调用存储过程
delimiter //
create procedure x()
begin
select * from student;
end //
delimiter;
call x()（4）示例2
存储过程支持输入参数和输出参数，参数存放在存储过程名字后的小括号中。
需求：用户输入女生，查询出女生的数量；输入男生，查询的是男生的数量。
delimiter //
create procedure y(in s_gender varchar(50),out num int)
begin
select count(*) into num from student where gender=s_gender;
end //
delimiter;
--in s_gender varchar(50),out num int
--in后面跟的是输入参数s_gender存放用户输入的内容
--out后面跟的是输出参数，num存放的是存储过程的结果
--计算女生的数量：
call y('女',@num);
select @num;
select @num as '女生数量'; --给@num取别名“女生数量”
--计算男生的数量：
call y('男',@num);
select @num;3.触发器
需求：往student表插入数据的时候能够触发另外一个表timelog插入内容。触发的过程就是触发器。
--创建触发器的语法：
delimiter //
create trigger 触发器名字 触发时机 for each row
触发的内容; //
delimiter;
--数据准备，创建一个timelog表
create table timelog(
id int primary key auto_increment,
savetime varchar(50) not null
);
--写一个触发器实现：当往student插入数据的时候，它会触发往timelog表插入当前时间。
delimiter //
create trigger z before insert on student for each row
insert into timelog(savetime) values(nowtime); //
delimiter;
--触发器的效果
insert into student(name) values('zhangsan');
--会触发往timelog表里插入当前时间记录
select * from timelog;补充1：
查看触发器：show triggers;
删除触发器：drop trigger 触发器名字;
查看存储过程：show procedure status like '%';
删除存储过程：drop procedure 存储过程名字;
触发器是在一张表中做操作（插入、修改、删除，不能是查询操作，查询无法引发触发器），引发另外一张表的操作。
补充2：
1）order by：排序
order by 字段名1 asc/desc,字段2 asc/desc
说明：
asc：ascend 升序
desc：descend 降序
2）存储在数据库里的数据是区分大小写的，但是sql语句里的关键字、表名、字段名等都是不区分的
3）select distinct 表示的是：distinct的作用是将重复的记录去掉，只保留一份，所以指的是行（记录）都不相同
4）聚合函数不允许出现在where子句中。
扫一扫
5万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
