基本SQL语句

# =========================  数据库操作  ======================================
# 创建一个练习用的数据库，设置字符集和排序规则。
CREATE DATABASE db_practice CHARACTER SET utf8 COLLATE `utf8_general_ci`;

# 查看所有库
SHOW DATABASES;

# 选择数据库
USE db_practice;

# 删除数据库
DROP DATABASE db_practice;


# =========================  表操作  ========================================
# 创建表
CREATE TABLE IF NOT EXISTS  tab_student(
    `Sno` INT(3) NOT NULL AUTO_INCREMENT COMMENT '学号',
    `Sname` VARCHAR(10) NOT NULL DEFAULT '匿名' COMMENT '姓名',
    `Ssex` VARCHAR(3) NOT NULL DEFAULT '男' COMMENT '性别',
    `bornday` DATE DEFAULT NULL COMMENT '出生日期',
    `address` VARCHAR(50) DEFAULT '未填写' COMMENT '家庭地址',
    `email` VARCHAR(30) DEFAULT '未填写' COMMENT '电子邮箱',
    `password` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
    CONSTRAINT `pk_Sno` PRIMARY KEY (`Sno`) 
)ENGINE=INNODB DEFAULT CHARSET=utf8;

# 查看表属性
DESCRIBE tab_student;

# 查看表详细定义
SHOW CREATE TABLE t_employee;

# 删除表
DROP TABLE tab_student;

# 修改表属性
ALTER TABLE tab_student RENAME tab_stu;  -- 修改表名
ALTER TABLE tab_stu RENAME tab_student;

ALTER TABLE tab_student                  -- 删除多个字段
DROP Sno,
DROP`password`,
DROP`bornday`;

ALTER TABLE tab_student                                   -- 添加多个字段；
ADD  Sno INT(3) NOT NULL COMMENT '学号' FIRST,            -- 用first添加在最前；
ADD  bornday DATE DEFAULT NULL COMMENT '生日' AFTER Ssex, -- 用after添加在某字段后；
ADD  `password` VARCHAR(10) NOT NULL DEFAULT '123456';    -- 没有介词时添加到最后；

# 修改字段属性
ALTER TABLE tab_student MODIFY Sno CHAR(3);               -- 只修改数据类型
ALTER TABLE tab_student CHANGE Sno id CHAR(3);            -- 只修改字段名
ALTER TABLE tab_student CHANGE id Sno INT(3);             -- 同时修改字段名和数据类型
ALTER TABLE tab_student MODIFY Sno INT(3) AFTER Sname;    -- 移动字段的位置
ALTER TABLE tab_student MODIFY Sno INT(3) FIRST;          -- 将字段置顶


# ================================  索引  =============================================
# 用创建的方式添加索引，关键字create index
CREATE INDEX index_name 
ON t_employee(`name` ASC);             -- 普通索引（默认升序）

CREATE UNIQUE INDEX index_id           -- 唯一索引（默认升序）
ON t_employee(`id`);

# 用修改表的方式添加索引
ALTER TABLE t_employee                 -- 多列索引
ADD INDEX index_id_name(id, `name`);

# 删除索引,关键字drop index
DROP INDEX index_name
ON t_employee;
DROP INDEX index_id
ON t_employee;

# 查看查询操作时索引是否生效
EXPLAIN
SELECT * FROM t_employee WHERE `name`='Smith';


# ==========================  数据操作 ===============================================
# 插入记录
INSERT INTO tab_student(Sno,Sname,Ssex,bornday)
VALUE (1,'张小使','男','1985-9-5'),
      (2,'王晓红','女','1997-11-09'),
      (3,'李晓明','女','1996-09-22');
     
# 插入查询结果
INSERT INTO tab_student(Sname,Ssex)
SELECT Sname, Ssex
FROM tab_student
WHERE Sname='李晓明';

# 更新所有记录
UPDATE tab_student
SET email='345678@123.com';

# 更新特定记录
UPDATE tab_student
SET address = '广东省广州市珠江新城'
WHERE Sname = '王晓红';

# 删除记录
DELETE FROM tab_student        -- 删除特定记录
WHERE Sname='张小使';

TRUNCATE tab_student;          -- 删除所有记录


# ===========================  单表查询  ===========================================
# 简单查询，关键字select from
SELECT *                           -- 查询所有字段
FROM t_employee;

SELECT `name`,`job`,`salary`       -- 查询指定字段
FROM t_employee;

SELECT DISTINCT job                -- 去重查询
FROM t_employee;

SELECT `name`AS'姓名', salary*12 AS '年薪', ROUND(salary/26,2) AS '日薪'   -- 数学运算查询
FROM t_employee;                   

SELECT CONCAT(`name`,'员工的年薪为：',salary*12) AS '年薪',                -- 显示格式的查询
       CONCAT(`name`,'员工的日薪为：',ROUND(salary/26, 2)) AS '日薪'
