Mysql基础（二）——SQL语言及基本操作

---

启动MySQL服务

• 通过命令net start mysql启动MySQL服务
  

• 通过命令mysqll -u root -p，输入密码，连接sql
  

• 通过命令alter user ‘root’@‘localhost’ identified with mysql_native_password BY ‘新密码’;修改密码（tips：分号不能省略）
  

• 通过命令quit退出数据库
  

卸载sql

• 通过命令net stop mysql停止服务
  
  -通过命令sc delete mysql或者mysqld remove mysql删除sql
  
• 删除目录文件

---

SQL语言

SQL：Structure Query Language（结构化查询语言）

SQL被美国国家标准局（ANSI）确定为关系型数据库语言的美国标准，后来被国际化标准组织（ISO）采纳为关系数据库语言的国际标准

CRUD操作： Create、Read、Update、Delete

SQL 是一种标准化的语言，它允许你在数据库上执行操作，如创建项目、查询内容、更新内容、删除条目等操作

• 一个简单的类比：
  普通话：各数据库厂商都支持ISO的SQL标准
  方言：各数据库厂商在标准的基础上所做的自己的扩展

---

SQL运算符

算术运算符

符号	描述
+	加
-	减
*	乘
/	除
%	取余

赋值运算符

符号	描述
=	等于

tips：从右往左赋值

逻辑运算符

作用:用于连接多个条件时使用

符号	描述
and	与
or	或
not	非

关系运算符

符号	描述
>	大于
<	小于
>=	大于等于
<=	小于等于
=	等于
!=	不等于
<>	不等于

---

SQL语句分类

• DDL（Data Definition Language）
  数据定义语言，用来定义数据库对象：库、表、列等

• DML（Data Manipulation Language）
  数据操作语言，用来定义数据库记录（数据）增删改

• DCL（Data Control Language）
  数据控制语言，用来定义访问权限和安全级别

• DQL（Data Query Language）
  数据查询语言，用来查询记录（数据）查询

tips：
sql语句以;结尾
mysql中关键字不区分大小写
字符串类型和日期类型都要用单引号括起来
空值：null

---

一、 DDL数据定义语言

数据定义语言，用来定义数据库对象：库、表、列等

DDL操作数据库

创建数据库

CREATE DATABASE用于创建新的数据库

编码方式:gb2312,utf-8,gbk,iso-8859-1

---

1.create database 数据库名

CREATE DATABASE mydb1;

显示OK即创建成功：

---

2.create database 数据库名 character set 编码方式

CREATE DATABASE mydb2 character SET GBK;

显示OK即创建成功：

---

3.create database 数据库名 set 编码方式 collate 排序规则

CREATE DATABASE mydb3 character SET GBK COLLATE gbk_chinese_ci;

显示OK即创建成功：

---

查看数据库

SHOW DATABASE用于查看创建的数据库

1.查看当前数据库服务器中的所有数据库

show databases;

操作结果如下：

tips：系统创建的默认数据库不能删除，否则会出现错误

---

2.查看前面创建的数据库的定义信息
show create database 数据库名;

Show CREATE DATABASE mydb2;

操作结果如下：

---

修改数据库

alter database 数据库名 character set 编码方式

查看服务器中的数据库，并把mydb2的字符集修改为utf8;

ALTER DATABASE mydb2 character SET utf8;

操作结果如下：

---

删除数据库

drop database 数据库名

DROP DATABASE mydb3;

操作结果如下：

---

其他语句

查看当前使用的数据库

Select database();

操作结果如下：

---

使用、切换数据库

use 数据库名

USE mydb2;

操作结果如下：

---

DDL操作表

常用数据类型

类型	描述
int	整型
double	浮点型，例如double(5,2)表示最多5位，其中必须有2位小数，即最大值为999.99；默认支持四舍五入
char	固定长度字符串类型； char(10) 'aaa ’ 占10位
varchar	可变长度字符串类型； varchar(10) ‘aaa’ 占3位
text	字符串类型，比如小说信息；
blob	字节类型，保存文件信息(视频，音频，图片)；
date	日期类型，格式为：yyyy-MM-dd；
time	时间类型，格式为：hh:mm:ss
timestamp	时间戳类型 yyyy-MM-dd hh:mm:ss 会自动赋值
datetime	日期时间类型 yyyy-MM-dd hh:mm:ss

获取当前系统时间：now()

select now();

---

创建表

CREATE TABLE语句用于创建新表

