新手这个月学的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)

------修改数据

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

------多表联查

select c.classroom,st.sname,st.sex ,sc.school from class as c INNER join student as st on c.sid = st.sid INNER JOIN school as sc on c.cid = sc.cid

-----子查询(一个表的查询的结果作为另一个表的条件)

SELECT cid from school where school =‘北京’

SELECT classroom from class where cid=(SELECT cid from school where school =‘北京’)