【Mysql basic commands/query: how to update the message/record data of each row】

Dyan GONG

已于 2024-03-16 11:22:46 修改

阅读量748

点赞数 6

分类专栏： DataBase - SQL 文章标签： mysql adb android

于 2023-12-21 00:15:15 首次发布

本文链接：https://blog.csdn.net/DavidsonGong/article/details/135120437

版权

DataBase - SQL 专栏收录该内容

11 篇文章 1 订阅

订阅专栏

mysql common commands

I) desc, insert and update

I) desc, insert and update

desc = describe,
insert into subject values(null,“Geometry Politics”, 99);

mysql> select * from subject;
+------------+----------------------+---------------+
| subject_id | subject_name         | subject_hours |
+------------+----------------------+---------------+
|          1 | Communication skills |           100 |
+------------+----------------------+---------------+
1 row in set (0.00 sec)

mysql> desc subject;
+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| subject_id    | int(11)     | YES  |     | NULL    |       |
| subject_name  | varchar(20) | YES  |     | NULL    |       |
| subject_hours | int(11)     | YES  |     | NULL    |       |
+---------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into subject values(null,"Geometry Politics", 99);
Query OK, 1 row affected (0.00 sec)

mysql> select * from subject;
+------------+----------------------+---------------+
| subject_id | subject_name         | subject_hours |
+------------+----------------------+---------------+
|          1 | Communication skills |           100 |
|       NULL | Geometry Politics    |            99 |
+------------+----------------------+---------------+
2 rows in set (0.00 sec)

mysql> update subject set subject_id = 3 where subject_hours = 99
    -> ;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from subject;
+------------+----------------------+---------------+
| subject_id | subject_name         | subject_hours |
+------------+----------------------+---------------+
|          1 | Communication skills |           100 |
|          3 | Geometry Politics    |            99 |
+------------+----------------------+---------------+
2 rows in set (0.00 sec)

mysql> describ subject;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'describ subject' at line 1
mysql> describe subject;
+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| subject_id    | int(11)     | YES  |     | NULL    |       |
| subject_name  | varchar(20) | YES  |     | NULL    |       |
| subject_hours | int(11)     | YES  |     | NULL    |       |
+---------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

II) how to use constrain UNIQUE in Mysql

only add/create one UNIQUE constrain

mysql> CREATE TABLE Persons (
    ->     ID int NOT NULL,
    ->     LastName varchar(255) NOT NULL,
    ->     FirstName varchar(255),
    ->     Age int,
    ->     UNIQUE (ID)
    -> ); 
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO Persons VALUES (1,"King","Larry",39);
Query OK, 1 row affected (0.01 sec)

mysql> desc Persons;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| ID        | int(11)      | NO   | PRI | NULL    |       |
| LastName  | varchar(255) | NO   |     | NULL    |       |
| FirstName | varchar(255) | YES  |     | NULL    |       |
| Age       | int(11)      | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> select ID from Persons;
+----+
| ID |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

mysql> select * from Persons;
+----+----------+-----------+------+
| ID | LastName | FirstName | Age  |
+----+----------+-----------+------+
|  1 | King     | Larry     |   39 |
+----+----------+-----------+------+
1 row in set (0.00 sec)

mysql> INSERT INTO Persons VALUES (1,"Keril","Lam",29);
ERROR 1062 (23000): Duplicate entry '1' for key 'ID'

add/create more than one UNIQUE constrains, and also drop the UNIUE constrain

mysql> select * from Persons;

+----+----------+-----------+------+

| ID | LastName | FirstName | Age  |

+----+----------+-----------+------+

|  1 | King     | Larry     |   39 |

|  2 | Keril    | Sam       |   10 |

|  3 | Keril    | Jam       |   19 |

+----+----------+-----------+------+

3 rows in set (0.00 sec)



mysql> ALTER TABLE Persons

    -> DROP INDEX UC_Person; 

Query OK, 0 rows affected (0.01 sec)

Records: 0  Duplicates: 0  Warnings: 0



mysql> DESC Persons;

+-----------+--------------+------+-----+---------+-------+

| Field     | Type         | Null | Key | Default | Extra |

+-----------+--------------+------+-----+---------+-------+

| ID        | int(11)      | NO   | PRI | NULL    |       |

| LastName  | varchar(255) | NO   |     | NULL    |       |

| FirstName | varchar(255) | YES  |     | NULL    |       |

