MySQL数据库学习笔记.doc

1、MySQL 数据库学习笔记1共 13 页知通内部学习资料UFOE 整理打印共 13 页MySQL 数据库学习笔记关系数据库的查询语言 SQL (Structured Query Language)，是一种非常口语化、既易学又易懂的语法。此一语言几乎是每个资料库系统都必须提供的，用以表示关连式的操作，包含了资料的定义(DDL)以及资料的处理(DML)。SQL 原来拼成 SEQUEL，这语言的原型以“ 系统 R“的名字在 IBM 圣荷西实验室完成，经过 IBM 内部及其他的许多使用性及效率测试，其结果相当令人满意，并决定在系统 R 的技术基础发展出来 IBM 的产品。而且美国国家标准学会(ANSI

2、)及国际标准化组织(ISO)在 1987 遵循一个几乎是以 IBM SQL 为基础的标准关连式资料语言定义。 第一章、了解概念，并理解它一、资料定义 DDL(Data Definition Language) 资料定语言是指对资料的格式和形态下定义的语言，他是每个资料库要建立时候时首先要面对的，举凡资料分哪些表格关系、表格内的有什麽栏位主键、表格和表格之间互相参考的关系等等，都是在开始的时候所必须规划好的。 数据库第二章 MySQL 数据库数据类型 第一节、数据类型MySQL 数据库目前支持以下几种下列代码字母用于描述中：M 指出最大的显示尺寸。最大的显示尺寸长度为 255。 D 适用于浮点类

