MySQL小结(三)表内操作

2013年2月18日 发表评论 阅读评论

首先创建测试库和测试表:

-- 创建数据库
CREATE DATABASE dbname DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
-- 创建表
CREATE TABLE `tb` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `name` char(15) NOT NULL,
  `alias` varchar(10) DEFAULT NULL,
  `email` varchar(30) DEFAULT NULL,
  `password` varchar(20) NOT NULL,
  `phone` char(11) DEFAULT '13800138000',
  PRIMARY KEY (`id`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

一、增加表内数据

# 查看tb表内的内容
mysql> select * from tb;
Empty set (0.00 sec)
# 插入数据
-- 插入单条数据
insert into tb(name,email,password) values("361way","361way.com@163.com","as");
-- 同时插入多条数据
insert into tb(name,email,password) values("as","i@361way.com","pwd"),("info","info@361way.com","i");

查看结果:

mysql> select * from tb;
+----+---------+-------+-------------------------+----------+-------------+
| id | name    | alias | email                   | password | phone       |
+----+---------+-------+-------------------------+----------+-------------+
|  2 | 361way | NULL  | 361way.com@163.com | as       | 13800138000 |
|  3 | as      | NULL  | i@361way.com         | pwd      | 13800138000 |
|  4 | info    | NULL  | info@361way.com      | i        | 13800138000 |
+----+---------+-------+-------------------------+----------+-------------+
3 rows in set (0.00 sec)

把别的表的数据插入当前表。查看tb_copy表内的内容:

mysql> select * from tb_copy;
+----+--------+-------+-------+----------+-------------+
| id | name   | alias | email | password | phone       |
+----+--------+-------+-------+----------+-------------+
|  5 | hello  | NULL  | NULL  | 1        | 13800138000 |
|  6 | word   | NULL  | NULL  | 2        | 13800138000 |
|  7 | python | NULL  | NULL  | 3        | 13800138000 |
+----+--------+-------+-------+----------+-------------+
3 rows in set (0.00 sec)

把tb_copy表内的name,email,password列插入到tb表中

insert into tb (name, email, password) select name,email,password from tb_copy;

查询结果如下:

mysql> select * from tb;
+----+---------+-------+-------------------------+----------+-------------+
| id | name    | alias | email                   | password | phone       |
+----+---------+-------+-------------------------+----------+-------------+
|  2 | 361way | NULL  | 361way.com@163.com | as       | 13800138000 |
|  3 | as      | NULL  | i@361way.com         | pwd      | 13800138000 |
|  4 | info    | NULL  | info@361way.com      | i        | 13800138000 |
|  5 | hello   | NULL  | NULL                    | 1        | 13800138000 |
|  6 | word    | NULL  | NULL                    | 2        | 13800138000 |
|  7 | python  | NULL  | NULL                    | 3        | 13800138000 |
+----+---------+-------+-------------------------+----------+-------------+
6 rows in set (0.00 sec)

二、删除更改数据

-- 删除表内的所有内容
delete from tb_copy;
-- 删除表内某一条数据
delete from tb where id=2 and name="361way";
-- 更改表内数据
update tb set name="as" where id="3";

三、其他操作

1、按条件查询

-- 多条件查询
select * from tb where id>3 and name="hello" and password="1";
-- 查询指定范围
select * from tb where id between 4 and 6;
-- 查询括号内存在的数据
select * from tb where id in (4,6);
-- 查询括号内不存在的数据
select * from tb where id not in (4,6);
-- 以别的表的内容为查询条件
select * from tb where id in (select id from tb_copy);

2、通配符

-- 以p开头的所有(多个字符串)
select * from tb where name like "p%";
-- 以p开头的所有(一个字符)
select * from tb where name like "p%";

3、限制

-- 前三行数据
select * from tb limit 3;
-- 从第2行开始的3行
select * from tb limit 2,3;
-- 从第4行开始的5行
select * from tb limit 5 offset 4;

4、排序

-- 根据"name"列从小到大排列
select * from tb order by name asc;
-- 根据"name"列从大到小排列
select * from tb order by name desc;
-- 根据 “列1” 从大到小排列,如果相同则按列2从小到大排序
select * from 表 order by 列1 desc,列2 asc;

5、分组

select id from tb group by id;
select id,name from tb group by id,name;
select num,nid from 表  where nid > 10 group by num,nid order nid desc;
select num,nid,count(*),sum(score),max(score),min(score) from 表 group by num,nid;
select num from 表 group by num having max(id) > 10;

group by 必须在where之后,order by之前 。

6、连表

无对应关系则不显示
select A.num, A.name, B.name from A,B where A.nid = B.nid;
无对应关系则不显示
select A.num, A.name, B.name from A inner join B on A.nid = B.nid;
A表所有显示,如果B中无对应关系,则值为null
select A.num, A.name, B.name from A left join B on A.nid = B.nid;
B表所有显示,如果B中无对应关系,则值为null
select A.num, A.name, B.name from A right join B on A.nid = B.nid;

7、组合

组合,自动处理重合
select nickname from A union select name from B;
组合,不处理重合
select nickname from A union all select name from B;




本站的发展离不开您的资助,金额随意,欢迎来赏!

You can donate through PayPal.
My paypal id: itybku@139.com
Paypal page: https://www.paypal.me/361way

分类: 数据库 标签:
  1. 本文目前尚无任何评论.
  1. 本文目前尚无任何 trackbacks 和 pingbacks.