SQL(一)

来源:互联网 时间:1970-01-01


1. SQL介绍:

数据库用来存储和管理数据。数据库的优点在于:

  1. 可以存储大量数据;
  2. 方便检索;
  3. 保持数据的一致性,完整性;
  4. 安全,共享:
  5. 通过组合分析,可以获得新的数据。

SQL(Structured Query Language)为结构化查询语言,用来操作关系型数据库。常见的关系型数据库有:Oracle,DB2,SQL Server,Sybase,MySQL等。各个数据库不仅支持标准的SQL语法,不同的数据库还支持一些独有的语法。

RDBMS(Relational database management system)指关系型数据库管理系统,是SQL的基础。

  • RDBMS指由管理员(manager)和数据库(database)的组合。
  • database由多个数据表(table)组成。
  • table包括表结构和表记录。表结构定义表的列名和列类型;表记录即一行一行的数据记录。

2. MySQL:

1. 介绍:

MySQL为关系型数据库的一种,MySQL的数据存储目录为安装路径下的data文件夹,在data文件夹下,每个目录即为一个数据表。bin文件夹中存储可执行文件,my.ini为文件为MySQL的配置文件。

2. 基本命令:

  1. 启动和关闭:

    启动MySQL使用* ‘net start mysql’ 命令,关闭MySQL使用 ‘net stop mysql’*服务。

  2. 登陆退出:

    登陆使用 ‘mysql -uroot -p123 -hlocalhost’命令,该命令中:

    • -u :后面的root是用户名,root为超级管理员用户名。
    • -p :后面的123是登陆密码,是安装mysql时使用的密码。
    • -h :后面的localhost是本地主机名,可以通过远程登陆服务器主机名来实现远程操作数据库。

3. SQL语句:

1. 概述:

使用SQL语句可以实现对关系型数据库的各种操作。在格式上,SQL有如下要求:

  • SQL语句可以单行或者多行书写,以分好为结尾;
  • 可以使用空格和缩进来增强语句的可读性;
  • 关键字不区分大小写(建议使用大小写)。

按照功能的不同,SQL语句可以分为以下四类:

  1. DDL: DDL(Data Definiton Language)数据定义语言,用来定义数据库对象:库,表,列等。
  2. DML: DML(Data Manipulation Language)数据操作语言,用来修改数据库记录。
  3. DCL: DCL(Data Control Language)数据控制语言,用来定义访问权限和安全级别。
  4. DQL: DQL(Data Query Language)数据查询语言,用来查询符合条件的数据。

2. DDL:

  1. 基本操作:

    • 查看所有数据库名称:SHOW DATABASES
    • 切换数据库:USE + 数据库名。例如: USE mydb,切换到mydb数据库。
  2. 操作数据库:

    • 创建数据库:CREATE DATABASE ( IF NOT EXISTS ) + 数据库名

    创建数据库时,如果使用CREATE DATABASE + 数据库名,如果数据库已经存在,则会报错。在中间加上条件判断 IF NOT EXITS即CREATE DATABASE IF NOT EXISTS +数据库名则会在数据库不存在时创建数据库,避免出现错误。

    • 删除数据库:DROP DATABASE (IF EXISTS) + 数据库名

    同创建数据库一样,通过首先判断数据库是否存在,在数据库存在的情况下删除数据库可以有效避免出错。

    • 修改数据库编码:* ALTER DATABASE + 数据库名 + CAHRACTER SET + 字符编码类型*

    修改数据库的编码为指定的字符编码类型。注:指定为utf8不能带中间的短线,即不能使用utf-8。

  3. 数据类型:

    MySQL的数据类型主要用于列上,常用的类型可分为文本,数字和日期/时间类型:

    • 文本类型:
    CHAR(size) 保存固定长度的字符串(可包含字母、数字以及特殊字符)。在括号中指定字符串的长度。最多 255 个字符。 VARCHAR(size) 保存可变长度的字符串(可包含字母、数字以及特殊字符)。在括号中指定字符串的最大长度。最多 255 个字符。 TINYTEXT 存放最大长度为 255 个字符的字符串。 TEXT 存放最大长度为 65,535 个字符的字符串。 LONGTEXT 存放最大长度为 4,294,967,295 个字符的字符串。
    数据类型 描述
    • 数字类型:
    TINYINT(size) -128 到 127 常规。0 到 255 无符号*。在括号中规定最大位数。 SMALLINT(size) -32768 到 32767 常规。0 到 65535 无符号*。在括号中规定最大位数。 MEDIUMINT(size) -8388608 到 8388607 普通。0 to 16777215 无符号*。在括号中规定最大位数。 INT(size) -2147483648 到 2147483647 常规。0 到 4294967295 无符号*。在括号中规定最大位数。 FLOAT(size,d) 带有浮动小数点的小数字。在括号中规定最大位数。在 d 参数中规定小数点右侧的最大位数。 DOUBLE(size,d) 带有浮动小数点的大数字。在括号中规定最大位数。在 d 参数中规定小数点右侧的最大位数。 DECIMAL(size,d) 作为字符串存储的 DOUBLE 类型,允许固定的小数点。
    数据类型 描述
    • 日期/时间类型:
    DATE() 日期。格式:YYYY-MM-DD。 支持的范围是从 ‘1000-01-01’ 到 ‘9999-12-31’ DATETIME() 日期和时间的组合。格式:YYYY-MM-DD HH:MM:SS。 支持的范围是从 ‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’ TIMESTAMP() 时间戳。TIMESTAMP 值使用 Unix 纪元(‘1970-01-01 00:00:00’ UTC) 至今的描述来存储。格式:YYYY-MM-DD HH:MM:SS TIME() 时间。格式:HH:MM:SS。
    数据类型 描述
  4. 操作表:

    • 创建表:

    使用CREATE TABLE语句来创建一个数据表:

    CREATE TABLE 表名(
    列名1 列类型,
    列名2 列类型,
    列名3 列类型,

    );

    • 查看当前库所有表名称:SHOW TABLES;
    • 查看指定表的创建语句:SHOW CREATE TABLE + 表名;
    • 查看表结构:DESC + 表名;
    • 删除表:DROP TABLE + 表名;
    • 修改表:

      • 添加列: ALTER TABLE + 表名 + ADD +(列名 列类型);
      • 修改列类型:ALTER TABLE + 表名 + MODIFY + 列名 +类型;
      • 修改列名:ALTER TABLE + 表名 + CHANGE + 列名 + 新列名 + 新列名类型;
      • 删除列:ALTER TABLE + 表名 + DROP + 表名;
      • 修改表名称:ALTER TABLE + 表名 + RENAME TO + 新表名

