SQL基本语法

声明:本文所有内容基于MySQL测试环境

语句分类

DDL:数据定义语言(Data Definition Language)

基本关键字

下表列出了DDL的一些关键字,可以对库或表执行一些特定的操作。

关键字操作对象作用备注
CREATEDATABASE/TABLE增加库或表SCHEMA也表示库
DROPDATABASE/TABLE删除库或表 使用时需特别注意
ALTERDATABASE/TABLE修改库或表
CREATE
建库
  1. 创建一个名为halodb的库:

    CREATE DATABASE halodb;
    #或
    CREATE SCHEMA halodb;
    
  2. 加入判断语句,这是防止出错的好办法:

    #如果未存在则创建
    CREATE DATABASE IF NOT EXISTS halodb;
    
  3. 设置默认字符集和校验规则:

    #设定字符集位utf8,校验规则为utf8_general_ci
    CREATE DATABASE IF NOT EXISTS halodb CHARSET utf8 COLLATE utf8_general_ci;
    
建表
  1. 创建一个名为contacts的表,列名为:id;(PS: 创建表必须包含一个字段,也就是至少包含1列),如下:

    #需要USE一个库,或指定已存在库
    CREATE TABLE contacts(id int);
    
  2. 加入多个字段,并指定主键(加快查询速度),加入唯一键(防止出现重复数据)

    #加入判读,若已存在表则不创建
    #主键自增
    #Name唯一和非空,不允许重复的联系人
    CREATE TABLE IF NOT EXISTS contacts(
    ID INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '联系人ID,主键',
    Name VARCHAR(40) UNIQUE KEY NOT NULL COMMENT '联系人姓名',
    Phone VARCHAR(11) NOT NULL COMMENT '联系人手机号码',
    Email VARCHAR(255) DEFAULT NULL COMMENT '联系人email',
    Address VARCHAR(255) DEFAULT NULL COMMENT '联系地址');
    

    查看表结构:

    mysql> DESC contacts;
    +---------+------------------+------+-----+---------+----------------+
    | Field   | Type             | Null | Key | Default | Extra          |
    +---------+------------------+------+-----+---------+----------------+
    | ID      | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
    | Name    | varchar(40)      | NO   | UNI | NULL    |                |
    | Phone   | varchar(11)      | NO   |     | NULL    |                |
    | Email   | varchar(255)     | YES  |     | NULL    |                |
    | Address | varchar(255)     | YES  |     | NULL    |                |
    +---------+------------------+------+-----+---------+----------------+
    
DROP
删库

删除名为test的数据库

DROP DATABASE test;
删表

删除名为test库中的tbl1

DROP TABLE test.tbl1;
ALTER
修改库

修改库test的默认字符集为utf8

ALTER DATABASE test CAHRSET utf8

修改库test的校验规则为utf8_general_ci

ALTER DATABASE test COLLATE utf8_general_ci;
修改表
  1. test.tbl1中添加字段address,默认添加到最后一列

    #插入address字段,默认可以为空
    ALTER TABLE test.tbl1 ADD address VARCHAR(255);
    
  2. test.tb1表头添加字段id

    #主键,自增
    ALTER TABLE test.tbl1 ADD id int PRIMARY KEY AUTO_INCREMENT FIRST;
    
  3. test.tb1表的id字段之后插入字段name

    #name唯一键,非空
    #在id之后插入name键
    ALTER TABLE test.tbl1 ADD name VARCHAR(40) UNIQUE KEY NOT NULL AFTER id;
    
  4. 添加多个字段

    #phone非空
    #在name之后插入phone
    #email可以为空
    #在phone之后插入
    #注:AFTER如果不存在则会报错,所以需注意顺序
    ALTER TABLE test.tbl1 ADD phone VARCHAR(11) NOT NULL AFTER name,
    ADD email VARCHAR(255) AFTER phone;
    
  5. 删除某个字段

    #删除email字段
    ALTER TABLE test.tbl1 DROP email;
    
  6. 修改字段的数据类型

    #修改id的int类型为bigint
    ALTER TABLE test.tbl1 MODIFY id BIGINT;
    
  7. 修改字段名称,也可使用CHANGE来修改字段的数据类型和字段约束信息

    #修改phone字段为phone_num
    #可选的约束参数:非空,默认,等
    #必须跟随重命名字段的数据类型
    ALTER TABLE test.tbl1 CHANGE phone phone_num VARCHAR(11) NO NULL;
    #修改类型为BIGINT
    ALTER TABLE test.tbl1 CHANGE phone phone_num BIGINT(11) NO NULL;
    
  8. 修改表的名称

    #修改表tbl1为contacts
    ALTER TABLE test.tbl1 RENAME test.contacts;
    

