1.查看数据库服务器已有的数据库
show databases;
2.查看数据库(school)定义的信息
show create database school;
3.连接数据库
use school;
4.查看当前连接的数据库
select database();
5.数据库创建
create database school;
6.数据库修改
alter database school character set utf8;
7.数据库删除
drop database school;
8.查看当前库已有表
show tables;
9.创建表
create table student(
idint,
name varchar(20),
ageint);
10.查看表定义信息
show create table student;
11.数据库的增、删、改(这里我是在图形化软件dataGrip里添加的数据)
/*增*/
insert into student(`num`,`name`,`age`,`class`,`math`) values (3,"赵子龙",20,3,100);
/*删*/
delete from student where name = "张三";
/*改*/
update student set age = 100 , num = 6 where name = "赵子龙";
12.数据库的查询(比较多)
/*查询表所有数据*/
select * from student;
/*查询表带条件数据*/
select * from student where age = 100;
/*查询表某个字段数据*/
select age,name from student;
/*查询表数据总数*/
select count(1) from student;
/*查询表数据带条件数目*/
select count(1) from student where age = 20 and class = 2; /*且*/
select count(1) from student where age = 20 or class = 2; /*或*/
/*查询表数据所有人数带条件(age)总和*/
select sum(age) from student;
/*查询表数据带条件(age)的平均数*/
select avg(age) from student;
/*查询表数据带条件(age)的平均数并且改名(as)为age_avg*/
select avg(age) as age_avg from student;
/*查询表数据按条件(class)进行分组求总数*/
select count(1) from student from group by class;
13.数据库分页查询
/*按10个一页查询第二页数据*/
select * from student limit 10,2;
/*按正序排*/
select * from student order by id;
/*按倒序排*/
select * from student order by id desc;
---------------------
转播
