MySQL-创建 删除数据库表 SQL命令

一、MySQL 创建数据表

CREATE TABLE table_name (column_name column_type);

1、创建库并设置字符集编码为utf8

create database '库名' default character set = 'utf8';

2、创建表并设置字符集编码为utf8

create table ‘表名’(id int(6),name char(10)) default character set = 'utf8';

实例：

create table IF NOT EXISTS user (id int(11) not null AUTO_INCREMENT,name char(20) NOT NULL DEFAULT '' comment '用户名',primary key(id)) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1

PRIMARY KEY关键字用于定义列为主键；可以使用多列来定义主键，列间以逗号 ',' 分隔

ENGINE设置存储引擎

CHARSET设置编码

3、快速创建表

create table table表名2 as select * from table表名1;

如下：

create table emp2 as select * from emp;

将emp表的查询结果当做一张表新建；可以完成表的快速复制；表创建出来，同时表中的数据也复制过来了

也可以复制一张表的部分字段

create table mytable as select empno,empname from emp where job = 'salesman';

注：

如果建表时没有指定设置字符集编码为utf8，则在插入数据时插入了中文，则会报错

mysql> insert into t_user values(3,'王二麻子','1992-06-18');ERROR 1366 (HY000): Incorrect string value: '\xCD\xF5\xB6\xFE\xC2\xE9...' for column 'name' at row 1

二、MySQL 删除数据表

DROP TABLE table_name;

如果数据库中不存在该表，会报错：

mysql> drop table t_user;ERROR 1051 (42S02): Unknown table 'mydb.t_user'

因此需要使用如下语句，删除表之前判断表是否存在

drop table if exists table_name;

这样就不会报错

mysql> drop table if exists t_user;Query OK, 0 rows affected, 1 warning (0.00 sec)

三、MySQL 插入数据

INSERT INTOSQL

1、字段名和值要一一对应；数量要对应，数据类型要对应

INSERT INTO table_name ( column_name1, column_name2,...column_nameN )VALUES( value1, value2,...valueN );或者 （无需列名，直接插入值）INSERT INTO table_name VALUES( value1, value2,...valueN );

实例：

insert into user (id,name,) values(null,'小明');或者insert into user values(null,'小明');

不需要提供 主键 id 的数据，因为该字段我们在创建表的时候已经设置它

AUTO_INCREMENT(自动增加) 属性。该字段会自动递增，而不需要手动设置

注：

字段名省略的时候，值需要都写上

insert语句只要执行成功了，那么表中必然会增加一条记录；没有给其它字段指定值的话，默认值是NULL

2、批量插入数据

insert into t_user(字段名1,字段名2) values(),(),(),();

如下：

insert into t_user(id,name,birth,create_time) values(1,'zs','1992-10-11',now()), (2,'lisi','1993-06-12',now()),(3,'wangwu','1999-08-16',now());或者insert into t_user values(1,'zs','1992-10-11',now()), (2,'lisi','1993-06-12',now()),(3,'wangwu','1999-08-16',now());

3、快速插入数据

insert into table表名2 select * from table表名1;

如下：

insert into dept_bak select * from dept;

四、MySQL 查询数据

SELECT

SELECT column_name,column_nameFROM table_name[WHERE Clause][LIMIT N][ OFFSET M]

查询语句中可以使用一个或者多个字段，字段之间使用逗号(,)分割，并使用WHERE语句来设定查询条件。

SELECT 命令可以读取一条或者多条记录

使用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据

使用 WHERE 语句来包含任何条件

使用 LIMIT 属性来设定返回的记录数

通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0

1、查询所有字段两种方式

【1】每个字段都写上，中间使用逗号（,）分割开来

select a,b,c,d from tablename;

【2】使用星号（*）来代替其他字段

select * from tablename;

注：

使用星号（*）这种方式的缺点：

1、效率低；（*）星号也是先替换表中所有字段再进行查询

2、可读性差

select和from都是关键字

字段名和表名都是标识符

实例：

select * from table_name;

2、distinct去除重复记录

select distinct job from emp;

注：

【1】select distinct查询原表数据不会被修改，只是查询结果去重

【2】distinct只能出现在所有字段的最前方

