mysql数据库

最新推荐文章于 2023-06-29 22:20:57 发布

空城1995

最新推荐文章于 2023-06-29 22:20:57 发布

阅读量410

点赞数

分类专栏：数据库文章标签： mysql 数据库 SQL语句 DML DQL

本文链接：https://blog.youkuaiyun.com/fy_java1995/article/details/80272039

版权

数据库专栏收录该内容

22 篇文章

订阅专栏

一、数据库

1.数据库概述

（1）什么是数据库

数据库就是用来存储和管理数据的仓库！

数据库存储数据的优点：

可存储大量数据；

方便检索；

保持数据的一致性、完整性；

安全，可共享；

通过组合分析，可产生新数据。

（2）常见数据库

Oracle：甲骨文；

DB2：IBM；

SQL Server：微软；

Sybase：赛尔斯；

MySQL：甲骨文；

（3）数据库的理解

RDBMS = 管理员（manager）+仓库（database）

database = N个table

table：

表结构：定义表的列名和列类型！

表记录：一行一行的记录！

我们现在所说的数据库泛指“关系型数据库管理系统（RDBMS - Relational database management system[关系型数据库管理系统]）”，即“数据库服务器”。

（4）应用程序与数据库

应用程序使用数据库完成对数据的存储！

2.MySQL数据库

（1）启动和关闭mysql服务器

启动：net start mysql；

关闭：net stop mysql；

（2）客户端登陆退出mysql

在启动MySQL服务器后，我们需要使用管理员用户登录MySQL服务器，然后来对服务器进行操作。登录MySQL需要使用MySQL的客户端程序：mysql.exe

-u：后面的root是用户名，这里使用的是超级管理员root；

-p：后面的123是密码，这是在安装MySQL时就已经指定的密码；

-h：后面给出的localhost是服务器主机名，它是可以省略的，例如：mysql -u root -p 123

退出：quit或exit；

在登录成功后，打开windows任务管理器，会有一个名为mysql.exe的进程运行，所以mysql.exe是客户端程序。

二、SQL语句

1.SQL概述

（1）什么是SQL

SQL（Structured Query Language）是“结构化查询语言”，它是对关系型数据库的操作语言。它可以应用到所有关系型数据库中，例如：MySQL、Oracle、SQL Server等。SQ标准（ANSI/ISO）有：

SQL-92：1992年发布的SQL语言标准；

SQL:1999：1999年发布的SQL语言标签；

SQL:2003：2003年发布的SQL语言标签；

这些标准就与JDK的版本一样，在新的版本中总要有一些语法的变化。不同时期的数据库对不同标准做了实现。

虽然SQL可以用在所有关系型数据库中，但很多数据库还都有标准之后的一些语法，我们可以称之为“方言”。例如MySQL中的LIMIT语句就是MySQL独有的方言，其它数据库都不支持！当然，Oracle或SQL Server都有自己的方言。

（2）语法要求

SQL语句可以单行或多行书写，以分号结尾；

可以用空格和缩进来来增强语句的可读性；

关键字不区别大小写，建议使用大写；

2.分类

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

DML（Data Manipulation Language）：数据操作语言，用来定义数据库记录（数据）；

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

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

3.DDL

（1）基本操作

查看所有数据库名称：SHOW DATABASES；　

切换数据库：USE mydb1，切换到mydb1数据库；

（2）操作数据库

创建数据库：CREATE DATABASE [IF NOT EXISTS] mydb1；

创建数据库，例如：CREATE DATABASE mydb1，创建一个名为mydb1的数据库。如果这个数据已经存在，那么会报错。例如CREATE DATABASE IF NOT EXISTS mydb1，在名为mydb1的数据库不存在时创建该库，这样可以避免报错。

删除数据库：DROP DATABASE [IF EXISTS] mydb1；

删除数据库，例如：DROP DATABASE mydb1，删除名为mydb1的数据库。如果这个数据库不存在，那么会报错。DROP DATABASE IF EXISTS mydb1，就算mydb1不存在，也不会的报错。

修改数据库编码：ALTER DATABASE mydb1 CHARACTER SET utf8

修改数据库mydb1的编码为utf8。注意，在MySQL中所有的UTF-8编码都不能使用中间的“-”，即UTF-8要书写为UTF8。

注意：[ ]中的内容为可选项，可以写也可以不写，写的话把[ ]去掉。

（3）数据类型

常用类型：

int：整型

double：浮点型，例如double(5,2)表示最多5位，其中必须有2位小数，即最大值为999.99；

