MySQL_2_SQL语句基础

SQL语句

1、何为SQL？

SQL：Structured Query Language，即结构化查询语言。

其实就是定义了操作所有关系型数据库的规则。每一种数据库操作的方式存在不一样的地方，称为“方言”。

注：如果还没有安装MySQL数据库，具体可以参考文章《MySQL(一) | 数据库的详细安装和卸载过程（附带软件）》，里面很详细的介绍了MySQL的安装以及简单使用。

---

2、SQL的通用语法

1. SQL 语句可以单行或多行书写，以分号结尾。
2. 可使用空格和缩进来增强语句的可读性。
3. MySQL 数据库的 SQL 语句不区分大小写，关键字建议使用大写。
4. 3 种注释的方式
   • 单行注释: – 注释内容 或 # 注释内容(MySQL 特有)
   • 多行注释: /* 注释 */

---

3、SQL分类

1. DDL(Data DefINition Language)：数据定义语言

   用来定义数据库对象：数据库，表，列等。

   关键字：CREATE, DROP,ALTER 等

2. DML(Data Manipulation Language)：数据操作语言

   用来对数据库中表的数据进行增删改。

   关键字：INSERT, DELETE, UPDATE 等

3. DQL(Data Query Language)：数据查询语言

   用来查询数据库中表的记录(数据)。

   关键字：SELECT, WHERE 等

4. DCL(Data Control Language)：数据控制语言

   用来定义数据库的访问权限和安全级别，及创建用户。

   关键字：GRANT， REVOKE 等

---

4、DDL–数据定义语言

4.1、操作数据库(CRUD)

1. C(CREATE)：创建

1. 创建数据库
CREATE DATABASE `数据库名称`;

2. 创建数据库，判断是否存在在创建
CREATE DATABASE IF NOT EXISTS `数据库名称`;

3. 创建数据库，并指定字符集
CREATE DATABASE `数据库名称` CHARACTER SET `数据库名称`


#例子：创建db4数据库，判断是否存在，并制定字符集为gbk
CREATE DATABASE IF NOT EXISTS `db4` CHARACTER SET `GBK`;

2. R(RETRIEVE)：查询

1. 查询所有数据库的名称
show DATABASES;

3. U(UPDATE)：修改

1. 修改数据库的字符集
ALTER DATABASE `数据库名称` CHARACTER SET `字符集名称`;

4. D(DELETE)：删除

1. 删除数据库
DROP DATABASE `数据库名称`;

2. 判断数据库存在，存在再删除
DROP DATABASE IF EXISTS `数据库名称`;

5. 使用数据库

1. 查询当前正在使用的数据库名称
SELECT DATABASE();

2. 使用数据库
USE `数据库名称`;

---

4.2、操作表(CRUD)

1. C(CREATE)：创建

1. 创建表
CREATE TABLE `表名`(
    列名1  数据类型1,
    列明2  数据类型2,
    ……
    列名n  数据类型n
);
#注意：最后一列，不需要加逗号","

2. 复制表
CREATE TABLE `表名` LIKE `被复制的表名`;

#数据类型：
1. INT：整数类型
    例如，age INt

2. DOUBLE：小数类型
    例如，score double(5, 2)

3. DATE：日期(只包含年、月、日)
    格式：yyyy-MM-dd

4. DATETIME：日期(包含年、月、日、时、分、秒)
    格式：yyyy-MM-dd HH:mm:ss

5. TIMESTAMP：时间戳类型(包含年、月、日、时、分、秒)
    格式：yyyy-MM-dd HH:mm:ss
    如果不给这个字段赋值或者将其赋值为NULL，则默认使用当前的系统时间进行赋值

6. VARCHAR：字符串
    name VARCHAR(20) ：最大20个字符
    例如，``zhangsan``为8个字符，``张三``为2个字符

补充：在MySQL数据库中，字段或列的注释是用属性COMMENT来添加。

例如，创建新表的脚本中，可在字段定义脚本中添加COMMENT属性来添加注释，接着我们来举一个创建表的例子。