3. DML:

  1. 插入数据:

    • INSERT INTO + 表名(列名1, 列名2, 列名3…) + VALUES(值1,值 2…)
      其中,列的个数可选,值的个数和类型要与列名对应。对于没有指定值的列,默认为null。
    • INSERT INTO + 表名 + VALUES(值1, 值2,…)
      在没有指定列名的情况下,默认按照创建表时的顺序创建各列的值。
  2. 修改数据:

    UPDATE + 表名 + SET + 列名1=值1,列名2=值2,列名3=值3… + WHERE 条件
    对于条件而言,可以是指定的列名以及对应的值,如: age=23;表示修改age为23的列。
    也可以是某些范围之内的条件例如:age > 20;表示修改age大于20的列。
    也可以使用逻辑判断符号 AND, OR 等来实现多个条件进行判断。

  3. 删除数据:

    DELETE FROM + 表名 + WHERE 条件
    删除表的WHERE条件与修改数据的WHERE条件相似。注意的是,如果不使用WHERE条件,直接使 用 DELETE FROM + 表名 的方式,会一条一条地将表中所有的数据删除。
    此外,删除整个表数据还可以使用TRUNCATE + 表名语句,与上面删除操作不同的是,该 语句直接删除整张表,然后再创建一张空表,效率相对较高。

4. DCL:

  1. 创建用户:CREATE USER + 用户名@地址 + IDENTIFIED BY ‘密码’
    注意:密码要使用单引号包裹起来。

  2. 给用户授权:GRANT + 权限1,权限2,权限3…..ON + 数据库名.表名 + TO + 用户名@地址
    权限包括CREATE, DROP ,ALTER, INSERT ,UNDATE, DELETE等。其中,使用数据库+点+表名告诉数据库用户可以在哪些数据表中使用这些权限,使用通配符*默认当前所有表,使用ALL代替各种权限名称将将所有权限赋给用户。

  3. 撤销权限:REVOKE 权限1,权限2,权限3…ON + 数据库名.表名 + FROM + 用户名@地址

  4. 查看权限:SHOW GRANTS FOR 用户名@地址

  5. 删除用户:DROP USER 用户名@地址

  6. 修改密码:UPDATE USER SET PASSWORD=PASSWORD(‘密码’) + WHERE + USER=’用户名’ and HOST=’IP’

