数据库基本指令大全-使用合集

# 数据库操作
SHOW DATABASES; #查看当前所有的数据库
USE 库名; #打开指定的库
SHOW TABLES; #查看当前库的所有表
SHOW TABLES FROM 库名; #查看其他库的所有表
DESC 表名; #查看表的列信息
SELECT * FROM 表名; #查看表中所有数据
SELECT `字段`,`字段`FROM 表名; #自定义查询表中多个字段
SELECT DATABASE(); #查看当前所在库

# 修改密码
UPDATE USER SET PASSWORD = PASSWORD('123456')WHERE USER = 'root'; #修改密码
SET PASSWORD = PASSWORD('123456'); #改密码
FLUSH PRIVILEGES; #刷新数据库权限

# 创建数据库
CREATE DATABASE NAME; #创建数据库
SELECT VERSION(); #查看服务器的版本

# 数据类型属性
# unsigned 无符号整数
# zerofill 空位零补充
# auto_InCrement 自增

# 修改表结构
ALTER TABLE 旧表名 RENAME AS 新表明; #修改表名
ALTER TABLE 表名 ADD 字段名 列属性[INT(4)]; #增加表的字段

ALTER TABLE 表名 MODIFY 字段名 列属性; #修改表的字段名
ALTER TABLE 表名 CHANGE 旧名字 新名字 列属性; #字段修改约束或类型
# change用来字段重命名，不能修改字段类型和约束
# modify不用来字段重命名，只能修改字段类型和约束

# 删除表
DROP TABLE IF EXISTS 表名; #删除表

# 查看数据库和表定义
SHOW CREATE DATABASE 库名; #查看数据库的定义
SHOW CREATE TABLE 表名; #查看数据表的定义

# 数据操作
# 增
INSERT INTO 表名(字段1, 字段2, 字段3, ...) VALUES('值1', '值2', '值3');
# 注：字段和字段之间使用英文逗号隔开；
#     字段是可以省略的，但是后面的值必须要一一对应，不能少
#     可以同时插入多条数据，values后面的值，需要使用,隔开即可 values(),()...

# 改
UPDATE 表名 SET `指定列名`='要改的内容',[`指定列名`='要改的内容'] WHERE[条件];
# 注：指定列名是数据库的列，尽量带上``
#     条件：筛选的条件如果没有指定，则会修改组欧诺的列
#     要更改的内容是一个具体的值，也可以是一个变量
#     多个设置的属性之间，使用英文逗号隔开

# 删
DELETE FROM `表名`; --自增不清零
TRUNCATE TABLES `表名`; --自增清零

# 查
SELECT `字段`,... FROM `表名`;

# 查询指定字段
SELECT `employee_id`,`first_name` FROM `employees`

# 给查询的字段取别名[AS 可以给字段和表起别名]
SELECT `employee_id`AS 员工ID, `first_name`AS 名字 FROM `employees`AS kin

# 函数 Concat（a, b）
SELECT CONCAT('名字', `first_name`,'工资：', `salary`) AS 新名字 FROM `employees`

# 去重
SELECT * FROM `employees`;--查看所以数据
SELECT `manager_id` AS 工种 FROM `employees`;--发现重复数据
SELECT DISTINCT `manager_id` AS 工种 FROM `employees`;--去重

# 数学计算
SELECT 250 * 7 + 2500 + 1750 + 1750 AS 计算结果; --计算数值

# WHERE查询
SELECT `employee_id` AS ID, `salary` AS 薪水 FROM `employees`
    WHERE `employee_id`>= 110 AND `employee_id`<= 130; #ID 在110~130之间的数据

SELECT `employee_id` AS ID, `salary` AS 薪水 FROM `employees`
    WHERE `employee_id`<= 110 || `employee_id`=130; #ID 小于110或等于130的数据

SELECT `employee_id` AS ID, `salary` AS 薪水 FROM `employees`
    WHERE `employee_id`!= 100 AND `salary`>= 10000; #ID 不等于110的数据

SELECT `employee_id` AS ID, `salary` AS 薪水 FROM `employees`
    WHERE `employee_id` BETWEEN 100 AND 110 AND `salary`<= 10000;

SELECT `department_id` AS 部门, `salary` AS 薪水 FROM `employees`
    WHERE `department_id`>= 90 AND NOT `salary`<= 10000

# 模糊查询
SELECT `first_name`,`last_name` FROM `employees`
    WHERE `first_name` LIKE 'S%' #以S开头的名字

SELECT `first_name`,`last_name` FROM `employees`
    WHERE `first_name` LIKE '_e%' #第二个字符为e的名字

