ql语

ql语

关注
发信
关注(28)粉丝(399)

SQL语法：MySQL系列之四

一、SQL语言的简介和规范

​是一种特定目的程序语言，用于管理关系数据库管理系统（RDBMS），或在关系流数据管理系统（RDSMS）中进行流处理。

  • 20世纪70年代，IBM开发出SQL，用于DB2
  • 1981年，IBM推出SQL/DS数据库
  • 业内标准微软和Sybase的T-SQL，Oracle的PL/SQL
  • SQL作为关系型数据库所使用的标准语言，最初是基于IBM的实现在1986年被批准的。1987年，“国际标准化组织(ISO)”把ANSI(美国国家标准化组织) SQL作为国际标准。
  • SQL：ANSI SQL ——SQL-86, SQL-89, SQL-92, SQL-99, SQL-03

SQL语言的规范

  1. 在数据库系统中，SQL语句不区分大小写(建议用大写)

  2. 但字符串常量区分大小写

  3. SQL语句可单行或多行书写，以“;”结尾

  4. 关键词不能跨多行或简写

  5. 用空格和缩进来提高语句的可读性

  6. 子句通常位于独立行，便于编辑，提高可读性

  7. 注释：

    • SQL标准：

      • /* 注释内容 */ 多行注释
      • -- 注释内容 单行注释，注意有空格
    • MySQL注释： #

  • 数据库对象的命名规则

     - 必须以字母开头
    
 - 可包括数字和三个特殊字符（# _ $）
    
 - 不要使用MySQL的保留字
    
 - 同一database(Schema)下的对象不能同名

SQL语句的分类

  • DDL: Data Defination Language 数据定义语言

    - CREATE, DROP, ALTER

  • DML: Data Manipulation Language 数据操作语言

    - INSERT, DELETE, UPDATE

  • DCL：Data Control Language 数据控制语言

    - GRANT, REVOKE

  • DQL：Data Query Language 数据查询语言

    - SELECT

二、数据库操作

1、创建库

CREATE DATABASE [IF NOT EXISTS] db_name; 创建数据库

CHARACTER SET 'character set name' 设置字符集类型

COLLATE 'collate name' 设置排序规则

MariaDB [(none)]> CREATE DATABASE IF NOT EXISTS testdb;

2、删除库

​我不会

3、查看数据库列表

SHOW DATABASES;

三、表操作

1、创建表

  • 方法一： 直接创建

CREATE TABLE [IF NOT EXISTS] tbl_name (create_definition,...)

MariaDB [testdb]> CREATE TABLE IF NOT EXISTS students (id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(30) NOT NULL,phone CHAR(11),gender ENUM('M','F'));
  • 方法二： 通过查询现存表创建；新表会被直接插入查询而来的数据

CREATE TABLE [IF NOT EXISTS] tbl_name select_statement

MariaDB [testdb]> CREATE TABLE user SELECT user,host,password FROM mysql.user;


MariaDB [testdb]> CREATE TABLE user2 SELECT user,host,password FROM mysql.user WHERE 0=1;
  • 方法三： 通过复制现存的表的表结构创建，但不复制数据

CREATE TABLE [IF NOT EXISTS] tbl_name LIKE old_tbl_name

MariaDB [testdb]> CREATE TABLE user3 LIKE mysql.user;

2、修改表

ALTER TABLE tbl_name [alter_specification [, alter_specification] ...]

  • 增加属性 ADD
MariaDB [testdb]> ALTER TABLE students ADD age TINYINT AFTER name;
  • 删除属性 DROP
MariaDB [testdb]> ALTER TABLE students DROP phone;
  • 修改属性 CHANGE, MODIFY
MariaDB [testdb]> ALTER TABLE students CHANGE age ages TINYINT(2) NOT NULL;


MariaDB [testdb]> ALTER TABLE students MODIFY gender ENUM('M','F');

3、删除表

MariaDB [testdb]> DROP TABLE user3;

4、查看表

SHOW TABLES; 列出库中所有的表

DESC [db_name.]tb_name; 查看表结构

SHOW CREATE TABLE tbl_name; 查看创建表的命令

