Mysql笔记

浏览数：30 / 时间：2015年06月12日

Mysql笔记

PHP和MySQL合作的方式：

a) PHP是作为MySQL的客户端

b) PHP向MySQL服务器发送一些sql语句，来操作mysql服务器当中的数据

SQL语言分为四个部分：

a) DDL（定义语言）：

建库、建表、修改表等操作

b) DML（操作语句）：

增加数据、删除数据、修改数据

c) DQL（查询语句）：

查询数据

d) DCL（控制语句）：

GRANT、REVOKE、COMMIT、ROLLBACK
Mysql客户端登陆：

a) Mysql –u用户名 –p密码 [数据库名称] –h192.168.170.250

查看当前数据库服务器下的所有数据库：

a) Show databases;

选择到某一个数据库下：

a) Use test;

查看当前所在的数据库：

a) Select database();

查看当前数据库下的所有的数据表：

a) Show tables;

查看帮助信息：

a) Help

\c 取消当前输入的未完成的命令
\d 临时修改执行符
\G 将查询出的数据格式化（立起来）
\q 退出 exit ctrl+c quit
\t 取消tee
\T 将命令记录到外部文件当中
\s 获取服务器的信息
SQL语言需要注意的问题：

a) 需要使用分号执行

b) 可以使用折行，最后加分号才执行

DDL语句：

a) 建立数据库：

CREATE DATABASE [IF NOT EXISTS] 数据库名称
1. 注意：数据库名称尽量小写（一个数据库会产生一个文件夹，对于Linux，文件夹和文件是严格区分大小写的）
2. 命令不区分大小写
3. 建立数据表：
  1. 建立数据表之前，一定要选择数据库
  2. 注意：

a) 数据表名称小写

Create table bbs_user(
Id int unsigned not null auto_increment primary key,
Username varchar(32)
)engine=myisam default charset=utf8;
删除数据表：
1. Drop table 数据表名称
2. 删除数据库：
  1. Drop database [IF EXISTS] 数据库名称
  2. 注意：

a) 删除结构化的内容，我们使用drop语句

b) 删除数据，我们使用delete语句

修改表：
1. Alter table 表名称具体的操作
数据表的列类型：

a) 数值类：

Tinyint
Smallint
Mediumint
Int IP地址时间戳主键ID 年龄性别等
Bigint
Float
Double
Decimal
注意：
1. 整型后面的数字可以省略，默认情况下为10
2. 如果指定了整型的长度，该长度不会截取，不影响取值范围，只会影响现实范围（只配合零填充的时候有效）
3. Float和double后面的数字会影响精度，float(3,1)整体长度最大为3位，超出3位，会以最大值99.9来存储，1代表的是小数点后保留一位小数

登陆MySQL客户端：

a) Mysql –uroot –p123 [数据库名称] –hlocalhost

查看当前数据库服务器下的所有数据库：

a) Show databases;

选择数据库：

a) Use 数据库名称;

查看所有数据表：

a) Show tables;

查看当前所在数据库：

a) Select database();

查看帮助信息：

a) Help

\c 取消之前输入的命令
\q 退出
\G 将输出的结果格式化，将表立起来
\t 取消tee
\T 将以下的所有记录到外部文件当中
\s 获取服务器相关信息
\d 更改执行符
创建数据库：

a) Create database [IF NOT EXISTS] 数据库名称;

删除数据库：

a) Drop database [IF EXISTS] 数据库名称；

创建数据表：

a) Create table 数据表名称(

b) Id int unsigned not null auto_increment primary key,

c) Username varchar(32) not null default ‘admin’,

d) Password char(32) not null default ‘123’

e) )engine=myisam default charset=utf8;

修改数据表：

a) Alter table 数据表名称操作

删除数据表：

a) Drop table 数据表名称

数据字段的类型：

a) 数值类型：

整型
1. Tinyint
2. Smallint
3. Mediumint
4. Int
5. Bigint
6. 注意：

a) Int(10) 后面的数字不会影响精度，只配合zerofill零填充来使用才有效，如果使用了零填充，而我们存储的数字的显示位数不够10位，用0填充到十位，如果插入的值超过10位，不会影响精度

浮点类型
1. Float
2. Double
3. Decimal
4. 注意：

a) Float(3,1) 整体会截取到3位，99.9，1代表保留一位小数

b) 字符串类型：

Char 字符串类型定长 2000左右
Varchar 字符串类型可变长度（只能缩短，不能扩大） 65533
Char和varchar的区别：
1. Char的最大取值范围较varchar小一些
2. Char是定长，varchar是可变长度，如果char当中存入的实际字符串的长度不到指定长度，会自动以空格补全至指定长度，如果varchar当中存入的实际字符串的长度不到指定长度，不会自动补全，反之，会缩小至实际长度
3. Char和varchar存入的实际字符串的长度如果超出指定长度，同样都会被截取
4. Char的效率更高于varchar，但是占用空间更大
5. Tinyblob
6. Tinytext
7. Blob text
8. Mediumblob, mediumtext
9. Longblob longtext
10. 注意：
  1. Blob重点存储数据对象，一些文件
  2. Text重点存储文本内容
  3. Enum(‘val1’,’val2’,’val3’…..) 最大成员 65535个
  4. Set(‘val1’,’val2’…..) 最大成员64个
  5. 注意：
    1. 效率较高，占用空间较大
    2. Enum只能选一个，set可以选多个
