Mysql常用命令

emp_id是别名，别名可以用双引号圈起来，也可以不写。也可以加AS。

SELECT employ_id （AS） emp_id ，salary*12 AS "annual sal" FROM employees;
SELECT DISTINCT department_id FROM employees;//去重了
SELECT DISTINCT department_id,salary FROM employees;//(√
SELECT * FROM 'ORDER';//着重号''，不会和原有关键字冲突
SELECT * FROM employees WHERE last_name='KING';#mysql这个king也是不区分大小写的

DESCRIBE employees;#显示了表中字段详细信息
DESC employees;#同上

比较运算符

0='A';//true 字符串存在隐式转换，如果转换数值不成功，为0
'a'='b';//false 两边都是字符串，按ASCLL进行比较
NULL=NULL;//NULL
NULL <=> NULL//true <=>为NULL而生
SELECT last_name FROM employees WHERE commision = NULL;//找不到任何数据，因为返回NULL
SELECT last_name FROM employees WHERE commision <=> NULL;//成功找到NULL的数据
SELECT last_name FROM employees WHERE commision IS (NOT) NULL;//常规写法

SELECT LEAST('G','B','T') FROM employees;//挑最小的
SELECT GREATEST('G','B','T') FROM employees;//挑最大的
SELECT ... FROM ... WHERE ...BETWEEN...AND...//范围内
SELECT .............WHERE department_id IN (10,20,30);
SELECT .............WHERE last_name LIKE '%a%';//%代表不确定个数字符，（0个，1个，多个）
SELECT..............WHERE last_name LIKE '_a%';//_代表一个不确定的字符。第二个字符是a的名字。
SELECT..............WHERE last_name LIKE '_\_a%';//\是转义字符，这样第二个字符是下划线，第三个字符是a

排序
ASC:升序（默认）
DESC:降序
列的别名只能在ORDER BY使用，不能在WHERE使用
WHERE需要在FROM后，ORDER BY前

SELECT employee_id,last_name,salary FROM employees ORDER BY salary DESC;
SELECT employee_id,salary,department_id FROM employees ORDER BY department_id DESC,salary ASC;

分页–SELECT
需求：每页显示pagesize条记录，此时显示第pageNo页
公式：LIMIT (pageNo-1)*pageSize,pageSize
特殊时，LIMIT 0,条目数=LIMIT 条目数

SELECT employee_id,last_name
FROM employees
LIMIT 0,20;//0:偏移量，20：一页20个数据。0从0开始，20从20开始
#WHERE ORDER BY LIMIT声明顺序
SELECT employee_id,last_name,salary
FROM employees
Where salary>6000
ORDERBY salary DESC
LIMIT 0,10; 
#mysql8.0新特性
SELECT employee_id,last_name,salary
FROM employees
LIMIT 31，2;
#等价于（↓新特性）
SELECT employee_id,last_name,salary
FROM employees
LIMIT 2 OFFSET 31;
#↑从31开始显示两条，和正常LIMIT的是颠倒的

多表查询
笛卡尔积：

SELECT employee_id,department_name
FROM employees,departments;
#等同于
SELECT employee_id,department_name
FROM employees CROSS JOIN departments;
#会给每个

正确的多表连接条件
关联查询
如果有n个表实现多表查询，则至少需要n-1个连接条件

SELECT employee_id,department_name
FROM employee,departments
WHERE employees.'department_id'=departments.'department_id';
#如果查询语句中出现了多个表中都存在的字段，则必须指明此字段所在的表
SELECT employees.employee_id,departments.department_name,employees,department_id
FROM employees,departments
WHERE employees.'department_id'=departments.'departments_id';
#如果给表取了别名（只能在FROM中），一旦在SELECT或者WHERE中使用表名的话，必须使用表的别名，而不能使用表的原名
#非等值连接
SELECT last_name,salary,grade_level
FROM employees e,job_grades j
WHERE e.salary BETWEEN j.lowest_sal and j.highest_sal;
#查询员工id，员工姓名以及管理者的id和姓名
SELECT emp.employee_id,emp.last_name,mgr.employee_id,mgr.last_name
FROM employees emp,employees mgr
WHERE emp.manager_id=mgr.employee_id;

