SQL:基本语句

0、基本

①注释

--单行注释
# 单行注释
/* *多行注释 */


 

1、数据库相关

复制代码
--创建
CREATE DATABASE xxx;

--删除
DROP DATABASE xxx IF EXISTS xxx;

--转换到数据库xxx,此后的SQL操作都是对该数据库进行了
USE xxx;

--显示所有数据库
SHOW Databases;

--退出
EXIT
复制代码

 

1.1、删除数据库

DROP DATABASE IF EXISTS xxx

说明

删除名为xxx的数据库(如果存在的话)。

例子

DROP DATABASE IF EXISTS learnjdbc;

1.2、创建数据库

CREATE DATABASE xxx

说明

创建名为xxx的数据库

1.3、创建用户、密码

CREATE USER IF NOT EXISTS user@'host' IDENTIFIED BY 'password'

例子

-- 创建登录用户learn/口令learnpassword
CREATE USER IF NOT EXISTS learn@'%' IDENTIFIED BY 'learnpassword';

说明

  • user@'host'用户名user主机名host;如果写法为user@'%',表明主机为本机

  • 'password'密码password

1.4、权限

GRANT ALL PRIVILEGES ON dbname.tablename To user@'host' WITH GRANT OPTION;

FLUSH PRIVILEGES;

说明

在数据库dbname表tablename上,为用户user@'host'赋予全部权力;

如果要为所有表赋予相同权力,可以写为dbname.*

例子

GRANT ALL PRIVILEGES ON learnjdbc.* TO learn@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

2、表相关

复制代码
--列出当前数据库的所有表
SHOW tables;

SHOW tablename;

--查看某个表结构
DESC tablename;

--查看创建表的SQL语句
SHOW CREATE TABLE tablename;

--创建表
CREATE TABLE tablename (
  列1 类型(length) 限制条件,
  列2 类型(length) 限制条件,
        ...
  列n 类型(length) 限制条件,
      PRIMARY KEY(主键列名)
)ENGINE=引擎 AUTO_INCREMENT=自增值 DEFAULT CHARSET=utf8

CREATE TABLE `students` (                             
             `id` bigint(20) NOT NULL AUTO_INCREMENT,            
             `class_id` bigint(20) NOT NULL,                     
             `name` varchar(100) NOT NULL,                       
             `gender` varchar(1) NOT NULL,                       
             `score` int(11) NOT NULL,                           
             PRIMARY KEY (`id`)                                  
           ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

--删除表
DROP TABLE tablename;

--修改表
--a、加一列
ALTER TABLE tablename ADD COLUMN 列名 类型(length) 限制条件;
--b、修改列名
ALTER TABLE tablename CHANGE COLUMN 旧列名 新列名 类型(length) 限制条件;
--c、删除列
ALTER TABLE tablename DROP COLUMN 列名;
复制代码

 

 

2.1、转到某个数据库

USE dbname;

说明

转到某个数据库上,之后所有操作都是对该数据库进行的。

需要注意的是数据库名都是小写。

例子

USE learnjdbc;

2.2、创建表

1)正常创建

CREATE TABLE tablename (

  列名1 类型 限制,

  列名2 类型 限制,

  ...

  列名n 类型 限制

) Engine=xxx DEFAULT CHARSET=UTF-8

说明

创建表tablename,同时设置各列

例子

CREATE TABLE students (
  id BIGINT AUTO_INCREMENT NOT NULL,
  name VARCHAR(50) NOT NULL,
  gender TINYINT(1) NOT NULL,
  grade INT NOT NULL,
  score INT NOT NULL,
  PRIMARY KEY(id)
) Engine=INNODB DEFAULT CHARSET=UTF8;

 

2)用另一个表进行复制(快照)

CREATE TABLE new_tablename
SELECT * FROM old_tablename WHERE 条件表达式;

 

 

2.3、插入 INSERT

2.3.1、基本用法

1)用法

--a、插入一行
INSERT INTO tablename (列名1,列名2,...,列名n) VALUES (值1,值2,...,值n)

--b、插入多行
INSERT INTO tablename (列名1,列名2,...,列名n) VALUES 
(值a1,值a2,...,值an),
(值b1,值b2,...,值bn);

 

2)说明

可以不指定id字段,如果id是自增主键时,数据库会自动推算出它的值;

如果某个字段有默认值,那它在INSERT语句中也可以不出现。

3)例子