FROM t_employee;

# 条件查询，关键字where
SELECT *                   
FROM t_employee
WHERE job='salesman' AND salary >= 800;       -- 比较运算和逻辑运算匹配条件

SELECT *
FROM t_employee
WHERE NOT job = 'salesman';                   -- 取反查询，关键字not

SELECT *                                      
FROM t_employee
WHERE salary BETWEEN 3000 AND 6000;           -- 范围查询，关键字between and

SELECT *
FROM t_employee
WHERE leader IS NULL;                         -- 空值查询，关键字is null

SELECT *
FROM t_employee
WHERE job IN('salesman', 'clerk', 'analyst'); -- 集合查询，关键字in（……）

SELECT *
FROM t_employee
WHERE `name` LIKE '_a%';                      -- 模糊查询，关键字like，通配符‘_’,'%'

# 排序查询结果，关键字order by
SELECT *
FROM t_employee
ORDER BY job ASC, `name` DESC;                -- 分两级排序，asc升序(默认)，desc降序

# 限制查询结果数（用于分页），关键字limit
SELECT *
FROM t_employee
ORDER BY salary DESC
LIMIT 5;                                      -- 仅指定结果数，不指定起始位置

SELECT *
FROM t_employee
ORDER BY salary DESC
LIMIT 5, 5;                                   -- 跳过5条记录，再显示5条记录

# 用统计函数查询（聚合函数）
SELECT COUNT(`name`) '人数', AVG(salary) '平均工资',
       MIN(age) '最小年龄', MAX(age) '最大年龄',
       SUM(salary) '总薪金'
FROM t_employee;

# 分组查询，关键字group by
SELECT job, GROUP_CONCAT(`name`) mumber, COUNT(`name`) `mount`
FROM t_employee
GROUP BY job;                             -- 分组查询的字段必须是聚合函数或被分组的字段，否则会报错。

SELECT dpmtID, job, GROUP_CONCAT(`name`) mumber, COUNT(`name`) mount
FROM t_employee
GROUP BY dpmtID, job;                                             -- 多级分组查询

SELECT job, AVG(salary) '平均工资', COUNT(`name`) '人数'                  
FROM t_employee
GROUP BY job
HAVING AVG(salary) < 2000;                                        -- 分组下的条件查询，关键字having
                                                 

# ============================  多表查询  ========================================
# 单表自连接
SELECT e.name '员工', e.job '职务', l.name '上级'          -- ANSI语法的连接
FROM t_employee AS e 
INNER JOIN t_employee AS l
ON e.leader = l.id;

SELECT e.name '员工', e.job '职务', l.name '上级'          -- 查询语法的连接
FROM t_employee AS e , t_employee AS l
WHERE e.leader = l.id; 

# 内连接，关键字inner join on
SELECT e.name '员工', e.job '职务', d.name '部门', d.locate '地址'
FROM t_employee AS e
INNER JOIN t_dpmt AS d
ON e.dpmtID = d.dpmtID;  

SELECT e.name '员工', e.job '职务', d.name '部门', d.locate '地址'
FROM t_employee AS e, t_dpmt AS d
WHERE e.dpmtID = d.dpmtID;

# 外连接
SELECT e.name '员工', e.job '职务', d.name '部门', d.locate '地址'
FROM t_employee AS e
LEFT JOIN t_dpmt AS d                                         -- 左外连接，关键字left join on
ON e.dpmtID = d.dpmtID;

SELECT e.name '员工', e.job '职务', d.name '部门', d.locate '地址'
FROM t_employee AS e
RIGHT JOIN t_dpmt AS d                                        -- 右外连接，关键字lright join on
ON e.dpmtID = d.dpmtID;

# 不等值连接
SELECT e.name '员工', e.job '职务', l.name '上级'         
FROM t_employee AS e 
INNER JOIN t_employee AS l
ON e.leader = l.id AND e.id > l.id;     

# 三表连接
SELECT e.name '员工', e.job '职务', l.name '上级', d.name '部门', d.locate '地址'
FROM t_employee AS e 
INNER JOIN t_employee AS l
ON e.leader = l.id
LEFT JOIN t_dpmt AS d
ON e.dpmtID = d.dpmtID;

# 合并查询结果，关键字union
SELECT `name`
FROM t_dpmt
UNION
SELECT `name`
FROM t_employee;

# 子查询（嵌套查询）
SELECT e.name, e.job, e.salary        -- from子句的子查询，需要给子查询起别名
FROM (
    SELECT *
    FROM t_employee
    WHERE job = 'salesman'
)AS e
WHERE e.salary > 1000;

SELECT *                              -- where子句的集合子查询，有IN(),ANY(),ALL(),EXISTS()
FROM t_employee
WHERE dpmtID IN(                     
    SELECT dpmtID
    FROM t_dpmt
)


