#数据库mysql安装并使用
##mysql安装
- 打开下载的 mysql 安装文件双击解压缩,运行“mysql-5.5.40-win32/64.msi”,根据自己电脑的位数选择相应的安装文件。
2.选择安装类型,有“Typical(默认)”、“Complete(完全)”、“Custom(用户自定义)”三个选项,选择“Custom”,
按“next”键继续。
3.点选“Browse”,手动指定安装目录。
4.填上安装目录,我的是“d:\Program Files (x86)\MySQL\MySQL Server 5.0”(自己选择自己想要安装的位置)按“OK”继续。
5.确认一下先前的设置,如果有误,按“Back”返回重做。按“Install”开始安装。
6.正在安装中,请稍候,直到出现下面的界面, 则完成 MYSQL 的安装
- 安装完成了,出现如下界面将进入 mysql 配置向导。
- 选择配置方式,“Detailed Configuration(手动精确配置)”、“Standard Configuration(标准配置)”,我
们选择“Detailed Configuration”,方便熟悉配置过程。
- 选择服务器类型,“Developer Machine(开发测试类,mysql 占用很少资源)”、“Server Machine(服务
器类型,mysql 占用较多资源)”、“Dedicated MySQL Server Machine(专门的数据库服务器,mysql 占
用所有可用资源)”
10.选择mysql数据库的大致用途,“Multifunctional Database(通用多功能型,好)”、“Transactional
Database Only(服务器类型,专注于事务处理,一般)”、“Non-Transactional Database Only(非事务
处理型,较简单,主要做一些监控、记数用,对 MyISAM 数据类型的支持仅限于 non-transactional),按“Next”
继续。
11.选择网站并发连接数,同时连接的数目,“Decision Support(DSS)/OLAP(20个左右)“Online ”、 Transaction
Processing(OLTP)(500 个左右)”、“Manual Setting(手动设置,自己输一个数)”。
12.是否启用 TCP/IP 连接,设定端口,如果不启用,就只能在自己的机器上访问 mysql 数据库了,在这个页
面上,您还可以选择“启用标准模式”(Enable Strict Mode),这样 MySQL 就不会允许细小的语法错误。
如果是新手,建议您取消标准模式以减少麻烦。但熟悉 MySQL 以后,尽量使用标准模式,因为它可以降
低有害数据进入数据库的可能性。按“Next”继续
- 就是对 mysql 默认数据库语言编码进行设置(重要),一般选 UTF-8,按 “Next”继续。
- 选择是否将 mysql 安装为 windows 服务,还可以指定 Service Name(服务标识名称),是否将 mysql 的 bin
目录加入到 Windows PATH(加入后,就可以直接使用 bin 下的文件,而不用指出目录名,比如连接,
“mysql.exe -uusername -ppassword;”就可以了,不用指出 mysql.exe 的完整地址,很方便),我这里全部
打上了勾,Service Name 不变。按“Next”继续。
- 询问是否要修改默认 root 用户(超级管理)的密码。“Enable root access from remote machines(是否允
许 root 用户在其它的机器上登陆,如果要安全,就不要勾上,如果要方便,就勾上它)”。最后“Create
An Anonymous Account(新建一个匿名用户,匿名用户可以连接数据库,不能操作数据,包括查询)”,一般就不用勾了,设置完毕,按“Next”继续。
- 确认设置无误,按“Execute”使设置生效,即完成 MYSQL 的安装和配置。
**注意:**设置完毕,按“Finish”后有一个比较常见的错误,就是不能“Start service”,一般出现在以前有安装 mysql
的服务器上,解决的办法,先保证以前安装的 mysql 服务器彻底卸载掉了;不行的话,检查是否按上面一步所说,
之前的密码是否有修改,照上面的操作;如果依然不行,将 mysql 安装目录下的 data 文件夹备份,然后删除,在
安装完成后,将安装生成的 data 文件夹删除,备份的 data 文件夹移回来,再重启 mysql 服务就可以了,这种情况
下,可能需要将数据库检查一下,然后修复一次,防止数据出错。
##未安装成功则进行卸载重新安装 - 停止 window 的 MySQL 服务。 找到“控制面板”-> “管理工具”-> “服务”,停止 MySQL 后台服务。
- 卸载 MySQL 安装程序。找到“控制面板”-> “程序和功能”,卸载 MySQL 程序。
- 删除 MySQL 安装目录下的所有文件。
- 删除 c 盘 ProgramDate 目录中关于 MySQL 的目录。路径为:C:\ProgramData\MySQL(是隐藏文件,需要显示
出来)
##数据库服务的启动与登录
MySQL 服务器启动方式有两种: - 通过服务的方式自动启动
2.手动启动的方式
Windows 服务方式启动
1.1 操作步骤:
2 DOS 命令方式启动
2.1 操作步骤:
###mysql的目录结构
##SQLyog 图形化工具——客户端
SQLyog 是业界著名的 Webyog 公司出品的一款简洁高效、功能强大的图形化 MySQL 数据库管理工具。使用
SQLyog 可以快速直观地让您从世界的任何角落通过网络来维护远端的 MySQL 数据库
安装过程一直next即可。
在连接数据库的界面时按照以下方式配置
###SQLyog的使用
右键点击主机名来创建数据库
到如下界面输入数据库名称,选择集字符集为utf8
创建数据库成功后会有数据库的文件层
###sql语句的学习
在工具右边点击询问,在中间写sql语句,选择要执行的sql语句,点击左上角执行查询来执行选中的sql语句。
1.Data Definition Language (DDL 数据定义语言) 如:建库,建表
2 Data Manipulation Language(DML 数据操纵语言),如:对表中的记录操作增删改
3 Data Query Language(DQL 数据查询语言),如:对表中的查询操作
4 Data Control Language(DCL 数据控制语言),如:对用户权限的设置
- 创建表
creat table 表名(表列名 数据类型,…,表列名 数据类型);
create table 表名 like 另一个表名(创建两个一样的表)
数据类型:
整数:
tinyInt(微整型:很小的整数(占 8 位二进制))
smallint(小整型:小的整数(占 16 位二进制))
mediumint(中整型:中等长度的整数(占 24 位二进制))
int(integer)(整型:整数类型(占 32 位二进制))
小数:
float(单精度浮点数,占 4 个字节)
double(双精度浮点数,占 8 个字节)
日期:
time(表示时间类型)
date(表示日期类型)
datetime(同时可以表示日期和时间类型)
字符串:
char(m)(固定长度的字符串,无论使用几个字符都占满全部,m为 0~255 之间的整字符串 数)
varchar(m)可变长度的字符串,使用几个字符就占用几个,m为 0~65535 之间的整数()
大二进制:
tinyblob&&Big Large Object(允许长度 0~255 字节)
blob(允许长度 0~65535 字节)
mediumblob(允许长度 0~167772150 字节)
longblob(允许长度 0~4294967295 字节)
大文本:
tinytext(允许长度 0~255 字节)
text(允许长度 0~65535 字节)
mediumtext(允许长度 0~167772150 字节)
longtext(允许长度 0~4294967295 字节)
CREATE TABLE student3 (
id INT, -- 编号
NAME VARCHAR(20), -- 姓名
age INT, -- 年龄
sex VARCHAR(5), -- 性别
address VARCHAR(100), -- 地址
math INT, -- 数学
english INT -- 英语
);
CREATE TABLE stu3 LIKE student3;
运行结果:
增加数据:
INSERT INTO 表名:表示往哪张表中添加数据
(字段名 1, 字段名 2, …):要给哪些字段设置值
VALUES (值 1, 值 2, …):设置具体的值
INSERT INTO student3(id,NAME,age,sex,address,math,english)
VALUES (1,'马云',55,'男','杭州',66,78),
(2,'马化腾',45,'女','深圳',98,87),
(3,'马景涛',55,'男','香港',56,77),
(4,'柳岩',20,'女','湖南',76,65),
(5,'柳青',20,'男','湖南',86,NULL),
(6,'刘德华',57,'男','香港',99,99),
(7,'马德',22,'女','香港',99,99),
(8,'德玛西亚',18,'男','南京',56,65);
删除数据
DROP TABLE 表名;
DROP TABLE IF EXISTS 表名;
DROP TABLE student3;
DROP TABLE IF EXISTS student3;
修改数据
ALTER TABLE 表名 ADD 列名 类型;
ALTER TABLE 表名 MODIFY 列名 新的类型;
ALTER TABLE 表名 CHANGE 旧列名 新列名 类型;
ALTER TABLE 表名 DROP 列名;
RENAME TABLE 表名 TO 新表名;
ALTER TABLE 表名 character set 字符集;
ALTER TABLE student3 ADD chinese INT;--添加chinese列 类型为整型
ALTER TABLE student3 MODIFY chinese VARCHAR(20);--将chinese列类型改为varchar类型长度为20
ALTER TABLE student3 CHANGE chinese chinese2 INT;--将chinese列名改为chinese2类型为整型
ALTER TABLE student3 DROP chinese2;--删除chinese2列
RENAME TABLE student3 TO student4;--将表student3名字改为student4
ALTER TABLE student4 CHARACTER SET gbk;--将student4的字符集改为gbk
查询表
select 列名,…,列名 from 表名;(*表示全部列)
SELECT * FROM student3;
SELECT id,NAME,age,sex,address,math,english FROM student3;
运行结果
select 列名,…,列名 from 表名 order by 列名 asc/desc,列名 asc/desc;(asc:升序(默认);desc:降序)
- 注意:
如果有多个排序条件,则当前边的条件值一样时,才会判断第二条件。
SELECT * FROM student3 ORDER BY age ASC;
SELECT * FROM student3 ORDER BY age DESC;
SELECT * FROM student3 ORDER BY math DESC,english DESC;
聚合函数:将一列数据作为一个整体,进行纵向的计算。
select 聚合函数 as 别名 from 表名;
1. count:计算个数(一般选择主键来计算个数)
SELECT COUNT(*) FROM student3;
2. max:计算最大值
SELECT MAX(math) FROM student3;
3. min:计算最小值
SELECT MIN(math) FROM student3;
4. sum:计算和
SELECT SUM(math) FROM student3;
5. avg:计算平均值
SELECT AVG(math) FROM student3;
* 注意:聚合函数的计算,排除null值。
解决方案:
1. 选择不包含非空的列进行计算
2. IFNULL函数
> **一般要用的函数**
#GROUP_CONCAT() 该函数返回带有来自一个组的连接的非NULL值的字符串结果
以后项目课再演示!!!
#ifnull(exp1,exp2) 如果为空
select ifnull(a,'无') 如果a字段有空数据则显示为'无'
#if(exp1,exp2,exp3)
select if(male=0,'男','女') 如果male字段等于0显示'男'否则显示'女'
一、数学函数:
#ABS 绝对值函数
select abs(-5) ;
#ceil 天花板整数,也就是大于x的整数
select ceil(-13.5);
#FLOOR(X)小于x的最大整数
select FLOOR(3.5);
#返回集合中最大的值,least返回最小的,注意跟max,min不一样,max里面跟的是col,返回这个列的最大值最小值
select GREATEST(1,2,3,4);
#mod(x,y)返回x/y的余数
select mod(5,2);
#pi() 返回pi
select pi();
#RAND()返回0,1之间随机数
select rand();
#round(x,y)返回x四舍五入有y位小数的值
select round(pi(),3);
#truncate(x,y) 返回数字x截断为y位小数的结果,就是不考虑四舍五入,直接砍掉
select TRUNCATE(pi(),4),ROUND(pi(),4),pi();
#sign(x) 返回x符号
select sign(-5);
二、聚合函数(常用语group by从句select语句中)
#avg(col)返回指定列平均值
select AVG( quantity) from orderitems;
#count(col)返回指定列中非null数量
select count(quantity) from orderitems;
#min(col)max(col)返回指定列最大最小值
select max(quantity),min(quantity) from orderitems;
#sum(col)返回制定列求和值
select sum(quantity) from orderitems;
#group_concat(col)返回这个列连接组合的结果,中间有逗号隔开
select group_concat(prod_id) from orderitems
三、字符串函数
#concat()连接字符串
select concat('1','我','2');
#concat_ws(sep,s1,s2,s3)连接字符串用sep隔开
select concat_ws('|','1','我','2');
#INSERT(str,pos,len,newstr),将字符串str从pos位置开始的len长度替换为newstr
select 'hello world',INSERT('hello world',2,3,'杰哥哥');
#FIND_IN_SET(str,strlist)分析逗号分隔的list,如果发现str返回list中的位置
select find_in_set('abc','aabc,sdf,det,abc') #返回4
#LCASE(str),LOWER(str)都是返回小写的结果,UPPER(),UCASE() 返回的都是大写结果
select LCASE('UUSU');
#LEFT(str,len)从左到右从str中选择长度为len的字符串,`RIGHT`(str,len)相反
select left('hello world',4);
#substring(str,len)从左到右从str中选择长度为len的字符串截取至尾
select substring('hello world',4);
#length(str)返回str字符串长度
select length('hello world');
#trim(),ltrim()rtrim()分别去掉两头的空格,左边的空格,右边的空格
select trim(' hello ');
#QUOTE(str)用反斜杠转义str中的单引号
select QUOTE("hello ' world")
#REPLACE(str,from_str,to_str)在str中,把from str 转成to_str
select replace('hello world','hello','hi');
#repeat(str,count)重复str count次
select repeat('hello world',3);
#reverse(str)颠倒str
select reverse('hello world');
#STRCMP(expr1,expr2)比较两个字符串,一模一样返回0,不一样返回1
select STRCMP('hello','world')
四、日期和时间函数
#datediff(date1,date2)返回两个日期相隔的天数
SELECT DATEDIFF('2008-12-30','2008-12-29');
#curdate()或者current_date() NOW()是返回日期+时间
select current_date();
#curtime()当前时间,或者current_time()
select curtime();
#DATE_ADD(date,INTERVAL expr unit)返回date加上int日期后的日期,date_sub()也是一样的
select date_add(curdate(),interval 5 day); --返回当前日期后5天 参数可为负
select date_add(curtime(),interval 5 hour); --返回当前时间后5个小时 参数可为负
#dayofweek() DAYOFMONTH(date) DAYOFYEAR(date) 返回日期中是一周中第几天,一个月中第几天,一年中第几天
select DAYOFMONTH(CURDATE());
#dayname返回日期星期名
select dayname(CURDATE());
#hour(time),minute(time),month(date)
select curtime(),hour(CURTIME()),minute(curtime());
select curdate(),month(curdate()),year(curdate()),day(curdate());
#MONTHNAME(date) 返回月份名称
select monthname(curdate());
#quarter(date)返回日期的第几季度
select quarter(curdate());
#week(date)返回星期
select week(curdate());
#year()返回年
select year(curdate());
#minute()返回分钟
select week(now());
#TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)计算两个时间差,还有datediff()
select TIMESTAMPDIFF(year,19920202,CURDATE())
#DATE_FORMAT(date,format)按照格式转化日期,具体的format格式可以查查
select date_format(CURDATE(),'%m-%d-%Y');
%a 缩写星期名
%b 缩写月名
%c 月,数值
%D 带有英文前缀的月中的天
%d 月的天,数值(00-31)
%e 月的天,数值(0-31)
%f 微秒
%H 小时 (00-23)
%h 小时 (01-12)
%I 小时 (01-12)
%i 分钟,数值(00-59)
%j 年的天 (001-366)
%k 小时 (0-23)
%l 小时 (1-12)
%M 月名
%m 月,数值(00-12)
%p AM 或 PM
%r 时间,12-小时(hh:mm:ss AM 或 PM)
%S 秒(00-59)
%s 秒(00-59)
%T 时间, 24-小时 (hh:mm:ss)
%U 周 (00-53) 星期日是一周的第一天
%u 周 (00-53) 星期一是一周的第一天
%V 周 (01-53) 星期日是一周的第一天,与 %X 使用
%v 周 (01-53) 星期一是一周的第一天,与 %x 使用
%W 星期名
%w 周的天 (0=星期日, 6=星期六)
%X 年,其中的星期日是周的第一天,4 位,与 %V 使用
%x 年,其中的星期一是周的第一天,4 位,与 %v 使用
%Y 年,4 位
%y 年,2 位
分组查询
select 列名 from 表名 group by 列名;
SELECT sex FROM student3 GROUP BY sex;
一般将分组查询和聚合函数组合使用。
select 列名…,聚合函数 from 表名 group by 列名…;
SELECT sex,COUNT(*) FROM student3 GROUP BY sex;
在分组查询前面加上条件判断来筛选内容
select 列名,聚合函数 from 表名 where 条件判断 group by 列名;
SELECT math ,COUNT(*) FROM student3 WHERE math>80 GROUP BY math;
在分组后添加条件判断来筛选分组后的内容。
select 列名,聚合函数 from 表名 where 条件判断 group by 列名 having 条件判断;
SELECT math ,COUNT(*) AS c FROM student3 WHERE math>80 GROUP BY math HAVING c>1;
where 和 having 的区别:
1.先进行where条件的筛选再进行分组,
2.删选后的结果再进行having条件筛选
3.where后不能用聚合函数进行判断,having可以用聚合函数进行判断。
4.where不能使用别名
5.having可以使用别名
分页查询
select * from 表名 limit 开始索引,查询条数;
这是mysql的方言,每个数据库写法可能不一样。
SELECT * FROM student3 LIMIT 0,4;
约束
* 概念: 对表中的数据进行限定,保证数据的正确性、有效性和完整性。
* 分类:
1. 主键约束:primary key
2. 非空约束:not null
3. 唯一约束:unique
4. 外键约束:foreign key
* 非空约束:not null,某一列的值不能为null
1. 创建表时添加约束
CREATE TABLE stu(
id INT,
NAME VARCHAR(20) NOT NULL -- name为非空
);
2. 创建表完后,添加非空约束
ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;
3. 删除name的非空约束
ALTER TABLE stu MODIFY NAME VARCHAR(20);
* 唯一约束:unique,某一列的值不能重复
1. 注意:
* 唯一约束可以有NULL值,但是只能有一条记录为null
2. 在创建表时,添加唯一约束
CREATE TABLE stu(
id INT,
phone_number VARCHAR(20) UNIQUE -- 手机号
);
3. 删除唯一约束
ALTER TABLE stu DROP INDEX phone_number;
4. 在表创建完后,添加唯一约束
ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;
* 主键约束:primary key。
1. 注意:
1. 含义:非空且唯一
2. 一张表只能有一个字段为主键
3. 主键就是表中记录的唯一标识
2. 在创建表时,添加主键约束
create table stu(
id int primary key,-- 给id添加主键约束
name varchar(20)
);
3. 删除主键
-- 错误 alter table stu modify id int ;
ALTER TABLE stu DROP PRIMARY KEY;
4. 创建完表后,添加主键
ALTER TABLE stu MODIFY id INT PRIMARY KEY;
5. 自动增长:
1. 概念:如果某一列是数值类型的,使用 auto_increment 可以来完成值得自动增长
2. 在创建表时,添加主键约束,并且完成主键自增长
create table stu(
id int primary key auto_increment,-- 给id添加主键约束
name varchar(20)
);
3. 删除自动增长
ALTER TABLE stu MODIFY id INT;
4. 添加自动增长
ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;
* 外键约束:foreign key,让表于表产生关系,从而保证数据的正确性。
1. 在创建表时,可以添加外键
* 语法:
create table 表名(
....
外键列
constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
);
2. 删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
3. 创建表之后,添加外键
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);
4. 级联操作
1. 添加级联操作
语法:ALTER TABLE 表名 ADD CONSTRAINT 外键名称
FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE ;
2. 分类:
1. 级联更新:ON UPDATE CASCADE
2. 级联删除:ON DELETE CASCADE
用可视化界面来操作数据库,来添加约束。
右键点击要改的表,点击改变表
数据库的设计
1. 多表之间的关系
1. 分类:
1. 一对一(了解):
* 如:人和身份证
* 分析:一个人只有一个身份证,一个身份证只能对应一个人
2. 一对多(多对一):
* 如:部门和员工
* 分析:一个部门有多个员工,一个员工只能对应一个部门
3. 多对多:
* 如:学生和课程
* 分析:一个学生可以选择很多门课程,一个课程也可以被很多学生选择
2. 实现关系:
1. 一对多(多对一):
* 如:部门和员工
* 实现方式:在多的一方建立外键,指向一的一方的主键。
2. 多对多:
* 如:学生和课程
* 实现方式:多对多关系实现需要借助第三张中间表。中间表至少包含两个字段,这两个字段作为第三张表的外键,分别指向两张表的主键
3. 一对一(了解):一般将一对一的两张表合成一张表。
* 如:人和身份证
* 实现方式:一对一关系实现,可以在任意一方添加唯一外键指向另一方的主键。
- 数据库设计的范式
第一范式:每一列都是不可再分的,能在数据库创建的表都是第一范式(没有复合列的表存在);
第二范式:表中的每一列都是对主键完全依赖(表中的每一列都只被主键唯一确定);
第三范式:表中不存在列之间的传递依赖(不存在a决定b,b决定c);
数据库的备份和还原
1. 命令行:
* 语法:
* 备份: mysqldump -u用户名 -p密码 数据库名称 > 保存的路径
* 还原:
1. 登录数据库
2. 创建数据库
3. 使用数据库
4. 执行文件。source 文件路径
2. 图形化工具:
备份:右键想要备份的数据库–>备份/导出–>备份数据库,转存到;
结构唯一:存储数据库和表结构,没有数据。
仅有数据:只存储数据,没有结构
结构和数据:存储数据库的全部内容;
导入;右键想要导入的数据库–>导入–>执行sql脚本;