mysql学习笔记之一

青旅半醒 2023-10-17 09:14 140阅读 0赞

shell> mysql --help
    
    3.1连接和断开服务器
    ---------------------
    连接服务器
        shell> mysql -h host -u user -p
    断开服务器
        1、mysql> QUIT
        2、mysql> \q
        3、Ctrl + D (unix)
    ---------------------
    
    3.2输入查询语句
    ---------------------
    查询MySQL版本和当前日期
        mysql> SELECT VERSION(),CURRENT_DATE; 
        mysql> select version(),current_date;
        mysql> SeLeCt vErSion(),current_DATE;
            MySQL关键字不区分大小写
    
    简单计算
        mysql> SELECT SIN(PI()/4),(1+2)*3;
    
    一行里边写多条查询语句
        mysql> SELECT VERSION();SELECT NOW();
    
    取消查询
        mysql> SELECT USER()\c
            \c 取消编辑
    ---------------------
    
    3.3创建和使用数据库
    ---------------------
    mysql> SHOW DATABASES;
    mysql> USE test (USE 和 QUIT 一样，不需要分号结束，但是只能写在一行上面)
        ++++++++++++++++++++
    
        3.3.1创建和选择数据库
        ++++++++++++++++++++
        mysql> CREATE DATABASE menagerie;
            在unix下，数据库名字和表名是大小写敏感的
        mysql> USE menagerie
            选择数据库
        另外一种方式：
            shell> mysql -h host -u user -p menagerie (选择menagerie这个数据库)
        mysql> SELECT DATABASE();
            查看现在正在使用的数据库的名字
        ++++++++++++++++++++
    
        3.3.2创建表
        ++++++++++++++++++++
        mysql> SHOW TABLES;
            查看当前数据库中有哪些表
        mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
            -> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
            创建表 
        mysql> DESCRIBE pet; 或者 mysql> DESC pet;
            查看表结构
        ++++++++++++++++++++
    
        3.3.3向表里边装载数据   
        ++++++++++++++++++++
        pet.txt 
        Fluffy  Horold  cat f   1993-02-04  \N
        Claws   Gwen    cat m   1994-03-17  \N
        Buffy   Horold  dog f   1989-05-13  \N
        Fang    Benny   dog m   1990-08-27  \N
        Bowser  Diane   dog m   1979-08-31  1995-07-29
        Chirpy  Gwen    bird    f   1998-09-11  \N
        Whistler    Gwen    bird    \N  1997-12-09  \N
        Slim    Benny   snake   m   1996-04-29  \N
        空值NULL用\N表示
        mysql> LOAD DATA LOCAL INFILE 'D:\\qs\\sql\\mysql\\pet.txt' INTO TABLE pet LINES TERMINATED BY '\r\n';
            Windows系统下的装载方式
        mysql> LOAD DATA LOCAL INFILE '/home/qs/pet.txt' INTO TABLE pet;
            UNIX系统下的装载方式
        mysql> LOAD DATA LOCAL INFILE '/home/qs/pet.txt' INTO TABLE pet LINES TERMINATED BY '\r';
            MACOS下的装载方式
        mysql> INSERT INTO pet
            -> VALUES('Puffball', 'Diane', 'hamster', 'f', '1999-03-30', NULL);
            一条插入语句
        ++++++++++++++++++++
    
        3.3.4从表里边获取数据
        ++++++++++++++++++++
    
            3.3.4.1选择所有数据
            ====================
            mysql> SELECT * FROM pet;
    
            3.3.4.2选择特定的行
            ====================
            mysql> SELECT * FROM pet WHERE name = 'Bowser';
            mysql> SELECT * FROM pet WHERE name = 'bowser'; 大小寫不敏感
    
            mysql> SELECT * FROM pet WHERE birth >= '1998-1-1';
            mysql> SELECT * FROM pet WHERE species = 'dog' AND sex = 'f';
            mysql> SELECT * FROM pet WHERE species = 'snake' OR species = 'bird';
            mysql> SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm') OR (species = 'dog' AND sex = 'f');
    
            3.3.4.3選擇特定的列
            ====================
            mysql> SELECT name, birth FROM pet;
            mysql> SELECT owner FROM pet;
            mysql> SELECT DISTINCT owner FROM pet;
            mysql> SELECT name, species, birth FROM pet WHERE species = 'dog' OR species = 'cat';
    
            3.3.4.4對行進行排序
            ====================
            mysql> SELECT name, birth FROM pet ORDER BY name;
            mysql> SELECT name, birth FROM pet ORDER BY birth;
            mysql> SELECT * FROM t_dept ORDER BY BINARY dname; 如果需要明確區分大小寫，使用這個
            mysql> SELECT name, birth FROM pet ORDER BY birth DESC; 明確指定降序排列，默認是升序排列
            mysql> SELECT name, species, birth FROM pet ORDER BY species, birth DESC;
    
            3.3.4.5日期計算
            ====================
            mysql> SELECT name, birth, CURDATE(), TIMESTAMPDIFF(YEAR, birth, CURDATE()) AS age FROM pet; 計算生日
            mysql> SELECT name, birth, CURDATE(), TIMESTAMPDIFF(YEAR, birth, CURDATE()) AS age FROM pet ORDER BY name; 最好給排個序
    
            mysql> SELECT name, birth, CURDATE(), TIMESTAMPDIFF(YEAR, birth, CURDATE()) AS age FROM pet WHERE death IS NOT NULL ORDER BY name;
            下面這兩個不會報錯，但是查詢結果是錯的，不能用
            mysql> SELECT name, birth, CURDATE(), TIMESTAMPDIFF(YEAR, birth, CURDATE()) AS age FROM pet WHERE death <> NULL ORDER BY name;
            mysql> SELECT name, birth, CURDATE(), TIMESTAMPDIFF(YEAR, birth, CURDATE()) AS age FROM pet WHERE death != NULL ORDER BY name;
    
            mysql> SELECT name, birth, MONTH(birth) FROM pet; 
            mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = 5; 從日期裡邊抽取月份
            mysql> SELECT YEAR(CURDATE()), MONTH(CURDATE()), DAYOFMONTH(CURDATE()) FROM DUAL; 提取當前的年份、月份和日子
            mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(), INTERVAL 1 MONTH)); 找出下個月出生的寵物
    
            3.3.4.6关于NULL
            ====================
            mysql> SELECT 1 IS NULL, 1 IS NOT NULL;
            mysql> SELECT 1 > NULL, 1 < NULL, 1 = NULL, 1 <> NULL; 任何邏輯運算符和NULL操作的結果都是NULL
            mysql> SELECT * FROM pet ORDER BY death; 升序排列的時候，NULL在前
            mysql> SELECT * FROM pet ORDER BY death DESC; 降序排列的時候，NULL在后
            mysql> SELECT 0 IS NULL, 0 IS NOT NULL, '' IS NULL, '' IS NOT NULL, NULL IS NULL; 定義為NOT NULL的列是可以插入0或者空字符串的（0 IS NOT NULL | '' IS NOT NULL）
    
            3.3.4.7模式匹配
            ====================
            標準
            LIKE | NOT LIKE | _ | %
            mysql> SELECT * FROM pet WHERE name LIKE 'b%';
            mysql> SELECT * FROM pet WHERE name LIKE '%FY';
            mysql> SELECT * FROM pet WHERE name LIKE '%w%';
            mysql> SELECT * FROM pet WHERE name LIKE '_____';
            擴展的正則表達式 
            REGEXP | NOT REGEXP | RLIKE | NOT RLIKE | . | [] | * | ^ | $ 
            mysql> SELECT * FROM pet WHERE name REGEXP '^b'; 以b開頭，大小寫不敏感
            mysql> SELECT * FROM pet WHERE name REGEXP BINARY '^b'; 以b開頭，大小寫敏感
            mysql> SELECT * FROM pet WHERE name REGEXP BINARY 'fy$'; 以fy結尾
            mysql> SELECT * FROM pet WHERE name REGEXP BINARY 'w'; 包含w
            mysql> SELECT * FROM pet WHERE name REGEXP BINARY '^.....$';
            mysql> SELECT * FROM pet WHERE name REGEXP BINARY '^.{5}$';
    
            3.3.4.8行計數
            ====================
            mysql> SELECT COUNT(*) FROM pet;
            mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner;
            mysql> SELECT sex, COUNT(*) FROM pet GROUP BY sex;
            mysql> SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex;
            mysql> SELECT species, sex, COUNT(*) FROM pet WHERE species = 'dog' OR species = 'cat' GROUP BY species, sex;
            //
            mysql> SET sql_mode = 'ONLY_FULL_GROUP_BY';
            Query OK, 0 rows affected (0.00 sec)
    
            mysql> SELECT name, COUNT(*) FROM pet;
            ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
            mysql> SET sql_mode = '';
            Query OK, 0 rows affected (0.00 sec)
    
            mysql> SELECT name, COUNT(*) FROM pet;
            +--------+----------+
            | name   | COUNT(*) |
            +--------+----------+
            | Fluffy |        9 |
            +--------+----------+
            1 row in set (0.00 sec)
    
            mysql>
            //
    
            3.3.4.9使用多表
            ====================
            event.txt 
            Fluffy  1995-05-15  litter  4 kittens, 3 female, 1 male
            Buffy   1993-06-23  litter  5 puppies, 2 female, 3 male
            Buffy   1994-06-19  litter  3 puppies, 3 female
            Chirpy  1999-03-21  vet need beak straightened
            Slim    1997-08-03  vet broken rib
            Bowser  1991-10-12  kennel
            Fang    1991-10-12  kennel
            Fang    1998-08-28  birthday    Gave him a new chew toy
            Claws   1998-03-17  brithday    Gave him a new flea collar
            Whistler    1998-12-09  birthday    First birthday
    
            mysql> LOAD DATA LOCAL INFILE 'D:/教程/其他/mysql-study/mysql5.1-doc/event.txt' INTO TABLE event LINES TERMINATED BY '\r\n';
    
            mysql> SELECT pet.name, (YEAR(date) - YEAR(birth)) - (RIGHT(date, 5) < RIGHT(birth, 5)) AS age, remark FROM pet INNER JOIN event ON pet.name = event.name WHERE event.type = 'litter';
            mysql> SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species FROM pet AS p1 INNER JOIN pet AS p2 ON p1.species = p2.species AND p1.sex = 'f' AND p2.sex = 'm';
    
    3.4獲取數據庫和表的信息       
    ---------------------
    mysql> SELECT DATABASE(); 查看當前正在使用的數據庫
    mysql> SHOW TABLES; 查看當前使用的數據庫中的表
    mysql> DESCRIBE pet; 查看表结构
    
    3.5使用批量模式
    ---------------------
    --force 使用選項
    shell> mysql < batch-file 
    C:\> mysql -e "source batch-file"
    shell> mysql -h host -u user -p < batch-file
    shell> mysql < batch-file | more
    shell> mysql < batch-file > mysql.out 
    
    批量模式輸出非常簡潔
    C:\Users\qs>mysql < F:/mysql.txt
    name    owner   species sex     birth   death
    Fluffy  Horold  cat     f       1993-02-04      NULL
    Claws   Gwen    cat     m       1994-03-17      NULL
    Buffy   Horold  dog     f       1989-05-13      NULL
    Fang    Benny   dog     m       1990-08-27      NULL
    Bowser  Diane   dog     m       1989-08-31      1995-07-29
    Chirpy  Gwen    bird    f       1998-09-11      NULL
    Whistler        Gwen    bird    NULL    1997-12-09      NULL
    Slim    Benny   snake   m       1996-04-29      NULL
    Puffball        Diane   hamster f       1999-03-30      NULL
    
    C:\Users\qs>
    
    如果想讓輸出和交互模式一樣，可以使用-t 選項
    C:\Users\qs>mysql -t < F:/mysql.txt
    +----------+--------+---------+------+------------+------------+
    | name     | owner  | species | sex  | birth      | death      |
    +----------+--------+---------+------+------------+------------+
    | Fluffy   | Horold | cat     | f    | 1993-02-04 | NULL       |
    | Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       |
    | Buffy    | Horold | dog     | f    | 1989-05-13 | NULL       |
    | Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       |
    | Bowser   | Diane  | dog     | m    | 1989-08-31 | 1995-07-29 |
    | Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL       |
    | Whistler | Gwen   | bird    | NULL | 1997-12-09 | NULL       |
    | Slim     | Benny  | snake   | m    | 1996-04-29 | NULL       |
    | Puffball | Diane  | hamster | f    | 1999-03-30 | NULL       |
    +----------+--------+---------+------+------------+------------+
    
    C:\Users\qs>
    
    如果想讓輸出顯示操作語句，可以使用-v選項
    C:\Users\qs>mysql -v < F:/mysql.txt
    --------------
    SELECT * FROM pet
    --------------
    
    name    owner   species sex     birth   death
    Fluffy  Horold  cat     f       1993-02-04      NULL
    Claws   Gwen    cat     m       1994-03-17      NULL
    Buffy   Horold  dog     f       1989-05-13      NULL
    Fang    Benny   dog     m       1990-08-27      NULL
    Bowser  Diane   dog     m       1989-08-31      1995-07-29
    Chirpy  Gwen    bird    f       1998-09-11      NULL
    Whistler        Gwen    bird    NULL    1997-12-09      NULL
    Slim    Benny   snake   m       1996-04-29      NULL
    Puffball        Diane   hamster f       1999-03-30      NULL
    
    C:\Users\qs>
    
    3.6普通查詢舉例
    ---------------------
    準備實驗環境
    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));
    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);
    
        3.6.1查詢列的最大值
        ++++++++++++++++++++
        mysql> SELECT MAX(article) FROM shop;
    
        3.6.2查詢指定列的最大值所對應的記錄
        ++++++++++++++++++++
        mysql> SELECT article, dealer, price FROM shop WHERE price = (SELECT MAX(price) FROM shop);
        mysql> SELECT article, dealer, price FROM shop ORDER BY price DESC LIMIT 1; 另一種方法
        另一種方法（左外連接）
        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;
    
        3.6.3每一組的最大值
        ++++++++++++++++++++
        mysql> SELECT article, MAX(price) FROM shop GROUP BY article;
    
        3.6.4
        ++++++++++++++++++++
        使用關聯子查詢的方式-效率低下
        mysql> SELECT article, dealer, price
            -> 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;
        使用左外連接
        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;
    
        3.6.5使用用戶自定義變量
        ++++++++++++++++++++
        mysql> SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;
        mysql> SELECT * FROM shop WHERE price=@min_price OR price=@max_price;
    
        3.6.6使用外鍵
        ++++++++++++++++++++
    
        3.6.7
        ++++++++++++++++++++
    
        3.6.8
        ++++++++++++++++++++
    
        3.6.9
        ++++++++++++++++++++
    
    3.7mysql和Apache一起使用
    ---------------------