时间和日期类型：

a) Date

b) Time

c) Datetime

d) Timestamp

e) Year

NULL值：

a) 什么都没有

MySQL的字段属性：

a) Unsigned

存入的数值类型不能为负数，取值范围会跟着改变
Tinyint -128-127 0-255

b) Zerofill

零填充，配合整型后面的数字使用

c) Auto_increment

自增列，需要注意的是，使用此属性，必须要给该字段设置索引

d) Null和not null

Not null 该字段不能存入空值，只有配合default属性使用才有意义

e) Default

默认值，如果没有存入值，以默认值存储，通常配合not null使用
MySQL的索引：

a) 什么是索引？

快速定位的工具，相当于是书的目录
索引是创建在字段上的

b) 索引有几种？

主键索引
唯一索引
常规索引
全文索引
共同点：都可以提高查询速度
区别：
1. 主键索引：

a) 一张表只允许有一个主键

b) 唯一性

唯一索引：

a) 唯一性

常规索引：

a) 只是简单的定位

全文索引：

a) 针对text类型创建，不使用全文索引，将来会用sphinx（全文检索引擎）替代

索引创建的方式：
1. 建表的时候在字段后直接加索引

a) Create table t1(

b) Id int unsigned not null auto_increment primary key,

c) Username varchar(32) unique,

d) Password char(32) index

e) )engine=myisam default charset=utf8;

在建表的时候，在所有字段后加索引

a) Create table t2(

b) Id int unsigned not null auto_increment,

c) Username varchar(32),

d) Password char(32),

e) Primary key(id),

f) Unique(username)，

g) Index/key(password)

h) )engine=myisam default charset=utf8;

创建表完成以后，对数据表进行修改：

a) Alter table t3 add index 索引名称(字段)

b) Alter table t3 add unique 索引名称（字段）

c) Alter table t3 add primary key(字段)

ALTER语句：

a) 修改表：

修改表名称：
1. Alter table t5 rename to t5_new
2. 修改字段：
  1. 增加字段

a) Alter table t4 add column rtime int unsigned not null default 0;

修改字段属性

a) Alter table t4 modify rtime int not null default 0;

b) Alter table t4 change rtime ptime int not null default 0;

c) 注意：

Modify无法更改字段的名称
change可以更改字段名称
删除字段

a) Alter table t4 drop column 字段名称

修改索引：
1. 增加索引：

a) Alter table t6 add index 索引名称(字段名)

b) Alter table t6 add unique 索引名称（字段名称）

c) Alter table t6 add primary key(字段名称)

删除索引：

a) Alter table t6 drop index 普通索引的名称

b) Alter table t6 drop index 唯一索引的名称

c) Alter table t6 drop primary key;

d) 注意：

删除主键之前，需要将auto_increment属性删除掉，因为auto_increment属性需要依附在一个索引上
查看索引：

a) Show indexes from 表名称

数据表类型：

a) 在建表的最后使用engine来指定表类型

b) 数据表类型有很多，我们经常用的只有myisam和innodb

c) Myisam和innodb的区别：

Myisam更成熟，更稳定，更易于管理
Myisam会产生碎片空间，innodb不会产生，myisam需要经常性的使用optimize来优化表
Myisam不支持事务处理，innodb支持
Myisam不支持外键索引，innodb支持
Myisam效率更高
Myisam会产生三个文件，表名称.frm(存储表结构)，表名称.MYD(存储表数据)，表名称.MYI(存储表索引)，innodb只会产生一个文件表名称.frm（存储表结构），innodb的表数据在ibdata这个文件当中存储（共享表空间，所有的innodb表的数据和索引全部存在这里）
字符集：

a) 数据库服务器字符集

b) 数据库字符集

c) 数据表字符集

d) 数据字段字符集

e) 客户端字符集

Php作为客户端
1. 对php文件设置utf-8无bom的字符集
2. Header(‘content-type:text/html;charset=utf-8’)
3. Html页面也必须是utf-8字符集
4. <meta http-equiv=”content-type” content=”text/html;charset=utf-8” />
5. Set names utf8

f) 连接字符集

g) 校验字符集

SQL语句分为四种：

a) DDL 定义语句

Create
Alter
Drop

b) DML语句（操作语句，增删改）

Insert
1. Insert into t1(id,username,password) values(1,’zhangsan’,’123’);

