【数据库】SQL常用命令语法初级篇

我就是我 2022-06-03 01:58 251阅读 0赞

由于SQL学习的时候我是采用了MySQL，所以这里我就按照MySQL里的语法命令来操作，其它也差不多。这里主要是初级的语法语句，最关键的就是查询语句。还有其它的没有涉及，包括**存储过程、触发器、事件、过程编程、MySQL和Oracle等的比较**。

# 一、MySQL登录退出 #

mysql -uroot -V -hlocalhost -p12345678  --登录，“--”表示注释说明
    exit  -- 退出

### 提示符修改，结束符修改 ###

prompt \h~\u~\D~\d  -- 修改DBMS的提示符 mysqlț
    delimiter >> --修改终止符，默认是\g

### 数据库操作log记录 ###

\T C:/Mysql80/data/mysql_log.txt  -- 对数据库中的所有操作都记录在指定的文件中
    \t  -- 退出登记在log日志中

### 显示帮助 ###

help tinyint  -- 显示帮助信息
    ? index  -- 效果等同于 help

### 显示系统信息 ###

Select Now();  -- 显示当前的数据库
    SELECT USER();  -- 显示当前的用户
    SELECT VERSION();  -- 显示当前的版本信息

# 二、创建数据库 #

CREATE DATABASE IF NOT EXISTS cecil_db3 DEFAULT CHARACTER SET=gbk;
    CREATE SCHEMA cecil_db2;  -- 和创建数据库等效
    SHOW DATABASES;
    SHOW SCHEMAS;
    SHOW CREATE DATABASE information_schema;  -- 显示数据库的结构信息
    ALTER DATABASE cecil_db4 DEFAULT CHARACTER SET utf8;
    
    CREATE DATABASE IF NOT EXISTS \`cecil_db` DEFAULT CHARACTER SET 'utf8';
    USE cecil_db;
    -- 完整的一个创建表语句
    CREATE TABLE IF NOT EXISTS `user` (
    id SMALLINT,
    username VARCHAR(20),
    age TINYINT,
    gender ENUM('男','female','secret'),
    email VARCHAR(50),
    addr VARCHAR(200),
    birth YEAR,
    salary FLOAT,
    tel INT,
    married TINYINT(1) COMMENT '0代表没结婚，非0代表已婚'
    )ENGINE=INNODB CHARSET=UTF8;

### 查看指定表的结构 ###

DESC tbl_name;
    DESCRIBE tbl_name;
    SHOW COLUMNS FROM tbl_name;

USE cecil_db2;  -- 选择操作哪一个数据库
    SELECT DATABASE();  -- 显示当前正在操作的数据库名
    DROP DATABASE IF EXISTS cecil_db4;  -- 删除数据库
    SHOW WARNINGS;  -- 显示SQL语句执行完没有显示出来的WARNING信息

# 三、SQL的数据类型 #

*  **整数类型**：tinyint、smallint、int、mediumint、bigint。
 *  **浮点数类型**：FLOAT、DOUBLE、DECIMAL(***定点数，以字符串形式存放在数据库中，所以，字符串和数字类型在SQL中是相通的***)。
 *  **字符串类型**：  
    **CHAR**(M)：占用空间大，但是顺序排列，查询速度快，不保存字符串末尾的空格  
    **VARCHAR**(M)：变长，占用空间小，但是查询速度慢，保存所有字符串中的空格  
    TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT：查询速度最慢，以后能不用就不用这种类型  
    **ENUM**(‘val1’,’val2’,…)：可以插入NULL空值，自动去掉值后面的末尾的空格和 CHAR一样，数据库自动将各个值赋上索引。也就是说，当插入一个2时，系统自动增加一个 ‘val2’的值（从1开始的索引）  
    **SET**(‘val1’,’val2’,…)：集合中最多可以保存64个值。不可以有NULL，在权限管理里面会用到
 *  **日期时间类型**：TIME、DATE、DATETIME、TIMESTAMP、YEAR：可以识别两位数字表示的年份和字符串表示的年份
 *  **二进制类型**：BLOB、MEDIUMBLOB等。

