SQL基础操作
DDL 数据定义语言：create drop alter show
一：库
创建库：creat database [if not exists]name; db：数据库
删除库：drop databsae [if exists]name; if exists:如果存在
查看库：show databases;
二：表
创建表 char(128):定长
create table tb_name( varchar(128):不定长(申请大小)
字段名称 字段类型[字段约束] [注释], tb:表
字段名称 字段类型[字段约束] [注释], 注释： comment “”
字段名称 字段类型[字段约束] [注释], 注意事项：
字段名称 字段类型[字段约束] [注释] 逗号隔开，最后一列不添加逗号
); 分号结束，字段类型的大小()
create table stu(
id char(10) primary key comment "学号",
name varchar(20)not null comment "姓名",
age int not null comment "年龄",
sex enum("man","woman") not null comment "性别"
);
字段约束：
主键 非空+唯一 primary key
外键 foreign key
唯一 unique
非空 not null
为空 default null(默认)
删除表：drop table tb_name / 字段名称 / 字段约束； show warning：显示警告内容
查看表：显示有那些表: show tables;
查看表的创建过程： show create table tb_name；
查看某一个表中的所有字段: desc tb_name；
表修改：修改字段名称 change
alter table tb_name change oldname newname 新字段类型;
alter table stu change name mname varchar(10);
修改字段类型 modify
alter table tb_name modify 字段名称 新字段类型;
alter table stu modify id varchar(10);
添加新的字段 add first/after first：放在第一给位置 after 某一字段名：放在某一字段后
alter table tb_name add 字段名称 字段类型 [字段约束][注释] first；
alter table stu add socre float after id;
删除一个字段 drop
alter table tb_name drop 字段名称；
alter table stu drop socre；
修改表名称 rename
alter table tb_name new_tb_name;
alter table stu student;
DML数据操纵语言：insert delete update select
表：
插入数据 insert单行插入(小批量) load：大批量加载 source replace单行插入(delete+insert)(删除旧的+新插入)
insert into tb_name values("id","name","age","sex");
insert into tb_name(id,name,age) values("id","name","age");
insert into stu values("id","name","age","sex"),
("id","name","age","sex"),
("id","name","age","sex"); 批量插入
insert into stu value("001","zhangsan","20","man");
insert into stu(id,name,age) value("006","lisi","18");
insert into stu values("001","zhangsan","18","man"),
("002","lisi","17","woman"),
("003","wangwu","20","man");
删除数据 delete DDL：truncate
delete from tb_name；删除所有数据
delete from tb_name where 字段名称 = “数据字段”：删除单独数据
delete from stu where id = "004";
修改数据 set
update tb_name set 字段名称 = 修改为何值
update tb_name set 字段名称 = 修改为何值 where 字段名称 = “字段数据”
update stu set age = 19;
update stu set age=19 where id = "002";
查询数据
1、普通查询：
select * from tb_name;
select 字段名称，字段名称，字段名称 from tb_name;
select * from stu;
select id，name,age,sex from stu;
2、去重查询：distinct
select distinct 字段名称 from tb_name；
select distinct age from stu；
3、排序： order by asc：升序 desc：降序
select distinct age from stu
order by age;
select distinct age from stu
order by age desc;
4、分组： group by
select id,sum(score)
5、等值查询：缺点速度太慢，耗费大量时间空间
查询年龄大于20的同学不及格的成绩
select name,score
from stu,result
where stu.id =result.id and age>20 and score<60;
6、链接查询：
<1>、左链接查询：
select 字段名称,字段名称
from
(select 字段名称，字段名称 from 字段名称 where 查询条件)[as] a
left join
(select 字段名称，字段名称 from 字段名称 where 查询条件)[as] b
on a.id = b.id;
二次筛选：
where 字段名称 is not null;
select name,score
from
(select id,name from stu where age > 21)[as] a
left join
(select id,score from result where score < 60)[as] b
on a.id = b.id
where score is not null;
2、右链接查询：
select 字段名称,字段名称
from
(select 字段名称，字段名称 from 字段名称 where 查询条件)[as] a
right join
(select 字段名称，字段名称 from 字段名称 where 查询条件)[as] b
on a.id = b.id;
二次筛选：
where 字段名称 is not null;
select name,score
from
(select id,name from stu where age > 21)[as] a
right join
(select id,score from result where score < 60)[as] b
on a.id = b.id
where score is not null;
3、全外链接：（目前不支持）
select 字段名称,字段名称
from
(select 字段名称，字段名称 from 字段名称 where 查询条件)[as] a
full join
(select 字段名称，字段名称 from 字段名称 where 查询条件)[as] b
on a.id = b.id;
二次筛选：
where 字段名称 is not null;
select name,score
from
(select id,name from stu where age > 21)[as] a
full join
(select id,score from result where score < 60)[as] b
on a.id = b.id
where score is not null;
4、内链接：
select 字段名称,字段名称
from
(select 字段名称，字段名称 from 字段名称 where 查询条件)[as] a
inner join
(select 字段名称，字段名称 from 字段名称 where 查询条件)[as] b
on a.id = b.id;
select name,score
from
(select id,name from stu where age > 21)[as] a
inner join
(select id,score from result where score < 60)[as] b
on a.id = b.id
7、联合查询：union
联合查询：
select id,name,age,sex from stu union all
select id,name,age,sex from teacher;
单个查询：
select id,name,age,sex from teacher;
DCL数据控制语言，权限管理 grant revoke
1、创建用户
create user u4@localhost
2、授权
grant all on cy1201.*to u4;
3、回收
revoke select on cy1202.*from u4;
基础操作举例：
create table stu(
id char(10) primary key comment "学号",
name varchar(20)not null comment "姓名",
age int not null comment "年龄",
sex enum("man","woman") not null comment "性别"
);
insert into stu values("001","zhangsan","18","man"),
("002","lisi","17","woman"),
("003","wangwu","20","man");
create table result(
id char(10) comment "学号",
pid char(10)not null comment "课程号",
score int not null comment "成绩"
);
insert into result values("001","p01","78"),("001","p02","65"),("001","p03","58"),
("002","p01","80"),("002","p02","95"),("002","p03","66"),
("003","p01","55"),("003","p02","69"),("003","p03","78");
create table teacher(
id char(10) primary key comment "学号",
name varchar(10)not null comment "姓名",
age int default null comment "年龄",
sex enum("man","woman"comment "性别"
);
insert into teacher values("t01","yang","18","man"),
("t02","su","17","man"),
("t03","he","16","man");
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
