表记录曾删改查

一、操作库（文件夹）

增：create database db1 charset utf8;
删：drop database db1;
改：alter database db1 charset gbk;
查：show databases; #查看所有的数据库
    show create database db1; #查看db1数据库

二、操作表（文件）

复制代码
切换到文件夹下:use db1
增：create table t1(id int,name char(10)) engine=innodb;
删：drop table t1;
改：alter table t1 add age int;
    alter table t1 modify name char(12);
查：show tables; #查看所有表
    show create table t1; #查看t1表
    desc t1;#查看表结构
    show create table t1G; #查看表详细结构，可加G
    select * from t1; #查看所有的表数据

三、操作记录（内容）

曾：insert into xiaoyu.member values(1,'zjy'),(2,'xiao'),(3,'xiaoyu');

mysql> select * from member;
+------+--------+
| id   | name   |
+------+--------+
|    1 | zjy    |
|    2 | xiao   |
|    3 | xiaoyu |
+------+--------+
3 rows in set (0.00 sec)

增

删：delete from t1 where id = 2;

    #对于清空记录有两种方式，但是推荐后者 delete删除id记录会保留，truncae不会保留
    delete from t1;
    truncate t1; #当数据量比较大的情况下，使用这种方式，删除速度快

mysql> turncate member;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'turncate member' at line 1
mysql> truncate member;
Query OK, 0 rows affected (0.24 sec)

mysql> select * from member;
Empty set (0.00 sec)


mysql> delete from member where id=1;
Query OK, 1 row affected (0.05 sec)

mysql> select * from member;
+------+--------+
| id   | name   |
+------+--------+
|    2 | xiao   |
|    3 | xiaoyu |
+------+--------+
2 rows in set (0.00 sec)

mysql> delete from member ;
Query OK, 3 rows affected (0.05 sec)

删

delete from t7 where id = 1; #删记录（只是删除一行当id=1的时候）
pdate t7 set name = '';#修改字段对应的值

改：update member set name='zjy' where name='xiaoyu';

　　update member set name='xiaoyu' where id=1;

mysql> update member set name='xiaoyu' where id=1;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from member;
+------+--------+
| id   | name   |
+------+--------+
|    1 | xiaoyu |
|    2 | xiao   |
|    3 | xiaoyu |
+------+--------+
3 rows in set (0.00 sec)

改

查：

select * from member; #查看t1里所有的数据

select name frommember; #查看t1里所有的name

select id,name from member; #查看t1里所有的id,name

四、id自增涨：member 主键：primary key 不为空：not null

create table yuer(id int primary key not null auto_increment,name varchar(12));

mysql> create table yuer(id int primary key not null auto_increment,name varchar(12));
Query OK, 0 rows affected (0.31 sec)

mysql> show create table yuer;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                               |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| yuer  | CREATE TABLE `yuer` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(12) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table yuerG
*************************** 1. row ***************************
       Table: yuer
Create Table: CREATE TABLE `yuer` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(12) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

五、拷贝表、表结构：

create test1 as select * from member;

mysql> select * from member;
+------+------+
| id   | name |
+------+------+
|    1 | zjy  |
|    2 | xiao |
|    3 | zjy  |
+------+------+
3 rows in set (0.00 sec)

mysql> create test1 as select * from member;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'test1 as select * from member' at line 1
mysql> create table test1 as select * from member;
Query OK, 3 rows affected (0.53 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test1;
+------+------+
| id   | name |
+------+------+
|    1 | zjy  |
|    2 | xiao |
|    3 | zjy  |
+------+------+
3 rows in set (0.00 sec)

mysql>

复制表

复制表结构（主键等可能会丢失）：create test3 like member;

mysql> select * from test3;
Empty set (0.00 sec)

mysql> desc test3;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | YES  |     | NULL    |       |
| name  | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

复制表结构（#拷贝表结构，不拷贝表数据（条件为假时，查不到任何记录））

mysql> create table test4 as select * from member where 1=2;
Query OK, 0 rows affected (0.31 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test4;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | YES  |     | NULL    |       |
| name  | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

复制表结构，where条件为假

六、修改表id自增涨

alter table t7 modify id int primary key auto_increment;  修改id为主键并且自增