MYSQL基础语法

目录

• 1. 什么是SQL？
• 2. SQL语句分类
• 3. DDL语句操作数据库
  • 3.1 创建数据库
  • 3.2 查看数据库
  • 3.3 修改数据库
  • 3.4 删除数据库
  • 3.5 使用数据库
• 4. DDL语句操作表结构
  • 4.1 创建表
  • 4.2 查看表
  • 4.3 修改表
  • 4.4 删除表
• 5. DML语句操作表数据
  • 5.1 给表插入数据
  • 5.2 更新表数据
  • 5.3 删除表数据
• 6. DQL语句查询表数据
  • 6.1 基本查询
  • 6.2 使用别名查询
  • 6.3 清除重复记录查询
  • 6.4 查询结果参与运算
  • 6.5 条件查询
  • 6.6 排序查询order by
  • 6.7 聚合函数查询
  • 6.8 分组查询group by
  • 6.9 分页查询limit
  • 6.10 多表连接查询
  • 6.11 子查询
• 7. DCL语句管理用户和权限
  • 7.1 创建用户
  • 7.2 用户授权
  • 7.3 撤销授权
  • 7.4 查看用户权限
  • 7.5 删除用户
  • 7.5 修改用户密码

1. 什么是SQL？

结构化查询语言(Structured Query Language)简称SQL，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。

SQL有如下特点：

• SQL是一种所有关系型数据库的查询规范，不同的数据库都支持。
• SQL是通用的数据库操作语言，可以用在不同的数据库中。
• 不同的数据库SQL 语句有一些区别。

通俗来讲，SQL相当于普通话，oracle、mysql都实现了SQL规范，但是又有一些独有的语法（相当于方言）

2. SQL语句分类

SQL语句分为4个类别，分别是：

（1）Data Definition Language (DDL 数据定义语言) 如：建库，建表。

（2）Data Manipulation Language(DML 数据操纵语言)，如：对表中的记录操作增删改。

（3）Data Query Language(DQL 数据查询语言)，如：对表中的查询操作。

（4）Data Control Language(DCL 数据控制语言)，如：对数据库用户权限的设置。

3. DDL语句操作数据库

在操作数据库之前我们先来了解一下基本常识：

（1） 每条语句以分号结尾，如果在图形化界面中不是必须加的。

（2）SQL 中不区分大小写，关键字中认为大写和小写是一样的 。

（3） SQL有3种注释方式，单行注释：--加上空格；多行注释：/* */ ;MYSQL独有注释：#。

3.1 创建数据库

-- 创建数据库有以下几种方式：
-- 1.创建数据库，不判断是否存在
CREATE DATABASE 数据库名;
-- 2.判断数据库是否已经存在，不存在则创建数据库
CREATE DATABASE IF NOT EXISTS 数据库名;
-- 3.创建数据库并指定字符集
CREATE DATABASE 数据库名 CHARACTER SET 字符集(gbk、utf8等,必须为小写字符串);

3.2 查看数据库

-- 1.查看所有数据库
show databases;
-- 2.查看某个数据库定义信息
show create database 数据库名;

3.3 修改数据库

-- 1.修改数据库默认字符集
alter database 数据库名 character set 字符集;

3.4 删除数据库

-- 1.删除数据库
drop database 数据库名;

3.5 使用数据库

-- 1.查看正在使用的数据库,属于mysql语法
select database();
-- 2.切换使用的数据库
use 数据库名;

4. DDL语句操作表结构

注意：要先使用某个数据库才能操作表

4.1 创建表

-- 1.创建表格式：
create table 表名(
	字段名1 字段数据类型1,
    字段名2 字段数据类型2,
);
-- 2.示例：创建 student 表包含 id,name,birthday 字段
create table student(
	id int,
    name varchar(20),
    birthday date -- 最后一个字段无需加,号
);
-- 3.快速创建一个表结构与student相同的表
create table teacher like student;

mysql支持以下数据类型：

4.2 查看表

-- 1.查看所有表
show tables;
-- 2.查看表结构
desc 表名;
-- 3.查看创建表语句
show create table 表名;

4.3 修改表

-- 1.修改表名
alter table 旧表名 to 新表名;
-- 2.修改表字符集
alter table 表名 character set 字符集;
-- 3.给表结构添加列
alter table 表名 add 列名 列类型;
alter table student add age int; -- 给student表添加age列，类型为int
-- 4.修改列名
alter table 表名 change 旧列名 新列名 新列名类型;
alter table student change age remark int; -- 给student表的age列名改为remark,类型为Int
-- 5.修改列类型
alter table 表名 modify 列名 列类型;
alter table student modify remark varchar(50); -- student表列名remark的类型改为varchar
-- 6.表结构删除列
alter table 表名 drop 列名;
alter table student drop remark; -- 删除student表的remark列

