数据库操作
创建/使用/查看/删除数据库
create database 数据库名;(以分号结束),SQL命令大小写无关 show databases;use 数据库名;
数据库表操作
创建/查看/修改/删除表
create table 表名 show create table 表名 desc 表名 drop table 表名 MySQL(MyISAM数据库引擎和InnoDB(支持表间查询,事务安全(保证多个线程情况下数据库是安全的,这样子就可能导致速度比较慢),支持多表间的关联查询)数据库表的CRUD
insert
update delete from 或truncate table或drop table 对于删除的操作一定要慎用 delete from:按行删除表中的所有记录,但会保留表,适合删除数据量不大的数据,可按条件删除 truncate table:复制原表结构-〉一次性删除整表 -> 自动恢复原表结构,适合删除数据量较大的数据,不能按条件删除 drop table:删除表本身 删除记录时,一定要留意表间的关联关系 修改、备份、恢复数据库数据 备份: c:\> mysqldump -u root -p mydb2 > d:\mydb2.sql回车(可以无分号结束) 密码回车 注意:该SQL命令是MySQL特有的,必须是MySQL环境外执行,即WindowXp环境中执行 恢复: mysql:\> source d:\mydb2.sql;回车(需要分号结束)注意:该SQL命令是MySQL特有的,必须是MySQL环境中执行。
案例说明
创建一个名称为mydb1的数据库
create database mydb1;创建一个使用utf8字符集的mydb2数据库create database if not exists mydb2 character set UTF8; //UTF-8在这里的指定方式是UTF8,和其他有点不一样//如果不存在mydb2,就创建,如果存在,则不创建mydb2数据库创建一个使用utf8字符集,并带校对规则的mydb3数据库这个命令是让mydb3数据库使用utf8_general_ci校验规则,也就是UTF-8规则可以通过show character set来获取校验规则表 是指定数据库间按照什么标准排序。。。create database if not exists mydb3 character set UTF8 collate utf8_general_ci;查看当前数据库服务器中的所有数据库show databases;查看前面创建的mydb2数据库的定义信息show create database mydb2;删除前面创建的mydb1数据库 //最好就是加上if exists来判定是否有存在数据库。。drop database if exists mydb1;查看服务器中的数据库,并把其中mydb3库的字符集修改为gbkalter database mydb3 character set gbk; 备份mydb2库中的数据,并恢复准备工作:create table if not exists user( name varchar(20) not null);insert into user(name) values('jack');insert into user(name) values('marry');使用MySQL特有的备份SQL命令 mysql > 创建一张表,含有id/name/password/birthday字段drop table if exists user; 修改表的使用方法create table user( id int(5), name varchar(20), password varchar(6), birthday timestamp, salary float(6,2));insert into user(id,name,password,salary) values(1,'jack','000000',1234.12);创建/查看一个员工表(并指明字符集为UTF8)drop table if exists employee;create table employee( id int, name varchar(20), gender varchar(6), 这个数据的选择指的是长度,女就是flamer 所以是6 birthday date, entry_date date, job varchar(30), salary float(5,1), resume text);insert into employee(id,name,gender,birthday,entry_date,job,salary,resume)values(1,'jack','male','2011-10-8','2011-12-31','software',5000.1,'hello');在上面员工表的基本上增加一个image列。alter table employeeadd image blob;修改job列,使其长度为60。alter table employeemodify job varchar(60) default 'teacher';删除gender列。alter table employeedrop gender;表名改为user。rename table employee to user;修改表的字符集为gbk。alter table usercharacter set UTF8;列名name修改为username。alter table userchange column name username varchar(20); 向user表插入一条中文记录 如果插入中文会发现,varchar(20)竟然连两个中文汉字都存不进去 这就是客户端(gbk),数据库以及传输过程中编码不统一的问题,所以要把编码统一才能使用中文 ,在eclipse中编码就不用这么麻烦咯insert into user(username,id,birthday,entry_date,job,salary,resume)values('杰克',2,'2011-10-8','2011-12-31','software',5000.1,'你好');insert into user values(3,'马利','2011-10-8','2011-12-31','software',5000.1,'你好',NULL);insert into user values(4,'马利','2011-10-8','2011-12-31','software',5000.1,NULL,NULL);insert into user(id,username,birthday,entry_date,job,salary,image) values(5,'马利','2011-10-8','2011-12-31','software',5000.1,NULL);//修改客户端输入和输出使用的编码方式,与WindowXP平台(使用的是gbk)一致set character_set_client=gbk; //这种设置是只在当前窗口有效的,要一直有效就要去改配置文件set character_set_results=gbk;将所有员工薪水修改为6000元。update user set salary = 6000;将姓名为’马利’的员工薪水修改为7000元。update user set salary = 7000 where username = '马利';将’jack’的薪水在原有基础上增加1000元。 update user set salary = salary + 1000 where username = 'jack';删除表中名称为’jack’的记录。delete from user where username = 'jack';删除表中所有记录。delete from user;使用truncate删除表中记录。truncate table user;查询表中所有学生的信息。select * from student;select id,name,math,chinese,english from student;select name,id,math,chinese,english from student;select name,math from student;查询表中所有学生的姓名和对应的英语成绩。select name,english from student;过滤表中重复数据。select distinct english from student;select distinct name,english from student;在所有学生分数上加10分特长分。select name,math+10 from student;select name as 姓名,math+10 as 数学 from student;统计每个学生的总分。select name,math+chinese+englishfrom student;使用别名表示学生分数。select name,math+chinese+english as 总分from student;查询姓名为’张小明’的学生成绩select *from student where name = '张小明';查询英语成绩大于90分的同学select *from student where english > 90;查询总分大于200分的所有同学select name,chinese+math+english as 总分from studentwhere chinese+math+english > 200;查询英语分数在 80-90之间的同学。select *from studentwhere english>=80 and english<=90;或 select *from studentwhere english between 80 and 90;查询数学分数为89,90,91的同学。select *from studentwhere math=89 or math= 90 or math=91;或select *from studentwhere math [not] in(89,90,91);查询所有姓’李’的学生成绩。select *from studentwhere name LIKE '李%';select *from studentwhere name LIKE '%李';select *from studentwhere name LIKE '%李%';---------------------------------------在网站中,多条件查询中用到select *from studentwhere name LIKE '%%';select *from studentwhere name LIKE '__李';select * from studentwhere math IS [NOT] NULL;查询数学分>80且语文分>80的同学。select * from studentwhere math >80 and chinese>80;对数学成绩排序后输出。升序:select * from studentorder by math asc; 降序:select * from studentorder by math desc; 对总分降序后输出。select name,math+chinese+english as 总分from studentorder by math+chinese+english desc;对姓’李’的学生总分降序输出。select name,math+chinese+english as 总分from studentwhere name LIKE '李%'order by math+chinese+english desc;统计一个班级共有多少学生?select count(*) as 总人数from student;统计数学成绩大于80的学生有多少个?select count(*) as 总人数from studentwhere math > 80;统计总分大于250的人数有多少?select count(*) as 总人数from studentwhere (math+chinese+english) > 250;select count(english) as 总人数from student;//13select count(math) as 总人数from student;统计一个班级数学总成绩。select sum(math)from student;select sum(name)from student;//0统计一个班级语文、英语、数学各科的总成绩。select sum(math) as 数学总分,sum(chinese) as 语文总分,sum(english) as 英语总分from student;统计一个班级语文、英语、数学的成绩总和。select sum(math)+sum(chinese)+sum(english) as 班级总分from student;统计一个班级语文成绩平均分。select sum(math)/count(math)from student;select sum(math)/count(*)from student;求一个班级数学平均分。 select sum(math) / count(math) as 数学平均分 from student; select avg(math) as 数学平均分 from student; select avg(name) as XX平均分 from student;//0 求一个班级总分平均分。 select (sum(chinese)+sum(math)+sum(english)) / count(*) from student; select avg(chinese+math+english) from student; 求班级语文最高分和最低分。 select max(name),min(name) from student; drop table if exists teacher; create table teacher( id int, name varchar(20), birthday date ); insert into teacher(id,name,birthday) values(1,'jack','2011-1-1'); 这个是min insert into teacher(id,name,birthday) values(2,'marry','2011-2-2'); insert into teacher(id,name,birthday) values(3,'sisi','2011-3-3'); 这个是max select max(birthday),min(birthday) from teacher; 对订单表中商品归类后,显示每一类商品的总价 select product as 类别名,sum(price) as 商品类别总价 from orders group by product; 查询购买了几类商品,并且每类总价大于100的商品 select product as 类别名,sum(price) as 商品类别总价 from orders group by product having sum(price) > 100; where v.s. having区别: where主要用于行过滤器 having主要用于类别过滤器,通常有having就一定出现group by,但有group by的地方,不一定出现having。 drop table if exists teacher; create table teacher( id int primary key auto_increment, name varchar(20) not null unique, //指定老师的名字一定不能是重复的 birthday date ); insert into teacher(name,birthday) values(NULL,'2011-1-1'); insert into teacher(name,birthday) values('marry','2011-2-2'); insert into teacher(id,name,birthday) values(3,'sisi','2011-3-3'); select max(birthday),min(birthday) from teacher; 删除主键,主键在表中只有一个,要么是一列,要么是多列 alter table teacher drop primary key; 一对一关系(方案一): drop table if exists card; drop table if exists person; create table person( id int primary key auto_increment, name varchar(20) not null ); insert into person(name) values('jack'); insert into person(name) values('marry'); create table card( id int primary key auto_increment, location varchar(20) not null, pid int, constraint pid_FK foreign key(pid) references person(id) //定义外键 ); insert into card(location,pid) values('BJ',1); insert into card(location,pid) values('GZ',2); insert into card(location,pid) values('CS',NULL); //外键要么参照,要么为空 insert into card(location,pid) values('NJ',3);//出错 //删除person表的某记录 在删除的时候要注意是不是其他表的外键引用,是的话会出错。所以当被作为外键的时候,delect的时候要注意顺序,insert的时候也要注意外键 delete from person where name = 'jack'; 一对一关系(方案二): 当限制外键不能为null的时候,外键ID就等于主键ID drop table if exists card; drop table if exists person; create table person( id int primary key auto_increment, name varchar(20) not null ); insert into person(name) values('jack'); insert into person(name) values('marry'); create table card( id int primary key auto_increment, location varchar(20) not null, constraint id_FK foreign key(id) references person(id) ); insert into card(location) values('BJ'); insert into card(location) values('GZ'); insert into card(location) values('CS');//出错 insert into card(location) values(NULL); 一对多/多对一关系: 一对一关系的时候外键要看业务确定在那方,而多对多关系就是确定外键在多方。。 drop table if exists employee; drop table if exists department; create table department( //被当做外键的先create id int primary key auto_increment, name varchar(20) not null ); insert into department(name) values('软件部'); insert into department(name) values('销售部'); create table employee( id int primary key auto_increment, name varchar(20) not null, did int, constraint did_FK foreign key(did) references department(id) ); insert into employee(name,did) values('jack',1); insert into employee(name,did) values('marry',1); 问题?查询"软件部"的所有员工(组合式) select d.name as 部门名,e.name as 员工名 from department as d,employee as e where d.name = '软件部'; //强烈注意 select d.name as 部门名,e.name as 员工名 from department as d,employee as e 连接其实就是对两个表的所有行两两相互连接,一般连接好了再根据一定的条件进行筛选,单纯的连接是没有筛选的,就只是两两连接 思考:还有没有其它方法? 分解: (1)select id from department where name='软件部'; (2)select name from employee where did = 1; (总)嵌入式SQL select name as 员工 from employee where did = ( select id from department where name='软件部' ); 多对多关系: drop table if exists middle; drop table if exists student; drop table if exists teacher; create table if not exists student( id int primary key auto_increment, name varchar(20) not null ); insert into student(name) values('jack'); insert into student(name) values('marry'); create table if not exists teacher( id int primary key auto_increment, name varchar(20) not null ); insert into teacher(name) values('赵'); insert into teacher(name) values('蔡'); create table if not exists middle( sid int, tid int, constraint sid_FK foreign key(sid) references student(id), constraint tid_FK foreign key(tid) references teacher(id), primary key(sid,tid) ); insert into middle(sid,tid) values(1,1); insert into middle(sid,tid) values(1,2); insert into middle(sid,tid) values(2,1); insert into middle(sid,tid) values(2,2); 问题?查询"赵"所教过的所有学员 select t.name as 老师, s.name as 学员 from teacher as t,student as s,middle as m where t.name = '赵'and m.sid=s.id and m.tid=t.id; 模式: select 列出需要显示的字段 from 列出所涉及到的所有表,建议写别名 where 业务条件 and 表关联条件 使用MySQL特有函数: 到年底还有几少天? select datediff('2011-12-31',now()); 截取字符串 select substring('mysql',1,2); //从1开始 保留小数点后3位(四舍五入) select format(3.1415926535657989,3); 向下取整(截取) select floor(3.14); 3 select floor(-3.14); -4 select floor(3.54); 3 select floor(-3.54); -4 取随机值 select format(rand(),2); 取1-6之间的随机整数值 select floor(rand()*6) + 1; MySQL扩展知识: 查MySQL文档,利用MySQL的函数:随机产生'a'-'z'之间的随机字符。 随机产生'a'-'z'之间的随机字符 (1)查询'a'-'z'对应的Unicode值 select ascii('a');//97 select ascii('z');//122 (2)产生97-122之间的随机整数 select floor(rand()*26)+97; (3)产生97-122对应的字符 select char(floor(rand()*26)+97); 查MySQL文档,利用MySQL的函数:对密码'123456'进行MD5加密。 select md5('123456'); drop table user; create table user( id int primary key auto_increment, name varchar(20), gender varchar(6), salary float ); insert into user(name,gender,salary) values('jack','male',4000); insert into user(name,gender,salary) values('marry','female',5000); insert into user(name,gender,salary) values('jim','male',6000); insert into user(name,gender,salary) values('tom','male',7000); insert into user(name,gender,salary) values('soso','female',NULL); insert into user(name,gender,salary) values('haha','female',3500); insert into user(name,gender,salary) values('hehe','female',4500); select * from user; MySQL特有流程控制函数: 1) if(value,第一值,第二值); value为真,取第一值,否则取第二值 将5000元(含)以上的员工标识为"高薪",否则标识为"起薪" 类似于Java中的三目运算符 select if(salary>=5000,'高薪','起薪') from user; 2) ifnull(value1,value2) value1为NULL,用value2替代 将薪水为NULL的员工标识为"无薪" select name as 员工,ifnull(salary,'无薪') as 薪水情况 from user; 3) case when [value] then [result1] else [result2] end; 当value表达式的值为true时,取result1的值,否则取result2的值(if...else...) 将5000元(含)以上的员工标识为"高薪",否则标识为"起薪" select case when salary>=5000 then '高薪' else '起薪' end from user; 4) case [express] when [value1] then [result1] when [value2] then [result2] else [result3] end; 当express满足value1时,取result1的值,满足value2时,取result2的值,否则取result3的值(switch...case..) 将7000元的员工标识为"高薪",6000元的员工标识为"中薪",5000元则标识为"起薪",否则标识为"低薪" select case salary when 7000 then '高薪' when 6000 then '中薪' when 5000 then '起薪' else '低薪' end from user; 课堂练习: 1)查询相同性别的员工总人数>2的工资总和,并按工资总和降序排列 select count(*) as 员人数,gender as 性别,sum(salary) as 工资和 from user group by gender having count(*)>2 order by sum(salary) desc; 2)将性别为男的员工工资-1000,性别为女的员工工资+1000,在一条SQL上完成 select if(gender='female',salary+1000,salary-1000) as 工资 from user;