(八).Linux下使用mysql-4(建表,查询)

作者: 木子心语 | 来源:发表于2018-04-21 10:13 被阅读0次

(八).Linux下使用mysql-4(建表,查询)
网络日志的Hive简单查询
DDL-数据表操作
Python+MySQL数据库操作（PyMySQL）
Hive 基本语法
案例加源码：万字长文带你彻底搞懂MySQL的索引优化
2019-03-25
MySQL连接查询索引优化
2019-02-22
MySQL ：Engine存储引擎

1. 单表查询
2. 子查询
3. 联表查询
4. 事务

五张关系表的创建

创建并进入数据库：
mysql> CREATE DATABASE `mydb`;
Query OK, 1 row affected (0.00 sec)
mysql> USE `mydb`;
Database changed

创建学院表：
mysql> CREATE TABLE `department`(
    -> `id` INT PRIMARY KEY AUTO_INCREMENT,
    -> `name` VARCHAR(20) NOT NULL
    -> );
Query OK, 0 rows affected (0.69 sec)
创建学生表：
mysql> CREATE TABLE `students`(
    -> `s_id` INT PRIMARY KEY AUTO_INCREMENT,
    -> `name` VARCHAR(20) NOT NULL,
    -> `d_id` INT,
    -> FOREIGN KEY(`d_id`) REFERENCES `department` (`id`)
    -> );
Query OK, 0 rows affected (0.65 sec)
创建学生的详细信息表：
mysql> CREATE TABLE `stu_details`(
    -> `s_id` INT PRIMARY KEY,
    -> `age` INT,
    -> `gender` CHAR(1)
    -> ,
    -> FOREIGN KEY(`s_id`) REFERENCES `students` (`s_id`)
    -> );
Query OK, 0 rows affected (0.55 sec)
创建课程表：
mysql> CREATE TABLE `course`(
    -> `id` INT PRIMARY KEY AUTO_INCREMENT,
    -> `name` VARCHAR(20) NOT NULL
    -> );
Query OK, 0 rows affected (0.50 sec)
创建中间表：
mysql> CREATE TABLE `select`(
    -> `s_id` INT,
    -> `c_id` INT,
    -> PRIMARY KEY (`s_id`,`c_id`),
    -> FOREIGN KEY (`s_id`) REFERENCES `students`(`s_id`),
    -> FOREIGN KEY (`c_id`) REFERENCES `course`(`id`)
    -> );
Query OK, 0 rows affected (0.84 sec)
查看当前存在的表：
mysql> SHOW TABLES;
+----------------+
| Tables_in_info |
+----------------+
| course         |
| department     |
| select         |
| stu_details    |
| students       |
+----------------+
5 rows in set (0.00 sec)

往数据表中添加数据

往学院表中添加数据：
mysql> INSERT INTO `department`(`name`)
    -> VALUES('foreign'),
    ->       ('art'),
    ->       ('computer'),
    ->       ('abstract')
    -> ;
Query OK, 4 rows affected (0.11 sec)
Records: 4  Duplicates: 0  Warnings: 0
往学生表中添加数据：
mysql> INSERT INTO `students`(`name`,`d_id`)
    -> VALUES('ming',1),
    ->       ('hong',3),
    ->       ('hua',3),
    ->       ('xin',4)
    -> ;
Query OK, 4 rows affected (0.09 sec)
Records: 4  Duplicates: 0  Warnings: 0
往学生详细信息表中添加数据：
mysql> INSERT INTO stu_details
    -> VALUES(1,18,'m'),
    ->       (4,20,'m'),
    ->       (3,16,'f'),
    ->       (2,19,'f')
    -> ;
Query OK, 4 rows affected (0.11 sec)
Records: 4  Duplicates: 0  Warnings: 0
往课程表中添加数据：
mysql> INSERT INTO `course`
    -> (`name`)VALUES
    -> ('psychology'),
    -> ('history'),
    -> ('music')
    -> ;
