算术运算函数

sum()求和

mysql> select sum(price) from books;

+------------+

| sum(price) |

+------------+

| 10048 |

+------------+

1 row in set (0.00 sec)

avg()平均值

 求书籍id小于3的所有书籍的平均价格

mysql> select avg(price) from books where bid<=3;

+------------+

| avg(price) |

+------------+

| 39.3333 |

+------------+

1 row in set (0.00 sec)

max()最大值

min()最小值

 

count()统计记录数

统计价格大于40的所有书籍的数量

mysql> select count(*) from books where price>40;

+----------+

| count(*) |

+----------+

| 43 |

+----------+

1 row in set (0.00 sec) 

mysql> select count(distinct price) from books where price>40;

+-----------------------+

| count(distinct price) |

+-----------------------+

| 26 |

+-----------------------+

1 row in set (0.00 sec)

 

算数运算 +- * /

 

给所有价格小于40元的书籍涨价5元

mysql> update books set price=price+5 where price <40;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

给所有价格高于70元的书籍打8折

mysql> update books set price=price*0.8 where price>70;

Query OK, 10 rows affected (0.00 sec)

Rows matched: 10 Changed: 10 Warnings: 0

 

字段修饰符（约束）null 和not null

not null的字段是不能插入null的，只能插入空值或其他内容，空值不占用空间，而null占用额外空间。

not null 比null的效率更高，因为在索引中如果含义null，索引不会存储null的空间，所以索引中的null效率会变慢。

mysql> select * from test;

+------+------+

| col1 | col2 |

+------+------+

| | NULL |

| 1 | 2 |

| | 2 |

| | 2 |

| adb | NULL |

+------+------+

5 rows in set (0.00 sec)

mysql> select * from test where col1 is not null;   　　　#not null也包含空值

+------+------+

| col1 | col2 |

+------+------+

| | NULL |

| 1 | 2 |

| | 2 |

| | 2 |

| adb | NULL |

+------+------+

5 rows in set (0.00 sec)

mysql> select * from test where col1 <> ''; 　　　　#其中“<>”代表不等于

+------+------+

| col1 | col2 |

+------+------+

| 1 | 2 |

| adb | NULL |

+------+------+

2 rows in set (0.00 sec)

 

DEfault 默认字段

mysql> create table test2(name varchar(8) not null,dept varchar(25) default 'sister');

Query OK, 0 rows affected (0.03 sec)

mysql> desc test2;

+-------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| name | varchar(8) | NO | | NULL | |

| dept | varchar(25) | YES | | sister | |

+-------+-------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

mysql> insert into test2(name) values('abc');

Query OK, 1 row affected (0.01 sec)

mysql> select * from test2;

+------+--------+

| name | dept |

+------+--------+

| abc | sister |　　　　　　　　　　　　#default默认字段自动加上，无需手写

+------+--------+

1 row in set (0.00 sec)

 

 AUTO_INCREMENT 字段约束 自增长

mysql> create table test3(id int not null auto_increment primary key ,label varchar(20) not null);

mysql> desc test3;

+-------+-------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+----------------+

| id     | int(11) | NO| PRI  | NULL  | auto_increment |

| label | varchar(20) | NO |   | NULL  |         |

+-------+-------------+------+-----+---------+----------------+

2 rows in set (0.00 sec)

mysql> insert into test3(label) values('abc');

mysql> insert into test3(label) values('abcd');

 

mysql> insert into test3 values(9,'abc');

mysql> select * from test3;

+----+-------+

| id | label |

+----+-------+

| 1 | abc |

| 2 | abcd |

| 9 | abc |

+----+-------+

3 rows in set (0.00 sec)

mysql> insert into test3(label) values('abcff');

Query OK, 1 row affected (0.05 sec)

mysql> select * from test3;

+----+-------+

| id | label |

+----+-------+

| 1 | abc |

| 2 | abcd |

| 9 | abc |

| 10 | abcff |

+----+-------+