# 四、存储引擎： #

### 显示存储引擎 ###

SHOW ENGINES;  -- 显示当前系统中有哪些存储引擎
    SHOW ENGINES\G;  -- 显示存储引擎的详细信息
    --*************************** 9. row ***************************
          Engine: ARCHIVE
         Support: YES  -- 表示当前系统是否支持该种存储引擎
         Comment: Archive storage engine  -- 指注释信息
    Transactions: NO  -- 指是否支持事务处理
              XA: NO  -- 指是否支持异步并发
      Savepoints: NO  -- 保留点

### 显示系统的各种变量 ###

SHOW VARIABLES LIKE 'have%';  -- 展示了系统中的各种变量信息
      SHOW VARIABLES LIKE 'show_engines';  -- 展示系统的存储引擎信息

### MySQLּ͛的几种存储引擎： ###

*  **INNODB**：  
    速度最慢，也是默认的存储引擎，支持全文索引，采用的也是B树，检索速度最慢，但是功能强大，支持外键。
 *  **MYISAM**：  
    采用B树索引，检索速度比INNODB快。
 *  **MEMORY**：  
    ֦该种方式采用Hash索引，查询速度最快，存储在内存中，但是无法长期保存。

# 五、向表中插入记录 #

INSERT tbl_name VALUE | VALUES(值,...);
    INSERT test2 VALUES(129,32768);

# 六、完整性约束 #

（这些都是加到属性后面的）

*  **PRIMARY KEY**：主键，一般加到一个无意义的字段上。
 *  **AUTO\_INCREMENT**：自增长，在insert过程中不需要明确指定该值，系统会自动增加。从1开始。
 *  **NOT NULL**：指定某个属性字段的值必须存在，不可以为空。
 *  **DEFAULT**：给字段添加默认值。一般都和 NOT NULL 配合使用，确保非空有值。
 *  **UNIQUE**：确保属性值唯一性的关键字，如果配合主键来使用，则写成UNIQUE KEY

### 查看创建表时的详细信息 ###

`SHOW CREATE TABLE test1;`

# 七、表结构相关操作命令: #

## 1、重命名数据表 ##

ALTER TABLE old_tbl_name RENAME TO new_tbl_name;
    ALTER TABLE old_tbl_name RENAME AS new_tbl_name;
    ALTER TABLE old_tbl_name RENAME new_tbl_name;
    RENAME TABLE old_tbl_name TO new_tbl_name;

## 2、添加和删除若干表中的属性（字段） ##

### 可以一次添加多个字段，也就是把ADD 和 DROP等字段分行写 ###

ALTER TABLE tbl_name [ADD | MODIFY] attr_name attr_type [constraints] [FIRST | AFTER attr];  -- FIRST、AFTER是指添加在哪个字段属性之后，默认添加到末尾
    ALTER TABLE tbl_name DROP attr_name;
    ALTER TABLE tbl_name CHANGE old_sttr_name new_attr_name [constraints] [FIRST | AFTER attr];  -- 功能要比MODIFY多一些，可以修改属性字段的名称。

### 添加和删除属性字段的默认值 ###

ALTER TABLE tbl_name ALTER attr_name SET DEFAULT default_val;  -- 添加属性的默认值
    ALTER TABLE tbl_name ALTER attr_name DROP DEFAULT;

### 添加和删除主键：（如果表格中已经有了数据，或主键已经自增长，很可能无法删除主键，我遇到了这些情况，此时应该先MODIFY 掉自增长关键字） ###

ALTER TABLE tbl_name ADD CONSTRAINT symbol PRIMARY KEY index_type(attr_name);  -- 系统默认给每个字段添加了一个索引，索引名就是属性字段名
    ALTER TABLE tbl_name DROP PRIMARY KEY;