Query OK, 4 rows affected (0.08 sec)
Records: 4  Duplicates: 0  Warnings: 0
查看中间表的结构：
mysql> DESC `select`;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| s_id  | int(11) | NO   | PRI | NULL    |       |
| c_id  | int(11) | NO   | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.03 sec)
往中间表中添加数据
mysql> INSERT INTO `select`
    -> VALUES(1,2),
    ->       (1,4),
    ->       (2,1),
    ->       (2,4),
    ->       (4,1),
    ->       (4,2),
    ->       (4,4)
    -> ;
Query OK, 7 rows affected (0.06 sec)
Records: 7  Duplicates: 0  Warnings: 0

查询

查询所有记录

SELECT * FROM tb_name;

查询选中列数据

SELECT col_name1,col_name2 FROM tb_name;

查询指定条件下的记录

SELECT col_name FROM tb_name WHERE 条件

查询后为列取别名

SELECT col_name AS new_name FROM tab_name

例如:

查询所有记录：
mysql> SELECT * FROM `students`;
+------+--------+------+
| s_id | name   | d_id |
+------+--------+------+
|    1 | ming   |    1 |
|    2 | hong   |    3 |
|    3 |  hua   |    3 |
|    4 |  xin   |    4 |
+------+--------+------+
4 rows in set (0.00 sec)

查询选中列记录
mysql> SELECT name,d_id FROM students;
+--------+------+
| name   | d_id |
+--------+------+
| ming   |    1 |
| hong   |    3 |
| hua    |    3 |
| xin    |    4 |
+--------+------+
4 rows in set (0.00 sec)

查询指定条件下的记录
mysql> SELECT * FROM students WHERE `name`='小红';
+------+--------+------+
| s_id | name   | d_id |
+------+--------+------+
|    2 | hong   |    3 |
+------+--------+------+
1 row in set (0.00 sec)

查询后为列取别名
mysql> SELECT name AS `姓名` ,d_id AS 学院id  FROM students WHERE s_id>=2;
+--------+----------+
| 姓名   | 学院id   |
+--------+----------+
| hong   |        3 |
| hua    |        3 |
| xin    |        4 |
+--------+----------+
3 rows in set (0.00 sec)

排序`GROUP BY`

ASC升序(默认) DESC降序

例如:

查询学生的选修表（中间表）
mysql> SELECT * FROM `select`;
+------+------+
| s_id | c_id |
+------+------+
|    2 |    1 |
|    4 |    1 |
|    1 |    2 |
|    4 |    2 |
|    1 |    4 |
|    2 |    4 |
|    4 |    4 |
+------+------+
7 rows in set (0.00 sec)

按学生学号升序输出
mysql> SELECT * FROM `select` ORDER BY `s_id`;
+------+------+
| s_id | c_id |
+------+------+
|    1 |    2 |
|    1 |    4 |
|    2 |    1 |
|    2 |    4 |
|    4 |    1 |
|    4 |    2 |
|    4 |    4 |
+------+------+
7 rows in set (0.00 sec)

按课程id降序输出：
mysql> SELECT * FROM `select` ORDER BY `c_id` DESC;
+------+------+
| s_id | c_id |
+------+------+
|    4 |    4 |
|    2 |    4 |
|    1 |    4 |
|    4 |    2 |
|    1 |    2 |
|    4 |    1 |
|    2 |    1 |
+------+------+
7 rows in set (0.00 sec)

限制显示数据的数量`LIMIT`

例如:

按学生学号升序输出的前4条数据
mysql> SELECT * FROM `select` ORDER BY `s_id` LIMIT 4;
+------+------+
| s_id | c_id |
+------+------+
|    1 |    2 |
|    1 |    4 |
|    2 |    1 |
|    2 |    4 |
+------+------+
4 rows in set (0.00 sec)
指定的返回的数据的位置和数量
mysql> SELECT * FROM `select` ORDER BY `s_id` LIMIT 4,2;
+------+------+
| s_id | c_id |
+------+------+
|    4 |    1 |
|    4 |    2 |
+------+------+
2 rows in set (0.00 sec)

分组查询`GROUP BY`

例如:

