Mysql基本操作语句

最新推荐文章于 2022-09-02 19:33:09 发布

agogla

最新推荐文章于 2022-09-02 19:33:09 发布

阅读量541

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/weixin_41796255/article/details/106618671

版权

mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

21条sql基础语句

引言

测试必备数据库，21条sql语句实操

连接数据库

方法1：使用MySQL二进制方式进入到mysql命令提示符下来连接MySQL数据库。

[root@host]# mysql -u root -p
Enter password:******

退出：

mysql> exit
Bye

创建数据库

1）创建数据库

2）查看数据库创建是否成功

直接创建：

CREATE TABLE table_name (column_name column_type);

在用户权限下创建，使用 mysqladmin 创建数据库

[root@host]# mysqladmin -u root -p create RUNOOB
Enter password:******

查看是否成功

show DATABASES [IF EXISTS] <DB_NAME>;

创建数据表CREATE

选择数据库

二级标题在数据库下创建表需先选择数据库

mysql> use BTS
Database changed

1）表1的字段、类型、属性：

id INT(10) NOT NULL UNIQUE PRIMARY KEY ,
uname VARCHAR(20) NOT NULL ,
sex VARCHAR(4) ,
birth YEAR,
department VARCHAR(20) ,
address VARCHAR(50) ,
idoxu VARCHAR(20)

2）表2的字段、类型、属性：

id INT(10) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT ,
stu_id INT(10) NOT NULL ,
c_name VARCHAR(20) ,
istester VARCHAR(50) ,
grade INT(10)

创建数据表需要的信息：

标明
表字段名
定义每个字段

语法：

CREATE TABLE table_name (column_name column_type);

1）love_yourself表的创建

mysql> CREATE TABLE love_yourself(
    -> id  INT(10)  NOT NULL  UNIQUE  PRIMARY KEY,
    -> uname  VARCHAR(20)  NOT NULL,
    -> sex  VARCHAR(4),
    -> birth  YEAR,
    -> department  VARCHAR(20),
    -> address  VARCHAR(50),
    -> idoxu VARCHAR(20)
    -> );

2）wings表的创建

mysql> CREATE TABLE wings(
    -> id  INT(10)  NOT NULL  UNIQUE  PRIMARY KEY  AUTO_INCREMENT,
    -> stu_id  INT(10)  NOT NULL,
    -> c_name  VARCHAR(20),
    -> istester VARCHAR(50),
    -> grade  INT(10)
    -> );

根据已有的表创建新表（复制表）

1）复制love_yourself表结构，创建新表love_myself

注：可以有两种方式

#方法1
mysql> CREATE TABLE love_myself SELECT * FROM love_yourself WHERE 2=1;
#方法2
mysql> CREATE love_myself LIKE love_yourself;

2）复制wings表结构和数据，创建新表wings2

mysql> CREATE TABLE wings SELECT * FROM wing;

3）复制wings表结构的 id,stu_id,istester三个字段，创建新表wings3

 CREATE TABLE wings2 SELECT id,stu_id,isTester FROM wings;

这么结果也对，还有另外一种语法

CREATE TABLE wings3 AS SELECT id,stu_id,istester FROM wings where 1<>1;

在这里插入图片描述

删除表DROP

1）删除表 love_myself2

2）同时删除表wings2和wings3

3）查看还有哪些表

语法：

DROP TABLE table_name ;

1）删除单个表

mysql> DROP TABLE love_myself;

2）同时删除两个表

mysql> DROP TABLE wings2,wings3;

3）查看剩余表

mysql> SHOW TABLES;

修改表的字段 ALTER

1）修改love_yourself表，添加一个字段thumb_up （字符类型VARCHAR，长度100，不允许为空）

2）查看字段thumb_up ，是否添加成功

3）修改love_yourself表，删除字段thumb_up

4）确认字段thumb_up ，是否删除成功

ALTER命令

修改数据表名或者修改数据表字段时，就需要使用到MySQL ALTER命令。

添加ADD

mysql> ALTER TABLE table_name ADD field_name type ;
删除DROP

mysql> ALTER TABLE table_name DROP field_name ;
修改MODIFY

mysql> ALTER TABLE table_name MODIFY table_name type ;
修改CHANGE

mysql> ALTER TABLE table_name CHANGE field_name new_name new_type;

在 CHANGE 关键字之后，紧跟着的是你要修改的字段名，然后指定新字段名及类型。

如果你需要指定新增字段的位置，可以使用MySQL提供的关键字 FIRST (设定位第一列)， AFTER 字段名（设定位于某个字段之后）。

1）2）新增字段并查看

