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

深碍√TFBOYSˉ_ 2023-10-03 12:09 32阅读 0赞

#### 一、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 INTO**SQL

**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_name
    FROM 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;

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBATWluZ2dlUWluZ2NodW4_size_7_color_FFFFFF_t_70_g_se_x_16][]

注：

【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

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBATWluZ2dlUWluZ2NodW4_size_15_color_FFFFFF_t_70_g_se_x_16][]

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

select distinct job,deptno from emp;

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBATWluZ2dlUWluZ2NodW4_size_8_color_FFFFFF_t_70_g_se_x_16][]

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

select count(distinct job) as jobcount  from emp;

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBATWluZ2dlUWluZ2NodW4_size_11_color_FFFFFF_t_70_g_se_x_16][]

#### 五、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 表中的字段值来读取指定的数据。

<table> 
 <tbody> 
  <tr> 
   <th>操作符</th> 
   <th>描述</th> 
   <th>实例</th> 
  </tr> 
  <tr> 
   <td>=</td> 
   <td>等号，检测两个值是否相等，如果相等返回true</td> 
   <td>(A = B) 返回false。</td> 
  </tr> 
  <tr> 
   <td>&lt;&gt;, !=</td> 
   <td>不等于，检测两个值是否相等，如果不相等返回true</td> 
   <td>(A != B) 返回 true。</td> 
  </tr> 
  <tr> 
   <td>&gt;</td> 
   <td>大于号，检测左边的值是否大于右边的值, 如果左边的值大于右边的值返回true</td> 
   <td>(A &gt; B) 返回false。</td> 
  </tr> 
  <tr> 
   <td>&lt;</td> 
   <td>小于号，检测左边的值是否小于右边的值, 如果左边的值小于右边的值返回true</td> 
   <td>(A &lt; B) 返回 true。</td> 
  </tr> 
  <tr> 
   <td>&gt;=</td> 
   <td>大于等于号，检测左边的值是否大于或等于右边的值, 如果左边的值大于或等于右边的值返回true</td> 
   <td>(A &gt;= B) 返回false。</td> 
  </tr> 
  <tr> 
   <td>&lt;=</td> 
   <td>小于等于号，检测左边的值是否小于或等于右边的值, 如果左边的值小于或等于右边的值返回true</td> 
   <td>(A &lt;= B) 返回 true。</td> 
  </tr> 
 </tbody> 
</table>

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;

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

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删除了表数据，如果没有备份的话，数据就很难恢复了。

注：

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

[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBATWluZ2dlUWluZ2NodW4_size_7_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/8dd9350f096c4aa7841c10749c727c80.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBATWluZ2dlUWluZ2NodW4=,size_7,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBATWluZ2dlUWluZ2NodW4_size_15_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/61d2e8dae6e844b2860f74ca495a699b.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBATWluZ2dlUWluZ2NodW4=,size_15,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBATWluZ2dlUWluZ2NodW4_size_8_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/f9598a6fe3664685a6ccd66510bde8bf.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBATWluZ2dlUWluZ2NodW4=,size_8,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBATWluZ2dlUWluZ2NodW4_size_11_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/b60d1a911c0b4946bf6f5db0e9d7e3c5.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBATWluZ2dlUWluZ2NodW4=,size_11,color_FFFFFF,t_70,g_se,x_16