mysql常用命令
来源:互联网 发布:java 生成eml 编辑:程序博客网 时间:2024/06/09 22:24
1、数据库操作
create database db_name; 建库
drop database db_name; 删库
2、建立数据表
先选择数据库
use db_name;
create table `table_name`(
order_id int(10) not null auto_increment primary key,
uid int(10) not null,
prodult_id int(10) not null
)engine=myisam default charset=utf8;
3、数据表操作
rename table`old_table_name` to`new_table_name`;
alter table `table_name` change column old_column_name new_column_name int(10) not null;
4、表数据操作
添
insert into `order` (uid,procuct_id) values(2,4),(2,3),(2,7);
insert into `order` (order_id,uid,product_id) values(0,4,4),(null,4,3);
insert into `order` values(0,3,4);
insert into `order` values(null,3,5);
insert into `order` set uid=4,product_id=4;
删
delete from `order` where order_id=12;
delete from `order`;
改
update `order` set product_id=2 where uid=1;
查
select * from `order` where uid=1 group by product_id having product_id>2 order by order_id desc;
5、经典题型
create table `user`(
uid int(10) not null auto_increment primary key,
username varchar(20) not null,
passwd varchar(20) not null
)engine=myisam default charset=utf8;
create table `order`(
order_id int(10) not null auto_increment primary key,
uid int(10) not null,
prodult_id int(10) not null
)engine=myisam default charset=utf8;
(1)查询订单最多的用户名 select user.username from user left join (select uid,count(*) as c from `order` group by uid
order by c desc) as O on user.uid=O.uid limit 1;
select user.username from user right join (select uid,count(*) as c from `order` group by uid
order by c desc limit 1) as O on user.uid=O.uid;
select user.username from (select uid,count(*) as c from `order` group by uid order by c desc limit 1)
as O left join user on user.uid=O.uid;
select username from user where uid=(select uid from `order` group by uid order by count(*) desc limit 1)
(2)查询没有下订单的用户
select username from user where uid not in (select distinct uid from `order`);
- MySQL常用命令
- MySql常用命令
- MySQL常用命令
- mysql常用命令
- mysql常用命令
- mysql常用命令
- Mysql常用命令
- MySQL常用命令
- mysql 常用命令
- MySQL常用命令
- mysql常用命令
- MySql常用命令
- MySQL常用命令
- mysql 常用命令
- mysql常用命令
- mysql常用命令
- mysql 常用命令
- MySQL常用命令
- ArrayList与LinkedList的简单比较
- HDU 1018-Big Number(数学)
- Android--书籍翻页效果
- Exception in thread "main" java.lang.IllegalArgumentException
- self.window.rootViewController都干了些啥事?
- mysql常用命令
- PullToRefreshListView geCount不为0 不getView的问题
- 关于Android SDK、Eclipse插件下载慢的解决
- Java中数据库连接池原理机制的详细讲解
- Android 自定义控件实现刮刮卡效果 真的就只是刮刮卡么
- 黑马程序员-NSstring的使用
- 利用文件锁保证一个程序最多只有一份拷贝在运行
- Java设计模式-简单工厂模式
- C#作业 跪求管理员通过 QB已到帐