MySQL Life

初识mysql,ddl,dml相关操作

时间:2017-12-8 13:17:08  作者:solgle  来源:solgle.com  查看:58  评论: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 版权所有,欢迎分享!!!

相关文章
    相关评论
       Copyright © 2013-2020 solgle.com,All rights reserved.[solgle.com] 公安机关备案号:51010802000219
    Email:solgle@solgle.com; weixin:cd1008610000 ICP:蜀ICP备14011070号-1