INSERT INTO students (name, gender, grade, score) VALUES ('小明', 1, 1, 88);
INSERT INTO students (name, gender, grade, score) VALUES ('小红', 1, 1, 95);
INSERT INTO students (name, gender, grade, score) VALUES ('小军', 0, 1, 93);
INSERT INTO students (name, gender, grade, score) VALUES ('小白', 0, 1, 100);

 

2.3.2、插入冲突

1)插入或替换

插入新记录,如果记录存在(一般根据主键是否重复来判断)则先删除再插入

REPLACE INTO tablename (列1,列2,...,列n) VALUES (值1,值2,...,值n);

2)插入或更新

与1)的区别在于,如果记录存在则更新

INSERT INTO tablename (列1,列2,...,列n) 
VALUES (值1,值2,...,值n) 
ON DUPLICATE KEY 
UPDATE 列1=值1,列2=值2,...,列m=值m;

需要更新的列,由UPDATE之后的语句加以说明,不必每列都写明,修改部分列是允许的

 

3)插入或忽略

与1)和2)的区别在于,如果记录存在则放弃插入

INSERT IGNORE INTO tablename (列1,列2,...,列n) VALUES (值1,值2,...,值n);

 

2.4、更新 UPDATE

1)用法

复制代码
--a、基本用法
UPDATE tablename
SET
    列1=值1,
    列2=值2,
    ...
    列n=值n
WHERE 条件表达式;

--b、赋值时使用表达式,见例子
UPDATE tablename
SET
    列1=表达式1,
    列2=表达式2
, ... 
  列n=表达式n 
WHERE 条件表达式;
复制代码

 

2)说明

  • WHERE条件表达式与SELECT中的条件表达式相同,因此可以一次更新多条记录;
  • 如果WHERE表达式没有匹配到任何记录,那么不会修改任何记录;
  • 如果没有WHERE,则会修改所有记录;
  • 最好在UPDATE前,先用SELECT+WHERE看看最终会修改哪些记录;
  • UPDATE语句执行后,如果是MySQL数据库,会返回更新的行数与WHERE匹配到的行数:
    mysql> UPDATE students SET name='大宝' WHERE id=1;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0

     

3)例子

复制代码
--a、修改多行
UPDATE students 
SET 
    name='小牛', score=77 
WHERE id>=5 AND id<=7;

--b、使用表达式赋值
--下文会把所有score都加10
UPDATE students 
SET 
    score=score+10 
WHERE score<80;
复制代码

 

2.5、删除 DELETE

1)用法

DELETE FROM tablename
WHERE 条件表达式;

2)说明

  • 可以根据WHERE条件表达式一次性删除多行;
  • 如果没有WHERE会删除全部记录;
  • 使用MySQL时,会返回删除的行数和WHERE匹配到的行数
  • DELETE语句不能给表起别名,不然会报错You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't...

3)例子

--删除一行
DELETE FROM students WHERE id=1;

--删除多行
DELETE FROM students WHERE id>=5 AND id<=7;

--删除全部
DELETE FROM students;

 

2.6、查询 SELECT

复制代码
--①查询全部
SELECT * FROM tablename 表别名;
--②条件查询 WHERE SELECT * FROM tablename WHERE 条件表达式;
--③投影查询 查询部分列 SELECT 列1,列2,列3 FROM tablename WHERE 条件表达式; --起别名: SELECT 列1 名1,列2 名2,列3 名3 FROM tablename WHERE 条件表达式; --④排序 --a、正序 SELECT 列1,列2 FROM tablename ORDER BY 列1; --b、逆序 DESC SELECT 列1,列2 FROM tablename ORDER BY 列1 DESC; --c、正逆序混合 先排前边的列,相同时再排后边的列 SELECT 列1,列2 FROM tablename ORDER BY 列1 DESC,列2; --d、附加条件判断 SELECT 列1,列2 FROM tablename WHERE 条件表达式 ORDER BY 列1 DESC; --⑤分页 SELECT 列1,列2,列3 FROM tablename ORDER BY 列1 DESC LIMIT N OFFSET M; --⑥聚合查询 --a、统计一列信息 SELECT 聚合函数(列名) 别名 FROM tablename; SELECT 聚合函数(列名) 别名 FROM tablename WHERE 条件表达式; --b、分组聚合,先按某列进行分组,再分别聚合每组的信息 SELECT 列名1,聚合函数(列名2) 别名 FROM tablename GROUP BY 列名1; SELECT 列名1,列名2,聚合函数(列名2) 别名 FROM tablename GROUP BY 列名1,列名2;
--⑦多表查询 同时从多表查询 --a 多表查询 SELECT * FROM table1,table2;
--b 多表投影查询 SELECT 表1.列1 别名1 , 表1.列2 别名2 , 表2.列1 别名3 , 表2.列2 别名4 FROM 表1,表2 WHERE 条件表达式; --c 多表投影查询,给表起别名 SELECT 表别名1.列1 别名1 , 表别名1.列2 别名2 , 表别名2.列1 别名3 , 表别名2.列2 别名4 FROM 表1 表别名1 , 表2 表别名2; WHERE 条件表达式; --⑧连接查询 先选定主表,再把另一个表的额外列附加到主表中作为结果集 --a、内连接 --FROM 主表 --INNER JOIN 需要连接的表 SELECT 表1.列1,表1.列2,表1.列3, 表2.列1,表2.列2 FROM 表1 别名1 INNER JOIN 表2 别名2 ON 表1.列1 = 表2.列1 WHERE 条件表达式 ORDER BY 某列; --b、外连接 --FROM 主表 -- OUTER JOIN 需要连接的表 SELECT 表1.列1,表1.列2,表1.列3, 表2.列1,表2.列2 FROM 表1 别名1 FULL/RIGHT/LEFT OUTER JOIN 表2 别名2 ON 表1.列1 = 表2.列1 WHERE 条件表达式 ORDER BY 某列;
复制代码

 

 

