数据库基本信息与mysql常用命令

数据库分类
大型:oracle,db2
中型:MySQL、
小型:sqlite
关系型数据库:oracle,db2,MySQL,sqlite
非关系型数据库:redis,mongodb
DML(data manipulation language) 数据操纵语言 select update delete insert
DDL(data definition language)数据定义语言 CREATE、ALTER、DROP
DCL(Data Control Language)数据控制语言 grant、revoke

数据类型的属性
NULL 空
NOT NULL 数据列不允许包含NULL值
DEFAUL 默认值
PRIMARY KEY 主键
AUTO_INCREMENT 自动递增，适用于整数类型
UNSIGNED 无符号
CHARACTER SET 指定一个字符集

创建数据库
create DATABASE 2002C CHARACTER set utf8;

使用数据库
use 2002c;

创建student表
create table student(
id int PRIMARY key auto_increment,
name VARCHAR(255),
sex VARCHAR(255),
score FLOAT(5,2)
)

新增数据
单条
insert into student values(2,‘张博1’,‘男’,‘72.56’)；
多条
insert into student values(2,‘张博1’,‘男’,‘72.56’),(3,‘张博2’,‘男’,‘72.56’),(4,‘张博3’,‘男’,‘72.56’);

查询
所有
select * FROM student;
约束条件
select * from student where score >60;
select * from student where score BETWEEN 70 and 80;
select * from student where score >70 and score<80;
select * from student where score in(72.56)
select * from student where score >90 or score<30;
模糊查询
select * from student where name like ‘张%’
select * from student where name like ‘%张%’
select * from student where name like ‘%张’
分组
select count(*),sex from student group by sex;
排序
select * from student order by score desc;倒序
select * from student order by score ;正序
分页显示
select * from student limit 1,2 第一个数据是显示起始+1,第二个数据是显示多少条数据的

修改
按条件修改
update student set sex=“男” where name=‘张三’
修改全部
update student set sex=“男”
物理删除:真删除
逻辑删除:修改字段
删除
按条件删除
delete from student where id=5
删除全部
delete from student
查询表创建
show create table student
查询表结构
desc student
修改表结构
alter table classroom add sid int(12)

多表联查

right join 右连接 以右边表为主，左边表没有的用null代替
left join 左连接 以左边表为主，右边表没有的用null代替
inner join 内连接
full join (不适用mysql，适用oracle) UNION 左外连接+ union+右外连接实现

两表联查
select student.name,classroom.room from student inner join classroom on student.id=classroom.sid
select 表1.字段，表2.字段 from 表1 连接(内连接/外连接) 表2 on 表1.字段=表2.字段

全连接 了解
select student.name,classroom.room from student right join classroom on student.id=classroom.sid UNION select student.name,classroom.room from student left join classroom on student.id=classroom.sid

三表联查
select sc.schools,st.name,cs.room from school
INNER JOIN student on sc.sid=st.id
INNER JOIN classroom on sc.cid = cs.id
select 表1.字段，表2.字段 from 表1 连接(内连接/外连接) 表2 on 表1.字段=表2.字段 连接(内连接/外连接) 表3 on 表1.字段=表3.字段

子查询
select name from student where id in(select id from classroom)

聚合函数
select max(score) from student 最大值
select min(score) from student 最小值
select avg(score) from student 平均值
select count(*) from student 计数
select sum(score) from student; 统计

子查询
select * from student where id in(select id from classroom)