4.4 删除表

-- 1.直接删除表
drop table 表名;
-- 2.如果表存在，则删除表
drop table IF EXISTS 表名;

5. DML语句操作表数据

5.1 给表插入数据

-- 给表插入数据，有几种方式：
-- 1.插入全部字段
INSERT INTO 表名 (字段名 1, 字段名 2, 字段名 3) VALUES (值 1, 值 2, 值 3);
-- 插入全部字段时,可以省略字段名
INSERT INTO 表名 VALUES (值 1, 值 2, 值 3);
-- 2.插入部分字段时，必须填写字段名称，不指定列或使用null，表示插入空值。
INSERT INTO 表名 (字段名 1, 字段名 3) VALUES (值 1,  值 3);
-- 3.蠕虫复制，即将一个表的数据复制到另一个表中，需要注意两表的列数和列类型必须相同
INSERT INTO 表名 1(列 1, 列 2) SELECT 列 1, 列 2 FROM 表名 2;

给表插入数据时的注意事项：

1. 插入的数据应与字段的数据类型相同。
2. 数据的大小应在列的规定范围内，例如：不能将一个长度为 80 的字符串加入到长度为 40 的列中。
3. 在 values 中列出的数据位置必须与被加入的列的排列位置相对应。在 mysql 中可以使用 value，但不建议使 用，功能与 values 相同。
4. 字符和日期型数据应包含在单引号中。MySQL 中也可以使用双引号做为分隔符。
5. 不指定列或使用 null，表示插入空值。

5.2 更新表数据

-- 1.修改表中所有数据，多个列之间用逗号隔开
UPDATE 表名 SET 字段名1=值1,字段名2=值2; 
-- 2.修改表中符合条件的数据
UPDATE 表名 SET 字段名1=值1,字段名2=值2 WHERE 字段名=值;

5.3 删除表数据

-- 1.删除所有表数据
DELETE FROM 表名;
-- 2.删除表中符合条件的数据
DELETE FROM 表名 WHERE 字段名=值;
-- 3.删除表的结构，再创建一个表名表结构相同的表。
TRUNCATE TABLE 表名;

truncate 和 delete 的区别：

	truncate	delete
条件删除	不支持	支持
事务回滚	不支持	支持
清理速度	快	慢
高水位重置	是	否

高水位重置的含义：随着不断地进行表记录的DML操作，会不断提高表的高水位线（HWM），DELETE操作之后虽然表的数据删除了，但是并没有降低表的高水位，随着DML操作数据库容量也只会上升，不会下降。所以如果使用DELETE，就算将表中的数据减少了很多，在查询时还是很和DELETE操作前速度一样。而TRUNCATE操作会重置高水位线，数据库容量也会被重置，之后再进行DML操作速度也会有提升。

6. DQL语句查询表数据

6.1 基本查询

-- 1.查询所有行和列的数据，使用*表示所有列
SELECT * FROM 表名;
-- 2.查询所有行的指定列数据
SELECT 字段名 1, 字段名 2, 字段名 3 FROM 表名;

6.2 使用别名查询

表使用别名的原因：用于多表查询操作,关键字为as

使用别名的好处： 显示的时候使用新的名字，并不修改表的结构。

-- 1. 给列起别名
SELECT 字段名 1 AS 别名, 字段名 2 AS 别名 FROM 表名;
-- 2. 给列和表起别名
SELECT 字段名 1 AS 别名, 字段名 2 AS 别名 FROM 表名 AS 表别名;

6.3 清除重复记录查询

一般用来查询不重复记录的条数，关键字为distinct

-- 1.清除字段名1重复的记录
SELECT DISTINCT 字段名1 FROM 表名;
-- 2.清除字段名1和字段名2都重复的记录
SELECT DISTINCT 字段名1,字段名2 FROM 表名;

6.4 查询结果参与运算

注意： 参与运算的必须是数值类型，数值类型加上单引号也可以

-- 1.列名加固定值运算查询
SELECT 列名 1 + 固定值 FROM 表名;
-- 2.列名加列名运算查询
SELECT 列名 1 + 列名 2 FROM 表名;

6.5 条件查询

如果没有查询条件，则每次查询所有的行。实际应用中，一般要指定查询的条件。对数据进行过滤。

常用条件运算符：

