MySQL数据库基本操作

October 11, 2020 9132

创建数据库

1
2
mysql> create database text;
Query OK, 1 rows affected (0.04 sec)

查看所有数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| test               |
| world              |
+--------------------+
7 rows in set (0.00 sec)

连接一个数据库

1
2
mysql> use test;
Database changed

查看当前数据库

1
2
3
4
5
6
7
mysql> select database();
+------------+
| database() |
+------------+
| test       |
+------------+
1 row in set (0.00 sec)

删除数据库

1
2
mysql> drop database text;
Query OK, 0 rows affected (0.04 sec)

在数据库中创建表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
mysql> create database test; #创建数据库
Query OK, 1 row affected (0.10 sec)
mysql> use test； #连接数据库
Database changed #连接成功
mysql> create table if not exists exam_score(     #创建了一个叫exam_score的成绩表
    -> id int(4) not null primary key auto_increment,          
    #id为自增主键：对于一个表中的主键可以定义为自增长的，当主键定义为自增长后，那么这个主键的值就不需要用户输入数据，而由数据库系统根据定义自动赋值。当增加一条记录时，主键自动的以相同的步长来进行增长，这个步长是在建表的时候用户自己定义的。一般自增长数据只能是数值类型,如: int、 bigint、 decimal等，这里的int(4)表示Int 4B，存储-2^31~2^31-1范围的整数。

    -> name char(20) not null, #char(20)表示长度为20的字符串
    -> score double); #double类型表示双精度浮点数值
Query OK, 0 rows affected, 1 warning (0.12 sec)

mysql> show columns from exam_score; #展示表的结构
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int      | NO   | PRI | NULL    | auto_increment |
| name  | char(20) | NO   |     | NULL    |                |
| score | double   | YES  |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
3 rows in set (0.07 sec)

mysql> show tables; #显示当前存在的表
+----------------+
| Tables_in_test |
+----------------+
| exam_score     |
+----------------+
1 row in set (0.04 sec)

mysql> drop table exam_score; #删除表
Query OK, 0 rows affected (0.06 sec)

插入数据和查找数据

插入数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql> create table if not exists exam_score(
    -> id int(4) not null primary key auto_increment,
    -> name char(20) not null,
    -> score double(16,2));
Query OK, 0 rows affected, 2 warnings (0.09 sec)

mysql> show columns from exam_score;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int          | NO   | PRI | NULL    | auto_increment |
| name  | char(20)     | NO   |     | NULL    |                |
| score | double(16,2) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.04 sec)

mysql> insert into exam_score values (1,'zhao',95.55),(2,'qian',80.95),(3,'sun',70.10),(4,'li',60.20); #插入数据
Query OK, 4 rows affected (0.04 sec)
Records: 4  Duplicates: 0  Warnings: 0

获取数据

获取前两个两位同学成绩

方法一
1
2
3
4
5
6
7
8
mysql> select * from exam_score order by id limit 0,2; #order by 子句：根据...排序，默认按照升序排列
+----+------+-------+
| id | name | score |
+----+------+-------+
|  1 | zhao | 95.55 |
|  2 | qian | 80.95 |
+----+------+-------+
2 rows in set (0.00 sec)

降序排列：select * from 表名 order by 字段名 desc；（升序的话，后接asc）

方法二
1
2
3
4
5
6
7
8
mysql> select * from exam_score limit 0,2;
+----+------+-------+
| id | name | score |
+----+------+-------+
|  1 | zhao | 95.55 |
|  2 | qian | 80.95 |
+----+------+-------+
2 rows in set (0.00 sec)

获取某位同学成绩（where子句）

方法一
1
2
3
4
5
6
7
mysql> select * from exam_score where name = 'li';
+----+------+-------+
| id | name | score |
+----+------+-------+
|  4 | li   | 60.20 |
+----+------+-------+
1 row in set (0.00 sec)
方法二
1
2
3
4
5
6
7
mysql> select * from exam_score where id = 4;
+----+------+-------+
| id | name | score |
+----+------+-------+
|  4 | li   | 60.20 |
+----+------+-------+
1 row in set (0.00 sec)

查询同名同学成绩

1
2
3
4
5
6
7
8
9
10
mysql> insert into exam_score values (5,'zhao',45.50);
Query OK, 1 row affected (0.04 sec)