DCL:数据控制语言(Data Control Language)

主要用于数据库的权限管理相关操作

基本语法
GRANT

授予权限

  1. 授予root用户所有库的所有表的所有权限且只能通过本地主机域连接数据库,添加密码123

    #GRANT执行的授权操作默认不带GRANT_PRIV权限,需要添加附加参数WITH GRANT OPTION
    GRANT ALL ON *.* TO 'root'@'localhost' IDENTIFIED BY '123' WITH GRANT OPTION;
    
  2. 授予halo用户的halodb库的所有表的所有权限,仅允许特定IP段访问该数据库,添加密码123

    GRANT ALL ON halodb.* TO 'halo'@'172.16.1.%' IDENTIFIED BY '123';
    
  3. 单表级别授权

    GRANT INSERT,DELETE,UPDATE,SELECT ON test.contacts TO 'kevin'@'localhost' IDENTIFIED BY '123';
    
  4. 单列级别授权,脱敏

    #仅允许查询contacts表的name字段
    #实验环境建议不要使用系统自带的test库,否则单列授权无效
    GRANT SELECT(name) ON test.contacts TO 'kevin'@'localhost' IDENTIFIED BY '123';
    
REVOKE

回收权限

回收kevin@localhost用户的DELETE权限,该用户重新登陆时生效。

REVOKE DELETE ON *.* FROM 'keivn'@'localhost';

DML:数据操作语言(Data Manipulation Language)

主要针对执行操作。

INSERT

向表中插入数据

插入单条数据到表内

#以插入contacts表为例
INSERT INTO contacts(Name,Phone,Email,Address) VALUES('张三',13800138000,'zhangsan@hotmail.com','上海市浦东新区环桥路1481弄34号');

插入多条数据到表内

INSERT INTO contacts
(Name,Phone,Email,Address)
VALUES
('李四',14700138000,'lisi@hotmail.com','上海市浦东新区环桥路1481弄35号'),
('王舞',15900000000,'wangwu@hotmail.com','上海市浦东新区环桥路1481弄35号');
DELETE

删除数据

危险操作:删除整表数据

#清空test.tbl1表,自增值不清零
DELETE FROM test.tbl1;

建议操作:删除数据时务必添加条件语句

#删除单条数据
DELETE FROM test.tbl1 WHERE name='张三';
#删除多条数据
DELETE FROM test.tbl1 WHERE name='张三' AND name='李四';
#若清空整表则加入永远为TRUE的表达式
DELETE FROM tset.tbl1 WHERE 1=1;
#PS: 为了防止误删带来的灾难,请务必加入,即便是清空整表,时刻保持严谨!

注: 删除参见修改表[5]

UPDATE

更新数据

基本操作

危险操作:整列数据修改

#将test.tbl1表的status字段值整列全部改为1
#不建议的操作
UPDATE test.tbl1 SET status=1;

建议操作:修改数据时加入务必添加条件语句

#将test.tbl1表name为'张三'的行的status字段值改为0
UPDATE test.tbl1 SET status=0 WHERE name='张三';
#更新多条
UPDATE test.tbl1 SET status=0 WHERE name='张三' AND name='李四';
#更新整列建议加入条件,整列修改时
UPDATE test.tbl1 SET status=0 WHERE 1=1;
#PS: 永远别太相信自己,任何的‘骚操作’结果都是灾难。
扩展:使用UPDATE实现伪删除

