【MYSQL】数据库的一些基本操作2(复习用)

适用于学过mysql的进行复习，看的多，敲的多，自然就会了。

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

mysql> use z1;
Database changed
mysql> show tables
    -> ;
+--------------+
| Tables_in_z1 |
+--------------+
| customer     |
| exam         |
| goods        |
| purchase     |
+--------------+
4 rows in set (0.00 sec)

mysql> create table student (id int not null,name varchar(20));
Query OK, 0 rows affected (0.03 sec)

mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> insert into student values(null,'张三');
ERROR 1048 (23000): Column 'id' cannot be null
mysql> insert into student (name) values('z1');
ERROR 1364 (HY000): Field 'id' doesn't have a default value
mysql> drop table student;
Query OK, 0 rows affected (0.01 sec)

mysql> create table student(id int unique,name varchar(20));
Query OK, 0 rows affected (0.03 sec)

mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  | UNI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> create table student2(id int, name varchar(20));
Query OK, 0 rows affected (0.02 sec)

mysql> desc student2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into student values(1,'z1');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(1,'z2');
ERROR 1062 (23000): Duplicate entry '1' for key 'student.id'
mysql> insert into student values(2,'z2');
Query OK, 1 row affected (0.00 sec)

mysql> create student3 (id int not null unique,name varchar(20));
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 'student3 (id int not null unique,name varchar(20))' at line 1
mysql> create table student3 (id int not null unique,name varchar(20));
Query OK, 0 rows affected (0.03 sec)

mysql> desc student3;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> drop student3;
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 'student3' at line 1
mysql> drop table student3;
Query OK, 0 rows affected (0.01 sec)

mysql> create table student3 (id int not null unique, name varchar(20) default '未知');
Query OK, 0 rows affected (0.02 sec)

mysql> desc student3;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | 未知    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

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

mysql> insert into student3 (id) values(1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from student3;
+----+------+
| id | name |
+----+------+
|  1 | 未知 |
+----+------+
1 row in set (0.00 sec)

mysql> insert into student3 (id,name) values(1,'z2');
ERROR 1062 (23000): Duplicate entry '1' for key 'student3.id'
mysql> insert into student3 (id,name) values(2,'z2');
Query OK, 1 row affected (0.00 sec)

mysql> select * from student3;
+----+------+
| id | name |
+----+------+
|  1 | 未知 |
|  2 | z2   |
+----+------+
2 rows in set (0.00 sec)

mysql> drop table student;
Query OK, 0 rows affected (0.02 sec)

mysql> create table student (id int primary key,name varchar(20));
Query OK, 0 rows affected (0.02 sec)

mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into student values(1);
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> insert into student (id) values(1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into student (id) values(2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into student (id) values(1);
ERROR 1062 (23000): Duplicate entry '1' for key 'student.PRIMARY'
mysql> insert into sudent values(null,'z5');
ERROR 1146 (42S02): Table 'z1.sudent' doesn't exist
mysql> insert into sudent values(null,'z5');
ERROR 1146 (42S02): Table 'z1.sudent' doesn't exist
mysql> insert into sudent values(3,'z3');
ERROR 1146 (42S02): Table 'z1.sudent' doesn't exist
mysql> insert into student values(null,'z5');
ERROR 1048 (23000): Column 'id' cannot be null
mysql> drop table student;
Query OK, 0 rows affected (0.01 sec)

mysql> create table student(id int primary key,name varchar(20) primary key);
ERROR 1068 (42000): Multiple primary key defined
mysql> drop table student;
ERROR 1051 (42S02): Unknown table 'z1.student'
mysql> create table student (id int primary key auto_increment,name varchar(20));
Query OK, 0 rows affected (0.03 sec)

mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int         | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

mysql> insert into student name values 'z1';
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 'name values 'z1'' at line 1
mysql> insert into student name values('z1');
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 'name values('z1')' at line 1
mysql> insert into student (name) values('z1');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student (name) values('z2');
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+----+------+
| id | name |
+----+------+
|  1 | z1   |
|  2 | z2   |
+----+------+
2 rows in set (0.00 sec)

mysql> insert into student (id,name) values(null,'z3');
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+----+------+
| id | name |
+----+------+
|  1 | z1   |
|  2 | z2   |
|  3 | z3   |
+----+------+
3 rows in set (0.00 sec)

mysql> insert into student (id,name) values(6,'z6');
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+----+------+
| id | name |
+----+------+
|  1 | z1   |
|  2 | z2   |
|  3 | z3   |
|  6 | z6   |
+----+------+
4 rows in set (0.00 sec)

mysql> insert into student (id,name) values(null,'z7');
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+----+------+
| id | name |
+----+------+
|  1 | z1   |
|  2 | z2   |
|  3 | z3   |
|  6 | z6   |
|  7 | z7   |
+----+------+
5 rows in set (0.00 sec)

mysql> insert into student (id,name) values(null,'z8');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student (id,name) values(null,'z9');
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+----+------+
| id | name |
+----+------+
|  1 | z1   |
|  2 | z2   |
|  3 | z3   |
|  6 | z6   |
|  7 | z7   |
|  8 | z8   |
|  9 | z9   |
+----+------+
7 rows in set (0.00 sec)

mysql> delete from student where id = 9;
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+----+------+
| id | name |
+----+------+
|  1 | z1   |
|  2 | z2   |
|  3 | z3   |
|  6 | z6   |
|  7 | z7   |
|  8 | z8   |
+----+------+
6 rows in set (0.00 sec)

mysql> insert into student (id,name) values(null,'z9');
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+----+------+
| id | name |
+----+------+
|  1 | z1   |
|  2 | z2   |
|  3 | z3   |
|  6 | z6   |
|  7 | z7   |
|  8 | z8   |
| 10 | z9   |
+----+------+
7 rows in set (0.00 sec)

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

mysql> select * from student;
+----+------+
| id | name |
+----+------+
|  1 | z1   |
|  2 | z2   |
|  3 | z3   |
|  6 | z6   |
|  7 | z7   |
|  8 | z8   |
| 10 | z10  |
+----+------+
7 rows in set (0.00 sec)