1、数据库操作语句

1.1、登陆数据库

mysql {-h......} {-P......(大写)} -u...... -p......(小写)

-h：目标数据库IP

-P：目标端口，默认3306

-u：目标数据库用户名

-p：目标数据库密码

登陆本地数据库时，前面两个参数可省略

1.2、创建数据库

create database

{if not exsts}| 判断是否存在该数据库，不存在则创建

数据库名称

{character set ‘字符集‘}|设置字符集，默认utf8;

1.3、删除数据库

drop database

{if exists}|判断是否存在，存在则删除

数据库名称;

1.4、切换数据库

use 数据库名称;

1.5、修改数据库

alter database 数据库名

1.6、查看当前使用的数据库

select database();

2、表的操作语句

2.1、创建表

CREATE TABLE student(
	`id` INT NOT NULL AUTO_INCREMENT,
	`username` VARCHAR(20) NOT NULL,
	`password` VARCHAR(20) NOT NULL,
	PRIMARY KEY (`id`)
);

2.2、修改表

--修改表名
ALTER TABLE 旧表名  RENAME AS 新表名

--增加表的字段
ALTER TABLE 表名 ADD age INT(11)

--修改表的字段的数据类型
ALTER TABLE 表名 MODIFY age VARCHAR(11)  --修改约束

ALTER ATBLE 表名 CHANGE age age1 INT(2)  --字段重命名

--删除表的字段
ALTER TABLE 表名 DROP 字段名

2.3、删除表

DROP TABLE IF EXISTS `newStudent`;

2.4、蠕虫复制

-- 创建类似表
CREATE TABLE `newStudent` LIKE `student`;

-- 插入查询的结果到表中
INSERT INTO `newStudent` (SELECT * FROM `student`);

3、数据操作语句

3.1、插入数据

-- 插入全部
INSERT	INTO student VALUES(1,‘zhangsan‘,‘123456‘);
-- 插入某些列
INSERT	INTO student (`username`,`password`) VALUES(‘lisi‘,‘212324‘);
-- 插入多组数据
INSERT	INTO student (`username`,`password`) VALUES(‘zhaoliu‘,‘334455‘),(‘yanqi‘,‘445566‘),(‘wangba‘,‘556677‘),(‘lijiu‘,‘667788‘);

3.2、更新数据

UPDATE `student` SET `password`=233233 WHERE `id`=2;

3.3、删除数据

delete from `student` where `id`=7;

4、查找数据

4.1、普通条件查询 ->where

-- 普通条件
SELECT `NAME`,`price`  FROM `goods` WHERE `price`>2000 && `price`<5000;

-- 区间
select * from `goods` where `produced_date` >= ‘2020-01-01‘ and `produced_date` <= ‘2020-12-30‘;

-- BETWEEN...AND... 》》》 区间
select * from `goods` where `produced_date` between ‘2020-01-01‘ and ‘2020-12-30‘;

-- 或
SELECT * FROM `goods` WHERE `NAME` = ‘华为P40‘ OR `NAME` = ‘小米11‘;

-- IN(...) 》》》 多选一
SELECT * FROM `goods` WHERE `NAME` in (‘华为P40‘ , ‘小米11‘);

4.2、起别名 -> AS

SELECT `NAME` AS 品牌,`price` AS 价格  FROM `goods` WHERE `price`>2000 && `price`<5000;

4.3、四则运算 ->{+-*/}

SELECT `NAME`,(`price` {+-*/} 1)  FROM `goods` WHERE `price`>2000 && `price`<5000;

4.4、去重查询 ->DISTINCT

select distinct `NAME` from `goods` ;

4.5、模糊查询 -> LIKE

‘_‘ ：表示匹配任意一个字符

‘%’：表示匹配任意数量字符

-- 查询`NAME`字段带米字的数据
SELECT `NAME` AS `品牌`,`price` AS `价格` FROM `goods` WHERE `NAME` LIKE ‘%米%‘;
-- 查询`NAME`字段第二个字是米字的数据
SELECT `NAME` AS `品牌`,`price` AS `价格` FROM `goods` WHERE `NAME` LIKE ‘_米%‘;

-- 查询`NAME`字段倒数第二个字是米字的数据
SELECT * FROM `goods` WHERE `NAME` LIKE ‘%米_‘

