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）的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！
- 2024-11-16MySQL资料：新手入门教程
- 2024-11-16MySQL资料：新手入门教程
- 2024-11-15MySQL教程：初学者必备的MySQL数据库入门指南
- 2024-11-15MySQL教程：初学者必看的MySQL入门指南
- 2024-11-04部署MySQL集群项目实战：新手入门教程
- 2024-11-04如何部署MySQL集群资料：新手入门指南
- 2024-11-02MySQL集群项目实战：新手入门指南
- 2024-11-02初学者指南：部署MySQL集群资料
- 2024-11-01部署MySQL集群教程：新手入门指南
- 2024-11-01如何部署MySQL集群：新手入门教程
