首页 > 代码库 > mysql语句进阶
mysql语句进阶
1.null
mysql> create table worker(id int not null,name varchar(8) not null,pass varchar(20) not null);
mysql> insert into worker values(1,‘HA‘,‘123456‘);
mysql> insert into worker values(1,‘LB‘,null);
ERROR 1048 (23000): Column ‘pass‘ cannot be null 不能为null
mysql> insert into worker values(2,‘HPC‘,‘‘);
mysql中的NULL是占用空间的。是一个数值。 null 和‘‘是不同的。
2.default 设定字段的默认值
mysql> create table test2(name varchar(8) not null,dept varchar(25) default ‘SOS‘);
mysql> insert into test2 (name) values (‘kko‘);
如果时间字段，默认为当前时间，插入0时，默认为当前时间。
如果是enum 类型，默认为第一个元素。
3.auto_increment字段约束
对于主键，这是非常有用的。可以为每条记录创建一个惟一的标识符（最大值加1）
mysql> create table items ( id int not null auto_increment primary key , label varchar(20) not null);
mysql> insert into items (label) values (‘aaba‘);
mysql> insert into items values (9,‘aaba‘);
再插入一条id将为多少
mysql> insert into items (label) values (‘abc‘);
delete 不能清除auto_increment
mysql> delete from items;
mysql> insert into items (label) values ("aaaa");
方法二：删除表中所有记录，清auto_increment 值。
mysql> truncate table items;
mysql> insert into items(label)values(‘abv‘);
4.索引
为了加快搜索速度，减少查询时间 。
1 索引是以文件存储的。如果索引过多，占磁盘空间较大。而且他影响： insert ,update ,delete 执行时间。
2索引中数据必须与数据表数据同步：如果索引过多，当表中数据更新的时候后，索引也要同步更新，这就降低了效率。
索引的类型
1、普通索引
2、唯一性索引
3、主键索引（主索引）
4、复合索引
普通索引
最基本的索引，不具备唯一性，就是加快查询速度
注：可以使用key，也可以使用index 。index 索引名称（字段） ，索引名称，可以加也可以不加，不加使用字段名作为索引名。。
mysql> create table demo( id int(4), name varchar(20), pwd varchar(20), index(pwd) );
注意：index和 key 是相同的
mysql> create table demo1( id int(4), name varchar(20), pwd varchar(20), key(pwd) );
mysql> create table demo2( id int(4), name varchar(20), pwd varchar(20), key index_pwd(pwd) ); #加上索引名称
查看索引
注：如果Key是MUL, 那么该列的值可以重复, 该列是一个非唯一索引的前导列(第一列)或者是一个唯一性索引的组成部分但是可以含有空值NULL。就是表示是一个普通索引。
我们先删除索引
mysql> alter table demo drop key pwd; 注意此处的pwd指的是索引的名称,而不是表中pwd的那个字段
再用alter添加
mysql> alter table demo add key(pwd);
唯一索引
与普通索引基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须唯一，用来约束内容，字段值只能出现一次。应该加唯一索引。唯一性允许有NULL值<允许为空>。
mysql> create table demo3(id int(4) auto_increment primary key, uName varchar(20), uPwd varchar(20), unique index (uName));
方法二：修改表时加唯一索引
alter table 表名 add unique 索引名 (字段);
mysql> alter table demo3 drop key uName;
mysql> alter table demo3 add unique(uName);
主键索引
查询数据库，按主键查询是最快的，每个表只能有一个主键列，可以有多个普通索引列。主键列要求列的所有内容必须唯一，而索引列不要求内容必须唯一，不允许为空
mysql> create table demo4 (id int(4) not null auto_increment primary key,name varchar(4) not null );
方法二：创建表后添加<不推荐>
删除遇到这种情况是auto_increment的原因
mysql> alter table demo4 change id id int(4) not null;
mysql> alter table demo4 drop primary key;
再添加
mysql> alter table demo4 change id id int(4) not null primary key auto_increment;
总结：主键索引，唯一性索引区别：主键索引不能有NULL，唯一性索引可以有空值
复合索引
索引可以包含一个、两个或更多个列。两个或更多个列上的索引被称作复合索引
例：创建一个表存放服务器允许或拒绝的IP和port，要记录中IP和port要唯一。
mysql> create table firewall ( host varchar(15) not null ,port smallint(4) not null ,access enum(‘deny‘,‘allow‘) not null, primary key (host,port));
mysql> insert into firewall values(‘10.96.52.46‘,22,‘deny‘);
mysql> insert into firewall values(‘10.96.52.46‘,21,‘allow‘);
mysql> insert into firewall values(‘10.96.52.46‘,21,‘allow‘);
ERROR 1062 (23000): Duplicate entry ‘10.96.52.46-21‘ for key ‘PRIMARY‘
5.外键
外键：每次插入或更新时，都会检查数据的完整性。
注：创建成功，必须满足以下4个条件：
1、确保参照的表和字段存在。
2、组成外键的字段被索引。
3、必须使用type指定存储引擎为：innodb.
4、外键字段和关联字段，数据类型必须一致。
例子：我们创建一个数据库，包含用户信息表和订单表
mysql> create database market;
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;
#创建时，如果表名是sql关键字，使用时，需要使用反引号``
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;
注：
1:on delete cascade on update cascade 添加级联删除和更新：
2:：确保参照的表user中id字段存在。组成外键的字段u_id被索引。必须使用type指定存储引擎为：innodb。
外键字段和关联字段，数据类型必须一致。
插入测试数据
mysql> insert into user(name,sex)values(‘HA‘,1),(‘LB‘,2),(‘HPC‘,1);
mysql> insert into `order` (u_id,username,money)values(1,‘HA‘,234),(2,‘LB‘,146),(3,‘HPC‘,256);
测试级联删除：
mysql> delete from user where id=1; 删除user表中id为1的数据
测试级联更新：
mysql> update user set id=6 where id=2;
测试数据完整性
外键约束，order表受user表的约束
在order里面插入一条数据u_id为5用户，在user表里面根本没有，所以插入不进去
mysql> insert into user values(5,‘Find‘,1);
mysql> insert into `order` (u_id,username,money)values(5,‘Find‘,346);
方法二：通过alter table 创建外键和级联更新，级联删除
mysql> create table order1(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))type=innodb;
mysql> alter table order1 add foreign key(u_id) references user(id) on delete cascade on update cascade,type=innodb;
mysql> alter table order1 add constraint `bk`foreign key(u_id) references user(id) on delete cascade on update cascade,type=innodb; 指定外键名称
mysql> show create table order1;
删除外键：
语法
alter table 数据表名称 drop foreign key 约束（外键）名称
mysql> alter table order1 drop foreign key order1_ibfk_1;
mysql> show create table order1;
6.视图
创建视图
语法：create view视图名称（即虚拟的表名） as select 语句。
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 ;
可以按照普通表去访问。
另外视图表中的数据和原数据表中数据是同步的。
mysql> show create view bc \G
更新或修改视图
alter view视图名称（即虚拟的表名） as select 语句。
update view视图名称（即虚拟的表名）set
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 ;
mysql> update bc set bName=‘HA‘ where price=34;
删除视图
drop view 视图名。
mysql> drop view bc;
mysql语句进阶