一.連接數(shù)據(jù)庫(kù)鸠蚪，在終端下輸入以下命令?
1.?mysql -u root -p?
2. 輸入密碼
二.創(chuàng)建數(shù)據(jù)庫(kù)(注意要有分號(hào))
create database shenhua;
三.刪除數(shù)據(jù)庫(kù)
drop database shenhua;
四.切換數(shù)據(jù)庫(kù)
use shenhua;
五.查看當(dāng)前數(shù)據(jù)庫(kù)
select database();
六.查看當(dāng)前數(shù)據(jù)庫(kù)所有的表
show tables;
七.創(chuàng)建表
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;
九.查看表結(jié)構(gòu)
desc ?student;
十.查看建表語(yǔ)句
show create table student;
十一.重命名表名
rename table student to newStudent;
十二.修改表結(jié)構(gòu)
alter table student add ?weight int ;
alter table student add|change|drop ?weight int ;
數(shù)據(jù)操作
一.增
1.全列插入（主鍵列是自動(dòng)增長(zhǎng)的柱衔，但是全列插入時(shí)需要占位夺刑，通常使用0，插入成功后已實(shí)際數(shù)據(jù)為主）
insert ?into student values(0,"shenhua",15,0,"123",0,66);
2.缺省插入
insert into student(name,age)values("shenhua1",44);
3.同時(shí)插入多條數(shù)據(jù)
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;
四.查看所有數(shù)據(jù)
select * from student;
查
一.基本語(yǔ)法
select * from student;
select name,age from student;
二.消除重復(fù)行
select ?distinct name from student;
三.條件查詢
1.語(yǔ)法
select * from student where id = 2;
2.比較運(yùn)算符
大于>
小于<
等于=
大于等于>=
小于等于<=
不等于!=
select * from student where id >=8;
3.邏輯運(yùn)算符
and
or?
not?
select * from student where id >7 and gender = 1;
4.模糊查詢
like
%表示任意多個(gè)任意字符
_表示一個(gè)任意字符
select * from student ?where name like "shen%";
select * from student ?where name like "shen_";
5.范圍查詢
in ? ? ? ? ? ? ? ? ? ? ? ? ? ? 表示在一個(gè)非連續(xù)的范圍內(nèi)
between...and... ? ???表示在一個(gè)連續(xù)的范圍內(nèi)
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.優(yōu)先級(jí)
小括號(hào)翎蹈，not淮菠，比較運(yùn)算符，邏輯運(yùn)算符
and比or的優(yōu)先級(jí)高荤堪，如果同時(shí)出現(xiàn)并先選擇or合陵，需要結(jié)合括號(hào)使用
四.聚合
1.為了快速得到統(tǒng)計(jì)數(shù)據(jù)，提供了5個(gè)聚合函數(shù)
a.count(*) 括號(hào)中可以寫*和列名 ?
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;
七.分頁(yè)
select * from student limit 0,10;//從0開(kāi)始取10條
select * from student where gender = 1 limit 0,3;
關(guān)聯(lián)表
1.創(chuàng)建關(guān)聯(lián)表
班級(jí)表：create table class(id int auto_increment primary key,name varchar(20) not null,stuNum int not null);
學(xué)生表: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.查詢關(guān)聯(lián)的數(shù)據(jù)
select students.name ,class.name from class inner join students on class.id = students.id;