对学生表中学院栏进行分组，并统计学院的学生人数：
mysql> SELECT d_id AS 学院id,count(d_id) AS 学生个数 FROM students GROUP BY `d_id`;
+----------+--------------+
| 学院id   | 学生个数     |
+----------+--------------+
|        1 |            1 |
|        3 |            2 |
|        4 |            1 |
+----------+--------------+
3 rows in set (0.00 sec)

HAVING分组条件
HAVING 后的字段必须是SELECT后出现过的
mysql> SELECT d_id AS 学院id,count(d_id) AS 学生个数 FROM students GROUP BY `d_id`HAVING  学生个数=1;
+----------+--------------+
| 学院id   | 学生个数     |
+----------+--------------+
|        1 |            1 |
|        4 |            1 |
+----------+--------------+
2 rows in set (0.01 sec)

查询中一些较为常见的函数

例如:

求最大年龄
mysql> SELECT MAX(`age`) FROM `stu_details`;
+------------+
| MAX(`age`) |
+------------+
|         20 |
+------------+
1 row in set (0.03 sec)
求最小年龄
mysql> SELECT MIN(`age`) FROM `stu_details`;
+------------+
| MIN(`age`) |
+------------+
|         16 |
+------------+
1 row in set (0.00 sec)
求和
mysql> SELECT SUM(`age`) FROM `stu_details`;
+------------+
| SUM(`age`) |
+------------+
|         73 |
+------------+
1 row in set (0.05 sec)
求平均数
mysql> SELECT AVG(`age`) FROM `stu_details`;
+------------+
| AVG(`age`) |
+------------+
|    18.2500 |
+------------+
1 row in set (0.00 sec)

子查询

出现在其他SQL语句内的SELECT字句。
1)嵌套在查询内部
2)必须始终出现在圆括号内
3)可以包含多个关键字或条件

例如:

查找出大于平均年龄的数据
mysql> SELECT * FROM `stu_details` WHERE `age`>18.25;
+------+------+--------+
| s_id | age  | gender |
+------+------+--------+
|    2 |   19 | f      |
|    4 |   20 | m      |
+------+------+--------+
2 rows in set (0.00 sec)
将平均数的SQL语句作为子查询放入上一条语句中
mysql> SELECT * FROM `stu_details` WHERE `age`>(SELECT AVG(`age`) FROM `stu_details`);
+------+------+--------+
| s_id | age  | gender |
+------+------+--------+
|    2 |   19 | f      |
|    4 |   20 | m      |
+------+------+--------+
2 rows in set (0.10 sec)

联表查询

内连接`[INNER| CROSS] JOIN`

无条件内连接：
无条件内连接，又名交叉连接/笛卡尔连接
第一张表种的每一向会和另一张表的每一项依次组合

有条件内连接
在无条件的内连接基础上，加上一个ON子句
当连接的时候，筛选出那些有实际意义的记录行来进行拼接

在写条件时注意两张表的列名是否一样，
如果时一样的则要在前面加上表名，tb_name.colname这种形式存在

例如:

无条件内连接：
mysql> SELECT * FROM `students` INNER JOIN `department`;
+------+--------+------+----+-----------+
| s_id | name   | d_id | id | name      |
+------+--------+------+----+-----------+
|    1 | ming   |    1 |  1 | foreign   |
|    2 | hong   |    3 |  1 | foreign   |
|    3 | hua    |    3 |  1 | foreign   |
|    4 | xin    |    4 |  1 | foreign   |
|    1 | ming   |    1 |  2 | art       |
|    2 | hong   |    3 |  2 | art       |
|    3 | hua    |    3 |  2 | art       |
|    4 | xin    |    4 |  2 | art       |
|    1 | ming   |    1 |  3 | computer  |
|    2 | hong   |    3 |  3 | computer  |
|    3 | hua    |    3 |  3 | computer  |
|    4 | xin    |    4 |  3 | computer  |
|    1 | ming   |    1 |  4 | abstract  |
|    2 | hong   |    3 |  4 | abstract  |
|    3 | hua    |    3 |  4 | abstract  |
|    4 | xin    |    4 |  4 | abstract  |
+------+--------+------+----+-----------+
16 rows in set (0.04 sec)
有条件内连接：
mysql> SELECT * FROM `students` INNER JOIN `department`
    -> ON d_id=id;