mysql> select * from exam_score where name = 'zhao' and score < 60.00; #用and链接两个条件，表示且，也可以用or链接，表示或。
+----+------+-------+
| id | name | score |
+----+------+-------+
|  5 | zhao | 45.50 |
+----+------+-------+
1 row in set (0.04 sec)

修改数据和删除数据

修改数据

修改前

1
2
3
4
5
6
7
8
9
10
11
mysql> select * from exam_score;
+----+------+-------+
| id | name | score |
+----+------+-------+
|  1 | zhao | 95.55 |
|  2 | qian | 80.95 |
|  3 | sun  | 70.10 |
|  4 | li   | 60.20 |
|  5 | zhao | 45.50 |
+----+------+-------+
5 rows in set (0.00 sec)

修改方法

1
2
3
mysql> update exam_score set score = 65.20 where id = 4;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

修改后

1
2
3
4
5
6
7
8
9
10
11
mysql> select * from exam_score;
+----+------+-------+
| id | name | score |
+----+------+-------+
|  1 | zhao | 95.55 |
|  2 | qian | 80.95 |
|  3 | sun  | 70.10 |
|  4 | li   | 65.20 |
|  5 | zhao | 45.50 |
+----+------+-------+
5 rows in set (0.00 sec)

修改多个字段数据，赋值语句中间用逗号（,）隔开

替换数据

修改5号zhao同学的名字

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> update exam_score set name = replace(name,'zhao','jack') where id = 5;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from exam_score;
+----+------+-------+
| id | name | score |
+----+------+-------+
|  1 | zhao | 95.55 |
|  2 | qian | 80.95 |
|  3 | sun  | 70.10 |
|  4 | li   | 65.20 |
|  5 | jack | 45.50 |
+----+------+-------+
5 rows in set (0.04 sec)

删除数据

单个删除

5号同学转走了

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> delete from exam_score where id = 5;
Query OK, 1 row affected (0.04 sec)

mysql> select * from exam_score;
+----+------+-------+
| id | name | score |
+----+------+-------+
|  1 | zhao | 95.55 |
|  2 | qian | 80.95 |
|  3 | sun  | 70.10 |
|  4 | li   | 65.20 |
+----+------+-------+
4 rows in set (0.04 sec)

全部删除

1
2
3
4
5
mysql> delete from exam_score; #delete 清空表数据，表还在；drop 删除整个表。 
Query OK, 4 rows affected (0.01 sec)

mysql> select * from exam_score;
Empty set (0.04 sec)

修改表结构

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
mysql> desc exam_score;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int          | NO   | PRI | NULL    | auto_increment |
| name  | char(20)     | NO   |     | NULL    |                |
| score | double(16,2) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.05 sec)

mysql> alter table exam_score add other int(4) default 10;
Query OK, 0 rows affected, 1 warning (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> desc exam_score;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int          | NO   | PRI | NULL    | auto_increment |
| name  | char(20)     | NO   |     | NULL    |                |
| score | double(16,2) | YES  |     | NULL    |                |
| other | int          | YES  |     | 10      |                |
+-------+--------------+------+-----+---------+----------------+
4 rows in set (0.04 sec)

修改格式：alter table 表名 add 字段名 数据类型 default 默认值；

表格中的数据改为文字

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> alter table exam_score change other evaluation char(20) default 'normal';
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc exam_score;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int          | NO   | PRI | NULL    | auto_increment |
| name       | char(20)     | NO   |     | NULL    |                |
| score      | double(16,2) | YES  |     | NULL    |                |
| evaluation | char(20)     | YES  |     | normal  |                |
+------------+--------------+------+-----+---------+----------------+
4 rows in set (0.04 sec)

修改格式：alter table 表名 change 旧字段 新字段 新数据类型 default 默认值;

删除字段

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> alter table exam_score drop column evaluation;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc exam_score;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int          | NO   | PRI | NULL    | auto_increment |
| name  | char(20)     | NO   |     | NULL    |                |
| score | double(16,2) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.05 sec)

修改格式：alter table 表名 drop column 字段名;

修改表名

1
2
3
4
5
6
7
8
9
10
mysql> rename table exam_score to score;
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| score          |
+----------------+
1 row in set (0.04 sec)

修改格式：rename table 旧表名 to 新表名；