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

数据库表的操作实例

作者: 叫我老村长 | 来源:发表于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)

相关文章

  • Oracle 基础

    一。基础知识 Oracle - 数据库的实例、表空间、用户、表之间关系 数据库的实例 数据库实例名(instanc...

  • 记一下oracle操作

    配置文件 用户的操作 修改oracle数据库的内存 修改用户密码 表的删除操作 实例检查脚本 查看表空间 表空间的...

  • 数据库表的操作实例

  • Spark实例-操作Hive数据

    Spark操作Hive数据库，实现数据表创建，数据加载，以及数据查询。实例代码如下：

  • Oracle数据库、实例、用户、表空间、表之间的关系

    Oracle数据库、实例、用户、表空间、表之间的关系 通常我们oralce数据库，说的是实例，而不是真正的数据库，...

  • C# EXCEL表的写入操作

    C#操作Excel表的操作实例

  • 数据库、表、字段操作速览

    6.1 数据库操作 创建数据库 查看数据库 选中数据库 查看数据库中的表 删除数据库 6.2 数据表操作 创建表 ...

  • Hive DDL

    Hive 库操作 创建数据库 删除数据库 修改数据库信息 数据库信息 Hive 表操作 内部表又称管理表。 Hiv...

  • SQL之DDL

    DDL是用于操作数据库以及表结构的语句 数据库操作 数据类型 表操作 一.数据库操作 查看所有数据库：SHOW D...

  • 数据库第一周

    mysql的命令和查询语句: 登陆系统 数据库的操作 如何创建表 关于表的操作： DML:数据库操作语言：对于表中...

网友评论

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

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

    延伸阅读

    深度阅读

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

    栏目导航

    热点阅读

    数据库

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

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

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

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

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

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