mysql基础教程(独家整理).doc

mysql数据库(第1版)

小编：郑保珍

为什么要使用数据库：海量存储高速查询

1.登录与服务：

登录： mysql -uroot -proot (登录本机mysql服务器)
mysql -uroot -proot -h 192.168.10.1 (登录ip为192.168.10.1的mysql服务器)

退出： exit \q quit Ctrl+c

服务：
net stop mysql 停止mysql服务
net start mysql 启动mysql服务
net restart mysql 重启mysql服务
C：> 注意最后没有封号。

2:mysql编码：

set names gbk|utf8 ：临时编码设置，当再次进入mysql中的时候将再次转变为默认编码：
status; \s ：查看编码
show variables like 'charac%' ：查看编码

Server characterset: utf8 服务器编码
Db characterset: utf8 当前数据库编码
Client characterset: utf8 客户编码
Conn. characterset: utf8 客户连接编码

character_set_client | utf8 客户编码
character_set_connection | utf8 连接编码
character_set_database | utf8 当前数据库编码
character_set_results | utf8 结果集
character_set_server | utf8 服务器
character_set_system | utf8 系统

3.database操作：

1 查看和切换：
显示当前所有数据为名称：show databases; show schemas();
查看当前使用的数据库：select database();
切换或进入当前数据库：use db_name;

2 创建：create database|schema [if not exists] db_name [default] character set [=] '字符集';
查看：show create database db_name;
删除：drop database db_name

4.data类型

数值
tinyint 1个字节 有符号：-128 到127 ，无符号：0-255
smallint 2个字节 3.27w正负
mediumint 3个字节 3000w
int 4个字节 21亿正负
int 和 int(5) 都是4个字节 21亿正负，基本无区别。
bigint 8个字节

总结，int(M) zerofill，加上zerofill后M才表现出有点点效果，比如 int(3) zerofill,你插入到数据库里的是10,则实际插入为010,也就是在前面补充加了一个0.如果int(3)和int(10)不加 zerofill,则它们没有什 么区别．Ｍ不是用来限制int个数的．int(M)的最大值和最小值与undesigned有关，如下图：
mysql> create table t (t int(3) zerofill);
mysql> insert into t set t = 10;
mysql> select * from t;
+——+
| t |
+——+
| 010 |
+——+
mysql> create table t (t int zerofill);
mysql> insert into t set t = 10;
mysql> select * from t;
+————+
| t |
+————+
| 0000000010 |
+————+
mysql> create table t (t int);
mysql> insert into t set t = 10;
mysql> select * from t;
+——+
| t |
+——+
| 10 |
+——+
其实当我们在选择使用int的类型的时候，不论是int(3)还是int(11)，它在数据库里面存储的都是4个 字节的长度，在使用int(3)的时候如果你输入的是10，会默认给你存储位010,也就是说这个3代表的 是默认的一个长度，当你不足3位时，会帮你补全，当你超过3位时，就没有任何的影响。他们之间 除了在存储的时候稍微有点区别外，在我们使用的时候是没有任何区别的。int(10)也可以代表 2147483647这个 值int(11)也可以代表。要查看出不同效果记得在创建类型的时候加 zerofill这个值， 表示用0填充，否则看不出效果的。我 们通常在创建数据库的时候都不会加入这个选项，所以可以 说他们之间是没有区别的。

float(M,D) 4或8个字节
M:代表小数的总位数，D代表小数点后的位数Float(6,2) -9999.99到+9999.99
M最大位数10的38次方，D的位数也是10的38次方。
如果M<=24的时候，占4个字节，负责占8个字节。

decimal ‘高精度’浮点数
定点，是把整形部分和小数部分分开存储的，所以精确度比fload精确。
而且fload有时候会出现细微误差。但是如果银行的工资表等也不会用decimal，
它会用int型，或者bigint，把单位定了为分来取消小数存储。
123456789.987456321 把整数部分和小数部分拆开，每9个位占4个字节，所以一共占 8字节

double 8个字节