a) 把所有的字段列出来，values对应的值的顺序要与前面字段的一致，类型也要一致

Insert into t1(id,username,password) values(null,’lisi’,’234’);

a) Id如果是自增列，values对应的值可以使用null

Insert into t1(username,id,password) values(‘wangwu’,null,’345’);

a) 前面字段的顺序可以打乱，但是后面的values对应的值得顺序依然要与前面字段的顺序一致

Insert into t1(username,password) values(‘zhaoliu’,’456’);

a) Id列可以省略，此时id列依然会自增

Insert into t1(username) values(‘tianqi’);

a) Password列可以省略，但是会插入null值，如果password列有not null属性，会插入空字符串，通常情况下，not null会配合default属性来使用，当没有插入password时，会按照默认值插入

Insert into t1 values(null,’zhaoba’,’678’);

a) 前面的字段可以不写，但是values后面需要把所有字段对应的值都写上，并且按照字段的顺序来插入值

Insert into t1(username,password) values(‘zhangsan’,’123’),(‘lisi’,’345’),(‘wangmazi’,’678’);

a) 可以一次性插入多个值，这样效率较高，mysql只会解析一次sql语句的语法

Delete
1. Delete from t1;

a) 删除语句，后面如果没有where子句，会将所有的记录全部删除掉，强烈要求大家把where条件加上

Delete from t1 where id=14;

a) 可以使用where子句来对删除的记录进行筛选

Delete from t1 order by id desc limit 2;

a) 删除id最大的两条记录

Update
1. Update t1 set username=’xiaoqingxin’;

a) 如果没有where子句，会将当前表当中的所有记录全部更新掉，非常不安全，强烈要求大家把where子句加上

Update t1 set username=’wangwu’ where id=3;

a) 根据where条件筛选后的记录进行更改（找到id=3的记录，将username进行更改）

Update t1 set username=’pijiu’ where id>8;

a) Where条件可以有多样性

Update t1 set username=’wangmazi’,password=’222’ where id>8;

a) 可以同时更改多个字段，用逗号（,）隔开

Update t1 set username=’xiaoqingxin’,password=’444’ order by id desc limit 3;

a) Order by代表排序，limit代表限制条数，这句话的意思是：以id降序排序，只将最大的3条进行修改。

c) DQL语句（查询语句）

Select
1. Select [all|distinct] [表名.]字段名称 [as 字段的别名]|* from 表名 [where子句][group by][having][order by][limit];

a) Select * from user;

没有where子句，代表显示所有记录，*代表显示所有字段

b) Select username,age,sex from user;

只显示username，age和sex字段

c) Select username as u from user;

可以给username别名为u，使用as来给别名

d) Select username u from user;

定义别名的as可以省略，用一个空格隔开也是可以的

e) Select user.username from user;

可以使用表名.字段名来显示字段

f) Select u.username from user as u;

同样可以使用as给表名加别名

g) Select u.username from user u;

As同样可以省略

h) Select all age from user;

查询出所有记录的age字段的内容

i) Select distinct age from user;

Where
1. 查询出不重复的age内容

a) Select * from user where age>18;

查询age大于18的记录

b) Select * from user where age>18 and username=’wangwu’;

查询age大于18并且username为王五的用户

c) Select * from user where age=18 or age=35;

查询age等于18的或者是age等于35的用户的记录

d) Select * from user where age!=20;

查询age不等于20的用户

e) Select * from user where age is null;

查询age为null的记录

f) Select * from user where age is not null;

查询age不为null的记录

g) Select * from user where age between 20 and 30;

查询age在20到30之间的记录，等价于age>=20 and age<=30

h) Select * from user where username like ‘zhang%’;

查询username以zhang开始的记录，这里的%是通配符，通配符还是_,%代表任意个任意字符，_代表一个任意字符

i) Select * from user where id in(1,3,5,7);

查询id为1,3,5,7的用户，等价于id=1 or id=3 or id=5 or id=7

j) Select * from user where id not in(1,3,5,7);

查询id不为1,3,5,7的用户
Group by

a) Select age,count(*) from user group by age;

以age的值进行分类，每种值分为不同的类，配合count(*)，统计每种类的记录的个数，这样我们就可以完成对不同年龄段的会员的个数的统计，group by在配合聚合函数的时候，才有更大的意义

b) Select age,count(*) from user where age>18 group by age;

查询出age大于18的每个年龄段的记录的个数

c) Select age,count(*) as c from user where age>18 group by age having c>1;

对分组后的数据进行二次筛选，having的用法和where的用法一模一样，只是进行二次筛选
Order by

a) 排序

Select * from user order by age desc;
1. Desc代表降序排序，asc代表升序排序，默认情况下即为升序
2. Select * from user order by age desc , id asc;
  1. 先对age进行降序排序，如果age有相同的，再对id进行升序排序。
  2. Select * from user order by rand();
    1. 随机排序
    2. Limit