create database [if not exists] 数据库名 use 数据库名 show create database 数据库名 drop database if exists 数据库名 alter database 数据库名 c:/>mysqldump -u root -p 数据库名 > d:\xx.sql回车 密码
mysql>source d:\xx.sql;回车 日期时间：date(YYYY-MM-DD)/datetime(YYYY-MM-DD HH:MM:SS)/timestamp(系统自动设置值，YYYY-MM-DD HH:MM:SS) 文本：varchar/char blob(存图片，视频，音乐，....) JDBC text(存储超过65535长度的数据) JDBC 数值类型 >>整型(INT) >>浮型(FLOAT/DOUBLE) 创建/查看/修改/删除表 create table if not exists 表名 desc 表名 alter table 表名 drop table if exists 表名 insert into 表名(字段) values(字段对应的值) update 表名 set 字段=新值,字段=新值 where 条件
delete from 表名 创建/查看/修改/删除表 create table if not exists 表名 desc 表名 alter table 表名 drop table if exists 表名 MySQL解决插入中文乱码问题(WindowXP/7平台) 表的 增删改 操作 insert into 表名(字段) values(字段对应的值) update 表名 set 字段=新值,字段=新值 where 条件select * from student;*表示所有字段 select name,chinese from student; select chinese,name from student; select distinct chinese from student;排序 order by 排序字段 asc/desc>>有having就有group by，但有group by不一定会有having >>having是针对分组后过滤，而where是针对原始记录做过滤 >>count(math)统计行数，NULL值排除在外 >>sum(math)求和函数，NULL值排除在外 >>avg() >>max() >>min()
drop table if exists staff;drop table if exists department;create table if not exists department(id int primary key auto_increment,name varchar(20) not null unique);insert into department(name) values('财务部');insert into department(naem) values('软件部');
create table if not exists staff( id int primary key auto_increment,name varchar(20) not null unique,did int,constraint did_FK foreign key(did) referencesdepartment(id));insert into staff(name,did) values('jack',1);insert into staff(name,did) values('marry',2);
select d.name as 部门, s.name as 员工from fepartment as d,staff as swhere s.name = 'jack' and s.did = d.id
select d.name as 部门, s.name as 员工from department as d,staff as s where d.name = ' 财务部' and s.did = d.id;
drop table if exists middle;drop table if exists teacher;drop table if exists strdent;create table if not exists teacher(id int primary key auto_increment,name varchar(20) not null);insert into teacher(name) values('cai');insert into teacher(name) values('zhao'); create table if not exists student(id int primary key auto_increment,name varchar(20) not null);insert into student(name) values('haha');insert into student(name) values('hehe');create table if not exists middle(tid int,sid int,constraint tid_FK foreign key(tid) referencesteacher(id),constraint sid_FK foreign key(sid) references student(id),primary key(tid,sid));insert into middle(tid,sid) values(1,1);insert into middle(tid ,sid) values(1,2);insert into middle(tid , sid) values(2,1);insert into middle(tid,sid) values(2,1);select s.name as 学员,t.name as 老师from teacher as t,student as s , middle as mwhere s.name = 'haha' and m.tid = t.id and m.sid = s.id;
mysql>source d:\xx.sql;回车 日期时间：date(YYYY-MM-DD)/datetime(YYYY-MM-DD HH:MM:SS)/timestamp(系统自动设置值，YYYY-MM-DD HH:MM:SS) 文本：varchar/char blob(存图片，视频，音乐，....) JDBC text(存储超过65535长度的数据) JDBC 数值类型 >>整型(INT) >>浮型(FLOAT/DOUBLE) 创建/查看/修改/删除表 create table if not exists 表名 desc 表名 alter table 表名 drop table if exists 表名 insert into 表名(字段) values(字段对应的值) update 表名 set 字段=新值,字段=新值 where 条件
delete from 表名 创建/查看/修改/删除表 create table if not exists 表名 desc 表名 alter table 表名 drop table if exists 表名 MySQL解决插入中文乱码问题(WindowXP/7平台) 表的 增删改 操作 insert into 表名(字段) values(字段对应的值) update 表名 set 字段=新值,字段=新值 where 条件select * from student;*表示所有字段 select name,chinese from student; select chinese,name from student; select distinct chinese from student;排序 order by 排序字段 asc/desc>>有having就有group by，但有group by不一定会有having >>having是针对分组后过滤，而where是针对原始记录做过滤 >>count(math)统计行数，NULL值排除在外 >>sum(math)求和函数，NULL值排除在外 >>avg() >>max() >>min()
drop table if exists staff;drop table if exists department;create table if not exists department(id int primary key auto_increment,name varchar(20) not null unique);insert into department(name) values('财务部');insert into department(naem) values('软件部');
create table if not exists staff( id int primary key auto_increment,name varchar(20) not null unique,did int,constraint did_FK foreign key(did) referencesdepartment(id));insert into staff(name,did) values('jack',1);insert into staff(name,did) values('marry',2);
select d.name as 部门, s.name as 员工from fepartment as d,staff as swhere s.name = 'jack' and s.did = d.id
select d.name as 部门, s.name as 员工from department as d,staff as s where d.name = ' 财务部' and s.did = d.id;
drop table if exists middle;drop table if exists teacher;drop table if exists strdent;create table if not exists teacher(id int primary key auto_increment,name varchar(20) not null);insert into teacher(name) values('cai');insert into teacher(name) values('zhao'); create table if not exists student(id int primary key auto_increment,name varchar(20) not null);insert into student(name) values('haha');insert into student(name) values('hehe');create table if not exists middle(tid int,sid int,constraint tid_FK foreign key(tid) referencesteacher(id),constraint sid_FK foreign key(sid) references student(id),primary key(tid,sid));insert into middle(tid,sid) values(1,1);insert into middle(tid ,sid) values(1,2);insert into middle(tid , sid) values(2,1);insert into middle(tid,sid) values(2,1);select s.name as 学员,t.name as 老师from teacher as t,student as s , middle as mwhere s.name = 'haha' and m.tid = t.id and m.sid = s.id;
