MySQL基础---DML的基本操作

 1、启动虚拟机并登录MySQL，并查看数据库

[root@localhost ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 24
Server version: 5.7.40 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| kb21               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

2、创建myschool database 并进入myschool

mysql> create database myschool;
Query OK, 1 row affected (0.00 sec)

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

mysql> use myschool;
Database changed

3、创建grade表

mysql> create table if not exists `grade`(gradeid int(10) primary key auto_increment, subjectno varchar(50),gradename varchar(50));
Query OK, 0 rows affected (0.09 sec)

mysql> desc grade;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| gradeid   | int(10)     | NO   | PRI | NULL    | auto_increment |
| subjectno | varchar(50) | YES  |     | NULL    |                |
| gradename | varchar(50) | YES  |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

4、创建student表

mysql> create table student(StudentNo int(10) primary key auto_increment,LoginPwd varchar(20) ,StudentName varnyint(1),GradeID int (10),phone varchar(50),address varchar(255),borndate datetime,email varchar(50),IdentityC,constraint FK_GradeId foreign key (GradeID) references grade(GradeID));
Query OK, 0 rows affected (0.06 sec)

mysql> desc student;
+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| StudentNo    | int(10)      | NO   | PRI | NULL    | auto_increment |
| LoginPwd     | varchar(20)  | YES  |     | NULL    |                |
| StudentName  | varchar(20)  | YES  |     | NULL    |                |
| sex          | tinyint(1)   | YES  |     | NULL    |                |
| GradeID      | int(10)      | YES  | MUL | NULL    |                |
| phone        | varchar(50)  | YES  |     | NULL    |                |
| address      | varchar(255) | YES  |     | NULL    |                |
| borndate     | datetime     | YES  |     | NULL    |                |
| email        | varchar(50)  | YES  |     | NULL    |                |
| IdentityCard | varchar(18)  | YES  |     | NULL    |                |
+--------------+--------------+------+-----+---------+----------------+
10 rows in set (0.00 sec)

5、向grade表中插入数据

mysql> insert into grade(gradename) values("one"),("two"),("three"),("four");
Query OK, 4 rows affected (0.02 sec)

6、创建subject表并关联

mysql> create TABLE `subject` ( subjectno int PRIMARY key auto_increment, subjectname varchar(50), classhour i int(10)  );
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
+--------------------+
| Tables_in_myschool |
+--------------------+
| grade              |
| student            |
| subject            |
+--------------------+
3 rows in set (0.00 sec)

mysql> alter table subject add constraint FK_gradeid2 foreign key(gradeid) references grade(gradeid);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

7、创建result表

mysql> create table if not exists `result`
    -> (
    -> studentno int(10),
    -> subjectno int(10),
    -> examdate datetime,
    -> studentresult int(10)
    -> );

8、向student表中插入数据

mysql> insert into student(studentno,studentname,sex,gradeid,borndate,identitycard)
    -> values(1000,'zhangsan',1,1,now(),'511133199907140613'),
    -> (1001,'lisi',0,2,now(),'511133199907140614'),
    -> (1002,'wangwu',1,3,now(),'511133199907140615');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0


mysql> select * from student;
+-----------+----------+-------------+------+---------+-------+---------+---------------------+-------+-------
| studentno | loginpwd | studentname | sex  | gradeid | phone | address | borndate            | email | identi
+-----------+----------+-------------+------+---------+-------+---------+---------------------+-------+-------
|      1000 | NULL     | zhangsan    |    1 |       1 | NULL  | NULL    | 2022-12-05 10:54:49 | NULL  | 511133
|      1001 | NULL     | lisi        |    0 |       2 | NULL  | NULL    | 2022-12-05 10:54:49 | NULL  | 511133
|      1002 | NULL     | wangwu      |    1 |       3 | NULL  | NULL    | 2022-12-05 10:54:49 | NULL  | 511133
+-----------+----------+-------------+------+---------+-------+---------+---------------------+-------+-------
3 rows in set (0.01 sec)

9、向subject表中插入数据

mysql> insert into subject(subjectno,subjectname,classhour,gradeid) value(1,'MATH1',40,1),(2,'MATH2',38,2),(3,8,3),(4,'Hadoop',44,4);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from subject;
+-----------+-------------+-----------+---------+
| subjectno | subjectname | classhour | gradeid |
+-----------+-------------+-----------+---------+
|         1 | MATH1       |        40 |       1 |
|         2 | MATH2       |        38 |       2 |
|         3 | JavaProject |        38 |       3 |
|         4 | Hadoop      |        44 |       4 |
+-----------+-------------+-----------+---------+
4 rows in set (0.00 sec)

10、update更新grade表

mysql> update grade set subjectno=1 where gradeid=1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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


mysql> update grade set subjectno=3 where gradeid=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0


mysql> update grade set subjectno=4 where gradeid=4;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from grade;
+---------+-----------+-----------+
| gradeid | subjectno | gradename |
+---------+-----------+-----------+
|       1 |         1 | one       |
|       2 |         2 | two       |
|       3 |         3 | three     |
|       4 |         4 | four      |
+---------+-----------+-----------+
4 rows in set (0.00 sec)