MySQL基础语句概括

1.DDL语句

（1）DDL数据库操作

SHOW DATABASES;     //显示当前数据库列表
CREATE DATABASE 数据库名;      // 创建数据库
USE 数据库名;                   //转到指定数据库
SELECT DATABASE();            //返回当前数据库名称

（2）DDL表操作

SHOW TABLES;
CREATE TABLE 表名（字段 字段类型，字段 字段类型...字段，字段类型）;
DESC 表名;    //查看表结构
SHOW CREATE TABLE 表名;     //查看建表语句
ALTER TABLE 表名 ADD/MODIFY/CHANGE/DROP/RENAME TO...
ALTER TABLE 表名 MODIFY  字段名，新数据类型;   //修改数据类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名  新数据类型;    //修改数据字段和数据类型。
DROP TABLE 表名;                  //删表

DML语句

INSERT INTO 表名（字段1，字段2...）VALUES(值1，值2...),(值1，值2...)
UPDATE 表名 SET 字段1=值1，字段2=值2...（WHERE 条件）;   //修改数据
DELETE FROM 表名（WHERE 条件);   //删除数据

DQL语句

查询第一页员工信息，显示10条数据
SELECT * FROM EMP LIMIT 0,10;
查询第二页员工信息，显示10条数据
SELECT * FROM EMP LIMIT 10,10;//SELECT * FROM EMP LIMIT （页码-1）*每页展示数，每页展示数;

DCL语句

(1)用户管理

