小黑框如何连接mysql_珍藏版（cmd小黑框）数据库命令及操作

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

查看数据库

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;