Mysql-基础

mysql 数据库管理系统

数据库出厂设置

(1)information_schema:记录mysql数据库所含库、表信息(本质是个视图)
(2)mysql             :存放mysql数据库核心的相关信息(本质是数据库)
(3)performance_shcema:存放mysql数据库性能相关的信息(本质是数据库)
(4)sys               :存放mysql数据库系统相关的信息(本质是数据库)

数据库管理命令

数据库登录命令

mysql -uroot -pyingxin20000303 -h192.168.1.1 -P3306
	-u:设置登录用户
	-p:输入登录密码
	-h:设置登录服务器ip地址
	-P:设置登录端口号

数据库登出命令

(1)exit
(2)quit

mysql 结构化查询语言

sql 通用规则

(1)SQL可单行也可多行书写,以分号结尾
(2)不区分大小写,关键字建议大写
(3)单行注释用"#"或"-- ",多行注释用"/* */"

sql 语句分类

DDL:数据库定义语句(定义数据库对象)
DML:数据的操作语句(对数据表进行增、删、改)
DQL:数据的查询语句(对数据表进行查询)
DCL:数据的控制语句(定义数据库访问权限与安全级别和创建用户)

sql 具体语句

DDL 语句

数据库操作

查询数据库

SHOW DATABASES;

创建数据库

CREATE DATABASE 数据库名;                #直接创建数据库
CREATE DATABASE IF NOT EXISTS 数据库名;  #判断是否不存在同名数据库后再创建数据库

删除数据库

DROP DATABASE 数据库名;                  #直接删除数据库
DROP DATABASE IF EXISTS 数据库名;        #判断是否存在数据库后再删除数据库

使用数据库

USE 数据库名;

查看数据库

SELECT DATABASE();

数据表操作

查询数据表

SHOW TABLES;                         #查询数据表
DESC 数据表名;                        #查询数据表结构

创建数据表

CREATE TABLE 数据表名( 
  字段名1	数据类型1,
  字段名2	数据类型2,
  字段名3	数据类型3
);

约束:约束时作用于表中列上的规则,用于限制加入表的数据
作用:保证了数据库中数据的正确性、有效性和完整性

约束名称	描述	关键字
非空约束	列中数据不为空	NOT NULL
唯一约束	列中数据各不相同	UNIQUE
主键约束	列中数据非空且唯一	PRIMARY KEY
检测约束	列中数据满足自定义条件	CHECK
默认约束	列中未指定的数据采用默认值	DEFAULT
外键约束	建立多表连接,保证数据的一致性和完整性	FOREIGN KEY

AUTO_INCREMENT:自动增长(当列是数字类型且唯一约束)(从1开始自动增长)

CREATE TABLE STU(
  ID INT PRIMARY KEY AUTO_INCREMENT,       #主键,自动增长
  NAME VARCHAR(50) NOT NULL UNIQUE,        #非空,唯一
  JOINDATA DATE NOT NULL,                  #非空
  SALARY DOUBLE(7,2) DEFAULT,              #默认
  CLASSID INT NOT NULL,                    #非空
  CONSTRAINT FK_CLASS_CLASS FOREIGN KEY(CLASSID) REFERENCES CALSS(ID) #外键
)

表间关系:通过外键建立标间关系(外键是主表的一个字段,来源于从表的主键)
	(1):一对一(外键需设置唯一约束)
	(2):一对多(多的一方是外键)
	(3):多对多(借助第三张表实现)

删除数据表

DROP DATABASE 数据表名;                  #直接删除数据表
DROP DATABASE IF EXISTS 数据表名;        #判断是否存在数据库后再删除数据表

修改数据表

ALTER TABLE 表名 RENAME TO  新表名;       
#修改数据表名称
ALTER TABLE 表名 ADD    列名 数据类型;    
#给数据表增加一列
ALTER TABLE 表名 DROP   列名;             
#给数据表删除一列
ALTER TABLE 表名 MODIFY 列名 新数据类型;  
#修改数据表列的数据类型
ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型; 
#修改数据表列名和数据类型

事务的操作

概念

事务:事务上一个不可分割的逻辑工作单元,包含一组数据库操作,事务整体向系统提交请求,要么同时执行成功,要么同时执行失败
注意:在mysql中当未手动设置事务时,系统一条语句即为一个事务,自动开始事务和提交事务

特性

A(原子性):事务是不可分割的最小操作单位,要么同时执行成功,要么同时执行失败
C(一致性):事务完成后必须保证所有数据保持一致性
I(隔离性):
D(持久性):事务已提交之后将所有临时操作边为永久的操作

操作

BEGIN;   :开启事务(事务未提交之前对数据库所做操作均为临时操作)
COMMIT;  :提交事务(事务已提交之后将所有临时操作边为永久的操作)
ROLLBACK;:回滚事务(事务未提交之前可随时使用回滚将数据库状态恢复到事务开启之前)

