1. Entering Queries
1. check out the version number and the current data for server.
[root@MaxwellDBA ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 23
Server version: 8.0.26 Source distribution
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select version(),current_date;
+-----------+--------------+
| version() | current_date |
+-----------+--------------+
| 8.0.26 | 2022-10-11 |
+-----------+--------------+
1 row in set (0.00 sec)
mysql> 2. mysql can be as a simple calculator
mysql> SELECT SIN(PI()/4),(4+1)*5;
+--------------------+---------+
| SIN(PI()/4) | (4+1)*5 |
+--------------------+---------+
| 0.7071067811865475 | 25 |
+--------------------+---------+
1 row in set (0.01 sec)
mysql> 3. You can enter multiple statements on a single line ,just use end each one with a semicolon
mysql> select version();select NOW();
+-----------+
| version() |
+-----------+
| 8.0.26 |
+-----------+
1 row in set (0.00 sec)
+---------------------+
| NOW() |
+---------------------+
| 2022-10-11 18:22:53 |
+---------------------+
1 row in set (0.00 sec)
mysql> 2. Create and Using a Database
This section shows how to perform the following operations:
- Create a database
- Create a table
- Load data into the table
- Use multiple tables
[root@MaxwellDBA ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 27
Server version: 8.0.26 Source distribution
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql>
mysql> 2.1 Creating and Selecting a Database
mysql>
mysql> CREATE DATABASE menagerie;
Query OK, 1 row affected (0.01 sec)
mysql> use menagerie
Database changed
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| menagerie |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql>
mysql> select database();
+------------+
| database() |
+------------+
| menagerie |
+------------+
1 row in set (0.00 sec)
mysql> 2.2 Creating a Table
mysql>
mysql> SHOW TABLES;
Empty set (0.00 sec)
mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
Query OK, 0 rows affected (0.02 sec)
mysql> SHOW TABLES;
+---------------------+
| Tables_in_menagerie |
+---------------------+
| pet |
+---------------------+
1 row in set (0.01 sec)
mysql> DESCRIBE pet;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name | varchar(20) | YES | | NULL | |
| owner | varchar(20) | YES | | NULL | |
| species | varchar(20) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| birth | date | YES | | NULL | |
| death | date | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> 3.Loading Data into a Table(Linux Enviroment)
The LOAD DATA and INSERT statements are useful.
Fluffy Harold cat f 1993-02-04 \N
Claws Gwen cat m 1994-03-17 \N
Buffy Harold 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[root@MaxwellDBA mysql-files]# pwd
/var/lib/mysql-files
[root@MaxwellDBA mysql-files]# pwd
/var/lib/mysql-files
[root@MaxwellDBA mysql-files]# cat pet.txt
Fluffy Harold cat f 1993-02-04 \N
Claws Gwen cat m 1994-03-17 \N
Buffy Harold 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
[root@MaxwellDBA mysql-files]#
mysql> use menagerie
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql>
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| menagerie |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> select database();
+------------+
| database() |
+------------+
| menagerie |
+------------+
1 row in set (0.00 sec)
mysql> show tables;
+---------------------+
| Tables_in_menagerie |
+---------------------+
| pet |
+---------------------+
1 row in set (0.00 sec)
mysql> describe pet;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name | varchar(20) | YES | | NULL | |
| owner | varchar(20) | YES | | NULL | |
| species | varchar(20) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| birth | date | YES | | NULL | |
| death | date | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> show variables like '%secure%';
+--------------------------+-----------------------+
| Variable_name | Value |
+--------------------------+-----------------------+
| require_secure_transport | OFF |
| secure_file_priv | /var/lib/mysql-files/ |
+--------------------------+-----------------------+
2 rows in set (0.00 sec)
mysql> LOAD DATA INFILE '/var/lib/mysql-files/pet.txt' INTO TABLE pet;
Query OK, 8 rows affected (0.00 sec)
Records: 8 Deleted: 0 Skipped: 0 Warnings: 0
mysql>
mysql> INSERT INTO pet VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);
Query OK, 1 row affected (0.00 sec)
mysql> 4.Retrieving Information from a Table
SELECT what_to_select
FROM which_table
WHERE conditions_to_satisfy;4.1 Selecting All Data
mysql> select * from pet;
+----------+--------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+----------+--------+---------+------+------------+------------+
| Fluffy | Harold | cat | f | 1993-02-04 | NULL |
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
| Fang | Benny | dog | m | 1990-08-27 | NULL |
| Bowser | Diane | dog | m | 1979-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 |
+----------+--------+---------+------+------------+------------+
9 rows in set (0.00 sec)
mysql> UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from pet where name = 'Bowser';
+--------+-------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+-------+---------+------+------------+------------+
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
+--------+-------+---------+------+------------+------------+
1 row in set (0.00 sec)
mysql> 4.2 Selecting Particular Rows
mysql> select * from pet where name = 'Bowser';
+--------+-------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+-------+---------+------+------------+------------+
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
+--------+-------+---------+------+------------+------------+
1 row in set (0.00 sec)
mysql> select * from pet where birth >= '1998-1-1';
+----------+-------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+----------+-------+---------+------+------------+-------+
| Chirpy | Gwen | bird | f | 1998-09-11 | NULL |
| Puffball | Diane | hamster | f | 1999-03-30 | NULL |
+----------+-------+---------+------+------------+-------+
2 rows in set (0.00 sec)
mysql> select * from pet where species = 'dog' and sex = 'f';
+-------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+-------+--------+---------+------+------------+-------+
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+
1 row in set (0.00 sec)
mysql> select * from pet where species = 'snake' or species = 'bird';
+----------+-------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+----------+-------+---------+------+------------+-------+
| Chirpy | Gwen | bird | f | 1998-09-11 | NULL |
| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |
| Slim | Benny | snake | m | 1996-04-29 | NULL |
+----------+-------+---------+------+------------+-------+
3 rows in set (0.00 sec)
mysql> select * from pet where (species = 'cat' and sex = 'm') or (species = 'dog' and sex = 'f');
+-------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+
2 rows in set (0.00 sec)
mysql> 4.3 Selecting Particular Columns
mysql>
mysql> select name,birth from pet;
+----------+------------+
| name | birth |
+----------+------------+
| Fluffy | 1993-02-04 |
| Claws | 1994-03-17 |
| Buffy | 1989-05-13 |
| Fang | 1990-08-27 |
| Bowser | 1989-08-31 |
| Chirpy | 1998-09-11 |
| Whistler | 1997-12-09 |
| Slim | 1996-04-29 |
| Puffball | 1999-03-30 |
+----------+------------+
9 rows in set (0.00 sec)
mysql> select owner from pet;
+--------+
| owner |
+--------+
| Harold |
| Gwen |
| Harold |
| Benny |
| Diane |
| Gwen |
| Gwen |
| Benny |
| Diane |
+--------+
9 rows in set (0.00 sec)
mysql> select distinct owner from pet;
+--------+
| owner |
+--------+
| Harold |
| Gwen |
| Benny |
| Diane |
+--------+
4 rows in set (0.00 sec)
mysql> select name,species,birth from pet where species = 'dog' or species = 'cat';
+--------+---------+------------+
| name | species | birth |
+--------+---------+------------+
| Fluffy | cat | 1993-02-04 |
| Claws | cat | 1994-03-17 |
| Buffy | dog | 1989-05-13 |
| Fang | dog | 1990-08-27 |
| Bowser | dog | 1989-08-31 |
+--------+---------+------------+
5 rows in set (0.00 sec)
mysql> 4.4 Sorting Rows
mysql>
mysql> select name,birth from pet order by birth;
+----------+------------+
| name | birth |
+----------+------------+
| Buffy | 1989-05-13 |
| Bowser | 1989-08-31 |
| Fang | 1990-08-27 |
| Fluffy | 1993-02-04 |
| Claws | 1994-03-17 |
| Slim | 1996-04-29 |
| Whistler | 1997-12-09 |
| Chirpy | 1998-09-11 |
| Puffball | 1999-03-30 |
+----------+------------+
9 rows in set (0.00 sec)
mysql> select name birth from pet order by birth desc;
+----------+
| birth |
+----------+
| Whistler |
| Slim |
| Puffball |
| Fluffy |
| Fang |
| Claws |
| Chirpy |
| Buffy |
| Bowser |
+----------+
9 rows in set (0.00 sec)
mysql> select name,species,birth from pet order by species,birth desc;
+----------+---------+------------+
| name | species | birth |
+----------+---------+------------+
| Chirpy | bird | 1998-09-11 |
| Whistler | bird | 1997-12-09 |
| Claws | cat | 1994-03-17 |
| Fluffy | cat | 1993-02-04 |
| Fang | dog | 1990-08-27 |
| Bowser | dog | 1989-08-31 |
| Buffy | dog | 1989-05-13 |
| Puffball | hamster | 1999-03-30 |
| Slim | snake | 1996-04-29 |
+----------+---------+------------+
9 rows in set (0.00 sec)
mysql> 4.5 Date Calculations
mysql>
mysql> select name,birth,curdate(),timestampdiff(year,birth,curdate()) as age from pet;
+----------+------------+------------+------+
| name | birth | curdate() | age |
+----------+------------+------------+------+
| Fluffy | 1993-02-04 | 2022-10-11 | 29 |
| Claws | 1994-03-17 | 2022-10-11 | 28 |
| Buffy | 1989-05-13 | 2022-10-11 | 33 |
| Fang | 1990-08-27 | 2022-10-11 | 32 |
| Bowser | 1989-08-31 | 2022-10-11 | 33 |
| Chirpy | 1998-09-11 | 2022-10-11 | 24 |
| Whistler | 1997-12-09 | 2022-10-11 | 24 |
| Slim | 1996-04-29 | 2022-10-11 | 26 |
| Puffball | 1999-03-30 | 2022-10-11 | 23 |
+----------+------------+------------+------+
9 rows in set (0.00 sec)
mysql> select name,birth,curdate(),timestampdiff(year,birth,curdate()) as age from pet order by name;
+----------+------------+------------+------+
| name | birth | curdate() | age |
+----------+------------+------------+------+
| Bowser | 1989-08-31 | 2022-10-11 | 33 |
| Buffy | 1989-05-13 | 2022-10-11 | 33 |
| Chirpy | 1998-09-11 | 2022-10-11 | 24 |
| Claws | 1994-03-17 | 2022-10-11 | 28 |
| Fang | 1990-08-27 | 2022-10-11 | 32 |
| Fluffy | 1993-02-04 | 2022-10-11 | 29 |
| Puffball | 1999-03-30 | 2022-10-11 | 23 |
| Slim | 1996-04-29 | 2022-10-11 | 26 |
| Whistler | 1997-12-09 | 2022-10-11 | 24 |
+----------+------------+------------+------+
9 rows in set (0.00 sec)
mysql> select name,birth,curdate(),timestampdiff(year,birth,curdate()) as age from pet order by age;
+----------+------------+------------+------+
| name | birth | curdate() | age |
+----------+------------+------------+------+
| Puffball | 1999-03-30 | 2022-10-11 | 23 |
| Chirpy | 1998-09-11 | 2022-10-11 | 24 |
| Whistler | 1997-12-09 | 2022-10-11 | 24 |
| Slim | 1996-04-29 | 2022-10-11 | 26 |
| Claws | 1994-03-17 | 2022-10-11 | 28 |
| Fluffy | 1993-02-04 | 2022-10-11 | 29 |
| Fang | 1990-08-27 | 2022-10-11 | 32 |
| Buffy | 1989-05-13 | 2022-10-11 | 33 |
| Bowser | 1989-08-31 | 2022-10-11 | 33 |
+----------+------------+------------+------+
9 rows in set (0.00 sec)
mysql> select name,birth,curdate(),timestampdiff(year,birth,curdate()) as age from pet where death is not null order by age;
+--------+------------+------------+------+
| name | birth | curdate() | age |
+--------+------------+------------+------+
| Bowser | 1989-08-31 | 2022-10-11 | 33 |
+--------+------------+------------+------+
1 row in set (0.00 sec)
mysql> select name,birth,month(birth) from pet;
+----------+------------+--------------+
| name | birth | month(birth) |
+----------+------------+--------------+
| Fluffy | 1993-02-04 | 2 |
| Claws | 1994-03-17 | 3 |
| Buffy | 1989-05-13 | 5 |
| Fang | 1990-08-27 | 8 |
| Bowser | 1989-08-31 | 8 |
| Chirpy | 1998-09-11 | 9 |
| Whistler | 1997-12-09 | 12 |
| Slim | 1996-04-29 | 4 |
| Puffball | 1999-03-30 | 3 |
+----------+------------+--------------+
9 rows in set (0.00 sec)
mysql> select name,birth from pet where month(birth) = 5;
+-------+------------+
| name | birth |
+-------+------------+
| Buffy | 1989-05-13 |
+-------+------------+
1 row in set (0.00 sec)
mysql> select name,birth from pet where month(birth) = month(date_add(curdate(),interval 1 month));
Empty set (0.00 sec)
mysql> select name,birth from pet
-> where month(birth) = mod(month(curdate()),12)+1;
Empty set (0.00 sec)
mysql> # MONTH() returns a number between 1 and 12. And MOD(something,12) returns a number between 0 and 11. So the addition has to be after the MOD(), otherwise we would go from November (11) to January (1)
mysql> select '2018-10-31' + INTERVAL 1 DAY;
+-------------------------------+
| '2018-10-31' + INTERVAL 1 DAY |
+-------------------------------+
| 2018-11-01 |
+-------------------------------+
1 row in set (0.00 sec)
mysql> select '2018-10-32' + INTERVAL 1 DAY;
+-------------------------------+
| '2018-10-32' + INTERVAL 1 DAY |
+-------------------------------+
| NULL |
+-------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> SHOW WARNINGS;
+---------+------+----------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------+
| Warning | 1292 | Incorrect datetime value: '2018-10-32' |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)
mysql> 4.6 Working with NULL Values
mysql>
mysql> SELECT 1 IS NULL, 1 IS NOT NULL;
+-----------+---------------+
| 1 IS NULL | 1 IS NOT NULL |
+-----------+---------------+
| 0 | 1 |
+-----------+---------------+
1 row in set (0.00 sec)
mysql> SELECT 1 = NULL,1 <> NULL, 1 < NULL, 1 > NULL;
+----------+-----------+----------+----------+
| 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL |
+----------+-----------+----------+----------+
| NULL | NULL | NULL | NULL |
+----------+-----------+----------+----------+
1 row in set (0.00 sec)
mysql> SELECT 0 IS NULL,0 IS NOT NULL, '' IS NULL ,'' IS NOT NULL;
+-----------+---------------+------------+----------------+
| 0 IS NULL | 0 IS NOT NULL | '' IS NULL | '' IS NOT NULL |
+-----------+---------------+------------+----------------+
| 0 | 1 | 0 | 1 |
+-----------+---------------+------------+----------------+
1 row in set (0.00 sec)
mysql> 4.7 Pattern Matching
mysql>
mysql>
mysql> SELECT * FROM pet where name like 'b%';
+--------+--------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+--------+---------+------+------------+------------+
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM pet where name like '%fy';
+--------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+--------+--------+---------+------+------------+-------+
| Fluffy | Harold | cat | f | 1993-02-04 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+--------+--------+---------+------+------------+-------+
2 rows in set (0.00 sec)
mysql> select * from pet where name like '%w%';
+----------+-------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+----------+-------+---------+------+------------+------------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |
+----------+-------+---------+------+------------+------------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM pet WHERE name LIKE '_____';
+-------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM pet where regexp_like(name,'^b');
+--------+--------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+--------+---------+------+------------+------------+
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, '^b' COLLATE utf8mb4_0900_as_cs);
Empty set (0.00 sec)
mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, BINARY '^b');
ERROR 3995 (HY000): Character set 'utf8mb4_0900_ai_ci' cannot be used in conjunction with 'binary' in call to regexp_like.
mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, '^b', 'c');
Empty set (0.00 sec)
mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, 'fy$');
+--------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+--------+--------+---------+------+------------+-------+
| Fluffy | Harold | cat | f | 1993-02-04 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+--------+--------+---------+------+------------+-------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, 'w');
+----------+-------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+----------+-------+---------+------+------------+------------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |
+----------+-------+---------+------+------------+------------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, '^.....$');
+-------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, '^.{5}$')
-> ;
+-------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+
2 rows in set (0.00 sec)
mysql> 4.8 Counting Rows
mysql> select count(*) from pet;
+----------+
| count(*) |
+----------+
| 9 |
+----------+
1 row in set (0.00 sec)
mysql> select owner,count(*) from pet group by owner;
+--------+----------+
| owner | count(*) |
+--------+----------+
| Harold | 2 |
| Gwen | 3 |
| Benny | 2 |
| Diane | 2 |
+--------+----------+
4 rows in set (0.00 sec)
mysql> select species,count(*) from pet group by species;
+---------+----------+
| species | count(*) |
+---------+----------+
| cat | 2 |
| dog | 3 |
| bird | 2 |
| snake | 1 |
| hamster | 1 |
+---------+----------+
5 rows in set (0.00 sec)
mysql> select sex,count(*) from pet group by sex;
+------+----------+
| sex | count(*) |
+------+----------+
| f | 4 |
| m | 4 |
| NULL | 1 |
+------+----------+
3 rows in set (0.01 sec)
mysql> select species,sex,count(*) from pet group by species,sex;
+---------+------+----------+
| species | sex | count(*) |
+---------+------+----------+
| cat | f | 1 |
| cat | m | 1 |
| dog | f | 1 |
| dog | m | 2 |
| bird | f | 1 |
| bird | NULL | 1 |
| snake | m | 1 |
| hamster | f | 1 |
+---------+------+----------+
8 rows in set (0.00 sec)
mysql> select species,sex,count(*) from pet
-> where species = 'dog' or species = 'cat'
-> group by species,sex;
+---------+------+----------+
| species | sex | count(*) |
+---------+------+----------+
| cat | f | 1 |
| cat | m | 1 |
| dog | f | 1 |
| dog | m | 2 |
+---------+------+----------+
4 rows in set (0.01 sec)
mysql> select species,sex,count(*) from pet
-> where sex is not null
-> group by species,sex;
+---------+------+----------+
| species | sex | count(*) |
+---------+------+----------+
| cat | f | 1 |
| cat | m | 1 |
| dog | f | 1 |
| dog | m | 2 |
| bird | f | 1 |
| snake | m | 1 |
| hamster | f | 1 |
+---------+------+----------+
7 rows in set (0.00 sec)
mysql> SELECT owner, COUNT(*) FROM pet;
ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'menagerie.pet.owner'; this is incompatible with sql_mode=only_full_group_by
mysql> SET sql_mode = '';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT owner, COUNT(*) FROM pet;
+--------+----------+
| owner | COUNT(*) |
+--------+----------+
| Harold | 9 |
+--------+----------+
1 row in set (0.00 sec)
mysql> SET sql_mode = 'ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT owner, COUNT(*) FROM pet;
ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'menagerie.pet.owner'; this is incompatible with sql_mode=only_full_group_by
mysql> 4.9 Using More Than one Table
[root@MaxwellDBA mysql-files]# vim event.txt
[root@MaxwellDBA mysql-files]# rm -rf event.txt
[root@MaxwellDBA mysql-files]# vim event.txt
[root@MaxwellDBA mysql-files]# chmod 775 event.txt
[root@MaxwellDBA mysql-files]# cat 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 needed beak straightened
Slim 1997-08-03 vet broken rib
Bowser 1991-10-12 kennel \N
Fang 1991-10-12 kennel \N
Fang 1998-08-28 birthday Gave him a new chew toy
Claws 1998-03-17 birthday Gave him a new flea collar
Whistler 1998-12-09 birthday First birthday
[root@MaxwellDBA mysql-files]# 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 needed beak straightened
Slim 1997-08-03 vet broken rib
Bowser 1991-10-12 kennel \N
Fang 1991-10-12 kennel \N
Fang 1998-08-28 birthday Gave him a new chew toy
Claws 1998-03-17 birthday Gave him a new flea collar
Whistler 1998-12-09 birthday First birthdaymysql>
mysql>
mysql> CREATE TABLE event (name VARCHAR(20), date DATE,type VARCHAR(15), remark VARCHAR(255));
Query OK, 0 rows affected (0.02 sec)
mysql> LOAD DATA INFILE 'event.txt' INTO TABLE event;
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
mysql> show variables like '%secure%';
+--------------------------+-----------------------+
| Variable_name | Value |
+--------------------------+-----------------------+
| require_secure_transport | OFF |
| secure_file_priv | /var/lib/mysql-files/ |
+--------------------------+-----------------------+
2 rows in set (0.01 sec)
mysql> LOAD DATA INFILE '/var/lib/mysql-files/event.txt' INTO TABLE event;
Query OK, 10 rows affected, 3 warnings (0.00 sec)
Records: 10 Deleted: 0 Skipped: 0 Warnings: 3
mysql> SELECT pet.name,
-> TIMESTAMPDIFF(YEAR,birth,date) AS age,
-> remark
-> FROM pet INNER JOIN event
-> ON pet.name = event.name
-> WHERE event.type = 'litter';
+-------+------+-----------------------------+
| name | age | remark |
+-------+------+-----------------------------+
| Buffy | 5 | 3 puppies, 3 female |
| Buffy | 4 | 5 puppies, 2 female, 3 male |
+-------+------+-----------------------------+
2 rows in set (0.00 sec)
mysql> select * from pet;
+----------+--------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+----------+--------+---------+------+------------+------------+
| Fluffy | Harold | cat | f | 1993-02-04 | NULL |
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | 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 |
+----------+--------+---------+------+------------+------------+
9 rows in set (0.00 sec)
mysql> select * from event;
+----------+------------+----------+-----------------------------+
| name | date | type | remark |
+----------+------------+----------+-----------------------------+
| tter | 0000-00-00 | NULL | NULL |
| 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 | needed beak straightened |
| Slim | 1997-08-03 | vet | broken rib |
| Bowser | 1991-10-12 | kennel | NULL |
| Fang | 1991-10-12 | kennel | NULL |
| Fang | 1998-08-28 | birthday | Gave him a new chew toy |
| Claws | 1998-03-17 | birthday | Gave him a new flea collar |
| Whistler | 1998-12-09 | birthday | First birthday |
+----------+------------+----------+-----------------------------+
10 rows in set (0.00 sec)
mysql> delete from event;
Query OK, 10 rows affected (0.00 sec)
mysql> LOAD DATA INFILE '/var/lib/mysql-files/event.txt' INTO TABLE event;
Query OK, 10 rows affected (0.00 sec)
Records: 10 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select * from event;
+----------+------------+----------+-----------------------------+
| name | date | type | remark |
+----------+------------+----------+-----------------------------+
| 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 | needed beak straightened |
| Slim | 1997-08-03 | vet | broken rib |
| Bowser | 1991-10-12 | kennel | NULL |
| Fang | 1991-10-12 | kennel | NULL |
| Fang | 1998-08-28 | birthday | Gave him a new chew toy |
| Claws | 1998-03-17 | birthday | Gave him a new flea collar |
| Whistler | 1998-12-09 | birthday | First birthday |
+----------+------------+----------+-----------------------------+
10 rows in set (0.00 sec)
mysql> SELECT pet.name,
-> TIMESTAMPDIFF(YEAR,birth,date) AS age,
-> remark
-> FROM pet INNER JOIN event
-> ON pet.name = event.name
-> WHERE event.type = 'litter';
+--------+------+-----------------------------+
| name | age | remark |
+--------+------+-----------------------------+
| Fluffy | 2 | 4 kittens, 3 female, 1 male |
| Buffy | 5 | 3 puppies, 3 female |
| Buffy | 4 | 5 puppies, 2 female, 3 male |
+--------+------+-----------------------------+
3 rows in set (0.00 sec)
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 p1.death IS NULL
-> AND p2.sex = 'm' AND p2.death IS NULL;
+--------+------+-------+------+---------+
| name | sex | name | sex | species |
+--------+------+-------+------+---------+
| Fluffy | f | Claws | m | cat |
| Buffy | f | Fang | m | dog |
+--------+------+-------+------+---------+
2 rows in set (0.00 sec)
mysql> 5 .Getting Information About Databases and Tables
mysql>
mysql> select DATABASE();
+------------+
| DATABASE() |
+------------+
| menagerie |
+------------+
1 row in set (0.00 sec)
mysql> show tables;
+---------------------+
| Tables_in_menagerie |
+---------------------+
| event |
| pet |
+---------------------+
2 rows in set (0.00 sec)
mysql> describe pet;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name | varchar(20) | YES | | NULL | |
| owner | varchar(20) | YES | | NULL | |
| species | varchar(20) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| birth | date | YES | | NULL | |
| death | date | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> 6.Using mysql in Batch Mode
$> mysql < batch-filerunning mysql under windows
C:\> mysql -e "source batch-file"need to specify connection parameters on the command line,
$> mysql -h host -u user -p < batch-file
Enter password: ********have a query that produces a lot of output, you can run the output through a pager rather than watching it scroll off the top of your screen:
$> mysql < batch-file | morecatch the output in a file for further processing:
$> mysql < batch-file > mysql.out
2224
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
