MySql数据库基础知识(包括workbench)

2021/11/9 2:13:52

本文主要是介绍MySql数据库基础知识(包括workbench),对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

文章目录

  • 1 Table and Keys
    • 0 引言
    • 1.1基本概念
    • 1.2基础操作
      • 1.2.0
      • 1.2.1 SELECT UPDATE DELETE
        • 显示表中的全部数据
        • 显示表中的某两行数据
        • 选择显示名字叫Mike的所有数据
        • 更新表格里的某个数据
        • 多条件选取
        • 多条件删除数据
  • 2 More Basic Query
    • 2.1相关命令解释
    • 2.2 相关命令练习
      • 2.2.1 排序 (数字、字母)
      • 2.2.2查找列表的前几行
      • 2.2.3 给表格的name起小名
      • 2.2.4 DISTINCT 提取出不同数值
      • 2.2.5 选择日期
    • 2.3通用符 wild card
        • LIKE
        • REGEXP 正则表达式
  • 3 MySql workbench 基础操作
    • 3.1学习资料
    • 3.2 Workbench
      • 3.2.1界面介绍
      • 3.2.2 JOIN (表与表之间的关系)
        • 3.2.2.1
        • 3.2.3 使用MySql Workbench 创建 表

1 Table and Keys

0 引言

如果想在一个已经建好的表中添加一列,可以用以下代码:

alter table 表名 add column 列名 varchar(20) not null;

这条语句会向已有的表中加入一列,这一列在表的最后一列位置。如果我们希望添加在指定的一列,可以用:

alter table 表名 add column 列名 varchar(20) not null after user1;

注意,上面这个命令的意思是说添加addr列到user1这一列后面。如果想添加到第一列的话,可以用:

alter table 表名 add column 列名 varchar(20) not null first;


将表yusheng中,列名def改为unit

alter table yusheng change  def unit char;


将表yusheng中,列名def的列删除

alter table yusheng drop column def ;

1.1基本概念

MySql安装教程:见其他专栏
Primary Key 主键 针对每一行都是唯一的 PRI会出现在表格里
Foreign Key 外键 与表内的数据或表外的数据相关联
branch_id
Branch Table
Supplier Table
在这里插入图片描述
在这里插入图片描述

1.2基础操作

1.2.0

打开下图的软件
在这里插入图片描述
依次输入以下代码
1.创建数据库

mysql> CREATE DATABASE school;  //创建数据库 school
Query OK, 1 row affected (0.02 sec)

2.使用这个数据库

mysql> USE school; //使用这个数据库
Database changed

3.创建表格

mysql> CREATE TABLE student(           //创建student表格
    -> student_id INT PRIMARY KEY,
    -> name VARCHAR(20),
    -> major VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.07 sec) //创建成功

4.展示创建的表格

mysql> DESCRIBE student;  //展示创建的表格
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| student_id | int         | NO   | PRI | NULL    |       |
| name       | varchar(20) | YES  |     | NULL    |       |
| major      | varchar(20) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

5.表格增加一列

mysql> ALTER TABLE student ADD gpa DECIMAL(3,2); //往表格里增加一行东西
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0
///通过下面的表格可以看到  student_id属于PRIMARY KEY 唯一 不可更改 且不允许为空值  name major gpa都允许空值

///varchar(20) 表示其可以有20个字符
mysql> DESCRIBE student;  //展示新加的表格数据
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| student_id | int          | NO   | PRI | NULL    |       |
| name       | varchar(20)  | YES  |     | NULL    |       |
| major      | varchar(20)  | YES  |     | NULL    |       |
| gpa        | decimal(3,2) | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

6.往表格里插入数据

 INSERT INTO student VALUES(2,'John','English',2);//往表格里插入数据
 INSERT INTO student (student_id,name) VALUES(3,'Jonas'); //往表格里插入数据
 mysql> SELECT * FROM student; //SELECT * FROM    *号表示全部
