Day02.Mysql基本操作

1，创建2个表，练习约束条件
mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test;
Database changed
mysql> create table tea(name varchar(4) not null,gender enum(‘boy’,’girl’) default “boy”,interest set(‘book’,’film’,’music’,’football’));
Query OK, 0 rows affected (0.36 sec)

mysql> desc tea;
+———-+—————————————+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+———-+—————————————+——+—–+———+——-+
| name | varchar(4) | NO | | NULL | |
| gender | enum(‘boy’,’girl’) | YES | | boy | |
| interest | set(‘book’,’film’,’music’,’football’) | YES | | NULL | |
+———-+—————————————+——+—–+———+——-+
3 rows in set (0.02 sec)

mysql> create table tea2(name varchar(4) not null,gender enum(‘boy’,’girl’) default “boy”,age int(3) not null default 21,interest set(‘book’,’film’,’music’,’football’,’girl’));
Query OK, 0 rows affected (0.32 sec)

mysql> desc tea2;
+———-+———————————————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+———-+———————————————-+——+—–+———+——-+
| name | varchar(4) | NO | | NULL | |
| gender | enum(‘boy’,’girl’) | YES | | boy | |
| age | int(3) | NO | | 21 | |
| interest | set(‘book’,’film’,’music’,’football’,’girl’) | YES | | NULL | |
+———-+———————————————-+——+—–+———+——-+
4 rows in set (0.00 sec)

