MySQL数据库学习笔记

sql 代码

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

第一章、了解概念，并理解它
一、资料定义 DDL(Data Definition Language)
资料定语言是指对资料的格式和形态下定义的语言，他是每个资料库要建立时候时首先要面对的，举凡资料分哪些表格关系、表格内的有什麽栏位主键、表格和表格之间互相参考的关系等等，都是在开始的时候所必须规划好的。
数据库


第二章　MySQL数据库数据类型

第一节、数据类型

MySQL数据库目前支持以下几种
下列代码字母用于描述中：
M 指出最大的显示尺寸。最大的显示尺寸长度为 255。
D 适用于浮点类型。指出跟随在十进制小数点后的数字数量。最大可能值为 30，但不应大于 M-2。
方括号 (
"["
 
and
 
"]"
) 指定可选的类型修饰部份。

1、数值型(包括整数与浮点数，浮点型支持科学表示法)
浮点类型:
numeric
 [nju:merik] 
decimal
(5,2),数字位:5,小数位:2
numeric
 等价于 
decimal
 并且是作为字符串存储的.而不是作为二进制浮点数.

说明:
salary 
DECIMAL
(3,2)
在这个例子中，3 (精度(
precision
)) 代表重要的十进制数字的数目，2 (数据范围(scale)) 代表在小数点后的数字位数。在这种情况下，因此，salary 列可以存储的值范围是从 -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、布尔类型 TURE 
FALSE

标识符约定
标识符   最大长度 允许的字符
－－－－－－－－－－－－－－－
数据库  64  一个目录名中允许的任何字符,除了/,\和.
表  64  一个目录名中允许的任何字符,除了/和.
列  64  所有的字符
索引 64  所有的字符
加名 255  所有的字符
定界符``,反引号ANSI SQL中双引号为定界符

注意:在MYSQL中,数据库和表对应操作系统下的目录与文件,因而,操作系统的敏感性决定数据库和表命名的大小敏感,所以在LINUX下数据库与表名的大小写敏感.
提示:USE ,HELP 是Mysql中为少数几个不需要终结符的语句之一.

用户变量表示为: @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码被转为一个字符:
  
select
 0x787868 ; =
select
 x'787868';
  
select
 hex('xxh');
  
select
 0xa+5;

  
select
 
true
,
true
,flase,
FALSE
;

　　其中浮点型用于域，表示方法解释一下，如：
　　
FLOAT
[(M,D)] [UNSIGNED] [ZEROFILL]
　　如果 UNSIGNED 被指定，负值是不允许的。M 是显示宽度，D 是小数位数。并且如果负号“－”也占一个宽度。
2、字符串类型
　　字符串是多个字符组成的一个字符序列,由单引号,或双引号字符包围,但在ANSI模式中运行时只能用单引号,也就是说在SQL SERVER数据库,使用应该使用单引号,保证兼容性应该使用单引号.
    字符串类型有 
CHAR
、
VARCHAR
、BLOB、TEXT、ENUM 和 
SET
　；
　　ENUM 是一个字符串对象，其值通常选自一个允许值列表中，该列表在表创建时的列规格说明中被明确地列举。
　　每个枚举值均有一个索引值：
　　在列说明中列表值所允许的成员值被从 1 开始编号。
　　空字符串错误值的索引值为 0。这就意味着，你可以使用下面所示的 
SELECT
 语句找出被赋于无效 ENUM 值的记录行。
mysql> 
SELECT
 * 
FROM
 tbl_name 
WHERE
 enum_col=0;
NULL
 值的索引值为 
NULL
。
总结：ENUM用于互相排斥的值，而 
SET
列可以从一个值的列表中选择多个值。
常用技巧:
1.在命令提示符下:mysql -h -u root db_name <x.sql


　　在串的环境中，可用十六进制常数来指定串值。其语法与前面描述的数值值相同，但是每对十六进制的数字都被看作 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 系统中 ASCII(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
 和
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
列表中的一值,它将返回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
时,返回expr1

 
case
 value 
when
 [compare-value] 
then
 result [..]
 
set
 @a=2;
 
select
 
case
 @a
 
when
 1 
then
 'one'
 
when
 2 
then
 'two'
 
else
 'more' 
end
;

 if (expr1,expre2,expr3)
 如果expr1为真,(expr1<>0以及expr1<>
null
),那么if()返回expr2,否则返回expr3

 
nullif
(expr1,expr2) 如果expr1=expr2为真,返回
null
,否则返回expr1;
mysql> 
select
 
nullif
(1,1);
 -> 
null
mysql> 
select
 
nullif
(1,2)
 -> 1
D.mysql字符串函数
返回字符串str最左边的那个字符的ASCII码值.
mysql> 
select
 ascii('2');
 -> 50
返回N的字符串表示的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);
 -> ff

mysql> 
select
 hex('abc'); //
select
 ascii('a'); -> 97 
select
 hex(97); -> 61
 -> 616263
mysql> 0x616263;
 -> abc

char
()以整数类型解释参数,返回这个整数所代表的Ascii码值给出的字符组成的字符串,
null
值将被忽略
mysql> 
select
 
char
(120,120,104);
 -> xxh

concat(str1,str2,...)
将参数连接成字符串返回,如果有任何一个参数为
null
,返回值也为
null
.
locate(子串substr,母串str)返回子串substr在字符吕中第一次出现的位置.如果子串substr在str中不存在,返回值为0:

