第十一天sql基础

1. MySql命令

1.1
开启服务 net start mysql
关闭服务 net stop mysql
1.2
登陆在bin目录下 ：mysql -uroot -proot
退出 ：exit、quit
远程连接 ：mysql -hIP地址 -uroot -proot

2. MySql语法

1.注释

杆杆空格 （ – 注释 ）或者（ # 注释 ），多行注释 /* 注释 */

2.MySql语法分类

1.DDL（操作数据库、表）

CRUD操作
1.1 C（Create）
//创建数据库
create database db1;
//如果不存在再创建
create database db1 if not exists db1;
//指定字符集
create database db1 character set GBK;
//连接到一起
create database if not exists db1 character set GBK;
1.2 R（Retrieve）
//查询所有数据库
show databases;
//查询数据库（mysql）的创建语句，主要是看编码。
show create database mysql;
1.3 U（Update）
//修改数据库字符集
alter database db1 character set utf8;
1.4 D（Delete）
//删除数据库
drop database db1;
//如果存在删除
drop database if exists db1;
1.5使用数据库
//查询正在使用的数据库
select database();
//使用数据库
use db1;

2.DDL（操作数据库中的表）

1.1 C（Create）
//创建一个学生表
create table student(
id int(11),
name varchar(32),
age int(11),
score double(4,1),
birthday date,
insert_time timestamp
);
//复制表
create table 表名 like student;
1.2 R（Retrieve）
//查询表名称
show tables;
//查询表结构
desc 表名;
1.3 U（Update）
//改表名
alter table 表名 rename to 新表名；
//改字符集
alter table 表名 character set 字符集;
//添加列
alter table 表名 add 列名 数据类型;
//既改列名，也改类型
alter table 表名 change 列名 新列名 新数据类型;
alter table 表名 modify 列名 新数据类型；
//删除列
1.4 D（Delete）
//删除表,如果存在
drop table if exists student;
//删除列
alter table 表名 drop 列名；

3.DML（增删改表中的数据）

1.添加
INSERT INTO 表名（列名集合）VALUES(数据内容);
2.删除
//如果没有加条件，直接删除所有记录
DELETE FROM 表名 WHERE 条件;
//删除表，然后创建空表
TRUNCATE TABLE user;
3.修改
UPDATE 表名 SET 列名 = 值 WHERE 条件;

4.DQL（查询表中数据）

语法
SELECT 字段列表
FROM 表名列表
WHERE 条件列表
GROUP BY 分组字段
HAVING 分组之后的条件
ORDER BY 排序
LIMIT 分页限定

4.1 基础查询

1.DISTINCT关键字的使用，去除重复
SELECT DISTINCT 字段 FROM 表名；
2.IFNULL(A,B)关键字的使用，如果为NULL
A,如果为NULL则使用B的值
SELECT IFNULL(字段值，替换值) FROM 表名;
3.AS 关键字的使用,起别名

4.2 条件查询

1.//查询20与30之间的age
SELECT * FROM student WHERE age>=20 && age <=30;
SELECT * FROM student WHERE age>=20 andage <=30;
SELECT * FROM student WHERE age BERWEEN 20 AND 30;
2.//查询age为22，18，25
SELECT * FROM student WHERE age=22 OR age = 18 OR age=25;
SELECT * FROM student WHERE age IN(22,18,25);
3.//查询条件为null
SELECT * FROM student WHERE age IS NULL;
4.LIKE关键字，模糊查询
//查询name第一个为马的
SELECT * FROM student WHERE name LIKE ‘马%’;
//查询name第二个为化的
SELECT * FROM student WHERE name LIKE ‘_化%’;
//查询name为三个字的人
**SELECT * FROM student WHERE name LIKE ‘_ _ _’;
**

4.3 排序查询

1.语法
ORDER BY 排序字段 排序方式
//根据age排序
SELECT * FROM user ORDER BY age ASC；
SELECT * FROM user ORDER BY age DESC；
//排序条件可以多个，当age一样时，按照下一个排序条件进行
SELECT * FROM user ORDER BY age ASC，其他字段.ASC；

4.4 聚合函数

1.count:统计
2.max:最大
3.min:最小
4.sum:和
5.avg:平均值

4.5 分组查询

语法
GROUP BY 分组字段
//分组后其他字段没意义，只能加分组之后的字段，要么加聚合函数
SELECT password,AVG(id) FROM user GROUP BY password;
WHERE 和 HAVING 的区别
1.WHERE 在分组前限定，HAVING在分组后进行限定
2.WHERE不可以使用聚合函数，HAVING可以
3.GROUP BY 与 HAVING 同时出现时HAVING必须在后面

4.6 分页查询

语法
LIMIT 开始的索引，每页查询的条数
分页公式
开始的索引 = （当前的页码 - 1）* 每页显示的条数

5. 约束

对表中的数据进行限定，保证
1.主键约束 PRIMARY KEY AUTO_INCREMENT,-- 自动增长主键
2.非空约束 NOT NULL
3.唯一约束 UNIQUE