2、修改字段
1添加表字段
alter table table1 add transactor varchar(10) not Null;
alter table table1 add id int unsigned not Null auto_increment primary key
2.修改某个表的字段类型及指定为空或非空
alter table 表名称 change 字段名称 字段名称 字段类型 [是否允许非空];
alter table 表名称 modify 字段名称 字段类型 [是否允许非空];
3.修改某个表的字段名称及指定为空或非空
alter table 表名称 change 字段原名称 字段新名称 字段类型 [是否允许非空
4如果要删除某一字段，可用命令：ALTER TABLE mytable DROP 字段名;

mysql> desc tea2;
+———-+———————————————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+———-+———————————————-+——+—–+———+——-+
| name | varchar(4) | NO | | NULL | |
| gender | enum(‘boy’,’girl’) | YES | | boy | |
| age | int(3) | NO | | 21 | |
| interest | set(‘book’,’film’,’music’,’football’,’girl’) | YES | | NULL | |
+———-+———————————————-+——+—–+———+——-+
4 rows in set (0.00 sec)

add 添加 modify 修改 change 修改字段名 drop 删除 rename 修改表名

添加
mysql> alter table tea2 add (address varchar(50) not null default “chengdu”);
Query OK, 0 rows affected (0.45 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc tea2;
+———-+———————————————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+———-+———————————————-+——+—–+———+——-+
| name | varchar(4) | NO | | NULL | |
| gender | enum(‘boy’,’girl’) | YES | | boy | |
| age | int(3) | NO | | 21 | |
| interest | set(‘book’,’film’,’music’,’football’,’girl’) | YES | | NULL | |
| address | varchar(50) | NO | | chengdu | |
+———-+———————————————-+——+—–+———+——-+
5 rows in set (0.00 sec)

修改
第一种修改，change，后面要跟新的字段，包括属性；
mysql> alter table tea2 change address road varchar(80) not null default “beijing”;
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc tea2;
+———-+———————————————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+———-+———————————————-+——+—–+———+——-+
| name | varchar(4) | NO | | NULL | |
| gender | enum(‘boy’,’girl’) | YES | | boy | |
| age | int(3) | NO | | 21 | |
| interest | set(‘book’,’film’,’music’,’football’,’girl’) | YES | | NULL | |
| road | varchar(80) | NO | | beijing | |
+———-+———————————————-+——+—–+———+——-+
5 rows in set (0.00 sec)

第二种修改，modify，直接跟属性。
mysql> alter table tea2 modify road varchar(60) not null default “Sichuan”;
Query OK, 0 rows affected (0.87 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc tea2;
+———-+———————————————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+———-+———————————————-+——+—–+———+——-+
| name | varchar(4) | NO | | NULL | |
| gender | enum(‘boy’,’girl’) | YES | | boy | |
| age | int(3) | NO | | 21 | |
| interest | set(‘book’,’film’,’music’,’football’,’girl’) | YES | | NULL | |
| road | varchar(60) | NO | | Sichuan | |
+———-+———————————————-+——+—–+———+——-+
5 rows in set (0.00 sec)

删除，直接drop
mysql> alter table tea2 add (address char(80) not null default “Chengdu”,post int not null default “650000”);
Query OK, 0 rows affected (0.49 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc tea2;
+———-+———————————————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+———-+———————————————-+——+—–+———+——-+
| name | varchar(4) | NO | | NULL | |
| gender | enum(‘boy’,’girl’) | YES | | boy | |
| age | int(3) | NO | | 21 | |
| interest | set(‘book’,’film’,’music’,’football’,’girl’) | YES | | NULL | |
| road | varchar(60) | NO | | Sichuan | |
| address | char(80) | NO | | Chengdu | |
| post | int(11) | NO | | 650000 | |
+———-+———————————————-+——+—–+———+——-+
7 rows in set (0.00 sec)

mysql>
mysql> alter table tea2 drop road;
Query OK, 0 rows affected (0.46 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc tea2;
+———-+———————————————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+———-+———————————————-+——+—–+———+——-+
| name | varchar(4) | NO | | NULL | |
| gender | enum(‘boy’,’girl’) | YES | | boy | |
| age | int(3) | NO | | 21 | |
| interest | set(‘book’,’film’,’music’,’football’,’girl’) | YES | | NULL | |
| address | char(80) | NO | | Chengdu | |
| post | int(11) | NO | | 650000 | |
+———-+———————————————-+——+—–+———+——-+

重命名表,rename
mysql> alter table tea2 rename tea3;
Query OK, 0 rows affected (0.10 sec)

mysql> show tables;
+—————-+
| Tables_in_test |
+—————-+
| tea |
| tea3 |
+—————-+
2 rows in set (0.00 sec)

mysql> alter table tea3 rename tea2;
Query OK, 0 rows affected (0.14 sec)

mysql> show tables;
+—————-+
| Tables_in_test |
+—————-+
| tea |
| tea2 |
+—————-+
2 rows in set (0.00 sec)

mysql> create table tea4(id char(6) not null,name varchar(4) not null,age int(3) not null, gender enum(‘boy’,’girl’) default ‘boy’,index(id), index(name));
Query OK, 0 rows affected (0.37 sec)

INDEX索引
创建一个新表tea4，将索引设为id，name;
mysql> create table tea4(id char(6) not null,name varchar(4) not null,age int(3) not null,gender enum(“boy”,”girl”) default “boy”,index(id),index(name));
Query OK, 0 rows affected (0.30 sec)

mysql> desc tea4;
+——–+——————–+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+——–+——————–+——+—–+———+——-+
| id | char(6) | NO | MUL | NULL | |
| name | varchar(4) | NO | MUL | NULL | |
| age | int(3) | NO | | NULL | |
| gender | enum(‘boy’,’girl’) | YES | | boy | |
+——–+——————–+——+—–+———+——-+
4 rows in set (0.00 sec)

mysql> show create table tea4;
+——-+————————————————————————————————————————————————————————————————————————————+
| Table | Create Table |
+——-+————————————————————————————————————————————————————————————————————————————+
| tea4 | CREATE TABLE `tea4` (
`id` char(6) NOT NULL,
`name` varchar(4) NOT NULL,
`age` int(3) NOT NULL,
`gender` enum(‘boy’,’girl’) DEFAULT ‘boy’,
KEY `id` (`id`),
KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+——-+————————————————————————————————————————————————————————————————————————————+
1 row in set (0.00 sec)

查看一下索引
mysql> show index from tea4;
+——-+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+—————+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+——-+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+—————+
| tea4 | 1 | id | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
| tea4 | 1 | name | 1 | name | A | 0 | NULL | NULL | | BTREE | | |
+——-+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+—————+
2 rows in set (0.00 sec)

删除已有的索引
mysql> drop index name on tea4;
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show index from tea4;
+——-+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+—————+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+——-+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+—————+
| tea4 | 1 | id | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
+——-+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+—————+
1 row in set (0.00 sec)

在已有表中增加索引
mysql> create index name on tea4(name);
Query OK, 0 rows affected (0.15 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show index from tea4;
+——-+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+—————+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+——-+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+—————+
| tea4 | 1 | id | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
| tea4 | 1 | name | 1 | name | A | 0 | NULL | NULL | | BTREE | | |
+——-+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+—————+
2 rows in set (0.00 sec)

primary key 主键
一个表中只能有一个 primary key。多个字段需要同时创建。标记PRI,通常与AUTO_INCREMENT连用，被主键的字段称为主键字段
mysql> create table tea6(id int(4) auto_increment,name varchar(4) not null,age int(2) not null,primary key(id));
Query OK, 0 rows affected (0.42 sec)

mysql> show create table tea6;
+——-+———————————————————————————————————————————————————————————+
| Table | Create Table |
+——-+———————————————————————————————————————————————————————————+
| tea6 | CREATE TABLE `tea6` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`name` varchar(4) NOT NULL,
`age` int(2) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+——-+———————————————————————————————————————————————————————————+
1 row in set (0.00 sec)

mysql> desc tea6;
+——-+————+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+——-+————+——+—–+———+—————-+
| id | int(4) | NO | PRI | NULL | auto_increment |
| name | varchar(4) | NO | | NULL | |
| age | int(2) | NO | | NULL | |
+——-+————+——+—–+———+—————-+
3 rows in set (0.00 sec)

删除主键（先删除自增auto_increment）
mysql> alter table tea6 modify id int(4) not null;
Query OK, 0 rows affected (0.61 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table tea6 drop primary key;
Query OK, 0 rows affected (0.48 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc tea6;
+——-+————+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+——-+————+——+—–+———+——-+
| id | int(4) | NO | | NULL | |
| name | varchar(4) | NO | | NULL | |
| age | int(2) | NO | | NULL | |
+——-+————+——+—–+———+——-+
3 rows in set (0.00 sec)

mysql> show create table tea6;
+——-+——————————————————————————————————————————————–+
| Table | Create Table |
+——-+——————————————————————————————————————————————–+
| tea6 | CREATE TABLE `tea6` (
`id` int(4) NOT NULL,
`name` varchar(4) NOT NULL,
`age` int(2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+——-+——————————————————————————————————————————————–+
1 row in set (0.00 sec)

增加主键
mysql> alter table tea6 add primary key(id);
Query OK, 0 rows affected (0.44 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table tea6 modify id int(4) not null auto_increment;
Query OK, 0 rows affected (0.62 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc tea6;
+——-+————+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+——-+————+——+—–+———+—————-+
| id | int(4) | NO | PRI | NULL | auto_increment |
| name | varchar(4) | NO | | NULL | |
| age | int(2) | NO | | NULL | |
+——-+————+——+—–+———+—————-+
3 rows in set (0.00 sec)

mysql> show create table tea6;
+——-+———————————————————————————————————————————————————————————+
| Table | Create Table |
+——-+———————————————————————————————————————————————————————————+
| tea6 | CREATE TABLE `tea6` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`name` varchar(4) NOT NULL,
`age` int(2) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+——-+———————————————————————————————————————————————————————————+
1 row in set (0.00 sec)