-- 1.查询 math 分数大于 80 分的学生
select * from student where math>80;
-- 2.查询 english 分数小于或等于 80 分的学生
select * from student where english <=80;
-- 3.查询 age 等于 20 岁的学生
select * from student where age = 20;
-- 4.查询 age 不等于 20 岁的学生，注：不等于有两种写法,!=属于mysql自有写法
select * from student where age <> 20;
select * from student where age != 20;
-- 5.查询 id 是 1 或 3 或 5 的学生
select * from student where id in(1,3,5);
-- 6.查询 id 不是 1 或 3 或 5 的学生
select * from student where id not in(1,3,5);
-- 7.查询 english 成绩大于等于 75，且小于等于 90 的学生
select * from student where english between 75 and 90;
-- 8.模糊查询，%表示匹配多个字符，_表示只匹配一个字符
-- 9.查询姓张的学生
select * from student where name like '张%';
select * from student where name like '张';
-- 10.查询姓名中包含'三'字的学生
select * from student where name like '%三%';
-- 查询姓张，且姓名有两个字的学生
select * from student where name like '张_';
-- 11.查询 age 大于 35 且性别为男的学生(两个条件同时满足)
select * from student where age>35 and sex='男';
-- 12.查询 age 大于 35 或性别为男的学生(两个条件其中一个满足)
select * from student where age>35 or sex='男';
-- 13.查询 id 是 1 或 3 或 5 的学生
select * from student where id=1 or id=3 or id=5;

6.6 排序查询order by

通过 ORDER BY 子句，可以将查询出的结果进行排序(排序只是显示方式，不会影响数据库中数据的顺序)

-- 语法格式如下，其中ASC表示升序，是默认值;DESC是降序。
SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名 [ASC|DESC];
-- 1.查询所有数据,使用年龄降序排序
select * from student order by age desc;
-- 2.查询所有数据,在年龄降序排序的基础上，如果年龄相同再以数学成绩升序排序
select * from student order by age desc, math asc;

6.7 聚合函数查询

之前我们做的查询都是横向查询，它们都是根据条件一行一行的进行判断，而使用聚合函数查询是纵向查询， 它是对一列的值进行计算，然后返回一个结果值。聚合函数会忽略空值 NULL。

常用聚合函数：

-- 1.查询学生总数,我们发现对于NULL的记录不会统计
select count(id) as 总人数 from student;
select count(*) as 总人数 from student;
-- 2.我们可以利用 IFNULL()函数，如果记录为 NULL，给个默认值，这样统计的数据就不会遗漏
-- IFNULL()函数解析：IFNULL(列名，默认值) 如果列名不为空，返回这列的值。如果为 NULL，则返回默认值。
select count(ifnull(id,0)) from student;
-- 3.查询年龄大于 20 的总数
select count(*) from student where age>20;
-- 4.查询数学成绩总分
select sum(math) 总分 from student;
-- 5.查询数学成绩平均分
select avg(math) 平均分 from student;
-- 6.查询数学成绩最高分
select max(math) 最高分 from student;
-- 7.查询数学成绩最低分
select min(math) 最低分 from student;

6.8 分组查询group by

分组查询是指使用 GROUP BY 语句对查询信息进行分组，相同数据作为一组。分组的目的是为了统计，一般分组会跟聚合函数一起使用。

-- 1.按性别进行分组，求男生和女生数学的平均分
select sex, avg(math) from student3 group by sex;
-- 2.按性别进行分组，求男生和女生的人数
select sex, count(*) from student3 group by sex;
-- 3.按性别分组，查询男生和女生年龄大于 25 岁的人数
select sex, count(*) from student3 where age > 25 group by sex ;
-- 4.按性别分组，查询男生和女生年龄大于 25 岁的人数，并且只显示人数大于2的分组
-- 正确写法：对分组查询的结果再进行过滤
SELECT sex, COUNT(*) FROM student3 WHERE age > 25 GROUP BY sex having COUNT(*) >2;
-- 错误写法：WHERE子句，是针对行的过滤。要对分组结果进行过滤，必须使用HAVING子句
SELECT sex, COUNT(*) FROM student3 WHERE age > 25 GROUP BY sex WHERE COUNT(*) >2;

having 与 where 的区别：

6.9 分页查询limit

我们在登录京东，淘宝时，返回的商品信息可能有几万条，不可能一次全部显示出来。必须使用分页来限制返回的数据条数。

limit的语法格式：LIMIT offset,length;

offset表示起始行数，从 0 开始，如果省略，默认就是 0；length表示从起止行数开始，要返回几条记录。

-- 1.查询学生表中数据，从第 3 条开始显示，显示 6 条。
select * from student limit 2,6;
-- 2.如果第一个参数是 0 可以省略写：
select * from student limit 5;
-- 3.如果数据不够 10 条，有多少显示多少
select * from student limit 10,10;

6.10 多表连接查询

（1）内连接

用左边表的记录去匹配右边表的记录，如果符合条件的则显示。如：从表.外键=主表.主键

隐式内连接：看不到 JOIN 关键字，条件使用 WHERE 指定

-- 格式：SELECT 字段名 FROM 左表, 右表 WHERE 条件
-- 具体实现：
select * from emp,dept where emp.`dept_id` = dept.`id`;

显式内连接：使用 INNER JOIN ... ON 语句, 可以省略 INNER