内连接：合并具有同一列的两个以上表的行，结果集中不包含一个表与另一个表不匹配的行
外连接：还查询到了左表（左外连接）或右表（右外连接）不匹配的行

#SQL99内连接
SELECT employee_id,department_name
FROM employees e JOIN departments d
ON e.department_id=d.department_id;
#SQL99外连接
SELECT employee_id,department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.department_id=d.department_id;
#mysql不支持满外连接，只能自己写union
#UNION：去重
#UNION ALL:不去重
SELECT employee_id,department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.department_id=d.department_id
WHERE e.department_id IS NULL;//A交（！B）
#要外连接，只能两个SELECT UNION在一起
SELECT employee_id,department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.department_id=d.department_id
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.department_id=d.department_id
WHERE e.department_id IS NULL;//A交（！B）

#LPAD：实现右对齐
SELECT employee_id,last_name,LPAD(salary,10,' ')#10位，不足用空格补齐
FROM employees;

GROUP BY
有两个的时候 先按department_id分类，然后按job_id分类（这个看具体的，同一部门下同一工作）
SELECT出现的必须出现在GROUP BY

SELECT department_id,job_id,AVG(salary)#顺序颠倒也不影响执行
FROM employees
GROUP BY job_id,department_id;
#HAVING：过滤数据。如果过滤条件中使用----聚合函数----，则必须使用HAVING替换WHERE否则报错。HAVING必须放在GROUP BY的后面。但是若不是聚合函数，建议使用在WHERE中，效率比较高。
SELECT department_id,MAX(salary)
FROM employees
GROUP BY deparment_id;
HAVING MAX(salary)>10000；

数据库的管理

USE atiguigudb;//切换到这个数据库
SHOW DATABASES
SHOW TABLES
#查看当前使用的数据库
SELECT DATABASE() FROM DAUL;
#查看指定数据库下保存的数据表
SHOW TABLES FROM mysql；
#修改数据库
#更改字符集
ALTER DATABASE mytest2 CHARACTER SET 'utf8';
#数据库不可改名
#删除数据库
DROP DATABASE (IF EXISTS) mytest1;

对表操作：

CREATE TABLE IF NOT EXISTS myemp1(
id INT,
emp_name VARCHAR(15),
hire_date DATE
);
DESC myempl;//查看表结构
CREATE TABLE myemp2
AS
SELECT employee_id,last_name,salary
FROM employees;
#不包括表数据的
CREATE TABLE employees_blank
AS
SELECT *
FROM employees
WHERE 1=2;//确定没有的数据
#修改表
##添加一个字段
ALTER TABLE myemp1
ADD salary DOUBLE(10,2);//10位.2位
ALTER TABLE myemp1
ADD phone_number VARCHAR(20) FIRST;
ALTER TABLE myemp1
ADD email VARCHAR(45) AFTER emp_name
##修改一个字段
ALTER TABLE myemp1
MODIFY emp_name VACHAR(25);
##重命名字段
ALTER TABLE myemp1
CHANGE salary monthly_salary DOUBLE(10,2);
##删除一个字段
ALTER TABLE myemp1
DROP COLUMN email;
#重命名表
RENAME TABLE myeqmp1
TO myemp11;
ALTER TABLE myemp2
RENAME TO myemp12;
#删除表
#删除表结构、数据、释放表空间
DROP TABLE IF EXISTS myemp2;
#清空表，清空数据，但表结构保留
TRUNCATE TABLE employees_copy;

COMMIT和ROLLBACK
DML（data manipulation language）是数据操纵语言：它们是SELECT、UPDATE、INSERT、DELETE，就象它的名字一样，这4条命令是用来对数据库里的数据进行操作的语言。

DDL（data definition language）是数据定义语言：DDL比DML要多，主要的命令有CREATE、ALTER、DROP等，DDL主要是用在定义或改变表（TABLE）的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用。

#COMMIT提交数据，一旦执行，数据就被永久保存在数据库中，不可以回滚
#ROLLBACK：回滚到最近的一次COMMIT
#TRUNCATE TABLE/DELETE FROM
#TRUNCATE TABLE删除全部表数据，同时数据不可以回滚
#DELETE FROM：可以删除全部数据。同时数据可以实现回滚。
#DDL和DML的说明：DDL的操作一旦执行，就不可回滚。指令SET autocommit=FALSE对DDL操作时效
#DML的操作默认情况，一旦执行，也是不可回滚的，但是如果执行DML之前执行SET autocommit=FALSE，则执行的DML操作就可以实现回滚

