mysql插入数据综合实训

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bms                |
| db_com             |
| mysql              |
| performance_schema |
| sys                |
| test1              |
+--------------------+
7 rows in set (0.00 sec)

mysql> create database if not exists db_test1;
Query OK, 1 row affected (0.00 sec)

mysql> use db_test1;
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| db_test1   |
+------------+
1 row in set (0.00 sec)

mysql> create table user (id int  primary key auto_increment,name varchar(60) un
ique not null,score float default 0) character set utf8mb4;
Query OK, 0 rows affected (0.14 sec)

mysql> insert into user (name,xingbie,score) values ('张三','男',89),('李四','男
',99),('小红','女',100),('嚣张','男',150);
ERROR 1054 (42S22): Unknown column 'xingbie' in 'field list'
mysql> alter table user add xingbie varchar(30) not null;
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into user (name,xingbie,score) values ('张三','男',89),('李四','男
',99),('小红','女',100),('嚣张','男',150);
Query OK, 4 rows affected (0.03 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> alter table user modify xingbie varchar(30) not null after name;
Query OK, 0 rows affected (0.32 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into user (name,xingbie,score) values ('张三','男',89),('李四','男
',99),('小红','女',100),('嚣张','男',150);
ERROR 1062 (23000): Duplicate entry '张三' for key 'name'
mysql> select * from user;
+----+--------+---------+-------+
| id | name   | xingbie | score |
+----+--------+---------+-------+
|  1 | 张三   | 男      |    89 |
|  2 | 李四   | 男      |    99 |
|  3 | 小红   | 女      |   100 |
|  4 | 嚣张   | 男      |   150 |
+----+--------+---------+-------+
4 rows in set (0.00 sec)

mysql>

通过character set来设置数据表字符集

如果已经创建数据表可以通过 convert to character set 来修改