5. DQL:

  1. 基础查询:

    • 查询所有列:SELECT * FROM + 表名
    • 查询指定列:SELECT 列名1,列名2,列名3 FROM + 表名
  2. 条件查询:

    1. 介绍:
      条件查询就是使用WHERE条件来限定查询的范围,在WHERE子句中可以使用以下关键字:

      • =、!=、<>、<、<=、>、>=;
      • BETWEEN…AND;
      • IN(set);
      • IS NULL;
      • AND,OR , NOT;
    2. 例子:
      假设现在有一张学生表stu,通过条件查询来查询不同数据:

      • 查询性别为女,分数在80以上的女生的数据:SELECT * FROM stu WHERE gender=‘female’ AND score>80;
      • 查询学号为123和姓名为Bob的数据:SELECT * FROM stu WHERE id=123 OR name=’Bob’;
      • 查询学号为125,130,160的数据:SELECT * FROM stu WHERE id IN (125,130,160);
      • 查询学号不是125,130,160的数据:SELECT * FROM stu WHERE id NOT IN (125,130,160);
      • 查询成绩为NULL的数据:SELECT * FROM stu WHERE score IS NULL;
      • 查询成绩在60-80之间的数据:SELECT * FROM stu WHERE score BETWEEN 60 AND 80 或者 SELECT * FROM stu WHERE score >= 60 AND score <= 80;
      • 查询爱好不是学习的学生(23333):SELECT * FROM stu WHERE hobby!=’learn’或者 SELECT * FROM stu WHERE hobby<>’learn’。<>表示不相等,不是的意思。
  3. 模糊查询:

    1. 介绍:

    当查询的条件不是特别精确时,可使用模糊查询来查询数据。模糊查询使用关键字LIKE

    1. 例子:
      • 查询学生姓名为5个字母组成的数据:SELECT * FROM stu WHERE name LINE ‘___。其中,下划线‘_’匹配任意字符,5个下划线匹配任意五个字符;
      • 查询学生姓名为5个字符,且第五个字母为i的数据:SELECT * FROM stu WHERE name LIKE ‘____i’
      • 查询学生姓名以Z开头的数据:SELECT * FROM stu WHERE name LIKE ‘Z%’。其中%匹配任意0~n个字符。
  4. 字段控制查询:

    • 去除重复记录:

    当某列中有多个数据相同,则使用DISTINCT关键字在查询时可以去除相同的数据。例如,查询学生成绩为哪些数值,则:
    SELECT DISTINCT FROM stu

    • 查看列的运算结果:

    当查询几个列的数据进行运算获取结果时,可使用运算子句将结果返回用于查询。注意:参与运算列的数据的类型需要相同。例如,查询学生的总成绩 = 语文 + 数学 + 英语:
    SELECT *, Chinese + math +English FROM stu

    • 给列名添加别名:

    对于上面的查询结果,列名会显示Chinese + math + English,可以为该列取一个更加简洁易明的别名sum,语句如下:
    SELECT *,ch+math+en AS SUM FROM stu
    其中,AS可以省略。

  5. 排序:

    • 升序排列:升序排列使用ASC关键字。
      例如学生表按照总成绩升序排序,语句如下:
      SELECT * FROM stu ORDER BY sum ASC;因为MySQl默认使用升序排列,可以省略关键字ASC。
    • 降序排列:降序排列使用DESC关键字。
      如上,如果按照总成绩的降序排列,语句如下:
      SELECT * FROM stu ORDER BY sum DESC

    • 对于某一列数据相同时,我们可以再选择一列进行排序。比如:学生表中如果两个学生的总成绩相同,再按照语文成绩进行排序,如下:
      SELECT * FROM stu ORDER BY sum DESC, Chinese DESC

  6. 聚合函数:

    聚合函数是用来做纵向运算的函数:

    • COUNT():统计指定列不为NULL的记录行数;
    • MAX():计算指定列的最大值,如果指定列是字符串类型,那么使用字符串排序运 算;
    • MIN():计算指定列的最小值,如果指定列是字符串类型,那么使用字符串排序运 算;
    • SUM():计算指定列的数值和,如果指定列类型不是数值类型,那么计算结果为0;
    • AVG():计算指定列的平均值,如果指定列类型不是数值类型,那么计算结果为0;

    例如,查询表中的记录数,可使用COUNT语句,如下:
    SELECT COIUNT(*) FROM stu;
    查询数学列的平均值:
    SELECT AVG(math) FROM stu

  7. 分组查询:

    例如,现在学生表为多个班级的学生表,现在我们统计每个班级的平均分,这时可以使用分组查询。分组查询使用GROUP BY,查询平均分语句如下:
    SELECT AVG(sum) FROM stu GROUP BY class;

    如果要求获取每个班级数学成绩及格同学的平均成绩,可以使用WHERE子句进行条件限制:
    SELECT AVG(math) FROM stu GRUOP BY class WHERE math>60;

    此外,还可以使用HAVING子句进行条件限制,与WHERE不同的地方在于,WHERE在选择之前进行条件匹配,HAVING在选择之后进行条件匹配。上面的语句可以写成一下形式:
    SELECT AVG(math) FROM stu GRUOP BY class HAVING math>60

  8. LIMIT:

    LIMIT关键字用于限定查询的起始行和查询行数。例如,从第三行开始查询,共查询十 行:
    SELECT * FROM stu LIMIT 3,10;
    注:起始行为0。