### 添加和删除唯一性关键字：（添加唯一性的时候，可以给字段指定一个索引名称的） ###

-- 总结一下：PRIMARY KEY、KEY、UNIQUE KEY、INDEX的区别。
    ALTER TABLE tbl_name ADD UNIQUE(attr_name);
    -- 在创建表的时候，可以添加这样的语句：
    UNIQUE KEY `attr_index_name` (`attr_name`)  -- 也就是在属性添加了一个索引名称
    UNIQUE INDEX `attr_index_name` (`attr_name`) 
    -- 在这种情况下，添加唯一性关键字的时候就可以使用以下语句：
    ALTER TABLE tbl_name ADD CONSTRAINT symbol UNIQUE KEY attr_index_name (attr_name);
    ALTER TABLE tbl_name ADD CONSTRAINT symbol UNIQUE INDEX attr_index_name (attr_name);

### 更改表的存储引擎 ###

ALTER TABLE tbl_name ENGINE=engine_name;

### 修改自增长值的起始值： ###

ALTER TABLE tbl_name AUTO_INCREMENT=incre_num;

### 删除表： ###

DROP TABLE IF EXISTS tbl_name;

# 八、表中记录的操作 #

## 1、向表中插入记录： ##

INSERT [INTO] tbl_name VALUES(val1,val2,...);
    INSERT [INTO] tbl_name(attr_name1,attr_name2,...) VALUES(val1,val2,...);
    INSERT [INTO] tbl_name SET attr_name1=val1,attr_name2=val2,...;
    INSERT [INTO] tbl_name[(attr_name1,attr_name2,...)] SELECT * FROM tbl_name

## 2、更新表中记录：（如果不加WHERE 条件语句，所有记录都会被更新） ##

UPDATE tbl_name SET attr_name1=val1,attr_name2=val2;
    UPDATE tbl_name SET attr_name1=val1 WHERE attr_name2=val2;
    UPDATE tbl_name SET attr_name=attr_name-3 WHERE attr_name1>2;

## 3、删除表中某些记录：(如果不加WHERE 所有记录都会被删除) ##

DELETE FROM tbl_name; 
    DELETE FROM tbl_name WHERE ...;  -- 删除之后不重置AUTO_INCREMENT的值
    TRUNCATE TABLE tbl_name;  -- 两项操作，一个是删除表中的所有记录，第二个操作是删除表的结构

# 九、查询操作 #

## 1、单表查询 ##

SELECT tbl_name.* FROM tbl_name;  -- 如果各个表中的属性名有重复，需要指明到底是那个表里的属性
    SELECT attr1,attr2,attr3,... FROM tbl_name;
    SELECT attr1,attr2,attr3,... FROM db_name.tbl_name;  -- 指明到底是哪个数据库里的表
    SELECT tbl_name_abbre.attr1 AS attr_abbre,tbl_name_abbre.attr2,... FROM tbl_name AS tbl_name_abbre;  -- 给表名起别名，给字段起别名

### WHERE条件查询：(返回的都是集合) ###

WHERE 中的属性判断有以下几种符号：=、!=、<=>、IS \[NOT\] NULL（这两种可以查询NULL值）

WHERE attr BETWEEN val1 AND val2;
    WHERE attr IN(val1,val2,...);

#### 模糊查询 LIKE：%：0个1个或多个字符，\_代表1个任意字符 ####

SELECT * FROM tbl_name WHERE attr [NOT] LIKE '张%';
    SELECT * FROM tbl_name WHERE attr [NOT] LIKE '_in%';

#### 逻辑运算符 ####

SELECT * FROM tbl_name WHERE attr1=val1 AND [OR] attr2<val2;

### GROUP BY 分组查询，分组查询如果不指明只返回每一组的第一条记录 ###

SELECT * FROM tbl_name GROUP BY attr;  -- 组中每一个值的第一条记录
    SELECT * FROM tbl_name GROUP BY 9;  -- 这里9是指表中的第9列属性
    SELECT * FROM tbl_name GROUP BY attr1,attr2;  -- 这其实是交叉乘法得到的结果

