【数据分析】基本MySQL语句

作者: FLYNNNOTES | 来源:发表于2018-08-28 21:46 被阅读0次

【数据分析】基本MySQL语句
MySQL 从零开始学（五）查询数据
MySQL学习
mysql同步数据到redis-全量同步
mysql常用命令
BigData-MySQL总结大全（一）苏暖人
2018-12-11
MySQL如何开启binlog？binlog三种模式的分析
MySQL --Section one
数据库常用操作

平时多奋战于excel中，用到MySQL时，颇为生疏，梳理基本用法，以备翻阅。

Attention: SQL语句要以【;】结束，习惯了python，经常忘记Orz...

数据库启动，连接

net start mysql
mysql -uroot -p

有时启动不了数据库，可以尝试win+R至services.msc中手动停止mysql再重新启动。

mysql> show databases; #显示所有database
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| report             |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> use test;    # 使用test库
Database changed

创建表格

creat table [table_name](
    cn1 cn1_type,
    ...,
    cnk cnk_type
    );

mysql> CREATE TABLE Produce48(
    -> member_name VARCHAR(30) NOT NULL,
    -> group_name VARCHAR(20) NOT NULL,
    -> age INT(10) NOT NULL
    -> );
Query OK, 0 rows affected (0.60 sec)

查看表结构

mysql> DESC Produce48;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| member_name | varchar(30) | NO   |     | NULL    |       |
| group_name  | varchar(20) | NO   |     | NULL    |       |
| age         | int(10)     | NO   |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
3 rows in set (0.06 sec)

插入数据

INSERT INTO table_name ( field1, field2,...,fieldN )
                       VALUES
                     (valueA1,valueA2,...,valueAN),
                     (valueB1,valueB2,...,valueBN),
                     (valueC1,valueC2,...,valueCN);

mysql> INSERT INTO Produce48(member_name, group_name, age)
    -> VALUES
    -> ("Sakura"," HKT48","20"),
    -> ("Saep","NMB48", "20");
Query OK, 2 rows affected (0.13 sec)
Records: 2  Duplicates: 0  Warnings: 0

数据查询

SELECT column_name FROM table_name

mysql> SELECT member_name FROM Produce48;   #选择某一列数据
+-------------+
| member_name |
+-------------+
| Sakura      |
| Saep        |
| Erii        |
+-------------+
3 rows in set (0.08 sec)

mysql> SELECT * FROM Produce48; #选择全量数据
+-------------+------------+-----+
| member_name | group_name | age |
+-------------+------------+-----+
| Sakura      |  HKT48     |  20 |
| Saep        | NMB48      |  20 |
| Erii        | AKB48      |  16 |
+-------------+------------+-----+
3 rows in set (0.06 sec)

mysql> SELECT * FROM Produce48
    -> LIMIT 2; #限制查询的数据条数
+-------------+------------+-----+
| member_name | group_name | age |
+-------------+------------+-----+
| Sakura      |  HKT48     |  20 |
| Saep        | NMB48      |  20 |
+-------------+------------+-----+
2 rows in set (0.00 sec)

条件语句

