MySQL数据库：sql语句的执行

0. 基础

• MySQL

mysql-8.0.12-win64bit

• Navicat Premium

navicat120_premium_cs_x64

• 系统

Windows7-64bit-旗舰版

• 时间

2018/10/24

• 关联文章

MySQL数据库安装配置

 

1. 利用Navicat Premium操作MySQL数据库

• 查看MySQL数据库

• 执行sql语句

Navicat常用快捷键 : 

ctrl+q            打开查询窗口 
ctrl+/             注释 
ctrl +shift+/   解除注释 
ctrl+r             运行选中的SQL语句 
ctrl+l              删除选中行内容 
ctrl+d            复制当前行内容并粘贴到下一行 
ctrl+w           关闭当前查询窗口

1) 进入查询界面执行sql语句

2) 进入命令列界面执行sql语句

 

2. 常用sql语句

• 所用数据下载

百度云链接：提取码( aw7s )

• 创建数据库

create database 所要创建的数据库的名称

• 删除数据库

drop database 所要删除的数据库的名称

• 创建数据表

create table 所要创建的数据表的名称 (

                                        column1_name column1_type column1_property,

                                        column2_name column2_type column2_property,

                                        ...

                                        columnN_name columnN_type columnN_property,

                                        PRIMARY KEY ( 主键列名称)

                                        )ENGINE=设置存储引擎 DEFAULT CHARSET=设置编码

• 删除数据表

方法一：删除表全部数据和表结构，立刻释放磁盘空间

drop table 所要删除的数据表的名称

方法二：删除表全部数据，保留表结构，立刻释放磁盘空间

truncate table 所要删除的数据表的名称

• 插入数据          

insert into table_name ( field1, field2,...fieldN )

                                       values

                                       ( valueA1, valueA2,...valueAN ),

                                       ( valueB1, valueB2,...valueBN ),

                                       ...

                                       ( valueZ1, valueZ2,...valueZN )

• 删除数据

方法一：删除若干条数据

delete from 表名 where 删除条件

方法二：删除整张表的数据

delete from 表名

或

truncate table 表名

• 查询数据

1) 基本方式

select column1_name,...,columnN_name 

from table1_name,...,tableN_name

where Clause

order by column_name

limit N offset M

或

select column1_name,...,columnN_name

from table1_name,...,tableN_name

where Clause

order by column_name

limit M,N

2) 分页查询

方法一：

select * from table_name limit (page_number-1)*lines_perpage, lines_perpage

方法二：

select * from table_name limit lines_perpage offset (page_number-1)*lines_perpage

方法三：（子查询）

select * from table_name where  字段 逻辑运算 (select 字段 from table_name  where 条件 limit M, 1)

3) 范围查询

select * from table_name where column_name between 起始范围 and 终止范围

• 更新数据

update 数据表 set field1=new-value1,...,fieldN=new-valueN where 条件

• 子句

1) 关联子句

1.1) 格式 select 字段 from 表1 left/right/inner join 表2 on  条件 (一般为表1与表2的关联条件)

1.2) 左关联：获取左表所有记录，即使右表没有对应匹配的记录         left join ... on ...

1.3) 右关联：获取右表所有记录，即使左表没有对应匹配的记录         right join ... on ...

1.4) 内关联：获取两个表中字段匹配关系的记录                                  inner join ... on ...

2) 分组子句

方法一：通过字段将结果集进行分组，并统计该字段的信息

select 字段,  聚合函数(字段) from 数据表 where 条件 group by 字段

方法二：通过字段一将结果集进行分组，在此基础上统计字段二的信息

select 字段一, 聚合函数(字段二) from 数据表 where 条件 group by 字段一 with rollup

3) 模糊子句

... where 字段 like 匹配字符串

常用匹配字符串：
'%a'     //以a结尾的数据
'a%'     //以a开头的数据
'%a%' //含有a的数据
'_a_'    //三位且中间字母是a的
'_a'      //两位且结尾字母是a的
'a_'      //两位且开头字母是a的

4) 别名子句

第一种： 字段别名

select 字段 as 字段别名 from 数据表

第二种： 数据表别名

select 数据表别名.字段 from 数据表 数据表别名

5) 排序子句

select field1,...,fieldN from table_name1,...,table_nameN where 条件 order by field1,...,fieldN ASC DESC

6) UNION子句

说明：UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中，多个 SELECT 语句会删除重复的数据。