decimal：泛型型，在表单钱方面使用该类型，因为不会出现精度缺失问题；

char：固定长度字符串类型；

varchar：可变长度字符串类型；

text：字符串类型；

blob：字节类型；

date：日期类型，格式为：yyyy-MM-dd；

time：时间类型，格式为：hh:mm:ss

timestamp：时间戳类型；

（4）操作表

创建表：

CREATE TABLE 表名(

列名列类型,

......

);

例如：

CREATE TABLE emp(
	eid		CHAR(6),
	ename	VARCHAR(50),
	age		INT,
	gender	VARCHAR(6),
	birthday	DATE,
	hiredate	DATE,
	salary	DECIMAL(7,2),
	resume	VARCHAR(1000)
);

查看当前数据库中所有表名称：SHOW TABLES；　

查看指定表的创建语句：SHOW CREATE TABLE emp，查看emp表的创建语句；

查看表结构：DESC emp，查看emp表结构；

删除表：DROP TABLE emp，删除emp表；

修改表

1.修改之添加列：给stu表添加classname列：

ALTER TABLE stu ADD (classname varchar(100));

2.修改之修改列类型：修改stu表的gender列类型为CHAR(2)：

ALTER TABLE stu MODIFY gender CHAR(2);

3.修改之修改列名：修改stu表的gender列名为sex：

ALTER TABLE stu change gender sex CHAR(2);

4.修改之删除列：删除stu表的classname列：

ALTER TABLE stu DROP classname;

5.修改之修改表名称：修改stu表名称为student：

ALTER TABLE stu RENAME TO student;

4.DML

（1）插入数据

语法：INSERT INTO 表名(列名1,列名2, …) VALUES(值1, 值2)

INSERT INTO stu(sid, sname,age,gender) VALUES('s_1001', 'zhangSan', 23, 'male');

INSERT INTO stu(id, name) VALUES('s_1001', 'zhangSan');

第二条语句没有给age和gender赋值，则该stu的age和gender属性为null

语法：INSERT INTO 表名 VALUES(值1,值2,…)

因为没有指定要插入的列，表示按创建表时列的顺序插入所有列的值：

INSERT INTO stu VALUES('s_1002', 'liSi', 32, 'female');

（2）修改数据

语法：UPDATE 表名 SET 列名1=值1, … 列名n=值n [WHERE 条件]

UPDATE stu SET name=’zhangSanSan’, age=’32’, gender=’female’ WHERE id=’s_1001’;
UPDATE stu SET name=’liSi’, age=’20’ WHERE age>50 AND gender=’male’;
UPDATE stu SET name=’wangWu’, age=’30’ WHERE age>60 OR gender=’female’;
UPDATE stu SET gender=’female’ WHERE gender IS NULL
UPDATE stu SET age=age+1 WHERE name=’zhaoLiu’;

注意：所有字符串数据必须使用单引用！

（3）删除数据

语法：DELETE FROM 表名 [WHERE 条件]

DELETE FROM stu WHERE sid=’s_1001’003B
DELETE FROM stu WHERE sname=’chenQi’ OR age > 30;
DELETE FROM stu;

语法：TRUNCATE TABLE 表名

TRUNCATE TABLE stu;

删除stu表，再创建stu表。等同与删除stu表所有记录。

虽然TRUNCATE和DELETE都可以删除表的所有记录，但有原理不同。DELETE的效率没有TRUNCATE高！

TRUNCATE其实属于DDL语句，因为它是先DROP TABLE，再CREATE TABLE。而且TRUNCATE删除的记录是无法回滚的，但DELETE删除的记录是可以回滚的（回滚是事务的知识！）。

5.DCL

（1）创建用户

语法：CREATE USER 用户名@地址 IDENTIFIED BY '密码';

user1用户只能在localhost这个IP登录mysql服务器

CREATE USER user1@localhost IDENTIFIED BY ‘123’;

user2用户可以在任何电脑上登录mysql服务器

CREATE USER user2@’%’ IDENTIFIED BY ‘123’;

（2）给用户授权

语法：GRANT 权限1, … , 权限n ON 数据库.* TO 用户名

给user1用户操作mydbc数据库的create、alter等权限

GRANT CREATE,ALTER,DROP,INSERT,UPDATE,DELETE,SELECT ON mydbc.* TO user1@localhost;

给user2用户操作mydbc数据库的所有权限

GRANT ALL ON mydbc.* TO user2@localhost;

（3）撤销授权

语法：REVOKE权限1, … , 权限n ON 数据库.* FORM 用户名