4 rows in set (0.00 sec)

 

mysql> delete from test3;

Query OK, 3 rows affected (0.01 sec)

mysql> select * from test3;

Empty set (0.00 sec)

mysql> insert into test3(label) values('dosg');

Query OK, 1 row affected (0.01 sec)

mysql> select * from test3;

+----+-------+

| id | label |

+----+-------+

| 9 | dosg |　　　　　　　　#默认从上次的值往后计算

+----+-------+

1 row in set (0.01 sec)

mysql> delete from test where col1='1';

Query OK, 0 rows affected (0.00 sec)

 

 

mysql> truncate test3;　　　　　　　　　　　#截断表，默认表内容全丢失，下次插入新字段从1开始计算

Query OK, 0 rows affected (0.01 sec)

mysql> insert into test3(label) values('xuesheng');

Query OK, 1 row affected (0.01 sec)

mysql> select * from test3;

+----+----------+

| id | label |

+----+----------+

| 1 | xuesheng |

+----+----------+

1 row in set (0.01 sec)

 

索引是一种特殊的文件（InnoDB数据表中的索引是表空间的一个组成部分），包含对数据表里所有记录的引用指针。相当于一本书里面的目录，加快数据库查询速度。

缺点：索引是以文件存储的，索引过多，占用磁盘空间较大。影响insert，update，delete的执行时间。

索引数据必须与数据表数据同步，如果索引过多，当数据表数据更新时，索引也要同步更新，这就降低了效率。

索引类型 

普通索引：  不具备唯一性，只是为了提高查询速度

mysql> create table test6(id int(4),name varchar(20),pwd varchar(20),index(pwd));　　　　#index(pwd)中index为索引，pwd为需要索引的列；还可以写成key(pwd)

Query OK, 0 rows affected (0.02 sec)

mysql> desc test6;

+-------+-------------+------+-----+---------+-------+

| Field　　 | Type　　 | Null　　 | Key　　 | Default 　　| Extra |

+-------+-------------+------+-----+---------+-------+

| id |　　 int(4) 　　 | YES　　   | 　　       | NULL |　　 |

| name　 | varchar(20)| YES      | 　　       | NULL | 　　 |

| pwd　　 | varchar(20) | YES    | MUL 　  | NULL |　　 |　　　　　　　　#Key为MUL表示为普通索引

+-------+-------------+------+-----+---------+-------+

3 rows in set (0.01 sec)

 

mysql> create table test7(id int(4),name varchar(20),pwd varchar(20),index Chen (pwd));　　#指定pwd列的索引名称为Chen

mysql> show create table test7; 

| Table | Create Table |

+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| test7 | CREATE TABLE `test7` (

`id` int(4) DEFAULT NULL,

`name` varchar(20) DEFAULT NULL,

`pwd` varchar(20) DEFAULT NULL,

KEY `Chen` (`pwd`)　　　　　　　　　　　　　　#指定pwd的索引名称为Chen，默认不指定为列名

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.01 sec)

 

主键索引 primary

mysql> create table demo(id int(4) not null auto_increment,name varchar(20) default null,primary key(id));  　　#主键设置key（id）

Query OK, 0 rows affected (0.01 sec)

mysql> desc demo;

+-------+-------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+----------------+

| id | int(4) | NO | PRI | NULL | auto_increment |　　　　　　　　#key 为PRI主键模式

| name | varchar(20) | YES | | NULL | |

+-------+-------------+------+-----+---------+----------------+

2 rows in set (0.00 sec)

 

 

mysql> create table test12(id int(4) not null ,name varchar(20)default null,key chen(name));

Query OK, 0 rows affected (0.07 sec)

mysql> show create table test12;

+--------+-----------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table |

+--------+-----------------------------------------------------------------------------------------------------------------------------------------------+

