MySQL

1. MySQL

平时如何保存数据？

l 使用文本文件

l 使用word或者excel

什么是数据库？

存放数据的仓库

常用的数据库有哪些？

l Oracle：MySQL、Oracle

l 微软：Sql Server、Access

什么是MySQL？

Mysql是最流行的关系型数据库管理系统，是一个软件。

数据以表格的形式出现

名词解释

DB(DataBase)数据库

DBMS(Database Management System)数据库管理系统

RDBMS(Relational Database Management System)关系数据库管理系统

DBA(Database Administrator)数据库管理员。负责维护DBMS稳定、高效的运行。

SQL（Structured Query Language）结构化查询语言。数据库语言，可以操作数据。

1.1. MySQL数据库安装

1.1.1. 下载MySQL

http://dev.mysql.com/downloads/

1.1.2. 安装MySQL

全部默认配置，密码可使用1234

1.1.3. 启动MySQL

开始->程序->MySQL Notifier->状态栏小图标->控制启停

1.1.4. 分析MySQL目录

bin目录：存放都是可执行文件

mysql.exe：mysql客户端的命令。

mysqld.exe：mysql服务器端的命令。

1.1.5. 配置环境变量

在Path中增加mysql.exe所在目录。

1.1.6. 初始化data目录

mysqld  --initialize-insecure  --user=mysql

1.2. MySQL登录与退出

1.2.1. 登录数据库

mysql -uroot –ppassword [-h  ip]

-u  user  用户名

-p  password  密码

-h  hostname  MySQL服务器的IP地址

如果用户名和密码都是root，可输入

mysql -uroot –proot  登录的本地的

mysql –uroot –proot  -h10.0.5.90  表示 你要登录ip地址为10.0.5.90的主机上的mysql数据库

mysql –u用户名 –p密码  -hip地址

mysql –uxiaoming  –p123456  –h10.0.5.98   自定义的用户的登录 （xiaoming这个用户是root用户创建的）

mysql –uroot  -p密码   根用户的用户（登录的密码，可以自行修改）

1.2.2. 退出数据库

\q  或者  quit

1.2.3. 退出终端

exit

1.3. 数据库操作

1.3.1. 语法格式

一行语句输入完成后，回车是换行，不是执行，当语句中包含分号时，回车才会执行。

语法不区分大小写，数据的内容区分大小写。

一般情况下，关键字全部大写。为了方便，学习时使用小写。

1.3.2. 显示所有的数据库

show databases;

1.3.3. 显示所有数据库，数据库名称中包含my才会被显示

show databases like '%my%';  名字中包含“my”的所有数据库

show databases like 'nj%'; 以“nj” 开头的所有数据库

show databases like ‘nj__’; 以“nj”开头，后面带2个任意字符的数据库

show databases like ‘____’; 检索出名字是4个任意字符的数据库

%表示任意长度的字符串

_表示一个字符

1.3.4. 创建数据库

create database njwangbo default charset utf8;

create database if not exists njwangbo default charset utf8;

1.3.5. 删除数据库

drop database njwangbo;

1.4. 表操作

1.4.1. 使用数据库

一个数据库中可以储存若干张表，表必须保存在数据库中。

可以把数据库看作是文件夹，表是文件夹中的文件，一个数据库中的表不能同名。

对表进行任何操作之前必须选择一个数据库，否则不知道你想操作哪个数据库中的表。

use njwangbo;

1.4.2. 显示数据库中的所有表

show tables;

1.4.3. 创建表

创建学生表

t_student

t_id  学号

t_name 姓名

t_age  年龄

t_sex   性别

t_birthday  生日

一个表，由列组成，且每一列必须指定存放的数据类型。

 

1.4.4. mysql中的数据类型

int 4字节，整数

float(m, d) 4字节，单精度浮点型，m总个数，d小数位 同double（m,d）原理相同