+------+--------+------+----+-----------+
| s_id | name   | d_id | id | name      |
+------+--------+------+----+-----------+
|    1 | ming   |    1 |  1 | foreign   |
|    2 | hong   |    3 |  3 | computer  |
|    3 | hua   |    3 |  3 | computer   |
|    4 | xin   |    4 |  4 | abstract   |
+------+--------+------+----+-----------+
4 rows in set (0.03 sec)

有条件的外连接：
{ LEFT| RIGHT } [OUTER] JOIN

左外连接：
两张表做连接的时候，在连接条件不匹配的时候
留下左表中的数据，而右表中的数据以NULL填充

右外连接
对两张表做连接的时候，在连接条件不匹配的时候
留下右表中的数据，而左表中的数据以NULL填充

例如:

往学生表中添加数据，只添加名字
mysql> INSERT INTO students(name)
    -> VALUES('tom');
Query OK, 1 row affected (0.11 sec)
查看所有学生表数据
mysql> SELECT * FROM  students;
+------+--------+------+
| s_id | name   | d_id |
+------+--------+------+
|    1 | ming   |    1 |
|    2 | hong   |    3 |
|    3 | hua    |    3 |
|    4 | xin    |    4 |
|    5 | tom    | NULL |
+------+--------+------+
5 rows in set (0.00 sec)
使用内连接加条件只能看到有分配好学院的学生的信息；
mysql> SELECT * FROM  students INNER JOIN department
    -> ON d_id=id;
+------+--------+------+----+-----------+
| s_id | name   | d_id | id | name      |
+------+--------+------+----+-----------+
|    1 | ming   |    1 |  1 | foreign   |
|    2 | hong   |    3 |  3 | computer  |
|    3 | hua    |    3 |  3 | computer  |
|    4 | xin    |    4 |  4 | abstract  |
+------+--------+------+----+-----------+
4 rows in set (0.02 sec)
使用左连接把学生的数据全取出来，该学生没有学院信息的用NULL填充
mysql> SELECT * FROM  students LEFT JOIN department
    -> ON d_id=id;
+------+--------+------+------+-----------+
| s_id | name   | d_id | id   | name      |
+------+--------+------+------+-----------+
|    1 | ming   |    1 |    1 | foreign   |
|    2 | hong   |    3 |    3 | computer  |
|    3 | hua    |    3 |    3 | computer  |
|    4 | xin    |    4 |    4 | abstract  |
|    5 | tom    | NULL | NULL | NULL      |
+------+--------+------+------+-----------+
5 rows in set (0.00 sec)
使用右外连接把目前还没有学生的学院的数据也显示出来
mysql> SELECT * FROM  students RIGHT JOIN department
    -> ON d_id=id;
+------+--------+------+----+-----------+
| s_id | name   | d_id | id | name      |
+------+--------+------+----+-----------+
|    1 | ming   |    1 |  1 | foreign   |
|    2 | hong   |    3 |  3 | computer  |
|    3 | hua    |    3 |  3 | computer  |
|    4 | xin    |    4 |  4 | abstract  |
| NULL | NULL   | NULL |  2 | art       |
+------+--------+------+----+-----------+
5 rows in set (0.00 sec)

mysql>

事务

为了保证数据库记录的更新从一个一致性状态变更为另一个一致性状态,使用事务来处理是非常必要。

例如:

创建一张银行账户的表
mysql> CREATE TABLE `account`(
    -> `id` INT PRIMARY KEY AUTO_INCREMENT,
    -> `name` VARCHAR(20) NOT NULL,
    -> `balance` INT
    -> );
Query OK, 0 rows affected (0.52 sec)
添加两个用户及用户的存款的信息
mysql> INSERT INTO `account`(`name`,`balance`)
    ->  VALUES('shangdian',3000),
    ->        ('ming',200)
    -> ;
Query OK, 2 rows affected (0.09 sec)
Records: 2  Duplicates: 0  Warnings: 0

