mysql基本操作，创建、插入、修改默认字符集

来源：互联网发布：js导出excel 数字格式编辑：程序博客网时间：2024/06/10 09:41

- mysql创建表
- mysql 插入
- 遇到的问题

1. mysql创建表

create table "tablename"(column_name column type other);

NOT NULL,字段非空
PRIMARY KEY,设置为主键
AUTO_INCREMENT,设置为自增字段

eg：create table student(id int PRIMARY KEY AUTO_INCREMENT,age tinyint,name varchar(10) NOT NULL )

2. mysql 插入

1. 插入全部字段insert into student values(NULL,19,张家岭);查看表MariaDB [jlzhang]> select * from student;+----+------+-----------+| id | age  | name      |+----+------+-----------+|  1 |   19 | 张家岭    |+----+------+-----------+1 row in set (0.00 sec)2. 插入特定字段intsert into table(column) values();MariaDB [jlzhang]> insert into student(name) values("张家岭");Query OK, 1 row affected (0.01 sec)MariaDB [jlzhang]> select *from st;+----+------+--------------------------------+| id | age  | name                           |+----+------+--------------------------------+|  1 |   19 | 张家岭                         ||  2 |   19 | 张家岭实打实的杀毒啊           ||  3 |   19 | 张家岭实打实的杀毒啊           ||  4 |   20 | 张家岭                         ||  5 | NULL | 张家岭                         |+----+------+--------------------------------+

3. 遇到的问题

插入name字段时，编码不识别
解决方法：修改表的字符集，修改数据库的默认字符集

1.查看数据库默认字符集show variables like 'character%';2.查看表的字符集MariaDB [jlzhang]> show create table student    -> ;+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table   | Create Table                                                                                                                                                                                                                         |+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| student | CREATE TABLE `student` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `age` tinyint(4) DEFAULT NULL,  `name` varchar(10) CHARACTER SET latin1 DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 |+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)3.设置表的字符集MariaDB [jlzhang]> set names utf8    -> ;Query OK, 0 rows affected (0.00 sec)4.查看数据库的字符集MariaDB [jlz]> show create database jlz;+----------+--------------------------------------------------------------+| Database | Create Database                                              |+----------+--------------------------------------------------------------+| jlz      | CREATE DATABASE `jlzhang` /*!40100 DEFAULT CHARACTER SET utf8 */ |+----------+--------------------------------------------------------------+1 row in set (0.00 sec)5.修改数据库的默认字符集,在/etc/my.cnf加入如下内容[client]default-character-set=utf8[mysqld]character-set-server=utf8

0 0