数据库
一.连接数据库，在终端下输入以下命令
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;
