一、什么是数据库?
答:数据库(Database)是按照数据结构来组织、存储和管理数据的建立在计算机存储设备上的仓库。
其实就是文件的集合。
二、SQL语言的分类
1、数据定义语言(DDL):
1.DDL
数据库 数据表
information_schema 对象信息 列信息,用户信息,权限信息,字符集
mysql 用户的权限信息
test
cluster 集群信息
1.创建数据库 create database Cy1703;
2.查询 show databases;
3.删除 drop databases Cy1703;
1.建表
(1)建一个学生成绩 name age sex score;
create TABLE Stu(
id varchar(20) primary key,
name varchar(20) not null,
age int default null,
sex varchar(8),
score float
);
2.删除表
drop TABLE tablename;
3.查询表结构SHOW
show Create table tablename;
还可以用DESC。
4.表的修改alter
(1)修改字段属性 alter table Stu MODIFY sex varchar(10);
(2)添加字段
alter table Stu ADD height2 float not NULL AFter sex;
(3)删除一个字段
alter table Stu DROP height2;
(4)修改字段名称
alter table Stu CHANGE sex mysex enum("man","woman");
(5)修改表名
alter table Stu RENAME student;
2、数据操作语言(DML:Data Manipulation Language):
其语句包括动词INSERT,UPDATE和DELETE。它们分别用于添加,修改和删除表中的行。也称为动作查询语言。
DML 数据表 insert|load|replace delete update select
1.数据插入zhangsan 10 man 98.6
insert into Stu value("zhangsan",10,"man",98.6);
insert into stu(name,mysex) value("lisi","man");
insert into stu value("zhangsan",10,"man",78.1),
("lisi",10,"man",98.6),
("lisi",10,"man",59.4),
("lisi",10,"man",65.5);
还有source 、load 、 replace
1 张三 10 man 98.6
insert
1 李四 12 man 99.6
replace
2.数据的删除 delete
(1)删除表中的所有数据
DELETE FROM stu;
(2)删除符合条件的数据
DELETE FROM stu where name = "lisi";
(3)数据的修改 update
update stu SET score = 76 where name = "zhangsan";
(4)数据的查询select
1.select * from Stu;
2.select name,age,mysex,score from Stu;
去重 distinct
1.select distinct name from stu;
2.排序 order by asc desc 分组 group by
select name,score from stu
order by score desc;
select name, Sum(score) all_score from stu
group by name
having Sum(score) > 100;
3.等值查询
(1)基本信息表 id name zhangsan
create table info(
id int primary key,
name varchar(20) not null
);
(2)工资表 id mysalary
create table salary(
id int,
mysalary float
);
select mysalary from salary a,
(select id infoid from info where name = "zhangsan") b
where a.id = b.infoid;
select info.id,name,mysalary from info,salary
where info.id = salary.id;
insert into info value(1,"zhangsan"),
(2,"lisi"),
(3,"wangwu"),
(4,"maliu");
insert into salary value(1,5000),
(1,7800),
(3,6000),
(3,6000),
(3,6100);
4.连接查询
(1)左连接
select b.name,a.mysalary from salary a
left join (select id,name from info where name= "zhangsan") b
on a.id = b.id
where b.name is not null;
(2)右连接
select b.name,a.mysalary from salary a
right join (select id,name from info where name in("lisi","zhangsan")) b
on a.id = b.id
where a.mysalary is not null;
(3)内连接inner
5.子查询
select mysalary from salary
where id in (select id from info where name = "zhangsan");
6.联合查询union 、union all
create table money
(
id int,
mymoney float
);
insert into money value(1,2000),
(3,5000);
select id,mysalary from salary where id = 1
union all
select id,mymoney from money where id = 1;
insert into salary value(1,2000);
select sum(mysalary) + sum(mymoney)
from salary,money
where salary.id = 1 and money.id = 1;
alter table salary add loadtime datetime;
insert into salary value(2,7000,now());
3、数据控制语言(DCL):
它的语句通过GRANT或REVOKE获得许可,确定单个用户和用户组对数据库对象的访问。某些RDBMS可用GRANT或REVOKE控制对表单个列的访问。
grant:给某个用户赋予针对于某个库或者表的某个权限
revoke:回收权限
all all all
1.root ==> user1 ==> user2 ==> user3