3、型。指出跟随在十进制小数点后的数字数量。最大可能值为 30，但不应大于 M-2。 方括号 (“ and “) 指定可选的类型修饰部份。 1、数值型( 包括整数与浮点数，浮点型支持科学表示法)浮点类型:numeric nju:merik decimal(5,2),数字位:5,小数位:2 numeric 等价于 decimal 并且是作为字符串存储的.而不是作为二进制浮点数.说明:salary DECIMAL(3,2)在这个例子中，3 (精度(precision) 代表重要的十进制数字的数目，2 (数据范围(scale) 代表在小数点后的数字位数。在这种情况下，因此，salary 列可以存储的值范

4、围是从 -99.99 到 99.99。(实际上 MySQL 在这个列中可以存储的数值可以一直到 999.99，因为它没有存储正数的符号)。 整型:integer类型 占用字节 从 到 TINYINT 1 -128 127 SMALLINT 2 -32768 32767 MEDIUMINT 3 -8388608 8388607 INT 4 -2147483648 2147483647 BIGINT 8 -9223372036854775808 9223372036854775807 2、字符串( 字符)类型3、日期和时间类型4、Null 类型 不同于例如数字类型的 0 或字符串的5、布尔类型 T

5、URE FALSE 标识符约定标识符 最大长度 允许的字符数据库 64 一个目录名中允许的任何字符,除了/, 和.表 64 一个目录名中允许的任何字符,除了/和.列 64 所有的字符 索引 64 所有的字符 加名 255 所有的字符定界符,反引号 ANSI SQL 中双引号为定界符MySQL 数据库学习笔记2共 13 页知通内部学习资料UFOE 整理打印共 13 页注意:在 MYSQL 中,数据库和表对应操作系统下的目录与文件,因而,操作系统的敏感性决定数据库和表命名的大小敏感,所以在 LINUX 下数据库与表名的大小写敏感.提示:USE ,HELP 是 Mysql 中为少数几个不需要终结符的

6、语句之一.用户变量表示为: var_name;set x=99; 等同于x := 99;系统变量用两个表示,它分为 2 种,全局系统变量和会话系统变量.下面详细说明各种类型MySQL 支持所有的 ANSI/ISO SQL92 数字类型.1、数值型一个整数可以在浮点语境中使用;它被解释为等值的浮点数;同样,一个浮点数可以被转化为整数,但损失精度,如 100.1-100,100.5-101;2.Mysql 支持 16 进制数值,在数字的语境中,它们表现类似于一个整数(64 位精度); 如 select 0xa+0;在字符串的语境中,它们表现类似于一个二进制的字符串,每一对 16 进制 ASCII

7、码被转为一个字符:select 0x787868 ; =select x787868;select hex(xxh);select 0xa+5;select true,true,flase,FALSE;其中浮点型用于域，表示方法解释一下，如：FLOAT(M,D) UNSIGNED ZEROFILL 如果 UNSIGNED 被指定，负值是不允许的。M 是显示宽度，D 是小数位数。并且如果负号“” 也占一个宽度。2、字符串类型字符串是多个字符组成的一个字符序列,由单引号,或双引号字符包围,但在 ANSI 模式中运行时只能用单引号,也就是说在 SQL SERVER 数据库 ,使用应该使用单引号,保证

8、兼容性应该使用单引号.字符串类型有 CHAR、VARCHAR 、BLOB 、TEXT、ENUM 和 SET ；ENUM 是一个字符串对象，其值通常选自一个允许值列表中，该列表在表创建时的列规格说明中被明确地列举。 每个枚举值均有一个索引值： 在列说明中列表值所允许的成员值被从 1 开始编号。 空字符串错误值的索引值为 0。这就意味着，你可以使用下面所示的 SELECT 语句找出被赋于无效 ENUM 值的记录行。 mysql SELECT * FROM tbl_name WHERE enum_col=0;NULL 值的索引值为 NULL。 总结：ENUM 用于互相排斥的值，而 SET 列可以从一

9、个值的列表中选择多个值。常用技巧:1.在命令提示符下:mysql -h -u root db_name ),=,is null ,is not null isnull(express)如果 expr 大于或等于 min,并且 expr 小于或等于max,between 返回 1,否则返回 0.它等价于表达式(min0 以及 expr1 select nullif(1,1);- nullmysql select nullif(1,2)- 1D.mysql 字符串函数返回字符串 str 最左边的那个字符的 ASCII 码值.mysql select ascii(2);- 50返回 N 的字符串表示

10、的 2 进制值形式.mysql select bin(12);- 1100返回 N 的字符串表示的八进制值形式mysql select oct(12);- 14MySQL 数据库学习笔记4共 13 页知通内部学习资料UFOE 整理打印共 13 页如果 x 是一个数字,返回 n 的字符串表示的 16 进制值形式,这里 n 是一个长长的 (bigint)数字.如果 x 是一个字符串,x 中的每个字符均被转换为 2 位16 进制数字,并以 16 进制的字符串形式返回.这是 0xff 形式的字符串反转操作.mysql select hex(255);- ffmysql select hex(abc);

11、 /select ascii(a); - 97 select hex(97); - 61- 616263mysql 0x616263;- abcchar()以整数类型解释参数,返回这个整数所代表的Ascii 码值给出的字符组成的字符串,null 值将被忽略mysql select char(120,120,104);- xxhconcat(str1,str2,.)将参数连接成字符串返回,如果有任何一个参数为 null,返回值也为 null.locate(子串 substr,母串 str)返回子串 substr 在字符吕中第一次出现的位置.如果子串 substr 在 str 中不存在,返回值为

12、0:ltrim(str)返回移除了领头的空格字符后的 str;第 08 章 MySQL 数据操纵语言 DMLselect 语法select 列 1,列 2,列 3,from 表 1,表 2,表 3where 条件group by .having 第二节 域/列类型属性域/列的类型属性，有以下特性： 其中可以存放什么类型的值。 值要占据多少空间，以及该值是否是定长的(所有值占相同数量的空间)或(所占空间量依赖于所存储的值)。 该类型的值怎样比较和存储。 此类型是否允许 NULL 值。 此类型是否可以索引。 数字日期和时间字符串(字符)列类型的属性有以下四种：DefaultNot Null 或 N

13、ullPrimary Key 即主键Auto_increment (只有 1 个只能被索引)一、数据库的操作操作系统下的操作mysql -h -u -pmysql -h localhost -uroot 创建数据库删除数据库显示数据库使用数据库数据库其他相关操作1、创建数据库CREATE DATABASE db_Name;说明：MySQL 数据库学习笔记5共 13 页知通内部学习资料UFOE 整理打印共 13 页2、删除数据库DROP DATABASE db_Name;说明：可以简写为:drop db_name;3、显示数据库SHOW databases;说明：显示当前有哪些数据库。4、使用数

14、据库USE database;说明：5、显示数据库中有哪些表首先 USE database;然后 SHOW tables;参见：show 的用法二、表格、索引的操作创建表格更改表格建立索引删除表格、索引表格其他相关操作以下操作都是以表格 table_name 或索引为操作单位。DESCRIBE 或 DESC 命令除外。文件 用途 tbl_name.frm 表定义 (form) 文件 tbl_name.MYD 数据文件 tbl_name.MYI 索引文件 1、创建表格： CREATE TABLE table_name( column1 DATATYPE NOT NULL NOT NULL PRI

15、MARY KEY, column2 DATATYPE NOT NULL, .)说明： DATATYPE -是资料的格式，也就是字段的数据类型Not Null -不允许资料有空的PRIMARY KEY -是本表的主键。 可以通过一个表的内容来拷贝一个表，如CREATE TEMPORARY TABLE new_tbl_name SELECT * FROM old_tbl_name;CREATE TEMPORARY TABLE new_tbl_name SELECT * FROM old_tbl_name where 1=0; (只是拷贝空表)2、更改表格 ALTER TABLE tbl_name

16、ADD COLUMN col_name DATATYPE 说明：增加一个栏位即(字段)( 没有删除某个栏位的语法)。 ALTER TABLE tbl_name RENAME AS new_name;说明:对表重命名。ALTER TABLE table_name drop column_name说明：删除指定的列名。ALTER TABLE tbl_name CHANGE old_column_name new_column_name DATATYPE 说明：更改列名及列属性。 例：ALTER TABLE tbl_name CHANGE old_column_name new_column_nam

17、e int(2) not null primary key ; ALTER TABLE table_name ADD PRIMARY KEY (column_name) 说明：更改表得的定义把某个栏位设为主键。 ALTER TABLE table_name DROP PRIMARY KEY (column_name) 说明：把主键的定义删除。 MySQL 数据库学习笔记6共 13 页知通内部学习资料UFOE 整理打印共 13 页ALTER TABLE my_tbl TYPE=ISAM 或MYISAM,HELP,InnoDB 等等 说明：更改表的类型3、建立索引 CREATE INDEX ind

18、ex_name ON table_name (column_name) ;CREATE UNIQUE index_name ON table_name (column_name) ;ALTER TABLE tbl_name ADD INDEX index_name(column_list);ALTER TABLE tbl_name ADD UNIQUE index_name(column_list);ALTER TABLE tbl_name ADD PRIMARY KEY (column_list);说明：对某个表格的栏位建立索引以增加查询时的速度。 4、删除表格、索引 DROP tbl_na

19、me1,tbl_name2,tbl_name3; DROP INDEX index_name ON tbl_name;ALTER TABLE tbl_name DROP INDEX index_name;ALTER TABLE tbl_name DROP Primary Key;5、显示表格中列的信息语法：DESCRIBE | DESC tbl_name col_name | wildDESCRIBE tbl_name;说明：DESCRIBE 是 SHOW COLUMNS FROM 的缩写。用于显示表格中列的信息。DESCRIBE tbl_name; 等于 SHOW COLUMNS FROM

20、tbl_name; 也可以简写为:desc tal_name;describe vt. 描写, 记述, 形容, 形容 三、记录的操作记录的添加记录的更改记录的删除首先来大概了解一下 INSERT 的语法，有以下几种方式：INSERT INTO tbl_name (col_name,.) VALUES (expression | DEFAULT),.),(.),. ON DUPLICATE KEY UPDATE col_name=expression, . 或者 INSERT INTO tbl_name (col_name,.) SELECT .或者 INSERT INTO tbl_name S

21、ET col_name=(expression | DEFAULT), . ON DUPLICATE KEY UPDATE col_name=expression, . 说明：我们只关心记录添加的方式啦。注意啦，现在我们开始啦。1、记录的添加最简单的一种：直接添加一行记录就是：INSERT INTO tbl_name (col_name,.) VALUES (expression | DEFAULT),.),(.),.);例:INSERT INTO tbl_name (col1,col2) VALUES(15,col1*2); insert tbl_name (col1,col2) value

