MySQL笔记_计算机软件及应用_IT计算机_专业资料.docx

数据库概述二、MySQL安装和配置三、MySQL数据库基本操作1.数据库和数据库对象1）数据库是存储数据库对象的容器;2）数据対象是指存储、管理和使用数据的不同结构形式,主要包含表、视图、存储过程、 函数、触发器和时间等;3）数据库分为系统数据库和用户数据库两大类。2.数据库相关操作1）创建数据库;create database数据库名；2）显示数据库;show databases;3）选择数据库;use database数据库名;4）删除数据库;drop database数据库名；注:创建数据库时,数据库名不能与已存在的数据库名重复;数据库命名（标识符）规则如;令 由字母、数字、下划线、#和$符号组成,其中字母可以是英文字符az 或AZ,也可以是其他语言的字母字符；令首字母不能是数字和$符号；令 标识符不允许是MySQL的保留字;令不允许有空格或特殊字符;令长度小于128位。四、MySQL数据库存储引擎和数据类型1,存储引擎1）存储引擎制定了表的类型，即如何存储和索引数据、是否支持事务，也决定了表在计算机中的存储方式;2）查看MySQL数据库管理系统中支持的存储引擎:show engines；令Engine表示存储引擎名称:令Support表示MySQL数据库管理系统是否支持该存储引擎（YES表示支持,N0表 示不支持,值DEFAULT表示该存储引擎是数据库管理系统默认支持的存储引擎）；令Comment表示关于存储引擎的评论；令Transactions表示存储引擎是否支持事务（YES表示支持，N0表示不支持）；令XA表示存储引擎所支持的分布式是否符合XA规范（YES表示支持，N0表示不支 持）；令Sava points表示存储擎是否支持事务处理中的保存点（YES表示支持，N0表示 不支持）3） SQL语句中,可以用“、“g”和“'G”符号表示语句结束；（G显示结果更美观）4）查看MySQL数据库管理系统中支持的弓|擎:show variables like ava%,；令Variable一name表示存储引擎的名字:令Value表示MySQL数据库管理系统是否支持该存储弓I擎（YES表示支持，N0表示 不支持,值DISABLED表示支持但还没开启）；2 .操作存储引擎1） 查看默认存储引擎:show variables like ,storage_engine%,；令 设置like的关键字为“storage一engine%”，表示查询默认存储引擎；2）修改默认存储弓I擎:手动修改配置文件my.ini,修改mysqld组中的defaule-storage-engine 参数令 如果想使修改后的参数生效,需重新启动MySQL服务；3）选择存储引擎（参考PDF）3 .数据类型1）整数类型:1字节=8位Tinylnt： ! 字节-128 255Smalllnt: 2 字节-32768 65535Mediumlnt: 3 字节8388608 1677215Int 和 IntEger: 4 字节2147483648 4294967295Biglnt: 8 字节922337203685477580818446744073709551615注:查看整数类型的取值范围可以通过mySQL的系统帮助令Help contents;杳看MySQL帮助文档支持的目录列表； Help data types；令Help int漬看Int类型的帮助；2）浮点数类型3）定点数类型4）位类型5）日期和时间类型6）字符串类型五、表的操作1,表的概念1）表是包含数据库中所有数据的数据库对象,主要用来存储数据记录。注:表中的数据库对象包含列、索引和触发器: 列（Columns）：也称属性列,创建表时必须指定列的名字和数据类型; 索引（Indexes）;是指根据指定的数据库表列建立起来的顺序，提供了快速访问数 据的途径,可监督表的数据,使其索引所指向的列中的数据不重复:令 触发器（Triggers）:是指用户定义的事务命令的集合，当个表中的数据进行插入、 更新或删除时这组命令就uj以自动执行,可以用来确保数据的完全性和安全性。2,表的操作1）创建表：create table表名（属性名数据类型，属性名数据类型，属性名数据类型）： 令属性名:表示表字段的名称;数据类型：指定字段的数据类型; 表名命名规则与数据库命名规则致;令 表的具体内容定义在圆括号之内,各列用逗号隔开，最后一个属性不需要逗号。2）査看表结构:describe表名;3） 查看表详细定义:show create table表名;4）删除表:drop table表名;5） 修改表名:alter table旧表名rename新表名;6）增加字段:在表的最后个位置增加字段:alter table表名add属性名属性类型；在表的第一个位置增加字段:alter table表名add属性名属性类型first;在表的指定字段之后增加字段：avertable表名add属性名属性类型after属性名;7）删除字段:alter table表名drop属性名:8）修改字段:修改字段的数据类型:avertable表名modify属性名属性类型;修改字段的名字:alter table表名change旧属性名新属性名旧数据类型:同时修改字段的名字和属性:alterable表名change旧属性名新属性名新属性类型; 修改字段的顺序：界面操作略3,表的约束1） MySQL支持完整性约束，指数据的得准确性和一致性，根据约束数据列限制，分为单列 约束和多列约束：令单列约束:指每个约束只约束一列数据;令多列约束:指每个约束可以约束多列数据:2）设置非空约束（not null,NK）:约束字段的值不能为空create table 表名（属性名属性类型 not null,.）;3）设置字段的默认值（default） :createtable表名（属性名数据类型default默认值,.）;4）设置唯一约束（unique,UK）:约束字段的值是唯一,字段上的内容不允许重复create table表名（属性名属性类型unique,.）;设置UK约束名字:constraint uk一字段unique（字段名）；令约束名标识符推荐使用“约束缩写一字段名”：5）设置主键约束（primary key,PK）:约束字段为表的主键,可以记录该表记录的唯一标识, 便于快速找到表中的记录令 设置主键约束时,必须满足主键字段的值是唯一的、非空的:单字段主键设置:create table表名（属性名数据类型primary key,.）;设置PK约束名字：constraint uk一字段primary key（字段名）；多字段主键设置:create table表名（属性名数据类型primary key,. constraint约束名 primary key（属性名,属性名.）;6）设置字段值自动增加（auto 一 increment） ： create table表名（属性名数据类型 auto_ increment, ）令 表中只能有一个字段使用autojncrement约束,并且字段的数据类型必须是整数类 型：7）设置外键约束（foreing key,FK） :create table表名（属性名数据类型，属性名数据类 型 constraint外键约束名foreing key （属性名1） feferences表名（属性名2）令外键约束名：用来标识约束名:属性名1：子表中设置外键的字段名:属性名2：父表中设置主键约束的字段名:六、索引的操作1.索引的概念1）为了提高从表中检索数据的速度，与书目录类似。2）创建索引方式：自动索引和手动索引。令 自动索引:在数据库表里设置某个字段的完整性约束时,系统会自动创建该表字段 的索引:令手动索引:指手动在表上创建索引:3） 6种索引:普通索引、唯一索引、全文索引、単列索引、空间索引。令 普通索引:在表的个字段或多个字段上建立索引不附加任何限制条件（唯一、非 空等限制）：令唯索引：创建索引时，限制索引的值必须是唯的：令 全文索引：主要关联在数据类型为char、varchar,和text字段上,以便快速查询数据量较大的字符串类型的字段;0 多列索引:创建索引时,所关联的字段不是个字段,而是多个字段;2.索引的操作1）创建普通索引;可以创建在任何数据类型的字段上 创表时创建;create table表名（属性名数据类型,属性名数据类型index/key索引名（属性名!）长度ASC|DESC）检测是否创建成功;show create table表名、G检测表中索引是否启动;explain select*from表名field=lG令Index/key:指定字段为索引令索引名:指定所创建索引名令属性名1;指定索引所关联的字段名称长度;指定索引的长度令ASC：指定为升序排序令DESC:指定为降序排序己存在的表上创建:create index索引名on表名（属性名）长度ASC|DESC令On：指定所要创建索引的表名称通过SQL语句alter table创建:alter table表名add index/key索引名（属性名）长度 ASC|DESC2）创建唯一索引:创表时创建：create table表名（属性名数据类型，属性名数据类型， uniqueindex/key索引名（属性名1）长度ASC|DESC）已存在的表上创建：create unique index索引名on表名（属性名）长度ASC|DESC通过 SQL 语句 alter table 创建:alter table 表名 add unique index/key 索引名（属性名） 长度 ASC|DESC3）创建全文索引:创建在char、varchar,和text字段上创表时创建：create table表名（属性名数据类型，属性名数据类型，fulltext index/key 索引名（属性名 1）长度 ASCIDESC） engine=MylSAM已存在的表上创建：createfulltext index索引名on表名（属性名）长度ASC|DESC通过 SQL 语句 alter table 创建:alter table 表名 add fulltext index/key 索引名（属性名） 长度 ASC|DESC注:存储引擎为MylSAM的数据库表上才能创建全文索引4）创建多列索引创表时创建：create table表名（属性名数据类型，属性名数据类型, index/key索引名属性名1长度ASCIDESC, c已存在的表上创建：createindex索弓I名on表名（属性名长度ASC|DESC,属性名n长 度 ASC|DESC）通过SQL语句alter table创建:alter table表名add index/key索引名（属性名长度 ASC|DESC,属性名 n 长度 ASC|DESC）5）査看索引:检测是否创建成功：show create table表名G检测表中索引是否启动:explain select*from表名field=lG6）删除索引：drop index索引名on表名七、视图的操作1 .视图的概念1）视图是个虚拟表,内容由查询定义,包含一系列带有名称的列和行数据,但视图并不 在数据库中以存储的数据值集形式存在。使徒的功能实际上是封装査询语句。2）视图特点: 视图的列可以来自不同的表，是表的抽象和在逻辑意义上建立的新关系; 视图是由基本表（实表）产生的表（虚表）： 视图的建立和删除不影响基本表: 对视图内容的更新、添加、删除和修改，直接影响基本表: 当视图来自多个基本表时，不允许添加和删除数据:2I视图的操作1） 创建视图:create view view_name as 查询语句;查询视图:select*from view_name;将视图当作表来操作Drop view if exists '数据库视图名；Create view '数据库视图名as select字段1,字段2表名;2）创建各种形式的视图:封装实现查询常量语句的视图常量视图create view 视图名 as select 3.1415926;封装使用聚合函数（SUM、MIN、MAX、COUNT查询语句的视图）create view 视图名 as select count（name） from 表名;封装实现排序功能（order by）查询语句的视图create view 视图名 as select 字段 from 表名 order by id desc;封装实现表内连接查询语句的视图create view 视图名 as select s.name from 表名 !as s,表名 2 as g where s.表 2id=g.id and g.id=2;封装实现表外连接查询语句的视图create view 视图名 as select s.name from 表名 1 as sleft join 表名 2 as g on s.表 2id=g.id where g.id=2;封装实现子查询相关查询语句的视图create view 视图名 as select s.name from 表名 1 as swhere s表 2Jd in（select id from 表2）；封装实现记录联合（union和union all）查询语句的视图create view 视图名 as select idzname from 表名 lunion all select id,name from 表 2;3）查询视图查看视图名:show tables;查看给数据库里所有表名和视图名查看视图详细信息:show table status; 查看视图详细信息Show table status from数据库查看数据库里视图和表的详细信息Show table status from数据库like视图名查看数据库里具体视图和表的详细信息 查询视图定义信息:show create view视图名 查询视图设计信息:describe|desc视图名;通过系统表查看视图信息:select*from views where表名视图名G;系统数据库information_schema中包含视图信息表views4）删除视图Drop view视图名,视图名2视图名3.;可以删除个或多个5）修改视图方法一:Create or replace view视图名as查询语句;方法二:Alter view视图名as查询语句6）通过视图操作表数据：更安全简单检索/查询数据:select*from视图名;添加数据：insert into视图名（字段1,字段2,字段3）values（值1,值2,值3）;删除数据:delete 视图名 where name二'pearl"更新数据:update视图名set字段二字段值where name='pearl'、触发器的操作1 .触发器的概念2 .触发器的操作1)创建触发器一条执行语句的触发器:create trigger trigger_name before/after trigger_event on 表名 for each row trigger_stmt Trigger_name表示要创建的触发器的名字 Before/after指定触发器的执行时间(触发器之前之后执行触发器语句)令 Trigger_event触发器事件,即触发器执行条件(包含delete、insert、update 语句)令For each row表示任何一条记录上的操作满足触发事件都会触发该触发 器令Tiggejstmt激活触发器后被执行的语句例:create trigger 触发器名 before insert on 表 1 for each row insert into 表 2 values(null/ 表 l',now();Insert into 表 ! values(l/hehe'/haha');Select * from 表 2;多条执行语句的触发器:create trigger trigger_name before/after trigger_event on 表名 for each rowbegin trigger_stmtend例:deliniter $ create trigger 触发器名 after insert on 表:1 for each row begininsert into 表 2 values(null表 !'znow();insert into 表 2 values(null表 l*znow();end $Insert into 表 ! values(l/hehe7haha/)； Select * from 表 2;2)查看触发器Show triggersG；通过系统表 triggers 查看:select*from triggers where trigger_name触发器名G;3)删除触发器drop trigger触发器名;九、数据的操作1) 插入数据记录1)插入完整数据记录:insert into 表名(fieldl , field 2 , field 3 , field 4 , field n ) values(valuel/value2.value3/value4,valuen);insert into 表名 (valuelzvalue2/value3valuen);令field表示字段名: value表示所要插入的数值;数据值与所写字段的顺序一致: 省略字段参数插入数据时,数据值必须与字段顺序一致:插入部分数据记录:insert into 表名(fieldl> field 2, field 3, field 4,field n) values (valuel,value2.value3,value4,.valuen);令field表示部分字段名; value表示所要插入的部分数值;令插入部分数据时,插入数值的个数必须与插入字段的个数一致,顺序一致2) 插入多条完整数据记录:insert into 表名(fieldl, field 2, field 3, field 4field n)values (valuellzvaluel2.valuel3valuenl),(value21zvalue22.value23valuen2),(value31zvalue32.value33valuen3),(valuelmzvalue2mzvalue3mvaluenm);插入多条部分数据记录:insert into表名values(valuellzvaluel2.valuel3valuenl),(value21zvalue22.value23valuen2),(value31zvalue32.value33valuen3),(valuelmzvalue2mzvalue3mvaluenm);今 多条完整插入:字段顺序可以与表中的字段顺序不一致;多条部分插入:数值顺序必须与表中字段顺序一致;3) 插入查询结果:insert into 表名 ! (fieldll* field 12, field 1fieldin) select(value21,value22.value23valuen2n) from 表名 2 where2 .更新数据记录1)更新特定数据记录:update 表名 set fieldl=valuel,field2=value2,field3=value3,where condition Field表示所要更新数值的字段名:令Value表示更新后的数值;删除特定数据记录2)更新所有数据记录:update 表名 set fieldl=valuel/field2=value2/field3=value3/where condition;update 表名 setfield=value;3 .删除数据记录1) 删除特定数据记录:delete from表名where condition;2)删除所有数据记录:delete from表名where condition;delete from 表名;4 .单表数据记录查询音询数据记录操作包含:简单数据记录查询、条件数据记录查询、排序数据记录查 询、限制数据记录査询数量、统计函数和分组数据记录査询。(-)简单数据记录查询方式:简单数据查询避免市复数据査询实现数学四则运算数据查询设置显示格式数据査询1)查询所有字段数据:select fieldl,field2, field3,.fieldn from 表名;Select * from 表名;令字段顺序可以改变;令代表表中所有字段;2)查询指定字段数据：select fieldl,field2,field3,.from 表名;3)避免重复数据查询(distinct) : select distinct fieldl,field2,field3.fieldn from 表名 关健字distinct表示去除掉重复数据;4)实现数学四则运算数据查询：select field数字from表名;select fieldl数字 as otherfieldl,field2数字 as otherfield2 from 表名;令 为了显示直观,所有要用AS为字段设置新字段；令 MySQL支持的关系运算符;四则运算符(+加,减,乘,/除,求余)；5)设置显示格式数据査询:select concat(name字段名;学生的年龄为:負ge*5)新字段名from表名;令 MySQL中的函数concat()用来连接字符串;(二)条件数据记录査询:select fieldl,field2.fieldn from 表名 where condition;where关键字:对所查询的数据记录进行过滤:令Condition参数:对数据进行条件限制查询;<条件数据查询语句包含如下功能:带关系运算符和逻辑运算符的条件数据查询;带between and关键字的条件数据查询;带is nul!关键字的条件数据查询;带in关键字的条件数据查询;带like关键字的条件数据查询;1)带关系运算符和逻辑运算符的条件数据查询:单条件表达式数据查询:select name from 表名 where age=22;查询表中22岁的学生姓名Q MySQL支持的比较运算符：。大于,小于,=等于,！=不等于,>=大于等于,<=小于等于)令 MySQL支抒的逻辑运算符:(and-&&逻辑与,or| |逻辑或,xor逻辑异或，not-! 逻辑非)多条件表达式数据查询:select name from 表名 where job=,java,&&age>22;查询表中从事java并且年龄大于22的学生姓名2) 带 between and 关键字的范围查询:select fieldl,field2.fieldn from 表名 where field between valuel and value2;令Between and判断字段的数值足否在指定的范围内,这些关键字只针对数字类 型:符合范围的数据记录查询:select name from student where age between 20 and 30;查询表中年龄在20到30岁之间的学生不符合范围的数据记录查询:select name from student where age not between 20 and 30; 查询表中年龄不在20到30岁之间的学生3) 带 is null 关键字的空值查询：select fieldl,field2.fieldn from 表名 where field is not;空值数据记录查询:select name from表名where job is null;查询表中没有工作的学生不是空值数据记录查询：select name from表名where job is not null;查询表中有工作的学生通过非逻辑运算设置非空值条件查询：select name from表名where not job is null;通过非逻辑运算设置空值条件查诒J： select name from表名where not job is not null;4) 带 in 关键字的集合查询:select fieldl,field2.fieldn from 表名 where fieldin(valuel,value2,value3.valuen);在集合中数据记录查询：select name from表名where age in(22,35);查询年龄为22和35的学生通过或逻辑符查询在集合中数据的记录select name from表名where age=22 or age=35;不在集合中数据记录查询:select name from表名where age not in(22,35);查询年龄不是22和35的学生通过非逻辑符查询在集合中数据的记录select name from表名where not age in(22,35);集合查询注意点: 使用关键字in时,查询集合中如存在null,不会影响查询; 使用关键字not null,查询集合中存在null,不会有任何的查询结果;5) 带 like 关键字的模糊查询:select fieldl ,field2 .fieldn from 表名 where field like value;Like关键字支持的通配符:通配符,该通配符值能匹配单个字符:e %通配符,该通配符值可以匹配任意长度的字符串,既可以是0个字符,1个 字符，也可以是多个字符:带有通配符的模糊查询:select name from 表名 where name like't%'査询表中姓名是以t开头的学生;select name from 表名 where name not like't%'查询表中姓名不是以t开头的学生;通过逻辑非运算符not或!的模糊查询:select name from 表名 where not name like 'h%'查询姓名不以h开头的学生;带有一通配符的模糊查询:select name from 表名 where name like '一i%'查询第二个字母是i的姓名的学生；通过逻辑非运算符not或!的模糊查询:select name from 表名 where not name like i%'查询第二个字母不是i的姓名的学生;Like关键字模糊查询注意点:令Like关键字可以操作任何数据类型；< Like关键字匹配%时，表示查询所有数据记录;（三）排序数据记录查询;select fieldl,field2.fieldn from 表名 where condition orderby filed ml asc |desc,filedm2 asc|desc;令参数fieldm:表示按照该字段进行排序;令参数asc;表示按升序的顺序排序;令参数desc:表示按降序顺序排序;令 默认情况下按asc升序排序;令 在order by后设置多个不同字段进行排序:排序数据查询功能如:Q按照单字段排序;按照多字段排序;按照单字段升序排序:select * from表名order by sudentid ASC;表中字段studentid的值对该表所有的数据记录进行升序排序;按照单字段降序排序:select * from表名order by sudentid desc;表中字段studentid的值对该表所有的数据记录进行降序排序:按照多字段排序：select * from 表名 order by age asc,tel desc;査询结果先按字段age进行升序排序,值遇到相同的数据记录时,按字段tel的值进 行从大到小排序（四）限制数据记录査询数量:select fieldl,field2.fieldn from 表名 where conditionlimit offset_startzrow_count 关键字limit:限制数据查询结果数量,被应用在分页系统中,对于第一页数 据记录,通过不指定初始位置来实现,对于第二页及其他页则需指定初始位置 offset_start 来实现:参数offset_start:表示数据记录的起始偏移量;令参数row_count:表示显示的行数;令 根据是否指定初始位置(起始偏移量),分为不指定初始位置,指定初始位置:不指定初始位置:默认值为0,从第一天记录开始显示显示记录数小于查询结果:select*from表名where field is null limit 2;显示记录数大于査询结果:select*from表名where field is null limit 11;要求现实的查询结果数量为11,但查询结果总数10,所以会把所有查询结果显 示出来指定初始位置:默认值为0select*from 表名 where field is null order by fieldl limit 0,5;等同于 select*from 表名 where field is null order by fieldl limit 5;从第一条开始显示,显示5条记录并排序select*from 表名 where field is null order by fieldl limit 5, 5;从第六条开始显示,显示5条记录并排序(五)统计函数和分组记录查询统计函数查询数据语法:select function(field)from 表名 where condition;统计函数:经常与分组起使用 Count。函数:统计表中记录的条数;令Avg()函数：计算字段值的平均和:令Sum()函数:计算字段值的总和:令Max()函数：查询字段值的最大值; Miu()函数：查询字段值的最小值;1)统计数据记录条数统计函数Count。令CountO：不论表字段是null还是非null,都可以对表中记录进行统计；令Count(field):对特定记录的字段进行统计,统计时将忽略null值。例:select count(*) name from表名；利用函数对表中的学生人数进行统计 select count(field) name from表名;利用函数对表中的指定字段进行统计 select count(field) name from 表名 where not field=O; 不包含部分数据时需加条件2)统计计算平均值统计函数Avg。 Avg(field):计算指定字段的平均值,计算时忽略null值,忽略值为,需加条件 where not field=O;例:selectavg(field)新字段 from 表名;3)统计计算求和-统计函数Sum。令Sum(field):计算指定字段值之和,计算时会忽略null值,忽略值为0,需加条 件 where not field=0;例:selectsum(field)新字段 from 表名;统计计算最大值和最小值-Max(field)和Min(field)令 Max(field):计算指定字段中的最大值,统计时忽略null值; Min(field)：计算指定字段中的最小值,统计时忽略null值;例:select Max(field)新字段 l,Min(field)新字段 2 from 表名；统计函数注意点:当表中无数据记录时,函数count。返回数据0;其他函数返冋null.4)分组数据查询:select function。from 表名 where condition group by field;令分组时字段上的值一定要具有重复值简单分组査询:例:select*from表名group by field;对所有数据进行分组统计功能分组査询:函数group_concat实现显示每个分组中的指定字段Select group_concat(field) from 表名 where condition group by field;例：select fieldl,group_concat(field2)新字段 from 表名 group by fieldl;对表中字段进行分组,并显示出字段值的名和个数多个字段分组査询:Select group_concat(field) , fuction(field)from 表名 where condition group by fieldl,field2fieldn例：select fieldlzfield2 from 表名 group by fieldl,field2;多字段分组select fieldl,field2,group_concat(field3)新字段,count(field3) from 表名 group byfieldl,field2;多字段分组并显示每个分组中的名称和个数实现having子句限定分组査询:having关键字实现条件限制分组数据记录例:select fieldl,avg(field2)group_concat(field3)新字段,count(field3)新字段 from 表 名 group by fieldl having avg(field2)>2000;5.多表数据记录查询1)十、MySQL运算符MySQL常用函数十二、MySQL安全性机制1.系