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

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

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

一、MySQL登录退出

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

提示符修改，结束符修改

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

数据库操作log记录

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

显示帮助

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

显示系统信息

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

二、创建数据库

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

查看指定表的结构

  1. DESC tbl_name;
  2. DESCRIBE tbl_name;
  3. SHOW COLUMNS FROM tbl_name;
  5. USE cecil_db2;  -- 选择操作哪一个数据库
  6. SELECT DATABASE();  -- 显示当前正在操作的数据库名
  7. DROP DATABASE IF EXISTS cecil_db4;  -- 删除数据库
  8. 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等。

四、存储引擎：

显示存储引擎

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

显示系统的各种变量

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

MySQLּ͛的几种存储引擎：

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

五、向表中插入记录

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

六、完整性约束

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

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

查看创建表时的详细信息

SHOW CREATE TABLE test1;

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

1、重命名数据表

  1. ALTER TABLE old_tbl_name RENAME TO new_tbl_name;
  2. ALTER TABLE old_tbl_name RENAME AS new_tbl_name;
  3. ALTER TABLE old_tbl_name RENAME new_tbl_name;
  4. RENAME TABLE old_tbl_name TO new_tbl_name;

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

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

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

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

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

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

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

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

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

更改表的存储引擎

  1. ALTER TABLE tbl_name ENGINE=engine_name;

修改自增长值的起始值：

  1. ALTER TABLE tbl_name AUTO_INCREMENT=incre_num;

删除表：

  1. DROP TABLE IF EXISTS tbl_name;

八、表中记录的操作

1、向表中插入记录：

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

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

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

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

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

九、查询操作

1、单表查询

  1. SELECT tbl_name.* FROM tbl_name;  -- 如果各个表中的属性名有重复，需要指明到底是那个表里的属性
  2. SELECT attr1,attr2,attr3,... FROM tbl_name;
  3. SELECT attr1,attr2,attr3,... FROM db_name.tbl_name;  -- 指明到底是哪个数据库里的表
  4. 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值）

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

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

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

逻辑运算符

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

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

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

带有聚合函数的查询

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

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

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

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

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

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

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

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

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

内连接查询

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

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

  1. LEFT [OUTER] JOIN 
  2.         RIGHT [OUTER] JOIN

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

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

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

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

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

比较运算符子查询：

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

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

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

ANY|SOME|ALL 判断：

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

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

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

正则表达式查询：

  1. SELECT * FROM tbl_name WHERE attr REGEXP 'REG_EXPRESSION';

十、外键操作

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

创建外键

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

删除外键：

  1. ALTER TABLE tbl_name DROP FOREIGN KEY foreign_key_name;

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

十一、MySQL运算符

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

十二、MySQL系统函数

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

十三、索引

创建索引

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

给已经存在的表添加索引

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

删除索引

  1. DROP INDEX index_name ON tbl_name;

发表评论

表情：
评论列表 （有 0 条评论，350人围观）

还没有评论，来说两句吧...

相关阅读