+------------+-------+---------+------+
| student_id | name  | major   | gpa  |
+------------+-------+---------+------+
|          1 | Mike  | Math    | 3.90 |
|          2 | John  | English | 2.00 |
|          3 | Jonas | NULL    | NULL |
+------------+-------+---------+------+
3 rows in set (0.00 sec)
ALTER TABLE student DROP COLUMN gpa; //删除GPA这一列
mysql> SELECT * FROM student; //显示数据
+------------+-------+---------+
| student_id | name  | major   |
+------------+-------+---------+
|          1 | Mike  | Math    |
|          2 | John  | English |
|          3 | Jonas | NULL    |
+------------+-------+---------+
3 rows in set (0.00 sec)

mysql在表的某一位置增加一列、删除一列、修改列名

1.2.1 SELECT UPDATE DELETE

各种命令
SELECT the whole table 选取整个表
SELECT columns 按条件选取列
SELECT rows/records 按条件选取行
UPDATE row/records 更新表的内容
Multi-condition SELECT 多条件选取
Multi-condition UPDATE 多条件更新
Multi-condition DELETE 多条件删除

代码编写

显示表中的全部数据

mysql> SELECT * FROM student;
+------------+-------+---------+
| student_id | name  | major   |
+------------+-------+---------+
|          1 | Mike  | Math    |
|          2 | John  | English |
|          3 | Jonas | NULL    |
+------------+-------+---------+
3 rows in set (0.00 sec)

显示表中的某两行数据

mysql> SELECT  name,major FROM student;  
+-------+---------+
| name  | major   |
+-------+---------+
| Mike  | Math    |
| John  | English |
| Jonas | NULL    |
+-------+---------+
3 rows in set (0.00 sec)

选择显示名字叫Mike的所有数据

mysql> SELECT * FROM student WHERE name = 'Mike';
+------------+------+-------+
| student_id | name | major |
+------------+------+-------+
|          1 | Mike | Math  |
+------------+------+-------+
1 row in set (0.00 sec)

更新表格里的某个数据

mysql> UPDATE student SET name ='Mi' WHERE name = 'Mike';  //把Mike更新为Mi
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM student;
+------------+-------+---------+
| student_id | name  | major   |
+------------+-------+---------+
|          1 | Mi    | Math    |
|          2 | John  | English |
|          3 | Jonas | NULL    |
+------------+-------+---------+
3 rows in set (0.00 sec)

多条件选取

1.先创建下图所示的表格

mysql> SELECT * FROM student1;
+------------+------------+-----------+--------+------+-------------+-------------+
| student_id | first_name | last_name | gender | gpa  | major       | nationality |
+------------+------------+-----------+--------+------+-------------+-------------+
|          1 | Michael    | Boss      | M      | 3.90 | Mathematics | US          |
|          2 | John       | Leek      | M      | 2.85 | English     | UK          |
|          3 | Jonas      | Daffy     | M      | 3.30 | History     | DE          |
|          4 | Julian     | Brandt    | M      | 3.33 | Physics     | DE          |
|          5 | Gavin      | DeGraw    | M      | 3.52 | Music       | US          |
|          6 | Juan       | Cervantes | M      | 3.22 | Music       | ES          |
|          7 | Vladmir    | Ivanov    | M      | 3.80 | Computer    | RU          |
|          8 | Michael    | Hoffmann  | M      | 3.50 | History     | DE          |
+------------+------------+-----------+--------+------+-------------+-------------+

2.多条件选取
从上面表格里选取 gpa < 3.5 且为男性的条框 或的 就把 AND 改成 OR