double(m, d) 8字节，双精度浮点型，m总个数，d小数位，整数部分个数最多=m-d , 小数点不要算在内  double(5,2)   222.67 ok  2222.67 no,  222.673à222.67 ok, 222.6753-à222.68 ok ,788.9-à788.90 ok,

decimal(5,2) decimal是存储为字符串的浮点数，整数部分为3位，小数部分为2位

date  3字节，日期，格式：2016-01-01

datetime 8字节，日期时间，格式：2016-01-01 01:01:01

char(10) 固定分配10个字符长度

varchar(10) 长度不确定，最多能存储10个字符，可变长度

char和varchar区别？

(1)char长度固定，不满10个字符，使用空格表示。

varchar不固定，不满10个字符，不需要使用空格表示。

varchar比char省空间

(2)varchar比char效率低

varchar类型的数据，存储时，数据库需要额外的计算出有多少长度的字符并且记录下来，在检索时，还要根据实际的长度获取，这样降低了效率，varchar以时间换空间。

(3)什么时候使用char类型？varchar类型

性别用char

手机号码11位用char

新闻的标题(具体多少个字符，不确定，可长可短) 用varchar(200)

家庭住址 用varchar(60)

blob 二进制大对象，可以储存可变长度的数据，比如图片

text 长文本数据，比如保存一篇文章  (textArea文本域内容很长)

例题1：

讲解数据类型

create table t_student03(

t_stuNo  int(5)  not null,

java_score double  ,

html_score double(5,2),

js_score float,

oracle_score float(5,2),

born_date  date,

createTime  dateTime

)engine=InnoDB;

select  *  from t_student03;

插入数据： 

insert into t_student03(t_stuNo,java_score,html_score, js_score ,oracle_score ,born_date  ,createTime )

values(123456,34.56223, 222.6753,23.3333,889.8,'1993-4-5','2017-5-18 11:55:23');

1.4.5. 创建表的步骤

（1） 表名称

（2） 表的字段

（3） 表的字段类型

1.4.6. 查看表结构

desc  表名

如：desc t_student

1.4.7. 查看表的创建语句

show create table 表名

1.4.8. 删除表

drop table 表名

1.4.9. 如何处理表结构错误？（了解）

在实际使用中，一般表建立之后，不会修改表中结构。

先删表，再重新创建表

修改表名

rename table t_旧表名 to t_新表名;

rename table t_student02 to student02;

rename table student02 to t_student02 ;

修改字段的类型

alter table t_表名 modify t_字段名 数据类型;

alter table t_student02 modify t_stuNo  varchar(10);

给表添加一个字段

alter table t_表名 add t_字段名 数据类型;

alter table t_student02 add t_email varchar(50);

删除一个字段

alter table t_表名 drop t_字段名;

alter table t_student02 drop t_email;

1.5. 数据增删改查

如果是字符类型(char,varchar)或日期类型，使用单引号'

如果是是数值类型，直接写数字

1.5.1. 表中插入一条数据

格式

insert into 表名(列名1, 列名2…列名n)  values (值1,值2…值n);

如

注意：如果不是基本数据类型(int,double)，需要加英文输入法下的单引号  

单行插入 

insert into t_student01(t_stuNo,t_name,t_email,t_phone,t_address,t_sex)

values(101,'jack','[e-mail]','13611259877','南京','男');

多行插入

insert into t_student01(t_stuNo,t_name,t_email,t_phone,t_address,t_sex)

values(102,'jack2','[e-mail]','13611259807','北京','男'),

(103,'tom','[e-mail]','13011259807','北京','男'),

(104,'helen','[e-mail]','13711259807','南京玄武路18号','男');

 

insert into t_student(t_id,t_name,t_age,t_sex,t_birthday,t_score,t_salary)

values(101,'张三1',10,'F','2013-4-5',89,5600),

(102,'张三2',11,'F','1993-4-5',89,2600),

(103,'张三3',20,'F','1980-4-5',89,1600),