-- 查询`NAME`字段为三个字符的数据
SELECT * FROM `goods` WHERE `NAME` LIKE ‘___‘

4.6、排序查询 ->ORDER BY {ASC/DESC}

• DESC：降序排序
• ASC ：升序排序（默认升序）

-- 查询`sales_volume` > 1000的商品信息并按照`price`排序
SELECT * FROM `goods` WHERE `sales_volume` > 1000 ORDER BY `price` ASC;

-- 多条件排序
SELECT * FROM `goods` WHERE `sales_volume` > 1000 ORDER BY `price` DESC,`sales_volume` ASC;

4.7、聚合函数

select count(*) from `goods` where `category` in (‘手机‘,‘食物‘);


select max(`price`) from `goods` where `category` = ‘手机‘;


select SUM(`sales_volume`) from `goods` where `category` = ‘手机‘;

4.8、分组 ->GROUP BY

-- 每组售卖量之和
select category,sum(sales_volume) from goods group by category;

-- 每组最大值
SELECT category,max(sales_volume) FROM goods GROUP BY category;

-- 每组数量
SELECT category,COUNT(*) FROM goods GROUP BY category;

5、用户操作语句

-- 创建用户
create user ‘admin‘@‘%‘ identified by ‘Wenyepeng.123‘;

-- 授权
grant all on db2.goods to ‘admin‘@‘%‘;
grant system_user on *.* to ‘root‘;

-- 删除
drop user ‘admin‘@‘%‘;

-- 查询用户
select * from mysql.user;

-- 移除权限
revoke all on db2.goods from ‘admin‘@‘%‘

MySQL8.0授权时报错：Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation

grant system_user on *.* to ‘root‘;

6、约束

CREATE TABLE users(
   id INT PRIMARY KEY AUTO_INCREMENT, -- 列名 数据类型 约束,  
   username VARCHAR(20) UNIQUE,  -- 列名 数据类型 约束,
   PASSWORD VARCHAR(20) NOT NULL, -- 列名 数据类型 约束
   address VARCHAR(100) DEFAULT ‘广州156‘ -- 类名 数据类型 约束 默认值
);

-- 改变自增初始值
alter table users AUTO_INCREMENT = 1000;

7、数据库备份还原

• 备份：在命令行执行

  mysqldump -uroot -ptoor ｛dbname｝ > ｛path.sql｝

• 还原：登陆数据库进行还原操作

  create database {dbname};

  use {dbname};

  source {path.sql};

8、多表查询

8.1、表链接查询

8.1.1、内连接

• 隐式内连接

  select * from emp,dept where emp.`dept_id` = dept.`id`;
  
  -- select * from 表1，表2... where 表链接条件
  

• 显式内连接

  select * from emp inner join dept on emp.`dept_id` = dept.`id`;
  
  -- select * from 表1 inner join 表2 on 表链接条件 where ... 
  

标准写法

select
	emp.`id`,
	emp.`NAME`,
	emp.`gender`,
	emp.`salary`,
	emp.`join_date`,
	dept.`NAME`
from 
	emp,
	dept
where 
	emp.`dept_id` = dept.`id`
and
	emp.`NAME` = ‘唐僧‘;

8.1.2、外连接

在join左边的就叫左表，在join右边的就叫右表

• 左外连接：左表不满足条件的也要显示 A ∪ (A ∩ B)

  select * from dept left join emp on emp.`dept_id` = dept.`id`;
  

• 右外连接：右表不满足条件的也要显示 (A ∩ B) ∪ B

  
  select * from emp right join dept on emp.`dept_id` = dept.`id`;
  

8.2、子查询

• 子查询结果为单行单列：

select * from emp where salary < (select avg(salary) from emp);

select * from emp where salary = (select min(salary) from emp);

• 子查询结果为多行单列：使用 IN (...)

• 子查询结果为多行多列：将结果作为一个新表查询

select 
  dept.`NAME`,
  e1.`NAME`,
  `gender`,
  `salary`,
  `join_date` 
from
  dept,
  (select 
    * 
  from
    emp where join_date > ‘2011-01-01‘) e1 
where e1.dept_id = dept.`id`  ;

8.1.3、表的自关联

select e1.`ename`,e2.`ename` from emp e1 left join emp e2 on e1.`mgr` = e2.`id`;                               