SELECT `姓名`,`年龄`FROM `stu`
WHERE `姓名` LIKE '王__' #姓王，名字为三个字

SELECT `姓名`,`年龄`FROM `stu`
WHERE `姓名` LIKE '%梦%' #名字中带有梦字

# IN() 在某个范围内
SELECT `ID`,`姓名`FROM `stu`
WHERE `性别` IN('女')

# IS NULL  IS NOT NULL
SELECT * FROM `departments`
SELECT `department_name` AS 姓名, `location_id` AS 工种 FROM `departments`
WHERE `manager_id` IS NULL OR `department_id` IN(230, 240, 250)

SELECT `department_name` AS 姓名, `location_id` AS 工种 FROM `departments`
WHERE `manager_id` IS NOT NULL

# 聚合函数
SELECT COUNT(`姓名`) FROM `stu_copy` #计算姓名的数量
--都能够统计 表中的数据（想查询一个表中有多少个记录，就使用这个count()）
SELECT COUNT(`ID`) FROM `copy`;--count(列名)，会忽略所有的 null 值
SELECT COUNT(*) FROM `copy`;--count(*)，不会忽略null值，本质计算行数
SELECT COUNT(1) FROM `copy`- - count(1), 不会忽略所有的null值 本质计算行数
 
# 函数名称  		函数功能
COUNT() 	#返回选取结果集中行的数目
SUM()  		#返回选取结果集中所有值的总和
AVG()  		#返回选取结果集中所有值的平均值
MAX()  		#返回选取结果集中所有值的最大值
MIN()  		#返回选取结果集中所有值的最小值
SELECT SUM(`成绩`) AS 总分 FROM `copy`- - 求和
SELECT AVG(`成绩`) AS 平均分 FROM `copy`- - 求平均值
SELECT MAX(`成绩`) AS 最高分 FROM `copy`- - 求最大值
SELECT MIN(`成绩`) AS 最低分 FROM `copy`- - 求最小值

# 排序查询 order by
SELECT * FROM `stu_copy` ORDER BY `工资` ASC #升序
SELECT * FROM `stu_copy` ORDER BY `工资` DESC #降序

# 分页函数 limit
SELECT * FROM `stu`
LIMIT 0, 5 #获取前5条数据

# 联立查询
# INNER JOIN
SELECT s.`ID`,`工种`,`成绩`,`性别`
FROM `stu` AS s
INNER JOIN `stu_copy` AS c
ON s.stu = c.stu_copy

# Right join
SELECT s.`ID`,`工种`,`成绩`,s.`姓名`
FROM `stu` AS s
RIGHT JOIN `stu_copy` AS c
ON s.`ID`=c.`ID`

# Left join
SELECT s.`ID`,`工种`,`成绩`,s.`姓名`
FROM `stu` AS s
LEFT JOIN `stu_copy` AS c
ON s.`ID`=c.`ID`

SELECT s.`ID`,`工种`,`成绩`,s.`姓名`
FROM `stu` AS s
LEFT JOIN `stu_copy` AS c
ON s.`ID`=c.`ID`
WHERE `成绩` IS NULL
--join(连接的表) on(判断的条件) 连接查询
--where 等值查询
 
#方法
/*
我要查询哪些数据：select ……
从哪几个表中查：from 表名 左表[as *] join 连接的表[as *] on 交叉条件
假设存在一种多张表查询，慢慢来，先查询两张表然后在慢慢增加
*/

# SELECT语法
SELECT[ALL | DISTINCT]
{*| table.* | [table.field1[AS alias1]][, ...]]}
FROM table_name[AS table_alias]
[LEFT | RIGHT | INNER JOIN table_name2]  --联合查询
[WHERE ...]  --指定结果需满足的条件
[GROUP BY ...]  --指定结果按照哪几个字段来分组
[HAVING]  --过滤分组的记录必须满足的次要条件
[ORDER BY ...]  --指定查询记录按一个或多个条件排序
[LIMIT{ [OFFSET,] ROW_COUNT | row_countOFFSET OFFSET }];
--指定查询的记录从哪条至哪条
# 注意 : [] 括号代表可选的, { }括号代表必选得

# 函数
# 数学运算
SELECT ABS(-8)--绝对值
SELECT CEILING(9.5)--向上取整
SELECT FLOOR(9.5)--向下取整
SELECT RAND()--返回0~1之间的随机数
SELECT SIGN()--判断一个数的符号 0 = 0 正 = 1 负 = -1