CREATE TABLE student(
    id INt COMMENT '学生id',
    name VARCHAR(32) COMMENT '姓名',
    age INt COMMENT '年龄',
    scORe double(4,1) COMMENT '成绩',
    birthday date COMMENT '生日',
    INSERT_time timestamp COMMENT '插入的时间'
);

2. R(RETRIEVE)：查询

1. 查询某个数据库中所有的表名称
show TABLES;

2. 查询表结构
DESC `表名`;	#等价于SHOW COLUMNS FROM `表名`; DESCRIBE `表名`;
#注意1：此处的DESC是DESCRIBE的缩写。
#注意2：DESC（DESCENT的缩写）在排序中的意义是降续排列。

3. U(UPDATE)：修改

1. 修改表名
ALTER TABLE `表名` RENAME TO `新的表名`;

2. 修改表的字符集
ALTER TABLE `表名` CHARACTER SET `字符集名称`;

3. 添加一列
ALTER TABLE `表名` ADD `列名` 数据类型;

4. 修改列的名称和数据类型
ALTER TABLE `表名` CHANGE `列名` `新列名` 新数据类型;

5. 修改数据类型
ALTER TABLE `表名` MODIFY `列名` 新数据类型;

6. 删除列
ALTER TABLE `表名` DROP `列名`;

4. D(DELETE)：删除

1. 删除表
DROP TABLE `表名`;

2. 如果表存在,则删除表
DROP TABLE IF EXISTS `表名`;

---

5、DML–数据操纵语言

DML（数据操纵语言）主要是用于增、删、改**“表”**中的数据。

1. 添加数据
INSERT INTO `表名` (`列名1`,`列名2`,...`列名n`)
VALUES (值1,值2,...值n);

#注意:
	1. 列名和值要一一对应
	2. 如果表名后不定义列名,则默认给所有列添加值
		INSERT INTO `表名`
		VALUES (值1,...值n);
	3. 除了数字类型，其他类型需要使用引号(单|双)引起来

2. 删除数据
DELETE FROM `表名` [WHERE 条件]

#注意:
	1. 如果不加条件,则会删除表中所有记录
	2. 如果要删除表中所有的记录
		-- 不推荐使用，有多少条记录会执行多少次删除操作
		1. DELETE FROM `表名`; 
		
		-- 推荐使用，效率更高；原理
		-- 原理为先删除表，然后再创建一张一样的表
		2. truncate TABLE `表名`;

3. 修改数据
UPDATE `表名` 
SET 
	列名1 = 值1,
	列名2 = 值2,
	...
	[WHERE 条件];

#注意：如果不加任何条件，则会将表中所有记录全部修改

---

6、DQL–数据查询语言

DQL（数据查询语言）主要用于查询“表”中的数据。

1. SELECT * FROM `表名`;

2. 语法
SELECT 
	`字段列表`
FROM
	`表名`
WHERE
	条件列表
GROUP BY
	分组字段
HAVING
	分组条件
ORDER BY
	排序
LIMIT
	分页限定;

6.1、基础查询

1. 多个字段的查询
SELECT `字段1`, `字段2`, ...`字段n` 
FROM `表名`;

#注意:如果查询所有字段,则可以使用"*"来替代字段列表

2. 去除重复
DISTINCT

3. 计算列
	1. 一般可以使用四则运算计算一些列的值。
	-- 一般只会进行数值型的计算
	
	2.IFNULL(表达式1, 表达式2)
    

4. 起别名
AS `别名` -- AS也可以忽略

6.2、条件查询

1. WHERE 子句后跟条件

2. 运算符
	1. >、 <、 >=、 <=、 =、 <> -- <>是不等于
	2. BETWEEN...AND -- 包含边界
	3. IN (集合)
	4. LIKE：模糊查询
		占位符：
			1. _：单个占位符
			2. %：多个任意符号
    5. IS NULL
    6. AND 或者 &&
    7. OR 或者 ||
    8. NOT 或者 !

接下来，我们以上面创建的student表为例子，我们练习几个查询语句。