22、s (007,xxh5fd)第二种：2、记录的更改还是先来看一看 UPDATE 的语法UPDATE tbl_name , tbl_name . SET MySQL 数据库学习笔记7共 13 页知通内部学习资料UFOE 整理打印共 13 页col_name1=expr1 , col_name2=expr2 .WHERE where_definition说明：UPDATE 以新的值更新现存表中行的列。SET 子句指出要修改哪个列和他们应该给定的值。WHERE 子句如果被给出，指定哪个记录行应该被更新。否则，所有的记录行被更新。如果 ORDER BY 子句被指定，记录行将被以指定的次序更新。 例子：

23、UPDATE ybc set id=8 where id=0;3.记录的删除简单的语法:DELETE FROM table_name WHERE conditions 说明：删除符合条件的资料。 数据语法:SHOW 语句可用来获取数据库和表的几个方面的信息，它有如下用法：SHOW DATABASES 列出服务器上的数据库SHOW TABLES 列出当前数据库中的表SHOW TABLESFROM db_name 列出指定数据库中的表SHOW FULL COLUMNS FROM tbl_name 显示指定表中列的信息,等同于 desc tbl_name;SHOW INDEX FROM tbl_na

24、me 显示指定表中索引的信息SHOW TABLE STATUS 显示缺省数据库中表的说明信息SHOW TABLE STATUS FROM db_name 显示指定数据库中表的说明信息DESCRIBE tbl_name 和 EXPLAIN tbl_name 语句与 SHOW COLUMNS FROM tbl_name 功能相同。SELECT 的使用及语法详解作用说明:SELECT 用于检索从一个或多个表中选取出的行。select_expression 表示你希望检索的列。 SELECT 也可以被用于检索没有引用任何表的计算列。要讲的内容是：第一个查询选择特定的列，对列进行排序查找不重复的数据创建