#### 带有聚合函数的查询 ####

聚合函数包括SUM()、COUNT()、AVG()、MAX()、MIN()  
GROUP BY \[attr1,attr2,…\] **WITH ROLLUP** 是指记录上面所有记录的总和，是一个汇总的作用。

SELECT attr1,attr2,... GROUP_CONCAT(attr3) FROM tbl_name GROUP BY attr4;  -- 将attr3属性的值都串成一个串，得到详细信息

#### 带有 HAVING 的查询语句：是指通过分组查询出来的结果进行再筛选。 ####

SELECT attr1,GROUP_CONCAT(attr2) AS atttr2_abbre,
    COUNT(*) AS func1_name,
    MAX(attr3) AS func2_name,
    MIN(attr4) AS func3_name,
    SUM(attr5) AS func4_name
    FROM tbl_name
    GROUP BY attr6 WITH ROLLUP
    HAVING COUNT(*)>7 AND MAX(attr3)>60;

### ORDER 排序关键字：默认是升序ASC，但是可以降序DESC. ###

SELECT * FROM tbl_name ORDER BY attr1 [DESC | ASC];
    SELECT * FROM tbl_name ORDER BY CONCAT(attr1,attr2) DESC;
    -- 上面的一句效果等同于：
    SELECT * FROM tbl_name ORDER BY attr1 DESC, attr2 DESC;

### LIMIT 记录条数限制：指得到的结果集中的前几条记录 ###

SELECT * FROM tbl_name LIMIT 6;   --默认偏移量OFFSET是0
    SELECT * FROM tbl_name LIMIT 5,13;  -- 偏移量是5，条数限制是13
    UPDATE tbl_name SET attr=attr+8 LIMIT 4;  -- 更新前4条记录，对属性操作，此时如果加入偏移量会报错

## 2、连接查询：两个表之间的联合查询 ##

### 内连接查询 ###

SELECT tbl1_name.attr1,attr2,attr3 FROM tbl1_name,tbl2_name
    WHERE tbl1_name.attr=tbl2_name.attr;
    -- 该语句与内连接的效果相同,关键字INNER 和CROSS 都可以省略，效果相同
    SELECT u.attr1,u.attr2,u.attr3,p.attr1 
    FROM tbl1_name AS u
    [INNER | CROSS] JOIN tbl2_name AS p
    ON u.attr1=p.attr2
    WHERE ...;  -- 这就是内连接，WHERE内的内容和单表查询相同

### 外连接查询：左外连接显示左表中的全部记录及右表符合条件的记录，右外连接相反。 ###

LEFT [OUTER] JOIN 
            RIGHT [OUTER] JOIN

### 联合查询：多个表中的查询结果合并在一起。UNION（去掉相同记录的合并）、UNION ALL（简单的合并到一起。） ###

SELECT attr1 FROM tbl1_name UNION [ALL] SELECT attr2 FROM tbl2_name;

## 3、子查询：将一个查询语句嵌套在另外一个查询语句中。 ##

#### 由 IN、NOT IN 引发的子查询： ####

SELECT attr1,attr2 FROM tbl1_name WHERE attr3 [NOT] IN(SELECT attr FROM tbl2_name);

#### 比较运算符子查询： ####

SELECT attr1,attr2 FROM tbl1_name WHERE attr3>=(SELECT attr FROM tbl2_name);

#### 判断关键字子查询：看子查询中的结果是不是个empty set ####

SELECT attr1,attr2 FROM tbl1_name WHERE [NOT] EXISTS(SELECT attr FROM tbl2_name);

#### ANY|SOME|ALL 判断： ####

SELECT attr1,attr2 FROM tbl1_name WHERE attr3>=ANY [SOME](SELECT attr FROM tbl2_name);  -- 当子查询中的结果满足其中一个时
            SELECT attr1,attr2 FROM tbl1_name WHERE attr3>=[<>相当于NOT IN]ALL(SELECT attr FROM tbl2_name);  -- 当子查询中条件全部满足，即大于里面的最大值时

