Article Detail Page

MySQL - 机器信息

Macbook 13寸 Pro 版
OS - 10.14.1

安装MySQL

brew install mysql

装好后应该能在系统里面看到这个图标

MySQY - GUI界面工具

Navicat for MySQL 密码账号,以及端口一般默认就是3306

相关命令(注意一定要以分号结尾,不然会车默认就是换行)

展示有哪些数据库

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| vmei               |
+--------------------+
5 rows in set (0.01 sec)

创建数据库

mysql> CREATE DATABASES tests;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DATABASES tests' at line 1
mysql> CREATE DATABASE tests;
Query OK, 1 row affected (0.02 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| tests              |
| vmei               |
+--------------------+
6 rows in set (0.00 sec)

使用X数据库

mysql> USE vmei;
Database changed

创建某个表

CREATE TABLE cats
(
  id              INT unsigned NOT NULL AUTO_INCREMENT, # Unique ID for the record
  name            VARCHAR(150) NOT NULL,                # Name of the cat
  owner           VARCHAR(150) NOT NULL,                # Owner of the cat
  birth           DATE NOT NULL,                        # Birthday of the cat
  PRIMARY KEY     (id)                                  # Make the id the primary key
);

使用某个表

mysql> SHOW TABLES;
+----------------+
| Tables_in_vmei |
+----------------+
| cats           |
+----------------+
1 row in set (0.00 sec)

查看某个表的描述 describe

mysql> DESCRIBE cats;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(150)     | NO   |     | NULL    |                |
| owner | varchar(150)     | NO   |     | NULL    |                |
| birth | date             | NO   |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

插入数据

INSERT INTO cats ( name, owner, birth) VALUES
  ( 'Sandy', 'Lennon', '2015-01-03' ),
  ( 'Cookie', 'Casey', '2013-11-13' ),
  ( 'Charlie', 'River', '2016-05-21' );

获取`cat`表里面所有数据

mysql> SELECT * FROM cats;
+----+---------+---------+------------+
| id | name    | owner   | birth      |
+----+---------+---------+------------+
|  1 | peng    | liheng  | 2015-11-11 |
|  2 | chen    | caifeng | 2015-11-12 |
|  3 | chen    | caifeng | 2015-11-12 |
|  4 | hu      | gangyu  | 2015-11-13 |
|  5 | Sandy   | Lennon  | 2015-01-03 |
|  6 | Cookie  | Casey   | 2013-11-13 |
|  7 | Charlie | River   | 2016-05-21 |
+----+---------+---------+------------+
7 rows in set (0.00 sec)

查询表里面某个键值得所有数据

mysql> SELECT name FROM cats;
+---------+
| name    |
+---------+
| peng    |
| chen    |
| chen    |
| hu      |
| Sandy   |
| Cookie  |
| Charlie |
+---------+
7 rows in set (0.00 sec)

根据条件查询某个键值所有数据

mysql> SELECT name FROM cats WHERE owner = 'Casey';
+--------+
| name   |
+--------+
| Cookie |
+--------+
1 row in set (0.00 sec)

在表里面删除某个值

mysql> DELETE FROM cats WHERE name = 'hu';
Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM catS;
+----+---------+---------+------------+
| id | name    | owner   | birth      |
+----+---------+---------+------------+
|  1 | peng    | liheng  | 2015-11-11 |
|  2 | chen    | caifeng | 2015-11-12 |
|  3 | chen    | caifeng | 2015-11-12 |
|  5 | Sandy   | Lennon  | 2015-01-03 |
|  6 | Cookie  | Casey   | 2013-11-13 |
|  7 | Charlie | River   | 2016-05-21 |
+----+---------+---------+------------+
6 rows in set (0.00 sec)

插入某一列数据,再删除这一列数据

mysql> ALTER TABLE cats ADD gender CHAR(1) AFTER name;
Query OK, 0 rows affected (0.25 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM cats;
+----+---------+--------+---------+------------+
| id | name    | gender | owner   | birth      |
+----+---------+--------+---------+------------+
|  1 | peng    | NULL   | liheng  | 2015-11-11 |
|  2 | chen    | NULL   | caifeng | 2015-11-12 |
|  3 | chen    | NULL   | caifeng | 2015-11-12 |
|  5 | Sandy   | NULL   | Lennon  | 2015-01-03 |
|  6 | Cookie  | NULL   | Casey   | 2013-11-13 |
|  7 | Charlie | NULL   | River   | 2016-05-21 |
+----+---------+--------+---------+------------+
6 rows in set (0.00 sec)

mysql> ALTER TABLE cats DROP gender;
Query OK, 0 rows affected (0.24 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM cats;
+----+---------+---------+------------+
| id | name    | owner   | birth      |
+----+---------+---------+------------+
|  1 | peng    | liheng  | 2015-11-11 |
|  2 | chen    | caifeng | 2015-11-12 |
|  3 | chen    | caifeng | 2015-11-12 |
|  5 | Sandy   | Lennon  | 2015-01-03 |
|  6 | Cookie  | Casey   | 2013-11-13 |
|  7 | Charlie | River   | 2016-05-21 |
+----+---------+---------+------------+
6 rows in set (0.00 sec)

添加两列数据

mysql>  ALTER TABLE cats ADD gender CHAR(1) AFTER name;
Query OK, 0 rows affected (0.23 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>  ALTER TABLE cats ADD what? CHAR(1) AFTER name;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '? CHAR(1) AFTER name' at line 1
mysql>  ALTER TABLE cats ADD what CHAR(1) AFTER name;
Query OK, 0 rows affected (0.18 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESCRIBE cats;
+--------+------------------+------+-----+---------+----------------+
| Field  | Type             | Null | Key | Default | Extra          |
+--------+------------------+------+-----+---------+----------------+
| id     | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name   | varchar(150)     | NO   |     | NULL    |                |
| what   | char(1)          | YES  |     | NULL    |                |
| gender | char(1)          | YES  |     | NULL    |                |
| owner  | varchar(150)     | NO   |     | NULL    |                |
| birth  | date             | NO   |     | NULL    |                |
+--------+------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

mysql> SELECT * FROM cats;
+----+---------+------+--------+---------+------------+
| id | name    | what | gender | owner   | birth      |
+----+---------+------+--------+---------+------------+
|  1 | peng    | NULL | NULL   | liheng  | 2015-11-11 |
|  2 | chen    | NULL | NULL   | caifeng | 2015-11-12 |
|  3 | chen    | NULL | NULL   | caifeng | 2015-11-12 |
|  5 | Sandy   | NULL | NULL   | Lennon  | 2015-01-03 |
|  6 | Cookie  | NULL | NULL   | Casey   | 2013-11-13 |
|  7 | Charlie | NULL | NULL   | River   | 2016-05-21 |
+----+---------+------+--------+---------+------------+
6 rows in set (0.00 sec)

查询表格 `cinema`

表格如下

+---------+-----------+--------------+-----------+
|   id    | movie     |  description |  rating   |
+---------+-----------+--------------+-----------+
|   1     | War       |   great 3D   |   8.9     |
|   2     | Science   |   fiction    |   8.5     |
|   3     | irish     |   boring     |   6.2     |
|   4     | Ice song  |   Fantacy    |   8.6     |
|   5     | House card|   Interesting|   9.1     |
+---------+-----------+--------------+-----------+

它的输出:

+---------+-----------+--------------+-----------+
|   id    | movie     |  description |  rating   |
+---------+-----------+--------------+-----------+
|   5     | House card|   Interesting|   9.1     |
|   1     | War       |   great 3D   |   8.9     |
+---------+-----------+--------------+-----------+

条件:

id是奇数
description != boring
最后输出的表格,按照 rating进行递减排序

SELECT * FROM cinema WHERE (id % 2 = 1) AND (description != 'boring') ORDER BY rating DESC;

关联查询

left join 以 table1 为主表左交集

select * from table1
left join table2
    on table1.id=table2.id
    having table1.age>2

right join 以 right join 进来的为主表, 右交集
inner join 并集

合并两个查询

union
union all

查询条件

group by <condition 1>, ...

排序

order by <asc 增序/ desc 降序>

限制查询个数 <限制一条>

limit by 1

性能优化

单个表查询性能优于内嵌表, 优于 left join

[SQL] 基础语法

MySQL - 机器信息

安装MySQL

MySQY - GUI界面工具

相关命令(注意一定要以分号结尾,不然会车默认就是换行)

展示有哪些数据库

创建数据库

使用X数据库

创建某个表

使用某个表

查看某个表的描述 describe

插入数据

获取cat表里面所有数据

查询表里面某个键值得所有数据

根据条件查询某个键值所有数据

在表里面删除某个值

插入某一列数据,再删除这一列数据

添加两列数据

查询表格 cinema

关联查询

合并两个查询

查询条件

排序

限制查询个数 <限制一条>

性能优化

获取`cat`表里面所有数据

查询表格 `cinema`