SHOW TABLE STATUS LIKE 'tbl_name'; 查看表状态

SHOW TABLE STATUS FROM db_name; 查看指定库中所有表状态

SHOW ENGINES; 查看所有存储引擎

四、DML: 数据操作语言

MariaDB [testdb]> DESC students;  #示例表

+--------+---------------------+------+-----+---------+----------------+

| Field  | Type                | Null | Key | Default | Extra          |

+--------+---------------------+------+-----+---------+----------------+

| id     | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |

| name   | varchar(30)         | NO   |     | NULL    |                |

| ages   | tinyint(2)          | NO   |     | NULL    |                |

| gender | enum('M','F')       | YES  |     | NULL    |                |

+--------+---------------------+------+-----+---------+----------------+

1、INSERT 插入数据

  • 单条记录插入

INSERT INTO tb_name (col1,col2,...) VALUES (val1,val2,...);

MariaDB [testdb]> INSERT students(id,name,ages,gender) VALUES (1,'tom',26,'M');

MariaDB [testdb]> INSERT students(name,ages,gender) VALUES ('jerry',19,'M');

MariaDB [testdb]> INSERT students(name,ages,gender) VALUES ('maria',19,'M');

MariaDB [testdb]> INSERT students SET name='ouyangfeng',ages=56,gender='M';
  • 多条记录插入

INSERT INTO tb_name (col1,col2,...) VALUES (val1,val2,...)[,(val1,val2,...),...];

MariaDB [testdb]> INSERT students(name,ages,gender) VALUES ('xiaolongnv',18,'F'),('dongfangbubai',28,'F');


MariaDB [testdb]> SELECT * FROM students;

+----+---------------+------+--------+

| id | name          | ages | gender |

+----+---------------+------+--------+

|  1 | tom           |   26 | M      |

|  2 | jerry         |   19 | M      |

|  3 | maria         |   19 | M      |

|  4 | xiaolongnv    |   18 | F      |

|  5 | dongfangbubai |   28 | F      |

|  6 | ouyangfeng    |   56 | M      |

+----+---------------+------+--------+
  • 从其他表查询数据保存到此表中
MariaDB [testdb]> ALTER TABLE students ADD address TEXT;  #加个字段做测试用


MariaDB [testdb]> INSERT students(name,address) SELECT user,host FROM mysql.user;


MariaDB [testdb]> SELECT * FROM students;

+----+---------------+------+--------+-----------+

| id | name          | ages | gender | address   |

+----+---------------+------+--------+-----------+

|  1 | tom           |   26 | M      | NULL      |

|  2 | jerry         |   19 | M      | NULL      |

|  3 | maria         |   19 | M      | NULL      |

|  4 | xiaolongnv    |   18 | F      | NULL      |

|  5 | dongfangbubai |   28 | F      | NULL      |

|  6 | ouyangfeng    |   56 | M      | NULL      |

|  7 | root          |    0 | NULL   | 127.0.0.1 |

|  8 | root          |    0 | NULL   | ::1       |

|  9 |               |    0 | NULL   | centos7   |

| 10 | root          |    0 | NULL   | centos7   |

| 11 |               |    0 | NULL   | localhost |

| 12 | root          |    0 | NULL   | localhost |

+----+---------------+------+--------+-----------+

2、UPDATE 修改数据

UPDATE tbl_name SET col1=value1,col2=value2,... WHERE col=value;

MariaDB [testdb]> UPDATE students SET gender='F' WHERE id=3;

3、DELETE 删除数据

MariaDB [testdb]> DELETE FROM students WHERE name='';  #删除名字为空的记录

MariaDB [testdb]> TRUNCATE TABLE user;  #情况表记录

