latte Studio

mysql之sql基础与mysql对象

sql语句与mysql常用对象
 2164  5 分钟  

sql简介

​ structure query language ，结构化查询语言，sql语句由dbms中的sql解释器进行解释执行，属于应用层协议

  • c/s架构
    • server，监听套接字，等待客户端请求
    • client，
      • 人操作接口：cli、gui
      • 编程操作接口：jdbc（java database connectivity），odbc等

sql标准

  • ANSI SQL(国际标准)
    • sql-1986
    • sql-1989
    • sql-1992
    • sql-1999
    • sql-2003
    • sql-2008
    • sql-2011
  • 微软和sybase的T-sql
  • Oracle的PL/sql

关系型数据库术语：

约束

​ 填入表格中的数据，必须要遵守的格式，规则的集合

  • 主键primary key：一张表中可以唯一标识一行的一个字段或多个字段的组合，即该字段的值在此表中有且唯一，不允许为空Not Null，即必须有值，一张表只能有一个主键！
  • 唯一键uniq key：一张表中一个字段或多个字段的组合，允许为空，即可以不填入值，该字段的值在此表中必须唯一，一张表可有多个唯一键；因此主键才是能唯一代表一条数据的标识
  • 检查：填入表中的数据，数据类型、占用空间大小，必须符合表字段的定义，才允许填入！

索引

​ 给表中某个字段复制一份，按照一定规则排序后进行存储；

​ 比如：给表的性别字段做索引，建好后利用性别查询时如男，只需检索性别为男的数据条目即可；（男女数据量相等时，减少了一半的查询量）

​ 比如：给某数字类型字段建立索引，按照大小顺序排列，建好后根据该字段查询，如socre=666，根据2分查找可以很快找到score=666的索引数据，之后根据和该索引一起存储的“指针”，由指针指向找到对应的一整条数据提高了查询效率，避免了全表扫描

​ 直接索引：一般是用主键做索引当作直接索引，索引的指针，直接指向对应的一条数据

​ 间接索引：其他字段建立索引，其指针指向的是直接索引的数据项，然后再根据直接索引的指针最终找到对应的一条数据

数据库database

database，一个数据库实例，可以有多个database数据库，每个库在数据目录下都是一个文件夹；

表table

由行和列组成；

视图view

对实际存储的表抽离出一定的字段组成的特殊表，实际不占用存储空间，只是表数据的截取，与拼接；如可以剥离一些敏感字段，组成一个视图开放给某用户

用户user

可以登陆连入mysql实例的用户，分为root用户和普通用户；

权限privilege

用户对数据库对象，如：表、数据库、函数、等的权限，权限有：读、修改、执行等；

存储过程procedure

用户定义的一段执行逻辑，具有一定功能

存储函数function

用户定义的一段执行逻辑，和procedure的区别，可以接收参数，具有返回值

触发器trigger

定义在某字段的触发器，如该字段的输入的值大于某值，就触发某个操作；

事件调度器event scheduler

​ 类似于linux的任务计划

sql语句分类

  • DDL：数据定义语言
    • 创建或删除库、表，或者改变它们的属性定义
    • create
    • drop
    • alter
  • DML：数据管理语言
    • 插入、修改、删除，表中某行的数据
    • insert
    • update
    • delete
  • DCL：数据控制语言
    • 权限的授予和收回；事务的提交和回滚；
    • grant
    • revoke
    • commit
    • rollback
  • DQL：数据查询语言
    • 数据查询
    • select

select查询

单表查询

多表查询

查看帮助

help

​ help查看的时客户端sql命令行的帮助信息，非mysql的；

MariaDB [(none)]> help

General information about MariaDB can be found at
http://mariadb.org

List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
?         (\?) Synonym for `help'.
clear     (\c) Clear the current input statement.
connect   (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.

help可以查看的帮助

​ help后加不同的子句，可以查看对应的帮助信息，如create的建库、建表语法；show的查看已经存在的库、表的定义时属性；（字符编码、字段类型、表结构定义等）

MariaDB [(none)]> help create
Many help items for your request exist.
To make a more specific request, please type 'help <item>',
where <item> is one of the following
topics:
   CREATE DATABASE
   CREATE EVENT
   CREATE FUNCTION
   CREATE FUNCTION UDF
   CREATE INDEX
   CREATE PROCEDURE
   CREATE SERVER
   CREATE TABLE
   CREATE TABLESPACE
   CREATE TRIGGER
   CREATE USER
   CREATE VIEW
   SHOW
   SHOW CREATE DATABASE
   SHOW CREATE EVENT
   SHOW CREATE FUNCTION
   SHOW CREATE PROCEDURE
   SHOW CREATE TABLE
   SPATIAL

建表语法

MariaDB [(none)]> help create table
Name: 'CREATE TABLE'
Description:
Syntax:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]

Or:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)]
    [table_options]
    [partition_options]
    select_statement
...

插入数据语法

​ 每个帮助下面会给出官网更详细的语法介绍，地址；

MariaDB [(none)]> help insert
Name: 'INSERT'
Description:
Syntax:
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [(col_name,...)]
    {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

Or:

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    SET col_name={expr | DEFAULT}, ...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]
INSERT inserts new rows into an existing table. The INSERT ... VALUES
and INSERT ... SET forms of the statement insert rows based on
explicitly specified values. The INSERT ... SELECT form inserts rows
selected from another table or tables. INSERT ... SELECT is discussed
further in [HELP INSERT SELECT].

URL: https://mariadb.com/kb/en/insert/

创建数据库

MariaDB [(none)]> create database db1;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| db1                |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]> show create database db1;
+----------+----------------------------------------------------------------+
| Database | Create Database                                                |
+----------+----------------------------------------------------------------+
| db1      | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> alter database db1 character set utf8;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> show create database db1;
+----------+--------------------------------------------------------------+
| Database | Create Database                                              |
+----------+--------------------------------------------------------------+
| db1      | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)
# 创建库db1，默认编码英文，修改为utf8以支持中文；

创建表

# 切换到db1库，创建stu1表，共有3个字段，分别为id name age，其中id为主键；
# create table tb_name(字段名 类型 修饰符,字段名 类型 修饰符,...);
# 修饰符为定义为主键，非空，无符号整数，取值范围等一系列该字段填充数据应该遵守的约束！

MariaDB [(none)]> use db1;
Database changed

MariaDB [db1]> create table stu1 (id int UNSIGNED NOT NULL PRIMARY KEY,name VARCHAR(20) NOT NULL, age tinyint UNSIGNED);
Query OK, 0 rows affected (0.02 sec)


MariaDB [db1]> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| stu1          |
+---------------+
1 row in set (0.00 sec)

MariaDB [db1]> show create table stu1;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                               |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| stu1  | CREATE TABLE `stu1` (
  `id` int(10) unsigned NOT NULL,
  `name` varchar(20) NOT NULL,
  `age` tinyint(3) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

数据类型

https://mariadb.com/kb/en/data-types/

数据类型包含2方面定义：数据的分类是数字还是日期等，数据占用的空间大小是多少；如int（）表示存储的是整数数值，默认磁盘上占据4个字节大小；

  • 整型
  • 浮点型
  • 字符型
  • 字符串型
  • 日期
  • 用户自定义数据类型
updatedupdated2021-03-092021-03-09
加载评论