mysql 学习笔记

一、数据库命令行操作

1. 登录
   mysql -h ip地址 -u 用户名 -p 密码
   2.修改密码
   alter user 'root'@'localhost' identified by '密码';
   3.退出
   exit
   4.开启mysql服务
   net start mysql
   5.关闭mysql服务
   net stop mysql

二、数据库客户端工具

1. WorkBench

2. Navicat

三、数据库命令

1.连接mysql

• 新建连接

  • name：自定义名字
  • method：tcp/ip
  • hostname: sql服务地址，如果是本地就是127.0.0.1
  • port：端口，默认3306

• 创建用户

• CREATE USER 'username'@'地址' IDENTIFIED BY 'password'

• 给用户权限

• grant all privileges on *.* to 'username'@'%'
  % 代表任意地址

2.新建数据库

• CREATE {DATABASE|SCHEMA} [IF NOT EXISTS] 数据库名 CHARACTER SET [] 字符集

3.查看数据库

• 选择数据库
  USE 数据库名
• 显示创建的所有数据库
  SHOW DATABASES
• 查看数据库的定义信息
  SHOW CREATE DATABASE 数据库名
• 查看数据库data存储路径
  SHOW variables LIKE '%datadir%';

4.修改数据库

• 修改数据库
  ALTER {DATABASE} [数据库名] CHARACTER SET [=] 字符集 --不写数据库名默认修改当前数据库

5.删除数据库

• 删除已存在的数据库
  DROP DATABASE [IF EXISTS] 数据库名

6.操作数据库表

• 创建表
  CREATE TABLE student(id INT,name VARCHAR(10))

• 创建表带备注
  CREATE TABLE student (id INT,name VARCHAR(10) COMMENT '学生名字')

• 复制表
  CREATE TABLE 数据表名 {LIKE 源数据表名 | (LIKE 源数据表名)}
CREATE TABLE teacher LIKE student;

• 查看表
  SHOW TABLES;

• 查看表结构
  DESCRIBE 表名; -- 简写DESC
DESCRIBE 表名 列名;

• 查看表完整信息，包括备注
  SHOW FULL COLUMNS FROM student;

• 删除表
  DROP TABLE [IF EXISTS] 表名

• 修改表名
  ALTER TABLE 原表名 RENAME 新表名;
RENAME TABLE 原表名 TO 新表名;

• 添加列
  ALTER TABLE 表名 ADD 列名 列属性
ALTER TABLE studen ADD emial varchar(50) NOT NULL
ALTER TABLE student ADD (email VARCHAR(10),age INT);

• 修改列属性
  ALTER TABLE 表名 MODIFY 列名 列属性
ALTER TABLE student MODIFY email VARCHAR(5) NOT NULL;

• 修改列名
  ALTER TABLE 表名 CHANGE 原列名 新列名 列属性
ALTER TABLE student CHANGE COLUMN iphone new_iphone VARCHAR(10) DEFAULT '123';

• 删除列
  ALTER TABLE 表名 DROP 列名
ALTER TABLE student DROP new_iphone;

• 设置主键列
  ALTER TABLE student ADD PRIMARY KEY(id);

7.表数据操作

• 插入数据
  INSERT INTO 表名 (列名1，列名2) VALUES(值1，值2) -- 值1写入列1，值2写入列2，如果没有写列名，按表顺序插入数据

CREATE TABLE user(
id INT,
name VARCHAR(10),
age INT,
sex CHAR(1),
address VARCHAR(50)
);
INSERT INTO user (id,name,sex) VALUES(01,'XXX','女');

完整添加所有列数据
INSERT INTO user VALUES(02,'xx1',12,'男','地址地址');
一次添加多条数据
INSERT INTO user (id,name,age) VALUES(03,'XX3',13),(04,'xx4',14);

• 修改表数据
  UPDATE 表名 SET 列名=值 [WHERE 条件表达式]
INSERT INTO student VALUES (01,'赵','111@qq.com',12), (2,'钱','22@qq.com',13), (3,'孙','33@qq.com',14);
ALTER TABLE student ADD PRIMARY KEY(id); -- 必须有主键不然报错
UPDATE student SET email='xxx@qq.com' WHERE id=2;
UPDATE student SET email='xxx11@qq.com',age=33 WHERE id=1;
• 删除表数据
  DELETE FROM 表名 WHERE 条件表达式