select 字段1 from 数据表1 where 条件 union select 字段2 from 数据表2 where 条件

 

3. 几个常用概念

• ALTER命令

1) 修改数据表名

ALTER TABLE 旧数据表 RENAME TO 新数据表

2) 修改字段类型及名称

ALTER TABLE 数据表 MODIFY 字段 字段新类型

或

ALTER TABLE 数据表 CHANGE 旧字段 新字段 新字段新类型

3) 删除、添加字段

ALTER TABLE 数据表  DROP 所要删除字段

ALTER TABLE 数据表 ADD 所要添加字段 所要添加字段类型

ALTER TABLE 数据表 ADD 所要添加字段 所要添加字段类型 FIRST

ALTER TABLE 数据表 ADD 所要添加字段 所要添加字段类型 AFTER 原有字段

4) 修改/删除字段默认值

ALTER TABLE 数据表 ALTER 字段 SET DEFAULT 默认值

ALTER TABLE 数据表 ALTER 字段 DROP DEFAULT

ALTER TABLE 数据表 MODIFY 字段 字段新类型 DEFAULT 默认值

5) 修改存储引擎

ALTER TABLE 数据表 ENGINE=新引擎

6) 修改字段的相对位置

ALTER TABLE 数据表 MODIFY 字段1 字段1类型 FIRST

ALTER TABLE 数据表 MODIFY 字段1 字段1类型 AFTER 字段2

7) 添加/删除索引

方式一：添加一个唯一索引

ALTER TABLE 数据表 ADD UNIQUE 索引名 (字段1,...,字段N)

方式二：添加一个普通索引

ALTER TABLE 数据表 ADD INDEX 索引名 (字段1,...,字段N)

方式三：添加一个全文索引

ALTER TABLE 数据表 ADD FULLTEXT 索引名 (字段1,...,字段N)

方式四：删除一个索引

ALTER TABLE 数据表 DROP INDEX 索引名

8) 添加/删除主键（主键只能作用于一个列上，添加主键时，要确保该主键默认不为空（NOT NULL））

方式一：添加一个主键

ALTER TABLE 数据表 ADD PRIMARY KEY (字段)

方式二：删除一个主键

ALTER TABLE 数据表 DROP PRIMARY KEY

• 复制表

1) 方法一

第一步：显示被复制表的信息

SHOW CREATE TABLE 被复制数据表 \G

第二步：创建复制表

CREATE TABLE 复制表 ...

第三步：拷贝数据

INSERT INTO 复制表 (字段1,字段2,...,字段N) SELECT 字段1,字段2,...,字段N FROM 被复制表

2) 方法二

CREATE TABLE 复制表 LIKE 被复制表
INSERT INTO 复制表 SELECT * FROM 被复制表

3) 方法三

CREATE TABLE 复制表 SELECT * FROM 被复制表

4) 方法四

CREATE TABLE 复制表 AS
(
    SELECT 字段1,...,字段N FROM 被复制表
)

或

CREATE TABLE 复制表 AS
(  
    SELECT 字段1 AS 字段1别名,...,字段N AS 字段N别名 FROM 被复制表
)

或

CREATE TABLE 复制表 AS
(
    SELECT * FROM 被复制表 WHERE 条件
)

或

CREATE TABLE 复制表
(
    字段i 字段i类型 字段i属性
)
AS
(
    SELECT * FROM 被复制表
)  

• 索引

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引。组合索引，即一个索引包含多个列。索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

1) 创建普通索引

方法一：基本方式

CREATE INDEX 索引名 ON 数据表(字段(字段类型长度))

方法二：修改表结构

ALTER TABLE 数据表 ADD INDEX 索引名 (字段)

方法三：创建表时指定

create table 所要创建的数据表的名称 (

                                        column1_name column1_type column1_property,

                                        column2_name column2_type column2_property,

                                        ...

                                        columnN_name columnN_type columnN_property,

                                        PRIMARY KEY ( 主键列名称),

                                        INDEX 索引名 (字段(字段类型长度))

                                        )ENGINE=设置存储引擎 DEFAULT CHARSET=设置编码

2) 创建唯一索引（要求索引列的值或索引列值的组合必须唯一）

方法一：基本方式

CREATE UNIQUE INDEX 索引名 ON 数据表(字段(字段类型长度))

方法二：修改表结构

ALTER TABLE 数据表 ADD UNIQUE 索引名 (字段)

方法三：创建表时指定

