一千行MySQL学习笔记0629144852.docx

1、一千行MySQL学习笔记0629144852千行MySQL学习笔记/* 启动 MySQL */net start mysql/*连接与断开服务器*/mysql -h地址-P端口 -u用户名-p密码/*跳过权限验证登录MySQL */mysqld -skip-grant-tables-修改root密码密码加密函数password。update mysql. user set password = password( root);SHOW PROCESSLIST -显示哪些线程正在运行SHOW VARIABLES -/*数据库操作*/ -查看当前数据库select database ();-显示当

2、前时间、用户名、数据库版本select now(), user (), version();-创建库create database if not exists 数据库名 数据库选项创建数据库最简单的方式CREATE DATABASE my_db;或者是CREATE DATABASE IF NOT EXISTS my_db;创建utf8编码的数据库CREATE DATABASE IF NOT EXISTS my_db default character set utf8 COLLATE utf8_ge neral_ci;那么在这个数据库下创建的所有数据表的默认字符集都会是 utf8 了，注意后面

3、这句话 COLLATE utf8_ge neral_ci, 大致意思是在排序时根据 utf8变码格式来排序查看数据库列表show databases;使用数据库use my_db;查看数据库默认编码show variables like character_set_%| Variable_ name+ +-| Value| +| character_set_clie nt| utf8| character_set_c onn ecti on | utf8| character_set_database | lat in1| character_set_filesystem | binary|

4、character_set_results| utf8| character_set_server| lat in1| character_set_system| utf8| character_sets_dir| /usr/local/mysql-5.5.28-linux2. 6-i686/share/charsets/+修改数据库编码alter database my_db character set lat in1;删除数据库drop database my_db;或者是drop database IF EXISTS my_db;数据库选项：CHARACTER SET charset_n

5、ameCOLLATE collation_name-查看已有库show databases like pattern-查看当前库信息show create database 数据库名-修改库的选项信息alter database 库名选项信息-删除库drop database if exists 数据库名同时删除该数据库相关的目录及其目录内容/*表的操作*/ -创建表create temporary table if not exists库名.表名（表的结构定义 ）表选项每个字段必须有数据类型最后一个字段后不能有逗号temporary 临时表，会话结束时表自动消失对于字段的定义：字段名 数据类

6、型NOT NULL | NULL DEFAULT default_valueAUTONCREMENT UNIQUE KEY | PRIMARY KEY COMMENT string -表选项- 字符集CHARSET = charset_name如果表没有设定，则使用数据库字符集- 存储引擎ENGINE = engine_name表在管理数据时采用的不同的数据结构，结构不同会导致处理方式、提供的特性操作等不同 常见的引擎： InnoDB MyISAM Memory /Heap BDB Merge Example CSV MaxDB Archive不同的引擎在保存表的结构和数据时采用不同的方式My

7、ISAM 表文件含义： .frm 表定义， .MYD 表数据， .MYI 表索引InnoDB 表文件含义： .frm 表定义，表空间数据和日志文件SHOW ENGINES - 显示存储引擎的状态信息SHOW ENGINE 引擎名 LOGS|STATUS - 显示存储引擎的日志或状态信息- 数据文件目录DATA DIRECTORY = 目录 - 索引文件目录INDEX DIRECTORY = 目录 - 表注释COMMENT = string- 分区选项PARTITION BY . （详细见手册 ）- 查看所有表SHOW TABLES LIKE patternSHOW TABLES FROM 表名

8、- 查看表机构SHOW CREATE TABLE 表名 （信息更详细）DESC 表名 / DESCRIBE 表名 / EXPLAIN 表名 / SHOW COLUMNS FROM 表名 LIKE PATTERNSHOW TABLE STATUS FROM db_name LIKE pattern- 修改表- 修改表本身的选项ALTER TABLE 表名 表的选项EG: ALTER TABLE 表名 ENGINE = MYISAM;- 对表进行重命名RENAME TABLE 原表名 TO 新表名RENAME TABLE 原表名 TO 库名 .表名 （可将表移动到另一个数据库）- RENAME 可