添加数据

INSERT INTO emp1
VALUES(1,'TOM','2000-01-21',3400)；
INSERT INTO emp1(id,hire_date,salary,'name')#指明需要添加的字段
VALUES(1,'TOM','2000-01-21',3400)；
INSERT INTO emp1(id,NAME,salary,hire_Date)
SELECT employee_id,last_name,salary,hire_date#查询字段一定要和添加字段一一对应，字段长度要注意
FROM employees
WHERE depart ment_id IN(70,60);

更新数据

#UPDATE...SET...WHERE...
UPDATE emp1
SET hire_Date=CURDATE(),salary=5000
WHERE id=5;

删除数据

DELETE FROM emp1
WHERE id=1;

约束
非空约束

CREATE TABLE test1(
id INT NOT NULL,
last_name VARCHAR(15),
email VARCHAR(25),
salary DECIMAL(10,2)
);
#增加约束
ALTER TABLE test1
MODIFY email VARCHAR(25) NOT NULL;
#删除约束
ALTER TABLE test1
MODIFY email VARCHAR(25) NULL;

约束
唯一性约束

CREATE TABLE test2(
id INT UNIQUE,
last_name VARCHAR(15),
email VARCHAR(25),
salary DECIMAL(10,2),
#表级约束
CONSTRAINT UK_test2_email UNIQUE(email);
)

ALTER TABLE test2
ADD CONSTRAINT uk_test2_sal UNIQUE(salary);

ALTER TABLE test2
MODIFY last_name VARCHAR(15) UNIQUE;
#复合唯一
CREATE TABLE student_course(
id INT,
sid INT,
score INT,
UNIQUE KEY(sid,cid)
)
#删除unique约束，需要用索引
ALTER TABLE test2 
DROP INDEX last_name;

主键约束

CREATE TABLE test4(
id  INT PRIMARY KEY,
last_name VARCHAR(15),
salary DECIMAL(10,2),
email VARCHAR(25)
);

CREATE TABLE test6(
id INT,
last_name VARCHAR(15),
salary DECIMAL(10),
email VARCHAR(25),
#这里我们就是添加了一个表级约束,同时一个复合约束,这里同时给id字段和last_name字段添加了主键约束
CONSTRAINT pk_test6_id_name PRIMARY KEY(id,last_name)
);
#增加
ALTER TABLE test6
ADD PRIMARY KEY(id);

自增长

CREATE TABLE test7(
id INT PRIMARY KEY AUTO_INCREMENT,
last_name VARCHAR(15)
);
#增加
ALTER TABLE test8
MODIFY id INT AUTO_INCREMENT;
#删除
ALTER TABLE test8
MODIFY id INT;

外键约束
最好采用 ON UPDATE CASCADE ON DELETE SET NULL

CREATE TABLE dept1(
dept_id INT,,
dept_name VARCHAR(15)
);
CREATE TABLE emp1(
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(15),
department_id INT,
CONSTRAINT fk_emp1_dept_id FOREIGN KEY (department_id) REFERENCES dept1(dept_id) ON UPDATE CASCADE ON DELETE SET NULL
);
#CASECADE 一起改
#SET NULL删除的时候从表set null
ALTER TABLE dept1
ADD PRIMARY KEY(dept_id);
#删除外键约束
ALTER TABLE emp1
DROP FOREIGN KEY fk_emp1_dept_id;
ALTER TABLE emp1
DROP INDEX fk emp1_dept_id;

CHECK约束，MYSQL8.0支持

CREATE TABLE test10(
id INT,
last_name VARCHAR(15),
salary DECIMAL(10,2) CHECK(salary>2000)
);

DEFAULT
默认值

CREATE TABLE test1(
id INT,
last_name VARCHAR(15),
salary DECIMAL(10,2) DEFAULT 2000
);
#添加约束
ALTER TABLE test12
MODIFY salary DECIMAL(8,2) DEFAULT 25000;
#删除
ALTER TABLE test12
MODIFY salary DECIMAL(8,2)；