五、SELECT：数据查询

  • AS：别名
  • WHERE：指明过滤条件以实现“选择”的功能
    • +, -, *, /, %：算术操作符
    • =, !=, <>, >, <, >=, <=：比较操作符
    • BETWEEN min_num AND max_num：在min_num和max_mun之间
    • IN (element1,element2,...)：在element...中的
    • IS NULL：为空
    • IS NOT NULL：不为空
    • LIKE：做匹配，像。。。
      • %：任意长度的任意字符
      • _：单个任意字符
    • RLIKE：正则表达式，不建议用
    • REGEXP：同上
    • NOT, AND, OR, XOR：逻辑操作符
  • GROUP BY：根据指定的条件把查询结果进行“分组”以用于做“聚合”运算
    • AVG() 平均数
    • MAX() 最大数
    • MIN() 最小数
    • COUNT() 统计
    • SUM() 求和
    • HAVING ：对分组聚合运算后的结果指定过滤条件。类似WHERE的作用，但只能在分组中使用
  • ORDER BY：排序
    • ASC：正序，默认
    • DESC：倒序
    • -KEYWORD：在排序时在关键字前加-可以避免把NULL排在前边
  • LIMIT [[offset,]row_count]：对查询的结果进行输出行数数量限制

1、选择

MariaDB [testdb]> SELECT * FROM students WHERE name='maria';  #查询maria的信息

MariaDB [testdb]> SELECT * FROM students WHERE id BETWEEN 2 AND 5;  #查询2到5号学生的信息

MariaDB [testdb]> SELECT * FROM students WHERE name IN ('jerry','xiaolongnv');  #查询jerry和xiaolongnv的信息

MariaDB [testdb]> SELECT * FROM students WHERE gender IS NOT NULL;  #查询年龄不为空的信息

MariaDB [testdb]> SELECT * FROM students WHERE name LIKE '%o%';  #查询姓名中包含'o'的信息

2、投影

MariaDB [testdb]> SELECT user AS 用户,host AS 主机,password AS 密码 FROM mysql.user;

3、分组

MariaDB [testdb]> SELECT gender,AVG(ages) FROM students GROUP BY gender;  #查询男生、女生年龄的平均值

MariaDB [testdb]> SELECT gender,AVG(ages) FROM students GROUP BY gender HAVING gender='M';  #只显示男生的平均年龄信息

4、排序

MariaDB [testdb]> SELECT * FROM students ORDER BY ages DESC;  #按年龄排序，倒序显示

MariaDB [testdb]> SELECT * FROM students WHERE ages > 0 ORDER BY ages LIMIT 3;  #按年龄排序，过滤年龄大于0的，正序排序，取前三条记录

六、多表查询

SQL语法：MySQL系列之四-LMLPHP

JOIN ON：交叉连接

INNER JOIN ON：内连接

LEFT OUTER JOIN ON：左外连接

RIGHT OUTER JOIN ON：右外连接

UNION ON：完全外连接

MariaDB [testdb]> SELECT * FROM students AS s,score AS o WHERE s.sid=o.id;  #俩张表取交集

1、交叉连接

MariaDB [testdb]> SELECT * FROM students JOIN score;

2、内连接

MariaDB [testdb]> SELECT t.name,s.score FROM students AS t INNER JOIN score AS s ON t.sid=s.id;

+---------------+-------+

| name          | score |

+---------------+-------+

| tom           |    99 |

| jerry         |    98 |

| maria         |    88 |

| xiaolongnv    |    68 |

| dongfangbubai |    78 |

| ouyangfeng    |    87 |

+---------------+-------+

3、外连接

MariaDB [testdb]> SELECT t.name,s.score FROM students AS t LEFT JOIN score AS s ON t.sid=s.id;  #左外连接

+---------------+-------+

| name          | score |

+---------------+-------+

| tom           |    99 |

| jerry         |    98 |

| maria         |    88 |

| xiaolongnv    |    68 |

| dongfangbubai |    78 |

| ouyangfeng    |    87 |

+---------------+-------+


MariaDB [testdb]> SELECT * FROM students AS t RIGHT JOIN score AS s ON t.sid=s.id;  #右外连接

6、完全外连接

MariaDB [testdb]> SELECT name,address FROM students

    -> UNION

    -> SELECT user,host FROM mysql.user;

+---------------+-----------+

| name          | address   |

+---------------+-----------+

| tom           | NULL      |

| jerry         | NULL      |

| maria         | NULL      |

| xiaolongnv    | NULL      |

| dongfangbubai | NULL      |

| ouyangfeng    | NULL      |

| root          | 127.0.0.1 |

