初识mysql，ddl，dml相关操作

加入收藏设为首页

SQL Life: 初识mysql，ddl，dml相关操作
时间：2017/12/8 13:17:08 作者：solgle 来源：solgle.com 查看：242 评论：0
内容摘要：初识mysql，ddl，dml相关操作以及相关存储引擎
初识mysql，ddl，dml相关操作

---innodb存储引擎，支持事务

mysql> use test

Database changed

mysql> create table ai

( id bigint auto_increment,

name varchar(20),

sex varchar(5),

primary key(id)

)engine=innodb default charset=utf8;

Query OK, 0 rows affected (0.14 sec)

mysql> select * from ai;

Empty set (0.00 sec)

mysql> desc ai;

+-------+-------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+----------------+

| id | bigint(20) | NO | PRI | NULL | auto_increment |

| name | varchar(20) | YES | | NULL | |

| sex | varchar(5) | YES | | NULL | |

+-------+-------------+------+-----+---------+----------------+

3 rows in set (0.01 sec)

mysql>

mysql> insert into ai(name,sex) values('jenkin','F');

Query OK, 1 row affected (0.00 sec)

mysql> select * from ai;

+----+--------+------+

| id | name | sex |

+----+--------+------+

| 1 | jenkin | F |

+----+--------+------+

1 row in set (0.00 sec)

mysql>

mysql> insert into ai(name,sex) values('solgle','m');

Query OK, 1 row affected (0.00 sec)

mysql> select * from ai;

+----+--------+------+

| id | name | sex |

+----+--------+------+

| 1 | jenkin | F |

| 2 | solgle | m |

+----+--------+------+

2 rows in set (0.00 sec)

mysql>

-----创建memory存储引擎用于内存中使用的表，每个memory表只对应一个磁盘文件.frm，服务一旦关闭，表中数据就会丢掉。

mysql> create table temp_ai engine memory select * from ai;

Query OK, 2 rows affected (0.17 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from temp_ai;

+----+--------+------+

| id | name | sex |

+----+--------+------+

| 1 | jenkin | F |

| 2 | solgle | m |

+----+--------+------+

2 rows in set (0.00 sec)

mysql>

mysql> exit

Bye

[root@nameNode ~]# service mysqld restart;

Stopping mysqld: [ OK ]

Starting mysqld: [ OK ]

[root@nameNode ~]# mysql -uroot -p

......

mysql> use test

Database changed

mysql> show tables;

+----------------+

| Tables_in_test |

+----------------+

| ai |

| temp_ai |

+----------------+

2 rows in set (0.00 sec)

mysql> select * from temp_ai;

Empty set (0.00 sec) ---重启mysql后，表中数据消失

mysql>

--创建myisam是默认mysql的存储引擎，不支持事务，不支持外键。

mysql> create table myai

( id bigint auto_increment,

city varchar(20),

country varchar(30),

primary key (id)

) engine myisam;

Query OK, 0 rows affected (0.00 sec)

mysql> desc myai;

+---------+-------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+---------+-------------+------+-----+---------+----------------+

| id | bigint(20) | NO | PRI | NULL | auto_increment |

| city | varchar(20) | YES | | NULL | |

| country | varchar(30) | YES | | NULL | |

+---------+-------------+------+-----+---------+----------------+

3 rows in set (0.00 sec)

mysql>

mysql> insert into myai (city,country) values('成都','china');

Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from myai;;

+----+------+---------+

| id | city | country |

+----+------+---------+

| 1 | ??? | china |

+----+------+---------+

1 row in set (0.00 sec)

---提示中文乱码，修改字符集

mysql> alter table myai default charset utf8;

Query OK, 1 row affected (0.17 sec)

Records: 1 Duplicates: 0 Warnings: 0

mysql> show variables like 'character_set_server';

+----------------------+--------+

| Variable_name | Value |

+----------------------+--------+

| character_set_server | latin1 |

+----------------------+--------+

--编码不一致，修改my.cnf在[mysqld]下加上character-set-server=utf8

mysql> show variables like 'character_set_database';

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id: 2

Current database: test

+------------------------+-------+

| Variable_name | Value |

+------------------------+-------+

| character_set_database | utf8 |

+------------------------+-------+

1 row in set (0.00 sec)

---修改my.cnf后重启服务器，发现还是中文乱码，难道这样修改没有起到作用？

mysql> show create table myai; ---查看表的定义信息

| Table | Create Table -----------------------------------+

| myai | CREATE TABLE `myai` (

`id` bigint(20) NOT NULL AUTO_INCREMENT,

`city` varchar(20) CHARACTER SET latin1 DEFAULT NULL, ---此发现字段的定义信息latin1

`country` varchar(30) CHARACTER SET latin1 DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 |

+-------

1 row in set (0.00 sec)

--修改mysql表的字段的编码

mysql> alter table myai modify city varchar(20) character set utf8;

Query OK, 4 rows affected (0.07 sec)

Records: 4 Duplicates: 0 Warnings: 0

mysql> alter table myai modify country varchar(30) character set utf8;

Query OK, 4 rows affected (0.01 sec)

Records: 4 Duplicates: 0 Warnings: 0

mysql> show create table myai \G;

*************************** 1. row ***************************

Table: myai

Create Table: CREATE TABLE `myai` (

`id` bigint(20) NOT NULL AUTO_INCREMENT,

`city` varchar(20) DEFAULT NULL,

`country` varchar(30) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

ERROR:

No query specified

mysql> ----经过验证，重新写入的utf8汉字就是正常的了

mysql> select * from myai;

+----+------+---------+

| id | city | country |

+----+------+---------+

| 1 | ??? | china |

| 2 | ?? | china |

| 3 | ?? | china |

| 4 | ?? | china |

+----+------+---------+

4 rows in set (0.00 sec)

mysql> insert into myai (city,country) values('成都','china');

Query OK, 1 row affected (0.00 sec)

mysql> select * from myai;

+----+--------+---------+

| id | city | country |

+----+--------+---------+

| 1 | ??? | china |

| 2 | ?? | china |

| 3 | ?? | china |

| 4 | ?? | china |

| 5 | 成都 | china |

+----+--------+---------+

5 rows in set (0.00 sec)

mysql>

标签：mysql存储引擎表


solgle.com 版权所有，欢迎分享！！！

上一篇：MySql的存储过程及函数
下一篇：MySql的视图操作及使用

相关文章

相关评论

本类更新

本类推荐

本类排行

: 国家数据 | 中证指数 | Oracle官方文档 | 沪深行情 | 流量统计 | 深交所 | 上交所 | 中国证券登记 | 同业拆放利率 | 巨潮资讯 | 指数估值 | 看财报 |

联系我们 - 关于我们 - 网站同盟