11. MySQL语法大全（精要整理）
1、MySQL数据类型
- 整数类型
- 浮点数、定点数类型
- 日期、时间类型
- 字符串类型
- TEXT 系列字符串类型
- BINARY 系列字符串类型
- BLOB 系列字符串类型
2、表操作
-2.1 创建表：
创建表： create table blog_user ( user_Name varchar(15) not null, user_Password varchar(15) not null, user_emial varchar(20) not null unique, primary key(user_Name) )engine=innodb default charset=utf8;
-2.2 describe 查看表的定义
describe usersinfo;
desc usersinfo;
-2.3 show create table 查看表详细定义
show create table usersinfo;
-2.4 修改表名
alter table old_table_name rename new_table_name;
-2.5 增加字段（在表的最后一个位置增加字段）
alter table table_name add 属性名 属性类型
例如 alter table usersinfo add xsy varchar(20);
-2.6 增加字段（在表的第一个位置增加字段）
alter table table_name add 属性名 属性类型 first;
例如 alter table usersinfo add xsy varchar(20) first;
-2.7 增加字段（在表的指定字段后增加字段）
alter table table_name add 属性名 属性类型 after 属性名;
例如 alter table usersinfo add xsy1 varchar(20) after xsy;
alter table test1.ref_bond_info add bondtype1_code varchar(40) CHARACTER SET utf8mb4 COMMENT '债券一级编码' after bond_type ;
alter table test1.ref_bond_info add bondtype1_name varchar(100) CHARACTER SET utf8mb4 COMMENT '债券一级名称' after bondtype1_code;
alter table test1.ref_bond_info add bondtype2_code varchar(40) CHARACTER SET utf8mb4 COMMENT '债券二级编码' after bondtype1_name;
alter table test1.ref_bond_info add bondtype2_name varchar(100) CHARACTER SET utf8mb4 COMMENT '债券二级名称' after bondtype2_code;
-2.8 删除字段 alter table table_name drop 属性名
alter table usersinfo drop xsy;
-2.9 修改字段 alter table table_name modify 属性名 数据类型
alter table usersinfo modify xsy varchar(30);
-2.8 修改字段名（不修改数据类型）
alter table table_name change 旧属性名 新属性名 旧数据类型
-2.9 修改字段名（修改数据类型）
alter table table_name change 旧属性名 新属性名 新数据类型
-3.0 批量修改数据库中某一字段的值
SELECT table_name, CONCAT('alter table ', table_name, ' modify SubOrderNo varchar(30) not null comment \'子订单号\';') FROM information_schema.columns WHERE column_name = 'SubOrderNo';
alter table bankcardinfo modify SubOrderNo varchar(30) not null comment '子订单号';
alter table cardeviceinfo modify SubOrderNo varchar(30) not null comment '子订单号';
alter table cardriverinfo modify SubOrderNo varchar(30) not null comment '子订单号';
alter table carengageinfo modify SubOrderNo varchar(30) not null comment '子订单号';
alter table carfeerateinfo modify SubOrderNo varchar(30) not null comment '子订单号';
alter table carinfo modify SubOrderNo varchar(30) not null comment '子订单号';
alter table carkindinfo modify SubOrderNo varchar(30) not null comment '子订单号';
alter table carpeccancyinfo modify SubOrderNo varchar(30) not null comment '子订单号';
alter table carproposalmain modify SubOrderNo varchar(30) not null comment '子订单号';
alter table carriskiteminfo modify SubOrderNo varchar(30) not null comment '子订单号';
alter table carshiptaxinfo modify SubOrderNo varchar(30) not null comment '子订单号';
alter table carshiptaxpreinfo modify SubOrderNo varchar(30) not null comment '子订单号';
alter table claiminfo modify SubOrderNo varchar(30) not null comment '子订单号';
alter table clwalletpayinfo modify SubOrderNo varchar(30) not null comment '子订单号';
alter table customerinfo modify SubOrderNo varchar(30) not null comment '子订单号';
alter table dutyinfo modify SubOrderNo varchar(30) not null comment '子订单号';
alter table fcarkindinfo modify SubOrderNo varchar(30) not null comment '子订单号';
alter table insuranceordersub modify SubOrderNo varchar(30) not null comment '子订单号';
alter table orderapprovalinfo modify SubOrderNo varchar(30) not null comment '子订单号';
alter table orderdeliverinfo modify SubOrderNo varchar(30) not null comment '子订单号';
alter table orderquetwoinfo modify SubOrderNo varchar(30) not null comment '子订单号';
alter table orderviewsinfo modify SubOrderNo varchar(30) not null comment '子订单号';
alter table proposalmain modify SubOrderNo varchar(30) not null comment '子订单号';
alter table quotemain modify SubOrderNo varchar(30) not null comment '子订单号';
alter table riskinfo modify SubOrderNo varchar(30) not null comment '子订单号';
alter table unionpayorderinfo modify SubOrderNo varchar(30) not null comment '子订单号'
3、MySQL中查询所有数据库占用磁盘空间大小和单个库中所有表的大小的sql语句
-3.1 查询所有数据库占用磁盘空间大小的SQL语句
select table_schema,concat(truncate(sum(data_length)/1024/1024, 2), ' MB') as data_size, concat(truncate(sum(index_length)/1024/1024, 2), ' MB') as index_size from information_schema.tables les group by table_schema order by data_size desc;
-3.2 查询单个库中所有表磁盘占用大小的SQL语句
select table_name, concat(truncate(data_length/1024/1024,2),' MB') as data_size, concat(truncate(index_length/1024/1024,2),' MB') as index_size from information_schema.tables where table_schema = 'imove' group by table_name order by data_size desc;
4、视图
- 为什么要使用视图
为了提高复杂SQL语句的复用性和表操作的安全性，MySQL提供了视图特性。
所谓视图，本质上是一种虚拟表，其内容与真实的表相似，包含一系列带有名称的列和行数据。但是视图并不在数据库以存储的数据值形式存在。行和列数据来自定义视图的查询所引用的基本表，并且在具体引用视图时动态生成。
视图使程序员只关心感兴趣的某些特定的数据和他们负责的特定任务。这样程序员只能看到视图中所定义的数据，而不是视图所引用表中的数据，从而提高了数据库中数据的安全性。
-4.1 基本表
select * from versiondata;
-4.2 创建视图
create view view_versiondata as (select id, mobiletype, mobileversion from versiondata);
- #查询视图 （基本表数据改变，视图对应数据改变）
select * from view_versiondata;
- 视图特点
1. 视图的列可以来自不同的表，是表的抽象和在逻辑意义上建立的新关系
2. 视图是基本表（实表）产生的表（虚表）
3. 视图的建立和删除不影响基本表
4. 对视图内容的更新（添加、删除、修改）直接影响基本表
5、索引
创建单体索引：
ALTER TABLE `usersinfo` ADD INDEX index_mobile (`mobile`);
ALTER TABLE `usersinfo` ADD INDEX index_usercode (`usercode`);
ALTER TABLE `usersinfo` ADD INDEX index_id (`id`);
-》数据库表：usersinfo；索引名：index_mobile、index_usercode、index_id
创建组合索引：
ALTER TABLE `Userpushinforelation` ADD INDEX index_id_terminalSystem (`id`,`terminalSystem`);
-》数据库表：Userpushinforelation；索引名：index_id_terminalSystem
查询索引：
show index from usersinfo;
-》数据库表：usersinfo
6、MySQL查询数据库表数量
SELECT COUNT( * ) FROM information_schema.tables WHERE TABLE_SCHEMA = 'manage';
7、MySQL其他语法汇集
1.sql语句中保留一位小数： CAST(cur_FUND_RATE/max(REPLACE(ladderValue,'%',''))*10 AS decimal(5,1)) 2. 将 数据库中的 datatime 转换成 字符串格式 如将 2012-09-27 16:57:50 转换成 20120927： date_format(createdate,'%Y%m%d')>='20120918'实现。 3.mysql 中sql语句中datetime日期格式：timestamp('2015-04-01 14:33:00') 4.mysql中sql语句获得今天的凌晨的时间：timestamp(date(sysdate())) 5.mysql中sql语句获得第二天凌晨的时间：timestamp(adddate(date(sysdate()),1)) 6.mysql中sql语句获得第二天相同的时间：DATE_ADD( NOW() , INTERVAL 1 DAY) 7.将Int 转为varchar经常用 concat函数，比如concat(8,’0′) 得到字符串 ’80′ 8.将varchar 转为Int 用 cast(a as signed) a为varchar类型的字符串 9.将varchar 转为Decimal 用 cast(a as decimal(8,4)) a为varchar类型的字符串 10.将一个字段中的“%”去掉：REPLACE(ladderValue,'%','') 11.case when 语句： CASE WHEN f.cumulativeNet = '0' then '————' WHEN f.cumulativeNet is null THEN '————' else f.cumulativeNet end 12.在数据表中新增字段： ALTER TABLE funddeployinfo（数据表名） ADD createTime（字段名） datetime（字段类型） COMMENT '字段描述' AFTER fundDeployType（添加在本字段之后） 例如： ALTER TABLE `healthinformation` ADD COLUMN `contenttype` varchar(2) COMMENT '健康资讯内容类型(01:H5链接;02:自定义)' after `imgurl`; 13.创建索引：CREATE [UNIQUE] INDEX index_name ON table_name(cols,...) 删除索引：DROP INDEX index_name; #CREATE INDEX index_name ON FFundProductyield (fund_code,tradedate,yieldType); 索引不可更改，需要更改必须删除重新创建 14.创建视图：CREATE VIEW view_name AS SELECT statement; 删除视图：DROP VIEW view_name; 15.随机取出10条记录： 16.MySql数据库操作基础： #显示数据库 show databases; #判断是否存在数据库wpj1105,有的话先删除 drop database if exists wpj1105; #创建数据库 create database wpj1105; #删除数据库 drop database wpj1105; #使用该数据库 use wpj1105; #显示数据库中的表 show tables; #先判断表是否存在,存在先删除 drop table if exists student #修改表的名字 #格式:alter table tbl_name rename to new_name #修改表中某个字段的名字 alter table tablename change columnname newcolumnname type; #修改一个表的字段名 #表position 增加列test alter table position add(test char(10));
#表position 修改列test alter table position modify test char(20) not null;
#表position 修改列test 默认值 alter table position alter test set default 'system';
#表position 去掉test 默认值 alter table position alter test drop default;
#表position 去掉列test alter table position drop column test;
#表depart_pos 删除主键 alter table depart_pos drop primary key;
#表depart_pos 增加主键 alter table depart_pos add primary key PK_depart_pos (department_id,position_id); 查看Mysql表结构的命令，如下: desc 表名; show columns from 表名; describe 表名; show create table 表名; use information_schema select * from columns where table_name='表名'; 顺便记下： show databases; use 数据库名; show tables; 修改字段属性： -- 修改字段属性 -- ALTER TABLE tb_name MODIFY 字段名称 字段类型 [完整性约束条件] -- 将email字段 VARCHAR(50)修改成VARCHAR(200) -- 注意，修改时如果不带完整性约束条件，原有的约束条件将丢失，如果想保留修改时就得带上完整性约束条件 ALTER TABLE user10 MODIFY email VARCHAR(200) NOT NULL DEFAULT 'a@a.com'; -- 将card移到test后面 ALTER TABLE user10 MODIFY card CHAR(10) AFTER test; -- 将test放到第一个，保留原完整性约束条件 ALTER TABLE user10 MODIFY test CHAR(32) NOT NULL DEFAULT '123' FIRST; 修改字段名称和属性： -- 将test字段改为test1 -- ALTER TABLE 表名 CHANGE 原字段名 新字段名 字段类型 约束条件 ALTER TABLE user10 CHANGE test test1 CHAR(32) NOT NULL DEFAULT '123'; 添加删除默认值： -- 创建新表 CREATE TABLE user11( id TINYINT UNSIGNED KEY AUTO_INCREMENT, username VARCHAR(20) NOT NULL UNIQUE, age TINYINT UNSIGNED ); -- 给age添加默认值 ALTER TABLE user11 ALTER age SET DEFAUTL 18; -- 添加一个字段 ALTER TABLE user11 ADD email VARCHAR(50); -- 给email添加默认值 ALTER TABLE user11 ALTER email SET DEFAULT 'a@a.com'; -- 删除默认值 ALTER TABLE user11 ALTER age DROP DEFAULT; ALTER TABLE user11 ALTER email DROP DEFAULT; 更多详情，请参见： https://www.cnblogs.com/huangxm/p/5736807.html
添加主键：
-- 创建一个表 CREATE TABLE test12( id INT ); -- 添加主键 -- ALTER TABLE tb_name ADD [CONSTRAINT [sysmbol]] PRIMARY KEY [index_type] (字段名称,...) ALTER TABLE test12 ADD PRIMARY KEY(id); -- 添加复合主键 -- 先创建个表 CREATE TABLE test13( id INT, card CHAR(18), username VARCHAR(20) NOT NULL ); -- 添加复合主键 ALTER TABLE test13 ADD PRIMARY KEY(id,card);
删除主键：
-- 删除主键 ALTER TABLE test12 DROP PRIMARY KEY; -- 再给test12添加主键, 完整形式 ALTER TABLE test12 ADD CONSTRAINT symbol PRIMARY KEY index_type(id);
在删除主键时，有一种情况是需要注意的，我们知道具有自增长的属性的字段必须是主键，如果表里的主键是具有自增长属性的；那么直接删除是会报错的。如果想要删除主键的话，可以先去年自增长属性，再删除主键
-- 再创建一个表， CREATE TABLE test14( id INT UNSIGNED KEY AUTO_INCREMENT ); -- 删除主键，这样会报错，因为自增长的必须是主键 ALTER TABLE test14 DROP PRIMARY KEY; -- 先用MODIFY删除自增长属性，注意MODIFY不能去掉主键属性 ALTER TABLE test14 MODIFY id INT UNSIGNED; -- 再来删除主键 ALTER TABLE test14 DROP PRIMARY KEY;
唯一索引：
-- 添加唯一性约束 -- ALTER TABLE tb_name ADD [CONSTANT [symbol]] UNIQUE [INDEX | KEY] [索引名称](字段名称,...) -- 创建测试表 CREATE TABLE user12( id TINYINT UNSIGNED KEY AUTO_INCREMENT, username VARCHAR(20) NOT NULL, card CHAR(18) NOT NULL, test VARCHAR(20) NOT NULL, test1 CHAR(32) NOT NULL ); -- username添加唯一性约束，如果没有指定索引名称，系统会以字段名建立索引 ALTER TABLE user12 ADD UNIQUE(username); -- car添加唯一性约束 ALTER TABLE user12 ADD CONSTRAINT symbol UNIQUE KEY uni_card(card); -- 查看索引 SHOW CREATE TABLE user12; -- test,test1添加联合unique ALTER TABLE user12 ADD CONSTRAINT symbol UNIQUE INDEX mulUni_test_test1(test, test1); -- 删除唯一 -- ALTER TABLE tb_name DROP {INDEX|KEY} index_name; -- 删除刚刚添加的唯一索引 ALTER TABLE user12 DROP INDEX username; ALTER TABLE user12 DROP KEY uni_card; ALTER TABLE user12 DROP KEY mulUni_test_test1;
修改表的存储引擎：
-- 修改表的存储引擎 -- ALTER TABLE tb_name ENGINE=存储引擎名称 ALTER TABLE user12 ENGINE=MyISAM; ALTER TABLE user12 ENGINE=INNODB;
修改自增长值：
-- 修改自增长的值 -- ALTER TABLE tb_name AUTO_INCREMENT=值 ALTER TABLE user12 AUTO_INCREMENT=100;
浙公网安备 33010602011771号