①查询全部

1)用法

SELECT * FROM tablename

②条件查询 WHERE

1)用法

SELECT * FROM tablename WHERE 条件表达式

条件表达式中可以用连接符AND、OR、NOT + 括号()来实现多重条件查询:

SELECT * FROM tablename WHERE 条件1 AND 条件2;
SELECT * FROM tablename WHERE 条件1 OR 条件2;
SELECT * FROM tablename WHERE NOT 条件1;

2)常用条件表达式:

>、<、=、>=、<=、<>(不等于)、LIKE

条件表达式中,字符串要用单引号,字符串比较是通过ASCII码

LIKE常用于字符串匹配

3)例子

SELECT * FROM students WHERE score=80 AND name>'abc'

--LIKE用于字符串匹配
--%表示任意字符,例如'ab%'将匹配'ab','abc','abcd'
SELECT * FROM students WHERE name LIKE 'ab%'

③投影查询(查询部分列) 

1)用法

SELECT 列1,列2,列3 FROM tablename WHERE 条件表达式

起别名:SELECT 列1 名1,列2 名2,列3 名3 FROM tablename WHERE 条件表达式

2)例子

查询id,score,name,并将score列重命名为points:

SELECT id,score points,name FROM students;

④排序 (ORDER BY)

1)用法

a、正序(ASC,ASC可省略):

SELECT 列1,列2 FROM tablename ORDER BY 列1;

SELECT * FROM tablename ORDER BY 列1;

b、逆序(DESC):

SELECT 列1,列2 FROM tablename ORDER BY 列1 DESC

SELECT * FROM tablename ORDER BY 列1 DESC;

c、多列逆序正序混合:

先对列1进行排序,如果列1值相同,再比较列2

SELECT 列1,列2 FROM tablename ORDER BY 列1 DESC,列2; 

SELECT * FROM tablename ORDER BY 列1,列2;

d、排序 + 条件判断:

如果有WHERE子句,那么ORDER BY要放在WHERE子句之后

SELECT 列1,列2
FROM tablename
WHERE 条件表达式
ORDER BY 列1 DESC;

2)例子

复制代码
--a 正序
SELECT id, name, gender, score FROM students ORDER BY score;
--b 逆序
SELECT id, name, gender, score FROM students ORDER BY score DESC;
--c 多列正序逆序混合排序
SELECT id, name, gender, score FROM students ORDER BY score DESC, gender;
--d 排序+条件判断
SELECT id, name, gender, score
FROM students
WHERE class_id = 1
ORDER BY score DESC;
复制代码

⑤分页 LIMIT<N-M> OFFSET<M>

用于查询记录很多时,分页显示

1)用法

SELECT 列1,列2,列3 
FROM tablename 
ORDER BY 列1 DESC
LIMIT N OFFSET M;

每页最多显示N条,从第M号记录开始查询

2)说明

其中OFFSET可以省略,省略时默认从1开始查询;

在MySQL中,LIMIT N OFFSET M可以写为LIMIT M,N;

随着M、N的变大,查询效率会越来越低

3)例子

SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 0;

每页3个记录,从0号记录开始