# =============================  视图  =======================================
# 创建视图
CREATE VIEW v_emp_dpmt AS
SELECT e.name '姓名', e.job '职务', a.name '主管'
FROM t_employee AS e
INNER JOIN t_employee AS a
WHERE e.leader = a.id;

# 查看视图
SHOW TABLE STATUS FROM db_practice;       -- 查看库表详情
SHOW CREATE VIEW v_emp_dpmt;              -- 查看视图的定义信息
DESCRIBE v_emp_dpmt;                      -- 查看视图属性
                      
# 修改视图
CREATE OR REPLACE VIEW v_emp_dpmt AS                                 -- 用替换语句实现
SELECT e.name '员工', e.job '职务', d.name '部门', d.locate '地址'
FROM t_employee AS e
RIGHT JOIN t_dpmt AS d 
ON e.dpmtID = d.dpmtID;                     

ALTER VIEW v_emp_dpmt AS                                             -- 用更改语句实现
SELECT e.name '员工', e.job '职务', l.name '上级', d.name '部门', d.locate '地址'
FROM t_employee AS e 
INNER JOIN t_employee AS l
ON e.leader = l.id
LEFT JOIN t_dpmt AS d
ON e.dpmtID = d.dpmtID;

# 删除视图
DROP VIEW v_emp_dpmt;


# ===============================  用户管理 =====================================
# 创建普通用户
CREATE USER '陈奕迅'@'localhost' 
IDENTIFIED BY '123456';                              -- create user语句，identified by语句会自动加密
FLUSH PRIVILEGES;

INSERT INTO `user`(`host`,`User`,`authentication_string`,   -- insert into语句添加用户记录到user表
    ssl_cipher, x509_issuer, x509_subject)                  -- 该语法必须设置这3个安全字段的默认值
VALUE ('localhost','张学友',PASSWORD('123456'),'','','');   -- 新版本不用Password字段。
FLUSH PRIVILEGES;

# 用户授权
GRANT INSERT, SELECT 
ON db_practice.tab_student
TO '陈奕迅'@'localhost';                                    -- 给普通用户添加权限，注意用户名的写法。
FLUSH PRIVILEGES;

GRANT ALL PRIVILEGES 
ON db_practice.tab_student                               -- 创建新用户并授权，all privileges全部权限
TO '邓紫棋'@'localhost' IDENTIFIED BY '123456';
FLUSH PRIVILEGES;

# 查看用户权限
SELECT `User`,`Table_priv`,`Db`                         -- 查看某用户的表权限（系统表）
FROM tables_priv
WHERE `User`='邓紫棋';

# 修改root用户密码
SET PASSWORD = PASSWORD("654321");                      -- 当前登录用户set password修改

UPDATE `user` 
SET authentication_string = PASSWORD("888888")          -- 更新user表的方式修改密码
WHERE `User`='root' AND `host`='localhost';

`mysqladmin -uroot -p原密码 password '新密码'`          -- dos窗口执行mysqladmin命令

# 超级用户修改普通用户密码
SET PASSWORD FOR '陈奕迅'@'localhost'=PASSWORD("888888");
FLUSH PRIVILEGES;                                       -- 用set password for语句修改

UPDATE `user`
SET authentication_string = PASSWORD("888888")          -- 更新user表的方向修改密码
WHERE `User`='张学友' AND `host`='localhost';
FLUSH PRIVILEGES;

# 删除用户
DROP USER '陈奕迅'@'localhost';                           -- drop user语句直接删除

DELETE FROM `user`
WHERE `User` IN('邓紫棋','张学友');                       -- 在user表删除


# ============================= 备份与还原 ==============================
# 备份数据库（dos窗口）
`mysqldump -u root -p db_practice t_dpmt t_employee            
> C:\Users\Administrator\Desktop\sql练习\t_company_backup.sql` -- 备份单个数据库的表

`mysqldump -u root -p --databases
db_practice mysql
> C:\Users\Administrator\Desktop\sql练习\databases_backup.sql` -- 备份多个数据库

`mysqldump -u root -p --all-databases
> D:\Environment\mysql-5.7.23-winx64\all_backup.sql`       -- 备份所有数据库

# 还原数据库
`mysql -u root -p  
< C:\Users\Administrator\Desktop\sql练习\databases_backup.sql` -- 还原数据库

`mysql -u root -p db_test
< C:\Users\Administrator\Desktop\sql练习\t_company_backup.sql` -- 还原表到某数据库

# 导出到文本
SELECT *
FROM tab_student
INTO OUTFILE 'C:\Users\Administrator\Desktop\sql练习\t_student.txt'
FIELDS ESCAPED BY '\\'
LINES STARTING BY '>'
LINES TERMINATED BY '<\n'
ENCLOSED BY '-'
TERMINATED BY '|'
OPTIONALLY ENCLOSED BY '\'';