MySQL常用语句

      Hi,大家好，今天我给大家带来的是“MariaDB(MySQL)常用语句的使用”

伊通网站建设公司成都创新互联,伊通网站设计制作，有大型网站制作公司丰富经验。已为伊通1000+提供企业网站建设服务。企业网站搭建\成都外贸网站建设公司要多少钱，请找那个售后服务好的伊通做网站的公司定做！

     说明

              1、MySQL语句不区分字符大小写，在Unix或类Unix(Linux)系统下“参数”区分字符大小写

              2、多数MySQL语句以";"结尾；如果结尾处不写";"MySQL会等待输入下一条语句，直到结尾有";"为止

              3、一行需要执行多条语句用";"进行分隔

          本篇内容以Linux系统为例；本文中的[符号里边的内容为可选内容，既可有可无的 ]、{符号里的内容为必须给出的内容 }、|符号为或者之意；语句之前的符号#为Linux系统的命令提示符（需在系统下执行的）、符号>为MySQL数据库提示符（需在MySQL数据库中执行的）；字体颜色为红色的是需要注意的地方（可能会出现误操作的地方）

温馨提示：使用电脑阅读效果更佳

MariaDB(MySQL)语句分类

     服务端命令：通过mysql连接发往服务器执行并取回结果（SQL语句）

              DDL(Data Defined Language)：要用于管理数据库组件，例如表、索引、视图、用户、存储过程

                     CREATE DATABASE, ALTER DATABASE, DROP DATABASE, CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX, DROP INDEX, CREATE USER, DROP USER, SELECT

           DML(Data Manipulation Language)：数据操纵语言，主要用管理表中的数据，实现数据的增、删、改、查

                     INSERT, DELETE, UPDATE, SELECT

              DCL：权限管理

                     GRANT, REVOKE

     客户端命令：本地执行

    一、登录/退出MySQL

          登录

                登录本地数据库

                       匿名登录

                                   # mysql [数据库名称]

                                   # mysql -p [数据库名称]

                          指定用户登录

                                   # mysql -u 用户名 -p [数据库名称]

                                   # mysql -u 用户名 –p用户的密码 [数据库名称]

                远程登录数据库

                            # mysql -h主机 -u用户名 -p用户的密码 [数据库名称]

                注意：在登录时可指定数据库"[DATABASE_NAME]"

          退出

                     > exit

                     > quit

                     > \q

                     Ctrl+C

    二、选择/切换数据库：在数据库中执行的操作都要先选择数据库

              > USE数据库名称;

    三、数据库管理

          创建

                创建一个新的数据库

                            > CREATE {DATABASE | SCHEMA}数据库名称;

                创建一个新的数据库，如果数据库不存在，则创建

                            > CREATE DATABASE IF NOT EXISTS数据库名称;

                     注意：数据库名称在创建时定义，后期不可更改

          修改

                修改指定数据库的默认字符集

                            > ALTER DATABASE数据库名称 CHARACTER SET '字符集名称';

          查询

                查看当前设备上已有的数据库

                            > SHOW DATABASES;

                    查看以指定字符开头的数据库

                            > SHOW DATABASES LIKE 'name%';

                    查看以指定字符结尾的数据库

                            > SHOW DATABASES LIKE '%name';

                    查看数据库所支持的引擎

                            > SHOW ENGINES;

                  查看所支持的字符集

                            > SHOW CHARACTER SET;

                     常见字符集

                                   big5：繁体中文

                                   gb2312：简体中文

                                   utf8：统一的字符编码标准

                  查看字符集的排序方式

                            > SHOW COLLATION;

                    查看当前数据库的版本号和当前日期

                            > SELECT VERSION(), CURRENT_DATE;

          删除

                  删除数据库：删库需小心（MySQL没有回收站）

                            > DROP {DATABASE | SCHEMA}数据库名称;

                            > DROP {DATABASE | SCHEMA} [IF EXISTS]数据库名称;

                    建议把不用的数据库移动到其他位置，而不是删除

                            # mv /var/lib/mysql/DATABASE_NAME  /PATH/TO/DIRECTORY_NAME

    四、数据表管理

          创建

                  在数据库内创建数据表

                            > USE数据库名称;

                            > CREATE TABLE数据表名称 (字段1 数据类型 修饰符,字段2 数据类型 修饰符,字段3 数据类型 修饰符 ...) [table_option];

                    MySQL数据类型参考：点我查看

                      在创建数据表时还可以定义以下内容：

                            定义键

                                         主键：PRIMARY KEY (字段1,字段2, ...)

                                         唯一键：UNIQUE KEY  (字段1,字段2,...)

                                         外键：FOREIGN KEY (column)

                           定义索引

                                          KEY|INDEX [索引名称] (字段1,字段2,...)

