美文网首页
(八).Linux下使用mysql-4(建表,查询)

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

作者: 木子心语 | 来源:发表于2018-04-21 10:13 被阅读0次
    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(建表,查询)

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

          延伸阅读

          深度阅读

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

          栏目导航

          热点阅读

          关于我们|服务条款|联系我们|(八).Linux下使用mysql-4(建表,查询)|投稿指南|网站地图|RSS订阅|排版工具|手机版

          提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

          Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

          备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

          本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

          所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！