CREATE TABLE 表名(
列名1 数据类型 [约束],
列名2 数据类型 [约束],
列名n 数据类型 [约束]
);

• 表名、列名自定义
• 多列之间使用逗号间隔，最后一列的逗号不写
  [约束] 表示可有可无

CREATE TABLE Employees(
	id INT ,
	age INT ,
	first VARCHAR(255),
	last VARCHAR(255)
);

create table Student(name varchar(5),age int,sex char(1));

操作结果如下：

---

查询表

show tables;

操作结果如下：

---

查看表的字段（列）信息

desc 表名；

DESC Employees;
DESC Student;

操作结果如下：

---

删除表

drop table 表名;

drop table Student;

操作结果如下：

---

修改表名

alter table 旧表名 rename 新表名；

alter table employees rename employee;

操作结果如下：

---

查看表格创建细节

show create table 表名;

show create table employees;

---

修改表的字符集

不建议使用

alter table 表名 character set 编码方式

ALTER TABLE employees CHARACTER SET gbk;

---

DDL操作列

添加列

alter table 表名 add 新列名 新的数据类型

在上面员工表的基本上增加一个image列

ALTER TABLE employee ADD image blob;

操作结果如下：

---

修改列

1.删除旧列，增加新列

alter table 表名 change 旧列名 新列名 新的数据类型

ALTER TABLE employees change id newID int;

操作结果如下：

---

2.修改列的数据类型

alter table 表明 modify 列名 新的数据类型

ALTER TABLE employees MODIFY newID varchar(60);

操作结果如下：

---

3.修改列名
alter table 表名 change 旧列名 新列名 旧的数据类型

ALTER TABLE employees CHANGE newID id varchar(100);

操作结果如下：

---

删除列

一次只能删一列

alter table 表名 drop 列名

ALTER TABLE employees DROP image;

操作结果如下：

---

二、 DML数据操作语言

（区别于DDL）

数据操作语言，用来定义数据库记录（数据）增删改

即对表中的数据进行增、删、改的操作，主要包括INSERT 、UPDATE、 DELETE

---

插入操作

tips：

1. 列与列以及列值和列值之间用逗号隔开

2. 列名和列值一一对应，即列名与列值的类型、个数、顺序要一一对应

3. 非数值的列值两侧需要加单引号

4. 当给所有列添加数据的时候可将列名省略

5. 参数值不要超出列定义的长度

6. 插入空值：列名 is null 或者 列名=’'

7. 日期和字符使用引号括起来

---

1.插入单行数据

insert into 表名(列名) values(数据值);

insert into student values('张三',18,'男','2000-1-1');

操作结果如下：

---

2.插入多行数据
insert into 表名(列名) values(第一行数据),(第二行数据),(),();

insert into student(stuname,stuage,stusex,birthday)
	values ('张三',18,'男','2000-1-1'),
	('王五',18,'男','2000-1-3');

操作结果如下：

---

更新操作

UPDATE 表名 SET 列名1=列值1,列名2=列值2 … WHERE 列名=值

 update emp set salary = 5000;

操作结果如下：

---

删除操作

1. DELETE from 表名 【WHERE 列名=值】

2. TRUNCATE TABLE 表名

delete 与 truncate（面试题）

delete	truncate
删除表中的数据，表结构还在	把表直接DROP掉，再创建一个同样的新表
删除后的数据可以找回	删除的数据不能找回
执行速度慢	执行速度比delete快

---

1.删除某条记录

 delete from emp where name = 'zhangsan';

操作结果如下：

---

2.删除全部记录

delete

delete* from emp;

操作结果如下：

---

truncate

truncate table emp;

操作结果如下：

---

三、 DCL数据控制语言

数据控制语言，用来定义访问权限和安全级别

创建用户

1.create user 用户名@指定ip identified by 密码;

@localhost表示本机

create user admin666@localhost identified by '666'

操作结果如下：

---

2.指定ip才能登陆：create user 用户名@客户端ip identified by 密码;

create user test456@10.4.10.18 identified by 'test456'

---

3.任意ip都可以登录： create user 用户名@‘% ’ identified by 密码

create user test7@'%' identified by 'test7'

---

用户授权

1.给指定用户授予指定指定数据库指定权限

grant 权限1,权限2,…,权限n on
数据库名.* to 用户名@IP;

grant select,insert,update,delete,create on 
manaer.* to 'admin666'@localhost;

操作结果如下：

---

2.给指定用户授予所有数据库所有权限

grant all on . to 用户名@IP

