MySQL学习笔记

1、MySQL学习笔记（1）1，数据库管理概论（1）关系型数据模型：特性是利用表（Table）来呈现数据，将表视为一个集合。以关系型数据模型为基础的数据库称为关系型数据库（Relational DataBase，RDB）。在RDB中有许多表。表由行（Row）及字段（Column）组成，每一行有时称为一笔记录（record）,每一行的字段中，都分别存储着值（Value）。每一个字段都会有一个字段名称（Field Name）。这个表也称为关系（Relation），行称为群组（Tupple），字段名称称为属性（Attribute）。（2）表中拥有特别意义的字段称为键（Key）。主键（Primary Ke

2、y）：字段内容接不相同，可以用来指出表中的每一行。候选键（Condidate Key）：有资格成为主键的字段。连接键（Concatenated Key）：一个以上的字段组合而称为主键。外部键（Foreign Key）：一个表的字段与另一个表产生关联，但是这个字段不是主键。（3）关联的类型一对一：两个表之间存在一对一的关联一对多或多对一多对多：难以维护，不建议使用（4）集合运算与关系运算集合运算：并（Union）、差（Difference）、交（Intersection）、笛卡尔积（Cartesian Product）关系运算：选取（Select）：取出与条件相符合的行（Row）投影（Proje

3、ct）：取出与条件相符合的字段（field）结合（Join）：以共同的字段为中介，让多个表结合起来，建立一个新表。先执行笛卡尔积，再进行选取除法（Division）：选取被除数表中有除数表字段值的行，并且去除公共部分2，SQL概述（1）目前的SQL标准：SQL92。多数数据库软件使用不具有兼容性的扩展版本，Oracle：PL/SQL，微软：T-SQLSQL是关系型数据库专用语言，非过程语言。语句末尾要有分号“；”注释以两个负号“-”开始，直到行结束。也支持和#。SQL关键字不区分大小写（2）SQL语言的功能分类数据定义语言（DDL）：CREATE（建立数据库或表），ALTER（更改数据库或表的

4、结构），DROP（删除数据库或表）数据操作语言（DML）：SELECT（搜索数据），INSERT（增加数据），UPDATE（更新数据），DELETE（删除数据）数据控制语言（DCL）：GRANT（授予用户操作数据的权限），REVOKE（撤销用户操作数据的权限），COMMIT（确定数据的更改），ROLLBACK（取消数据的更改）3，数据库的定义步骤（1）使用CREATE DATABASE语句定义数据库（2）确定字段的名称与数据类型（3）建立表，用CREATE TABLE语句（4）插入数据，用INSERT语句（5）搜索数据库，SELECT FORM ;4，建立数据库（1）CREATE语句CREAT

5、E DATABASE database_name;（2）USE语句刚建立完成，或者拥有很多数据库，指定需要使用的数据库。USE db_name（3）DROP语句DROP db_name5，增加数据库用户MySQL有一个内置的数据库mysql，用户相关数据存在user、host、db这三个表中。host：可以访问数据库的主机及其相关信息，例如主机地址、主机名称user：可访问表中用户的相关信息db：来自特定主机的特定用户，可访问数据库的相关权限用如下语句，可以查看指定的表：use mysql;show columns from host;可以通过向这三个表插入数据增加用户及其权限：让MySQL数

6、据库知道是哪一部主机在访问db_name数据库：INSERT INTO host VALUES(localhost,db_name,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y);(10xY)增加用户到user表：INSERT INTO user VALUES(localhost,user_name,PASSWORD(password),Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,0,0,0,0);增加用户的权限到db表：INSERT INTO db VALUES(localhost,db_name,

7、user_name,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y);MySQL学习笔记（2）1，MySQL的数据类型：数值数据数值类型内存空间TINYINT1BSMALLINT2BMEDIUMINT3BINT或INTEGER4BBIGINT8BFLOAT(M,D)8BFLOAT4BDOUBLE(M,D)M+2DECIMAL(M,D)M+2注意事项：处理数值类型的数据速度是最快的，要想加快查询速度，尽量将字段设置为数值类型（M,D）中M表示数据的长度，D表示小数的位数声明过大的数值数据会降低效率使用数值类型字段作为主键，可以使数据具有唯一性，加快存取数据的速度DECIM

8、AL可以根据所存入的数据大小调整如果数据超界，MySQL只存最大值2，数据字段修饰符（1）自动增量设置：AUTO_INCREMENT每增加一笔新纪录会自动加一，存入改属性的字段值必须存入NULL、0、空白、没有用过的一个数字。用LAST_INSERT_ID()函数可以取得最新插入的AUTO_INCREMENT数值例子：CREATE TABLE TEST1(FIELD1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);INSERT INTO TEST1 (FIELD1) VALUES （NULL);INSERT INTO TEST1 (FIELD1) VALUE