假设现在用户小明在商店买了200元东西，现在要转账给商店，那么就需要从小明的账户上减去200，然后在商店的用户上加上200,
但是如果在减200的过程中出现了系统故障，再重新启动后发现小明的钱扣了，但商店却没有收到，这时候就会出现数据变动不一致。
对于这种数据的修改我们需要的就是要么同时修改成功，要么同时修改失败，所以这就需要用事务来进行出来。

START TRANSACTION：开始一个新的事务
COMMIT：提交当前事务，做出永久改变
ROLLBACK：回滚当前事务，放弃修改

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE `account`
    -> SET `balance`= `balance`-200
    -> WHERE `name` ='ming'
    -> ;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

使用ROLLBACK;使数据的修改不生效，回到事务前的状态：
mysql> ROLLBACK;
Query OK, 0 rows affected (0.06 sec)


做一次正确的操作：
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE `account`
    -> SET `balance`=`balance`-200
    -> WHERE `name`='ming'
    -> ;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE `account`
    -> SET `balance`=`balance`+200
    ->
    -> WHERE `name`='shangdian'
    -> ;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM `account`;

mysql> COMMIT;
Query OK, 0 rows affected (0.07 sec)

当COMMIT后，数据修改成功，ROLLBACK也没法回到之前了。

mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)
```f

(八).Linux下使用mysql-4(建表,查询)
五张关系表的创建往数据表中添加数据查询查询所有记录 SELECT * FROM tb_name; 查询选中列...
网络日志的Hive简单查询
测试数据建表语句加载数据连接查询使用查询结果创建新表
DDL-数据表操作
查询查询当前数据库所有表查询表结构查询指定表的建表语句创建
Python+MySQL数据库操作（PyMySQL）
安装mysql驱动连接数据库建表插入查询 Python查询Mysql使用 fetchone() 方法获取单...
Hive 基本语法
建表通用建表利用查询结果建表建表并复制表结构 Tips TABLE 和 EXTERNAL TABLE 主要区...
案例加源码：万字长文带你彻底搞懂MySQL的索引优化
一、索引优化 1，单表索引优化建表查询案例查询category_id为1且comments 大于1的情况下，...
2019-03-25
-- 建表 -- 新建一张表 create table 表名( .... ) -- 子查询建表只继承结构和数据 ...
MySQL连接查询索引优化
一、单表索引优化建表：建一张表，SQL如下：需求：查询出类别id为1 且评论量大于1的情况下，阅读量最...
2019-02-22
mySQL Navicat for mySQL 关系型数据库：用表传数据如何建表：查询→新建查询注释： -- ...
MySQL ：Engine存储引擎
在使用Mysql建表过程中，有时候会遇到一些奇怪的现象。例如，如何插入数据就是查询不到数据，此时可能是建表的存储引...

(八).Linux下使用mysql-4(建表,查询)

五张关系表的创建

往数据表中添加数据

查询

查询所有记录

查询选中列数据

查询指定条件下的记录

查询后为列取别名

排序`GROUP BY`

限制显示数据的数量`LIMIT`

分组查询`GROUP BY`

查询中一些较为常见的函数

子查询

联表查询

内连接`[INNER| CROSS] JOIN`

事务

相关文章

(八).Linux下使用mysql-4(建表,查询)

网络日志的Hive简单查询

DDL-数据表操作

Python+MySQL数据库操作（PyMySQL）

Hive 基本语法

案例加源码：万字长文带你彻底搞懂MySQL的索引优化

2019-03-25

MySQL连接查询索引优化

2019-02-22

MySQL ：Engine存储引擎

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

(八).Linux下使用mysql-4(建表,查询)

五张关系表的创建

往数据表中添加数据

查询

查询所有记录

查询选中列数据

查询指定条件下的记录

查询后为列取别名

排序GROUP BY

限制显示数据的数量LIMIT

分组查询GROUP BY

查询中一些较为常见的函数

子查询

联表查询

内连接[INNER| CROSS] JOIN

事务

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

排序`GROUP BY`

限制显示数据的数量`LIMIT`

分组查询`GROUP BY`

内连接`[INNER| CROSS] JOIN`