25、虚拟列，及列的别名的使用按条件检索好，现在开始我们的第一个查询啦！SELECT * From tbl_name;组合查询 组合查询是指所查询得资料来源并不只有单一的表格，而是联合一个以上的 表格才能够得到结果的。 SELECT * FROM table1,table2 WHERE table1.colum1=table2.column1 说明： 1.查询两个表格中其中 column1 值相同的资料。 2.当然两个表格相互比较的栏位，其资料形态必须相同。 3.一个复杂的查询其动用到的表格可能会很多个。 整合性的查询： SELECT COUNT (*) MySQL 数据库学习笔记8共 13 页知通

26、内部学习资料UFOE 整理打印共 13 页FROM table_name WHERE column_name = xxx 说明： 查询符合条件的资料共有几笔。 SELECT SUM(column1) FROM table_name 说明： 1.计算出总和，所选的栏位必须是可数的数字形态。 2.除此以外还有 AVG() 是计算平均、MAX() 、MIN()计算最大最小值的整合性查询。 SELECT column1,AVG(column2) FROM table_name GROUP BY column1 HAVING AVG(column2) xxx 说明： 1.GROUP BY: 以 colu

27、mn1 为一组计算 column2 的平均值必须和 AVG、SUM 等整合性查询的关键字 一起使用。 2.HAVING : 必须和 GROUP BY 一起使用作为整合性的限制。 复合性的查询 SELECT * FROM table_name1 WHERE EXISTS ( SELECT * FROM table_name2 WHERE conditions ) 说明： 1.WHERE 的 conditions 可以是另外一个的 query。 2.EXISTS 在此是指存在与否。 SELECT * FROM table_name1 WHERE column1 IN ( SELECT column

28、1 FROM table_name2 WHERE conditions ) 说明： 1. IN 後面接的是一个集合，表示 column1 存在集合里面。 2. SELECT 出来的资料形态必须符合 column1。 其他查询 SELECT * FROM table_name1 WHERE column1 LIKE x% ;说明：LIKE 必须和後面的x% 相呼应表示以 x 为开头的字串。 SELECT * FROM table_name1 WHERE column1 IN (xxx,yyy,) 说明：IN 後面接的是一个集合，表示 column1 存在集合里面。 SELECT * FROM t