| Age       | int(11)      | YES  |     | NULL    |       |

+-----------+--------------+------+-----+---------+-------+

4 rows in set (0.00 sec)



mysql> ALTER TABLE Persons

    -> DROP INDEX ID; 

Query OK, 3 rows affected (0.03 sec)

Records: 3  Duplicates: 0  Warnings: 0



mysql> select * from Persons;

+----+----------+-----------+------+

| ID | LastName | FirstName | Age  |

+----+----------+-----------+------+

|  1 | King     | Larry     |   39 |

|  2 | Keril    | Sam       |   10 |

|  3 | Keril    | Jam       |   19 |

+----+----------+-----------+------+

3 rows in set (0.00 sec)



mysql> INSERT INTO Persons VALUES (3,"Keril","Jam",19);

Query OK, 1 row affected (0.00 sec)



mysql> select * from Persons;

+----+----------+-----------+------+

| ID | LastName | FirstName | Age  |

+----+----------+-----------+------+

|  1 | King     | Larry     |   39 |

|  2 | Keril    | Sam       |   10 |

|  3 | Keril    | Jam       |   19 |

|  3 | Keril    | Jam       |   19 |

+----+----------+-----------+------+

4 rows in set (0.00 sec)



mysql> ALTER TABLE Persons

    -> ADD CONSTRAINT UC_Person UNIQUE (ID,LastName); 

ERROR 1062 (23000): Duplicate entry '3-Keril' for key 'UC_Person'

mysql> Delete FROM Persons where age = 19;

Query OK, 2 rows affected (0.00 sec)



mysql> select * from Persons;

+----+----------+-----------+------+

| ID | LastName | FirstName | Age  |

+----+----------+-----------+------+

|  1 | King     | Larry     |   39 |

|  2 | Keril    | Sam       |   10 |

+----+----------+-----------+------+

2 rows in set (0.00 sec)



mysql> DESC Persons;

+-----------+--------------+------+-----+---------+-------+

| Field     | Type         | Null | Key | Default | Extra |

+-----------+--------------+------+-----+---------+-------+

| ID        | int(11)      | NO   |     | NULL    |       |

| LastName  | varchar(255) | NO   |     | NULL    |       |

| FirstName | varchar(255) | YES  |     | NULL    |       |

| Age       | int(11)      | YES  |     | NULL    |       |

+-----------+--------------+------+-----+---------+-------+

4 rows in set (0.00 sec)



mysql> ALTER TABLE Persons

    -> ADD CONSTRAINT UC_Person UNIQUE (ID,LastName); 

Query OK, 0 rows affected (0.04 sec)

Records: 0  Duplicates: 0  Warnings: 0



mysql> INSERT INTO Persons VALUES (3,"Keril","Jam",19);

Query OK, 1 row affected (0.00 sec)



mysql> INSERT INTO Persons VALUES (3,"Keril","Jam",19);

ERROR 1062 (23000): Duplicate entry '3-Keril' for key 'UC_Person'

mysql> select * from Persons;

+----+----------+-----------+------+

| ID | LastName | FirstName | Age  |

+----+----------+-----------+------+

|  1 | King     | Larry     |   39 |

|  2 | Keril    | Sam       |   10 |

|  3 | Keril    | Jam       |   19 |

+----+----------+-----------+------+

3 rows in set (0.00 sec)



mysql> INSERT INTO Persons VALUES (4,"Keril","Jam",19);

Query OK, 1 row affected (0.00 sec)



mysql> select * from Persons;

+----+----------+-----------+------+

| ID | LastName | FirstName | Age  |

+----+----------+-----------+------+

|  1 | King     | Larry     |   39 |

|  2 | Keril    | Sam       |   10 |

|  3 | Keril    | Jam       |   19 |

|  4 | Keril    | Jam       |   19 |

+----+----------+-----------+------+

4 rows in set (0.00 sec)



mysql> INSERT INTO Persons VALUES (4,"Keril","Joe",29);

ERROR 1062 (23000): Duplicate entry '4-Keril' for key 'UC_Person'

Dyan GONG

关注

6
点赞
踩
7

收藏

觉得还不错? 一键收藏
0
评论
【Mysql basic commands/query: how to update the message/record data of each row】

【代码】【Mysql basic commands/query: how to update the message/record data of each row】
复制链接

扫一扫

专栏目录