### 由子查询衍生出来的子插入，子创建插入： ###

INSERT tbl1_name(attr1,attr2) SELECT attr3,attr4 FROM tbl2_name;  -- 将表2中的数据插入表1
            CREATE TABLE tbl1_name(
            attr1 attr1_type,
            attr2 attr2_type
            )SELECT attr1,attr2 FROM tbl2_name;

### 正则表达式查询： ###

SELECT * FROM tbl_name WHERE attr REGEXP 'REG_EXPRESSION';

# 十、外键操作 #

外键是表的一个特殊字段，被参照的表是主表，外键所在的字段的表为子表，而依赖必须满足2NF，即依赖主键。父表中对记录做操作的时候，子表中与之对应的信息也应有相应的改变。父表和子表必须使用相同的存储引擎，且禁止使用临时表，只能是INNODB。外键列和参照列必须具有相似的数据结构，其中数字的长度和符号必须相同。字符串长度可以不同。外键列和参照列必须创建索引，如果不存在的话，DBMS会自动创建索引。删除外键的时候，索引不会被删除掉。

### 创建外键 ###

CREATE TABLE IF NOT EXISTS tbl_name(  -- 这是子表创建时的语句
    attr1 attr1_type,
    attr2 attr2_type,
    [CONSTRAINT `foreign_key_name`] FOREIGN KEY(attr1) REFERENCES tbl1_name(attr) ON DELETE CASCADE  ON UPDATE CASCADE  -- 可以给外键起一个名称
    )ENGINE=INNODB;
    ALTER TABLE tbl_name ADD [CONSTRAINT `foreign_key_name`] FOREIGN KEY(attr1) REFERENCES tbl1_name(attr);

### 删除外键： ###

ALTER TABLE tbl_name DROP FOREIGN KEY foreign_key_name;

外键操作的几个参数，都是物理外键：  
CASCADE、SET NULL、RESTRICT、NO ACTION(是SQL标准的关键字，和RESTRICT一回事)

# 十一、MySQL运算符 #

加减乘除、取余都有运算，支持数据类型转换，NULL有关的一切运算结果都是NULL。  
– 其他运算符与编程语言里都比较一致，唯一区别就是字符串表示的数字和数字类型的数字是相等的。

# 十二、MySQL系统函数 #

这里就不罗列了，实在太多，可参看[MySQL常用函数][MySQL]。

# 十三、索引 #

### 创建索引 ###

CREATE TABLE tbl_name(
    attr1 attr1_type,
    attr2 attr2_type,
    attr3 INT UNSIGNED AUTO_INCREMENT KEY,  -- AUTO_INCREMENT也是一种特殊的唯一性索引,主键也是索引
    attr4 VARCHAR(20) NOT NULL UNIQUE,  -- UNIQUE KEY也是一种索引
    attr5 attr5_type,
    attr6 GEOMETRY NOT NULL,  -- 空间数据类型
    INDEX index_name(attr1),  -- 给第一个属性创建一个索引，两种方法都可以
    KEY index_name1(attr2),  -- 创建索引，和INDEX是同义词
    FULLTEXT INDEX fulltext_index_name(attr5),  -- 创建全文索引
    INDEX mul_t1_t2(attr1,attr5,...) , -- 创建多列索引
    SPATIAL INDEX spa_index_name(attr6)  -- 创建空间索引
    )ENGINE=INNODB;

### 给已经存在的表添加索引 ###

CREATE [UNIQUE|...] INDEX [index_name] ON tbl_name(attr1,attr2,...);
    ALTER TABLE tbl_name ADD INDEX index_name(attr1,attr2,...);

### 删除索引 ###

DROP INDEX index_name ON tbl_name;

[MySQL]: http://blog.csdn.net/sugang_ximi/article/details/6664748