mysql语法汇总

创建表

CREATE TABLE tb_device(
device_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键（非空 + 唯一），自增长',
device_name VARCHAR(255),
device_num INT,
curr_vers VARCHAR(255),
issue_vers VARCHAR(255),
ecurr_vers VARCHAR(255),
eissue_vers VARCHAR(255),
down_time VARCHAR(255),
create_time VARCHAR(255)
);

删除表

DROP TABLE tb_device

修改表

ALTER TABLE <table_name> ADD | DROP | DROP | RENAME | CHANGE

#添加列
ALTER TABLE tb_device ADD test_column INT NOT NULL;

#删除列
ALTER TABLE tb_device DROP test_column;

#修改表名
ALTER TABLE tb_device2 RENAME tb_device;

#修改列
ALTER TABLE tb_device CHANGE device_num device_number VARCHAR(255) NOT NULL DEFAULT 'default';

插入

INSERT INTO <tabe_name> (column_name<,..>) VALUES(value<,..>)

# 插入部分字段
INSERT INTO tb_device (device_name,device_number,
curr_vers,issue_vers,eissue_vers,down_time,create_time)
 VALUES('test_name','3','20000','001','123','2020/2/2','2020/9/2');
# 插入字段全部
INSERT INTO tb_device VALUES(123,'test_name','3','20000','001','123','123','2020/2/2','2020/9/2');

删除

DELETE FROM <table_name> [<WHERE> ...]

# 条件删除
DELETE FROM tb_device WHERE device_id = 123;
# 删除全部
DELETE FROM tb_device ;

更新

UPDATE <table_name> SET <column_name> = <val>[, <column_name> = <val> ]
WHERE <exp>

UPDATE tb_device SET down_time = date_format(now(),'%Y-%m-%d'), device_name = 'test_name' 
WHERE device_id = 3;

排序

ORDER BY <column_name>

SELECT create_time FROM tb_device ORDER BY create_time;

分组

GROUP BY <column_name>
》 一般用于统计数目

# 统计issue_vers中各版本数目
SELECT issue_vers '版本', count(issue_vers) '数目' FROM tb_device GROUP BY issue_vers

分组条件HAVING

# 部门表
create table dept(
id int primary key,
deptName varchar(20)
)
--员工表（副表/从表）
create table employee(
id int ,
deptId int,
constraint bydept foreign key(deptId) references dept(id)
)

# 查询人数大于1的部门
SELECT d.deptName '部门名称', count(e.id) '人数' FROM dept d INNER JOIN employee e ON e.deptId = d.id
GROUP BY e.deptId HAVING count(e.id) > 1

去重复DISTINCT

其他字段中有不同值依然会显示

SELECT DISTINCT c.id , c.chenji, c.kemu  FROM test_chenji c;

自连接查询

#将自身当作一张表，获取t1表id为3的记录，向表t2查询t2.number < t1.number 
SELECT t2.* FROM tb_device t1, tb_device t2 WHERE t1.device_id = 3 AND t2.device_number < t1.device_number;

子查询

IN语句

IN 操作符允许我们在 WHERE 子句中规定多个值
尽量避免使用IN/NOT IN

# 将角色权限表的结果集作为查询权限表的条件，筛选出指定角色的所有权限
select a.* from tb_auth a where a.auth_id in (select ra.auth_id from tb_role_auth ra where ra.role_id =  3)

ANY

内连接

#满足条件才会显示
create table dept(
id int primary key,
deptName varchar(20)
)
--员工表（副表/从表）
create table employee(
id int ,
deptId int,
constraint bydept foreign key(deptId) references dept(id)
)

#员工表和部门表内连接查询，查询条件为员工表职务id等于部门id
SELECT e.id, d.deptName FROM employee e INNER JOIN  dept d ON e.deptId = d.id;

左连接

ALTER TABLE employee ADD name VARCHAR(20);
INSERT INTO employee (name) VALUES ('zhangsan');

#左表完全显示,当前左表为部门表
SELECT d.deptName '部门', e.id '员工号', e.name '姓名' FROM dept d LEFT JOIN  employee e ON e.deptId = d.id;

右连接

SELECT e.id '员工号', e.name '姓名',d.deptName '部门' FROM dept d RIGHT JOIN  employee e ON e.deptId = d.id;