mysql基本シナリオ列操作

文書ディレクトリ

mysqlサービス

を構築

名前のデータベースを作成し、テーブル

を作成します.

新しいテーブルの内容の有無(select文で)

を確認します.

新しいstudentテーブルにデータを挿入する(insert文

を用いる.

andyを修正する年齢は50

です.

ageフィールド降順で

並べ替え

studentテーブルで最年少の3人の同級生

を検索

studentテーブルで最も年上の4人の同級生

を検索

クエリーstudentテーブルのtomという名前のレコード

クエリーstudentテーブルの名前tomと19歳以上のレコード

クエリーstudentテーブルの年齢が20～50のレコード

natashaを修正する年齢は100

である.

削除studentの名前はzhangshanで、年齢が20以下の

です.
mysqlサービスの構築

[root@mysql ~]# systemctl stop firewalld
[root@mysql ~]# systemctl disable firewalld
[root@mysql ~]# getenforce
Disabled
[root@mysql ~]# wget http://dev.mysql.com/get/mysql57‐community‐release‐e
l7‐10.noarch.rpm
[root@mysql ~]# yum ‐y install mysql57‐community‐release‐el7‐10.noarch.rp
m
[root@mysql ~]# yum ‐y install mysql‐community‐server mysql‐community‐cli
ent mysql‐community‐common mysql‐community‐devel
[root@mysql ~]# systemctl start mysqld
[root@mysql ~]# ss ‐antl
State Recv‐Q Send‐Q Local Address:Port Peer Address:Port
 LISTEN 0 128 *:22 *:*
 LISTEN 0 100 127.0.0.1:25 *:*
 LISTEN 0 128 :::22 :::*
 LISTEN 0 100 ::1:25 :::*
 LISTEN 0 80 :::3306 :::*
 

あなたの名前のデータベースを作成し、テーブルを作成します.

student，        （id，name，age），  
   ：
 [root@mysql ~]# mysql ‐uroot ‐pwyh123!
 mysql> create database wyh;
 mysql> use wyh;
 mysql> create table student(id int NOT NULL,name VARCHAR(100) NOT NULL,ag
e tinyint NULL);
 mysql> desc wyh.student;
 +‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+
 | Field | Type | Null | Key | Default | Extra |
 +‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+
 | id | int(11) | NO | | NULL | |
 | name | varchar(100) | NO | | NULL | |
 | age | tinyint(4) | YES | | NULL | |
 +‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+

新しいテーブルの内容の有無を確認します(select文で)

 mysql> select * from student;
 Empty set (0.00 sec)

新しいstudentテーブルにデータを挿入する(insert文で

 mysql> insert into student (id,name,age) values (1,'tom',20),
(2,'natasha',17),(3,'harry',15),(4,'andy',20),(5,'jerry',19),(6,'jack',21);
Query OK, 6 rows affected (0.00 sec)
 Records: 6 Duplicates: 0 Warnings: 0

 mysql> select * from student;
+‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+
 | id | name | age |
 +‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+
 | 1 | tom | 20 |
 | 2 | natasha | 17 |
 | 3 | harry | 15 |
 | 4 | andy | 20 |
 | 5 | jerry | 19 |
 | 6 | jack | 21 |
 +‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+
6 rows in set (0.00 sec)

andyを修正する年齢は50です

 mysql> update student set age = 50 where name = 'andy';
 Query OK, 1 row affected (0.00 sec)
 Rows matched: 1 Changed: 1 Warnings: 0

 mysql> select * from student;
 +‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+
 | id | name | age |
+‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+
 | 1 | tom | 20 |
 | 2 | natasha | 17 |
 | 3 | harry | 15 |
 | 4 | andy | 50 |
 | 5 | jerry | 19 |
 | 6 | jack | 21 |
 +‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+
 6 rows in set (0.00 sec)

ageフィールド降順でソート

mysql> select * from student order by age desc;
 +‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+
 | id | name | age |
 +‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+
 | 4 | andy | 50 |
 | 6 | jack | 21 |
 | 1 | tom | 20 |
 | 5 | jerry | 19 |
 | 2 | natasha | 17 |
 | 3 | harry | 15 |
 +‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+
 6 rows in set (0.00 sec)

studentテーブルの中で一番年下の3人の同級生を検索します

mysql> select * from student order by age limit 3;
+‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+
| id | name | age |
+‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+
| 3 | harry | 15 |
| 2 | natasha | 17 |
| 5 | jerry | 19 |
+‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+
3 rows in set (0.00 sec)

studentテーブルの中で最も年上の4人の同級生を検索します

mysql> select * from student order by age desc limit 4;
+‐‐‐‐+‐‐‐‐‐‐‐+‐‐‐‐‐‐+
| id | name | age |
+‐‐‐‐+‐‐‐‐‐‐‐+‐‐‐‐‐‐+
| 4 | andy | 50 |
| 6 | jack | 21 |
| 1 | tom | 20 |
| 5 | jerry | 19 |
+‐‐‐‐+‐‐‐‐‐‐‐+‐‐‐‐‐‐+
4 rows in set (0.00 sec)

クエリーstudentテーブルのtomという名前のレコード

 mysql> select * from student where name = 'tom';
 +‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐+
 | id | name | age |
 +‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐+
 | 1 | tom | 20 |
 +‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐+
 1 row in set (0.00 sec)

クエリーstudentテーブルの名前tomと19歳以上のレコード

 mysql> select * from student where name = 'tom' and age > 19;
 +‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐+
 | id | name | age |
 +‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐+
 | 1 | tom | 20 |
 +‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐+
1 row in set (0.00 sec)

クエリーstudentテーブルの年齢が20～50のレコード

 mysql> select * from student where age between 20 and 50;
 +‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐+
 | id | name | age |
 +‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐+
 | 1 | tom | 20 |
 | 4 | andy | 50 |
 | 6 | jack | 21 |
 +‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐+
 3 rows in set (0.00 sec)

natashaの年齢を100に変更

 mysql> update student set age = 100 where name = 'natasha';
  Query OK, 1 row affected (0.00 sec)
 Rows matched: 1 Changed: 1 Warnings: 0

 mysql> select * from student;
 +‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+
 | id | name | age |
 +‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+
 | 1 | tom | 20 |
 | 2 | natasha | 100 |
 | 3 | harry | 15 |
 | 4 | andy | 50 |
| 5 | jerry | 19 |
 | 6 | jack | 21 |
 +‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+
 6 rows in set (0.00 sec)

削除studentの名前はzhangshanで、年齢が20以下の

  
mysql> delete from student where name = 'tom' and age <= 20;
Query OK, 1 row affected (0.00 sec)

 mysql> select * from student;
 +‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+
 | id | name | age |
 +‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+
| 2 | natasha | 100 |
 | 3 | harry | 15 |
| 4 | andy | 50 |
| 5 | jerry | 19 |
 | 6 | jack | 21 |
 +‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+
 5 rows in set (0.00 sec)