mysql> SELECT * FROM student1 WHERE gpa<3.5 AND gender = 'M';
+------------+------------+-----------+--------+------+---------+-------------+
| student_id | first_name | last_name | gender | gpa  | major   | nationality |
+------------+------------+-----------+--------+------+---------+-------------+
|          2 | John       | Leek      | M      | 2.85 | English | UK          |
|          3 | Jonas      | Daffy     | M      | 3.30 | History | DE          |
|          4 | Julian     | Brandt    | M      | 3.33 | Physics | DE          |
|          6 | Juan       | Cervantes | M      | 3.22 | Music   | ES          |
+------------+------------+-----------+--------+------+---------+-------------+
4 rows in set (0.00 sec)

更改多个条件下指定的某些数据

mysql> UPDATE student1 SET nationality = 'Ge' WHERE gender = 'M' AND nationality = 'DE';
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0
mysql> SELECT * FROM student1;
+------------+------------+-----------+--------+------+-------------+-------------+
| student_id | first_name | last_name | gender | gpa  | major       | nationality |
+------------+------------+-----------+--------+------+-------------+-------------+
|          1 | Michael    | Boss      | M      | 3.90 | Mathematics | US          |
|          2 | John       | Leek      | M      | 2.85 | English     | UK          |
|          3 | Jonas      | Daffy     | M      | 3.30 | History     | Ge          |
|          4 | Julian     | Brandt    | M      | 3.33 | Physics     | Ge          |
|          5 | Gavin      | DeGraw    | M      | 3.52 | Music       | US          |
|          6 | Juan       | Cervantes | M      | 3.22 | Music       | ES          |
|          7 | Vladmir    | Ivanov    | M      | 3.80 | Computer    | RU          |
|          8 | Michael    | Hoffmann  | M      | 3.50 | History     | Ge          |
+------------+------------+-----------+--------+------+-------------+-------------+

多条件删除数据

mysql> DELETE FROM student1 WHERE student_id = 3;
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM student1;
+------------+------------+-----------+--------+------+-------------+-------------+
| student_id | first_name | last_name | gender | gpa  | major       | nationality |
+------------+------------+-----------+--------+------+-------------+-------------+
|          1 | Michael    | Boss      | M      | 3.90 | Mathematics | US          |
|          2 | John       | Leek      | M      | 2.85 | English     | UK          |
|          4 | Julian     | Brandt    | M      | 3.33 | Physics     | Ge          |
|          5 | Gavin      | DeGraw    | M      | 3.52 | Music       | US          |
|          6 | Juan       | Cervantes | M      | 3.22 | Music       | ES          |
|          7 | Vladmir    | Ivanov    | M      | 3.80 | Computer    | RU          |
|          8 | Michael    | Hoffmann  | M      | 3.50 | History     | Ge          |
+------------+------------+-----------+--------+------+-------------+-------------+
7 rows in set (0.00 sec)

2 More Basic Query

2.1相关命令解释

ORDER BY ASC/ DESC 排序
LIMIT 想看到表里的某一行
ALIAS 给文章起个小名
< ,> ,>=,<=, =, 关系符号
BETWEEN 在什么什么之前查询
IN 很神奇的小词
DISTINCT 所有不同的值 提出来一个

2.2 相关命令练习

2.2.1 排序 (数字、字母)

数字排序

mysql> SELECT * FROM student1 ORDER BY gpa DESC; //从大到小排下去 不加
//DESC表示从小到大
+------------+------------+-----------+--------+------+-------------+-------------+
| student_id | first_name | last_name | gender | gpa  | major       | nationality |
+------------+------------+-----------+--------+------+-------------+-------------+
|          1 | Michael    | Boss      | M      | 3.90 | Mathematics | US          |
|          7 | Vladmir    | Ivanov    | M      | 3.80 | Computer    | RU          |
|          5 | Gavin      | DeGraw    | M      | 3.52 | Music       | US          |
|          8 | Michael    | Hoffmann  | M      | 3.50 | History     | Ge          |
|          4 | Julian     | Brandt    | M      | 3.33 | Physics     | Ge          |
|          6 | Juan       | Cervantes | M      | 3.22 | Music       | ES          |
|          2 | John       | Leek      | M      | 2.85 | English     | UK          |
+------------+------------+-----------+--------+------+-------------+-------------+
7 rows in set (0.00 sec)