| root          | ::1       |

|               | centos7   |

| root          | centos7   |

|               | localhost |

| root          | localhost |

+---------------+-----------+

5、自连接

MariaDB [testdb]> ALTER TABLE students ADD tid TINYINT(2);  #再加一个tid字段

MariaDB [testdb]> SELECT * FROM students;

+----+---------------+------+--------+---------+------+------+

| id | name          | ages | gender | address | sid  | tid  |

+----+---------------+------+--------+---------+------+------+

|  1 | tom           |   26 | M      | NULL    |    1 |    2 |

|  2 | jerry         |   19 | M      | NULL    |    2 |    1 |

|  3 | maria         |   19 | F      | NULL    |    3 |    4 |

|  4 | xiaolongnv    |   18 | F      | NULL    |    4 |    5 |

|  5 | dongfangbubai |   28 | F      | NULL    |    5 |    4 |

|  6 | ouyangfeng    |   56 | M      | NULL    |    6 |    4 |

+----+---------------+------+--------+---------+------+------+


MariaDB [testdb]> SELECT s1.name AS studentname,s2.name AS teachername FROM students AS s1 INNER JOIN students AS s2 ON s1.id=s2.tid;

+---------------+---------------+

| studentname   | teachername   |

+---------------+---------------+

| jerry         | tom           |

| tom           | jerry         |

| xiaolongnv    | maria         |

| dongfangbubai | xiaolongnv    |

| xiaolongnv    | dongfangbubai |

| xiaolongnv    | ouyangfeng    |

+---------------+---------------+

七、子查询

子查询：在查询语句嵌套着查询语句，性能较差，基于某语句的查询结果再次进行的查询

1、用在WHERE子句中的子查询

  • 用于比较表达式中的子查询；子查询仅能返回单个值
MariaDB [testdb]> SELECT name,ages FROM students WHERE ages > (SELECT AVG(ages) FROM students);    #查询大于平均年龄的同学
  • 用于IN中的子查询：子查询应该单键查询并返回一个或多个值从构成列表

2、用于FROM子句中的子查询

SELECT tb_alias.col1,... FROM (SELECT clause) AS tb_alias WHERE Clause;

八、数据类型

选择正确的数据类型对于获得高性能至关重要，三大原则：

  • 更小的通常更好，尽量使用可正确存储数据的最小数据类型
  • 简单就好，简单数据类型的操作通常需要更少的CPU周期
  • 尽量避免NULL，包含为NULL的列，对MySQL更难优化

1、数值型

  • 精确数值

    • INT

      • TINYINT 微整型 1

      • SMALLINT 小整型 2

      • MEDIUMINT 中整型 3

      • INT 整型 4

      • BIGINT 大整型 8

    • DECIMAL 精确定点型

  • 近似数值

    • FLOAT 单精度浮点型 4

    • DOUBLE 双精度浮点型 8

    • REAL

    • BIT

2、字符型

  • 定长

    - CHAR（不区分大小写）255

- BINARY（区分大小写）

  • 变长

    • VARCHAR（不区分大小写）65,535

    • VARBINNARY（区分大小写）

  • TEXT（不区分大小写）

    • TINYTEXT 255

    • TEXT 65,535

    • MEDIUMTEXT 16,777,215

    • LONGTEXT 4,294,967,295

  • BLOB（区分大小写）

    • TINYBLOB 微二进制大对象 255

    • BLOB 二进制大对象 64K

    • MEDIUMBLOB 中二进制大对象 16M

    • LONGBLOB 长二进制大对象 4G

  • ENUM 枚举 65535种变化

  • SET 集合 1-64个字符串，可以随意组合

3、日期时间型

  • DATE 3

  • TIME 3

  • DATETIME 8

  • TIMESTAMP 4

  • YEAR{2|4} 1

4、布尔型

  • BOOL，BOOLEAN：布尔型，是TINYINT(1)的同义词。zero值被视为假。非zero值视为真。

参考官方文档：https://dev.mysql.com/doc/refman/5.5/en/data-types.html

@@ 2018.06.07 13:37

05-12 05:28
Powered by LMLPHP ©2024 ql语 0.050340