文本
char(M) [固定长度字符段串]
优点：在查找行记录的时候，如果都是定长，完全可以通过行数和行的长度来计算出来 文件指针的偏移量，加快查询效率
缺点：对于定长N，不论够不够指定长度，实际都占据N个长度，如果不够N个长度， 用空格在尾部补至N个长度
M范围：0到255 之间
varchar(M) [可变长度字符段串]
如果用varchar(M)中，字节数为达到M，不用空格补齐，但列内容前有1-2个字节来标 记该列的内容。
M范围：0到65535之间。utf8 22000左右

注意：char(M),varchar(M)限制的是字符，不是字节。
比如：char(2) charset utf8 能存2个utf8字符，比如“中国”或者’aa’;

text 文本不用加默认值，加了也没用。2万到3万汉子，新闻，论坛中可以使用。
文本类型，可以存比较大的文本段，搜索速度稍慢，因此，如果不是特别大的内容，建议使 用char或varchar来代替

blob 可以原样存储数据。

日期
date 3字节 年月日 1000-01-01 ~ 9999-12-31
time 3字节 时分秒 00:00:00 ~ 23:59:59
datetime 8字节 年月日时分秒 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
timstamp 4字节 年月日时分秒1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
year 1字节 YYYY 1901~2155

ts timestamp default CURRENT_TIMESTAMP
性别
char(1) 女 男
enum('男','女');

2》日期函数：

得到当前日期：curdate(),current_date(),current_date;

得到当前时间：curtime(),current_timie(),current_time;

得到系统日期：now (),sysdate();

3》字符串函数
length(str):字符串长度
password(str):加密字符串
concat(str1,str2...):连接字符串，如果连接的字符串中如果出现NULL，返回结构为NULL

列名称
列类型
默认值
是否主键
id
Int unsigned

PRE
userName
varchar(20)

sex
char(1) /tinyint

weight
tinyint unsigned

birth
date

salary
datetime

lastlogin
datetime

intro
varchar(1500)

这张表不够好，可以优化：

分析：这张表除了userName和intro之外，每一类都是定长，如果我们让所有列都是定长的话，可以极大 提高查询效率。

1. userName 由varchar(20) 变成了char(20)后 ，虽然造成了空间的小量浪费，但是提高了速度。

2. Intro 由varchar(1500) 变成了 char(1500) 后，就不再像userName那样，牺牲小空间来换取高速度了。 所以我们采取其他方式。由于个人简介一旦注册以后，修改的频率也不高，所以我们可以把intro列单独拿出来，放到另一个表里

注意：在开发中，护院的信息优化往往是把频繁用到的信息，有限考虑效率，存储到一张表中。
不常用的信息和比较占据空间的信息，优先考虑空间占用，存储到辅表中。

列名称
列类型
默认值
是否主键
id
Int unsigned

PRE
userName
char(20)

sex
char(1) /tinyint

weight
tinyint unsigned

birth
date

salary
Decimal(8,2)

lastlogin
int unsigned

列名称
列类型
默认值
是否主键
id
Int unsigned

PRE
userName
char(20)

intro
varchar(1500)

下面的每句代码最好不要有空格：

create table `member`(
`id` int unsigned auto_increment primary key,
`username` char(20) not null default '',
`sex` char(1) not null default '',
`weight` tinyint unsigned not null default 0,
`birth` date not null default '0000-00-00',
`salary` decimal(8,2) not null default '0.00',
`lastlogin` int unsigned not null default 0
)engine MyIsam charset utf8;

create table `goods`(
`goods_id` mediumint(8) unsigned not null auto_increment primary key,
`cat_id` smallint(5) unsigned not null default 0,
`goods_sn` varchar(60) not null default '',
`goods_name` varchar(120) not null default '',
`click_count` int(10) unsigned not null default 0,
`goods_number` smallint(5) unsigned not null default 0,
`market_price` decimal(10,2) unsigned not null default '0.00',
`add_time` int(10) unsigned not null default 0,
`is_best` tinyint(1) unsigned not null default 0,
`is_new` tinyint(1) unsigned not null default 0,
`is_hot` tinyint(1) unsigned not null default 0
)engine=MyIsam default charset=utf8;
insert into zhengbaozhen.goods
select goods_id,cat_id,goods_sn,goods_name,click_count,goods_number,market_price,add_time,is_best,is_new,is_hot from ecshop.goods;
5.table操作：
1.创建：
create table 表名(
字段名 数据类型 约束,
字段名 数据类型 约束,
) type|engine='InnoDB|MyISAM' default charset '字符集';
? alter table 查看创建表的帮助