29、able_name1 WHERE column1 BETWEEN xx AND yy 说明：BETWEEN 表示 column1 的值介於 xx 和 yy 之间。 其它重要概念：SQL 允许你创建一个虚拟列或对已有的列进组合和修改后产生的派生列。SQL 允许可以创建一个新的虚拟列，而且还可以对它安自己的需要进行重命名，你可以按语法列名 别名来对任何一个列进行重命名(注意在列名与别名之间有空格)或用()括起来。MySQL 数据库学习笔记9共 13 页知通内部学习资料UFOE 整理打印共 13 页词语解释：numeric nju:merik adj.(=mumerical)数字的, 数值的deci

30、mal desimEl adj. 以十作基础的, 十进位的SQL 查询语句精华使用简要一、 简单查询 简单的 Transact-SQL 查询只包括选择列表、FROM 子句和 WHERE 子句。它们分别说明所查询列、查询的表或视图、以及搜索条件等。例如，下面的语句查询 testtable 表中姓名为“ 张三“的 nickname 字段和 email 字段。SELECT nickname,emailFROM testtableWHERE name=张三 (一) 选择列表选择列表(select_list) 指出所查询列，它可以是一组列名列表、星号、表达式、变量(包括局部变量和全局变量)等构成。1 、

31、选择所有列例如，下面语句显示 testtable 表中所有列的数据：SELECT *FROM testtable 2 、选择部分列并指定它们的显示次序查询结果集合中数据的排列顺序与选择列表中所指定的列名排列顺序相同。例如：SELECT nickname,emailFROM testtable 3 、更改列标题在选择列表中，可重新指定列标题。定义格式为：列标题=列名列名 列标题如果指定的列标题不是标准的标识符格式时，应使用引号定界符，例如，下列语句使用汉字显示列标题：SELECT 昵称=nickname,电子邮件=emailFROM testtable 4 、删除重复行SELECT 语句中使用

32、ALL 或 DISTINCT 选项来显示表中符合条件的所有行或删除其中重复的数据行，默认为 ALL。使用 DISTINCT 选项时，对于所有重复的数据行在 SELECT 返回的结果集合中只保留一行。5 、限制返回的行数使用 TOP n PERCENT选项限制返回的数据行数，TOP n 说明返回 n 行，而 TOP n PERCENT 时，说明 n 是表示一百分数，指定返回的行数等于总行数的百分之几。例如：SELECT TOP 2 *FROM testtableSELECT TOP 20 PERCENT *FROM testtable(二)FROM 子句 FROM 子句指定 SELECT 语句查

