【MySQL学习笔记】1.数据库操作

MySQL数据库操作

1.MySQL服务启动：

1. 手动；
2. cmd->services.msc 打开服务的窗口；
3. 使用管理员打开cmd：net start/stop mysql打开/关闭mysql的服务

2.MySQL的登录登出

登录：

1. mysql -uroot -p密码
2. mysql -hip -uroot -p链接目标的密码
3. mysql、
   –host=ip --user=root --password=连接目标的密码

登出：

• quit/exit

3.MySQL目录结构

1. MySQL安装目录C:/Program Files/MySQL/MySQL Server 8.0/
   配置文件：my.ini
2. MySQL数据目录C:/ProgramData/MySQL/MySQL Server 8.0/Data

几个概念：数据库（文件夹）；表（文件）；数据

4.SQL:Structured Query Language(结构化查询语言）

定义了操作所有关系型数据库的规则

SQL语句不区分大小写，关键字建议使用大写

3种注释：

• 单行注释：-- 注释内容 或 #注释内容（MySQL特有）
• 多行注释：/* 注释内容 */

5.SOL分类：

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…

6.DDL:操作数据库、表

6.1 操作数据库：CRUD

• CREATE

CREATE DATABASE 数据库名;
CREATE DATABASE IF NOT EXISTS 数据库名;
CREATE DATABASE 数据库名 CHARACTER SET 字符集名;
CREATE DATABASE IF NOT EXISTS db1 CHARACTER SET UTF8;

• RETRIEVE

SHOW DATABASES-- 查询所有数据库名;
SHOW CREATE DATABASE 数据库名;

• UPDATE

ALTER DATABASE 数据库名 CHARACTER SET 字符集名;

• DELETE

DROP DATABASE 数据库名;
DROP DATABASE IF EXISTS 数据库名;

• 使用数据库

SELECT   DATABASE();-- 查询当前正在使用的数据库名
USE 数据库名;-- 使用数据库

6.2 操作表

• CREATE

CREATE TABLE 表名(CREATE TABLE STUDENT(
                列名1 数据类型1，           id int,
                列名2 数据类型2，           score double(4,1),
                ...                        birthday date,
                列名n 数据类型n             insert_time timestamp
									  );
				);

• 数据类型

age int,
		score double(5,2)-- 五位数字，小数点后两位
date：日期，yyyy-MM-dd
datetime:日期，yyyy-MM-dd HH:mm:ss
timestamp:时间戳类型，yyyy-MM-dd HH:mm:ss
          若不赋值或赋值为null，则默认使用当前系统时间自动赋值
varchar:字符串
        name varchar(20)-- 最大20字符 
CREATE TABLE 表名 like 被复制的表名；-- 复制表   

• RETRIEVE

SHOW TABLES;-- 查询某数据库中所有表名
desc 表名;-- 查询表结构

• UPDATE

ALTER TABLE 表名 rename 新表名；
ALTER TABLE 表名 CHARACTER SET 字符集名；
ALTER TABLE 表名 ADD 列名 数据类型；
ALTER TABLE 表名 CHANGE 列名 新列别 新数据类型；
ALTER TABLE 表名 MODIFY 列名 新数据类型；-- 修改列名、类型
ALTER TABLE 表名 DROP 列名；

• DELETE

DROP TABLE 表名；
DROP TABLE IF EXISTS 表名；

7.DML：增删改表中数据

1. 添加数据：
   INSERT INTO 表名(列名1，列名2，…列名n) VALUES(指1,值2，…值n)；
   除了数字类型，其他类型数据需使用单或双引号引起来
2. 删除数据：
   DELETE FROM 表名 [WHERE 条件];-- 不加条件删除所有记录
   TRUNCATE TABLE 表名;-- 先删除表再创建一个一样的表(效率高)
3. 修改数据
   UPDATE 表名 SET 列名1=值1，列名2=值2,…[WHERE 条件]；
   /*
   MySQL运行在safe-updates模式非主键条件下无法执行update或delete命令
   执行命令SET SQL_SAFE_UPDATES = 0;修改数据库模式
   若要提高数据库安全等级可恢复回原有设置
   执行命令SET SQL_SAFE_UPDATES = 1;
   */

8.DQL：查询表中数据

1. 语法

SELECT
    字段列表
FROM 
    表名列表
WHERE 
    条件列表
group by 
    字段列表
having
    分组之后的条件
order by 
    排序
limit
    分页限制

2. 基础查询

1. 多字段查询
	SELECT * FROM 表名；-- 查询所有字段
	SELECT 字段1，字段2，... FROM 表名
2. 去除重复：distinct
3. 计算列:可进行四则运算            
4. 起别名：as

3. 条件查询

1. where后加条件
2. 运算符
        like：模糊查询
            占位符：_单个任意字符 %多个任意字符

4. 排序查询

order by 排序字段1 排序方式1，排序字段2 排序方式2...
排序方式：ASC默认升序，DESC降序

5. 聚合函数：将一列数据作为一个整体，进行纵向计算

 count max min sum avg

6. 分组查询

group by 分组字段；
分组之后查询的字段：分组字段、聚合函数
where和having区别：
    1.where在分组前限定，不满足条件不参与分组。
      having在分组后限定，不满足结果不会被查询出来。
    2.where后不可跟聚合函数，having可进行聚合函数的判断

7. 分页查询

语法：limit 开始的索引，每页查询的条数；
公式：开始的索引 = (当前页码 - 1) * 每页显示条数

实例

USE db1;
INSERT INTO student VALUES(9,'张三',18,'男',NULL,NULL,NULL);
SET SQL_SAFE_UPDATES = 0;
DELETE FROM student WHERE id = 9;
UPDATE student SET age = 99 WHERE id = 9;
SELECT * FROM student;

CREATE TABLE student(
	id INT,
    NAME VARCHAR(20),
    age INT,
    sex VARCHAR(10),
    adress VARCHAR(10),
    math INT,
    english INT
);
INSERT INTO student VALUES('1','马云','55','男', '杭州', '66', '78'),
						  ('2', '马化腾', '45', '女', '深圳', '98', '87'),
						  ('3', '马景涛', '55', '男', '香港', '56', '77'),
						  ('4', '柳岩', '20', '女', '湖南', '76', '65'),
						  ('5', '柳青', '20', '女', '湖南', '86', NULL),
						  ('6', '刘德华', '57', '男', '香港', '99', '99'),
						  ('7', '马德', '22', '女', '香港', '99', '99'),
                          ('8', '德玛西亚', '18', '男', '南京', '56', '65');
SELECT * FROM student;-- 查询表的所有内容
-- 查询姓名和年龄
SELECT 
	NAME,-- 姓名
    age-- 年龄
FROM
	student;-- 学生表 

-- 去除重复的结果集
SELECT DISTINCT adress FROM student;
 
-- 计算math和english分数之和
SELECT math, english, math + english FROM student;-- 如果有null参与运算，计算结果都为null
SELECT math, english, math + IFNULL(english, 0) FROM student;

-- 起别名 
SELECT math, english, math + IFNULL(english, 0) AS 总分 FROM student;
SELECT math, english, math + IFNULL(english, 0) 总分 FROM student;

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

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;

SELECT * FROM student WHERE age = 22 OR age = 25 OR age = 18;
SELECT * FROM student WHERE age IN (22,18,25); 

SELECT * FROM student WHERE english IS NULL;
SELECT * FROM student WHERE english IS NOT NULL;

-- 查询姓马的
SELECT * FROM student WHERE NAME LIKE '马%';
-- 查询姓名第二个字是化的 
SELECT * FROM student WHERE NAME LIKE '_化%';
-- 查询姓名是三个字的 
SELECT * FROM student WHERE NAME LIKE '___';
-- 查询姓名中包含德的 
SELECT * FROM student WHERE NAME LIKE '%德%';

-- 按数学成绩升序排名，若排名相同则按英语成绩降序排名 
SELECT * FROM student ORDER BY math ASC, english DESC; 

SELECT COUNT(*) FROM student;
SELECT COUNT(IFNULL(english,0)) FROM student;
SELECT MAX(math) FROM student;
SELECT SUM(math) FROM student;

-- 按照性别分组分别查询男女生的平均分、人数，要求数学分数低于70分的人不参与分组，分组后人数要大于两个 
SELECT sex, AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex HAVING COUNT(id) > 2;

-- 每页显示三条数据 
SELECT * FROM student LIMIT 0,3;-- 第一页 
SELECT * FROM student LIMIT 3,3;-- 第二页 
SELECT * FROM student LIMIT 6,3;-- 第三页
-- 公式：开始的索引 = （当前页码 - 1) * 每页显示的条数