一.连接数据库，在终端下输入以下命令 

1. mysql -u root -p 

2. 输入密码

二.创建数据库(注意要有分号)

create database shenhua;

三.删除数据库

drop database shenhua;

四.切换数据库

use shenhua;

五.查看当前数据库

select database();

六.查看当前数据库所有的表

show tables;

七.创建表

create table student(id int auto_increment primary key,name varchar(20) not null,age int not null,gender bit not null default 1,address varchar(20) ,isdelete bit not null default 0);

八.删除表

drop table student;

九.查看表结构

desc  student;

十.查看建表语句

show create table student;

十一.重命名表名

rename table student to newStudent;

十二.修改表结构

alter table student add  weight int ;

alter table student add|change|drop  weight int ;

数据操作

一.增

1.全列插入（主键列是自动增长的，但是全列插入时需要占位，通常使用0，插入成功后已实际数据为主）

insert  into student values(0,"shenhua",15,0,"123",0,66);

2.缺省插入

insert into student(name,age)values("shenhua1",44);

3.同时插入多条数据

insert into student values(0,"shenhua2",13,0,"北京",0,44),(0,"shenhua3",13,0,"廊坊",0,44);

二.删

delete from student where id = 2;

delete from student;全删除慎用

三.改

update student set age = 10,weight = 66 where id = 7;

四.查看所有数据

select * from student;

查

一.基本语法

select * from student;

select name,age from student;

二.消除重复行

select  distinct name from student;

三.条件查询

1.语法

select * from student where id = 2;

2.比较运算符

大于>

小于<

等于=

大于等于>=

小于等于<=

不等于!=

select * from student where id >=8;

3.逻辑运算符

and

or 

not 

select * from student where id >7 and gender = 1;

4.模糊查询

like

%表示任意多个任意字符

_表示一个任意字符

select * from student  where name like "shen%";

select * from student  where name like "shen_";

5.范围查询

in                             表示在一个非连续的范围内

between...and...      表示在一个连续的范围内

select * from student where id in (8,10,15);

select * from student where id between 6 and 8;

6.空查询

is null ,is not null

select * from student where address is null ;

select * from student where address is not null ;

7.优先级

小括号，not，比较运算符，逻辑运算符

and比or的优先级高，如果同时出现并先选择or，需要结合括号使用

四.聚合

1.为了快速得到统计数据，提供了5个聚合函数

a.count(*) 括号中可以写*和列名  

select count(*) from student;

select count(id) from student;

b.max(列)表示求此列的最大值

select max(id) from student where gender = 0;

c.min(列)表示求此列的最小值

select min(id) from student where gender = 0;

d.sum(列)表示求此列的和

select sum(age) from student where gender = 0;

e.avg(列)表示求此列的平均值

select avg(weight) from student where gender = 0;

五.分组

select gender,count(*) from student group by gender;

select gender,weight from student group by gender,weight having weight;

六.排序

asc 升序   desc 降序 

select * from student where isdelete = 0 order by  age desc, id;

select * from student where isdelete = 0 order by  age desc, id desc;

七.分页

select * from student limit 0,10;//从0开始取10条

select * from student where gender = 1 limit 0,3;

关联表

1.创建关联表

班级表：create table class(id int auto_increment primary key,name varchar(20) not null,stuNum int not null);

学生表:create table students(id int auto_increment primary key,name varchar(20) not null,gender bit default 1,classid int not null,foreign key(classid) references class(id));

2.查询关联的数据

select students.name ,class.name from class inner join students on class.id = students.id;