首页 > 代码库 > 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 基础总结
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。