grant all on *.* to 'admin666'@localhost;

---

用户权限查询

show grants for 用户名@IP;

show grants for 'admin666'@localhost;

操作结果如下：

---

撤销用户权限

revoke 权限1,权限2,…,权限n on 数据库名.* from 用户名@IP

 revoke select on manaer.* from 'admin666'@localhost;

操作结果如下：

---

删除用户

drop user 用户名@IP;

drop user admin666@localhost;

操作结果如下：

---

四、 ☆DQL数据查询语言

查询关键字：SELECT

不会对数据进行改变，而是发送查询结果给客户端

查询返回的结果集是一张虚拟表

* 表示所有列

语法：

SELECT 列名 FROM 表名 【WHERE --> BROUP BY–>HAVING–> ORDER BY】

• SELECT 要查询的列名称
• FROM 表名称
• WHERE 限定条件 -----行条件
• GROUP BY grouping_columns -----对结果分组
• HAVING condition -----分组后的行条件
• ORDER BY sorting_columns -----对结果分组
• LIMIT offset_start, row_count -----结果限定

---

简单查询

对stu表进行操作
1.查询所有列

SELECT * FROM 表名

SELECT * FROM stu;

操作结果如下：

---

2.查询指定列

SELECT 列1,列2… FROM 表名;

select sname,age from stu;

操作结果如下：

---

条件查询

在查询时给出WHERE

在WHERE子句中可使用如下运算符及关键字：
AND；OR；NOT；
=、!=、<>、<、<=、>、>=;
BETWEEN…AND；
IN(set)；
IS NULL；

---

代码示例：

1.查询性别为女，并且年龄50以内的记录

select* from stu where gender='female' AND age<50;

操作结果如下：

---

2.查询学号为S_1001，或者姓名为liSi的记录

SELECT * FROM stu WHERE sid ='S_1001' OR sname='liSi';

操作结果如下：

---

3.查询学号为S_1001，S_1002，S_1003的记录

SELECT * FROM stu WHERE sid IN ('S_1001','S_1002','S_1003');

操作结果如下：

---

4.查询学号不是S_1001，S_1002，S_1003的记录

SELECT * FROM stu WHERE sid NOT IN('S1001','S1002','S_1003');

操作结果如下：

---

5.查询年龄为null的记录

SELECT * FROM stu WHERE age IS NULL;

操作结果如下：

---

6.查询年龄在20到40之间的学生记录

SELECT * FROM stu WHERE age>=20 AND age<=40;
#或者
SELECT * FROM stu WHERE age BETWEEN 20 AND 40;

操作结果如下：

---

7.查询性别非男的学生记录

SELECT * FROM stu WHERE gender!='male';
#或者
SELECT * FROM stu WHERE gender<>'male';
#或者
SELECT * FROM stu WHERE NOT gender='male';

操作结果如下：

---

8.查询姓名不为null的学生记录

SELECT * FROM stu WHERE NOT sname IS NULL;
#或者
SELECT * FROM stu WHERE sname IS NOT NULL;

操作结果如下：

---

模糊查询

关键字：LIKE

查询仅包含某一部分的字段内容

列名 like ‘表达式’ //表达式必须是字符串

表达式：

通配符	描述
_(下划线)	任意一个字符
%	任意n个字符

---

代码示例：
1.查询姓名由3个字构成的学生记录

SELECT * FROM stu WHERE sname LIKE '___'; # 三个'_'（下划线）

操作结果如下：

---

2.查询姓名由5个字母构成，并且第5个字母为“i”的学生记录

SELECT * FROM stu WHERE sname LIKE '____i';

操作结果如下：

---

3.查询姓名以“i”结束的学生记录

SELECT * FROM stu WHERE sname LIKE '%i';

操作结果如下：

---

4.查询姓名中第2个字母为“i”的学生记录

 SELECT * FROM stu WHERE sname LIKE '_i%';

操作结果如下：

---

5.查询姓名中包含字母“i”的学生记录

SELECT * FROM stu WHERE sname LIKE '%i%';

操作结果如下：

---

字段控制查询

对emp表进行操作

select* from emp;

操作结果如下：

---

去重

关键字：distinct

select distinct deptno from emp;

操作结果如下：

---

求和

数值类型可以做加运算

查看月薪sal与佣金comm之和

SELECT *,sal+comm FROM emp;

操作结果如下：

IFNULL：NULL转换成数值0

SELECT *,sal+IFNULL(comm,0) FROM emp;

操作结果如下：

---