9、以交换两个表名- 修改表的字段机构ALTER TABLE 表名 操作名- 操作名ADD UNIQUE 索引名 （字段名 ）- 创建唯一索引ADDINDEX 索引名 （字段名 ） - 创建普通索引ADD字段名 （所有原有属性也需写上 ）DROP PRIMARY KEY - 删除主键 （删除主键前需删除其 AUTO_INCREMENT 属性 ）DROP INDEX 索引名 - 删除索引DROP FOREIGN KEY 外键 - 删除外键- 删除表DROP TABLE IF EXISTS 表名 .- 清空表数据TRUNCATE TABLE 表名- 复制表结构CREATE TABLE 表名 LIKE

10、要复制的表名- 复制表结构和数据CREATE TABLE 表名 AS SELECT* FROM 要复制的表名- 检查表是否有错误CHECK TABLE tbl_name , tbl_name . option .- 优化表OPTIMIZE LOCAL | NO_WRITE_TO_BINLOG TABLE tbl_name , tbl_name .- 修复表REPAIR LOCAL | NO_WRITE_TO_BINLOG TABLE tbl_name , tbl_name . QUICK EXTENDED USE_FRM- 分析表ANALYZE LOCAL | NO_WRITE_TO_BINL

11、OG TABLE tbl_name , tbl_name ./* 数据操作 */ - 增INSERT INTO 表名 （字段列表 ） VALUES （值列表）, （值列表）, .- 如果要插入的值列表包含所有字段并且顺序一致，则可以省略字段列表- 可同时插入多条数据记录！REPLACE 与 INSERT 完全一样，可互换。INSERT INTO 表名 SET 字段名=值, 字段名=值, .- 查SELECT 字段列表 FROM 表名 其他子句 - 可来自多个表的多个字段- 其他子句可以不使用- 字段列表可以用 * 代替，表示所有字段- 删DELETE FROM 表名 删除条件子句 没有条件子句

12、，则会删除全部- 改UPDATE 表名 SET 字段名=新值, 字段名=新值 更新条件/* 字符集编码 */ - MySQL 、数据库、表、字段均可设置编码- 数据编码与客户端编码不需一致SHOW VARIABLES LIKE character_set_% - 查看所有字符集编码项character_set_client客户端向服务器发送数据时使用的编码character_set_results服务器端将结果返回给客户端所使用的编码SET 变量名 = 变量值set character_set_client = gbk;set character_set_results = gbk;set c

13、haracter_set_connection = gbk;SET NAMES GBK; - 相当于完成以上三个设置- 校对集校对集用以排序查看所有字符SHOW CHARACTER SET LIKE pattern /SHOW CHARSET LIKE pattern 集SHOW COLLATION LIKE pattern 查看所有校对集charset 字符集编码 设置字符集编码collate 校对集编码 设置校对集编码/* 数据类型（列类型） */ 1. 数值类型- a. 整型 类型 字节 范围（有符号位）tinyint 1 字节 -128 127 无符号位： 0 255smallint

14、2 字节 - 32768 32767mediumint 3 字节 - 8388608 8388607int 4 字节bigint 8 字节int (M) M 表示总位数- 默认存在符号位， unsigned 属性修改- 显示宽度，如果某个数不够定义字段时设置的位数，则前面以 0 补填， zerofill 属性修改例： int (5) 插入一个数 123 ，补填后为 00123- 在满足要求的情况下，越小越好。- 1 表示 bool 值真，0 表示 bool 值假。 MySQL 没有布尔类型，通过整型 0 和 1 表示。常用 tinyint( 1 )表示布尔型。double （双精度 ）8 字节

15、- b. 浮点型浮点型既支持符号位unsigned 属性，也支持显示宽度 zerofill 属性。不同于整型，前后均会补填 0.定义浮点型时，需指定总位数和小数位数。float (M, D) double (M, D)M 表示总位数， D 表示小数位数。M 和 D 的大小会决定浮点数的范围。不同于整型的固定范围。M 既表示总位数(不包括小数点和正负号)，也表示显示宽度(所有显示符号均包括) 支持科学计数法表示。浮点数表示近似值。- c. 定点数 decimal - 可变长度decimal (M, D) M 也表示总位数， D 表示小数位数。 保存一个精确的数值，不会发生数据的改变，不同于浮点数