| test12 | CREATE TABLE `test12` (

`id` int(4) NOT NULL,

`name` varchar(20) DEFAULT NULL,

KEY `chen` (`name`)　　　　　　　　　　　　　　　　　　　　　　　　　　#创建索引，并设置索引名称为chen

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

+--------+-----------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

mysql> alter table test12 drop key name;　　　　　　　　　　　　　　　　#删除索引字段，无法删除

ERROR 1091 (42000): Can't DROP 'name'; check that column/key exists

mysql> alter table test12 drop key chen;　　　　　　　　　　　　　　　　#删除索引名称，可以删除

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> show create table test12;

+--------+------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table |

+--------+------------------------------------------------------------------------------------------------------------------------+

| test12 | CREATE TABLE `test12` (

`id` int(4) NOT NULL,

`name` varchar(20) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

+--------+------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

 

mysql> alter table test12 add key wang(id);　　　　　　　　　　　　#增加索引，给id字段增加索引名称为wang

Query OK, 0 rows affected (0.05 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> show create table test12;

+--------+---------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table |

+--------+---------------------------------------------------------------------------------------------------------------------------------------------+

| test12 | CREATE TABLE `test12` (

`id` int(4) NOT NULL,

`name` varchar(20) DEFAULT NULL,

KEY `wang` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

+--------+---------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

 

唯一性索引 unique

mysql> create table test13(id int(4) auto_increment primary key,uname varchar(20),up varchar(20),unique index(uname));

Query OK, 0 rows affected (0.04 sec)　　　　　　　　　　　　　　　　#对uname字段做唯一性索引

mysql> desc test13;

+-------+-------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+----------------+

| id | int(4) | NO | PRI | NULL | auto_increment |

| uname | varchar(20) | YES | UNI | NULL | |　　　　　　　　　　　　#唯一性索引，字段内容不能重复，具有唯一性

| up | varchar(20) | YES | | NULL | |

+-------+-------------+------+-----+---------+----------------+

3 rows in set (0.00 sec)

mysql> alter table test13 drop key uname;　　　　　　　　　　　　#删除索引

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table test13 add unique chen(uname);　　　　　　　　#增加唯一性索引，字段为uname，索引名称为chen

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> show create table test13;

+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table |

+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| test13 | CREATE TABLE `test13` (

`id` int(4) NOT NULL AUTO_INCREMENT,

`uname` varchar(20) DEFAULT NULL,

`up` varchar(20) DEFAULT NULL,

PRIMARY KEY (`id`),

UNIQUE KEY `chen` (`uname`)　　　　　　　　　　　　　　　　　#修改唯一性索引后的名称

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

 

mysql> alter table test13 change id id int(4) not null;　　　　　　　#删除auto_increment类型，使用change

Query OK, 0 rows affected (0.10 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc test13;

+-------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| id | int(4) | NO | PRI | NULL | |

| uname | varchar(20) | YES | UNI | NULL | |

| up | varchar(20) | YES | | NULL | |

+-------+-------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

mysql> alter table test13 drop primary key;　　　#删除主键类型　

Query OK, 0 rows affected (0.04 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc test13;

+-------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| id | int(4) | NO |    | NULL | |　　　　　　　　　　#主键类型为空，已删除

| uname | varchar(20) | YES | UNI | NULL | |

| up | varchar(20) | YES | | NULL | |

+-------+-------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

 

复合索引

mysql> create table firewall(host varchar(15) not null, port smallint(4) not null, access enum('deny','allow') not null,primary key(host,port));

Query OK, 0 rows affected (0.01 sec)　　　　　　　　　　　　　　#主键有2个，分别为host和port字段

mysql> desc firewall;

+--------+----------------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+--------+----------------------+------+-----+---------+-------+

| host | varchar(15) | NO | PRI | NULL | |

| port | smallint(4) | NO | PRI | NULL | |

| access | enum('deny','allow') | NO | | NULL | |

+--------+----------------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

mysql> insert into firewall values('192.168.2.1',22,'allow');

Query OK, 1 row affected (0.01 sec)

mysql> insert into firewall values('192.168.2.1',23,'deny');　　　　#这里host和port作为一个整体，只有这个整体信息一样，才被认为是内容相同不能插入

Query OK, 1 row affected (0.00 sec)

 

外键约束 设定表与表之间的约束关系on delete cascade on update cascade 表示级联更新和级联删除

例如有2张表，一张用户表，一张订单表，用户表里的用户删除了，订单表里的用户就成了无头数据。所以需要使用外键约束来定义2张表之间的关联关系，保障第一张表改变的同时第二张表也跟着改变。

mysql> create table `user`(id int(11) not null auto_increment,name varchar(50) not null default '',sex int(1) not null default '0',primary key(id))ENGINE=innodb;

Query OK, 0 rows affected (0.01 sec)

mysql> create table `order`(o_id int(11) auto_increment,u_id int(11) default '0',username varchar(50),money int(11),primary key(o_id),index(u_id),foreign key order_f_key(u_id) references user(id) on delete cascade on update cascade)ENGINE=innodb;

Query OK, 0 rows affected (0.01 sec)

mysql> insert into `order`(u_id,username,money) values(1,'HA',234),(2,'LB',146),(3,'HPC',256);

Query OK, 3 rows affected (0.01 sec)

Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from `order`;

+------+------+----------+-------+

| o_id | u_id | username | money |

+------+------+----------+-------+

| 1 | 1 | HA | 234 |

| 2 | 2 | LB | 146 |

| 3 | 3 | HPC | 256 |

+------+------+----------+-------+

3 rows in set (0.00 sec)

mysql> delete from user where id=1;　　　　　#删除user表中id=1的行

Query OK, 1 row affected (0.06 sec)

mysql> select * from `order`;　　　　　　　　#删除了user表后，order表自动进行了更新

+------+------+----------+-------+

| o_id | u_id | username | money |

+------+------+----------+-------+

| 2 | 2 | LB | 146 |

| 3 | 3 | HPC | 256 |

+------+------+----------+-------+

2 rows in set (0.01 sec)

 

视图 view  经常需要查看几个固定表时，可以使用视图关联这几个表，以后只查看该视图就能够找到这几个表中的内容，不用单独去查看每一张表。视图是存在于数据库中的虚拟表

table1 table2 table3 >>      select * from view1;book

mysql> create view bc as select b.bName,b.price,c.bTypeName from books as b left join category as c on b.bTypeId=c.bTypeId;

Query OK, 0 rows affected (0.01 sec)　　　　　　　　b和c为别名，b表示books，c表示category    其中left join表示左连接

mysql> select * from bc;　　　　　　　　　　　　　　#通过视图查看表信息

+---------------------------------------------------------+-------+---------------+

| bName | price | bTypeName |

+---------------------------------------------------------+-------+---------------+

| 网站制作直通车 | 39 | 网站 |

| 黑客与网络安全 | 41 | 黑客 |

| 网络程序与设计－asp | 43 | 网站 |

| pagemaker 7.0短期培训教程 | 43 | 平面

+---------------------------------------------------------+-------+---------------+

4 rows in set (0.07 sec)

mysql> select * from bc where price<40;

+-----------------------+-------+-----------+

| bName | price | bTypeName |

+-----------------------+-------+-----------+

| 网站制作直通车 | 39 | 网站 |

+-----------------------+-------+-----------+

1 row in set (0.00 sec)

 

mysql> alter view bc as select b.bName,b.publishing,c.bTypeId from books as b left join category as c on b.bTypeId=c.bTypeId;

Query OK, 0 rows affected (0.01 sec)　　　　　　　　#修改视图信息

mysql> update bc set bName='HA' where price=39;　　　　　#不能更改原因，修改了视图列信息，只有bName,b,publishing,而没有　　　

ERROR 1288 (HY000): The target table bc of the UPDATE is not updatable

mysql> drop view bc;　　　　　　　　　　　　　　　#删除视图

Query OK, 0 rows affected (0.00 sec)