mysql> ALTER TABLE love_yourself ADD COLUMN thumb_up VARCHAR(100) NOT NULL;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> SHOW COLUMNS FROM love_yourself;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| id         | int          | NO   | PRI | NULL    |       |
| uname      | varchar(20)  | NO   |     | NULL    |       |
| sex        | varchar(4)   | YES  |     | NULL    |       |
| birth      | year         | YES  |     | NULL    |       |
| department | varchar(20)  | YES  |     | NULL    |       |
| address    | varchar(50)  | YES  |     | NULL    |       |
| idoxu      | varchar(20)  | YES  |     | NULL    |       |
| thumb_up   | varchar(100) | NO   |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

3）4）删除字段并查看

mysql> ALTER TABLE love_yourself DROP thumb_up;
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW COLUMNS FROM love_yourself;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id         | int         | NO   | PRI | NULL    |       |
| uname      | varchar(20) | NO   |     | NULL    |       |
| sex        | varchar(4)  | YES  |     | NULL    |       |
| birth      | year        | YES  |     | NULL    |       |
| department | varchar(20) | YES  |     | NULL    |       |
| address    | varchar(50) | YES  |     | NULL    |       |
| idoxu      | varchar(20) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

mysql> DESC love_yourself;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id         | int         | NO   | PRI | NULL    |       |
| uname      | varchar(20) | NO   |     | NULL    |       |
| sex        | varchar(4)  | YES  |     | NULL    |       |
| birth      | year        | YES  |     | NULL    |       |
| department | varchar(20) | YES  |     | NULL    |       |
| address    | varchar(50) | YES  |     | NULL    |       |
| idoxu      | varchar(20) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

插入数据INSERT

1）向表love_yourself插入数据

id = 1 ,uname = idoxu ,idoxu = 2020
id = 2 ,uname = idoxu2 ,idoxu = 2020

id=11,uname = idoxu3,sex=1
id=12,uname = idoxu4,sex=2
id=13,uname = idoxu5,sex=1
id=14,uname = idoxu6,sex=2

2）向表wings插入数据

id=4,stu_id=11,c_name=idoxu,grade=90
id=5,stu_id=12,c_name=lin,grade=100
id=6,stu_id=33,c_name=istester,grade=20

向MySQL数据表插入数据通用的 INSERT INTO SQL语法：

INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );

1）向表love_yourself插入数据

mysql> INSERT INTO love_yourself
    -> (id ,uname ,idoxu )
    -> VALUES
    -> (1,"JIN",2020);
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO love_yourself
    -> (id ,uname ,idoxu )
    -> VALUES
    -> (2,"SG",2020);
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO love_yourself
    -> (id ,uname ,idoxu )
    -> VALUES
    -> (3,"JH",2019);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO love_yourself
    -> (id ,uname ,idoxu )
    -> VALUES
    -> (4,"RM",2019);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO love_yourself
    -> (id ,uname ,idoxu )
    -> VALUES
    -> (5,"JM",2018);
Query OK, 1 row affected (0.13 sec)

mysql> INSERT INTO love_yourself
    -> (id ,uname ,sex )
    -> VALUES
    -> (6,"V",1);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO love_yourself
    -> (id ,uname ,sex )
    -> VALUES
    -> (7,"JK",1);
Query OK, 1 row affected (0.01 sec)

2）向表wings插入数据

mysql> INSERT INTO wings
    -> (id ,stu_id,c_name,grade )
    -> VALUES
    -> (56,95,"VMIN",3),(2,67,"TAEKOOK",1),(3,57,"KM",2);

修改表的内容

mysql> UPDATE `wings` SET `id`=1 WHERE `stu_id`=95;

