mysql数据库和数据库表操作语句学习笔记

数据库操作语句

1.创建数据库

creat database ‘数据库名’

mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.00 sec)

不过默认创建的数据库编码格式为latin1，在mysql中存储和显示中文时会产生乱码

查询数据库编码格式
mysql> show variables like 'character_set_database';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| character_set_database | latin1 |
+------------------------+--------+
1 row in set (0.00 sec)

我们可以在创建数据库的时候指定编码格式
例如：

mysql> CREATE DATABASE test1 charset=utf8mb4;
Query OK, 1 row affected (0.00 sec)

在这里插入图片描述

另外数据库不能重复创建，会报错（ERROR 1007 (HY000): Can’t create database ‘test’; database exists）

2.查看数据库

mysql> show databases; 用来查看所有数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
| test1              |
+--------------------+
6 rows in set (0.00 sec)

3.切换数据库

选择指定的数据库
use
例如：

mysql> use test1;
Database changed

执行以上命令后，就选择了test1这个数据库，后续的操作都会在test1数据库中进行操作

4.删除数据库

DROP DATABASE 语句用于删除数据库

mysql> drop database test;
Query OK, 0 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test1              |
+--------------------+
5 rows in set (0.00 sec)

5.修改数据库

只能修改字符集

mysql> alter database test charset=utf8mb4;
Query OK, 1 row affected (0.00 sec)

在这里插入图片描述

表的操作语句

1.创建表

表由行和列组成，每个表都必须有个表名

CREATE TABLE table_name
(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
....
);

column_name 参数规定表中列的名称。
data_type 参数规定列的数据类型（例如 varchar、integer、decimal、date 等等）。
size 参数规定表中列的最大长度。
例如：
创建一个学生表，包含三列，ID，姓名，年龄
在创建表之前要先进入到相应的数据库中，在数据库中创建表

mysql> use test1;
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql> create table student (id int ,name varchar(128),age int);
Query OK, 0 rows affected (0.02 sec)
id列和age列的数据类型是int，包含整数，name列的数据类型是varchar，包含字符

2.查询表

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

3.删除表

DROP TABLE table_name

mysql> drop table student;
Query OK, 0 rows affected (0.03 sec)
mysql> show tables;
Empty set (0.00 sec)

补充：如果只删除表内的数据，不删除表本身，使用TRUNCATE TABLE语句

mysql> truncate table student;
Query OK, 0 rows affected (0.01 sec)

4.查询表结构的详细信息

describe(或者desc)

mysql> describe student;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  |     | NULL    |       |
| name  | varchar(128) | YES  |     | NULL    |       |
| age   | int(11)      | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

5.查询建表语句

show create table

mysql> show create table student;
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                   |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(128) DEFAULT NULL,
  `age` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

6.修改表

alter table 原表名 rename 新表名

mysql> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| student         |
+-----------------+
1 row in set (0.00 sec)
mysql> alter table student rename teacher;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| teacher         |
+-----------------+
1 row in set (0.00 sec)

也可以将表移动到另一个数据库中
alter table rename to newdatabase.newtablename;
例如：将test1中的teacher表移动到test库中并取名为student

mysql> alter table teacher rename to test.student;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
Empty set (0.00 sec)
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| student        |
+----------------+
1 row in set (0.00 sec)

7.修改表字段

增加一个新字段

alter table add 字段名数据类型 [属性] ;
例：新增一个分数字段

mysql> alter table student add score float;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

增加一个新字段，并放在首位

alter table add 字段名数据类型 [属性] first ;
例：新增一个class字段，并放到第一位

mysql> alter table student add class  int first ;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> describe student;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| class | int(11)      | YES  |     | NULL    |       |
| id    | int(11)      | YES  |     | NULL    |       |
| name  | varchar(128) | YES  |     | NULL    |       |
| age   | int(11)      | YES  |     | NULL    |       |
| score | float        | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

新增一个字段，并放到指定字段后面

alter table add 字段名数据类型 [属性] after 指定字段 ;
例：加一个aaa字段放到age后面

mysql> alter table student add aaa int after age ;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> describe student;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| class | int(11)      | YES  |     | NULL    |       |
| id    | int(11)      | YES  |     | NULL    |       |
| name  | varchar(128) | YES  |     | NULL    |       |
| age   | int(11)      | YES  |     | NULL    |       |
| aaa   | int(11)      | YES  |     | NULL    |       |
| score | float        | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

修改字段属性

alter table modify 字段名数据类型 [属性] ;
例：将原本class的int类型改为varchar属性并限制最大长度为12

mysql> alter table student modify class varchar(12) ;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> describe student;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| class | varchar(12)  | YES  |     | NULL    |       |
| id    | int(11)      | YES  |     | NULL    |       |
| name  | varchar(128) | YES  |     | NULL    |       |
| age   | int(11)      | YES  |     | NULL    |       |
| aaa   | int(11)      | YES  |     | NULL    |       |
| score | float        | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

修改字段名

alter table change 原字段名新字段名数据类型 [属性] ;
例：将aaa字段改为 bbb字段

mysql> alter table student change aaa bbb int;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> describe student;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| class | varchar(12)  | YES  |     | NULL    |       |
| id    | int(11)      | YES  |     | NULL    |       |
| name  | varchar(128) | YES  |     | NULL    |       |
| age   | int(11)      | YES  |     | NULL    |       |
| bbb   | int(11)      | YES  |     | NULL    |       |
| score | float        | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

修改字段位置

alter table change 原字段名新字段名数据类型 [属性] after 指定字段;
例：将bbb字段改为ccc 并放到name后面

mysql> alter table student change bbb ccc  int after name;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> describe student;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| class | varchar(12)  | YES  |     | NULL    |       |
| id    | int(11)      | YES  |     | NULL    |       |
| name  | varchar(128) | YES  |     | NULL    |       |
| ccc   | int(11)      | YES  |     | NULL    |       |
| age   | int(11)      | YES  |     | NULL    |       |
| score | float        | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

删除字段

alter table drop <字段>
例：删除ccc字段

mysql> alter table student  drop ccc;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> describe student;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| class | varchar(12)  | YES  |     | NULL    |       |
| id    | int(11)      | YES  |     | NULL    |       |
| name  | varchar(128) | YES  |     | NULL    |       |
| age   | int(11)      | YES  |     | NULL    |       |
| score | float        | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)