DML 语句

添加数据

INSERT INTO 表名(列名1,列名1,列名1)VALUES(值1,值1,值1);                      
#给指定列添加数据
INSERT INTO 表名(列名1,列名1,列名1)VALUES(值1,值1,值1),VALUES(值1,值1,值1);   
#给指定列批量添加数据
INSERT INTO 表名 VALUES(值1,值1,值1);                                     
#给所有列添加数据
INSERT INTO 表名 VALUES(值1,值1,值1),VALUES(值1,值1,值1);                  
#给所有列批量添加数据

删除数据

DELETE FROM 表名 WHERE 标识记录的条件;     #删除一行的数据
DELETE FROM 表名;                        #删除所有行数据

修改数据

UPDATE 表名 SET 列名1=值1,列名2=值3,列名3=值3 WHERE 标识记录的条件;
#更改一行的数据
UPDATE 表名 SET 列名1=值1,列名2=值3,列名3=值3;                 
#更改所有行数据

DQL 语句

单表查询

基础查询

SELECT * FROM 表名;               
#查询数据表所有数据
SELECT 字段列表 FROM 表名;         
#查询字段对应的数据
SELECT DISTINCT 字段列表 FROM 表名; 
#查询字段对于的数据(去除重复记录项)
SELECT 字段列表  AS 别名  FROM 表名;
#查询字段对于的数据(给字段添加别名)(同理可对数据表起别名)

(1)查询学生表全部信息
	SELECT * FROM STU;
(2)查询数学信息
	SELECT MATH FROM STU;
(3)查询数学成绩,相同的数学成绩只显示一次
	SELECT DISTINCT MATH FROM STU;
(4)查询数学成绩,给数学成绩字段取个别名
	SELECT MATH AS MATHGRADE FROM STU;

条件查询

SELECT 字段列表 FROM 表名 WHERE 条件列表;

条件符号	功能
>	大于
<	小于
>=	大于等于
<=	小于等于
==	等于
!=或<>	不等于
BETWEEN ADN	在范围之间
IN	多选一
LIKE	模糊查询(_:单字符占位符;%:多字符占位符)
IS NULL	是空
IS NOT NULL	非空
AND或&&	与
OR或\|\|	或
NOT或!	非

(1)查询年龄大于20岁学员信息
	SELECT * FROM STU WHERE AGE > 20;
(2)查询年龄大于等于20岁小于等于30岁学员信息
	SELECT * FROM STU WHERE AGE >= 20 AND AGE <= 30;
	SELECT * FROM STU WHERE AGE >= 20 &&  AGE <= 30;
	SELECT * FROM STU WHERE AGE BETWEEN 20  AND  30;
(3)查询年龄不等于18岁学员信息
	SELECT * FROM STU WHERE AGE != 20;
	SELECT * FROM STU WHERE AGE <> 20;
(4)查询年龄等于10岁或等于20岁或等于30岁学员信息
	SELECT * FROM STU WHERE AGE == 10 OR AGE == 20 OR AGE == 30;
	SELECT * FROM STU WHERE AGE == 10 || AGE == 20 || AGE == 30;
	SELECT * FROM STU WHERE AGE IN(10,20,30);
(5)查询年龄为空学员信息
	SELECT * FROM STU WHERE AGE IS NULL;
(6)查询姓马学员信息
	SELECT * FROM STU WHERE NAME LIKE '马%' ;
(7)查询名字第二个字是花的学员信息
	SELECT * FROM STU WHERE NAME LIKE '_花%';
(8)查询名字内包含德德学员信息
	SELECT * FROM STU WHERE NAME LIKE '%马%';

排序查询

SELECT 字段列表 FROM 表名 ORDER BY 排序字段名 排序方式;

排序方式:可多个排序条件,当前面排序条件相同时才会根据后面排序条件微调
	ASC :升序排列(默认) 
	DESC:降序排序

(1)查询学生信息,按照数学成绩升序排列
	SELECT * FROM STU ORDER BY MATH ASC;
(2)查询学生信息,按照英语成绩降序排列
	SELECT * FROM STU ORDER BY ENGLISH DESC;
(3)查询学生信息,按照数学成绩升序排列,当数学成绩一样时,按照英语成绩降序排列
	SELECT * FROM STU ORDER BY MATH ASC,ENGLISH DESC ;

聚合查询

SELECT 聚合函数名(列名) FROM 表名;

聚合函数:NULL不参与
	(1)COUNT():统计数量
	(2)MAX  ():最大值
	(3)MIN  ():最小值
	(4)SUM  ():求和
	(5)AVG  ():平均值

(1)统计班级总共学生
	SELECT COUNT(ID) FROM STU;
