mysql是一种开放源代码的关系型管理（rdbms），mysql数据库系统使用最常用的数据库管理语言（sql）进行数据库管理。本部分主要内容包括：掌握结构化语言sql，ddl创建数据库和表，dos命令修改和删除库和表，dml命令增删改表数据，dql命令查询表数据，数据库索引与备份。
一、了解数据库
2、数据库-db：长期存放在计算机内，有组织、可共享的大量数据的的集合，是一个数据“仓库”
数据库管理系统-dbms: 数据管理软件，科学组织和存储数据、高效地获取和维护数据
1、sql-结构化语言
ddl（数据定义语言) √ d = definition
定义和管理数据对象，如数据库，数据表等
create、drop、alter、truncate
dml（数据操纵语言）√ m = manipulation
用于操作数据库对象中所包含的数据
insert、update、delete、select
dql（数据查询语言）√ q = query
用于查询数据库数据 select
dcl（数据控制语言） c = control
用来管理数据库的语言，包括管理权限及数据更改
grant、commit、rollback
tcl（事务控制语言） c = control
事务控制语言tcl用于维护数据的一致性
commit、rollback、savepoint;
二、ddl语言创建数据库和表
创建数据库的语法
1、启动并登录数据库：
启动：管理员运行net start mysql或services.msc打开本地服务
登录：mysql -h 服务器主机地址 -uroot -p密码
2、创建数据库
create database if not exists 数据库名;
3、删除数据库
drop database if exists 数据库名;
4、查看数据库
show databases;
5、使用数据库
use 数据库名;
创建表的语法：
1、语法：注意：反引号（撇）用于和关键字区别开来
create table[if not exists] `表名`
(`字段名1` 列类型[属性][索引][注释],
`字段名2` 列类型[属性][索引][注释],
……
`字段名n` 列类型[属性][索引][注释])
[表类型][表字符集][注释];
2、列类型：规定数据库中该列存放的数据类型
数值型：
tinyint 非常小的数据<128 1字节
smallint 较小的数据 2字节
mediumint 中等大小的数据 3字节
int 标准整数 < 21亿 4字节
bigint 较大的整数 8字节
float 单精度浮点数 4字节
double 双精度浮点数 8字节
decimal（m,d）字符串形式的浮点数 m个字节 留d位小数
字符串型:
char(m) 固定长字符串 m字节（0~255）
varchar(m) 可变字符串 m+1字节（0~65535）
tinytext 微型文本串 2^8-1字节
text 文本串 2^16-1字节
日期和时间数值类型:
date yyyy-mm-dd 日期格式
time hh:mm:ss 时间格式
datetime yy-mm-dd hh:mm:ss 日期时间格式
timestamp yyyymmddhhmmss 时间戳
year yy格式的年份值
null值:
没有值或未知值。不要用null进行算术运算，结果仍为null。
mysql中，0或null为假，1为真。
3、列属性：
⑴unsigned无符号的 声明该数据列不允许负数
⑵zerofill 0填充的 不足位数的用0来填充，如 int(3),5则为 005
⑶auto_increment自动增长的，每添加一条数据，自动在上一个记录数
上加1、通常用于设置主键，且为整数类型、可定义起始值和步长。
⑷null 和 not null 默认为null,即没有插入该列的数值。如果设置为not null，则该列必须有值
⑸default 默认的 用于设置默认值 例如，性别字段，默认为“男”，否则为“女”；若无指定该列
的值，则默认为“男”的值
⑹列注释comment：create table [ if not exists ] `test` (
`id` int (11) unsigned comment ‘编码号’
)comment=‘测试表’;
4、可视化软件：sqlyog navicat
新建连接(需要登录)—新建数据库—新建表
新建表：行：记录 列：字段
二、sql实现修改、删除表
1、dos命令的一些语句:
show databases; //查看数据库
use mysql; //使用my
show tables; //展示表
desc 表名； //展示表结构
show create table 表名\g; //展现表创建语句
select * from user; //查询表信息
select * from user\g; //表中信息规范化显示
update user set password=password(‘1234’) where user=’root’; //更改密码
flush privileges; //清理权限
mysql> asbc //随便输入
-> \c //返回上一层
mysql> exit; //退出
show; //show帮助
set names gbk； //设置字符编码
creat table 表名 //创建表
alter table 表名; //修改表
使用工具的一些语法：
set sql-mode=’strict-trans-tables’;设置严格模式
default charset = gbk； //设置字符编码格式
alter table 表名； //修改表
add constraint键名； //添加键
set names gbk; //设置底层编码格式，解决窗口乱码问题
creat table t(id int(4) primary key auto_increment) auto_increment=100；从100开始增长，并设置id为主键
set @@auto_increment_ increment=5;设置自动增长的步幅
delete from 表名 清除表的内容，不影响修改的起始增长和增幅
truncate表名 截断表的内容，起始增长和增幅从头开始
2、创建表并设置类型
creat table 表名（#….单行注释）engine=myisam;
creat table 表名（/*…多行注释..*/）engine=innodb;
3、常见的myisam与innodb类型
适用场合：
使用myisam: 非事务型查询，节约空间及相应速度使用innodb: 安全性，事务处理及多用户操作数据表
名称 myisam innodb
事务处理 不支持 支持（增删改）
全文索引 支持 不支持
表空间大小 较小 较大,约2倍
innodb类型数据表有一个*.frm文件，以及上一级目录ibdata1文件
myisam类型数据表对应三个文件 *.frm–表结构定义文件
*.myd—数据文件 *myi—索引文件（存在于programdata文件夹）
4、修改数据表（针对结构）
alter table 旧表名 rename as新表名 #修改表名
alter table表名 add 列名 列类型[属性] #添加字段
alter table 表名 modify 列名 列类型[属性] #修改字段
alter table表名 change 旧字段名 新字段名 列类型 [ 属性 ]
alter table表名 drop 字段名 #删除字段
5、删除数据表 drop table [if exists] 表名 #删除表
6、修改表的起始增长和增幅：
alter table表名 auto_increment=要起始的值； #从多少开始增长
set @@auto-_increment_increment =幅度 #增长的步幅
三、mysql数据库数据管理（dbms）
1、使用dml语句增删改表数据
（1）用insert命令添加数据
insert into 表名 (字段1，字段2，字段3….) values (“值1”，”值2”，”值3”….)，（”值1”，”值2”，”值3”….）；
（2）用update命令修改数据
update 表名 set 字段名1 =value1，字段2 =value2，……[where condition]；
column_name为更改的数据列
例如: update student set name=“张三”，hobby=“篮球”；
（3）where条件子句
> < = != and or between
（4）使用delete命令删除表数据，如有参数则只删除某行
delete from 表名 where condition ；
（5）使用truncate命令完全清空表数据
truncate table 表名;或者truncate 表名;
与delete的区别：都能删除数据，不删除表结构，但truncate速度更快，使用truncate会重置auto_increment计数器，不会对事务有影响
4、mysql的事务处理（只支持innodb和bdb数据表类型）
事务就是将一组sql语句放在同一批次内去执行，只要有一次语句出错，该批次内所有sql都将被取消执行
（1）事务的acid原则：
atomic 原子性 consist一致性
isolated隔离性 durable持久性
（2）mysql的事务实现方法
使用事务时应先关闭自动提交，最后再开启自动提交
set autocommit =1； 开启自动提交模式（默认）
set autocommit =0； 关闭自动提交模式
start transaction 开始一个事务，标记事务的起始点
commit 提交一个事务给数据库,事务的结束
rollback 事务回滚，数据回到本次事务的初始状态
三、使用dql命令查询数据
1、数据查询语言（dql）
（1）select语法:
select * | t.* | t.no,t.name… from table as t
[ left | out | inner join table_name2 ] #联合查询
[ where …..] #指定结果满足的条件
[ group by … ] #指定结果按照哪几个字段来分组
[ having …] #过滤分组的记录必须满足的次要条件
[ order by…] #指定查询记录按一个或多个条件排序
[ limit { [offset , ] row_count | row_count offset offset } ]; #指定查询的记录从哪条至哪条
（2）指定查询字段
例：select * from student ;
select studentno，studentname from student；
查询两表同名的字段: table.
select student.studentno， studentname from student ,result;
（3）as子句作为别名（可省略不写）
可给数据列，表，统计结果 取一个新别名
select studentno as “学号” from student；
select a.studentno from student as a；
select count(studentno) 学生总数 from student ；
（4）distinct关键字
(all关键字是默认的，返回所有记录)
去掉select查询的记录结果中重复的记录，只返回一条
select distinct r.subjectno from result r;
（5）在sql语句中使用表达式
表达式一般由文本值、列值、null、函数和操作符等组成
select version()，100*3 返回mysql版本和计算结果
sql返回结果中有特殊字符最好别名
2、mysql查询子句（where）
（1）逻辑操作符（最好用）
and或&& a and b 或 a&&b 逻辑与，同时为真则为真
or或|| a or b 或 a||b 逻辑或，一个为真则为真
xor或^ a xor b 逻辑异或，不同为真，相同为假
not 或！ not a 或 ！a 逻辑非，操作为假，结果则为真
（2）比较操作符
is null a is null 若操作符为null，则结果为真
is not null a is not null 若操作符不为null，则结果为真
between a between b and c 若a在b和c之间，则为真
like a like b 若a匹配b，则结果为真
in a in （a1,a2,a3…） a等于（a1）中的某一个则为真
注:
数值数据类型的记录之间才能进行算术运算
相同数据类型的数据之间才能进行比较
解决要查询到通配符使用escape
使用like进行模糊查询，与%一起用，表示匹配0个或多个字符
使用like进行模糊查询，与_一起用，表示匹配单个字符
（3）连接查询（多表查询）
等值与非等值查询：
要求：从subject和grade数据表查询课程名称和所属年级名称
#非等值连接查询，也称交叉连接
selectsubjectname,gradenamefrom subject, grade;
#等值查询相当于内连接查询select subjectname,gradename from subject,grade where subject.gradeid= grade.gradeid;
a、内连接（inner join）
select 字段1，字段2… from table_1 inner join table_2 on table_1.字段x = table_2.字段y；
# inner join 和join是相同的；如果table_1中的行在table_2中没有匹配，则不返回；
例：从subject和grade数据表查询课程名称和所属年级名称
select subjectname，gradename from subject inner join grade on subject.gradeid = grade.gradeid;
b、左外连接（left join）
从左表（table_1）中返回所有的记录，即便在右表（table_2）中没有匹配的行；
select 字段1，字段2，…from table_1 left join table_2 on table_1.字段x =table_2.字段y；
c、右外连接（right join）
从右表（table_2）中返回所有的记录，即便在左表（table_1）中没有匹配的行；
select 字段1，字段2，…from table_1 right join table_2 on table_1.字段x =table_2.字段y；
d、自连接
数据表与自身进行连接。（使用到内连接）
#要求：从一个包含栏目id,栏目名称和父栏目id的表中，查询父栏目名称和其子栏目名称。
（4）order by 排序
默认升序排列（asc），降序排序desc
（5）limit语法-常用于分页显示
limit m , n 或者limit n offset m
限制select返回结果的行数，m表示从m开始，n表示显示数目
（6）子查询
在查询语句中的where条件字句中，又嵌套另外一个查询语句
嵌套查询可由多个子查询组成，求解的方式是由里及外；子查询返回的结果一般都是集合，故而建议使用in 关键字
（7）统计函数
count（） 满足select条件的记录总和数
sum（） 求数字字段或表达式列的总和
avg（） 求数字字段或表达式列的平均数
max（） 求数字字段或表达式列的中的最大数
min（） 求数字字段或表达式列的中的最小数
（8）分组统计：使用group by 关键字，与having结合进行对数据筛选
四、mysql的索引与备份
1、索引
作用：提高查询速度使用分组和排序子句进行数据检索时，可以显著减少分组和排序的时间。
分类：主键索引（primary key）、唯一索引（unique）、常规索引（index/key）、全文索引（fulltext 只有myisam类型表可以用）
select * from test where match （全文索引） against（要索引的内容）
2、管理索引
（1）创建索引：1、创建表时添加；2、建表后追加
alter table 表名 add 索引类型 （数据列名）
（2）删除索引
drop index 索引名 on 表名
alter table 表名 drop index 索引名
alter table 表名 drop primary key
（3）查看索引
show index（或keys） from 表名
3、数据库的备份和恢复
mysql数据库备份方法
1、数据库管理工具，如sqlyog navicat
2、直接拷贝数据库文件和相关配置文件
3、利用mysqldump备份工具
1、dos命令，利用mysqldump备份工具
如：mysqldump -uroot -p密码 库名 表名1 表名2 >g:abc.sql
mysqldump –h 主机名 –u 用户名 – p [options] 数据库名[table1 table2 table 3] > path/filename.sql(预存文件目录)
2、mysql数据库的恢复
如：mysqldump -uroot -p密码 库名 表名1 表名2 <>< p=""><>
3、利用sql语句导出、导入数据
导出数据
select * into outfile ‘file_name’ from tb_name
导入数据：
load data infile ‘file_name’ into table tb_name(列名);
如对本文有疑问， 点击进行留言回复！！
我要评论
网友评论