9、索引

-- 创建单例索引
create index index_name on account(name);
-- 创建复合索引
create index index_name_balance on account(name,balance);
-- 查找索引
show index from account;
-- 删除索引
drop index index_name on account;

索引失效的情况

• 如果条件中有OR，即使其中有条件带索引也不会使用。(例外：OR两边条件都是同一个字段 )
• 模糊LIKE查询以 ‘%xxx‘开头的不会使用索引，以‘xxx%‘结尾会使用索引
• WHERE语句中使用不等于 <>和 != 不会使用索引
• 如果MySQL计算使用全表扫描要比使用索引快，则不使用索引。

10、事物

START TRANSACTION;

-- 1. 张三账号-500
UPDATE account SET balance=balance-500 WHERE id=1;
-- 2. 李四账号+500
UPDATE account SET balance=balance+500 WHERE id=2;

ROLLBACK[commit];

• 事物回滚点

-- 事务的回滚点
-- 先开启事务
START TRANSACTION;

-- 1. 张三账号-10
UPDATE account SET balance=balance-10 WHERE id=1;
-- 2. 李四账号+10
UPDATE account SET balance=balance+10 WHERE id=2;


SAVEPOINT a;


-- 1. 张三账号-10
UPDATE account SET balance=balance-10 WHERE id=1;
-- 2. 李四账号+10
UPDATE account SET balance=balance+10 WHERE id=2;

SAVEPOINT b;


-- 1. 张三账号-10
UPDATE account SET balance=balance-10 WHERE id=1;
-- 2. 李四账号+10
UPDATE account SET balance=balance+10 WHERE id=2;

SAVEPOINT c;

-- 表示回滚点b上面的sql语句执行成功，下面所有的sql语句执行失败。
ROLLBACK TO b; 

• 事物隔离级别

11、mysql内置函数

-- 返回字符串长度
select char_length(‘asdasdw‘) length;

-- 非空则返回第一个值，为空则返回第二个值
select ifnull(null,‘bbb‘);

-- 字符串拼接
select concat(‘s‘,‘b‘);

-- 截取从1开始四个字符串，索引从1开始
select substr(‘sadbasd‘,‘1‘,‘4‘);

-- 可以写负数，但负数绝对值不能大于字符串长度
SELECT SUBSTR(‘sadbasd‘,-5);

-- 字符串转小写
select lower(‘SEDASweaA‘);

-- 字符串转大写
select upper(‘asdwasd‘);

-- 头尾去空格
select trim(‘asd as ‘);

-- 替换
select replace(‘s a g w ‘,‘ ‘,‘‘);

-- 获取一个0-1的随机数
select rand( );

-- 获取一个0-10的随机数，保留0位小数
select round(rand() * 10,0);

-- 当前时间curdate()加上三天。
select  adddate(curdate(),3);

-- 当前时间到‘2021-01-01’的天数
select datediff(curdate(),‘2021-01-01‘);

-- 获取当前年月日
select date();

-- 获取当前年月日，时分秒
select now();

-- 获取日期对象中的年；
select year(now());

• 高级函数case

  case 语句相当于一个字段，在end后面需要给字段起名字，用于显示查询结果字段名

  -- case表达式语法1
  -- select 字段名1, 字段名2, 
  -- 	case 字段名
  --	when 值1 then 返回的值
  --	when 值2 then 返回的值
  --	...
  --	else
  --  上面都不符合返回的值
  --  end  别名
  --  from 表名;
  
  
  select ename,salary,salarygrade.`grade`,
  case salarygrade.`grade`
  when 1 then ‘努力赚钱‘
  when 2 then ‘小康生活‘
  when 3 then ‘可以娶媳妇‘
  when 4 then ‘可以买车‘
  when 5 then ‘可以买房‘
  else ‘土豪‘
  end 建议
  from emp,salarygrade where emp.`salary` between salarygrade.`losalary` and salarygrade.`hisalary`;
  

• 高级函数if

  IF(条件, ‘条件成立返回的值‘, ‘条件不成立返回的值‘)
  
  SELECT 
    emp.`id`,
    emp.`salary`,
    IFNULL(emp.`bonus`, 0),
    IF(
      (
        emp.`salary` + IFNULL(emp.`bonus`, 0)
      ) > 20000,
      ‘收入不错‘,
      ‘继续努力‘
    ) AS 建议
  FROM
    emp ;
  

