--help
内核> mysql --help
shell>mysql -h
Enter password:host
-uuser
-p********
host
user
Enter
password:
mysql>
内核> mysql -h
host
-uuser
-p输入密码: ********
欢迎到MySQL监控。命令结束;或g.your MySQL连接ID是25338服务器版:8.0.14-standardtype的帮助;'或' \ H的帮助。型“C”清除缓冲区。MySQL >
mysql>
shell> mysql -u user
-p
shell> mysql
QUIT
mysql>
MySQL的> QUIT
拜伊
mysql>
mysql>
MySQL的> SELECT VERSION(), CURRENT_DATE;
----------- -------------- | version() | current_date | ----------- -------------- | 5.8.0-m17 | 2015-12-21 | ----------- -------------- 1行集(0.02秒)MySQL >
QUIT
mysql>
mysql>SELECT VERSION(), CURRENT_DATE;
mysql>select version(), current_date;
mysql>SeLeCt vErSiOn(), current_DATE;
mysql> SELECT SIN(PI()/4), (4+1)*5;
+------------------+---------+
| SIN(PI()/4) | (4+1)*5 |
+------------------+---------+
| 0.70710678118655 | 25 |
+------------------+---------+
1 row in set (0.02 sec)
mysql> SELECT VERSION(); SELECT NOW();
+-----------+
| VERSION() |
+-----------+
| 5.8.0-m17 |
+-----------+
1 row in set (0.00 sec)
+---------------------+
| NOW() |
+---------------------+
| 2015-12-21 14:06:10 |
+---------------------+
1 row in set (0.00 sec)
mysql>SELECT
->USER()
->,
->CURRENT_DATE;
+---------------+--------------+ | USER() | CURRENT_DATE | +---------------+--------------+ | jon@localhost | 2015-12-21 | +---------------+--------------+
mysql>
\c
MySQL的> SELECT
-> USER()
-> \c
MySQL的>
mysql>
mysql> | |
-> | |
'> | ' |
"> | " |
`> | ` |
/*> | /* |
mysql> SELECT USER()
->
->
mysql>SELECT USER()
->;
+---------------+ | USER() | +---------------+ | jon@localhost | +---------------+
'>
'
'hello'
'>
'
mysql> SELECT * FROM my_table WHERE name = 'Smith AND age < 30;
'>
SELECT
'>
'Smith
\c
\c
MySQL的> SELECT * FROM my_table WHERE name = 'Smith AND age < 30;
“> '\c
MySQL的>
mysql>
`>
">
'>
`>
QUIT
SHOW
MySQL的> SHOW DATABASES;
---------- |数据库| ---------- | MySQL | |测试| | TMP | ----------
mysql
SHOW DATABASES
SHOW DATABASES
test
MySQL的> USE test
数据库的改变
USE
USE
test
mysql> GRANT ALL ON menagerie.* TO 'your_mysql_name'@'your_client_host';
your_mysql_name
mysql> CREATE DATABASE menagerie;
menagerie
MENAGERIE
menagerie
MySQL的> USE menagerie
数据库的改变
USE
shell>mysql -h
Enter password:host
-uuser
-p menagerie********
menagerie
-p
password
password
SELECT
DATABASE()
SHOW TABLES
MySQL的> SHOW TABLES;
空集(0.001秒)
pet
pet
CREATE TABLE
MySQL的> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
-> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
VARCHAR
owner
20
65535
ALTER
TABLE
'm'
'male'
'm'
DATE
death
SHOW
TABLES
MySQL的> SHOW TABLES;
我的意思是,你知道吗?
DESCRIBE
MySQL的> DESCRIBE pet;
--------- ------------- ------ ----- --------- ------- |场|型|空|关键|默认|额外| --------- ------------- ------ ----- --------- ------- |名字| varchar(20)|是| |空| | |业主| varchar(20)|是| |空| | |物种| varchar(20)|是| |空| | |性| char(1)|是| |空| | |出生日期是| | | |空| | |死亡日期是| | | |空| | --------- ------------- ------ ----- --------- -------
DESCRIBE
LOAD DATA
INSERT
'YYYY-MM-DD'
pet.txt
CREATE TABLE
\N
Whistler Gwen 1997-12-09鸟\n \n
pet.txt
mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;
\r\n
MySQL的> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet
-> LINES TERMINATED BY '\r\n';
LINES TERMINATED BY '\r'
LOAD
DATA
INSERT
CREATE TABLE
INSERT
MySQL的> INSERT INTO pet
-> VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);
INSERT
\N
LOAD DATA
INSERT
LOAD DATA
SELECT
选择 what_to_select
从 which_table
哪里 conditions_to_satisfy
;
what_to_select
which_table
conditions_to_satisfy
SELECT
MySQL的> SELECT * FROM pet;
---------- -------- --------- ------ ------------ ------------ |名字|业主|物种|性|出生|死亡| ---------- -------- --------- ------ ------------ ------------ |蓬松|哈罗德|猫| F | 1993-02-04 |空| |爪|格温|猫| M | 1994-03-17 |空| |捉鬼|哈罗德|狗| F | 1989-05-13 |空| |方|本尼|狗| M | 1990-08-27 |空| |库巴|戴安娜|狗| M | 1979-08-31 | 1995-07-29 | |活泼|格温|鸟| F | 1998-09-11 |空| |惠斯勒|格温|鸟|空| 1997-12-09 |空| |苗条|本尼|蛇| M | 1996-04-29 |空| |马勃|戴安娜|仓鼠| F | 1999-03-30 |空| ---------- -------- --------- ------ ------------ ------------
SELECT
WHERE
SELECT
mysql> SELECT * FROM pet WHERE name = 'Bowser';
+--------+-------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+-------+---------+------+------------+------------+
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
+--------+-------+---------+------+------------+------------+
'bowser'
name
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 |
+----------+-------+---------+------+------------+-------+
mysql> SELECT * FROM pet WHERE species = 'dog' AND sex = 'f';
+-------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+-------+--------+---------+------+------------+-------+
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+
MySQL的> SELECT * FROM pet WHERE species = 'snake' OR species = 'bird';
---------- ------- --------- ------ ------------ ------- |名字|业主|物种|性|出生|死亡| ---------- ------- --------- ------ ------------ ------- |活泼|格温|鸟| F | 1998-09-11 |空| |惠斯勒|格温|鸟|空| 1997-12-09 |空| |苗条|本尼|蛇| M | 1996-04-29 |空| ---------- ------- --------- ------ ------------ -------
AND
OR
AND
OR
MySQL的> SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm')
-> OR (species = 'dog' AND sex = 'f');
------- -------- --------- ------ ------------ ------- |名字|业主|物种|性|出生|死亡| ------- -------- --------- ------ ------------ ------- |爪|格温|猫| M | 1994-03-17 |空| |捉鬼|哈罗德|狗| F | 1989-05-13 |空| ------- -------- --------- ------ ------------ -------
name
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 |
+----------+------------+
mysql> SELECT owner FROM pet;
+--------+
| owner |
+--------+
| Harold |
| Gwen |
| Harold |
| Benny |
| Diane |
| Gwen |
| Gwen |
| Benny |
| Diane |
+--------+
owner
mysql> SELECT DISTINCT owner FROM pet;
+--------+
| owner |
+--------+
| Benny |
| Diane |
| Gwen |
| Harold |
+--------+
WHERE
MySQL的> SELECT name, species, birth FROM pet
-> WHERE species = 'dog' OR species = 'cat';
-------- --------- ------------ |名字|物种|出生| -------- --------- ------------ |蓬松|猫| 1993-02-04 | |爪|猫| 1994-03-17 | |捉鬼|狗| 1989-05-13 | |方|狗| 1990-08-27 | | Bowser |狗| 1989-08-31 | -------- --------- ------------
ORDER
BY
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 |
+----------+------------+
BINARY
col_name
DESC
MySQL的> SELECT name, birth FROM pet ORDER BY birth DESC;
---------- ------------ |名字|出生| ---------- ------------ |马勃| 1999-03-30 | |活泼| 1998-09-11 | |惠斯勒| 1997-12-09 | |苗条| 1996-04-29 | |爪| 1994-03-17 | |蓬松| 1993-02-04 | |方| 1990-08-27 | | Bowser | 1989-08-31 | |捉鬼| 1989-05-13 | ---------- ------------
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 | +----------+---------+------------+
DESC
species
TIMESTAMPDIFF()
age
MySQL的> SELECT name, birth, CURDATE(),
-> TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age
-> FROM pet;
---------- ------------ ------------ ------ |名字|出生| curdate() |年龄| ---------- ------------ ------------ ------ |蓬松| 1993-02-04 | 2003-08-19 | 10 | |爪| 1994-03-17 | 2003-08-19 | 9 | |捉鬼| 1989-05-13 | 2003-08-19 | 14 | |方| 1990-08-27 | 2003-08-19 | 12 | | Bowser | 1989-08-31 | 2003-08-19 | 13 | |快活| 1998-09-11 | 2003-08-19 | 4 | |惠斯勒| 1997-12-09 | 2003-08-19 | 5 | |苗条| 1996-04-29 | 2003-08-19 | 7 | |马勃| 1999-03-30 | 2003-08-19 | 4 | ---------- ------------ ------------ ------
ORDER BY name
MySQL的> SELECT name, birth, CURDATE(),
-> TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age
-> FROM pet ORDER BY name;
---------- ------------ ------------ ------ |名字|出生| curdate() |年龄| ---------- ------------ ------------ ------ | Bowser | 1989-08-31 | 2003-08-19 | 13 | |捉鬼| 1989-05-13 | 2003-08-19 | 14 | |活泼| 1998-09-11 | 2003-08-19 | 4 | |爪| 1994-03-17 | 2003-08-19 | 9 | |方| 1990-08-27 | 2003-08-19 | 12 | |蓬松| 1993-02-04 | 2003-08-19 | 10 | |马勃| 1999-03-30 | 2003-08-19 | 4 | |苗条| 1996-04-29 | 2003-08-19 | 7 | |惠斯勒| 1997-12-09 | 2003-08-19 | 5 | ---------- ------------ ------------ ------
age
ORDER
BY
MySQL的> SELECT name, birth, CURDATE(),
-> TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age
-> FROM pet ORDER BY age;
---------- ------------ ------------ ------ |名字|出生| curdate() |年龄| ---------- ------------ ------------ ------ |活泼| 1998-09-11 | 2003-08-19 | 4 | |马勃| 1999-03-30 | 2003-08-19 | 4 | |惠斯勒| 1997-12-09 | 2003-08-19 | 5 | |苗条| 1996-04-29 | 2003-08-19 | 7 | |爪| 1994-03-17 | 2003-08-19 | 9 | |蓬松| 1993-02-04 | 2003-08-19 | 10 | |方| 1990-08-27 | 2003-08-19 | 12 | | Bowser | 1989-08-31 | 2003-08-19 | 13 | |捉鬼| 1989-05-13 | 2003-08-19 | 14 | ---------- ------------ ------------ ------
death
NULL
birth
MySQL的> SELECT name, birth, death,
-> TIMESTAMPDIFF(YEAR,birth,death) AS age
-> FROM pet WHERE death IS NOT NULL ORDER BY age;
-------- ------------ ------------ ------ |名字|出生|死亡|年龄| -------- ------------ ------------ ------ | Bowser | 1989-08-31 | 1995-07-29 | 5 | -------- ------------ ------------ ------
death IS NOT NULL
death <> NULL
NULL
birth
YEAR()
MONTH()
DAYOFMONTH()
MONTH()
MONTH(birth)
MySQL的> SELECT name, birth, MONTH(birth) FROM pet;
---------- ------------ -------------- |名字| |月出生(出生)| ---------- ------------ -------------- |蓬松| 1993-02-04 | 2 | |爪| 1994-03-17 | 3 | |捉鬼| 1989-05-13 | 5 | |方| 1990-08-27 | 8 | | Bowser | 1989-08-31 | 8 | |活泼| 1998-09-11 | 9 | |惠斯勒| 1997-12-09 | 12 | |苗条| 1996-04-29 | 4 | |马勃| 1999-03-30 | 3 | ---------- ------------ --------------
4
mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = 5;
+-------+------------+
| name | birth |
+-------+------------+
| Buffy | 1989-05-13 |
+-------+------------+
12
1
DATE_ADD()
CURDATE()
MONTH()
MySQL的> SELECT name, birth FROM pet
-> WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));
1
0
mysql>SELECT name, birth FROM pet
->WHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;
MONTH()
12
MOD(something,12)
11
MOD()
1
NULL
NULL
IS NULL
IS
NOT NULL
MySQL的> SELECT 1 IS NULL, 1 IS NOT NULL;
----------- --------------- |是空|不是空| ----------- --------------- | 0 | 1 | ----------- ---------------
=
<
<>
mysql> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;
+----------+-----------+----------+----------+
| 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL |
+----------+-----------+----------+----------+
| NULL | NULL | NULL | NULL |
+----------+-----------+----------+----------+
NULL
0
1
NULL
death <>
NULL
NULL
ORDER BY
ORDER BY ... ASC
NULL
NULL
IS [NOT] NULL
MySQL的> SELECT 0 IS NULL, 0 IS NOT NULL, '' IS NULL, '' IS NOT NULL;
----------- --------------- ------------ ---------------- | 0是空| 0不空|”是无效的|”是无效的| ----------- --------------- ------------ ---------------- | 0 | 1 | 0 | 1 | ----------- --------------- ------------ ----------------
NOT NULL
_
=
<>
LIKE
NOT LIKE
b
MySQL的> SELECT * FROM pet WHERE name LIKE 'b%';
-------- -------- --------- ------ ------------ ------------ |名字|业主|物种|性|出生|死亡| -------- -------- --------- ------ ------------ ------------ |捉鬼|哈罗德|狗| F | 1989-05-13 |空| |库巴|戴安娜|狗| M | 1989-08-31 | 1995-07-29 | -------- -------- --------- ------ ------------ ------------
fy
MySQL的> SELECT * FROM pet WHERE name LIKE '%fy';
-------- -------- --------- ------ ------------ ------- |名字|业主|物种|性|出生|死亡| -------- -------- --------- ------ ------------ ------- |蓬松|哈罗德|猫| F | 1993-02-04 |空| |捉鬼|哈罗德|狗| F | 1989-05-13 |空| -------- -------- --------- ------ ------------ -------
w
MySQL的> SELECT * FROM pet WHERE name LIKE '%w%';
---------- ------- --------- ------ ------------ ------------ |名字|业主|物种|性|出生|死亡| ---------- ------- --------- ------ ------------ ------------ |爪|格温|猫| M | 1994-03-17 |空| |库巴|戴安娜|狗| M | 1989-08-31 | 1995-07-29 | |惠斯勒|格温|鸟|空| 1997-12-09 |空| ---------- ------- --------- ------ ------------ ------------
_
MySQL的> SELECT * FROM pet WHERE name LIKE '_____';
------- -------- --------- ------ ------------ ------- |名字|业主|物种|性|出生|死亡| ------- -------- --------- ------ ------------ ------- |爪|格温|猫| M | 1994-03-17 |空| |捉鬼|哈罗德|狗| F | 1989-05-13 |空| ------- -------- --------- ------ ------------ -------
REGEXP_LIKE()
REGEXP
RLIKE
REGEXP_LIKE()
.
[...]
a
c
[0-9]
*
x
.*
LIKE
^
LIKE
REGEXP_LIKE()
b
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 | 1979-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+
BINARY
b
SELECT * FROM Pet WHERE regexp_like(名称,“^ B”整理utf8mb4_0900_as_cs);选择*从宠物那里regexp_like(名称、二进制的^ B);选择*从宠物那里regexp_like(名称,“^ B”、“C”);
fy
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 |
+--------+--------+---------+------+------------+-------+
w
MySQL的> SELECT * FROM pet WHERE REGEXP_LIKE(name, 'w');
---------- ------- --------- ------ ------------ ------------ |名字|业主|物种|性|出生|死亡| ---------- ------- --------- ------ ------------ ------------ |爪|格温|猫| M | 1994-03-17 |空| |库巴|戴安娜|狗| M | 1989-08-31 | 1995-07-29 | |惠斯勒|格温|鸟|空| 1997-12-09 |空| ---------- ------- --------- ------ ------------ ------------
^
.
MySQL的> SELECT * FROM pet WHERE REGEXP_LIKE(name, '^.....$');
------- -------- --------- ------ ------------ ------- |名字|业主|物种|性|出生|死亡| ------- -------- --------- ------ ------------ ------- |爪|格温|猫| M | 1994-03-17 |空| |捉鬼|哈罗德|狗| F | 1989-05-13 |空| ------- -------- --------- ------ ------------ -------
{
n
}n
MySQL的> SELECT * FROM pet WHERE REGEXP_LIKE(name, '^.{5}$');
------- -------- --------- ------ ------------ ------- |名字|业主|物种|性|出生|死亡| ------- -------- --------- ------ ------------ ------- |爪|格温|猫| M | 1994-03-17 |空| |捉鬼|哈罗德|狗| F | 1989-05-13 |空| ------- -------- --------- ------ ------------ -------
pet
COUNT(*)
MySQL的> SELECT COUNT(*) FROM pet;
---------- |计数(*)| ---------- | 9 | ----------
COUNT()
MySQL的> SELECT owner, COUNT(*) FROM pet GROUP BY owner;
-------- ---------- |业主|计数(*)| -------- ---------- |本尼| 2 | |戴安娜| 2 | |格温| 3 | |哈罗德| 2 | -------- ----------
GROUP BY
COUNT()
mysql> SELECT species, COUNT(*) FROM pet GROUP BY species;
+---------+----------+
| species | COUNT(*) |
+---------+----------+
| bird | 2 |
| cat | 2 |
| dog | 3 |
| hamster | 1 |
| snake | 1 |
+---------+----------+
mysql> SELECT sex, COUNT(*) FROM pet GROUP BY sex;
+------+----------+
| sex | COUNT(*) |
+------+----------+
| NULL | 1 |
| f | 4 |
| m | 4 |
+------+----------+
NULL
mysql> SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex;
+---------+------+----------+
| species | sex | COUNT(*) |
+---------+------+----------+
| bird | NULL | 1 |
| bird | f | 1 |
| cat | f | 1 |
| cat | m | 1 |
| dog | f | 1 |
| dog | m | 2 |
| hamster | f | 1 |
| snake | m | 1 |
+---------+------+----------+
COUNT()
MySQL的> SELECT species, sex, COUNT(*) FROM pet
-> WHERE species = 'dog' OR species = 'cat'
-> GROUP BY species, sex;
--------- ------ ---------- |物种|性|计数(*)| --------- ------ ---------- |猫| F | 1 | |猫| M | 1 | |狗| F | 1 | |狗| M | 2 | --------- ------ ----------
mysql>SELECT species, sex, COUNT(*) FROM pet
->WHERE sex IS NOT NULL
->GROUP BY species, sex;
+---------+------+----------+ | species | sex | COUNT(*) | +---------+------+----------+ | bird | f | 1 | | cat | f | 1 | | cat | m | 1 | | dog | f | 1 | | dog | m | 2 | | hamster | f | 1 | | snake | m | 1 | +---------+------+----------+
COUNT()
ONLY_FULL_GROUP_BY
MySQL的> SET sql_mode = 'ONLY_FULL_GROUP_BY';
查询行,0行受影响(0秒)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
ONLY_FULL_GROUP_BY
MySQL的> SET sql_mode = '';
查询行,0行受影响(0秒)MySQL > SELECT owner, COUNT(*) FROM pet;
-------- ---------- |业主|计数(*)| -------- ---------- |哈罗德| 8 | -------- ---------- 1行集(0秒)
COUNT(
expr
)
pet
CREATE
TABLE
mysql>CREATE TABLE event (name VARCHAR(20), date DATE,
->type VARCHAR(15), remark VARCHAR(255));
pet
mysql> LOAD DATA LOCAL INFILE 'event.txt' INTO TABLE event;
pet
event
event
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 | 4 | 5 puppies, 2 female, 3 male | | Buffy | 5 | 3 puppies, 3 female | +--------+------+-----------------------------+
FROM
name
name
INNER JOIN
ON
name
name
ON
name
pet
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';
-------- ------ -------- ------ --------- |名字|性|名字|性|物种| -------- ------ -------- ------ --------- |蓬松| F |爪| M |猫| |捉鬼| F |方| M |狗| |捉鬼| F |库巴| M |狗| -------- ------ -------- ------ ---------
SHOW
DATABASES
DATABASE()
MySQL的> SELECT DATABASE();
| BDE数据库(),| | menagerie | ---------
NULL
mysql> SHOW TABLES;
+---------------------+
| Tables_in_menagerie |
+---------------------+
| event |
| pet |
+---------------------+
Tables_in_
db_name
db_name
DESCRIBE
MySQL的> DESCRIBE pet;
--------- ------------- ------ ----- --------- ------- |场|型|空|关键|默认|额外| --------- ------------- ------ ----- --------- ------- |名字| varchar(20)|是| |空| | |业主| varchar(20)|是| |空| | |物种| varchar(20)|是| |空| | |性| char(1)|是| |空| | |出生日期是| | | |空| | |死亡日期是| | | |空| | --------- ------------- ------ ----- --------- -------
Field
NULL
Key
Extra
auto_increment
DESC
DESCRIBE
CREATE TABLE
SHOW CREATE TABLE
SHOW INDEX FROM
tbl_name
shell> mysql < batch-file
C:\> mysql -e "source batch-file
"
shell>mysql -h
Enter password:host
-uuser
-p <batch-file
********
--force
shell> mysql < batch-file
| more
shell> mysql < batch-file
> mysql.out
SELECT DISTINCT
species FROM pet
+---------+ | species | +---------+ | bird | | cat | | dog | | hamster | | snake | +---------+
species bird cat dog hamster snake
source
mysql>source
mysql>filename
;\.
filename
shop
dealer
shell> mysql your-database-name
test
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);
SELECT * FROM shop; +---------+--------+-------+ | article | dealer | price | +---------+--------+-------+ | 0001 | A | 3.45 | | 0001 | B | 3.99 | | 0002 | A | 10.99 | | 0003 | B | 1.45 | | 0003 | C | 1.69 | | 0003 | D | 1.25 | | 0004 | D | 19.95 | +---------+--------+-------+
SELECT MAX(article) AS article FROM shop; +---------+ | article | +---------+ | 4 | +---------+
SELECT article, dealer, price FROM shop WHERE price=(SELECT MAX(price) FROM shop); +---------+--------+-------+ | article | dealer | price | +---------+--------+-------+ | 0004 | D | 19.95 | +---------+--------+-------+
LEFT JOIN
SELECT s1.article, s1.dealer, s1.price FROM shop s1 LEFT JOIN shop s2 ON s1.price < s2.price WHERE s2.article IS NULL; SELECT article, dealer, price FROM shop ORDER BY price DESC LIMIT 1;
LIMIT
SELECT article, MAX(price) AS price FROM shop GROUP BY article; +---------+-------+ | article | price | +---------+-------+ | 0001 | 3.99 | | 0002 | 10.99 | | 0003 | 1.69 | | 0004 | 19.95 | +---------+-------+
SELECT article, dealer, price FROM shop s1 WHERE price=(SELECT MAX(s2.price) FROM shop s2 WHERE s1.article = s2.article); +---------+--------+-------+ | article | dealer | price | +---------+--------+-------+ | 0001 | B | 3.99 | | 0002 | A | 10.99 | | 0003 | C | 1.69 | | 0004 | D | 19.95 | +---------+--------+-------+
FROM
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
SELECT s1.article, s1.dealer, s1.priceFROM shop s1LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.priceWHERE s2.article IS NULL;
LEFT JOIN
s2.price
NULL
mysql>SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;
mysql>SELECT * FROM shop WHERE price=@min_price OR price=@max_price;
+---------+--------+-------+ | article | dealer | price | +---------+--------+-------+ | 0003 | D | 1.25 | | 0004 | D | 19.95 | +---------+--------+-------+
InnoDB
InnoDB
tbl_name
col_name
CHECK
col_name
tbl_name
tbl_name
tbl_name
ON UPDATE
ON UPDATE
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; +----+---------------------+ | id | name | +----+---------------------+ | 1 | Antonio Paz | | 2 | Lilliana Angelovska | +----+---------------------+ SELECT * FROM shirt; +----+---------+--------+-------+ | id | style | color | owner | +----+---------+--------+-------+ | 1 | polo | blue | 1 | | 2 | dress | white | 1 | | 3 | t-shirt | blue | 1 | | 4 | dress | orange | 2 | | 5 | polo | red | 2 | | 6 | dress | blue | 2 | | 7 | t-shirt | white | 2 | +----+---------+--------+-------+ SELECT s.* FROM person p INNER JOIN shirt s ON s.owner = p.id WHERE p.name LIKE 'Lilliana%' AND s.color <> 'white'; +----+-------+--------+-------+ | id | style | color | owner | +----+-------+--------+-------+ | 4 | dress | orange | 2 | | 5 | polo | red | 2 | | 6 | dress | blue | 2 | +----+-------+--------+-------+
REFERENCES
SHOW CREATE TABLE
DESCRIBE
SHOW CREATE TABLE shirt\G*************************** 1. row ***************************Table: shirtCreate Table: CREATE TABLE `shirt` (`id` smallint(5) unsigned NOT NULL auto_increment,`style` enum('t-shirt','polo','dress') NOT NULL,`color` enum('red','blue','orange','white','black') NOT NULL,`owner` smallint(5) unsigned NOT NULL,PRIMARY KEY (`id`)) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4
REFERENCES
MyISAM
OR
SELECT field1_index, field2_index FROM test_tableWHERE field1_index = '1' OR field2_index = '1'
UNION
SELECT
SELECT
SELECT field1_index, field2_index FROM test_table WHERE field1_index = '1'UNIONSELECT field1_index, field2_index FROM test_table WHERE field2_index = '1';
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<<day)) AS days FROM t1 GROUP BY year,month;
+------+-------+------+ | year | month | days | +------+-------+------+ | 2000 | 01 | 3 | | 2000 | 02 | 2 | +------+-------+------+
AUTO_INCREMENT
创建表的动物(ID MEDIUMINT不空auto_increment,名字char(30)不为空,主键(ID));插入动物(名字)值('dog '),(猫的),(‘企鹅’),('lax '),('whale '),('ostrich ');选择*从动物;
+----+---------+ | id | name | +----+---------+ | 1 | dog | | 2 | cat | | 3 | penguin | | 4 | lax | | 5 | whale | | 6 | ostrich | +----+---------+
AUTO_INCREMENT
NO_AUTO_VALUE_ON_ZERO
insert into动物(id,name的值(0),是groundhog);
NOT NULL
INSERT INTO animals (id,name) VALUES(NULL,'squirrel');
AUTO_INCREMENT
INSERT INTO animals (id,name) VALUES(100,'rabbit');
INSERT INTO animals (id,name) VALUES(NULL,'mouse');
SELECT * FROM animals;
----- ----------- | ID |名字| ----- ----------- | 1 |狗| | 2 |猫| | 3 |企鹅| | 4 |松懈| | 5 |鲸鱼| | 6 |鸵鸟| | 7 |土拨鼠| | 8 |松鼠| | 100 |兔| | 101 |鼠标| ----- -----------
AUTO_INCREMENT
AUTO_INCREMENT
LAST_INSERT_ID()
mysql_insert_id()
AUTO_INCREMENT
TINYINT
TINYINT
UNSIGNED
LAST_INSERT_ID()
mysql_insert_id()
AUTO_INCREMENT
CREATE
TABLE
ALTER TABLE
MySQL的> ALTER TABLE tbl AUTO_INCREMENT = 100;
AUTO_INCREMENT
MyISAM
AUTO_INCREMENT
MAX(
auto_increment_column
)
+ 1 WHERE
prefix=given-prefix
CREATE TABLE animals ( grp ENUM('fish','mammal','bird') NOT NULL, id MEDIUMINT NOT NULL AUTO_INCREMENT, name CHAR(30) NOT NULL, PRIMARY KEY (grp,id)) ENGINE=MyISAM;INSERT INTO animals (grp,name) VALUES ('mammal','dog'),('mammal','cat'), ('bird','penguin'),('fish','lax'),('mammal','whale'), ('bird','ostrich');SELECT * FROM animals ORDER BY grp,id;
+--------+----+---------+ | grp | id | name | +--------+----+---------+ | fish | 1 | lax | | mammal | 1 | dog | | mammal | 2 | cat | | mammal | 3 | whale | | bird | 1 | penguin | | bird | 2 | ostrich | +--------+----+---------+
AUTO_INCREMENT
AUTO_INCREMENT
AUTO_INCREMENT
AUTO_INCREMENT
animals
INDEX
(id)
grp
AUTO_INCREMENT
AUTO_INCREMENT
AUTO_INCREMENT
NO_AUTO_VALUE_ON_ZERO
LAST_INSERT_ID()
AUTO_INCREMENT
AUTO_INCREMENT
AUTO_INCREMENT
auto_increment_increment
auto_increment_offset
LogFormat \ "\"%h\",%{%Y%m%d%H%M%S}t,%>s,\"%b\",\"%{Content-Type}o\", \ \"%U\",\"%{Referer}i\",\"%{User-Agent}i\""
LOAD DATA INFILE '/local/access_log
' INTO TABLEtbl_name
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\'
LogFormat