MySQL数据库学习笔记

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

2、连式资料语言定义。第一章、了解概念，并理解它一、资料定义DDL(Data Definition Language)资料定语言是指对资料的格式和形态下定义的语言，他是每个资料库要建立时候时首先要面对的，举凡资料分哪些表格关系、 表格内的有什麽栏位主键、表格和表格之间互相参考的关系等等，都是在开始的时候所必须规划好的。数据库第二章MySQL 数据库数据类型第一节、数据类型MySQL 数据库目前支持以下几种下列代码字母用于描述中：M 指出最大的显示尺寸。最大的显示尺寸长度为255 。D 适用于浮点类型。 指出跟随在十进制小数点后的数字数量。最大可能值为 30 ，但不应大于 M-2 。方括号 ( an

3、d ) 指定可选的类型修饰部份。1、数值型 ( 包括整数与浮点数， 浮点型支持科学表示法)浮点类型 :numeric nju:merik decimal(5,2),数字位 :5, 小数位 :2numeric等价于decimal并且是作为字符串存储的.而不是作为二进制浮点数.说明 :salary DECIMAL(3,2)在这个例子中，3 ( 精度 (precision)代表重要的十进制数字的数目， 2 (数据范围 (scale)代表在小数点后的数字位数。在这种情况下，因此，salary列可以存储的值范围是从-99.99到 99.99 。(实际上MySQL在这个列中可以存储的数值可以一直到999.

4、99 ，因为它没有存储正数的符号) 。整型 :integer类型占用字节从到TINYINT 1-128127SMALLINT 2-3276832767MEDIUMINT 3-83886088388607INT4-21474836482147483647BIGINT 8-9223372036854775808 92233720368547758072 、字符串 (字符 ) 类型3 、日期和时间类型4 、Null 类型不同于例如数字类型的0 或字符串的5 、布尔类型TURE FALSE标识符约定标识符最大长度 允许的字符数据库64 一个目录名中允许的任何字符 ,除了和.表64一个目录名中允许的任何

5、字符,除了 /和 .列64所有的字符索引 64所有的字符加名 255所有的字符定界符 , 反引号 ANSI SQL 中双引号为定界符1 开始编注意 :在 MYSQL 中,数据库和表对应操作系统下的目录与文件 ,因而 ,操作系统的敏感性决定数据库和表命名的大小敏感 ,所以在 LINUX 下数据库与表名的大小写敏感.提示 :USE ,HELP是 Mysql 中为少数几个不需要终结符的语句之一 .用户变量表示为: var_name;set x=99;等同于 x := 99;系统变量用两个 表示 ,它分为 2 种,全局系统变量和会话系统变量 .下面详细说明各种类型MySQL 支持所有的ANSI/ISO

6、 SQL92数字类型 .1、数值型一个整数可以在浮点语境中使用;它被解释为等值的浮点数 ;同样 ,一个浮点数可以被转化为整数,但损失精度 ,如 100.1-100,100.5-101;2.Mysql 支持 16 进制数值 ,在数字的语境中,它们表现类似于一个整数 (64 位精度 );如 select 0xa+0;在字符串的语境中,它们表现类似于一个二进制的字符串 ,每一对 16 进制 ASCII 码被转为一个字符:select 0x787868 ; =select x787868;select hex(xxh);select 0xa+5;select true,true,flase,FALSE

7、;其中浮点型用于域，表示方法解释一下，如：FLOAT(M,D) UNSIGNED ZEROFILL如果 UNSIGNED被指定，负值是不允许的。 M 是显示宽度， D 是小数位数。 并且如果负号 “”也占一个宽度。2、字符串类型字符串是多个字符组成的一个字符序列,由单引号 ,或双引号字符包围,但在 ANSI 模式中运行时只能用单引号 ,也就是说在SQL SERVER数据库 ,使用应该使用单引号 , 保证兼容性应该使用单引号.字符串类型有CHAR 、VARCHAR 、BLOB 、TEXT 、ENUM和 SET；ENUM 是一个字符串 对象，其值通常选自一个允许值列表中， 该列表在表创建时的列规格

8、说明中被明确地列举。每个枚举值均有一个索引值：在列说明中列表值所允许的成员值被从号。空字符串错误值的索引值为 0。这就意味着， 你可以使用下面所示的 SELECT 语句找出被赋于无效ENUM值的记录行。mysql SELECT * FROM tbl_name WHEREenum_col=0;NULL值的索引值为NULL 。总结： ENUM 用于互相排斥的值，而 SET 列可以从一个值的列表中选择多个值。常用技巧 :1. 在命令提示符下 :mysql -h -u root db_name x.sql在串的环境中， 可用十六进制常数来指定串值。 其语法与前面描述的数值值相同， 但是每对十六进制的数

9、字都被看作 ASCII 代码并转换为字符， 其结果用于串。例如， 0x616263 作为串时为 “a b c。”在一个字符串中，如果某个序列具有特殊的含义，每个序列以反斜线符号 ( “”)开头，称为转义字符。MySQL识别下列转义字符：0一个 ASCII 0 (NUL) 字符。一个 ASCII 39单引号 (“ ”字符)。一个 ASCII 34双引号 (“”字符)。b一个 ASCII 8退格符。n一个 ASCII 10换行符。r一个 ASCII 13回车符。t 一个ASCII 9制表符 (TAB) 。z ASCII(26) (Control-Z)。这个字符可以处理在Windows系统中ASCI

10、I(26)代表一个文件的结束的问题。 (当使用mysql database filename时ASCII(26)可能会引起问题产生。) 一个ASCII 92反斜线( “” )字符。% 一个ASCII 37“ %”字符。它用于在正文中搜索“ %”的文字实例，否则这里“%”将解释为一个通配符。查看章节 6.3.2.1字符串比较函数。_ 一个 ASCII 95 “ _字”符。它用于在正文中搜索 “ _” 的文字实例，否则这里 “_将”解释为一个通配符。查看章节 6.3.2.1字符串比较函数.3、日期和时间类型4、 Null 类型Null 是 ANSI 中值的空。查找 NUll 类型用 IS Null

11、 和 IS Not Null 测试；并且不能对 NULL 类型进行算术运算。其结果为 NULL 。5.mysql 的注释为两种 :/*/ 和#( 单行注释 , 类似 UNIX 的脚本注释 ;)第 07 章 MySQL 函数mysql运算符A.比较运算符 =,!=(),=,=Null 安全等于比较 is null ,is not null isnull(express)如果 expr 大于或等于min, 并且 expr 小于或等于max,between返回 1,否则返回 0. 它等价于表达式(min=expr and expr=max)expr in (v1,v2,.);如果 expr 是 in

12、 列表中的一值 ,它将返回 1,否则返回0.B.逻辑运算符在 mysql 中, 所有的逻辑运算符返回的值均为true,false, 或 null( 未知 ), 在 mysql 中 ,它们由1(true),0(false)和 null 实现 .select !1等同于select not 1;C. 流程控制函数ifnull(expr1,value) 当 expr1 的值是 null 时,返回 value, 不是 null 时 ,返回 expr1case value when compare-value then result .set a=2;select case awhen 1 then o

13、newhen 2 then twoelse more end;if (expr1,expre2,expr3)如果 expr1 为真 ,(expr10以及 expr1null), 那么 if()返回 expr2, 否则返回expr3nullif(expr1,expr2)如果 expr1=expr2为真 ,返回 null,否则返回expr1;mysql select nullif(1,1);- nullmysql select nullif(1,2)- 1D.mysql字符串函数返回字符串str 最左边的那个字符的ASCII 码值 .mysql select ascii(2);- 50返回 N 的

14、字符串表示的2 进制值形式 .mysql select bin(12);- 1100返回 N 的字符串表示的八进制值形式mysql select oct(12);- 14如果 x 是一个数字 , 返回 n 的字符串表示的16 进制值形式 ,这里 n 是一个长长的 (bigint) 数字 .如果 x 是一个字符串,x 中的每个字符均被转换为2 位16 进制数字 , 并以 16进制的字符串形式返回 .这是 0xff 形式的字符串反转操作.mysql select hex(255);- ffmysql select hex(abc); /select ascii(a); - 97select hex

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

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

17、ment (只有 1 个只能被索引 )一、数据库的操作操作系统下的操作mysql -h -u -pmysql -h localhost -uroot创建数据库删除数据库显示数据库使用数据库数据库其他相关操作1 、创建数据库CREATE DATABASE db_Name;说明：2 、删除数据库DROP DATABASE db_Name;说明：可以简写为:drop db_name;3、显示数据库SHOW databases;说明：显示当前有哪些数据库。4、使用数据库USE database;说明：5、显示数据库中有哪些表首先 USE database;然后 SHOW tables;参见： show

18、 的用法二、表格、索引的操作创建表格更改表格建立索引删除表格、索引表格其他相关操作以下操作都是以表格table_name或索引为操作单位。DESCRIBE或 DESC 命令除外。文件用途tbl_name.frm表定义(form)文件tbl_name.MYD数据文件tbl_name.MYI索引文件1、创建表格：CREATE TABLE table_name(column1 DATATYPE NOT NULL NOT NULLPRIMARY KEY,column2 DATATYPE NOT NULL,.)说明：DATATYPE - 是资料的格式，也就是字段的数据类型Not Null - 不允许资料

19、有空的PRIMARY KEY -是本表的主键。可以通过一个表的内容来拷贝一个表，如CREATETEMPORARY TABLEnew_tbl_nameSELECT * FROM old_tbl_name;CREATETEMPORARY TABLEnew_tbl_nameSELECT * FROM old_tbl_name where 1=0;(只是拷贝空表 )2 、更改表格ALTER TABLE tbl_name ADD COLUMN col_nameDATATYPE说明：增加一个栏位即( 字段 )(没有删除某个栏位的语法 )。ALTER TABLE tbl_name RENAME AS new

20、_name;说明 : 对表重命名。ALTER TABLE table_name drop column_name说明：删除指定的列名。ALTER TABLE tbl_name CHANGEold_column_name new_column_name DATATYPE说明：更改列名及列属性。例： ALTER TABLE tbl_name CHANGEold_column_name new_column_name int(2) not nullprimary key ;ALTER TABLE table_name ADD PRIMARY KEY(column_name)说明：更改表得的定义把某个

21、栏位设为主键。ALTER TABLE table_name DROP PRIMARY KEY(column_name)说明：把主键的定义删除。ALTER TABLE my_tbl TYPE=ISAM或MYISAM,HELP,InnoDB等等 说明：更改表的类型3、建立索引CREATE INDEX index_name ON table_name(column_name) ;CREATE UNIQUE index_name ON table_name(column_name) ;ALTER TABLE tbl_name ADD INDEXindex_name(column_list);ALTER

22、 TABLE tbl_name ADD UNIQUEindex_name(column_list);ALTER TABLE tbl_name ADD PRIMARY KEY(column_list);说明：对某个表格的栏位建立索引以增加查询时的速度。4、删除表格、索引DROP tbl_name1,tbl_name2,tbl_name3.;DROP INDEX index_name ON tbl_name;ALTER TABLE tbl_nameDROP INDEXindex_name;ALTER TABLE tbl_nameDROP Primary Key;5、显示表格中列的信息语法：DESC

23、RIBE | DESC tbl_name col_name | wildDESCRIBE tbl_name;说明： DESCRIBE是 SHOW COLUMNS FROM的缩写。用于显示表格中列的信息。DESCRIBE tbl_name;等于 SHOW COLUMNSFROM tbl_name;也可以简写为 :desc tal_name;describe vt.描写 , 记述 , 形容 , 形容三、记录的操作记录的添加记录的更改记录的删除首先来大概了解一下INSERT的语法，有以下几种方式：INSERTINTO tbl_name(col_name,.)VALUES (expression |D

24、EFAULT),.),(.),. ON DUPLICATE KEY UPDATEcol_name=expression, . 或者INSERT INTO tbl_name(col_name,.)SELECT .或者INSERT INTO tbl_nameSETcol_name=(expression | DEFAULT), . ON DUPLICATE KEY UPDATEcol_name=expression, . 说明：我们只关心记录添加的方式啦。注意啦， 现在我们开始啦。1 、记录的添加最简单的一种：直接添加一行记录就是：INSERTINTO tbl_name(col_name,.)VA

25、LUES (expression |DEFAULT),.),(.),.);例 :INSERT INTO tbl_name (col1,col2)VALUES(15,col1*2); insert tbl_name (col1,col2)values (007,xxh5fd)第二种：2 、记录的更改还是先来看一看UPDATE的语法UPDATE tbl_name , tbl_name .SETcol_name1=expr1 , col_name2=expr2 .WHERE where_definition说明： UPDATE以新的值更新现存表中行的列。SET子句指出要修改哪个列和他们应该给定的值。

26、WHERE子句如果被给出，指定哪个记录行应该被更新。否则，所有的记录行被更新。如果ORDER BY子句被指定，记录行将被以指定的次序更新。例子： 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 COLU

27、MNS FROM tbl_name显示指定表中列的信息,等同于 desc tbl_name;SHOW INDEX FROM tbl_name显示指定表中索引的信息SHOW TABLE STATUS显示缺省数据库中表的说明信息SHOW TABLE STATUS FROM db_name显示指定数据库中表的说明信息DESCRIBE tbl_name和 EXPLAIN tbl_name语句与SHOW COLUMNS FROM tbl_name功能相同。SELECT的使用及语法详解作用说明 :SELECT用于检索从一个或多个表中选取出的行。 select_expression表示你希望检索的列。SEL

28、ECT也可以被用于检索没有引用任何表的计算列。要讲的内容是：第一个查询选择特定的列，对列进行排序查找不重复的数据创建虚拟列，及列的别名的使用按条件检索好，现在开始我们的第一个查询啦！SELECT * From tbl_name;组合查询组合查询是指所查询得资料来源并不只有单一的表格，而是联合一个以上的表格才能够得到结果的。SELECT *FROM table1,table2WHERE table1.colum1=table2.column1说明：1. 查询两个表格中其中 column1 值相同的资料。2. 当然两个表格相互比较的栏位，其资料形态必须相同。3. 一个复杂的查询其动用到的表格可能会

29、很多个。整合性的查询：SELECT COUNT (*)FROM table_nameWHERE column_name = xxx说明：查询符合条件的资料共有几笔。SELECT SUM(column1)FROM table_name说明：1.计算出总和，所选的栏位必须是可数的数字形态。2.除此以外还有AVG()是计算平均、 MAX() 、 MIN()计算最大最小值的整合性查询。SELECT column1,AVG(column2)FROM table_nameGROUP BY column1HAVING AVG(column2) xxx说明：1.GROUP BY:以 column1为一组计算c

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

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

32、EEN表示column1的值介於xx 和yy之间。其它重要概念：SQL允许你创建一个虚拟列或对已有的列进组合和修改后产生的派生列。SQL允许可以创建一个新的虚拟列，而且还可以对它安自己的需要进行重命名，你可以按语法列名别名来对任何一个列进行重命名(注意在列名与别名之间有空格 )或用 ()括起来。词语解释：numericnju:merikadj.(=mumerical)数字的 , 数值的decimaldesimEladj.以十作基础的 , 十进位的SQL 查询语句精华使用简要一、 简单查询简单的 Transact-SQL查询只包括选择列表、FROM 子句和 WHERE 子句。它们分别说明所查询列

33、、查询的表或视图、以及搜索条件等。例如，下面的语句查询testtable 表中姓名为 张三的 nickname字段和 email 字段。SELECT nickname,emailFROM testtableWHERE name=张三 (一 ) 选择列表选择列表 (select_list) 指出所查询列，它可以是一组列名列表、星号、表达式、变量 (包括局部变量和全局变量 )等构成。1、选择所有列例如，下面语句显示testtable 表中所有列的数据：SELECT *FROM testtable2、选择部分列并指定它们的显示次序查询结果集合中数据的排列顺序与选择列表中所指定的列名排列顺序相同。例如

34、：SELECT nickname,emailFROM testtable3、更改列标题在选择列表中，可重新指定列标题。定义格式为：列标题 =列名列名 列标题如果指定的列标题不是标准的标识符格式时，应使用引号定界符，例如，下列语句使用汉字显示列标题：SELECT昵称 =nickname, 电子邮件 =emailFROM testtable4 、删除重复行SELECT语句中使用ALL 或 DISTINCT选项来显示表中符合条件的所有行或删除其中重复的数据行，默认为 ALL 。使用 DISTINCT 选项时，对于所有重复的数据行在SELECT 返回的结果集合中只保留一行。5 、限制返回的行数使用 T

35、OP n PERCENT选项限制返回的数据行数， TOP n 说明返回 n 行，而 TOP n PERCENT时，说明 n 是表示一百分数， 指定返回的行数等于总行数的百分之几。例如：SELECT TOP 2 *FROM testtableSELECT TOP 20 PERCENT *FROM testtable( 二)FROM 子句FROM 子句指定SELECT 语句查询及与查询相关的表或视图。在FROM 子句中最多可指定256 个表或视图，它们之间用逗号分隔。在 FROM 子句同时指定多个表或视图时，如果选择列表中存在同名列， 这时应使用对象名限定这些列所属的表或视图。 例如在 usert

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

37、果选择列表中存在同名列， 这时应使用对象名限定这些列所属的表或视图。 例如在 usertable 和 citytable 表中同时存在 cityid 列，在查询两个表中的cityid 时应使用下面语句格式加以限定：SELECT username,citytable.cityidFROM usertable,citytableWHERE usertable.cityid=citytable.cityid在 FROM 子句中可用以下两种格式为表或视图指定别名：表名 as 别名表名 别名例如上面语句可用表的别名格式表示为：SELECT username,b.cityidFROM usertable a,citytable bWHERE a.cityid=b.cityidSELECT 不仅能从表或视图中检索数据，它还能够从其它查询语句所返回的结果集合中查询数据。例如：SELECT a.au_fname+a.au_lnameFROM authors a,titleauthor ta