2:查看表结构：
desc 表名
describe 表名
show create table 表名
show columns from table_name;

3.删除：drop table table_name;

DDL：

查看表的存储引擎: show create table 表名
修改表的存储引擎: alter table table_name ENGINE = MyISAM |InnoDB

修改库的编码方式: alter database db_name default character set '字符集'
修改表的编码方式: alter table table_name convert to character set '字符集';

添加字段默认值: alter table table_name alter sex set default 默认值;
删除字段默认值: alter table table_name alter sex drop default;

设置表排序方式：alter table table_name order by 字段名称 asc|desc;

修改auto_increment的值: alter table table_name auto_increment=值;

修改表名：
alter table old_table_name rename new_table_name;
rename table old_table_name to new_table_name;
添加字段:

alter table table_name add 字段1 类型[，add 字段2 类型,.........]
alter table table_name add 字段名称 字段类型 字段属性 [first]|[after 字段名称] ;
注意：如果没有first |after关键字的话，字段默认添加到最后一行。

alter table test2 add email varchar(50) not null default '' ;
alter table test2 add addr varchar(50) ,add flag tinyint(1);
alter table test2 add age tinyint after id;
alter table test2 add sex enum("男","女","保密") first;

修改字段:
alter table table_name change 原列名 新列名 新类型{可以修字段名称}
alter table table_name modify 列名 新类型｛只修改属性｝
alter table table_name modify 字段名称 字段类型 字段属性 [first]|[after 字段名称] ;

删除字段:
alter table table_name drop 列名;

sex加在password后面，之后再删除add
alter table test2 add sex enum("男","女","保密") after password,drop addr;

6.约束和索引：

辅助命令：
show create table table_name \G;
explain select * from table_name where deptno=1;

1.主键（约束+索引）: primary key：
当想用数据库表中的某个字段来唯一标识所有记录时，就可以使用PK约束来进行设置。在数据库表中之所以设置主键，是为了便于数据库管理系统快速地查询到表中的记录。在具体设置主键索引的时候，必须需要满足主键字段的值是唯一，非空的。
单列主键：
创建主键：
create table table_name(
属性名 属性类型 primary key,
.......
[constraint 主键名] primary key(字段名);
[constraint pk_dname ] primary key(dname)
);
添加主键：alter table table_name add primary key(字段);
修改主键：alter table table_name modify 字段 类型 属性 primary key;
删除主键：alter table table_name drop primary key;

多列主键：
create table table_name(
属性名 属性类型 ,
constraint 主键名 primary key(字段名,字段名,字段名,字段名...);
[constraint pk_dname _deptno] primary key(dname,deptno)
);

2.唯一(约束+索引): unique：
所谓唯一索引就是在创建索引时，限制索引的值必须是唯一的。当设置表中的某个字段为“主键”或者“唯一”完整性约束时，系统自动创建关联该字的唯一索引。

1>创建表时创建：
create table table_name(
属性名 数据类型 unique，
deptno int unique,
constraint uk_name unique(字段名)
unique index|key [索引名] (字段名 [长度] [asc|desc])
unique index index_deptno(deptno)

);
2>创建索引：create unique index 索引名 on table_name (属性名 [长度] [asc|desc])
3>修改索引：alter table table_name add unique index|key 索引名称(字段);

3.普通索引：
所谓普通索引，就是在创建索引时，不附加任何限制条件（唯一，非空等限制），该类型的索引可以添加创建在任何数据类型的字段上：

启用索引：alter table table_name enable(启用) keys;
禁用索引：alter table table_name disable(禁用) keys;

查看索引：show indexes from table_name;
删除索引：drop index 索引名称 on table_name;
alter table table_name drop index 索引名称;

单列索引：
1>创建表时：
create table table_name(
属性名 数据类型，
属性名 数据类型，
index|key [索引名] (属性名 [长度] [asc|desc])
index index_deptno(deptno)
);
2>创建索引：create index 索引名 on table_name (属性名 [长度] [asc|desc])
3>修改索引：alter table table_name add index|key 索引名称(字段);