在表内定状态字段,每次查表时通过条件语句判断该字段状态来确定数据删除与否(实际上并未删除该数据)

如下的一个简单表,添加一列status字段来专门标记数据状态,用0表示删除,1表示未删除

nameagestatus
张三340
李四551
王舞181

查询时只需要加入WHERE条件语句进行判断status字段来确定数据是否为有效数据。

DQL:数据库查询语言(Data Query Language)

主要为日常的查询数据所用。

SELECT

查询数据

基础查询

危险操作:查询表内所有数据

#使用SELECT *查询表内所有数据
#PS: 在表数据量不大的情况下没什么问题,但如果表内数据量庞大,内存放不下单张表的数据,就会瞬间造成OOM
SELECT * FROM test.tbl1;

注: OOM (Out Of Memory: 内存溢出),若在linux操作系统上发生了内存溢出,则内核会随机kill掉一些进程。

建议操作:统计数据总量(行)

#如果需要统计表内数据量,避免使用SELECT *
#可调用COUNT()函数进行统计
SELECT COUNT(*) FROM test.tbl1;

建议操作:用什么数据就查什么字段,并加入条件语句

#查询test.tbl1表中的name和phone_num字段
SELECT name,phone_num FROM test.tbl1;
#加入条件或多条件
SELECT name,phone_num FROM test.tbl1 WHERE name='张三';
  1. 对数据进行排序

    #升序
    SELECT * FROM [表名] ORDER BY [要排序的字段];
    #降序
    SELECT * FROM [表名] ORDER BY [要排序的字段] DESC;
    
  2. 对数据进行排序,并取TOP10

    #升序,取最小前10
    SELECT * FROM [表名] ORDER BY [要排序的字段] LIMIT 10;
    #降序,取最大前10
    SELECT * FROM [表名] ORDER BY [要排序的字段] DESC LIMIT 10;
    
  3. 分页查看数据,单步或设置步长

    #查询表内所有内容只取前100条记录,单步
    SELECT * FROM [表名] LIMIT 100;
    #查表内第101-200范围内的100条记录,设定步长,LIMIT第一个参数步长,第二参数为步
    #关于LIMIT的参数也可以叫做起始位置和偏移量
    SELECT * FROM [表名] LIMIT 100,100;
    
  4. 模糊查询

    #查询contacts表内名字以'舞'结尾的联系人信息
    SELECT * FROM test.contacts WHERE name LIKE '%舞%';
    #%不是正则,仅代表数据中包含某个字或单词
    
  5. 多条件查询,使用逻辑

    #查询contacts表内地址为"上海市浦东新区环桥路1481弄35号"且名为”李四"的联系人信息
    SELECT * FROM test.contacts
    WHERE address='上海市浦东新区环桥路1481弄35号'
    AND name='李四';
    
  6. 多条件查询,使用逻辑

    #查询contacts表内地址为"上海市浦东新区环桥路1481弄35号"和地址为34号的联系人信息
    SELECT * FROM test.contacts
    WHERE address='上海市浦东新区环桥路1481弄35号'
    OR address='上海市浦东新区环桥路1481弄34号';
    
  7. 条件查询,不等于

    ##查询contacts表内地址不为"上海市浦东新区环桥路1481弄35号"的联系人信息
    SELECT * FROM test.contacts WHERE address != '上海市浦东新区环桥路1481弄35号';
    #或
    SELECT * FROM test.contacts WHERE address <> '上海市浦东新区环桥路1481弄35号';
    #PS: 关于条件关系符,下面是可用的符号和关键字
    #################################
    #	描述			关键字
    #--------------------------------
    #	大于 				>
    #	小于 				<
    #	不等于 		!= 或 <>
    #	大于等于		>=
    #	小于等于		<=
    #	模糊条件		LIKE
    #	精确查询		  =
    
联合查询
#查询表内不同条件的内容
#库为world库,世界人口数量
#如果只查询单表的话可用多条件实现
SELECT Name FROM city WHERE CountryCode='CHN' UNION ALL SELECT Name FROM city WHERE CountryCode='USA';
多表连查

