mysql常用命令
日期:2021-08-09 10:27:10 浏览:272
显示的数据库
show databases;
显示所有的表
show tables;
创建数据库
create database new_demo;
删除数据库
drop database new_demo;
创建表
create table new_demo id int(11) ,name varchar(25);
修改表名
alter table old_demo rename to new_demo;
删除表
drop table new_demo;
插入
insert into 表名 (`id`,`name`,`sex`)values(100,'夏利','男');
更新
update 表名 set `name`="夏利" where id=1;
删除
delete from 表名 where id=1;
查询
select * from demo;
select * from demo where id=1;
select * from demo where id>100 and id<200;
select * from demo where id between 100 and 200
select * from demo where id not between 100 and 200
select * from demo where id in (1,2,4,5,6)
select * from demo where id not in (1,2,4,5,6)
笛卡尔积:CROSS JOIN
假如第一表5条数据,第二张表10条数据,查询后返回5*10=50条数据
select * from first_table join two_table;
SELECT * FROM first_table ,two_table;
select * from first_table where id>1 or name="夏利";
查询总数,他会返回一个字段
select count(*) from first_table ;
select count(id) from first_table ;
请平均数,求当前pricle列的平均价格
select avg(pricle) as curr_pricle from study;
去除重复的数据,但是这样有个缺点,它只返回一个name字段
select distinct name from user;