Mysql数据库操作

---

数据库基本操作

mysql
	是存储数据库一个仓库，可以存储图片，视频，文字
常见的数据库
	大型oracle,db2
	中型mysql,sqlserver...
	小型sqlite
mysql安装和卸载
	停服务mysql
	删除对应的文件夹
	删除注册表(mysql)
mysql使用
	1：黑窗口
	2：使用工具 navicat
sql语句分类
	数据定义语言DDL         create，drop，alter，truncate
	数据查询语言DQL         select
	数据操纵语言DML         insert，delete，update
	数据控制功能DCL          grant 授权 ,revoke回收
字段
	主键：primary key  
	自增：auto_increment
	空：null
	默认：default
	唯一：unique
	外键：foreign
	......
类型
	int   
	float
	double
	char      固定长度 	
	varchar   长度变              
	text
	date
	time
	datetime
	........

-----创建数据库
create DATABASE 20201910b
---使用数据库
use 20201910b
-----创建表
create table student(
id int PRIMARY key auto_increment,
sname VARCHAR(20),
ssex VARCHAR(2),
sage int,
sscore FLOAT(4,2)
)
------修改编码
alter database 20201910b CHARSET=utf8
--------新增数据
----单条添加
INSERT into student(id,sname,ssex,sage,sscore) VALUES(1,"张三","男",24,80.5)
-----多条添加
INSERT into student VALUES(2,"张三1","男",25,90.5),(3,"张三2","女",22,80),(4,"张三3","女",19,88)
------数据库添加字段
alter table 表名 add 字段名 约束(varchar(20));
------修改字段名
alter table 表名 change column 旧字段名 新字段名 新数据类型;
------修改表名
alter table 旧表名 rename to 新表名
------删除字段
alter table 表名 drop column 字段名
------删除表
drop table 表名
------删除库
drop database 库名
------修改数据
update 表名 set 字段=修改的数据(name =zhangsan)  where 字段=值（id=1）
UPDATE student set sname='张十' where id =2 
------查询（全查，条件查询，模糊查询(_)，排序，分组）
select * from +表名
select * from student
-------条件查询
select * from student where id=1 or id =2
select * from student where id in(1,2,3,4)
select * from student where id BETWEEN 1 and 4
------模糊查询
SELECT * from student where sname like '三%'
SELECT * from student where sname like '%三'
SELECT * from student where sname like '%三%'
------升序
select * from student ORDER BY sscore 
----降序
select * from student ORDER BY sscore  desc
------分组查询   HAVING   +  GROUP BY 
select count(*) from student GROUP BY ssex HAVING sum(sscore)>=85

-----删除------
------物理删除+逻辑删除-------
DELETE from +表名 +where 字段=值
delete from student where id=2
------聚合函数  sum ,avg,max ,min count(),
select count(sscore) from student 
select avg(sscore) from student 
select max(sscore) from student 
select min(sscore) from student 
select sum(sscore) from student 
	

多表联查
	内连接   inner join
	外连接   left join   ,right join
	全连接   full join (不适用mysql,使用oracle)      = left join+ union+right join

取别名  as 或者是不写    
--------sql语句
select s.字段,c.字段，，， from student(表1) as s  inner join（left join   ,right join） class(表2) c on s.字段 = c.字段
------内连接
select s.sname,s.sex,c.classroom from class c INNER JOIN student s on s.sid = c.sid
SELECT sc.school,c.classroom from school as sc INNER JOIN class as c on sc.cid = c.cid
---------外连接
select s.sname,s.sex,c.classroom from class c left JOIN student s on s.sid = c.sid    (左边为主表，右边有则填充，没有用null) 
select s.sname,s.sex,c.classroom from class c RIGHT JOIN student s on s.sid = c.sid   (右边为主表，左边有则填充，没有用null)
----------全连接（不适用mysql）
select s.sname,s.sex,c.classroom from class c full JOIN student s on s.sid = c.sid 
-------全连接
select s.sname,s.sex,c.classroom from class c left JOIN student s on s.sid = c.sid UNION select s.sname,s.sex,c.classroom from class c RIGHT JOIN student s on s.sid = c.sid


----