數(shù)據(jù)庫(kù)表的操作實(shí)例

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),('魏國(guó)',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 | 魏國(guó)         | 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),('魏國(guó)',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 | 魏國(guó)         | 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 | 魏國(guó)         | 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 | 魏國(guó)         | 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 | 魏國(guó)         | 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 | 魏國(guó)         | 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 | 魏國(guó)         | 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 | 魏國(guó)         |  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 | 魏國(guó)         | 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 | 魏國(guó)         |
|     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 | 魏國(guó)         | 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)

最后編輯于：2018.11.05 23:47:24

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者

人面猴
序言：七十年代末，一起剝皮案震驚了整個(gè)濱河市，隨后出現(xiàn)的幾起案子应闯，更是在濱河造成了極大的恐慌蛀序，老刑警劉巖欢瞪，帶你破解...
沈念sama閱讀 222,000評(píng)論 6贊 515
死咒
序言：濱河連續(xù)發(fā)生了三起死亡事件，死亡現(xiàn)場(chǎng)離奇詭異徐裸，居然都是意外死亡遣鼓，警方通過(guò)查閱死者的電腦和手機(jī)，發(fā)現(xiàn)死者居然都...
沈念sama閱讀 94,745評(píng)論 3贊 399
救了他兩次的神仙讓他今天三更去死
文/潘曉璐我一進(jìn)店門重贺，熙熙樓的掌柜王于貴愁眉苦臉地迎上來(lái)骑祟，“玉大人，你說(shuō)我怎么就攤上這事气笙〈纹螅” “怎么了？”我有些...
開(kāi)封第一講書人閱讀 168,561評(píng)論 0贊 360
道士緝兇錄：失蹤的賣姜人
文/不壞的土叔我叫張陵潜圃，是天一觀的道長(zhǎng)缸棵。經(jīng)常有香客問(wèn)我，道長(zhǎng)谭期，這世上最難降的妖魔是什么堵第？我笑而不...
開(kāi)封第一講書人閱讀 59,782評(píng)論 1贊 298
?港島之戀（遺憾婚禮）
正文為了忘掉前任吧凉，我火速辦了婚禮，結(jié)果婚禮上踏志，老公的妹妹穿的比我還像新娘阀捅。我一直安慰自己，他們只是感情好针余，可當(dāng)我...
茶點(diǎn)故事閱讀 68,798評(píng)論 6贊 397
惡毒庶女頂嫁案：這布局不是一般人想出來(lái)的
文/花漫我一把揭開(kāi)白布饲鄙。她就那樣靜靜地躺著，像睡著了一般圆雁。火紅的嫁衣襯著肌膚如雪忍级。梳的紋絲不亂的頭發(fā)上，一...
開(kāi)封第一講書人閱讀 52,394評(píng)論 1贊 310
城市分裂傳說(shuō)
那天摸柄，我揣著相機(jī)與錄音颤练，去河邊找鬼。笑死驱负，一個(gè)胖子當(dāng)著我的面吹牛嗦玖，可吹牛的內(nèi)容都是我干的。我是一名探鬼主播跃脊，決...
沈念sama閱讀 40,952評(píng)論 3贊 421
雙鴛鴦連環(huán)套：你想象不到人心有多黑
文/蒼蘭香墨我猛地睜開(kāi)眼宇挫，長(zhǎng)吁一口氣：“原來(lái)是場(chǎng)噩夢(mèng)啊……” “哼！你這毒婦竟也來(lái)了酪术？” 一聲冷哼從身側(cè)響起器瘪，我...
開(kāi)封第一講書人閱讀 39,852評(píng)論 0贊 276
萬(wàn)榮殺人案實(shí)錄
序言：老撾萬(wàn)榮一對(duì)情侶失蹤，失蹤者是張志新（化名）和其女友劉穎绘雁，沒(méi)想到半個(gè)月后橡疼，有當(dāng)?shù)厝嗽跇?shù)林里發(fā)現(xiàn)了一具尸體，經(jīng)...
沈念sama閱讀 46,409評(píng)論 1贊 318
?護(hù)林員之死
正文獨(dú)居荒郊野嶺守林人離奇死亡庐舟，尸身上長(zhǎng)有42處帶血的膿包…… 初始之章·張勛以下內(nèi)容為張勛視角年9月15日...
茶點(diǎn)故事閱讀 38,483評(píng)論 3贊 341
?白月光啟示錄
正文我和宋清朗相戀三年欣除，在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了。大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片挪略。...
茶點(diǎn)故事閱讀 40,615評(píng)論 1贊 352
活死人
序言：一個(gè)原本活蹦亂跳的男人離奇死亡历帚，死狀恐怖，靈堂內(nèi)的尸體忽然破棺而出杠娱，到底是詐尸還是另有隱情挽牢，我是刑警寧澤，帶...
沈念sama閱讀 36,303評(píng)論 5贊 350
?日本核電站爆炸內(nèi)幕
正文年R本政府宣布摊求，位于F島的核電站禽拔，受9級(jí)特大地震影響，放射性物質(zhì)發(fā)生泄漏。R本人自食惡果不足惜奏赘，卻給世界環(huán)境...
茶點(diǎn)故事閱讀 41,979評(píng)論 3贊 334
男人毒藥：我在死后第九天來(lái)索命
文/蒙蒙一寥闪、第九天我趴在偏房一處隱蔽的房頂上張望。院中可真熱鬧磨淌，春花似錦、人聲如沸凿渊。這莊子的主人今日做“春日...
開(kāi)封第一講書人閱讀 32,470評(píng)論 0贊 24
一樁弒父案，背后竟有這般陰謀
文/蒼蘭香墨我抬頭看了看天上的太陽(yáng)埃脏。三九已至搪锣，卻和暖如春，著一層夾襖步出監(jiān)牢的瞬間彩掐，已是汗流浹背构舟。一陣腳步聲響...
開(kāi)封第一講書人閱讀 33,571評(píng)論 1贊 272
情欲美人皮
我被黑心中介騙來(lái)泰國(guó)打工，沒(méi)想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留堵幽，地道東北人狗超。一個(gè)月前我還...
沈念sama閱讀 49,041評(píng)論 3贊 377
代替公主和親
正文我出身青樓，卻偏偏與公主長(zhǎng)得像朴下，于是被迫代替她去往敵國(guó)和親努咐。傳聞我的和親對(duì)象是個(gè)殘疾皇子，可洞房花燭夜當(dāng)晚...
茶點(diǎn)故事閱讀 45,630評(píng)論 2贊 359

數(shù)據(jù)庫(kù)表的操作實(shí)例

推薦閱讀更多精彩內(nèi)容