create table 所要创建的数据表的名称 (

                                        column1_name column1_type column1_property,

                                        column2_name column2_type column2_property,

                                        ...

                                        columnN_name columnN_type columnN_property,

                                        PRIMARY KEY ( 主键列名称),

                                        UNIQUE 索引名 (字段(字段类型长度))

                                        )ENGINE=设置存储引擎 DEFAULT CHARSET=设置编码

3) 删除索引

DROP INDEX 索引名 ON 数据表 

• 序列和主键

 序列是一组整数：1, 2, 3, ...，由于一张数据表只能有一个字段自增主键， 如果其他字段也实现自动增加，就可以通过序列来实现。

1) 设置自增序列

create table 所要创建的数据表的名称 (

                                        column1_name column1_type column1_property,

                                        ...

                                        columnH_name columnH_type AUTO_INCREMENT,

                                        PRIMARY KEY (columnH_name),

                                        ...

                                        columnN_name columnN_type columnN_property                          

                                        )ENGINE=设置存储引擎 DEFAULT CHARSET=设置编码

2) 重置序列

如果删除了数据表中的多条记录，并希望对剩下数据的AUTO_INCREMENT列进行重新排列，那么可以通过删除自增的列，然后重新添加来实现。

ALTER TABLE 数据表 DROP 自增字段

ALTER TABLE 数据表 ADD 自增字段 自增字段类型 AUTO_INCREMENT FIRST ADD PRIMARY KEY (自增字段)

3) 设置序列的起始值

方法一：

create table 所要创建的数据表的名称 (

                                        column1_name column1_type column1_property,

                                        ...

                                        columnH_name columnH_type AUTO_INCREMENT,

                                        PRIMARY KEY (columnH_name),

                                        ...

                                        columnN_name columnN_type columnN_property                          

                                        )ENGINE=设置存储引擎 AUTO_INCREMENT=序列起始值 DEFAULT CHARSET=设置编码

方法二：

ALTER TABLE 数据表 AUTO_INCREMENT = 序列起始值

• 重复数据

1) 防止表中出现重复数据

方法一：设置指定字段为主键

create table 所要创建的数据表的名称 (

                                        column1_name column1_type column1_property,

                                        column2_name column2_type column2_property,

                                        ...

                                        columnN_name columnN_type columnN_property,

                                        PRIMARY KEY ( 字段1,...,字段N)

                                        )ENGINE=设置存储引擎 DEFAULT CHARSET=设置编码

方法二：设置指定字段为唯一索引

create table 所要创建的数据表的名称 (

                                        column1_name column1_type column1_property,

                                        column2_name column2_type column2_property,

                                        ...

                                        columnN_name columnN_type columnN_property,

                                        UNIQUE (字段1,...,字段N)

                                        )ENGINE=设置存储引擎 DEFAULT CHARSET=设置编码

注：

INSERT INTO：在设置了记录的唯一性之后，如果数据库没有该数据，就插入新的数据，如果有该数据的话就报错；

INSERT IGNORE INTO：在设置了记录的唯一性之后，如果数据库没有该数据，就插入新的数据，如果有该数据的话就跳过这条数据；

REPLACE INTO：在设置了记录的唯一性之后，如果数据库没有该数据，就插入新的数据，如果有该数据的话就删除这条数据再插入新数据；

2) 过滤重复数据

方法一：

select distinct 字段 from 数据表

方法二：

select 字段1,...,字段N from 数据表 group by (字段1,...,字段N)

3) 删除重复数据

方法一：

CREATE TABLE 临时数据表 SELECT 字段1,...,字段N FROM 数据表 GROUP BY (字段1,...,字段N)

DROP TABLE 数据表

ALTER TABLE 临时数据表 RENAME TO 数据表

方法二：

ALTER IGNORE TABLE 数据表 ADD PRIMARY KEY (字段1,...,字段N)

 

4. 导入导出数据

• 导出数据

SELECT * FROM 数据表 INTO OUTFILE '/tmp/runoob.txt'

注：该文件被创建到服务器主机上。

注：可以借助于Navicat Premium 12来完成这个工作。

• 导入数据

LOAD DATA LOCAL INFILE 'runoob.txt' INTO TABLE 数据表

注：如果指定LOCAL关键词，则表明从客户主机上按路径读取文件，如果没有指定，则文件在服务器上按路径读取文件。

注：可以借助于Navicat Premium 12来完成这个工作。