MYSQL数据库常用命令学习
连接数据库,输入:
mysql -u root -p
完整输入:
mysql -h localhost -P 3306 -u root -p
展示数据库,输入:
show databases;
选择数据库,输入:
use 数据库名;
展示表,输入:
show tables;
数据操作:
查询表数据,格式:
select * from 表名;
select 字段1,字段2 from 表名;
select * from 表名 where 字段1 = 值1 and 字段2 = 值2;
注:字段数值字符串用单引号括住,数字不用单引号括
eg:
select * from student1;
select sname,age,address from student1;
select * from student1 where age > 20 and address = '北京';
排序
select * from 表名 order by 字段名;
select * from 表名 order by 字段名 desc;
注:末尾有desc表示倒序,无表示顺序
eg:
select * from student1 order by age;
select * from student1 order by age desc;
limit 限制查询数量
select * from student1 limit a,b;
注:a,b均为正整数,a为0时可以省略不写,表示从第a行开始,共显示b行
eg:
select * from student1 limit 3;
select * from student1 limit 3,5;
is 只能和null搭配使用
eg:
select * from student1 where phone is null;
select * from student1 where phone is not null;
in
eg:
select * from student1 where sname in ('张一','张二','李四');
select * from student1 where sname not in ('张一','张二','李四');
between
注:只能用来判断数字
eg:
select * from student1 where age between 20 and 23;
select * from student1 where age not between 20 and 23;
like 字符串模糊搜索
select * from student1 where sname like '张%';
select * from student1 where sname like '%二';
select * from student1 where sname like '%小%';
在数据表中添加数据 ,格式:
insert into 表名 (字段1,字段2) values (值1,值2);
eg:
insert into student1 (sname,sex,age) value ('周一','男',23);
修改表数据,格式:
update 表名 set 字段1 = 值1,字段2 = 值2 where 字段1 = 值1 and 字段2 = 值2;
eg:
update student1 set age = '20' where sex = '男';
删除表数据,格式:
delete from 表名 where 字段1 = 值1 and 字段2 = 值2;
eg:
delete from student1 where sname = '张小五';
多表联查,格式:
select * from 表1 join 表2 on 表1.字段1 = 表2.字段2;
eg:
select student1.id,student1.sname,class1.cname from student1 join class1 on student1.classid = class1.id;
select student1.id,student1.sname,class1.cname from student1 join class1 on student1.classid = class1.id where student1.sname like '张%' and student1.sex = '男';