字段排序 默认从A–Z;

mysql> SELECT * FROM student1 ORDER BY first_name; //加DESC表示倒序
+------------+------------+-----------+--------+------+-------------+-------------+
| student_id | first_name | last_name | gender | gpa  | major       | nationality |
+------------+------------+-----------+--------+------+-------------+-------------+
|          5 | Gavin      | DeGraw    | M      | 3.52 | Music       | US          |
|          2 | John       | Leek      | M      | 2.85 | English     | UK          |
|          6 | Juan       | Cervantes | M      | 3.22 | Music       | ES          |
|          4 | Julian     | Brandt    | M      | 3.33 | Physics     | Ge          |
|          1 | Michael    | Boss      | M      | 3.90 | Mathematics | US          |
|          8 | Michael    | Hoffmann  | M      | 3.50 | History     | Ge          |
|          7 | Vladmir    | Ivanov    | M      | 3.80 | Computer    | RU          |
+------------+------------+-----------+--------+------+-------------+-------------+
7 rows in set (0.00 sec)

选择表格中的两列进行排序 (规则 先按照一列再按照另一列)

mysql> SELECT * FROM student1 ORDER BY first_name,gpa;//改变gpa first_name的顺序 排列规则也会变化
+------------+------------+-----------+--------+------+-------------+-------------+
| student_id | first_name | last_name | gender | gpa  | major       | nationality |
+------------+------------+-----------+--------+------+-------------+-------------+
|          5 | Gavin      | DeGraw    | M      | 3.52 | Music       | US          |
|          2 | John       | Leek      | M      | 2.85 | English     | UK          |
|          6 | Juan       | Cervantes | M      | 3.22 | Music       | ES          |
|          4 | Julian     | Brandt    | M      | 3.33 | Physics     | Ge          |
|          8 | Michael    | Hoffmann  | M      | 3.50 | History     | Ge          |
|          1 | Michael    | Boss      | M      | 3.90 | Mathematics | US          |
|          7 | Vladmir    | Ivanov    | M      | 3.80 | Computer    | RU          |
+------------+------------+-----------+--------+------+-------------+-------------+
7 rows in set (0.00 sec)

2.2.2查找列表的前几行

mysql> SELECT * FROM student1 LIMIT 3; //查找表的前三行
+------------+------------+-----------+--------+------+-------------+-------------+
| student_id | first_name | last_name | gender | gpa  | major       | nationality |
+------------+------------+-----------+--------+------+-------------+-------------+
|          1 | Michael    | Boss      | M      | 3.90 | Mathematics | US          |
|          2 | John       | Leek      | M      | 2.85 | English     | UK          |
|          4 | Julian     | Brandt    | M      | 3.33 | Physics     | Ge          |
+------------+------------+-----------+--------+------+-------------+-------------+
3 rows in set (0.00 sec)

2.2.3 给表格的name起小名

mysql> SELECT first_name AS fn,last_name AS ln FROM student1;
+---------+-----------+
| fn      | ln        |
+---------+-----------+
| Michael | Boss      |
| John    | Leek      |
| Julian  | Brandt    |
| Gavin   | DeGraw    |
| Juan    | Cervantes |
| Vladmir | Ivanov    |
| Michael | Hoffmann  |
+---------+-----------+
7 rows in set (0.00 sec)

2.2.4 DISTINCT 提取出不同数值

mysql> SELECT DISTINCT gender FROM student1;
+--------+
| gender |
+--------+
| M      |
+--------+
1 row in set (0.00 sec)

2.2.5 选择日期

mysql> SELECT first_name,birthday FROM student1 WHERE birthday >= '1999-01-01';
mysql> SELECT first_name,birthday FROM student1 WHERE birthday BETWEEN '2000-01-01' AND '2002-01-02';

