本文详细介绍了MySQL数据库的基本操作，包括数据库的创建、查看、修改、删除等管理操作；表的创建、查看、修改、删除等操作；以及数据记录的增删改查等基本SQL语言操作。此外还介绍了备份恢复数据库的方法及多表查询技巧。
一、MySql的安装配置
二、MySql基本概念
客户端
mysql服务器
数据库 -- 一个工程一个数据库
表 -- 一个javabean类的对应一个表
数据记录 -- javabean类的一个对象可以对应到表中的一条记录
三、SQL语言 操作关系型数据库
一套标准的关系型数据库的操作语言
所有的关系型数据库都实现了这套标准sql语言，所以只要学习sql，所有的关系型数据库都可以操作
非过程性的语言
SQL方言
<1>.操作数据库
1.创建数据库
CREATE DATABASE [IF NOT EXISTS] db_name [create_specification [, create_specification] ...]
create_specification:
[DEFAULT] CHARACTER SET charset_name | [DEFAULT] COLLATE collation_name
~创建一个名称为mydb1的数据库。
create database mydb1;
~创建一个使用gbk字符集的mydb2数据库。
create database mydb2 character set gbk;
~创建一个使用utf8字符集，并带校对规则的mydb3数据库。
create database mydb3 character set utf8 collate utf8_bin;
2.查看数据库
显示数据库语句：
SHOW DATABASES
显示数据库创建语句：
SHOW CREATE DATABASE db_name
~查看当前数据库服务器中的所有数据库
show databases;
~查看前面创建的mydb2数据库的定义信息
show create database mydb2;
3.删除数据库
DROP DATABASE [IF EXISTS] db_name
~删除前面创建的mydb1数据库 drop database mydb1;
drop database mydb1;
4.修改数据库
ALTER DATABASE [IF NOT EXISTS] db_name [alter_specification [, alter_specification] ...]
alter_specification:
[DEFAULT] CHARACTER SET charset_name | [DEFAULT] COLLATE collation_name
~查看服务器中的数据库，并把其中某一个库的字符集修改为utf8;
alter database mydb2 character set utf8;
5.选择数据库
切换数据库 use db_name;
查看当前使用的数据库 select database();
<2>.操作表
1.创建表
CREATE TABLE table_name
(
field1 datatype,
field2 datatype,
field3 datatype,
.....
)character set 字符集 collate 校对规则;
~创建一个员工表employee
create table employee(
id int primary key auto_increment,
name varchar(20) unique,
gender bit not null,
birthday date,
entry_date date,
job varchar(40),
salary double,
resume text
);
约束:
主键约束:将表中的某一个字段声明为一个主键,要求它必须存在且必须唯一,数据库会帮我们保证这一点
primary key:不允许为空，不允许重复
auto_increment :声明主键要自动增长,只要将主键声明为自动增长,从此不用我们再去关心主键列的值了,他会自动取值,从0开是自动增上
唯一约束:声明表中的某一列的唯一约束,说明这一例的值不允许重复,数据库会帮我们保证这一点
unique
非空约束:声明表中的某一列为非空约束,说明这一列的值不允许为空,数据库会帮我们保证这一点
not null
外键约束:
2.查看表
查看表结构：
desc tabName
查看当前所有表：
show tables
查看当前数据库表建表语句
show create table tabName;
3.修改表
ALTER TABLE table ADD/MODIFY/DROP/character set/change (column datatype [DEFAULT expr] [, column datatype]...);
修改表的名称：rename table 表名 to 新表名;
~在上面员工表的基本上增加一个image列。
alter table employee add image blob;
~修改job列，使其长度为60。
alter table employee modify job varchar(60);
~删除gender列。
alter table employee drop gender;
~表名改为user。
rename table employee to user;
~ 修改表的字符集为utf8
alter table user character set utf8;
~列名name修改为username
alter table user change name username varchar(20);
4.删除表
drop table tabName;
~删除user表
drop table user;
<3>操作表记录
1.Insert语句
INSERT INTO table [(column [, column...])] VALUES (value [, value...]);
~使用insert语句向表中插入三个员工的信息
insert into employee (id,name,gender,birthday,entry_date,job,salary,resume)
values (null,'张飞',1,'1990-09-09','1990-10-01','打手',998.0,'真的很能打...');
insert into employee values (null,'关羽',0,'1980-01-01','1980-01-02','武圣',9000.0,'抢地盘都靠他了..');
insert into employee values (null,'刘备',0,'1970-01-01','1970-01-02','ceo',900000.0,'甩手掌柜,都交给其他人了...')
,(null,'赵云',1,'2000-10-01','2001-10-01','保镖队长',1000.0,'老板的贴身保镖..');
*乱码解决
set names xxx; #通知数据库服务器当前客户端使用的是什么编码,服务器收到这条指令后,和这个客户端的通信就会使用这个指定的编码了.就可以解决掉乱码.
如果每次都进来设置set names 很麻烦,此时可以修改mysql安装目录下的my.ini文件中的default-character-set=xxx,这样就可以修改mysql数据库默认认为的客户端的编码了.
2.Update语句
UPDATE tbl_name SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE where_definition]
~将所有员工薪水修改为5000元。
update employee set salary=5000;
~将姓名为’张飞’的员工薪水修改为3000元。
update employee set salary=3000 where name='张飞';
~将姓名为’关羽’的员工薪水修改为4000元,job改为ccc。
update employee set salary=4000 ,job='ccc' where name='关羽';
~将'刘备'的薪水在原有基础上增加1000元。
update employee set salary=salary+1000 where name='刘备';
3.Delete语句
delete from tbl_name [WHERE where_definition]
~删除表中名称为’张飞’的记录。
delete from employee where name='张飞';
~删除表中所有记录。
delete from employee;
~使用truncate删除表中记录。
truncate table employee;
4.Select 语句
~1.普通查询
SELECT [DISTINCT] *|{column1, column2. column3..} FROM table;
~查询表中所有学生的信息。
select * from exam;
~查询表中所有学生的姓名和对应的英语成绩。
select name,english from exam;
~过滤表中重复数据。
select distinct english from exam;
~在所有学生分数上加10分特长分显示。
select name ,english+10 ,chinese+10,math+10 from exam;
~统计每个学生的总分。
select name, english+math+chinese from exam;
~使用别名表示学生总分。
select name as 姓名 ,english+math+chinese as 总成绩 from exam;
select name 姓名 ,english+math+chinese 总成绩 from exam;
错误的写法:select name english from exam;
~2.过滤查询
~查询姓名为张飞的学生成绩
select * from exam where name='张飞';
~查询英语成绩大于90分的同学
select * from exam where english>90;
~查询总分大于230分的所有同学
select name 姓名,math+english+chinese 总成绩 from exam where math+english+chinese>230;
~查询英语分数在 80－100之间的同学。
select * from exam where english between 80 and 100;
~查询数学分数为75,76,77的同学。
select * from exam where math in (75,76,77);
~查询所有姓张的学生成绩。
select * from exam where name like '张%';
select * from exam where name like '张_';
select * from exam where name like '张__';
~查询数学分>70，语文分>80的同学。
select * from exam where math>70 and chinese>80;
~3.排序查询
SELECT column1, column2. column3.. FROM table order by column asc|desc
*默认是升序,可以通过asc desc手动控制升序降序
~对语文成绩排序后输出。
select * from exam order by chinese desc;
~对总分排序按从高到低的顺序输出
select name 姓名 ,math+english+chinese 总成绩 from exam order by 总成绩 desc;
~对姓张的学生成绩排序输出
select name 姓名 ,math+english+chinese 总成绩 from exam where name like '张%' order by 总成绩 desc;
~4.使用聚合函数
Count(列名)返回某一列，行的总数
~统计一个班级共有多少学生？
select count(*) from exam;
~统计数学成绩大于70的学生有多少个？
select count(*) from exam where math>70;
~统计总分大于250的人数有多少？
select count(math+english+chinese) from exam where math+english+chinese>230;
Sum函数返回满足where条件的行的和
~统计一个班级数学总成绩？
select sum(math) from exam;
~统计一个班级语文、英语、数学各科的总成绩
select sum(math),sum(english),sum(chinese) from exam;
~统计一个班级语文、英语、数学的成绩总和
select sum(math+english+chinese) from exam;
~统计一个班级语文成绩平均分
select sum(chinese)/count(*) from exam;
AVG函数返回满足where条件的一列的平均值
~求一个班级数学平均分？
select avg(math) from exam;
~求一个班级总分平均分？
select avg(math+english+chinese) from exam;
Max/min函数返回满足where条件的一列的最大/最小值
~求班级最高分和最低分（数值范围在统计中特别有用）
select max(math+english+chinese) from exam;
select min(math+english+chinese) from exam;
~5.分组查询
~对订单表中商品归类后，显示每一类商品的总价
select product,sum(price) from orders group by product;
~查询购买了几类商品，并且每类总价大于100的商品
select product,sum(price) from orders group by product having sum(price)>100;
~查询单价小于100而总价大于150的商品名称
select product from orders where price<100 group by product having sum(price)>150;
where 子句负责在分组之前进行过滤,having子句附在在分组之后进行过滤
where 子句中不可以使用聚合函数,having子句中可以使用聚合函数
使用where子句的地方可以使用having子句进行替代.
*sql语句书写的顺序:
select from where group by having order by
*sql语句的执行顺序:
from where select group by having order by
四、备份恢复数据库
备份数据库:
cmd：
mysqldump -u root -p dbname>c:/1.sql
恢复数据库:
方式1 cmd(恢复数据库,只能恢复数据库中的数据,库本身不能恢复,需要自己创建):
mysql -u root -p dbname<c:/1.sql
方式2 在mysql下(恢复数据前要创建出数据库并进入数据库):
source c:/1.sql
五、约束
1.创建表时指定约束：
create table tb(
id int primary key auto_increment,
name varchar(20) unique not null,
ref_id int,
foreign key(ref_id) references tb2(id)
);
create table tb2(
id int primary key auto_increment
);
2.外键约束：
（1）增加外键：
可以明确指定外键的名称，如果不指定外键的名称,mysql会自动为你创建一个外键名称。
RESTRICT : 只要本表格里面有指向主表的数据， 在主表里面就无法删除相关记录。
CASCADE : 如果在foreign key 所指向的那个表里面删除一条记录，那么在此表里面的跟那个key一样的所有记录都会一同删掉。
alter table book add [constraint FK_BOOK] foreign key(pubid) references pub_com(id) [on delete restrict] [on update restrict];
（2）删除外键
alter table 表名 drop foreign key 外键（区分大小写，外键名可以desc 表名查看）;
3.主键约束：
（1）增加主键（自动增长，只有主键可以自动增长）
Alter table tb add primary key(id) [auto_increment];
（2）删除主键
alter table 表名 drop primary key
（3）增加自动增长
Alter table employee modify id int auto_increment;
（4）删除自动增长
Alter table tb modify id int;
--------------------------------------------------------------------------------------------------
五、多表设计
一对一（311教室和20130405班级，两方都是一）:在任意一方保存另一方的主键
一对多、多对一（班级和学生，其中班级为1，学生为多）：在多的一方保存一的一方的主键
多对多（教师和学生，两方都是多）：使用中间表，保存对应关系
--------------------------------------------------------------------------------------------------
六、多表查询
create table tb (id int primary key,name varchar(20) );
create table ta (
id int primary key,
name varchar(20),
tb_id int
);
insert into tb values(1,'财务部');
insert into tb values(2,'人事部');
insert into tb values(3,'科技部');
insert into ta values (1,'刘备',1);
insert into ta values (2,'关羽',2);
insert into ta values (3,'张飞',3);
mysql> select * from ta;
+----+------+-------+
| id | name | tb_id |
+----+------+-------+
| 1 | aaa | 1 |
| 2 | bbb | 2 |
| 3 | bbb | 4 |
+----+------+-------+
mysql> select * from tb;
+----+------+
| id | name |
+----+------+
| 1 | xxx |
| 2 | yyy |
| 3 | yyy |
+----+------+
1.笛卡尔积查询：两张表中一条一条对应的记录，m条记录和n条记录查询，最后得到m*n条记录，其中很多错误数据
select * from ta ,tb;
mysql> select * from ta ,tb;
+----+------+-------+----+------+
| id | name | tb_id | id | name |
+----+------+-------+----+------+
| 1 | aaa | 1 | 1 | xxx |
| 2 | bbb | 2 | 1 | xxx |
| 3 | bbb | 4 | 1 | xxx |
| 1 | aaa | 1 | 2 | yyy |
| 2 | bbb | 2 | 2 | yyy |
| 3 | bbb | 4 | 2 | yyy |
| 1 | aaa | 1 | 3 | yyy |
| 2 | bbb | 2 | 3 | yyy |
| 3 | bbb | 4 | 3 | yyy |
+----+------+-------+----+------+
2.内连接：查询两张表中都有的关联数据,相当于利用条件从笛卡尔积结果中筛选出了正确的结果。
select * from ta ,tb where ta.tb_id = tb.id;
select * from ta inner join tb on ta.tb_id = tb.id;
mysql> select * from ta inner join tb on ta.tb_id = tb.id;
+----+------+-------+----+------+
| id | name | tb_id | id | name |
+----+------+-------+----+------+
| 1 | aaa | 1 | 1 | xxx |
| 2 | bbb | 2 | 2 | yyy |
+----+------+-------+----+------+
3.外连接
（1）左外连接：在内连接的基础上增加左边有右边没有的结果
select * from ta left join tb on ta.tb_id = tb.id;
mysql> select * from ta left join tb on ta.tb_id = tb.id;
+----+------+-------+------+------+
| id | name | tb_id | id | name |
+----+------+-------+------+------+
| 1 | aaa | 1 | 1 | xxx |
| 2 | bbb | 2 | 2 | yyy |
| 3 | bbb | 4 | NULL | NULL |
+----+------+-------+------+------+
（2）右外连接：在内连接的基础上增加右边有左边没有的结果
select * from ta right join tb on ta.tb_id = tb.id;
mysql> select * from ta right join tb on ta.tb_id = tb.id;
+------+------+-------+----+------+
| id | name | tb_id | id | name |
+------+------+-------+----+------+
| 1 | aaa | 1 | 1 | xxx |
| 2 | bbb | 2 | 2 | yyy |
| NULL | NULL | NULL | 3 | yyy |
+------+------+-------+----+------+
（3）全外连接：在内连接的基础上增加左边有右边没有的和右边有左边没有的结果
select * from ta full join tb on ta.tb_id = tb.id; --mysql不支持全外连接
select * from ta left join tb on ta.tb_id = tb.id
union
select * from ta right join tb on ta.tb_id = tb.id;
mysql> select * from ta left join tb on ta.tb_id = tb.id
-> union
-> select * from ta right join tb on ta.tb_id = tb.id; --mysql可以使用此种方式间接实现全外连接
+------+------+-------+------+------+
| id | name | tb_id | id | name |
+------+------+-------+------+------+
| 1 | aaa | 1 | 1 | xxx |
| 2 | bbb | 2 | 2 | yyy |
| 3 | bbb | 4 | NULL | NULL |
| NULL | NULL | NULL | 3 | yyy |
+------+------+-------+------+------+
扫一扫
1843
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
