时间:2021-07-01 10:21:17 帮助过:23人阅读
- 语法:
- create table 表名(
- 字段名1 类型[(宽度) 约束条件],
- 字段名2 类型[(宽度) 约束条件],
- 字段名3 类型[(宽度) 约束条件]
- );
- 注意:
- 1. 在同一张表中,字段名是不能相同
- 2. 宽度和约束条件可选
- 3. 字段名和类型是必须的
- MariaDB [(none)]><span style="color: #000000"> create database db1 charset utf8;
- MariaDB [(none)]><span style="color: #000000"> use db1;
- MariaDB [db1]><span style="color: #000000"> create table t1(
- -><span style="color: #000000"> id int,
- -> name varchar(50<span style="color: #000000">),
- -> sex enum(<span style="color: #800000">‘<span style="color: #800000">male<span style="color: #800000">‘,<span style="color: #800000">‘<span style="color: #800000">female<span style="color: #800000">‘<span style="color: #000000">),
- -> age int(3<span style="color: #000000">)
- -><span style="color: #000000"> );
- MariaDB [db1]> show tables; <span style="color: #008000">#<span style="color: #008000">查看db1库下所有表名
- <span style="color: #000000">
- MariaDB [db1]><span style="color: #000000"> desc t1;
- +-------+-----------------------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +-------+-----------------------+------+-----+---------+-------+
- | id | int(11) | YES | | NULL | |
- | name | varchar(50) | YES | | NULL | |
- | sex | enum(<span style="color: #800000">‘<span style="color: #800000">male<span style="color: #800000">‘,<span style="color: #800000">‘<span style="color: #800000">female<span style="color: #800000">‘) | YES | | NULL | |
- | age | int(3) | YES | | NULL | |
- +-------+-----------------------+------+-----+---------+-------+<span style="color: #000000">
- MariaDB [db1]> select id,name,sex,age <span style="color: #0000ff">from<span style="color: #000000"> t1;
- Empty set (0.00<span style="color: #000000"> sec)
- MariaDB [db1]> select * <span style="color: #0000ff">from<span style="color: #000000"> t1;
- Empty set (0.00<span style="color: #000000"> sec)
- MariaDB [db1]> select id,name <span style="color: #0000ff">from<span style="color: #000000"> t1;
- Empty set (0.00 sec)</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>
- MariaDB [db1]><span style="color: #000000"> insert into t1 values
- -> (1,<span style="color: #800000">‘<span style="color: #800000">egon<span style="color: #800000">‘,18,<span style="color: #800000">‘<span style="color: #800000">male<span style="color: #800000">‘<span style="color: #000000">),
- -> (2,<span style="color: #800000">‘<span style="color: #800000">alex<span style="color: #800000">‘,81,<span style="color: #800000">‘<span style="color: #800000">female<span style="color: #800000">‘<span style="color: #000000">)
- -><span style="color: #000000"> ;
- MariaDB [db1]> select * <span style="color: #0000ff">from<span style="color: #000000"> t1;
- +------+------+------+--------+
- | id | name | age | sex |
- +------+------+------+--------+
- | 1 | egon | 18 | male |
- | 2 | alex | 81 | female |
- +------+------+------+--------+<span style="color: #000000">
- MariaDB [db1]><span style="color: #000000"> insert into t1(id) values
- -> (3<span style="color: #000000">),
- -> (4<span style="color: #000000">);
- MariaDB [db1]> select * <span style="color: #0000ff">from<span style="color: #000000"> t1;
- +------+------+------+--------+
- | id | name | age | sex |
- +------+------+------+--------+
- | 1 | egon | 18 | male |
- | 2 | alex | 81 | female |
- | 3 | NULL | NULL | NULL |
- | 4 | NULL | NULL | NULL |
- +------+------+------+--------+<span style="color: #000000"><br></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>
- mysql><span style="color: #000000"> create database db1 charset latin1;
- mysql><span style="color: #000000"> use db1;
- mysql> create table t1(name varchar(20<span style="color: #000000">));
- mysql> show create table t1; <span style="color: #008000">#<span style="color: #008000">查看表,发现表默认与数据db1的字符编码一致
- mysql> insert into t1 values(<span style="color: #800000">‘<span style="color: #800000">林<span style="color: #800000">‘); <span style="color: #008000">#<span style="color: #008000">插入中文出错,因为latin1不支持中文
- ERROR 1366<span style="color: #000000"> (HY000):
- mysql>
- <span style="color: #008000">#<span style="color: #008000">解决方法一:删除库db1,重建db1,字符编码指定为utf8
- <span style="color: #008000">#<span style="color: #008000">解决方法二:修改
- mysql> alter table t1 charset utf8; <span style="color: #008000">#<span style="color: #008000">修改表t1的编码
- mysql> insert into t1 values(<span style="color: #800000">‘<span style="color: #800000">林<span style="color: #800000">‘); <span style="color: #008000">#<span style="color: #008000">虽然t1的编码改了,但是t1的字段name仍然是按照latin1编码创建的
- ERROR 1366<span style="color: #000000"> (HY000):
- mysql> alter table t1 modify name varchar(20); <span style="color: #008000">#<span style="color: #008000">需要重新定义下字段name
- mysql> insert into t1 values(<span style="color: #800000">‘<span style="color: #800000">林<span style="color: #800000">‘<span style="color: #000000">);
- mysql> select * <span style="color: #0000ff">from<span style="color: #000000"> t1;
- +------+
- | name |
- +------+
- | 林 |
- +------+<span style="color: #000000">
- ps:不要忘记将数据库编码也改成utf8,这样以后在该数据库下创建表时,都默认utf8编码了
- <span style="color: #008000">#<span style="color: #008000">配置文件:http://blog.csdn.net/yipiankongbai/article/details/16937815</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>
- <span style="color: #008000">#<span style="color: #008000">1. 修改配置文件
- <span style="color: #000000">[mysqld]
- default-character-set=<span style="color: #000000">utf8
- [client]
- default-character-set=<span style="color: #000000">utf8
- [mysql]
- default-character-set=<span style="color: #000000">utf8
- <span style="color: #008000">#<span style="color: #008000">mysql5.5以上:修改方式有所改动
- <span style="color: #000000"> [mysqld]
- character-set-server=<span style="color: #000000">utf8
- collation-server=<span style="color: #000000">utf8_general_ci
- [client]
- default-character-set=<span style="color: #000000">utf8
- [mysql]
- default-character-set=<span style="color: #000000">utf8
- <span style="color: #008000">#<span style="color: #008000">2. 重启服务<span style="color: #008000">
- #<span style="color: #008000">3. 查看修改结果:
- <span style="color: #000000">\s
- show variables like <span style="color: #800000">‘<span style="color: #800000">%char%<span style="color: #800000">‘</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>
注意注意注意:表中的最后一个字段不要加逗号
- MariaDB [db1]> describe t1; #查看表结构,可简写为desc 表名
- +-------+-----------------------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +-------+-----------------------+------+-----+---------+-------+
- | id | int(11) | YES | | NULL | |
- | name | varchar(50) | YES | | NULL | |
- | sex | enum(‘male‘,‘female‘) | YES | | NULL | |
- | age | int(3) | YES | | NULL | |
- +-------+-----------------------+------+-----+---------+-------+
- MariaDB [db1]> show create table t1\G; #查看表详细结构,可加\G
http://www.cnblogs.com/liluning/p/7489710.html
http://www.cnblogs.com/liluning/p/7489852.html
- 语法:
- 1. 修改表名
- ALTER TABLE 表名
- RENAME 新表名;
- 2. 增加字段
- ALTER TABLE 表名
- ADD 字段名 数据类型 [完整性约束条件…],
- ADD 字段名 数据类型 [完整性约束条件…];
- ALTER TABLE 表名
- ADD 字段名 数据类型 [完整性约束条件…] FIRST;
- ALTER TABLE 表名
- ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名;
- 3. 删除字段
- ALTER TABLE 表名
- DROP 字段名;
- 4. 修改字段
- ALTER TABLE 表名
- MODIFY 字段名 数据类型 [完整性约束条件…];
- ALTER TABLE 表名
- CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
- ALTER TABLE 表名
- CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];
- 示例:
- 1. 修改存储引擎
- mysql> alter table service
- -> engine=innodb;
- 2. 添加字段
- mysql> alter table student10
- -> add name varchar(20) not null,
- -> add age int(3) not null default 22;
- mysql> alter table student10
- -> add stu_num varchar(10) not null after name; //添加name字段之后
- mysql> alter table student10
- -> add sex enum(‘male‘,‘female‘) default ‘male‘ first; //添加到最前面
- 3. 删除字段
- mysql> alter table student10
- -> drop sex;
- mysql> alter table service
- -> drop mac;
- 4. 修改字段类型modify
- mysql> alter table student10
- -> modify age int(3);
- mysql> alter table student10
- -> modify id int(11) not null primary key auto_increment; //修改为主键
- 5. 增加约束(针对已有的主键增加auto_increment)
- mysql> alter table student10 modify id int(11) not null primary key auto_increment;
- ERROR 1068 (42000): Multiple primary key defined
- mysql> alter table student10 modify id int(11) not null auto_increment;
- Query OK, 0 rows affected (0.01 sec)
- Records: 0 Duplicates: 0 Warnings: 0
- 6. 对已经存在的表增加复合主键
- mysql> alter table service2
- -> add primary key(host_ip,port);
- 7. 增加主键
- mysql> alter table student1
- -> modify name varchar(10) not null primary key;
- 8. 增加主键和自动增长
- mysql> alter table student1
- -> modify id int not null primary key auto_increment;
- 9. 删除主键
- a. 删除自增约束
- mysql> alter table student10 modify id int(11) not null;
- b. 删除主键
- mysql> alter table student10
- -> drop primary key;
- 复制表结构+记录 (key不会复制: 主键、外键和索引)
- mysql> create table new_service select * from service;
- 只复制表结构
- mysql> select * from service where 1=2; //条件为假,查不到任何记录
- Empty set (0.00 sec)
- mysql> create table new1_service select * from service where 1=2;
- Query OK, 0 rows affected (0.00 sec)
- Records: 0 Duplicates: 0 Warnings: 0
- mysql> create table t4 like employees;
mysql-数据库表操作
标签:logs ror add 必须 class ima 存储引擎 nod 逗号