4. 完整性约束:

完整性约束限定了表格数据的一些格式,使得数据按照一定的格式进行添加,保证了数据的准确性。

1. 主键:

当某一列添加了主键约束后,那么这一列的数据就不能重复出现。这样每行记录中其主键列的值就是这一行的唯一标识。主键列的值不能为NULL,也不能重复!

指定主键约束使用PRIMARY KEY。创建主键有两种方式:一是在创建表时指定主键;二十在创建表后指定主键。如学生表中指定id为主键。两种方式分别如下:

  1. 创建表时指定主键:CREATE TABLE stu (id INT PRIMARY KEY, name verchar(20),……);

  2. 创建表后指定主键:ALTER TABLE stu ADD PRIMARY KEY(id)

删除主键约束时使用DROP关键字:ALTER TABLE stu DROP PRIMARY KEY

2. 主键自增长:

MySQL提供了主键自动增长的功能。当主键设置为自动增长后,在没有给出主键值时,主键的值会自动生成,而且是最大主键值+1,也就不会出现重复主键的可能了。

设定主键自增长使用AUTO_INCREMENT。同样,可以在创建表时指定主键自增长,也可以在之后指定主键自增长。

  1. 创建表时指定主键自增长:CREATE TABLE stu (id INT PRIMARY KEY AUTO_INCREMENT, name verchar(20),……);

  2. 创建表后指定主键自增长:ALTER TABLE stu CHANGE id id INT AUTO_INCREMENT

删除主键自增长限制的语句如下:ALTER TABLE stu CHANGE id id INT

3. 非空:

指定非空约束的列不能没有值,也就是说在插入记录时,对添加了非空约束的列一定要给值;在修改记录时,不能把非空列的值设置为NULL。非空约束使用关键字NOT NULL
例如,学生表姓名一栏不能为空,则在创建表时可以使用NOT NULL,如下:

CREATE TABLE stu (id INT PRIMARY KEY AUTO_INCREMENT, name VERCHAR(20)NOT NULL,……)

之后,在插入数据时,如果学生姓名为空,则会报错。

4. 唯一:

还可以为字段指定唯一约束!当为字段指定唯一约束后,那么字段的值必须是唯一的。这一点与主键相似!例如给stu表的姓名一栏设置唯一约束:
CREATE TABLE stu (id INT PRIMARY KEY AUTO_INCREMENT, name VERCHAR(20)NOT NULL

插入数据时,如果插入的名字在当前表中存在则报错。

5. 外键:

主外键是构成表与表关联的唯一途径!外键是另一张表的主键,用来约束这一列的值必须是另一张表的主键值!

比如学生表中每个学生都有一个班级属性,此外有一张年级表,年级表的主键为班级号。这样,学生表中的班级类的值和年级表的主键值对应起来,因此可以使用年级表的主键来约束学生表的班级列。创建该外键约束的过程如下:

  1. 第一步:创建年级表:

CREATE TABLE grade (
g_id INT PRIMARY KEY AUTO_INCREMENT,
stu_cont INT,
……
);

  1. 第二步:创建学生表并指定外键约束:

    • 在创建表的时候指定外键:
      CREATE TABLE stu (
      s_id INT PRIMARY KEY AUTO_INCREMENT,
      s_class INT,
      CONSTRAINT fk_grade FOREIGN KEY(s_class)REFRENCES grade(g_id);
      );

    • 创建表之后指定外键:
      ALTER TABLE STU ADD CONSTRAINT fk_grade FOREIGN KEY(s_class)REFERENCES grade(g_id);

5. 备份 & 还原:

  1. 生成sql脚本:

    在控制台使用mysqldump命令可以用来生成指定数据库的脚本文本,但要注意,脚本文本中只包含数据库的内容,而不会存在创建数据库的语句!所以在恢复数据时,还需要自已手动创建一个数据库之后再去恢复数据。备份数据库内容语句如下:

    mysqldump –u用户名 –p密码 数据库名>生成的脚本文件路径

    注意,mysqldump命令是在Windows控制台下执行,无需登录mysql。

  2. 执行sql脚本:

    执行SQL脚本需要登录mysql,然后进入指定数据库,才可以执行SQL脚本!!!
    执行SQL脚本不只是用来恢复数据库,也可以在平时编写SQL脚本,然后使用执行SQL 脚本来操作数据库!
    执行sql脚本有两种方式,分别如下:

    • 登陆MySQL后,执行 SOURCE 脚本文件路径
    • 执行mysql –u用户名 –p密码 数据库<要执行脚本文件路径



相关阅读:
Top