创建测试表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)