mysql查询分类语法_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 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 (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 [ ...]

# 条件删除

DELETE FROM tb_device WHERE device_id = 123;

# 删除全部

DELETE FROM tb_device ;

更新

UPDATE SET = [, = ]

WHERE

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

WHERE device_id = 3;

排序

ORDER BY

SELECT create_time FROM tb_device ORDER BY create_time;

分组

GROUP BY

》 一般用于统计数目

# 统计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;