MySQL的指令Ⅱ

进入mysql的指令

mysql -h localhost -u root -p

mysql -h 101.43.158.84 -u root -p

 

insert是插入数据，如果存在重复数据数据，insert就无法插入，而replace解决重复性数据插入不会出错

 

 

 

不能为空not null

alter table user add adress varchar(100)not null;

 

默认值default

增加一列名为sex的列，不能为空，且默认值是boy

alter table stop add sex varchar(6) not null default “boy”;

插入内容（id和address都不能为空且无默认需要单独输入）

replace into stop(id,adress) values(5,”xian”);

 

 

 

自增auto_increment

create table user(

id int primary key auto_increment,

name vaechar(20),

age int

);

replace into user (name,age) values(“xw”,22),(“xc”,23);

 

 

 

唯一约束性unique

alter table user add code varchar(18) unique;

replace into user values(4,”xw”,22,"xian",”1234567890”);

 ,

 

 

 

 

 

 

修改的时候一定要加上表达式，否则会进行批量的修改

 update user set age=21 where id=1;

 

 

删除

deleate from user where id=1;单个删除

delete from user;全部删除

truncate table user；删除大量数据

 

deleate from user where id=1;单个删除

 

 

delete from user;全部删除

 

 

 

 

truncate table user；适合删除大量数据

 

 

克隆表只能克隆表的结构，不能克隆表的数据

create table info like user;

 

 

把一个表的数据写入另外一个表

insert into info select * from user;

 

 

查询全表select * from user;

查询具体字段select name  from user;

查询有多少数据select count（*）from user;

效率更高select count（1） from user;

 

 

select count(1) as 总数 from user;

 

 

select * from user where name ="xw" and age=21;至少满足两项数据

select * from user where name ="xw" or age=32;满足一项数据即可

 

 

select * from user where name in ("xw","xc");

select * from user where age between 18 and 25;查询一个范围

 

 

加上not表示不在这个范围内的其他数据

select * from user where name not in ("xc");

select * from user where age not between 18 and 25;

 

 

select * from user where name rlike "^xw"; 以xw为开头的所有数据

select * from user where name like "x%"; 确定x，后边为任意字符所有数据，%代表任意字符

select * from user where name rlike "c$"; 以c为结尾的所有数据

 

 

 

导入文件

mysql -h localhost -u root -p <employees.sql