MySql数据库基础知识（包括workbench）

最新推荐文章于 2024-05-16 23:19:42 发布

dhuwangDHU

最新推荐文章于 2024-05-16 23:19:42 发布

阅读量307

点赞数

分类专栏： mysql数据库 C# 基础知识文章标签： mysql 数据库 database

本文链接：https://blog.csdn.net/dhuwangdhu/article/details/121153724

版权

mysql数据库同时被 2 个专栏收录

2 篇文章 0 订阅

订阅专栏

C# 基础知识

2 篇文章 2 订阅

订阅专栏

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$ ’ 以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 创建表

3.3 SQL语句深入了解

见 SQL教程

dhuwangDHU

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySql数据库基础知识（包括workbench）

1 Table and Keys1.1基本概念MySql安装教程：见其他专栏Primary Key 主键针对每一行都是唯一的 PRI会出现在表格里Foreign Key 外键与表内的数据或表外的数据相关联branch_idBranch TableSupplier Table1.2基础操作打开下图的软件依次输入以下代码1.创建数据库mysql> CREATE DATABASE school; //创建数据库 schoolQuery OK, 1 row affec
复制链接

扫一扫