CREATE TABLE stu(
	-- id INT PRIMARY KEY, -- 主键约束
	id INT PRIMARY KEY AUTO_INCREMENT,-- 自动增长主键
	name VARCHAR(20) NOT NULL, -- name为非空
	phone_number VARCHAR(20) UNIQUE -- 唯一约束
	
);
-- 删除主键约束
ALTER TABLE stu DROP PRIMARY KEY;
-- 添加主键约束
ALTER TABLE stu MODIFY id INT PRIMARY KEY;
-- 删除非空约束
ALTER TABLE stu MODIFY name VARCHAR(20);
-- 添加非空约束
ALTER TABLE stu MODIFY name VARCHAR(20) NOT NULL;
-- 删除唯一约束
ALTER TABLE stu DROP INDEX phone_number;
-- 添加唯一约束
ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;

4.外键约束
创建表的时候创建外键

CREATE TABLE stu (
	id INT PRIMARY KEY AUTO_INCREMENT,
	user VARCHAR(35) NOT NULL UNIQUE,
	user_id INT,
	CONSTRAINT stu_user_fk FOREIGN KEY (user_id) REFERENCES user(id)
);

删除外键

ALTER TABLE stu DROP FOREIGN KEY stu_user_fk;

添加外键

ALTER TABLE stu ADD CONSTRAINT stu_user_fk FOREIGN KEY (user_id) REFERENCES user(id);

//级联更新ON UPDATE CASCADE

ALTER TABLE stu ADD CONSTRAINT stu_user_fk 
FOREIGN KEY(user_id) REFERENCES user(id) ON UPDATE CASCADE;

删除
ON DELETE CASCADE

3. 多表操作

1. 多表之间的关系

1.一对一

2.一对多
实现：
在多的一方建立外键指向一的一方
3.多对多
实现：需要第三个表来建立外键

2. 范式

1.第一范式（1NF）：每一列都是不可分割的
2.第二范式（2NF）：

4. 数据库还原与备份

1.命令行
备份
mysqldump -u用户名 -p密码 数据库的名称 > 保存的路径//文件名称
得到一个sql文件
还原
创建数据库，进入数据库
执行
source 文件地址//文件名称
2.图形化界面

5. 多表查询

一.多表查询
1. 内连接
1.1 隐式内连接：使用where条件消除无用数据
SELECT * FROM emp,dept WHERE emp.dept_id = dept.id;
1.2 显式内连接：
SELECT * FROM emp INNER JOIN dept ON emp.dept_id = dept.id;
SELECT * FROM emp JOIN dept ON emp.dept_id = dept.id;
2. 外连接
2.1 左外连接
语法：select 字段列表 from 表1 left [outer] join 表2 on 条件；
查询的是左表所有数据以及其交集部分。
例子：
查询所有员工信息，如果员工有部门，则查询部门名称，没有部门，则不显示部门名称
SELECT t1.*,t2.name FROM emp t1 LEFT JOIN dept t2 ON t1.dept_id = t2.id;
2.2 右外连接
语法：select 字段列表 from 表1 right [outer] join 表2 on 条件；
查询的是右表所有数据以及其交集部分。
例子：
SELECT * FROM dept t2 RIGHT JOIN emp t1 ON t1.dept_id = t2.id;
3. 子查询

1. 子查询的结果是单行单列的：
   * 子查询可以作为条件，使用运算符去判断。 运算符： > >= < <= =
   *
   – 查询员工工资小于平均工资的人
   SELECT * FROM emp WHERE emp.salary < (SELECT AVG(salary) FROM emp);

2. 子查询的结果是多行单列的：
   * 子查询可以作为条件，使用运算符in来判断
   – 查询’财务部’和’市场部’所有的员工信息
   SELECT id FROM dept WHERE NAME = ‘财务部’ OR NAME = ‘市场部’;
   SELECT * FROM emp WHERE dept_id = 3 OR dept_id = 2;
   – 子查询
   SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = ‘财务部’ OR NAME = ‘市场部’);

3. 子查询的结果是多行多列的：
   * 子查询可以作为一张虚拟表参与查询
   – 查询员工入职日期是2011-11-11日之后的员工信息和部门信息
   – 子查询
   SELECT * FROM dept t1 ,(SELECT * FROM emp WHERE emp.join_date > ‘2011-11-11’) t2
   WHERE t1.id = t2.dept_id;

– 普通内连接
SELECT * FROM emp t1,dept t2 WHERE t1.dept_id = t2.id AND t1.join_date > ‘2011-11-11’

6. 事务

1. 事务的基本介绍
   1. 概念：
   * 如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。

2. 操作：
   1. 开启事务： start transaction;
   2. 回滚：rollback;
   3. 提交：commit;
   3. 例子：
   CREATE TABLE account (
   id INT PRIMARY KEY AUTO_INCREMENT,
   NAME VARCHAR(10),
   balance DOUBLE
   );
   – 添加数据
   INSERT INTO account (NAME, balance) VALUES (‘zhangsan’, 1000), (‘lisi’, 1000);