-- 查询年龄大于20岁
SELECT * FROM student WHERE age > 20;

-- 查询年龄等于20岁
SELECT * FROM student WHERE age = 20;

-- 查询年龄不等于20岁
SELECT * FROM student WHERE age != 20;
SELECT * FROM student WHERE age <> 20;

-- 查询年龄大于等于20 小于等于30
SELECT * FROM student WHERE age >= 20 &&  age <=30;
SELECT * FROM student WHERE age >= 20 AND  age <=30;
SELECT * FROM student WHERE age BETWEEN 20 AND 30;

-- 查询年龄22岁，18岁，25岁的信息
SELECT * FROM student WHERE age = 22 OR age = 18 OR age = 25
SELECT * FROM student WHERE age IN (22,18,25);

-- 查询英语成绩为NULL
SELECT * FROM student WHERE englISh = NULL; -- 错误写法。NULL值不能使用 = 和（!=） 判断

SELECT * FROM student WHERE englISh IS NULL; -- 正确写法

-- 查询英语成绩不为NULL
SELECT * FROM student WHERE englISh  IS NOT NULL;

-- 查询姓马的有哪些？ like
SELECT * FROM student WHERE name LIKE '马%';

-- 查询姓名第二个字是化的人
SELECT * FROM student WHERE name LIKE "_化%";

-- 查询姓名是3个字的人
SELECT * FROM student WHERE name LIKE '___';

-- 查询姓名中包含德的人
SELECT * FROM student WHERE NAME LIKE '%德%';

---

7、DCL–数据控制语言

DCL（数据控制语言）主要是DBA用来管理系统中的对象权限时所使用，一般的开发人员很少使用。DCL用来授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视等。

DBA：数据管理员

1. 管理用户
    1. 添加用户
    	CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
    	
    2. 删除用户
    	DROP USER '用户名'@'主机号';
    	
    3. 修改用户密码
    	UPDATE USER 
    	SET PASSWORD = PASSWORD('新密码') 
    	WHERE USER = '用户名';
    	
    	-- 例如，将lisi用户的密码改为abc
    	UPDATE USER SET 
    	PASSWORD = PASSWORD('abc') 
    	WHERE USER = 'lisi';
    	
    4. 查询用户
    	-- 1. 切换到MySQL数据库
		USE mysql;
		-- 2. 查询user表
		SELECT * FROM USER;
		通配符：% 表示可以在任意主机上使用用户登录数据库
		
		

#补充：MySQL中忘记了root用户密码怎么办?
    1. 以管理员的身份运行 cmd -- > net stop mysql 停止mysql服务
    2. 使用无验证方式启动mysql服务： mysqld --skip-grant-tables
    3. 打开新的cmd窗口,直接输入mysql命令，敲回车。就可以登录成功
    4. USE mysql;
    5. UPDATE USER 
       SET PASSWORD = PASSWORD('你的新密码') 
       WHERE USER = 'root';
    6. 关闭两个窗口
    7. 打开任务管理器，手动结束mysqld.exe 的进程
    8. 启动mysql服务
    9. 使用新密码登录。

2. 权限管理
	1. 权限查询
		-- 查询权限
		SHOW GRANTS FOR '用户名'@'主机名';
		
		-- 例如，查询李四的权限
		SHOW GRANTS FOR '李四'@'%';

    2. 授予权限
    	-- 授予权限
    	GRANT 授权列表 ON `数据库名`.`表名` TO '用户名'@'主机名';
    	
    	-- 例如，给张三用户授予所有权限，在任意数据库任意表上
    	GRANT ALL ON *.* TO '张三'@'localhost';

	3. 撤销权限
		-- 撤销权限
		REVOKE 权限列表 ON `数据库名`.`表名` FROM '用户名'@'主机名';
		
		-- 例如，撤销李四用户在db3数据库account表中的修改权限
		REVOKE UPDATE ON `db3`.`account` FROM '李四'@'%';

---

注：此文章为个人学习笔记，如有错误，敬请指正。