注意：表名用 ` 符号而不是’ ‘

查表SELECT1

1）查询istester表 id = 1 的内容

2）查询idoxu 表 grade=100 的内容

mysql> SELECT * FROM `love_yourself` WHERE `id`=1;
+----+-------+------+-------+------------+---------+-------+
| id | uname | sex  | birth | department | address | idoxu |
+----+-------+------+-------+------------+---------+-------+
|  1 | JIN   | NULL |  NULL | NULL       | NULL    | 2020  |
+----+-------+------+-------+------------+---------+-------+
1 row in set (0.00 sec)

mysql> SELECT * FROM `wings` WHERE `grade`=2;
+----+--------+--------+----------+-------+
| id | stu_id | c_name | istester | grade |
+----+--------+--------+----------+-------+
|  3 |     57 | KM     | NULL     |     2 |
+----+--------+--------+----------+-------+
1 row in set (0.00 sec)

语法

SELECT column_name,column_name
FROM `table_name`
[WHERE Clause]
[LIMIT N][ OFFSET M]

查询语句中你可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件。
SELECT 命令可以读取一条或者多条记录。
你可以使用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据
你可以使用 WHERE 语句来包含任何条件。
你可以使用 LIMIT 属性来设定返回的记录数。
你可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。

WHERE条件查询

比较运算 ‘<’ ‘<=’ ‘>’ ‘>=’ ‘!=’ （多个条件之间用’AND‘连接）
与条件 (AND，BETWEEN)
或条件（OR，IN） （IN+范围（，））
非条件（！=，NOT） （NOT与LIKE连用）

查表SELECT2

1）查找wings表，名称（c_name）包含 “M” 的数据

2）查找love_yourself表，idoxu 包含 “1” 的数据

3）查找love_yourself表，idoxu 包含 “1” 的数据，按idoxu降序

4）查找love_yourself表，idoxu 包含 “1” 的数据，取idoxu最大的2个

LIKE模糊查询
SQL中可以使用模糊查询like，其中 ‘_’ 占一位 ‘%’占多位。三种表示方法：

1. 包含’M’ : %M%
2. 第二位为’M’ : _M%
3. 倒数第二位为’M’ : %M_

1）2）模糊查询

mysql> SELECT * FROM `wings` WHERE `c_name` LIKE '%M%';
+----+--------+--------+----------+-------+
| id | stu_id | c_name | istester | grade |
+----+--------+--------+----------+-------+
|  1 |     95 | VMIN   | NULL     |     3 |
|  3 |     57 | KM     | NULL     |     2 |
+----+--------+--------+----------+-------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM `love_yourself` WHERE `idoxu` LIKE '%1%';
+----+-------+------+-------+------------+---------+-------+
| id | uname | sex  | birth | department | address | idoxu |
+----+-------+------+-------+------------+---------+-------+
|  3 | JH    | NULL |  NULL | NULL       | NULL    | 2019  |
|  4 | RM    | NULL |  NULL | NULL       | NULL    | 2019  |
|  5 | JM    | NULL |  NULL | NULL       | NULL    | 2018  |
+----+-------+------+-------+------------+---------+-------+
3 rows in set (0.00 sec)

3）排序查询

ORDER BY排序查询
SQL中可以对查询结果进行排序，DESC表示按从大到小排序，ASC表示按从小到大排序，默认为从小到大排序。

SELECT *
FROM `table_name` 
ORDER BY `u_score` DESC;

mysql> SELECT * FROM `love_yourself` WHERE `idoxu` LIKE '%1%'
    -> ORDER BY `IDOXU` DESC/ASC;
+----+-------+------+-------+------------+---------+-------+
| id | uname | sex  | birth | department | address | idoxu |
+----+-------+------+-------+------------+---------+-------+
|  3 | JH    | NULL |  NULL | NULL       | NULL    | 2019  |
|  4 | RM    | NULL |  NULL | NULL       | NULL    | 2019  |
|  5 | JM    | NULL |  NULL | NULL       | NULL    | 2018  |
+----+-------+------+-------+------------+---------+-------+
3 rows in set (0.00 sec)

4）限制结果的数量

mysql> SELECT * FROM `love_yourself` WHERE `idoxu` LIKE '%1%'
    -> ORDER BY `IDOXU` DESC
    -> LIMIT 2;
+----+-------+------+-------+------------+---------+-------+
| id | uname | sex  | birth | department | address | idoxu |
+----+-------+------+-------+------------+---------+-------+
|  3 | JH    | NULL |  NULL | NULL       | NULL    | 2019  |
|  4 | RM    | NULL |  NULL | NULL       | NULL    | 2019  |
+----+-------+------+-------+------------+---------+-------+
2 rows in set (0.00 sec)

LIMIT

SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。
如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。

offset默认值为0，如果只给定一个参数，它表示返回最大的记录行数目
检索某几行数据
为了检索从某一个偏移量到最末所有的记录行，可以指定第二个参数为 -1

mysql> SELECT * FROM table LIMIT 5,10; //检索记录行6-15 
mysql> SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last.
mysql> SELECT * FROM table LIMIT 5;     //检索前 5 个记录行

参考链接

查表SELECT3

1）找出wings表中，分数最高的同学和分数

2）找出wings表中，分数最低的同学和分数

1）找最大值

mysql> SELECT `c_name`,`grade` FROM `wings`
    -> ORDER BY `stu_id` DESC LIMIT 1;
+--------+-------+
| c_name | grade |
+--------+-------+
| VMIN   |     3 |
+--------+-------+
1 row in set (0.00 sec)

mysql> SELECT `c_name`,`grade` AS "maxvalue" FROM `wings`
    -> WHERE `grade` IN (SELECT MAX(grade) FROM `wings`);
+--------+----------+
| c_name | maxvalue |
+--------+----------+
| VMIN   |        3 |
+--------+----------+
1 row in set (0.00 sec)

2）找最小值

mysql> SELECT `c_name`,`grade` AS "minvalue" FROM `wings`
    -> WHERE `grade` IN (SELECT MIN(grade) FROM `wings`);
+---------+----------+
| c_name  | minvalue |
+---------+----------+
| TAEKOOK |        1 |
+---------+----------+
1 row in set (0.00 sec)

mysql> SELECT `c_name`,`grade` FROM `wings`
    -> ORDER BY `grade` LIMIT 1;
+---------+-------+
| c_name  | grade |
+---------+-------+
| TAEKOOK |     1 |
+---------+-------+
1 row in set (0.00 sec)

select max语法

SELECT MAX(列名) FROM 表名

注：请慎用max()函数，特别是频繁执行的sql，若需用到可转化为测试中的 order by id desc limit 1，因为往往min()或者max()函数往往会造成全表扫描（网上看到的）

更新数据UPDATE

– 1) 找出love_yourself 表，sex为空的的数据；

– 2）更新love_yourself 表，把sex为空的，设置为0（性别未知）

– 3）找出wings表，grade小于2的同学

– 4）更新wings表，把grade小于2的同学，一律改为1

1）条件查找 2）更新表

mysql> SELECT * FROM love_yourself WHERE sex is NULL;
+----+-------+------+-------+------------+---------+-------+
| id | uname | sex  | birth | department | address | idoxu |
+----+-------+------+-------+------------+---------+-------+
|  1 | JIN   | NULL |  NULL | NULL       | NULL    | 2020  |
|  2 | SG    | NULL |  NULL | NULL       | NULL    | 2020  |
|  3 | JH    | NULL |  NULL | NULL       | NULL    | 2019  |
|  4 | RM    | NULL |  NULL | NULL       | NULL    | 2019  |
|  5 | JM    | NULL |  NULL | NULL       | NULL    | 2018  |
+----+-------+------+-------+------------+---------+-------+
5 rows in set (0.00 sec)

/UPDATE
mysql> UPDATE love_yourself SET sex = 0 WHERE sex is NULL;
Query OK, 5 rows affected (0.01 sec)
Rows matched: 5  Changed: 5  Warnings: 0

mysql> SELECT * FROM love_yourself;
+----+-------+------+-------+------------+---------+-------+
| id | uname | sex  | birth | department | address | idoxu |
+----+-------+------+-------+------------+---------+-------+
|  1 | JIN   | 0    |  NULL | NULL       | NULL    | 2020  |
|  2 | SG    | 0    |  NULL | NULL       | NULL    | 2020  |
|  3 | JH    | 0    |  NULL | NULL       | NULL    | 2019  |
|  4 | RM    | 0    |  NULL | NULL       | NULL    | 2019  |
|  5 | JM    | 0    |  NULL | NULL       | NULL    | 2018  |
|  6 | V     | 1    |  NULL | NULL       | NULL    | NULL  |
|  7 | JK    | 1    |  NULL | NULL       | NULL    | NULL  |
+----+-------+------+-------+------------+---------+-------+
7 rows in set (0.00 sec)

3）4）

mysql> SELECT * FROM wings WHERE grade < 2;
+----+--------+---------+----------+-------+
| id | stu_id | c_name  | istester | grade |
+----+--------+---------+----------+-------+
|  2 |     67 | TAEKOOK | NULL     |     1 |
+----+--------+---------+----------+-------+
1 row in set (0.00 sec)

mysql> UPDATE wings set grade = 0 where grade < 2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM wings;
+----+--------+---------+----------+-------+
| id | stu_id | c_name  | istester | grade |
+----+--------+---------+----------+-------+
|  1 |     95 | VMIN    | NULL     |     3 |
|  2 |     67 | TAEKOOK | NULL     |     0 |
|  3 |     57 | KM      | NULL     |     2 |
+----+--------+---------+----------+-------+
3 rows in set (0.00 sec)

UPDATE语法

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

可以同时更新多个字段
可以在 WHERE 子句中指定任何条件。
你可以在一个单独表中同时更新数据。

批量修改某个字段的某个字符串

UPDATE table_name SET field=REPLACE(field, 'old-string', 'new-string') 
[WHERE Clause]

查询排序SELECT…ORDER BY…DESC/ASC

– 1）查找love_yourself 表，按id降序

– 2）查找wings表，按grade升序

mysql> select * from love_yourself
    -> order by `id` desc;
+----+-------+------+-------+------------+---------+-------+
| id | uname | sex  | birth | department | address | idoxu |
+----+-------+------+-------+------------+---------+-------+
|  7 | JK    | 1    |  NULL | NULL       | NULL    | NULL  |
|  6 | V     | 1    |  NULL | NULL       | NULL    | NULL  |
|  5 | JM    | 0    |  NULL | NULL       | NULL    | 2018  |
|  4 | RM    | 0    |  NULL | NULL       | NULL    | 2019  |
|  3 | JH    | 0    |  NULL | NULL       | NULL    | 2019  |
|  2 | SG    | 0    |  NULL | NULL       | NULL    | 2020  |
|  1 | JIN   | 0    |  NULL | NULL       | NULL    | 2020  |
+----+-------+------+-------+------------+---------+-------+
7 rows in set (0.00 sec)

mysql> select * from wings
    -> order by grade asc;
+----+--------+---------+----------+-------+
| id | stu_id | c_name  | istester | grade |
+----+--------+---------+----------+-------+
|  2 |     67 | TAEKOOK | NULL     |     0 |
|  3 |     57 | KM      | NULL     |     2 |
|  1 |     95 | VMIN    | NULL     |     3 |
+----+--------+---------+----------+-------+
3 rows in set (0.00 sec)

查询记录COUNT()

1）查询love_yourself表，有多少条数据

2）查询love_yourself表，有几种性别类型（sex字段，去重）

COUNT函数

返回 Products 表中 products 字段总共有多少条记录：

SELECT COUNT(ProductID) AS NumberOfProducts FROM Products;

1）查询

mysql> select COUNT(id) from love_yourself;
+-----------+
| COUNT(id) |
+-----------+
|         7 |
+-----------+
1 row in set (0.01 sec)

去重1：DISTINCT

DISTINCT用法

count计算不重复的记录

2）sex字段，去重

mysql> select COUNT(distinct sex) from love_yourself;
+---------------------+
| COUNT(distinct sex) |
+---------------------+
|                   2 |
+---------------------+
1 row in set (0.00 sec)

返回记录不同的id的具体值

mysql> select distinct sex from love_yourself;
+------+
| sex  |
+------+
| 0    |
| 1    |
+------+
2 rows in set (0.00 sec)

需要返回2列以上记录用，直接distinct id1，id2会有歧义；一般用group by

查询内置数字函数

1）查找wings表，学生成绩(grade) 总分

2）查找wings表，学生成绩(grade) 平均分

MySQL内置函数及用法

mysql> select sum(grade) as sum_grade from wings;
+-----------+
| sum_grade |
+-----------+
|         5 |
+-----------+
1 row in set (0.00 sec)

mysql> select avg(grade) as sum_avg from wings;
+---------+
| sum_avg |
+---------+
|  1.6667 |
+---------+

别名关键字AS
as是别名关键字，换句话说就是重新给sql某个字段取个别名的关键字，但as本身并不改变sql的字段的名称，只是在使用的时候有时为了临时给某一字段重新命名而起的过渡作用，经常会用在多表某一字段名称不相同的情况下使用

SELECT 列名 FROM 表名 AS 别名;
SELECT 列名 AS 别名 FROM 表名;
SELECT 列名 AS 别名 FROM 表名 AS 别名;

查询区间

1）查找wings表，成绩在1 - 3区间的学生；

2）查找love_yourself 表，id 为 1，2 的数据；

mysql> select * from wings where grade between 1 and 3;
+----+--------+--------+----------+-------+
| id | stu_id | c_name | istester | grade |
+----+--------+--------+----------+-------+
|  1 |     95 | VMIN   | NULL     |     3 |
|  3 |     57 | KM     | NULL     |     2 |
+----+--------+--------+----------+-------+

mysql> select * from love_yourself where id in (1,2);
+----+-------+------+-------+------------+---------+-------+
| id | uname | sex  | birth | department | address | idoxu |
+----+-------+------+-------+------------+---------+-------+
|  1 | JIN   | 0    |  NULL | NULL       | NULL    | 2020  |
|  2 | SG    | 0    |  NULL | NULL       | NULL    | 2020  |
+----+-------+------+-------+------------+---------+-------+
2 rows in set (0.01 sec)

删除delete

1）删除 love_yourself 表，id大于1的数据；

2）删除wings表，分数grade小于1的数据；

mysql> select * from wings;
+----+--------+---------+----------+-------+
| id | stu_id | c_name  | istester | grade |
+----+--------+---------+----------+-------+
|  1 |     95 | VMIN    | NULL     |     3 |
|  2 |     67 | TAEKOOK | NULL     |     0 |
|  3 |     57 | KM      | NULL     |     2 |
+----+--------+---------+----------+-------+
3 rows in set (0.00 sec)

mysql> delete from wings where grade < 1;
Query OK, 1 row affected (0.01 sec)

mysql> select * from wings;
+----+--------+--------+----------+-------+
| id | stu_id | c_name | istester | grade |
+----+--------+--------+----------+-------+
|  1 |     95 | VMIN   | NULL     |     3 |
|  3 |     57 | KM     | NULL     |     2 |
+----+--------+--------+----------+-------+
2 rows in set (0.00 sec)

delete语句
用 SQL 的 DELETE FROM 命令来删除 MySQL 数据表中的记录。

DELETE FROM table_name [WHERE Clause]

elete，drop，truncate 都有删除表的作用，区别在于：

1、delete 和 truncate 仅仅删除表数据，drop 连表数据和表结构一起删除，打个比方，delete 是单杀，truncate 是团灭，drop 是把电脑摔了。
2、delete 是 DML 语句，操作完以后如果没有不想提交事务还可以回滚，truncate 和 drop 是 DDL 语句，操作完马上生效，不能回滚，打个比方，delete 是发微信说分手，后悔还可以撤回，truncate 和 drop 是直接扇耳光说滚，不能反悔。
3、执行的速度上，drop>truncate>delete，打个比方，drop 是神舟火箭，truncate 是和谐号动车，delete 是自行车。

数据插入insert into

1）造数据，把love_yourself表的所有数据，插入到 wings表

字段取值规则：
	id 取id；
	stu_id 取id
	c_name 取 uname
	istester 和 grade字段，给默认值 60

因为两个表有主键冲突（id值数据相同），直接insert into会报错。

mysql> insert into wings(id,stu_id,c_name,istester, grade)
    -> select id,id,uname,60,7 from love_yourself;
ERROR 1062 (23000): Duplicate entry '1' for key 'wings.PRIMARY'

字段冲突

方案一：ignore
插入时检索主键列表，如存在相同主键记录，不更改原纪录，只插入新的记录。

INSERT IGNORE INTO

方案二：replace
插入时如发现主键已存在，则替换原记录，即先删除原记录，后insert新记录。

REPLACE INTO

方案三：on duplicate key update
插入时如果发现主键已存在，则执行UPDATE更新操作

INSERT INTO ... ON DUPLICATE KEY UPDATE

1）ignore解决

mysql> insert IGNORE into wings(id,stu_id,c_name,istester, grade)
    -> select id,id,uname,60,7 from love_yourself;
Query OK, 5 rows affected, 2 warnings (0.01 sec)
Records: 7  Duplicates: 2  Warnings: 2

mysql> select * from wings;
+----+--------+--------+----------+-------+
| id | stu_id | c_name | istester | grade |
+----+--------+--------+----------+-------+
|  1 |     95 | VMIN   | NULL     |     3 |
|  2 |      2 | SG     | 60       |     7 |
|  3 |     57 | KM     | NULL     |     2 |
|  4 |      4 | RM     | 60       |     7 |
|  5 |      5 | JM     | 60       |     7 |
|  6 |      6 | V      | 60       |     7 |
|  7 |      7 | JK     | 60       |     7 |
+----+--------+--------+----------+-------+
7 rows in set (0.00 sec)

修改表ALTER

– 1）把wings表，改名为 yolo

– 2）检查是否修改成功

– 3）修改表yolo，把字段istester ，改为test（字符类型varchar，长度160 ）

– 4）检查是否修改成功

ALTER语法

ALTER TABLE <表名> [修改选项]

修改选项包括
{ ADD COLUMN <列名> <类型>
| CHANGE COLUMN <旧列名> <新列名> <新列类型>
| ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT }
| MODIFY COLUMN <列名> <类型>
| DROP COLUMN <列名>
| RENAME TO <新表名>
| CHARACTER SET <字符集名>
| COLLATE <校对规则名> }

ALTER修改表名

ALTER TABLE <旧表名> RENAME [TO] <新表名>；

1）2）修改表名，并查看

mysql> alter table wings rename to yolo;
Query OK, 0 rows affected (0.05 sec)

mysql> show tables;
+---------------+
| Tables_in_bts |
+---------------+
| love_yourself |
| yolo          |
+---------------+
2 rows in set (0.00 sec)

3）4）修改字段名，并查看

mysql> alter table yolo change istester test varchar(160);
Query OK, 7 rows affected (0.14 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> desc yolo;
+--------+--------------+------+-----+---------+----------------+
| Field  | Type         | Null | Key | Default | Extra          |
+--------+--------------+------+-----+---------+----------------+
| id     | int          | NO   | PRI | NULL    | auto_increment |
| stu_id | int          | NO   |     | NULL    |                |
| c_name | varchar(20)  | YES  |     | NULL    |                |
| test   | varchar(160) | YES  |     | NULL    |                |
| grade  | int          | YES  |     | NULL    |                |
+--------+--------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

创建数据表2

1）创建数据库 ynwl

2）检查数据库是否创建成功；

3）进入ynwl 库

4）在数据库 ynwl，创建wings表，直接拷贝 bts库yolo表的数据和结构；

5）检查表是否创建成功；

mysql> create database ynwl;
Query OK, 1 row affected (0.02 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| bts                |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| ynwl               |
+--------------------+
6 rows in set (0.00 sec)

mysql> use ynwl;
Database changed

mysql> create table wings as select * from bts.yolo;
Query OK, 7 rows affected (0.04 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> show tables;
+----------------+
| Tables_in_ynwl |
+----------------+
| wings          |
+----------------+
1 row in set (0.00 sec)

LIMIT查询

ynwl表，分数grade
1）排名前三的学生和分数；
2）排名3 - 6名的学生和分数；
3）排名6名以后的所有学生和分数；

mysql> select c_name,grade from wings
    -> order by grade desc limit 3;
+--------+-------+
| c_name | grade |
+--------+-------+
| JK     |     7 |
| SG     |     7 |
| JM     |     7 |
+--------+-------+
3 rows in set (0.00 sec)

mysql> select c_name,grade from wings
    -> order by grade desc limit 2,4;
+--------+-------+
| c_name | grade |
+--------+-------+
| JM     |     7 |
| V      |     7 |
| JK     |     7 |
| VMIN   |     3 |
+--------+-------+
4 rows in set (0.00 sec)

mysql> select c_name,grade from wings
    -> order by grade desc limit 5,666;
+--------+-------+
| c_name | grade |
+--------+-------+
| VMIN   |     3 |
| KM     |     2 |
+--------+-------+
2 rows in set (0.00 sec)

表连接JOIN

JOIN 按照功能大致分为如下三类：

INNER JOIN (内连接,或等值连接）：获取两个表中字段匹配关系的记录。
LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
RIGHT JOIN（右连接）：与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

原表

mysql> select * from yolo;
+----+--------+--------+------+-------+
| id | stu_id | c_name | test | grade |
+----+--------+--------+------+-------+
|  1 |     95 | VMIN   | NULL |     3 |
|  2 |      2 | SG     | 60   |     7 |
|  3 |     57 | KM     | NULL |     2 |
|  4 |      4 | RM     | 60   |     7 |
|  5 |      5 | JM     | 60   |     7 |
|  6 |      6 | V      | 60   |     7 |
|  7 |      7 | JK     | 60   |     7 |
+----+--------+--------+------+-------+
7 rows in set (0.00 sec)

mysql> select * from love_yourself;
+----+-------+------+-------+------------+---------+-------+
| id | uname | sex  | birth | department | address | idoxu |
+----+-------+------+-------+------------+---------+-------+
|  1 | JIN   | 0    |  NULL | NULL       | NULL    | 2020  |
|  2 | SG    | 0    |  NULL | NULL       | NULL    | 2020  |
|  3 | JH    | 0    |  NULL | NULL       | NULL    | 2019  |
|  4 | RM    | 0    |  NULL | NULL       | NULL    | 2019  |
|  5 | JM    | 0    |  NULL | NULL       | NULL    | 2018  |
|  6 | V     | 1    |  NULL | NULL       | NULL    | NULL  |
|  7 | JK    | 1    |  NULL | NULL       | NULL    | NULL  |
+----+-------+------+-------+------------+---------+-------+
7 rows in set (0.00 sec)

左连接left join

语法：

SELECT  * FROM a_table a left join b_table b ON a.a_id = b.b_id;

说明：

left join 是left outer join的简写，它的全称是左外连接，是外连接中的一种。
左(外)连接，左表(a_table)的记录将会全部表示出来，而右表(b_table)只会显示符合搜索条件的记录。
右表记录不足的地方均为NULL。

mysql> select * from yolo a LEFT JOIN love_yourself b ON a.stu_id=b.id;
+----+--------+--------+------+-------+------+-------+------+-------+------------+---------+-------+
| id | stu_id | c_name | test | grade | id   | uname | sex  | birth | department | address | idoxu |
+----+--------+--------+------+-------+------+-------+------+-------+------------+---------+-------+
|  1 |     95 | VMIN   | NULL |     3 | NULL | NULL  | NULL |  NULL | NULL       | NULL    | NULL  |
|  2 |      2 | SG     | 60   |     7 |    2 | SG    | 0    |  NULL | NULL       | NULL    | 2020  |
|  3 |     57 | KM     | NULL |     2 | NULL | NULL  | NULL |  NULL | NULL       | NULL    | NULL  |
|  4 |      4 | RM     | 60   |     7 |    4 | RM    | 0    |  NULL | NULL       | NULL    | 2019  |
|  5 |      5 | JM     | 60   |     7 |    5 | JM    | 0    |  NULL | NULL       | NULL    | 2018  |
|  6 |      6 | V      | 60   |     7 |    6 | V     | 1    |  NULL | NULL       | NULL    | NULL  |
|  7 |      7 | JK     | 60   |     7 |    7 | JK    | 1    |  NULL | NULL       | NULL    | NULL  |
+----+--------+--------+------+-------+------+-------+------+-------+------------+---------+-------+
7 rows in set (0.00 sec)

当使用的字段重名时，会报错。这是应该在相同的字段名前面要加上表名.字段名。

mysql> SELECT id,stu_id,c_name from yolo a left join love_yourself b on a.id = b.id;
ERROR 1052 (23000): Column 'id' in field list is ambiguous

mysql> SELECT yolo.id,stu_id,c_name from yolo left join love_yourself on yolo.id = love_yourself.id;
+----+--------+--------+
| id | stu_id | c_name |
+----+--------+--------+
|  1 |     95 | VMIN   |
|  2 |      2 | SG     |
|  3 |     57 | KM     |
|  4 |      4 | RM     |
|  5 |      5 | JM     |
|  6 |      6 | V      |
|  7 |      7 | JK     |
+----+--------+--------+
7 rows in set (0.00 sec)

右连接right join

语法：

SELECT  * FROM a_table a right [outer] join b_table b on a.a_id = b.b_id;

说明：

right join是right outer join的简写，它的全称是右外连接，是外连接中的一种。
与左(外)连接相反，右(外)连接，左表(a_table)只会显示符合搜索条件的记录，而右表(b_table)的记录将会全部表示出来。
左表记录不足的地方均为NULL。

mysql> select * from yolo a right join love_yourself b on a.stu_id = b.id;
+------+--------+--------+------+-------+----+-------+------+-------+------------+---------+-------+
| id   | stu_id | c_name | test | grade | id | uname | sex  | birth | department | address | idoxu |
+------+--------+--------+------+-------+----+-------+------+-------+------------+---------+-------+
| NULL |   NULL | NULL   | NULL |  NULL |  1 | JIN   | 0    |  NULL | NULL       | NULL    | 2020  |
|    2 |      2 | SG     | 60   |     7 |  2 | SG    | 0    |  NULL | NULL       | NULL    | 2020  |
| NULL |   NULL | NULL   | NULL |  NULL |  3 | JH    | 0    |  NULL | NULL       | NULL    | 2019  |
|    4 |      4 | RM     | 60   |     7 |  4 | RM    | 0    |  NULL | NULL       | NULL    | 2019  |
|    5 |      5 | JM     | 60   |     7 |  5 | JM    | 0    |  NULL | NULL       | NULL    | 2018  |
|    6 |      6 | V      | 60   |     7 |  6 | V     | 1    |  NULL | NULL       | NULL    | NULL  |
|    7 |      7 | JK     | 60   |     7 |  7 | JK    | 1    |  NULL | NULL       | NULL    | NULL  |
+------+--------+--------+------+-------+----+-------+------+-------+------------+---------+-------+
7 rows in set (0.00 sec)

内连接 inner join

语法：

select * from a_table a inner join b_table b on a.a_id = b.b_id;

说明：组合两个表中的记录，返回关联字段相符的记录，也就是返回两个表的交集（阴影）部分。
内连接

mysql> select * from yolo a inner join love_yourself b on a.stu_id = b.id;
+----+--------+--------+------+-------+----+-------+------+-------+------------+---------+-------+
| id | stu_id | c_name | test | grade | id | uname | sex  | birth | department | address | idoxu |
+----+--------+--------+------+-------+----+-------+------+-------+------------+---------+-------+
|  2 |      2 | SG     | 60   |     7 |  2 | SG    | 0    |  NULL | NULL       | NULL    | 2020  |
|  4 |      4 | RM     | 60   |     7 |  4 | RM    | 0    |  NULL | NULL       | NULL    | 2019  |
|  5 |      5 | JM     | 60   |     7 |  5 | JM    | 0    |  NULL | NULL       | NULL    | 2018  |
|  6 |      6 | V      | 60   |     7 |  6 | V     | 1    |  NULL | NULL       | NULL    | NULL  |
|  7 |      7 | JK     | 60   |     7 |  7 | JK    | 1    |  NULL | NULL       | NULL    | NULL  |
+----+--------+--------+------+-------+----+-------+------+-------+------------+---------+-------+
5 rows in set (0.00 sec)

全连接 union

语法：

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

说明：MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。

union语句注意事项：

     1.通过union连接的SQL它们分别单独取出的列数必须相同；

     2.不要求合并的表列名称相同时，以第一个sql 表列名为准；

     3.使用union 时，完全相等的行，将会被合并，由于合并比较耗时，一般不直接使用 union 进行合并，而是通常采用union all 进行合并；

     4.被union 连接的sql 子句，单个子句中不用写order by ，因为不会有排序的效果。但可以对最终的结果集进行排序；

mysql> select c_name from yolo
    -> union
    -> select uname from love_yourself;
+--------+
| c_name |
+--------+
| VMIN   |
| SG     |
| KM     |
| RM     |
| JM     |
| V      |
| JK     |
| JIN    |
| JH     |
+--------+
9 rows in set (0.00 sec)

全连接 union all

union时不去重用union all

mysql> select c_name from yolo
    -> union all
    -> select uname from love_yourself;
+--------+
| c_name |
+--------+
| VMIN   |
| SG     |
| KM     |
| RM     |
| JM     |
| V      |
| JK     |
| JIN    |
| SG     |
| JH     |
| RM     |
| JM     |
| V      |
| JK     |
+--------+
14 rows in set (0.00 sec)