mysql 表操作

##field字段名，type字段类型是必须的，字段不能重复 )[CHARSET XXX]#指定字符编码，不写默认latin1 创建一个员工表employee: CREATE TABLE employee( id int primary key auto_increment, name varchar(20), gender bit defaultn 1, --BIT类型表示0和1或者说false和true birthday date, entry_date date, job varchar(20), salary double(4,2) unsigned, resume text --最后一个字段不能叫逗号 ); /* 约束 primary key (非空且唯一) : 能够唯一区分当前记录的字段成为主键 unique 唯一 not null 不为空 auto_increment 主键字段必须为数字类型 foreign key 外键约束 */ --查看表信息 --desc table_name 查看表结构 --show columns from table_name 查看表结构 -- show create table tab_name 查看当前数据库表健 表语句 --3 修改表结构 --(1) 增加列（字段） alter table tab_name add [column]列名 类型[完整约束性条件][first|after 字段名]； alter table user add addr varchar(20) not null unique first/after username; alter table employee add addr varchar(20), add age int first,#插到第一的位置 add birth varchar(20) after name ;#插到name 后面 --修改列 类型 alter table tab_name modify 列名 类型 [完整约束类型][first|after]； alter table employee modify age tinyint default 20; alter table employee modify age int after id ; --修改列名 alter table table_name change[cloumn] 列名 新列名 类型 [完整约束性条件][first|after 字段名]; alter table employee change age Age int default 28 first; --删除一列 alter table tab_name drop [column]列名； --修改表名 rename table 表名 to 新表名； --修改表所用的字符集 alter table tab_name charset 编码； --删除表 drop table tab_name --添加主键 ，删除主键(主键： 非空且唯一 not null unique 唯一) alter table tab_name add primary key(字段名称) alter table user drop primary key; eg: create table test2(num int primary key auto_increment); alter table test modify id int;#删除了 auto_increment，但是主键任然在 alter table test drop primary key;#加上这句才能删除主键 -- 唯一索引 alter table tab_name add unique [index|key] [索引名称](字段名称,...) alter table users add unique(name)-- 索引值默认为字段名show create table users; alter table users add unique key user_name(name);-- 索引值为user_name -- 添加联合索引 alter table users add unique index name_age(name,age);#show create table users; -- 删除唯一索引 alter table tab_name drop {index|key} index_name

示例：

#创建数据库，指定字符集utf8

mysql> create database nulige character set utf8;
Query OK, 1 row affected (0.00 sec)mysql> show databases;+--------------------+| Database |+--------------------+

| information_schema |
| mysql              |
| nulige             |
| performance_schema |
| s3                 |
| s4                 |
| test               |
+--------------------+
7 rows in set (0.00 sec)

#进入nulige库
mysql> use nulige
Database changed

#查看表
mysql> show tables;
Empty set (0.00 sec)

#创建employee表
CREATE TABLE employee(

       id TINYINT PRIMARY KEY auto_increment,
       name VARCHAR(25),
       gender boolean,
       age  INT DEFAULT 19,
       department VARCHAR(20),
       salary DOUBLE(7,2)
);

#查看表
mysql> show tables;
+------------------+
| Tables_in_nulige |
+------------------+
| employee         |
+------------------+
1 row in set (0.00 sec)

#查看表结构
mysql> desc employee;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | tinyint(4)  | NO   | PRI | NULL    | auto_increment |
| name       | varchar(25) | YES  |     | NULL    |                |
| gender     | tinyint(1)  | YES  |     | NULL    |                |
| age        | int(11)     | YES  |     | 19      |                |
| department | varchar(20) | YES  |     | NULL    |                |
| salary     | double(7,2) | YES  |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

#增加一个字段
mysql> alter table employee add is_married tinyint(1);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0


#在表中增加1个字段
mysql> alter table employee add entry_date date not null;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

#增加二个字段
mysql> alter table employee add a INT,
    -> ADD B VARCHAR(20);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

#创建表
  mysql> create table A(id int);
  Query OK, 0 rows affected (0.01 sec)

 

  #查看表

  mysql> show tables;
  +------------------+
  | Tables_in_nulige |
  +------------------+
  | A |
  | emp |
  +------------------+
  2 rows in set (0.00 sec)

 

  #删除表

mysql> alter table employee DROP A;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0


#删除employee表中b字段
mysql> alter table employee DROP b,
    -> DROP entry_date;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0


#修改表中age字段的 null default 属于，并且在ID后面(after)
mysql> alter table employee modify age smallint not null default 18 after id;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

#查看表结构
mysql> desc employee;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | tinyint(4)  | NO   | PRI | NULL    | auto_increment |
| age        | smallint(6) | NO   |     | 18      |                |
| name       | varchar(25) | YES  |     | NULL    |                |
| gender     | tinyint(1)  | YES  |     | NULL    |                |
| department | varchar(20) | YES  |     | NULL    |                |
| salary     | double(7,2) | YES  |     | NULL    |                |
| is_married | tinyint(1)  | YES  |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
7 rows in set (0.01 sec)

#修表employee表中字段名称：把department改成depart字段，并且把位置调到salary后面
mysql> alter table employee change department depart varchar(20) after salary;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

#查看表结构

mysql> desc employee;
  +------------+-------------+------+-----+---------+----------------+
  | Field | Type | Null | Key | Default | Extra |
  +------------+-------------+------+-----+---------+----------------+
  | id | tinyint(4) | NO | PRI | NULL | auto_increment |
  | age | smallint(6) | NO | | 18 | |
  | name | varchar(25) | YES | | NULL | |
  | gender | tinyint(1) | YES | | NULL | |
  | salary | double(7,2) | YES | | NULL | |
  | depart | varchar(20) | YES | | NULL | |
  | is_married | tinyint(1) | YES | | NULL | |
  +------------+-------------+------+-----+---------+----------------+
  7 rows in set (0.00 sec)

#修改表名
mysql> rename table employee to emp;
Query OK, 0 rows affected (0.00 sec)

#查看表
mysql> show tables;
+------------------+
| Tables_in_nulige |
+------------------+
| emp              |
+------------------+
1 row in set (0.00 sec)


#创建表
mysql> create table A(id int);
Query OK, 0 rows affected (0.01 sec)

#查看表
mysql> show tables;
+------------------+
| Tables_in_nulige |
+------------------+
| A                |
| emp              |
+------------------+
2 rows in set (0.00 sec)

#删除A表
mysql> drop table A;
Query OK, 0 rows affected (0.01 sec)

#查看表
mysql> show tables;
+------------------+
| Tables_in_nulige |
+------------------+
| emp              |
+------------------+
1 row in set (0.00 sec)

 

mysql 表操作

标签：des   records   not   rename   var   逗号   外键   条件   ddr