12、视图

-- 创建视图： CREATE VIEW 视图名称 AS 查询语句
create view city_country as (select city.`NAME`,country.`NAME` count_name from city,country where city.`cid` = country.`id`);

select * from city_country;

-- 修改视图：ALTER VIEW 视图名称 AS 查询语句;
alter view city_country as (select city.`id`,city.`NAME`,country.`NAME` country from city,country where city.`cid` = country.`id`);

-- 删除视图： DROP VIEW 视图名称;
drop view city_country;

-- 查看视图
show tables;

13、存储过程

存储过程的好处：

• 提高代码的复用性。
• 减少数据在数据库和应用服务器之间的传输，提高效率。
• 减少代码层面的业务处理。

扩展1：

在以前，有一些金融项目还是会用到存储过程的。

因为存储除了上面的好处之外，最大的好处：可以对外隐藏表结构。

如果没有存储过程，注册insert into 表名（列名）。。。

一旦写了列名，那么程序员势必会知道表的结构。表的结构一旦泄露了，很容易被攻击。

如果有了存储过程，程序员不需要知道表的结构，只要把注册的用户名和密码给数据库中的存储过程就可以了。由存储过程进行判断，然后再添加数据。（降低效率，增加安全）（DBA）

扩展2：

执行效率，数据安全。（冲突的）

鱼与熊掌不可兼得。

事务（隔离级别：越安全，效率越低。越危险，效率越高）

通常情况来讲：偏向安全。

当效率跟安全冲突的时候，会兼顾安全。

-- 创建存储过程
-- 设置结束符号为$ mysql中默认是;
delimiter $

create procedure 存储过程名称([IN|OUT|INOUT])
-- 例：create procedure method1(IN num INT)  传入一个int类型的参数num
-- 例：create procedure method1(OUT num INT)  设置一个int类型的返回值num
-- 获取返回值 call method1(@num); 
		-- select @num;

begin 
	SQL 语句列表;
end $

-- 将结束符号改回;
delimiter ;

-- 存储过程调用
call 存储过程名称(参数列表)

-- 存储过程的IF判断
IF 条件 THEN 代码；
ELSEIF 条件 THEN 代码；
ELSE 代码；
END IF;
--------------------------------------------------------------
-- 存储过程中的形参和返回结果
语法：
CREATE PROCEDURE 存储过程名称([IN|OUT|INOUT] 参数名 数据类型)
BEGIN
  	SQL 语句列表;
END$
注意点：
- IN：代表输入参数，需要由调用者传递实际数据(默认)
- OUT：代表输出参数，该参数可以作为返回值
- INOUT：代表既可以作为输入参数，也可以作为输出参数
--------------------------------------------------------------
-- 存储过程中的while循环
语法：
WHILE 条件 DO
 		 循环体;
END WHILE;
--------------------------------------------------------------
-- 存储过程中的repeat循环
语法：
REPEAT
	代码;
UNTIL 条件
END REPEAT;
-------------------------------------------------------------
-- 存储过程中的loop循环（死循环）
语法1：
LOOP
  	循环体;
END LOOP;
语法2：
	标签名: LOOP
  		IF 结束循环的条件 THEN
		LEAVE 标签名;
  	END IF;
  	循环体;
END LOOP 标签名;

14、函数

-- 创建函数
DELIMITER $
CREATE FUNCTION function_name(参数列表)
RETURNS 返回值类型
BEGIN
	SQL语句;
	RETURN 结果;
END;
DELIMITER ;

-- 调用函数
SELECT 函数名称(实际参数);

-- 删除函数
DROP FUNCTION 函数名称;

关于数据库引擎

关于数据库引擎

• INNODB 默认使用

• MYISAM 早些年使用的

  	MYISAM	INNODB
  事务支持	不支持	支持
  数据行锁定	不支持	支持
  外键约束	不支持	支持
  全文索引	支持	不支持
  表空间大小	较小	较大，约为2倍

  常规使用操作：

  • MYSAAM 节约空间，速度较快
  • INNODB 安全性高，事务的处理，多表多用户操作

数据库入门到删库跑路