【数据分析】基本MySQL语句

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

平时多奋战于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语句

本文链接：https://www.haomeiwen.com/subject/vozqwftx.html

延伸阅读

深度阅读

您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！