# 字符串函数
SELECT CHAR_LENGTH('勿以恶小而为之')--字符串长度
SELECT CONCAT('2020', '2月', '19日')--拼接字符串
SELECT INSERT('我爱炉石传说', 2, 1, '喜欢打')--替换字符串
SELECT LOWER('ScHool')--转换为小写字母
SELECT UPPER('ScHool')--转换为大写字母
SELECT INSTR('Hello word', 'o')--搜索指定字符，返回第一个发现指定字符的位置
SELECT REPLACE('二戒喜欢打炉石传说', '喜欢', '爱')--字符串替换函数
SELECT SUBSTR('我爱学习sql语言，然后学习jdbc', 2, 9)--返回指定的子字符串（源字符串, 截取的位置, 截取的长度）
SELECT REVERSE('你的生字表')--反转

# 时间和日期函数
SELECT NOW()--获取当前时间
SELECT YEAR(NOW())--年
SELECT MONTH(NOW())--月
SELECT DAY(NOW())--日
SELECT HOUR(NOW())--时
SELECT MINUTE(NOW())--分
SELECT SECOND(NOW())--秒

# 系统
SELECT SYSTEM_USER()--系统当前用户
SELECT USER()--系统当前用户
SELECT VERSION()--系统当前版本

# 用户管理
# 创建用户 create user 用户名 identified by '密码'
CREATE USER kin IDENTIFIED BY '123456'

# 修改密码（修改当前用户密码）
SET PASSWORD = PASSWORD('123456')

# 修改密码（修改指定用户密码）
SET PASSWORD FOR kin = PASSWORD('200105')

# 重命名 rename user 原来名字 to 新的名字
RENAME USER kin TO kin2

# 用户授权:all privileges on（全部的权限）库.表 to 指定用户
GRANT ALL PRIVILEGES ON * .*TO kin2

# 查看权限
SHOW GRANTS FOR kin2-- 查看指定用户权限

# 数据导出与导入
# 导出
# 使用命令行导出 mysqldump 命令行使用
# mysqldump - h 主机 - u 用户名 - p 密码 数据库 表名 > 物理磁盘位置 / 文件名
mysqldump - h localhost - u root - p123456 school day1 > C: / a.sql-- 单表导出

# 导入
--登录mysql的情况下
--命令行操作：source 物理磁盘位置 / 文件名
source c : / a.sql

# 索引
# 创建索引
CREATE INDEX id_app_user_name ON app_user(`name`)

# 检查索引使用情况
EXPLAIN SELECT * FROM `app_user` WHERE `name`='用户999999'

# 索引原则：
# 索引不是越多越好
# 不要对进程变动数据加索引
# 小数据量的表不需要加索引
# 索引一般加在常用来查询的字段上！

# SQL语句调试
# 查看SQL语句执行计划
EXPLAIN SELECT * FROM 表名 WHERE 条件;

# 错误处理
# 捕获和处理SQL错误
BEGIN TRY
  -- SQL语句
END TRY
BEGIN CATCH
  -- 错误处理语句
END CATCH

# 存储过程
DELIMITER //
CREATE PROCEDURE sp_GetEmployeeInfo(IN empID INT)
BEGIN
  SELECT * FROM employees WHERE employee_id = empID;
END //
DELIMITER ;
CALL sp_GetEmployeeInfo(1);

# 触发器
DELIMITER //
CREATE TRIGGER trg_AfterEmployeeUpdate
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
  INSERT INTO audit_log SET action = 'UPDATE', table_name = 'employees', record_id = NEW.employee_id;
END //
DELIMITER ;

# 视图
CREATE VIEW vw_EmployeeSummary AS
SELECT employee_id, first_name, last_name, department, position
FROM employees
JOIN departments ON employees.department_id = departments.department_id;

# 数据完整性
# 主键
ALTER TABLE 表名 ADD PRIMARY KEY (字段名);
# 外键
ALTER TABLE 表名 ADD CONSTRAINT fk_表名_外键名 FOREIGN KEY (字段名) REFERENCES 父表名(父表字段名);
# 唯一键
ALTER TABLE 表名 ADD UNIQUE (字段名);
# 非空
ALTER TABLE 表名 MODIFY 字段名 数据类型 NOT NULL;
# 默认值
ALTER TABLE 表名 MODIFY 字段名 数据类型 DEFAULT '默认值';

# 事务
START TRANSACTION;
-- SQL语句
COMMIT; -- 提交事务
ROLLBACK; -- 回滚事务

# 日志与备份
# 开启二进制日志
log-bin=mysql-bin
# 日志文件名
binlog_format=mixed
# 备份
mysqldump -u root -p database_name > backup.sql
# 恢复
mysql -u root -p database_name < backup.sql