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、普通索引
小时代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 );
⽅法⼆：创建表后添加<；不推荐>
proexml文件解析失败删除遇到这种情况是auto_increment的原因
mysql> alter table demo4 change id id int(4) not null;c盘清理
mysql> alter table demo4 drop primary key;
再添加
mysql> alter table demo4 change id id int(4) not null primary key auto_increment;
总结：主键索引，唯⼀性索引区别：主键索引不能有NULL，唯⼀性索引可以有空值
复合索引
索引可以包含⼀个、两个或更多个列。两个或更多个列上的索引被称作复合索引
mysql面试题导图例：创建⼀个表存放服务器允许或拒绝的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;
注：复杂的update语句
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;
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论