(104,'张三4',30,'F','1988-4-5',89,4600),

(105,'张三5',40,'F','1997-4-5',89,600),

(106,'张三6',80,'F','2013-4-5',89,1600);

如果表中所有列都要插入数据，可以不用在表名后面列出所有的字段，直接写值，但是顺序一定不能错。这种方法不规范，不要用。

A. 可以省略字段名(但是后面的数据必须和表的字段的顺序一致，而且所有字段都要包含)

insert into t_student01

values(102,'jack2','[e-mail]','13611259807','北京','男'),

(103,'tom','[e-mail]','13011259807','北京','男'),

(104,'helen','[e-mail]','13711259807','南京玄武路18号','男');

insert into t_student01

values('13611259807','北京','男',102,'jack2','[e-mail]'),

B. 可以只 插入部分列，但是列名必须写 ,没有插入的列有默认值NULL

insert into t_student01(t_stuNo,t_sex,t_name)

values(108,'女','李四');

insert into t_student01(t_stuNo,t_sex,t_name)

values(108,'女','李四'),

(109,'女','李四2'),

(110,'女','李四3');

注意：

（1） 列名的顺序和表创建时列的顺序必须相同。

（2） 值的顺序和列名的顺序必须相同，数据类型也必须相符。

（3） 如果要插入空值，可以以null代替。

（4） 如果字段列表没有写全，未设置值的列默认为null

Mysql特有的批量插入(测试时比较有用) 一次可以插入多行，见上方示例。

1.5.2. 查询表中所有的数据

使用where和> < =比较运算符控制查询结果

例题1

---查询所有年龄大于30岁的人员信息

select * from t_student where t_age>30;

---查询性别为M,出生日期在1980年之后的，分数在80分以上的人员的所有信息

select  *  from t_student  where t_sex='M'  and  year(t_birthday)>1980  and t_score>80;

---查询性别为M,出生日期在1980年之后的，分数在80分以上的人员的姓名，学号(部分列)

select  t_name, t_id  from t_student  where t_sex='M'  and  year(t_birthday)>1980  and t_score>80;

1.5.3. 修改数据

格式：

update 表名 set 列=值,列=值…

update 表名 set  列名1=值，列名2=值2，列名3=值3,.... where 条件

如：

--将分数全部更新成80分

update t_student set t_score=80;

---将学号为101的学生，分数更新成98分

update t_student set t_score=98 where t_id=101;

--将性别为M的所有学生，分数加5分

update t_student set t_score=t_score+5 where t_sex='M';

--将性别为F,同时出生日期在1993年以后的学生，将其分数加3分，将其年龄加2岁。

update t_student set t_score=t_score+3,t_age=t_age+2  where t_sex='F' and  t_birthday>'1993-12-31';

update t_student set t_score=t_score+3,t_age=t_age+2  where t_sex='F' and  year(t_birthday)>1993;

修改性别都为男

Update t_student set t_sex=’男’;

修改性别都为女 同时将年龄都改成21

Update t_student set t_sex=‘女’, t_age=21;

工资sal大于3000的工资增加500

Update t_student set t_salary=t_salary+500 where t_salary>3000;

将年龄大于23的学生的工资增加300

update t_student set t_salary=t_salary+300 where t_age>23;

将学号为1,11,21,31,41学生，年龄增长一岁，工资增长25%

update t_student set t_age=t_age+1,t_salary=t_salary*1.25  where  t_id in (1,11,21,31,41);

使用where和> < =比较运算符控制查询结果

1.5.4. 删除表中所有的数据

delete from 表名;

注意：仅仅是删除数据，表结构存在。

删除t_student表中的所有数据另外一种方法

truncate table t_student ;

delete和truncate 区别

delete删除的数据可以恢复。写日志，dba可以根据日志恢复数据。

truncate删除数据不能恢复。直接删，不记录日志。

delete删除慢，truncate删除快

delete可以有条件的删除，truncate默认所有的表内容都删除

