数据库->文件集合 对文件的管理->数据库管理系统
mysql->命名管道,共享内存 socket不仅连本地,还能连远程
启动服务器进程->管理员模式
开启服务器:service mysqld start
连接服务器:mysql -u root -p root是管理员,-p是加密码
SQL语句(struct query language 结构化查询语句)
DDL( datadefine language) 数据定义语言,对结构的增、删、查、改 (库 表 索引 视图)
create,drop,alter,show
DML 数据操纵语言,对数据的增 、删、查、改
insert,delete,update,select
DCL 数据控制语言
grant 控制权限(自主权限控制,强制权限控制) revoke (回收)
DDL
1.创建库 // 不允许库重名
create database if not exists cy1705 ; database 库的标识,cy1705 库的名字
2.删除库
drop database if exists cy1705;
3.查看当前库 (默认本身存在4个库)
show database;
4.选定库
use cy1705;
1.创建表
学生表stu(学号,姓名,年龄,性别)
create table stu
(
id varchar(20) primary key, //字段名称,字段类型,字段约束(主键约束,外键约束,唯一约束,非空约束,默认为空)
name varchar(10),
age int,
sex enum("man","woman")
);
学生成绩表 result(学号 ,科目编号,成绩)
create table result(
id varchar(20) not null,
pid varchar(20) not null,
score float
);
insert into result values("001","p01",59),
("001","p02",50),
("001","p03",89),
("002","p01",54),
("002","p02",34),
("002","p03",76),
("003","p01",24),
("003","p02",56),
("003","p03",67),
("004","p01",94),
("004","p02",24),
("004","p03",45);
2.删除表
drop table ifexists stu;
3.修改表
1.修改字段属性 modify
alter table stu modify name varchar(20); //varchar(20)为新的类型
2.修改字段名称 change
alter table stu change name mname varchar(10); //新的字段名称为mname
3.添加新的字段 add after first
alter table stu add rank1 int after age; //在age后新增加了一个字段 rank 类型为int
4.删除一个字段 drop
alter table stu drop rank1;
5.修改表名 rename
alter table stu rename student; //新的表名为student
4.查询表
1.Show create table stu; //查询表名为stu 的信息(可查建表语句)
2.desc stu; //(可查表的字段信息)
DML (只操作表,不用加结构标志)
insert delete update select
1.插入数据
insert into stu values("002",
"lisi",
NULL,
"man");
insert into stu(id,mname,sex) values ("003","wangwu","woman")
批量插入 load source
insert into stu values
("004","zhaoliu",20,"woman"),
("005","maqi",19,"man");
2.删除数据 truncate->DDL
delete from stu; //删除stu表的所有元素
delete from stu where name = 'lisi'; //删除满足条件的元素,where name = 'lisi' 属于条件过滤
3.修改元素 update
update stu set age = 22; //stu为表名
update stu set age = 22 where id = "003";
4.查询元素
(1)普通查询 select*from stu; 查询表中所有信息
select * from stu where age > 20; //条件过滤查询 where age > 20
select id,mname,age,sex from stu;
select mname,age from stu where id = "001";
(2)去重查询 distinct
select distinct age from stu; //去掉年龄一样的
(3)排序 order by asc(升序) desc(降序)
select distinct age from stu order by age; (默认升序,可无,即按年龄升序排列)
(4)分组查询 group by
select id, sum(score) from result //count 可查符合条件的条数
group by id; //优先:条件过滤,分组
1.查询大于等于20岁的学生的不及格成绩
(1)等值查询 匹配时间太多,效率低
Select mname,age,score
From stu,result
Where stu.id = result.id and stu.age >= 20 and result.score < 60;
(2)链接查询 (外链接查询,内链接查询)
外链接查询
左外链接查询 缩小左表
右外链接查询 缩小右表
全外链接查询
<1> 左连接查询 左表条件必须满足,右表条件为空
select mname,score
from (select id,mname from stu where age >= 20) a
left join
(select id,score from result where score < 60) b
on a.id = b.id
where score is not null;
<2> 右连接查询 右表条件必须满足,左表条件为空
select mname,score
from (select id,mname from stu where age >= 20) a
right join
(select id,score from result where score < 60) b
on a.id = b.id
where mname is not null; //对匹配好的结果集进行过滤
<3> 全外连接查询 左,右表条件必须满足,结果都存在,有很多NULL(先写左,后写右)
select mname,score
from (select id,mname from stu where age >= 20) a
full outer join
(select id,score from result where score < 60) b
on a.id = b.id; //where两个条件间and,可过滤条件
<4> 内外连接查询 只筛选匹配的结果集
select mname,score
from (select id,mname from stu where age >= 20) a
inner join
(select id,score from result where score < 60) b
on a.id = b.id;
<5>.联合查询 union union all 左右字段类型必须匹配
create table teach(
tid varchar(20) primary key,
tname varchar(10),
age int,
sex enum("man","woman")
);
insert into teach values
("001","zhang",35,"man"),
("002","yang",18,"man");
1.查询所有师生的编号,姓名,年龄
select id,mname,age from stu
union //union 自带去重
select tid,tname,age from teach;
select id from stu
union all //union all不去重
select tid from teach;