多列索引：
1>创建表时：
create table table_name(
属性名 数据类型，
deptno int,
dname varchar(20),
index|key [索引名] (属性名 [长度] [asc|desc]，
属性名 [长度] [asc|desc])
index|key index_deptno_dname(deptno,dname)
);
2>创建索引：create index 索引名 on table_name (属性名,属性名,属性名)
eg: create index index_deptno_dname on t_dept (deptno,dname)

3>修改索引：alter table table_name add index|key 索引名称(字段1,字段2,字段3........);
eg: alter table test3 add index|key index_deptno_dname(deptno,dname);

4.全文索引：
全文索引主要关联在数据类型为CHAR、VARCHAR和Text的字段上，以遍能够更加快速的查询数据量较大的字符串类型的字段。Mysql3.23版本开始支持全文索引，只能在存储引起为MyISAM的数据库表上创建全文索引。在默认情况下，全文索引的索引方式不区分大小写。如果全文索引所关联的字段为二进制数据类型的话，则区分大小写。
1>创建表时：
create table table_name(
属性名 数据类型，
属性名 数据类型，
FULLTEXT index|key [索引名] (属性名 [长度] [asc|desc])
FULLTEXT index_deptno(deptno)
);
2>创建索引：create FULLTEXT INDEX 索引名 on table_name (属性名 [长度] [asc|desc])
3>修改索引：alter table table_name add FULLTEXT INDEX|KEY 索引名称(字段);

5.自增长(约束)：auto_increment
当数据库表中插入新纪录时，字段上的值会自动生成唯一的ID。在具体设置auto_increment约束的时候，一个数据库表中只能有一个字段使用该约束，该数据类型是整数类型。由于设置auto_increment约束后字段会生成唯一ID，所以该字段也经常设置成PK主键。

create table test7(
id int unsigned not null auto_increment primary key
)auto_increment=100;

修改auto_increment的值: alter table table_name auto_increment=值;

5.外键(约束)：foreign

前面的约束都是针对单个数据表的，外键约束是保证多个表(常为两个表)之间的参照的。比如：有两个表，部门表和雇员表，然后雇员表中有一个表示部门编号的字段deptno，其依赖于部门表的主键，这样字段deptno就是雇员表的外键，通过该字段部门表和雇员表建立了关系。
注意：在具体设置FK约束时，设置FK约束的字段必须依赖于数据库中已经存在的父表的主键。同时外键可以为NULL。

create table table_name(
属性名 数据类型，
[constraint FK_name] foreign key (子表字段) references father_table_name (父表字段)；
);

alter table table_name add [constraint FK_name] foreign key (字字段) references father_table_name (父字段)；

6.外键(约束)：NULL

问题：在见表的时候，列后面not null default ‘’ , default 0 这里的not null 是什么意思呢？
答案：就是让这个列的值不为null。 如果某个列确实没有填值，也有默认值，也不为null。

问题：为什么不希望这里的值为null呢？
答案：不好比较，null本身是一种类型，比较式只能用专门的is null 和is nut null 来比较，遇到运算符一 律返回null。假设记录有null， 比如select * from books where name=null ，这个时候也无法取到任 何记录。而且null影响索引的效率，也就影响查询的效率。
select * from books where name is not null ：这个时候会把所有name不是null的记录取出来
select * from books where name is null ：这个时候会把所有name是null的记录取出来

问题：null和空字符 ,”” 有什么去别呢？
答案：null就是null，判断是不是null有专门的 is not null 和not null。就像去银行的话，连卡都没有。
“ ” 空字符 ，没有内容，就像去银行，有卡但是没有钱。

7.DML:

添加记录：
insert [into] table_name[(字段名称...)] values(字段值...);
insert [into] table_name[(字段名称...)] values(字段值...),(字段值...);

insert [into] table_name select 字段名称... from table_name1;

insert table_name[(字段名称)] select *|字段名称 from table_name

insert into database_name1.table_name1
SELECT 字段1，字段二，，，，from database_name2.table_name2;

修改记录：
update table_name set 字段名称=值... [where] [order by] [limit];

删除记录：
delete from table_name [where] [order by] [limit] 慢: 删记录空间保留
truncate table table_name; 稍快:删除记录空间回收;
alter table table_name auto_increment =1;
8.DQL：