⑥聚合查询

查询表的数据信息,而非数据本身,比如表中有多少条数据、数据平均值、求和

1)用法

a、统计一列的信息

SELECT 聚合函数(列名) 别名 FROM tablename;

SELECT 聚合函数(列名) 别名 FROM tablename WHERE 条件表达式;

b、分组聚合 先按照某列进行分组,再分别统计每组的信息

SELECT 列名1,聚合函数(列名2) 别名 FROM tablename GROUP BY 列名1;

SELECT 列名1,列名2,聚合函数(列名2) 别名 FROM tablename GROUP BY 列名1,列名2;

这段SQL语句,会先按照GROUP BY的列进行分组,再对每组进行聚合查询。

注意,分组聚合时,只能再额外查询一个列名1,即用于分组的列,不能再查询别的列,比如下边这个写法就是错误的

SELECT 列名3,聚合函数(列名2) 别名 FROM tablename GROUP BY 列名1

2)聚合函数

  • COUNT:统计数据个数;

  • SUM:某列之和;

  • AVG:某列均值;

  • MAX、MIN:最大值、最小值

如果在用WHERE时没匹配到任何列,COUNT会返回0,其他的会返回NULL

3)例子

复制代码
--a、查询student表一共有多少人
SELECT COUNT(*) num FROM students;

查询男生平均成绩
SELECT AVG(score) average FROM students WHERE gender = 'M';

--b、分组聚合
--按班级查询每班各有多少人
SELECT class_id, COUNT(*) num FROM students GROUP BY class_id;
--查询每班男女各有多少人
SELECT class_id, gender, COUNT(*) num FROM students GROUP BY class_id, gender;
复制代码

⑦多表查询 同时从多个表查询数据

1)用法

复制代码
--a 多表查询
SELECT * FROM table1,table2;

--b 多表投影查询
SELECT 
    表1.列1 别名1 , 
    表1.列2 别名2 , 
    表2.列1 别名3 , 
    表2.列2 别名4 
FROM 表1,表2
WHERE 条件表达式;

--c 多表投影查询,给表起别名
SELECT 
    表别名1.列1 别名1 , 
    表别名1.列2 别名2 , 
    表别名2.列1 别名3 , 
    表别名2.列2 别名4 
FROM 表1 表别名1 , 表2 表别名2;
WHERE 条件表达式;
复制代码

2)说明

多表查询时,查询结果的列数是两表列数之和,行数是两表行数之积(笛卡尔查询)。

多表查询结果集很大,通过WHERE进行投影之后可以减少数量。

3)例子

复制代码
--a
SELECT * FROM students, classes;

--b 投影查询,给表起别名,查询结果也起别名
SELECT
    s.id sid,
    s.name,
    s.gender,
    s.score,
    c.id cid,
    c.name cname
FROM students s, classes c;
复制代码

 

⑧连接查询

本质上也是多表查询,不过会先确定一个主表作为结果集,之后把其他表的行有选择性地连接到主表结果集上。

1)用法

复制代码
--a、内连接
--FROM 主表
--INNER JOIN 需要连接的表
SELECT 
    表1.列1,表1.列2,表1.列3,
    表2.列1,表2.列2
FROM 表1 别名1
INNER JOIN 表2 别名2
ON 表1.列1 = 表2.列1
WHERE 条件表达式
ORDER BY 某列;

--b、外连接
--FROM 主表
-- OUTER JOIN 需要连接的表
SELECT 
    表1.列1,表1.列2,表1.列3,
    表2.列1,表2.列2
FROM 表1 别名1
FULL/RIGHT/LEFT OUTER JOIN 表2 别名2
ON 表1.列1 = 表2.列1
WHERE 条件表达式
ORDER BY 某列;
复制代码

2)说明

内连接:只返回同时存在于两表的行数据;

左外连接:返回右表中存在的行,如果某一行只在右表存在,那么剩下的字段会填充NULL;

右外连接:返回左表中存在的行,如果某一行只在左表存在,那么剩下的字段会填充NULL;

全外连接:返回两表中的所有记录,并且自动填充对方不存在的列为NULL。

内连接

 

 

左外、右外、全外

 

posted @   ShineLe  阅读(144)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· 百万级群聊的设计实践
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
· 永远不要相信用户的输入:从 SQL 注入攻防看输入验证的重要性
历史上的今天:
2019-07-20 DeepFaceLab:NVML Shared Library Not Found 解决办法
点击右上角即可分享
微信分享提示