您当前的位置: 首页 >  sql

川川菜鸟

暂无认证

  • 3浏览

    0关注

    969博文

    0收益

  • 0浏览

    0点赞

    0打赏

    0留言

私信
关注
热门博文

mysql数据库(9):常用查询的例子

川川菜鸟 发布时间:2021-07-25 18:31:00 ,浏览量:3

(1)你可以使用以下语句创建示例表:

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);

(2)执行语句后,表应包含以下内容: SELECT * FROM shop;

(3)列的最大值

选取价格最贵:select max(price) as article from shop;

 (4)拥有某个列的最大值的行

任务:找出最贵物品的编号、销售商和价格。

方法一:

mysql> select article,dealer,price -> from shop -> where price=(select max(price) from shop);

 方法二:按价格降序排序所有行并用MySQL特定LIMIT子句只得到第一行,这里用到desc前面讲过,是降序的意思。

mysql> select article ,dealer,price -> from shop -> order by price desc

-> limit 1;

 注:如果有多项最贵的物品( 例如每个的价格为19.95),LIMIT解决方案仅仅显示其中一个!

(5)列的最大值:按组

任务:每项物品的的最高价格是多少?

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

(6)拥有某个字段的组间最大值的行

任务:对每项物品,找出最贵价格的物品的经销商。

SELECT article, dealer, price
FROM   shop s1
WHERE  price=(SELECT MAX(s2.price)
              FROM shop s2
              WHERE s1.article = s2.article);

(7)使用用户变量

任务:要找出价格最高或最低的物品的

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

  

(8)使用外键

CREATE TABLE person (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name CHAR(60) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE shirt (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
    color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
    owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),
    PRIMARY KEY (id)
);

INSERT INTO person VALUES (NULL, 'Antonio Paz');

SELECT @last := LAST_INSERT_ID();

INSERT INTO shirt VALUES
(NULL, 'polo', 'blue', @last),
(NULL, 'dress', 'white', @last),
(NULL, 't-shirt', 'blue', @last);

INSERT INTO person VALUES (NULL, 'Lilliana Angelovska');

SELECT @last := LAST_INSERT_ID();

INSERT INTO shirt VALUES
(NULL, 'dress', 'orange', @last),
(NULL, 'polo', 'red', @last),
(NULL, 'dress', 'blue', @last),
(NULL, 't-shirt', 'white', @last);

SELECT * FROM person;

SELECT * FROM shirt;

SELECT s.* FROM person p, shirt s
 WHERE p.name LIKE 'Lilliana%'
   AND s.owner = p.id
   AND s.color  'white';

 (8)根据天计算访问量

CREATE TABLE t1 (year YEAR(4), month INT(2) UNSIGNED ZEROFILL,
             day INT(2) UNSIGNED ZEROFILL);
INSERT INTO t1 VALUES(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2),
            (2000,2,23),(2000,2,23);

SELECT year,month,BIT_COUNT(BIT_OR(1            
关注
打赏
1665165634
查看更多评论
0.2217s