CREATE USER `用户名`@`主机名`(`%`  //任意主机) IDENTIFIED BY `密码`;
ALTER USER `用户名`@`主机名` IDENTIFIED WITH myasl_native_password BY `密码`;
DROP USER `用户名`@`主机名`;

(2)权限控制

GRANT 权限列表 ON 数据库名 表名 TO `用户名`@`主机名`;
REVOKE 权限列表 ON 数据库名 表名 FROM `用户名`@`主机名`;     //删除用户权限

函数

(1)常用字符串函数

--将工号左补0，补为6位。
UPDATE 表名 SET WORKNO=LPAD(WORKNO,6,'0'); 

(2)常用数值函数

--生成一个4位随机验证码
SELECT LPAD(ROUND(RAND(),4)*10000,4,'0');

(3)常用日期函数

---
--取出天数
SELECT DAY(CURDATE());
---65天后的日期
SELECT DATEADD(CURDATE(),INTERVAL 65 DAY);
--各员工入职时间,降序排列
SELECT NAME DATEDIFF(CURDATE(),entrydate) AS DAYS FROM EMP ORDER BY DAYS DESC;

(4)常用过程函数

--判断是否为NULL，不是返回default（包括""）;
IFNULL(null,default);
--判断武林门派是否为一流大派
SELECT (CASE COMPAY WHEN "武当派" THEN "一流大派" WHEN "少林派" THEN "一流大派" ELSE "二流势力" END) AS "势力" FROM EMP;
--判断是否为一流高手，还是二流高手
SELECT (CASE  WHEN KONGFUSCORE>=90 THEN "一流高手" WHEN KONGFUSCORE>=70 and KONGFUSCORE<=90 THEN "二流高手" ELSE "不入流" END) AS "武力值" FROM EMP;

约束

约束常见类型：

根据下列需求建表

CREATE TABLE emp{
id int PRIMARY KEY AUTO_INCREMENT,
name varchar(10) NOT NULL UNIQUE,
age int CHECK(age>=0&&age<=120),
status char(1) DEFAULT 1,
gender char(1) 
}

外键约束

外键用来让两张表之间的数据建立联系，从而保证数据的一致性和完整性。

员工表中的dept_id关联部门表中的主键 id ，在关系中员工表被称为子表，部门表被称为父表。将dept_id设为外键指向 id后，如果部门表发生了变化，员工表也会发生变化。
添加外键的两种方法

ALTER TABLE emp  ADD CONSTRAINT fk_emp_dept_id foreign key (dept_id) REFERENCES dept(id);
CREATE TABLE  emp{
...
dept id int,
CONSTRAINT fk_emp_dept_id foreign key (dept_id) REFERENCES dept(id)
} 
--删除外键
ALTER TABLE emp DROP FOREIGN KEY fk_emp_dept_id;

外键删除更新行为

外键删除更新行为有如下五种

设置外键的删除更新行为

多表查询

多表关系

多表关系分为三种：一对多，多对多，一对一
一对多：学生与班级的关系，多个学生对应一个班级
实现：在多的一方建立主键指向一的一方的主键
多对多：学生与课程的关系，一个学生可以选多个课程，一个课程可以被多个学生选
实现：建立第三张表，第三张表至少包含两个外键，分别关联两方的主键。
一对一：学生班级情况与学生选课情况，多用于单表拆分，分为两张表，提升操作效率
实现：在任意一方设置外键指向另一方主键，外键要设为唯一（UNIQUE）

内连接

查询两张表交集的部分
查询语句

SELECT emp.name,dept.name FROM emp,dept WHERE emp.dept_id=dept.id;
SELECT emp.name,dept.name FROM emp INNER JOIN dept ON emp.dept_id=dept.id;

外连接

左外连接
查询两张表左表所有数据，包含两张表交集的部分
右外连接
查询两张表右表所有数据，包含两张表交集的部分

SELECT emp.name,dept.name FROM emp LEFT OUTER JOIN dept ON emp.dept_id=dept.id;

SELECT emp.name,dept.name FROM emp RIGHT OUTER JOIN dept ON emp.dept_id=dept.id;

自连接

语法：

查询员工及其领导的姓名

SELECT a.name,b.name FROM emp a LEFT OUTER JOIN  emp b ON a.manger_id=b.id;

union

将两个表的查询结果汇总，UNION ALL 结果包含重复的，UNION 不包含重复的。

子查询

子查询分类

（1）标量子查询
子查询的结果是单个值，这种子查询为标量子查询，最简单的子查询。
（2）列查询
列查询的结果是一列，这种子查询为列子查询
常用的操作符有IN NOTIN ANY SONE ALL

查询销售部和市场部所有员工信息

select * from emp where dept_id in (select id from dept where name = '销售部'or name = '市场部');

查询比财务部员工工资都高的员工信息

select * from emp where salary > all ( select salary from emp where dept id = (select id from dept where name
='财务部')):

查询比研发部其中任意一人工资高的员工信息

select *from emp where salary > some
(select salary from emp where dept_id = (select id from dept where name ='硏发部'));

行子查询

行子查询的结果是一行，这种子查询为行子查询
常用的操作符有 = ，!= ，IN ，NOT IN
查询与“张无忌”的薪资及直属领导相同的员工信息;

select * from emp where (salary,managerid)= (select salary, managerid from emp where name = '张无忌');

表子查询

表查询的结果是多行多列，这种子查询为表查询
常用的操作符有 in
查询与“鹿杖客”，“宋远桥”的职位和薪资相同的员工信息

select * from emp where (job,salary) in ( select job, salary from emp where name ='鹿杖客'or name ='宋远桥’);

查询入职日期是“2006-01-01”之后的员工信息，及其部门信息

select e.*，d.* from(select*from emp where entrydate >'2006-01-01') e left join dept d on e.dept_id = d.id ;

事务

事务简介

事务 是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作
请求，即这些操作要么同时成功，要么同时失败。

事务操作

--方式1：
--检查当前事务
SELECT @@AUTOCOMMIT;
--设置手动管理事务
SET @@AUTOCOMMIT =0;
--提交事务
COMMIT;
--回滚
ROLLBACK;
--方式二：
--开启手动事务
START TRANSACTION/BEGIN;
--提交事务
COMMIT;
--回滚
ROLLBACK;

事务四大特性

并发事务问题

事务隔离级别

--查看事务隔离级别
SELECT @@TRANSACTION_ISOLATION;
--设置事务隔离级别
SET SESSION/GLOBAL TRANSACTION ISOLATION LEVEL 事务级别；

不同事务级别的演示