在原来的表格里增加一列birthday

mysql> ALTER TABLE student1 ADD birthday DATE AFTER last_name;

在原来的表格里更改birthday数据

mysql> UPDATE student1 SET birthday = '2000-12-23' WHERE student_id = 1;

挑出具有相同特征的条目
找出GPA为3.90 3.22的人

mysql> SELECT first_name FROM student1 WHERE gpa IN(3.90,3.22);
+------------+
| first_name |
+------------+
| Michael    |
| Juan       |
+------------+
2 rows in set (0.00 sec)

2.3通用符 wild card

LIKE

  • ‘%’ --any charcters ‘Mi%’ //匹配多个字符
  • ‘_’ – single characters ‘_t’ ,’__t’ //匹配一个字符
  • NOT – Negate // 表示否定``

应用

mysql> SELECT * FROM student1 WHERE first_name LIKE 'J%'; //以J开头
mysql> SELECT * FROM student1 WHERE first_name LIKE '%s'; //以s结尾
mysql> SELECT * FROM student1 WHERE first_name LIKE '%mi%';//mi在任何位置
mysql> SELECT * FROM student1 WHERE major LIKE '__t'; //t前面有两个字符
mysql> SELECT * FROM student1 WHERE major LIKE 'H__t'; //h t中间有两个字符
mysql> SELECT * FROM student1 WHERE gpa NOT LIKE '%0'; //找GPA里不以零结尾的数据

REGEXP 正则表达式

  • | – Multiselect 'Bo | Br ’
  • ‘^’ --Start with ‘^M’ 查询的语句以M为开头
  • ‘ ′ − − E n d w i t h ′ n n ' -- End with 'nn ′−−Endwith′nn’ 以nn为结尾
  • […] --one of the characters in the brackets ‘[ai]n’ 以an或者in开头
mysql> SELECT * FROM student1 WHERE last_name REGEXP 'BO|Br'; 
mysql> SELECT * FROM student1 WHERE major REGEXP '^M'; 
mysql> SELECT * FROM student1 WHERE last_name REGEXP 'nn$';
mysql> SELECT * FROM student1 WHERE last_name REGEXP 'le|^ce'; //含有le或者以ce为开头的	
mysql> SELECT * FROM student1 WHERE last_name REGEXP '[ai]n';

3 MySql workbench 基础操作

3.1学习资料

在这里插入图片描述>示例操作中用到的数据库
链接:https://pan.baidu.com/s/1KBmB2co1M5_rEvE2ehxe5w
提取码:03v4
–来自百度网盘超级会员V4的分享

3.2 Workbench

3.2.1界面介绍

在这里插入图片描述
在这里插入图片描述

3.2.2 JOIN (表与表之间的关系)

3.2.2.1

-- JOIN in same database;同一种数据库表与表之间的连接
-- JOIN cross database;不同数据库之间的连接
-- SELF JOIN ;同一个表内连接alter
-- Alias
-- Ambiguous and Clarity 

打开数据库order 在Query中逐次输入以下代码

-- 在两张表之间进行连接  可以在Result Grid中看到连接结果
SELECT * 
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id;

-- 在两种表之间进行连接 但是只显示 部分列的内容
SELECT order_id,first_name,last_name
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id;

-- 采用Alias简化代码
SELECT order_id,first_name,last_name
FROM orders o  -- orders 被简化为o
JOIN customers c ON o.customer_id = c.customer_id;

-- 显示customer_id时要明确显示哪个表里的 o.customer_id
SELECT order_id,first_name,last_name,o.customer_id
FROM orders o  -- orders 被简化为o
JOIN customers c ON o.customer_id = c.customer_id;

3.2.3 使用MySql Workbench 创建 表



这篇关于MySql数据库基础知识(包括workbench)的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!


扫一扫关注最新编程教程