文章目录
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;