工作中使用delete，truncate在测试时可以用

1.5.5. 根据条件删除数据

例题1：

delete from 表名 where 条件;

A.删除性别是男的所有人的信息

delete from t_student01 where t_sex='男';

A.删除地址以南京开头的所有人员信息

delete from t_student01 where t_address like '南京%';

C.删除地址为NUll的所有人员信息

delete from t_student01 where t_address  is  NULL;

D.删除手机号以807结尾，地址以‘北京开头’,性别是女的所有人的信息

delete from t_student01 where  t_phone like '%807'  and  t_address like '北京%'  and  t_sex='女';

比较运算符

=、!=/<>、>、>=、<、<=

删除姓名为xiaoMing学生信息

delete from  t_student where t_name='xiaoming';

删除性别不为F的所有学生信息

delete from t_student where t_sex !='F';

删除年龄大于25的所有学生信息

delete from t_student where t_age>25;

null

ex:删除电话为null的学生信息

alter table t_student add t_phone char(11);

delete from t_student where t_phone is null;

and  or

and连接两个条件，表示两个条件必须同时满足，结果才满足。

or 连接两个条件，表示其中任意一个条件成立，结果就成立。

删除性别为M且年龄在25岁以上的所有学生信息

delete from t_student where t_sex='M' and t_age>25;

删除分数在80到90之间所有学生的信息

--没有分数这列，先添加

alter table t_student add t_score double;

delete from t_student where t_score>=80 and t_score<=90;

删除性别为M或者分数小于60分所有学生的信息

delete from t_student where t_sex='M'  or  t_score<60;

删除学号为1, 2, 3的学生信息,t_id此处是int类型

delete from t_student where t_id =1 or t_id=2 or t_id=3;

delete from t_student where t_id in(1,2,3);

between  and

删除分数在80到90之间所有学生的信息

delete from t_student where t_score between 80 and 90;

not

删除分数不在80到90之间所有学生的信息

delete from t_student where t_score<80 or t_score>90;

delete from t_student where t_score not between 80 and 90;

not in

删除学号不为1， 2， 3的所有学生信息

delete from t_student where t_id not in(1,2,3);

delete from  t_student where t_id  !=1 and t_id  !=2  and  t_id  !=3;

删除电话不为null的所有学生信息

delete from t_student where t_phone is not  null;

1.6. 备份

备份： 先退出数据登录

1． 备份数据库（备份所有的表）

mysqldump –u登录的用户名 –p登录的密码  要备份的数据库名 > 物理位置/XXX.sql

mysqldump   -uroot  -proot njwangbo1  > E:/others/njwangbo.sql   表示将njwangbo1数据库，全部备份到E:/others/njwangbo.sql物理文件中,注意最后不要加分号

2． 备份部分表（可能是1张表，也可能是多张表）

mysqldump  –uroot  –proot njwangbo1 t_student  t_student01 > E:/others/student.sql 表示备份njwangbo1下面的2张表的数据

mysqldump  -uroot  -proot njwangbo1  t_student > E:/others/stu.sql  表示备份njwangbo1下面的1张表的数据

还原：

两种方式：（不管是哪种还原，都需要先创建1个空的数据库）

1． 登录状态下 source E:/others/njwangbo.sql 还原整个数据库中的所有表数据  source E:/others/twostudent.sql 还原2张表（t_student t_student01）的数据

2． 退出登录状态下，  mysql  –uroot  –proot njwangbo1 < E:/others/twostudent.sql 还原2张表的数据  

mysql  –uroot  –proot njwangbo1 < E:/others/njwangbo1.sql 还原所有的表（整个数据库）的数据  

1.7. 作业

图书管理系统数据设计

建立一个数据库存放图书

新建一张表存放图书，字段自拟，至少包含价格

往表中插入10条数据

把价格超过50元的书显示出来

把小于30元的书删除

把图书表删除

把数据库删除