SELECT column_name FROM table_name
[WHERE condition1 [AND|OR] condition2,...

mysql> SELECT * FROM Produce48
    -> WHERE age > 17;
+-------------+------------+-----+
| member_name | group_name | age |
+-------------+------------+-----+
| Sakura      |  HKT48     |  20 |
| Saep        | NMB48      |  20 |
+-------------+------------+-----+
2 rows in set (0.05 sec)

更新values

UPDATE table_name SET column_name1=new_value1
[WHERE conditon]

mysql> UPDATE Produce48 SET age="19"
    -> WHERE member_name="Saep";
Query OK, 1 row affected (0.12 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM Produce48;
+-------------+------------+-----+
| member_name | group_name | age |
+-------------+------------+-----+
| Sakura      |  HKT48     |  20 |
| Saep        | NMB48      |  19 |
| Erii        | AKB48      |  16 |
+-------------+------------+-----+
3 rows in set (0.00 sec)

LIKE语句

SELECT column_name FROM table_name
WHERE column_name1 LIKE condition

'%a' a结尾的数据
'a%' a开头的数据
'%a%' 包含a的数据

mysql> SELECT * FROM Produce48
    -> WHERE group_name LIKE "AKB%";
+-------------+------------+-----+
| member_name | group_name | age |
+-------------+------------+-----+
| Erii        | AKB48      |  16 |
| Jury        | AKB48      |  20 |
+-------------+------------+-----+
2 rows in set (0.00 sec)

除了用LIKE语句，还可以用正则

'^a'：以a开头的数据；
'a$'：以a结尾的数据；
'a'：包含a的数据

mysql> SELECT * FROM Produce48
    -> WHERE group_name REGEXP '^AKB';
+-------------+------------+-----+
| member_name | group_name | age |
+-------------+------------+-----+
| Erii        | AKB48      |  16 |
| Jury        | AKB48      |  20 |
+-------------+------------+-----+
2 rows in set (0.08 sec)

mysql> SELECT * FROM Produce48
    -> WHERE group_name REGEXP '^[N]|[B]';
+-------------+------------+-----+
| member_name | group_name | age |
+-------------+------------+-----+
| Saep        | NMB48      |  19 |
| Erii        | AKB48      |  16 |
| Jury        | AKB48      |  20 |
| Rika        | NGT48      |  17 |
+-------------+------------+-----+
4 rows in set (0.00 sec)

排序

SELECT column_name FROM table_name
ORDER BY column_name [ASC[DESC]]

mysql> SELECT * FROM Produce48
    -> ORDER BY age DESC;
+-------------+------------+-----+
| member_name | group_name | age |
+-------------+------------+-----+
| Sakura      |  HKT48     |  20 |
| Jury        | AKB48      |  20 |
| Saep        | NMB48      |  19 |
| Erii        | AKB48      |  16 |
+-------------+------------+-----+
4 rows in set (0.06 sec)

GROUP BY语句

SELECT column_name, function(column_name) 
FROM table_name
WHERE column_name operator value
GROUP BY column_name

mysql> SELECT group_name, COUNT(*) FROM Produce48
    -> GROUP BY group_name;
+------------+----------+
| group_name | COUNT(*) |
+------------+----------+
|  HKT48     |        1 |
| AKB48      |        2 |
| NMB48      |        1 |
+------------+----------+
3 rows in set (1.41 sec)

mysql> SELECT group_name, AVG(age) FROM Produce48
    -> GROUP BY group_name;
+------------+----------+
| group_name | AVG(age) |
+------------+----------+
|  HKT48     |  20.0000 |
| AKB48      |  18.0000 |
| NMB48      |  19.0000 |
+------------+----------+
3 rows in set (0.00 sec)

mysql> SELECT group_name, AVG(age) as group_average_age  FROM Produce48
    -> GROUP BY group_name WITH ROLLUP;
+------------+-------------------+
| group_name | group_average_age |
+------------+-------------------+
|  HKT48     |           20.0000 |
| AKB48      |           18.0000 |
| NMB48      |           19.0000 |
| NULL       |           18.7500 |
+------------+-------------------+
4 rows in set (0.06 sec)

表连接

INNER JOIN
LEFT JOIN
RIGHT JOIN

mysql> SELECT * FROM Produce48;
+-------------+------------+-----+
| member_name | group_name | age |
+-------------+------------+-----+
| Sakura      |  HKT48     |  20 |
| Saep        | NMB48      |  19 |
| Erii        | AKB48      |  16 |
| Jury        | AKB48      |  20 |
+-------------+------------+-----+
4 rows in set (0.00 sec)

mysql> SELECT * FROM Group_Message;
+------------+----------+---------------+
| group_name | location | member_number |
+------------+----------+---------------+
| AKB48      | TOKYO    |           400 |
| NMB48      | OSAKA    |           100 |
+------------+----------+---------------+
2 rows in set (0.00 sec)

mysql> SELECT a.member_name, a.age FROM Produce48 a
    -> INNER JOIN Group_Message b
    -> ON a.group_name = b.group_name;
+-------------+-----+
| member_name | age |
+-------------+-----+
| Saep        |  19 |
| Erii        |  16 |
| Jury        |  20 |
+-------------+-----+
3 rows in set (0.06 sec)

ALERT 用法

修改数据表名或数据表字段

增加一列

==ALTER TABLE table_name ADD column_name==

默认添加在最后一列
INT FIRST #添加在第一列
INT AFTER column_name #添加在某一列后面

mysql> ALTER TABLE Group_Message ADD establish_date INT AFTER location;
Query OK, 0 rows affected (0.88 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW COLUMNS FROM Group_Message;
+----------------+-------------+------+-----+---------+-------+
| Field          | Type        | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| group_name     | varchar(30) | NO   |     | NULL    |       |
| location       | varchar(30) | YES  |     | NULL    |       |
| establish_date | int(11)     | YES  |     | NULL    |       |
| member_number  | int(10)     | YES  |     | NULL    |       |
+----------------+-------------+------+-----+---------+-------+
4 rows in set (0.03 sec)

修改字段类型、名称等

mysql> ALTER TABLE Group_Message MODIFY establish_date DATE;
Query OK, 2 rows affected (0.75 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SHOW COLUMNS FROM Group_Message;
+----------------+-------------+------+-----+---------+-------+
| Field          | Type        | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| group_name     | varchar(30) | NO   |     | NULL    |       |
| location       | varchar(30) | YES  |     | NULL    |       |
| establish_date | date        | YES  |     | NULL    |       |
| member_number  | int(10)     | YES  |     | NULL    |       |
+----------------+-------------+------+-----+---------+-------+
4 rows in set (0.07 sec)

删除某一列

mysql> ALTER TABLE Group_Message DROP establish_date;
Query OK, 0 rows affected (0.52 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW COLUMNS FROM Group_Message;
+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| group_name    | varchar(30) | NO   |     | NULL    |       |
| location      | varchar(30) | YES  |     | NULL    |       |
| member_number | int(10)     | YES  |     | NULL    |       |
+---------------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

修改表名

mysql> ALTER TABLE Group_Message RENAME TO Group_Information;
Query OK, 0 rows affected (0.17 sec)

mysql> show tables;
+-------------------+
| Tables_in_test    |
+-------------------+
| group_information |
| produce48         |
+-------------------+
2 rows in set (0.00 sec)

【数据分析】基本MySQL语句
平时多奋战于excel中，用到MySQL时，颇为生疏，梳理基本用法，以备翻阅。 Attention: SQL语句要...
MySQL 从零开始学（五）查询数据
基本查询语句 MySQL 从数据库查询的基本语句为 SELECT，基本格式是： {* | <字段列表>} 星号是通...
MySQL学习
mysql -uroot -p//登录 //基本语句SELECT - 从数据库中提取数据UPDATE - 更新数据...
mysql同步数据到redis-全量同步
迁移前的数据建表语句插入示例语句迁移步骤分析源端数据结构分析源端数据结构，保存到名为mysql_to_r...
mysql常用命令
基本流程连接MySQL 选择数据库执行SQL语句退出关闭 1. 连接mysql mysql -u root ...
BigData-MySQL总结大全（一）苏暖人
BigData之MySQL总结大全 MYSQL常用的基本语句 MYSQL常用的基本语句例：SELECT TOP ...
2018-12-11
PHP连接Mysql和一些基本操作建立数据库连接查询语句取出执行语句受影响行数
MySQL如何开启binlog？binlog三种模式的分析
MySQL如何开启binlog？binlog三种模式的分析前提，创建表t，并插入数据，语句如下：判断MySQL...
MySQL --Section one
SQL常用的语句1.1 MySQL的基本操作1. 开启MySQL数据库服务格式: net start mys...
数据库常用操作
mysql开机自启动设置个人整理的基本的mysql操作语句 1. 创建数据库 create DATABASE R...

【数据分析】基本MySQL语句

数据库启动，连接

创建表格

查看表结构

插入数据

数据查询

条件语句

更新values

LIKE语句

排序

GROUP BY语句

表连接

ALERT 用法

增加一列

修改字段类型、名称等

删除某一列

修改表名

相关文章

【数据分析】基本MySQL语句

MySQL 从零开始学（五）查询数据

MySQL学习

mysql同步数据到redis-全量同步

mysql常用命令

BigData-MySQL总结大全（一）苏暖人

2018-12-11

MySQL如何开启binlog？binlog三种模式的分析

MySQL --Section one

数据库常用操作

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读