mysql 常用命令

1
show databases;

1
create database 数据库名;

1
create database one charset utf8;

1
drop database 数据库名;

1
use 数据库名

1
create table users(aid int unsigned primary key auto_increment,name char(20),password char(32));

1
desc users

1
insert into 表名 set name='lucy',password='123';

1
select * from users;

1
show tables;

1
drop table 表名;

1
delete from 表名 where aid=1;

1
update 表名 set name='nick',password='daad' where aid=1;

1
drop database if exists abc;

1
select content from art;

1
create table shop(title char(30),price decimal(10,3));

1
create table shop(title smallint(6) zerofill);

1
create table student(name char(20),sex enum('male','female'),hobby set('soccer','tennis',bsaketball));

1
create table fruit(fname char(30) unique,price decimal(7,3));

1
2
3
4
//只能搜索完整数据(不常用)
select * from student where find_in_set ('soccer',hobby);
//更广泛的搜索
select * from student where hobby like '%soc%';

1
show create table 表名;

1
truncate 表名;

1
create table ab like abc;

1
insert into ab select * from abc;

1
create table ab select * from abc;

1
insert into student (name,sex,hobby) values ('liming','male','soccer,tennis'),('zhaoxia','male','basketball');

1
select name,sex as s from student;

1
2
3
4
select * from student where name='liming' and sex='male';
select * from student where name='zhaoxia' or sex='male';
//搜索所有数据
select * from student where sex='male' or true;

1
select concat(name,'-',hobby) from student;

1
2
3
4
//age>22如果是真就是1,,假就是0
select name,age,age>22 from student;
//自定义真假所显示的值
select name,if(age>22,'true','false') from student;

1
2
3
select * from student where name is null;
//查询非null数据
select * from student where name is not null;

1
2
3
4
5
6
//由大到小
select * from student order by age desc;
//由小到大
select * from student order by age asc;
//先用age排序,若有相同的再用sid排序
select * from student order by age desc,sid desc;

1
2
3
4
//截取前五条
select * from student limit 5;
//从第3条开始，截取4条
select * from student limit 2,4;

1
2
3
4
//随机抽3条数据
select * from student order by rand() limit 3;
//随机抽4条年龄大于20岁的数据
select * from student where age>20 order by rand() limit 4;

1
select * from stu where age between 20 and 23;

1
select * from stu where age in(23,31);

1
select * from stu where name like '%guo%';

1
select * from stu where name like 'guo%';

1
select * from stu where name like '%cheng';

1
select * from stu where name like 'li_';

1
select sid,left(name,2) fro, stu;

1
2
3
4
//若不设置主键,追加到最后
replace into stu (name,age) value ('liming',25);
//设置主键,将替换数据
replace into stu (sid,name,age) value (18,'liming',25);

1
delete from stu where age<18;

1
select count(*) from stu;

1
2
select max(age) from stu;
select min(age) from stu;

1
select sum(age) from stu;

1
select avg(age) from stu;

1
select * from stu group by sex;

1
select * from stu group by sex having sex='male';