33、询及与查询相关的表或视图。在 FROM 子句中最多可指定 256 个表或视图，它们之间用逗号分隔。在 FROM 子句同时指定多个表或视图时，如果选择列表中存在同名列，这时应使用对象名限定这些列所属的表或视图。例如在 usertable 和 citytable 表中MySQL 数据库 学习笔记10 共 13 页知通内部学习资料UFOE 整理打印共 13 页同时存在 cityid 列，在查询两个表中的 cityid 时应使用下面语句格式加以限定：SELECT username,citytable.cityidFROM usertable,citytableWHERE usertable.cityi

34、d=citytable.cityid 在 FROM 子句中可用以下两种格式为表或视图指定别名：表名 as 别名表名 别名(二) FROM 子句FROM 子句指定 SELECT 语句查询及与查询相关的表或视图。在 FROM 子句中最多可指定 256 个表或视图，它们之间用逗号分隔。在 FROM 子句同时指定多个表或视图时，如果选择列表中存在同名列，这时应使用对象名限定这些列所属的表或视图。例如在 usertable 和 citytable 表中同时存在 cityid 列，在查询两个表中的 cityid 时应使用下面语句格式加以限定：SELECT username,citytable.cityid

35、FROM usertable,citytableWHERE usertable.cityid=citytable.cityid 在 FROM 子句中可用以下两种格式为表或视图指定别名：表名 as 别名表名 别名例如上面语句可用表的别名格式表示为：SELECT username,b.cityidFROM usertable a,citytable bWHERE a.cityid=b.cityid SELECT 不仅能从表或视图中检索数据，它还能够从其它查询语句所返回的结果集合中查询数据。例如：SELECT a.au_fname+a.au_lnameFROM authors a,titleauth

36、or ta(SELECT title_id,titleFROM titlesWHERE ytd_sales10000) AS tWHERE a.au_id=ta.au_idAND ta.title_id=t.title_id 此例中，将 SELECT 返回的结果集合给予一别名t，然后再从中检索数据。(三) 使用 WHERE 子句设置查询条件WHERE 子句设置查询条件，过滤掉不需要的数据行。例如下面语句查询年龄大于 20 的数据：SELECT *FROM usertableWHERE age20 WHERE 子句可包括各种条件运算符：比较运算符(大小比较)：、 =、 =、!、!=10 AND

37、age、=、 、!。3 、自然连接：在连接条件中使用等于(=)运算符比较被连接列的列值，但它使用选择列表指出查询结果集合中所包括的列，并删除连接表中的重复列。MySQL 数据库 学习笔记13 共 13 页知通内部学习资料UFOE 整理打印共 13 页例，下面使用等值连接列出 authors 和publishers 表中位于同一城市的作者和出版社：SELECT *FROM authors AS a INNER JOIN publishers AS pON a.city=p.city又如使用自然连接，在选择列表中删除 authors 和 publishers 表中重复列 (city 和 state

38、)：SELECT a.*,p.pub_id,p.pub_name,p.countryFROM authors AS a INNER JOIN publishers AS pON a.city=p.city (二)外连接内连接时，返回查询结果集合中的仅是符合查询条件( WHERE 搜索条件或 HAVING 条件)和连接条件的行。而采用外连接时，它返回到查询结果集合中的不仅包含符合连接条件的行，而且还包括左表(左外连接时)、右表( 右外连接时) 或两个边接表( 全外连接)中的所有数据行。如下面使用左外连接将论坛内容和作者信息连接起来：SELECT a.*,b.* FROM luntan LEFT

39、JOIN usertable as bON a.username=b.username 下面使用全外连接将 city 表中的所有作者以及user 表中的所有作者，以及他们所在的城市：SELECT a.*,b.*FROM city as a FULL OUTER JOIN user as bON a.username=b.username (三)交叉连接交叉连接不带 WHERE 子句，它返回被连接的两个表所有数据行的笛卡尔积，返回到结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。例，titles 表中有 6 类图书，而 publishers 表中有 8 家出版社，则下列交叉连接检索到的记录数将等于 6*8=48 行。SELECT type,pub_nameFROM titles CROSS JOIN publishersORDER BY type