9、S （0）;INSERT INTO TEST1 (FIELD1) VALUES （）;（2）自动补齐0修饰符：ZEROFILL会在数值之前补齐0，例如1234存入声明为INT(6)ZEROFILL的字段时，存放的数据会变为001234（3）无符号数：UNSIGNED只适用于整数型字段3，MySQL的数据类型：字符或字符串各种类型最大长度CHAR(X)255BVARCHAR(X)255BTINYTEXT255BTEXT65535BMEDIUMTEXT16777215BLONGTEXT4294967295BTINYBLOB255BBLOB65535BMEDIUMBLOG16777215BLONGB

10、LOB4294967295B（1）CHAR、VARCHAR声明：CHAR(X),VARCHAR(X)CHAR是固定长度的，VARCHAR变长。如果定义类型时混用，MySQL可能统一转换成VARCHAR，除非声明的长度小于4，则会统一转换成CAHR例如：CREATE TABLE TEST(FIELD1 CHAR(5), FIELD2 VARCHAR(10)相当于：CREATE TABLE TEST(FIELD1 VARCHAR(5), FIELD2 VARCHAR(10)CREATE TABLE TEST(FIELD1 VARCHAR(2)相当于CREATE TABLE TEST(FIELD1

11、VARCHAR(2)（）TEXT和BLOB类型这两种字段通常用来存放声音、图像、大量文字这类存储长度不固定的数据。可以看成非常大的VARCHAR。4，MySQL的数据类型：其他类型（1）ENUM类型（多选一）可以为NULL。值只可以是列表中的一项，如果不在列表中，就为空白。如果设置DEFAULT修饰符，则如果没有输入任何数据就用DEFAULT后面的值作为其值。例：CREATE TABLE TEST(FIELD1 ENUM(AM,PM) DEFAULT AM, FIELD2 ENUM(G,B,R);（2）SET类型（多选多）可以是列表中的各个项目。列表最多64个项目。例：CREATE TABLE

12、 TEST5(FIELD1 SET (BLACK,WHITE,GRAY,GREEN,RED);INSERT INTO TEST5 (FIELD1) VALUES (RED,GRAY);5，MySQL的数据类型：字段修饰符适用数据类型AUTO_INCREMENTINTBINARYCHAR、VARCHARDEFAULT除了BLOG、TEXTNOT NULL所有数据类型NULL所有数据类型PRIMARY KEY所有数据类型UNIQUE所有数据类型UNSIGNEDINTZEROFILL所有数值类型例子：mysql create table test1 (- id int auto_increment

13、not null primary key,- salary int zerofill unsigned,- address varchar(200),- job enum(工程师,教师,商人,农夫) default 工程师,- domain set(西安,广州,天津,上海,合肥)- );MySQL学习笔记（3）1，建立表与索引（1）CREATE的参数与命名参数：IF NOT EXISTS作用：要建立的表不存在时才建立表例子：CREATE TABLE IF NOT EXISTS Table_Name(Column_Name Data_Type)命名规范：表名用复数，字段名称用单数复合名称中间用下

14、划线所有数据库对象名称尽量前后保持一致（2）使用Script建立数据库把语句复制到文本文件中，存成*.sql（3）更改现有表的相关信息更改表名称：ALTER TABLE customers RENAME customer_table;更改字段名称：ALTER TABLE customer_table CHANGE name customer_name VARCHAR(30);更改字段类型：ALTER TABLE customer_table CHANGE customer_name VARCHAR(50);删除表：DROP TABLE table_name;删除部分字段：ALTER TABLE

15、 table_name DROP field_name;删除Primary Key：ALTER TABLE table_name DROP PRIMARY KEY;增加字段：ALTER TABLE table_name ADD column_name data_type;（4）使用数据库索引索引可以加快SELECT的速度，但是会降低INSERT、UPDATA、DELETE的速度。通常选择WHERE子句常出现的字段或者值不会重复的字段（Key）来建立索引。默认情况下，MySQL自动为表中的Primary Key建立索引。增加索引：CREATE INDEX index_name ON table_

16、name (column_list);或者：ALTER TABLE table_name ADD INDEX（column_name);删除索引：DROP INDEX index_name ON table_name;或者：ALTER TABLE table_name DROP INDEX column_name;2，数据库的增加删除操作（1）INSERT 插入数据INSERT INTO的语法如下：INSERT INTO LOW_PRIORITY | DELAYED Tbl_Name (Column_name,.) VALUES (expression,.);其中字符串类型需要用或者“”包围。

17、转义字符：0,n,t,r,b,%,_数字类型，如果在浮点域插于整数值，会自动转换。如果插入有唯一性的字段（PRIMARY KEY, AUTO_INCREMENT, UNIQUE），发生重复，则INSERT被忽略。以下REPLACE语句即使遇到唯一性字段重复也会覆盖：REPLEACE LOW_PRIORITY | DELAYED Tbl_Name (Column_name,.) VALUES (expression,.);（2）UPDATE 更新数据语法：UPDATE LOW_PRIORITY Tbl_Name SET col_name1=expr1, col_name1=expr1,. WHE

18、RE expr LIMIT #LIMIT #限制最多只有#个数据行更新。（3）DELETE 删除数据语法：DELETE LOW_PRIORITY FROM tbl_name WHERE expr LIMIT #如果不加上WHERE，整个表被删除可以先用SELECT查看有多少笔数据可以删除DELETE只是将数据的连接删除，空间实际上没有从硬盘释放。可以用下列命令释放：OPTIMIZE TABLE tbl_name;3，表达式与相关函数(1)常用数学函数：ABS(X)SIGN(X)MOD(N,M)FLOOR(X)CEILING(X)ROUND(X)ROUND(X,D)：四舍五入到小数第D位EXP(

19、X)LOG(X)：求ln（x）LOG10(X)POW(X,Y) OR POWER(X,Y)SQRT(X)PI()SIN(X),COS(X),TAN(X),ASIN(X),ACOS(X),ATAN(X)ATAN2(X,Y)：求y/x的ARCTAN函数COT(X)RAND(N)LEAST(N1,N2,N3,.)：在参数间比较，返回最小值GREATEST(N1,N2,N3,.)DEGREE(X)：弧度转化为角度RADIANS(X)：角度转化为弧度TRUNCATE(X,D)：将X的小数以下D位舍去（2）比较运算=(相等)！= OR (不等于)IS NULLIS NULL(exp)IS NOT NULL

20、BETWEEN A AND BIN(Value1,Value2,.)NOT IN(Value1,Value2,.)COLESCE(LIST)：在LIST的所有项目中，找出第一个不是NULL值的数据INTERVAL(N,N1,N2,.)：N小于Nk时，返回k，否则返回0.（3）逻辑运算符AND,OR,NOTLIKE运算符和通配符%：可以替代任何字符_或？：可替代一个字符例如：mysql select * from customers where address like *5;（4）字符串函数ASCII(X)：返回字符串最左边字符的ASCII值CONV(N,FROM_BASE,TO_BASE)：

21、N为底的FROM_BASE转成TO_BASE，N为字符串或数字BIN(N)：十进制N转化为二进制OCT(N)HEX(N)CHAR(N,.)：将ASCII字符N转换为对应的字符CONCAT(S1,S2,.)LENGTH(STRING)INSTR(S1,S2) 或 LOCATE(S1,S2)：s1在s2第一次出现的位置LPAN(STRING,LEN,PANSTR)：将PANSTR从左边填入，再加上STRING，总长度为LENRPAN(STRING,LEN,PANSTR)：将PANSTR从右边填入，再加上STRING，总长度为LENLEFT(STR,LEN)：从STR左边取LEN长度的字符串RIGH

22、T(STR,LEN)SUBSTRING(STR,POS,LEN)SUBSTRING_INDEX(STR,DELIM,COUNT)：将STR用DELIM切成数个字符串，取出COUNT返回，COUNT为负数时，从字符串结尾开始LTRIM(STR)：删除STR开头的空白RTRIM(STR)TRIM(BOTH | LEADING | TRAILING REMSTR FROM STR)：删除STR中的REMSTR，默认是BOTHSPACE(N)：产生N个空白字符REPLACE(STRING,FROM_STR,TO_STR)：用TO_STR替代FROM_STRREPEAT(STR,COUNT)：产生COU

23、NT个STRREVERSE(STR)INSERT(STR,POS,LEN,NEWSTR)：STR的POS之后插入长度为LEN的NEWSTRELT(N,S1,S2,.)：根据N选择S1,S2,.中的第N个返回FIELD(STRING,S1,S2,.)：STRING与后面的比较，第几个相同则返回几，否则返回0FIND_IN_SET(STRING,STR_LIST)：找出STRING是STR_LIST中的第几个，STR_LIST之间用，分割LCASE(STR) 或 LOWER(STR)UCASE(STR) 或 UPPER(STR)4，数据库的查询操作（1）SELECT语法：SELECT column

24、1,column2,.FROM table1,table2,. WHERE conditions LIMIT #可以用*代表所有字段用LIMIT # 限制只返回前#个结果如果同一个字段出现在FROM后一个以上的表中时，要显示的指明选取哪个表的哪个字段。例如：SELECT order.order_ID FROM orders, customers WHERE customers.tustomer_ID = orders.customer_ID可以将SQL语句中表的名称用别名（Alias）代替，可以介绍重复的输入，例如：SELECT order.order_ID FROM orders AS O, customers AS CWHERE C.tustomer_ID = O.customer_ID可以将别名与内部函数连用，结果显示在以别名为field_name的列中，例如：mysql select max(id) as maxid from customers（2）ORDER BYSELECT的结果以建立的时间