珍藏版（cmd小黑框）数据库命令及操作

快来打我* 2021-10-03 02:50 285阅读 0赞

话不多说直接来，后续补充。

查看数据库

show databases;

创建数据库

create database 2018;

使用数据库

use 2018;

查看数据库中的所有表

show tables;

删除数据库

drop database 库名;

create table student(

id int(10) auto\_increment primary key,

name varchar(20) not null,

age int(5) default 16,

address char(50);

)

查看表设计

desc student;

添加数据 不按照字段体添加 所有字段需要定义

insert into student values(1,'张三'，20，‘北京’);

不常用

按照字段添加

insert into student(name,age,address) values('李四'，22，'北京');

insert into student(name) values('王五')；

insert into student(name，age,address) values('赵六',29,'上海')，

('赵晓晓',24,'深圳'),('张红',21,'北京'),('赵晓刘',23,'深圳');

删除

delete from student where id =3;

delete from student where age=24;

delete from student where age=26 and ='赵六';

将表中的数据删除 表仍然在

delete from student;

删除表

drop table student;

修改

update student set age =26,address='上海' where name='王五';

update student set age =26,address='上海';

查询\* 表中的所有内容

select \* from student;

查询年龄在20-26之间的学生和年龄 between。。。and

select name,age from student where age between 20 and 26;

模糊查询 like %代表多个字符

select name from student where name like '李%'；

\# \_代表的为一个字符

select name from student where name like '李\_';

select name from student where name like '%小%';

select name,age,address from student where name like '%六';

\#查询ID为偶数的学生信息

select \* from student where id%2=0;

\#查询年龄不等于23的学生姓名和年龄 两种写法 != ,<>

select name,age from student where age !=23;

select name,age from student where age <>23;

\#查询id 为3,6，7,10 in包含

select \* from student where id in(10,6,7,3);

\#查询年龄26 并且上海 and

select \* from student where age = 26 and address='上海';

\#查询年龄26 或者来自上海

select \* from student where age = 26 or address='上海';

\#分页 显示第一页代表的是索引 每页显示3条 每次显示的条数

select \* from student limit 3,3;

select \* from student limit 4,2;

\#查询和李四年龄相同的学生姓名

select name from student where age = (select age from student where name='李四') and name !='李四';

\#将年龄从小到大排序

select \* from student order by age;

\#大到小排序 order by.. desc order by asc(小到大)

select \* from student order by age desc;

\#聚合函数

count(),sum(),avg.max().min()

\#统计表中总数量

select count(id) from student;

select count(\*) from student;

select count(1) from student;

\#年龄的总和

select sum(age) from student;

\#平均年龄

select avg(age) from student;

\#获取最大值

select max(age) from student;

\#获取最小值

select min(age) from student;

\#统计每个年龄分布有多少人 group by 分组

select count(id),age from student group by age;

\#group by 需要与聚合函数联合使用 having 必须与group by一起使用 为分组进行条件判断

select count(id),age from student group by age having age>20;

将地址分组查询

select count(id),address from student group by address;

统计同一地方人数大于3

select count(id),address from student group by address having count(id) >3;

select count(id) he,address from student group by address having he >3;

select count(id) as he,address from student group by address having he>3;

select count(id) as '总数',address from student group by address having 总数>3;

珍藏版（cmd小黑框）数据库命令及操作

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 cmd常用的操作命令

相关 python cmd mysql数据库_python操作MySQL数据库

相关 CMD命令详解——文件操作类

相关 linux常用命令—alaric珍藏版

相关 Java 调用执行CMD不弹出黑框

相关快速启动某个文件夹的cmd小黑框窗口

相关 cmd黑框框清空

相关 mysql中命令框cmd的操作

相关珍藏版（cmd小黑框）数据库命令及操作

相关 cmd文件操作命令与linux文件操作命令对应表