select empname,distinct job from emp;

如下就会报错：

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 'distinct job from emp' at line 1

（1）distinct出现在两个字段之前，表示两个字段联合起来去重

select distinct job,deptno from emp;

（2）distinct 可以和 分组函数使用

select count(distinct job) as jobcount from emp;

五、MySQL WHERE 子句

使用 WHERE 子句从数据表中读取数据

SELECT column1, column2,...columnN FROM table_name1, table_name2...[WHERE condition1 [AND [OR]] condition2.....

实例：

select * from user where id = '1';

查询语句中可以使用一个或者多个表，表之间使用逗号, 分割，并使用WHERE语句来设定查询条件。

可以在 WHERE 子句中指定任何条件。

可以使用 AND 或者 OR 指定一个或多个条件。

WHERE 子句也可以运用于 SQL 的 DELETE 或者 UPDATE 命令。

WHERE 子句类似于程序语言中的 if 条件，根据 MySQL 表中的字段值来读取指定的数据。

is null 为 null；is not null 不为空

在数据库当中null不能使用等号"="进行衡量；需要使用is null

数据库中的null代表什么也没有，它不是一个值，所以不能使用等号"="衡量

MySQL 的 WHERE 子句的字符串比较是不区分大小写的;；可以使用BINARY关键字来设定 WHERE 子句的字符串比较是区分大小写

ELECT * from user WHERE BINARY name='xiaoming';

六、MySQL UPDATE 更新

UPDATE table_name SET field1=new-value1, field2=new-value2[WHERE Clause]

可以同时更新一个或多个字段

可以在 WHERE 子句中指定任何条件

可以在一个单独表中同时更新数据

注：

没有条件限制会导致所有数据全部更新

七、MySQL DELETE 语句

DELETE FROM table_name [WHERE Clause]

如果没有指定 WHERE 子句，MySQL 表中的所有记录将被删除

可以在 WHERE 子句中指定任何条件

可以在单个表中一次性删除记录

1、删除可恢复

（1）删除之前执行start transaction;

start transaction;

在进行delete命令

如果想恢复，执行 rollback;

rollback;

在进行查询就可看到数据恢复

mysql> start transaction;Query OK, 0 rows affected (0.00 sec)mysql> delete from dept_bak;Query OK, 8 rows affected (0.00 sec)mysql> select * from dept_bak;Empty set (0.00 sec)mysql> rollback;Query OK, 0 rows affected (0.01 sec)mysql> select * from dept_bak;+--------+------------+-----------+| DEPTNO | DEPTNAME | CITY|+--------+------------+-----------+|10 | ACCOUNTING | BEI JING ||20 | PURCHASE | TIAN JIN ||30 | SALES| SHANG HAI ||40 | HR | SU ZHOU |+--------+------------+-----------+8 rows in set (0.00 sec)

2、删除不可恢复truncate

执行如下命令

truncate table table表名;

mysql> truncate table dept_bak;Query OK, 0 rows affected (0.02 sec)mysql> select * from dept_bak;Empty set (0.00 sec)mysql> rollback;Query OK, 0 rows affected (0.00 sec)

delete语句删除数据的原理

表中的数据被删除了，但是这个数据在硬盘上的真实存储空间不会被释放

这种删除缺点是：删除效率比较低

这种删除优点是：支持回滚，可以再恢复数据

truncate语句删除数据的原理

这种删除效率比较高，表被一次截断，物理删除

这种删除缺点：不支持回滚

这种删除优点：快速

truncate是删除表中的数据，表还在

3、逻辑删除 和 物理删除

（1）逻辑删除（软删除）：逻辑删除就是对要被删除的数据打上一个删除标记，通常使用一个is_deleted字段标示行记录是不是被删除（或者使用一个status字段代表所谓的“删除”状态），在逻辑上是数据是被删除的，但数据本身是依然存在的

（2）物理删除

指文件存储所用到的磁存储区域被真正的擦除或清零，这样删除的文件是不可以恢复的，物理删除是计算机处理数据时的一个概念。如果在数据库中直接使用delete、drop、truncate删除了表数据，如果没有备份的话，数据就很难恢复了。

注：

没有限制条件，整张表的数据会全部删除！