首页 > 代码库 > mysql 基础总结

mysql 基础总结

一,基于CMD操作

1.数据库文件夹:

      create   database  db1;                  #创建数据库db1

   drop      database  db1;         #删除数据库db1

     show    databases db1;             #查看文件db1

     use   db1;                                 #切换到t1文件 

2.数据表:

 建表:
create table t1用户表(id int auto_increment primary key,num int)
 engine=innodb default charset=utf8;

 show  tables;                   #查看建的表

 清空表:
delete from t1; #清除内容但id从清除的数字开始
truncate table t1 #清除内容但id从零开始

 select count(1) from t1;        查看表里多少数据

select * from t1; 查看t1表里的内容

desc t1; 查看t1是否有自增

show create table t1 \G; 查看t1表如何创建的

 

3. 增 删 改 查

   insert into tb11(name,age) values(‘alex‘,12);

insert into tb11(name,age) values(‘alex‘,12),(‘root‘,18);

insert into tb12(name,age) select name,age from tb11;

delete from tb12;
delete from tb12 where id !=2
delete from tb12 where id =2
delete from tb12 where id > 2
delete from tb12 where id >=2
delete from tb12 where id >=2 or name=‘alex‘


update tb12 set name=‘alex‘ where id>12 and name=‘xx‘
update tb12 set name=‘alex‘,age=19 where id>12 and name=‘xx‘


select * from tb12;

select id,name from tb12;

select id,name from tb12 where id > 10 or name =‘xxx‘;

select id,name as cname from tb12 where id > 10 or name =‘xxx‘;

select name,age,11 from tb12;

其他:
      alter table t1 auto_increment=2;   从序号二开始插入内容
      select * from tb12 where id != 1
select * from tb12 where id in (1,5,12); 查看1,5,12
select * from tb12 where id not in (1,5,12); 查看除1,5,12以外的
select * from tb12 where id in (select id from tb11) 查看表里具体一列,*只能一列
select * from tb12 where id between 5 and 12; 区(闭)间固定,取5到12之间。

通配符:

select * from tb12 where name like "a%" 举例 assss afffffffff
select * from tb12 where name like "a_" 举例 as ad ac


分页:

select * from tb12 limit 10; 查看前十条

select * from tb12 limit 0,10; 从0开始后面取10条
select * from tb12 limit 10,10;
select * from tb12 limit 20,10;

select * from tb12 limit 10 offset 20;


# page = input(‘请输入要查看的页码‘)
# page = int(page)
# (page-1) * 10
# select * from tb12 limit 0,10; 1
# select * from tb12 limit 10,10;2


排序:
select * from tb12 order by id desc; 大到小
select * from tb12 order by id asc; 小到大
select * from tb12 order by age desc,id desc;

取后10条数据
select * from tb12 order by id desc limit 10;

分组:

select count(id),max(id),part_id from userinfo5 group by part_id;

count
max
min
sum
avg

**** 如果对于聚合函数结果进行二次筛选时?必须使用having ****
select count(id),part_id from userinfo5 group by part_id having count(id) > 1;

select count(id),part_id from userinfo5 where id > 0 group by part_id having count(id) > 1;


连表操作:

select * from userinfo5,department5

select * from userinfo5,department5 where userinfo5.part_id = department5.id


select * from userinfo5 left join department5 on userinfo5.part_id = department5.id
select * from department5 left join userinfo5 on userinfo5.part_id = department5.id
# userinfo5左边全部显示


# select * from userinfo5 right join department5 on userinfo5.part_id = department5.id
# department5右边全部显示



select * from userinfo5 innder join department5 on userinfo5.part_id = department5.id
将出现null时一行隐藏






select * from
department5
left join userinfo5 on userinfo5.part_id = department5.id
left join userinfo6 on userinfo5.part_id = department5.id


select
score.sid,
student.sid
from
score

left join student on score.student_id = student.sid

left join course on score.course_id = course.cid

left join class on student.class_id = class.cid

left join teacher on course.teacher_id=teacher.tid




select count(id) from userinfo5;





















 

mysql 基础总结