查看: 2377|回复: 0

[Mysql数据库] 简单索引和insert、replace语句

发表于 2018-3-11 10:10:51

1、建立索引

mysql> create index agei on student(age desc); //agei是索引名,desc是降序

mysql> create index agei on student(age asc); //asc是默认,升序


2、删除索引

mysql> alter table student drop index agei;


3、建立索引后,再使用select查询会增快查询速度

mysql> select * from student where Age > 18;


4、索引的缺点

占用磁盘空间,降低插入速度和修改速度


5、索引的优点

索引是对数据库表中一列或多列的值进行排序的一种结构,使用索引可快速访问数据库表中的特定信息,加快数据库的查询速度


6、插入语句insert

mysql> create table student(

ID int unsigned auto_increment,

Name varchar(10),

Gender enum("男","女"),

Age tinyint unsigned default 18, //如果不写年龄,默认为18

Score tinyint unsigned,

Address varchar(200),

primary key(ID) );


mysql> insert into student (Name,Gender,Score,Address) values ("张三","男",95,"北京市海淀区"); //插入语句,可以不插入Age字段信息,因为有默认值


7、将查询结果再插入表中

mysql> insert into student (Name,Gender,Age,Score,Address) select Name,Gender,Age,Score,Address from student;


8、无条件删除所有记录

mysql> delete from student; //相当于把表清空


9、replace语句,在插入的时候如果指定主键,并且主键里面有插入的值,就替换

mysql> replace into student (ID,Name,Gender,Score,Address) values (1,"张三","男",95,"北京市海淀区");

mysql> replace into student (ID,Name,Gender,Score,Address) values (2,"张三","男",95,"北京市海淀区");

mysql> replace into student (ID,Name,Gender,Score,Address) values (2,"杨贵妃","女",100,"北京市朝阳区"); //次语句会把ID为2的数据给替换掉






回复

使用道具 举报