> CREATE TABLE server ( IDC_ROOM VARCHAR(3) NOT NULL,RACK_SLOT VARCHAR(10)NOT NULL ,SN VARCHAR(30) PRIMARY KEY,HOSTNAME VARCHAR(16) NOT NULL,MACHINE_TYPE VARCHAR(10) NOT NULL ) ;

                           table_option:

                              | ENGINE [=] engine_name： 【定义存储引擎】

                              | AUTO_INCREMENT [=] value： 【设置默认自动增长数据从数字几开始算，默认为1】

                              | [DEFAULT] CHARACTER SET [=] charset_name： 【定义字符集，如不定义则从数据库继承】

                              | [DEFAULT] COLLATE [=] collation_name： 【定义排序规则】

                              | COMMENT [=] 'string'： 【定义数据表的注释信息】

                              | MAX_ROWS [=] value【定义最大行数】

                              | MIN_ROWS [=] value【定义最小行数】

                              | PASSWORD [=] 'string'【加密】

                             | ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}【定义行格式】

                                         DEFAULT：默认格式

                                         DYNAMIC：动态格式

                                         FIXED：固定长度

                                         COMPRESSED：压缩存储

                                         REDUNDANT：冗余存储

                                         COMPACT：紧致存储

                          键类型可以在字段中指明

                                   > CREATE TABLE数据表名称 (ID TINYINT NOT NULL AUTO_INCREMENT UNIQUE KEY,NAME VARCHAR(20) NOT NULL UNIQUE KEY);

                       也可以单独指明：把多个字段联合起来定义（多字段索引）

                                   > CREATE TABLE数据表名称 (ID TINYINT NOT NULL AUTO_INCREMENT ,NAME VARCHAR(20) NOT NULL,UNIQUE KEY(ID,NAME));

                                           注意：创建数据表的时候，表名不可使用"table"作为其名称

                在指定数据库中创建数据表

                            > CREATE TABLE数据库名称.数据表名称 (字段1 数据类型 修饰符,字段2 数据类型 修饰符,字段3 数据类型 修饰符 ...) [table_option];

          插入

                向数据表中插入数据

                        一次插入一行

                                   > INSERT INTO [数据库名称.]数据表名称(字段1,字段2,字段3 ...)

                       一次插入多行

                                   > INSERT INTO [数据库名称.]数据表名称(字段1,字段2,字段3 ...) {VALUES | VALUE} (DATE1: '字段1的值','字段2的值',字段3的值' ...),(DATE2: '字段1的值','字段2的值','字段3的值' ...) ...;

注意

                                   (1)使用INSERT语句，可以一次插入一行，也可以一次插入多行"(line1_data),(line2_data)..."

                                   (2)如果想用INSERT语句插入空值时，可以直接用NULL

                    替换表中已有数据：如果没有数据，则插入；如果已有数据，则替换（支持单行插入、多行插入、给定字段插入或全字段插入）

                            > REPLACE [INTO]数据表名称(字段1,字段2,字段3 ...) VALUES ('字段1的值','字段2的值','字段3的值','字段4的值','字段5的值','字段6的值')[,(line2_data)...];

                从文件中读取数据并插入到表中：一个空表，填充它的一个简易方法是创建一个文本文件，每条信息各一行，然后用一个语句将文件的内容装载到表中

                            > LOAD DATA LOCAL INFILE '/PATH/TO/ FILE_NAME' INTO TABLE [数据库名称.]数据表名称;

                     注意：如果需要填充空值时，使用（反斜线+字母N）表示

                      我演示的是从文件中导入到数据库，表中的敏感数据我已修改

                从EXCEL表格中插入数据：点我查看

          修改

                修改数据表的名称

                            > RENAME TABLE [数据库名称.]数据表名称 TO数据库名称.新表名称 ;

                            > ALTER TABLE [数据库名称.]数据表名称 RENAME TO数据库名称.新表名称;

                修改注释信息

                            > ALTER TABLE [数据库名称.]数据表名称 COMMENT = '注释信息';

                    给表中添加一个新字段

                            > ALTER TABLE数据表名称 ADD新字段名 数据类型 修饰符 [FIRST | AFTER字段名];

                                  指明字段位置

                                          FIRST：添加到第一字段

                                          AFTER：添加到哪个字段后面

                                         如果不指定字段位置，则放在最后

                修改字段名称(CHANGE)

                            > ALTER TABLE数据表名称 CHANGE原名称 新名称 原有属性信息（数据类型、修饰符...）;

                修改/更新指定字段

                            > UPDATE [LOW_PRIORITY] [IGNORE] [数据库名称.]数据表名称 SET字段1=字段1的值[,字段2=字段2的值] ... WHERE where_condition [ORDER BY ...] [LIMIT row_count]

                       注意：用UPDATE修改行时必须使用WHERE子句指明条件（尽量指主键），否则将修改所有

          查询

                  查询当前数据库中的数据表

                            > USE数据库名称;

                            > SHOW TABLES;

                  查询指定库中的数据表

                            > SHOW TABLES IN数据库名称;

                            > SHOW TABLES FROM数据库名称;

                    查看数据表的详细信息

                            > DESCRIBE [数据库名称.]数据表名称;

                            > DESC [数据库名称.]数据表名称;

                  查看数据表的状态信息

                            > SHOW TABLE STATUS [LIKE '数据表名称'];

                查看数据表的状态信息（垂直显示）

                            > SHOW TABLE STATUS [LIKE '数据表名称']\G;

                                         输出内容说明

                  查看创建命令

                           > SHOW CREATE TABLE [数据库名称.]数据表名称;

                显示以指定字符开头的表的状态

                            > SHOW TABLE STATUS WHERE Name LIKE '数据表名称%'\G;

                显示指定时间创建的表

                            > SHOW TABLE STATUS WHERE Create_time LIKE '时间%'\G;

           SELECT语句：用来从数据表中检索信息

                            > SELECT SELECT_EXPR FROM [数据库名称.]数据表名称1,[数据表名称2 ...] [WHERE where_condition];

SELECT语句的子句的执行过程

                           SELECT_EXPR

                                   *：所有字段（所有行，所有列）

                                   ALL

                                  字段1,字段2,字段3：投影；只显示指定字段

                                   DISTINCT：相同的数据，只显示一次

                                   field AS field_alias：给field取个别名field_alias

                           只查看表中不同的数据

                                   > SELECT DISTINCT FROM [数据库名称.] 数据表1,[数据表2 ...];

                          查看指定表中所有字段（慎用）

                                   > SELECT * FROM [数据库名称.]数据表名称1,[数据表2 ...];

                           查看指定表中的指定列（如果表太大则不适合执行）

                                   > SELECT字段1,字段2,字段3 ... FROM[数据库名称.]数据表名称;

                     定义显示的字段别名

                                   > SELECT字段 AS字段别名 FROM[数据库名称.]数据表名称;

                           FROM子句：指定要查询的关系

                                  单表查询（简单查询）：后面跟一个表名

                                  多表查询：后面跟多个表名

                                  子查询：后面跟WHERE子句

                            WHERE子句：选择、指明条件；只输出符合条件的行（重要）

                                   > SELECT字段1,字段2,字段3 ... FROM [数据库名称.]数据表名称 WHERE where_condition;

                                         where_condition：

                                            操作符

                                               算数运算

                                               比较表达式

                                          组合条件（逻辑运算）

                                                           注意：AND和OR可以混用，但AND比OR具有更高的优先级。如果你使用两个操作符，使用圆括号指明如何对条件进行分组是一个好主意(NOT xxx AND NOT xxx AND NOT xxx等于NOT ( xxx OR xxx OR xxx ) )

                                      字符串比较

                                            通配符

                                                               LIKE '模式'：支持使用“通配符”做模糊匹配

                                                                      %：任意长度的任意字符

                                                                      _：任意单个字符

                                            正则表达式

                                                               {RLIKE | REGEXP} '模式'：基基于正则表达式做模式匹配；不用索引、效率低

                                                              正则表达式元字符：点我查看

                     排序(ORDER BY)：将查询到的结果排序显示；如果表太大，排序操作极其消耗资源

                            > ORDER BY字段名称 {ASC | DESC}

                           升序：默认(ASC)

                                           > SELECT字段1,字段2,字段3 ... FROM[数据库名称.]数据表名称 ORDER BY 字段名称 ASC;

                           降序(DESC)

                                           > SELECT字段1,字段2,字段3 ... FROM[数据库名称.]数据表名称 ORDER BY 字段名称 DESC;

                                  注意：默认是区分字符大小写的，如不想区分字符大小写则执行“ORDER BY BINARY字段名称”

                      LIMIT子句

                                   LIMIT [offset,]count

                                   LIMIT #：只显示前#行的内容

                                   LIMIT x,y：忽略x行，显示y行后的行

                                          x：偏移量

                                          y：偏移后取几个值予以显示

                            注意：在MySQL中字符串必须加引号，而数值一定不能加引号

                     聚合计算

                                   > SELECT聚合计算函数 FROM[数据库名称.]数据表名称;

                                  聚合计算函数

                                          AVG(字段名称)：求平均值

                                          MAX(字段名称)：求最大值

                                          MIN(字段名称)：求最小值

                                          SUM(字段名称)：求合

                                          COUNT(字段名称)：求个数之和

                     分组(GROUP BY)：分组主要目的是为了做聚合计算

                                   > SELECT * FROM[数据库名称.]数据表名称 GROUP BY '字段名';

                                  对分组进行过滤

                                          > HAVING conditions;

          复制

                  复制表结构

                            > CREATE TABLE数据表名称 LIKE 原数据库.原数据表;

                复制表中的几个字段并将其创建成一个新表（不会复制其属性）

                            > CREATE TABLE数据表名称 SELECT字段1,字段2,字段3 ... FROM原数据库.原数据表;

          删除   

                删除表：删表需小心（MySQL没有回收站）

                            > DROP TABLE [IF EXISTS][数据库名称.]数据表名称;

                删除表中的行(DELETE)

                            > DELETE FROM[数据库名称.]数据表名称 [WHERE where_condition];

                            > DELETE FROM数据表名称 [ORDER BY ...] [LIMIT row_count]

                         注意：用DELETE删除行时必须使用WHERE子句，否则将清空整张表(DELETE FROM table_name)

                先排序后删除

                            > DELETE FROM[数据库名称.]数据表名称 [WHERE where_condition] [ORDER BY '字段名' [DESC]]

                删除字段（列）

                            > ALTER TABLE数据表名称 DROP字段名称;

                清空整张数据表

                            > TRUNCATE[数据库名称.]数据表名称;

    五、索引管理

           创建

                    创建索引

                            > CREATE {INDEX | INDEXES}索引名称 ON[数据库名称.]数据表名称(字段1,字段2,字段3 ...);

                创建多列索引

                            > CREATE UNIQUE {INDEX | INDEXES}索引名称 ON[数据库名称.]数据表名称;      

                    给表中指定的字段加入索引

                            > ALTER TABLE[数据库名称.]数据表名称 ADD INDEX(‘字段名称’);

           查询

                查看表中索引

                            > SHOW {INDEX | INDEXES} FROM[数据库名称.]数据表名称;

、        删除：索引不用时，要立即删除

                删除索引：这可能会导致查询操作降低

                            > DROP {INDEX | INDEXES}索引名称 ON[数据库名称.]数据表名称;

          注意：INDEX为一个索引，INDEXES为多个索引

    六、键管理

          创建

                 (1)在数据表创建的时候定义主键

                 (2)给已有的表中指定的字段加入主键

                            ALTER TABLE[数据库名称.]数据表名称 ADD {PRIMARY KEY | UNIQUE KEY}(‘字段名称’);

          修改

                   先删除键，再添加键

          查询

                  查询主键

                      > SHOW KEYS FROM [数据库名称.]数据表名称 WHERE key_name='PRIMARY';

          删除

                    删除键：不是自增长的才可以删除，如果是自增长（有auto_increment属性）必须先将表中其他属性删除

                            >ALTER TABLE [数据库名称.]数据表名称 DROP {PRIMARY KEY | UNIQUE KEY};

    七、用户管理

          MySQL用户和操作系统中的用户不同；不能用MySQL用户登录操作系统，同样也不能用操作系统用户访问MySQL

          用户是公开的，大家都可以看到；而密码只有某个授权使用用户才能获取

           MySQL使用自己独有的加密机制password()函数加密用户密码

           MySQL默认管理员是root，和操作系统管理员名称一致

           MySQL用户由'用户名'@'主机'共同组成

                    MySQL用户名：不能超过16个字符

                主机

                          主机名：zhangdaifu

                            IP地址：10.0.0.1

                          网络地址：10.0.0.1/255.0.0.0

                                  支持使用通配符表示：10.%.%._/255.0.0.0

                                         如需表示通配符本身的含义则需要进行转义\

          创建

                创建用户帐号

                            > CREATE USER '用户名'@'主机';

                  创建用户账号并设置其密码

                            > CREATE USER '用户名'@'主机' IDENTIFIED BY '密码';

          修改

                修改用户名

                            > UPDATE mysql.user SET USER='新用户名称' WHERE User='用户名';

             查询：MySQL用户在mysql.user表中保存

                查看所有用户信息