mysql 文档， tutorial
来源：互联网 发布：mysql开启慢查询日志 编辑：程序博客网 时间：2024/06/14 00:53
参考文档
https://docs.oracle.com/cd/E17952_01/mysql-5.7-en/tutorial.html
[oracle@11gr2 ~]$ mysql -h localhost -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.20 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
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,则不用h
[oracle@11gr2 ~]$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.20 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
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>
-- 离开， ctl+d
[root@11gr2 oracle]# mysql -h localhost -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.20 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
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> ^DBye
[root@11gr2 oracle]#
-- 查询
mysql> select version(),current_date;
+-----------+--------------+
| version() | current_date |
+-----------+--------------+
| 5.7.20 | 2017-11-21 |
+-----------+--------------+
1 row in set (0.03 sec)
mysql>
-- 数字计算
mysql> SELECT SIN(PI()/4), (4+1)*5;
+--------------------+---------+
| SIN(PI()/4) | (4+1)*5 |
+--------------------+---------+
| 0.7071067811865475 | 25 |
+--------------------+---------+
1 row in set (0.00 sec)
-- 一行可以进行多个查询
mysql> select version();select now();
+-----------+
| version() |
+-----------+
| 5.7.20 |
+-----------+
1 row in set (0.00 sec)
+---------------------+
| now() |
+---------------------+
| 2017-11-21 15:09:15 |
+---------------------+
1 row in set (0.00 sec)
mysql>
--多个行的查询
mysql> select
-> user()
-> ,
-> current_date;
+----------------+--------------+
| user() | current_date |
+----------------+--------------+
| root@localhost | 2017-11-21 |
+----------------+--------------+
1 row in set (0.00 sec)
-- 如果不想执行一个查询，后面\c
mysql> select
-> user()
-> \c
mysql>
-- 查看db
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
-- 切换db
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> select database()
-> ;
+------------+
| database() |
+------------+
| NULL |
+------------+
1 row 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> GRANT ALL ON menagerie.* TO 'your_mysql_name'@'your_client_host';
-- 创建和选择一个db
mysql> CREATE DATABASE xxzhaobb;
Query OK, 1 row affected (0.00 sec)
mysql> CREATE DATABASE menagerie;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| menagerie |
| mysql |
| performance_schema |
| sys |
| xxzhaobb |
+--------------------+
6 rows in set (0.00 sec)
mysql>
mysql> use xxzhaobb
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| xxzhaobb |
+------------+
1 row in set (0.00 sec)
-- 另一种登录方式
[root@11gr2 oracle]# mysql -h localhost -u root -p xxzhaobb
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.7.20 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
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 database();
+------------+
| database() |
+------------+
| xxzhaobb |
+------------+
1 row in set (0.00 sec)
mysql>
-- 创建一个表
mysql> select database();
+------------+
| database() |
+------------+
| xxzhaobb |
+------------+
1 row in set (0.00 sec)
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.39 sec)
mysql> show tables;
+--------------------+
| Tables_in_xxzhaobb |
+--------------------+
| 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.02 sec)
mysql> desc 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)
-- 向表中填充数据
--方法1 load data, 内容如下， mull使用 \N 代替
Whistler Gwen bird \N 1997-12-09 \N
aa bb cc \N 1982-07-10 \N
LOAD DATA LOCAL INFILE '/u01/app/load' INTO TABLE pet;
mysql> LOAD DATA LOCAL INFILE '/u01/app/load' INTO TABLE pet;
Query OK, 2 rows affected, 6 warnings (0.08 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 6
mysql> select * from pet;
+----------------------+-------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+----------------------+-------+---------+------+------------+-------+
| Whistler Gwen | NULL | NULL | NULL | NULL | NULL |
| aa | bb | cc | NULL | 1982-07-10 | NULL |
+----------------------+-------+---------+------+------------+-------+
2 rows in set (0.00 sec)
mysql>
-- 如果在windows下，则要这样
mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet
-> LINES TERMINATED BY '\r\n';
-- insert into 语句
mysql> INSERT INTO pet
-> VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);
Query OK, 1 row affected (0.05 sec)
mysql> select * from pet;
+----------------------+-------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+----------------------+-------+---------+------+------------+-------+
| Whistler Gwen | NULL | NULL | NULL | NULL | NULL |
| aa | bb | cc | NULL | 1982-07-10 | NULL |
| Puffball | Diane | hamster | f | 1999-03-30 | NULL |
+----------------------+-------+---------+------+------------+-------+
3 rows in set (0.00 sec)
-- 其他条件查询，排序查询都一样，略
mysql> select * from pet;
+----------+--------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+----------+--------+---------+------+------------+------------+
| Fluffy | Harold | cat | f | 1993-02-04 | 0000-00-00 |
| Claws | Gwen | cat | m | 1994-03-17 | 0000-00-00 |
| Buffy | Harold | dog | f | 1989-05-13 | 0000-00-00 |
| Fang | Benny | dog | m | 1990-08-27 | 0000-00-00 |
| Bowser | Diane | dog | m | 1995-07-29 | NULL |
| Chirpy | Gwen | bird | f | 1998-09-11 | 0000-00-00 |
| Whistler | Gwen | bird | N | 1997-12-09 | 0000-00-00 |
| Slim | Benny | snake | m | 1996-04-29 | 0000-00-00 |
| Puffball | Diane | hamster | f | 1999-03-30 | 0000-00-00 |
| | NULL | NULL | NULL | NULL | NULL |
+----------+--------+---------+------+------------+------------+
10 rows in set (0.00 sec)
mysql>
mysql> SELECT name, birth, CURDATE(),
-> TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age
-> from pet;
+----------+------------+------------+------+
| name | birth | CURDATE() | age |
+----------+------------+------------+------+
| Fluffy | 1993-02-04 | 2017-11-21 | 24 |
| Claws | 1994-03-17 | 2017-11-21 | 23 |
| Buffy | 1989-05-13 | 2017-11-21 | 28 |
| Fang | 1990-08-27 | 2017-11-21 | 27 |
| Bowser | 1995-07-29 | 2017-11-21 | 22 |
| Chirpy | 1998-09-11 | 2017-11-21 | 19 |
| Whistler | 1997-12-09 | 2017-11-21 | 19 |
| Slim | 1996-04-29 | 2017-11-21 | 21 |
| Puffball | 1999-03-30 | 2017-11-21 | 18 |
| | NULL | 2017-11-21 | NULL |
+----------+------------+------------+------+
10 rows in set (0.00 sec)
-- 时间查询
mysql> select timestampdiff(year,'1992-01-01',curdate());
+--------------------------------------------+
| timestampdiff(year,'1992-01-01',curdate()) |
+--------------------------------------------+
| 25 |
+--------------------------------------------+
1 row in set (0.00 sec)
-- mysql 可以直接对别名进行排序 ？
mysql> SELECT name, birth, death,
-> TIMESTAMPDIFF(YEAR,birth,death) AS age
-> FROM pet WHERE death IS NOT NULL ORDER BY age;
+--------+------------+------------+------+
| name | birth | death | age |
+--------+------------+------------+------+
| Bowser | 1989-08-31 | 1995-07-29 | 5 |
+--------+------------+------------+------+
-- 关于null
https://docs.oracle.com/cd/E17952_01/mysql-5.7-en/working-with-null.html
-- mysql下批量执行
shell> mysql < batch-file
shell> mysql -h host -u user -p < batch-file
[oracle@11gr2 app]$ mysql -h localhost -u root -p xxzhaobb < '/u01/app/execmysql'
Enter password:
database()
xxzhaobb
[oracle@11gr2 app]$
-- 比较多的查询，显示出来
shell> mysql < batch-file | more
-- 将查询结果放在某一个文件中
shell> mysql < batch-file > mysql.out
[oracle@11gr2 app]$ mysql -h localhost -u root -p xxzhaobb < '/u01/app/execmysql' > '/home/oracle/mysqlresult'
Enter password:
[oracle@11gr2 app]$ cat /home/oracle/mysqlresult
database()
xxzhaobb
[oracle@11gr2 app]$
-- 也可以使用下面的语句 ，类似oracle的 @ start
mysql> source filename;
mysql> \. filename
mysql> source /u01/app/execmysql
+------------+
| database() |
+------------+
| xxzhaobb |
+------------+
1 row in set (0.00 sec)
mysql> \. /u01/app/execmysql
+------------+
| database() |
+------------+
| xxzhaobb |
+------------+
1 row in set (0.00 sec)
mysql>
-- 创建一个表，插入数据
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));
Query OK, 0 rows affected (0.34 sec)
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);
Query OK, 7 rows affected (0.06 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql>
mysql> 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 |
+---------+--------+-------+
7 rows in set (0.00 sec)
-- 使用用户定义的变量
mysql> SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;
+------------------------+------------------------+
| @min_price:=MIN(price) | @max_price:=MAX(price) |
+------------------------+------------------------+
| 1.25 | 19.95 |
+------------------------+------------------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM shop WHERE price=@min_price OR price=@max_price;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
| 0003 | D | 1.25 |
| 0004 | D | 19.95 |
+---------+--------+-------+
2 rows in set (0.00 sec)
-- 查看建表语句
--
https://docs.oracle.com/cd/E17952_01/mysql-5.7-en/calculating-days.html
3.6.8 Calculating Visits Per Day -- 暂时没有看明白
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);
mysql> select * from t1;
+------+-------+------+
| year | month | day |
+------+-------+------+
| 2000 | 01 | 01 |
| 2000 | 01 | 20 |
| 2000 | 01 | 30 |
| 2000 | 02 | 02 |
| 2000 | 02 | 23 |
| 2000 | 02 | 23 |
+------+-------+------+
6 rows in set (0.00 sec)
mysql> 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 |
+------+-------+------+
2 rows in set (0.04 sec)
--
3.6.9 Using AUTO_INCREMENT
mysql> CREATE TABLE animals (
-> id MEDIUMINT NOT NULL AUTO_INCREMENT,
-> name CHAR(30) NOT NULL,
-> PRIMARY KEY (id)
-> );
Query OK, 0 rows affected (0.58 sec)
mysql>
mysql> INSERT INTO animals (name) VALUES
-> ('dog'),('cat'),('penguin'),
-> ('lax'),('whale'),('ostrich');
Query OK, 6 rows affected (0.08 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> select * from animals;
+----+---------+
| id | name |
+----+---------+
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
+----+---------+
6 rows in set (0.00 sec)
mysql>
- mysql 文档， tutorial
- MySQL Tutorial
- MySQL Tutorial
- NumPy Tutorial官方文档
- Tutorial #Facebook Relay文档翻译#
- MapReduce Tutorial文档试译
- MHA文档翻译Tutorial部分
- Hive官方文档-Hive Tutorial
- MySql Linux Tutorial
- A Basic MySQL Tutorial
- MySQL Tutorial(1)
- MySQL Tutorial(2)
- 【翻译】MySQL-Tutorial ( 1 )
- 【翻译】MySQL-Tutorial ( 2 )
- 【翻译】MySQL-Tutorial ( 3 )
- MySQL C API programming tutorial
- Theano Tutorial文档翻译（一）：词汇
- Theano Tutorial文档翻译（二）：代数
- 《python入门学习笔记2》（基本逻辑处理）
- Exception信息转换为字符串，或者直接将使用Log4j进行异常记录
- sourceTree的基本使用说明
- 您应该知道的10GBASE-T SFP+光模块知识
- GDB 使用入门
- mysql 文档， tutorial
- 文章标题
- ssh命令
- 为了责任，小任到传智播客郑州校区学编程
- 单词加ed 以及 es 的规则及发音
- 关于ie8以上浏览器下—兼容性视图设置
- JQuery实现全选全不选
- lynda.com教程之Learing Python and Django零基础搭建Django项目
- MAC下将FFmpeg3.3源码编译成so
