-
-
-
-
- 1. 直观感受MySQL是怎么储存数据的
- 2. 创建一个database和表，以及表结构的操作。
- 3. 函数
- 4. 表内容的增删改查
-
-
-
1. 直观感受MySQL是怎么储存数据的
# 用root用户登陆进入数据库
[mysqladmin@hadoop000 ~]$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.
# 查看这台机器上有多少数据库，分别叫啥名字，这些数据库都放在定义的data目录下
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| ruoze |
| test |
+--------------------+
5 rows in set (0.01 sec)# 选择某一个数据库进去，里面存的是表格
mysql> use performance_schema
Database changed# 查看这个数据库有多少表格，叫什么
mysql> show tables;
+----------------------------------------------------+
| Tables_in_performance_schema |
+----------------------------------------------------+
| accounts |
| cond_instances |
| events_stages_current |
| users |
+----------------------------------------------------+
4 rows in set (0.00 sec)# 选择感兴趣的表，查看它的有哪些项目，叫什么，每一个项目的数据类型是什么，是否可以是空值，有无默认值等。
mysql> desc users;
+---------------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+------------+------+-----+---------+-------+
| USER | char(16) | YES | | NULL | |
| CURRENT_CONNECTIONS | bigint(20) | NO | | NULL | |
| TOTAL_CONNECTIONS | bigint(20) | NO | | NULL | |
+---------------------+------------+------+-----+---------+-------+
3 rows in set (0.00 sec)# 查看这张表的通过什么语法创建的，引擎是什么，字符编码是什么
mysql> show create table users;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| users | CREATE TABLE `users` (
`USER` char(16) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`CURRENT_CONNECTIONS` bigint(20) NOT NULL,
`TOTAL_CONNECTIONS` bigint(20) NOT NULL
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)# 查看这张表的某一项目的内容
mysql> select USER from users;
+------+
| USER |
+------+
| root |
| NULL |
+------+
2 rows in set (0.00 sec)# 查看这张表的所有内容。
mysql> select * from users;
+------+---------------------+-------------------+
| USER | CURRENT_CONNECTIONS | TOTAL_CONNECTIONS |
+------+---------------------+-------------------+
| root | 3 | 6 |
| NULL | 18 | 29 |
+------+---------------------+-------------------+
2 rows in set (0.00 sec)总结： 一台机器中可以有多个数据库，每一个数据库有多张表，每张表有不同的项目，不同的项目可以有不同性质（数据类型，长度，默认值，主键与否，是否支持中文等）
2. 创建一个database和表，以及表结构的操作。
#删除某个数据库
mysql> drop database ruoze;
Query OK, 0 rows affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)# 创建一个名为ubung的数据库
mysql> create database ubung;
Query OK, 1 row affected (0.00 sec)# 创建一个名为ubungadm的用户以及设置密码
mysql> create user ubungadm identified by '123qwe';
Query OK, 0 rows affected (0.00 sec)#赋予这个用户对这个数据库所有表的所有权限
mysql> grant all privileges on ubung.* to ubungadm@192.168.137.130;
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for ubungadm;
+---------------------------------------------------------------------------------------------------------+
| Grants for ubungadm@% |
+---------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'ubungadm'@'%' IDENTIFIED BY PASSWORD '*050376F3855A67F5E2C6514FD3130B31006C1276' |
+---------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)# 创建一个对这个数据库所有表都最高权限的bsp用户，然后去除它创建表的权限
mysql> grant all privileges on ubung.* to bsp identified by '123';
mysql> revoke create on ubung.* from bsp;
Query OK, 0 rows affected (0.00 sec)mysql> show grants for bsp;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for bsp@% |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'bsp'@'%' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
| GRANT SELECT, INSERT, UPDATE, DELETE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `ubung`.* TO 'bsp'@'%' |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> revoke create on ubung.* from bsp;
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for bsp;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for bsp@% |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'bsp'@'%' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
| GRANT SELECT, INSERT, UPDATE, DELETE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `ubung`.* TO 'bsp'@'%' |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)# 在ubung数据库中创建一张名为waren的表
mysql> use ubung
Database changed
mysql> create table waren(
-> id int AUTO_INCREMENT primary key,#id，整数 自动增加，主键
-> name varchar(100), # name， 字符，长度100byte内
-> price float, # 小数
-> menge int, # 整数
-> ctime timestamp DEFAULT CURRENT_TIMESTAMP, # 自动时间矬，默认后修改表时候不变
-> mtime timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP # 修改表后时间改变
-> )ENGINE=InnoDB AUTO_INCREMENT=2 DEFALUT CHARSET=utf8;# 自动增加从2开始，编码utf8# 给表里塞内容
mysql> insert into waren(id, name, price, menge) values(1, 'ios', 10.5, 10);
Query OK, 1 row affected (0.01 sec)
mysql> insert into waren(name, price, menge) values('andriod', 8, 15); # 不加id，自动补上
Query OK, 1 row affected (0.01 sec)
mysql> insert into waren(name, price, menge) values('blackb', 13.77, 9);
Query OK, 1 row affected (0.00 sec)
mysql> select * from waren; # 查看结果
+----+---------+-------+-------+---------------------+---------------------+
| id | name | price | menge | ctime | mtime |
+----+---------+-------+-------+---------------------+---------------------+
| 1 | ios | 10.5 | 10 | 2018-06-14 00:30:24 | 2018-06-14 00:30:24 |
| 2 | andriod | 8 | 15 | 2018-06-14 00:32:14 | 2018-06-14 00:32:14 |
| 3 | blackb | 13.77 | 9 | 2018-06-14 00:32:54 | 2018-06-14 00:32:54 |
+----+---------+-------+-------+---------------------+---------------------+
3 rows in set (0.00 sec)# 增加一个cuser的项目列
mysql> alter table waren add column cuser varchar(50);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> update waren set cuser='root';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> select * from waren;
+----+---------+-------+-------+---------------------+---------------------+-------+
| id | name | price | menge | ctime | mtime | cuser |
+----+---------+-------+-------+---------------------+---------------------+-------+
| 1 | ios | 10.5 | 10 | 2018-06-14 00:30:24 | 2018-06-14 00:39:33 | root |
| 2 | andriod | 8 | 15 | 2018-06-14 00:32:14 | 2018-06-14 00:39:33 | root |
| 3 | blackb | 13.77 | 9 | 2018-06-14 00:32:54 | 2018-06-14 00:39:33 | root |
+----+---------+-------+-------+---------------------+---------------------+-------+
3 rows in set (0.00 sec)# 在menge后增加一个orgin的项目列
mysql> alter table waren add column orgin varchar(50) after menge;
Query OK, 0 rows affected (0.15 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> update waren set orgin='CN';
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3 Changed: 3 Warnings: 0# 删除cuser这个列
mysql> alter table waren drop cuser;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from waren;
+----+---------+-------+-------+-------+---------------------+---------------------+
| id | name | price | menge | orgin | ctime | mtime |
+----+---------+-------+-------+-------+---------------------+---------------------+
| 1 | ios | 10.5 | 10 | CN | 2018-06-14 00:30:24 | 2018-06-14 00:46:47 |
| 2 | andriod | 8 | 15 | CN | 2018-06-14 00:32:14 | 2018-06-14 00:46:47 |
| 3 | blackb | 13.77 | 9 | CN | 2018-06-14 00:32:54 | 2018-06-14 00:46:47 |
+----+---------+-------+-------+-------+---------------------+---------------------+
3 rows in set (0.00 sec)# 复制waren这个表并且命名为sales
mysql> create table sales select * from waren;
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from sales;
+----+---------+-------+-------+-------+---------------------+---------------------+
| id | name | price | menge | orgin | ctime | mtime |
+----+---------+-------+-------+-------+---------------------+---------------------+
| 1 | ios | 10.5 | 10 | CN | 2018-06-14 00:30:24 | 2018-06-14 00:46:47 |
| 2 | andriod | 8 | 15 | CN | 2018-06-14 00:32:14 | 2018-06-14 00:46:47 |
| 3 | blackb | 13.77 | 9 | CN | 2018-06-14 00:32:54 | 2018-06-14 00:46:47 |
+----+---------+-------+-------+-------+---------------------+---------------------+
3 rows in set (0.00 sec)# 将orgion这个列重命名为market
mysql> alter table sales change column orgin market varchar(20) not null;
Query OK, 3 rows affected (0.03 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from sales;
+----+---------+-------+-------+--------+---------------------+---------------------+
| id | name | price | menge | market | ctime | mtime |
+----+---------+-------+-------+--------+---------------------+---------------------+
| 1 | ios | 10.5 | 10 | CN | 2018-06-14 00:30:24 | 2018-06-14 00:46:47 |
| 2 | andriod | 8 | 15 | CN | 2018-06-14 00:32:14 | 2018-06-14 00:46:47 |
| 3 | blackb | 13.77 | 9 | CN | 2018-06-14 00:32:54 | 2018-06-14 00:46:47 |
+----+---------+-------+-------+--------+---------------------+---------------------+
3 rows in set (0.00 sec)此外， 关于用户权限具体见下表。
| 权限 | 描述 |
|---|---|
| ALL PRIVILEGES | 影响除WITH GRANT OPTION之外的所有权限 |
| ALTER | 影响ALTER TABLE命令的使用 |
| ALTER ROUTINE | 影响创建存储例程的能力 |
| CREATE | 影响CREATE TABLE命令的使用 |
| CREATE ROUTINE | 影响更改和弃用存储例程的能力 |
| CREATE TEMPORARY TABLES | 影响CREATE TEMPORARY TABLE命令的使用 |
| CREATE USER | 影响创建、弃用；重命名和撤销用户权限的能力 |
| CREATE VIEW | 影响CREATE VIEW命令的使用 |
| DELETE | 影响DELETE命令的使用 |
| DROP | 影响DROP TABLE命令的使用 |
| EXECUTE | 影响用户运行存储过程的能力 |
| EVENT | 影响执行事件的能力（从MySQL5.1.6开始） |
| FILE | 影响SELECT INTO OUTFILE和LOAD DATA INFILE的使用 |
| GRANT OPTION | 影响用户委派权限的能力 |
| INDEX | 影响CREATE INDEX和DROP INDEX命令的使用 |
| INSERT | 影响INSERT命令的使用 |
| LOCK TABLES | 影响LOCK TABLES命令的使用 |
| PROCESS | 影响SHOW PROCESSLIST命令的使用 |
| REFERENCES | 未来MySQL特性的占位符 |
| RELOAD | 影响FLUSH命令集的使用 |
| REPLICATION CLIENT | 影响用户查询从服务器和主服务器位置的能力 |
| REPLICATION SLAVE | 复制从服务器所需的权限 |
| SELECT | 影响SELECT命令的使用 |
| SHOW DATABASES | 影响SHOW DATABASES命令的使用 |
| SHOW VIEW | 影响SHOW CREATE VIEW命令的使用 |
| SHUTDOWN | 影响SHUTDOWN命令的使用 |
| SUPER | 影响管理员级命令的使用，如CHANGE、MASTER、KILL thread、mysqladmin debug、PURGE MASTER LOGS和SET GLOBAL |
| TRIGGER | 影响执行触发器的能力（从MySQL5.1.6开始） |
| UPDATE | 影响UPDATE命令的使用 |
| USAGE | 只连接，不授予权限 |
3. 函数
- 目的：无论增删改查，除了直接数据，我们还可以根据原数据的函数来指定操作
- 命令
mysql> select * from sales
-> ;
+----+---------+-------+-------+--------+---------------------+---------------------+
| id | name | price | menge | market | ctime | mtime |
+----+---------+-------+-------+--------+---------------------+---------------------+
| 1 | ios | 10.5 | 10 | CN | 2018-06-14 00:30:24 | 2018-06-14 00:46:47 |
| 2 | andriod | 8 | 15 | CN | 2018-06-14 00:32:14 | 2018-06-14 00:46:47 |
| 3 | blackb | 13.77 | 9 | CN | 2018-06-14 00:32:54 | 2018-06-14 00:46:47 |
+----+---------+-------+-------+--------+---------------------+---------------------+
3 rows in set (0.00 sec)mysql> select sum(price) from sales;
+-------------------+
| sum(price) |
+-------------------+
| 32.27000045776367 |
+-------------------+
1 row in set (0.00 sec)mysql> select price*menge from sales;
+--------------------+
| price*menge |
+--------------------+
| 105 |
| 120 |
| 123.93000411987305 |
+--------------------+
3 rows in set (0.00 sec)mysql> select day(ctime) from sales;
+------------+
| day(ctime) |
+------------+
| 14 |
| 14 |
| 14 |
+------------+
3 rows in set (0.00 sec)mysql> select menge from sales where price=8;
+-------+
| menge |
+-------+
| 15 |
+-------+
1 row in set (0.00 sec)mysql> select menge from sales where price <> 8;
+-------+
| menge |
+-------+
| 10 |
| 9 |
+-------+
2 rows in set (0.00 sec)mysql> select name, substring(name, 1, 3), substring(name,2) from sales;
+---------+-----------------------+-------------------+
| name | substring(name, 1, 3) | substring(name,2) |
+---------+-----------------------+-------------------+
| ios | ios | os |
| andriod | and | ndriod |
| blackb | bla | lackb |
+---------+-----------------------+-------------------+
3 rows in set (0.00 sec)mysql> select concat(name,'-',price,'-',menge) as combin from sales;
+----------------+
| combin |
+----------------+
| ios-10.5-10 |
| andriod-8-15 |
| blackb-13.77-9 |
+----------------+
3 rows in set (0.00 sec)下表为常用函数
| 数学函数 | |
|---|---|
| ABS(x) | 返回x的绝对值 |
| SQRT(x) | 返回x的非负2次方根 |
| MOD(x,y) | 返回x被y除后的余数 |
| CEILING(x) | 返回不小于x的最小整数 |
| FLOOR(x) | 返回不大于x的最大整数 |
| ROUND(x,y) | 对x进行四舍五入操作 |
| TRUNCATE(x,y) | 舍去x中小数点y位后面的数 |
| SING(x) | 返回x的符号 |
| 字符串函数 | |
| LENGTH(str) | 返回字符串str的长度 |
| CONCAT(s1,s2,……) | 返回一个或者多个字符串连接 |
| TRIM(str) | 删除字符串两侧的空格 |
| REPLACE(str,s1,s2) | 使用字符串s2 |
| SUBSTRING(str,n,len) | 返回字符串str的子串 |
| REVERSE(str) | 返回字符串反转后的结果 |
| LOCATE(s1,str) | 返回子串s1在字符串str中的起始位置 |
| 日期和时间函数 | |
| CURDATE()——获取系统当前日期 | |
| CURTIME()——获取系统当前时间 | |
| SYSDATE()——获取当前系统日期和时间 | |
| TIME_TO_SEC()——返回将时间转换成秒的结果 | |
| ADDDATE()——执行日期的加运算 | |
| SBUDATE()——执行日期的减运算 | |
| DATE_FORMAT()——格式化输入日期和时间值 | |
| 条件判断函数 | |
| IF(expr,v1,v2)——如果expr表达式 | 为true返回v1 |
| IFNULL(v1,v2)——如果v1不为NULL | 返回v1 |
| CASE expr WHEN v1 THEN r1[WHEN v2 THEN r2……][ELSE m] END——如果 | expr值等于v1 |
| 加密函数 | |
| MD(str)——对字符串str | 进行MD5加密 |
| ENCODE(str,pwd_str)——使用pwd作为密码 | 加密字符串str |
| DECODE(str,pwd_str)——使用pwd作为密码 | 解码字符串str |
4. 表内容的增删改查
# 增
mysql> insert into waren(name, price, menge, orgin) values('sb', 6, 3, 'sw');
Query OK, 1 row affected (0.01 sec)mysql> select * from waren;
+----+---------+-------+-------+-------+---------------------+---------------------+
| id | name | price | menge | orgin | ctime | mtime |
+----+---------+-------+-------+-------+---------------------+---------------------+
| 1 | ios | 10.5 | 10 | CN | 2018-06-14 00:30:24 | 2018-06-14 00:46:47 |
| 2 | andriod | 8 | 15 | CN | 2018-06-14 00:32:14 | 2018-06-14 00:46:47 |
| 3 | blackb | 13.77 | 9 | CN | 2018-06-14 00:32:54 | 2018-06-14 00:46:47 |
| 4 | sb | 6 | 3 | sw | 2018-06-14 02:22:56 | 2018-06-14 02:22:56 |
+----+---------+-------+-------+-------+---------------------+---------------------+
4 rows in set (0.00 sec)
# 删
mysql> delete from sales where id=3
-> ;
Query OK, 1 row affected (0.00 sec)mysql> select * from sales;
+----+---------+-------+-------+--------+---------------------+---------------------+
| id | name | price | menge | market | ctime | mtime |
+----+---------+-------+-------+--------+---------------------+---------------------+
| 1 | ios | 10.5 | 10 | CN | 2018-06-14 00:30:24 | 2018-06-14 00:46:47 |
| 2 | andriod | 8 | 15 | CN | 2018-06-14 00:32:14 | 2018-06-14 00:46:47 |
+----+---------+-------+-------+--------+---------------------+---------------------+
2 rows in set (0.00 sec)# 查
mysql> select orgin, sum(price*menge) as cost from waren group by orgin order by cost desc; # group后才会计算sum（），降序desc
+-------+--------------------+
| orgin | cost |
+-------+--------------------+
| CN | 348.93000411987305 |
| sw | 18 |
+-------+--------------------+
2 rows in set (0.00 sec)mysql> select orgin, sum(price*menge) as cost from waren group by orgin order by cost limit 1; # 正常是升序排列。
+-------+------+
| orgin | cost |
+-------+------+
| sw | 18 |
+-------+------+
1 row in set (0.00 sec)mysql> select orgin, sum(price*menge) as cost from waren where id <> 2 group by orgin having cost > 100; # having的东西要在select里还有，只是是select东西的函数。
+-------+--------------------+
| orgin | cost |
+-------+--------------------+
| CN | 228.93000411987305 |
+-------+--------------------+
1 row in set (0.00 sec)##### 交并和集
## 数据
mysql> insert into sales(id, name, price, menge, market) values(7, 'mtk', 5, 7, 'CN')
-> ;
Query OK, 1 row affected (0.01 sec)mysql> select * from sales;
+----+---------+-------+-------+--------+---------------------+---------------------+
| id | name | price | menge | market | ctime | mtime |
+----+---------+-------+-------+--------+---------------------+---------------------+
| 1 | ios | 10.5 | 10 | CN | 2018-06-14 00:30:24 | 2018-06-14 00:46:47 |
| 2 | andriod | 8 | 15 | CN | 2018-06-14 00:32:14 | 2018-06-14 00:46:47 |
| 7 | mtk | 5 | 7 | CN | 2018-06-14 03:25:02 | 2018-06-14 03:25:02 |
+----+---------+-------+-------+--------+---------------------+---------------------+
3 rows in set (0.00 sec)mysql> select * from waren;
+----+---------+-------+-------+-------+---------------------+---------------------+
| id | name | price | menge | orgin | ctime | mtime |
+----+---------+-------+-------+-------+---------------------+---------------------+
| 1 | ios | 10.5 | 10 | CN | 2018-06-14 00:30:24 | 2018-06-14 00:46:47 |
| 2 | andriod | 8 | 15 | CN | 2018-06-14 00:32:14 | 2018-06-14 00:46:47 |
| 3 | blackb | 13.77 | 9 | CN | 2018-06-14 00:32:54 | 2018-06-14 00:46:47 |
| 4 | sb | 6 | 3 | sw | 2018-06-14 02:22:56 | 2018-06-14 02:22:56 |
+----+---------+-------+-------+-------+---------------------+---------------------+
4 rows in set (0.00 sec)mysql> select a.id, a.name, b.market, b.id from waren a left join sales b on a.id=b.id; # 左jion，以左表的id为准，查有的东西，没有则为空
+----+---------+--------+------+
| id | name | market | id |
+----+---------+--------+------+
| 1 | ios | CN | 1 |
| 2 | andriod | CN | 2 |
| 3 | blackb | NULL | NULL |
| 4 | sb | NULL | NULL |
+----+---------+--------+------+
4 rows in set (0.00 sec)mysql> select a.id, a.name, b.market, b.id from waren a right join sales b on a.id=b.id; # 右jion，以右id为准，没得为NULL。
+------+---------+--------+----+
| id | name | market | id |
+------+---------+--------+----+
| 1 | ios | CN | 1 |
| 2 | andriod | CN | 2 |
| NULL | NULL | CN | 7 |
+------+---------+--------+----+
3 rows in set (0.00 sec)mysql> select a.id, a.name, b.market, b.id from sales b right join waren a on a.id=b.id; ### a得左join等于 b得右join
+----+---------+--------+------+
| id | name | market | id |
+----+---------+--------+------+
| 1 | ios | CN | 1 |
| 2 | andriod | CN | 2 |
| 3 | blackb | NULL | NULL |
| 4 | sb | NULL | NULL |
+----+---------+--------+------+
4 rows in set (0.00 sec)mysql> select a.id, a.name, b.market, b.id from sales b left join waren a on a.id=b.id; # a得左join等于 b得右join
+------+---------+--------+----+
| id | name | market | id |
+------+---------+--------+----+
| 1 | ios | CN | 1 |
| 2 | andriod | CN | 2 |
| NULL | NULL | CN | 7 |
+------+---------+--------+----+
3 rows in set (0.00 sec)mysql> select a.id, a.name, b.market, b.id from sales b inner join waren a on a.id=b.id; # 合集 inner join
+----+---------+--------+----+
| id | name | market | id |
+----+---------+--------+----+
| 1 | ios | CN | 1 |
| 2 | andriod | CN | 2 |
+----+---------+--------+----+
2 rows in set (0.00 sec)#### 并集复杂做法（假设数据复杂，或则条件复杂，此次实为简单）
mysql> select sales.name from waren right join sales on waren.id = sales.id;
+---------+
| name |
+---------+
| ios |
| andriod |
| mtk |
+---------+
3 rows in set (0.00 sec)mysql> select waren.name from waren left join sales on waren.id = sales.id;
+---------+
| name |
+---------+
| ios |
| andriod |
| blackb |
| sb |
+---------+
4 rows in set (0.00 sec)mysql> select waren.name from waren left join sales on waren.id = sales.id
-> union
-> select sales.name from waren right join sales on waren.id = sales.id;
+---------+
| name |
+---------+
| ios |
| andriod |
| blackb |
| sb |
| mtk |
+---------+
5 rows in set (0.00 sec)#### 并集在简单的情况下
mysql> select name from waren
-> union
-> select name from sales;
+---------+
| name |
+---------+
| ios |
| andriod |
| blackb |
| sb |
| mtk |
+---------+
5 rows in set (0.00 sec)# 改
mysql> update waren set price = 15 where orgin <> 'CN';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from waren;
+----+---------+-------+-------+-------+---------------------+---------------------+
| id | name | price | menge | orgin | ctime | mtime |
+----+---------+-------+-------+-------+---------------------+---------------------+
| 1 | ios | 10.5 | 10 | CN | 2018-06-14 00:30:24 | 2018-06-14 00:46:47 |
| 2 | andriod | 8 | 15 | CN | 2018-06-14 00:32:14 | 2018-06-14 00:46:47 |
| 3 | blackb | 13.77 | 9 | CN | 2018-06-14 00:32:54 | 2018-06-14 00:46:47 |
| 4 | sb | 15 | 3 | sw | 2018-06-14 02:22:56 | 2018-06-14 04:08:45 |
+----+---------+-------+-------+-------+---------------------+---------------------+
4 rows in set (0.00 sec)