REVOKE CREATE,ALTER,DROP ON mydb1.* FROM user1@localhost;

（4）查看用户权限

语法：SHOW GRANTS FOR 用户名

SHOW GRANTS FOR user1@localhost;

（5）删除用户

语法：DROP USER 用户名

DROP USER user1@localhost;

（6）修改用户密码

语法：USE mysql;

UPDATE USER SET PASSWORD=PASSWORD(‘密码’)

WHERE User=’用户名’ and Host=’IP’;

FLUSH PRIVILEGES;

UPDATE USER SET PASSWORD=PASSWORD('1234') WHERE User='user2' and Host=’localhost’; FLUSH PRIVILEGES;

6.DQL

语法：

SELECT selection_list /*要查询的列名称*/

FROM table_list /*要查询的表名称*/

WHERE condition /*行条件*

GROUP BY grouping_columns /*对结果分组*/

HAVING condition /*分组后的行条件*/

ORDER BY sorting_columns /*对结果排序*/

LIMIT offset_start, row_count /*结果限定*/

（1）基础查询

查询所有列：SELECT * FROM stu;

查询指定列：SELECT sid, sname, age FROM stu;

（2）条件查询

1.条件查询介绍

条件查询就是在查询时给出WHERE子句，在WHERE子句中可以使用如下运算符及关键字：

=、!=、<>、<、<=、>、>=；

BETWEEN…AND；

IN(set)；

IS NULL；

AND；

OR；

NOT；

查询性别为女，并且年龄为20的记录

SELECT * FROM stu WHERE gender='female' AND age<20;

查询学号为001，或者姓名为ZhangSan的记录

SELECT * FROM stu WHERE sid ='001' OR sname='ZhangSan';

查询学号为001，002，003的记录

SELECT * FROM stu WHERE sid IN ('1001','1002','1003');

查询学号不是001，002，003的记录

SELECT * FROM stu WHERE sid NOT IN ('1001','1002','1003');

查询年龄为NULL的记录

SELECT * FROM stu WHERE age IS NULL;

查询年龄在18到25之间的记录

SELECT * FROM stu WHERE age>=18 AND age<=25;

SELECT * FROM stu WHERE age BETWEEN 18 AND 25;

查询性别非男的记录

SELECT * FROM stu WHERE gender!='male';

SELECT * FROM stu WHERE gender<>'male';

SELECT * FROM stu WHERE NOT gender='male';

（3）模糊查询

关键字：like

查询姓名由三个字组成，并且最后一个字为宇的学生记录

SELECT * FROM stu WHERE sname LIKE '__宇';

"_”匹配任意一个字符，2个“_”表示2个任意字符。

查询姓名中包含“梅”的记录

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

其中“%”匹配0~n个任何字符。

查询姓名中第二个字为"“欢”的记录

SELECT * FROM stu WHERE sname LIKE '_欢%';

（4）字段控制查询

1.去除重复记录

关键字：distinct

查询不重复的薪水记录

SELECT DISTINCT sal FROM emp;

2.列数数值相加

查看雇员的月薪和佣金总和

SELECT *,sal+comm FROM emp;

因为sal和comm两列的类型都是数值类型，所以可以做加运算。如果sal或comm中有一个字段不是数值类型，那么会出错。

如果comm列中有null值，任何数和null相加都为null。相加就应该先把null转化为0，使用ifnull函数

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

3.给列名添加别名

关键字：AS（可以省略）

将sname改为姓名

SELECT sname as '姓名' FROM emp;

（5）排序

关键字：order by xxx asc; 按照xxx升序排列（asc可以不写，默认为升序）

order by xxx desc; 按照xxx降序排列

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

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

（6）聚合函数

COUNT()：统计指定列不为NULL的记录行数；

MAX()：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；

MIN()：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；

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

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

查询emp表中月薪大于3000的人数

SELECT COUNT(*) FROM emp WHERE sal > 2500;

查询emp表中月薪的总和

SELECT SUM(sal) FROM emp;

（7）分组查询

关键字：group by

having

查询工资总和大于30000的部门以及工资和

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

（8）LIMIT(mysql数据库独有)

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

查询5行记录，从第一行开始查询

SELECT * FROM emp LIMIT 0, 5;

查询10行记录，从第3行开始查询

SELECT * FROM emp LIMIT 2, 10;

分页查询：每页有10行数据，查询第3页

SELECT * FROM emp LIMIT 20, 10;

起始行：(当前页-1) * 每页记录数