单表查询操作：

简单数据查询：
所有字段: select * from table_name;
查询特定字段和其他剩余字段：select other,字段 from table_name;
指定字段: select 字段1,字段2 from table_name;
关键字distinct去除重复记录 : select DISTINCT 字段[ ,字段 ] from table_name;
四则运算(+ - * / %)查询: select name ,sal*12 from t_employee;
设置显示格式数据查询，关键字CONCAT链接字符串：
select CONCAT(ename,’雇员的年薪为：’,sal * 12) as yearsalary from tabale_name;

select * from emp where hiredate > '1998-01-01';
select * from emp where year(hiredate)>'1998';
select year('2000-01-11');
条件数据查询：
between ...and：
select * from test where age>=50 and|&&|or| || age<=60;
select * from test where age between 50 and 60;

not ...between：
select * from test where age not between 30 and 90;

IS NULL :判断值是否为NULL
select field1 ,field2 from table_name where filed IS NULL ;
注意：如果field的值为NULL则为真，满足条件被记录下来。

IS NOT NULL :判断值是否不为NULL
select field1 ,field2 from table_name where filed IS NOT NULL ;

in:在一个范围内
select sal from test where id in(1,3,5) 相当于
select sal from test where id =1 or id=3 or id=5;

not in:不在某一范围
select * from test wehre id not in(1,32,43) 相当于
select sal from test where id !=1 and id!=3 and id!=5;

注意：在使用关键字in时，查询的结果集中如果存在null，则不会影响查询。
在使用关键字not in时，查询的结果集中如果存在null，则不会有任何结果即为Empty set。
select ename from t_employee where empno in (12,14,111,145,null); 有应有的结果
select ename from t_employee where empno not in (12,14,111,145,null); 结果为空Empty

like：包含模糊字符串的查询：
select ename from t_employee where ename like ‘AA%’; 查询包含AA开头的，后面任意长度字符
select ename from t_employee where enmae like ‘_AA’; 查询包含以AA结尾，前面一个字符

not like: 没有包含模糊字符串的查询
select ename from t_employee where ename not like ‘%A%’; 查询字段ename中没有A的数据
注意：
_: 通配符，该通配符值能匹配单个字符
%: 该通配符值可以匹配任意长度的字符串，即可以是0个字符，1个字符，也可以很多个字符

排序数据查询：

select field1,field2....from table_name where condition order by filed1 [asc|desc][,filed2 [asc|desc]]

限制数量查询：
select field1,field2....from table_name where condition limit [ offset_start,] row_count;
注意：当不指定初始位置offset_start时，默认值为0，表示从第一条记录开始显示。

聚合函数查询：
select count(*) from 表名; 查询的就是绝对的行数不会忽略你的NULL字段，哪怕某一行所有字段全部为null也计算在内，数的就是所有实际存在的行数。

select count(字段) from 表名; 会忽略你的NULL字段，查询的是该列不为null的所有行数。

当然如果要查询一个表的所有行数即记录数的话，可以有两种方法：

select count(1) 或者 select count(*) from 表名；那个好呢？

对于MyISAM引擎的表，没有区别的，因为在这种引擎内部有一个计数器在维护着行数，即随时 都 保存着这个行数，在用上面的任何一种方式取得总行数需要的时间几乎为零。
对于InnoDB引起的表，可以用select count(1) from 表名。但是如果用select count(*) 的话，效率会 很低，因为Innodb真的要去数一遍，如果数据量大的话可能让数据库奔溃。

select sum(字段) from 表名;
select avg(字段) from 表名;
select max(字段) from 表名;
select min(字段) from 表名;

分组数据查询：
select function() from table_name group by filed
with rollup:对聚合函数的结果进行汇总

GROUP_CONCAT() 可以把每组中某个字段包含的成员显示出来：
select id ,group_concat(name) names from t_employee group_by id;

技巧:
在select 的a,b 列，必须出现在group by a,b,c 列中，或者写聚会函数。语义上才不会矛盾。
select cat_id, avg(shop_price) as avgPrice from goods group by cat_id; 正确写法
select id ,avg(shop_price) as avgPrice from g