MySQL命令大全(基础)

title: MySQL命令大全(基础)
tags:
  - Linux
  - MySQL
categories:
  - 工具
top_img: false
cover: '/upload/cdn0files/20200721121449.jpg'
copyright: false
abbrlink: 75a9f85f
date: 2020-01-02 15:03:40
updated: 2020-01-02 15:03:40

基础命令

创建数据库

CREATE DATABASE dbname;

删除数据库

DROP DATABASE dbname;

创建新表

CREATE TABLE tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..);

根据已有的表创建新表:

CREATE TABLE tab_new LIKE tab_old;		//使用旧表创建新表
CREATE TABLE tab_new AS SELECT col1,col2… FROM tab_old DEFINITION ONLY;

删除表

DROP TABLE tabname;

增加一个列

注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度

ALTER TABLE tabname ADD COLUMN COL TYPE;

添加和删除主键

ALTER TABLE tabname ADD PRIMARY KEY(col);	添加
ALTER TABLE tabname DROP PRIMARY KEY(col);	删除

创建和删除索引

索引是不可更改的，想更改必须删除重新建

CREATE [unique] INDEX idxname ON tabname(col….);	创建
DROP INDEX idxname;		删除

创建和删除视图

CREATE VIEW viewname AS SELECT statement;	创建
DROP VIEW viewname;		删除

修改数据库名称

SP_RENAMEDB 'old_name', 'new_name';

简单的基本的SQL语句

选择

SELECT * FROM table1 WHERE 范围;

插入

INSERT INTO table1(field1,field2) VALUES(value1,value2);

删除

DELETE FROM table1 WHERE 范围更新：UPDATE table1 SET field1=value1 WHERE 范围;

查找

SELECT * FROM table1 WHERE field1 LIKE ’%value1%’;

排序

SELECT * FROM table1 ORDER BY field1,field2 [desc];

总数

SELECT COUNT AS totalcount FROM table1;

求和

SELECT SUM(field1) AS SUMVALUE FROM table1;

平均

SELECT AVG(field1) AS AVGVALUE FROM table1;

最大

SELECT MAX(field1) AS MAXVALUE FROM table1;

最小

SELECT MIN(field1) AS MINVALUE FROM table1;

进阶技巧

子查询(表名1：a 表名2：b)

SELECT a,b,c FROM a WHERE a IN (select d from b );	或者
SELECT a,b,c FROM a WHERE a IN (1,2,3);

外连接查询(表名1：a 表名2：b)

SELECT a.a, a.b, a.c, b.c, b.d, b.f FROM a LEFT OUT JOIN b ON a.a = b.c;

在线视图查询(表名1：a )

SELECT * FROM (SELECT a,b,c FROM a) T WHERE t.a > 1;

between的用法

between限制查询数据范围时包括了边界值,not between不包括

SELECT * FROM table1 WHERE time BETWEEN time1 AND time2;
SELECT a,b,c, FROM table1 WHERE a NOT BETWEEN 数值1 and 数值2;

in的使用方法

SELECT * FROM table1 WHERE a [not] IN ('值1','值2','值4','值6');

四表联查问题

SELECT * FROM a LEFT INNER JOIN b ON a.a=b.b RIGHT INNER JOIN c ON a.a=c.c INNER JOIN d ON a.a=d.d WHERE .....

前10条记录

SELECT TOP 10 * FORM table1 WHERE 范围;

随机取出10条记录

SELECT TOP 10 * FROM tablename ORDER BY NEWID();

列出数据库里所有的表名

SELECT name FROM sysobjects WHERE type='U';   //U代表用户

列出表里的所有的列名

SELECT name FROM syscolumns WHERE id=object_id('TableName');

初始化表table1

TRUNCATE TABLE table1;

选择从10到15的记录

SELECT TOP 5 * FROM (SELECT TOP 15 * FROM table ORDER BY id asc) table_别名 ORDER BY id desc;