美文网首页数据库
数据库表的操作实例

数据库表的操作实例

作者: 叫我老村长 | 来源:发表于2018-08-08 23:58 被阅读56次
    about-BY-gentle.jpg 
    mysql> CREATE IF NOT EXISTS gradeinfo(
    -> 

^C
mysql> CREATE IF NOT EXISTS gradeinfo charset=utf8(
    -> student_id int NOT NULL AUTO_INCREMENT,
    -> student_name char(25) NOT NULL,
    -> student_class char(25) NOT NULL,
    -> math int NOT NULL default 0,
    -> phy int NOT NULL default 0,
    -> lan int NOT NULL default 0,
    -> eng int NOT NULL default 0,
    -> bjgnum int NOT NULL default 0,
    -> gendent int NOT NULL default 0,
    -> toscore int NOT NULL default 0,
    -> PRIMARY KEY(student_id)
    -> );
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 'IF NOT EXISTS gradeinfo charset=utf8(
student_id int NOT NULL AUTO_INCREMENT,
st' at line 1
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| 这种bc             |
| bc                 |
| class1804          |
| class18042         |
| class1804h         |
| class1804n         |
| class1804p         |
| class222           |
| gebilaowang        |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
13 rows in set (0.00 sec)

mysql> ALTER DATABASE gebilaowang charset=utf8;
Query OK, 1 row affected (0.00 sec)

mysql> create databse gradeinfo DEFAULT charset=utf8;
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 'databse gradeinfo DEFAULT charset=utf8' at line 1
mysql> create database gradeinfo DEFAULT charset=utf8;
Query OK, 1 row affected (0.00 sec)

mysql> show dabases;
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 'dabases' at line 1
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| 这种bc             |
| bc                 |
| class1804          |
| class18042         |
| class1804h         |
| class1804n         |
| class1804p         |
| class222           |
| gebilaowang        |
| gradeinfo          |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
14 rows in set (0.00 sec)

mysql> use gradeinfo;
Database changed
mysql> show variables like 'character_set_database';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| character_set_database | utf8  |
+------------------------+-------+
1 row in set (0.02 sec)

mysql> creata table student(
    -> CREATE IF NOT EXISTS gradeinfo charset=utf8( student_id int NOT NULL AUTO_INCREMENT, student_name char(25) NOT NULL,  student_class char(25) NOT NULL, math int NOT NULL default 0, phy int NOT NULL default 0, lan int NOT NULL default 0, eng int NOT NULL default 0, bjgnum int NOT NULL default 0, gendent int NOT NULL default 0, toscore int NOT NULL default 0, PRIMARY KEY(student_id) );

^C
mysql> CREATE TABLE IF NOT EXISTS student( student_id int NOT NULL AUTO_INCREMENT, student_name char(25) NOT NULL,  student_class char(25) NOT NULL, math int NOT NULL default 0, phy int NOT NULL default 0, lan int NOT NULL default 0, eng int NOT NULL default 0, bjgnum int NOT NULL                                      Y(student_id) );
Query OK, 0 rows affected (0.41 sec)

mysql> show * from student;
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 '* from student' at line 1
mysql> SELECT * FROM student;
Empty set (0.00 sec)

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

mysql> SELECT * FROM student;
Empty set (0.00 sec)

mysql> DESC student;
+---------------+----------+------+-----+---------+----------------+
| Field         | Type     | Null | Key | Default | Extra          |
+---------------+----------+------+-----+---------+----------------+
| student_id    | int(11)  | NO   | PRI | NULL    | auto_increment |
| student_name  | char(25) | NO   |     | NULL    |                |
| student_class | char(25) | NO   |     | NULL    |                |
| math          | int(11)  | NO   |     | 0       |                |
| phy           | int(11)  | NO   |     | 0       |                |
| lan           | int(11)  | NO   |     | 0       |                |
| eng           | int(11)  | NO   |     | 0       |                |
| bjgnum        | int(11)  | NO   |     | 0       |                |
| gendent       | int(11)  | NO   |     | 0       |                |
| toscore       | int(11)  | NO   |     | 0       |                |
+---------------+----------+------+-----+---------+----------------+
10 rows in set (0.00 sec)

mysql> INSERT INTO student values(
    -> 180401,'王1',1804,77,88,99,100,0,1,300);
Query OK, 1 row affected (0.07 sec)

mysql> SELECT * FROM student;
+------------+--------------+---------------+------+-----+-----+-----+--------+---------+---------+
| student_id | student_name | student_class | math | phy | lan | eng | bjgnum | gendent | toscore |
+------------+--------------+---------------+------+-----+-----+-----+--------+---------+---------+
|     180401 | 王1          | 1804          |   77 |  88 |  99 | 100 |      0 |       1 |     300 |
+------------+--------------+---------------+------+-----+-----+-----+--------+---------+---------+
1 row in set (0.00 sec)

mysql> INSERT INTO student(student_name,student_class)
    -> VALUES('王2',1804);
Query OK, 1 row affected (0.06 sec)

mysql> SELECT * FROM student;
+------------+--------------+---------------+------+-----+-----+-----+--------+---------+---------+
| student_id | student_name | student_class | math | phy | lan | eng | bjgnum | gendent | toscore |
+------------+--------------+---------------+------+-----+-----+-----+--------+---------+---------+
|     180401 | 王1          | 1804          |   77 |  88 |  99 | 100 |      0 |       1 |     300 |
|     180402 | 王2          | 1804          |    0 |   0 |   0 |   0 |      0 |       0 |       0 |
+------------+--------------+---------------+------+-----+-----+-----+--------+---------+---------+
2 rows in set (0.00 sec)

mysql> SELECT age FROM student;
ERROR 1054 (42S22): Unknown column 'age' in 'field list'
mysql> SELECT lan FROM student;
+-----+
| lan |
+-----+
|  99 |
|   0 |
+-----+
2 rows in set (0.00 sec)

mysql> INSERT INTO student values('王3',1804,33,77,88,99,1,0,300);
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> INSERT INTO student values('王3',1804,33,77,88,99,1,0,300);
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> INSERT INTO student values(180403,'王3',1804,33,77,88,99,1,0,300);
Query OK, 1 row affected (0.07 sec)

mysql> select * from student;
+------------+--------------+---------------+------+-----+-----+-----+--------+---------+---------+
| student_id | student_name | student_class | math | phy | lan | eng | bjgnum | gendent | toscore |
+------------+--------------+---------------+------+-----+-----+-----+--------+---------+---------+
|     180401 | 王1          | 1804          |   77 |  88 |  99 | 100 |      0 |       1 |     300 |
|     180402 | 王2          | 1804          |    0 |   0 |   0 |   0 |      0 |       0 |       0 |
|     180403 | 王3          | 1804          |   33 |  77 |  88 |  99 |      1 |       0 |     300 |
+------------+--------------+---------------+------+-----+-----+-----+--------+---------+---------+
3 rows in set (0.00 sec)

mysql> INSERT INTO student(student_name,student_class,math,eng,bjgnum,gendent,toscore)VALUES(张5，1804,66,99,2,1,278)；
    -> 

^C
mysql> INSERT INTO student(student_name,student_class,math,eng,bjgnum,gendent,toscore)VALUES('张5'，1804,66,99,2,1,278);
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 '，1804,66,99,2,1,278)' at line 1
mysql> INSERT INTO student(student_name,student_class,math,eng,bjgnum,gendent,toscore)VALUES('张5',1804,66,99,2,1,278);
Query OK, 1 row affected (0.06 sec)

mysql> select * from student;
+------------+--------------+---------------+------+-----+-----+-----+--------+---------+---------+
| student_id | student_name | student_class | math | phy | lan | eng | bjgnum | gendent | toscore |
+------------+--------------+---------------+------+-----+-----+-----+--------+---------+---------+
|     180401 | 王1          | 1804          |   77 |  88 |  99 | 100 |      0 |       1 |     300 |
|     180402 | 王2          | 1804          |    0 |   0 |   0 |   0 |      0 |       0 |       0 |
|     180403 | 王3          | 1804          |   33 |  77 |  88 |  99 |      1 |       0 |     300 |
|     180404 | 张5          | 1804          |   66 |   0 |   0 |  99 |      2 |       1 |     278 |
+------------+--------------+---------------+------+-----+-----+-----+--------+---------+---------+
4 rows in set (0.00 sec)

mysql> INSERT INTO student(student_name,student_class,math,phy,lan,eng,bjgnum,gendent,toscore)values('刘红',1804,23,66,88,99,1,1,389),('刘至',1804,23,66,55,99,1,0,289),('魏国',1804,63,66,88,99,1,1,369),('李四',1804,93,96,88,99,0,0,389),('王武',1804,73,96,88,99,1,0,389),('李祯',1804,94,76,98,99,1,0,289),('李中',1804,20,67,88,99,1,1,389);
Query OK, 7 rows affected (0.05 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> select * from sudent;
ERROR 1146 (42S02): Table 'gradeinfo.sudent' doesn't exist
mysql> select * from student;
+------------+--------------+---------------+------+-----+-----+-----+--------+---------+---------+
| student_id | student_name | student_class | math | phy | lan | eng | bjgnum | gendent | toscore |
+------------+--------------+---------------+------+-----+-----+-----+--------+---------+---------+
|     180401 | 王1          | 1804          |   77 |  88 |  99 | 100 |      0 |       1 |     300 |
|     180402 | 王2          | 1804          |    0 |   0 |   0 |   0 |      0 |       0 |       0 |
|     180403 | 王3          | 1804          |   33 |  77 |  88 |  99 |      1 |       0 |     300 |
|     180404 | 张5          | 1804          |   66 |   0 |   0 |  99 |      2 |       1 |     278 |
|     180405 | 刘红         | 1804          |   23 |  66 |  88 |  99 |      1 |       1 |     389 |
|     180406 | 刘至         | 1804          |   23 |  66 |  55 |  99 |      1 |       0 |     289 |
|     180407 | 魏国         | 1804          |   63 |  66 |  88 |  99 |      1 |       1 |     369 |
|     180408 | 李四         | 1804          |   93 |  96 |  88 |  99 |      0 |       0 |     389 |
|     180409 | 王武         | 1804          |   73 |  96 |  88 |  99 |      1 |       0 |     389 |
|     180410 | 李祯         | 1804          |   94 |  76 |  98 |  99 |      1 |       0 |     289 |
|     180411 | 李中         | 1804          |   20 |  67 |  88 |  99 |      1 |       1 |     389 |
+------------+--------------+---------------+------+-----+-----+-----+--------+---------+---------+
11 rows in set (0.00 sec)

mysql> INSERT INTO student(student_name,student_class,math,phy,lan,eng,bjgnum,gendent,toscore)values('刘红',1804,23,66,88,99,1,1,389),('刘至',1804,23,66,55,99,1,0,289),('魏国',1804,63,66,88,99,1,1,369),('李四',1804,93,96,88,99,0,0,389),('王武',1804,73,96,88,99,1,0,389),('李祯',1804,94,76,98,99,1,0,289),('李中',1804,20,67,88,99,1,1,389)
    -> 

^C
mysql> INSERT INTO student(student_name,student_class,math,phy,lan,eng,bjgnum,gendent,toscore)values('王铁腿',1804,66,66,88,99,1,0,389),('牛力',1804,77,96,75,99,1,1,389),('胡汉三',1804,93,46,78,99,1,0,369),('李五',1804,93,99,88,99,0,0,389),('王李',1804,77,96,88,29,1,1,229),('李思',1804,94,76,98,99,1,1,189),('小红',1804,90,63,88,99,1,1,119);
Query OK, 7 rows affected (0.07 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> select * from student;
+------------+--------------+---------------+------+-----+-----+-----+--------+---------+---------+
| student_id | student_name | student_class | math | phy | lan | eng | bjgnum | gendent | toscore |
+------------+--------------+---------------+------+-----+-----+-----+--------+---------+---------+
|     180401 | 王1          | 1804          |   77 |  88 |  99 | 100 |      0 |       1 |     300 |
|     180402 | 王2          | 1804          |    0 |   0 |   0 |   0 |      0 |       0 |       0 |
|     180403 | 王3          | 1804          |   33 |  77 |  88 |  99 |      1 |       0 |     300 |
|     180404 | 张5          | 1804          |   66 |   0 |   0 |  99 |      2 |       1 |     278 |
|     180405 | 刘红         | 1804          |   23 |  66 |  88 |  99 |      1 |       1 |     389 |
|     180406 | 刘至         | 1804          |   23 |  66 |  55 |  99 |      1 |       0 |     289 |
|     180407 | 魏国         | 1804          |   63 |  66 |  88 |  99 |      1 |       1 |     369 |
|     180408 | 李四         | 1804          |   93 |  96 |  88 |  99 |      0 |       0 |     389 |
|     180409 | 王武         | 1804          |   73 |  96 |  88 |  99 |      1 |       0 |     389 |
|     180410 | 李祯         | 1804          |   94 |  76 |  98 |  99 |      1 |       0 |     289 |
|     180411 | 李中         | 1804          |   20 |  67 |  88 |  99 |      1 |       1 |     389 |
|     180412 | 王铁腿       | 1804          |   66 |  66 |  88 |  99 |      1 |       0 |     389 |
|     180413 | 牛力         | 1804          |   77 |  96 |  75 |  99 |      1 |       1 |     389 |
|     180414 | 胡汉三       | 1804          |   93 |  46 |  78 |  99 |      1 |       0 |     369 |
|     180415 | 李五         | 1804          |   93 |  99 |  88 |  99 |      0 |       0 |     389 |
|     180416 | 王李         | 1804          |   77 |  96 |  88 |  29 |      1 |       1 |     229 |
|     180417 | 李思         | 1804          |   94 |  76 |  98 |  99 |      1 |       1 |     189 |
|     180418 | 小红         | 1804          |   90 |  63 |  88 |  99 |      1 |       1 |     119 |
+------------+--------------+---------------+------+-----+-----+-----+--------+---------+---------+
18 rows in set (0.00 sec)

mysql> INSERT INTO student(student_name,student_class,math,phy,lan,eng,bjgnum,gendent,toscore)values('王一一',1804,76,66,78,79,1,1,389),('牛二二',1804,77,96,77,99,1,0,389);
Query OK, 2 rows affected (0.07 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from student;                                                                                 +------------+--------------+---------------+------+-----+-----+-----+--------+---------+---------+
| student_id | student_name | student_class | math | phy | lan | eng | bjgnum | gendent | toscore |
+------------+--------------+---------------+------+-----+-----+-----+--------+---------+---------+
|     180401 | 王1          | 1804          |   77 |  88 |  99 | 100 |      0 |       1 |     300 |
|     180402 | 王2          | 1804          |    0 |   0 |   0 |   0 |      0 |       0 |       0 |
|     180403 | 王3          | 1804          |   33 |  77 |  88 |  99 |      1 |       0 |     300 |
|     180404 | 张5          | 1804          |   66 |   0 |   0 |  99 |      2 |       1 |     278 |
|     180405 | 刘红         | 1804          |   23 |  66 |  88 |  99 |      1 |       1 |     389 |
|     180406 | 刘至         | 1804          |   23 |  66 |  55 |  99 |      1 |       0 |     289 |
|     180407 | 魏国         | 1804          |   63 |  66 |  88 |  99 |      1 |       1 |     369 |
|     180408 | 李四         | 1804          |   93 |  96 |  88 |  99 |      0 |       0 |     389 |
|     180409 | 王武         | 1804          |   73 |  96 |  88 |  99 |      1 |       0 |     389 |
|     180410 | 李祯         | 1804          |   94 |  76 |  98 |  99 |      1 |       0 |     289 |
|     180411 | 李中         | 1804          |   20 |  67 |  88 |  99 |      1 |       1 |     389 |
|     180412 | 王铁腿       | 1804          |   66 |  66 |  88 |  99 |      1 |       0 |     389 |
|     180413 | 牛力         | 1804          |   77 |  96 |  75 |  99 |      1 |       1 |     389 |
|     180414 | 胡汉三       | 1804          |   93 |  46 |  78 |  99 |      1 |       0 |     369 |
|     180415 | 李五         | 1804          |   93 |  99 |  88 |  99 |      0 |       0 |     389 |
|     180416 | 王李         | 1804          |   77 |  96 |  88 |  29 |      1 |       1 |     229 |
|     180417 | 李思         | 1804          |   94 |  76 |  98 |  99 |      1 |       1 |     189 |
|     180418 | 小红         | 1804          |   90 |  63 |  88 |  99 |      1 |       1 |     119 |
|     180419 | 王一一       | 1804          |   76 |  66 |  78 |  79 |      1 |       1 |     389 |
|     180420 | 牛二二       | 1804          |   77 |  96 |  77 |  99 |      1 |       0 |     389 |
+------------+--------------+---------------+------+-----+-----+-----+--------+---------+---------+
20 rows in set (0.00 sec)

mysql> DELETE FROM student WHERE student_id=180401;
Query OK, 1 row affected (0.06 sec)

mysql> select * from student;
+------------+--------------+---------------+------+-----+-----+-----+--------+---------+---------+
| student_id | student_name | student_class | math | phy | lan | eng | bjgnum | gendent | toscore |
+------------+--------------+---------------+------+-----+-----+-----+--------+---------+---------+
|     180402 | 王2          | 1804          |    0 |   0 |   0 |   0 |      0 |       0 |       0 |
|     180403 | 王3          | 1804          |   33 |  77 |  88 |  99 |      1 |       0 |     300 |
|     180404 | 张5          | 1804          |   66 |   0 |   0 |  99 |      2 |       1 |     278 |
|     180405 | 刘红         | 1804          |   23 |  66 |  88 |  99 |      1 |       1 |     389 |
|     180406 | 刘至         | 1804          |   23 |  66 |  55 |  99 |      1 |       0 |     289 |
|     180407 | 魏国         | 1804          |   63 |  66 |  88 |  99 |      1 |       1 |     369 |
|     180408 | 李四         | 1804          |   93 |  96 |  88 |  99 |      0 |       0 |     389 |
|     180409 | 王武         | 1804          |   73 |  96 |  88 |  99 |      1 |       0 |     389 |
|     180410 | 李祯         | 1804          |   94 |  76 |  98 |  99 |      1 |       0 |     289 |
|     180411 | 李中         | 1804          |   20 |  67 |  88 |  99 |      1 |       1 |     389 |
|     180412 | 王铁腿       | 1804          |   66 |  66 |  88 |  99 |      1 |       0 |     389 |
|     180413 | 牛力         | 1804          |   77 |  96 |  75 |  99 |      1 |       1 |     389 |
|     180414 | 胡汉三       | 1804          |   93 |  46 |  78 |  99 |      1 |       0 |     369 |
|     180415 | 李五         | 1804          |   93 |  99 |  88 |  99 |      0 |       0 |     389 |
|     180416 | 王李         | 1804          |   77 |  96 |  88 |  29 |      1 |       1 |     229 |
|     180417 | 李思         | 1804          |   94 |  76 |  98 |  99 |      1 |       1 |     189 |
|     180418 | 小红         | 1804          |   90 |  63 |  88 |  99 |      1 |       1 |     119 |
|     180419 | 王一一       | 1804          |   76 |  66 |  78 |  79 |      1 |       1 |     389 |
|     180420 | 牛二二       | 1804          |   77 |  96 |  77 |  99 |      1 |       0 |     389 |
+------------+--------------+---------------+------+-----+-----+-----+--------+---------+---------+
19 rows in set (0.00 sec)


mysql> UPDATE student SET lan=100,toscore=400 WHERE student_id=1803418;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> select * from student;
+------------+--------------+---------------+------+-----+-----+-----+--------+---------+---------+
| student_id | student_name | student_class | math | phy | lan | eng | bjgnum | gendent | toscore |
+------------+--------------+---------------+------+-----+-----+-----+--------+---------+---------+
|     180402 | 王2          | 1804          |    0 |   0 |   0 |   0 |      0 |       0 |       0 |
|     180403 | 王3          | 1804          |   33 |  77 |  88 |  99 |      1 |       0 |     300 |
|     180404 | 张5          | 1804          |   66 |   0 |   0 |  99 |      2 |       1 |     278 |
|     180405 | 刘红         | 1804          |   23 |  66 |  88 |  99 |      1 |       1 |     389 |
|     180406 | 刘至         | 1804          |   23 |  66 |  55 |  99 |      1 |       0 |     289 |
|     180407 | 魏国         | 1804          |   63 |  66 |  88 |  99 |      1 |       1 |     369 |
|     180408 | 李四         | 1804          |   93 |  96 |  88 |  99 |      0 |       0 |     389 |
|     180409 | 王武         | 1804          |   73 |  96 |  88 |  99 |      1 |       0 |     389 |
|     180410 | 李祯         | 1804          |   94 |  76 |  98 |  99 |      1 |       0 |     289 |
|     180411 | 李中         | 1804          |   20 |  67 |  88 |  99 |      1 |       1 |     389 |
|     180412 | 王铁腿       | 1804          |   66 |  66 |  88 |  99 |      1 |       0 |     389 |
|     180413 | 牛力         | 1804          |   77 |  96 |  75 |  99 |      1 |       1 |     389 |
|     180414 | 胡汉三       | 1804          |   93 |  46 |  78 |  99 |      1 |       0 |     369 |
|     180415 | 李五         | 1804          |   93 |  99 |  88 |  99 |      0 |       0 |     389 |
|     180416 | 王李         | 1804          |   77 |  96 |  88 |  29 |      1 |       1 |     229 |
|     180417 | 李思         | 1804          |   94 |  76 |  98 |  99 |      1 |       1 |     189 |
|     180418 | 小红         | 1804          |   90 |  63 |  88 |  99 |      1 |       1 |     119 |
|     180419 | 王一一       | 1804          |   76 |  66 |  78 |  79 |      1 |       1 |     389 |
|     180420 | 牛二二       | 1804          |   77 |  96 |  77 |  99 |      1 |       0 |     389 |
+------------+--------------+---------------+------+-----+-----+-----+--------+---------+---------+
19 rows in set (0.00 sec)

mysql> UPDATE student SET lan=100,toscore=400 WHERE student_id=1803418;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> UPDATE student SET lan=100,toscore=400 WHERE student_id=180418;
Query OK, 1 row affected (0.08 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student;
+------------+--------------+---------------+------+-----+-----+-----+--------+---------+---------+
| student_id | student_name | student_class | math | phy | lan | eng | bjgnum | gendent | toscore |
+------------+--------------+---------------+------+-----+-----+-----+--------+---------+---------+
|     180402 | 王2          | 1804          |    0 |   0 |   0 |   0 |      0 |       0 |       0 |
|     180403 | 王3          | 1804          |   33 |  77 |  88 |  99 |      1 |       0 |     300 |
|     180404 | 张5          | 1804          |   66 |   0 |   0 |  99 |      2 |       1 |     278 |
|     180405 | 刘红         | 1804          |   23 |  66 |  88 |  99 |      1 |       1 |     389 |
|     180406 | 刘至         | 1804          |   23 |  66 |  55 |  99 |      1 |       0 |     289 |
|     180407 | 魏国         | 1804          |   63 |  66 |  88 |  99 |      1 |       1 |     369 |
|     180408 | 李四         | 1804          |   93 |  96 |  88 |  99 |      0 |       0 |     389 |
|     180409 | 王武         | 1804          |   73 |  96 |  88 |  99 |      1 |       0 |     389 |
|     180410 | 李祯         | 1804          |   94 |  76 |  98 |  99 |      1 |       0 |     289 |
|     180411 | 李中         | 1804          |   20 |  67 |  88 |  99 |      1 |       1 |     389 |
|     180412 | 王铁腿       | 1804          |   66 |  66 |  88 |  99 |      1 |       0 |     389 |
|     180413 | 牛力         | 1804          |   77 |  96 |  75 |  99 |      1 |       1 |     389 |
|     180414 | 胡汉三       | 1804          |   93 |  46 |  78 |  99 |      1 |       0 |     369 |
|     180415 | 李五         | 1804          |   93 |  99 |  88 |  99 |      0 |       0 |     389 |
|     180416 | 王李         | 1804          |   77 |  96 |  88 |  29 |      1 |       1 |     229 |
|     180417 | 李思         | 1804          |   94 |  76 |  98 |  99 |      1 |       1 |     189 |
|     180418 | 小红         | 1804          |   90 |  63 | 100 |  99 |      1 |       1 |     400 |
|     180419 | 王一一       | 1804          |   76 |  66 |  78 |  79 |      1 |       1 |     389 |
|     180420 | 牛二二       | 1804          |   77 |  96 |  77 |  99 |      1 |       0 |     389 |
+------------+--------------+---------------+------+-----+-----+-----+--------+---------+---------+
19 rows in set (0.00 sec)


mysql> UPDATE student SET student_name='后改',lan=100 WHERE student_id=180419;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * FROM student;
+------------+--------------+---------------+------+-----+-----+-----+--------+---------+---------+
| student_id | student_name | student_class | math | phy | lan | eng | bjgnum | gendent | toscore |
+------------+--------------+---------------+------+-----+-----+-----+--------+---------+---------+
|     180402 | 王2          | 1804          |    0 |   0 |   0 |   0 |      0 |       0 |       0 |
|     180403 | 王3          | 1804          |   33 |  77 |  88 |  99 |      1 |       0 |     300 |
|     180404 | 张5          | 1804          |   66 |   0 |   0 |  99 |      2 |       1 |     278 |
|     180405 | 刘红         | 1804          |   23 |  66 |  88 |  99 |      1 |       1 |     389 |
|     180406 | 刘至         | 1804          |   23 |  66 |  55 |  99 |      1 |       0 |     289 |
|     180407 | 魏国         | 1804          |   63 |  66 |  88 |  99 |      1 |       1 |     369 |
|     180408 | 李四         | 1804          |   93 |  96 |  88 |  99 |      0 |       0 |     389 |
|     180409 | 王武         | 1804          |   73 |  96 |  88 |  99 |      1 |       0 |     389 |
|     180410 | 李祯         | 1804          |   94 |  76 |  98 |  99 |      1 |       0 |     289 |
|     180411 | 李中         | 1804          |   20 |  67 |  88 |  99 |      1 |       1 |     389 |
|     180412 | 王铁腿       | 1804          |   66 |  66 |  88 |  99 |      1 |       0 |     389 |
|     180413 | 牛力         | 1804          |   77 |  96 |  75 |  99 |      1 |       1 |     389 |
|     180414 | 胡汉三       | 1804          |   93 |  46 |  78 |  99 |      1 |       0 |     369 |
|     180415 | 李五         | 1804          |   93 |  99 |  88 |  99 |      0 |       0 |     389 |
|     180416 | 王李         | 1804          |   77 |  96 |  88 |  29 |      1 |       1 |     229 |
|     180417 | 李思         | 1804          |   94 |  76 |  98 |  99 |      1 |       1 |     189 |
|     180418 | 小红         | 1804          |   90 |  63 | 100 |  99 |      1 |       1 |     400 |
|     180419 | 后改         | 1804          |   76 |  66 | 100 |  79 |      1 |       1 |     389 |
|     180420 | 牛二二       | 1804          |   77 |  96 |  77 |  99 |      1 |       0 |     389 |
+------------+--------------+---------------+------+-----+-----+-----+--------+---------+---------+
19 rows in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| 这种bc             |
| bc                 |
| class1804          |
| class18042         |
| class1804h         |
| class1804n         |
| class1804p         |
| class222           |
| gebilaowang        |
| gradeinfo          |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
14 rows in set (0.00 sec)

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

mysql> CREATE TABLE student1(
    -> student_
    -> i

^C
mysql> CREATE TABLE student1(
    -> student_id int NOT NULL AUTO_INTCREMENT,
    -> student_name char(25) NOT NULL,
    -> eng int NOT NULL default 0,
    -> PRIMARY KEY(student_id)
    -> );


mysql> CREATE TABLE student1( student_id int NOT NULL AUTO_INCREMENT, student_name char(25) NOT NULL, eng int NOT NULL default 0, PRIMARY KEY(student_id) );
Query OK, 0 rows affected (0.34 sec)

mysql> show tables;
+---------------------+
| Tables_in_gradeinfo |
+---------------------+
| student             |
| student1            |
+---------------------+
2 rows in set (0.00 sec)


mysql> INSERT INTO student1(student_id,student_name,eng) SELECT student_id,student_name,eng FROM student; Query OK, 19 rows affected (0.07 sec)
Records: 19  Duplicates: 0  Warnings: 0

mysql> select * from student1;
+------------+--------------+-----+
| student_id | student_name | eng |
+------------+--------------+-----+
|     180402 | 王2          |   0 |
|     180403 | 王3          |  99 |
|     180404 | 张5          |  99 |
|     180405 | 刘红         |  99 |
|     180406 | 刘至         |  99 |
|     180407 | 魏国         |  99 |
|     180408 | 李四         |  99 |
|     180409 | 王武         |  99 |
|     180410 | 李祯         |  99 |
|     180411 | 李中         |  99 |
|     180412 | 王铁腿       |  99 |
|     180413 | 牛力         |  99 |
|     180414 | 胡汉三       |  99 |
|     180415 | 李五         |  99 |
|     180416 | 王李         |  29 |
|     180417 | 李思         |  99 |
|     180418 | 小红         |  99 |
|     180419 | 后改         |  79 |
|     180420 | 牛二二       |  99 |
+------------+--------------+-----+
19 rows in set (0.00 sec)


mysql> select * from student;
+------------+--------------+---------------+------+-----+-----+-----+--------+---------+---------+
| student_id | student_name | student_class | math | phy | lan | eng | bjgnum | gendent | toscore |
+------------+--------------+---------------+------+-----+-----+-----+--------+---------+---------+
|     180402 | 王2          | 1804          |    0 |   0 |   0 |   0 |      0 |       0 |       0 |
|     180403 | 王3          | 1804          |   33 |  77 |  88 |  99 |      1 |       0 |     300 |
|     180404 | 张5          | 1804          |   66 |   0 |   0 |  99 |      2 |       1 |     278 |
|     180405 | 刘红         | 1804          |   23 |  66 |  88 |  99 |      1 |       1 |     389 |
|     180406 | 刘至         | 1804          |   23 |  66 |  55 |  99 |      1 |       0 |     289 |
|     180407 | 魏国         | 1804          |   63 |  66 |  88 |  99 |      1 |       1 |     369 |
|     180408 | 李四         | 1804          |   93 |  96 |  88 |  99 |      0 |       0 |     389 |
|     180409 | 王武         | 1804          |   73 |  96 |  88 |  99 |      1 |       0 |     389 |
|     180410 | 李祯         | 1804          |   94 |  76 |  98 |  99 |      1 |       0 |     289 |
|     180411 | 李中         | 1804          |   20 |  67 |  88 |  99 |      1 |       1 |     389 |
|     180412 | 王铁腿       | 1804          |   66 |  66 |  88 |  99 |      1 |       0 |     389 |
|     180413 | 牛力         | 1804          |   77 |  96 |  75 |  99 |      1 |       1 |     389 |
|     180414 | 胡汉三       | 1804          |   93 |  46 |  78 |  99 |      1 |       0 |     369 |
|     180415 | 李五         | 1804          |   93 |  99 |  88 |  99 |      0 |       0 |     389 |
|     180416 | 王李         | 1804          |   77 |  96 |  88 |  29 |      1 |       1 |     229 |
|     180417 | 李思         | 1804          |   94 |  76 |  98 |  99 |      1 |       1 |     189 |
|     180418 | 小红         | 1804          |   90 |  63 | 100 |  99 |      1 |       1 |     400 |
|     180419 | 后改         | 1804          |   76 |  66 | 100 |  79 |      1 |       1 |     389 |
|     180420 | 牛二二       | 1804          |   77 |  96 |  77 |  99 |      1 |       0 |     389 |
+------------+--------------+---------------+------+-----+-----+-----+--------+---------+---------+
19 rows in set (0.00 sec)

mysql> SELECT student_id,student_name from student WHERE toscore>300;
+------------+--------------+
| student_id | student_name |
+------------+--------------+
|     180405 | 刘红         |
|     180407 | 魏国         |
|     180408 | 李四         |
|     180409 | 王武         |
|     180411 | 李中         |
|     180412 | 王铁腿       |
|     180413 | 牛力         |
|     180414 | 胡汉三       |
|     180415 | 李五         |
|     180418 | 小红         |
|     180419 | 后改         |
|     180420 | 牛二二       |
+------------+--------------+
12 rows in set (0.00 sec)


mysql> SELECT student_id,student_name from student where phy>80 AND gendent=1;
+------------+--------------+
| student_id | student_name |
+------------+--------------+
|     180413 | 牛力         |
|     180416 | 王李         |
+------------+--------------+
2 rows in set (0.00 sec)

mysql> SELECT DISTINCT math,phy,lan,eng FROM student;
+------+-----+-----+-----+
| math | phy | lan | eng |
+------+-----+-----+-----+
|    0 |   0 |   0 |   0 |
|   33 |  77 |  88 |  99 |
|   66 |   0 |   0 |  99 |
|   23 |  66 |  88 |  99 |
|   23 |  66 |  55 |  99 |
|   63 |  66 |  88 |  99 |
|   93 |  96 |  88 |  99 |
|   73 |  96 |  88 |  99 |
|   94 |  76 |  98 |  99 |
|   20 |  67 |  88 |  99 |
|   66 |  66 |  88 |  99 |
|   77 |  96 |  75 |  99 |
|   93 |  46 |  78 |  99 |
|   93 |  99 |  88 |  99 |
|   77 |  96 |  88 |  29 |
|   90 |  63 | 100 |  99 |
|   76 |  66 | 100 |  79 |
|   77 |  96 |  77 |  99 |
+------+-----+-----+-----+
18 rows in set (0.00 sec)

mysql> SELECT DISTINCT math FROM student;
+------+
| math |
+------+
|    0 |
|   33 |
|   66 |
|   23 |
|   63 |
|   93 |
|   73 |
|   94 |
|   20 |
|   77 |
|   90 |
|   76 |
+------+
12 rows in set (0.00 sec)

mysql> SELECT DISTINCT phy FROM student;
+-----+
| phy |
+-----+
|   0 |
|  77 |
|  66 |
|  96 |
|  76 |
|  67 |
|  46 |
|  99 |
|  63 |
+-----+
9 rows in set (0.00 sec)

mysql> SELECT DISTINCT lanFROM student;
ERROR 1054 (42S22): Unknown column 'lanFROM' in 'field list'
mysql> SELECT DISTINCT lan FROM student;
+-----+
| lan |
+-----+
|   0 |
|  88 |
|  55 |
|  98 |
|  75 |
|  78 |
| 100 |
|  77 |
+-----+
8 rows in set (0.00 sec)

mysql> SELECT DISTINCT eng FROM student;
+-----+
| eng |
+-----+
|   0 |
|  99 |
|  29 |
|  79 |
+-----+
4 rows in set (0.01 sec)


mysql> SELECT student_id,student_name,student_class,toscore from student where toscore between 288 and 300;
+------------+--------------+---------------+---------+
| student_id | student_name | student_class | toscore |
+------------+--------------+---------------+---------+
|     180403 | 王3          | 1804          |     300 |
|     180406 | 刘至         | 1804          |     289 |
|     180410 | 李祯         | 1804          |     289 |
+------------+--------------+---------------+---------+
3 rows in set (0.00 sec)

mysql> SELECT student_name  FROM student WHERE phy>70 and lan>80;
+--------------+
| student_name |
+--------------+
| 王3          |
| 李四         |
| 王武         |
| 李祯         |
| 李五         |
| 王李         |
| 李思         |
+--------------+
7 rows in set (0.00 sec)

mysql> SELECT eng from student where eng=70 or eng=80;
Empty set (0.00 sec)

mysql> SELECT math,phy,lan from student where eng=75 or eng=85;
Empty set (0.00 sec)

mysql> SELECT math,phy,lan from student where eng IN(70,80,75,85);
Empty set (0.00 sec)

mysql> SELECT * from student where student_name LIKE '赵%' and bjgnum>2;
Empty set (0.00 sec)

mysql> SELECT * from student where student_name LIKE '赵%' and bjgnum>0;
Empty set (0.00 sec)

mysql> SELECT * from student where student_name LIKE '王%' and bjgnum>0;
+------------+--------------+---------------+------+-----+-----+-----+--------+---------+---------+
| student_id | student_name | student_class | math | phy | lan | eng | bjgnum | gendent | toscore |
+------------+--------------+---------------+------+-----+-----+-----+--------+---------+---------+
|     180403 | 王3          | 1804          |   33 |  77 |  88 |  99 |      1 |       0 |     300 |
|     180409 | 王武         | 1804          |   73 |  96 |  88 |  99 |      1 |       0 |     389 |
|     180412 | 王铁腿       | 1804          |   66 |  66 |  88 |  99 |      1 |       0 |     389 |
|     180416 | 王李         | 1804          |   77 |  96 |  88 |  29 |      1 |       1 |     229 |
+------------+--------------+---------------+------+-----+-----+-----+--------+---------+---------+
4 rows in set (0.00 sec)

mysql> SELECT * from student where student_name LIKE '__';
+------------+--------------+---------------+------+-----+-----+-----+--------+---------+---------+
| student_id | student_name | student_class | math | phy | lan | eng | bjgnum | gendent | toscore |
+------------+--------------+---------------+------+-----+-----+-----+--------+---------+---------+
|     180402 | 王2          | 1804          |    0 |   0 |   0 |   0 |      0 |       0 |       0 |
|     180403 | 王3          | 1804          |   33 |  77 |  88 |  99 |      1 |       0 |     300 |
|     180404 | 张5          | 1804          |   66 |   0 |   0 |  99 |      2 |       1 |     278 |
|     180405 | 刘红         | 1804          |   23 |  66 |  88 |  99 |      1 |       1 |     389 |
|     180406 | 刘至         | 1804          |   23 |  66 |  55 |  99 |      1 |       0 |     289 |
|     180407 | 魏国         | 1804          |   63 |  66 |  88 |  99 |      1 |       1 |     369 |
|     180408 | 李四         | 1804          |   93 |  96 |  88 |  99 |      0 |       0 |     389 |
|     180409 | 王武         | 1804          |   73 |  96 |  88 |  99 |      1 |       0 |     389 |
|     180410 | 李祯         | 1804          |   94 |  76 |  98 |  99 |      1 |       0 |     289 |
|     180411 | 李中         | 1804          |   20 |  67 |  88 |  99 |      1 |       1 |     389 |
|     180413 | 牛力         | 1804          |   77 |  96 |  75 |  99 |      1 |       1 |     389 |
|     180415 | 李五         | 1804          |   93 |  99 |  88 |  99 |      0 |       0 |     389 |
|     180416 | 王李         | 1804          |   77 |  96 |  88 |  29 |      1 |       1 |     229 |
|     180417 | 李思         | 1804          |   94 |  76 |  98 |  99 |      1 |       1 |     189 |
|     180418 | 小红         | 1804          |   90 |  63 | 100 |  99 |      1 |       1 |     400 |
|     180419 | 后改         | 1804          |   76 |  66 | 100 |  79 |      1 |       1 |     389 |
+------------+--------------+---------------+------+-----+-----+-----+--------+---------+---------+
16 rows in set (0.00 sec)

    相关文章

      网友评论

        本文标题：数据库表的操作实例

        本文链接：https://www.haomeiwen.com/subject/pavfbftx.html

        延伸阅读

        深度阅读

        • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

        栏目导航

        热点阅读

        数据库

        关于我们|服务条款|联系我们|数据库表的操作实例|投稿指南|网站地图|RSS订阅|排版工具|手机版

        提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

        Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

        备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

        本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

        所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！