算术运算函数
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)