mysql 简单教程(三) ----常见表的操作-CSDN博客

本文链接：https://blog.csdn.net/u014257861/article/details/79613528

创建测试表test：

mysql> CREATE TABLE shop (
    ->     article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
    ->     dealer  CHAR(20)                 DEFAULT ''     NOT NULL,
    ->     price   DOUBLE(16,2)             DEFAULT '0.00' NOT NULL,
    ->     PRIMARY KEY(article, dealer));
mysql> INSERT INTO shop VALUES
    ->     (1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),
    ->     (3,'C',1.69),(3,'D',1.25),(4,'D',19.95);

UNSIGNED：无符号化，即只可存放正数
ZEROFILL：格式化，将宽度不足的用‘0’填充。如 article INT(4)中的数字5，查询处理即为 0005
对于数字类型的列(如int),设置了ZEROFILL属性时，自动附带UNSIGNED属性
DEFAULT：声明默认值
NOT NULL：声明不可为空
PRIMARY KEY：声明主键，唯一值

1.最大值

1.1 取一列的最大值

mysql> SELECT MAX(article) AS article FROM shop;

1.2 取一列最大值的所在行数据

子查询方法：

mysql> SELECT article, dealer, price
    -> FROM   shop
    -> WHERE  price=(SELECT MAX(price) FROM shop);

left join方法：(用小链接大，最大值没有可链接，筛选NULL）

mysql> SELECT s1.article, s1.dealer, s1.price
    -> FROM shop s1
    -> LEFT JOIN shop s2 ON s1.price < s2.price
    -> WHERE s2.article IS NULL;

LIMIT方法：

mysql> SELECT article, dealer, price FROM shop ORDER BY price DESC limit 1;

1.3 分组最大值

GROUP by方法(只显示的分组的列，与最大值列)：

mysql> SELECT article, MAX(price) AS price FROM   shop GROUP BY article;

子查询方法（显示所有列）：

mysql> SELECT * FROM   shop s1 WHERE  price=(SELECT MAX(s2.price) FROM shop s2 WHERE s1.article = s2.article);
或者
mysql> SELECT s1.article, dealer, s1.price FROM shop s1 JOIN (   SELECT article, MAX(price) AS price   FROM shop   GROUP BY article) AS s2   ON s1.article = s2.article AND s1.price = s2.price;

LEFT JOIN方法(同1.2中的left join 道理类似)：

mysql> SELECT s1.article, s1.dealer, s1.price FROM shop s1 LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price WHERE s2.article IS NULL;

2. 定义变量

可将查询结果赋予变量，后面语句引用变量
如：

mysql> SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;
mysql> select @min_price,@max_price;
mysql> SELECT * FROM shop WHERE price=@min_price OR price=@max_price;

3. 外键

前提：
① 表的存储引擎都是Innodb，否则无外键的约束效果
②字段“大”类型必须一致，如 int 对应smallint 可以，int 对应 char 则不行
③已有数据的字段后续添加外键，必须保证子表字段数据能与父表对应
创建外键表：

mysql> create table test( id int primary key,name varchar(10));   ---主表
mysql> create table fk_test(name varchar(10) not null,nameid int,constraint fk_nameid foreign key(nameid) references test(id));

现有的表添加外键：

alter table add  table_name1 add [constraint 外键名称] foreign key(外键字段) references table_name2(主表字段);

4. 查询两个KEY

OR的方法：

mysql> select * from shop where dealer='A' or dealer='B';

Union方式：

mysql> select * from shop where dealer='A' union select * from shop where dealer='B';

5. 自增序列AUTO_INCREMENT

使用方法：

mysql> create table test (id int unsigned primary key auto_INCREMENT,name varchar(10)) [auto_increment=100];

现有表修改

mysql> alter table  table_name auto_increment=your_number;

说明：

① AUTO_INCREMENT默认从 1开始，没有明确给定初始值的情况下

mysql> insert into test values(NULL,'wk1');
mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | wk1  |
+----+------+

② 当插入记录时（当前表中最大值是N-1，新记录序列值应该为N）：

没有为设置了AUTO_INCREMENT的了 ID 列指定值；或指定值为（0或NULL）自增序列生效自动插入当前应该递增的序列值N

mysql> insert into test values('0','wk1');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values(NULL,'wk2');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test(name) values('wk4');
Query OK, 1 row affected (0.00 sec)

列指定的值S小于N时：
如与表中现有值重复，则报错（有主键约束）

mysql> insert into test values('1','wk1');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

如不重复，则可插入该指定值S

mysql> delete from test where id =1;
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values('1','wk1');
Query OK, 1 row affected (0.00 sec)

列指定的值S大于N时：
插入成功，且下次序列值从S+1开始（跳号）

mysql> select max(id) from test;
+---------+
| max(id) |
+---------+
|       5 |
+---------+
mysql> insert into test values('10','wk1');
Query OK, 1 row affected (0.01 sec)
mysql> insert into test values(NULL,'wk2');
Query OK, 1 row affected (0.06 sec)
mysql> select max(id) from test;
+---------+
| max(id) |
+---------+
|      11 |
+---------+
1 row in set (0.00 sec)

③ 当UPDATE更新时

列指定值S小于N时：
同INSERT 插入相同

列指定值S>=N时——-更新成功：
数据库没有重启的情况下，下次序列值是当前内存中的N，且内存中值增加到S的时候会报错（就那么一下，后面正常）

mysql> update test set id=13 where id=1;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> insert into test values(NULL,'wk2');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values(NULL,'wk2');
ERROR 1062 (23000): Duplicate entry '13' for key 'PRIMARY'
mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  2 | wk1  |
|  3 | wk1  |
|  4 | wk2  |
|  5 | wk4  |
| 10 | wk1  |
| 11 | wk2  |
| 12 | wk2  |
| 13 | wk1  |
+----+------+
8 rows in set (0.00 sec)

mysql> insert into test values(NULL,'wk2');
Query OK, 1 row affected (0.00 sec)

重启之后，则变为 S+1

mysql> select max(id) from test;
+---------+
| max(id) |
+---------+
|      14 |
+---------+
1 row in set (0.00 sec)

mysql> update test set id=50 where id=2;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test order by id desc limit 2;
+----+------+
| id | name |
+----+------+
| 50 | wk1  |
| 14 | wk2  |
+----+------+
2 rows in set (0.00 sec)

mysql> insert into test values(NULL,'wk2');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test order by id desc limit 4;
+----+------+
| id | name |
+----+------+
| 50 | wk1  |
| 15 | wk2  |
| 14 | wk2  |
| 13 | wk1  |
+----+------+
4 rows in set (0.00 sec)

[root@localhost ~]# service mysqld restart

mysql> insert into test values(NULL,'wk2');
Query OK, 1 row affected (0.01 sec)

mysql> select * from test order by id desc limit 4;
+----+------+
| id | name |
+----+------+
| 51 | wk2  |
| 50 | wk1  |
| 15 | wk2  |
| 14 | wk2  |
+----+------+
4 rows in set (0.00 sec)