DELETE FROM student WHERE id = 3;
• 删除表中所有数据
  TRUNCATE TABLE 表名

7.查询数据库表

• 单表查询
  SELECT * FROM 表名;
• 字段查询
  SELECT dept_name FROM departments;
• 表 别名
  SELECT dept_name FROM departments dd;
• 字段别名
  SELECT dept_name AS 'name' FROM departments dd;
• 去掉重复数据
  SELECT DISTINCT 列名 FROM 表名
SELECT DISTINCT title FROM employees.titles;
• 条件查询
  SELECT * FROM 表名 WHERE 条件表达式
• 取特定位置的数据
  SELECT * FROM student ORDER BY age LIMIT 5; -- 取前5条数据
SELECT * FROM student ORDER BY age LIMIT 2,1; -- 取第3条数据（从0开始数）
SELECT * FROM student ORDER BY age LIMIT 2,5; -- 取第3条数据开始的5条数据

8.运算

• 查询运算
  SELECT (列名 运算表达式) FROM 表名;
SELECT emp_no,salary+1000 FROM salaries;

9.比较运算符

• 大于、小于……
  > < <= >= = <> !=
SELECT emp_no,salary FROM salaries WHERE salary=61117;
• 范围限定
  BETWEEN ... AND ...
• 子集限定
  IN
• 模糊查询
  LIKE '%or%'
• 空查询
  IS NULL

10.排序

• ORDER BY 排序命令
• DESC 降序
• ASC 升序（默认）

SELECT * FROM student
ORDER BY age DESC;

• 组合排序
  • 先以第一字段进行排序，第一字段相同就按照第二字段排序

-- 先对age进行降序排序，再按score进行排序
SELECT * FROM student ORDER BY age DESC,score;

11.聚合函数

SELECT 聚合函数(列) FROM 表名

• COUNT() ：统计统计列不为NULL的记录行数
  SELECT COUNT(score) FROM student
• MAX() ：计算指定列的最大值
  SELECT MAX(score) FROM student
• MIN() ：指定列的最小值
  SELECT MIN(score) FROM student
• SUM() : 指定列的数值和
  SELECT SUM(age) FROM student WHERE sex='女'
• AVG() : 指定列的平均值
  SELECT AVG(score) FROM student WHERE sex='女'

12.分组查询

SELECT 分组列/聚合函数 FROM 表名 GROUP BY 列名 HAVING 条件

1. SELECT emo_no,sum(salary) FROM salaries GROUP BY emo_no

• 先对相同的emo_no进行分组
• 再对每个分组的salary进行sum()操作

2. 查询员工编号小于10010的，薪资和小于400000的员工薪资和

SELECT 
   emp_no, SUM(salary)
FROM
   salaries
WHERE
   emp_no < 10010
GROUP BY emp_no
HAVING SUM(salary) < 400000;

• WHERE 从源数据去筛选
• GROUP BY 对数据进行分组，再利用聚合函数对分组进行计算
• HAVING子句 分完组之后再进行一次筛选

14.limit

限制查询结果的数量，默认从0开始计数

SELECT * FROM student LIMIT 3; --展示前3条数据
SELECT * FROM employees LIMIT 3，100； -- 展示第3条到103条数据
SELECT *FROM employees LIMIT 50 OFFSET 3; -- 展示第3条到第53条数据

mysql语句执行顺序

1、原始数据库 FROM 产生一个虚拟表1
2、虚拟表1 WHERE 语句 产生虚拟表2
3、虚拟表2 GROUP BY 语句 产生虚拟表3
4、虚拟表3 HAVING子句 产生虚拟表4
5、虚拟表4 SELECT 语句 产生虚拟表5
6、虚拟表5 DISTINCT 语句 产生虚拟表6
7、虚拟表6 ORDER BY语句 产生虚拟表7
8、虚拟表7 LIMIT 语句 产生最终结果

SQL 约束

• 对表中数据进行进一步的限制，保证数据的正确性、有效性、完整性

1. 主键约束 PRIMARY KEY

• 主键：唯一的标识表中的每一行
• 特点：不可重复，唯一，非空
• 创建主键的方式
  1. 创建表时设置主键

 --列名 字段类型 PRIMARY KEY