SELECT * FROM account;
UPDATE account SET balance = 1000;
– 张三给李四转账 500 元

– 0. 开启事务
START TRANSACTION;
– 1. 张三账户 -500

UPDATE account SET balance = balance - 500 WHERE NAME = ‘zhangsan’;
– 2. 李四账户 +500
– 出错了…
UPDATE account SET balance = balance + 500 WHERE NAME = ‘lisi’;

– 发现执行没有问题，提交事务
COMMIT;

– 发现出问题了，回滚事务
ROLLBACK;
4. MySQL数据库中事务默认自动提交

*事务提交的两种方式：
* 自动提交：
* mysql就是自动提交的
* 一条DML(增删改)语句会自动提交一次事务。
* 手动提交：
* Oracle 数据库默认是手动提交事务
* 需要先开启事务，再提交
* 修改事务的默认提交方式：
* 查看事务的默认提交方式：SELECT @@autocommit; – 1 代表自动提交 0 代表手动提交
* 修改默认提交方式： set @@autocommit = 0;

2. 事务的四大特征：
   1. 原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败。
   2. 持久性：当事务提交或回滚后，数据库会持久化的保存数据。
   3. 隔离性：多个事务之间。相互独立。
   4. 一致性：事务操作前后，数据总量不变
3. 事务的隔离级别（了解）
   * 概念：多个事务之间隔离的，相互独立的。但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题。
   * 存在问题：
   1. 脏读：一个事务，读取到另一个事务中没有提交的数据
   2. 不可重复读(虚读)：在同一个事务中，两次读取到的数据不一样。
   3. 幻读：一个事务操作(DML)数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改。
   * 隔离级别：
   1. read uncommitted：读未提交
   * 产生的问题：脏读、不可重复读、幻读
   2. read committed：读已提交 （Oracle）
   * 产生的问题：不可重复读、幻读
   3. repeatable read：可重复读 （MySQL默认）
   * 产生的问题：幻读
   4. serializable：串行化
   * 可以解决所有的问题

*注意：隔离级别从小到大安全性越来越高，但是效率越来越低
* 数据库查询隔离级别：
* select @@tx_isolation;
* 数据库设置隔离级别：
* set global transaction isolation level 级别字符串;

演示：
set global transaction isolation level read uncommitted;
start transaction;
– 转账操作
update account set balance = balance - 500 where id = 1;
update account set balance = balance + 500 where id = 2;

7.DCL

• SQL分类：
  1. DDL：操作数据库和表
  2. DML：增删改表中数据
  3. DQL：查询表中数据
  4. DCL：管理用户，授权

  • DBA：数据库管理员

  • DCL：管理用户，授权

    1. 管理用户

       1. 添加用户：

          • 语法：CREATE USER ‘用户名’@‘主机名’ IDENTIFIED BY ‘密码’;

       2. 删除用户：

          • 语法：DROP USER ‘用户名’@‘主机名’;

       3. 修改用户密码：

          UPDATE USER SET PASSWORD = PASSWORD(‘新密码’) WHERE USER = ‘用户名’;
          UPDATE USER SET PASSWORD = PASSWORD(‘abc’) WHERE USER = ‘lisi’;

          SET PASSWORD FOR ‘用户名’@‘主机名’ = PASSWORD(‘新密码’);
          SET PASSWORD FOR ‘root’@‘localhost’ = PASSWORD(‘123’);

          • mysql中忘记了root用户的密码？

            1. cmd – > net stop mysql 停止mysql服务

               • 需要管理员运行该cmd

            2. 使用无验证方式启动mysql服务： mysqld --skip-grant-tables

            3. 打开新的cmd窗口,直接输入mysql命令，敲回车。就可以登录成功

            4. use mysql;

            5. update user set password = password(‘你的新密码’) where user = ‘root’;

            6. 关闭两个窗口

            7. 打开任务管理器，手动结束mysqld.exe 的进程

            8. 启动mysql服务

            9. 使用新密码登录。

       4. 查询用户：
          – 1. 切换到mysql数据库
          USE myql;
          – 2. 查询user表
          SELECT * FROM USER;

          • 通配符： % 表示可以在任意主机使用用户登录数据库

    2. 权限管理：

       1. 查询权限：
          – 查询权限
          SHOW GRANTS FOR ‘用户名’@‘主机名’;
          SHOW GRANTS FOR ‘lisi’@’%’;

       2. 授予权限：
          – 授予权限
          grant 权限列表 on 数据库名.表名 to ‘用户名’@‘主机名’;
          – 给张三用户授予所有权限，在任意数据库任意表上

          GRANT ALL ON . TO ‘zhangsan’@‘localhost’;

       3. 撤销权限：
          – 撤销权限：
          revoke 权限列表 on 数据库名.表名 from ‘用户名’@‘主机名’;
          REVOKE UPDATE ON db3.account FROM ‘lisi’@’%’;