-- 格式：SELECT 字段名 FROM 左表 [INNER] JOIN 右表 ON 条件
-- 具体实现：
select * from emp e inner join dept d on e.`dept_id` = d.`id`;

（2）外连接

左外连接：使用 LEFT OUTER JOIN ... ON，OUTER 可以省略

左外连接是用左边表的记录去匹配右边表的记录，如果符合条件的则显示；否则，显示 NULL 可以理解为：在内连接的基础上保证左表的数据全部显示(左表是部门，右表员工)。

-- 格式：SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 条件
-- 具体实现：
select * from dept d left join emp e on d.`id` = e.`dept_id`;

右外连接：使用 RIGHT OUTER JOIN ... ON，OUTER 可以省略

右外连接是用右边表的记录去匹配左边表的记录，如果符合条件的则显示；否则，显示 NULL 可以理解为：在内连接的基础上保证右表的数据全部显示。

-- 格式：SELECT 字段名 FROM 左表 RIGHT [OUTER ]JOIN 右表 ON 条件
-- 具体实现：
select * from dept right join emp on dept.`id` = emp.`dept_id`;

6.11 子查询

子查询的概念：

• 一个查询的结果做为另一个查询的条件
• 有查询的嵌套，内部的查询称为子查询
• 子查询要使用括号

（1）子查询是单行单列

子查询结果是单行单列，一般在 WHERE 后面作为条件，给父查询使用。

-- 格式：SELECT 查询字段 FROM 表 WHERE 字段=（子查询）;
-- 具体实现：根据最高工资到员工表查询到对应的员工信息
select * from emp where salary = (select max(salary) from emp);

（2）子查询是多行单列

子查询结果是多行单列，结果集类似于一个数组，父查询使用 IN 运算符。

-- 格式：SELECT 查询字段 FROM 表 WHERE 字段 IN （子查询）;
-- 具体实现：查询工资大于 5000 的员工，来自于哪些部门
select name from dept where id in (select dept_id from emp where salary > 5000);

（3）子查询是多行多列

子查询结果是多行多列，一般在 FROM 后面作为表以供查询。

-- 格式：SELECT 查询字段 FROM （子查询） 表别名 WHERE 条件;
-- 具体实现：查询出 2011 年以后入职的员工信息，包括部门名称
select * from dept d, (select * from emp where join_date >='2011-1-1') e where
d.`id`= e.dept_id ;

7. DCL语句管理用户和权限

我们现在默认使用的都是 root 用户，超级管理员，拥有全部的权限。但是，一个公司里面的数据库服务器上面可能同时运行着很多个项目的数据库。所以，我们应该可以根据不同的项目建立不同的用户，分配不同的权限来管 理和维护数据库。

注：mysqld 是 MySQL 的主程序，服务器端。mysql 是 MySQL 的命令行工具，客户端。

7.1 创建用户

（1）语法格式

CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

（2）具体实现

-- 1.创建 user1 用户，只能在 localhost 这个服务器登录 mysql 服务器，密码为 123456
create user 'user1'@'localhost' identified by '123456';
-- 2.创建 user2 用户可以在任何电脑上登录 mysql 服务器，密码为 123456
create user 'user2'@'%' identified by '123456';

注：创建的用户名都在 mysql 数据库中的 user 表中可以查看到，密码经过了加密。

7.2 用户授权

用户创建之后，没什么权限，需要给用户授权。

（1）语法格式

GRANT 权限 1, 权限 2... ON 数据库名.表名 TO '用户名'@'主机名

（2）具体实现

-- 1. 给 user1 用户分配对 test 这个数据库操作的权限：创建表，修改表，插入记录，更新记录，查询
grant create,alter,insert,update,select on test.* to 'user1'@'localhost';
-- 2. 给 user2 用户分配所有权限，对所有数据库的所有表
grant all on *.* to 'user2'@'%';

7.3 撤销授权

（1）语法格式

REVOKE 权限 1, 权限 2... ON 数据库.表名  from '用户名'@'主机名';

（2）具体实现

-- 撤销 user1 用户对 test 数据库所有表的操作的权限
revoke all on test.* from 'user1'@'localhost';

7.4 查看用户权限

（1）语法格式

SHOW GRANTS FOR '用户名'@'主机名';

（2）具体实现

-- 查看user1的操作权限
SHOW GRANTS FOR user1'@'localhost';

7.5 删除用户

（1）语法格式

DROP USER '用户名'@'主机名';

（2）具体实现

-- 删除用户user2
DROP USER 'user2'@'%';

7.5 修改用户密码

（1）语法格式

set password for '用户名'@'主机名' = password('新密码');

（2）具体实现

-- 将'user1'@'localhost'的密码改成'666666'
set password for 'user1'@'localhost' = password('666666');