CREATE TABLE leaders (
   leader_id INT PRIMARY KEY,
   name VARCHAR(10),
   sex CHAR(1)
);
      ```

  2. 已有的表创建主键
     ```
     -- ALTER TABLE 表名 ADD PRIMARY KEY(列名)
     ALTER TABLE teacher ADD PRIMARY KEY(id);

3. 设置主键自动增加

CREATE TABLE new_table (
   id INT PRIMARY KEY AUTO_INCREMENT,
   name CHAR(10),
   sex CHAR(1)
);
INSERT INTO new_table VALUE (1,'XX','女');
INSERT INTO new_table(name,sex) VALUE ('XXY','女');

CREATE TABLE new_table (
   id INT PRIMARY KEY AUTO_INCREMENT,
   name CHAR(10),
   sex CHAR(1)
)AUTO_INCREMENT=100;  --主键自增的起始值为100

• 采用自增的方式添加主键，使用DELECT 删除表中的数据，再次添加会继续上一次的主键值继续自增，使用TRUNCATE 删除表后新增数据会重新创建新表

2. 非空约束 NOT NULL

- 特点：某一列不许为空
- `列名 字段类型 NOT NULL`
    `CREATE TABLE student (uid INT NOT NULL)`

3. 唯一约束 UNIQUE

- 特点：某一列值不能重复
- 对NULL 不做唯一的判断（可以有多个NULL）
- `列名 字段类型 UNIQUE`

CREATE TABLE student (uid INT UNIQUE)
- 唯一约束和主键约束的区别：
1. 主键约束唯一且不能为空，唯一约束唯一但是可以为空
2.一个表内只能有一个主键，但是可以有多个唯一约束

默认值 DEFAULT

CREATE TABLE student (uid INT PRIMARY KEY AUTO_INCREMENT, sex CHAR(1) DEFAULT '女')

多表结构

• 特点：简化数据、提高复用性、方便权限控制、提高系统的稳定性和负载能力

1.外键约束

• 定义：
  1.外键：从表中与主表的主键对应的字段
  2. 主表：外键所指向的表，约束其他表的表

• 价值：建立主表与从表的关联关系，约束两个表中数据的一致性和完整性

• 创建外键约束：

  1. 创建表时创建外键约束
     CONSTRAINT [外键约束的名称] FOREIGN KEY (外键字段) REFERENCES [主表名称(主键字段)]

  2. 插入外键约束
     ALTER TABLE [表名] ADD CONSTRAINT [外键约束的名称] FOREIGN KEY [外键字段] REFERENCES [主表名称(主键字段)]

  • 创建外键，先创建主表数据再创建从表数据

• 删除外键约束
  ALTER TABLE [表名] DROP FOREIGN KEY [外键约束名称]

  • 删除外键约束需要下删除从表数据再删除主表数据

2.外键查询

3、多表查询

1. 内连接 INNER JOIN

   • 隐式内连接 SELECT * FROM 表1，表2 WHERE [条件]（没有写出 INNER JOIN。通过过滤条件筛选出符合条件的两个表相等的数据）
   • 显式内连接 SELECT * FROM 表1 INNER JOIN 表2 ON 条件
   • 隐式内连接和显式内连接作用是相同的

2. 外连接

   • 左外连接 LEFT JOIN：左表显示所有数据，根据匹配条件，如果右表没有，显示为NULL
     SELECT * FROM dept LEFT JOIN emp_part ON dept.id=emp_part.id

   • 右外连接 RIGHT JOIN

   • 与内连接的差别：
     不仅返回符合匹配条件的行，也会返回左表（左连接）或右表（右连接）的所有数据行

4、 子查询

• 定义：一个查询语句嵌套再另一个查询语句内部，在SELECT 子句中先计算子查询，子查询的结果作为外层另一个查询的过滤条件，MYSQL 4.1开始引入。
• FROM 型子查询：将子查询的结果作为父查询的表来使用

-- 计算各部门性别为男性的员工人数
SELECT
COUNT(em.emp_no), dept_no
FROM
(SELECT
*
FROM
employees
WHERE
gender = 'F') em
INNER JOIN
dept_emp ON em.emp_no = dept_emp.emp_no
GROUP BY dept_no;

   - IN/ NOT IN 型子查询：子查询的结果是单列多行，作为WHERE的过滤条件
   - WHERE 型子查询：查询结果（单行单列）作为过滤条件出现在比较运算符的一端