现有下面结构的多张数据表

表1: student

字段类型长度是否可以为NULL默认其他约束注释
idVARCHAR(20)NOTPRIMARY学号
nameVARCHAR(40)NOT姓名
genderENUM('M','F')NOT'M'性别
birthdayDATENOT 生日

表2:score

字段类型长度是否可以为NULL默认其他约束注释
idVARCHAR(20)NOTPRIMARY学号
chineseFLOAT(4,1)默认0语文成绩
englishFLOAT(4,1)默认0英语成绩
mathFLOAT(4,1)默认0数学成绩
  1. 传统方式:找多张表之间的交集然后加入条件判断语句进行多表连查,通过预先约定好的规则设计的多张表,表之间具有一定的关系(交集),以此进行多表数据关联,如图为传统模式的典型方法

    传统模式多表连查

    • 查询小明的语文成绩
    SELECT name,chinese
    FROM student,score
    WHERE student.id=score.id;
    
    • 查询'高三(1)'班的所有学生各科成绩
    SELECT name,chinese,english,math
    FROM student,score
    WHERE student.id=score.id
    AND student.class='高三(1)';
    
    • 找出学渣,查看学渣的各科成绩和学渣的基本信息,60分为及格线
    SELECT name,gender,birthday,class,chinese,english,math
    FROM student,score
    WHERE student.id=score.id
    AND score.chinese < 60
    AND score.english < 60
    AND score.math < 60;
    

    注: 如果以传统方式进行查询,可套用一下基本思路:

    • 首先需明了各表的基本结构
    • 确定最终需要查询的数据
    • 确定查询数据的条件的逻辑关系,找出关联性数据,也就是共同数据
    • 根据表结构和要查询的数据字段,再并入查询数据的条件或逻辑关系表达式
  2. 高级用法: 自连接

    关键字:natural join 主键:聚集索引 描述:自己去查找表之间的关联字段 使用该方法之前,需要确保以下必要条件: 多个表之间需要有相同的键值对/交集(字段名,数据)

    NATUAL_JOIN
    • 找出各科都及格的学生,显示班级和各科分数
    #自连接,NATURAL JOIN
    #必要条件:表之间必须具有相同的键值
    SELECT student.`name`,student.class,score.* FROM student
    NATURAL JOIN score
    WHERE chinese > 60
    AND english > 60
    AND math > 60;
    
    • 输出:
    +--------+-----------+----+---------+---------+-------+
    | name   | class     | id | chinese | english | math  |
    +--------+-----------+----+---------+---------+-------+
    |  小明  |  高三(1)  | 1  |   77.0  |    80.0 | 100.0 |
    +--------+-----------+----+---------+---------+-------+
    
  3. 高级用法:内连接

    语法:table_1 JOIN table_2 ON 关系表达式
    知识点:通过简表查询复杂表可提高查询速度,查询是一种遍历索引的动作,表越大越复杂,索引越多,查表效率越低!表越精简,索引越简单,查询越快!简表速度快,通过查询的简表数据直接查询复杂表,加块查询速度(小表,大表)

    • 找出各科都及格的学生,显示班级和各科分数
    #内连接,JOIN ON
    SELECT student.`name`,student.class,score.* FROM student
    JOIN score ON student.id=score.id
    WHERE chinese > 60
    AND english > 60
    AND math > 60;
    
    • 找出学渣,查看学渣的各科成绩和学渣的基本信息,60分为及格线
    #内连接,JOIN ON
    SELECT student.`name`,student.class,score.* FROM student
    JOIN score ON student.id=score.id
    WHERE chinese < 60
    AND english < 60
    AND math < 60;
    
  4. 其他: 多表内连接简洁写法

    #只需要将要加入的表用圆括号扩起,条件也可以括起来
    SELECT [字段名1],[字段名2],[字段名n]
    FROM [表1]
    JOIN ([表2],[表3],[表4])
    ON ([表达式1] AND [表达式2])
    WHERE [条件...];
    

部分参考来源:DBA老司机带你删库到跑路.

# SQL  JOIN 

评论

Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×