(2)查询数学成绩最高分
	SELECT MAX(MATH) FROM STU;
(3)查询数学成绩最低分
	SELECT MIN(MATH) FROM STU;
(4)查询数学成绩总分
	SELECT SUM(MATH) FROM STU;
(5)查询数学成绩平均分
	SELECT AVG(MATH) FROM STU;

分组查询

SELECT 字段列表 FROM WHERE 分前限定条件 GROUP BY 分组字段名 HAVING 分后条件过滤;

WHERE :分组前进行条件限定,不满足条件则不参与分组;不支持聚合函数
HAVING:分组后进行条件限定;支持聚合函数(执行顺序:WHERE > 聚合函数 > HAVING)

(1)查询男同学和女同学各自的数学平均分
	SELECT SEX,AVG(MATH) FROM STU GROUP BY SEX;
(2)查询男同学和女同学各自的数学平均分以及各自的人数
	SELECT SEX,AVG(MATH),COUNT(*)FROM STU GROUP BY SEX;
(3)查询男同学和女同学各自的数学平均分以及各自的人数
   要求:分数低于70分的不参与分组
	SELECT SEX,AVG(MATH),COUNT(*)FROM STU WHERE MATH >= 70 GROUP BY SEX;
(4)查询男同学和女同学各自的数学平均分以及各自的人数
   要求:分数低于70分的不参与分组,分组后人数大于2个
	SELECT SEX,AVG(MATH),COUNT(*)FROM STU 
	WHERE MATH >= 70 GROUP BY SEX HAVING COUNT(*) > 2;

分页查询

SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询每页条目数

(1)起始索引 = (当前页码 - 1) * 每页显示条目数
(2)分页查询关键字:MySQL:LIMIT;Oracle:ROWNUMBER;SQL Server:TOP

(1)从零开始查询下,查询3条数据
	SELECT * FROM STU LIMIT 0,3;
(2)查询第一页数据,每页显示3条数据
	SELECT * FROM STU LIMIT 0,3;
(3)查询第二页数据,每页显示3条数据
	SELECT * FROM STU LIMIT 3,3;
(4)查询第三页数据,每页显示3条数据
	SELECT * FROM STU LIMIT 6,3;

多表查询

连接查询

SELECT 字段列表 FROM 表1,表2 WHERE 条件;        
#隐式内连接查询(查询两表之间的交集)
SELECT 字段列表 FROM 表1 INNER JOIN 表2 ON 条件;
#显示内连接查询(查询两表之间的交集)

SELECT 字段列表 FROM 表1 LEFT  OUTER JOIN 表2 ON 条件;
#左外连接查询(查询两表之间的交集加左表)
SELECT 字段列表 FROM 表1 RIGHT OUTER JOIN 表2 ON 条件; 
#右外连接查询(查询两表之间的交集加右表)

(1)查询EMP表和DEPT表中所有数据
	SELECT * FROM EMP,DEPT WHERE EMP.DEP_ID = DEMP.ID;
(2)查询EMP表和DEPT表中所有数据
	SELECT * FROM EMP INNER JOIN DEPT ON EMP.DEP_ID = DEMP.ID;
(3)查询EMP表和DEPT表中所有数据和EMP表独有数据
	SELECT * FROM EMP LEFT  OUTER DEPT ON EMP.DEP_ID = DEMP.ID;
(4)查询EMP表和DEPT表中所有数据和DEPT表独有数据
	SELECT * FROM EMP RIGHT OUTER DEPT ON EMP.DEP_ID = DEMP.ID;

嵌套查询

SELECT 字段列表 FROM 表 WHERE 字段名 =  (子查询);
#单行单列,子查询作为条件判断
SELECT 字段列表 FROM 表 WHERE 字段名 IN (字段名);
#多行单列,子查询作为条件判断
SELECT 字段列表 FROM (子查询) WHERE 条件;        
#多行多列,子查询作为虚拟查询表

(1)查询财务部所有员工信息
	1.SELECT STAFF_ID FROM DEPT WHERE DNAME = '财务部';
	2.SELECT * FROM EMP WHERE DEP_ID = '2';
	3.SELECT * FROM EMP WHERE 
	  DEP_ID = (SELECT STAFF_ID FROM DEPT WHERE DNAME = '财务部');
(2)查询财务部和市场部所有员工信息
	1.SELECT STAFF_ID FROM DEPT WHERE DNAME = '财务部' OR '市场部';
	2.SELECT * FROM EMP WHERE DEP_ID IN (2,3);
	3.SELECT * FROM EMP WHERE DEP_ID IN 
	  (SELECT STAFF_ID FROM DEPT WHERE DNAME = '财务部' OR '市场部');