ltrim(str)
返回移除了领头的空格字符后的str;


第08章 MySQL数据操纵语言DML
·
select
 语法
·

select
 列1,列2,列3,,,,,
from
 表1,表2,表3
[
where
 条件]
[
group
 
by
 ...]
[
having
 ....]
[]

第二节　域/列类型属性
域/列的类型属性，有以下特性：
■ 其中可以存放什么类型的值。
■ 值要占据多少空间，以及该值是否是定长的(所有值占相同数量的空间)或(所占空间量依赖于所存储的值)。
■ 该类型的值怎样比较和存储。
■ 此类型是否允许 
NULL
 值。
■ 此类型是否可以索引。

·数字
·日期和时间
·字符串(字符)

列类型的属性
有以下四种：
·
Default
·
Not
 
Null
  或 
Null
·
Primary
 
Key
 即主键
·Auto_increment (只有1个只能被索引)

一、数据库的操作
｜操作系统下的操作
mysql -h -u -p
mysql -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的用法



二、表格、索引的操作
·创建表格
·更改表格
·建立索引
·删除表格、索引
·表格其他相关操作
以下操作都是以表格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
 
PRIMARY
 
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 
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_name 
int
(2) 
not
 
null
 
primary
 
key
 ;

ALTER
 
TABLE
 table_name 
ADD
 
PRIMARY
 
KEY
 (column_name)
说明：更改表得的定义把某个栏位设为主键。
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
 
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_name1,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 | wild]
DESCRIBE tbl_name;
说明：DESCRIBE 是 SHOW COLUMNS 
FROM
 的缩写。用于显示表格中列的信息。
DESCRIBE tbl_name; 等于SHOW COLUMNS 
FROM
 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  
SET
 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) 
values
 ('007','xxh5fd')
第二种：




2、记录的更改
还是先来看一看
UPDATE
 的语法
UPDATE
 tbl_name [, tbl_name ...]   
SET
 col_name1=expr1 [, col_name2=expr2 ...]
[
WHERE
 where_definition]

说明：
UPDATE
 以新的值更新现存表中行的列。
SET
 子句指出要修改哪个列和他们应该给定的值。
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
] COLUMNS 
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 表示你希望检索的列。
SELECT
 也可以被用于检索没有引用任何表的计算列。

要讲的内容是：

·第一个查询
·选择特定的列，对列进行排序
·查找不重复的数据
·创建虚拟列，及列的别名的使用
·按条件检索
·
·

好，现在开始我们的第一个查询啦！
SELECT
 * 
From
 tbl_name;







组合查询
组合查询是指所查询得资料来源并不只有单一的表格，而是联合一个以上的
表格才能够得到结果的。
SELECT
 *
FROM
 table1,table2
WHERE
 table1.colum1=table2.column1
说明：
1.查询两个表格中其中 column1 值相同的资料。
2.当然两个表格相互比较的栏位，其资料形态必须相同。
3.一个复杂的查询其动用到的表格可能会很多个。

整合性的查询：
SELECT
 
COUNT
 (*)
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
: 以column1 为一组计算 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
 column1
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
 table_name1
WHERE
 column1 
BETWEEN
 xx 
AND
 yy
说明：
BETWEEN
 表示 column1 的值介於 xx 和 yy 之间。


其它重要概念：
SQL 允许你创建一个虚拟列或对已有的列进组合和修改后产生的派生列。
SQL 允许可以创建一个新的虚拟列，而且还可以对它安自己的需要进行重命名，你可以按语法列名 别名来对任何一个列进行重命名(注意在列名与别名之间有空格)或用()括起来。


词语解释：
numeric
　[nju:'merik]　adj.(=mumerical)数字的, 数值的
decimal
　[desimEl]　adj.以十作基础的, 十进位的


SQL查询语句精华使用简要



一、 简单查询
　　简单的Transact-SQL查询只包括选择列表、
FROM
子句和
WHERE
子句。它们分别说明所查询列、查询的表或视图、以及搜索条件等。
　　例如，下面的语句查询testtable表中姓名为
"张三"
的nickname字段和email字段。

 　 
SELECT
 nickname,email
　　
FROM
 testtable
　　
WHERE
 
name
='张三'

　　(一) 选择列表

　　选择列表(select_list)指出所查询列，它可以是一组列名列表、星号、表达式、变量(包括局部变量和全局变量)等构成。

　　1、选择所有列

　　例如，下面语句显示testtable表中所有列的数据：

 　 
SELECT
 *
　　
FROM
 testtable

　　2、选择部分列并指定它们的显示次序

　　查询结果集合中数据的排列顺序与选择列表中所指定的列名排列顺序相同。
　　例如：

 　　
SELECT
 nickname,email
　　
FROM
 testtable

　　3、更改列标题

　　在选择列表中，可重新指定列标题。定义格式为：
　　列标题=列名
　　列名 列标题
　　如果指定的列标题不是标准的标识符格式时，应使用引号定界符，例如，下列语句使用汉字显示列标题：

 　　
SELECT
 昵称=nickname,电子邮件=email
　　
FROM
 testtable

　　4、删除重复行

　　
SELECT
语句中使用
ALL
或
DISTINCT
选项来显示表中符合条件的所有行或删除其中重复的数据行，默认为
ALL
。使用
DISTINCT
选项时，对于所有重复的数据