给列名添加别名

关键字：AS（可省略）

SELECT *, sal+IFNULL(comm,0) AS total FROM emp;
#或者
SELECT *, sal+IFNULL(comm,0) total FROM emp;

操作结果如下：

---

排序

order by 列名 asc/desc

• asc：升序（默认为升序）
• desc：降序

代码示例：
1.查询所有雇员，按月薪升序排序

select* from emp
	order by sal asc;
#或者
select* from emp
	order by sal;

操作结果如下：

---

2.查询所有雇员，按月薪降序排序

select* from emp
	order by sal desc;

操作结果如下：

---

多列排序：当前面的列的值相同的时候，按照后面的列值进行排序

3.查询所有雇员，按月薪降序排序，如果月薪相同时，按编号升序排序

SELECT * FROM emp
ORDER BY sal DESC,empno ASC;

操作结果如下：

---

聚合函数

做纵向运算的函数

函数	描述
COUNT(列名)	统计指定列不为NULL的记录行数
MAX(列名)	计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算
MIN(列名)	计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算
SUM(列名)	计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0
AVG(列名)	计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0

---

count

统计指定列不为NULL的记录行数

代码示例：

1.查询emp表中记录数，查询emp表中有佣金的人数

SELECT COUNT(*) AS cnt FROM emp;
SELECT COUNT(comm) cnt FROM emp;

操作结果如下：

---

2.统计月薪与佣金之和大于2500元的人数

SELECT COUNT(*) AS cnt FROM emp
WHERE sal+IFNULL(comm,0) > 2500;

操作结果如下：

---

3.查询有佣金的人数、有领导的人数

SELECT COUNT(comm), COUNT(mgr)FROM emp;

操作结果如下：

---

max、min

计算指定列的最大值or指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算

代码示例：

查询最高工资和最低工资

SELECT MAX(sal), MIN(sal) FROM emp;

操作结果如下：

---

sum

计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0

代码示例：

1.查询所有雇员月薪和，以及所有雇员佣金和

SELECT SUM(sal), SUM(comm) FROM emp;

操作结果如下：

---

2.查询所有雇员月薪+佣金和

SELECT SUM(sal+IFNULL(comm,0))FROM emp;

操作结果如下：

---

avg

计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0

代码示例：

统计所有员工平均工资

SELECT AVG(sal) FROM emp;

操作结果如下：

---

分组查询

使用GROUP BY子句

tips：

1. 如果有分组操作，则select后面能添加的只能是聚合函数和被分组的列名
2. 支持多列分组

---

代码示例：

1.查询每个部门的部门编号和每个部门的工资和

SELECT deptno, SUM(sal) FROM emp GROUP BY deptno;

操作结果如下：

---

2.查询每个部门的部门编号以及每个部门工资大于1500的人数

SELECT deptno,COUNT(*) FROM emp WHERE sal>1500 GROUP BY deptno;

操作结果如下：

---

HAVING子句

having与where的区别

having	where
分组后对数据进行过滤	分组前对数据进行过滤
having后面可以使用分组函数(统计函数)	where后面不可以使用分组函数
对分组后数据的约束	对分组前的条件，如果某行记录没有满足WHERE子句的条件，那么这行记录不会参加分组

代码示例：

查询每个部门的部门编号和每个部门的工资和，并且工资和>7500的部门

select deptno,sum(sal)
	from emp
	group by deptno
	having sum(sal)>7500;

操作结果如下：

---

限定查询

关键字：limit

limit用来限定查询结果的起始行，以及总行数

• limit 开始下标,显示条数；//开始下标从0开始，即第一行
• limit 显示条数；//表示默认从0开始获取数据

代码示例：

1.查询5行记录，起始行从0开始

SELECT * FROM emp LIMIT 0, 5;
#或者
SELECT * FROM emp LIMIT 5;

操作结果如下：

---

2.查询10行记录，起始行从3开始

SELECT* FROM emp LIMIT 3, 10;

操作结果如下：

---

分页查询

如果一页记录为10条，希望查看第3页记录应该怎么查呢？
第一页记录起始行为0，一共查询10行； limit 0,10
第二页记录起始行为10，一共查询10行；limit 10,10
第三页记录起始行为20，一共查询10行； limit 20,10

limit (pageindex-1)*pagesize,pagesize;

• pageIndex 页码值
• pageSize 每页显示条数

查询语句书写顺序：select - from - where - groupby - having - order by - limit
查询语句执行顺序：from - where - group by - having - select - order by - limit