(3)查询入职如期式'2022-11-11'之后入职的员工和部门信息
	1.SELECT * FROM EMP WHERE DATE = '2022-11-11';
	2.SELECT * FROM EMP,DEPT WHERE EMP_ID = DEP_ID;
	3.SELECT * FROM (SELECT * FROM EMP,DEPT WHERE EMP.DEPT_ID = DEP.ID;) 
	  WHERE DATE = '2022-11-11';

DCL 语句

用户管理

查询用户

USE mysql;         #用户信息存放在mysql库中的user表里,直接查看即可
SELECT * FROM USER;

创建用户

CREATE USER '用户名'@'访问位置' IDENTIFIED BY '密码';

(1)创建用户yingxin,只能在本机登录数据库访问,密码yingxin20000303
	CREATE USER 'yingxin'@'localhost' IDENTIFIED BY 'yingxin20000303';
(2)创建用户yingxin,只能在任意主机登录数据库访问,密码yingxin20000303
	CREATE USER 'yingxin'@'%' IDENTIFIED BY 'yingxin20000303';

修改用户密码

ALTER USER '用户名'@'主机名' 
IDENTIFIED WITH mysql_native_password BY '新密码';

(1)修改用户yingxin的密码
	ALTER USER 'yingxin'@'%' 
	IDENTIFIED WITH mysql_native_password BY 'YingXin0303';

删除用户

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

(1)删除用户yingxin
	DROP USER 'yingxin'@'%';

权限管理

权限分类

权限	说明
ALL	所有权限
INSERT	插入数据
DELETE	删除数据
UPDATE	修改数据
SELECT	查询数据
ALERT	修改表
DROP	删除数据库、表、视图
CREATE	创建数据库、表

查询权限

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

(1)查询用户yingxin的权限
	SHOW GRANTS FOR 'yingxin'@'localhost';

授予权限

GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名'; 
#'*'可以同配所有数据库和所有数据表

(1)授予用户yingxin对数据库learn中的study表的查询权限和插入权限
	GRANT SELECT,INSERT ON learn.study TO 'yingxin'@'localhost';

撤消权限

REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

(1)撤消用户yingxin对数据库learn中的study表的查询权限
	REVOKE INSERT ON learn.study FROM 'yingxin'@'localhost';

mysql 中常见系统函数

字符函数

函数	功能
CONCAT(S1,S2...Sn)	字符串拼接函数，将S1,S2...Sn拼接成一个字符串
LOWER(str)	小写函数，将字符串str全部转换为小写字母
UPPER(str)	大写函数，将字符串str全部转换为大写字母
LPAD(str,n,pad)	左填充函数，用字符串pad对str左边进行填充，使得str达到n个字符长度
RPAD(str,n,pad)	右填充函数，用字符串pad对str右边进行填充，使得str达到n个字符长度
TRIM(str)	去空函数，去掉字符串str头部和尾部的空格
SUBSTRING(str, start,len)	截取函数，返回字符串str从start位置起的len个长度的字符串

数值函数

函数	功能
CLEIL(x)	取整函数，向上取整
FLOOR(x)	取整函数，向下取整
MOD(x,y)	取模函数，x，y的模
RAND()	随机函数，0-1随机数
ROUND(x,y)	舍弃函数，x的四舍五入值，保留y位小数

日期函数

函数	功能
CURDATE()	日期函数，返回当前日期
CURTIME()	时间函数，返回当前时间
NOW()	日期时间函数，返回当前日期和时间
YEAR(date)	截取年份函数，获取指定日期的年份
MONTH(date)	截取月份函数，获取指定日期的月份
DAY(date)	截取日份函数，获取指定日期的天份
DATE_ADD(date,INTERVAL expo type)	累加函数，返回在一个时间或日期后加上expr间隔后的时间值
DATEDIFF(date1,date2)	差值函数，返回起始日期data1和结束日期data2之间的天数

流程函数

函数	功能
IF(value,t,f)	如果value为true，则返回t，否则返回f
IFUNLL(value1,value2)	如果value1不为空，返回value1，否则返回value2
CASE WHEN [val1] THEN [res1] ... ELSE[default] EDN	如果val1为true，返回res1,...否则返回defaul默认值
CASE [expr] WHEN [val1] THEN [res1] ... ELSE [default] END	如果expr的值等于val1，返回res1，...否则返回defaul默认值

posted @ 2022-08-31 22:05 (应鑫) 阅读(32) 评论(0) 收藏举报

刷新页面返回顶部

应鑫

Mysql-基础

mysql 数据库管理系统

数据库出厂设置

数据库管理命令

mysql 结构化查询语言

sql 通用规则

sql 语句分类

sql 具体语句

DDL 语句

数据库操作

数据表操作

事务的操作

DML 语句

DQL 语句

单表查询

多表查询

DCL 语句

用户管理

权限管理

mysql 中常见系统函数

字符函数

数值函数

日期函数

流程函数

公告