MySQL小结（三）表内操作

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

1# 查看tb表内的内容
2mysql> select * from tb;
3Empty set (0.00 sec)
4# 插入数据
5-- 插入单条数据
6insert into tb(name,email,password) values("361way","[email protected]","as");
7-- 同时插入多条数据
8insert into tb(name,email,password) values("as","[email protected]","pwd"),("info","[email protected]","i");

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

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

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

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

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

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

1-- 以p开头的所有（多个字符串）
2select * from tb where name like "p%";
3-- 以p开头的所有（一个字符）
4select * from tb where name like "p%";

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

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

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

1无对应关系则不显示
2select A.num, A.name, B.name from A,B where A.nid = B.nid;
3无对应关系则不显示
4select A.num, A.name, B.name from A inner join B on A.nid = B.nid;
5A表所有显示，如果B中无对应关系，则值为null
6select A.num, A.name, B.name from A left join B on A.nid = B.nid;
7B表所有显示，如果B中无对应关系，则值为null
8select A.num, A.name, B.name from A right join B on A.nid = B.nid;

1组合，自动处理重合
2select nickname from A union select name from B;
3组合，不处理重合
4select nickname from A union all select name from B;