16、的四舍五入将浮点数转换为字符串来保存，每 9 位数字保存为 4 个字节2. 字符串类型- a. char, varchar char 定长字符串，速度快，但浪费空间varchar 变长字符串，速度慢，但节省空间M 表示能存储的最大长度，此长度是字符数，非字节数。 不同的编码，所占用的空间不同。char ,最多 255 个字符，与编码无关。varchar ,最多 65535 字符，与编码有关。一条有效记录最大不能超过 65535 个字节。utf8 最大为 21844 个字符， gbk 最大为 32766 个字符， latin1 最大为 65532 个字符varchar 是变长的，需要利用存储空间

17、保存 varchar 的长度，如果数据小于 255 个字节，则采用 一个字节来保存长度，反之需要两个字节来保存。varchar 的最大有效长度由最大行大小和使用的字符集确定。最大有效长度是 65532 字节，因为在 varchar 存字符串时，第一个字节是空的，不存在任何数据，然后还需两个字节来存放字符串的长度，所以有效长度是 64432 - 1- 2 = 65532 字节。例：若一个表定义为 CREATE TABLE tb(c1 int, c2 char (30 ), c3 varchar (N) charset = utf8; 问 N 的最大值是多少？ 答： (65535 -1-2-4-3

18、0*3)/3- b. blob, text blob 二进制字符串(字节字符串)tinyblob, blob, mediumblob, longblobtext 非二进制字符串(字符字符串)tinytext, text , mediumtext, longtexttext 在定义时，不需要定义长度，也不会计算总长度text 类型在定义时，不可给 default 值- c. binary, varbinary 类似于 char 和 varchar ，用于保存二进制字符串，也就是保存字节字符串而非字符字符串 char , varchar , text 对应 binary , varbinary ,

19、 blob.3. 日期时间类型一般用整型保存时间戳，因为 PHP 可以很方便的将时间戳进行格式化。datetime8 字节日期及时间1000 -01 -01 00:00:00 到 9999 -12-31 23:59:59date3 字节日期1000 -01-01 到 9999 -12-31timestamp4 字节时间戳19700101000000 到 2038 -01-19 03 :14 :07time3 字节时间-838 :59 :59 到 838 :59 :59year1 字节年份1901 - 2155datetime “YYYY- MM-DD hh:mm:ss ”timestamp“

20、YY-MM -DD hh:mm:ss ”“ YYYYMMDDhhmmss ”“ YYMMDDhhmmss ” YYYYMMDDhhmmss YYMMDDhhmmss date “ YYYY- MM -DD”“ YY-MM -DD”“ YYYYMMDD”“ YYMMDD”YYYYMMDDtimeyearhh:mm:ss ”hhmmss ”hhmmssYYYY”YYMMDDYY”YYYYYY4. 枚举和集合-枚举(enum) enum(val1, val2, val3.) 在已知的值中进行单选。最大数量为 65535.1 开始枚举值在保存时，以 2 个字节的整型 (smallint )保存。每个枚

21、举值，按保存的位置顺序，从 逐一递增。表现为字符串类型，存储却是整型。NULL 值的索引是 NULL 。 空字符串错误值的索引值是 0。- 集合( set ) set(val1, val2, val3.)create table tab ( gender set(男,女,无);insert into tab values (男 , 女);最多可以有 64 个不同的成员。以 bigint 存储，共 8 个字节。采取位运算的形式。 当创建表时， SET 成员值的尾部空格将自动被删除。/* 选择类型 */- PHP 角度1. 功能满足2. 存储空间尽量小，处理效率更高3. 考虑兼容问题- IP 存储

22、 1. 只需存储，可用字符串2. 如果需计算，查找等，可存储为 4 个字节的无符号 int ，即 unsigned1) PHP 函数转换ip2long 可转换为整型，但会出现携带符号问题。需格式化为无符号的整型利用 sprintf 函数格式化字符串sprintf( %u, ip2long( 192.168.3.134 );然后用 long2ip 将整型转回 IP 字符串2) MySQL 函数转换(无符号整型， UNSIGNED)INET_ATON( 127.0.0.1 ) 将 IP 转为整型INET_NTOA( 2130706433 ) 将整型转为 IP/* 列属性(列约束) */ 1. 主键

23、- 能唯一标识记录的字段，可以作为主键- 一个表只能有一个主键。- 主键具有唯一性。- 声明字段时，用 primary key 标识。也可以在字段列表之后声明例： create table tab ( id int, stu varchar (10), primary key (id);- 主键字段的值不能为 null 。- 主键可以由多个字段共同组成。此时需要在字段列表后声明的方法。例： create table tab ( id int, stu varchar (10), age int , primary key (stu, age);2. unique 唯一索引(唯一约束)使得某字段

24、的值也不能重复。3. null 约束null 不是数据类型，是列的一个属性。表示当前列是否可以为 null ，表示什么都没有。null , 允许为空。默认。not null , 不允许为空。insert into tab values (null , val );- 此时表示将第一个字段的值设为 null, 取决于该字段是否允许为 null4. default 默认值属性当前字段的默认值。insert into tab values ( default , val ); - 此时表示强制使用默认值。create table tab ( add_time timestamp default cu

25、rrent_timestamp );- 表示将当前时间的时间戳设为默认值。current_date , current_time5. auto_increment 自动增长约束自动增长必须为索引（主键或 unique ）只能存在一个字段为自动增长。默认为 1 开始自动增长。可以通过表属性 auto_increment = x 进行设置，或 alter table tbl auto_increment = x;6. comment 注释例： create table tab （ id int ） comment 注释内容 ;7. foreign key 外键约束用于限制主表与从表数据完整性。al

26、ter table t1 add constraint t1_t2_fk foreign key （t1d） references t2（id）;- 将表 t1 的 t1_id 外键关联到表 t2 的 id 字段。- 每个外键都有一个名字，可以通过 constraint 指定存在外键的表，称之为从表（子表），外键指向的表，称之为主表（父表）。作用：保持数据一致性，完整性，主要目的是控制存储在外键表（从表）中的数据。MySQL 中，可以对 InnoDB 引擎使用外键约束：语法：foreign key （外键字段） references 主表名 （关联字段 ） 主表记录删除时的动作 主表记录更新时

27、的动作 此时需要检测一个从表的外键需要约束为主表的已存在的值。外键在没有关联的情况下，可以设置为 null. 前提是该外键列，没有 not null 。 可以不指定主表记录更改或更新时的动作，那么此时主表的操作被拒绝。如果指定了 on update 或 on delete ：在删除或更新时，有如下几个操作可以选择：1. cascade ，级联操作。主表数据被更新（主键值更新），从表也被更新（外键值更新）。主 记录被删除，从表相关记录也被删除。2. 主表记录set null ，设置为 null 。主表数据被更新（主键值更新），从表的外键被设置为 null 。被删除，从表相关记录外键被设置成 nu

28、ll 。但注意，要求该外键列，没有 not null 属性约束。3. restrict ，拒绝父表删除和更新。 注意，外键只被 InnoDB 存储引擎所支持。其他引擎是不支持的。/* 建表规范 */ - Normal Format, NF- 每个表保存一个实体信息- 每个具有一个 ID 字段作为主键- ID 主键 + 原子表- 1NF, 第一范式字段不能再分，就满足第一范式。- 2NF, 第二范式满足第一范式的前提下，不能出现部分依赖。消除符合主键就可以避免部分依赖。增加单列关键字。- 3NF, 第三范式满足第二范式的前提下，不能出现传递依赖。某个字段依赖于主键，而有其他字段依赖于该字段。这就

29、是传递依赖。将一个实体信息的数据放在一个表内实现。/* select */ select all|distinct select_expr from - where - group by 合计函数 - having - order by - limita. select_expr- 可以用 * 表示所有字段。select * from tb;- 可以使用表达式(计算公式、函数调用、字段也是个表达式)select stu, 29+25, now() from tb;- 可以为每个列使用别名。适用于简化列标识，避免多个列标识符重复。- 使用 as 关键字，也可省略 as.select stu +10 as add10 from tb;b. from 子句用于标识查询来源。- 可以为表起别名。使用 as 关键字。select * from tb1 as tt, tb2 as bb;- from 子句后，可以同时出现多个表。- 多个表会横向叠加到一起，而数据会形成一个笛卡尔积select *