MYSQL中常用语言

 /*创建数据库*/
CREATE DATABASE z_testOcean

 /*删除数据库*/
DROP  DATABASE z_testOcean

/*创建新表*/
CREATE TABLE z_table_1
    (
    `Id` INT(10) NOT NULL PRIMARY KEY,
    `FirstName` VARCHAR(255) NOT NULL ,
    `CrTime` DATETIME NOT NULL
    )
DROP TABLE  `z_testocean`.`z_table_1`

/*comment:评论，意见*/
CREATE TABLE `z_testocean`.`z_staff_info`
    (
    `Id` INT(10) NOT NULL PRIMARY KEY COMMENT'#主键',
    `Name` VARCHAR(128) NOT NULL COMMENT'姓名' ,
    `Sex` VARCHAR(16) NOT NULL COMMENT'性别',
    `Age` VARCHAR(16) NOT NULL COMMENT'年龄',
    `Salaly` INT(10) NOT NULL COMMENT '工资'    
    )
    
/*插入单条数据*/
INSERT INTO z_staff_info(`Id`, `Name`, `Sex`, `Age`, `Salaly`) VALUES(1001,'小张','男','35',6500)
/*插入多条数据*/
INSERT INTO z_staff_info(`Id`, `Name`, `Sex`, `Age`, `Salaly`) VALUES(1005,'小张','男','35',6500),(1002,'小li','男','45',8500),(1003,'小hong','女','35',4500),(1004,'小张','女','35',6800)

INSERT INTO z_staff_info(`Id`, `Name`, `Sex`, `Age`, `Salaly`) VALUES(1006,'徐','男','23',6500)
/*更改数据*/
UPDATE     z_staff_info SET `Name`='小徐' ,`Salaly`=6400 WHERE `Id`=1005    

/*查询数据*/
SELECT *FROM z_staff_info
SELECT DISTINCT `Age` FROM z_staff_info
SELECT * FROM z_staff_info WHERE Salaly > 3000
SELECT * FROM z_staff_info WHERE Salaly > 3000 AND Age = '男'
SELECT * FROM z_staff_info WHERE Salaly > 3000 AND Age = '男' ORDER BY Id DESC  --降序排序
SELECT * FROM z_staff_info WHERE Salaly > 3000 AND Age = '女' ORDER BY Id ASC   --升序排序
SELECT * FROM z_staff_info WHERE Salaly > 3000 AND `Name` LIKE '%hong%'

/*删除某条数据*/
DELETE FROM z_staff_info WHERE `Name`='徐'

/*新建一列*/
ALTER TABLE `z_testocean`.`z_staff_info` ADD COLUMN `Post` VARCHAR(128) NULL COMMENT'职称' AFTER `Salaly`

/*将列Id的设置为自增，AUTO_INCREMENT*/
ALTER TABLE `z_testocean`.`z_table_2`  CHANGE `Id` `Id` INT(10) NOT NULL AUTO_INCREMENT;

UPDATE z_staff_info SET `Post`='工程师' WHERE `Name`='小li';
UPDATE z_staff_info SET `Post`='会计' WHERE `Name`='小hong'

/*不常用查询*/
SELECT * FROM z_staff_info  WHERE `Post` IS NULL
SELECT * FROM z_staff_info  WHERE `Post` IS NOT NULL
SELECT * FROM z_staff_info  WHERE  Age<>"女"

SELECT MAX(`Salaly`) FROM z_staff_info;
SELECT MIN(`Salaly`) FROM z_staff_info;
SELECT * FROM z_staff_info WHERE `Salaly` > ( SELECT AVG(`Salaly`)  FROM z_staff_info )
SELECT COUNT(`Salaly`) FROM z_staff_info WHERE `Salaly` > ( SELECT AVG(`Salaly`)  FROM z_staff_info )

ALTER TABLE `z_testocean`.`z_staff_info`   
  ADD COLUMN `Post` VARCHAR(128) NULL   COMMENT '职称' AFTER `Salaly`;

/*删除多张表，表之间用,分割*/
DROP  TABLE `z_table_test1` ,`z_table_test`

/*查询（information_schema数据库里面的）tables表中所有的自增ID：*/
/*多个数据库下所有有自增的*/
SELECT AUTO_INCREMENT FROM information_schema.`TABLES`;

/*查询指定表自增的Id*/
SELECT AUTO_INCREMENT FROM information_schema.`TABLES` WHERE table_name='z_table_2';

/*查询指定数据库，指定表名下的自增Id*/
SELECT AUTO_INCREMENT FROM information_schema.`TABLES` WHERE table_schema='z_testocean' AND table_name='z_table_2';


/*得到SQL语句删除表*/
SELECT CONCAT( 'drop table ', table_name, ';' ) FROM information_schema.tables WHERE table_schema='z_testocean' AND  table_name LIKE '%table%';
CONCAT( 'drop table ', table_name, ';' )
DROP TABLE z_table_test1;
DROP TABLE z_table_test2;

/*SQL删除多个表的数据*/
/*删除一个从两个数据表。不能在一个多表DELETE语句中使用ORDER BY或LIMIT*/
DELETE `z_staff_info` FROM `z_staff_info`, `z_table_test1` WHERE z_staff_info.Id=z_table_test1.Id ;
/*删除两个三个数据表。*/
DELETE t1, t2 FROM t1, t2, t3 WHERE t1.id=t2.id AND t2.id=t3.id;

DELETE FROM t1, t2 USING t1, t2, t3 WHERE t1.id=t2.id AND t2.id=t3.id;
DELETE a, b FROM a , b WHERE (a.id = b.cat_id) AND a.id=？；

/*使用join删除两个数据表中的数据，LEFT要删除的数据库，如下：*/
DELETE a,b FROM  a LEFT JOIN  b ON a.id = b.cat_id WHERE a.id=6; 
DELETE `z_staff_info`,`z_table_test1` FROM  z_staff_info LEFT JOIN  z_table_test1 ON  z_table_test1.`FirstName`=z_staff_info.`Name`  WHERE z_staff_info.`Name`='小hong'; 

/*遍历a张表，若有a表的名字在b表中有则删除b表数据*/
DELETE `z_staff_info`,`z_table_test1` FROM  z_staff_info LEFT JOIN  z_table_test1 ON  z_table_test1.`FirstName`=z_staff_info.`Name`  WHERE (SELECT )
/*当你引用表名时，必须使用引用名如：*/
DELETE `z_staff_info`,`z_table_test1` FROM category `z_staff_info left` JOIN article `z_table_test1` ON `z_staff_info`.`Name` = z_table_test1.`FirstName` WHERE z_staff_info.`Name`='小hong'

/*表T1中有个t2_id字段，想将关联表T2中对应的字段内容转移到t1中来*/
UPDATE t1 LEFT JOIN t2 ON t1.t2_id = t2.id  SET  t1.name=t2.name,t1.phone=t2.phone WHERE t1.t2_id>0;

/*根据给定的一些无规则id批量修改数据*/
UPDATE Table_name SET Age=0 WHERE id IN(1000,1003);

#每分钟内的平均值
SELECT DATE_FORMAT(FROM_UNIXTIME(`time`),'%Y-%m-%d %H:%i') AS TIME,AVG(tx_kb) FROM traffic WHERE machine_id=1234 GROUP BY DATE_FORMAT(FROM_UNIXTIME(`time`),'%Y-%m-%d %H:%i');
#每小时内的平均值
SELECT DATE_FORMAT(FROM_UNIXTIME(`time`),'%Y-%m-%d %H') AS TIME,AVG(tx_kb) FROM traffic WHERE machine_id=1234 GROUP BY DATE_FORMAT(FROM_UNIXTIME(`time`),'%Y-%m-%d %H');

#一对多关系分组统计
SELECT `z_staff_info`.`Id`,`z_staff_info`.`Name`,COUNT(`z_table_2`.`Id`) AS user_num FROM z_staff_info,z_table_2 WHERE z_staff_info.id=z_table_2.id GROUP BY z_staff_info.id;

#按日期分类统计用户
SELECT FROM_UNIXTIME(`date`,'%Y-%m-%d') days,COUNT(id) num FROM `z_staff_info` GROUP BY `date` ORDER BY `date` DESC;

#迁移表字段数据
INSERT INTO  user2(`u_id`,`name`) SELECT `id`,`name`  FROM USER;
INSERT  INTO z_table_test2(Id,class, FirstName,Gread,crTime) SELECT * FROM `z_table_test1`

#复制一张表

#仅仅表结构
CREATE TABLE  数据表名 LIKE 源数据表}
CREATE TABLE  z_staff_info_copy LIKE z_staff_info;
#仅仅表结构和数据
CREATE TABLE  数据表名 AS SELECT * FROM 源数据表名;
CREATE TABLE  z_staff_info_copy1 AS SELECT * FROM z_staff_info;

#正则表达式

#查找name字段中以'st'为开头的所有数据：
SELECT NAME FROM z_staff_info_copy1 WHERE NAME REGEXP '^st';

#查找name字段中以'ok'为结尾的所有数据：
SELECT NAME FROM z_staff_info_copy1 WHERE NAME REGEXP 'ok$';

#查找name字段中包含'mar'字符串的所有数据：
SELECT NAME FROM z_staff_info_copy1 WHERE NAME REGEXP 'mar';

#查找name字段中以元音字符开头或以'ok'字符串结尾的所有数据：
SELECT NAME FROM z_staff_info_copy1 WHERE NAME REGEXP '^[aeiou]|ok$';

#查找post中含有2或3的数据
SELECT *FROM `z_staff_info_copy1` WHERE   `Post` REGEXP '[23]'

#alter
#删除date列但若表中只有一个字段无法使用drop删除
ALTER TABLE z_staff_info_copy1  DROP `date`;
ALTER TABLE  z_staff_info_copy1 ADD COLUMN `i` INT(10) NOT NULL COMMENT '随便添加一列' AFTER `crdate`

#新增一列在首列
ALTER TABLE z_staff_info_copy1 ADD COLUMN `First_list`  INT (10) NOT NULL  COMMENT '自增' FIRST

#修改字段名字或名称 以在ALTER命令中使用 MODIFY 或 CHANGE 子句
ALTER TABLE z_staff_info_copy1 MODIFY Age  CHAR(10)

#使用 CHANGE 子句, 语法有很大的不同。 在 CHANGE 关键字之后，紧跟着的是你要修改的字段名，然后指定新字段的类型及名称
ALTER TABLE z_staff_info_copy1 CHANGE  j Age INT(10);
ALTER TABLE z_staff_info_copy1 MODIFY  Age INT(10) COMMENT '年龄';

#仅仅修改字段类型
ALTER TABLE z_staff_info_copy1 CHANGE j j INT;
ALTER TABLE z_staff_info_copy1  CHANGE Age1 Age INT(10) NOT NULL  COMMENT '年龄' ;

#alter设置默认值
ALTER TABLE z_staff_info_copy1 MODIFY Age INT(10) NOT NULL DEFAULT 100;

#修改默认值
ALTER TABLE z_staff_info_copy1 ALTER Age SET DEFAULT 1000;
SHOW COLUMNS FROM z_staff_info_copy1;

#删除默认值
ALTER TABLE z_staff_info_copy1 ALTER Age DROP DEFAULT;

#修改数据表类型
ALTER TABLE z_staff_info_copy1 TYPE = MYISAM;
#查看数据表类型
SHOW TABLE STATUS LIKE 'z_staff_info_copy1'

#修改表名
ALTER TABLE z_staff_info_copy1 RENAME TO z_staff_info_copy2;
#ALTER 命令还可以用来创建及删除MySQL数据表的索引


#索引
#创建索引，如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。
CREATE INDEX indexName ON z_staff_info_copy1(username(LENGTH));

#修改表结构添加索引
ALTER TABLE tableName ADD INDEX indexName(columnName)
#表创建时自动添加索引
CREATE TABLE mytable(  
 
ID INT NOT NULL COMMENT 'Id',   
 
username VARCHAR(16) NOT NULL COMMENT '用户名',  
 
INDEX [indexName] (username(LENGTH))  
 
);  
#删除索引的语法
DROP INDEX [indexName] ON mytable; 
DROP INDEX IX_ModuleId ON  jewelry_dress_disboard_log
DROP INDEX IX_ModuleId ,IX_CrTime ON  `jewelry_get_log`
ALTER TABLE `jewelry_get_log` DROP INDEX `IX_ModuleId`,
DROP INDEX `IX_CrTime`;
SHOW INDEX FROM `jewelry_get_log`

#唯一索引
#它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：
#创建索引
CREATE UNIQUE INDEX indexName ON mytable(username(LENGTH))

#修改表结构
ALTER TABLE mytable ADD UNIQUE [indexName] (username(LENGTH))
#创建表的时候直接指定
CREATE TABLE mytable(  
 
ID INT NOT NULL,   
 
username VARCHAR(16) NOT NULL,  
 
UNIQUE [indexName] (username(LENGTH))  
 
);  

#有四种方式来添加数据表的索引 unique独立的  fulltext全文索引
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): #该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list):# 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
ALTER TABLE tbl_name ADD INDEX index_name (column_list):# 添加普通索引，索引值可出现多次。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):#该语句指定了索引为 FULLTEXT ，用于全文索引。

#添加或删除索引
ALTER TABLE testalter_tbl ADD INDEX 索引名 (列组合);
ALTER TABLE testalter_tbl DROP INDEX c;

#添加主键，删除主键 
ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;
ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);
ALTER TABLE testalter_tbl DROP PRIMARY KEY;

#显示索引信息，过添加 \G 来格式化输出信息
SHOW INDEX FROM z_staff_info_copy ;

CREATE TEMPORARY TABLE SalesSummary (
    product_name VARCHAR(50) NOT NULL
    total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00
    avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00
    total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
)
INSERT INTO SalesSummary (product_name, total_sales, avg_unit_price, total_units_sold) VALUES ('cucumber', 100.25, 90, 2);
SELECT * FROM SalesSummary;

DROP TABLE 命令来手动删除临时表。
DROP TABLE SalesSummary;


#元数据
#查询结果信息： SELECT, UPDATE 或 DELETE语句影响的记录数。
#数据库和数据表的信息： 包含了数据库及数据表的结构信息。
#MySQL服务器信息： 包含了数据库服务器的当前状态，版本号等
SHOW TABLES 或 SHOW DATABASES 语句来获取数据库和数据表列表。

#AUTO_increment  每张表中唯一
#创建时添加(必须指定为主键)
CREATE  TABLE insect(
 Id INT(10)   NOT NULL AUTO_INCREMENT ,
 PRIMARY KEY (Id),
`Type` INT(10) NOT NULL COMMENT '类型',
`Name` VARCHAR(128) NOT NULL COMMENT '名字',
Origin VARCHAR(128) NOT NULL COMMENT '数据源'

);
DROP TABLE insect

#在MySQL的客户端中你可以使用 SQL中的LAST_INSERT_ID( ) 函数来获取最后的插入表中的自增列的值。        
LAST_INSERT_ID( ) 
#删除自增
ALTER TABLE insect DROP Id;

ALTER TABLE insect ADD Id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (id);

#一般情况下序列的开始值为1，但如果你需要指定一个开始值100    
CREATE  TABLE insect(
Id INT(10) NOT NULL AUTO_INCREMENT =100 ,
PRIMARY KEY(Id),
`Type` INT(10) NOT NULL COMMENT '类型',
`Name` VARCHAR(128) NOT NULL COMMENT '名字',
Origin VARCHAR(128) NOT NULL COMMENT '数据源',
)

#修改主键
ALTER TABLE insect AUTO_INCREMENT = 100;    
    
#ignore 忽略(主键)
INSERT IGNORE INTO tableName() VALUES ()#INSERT IGNORE会忽略数据库中已经存在的数据，如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据

#PRIMARY KEY（主键） 或者 UNIQUE（唯一） 索引来保证数据的唯一性。
#REPLACE INTO如果存在primary 或 unique相同的记录，则先删除掉。再插入新记录。
#查询重复
SELECT user_name,COUNT(*) AS `count` FROM user_table GROUP BY user_name HAVING COUNT>1;
SELECT * FROM people WHERE peopleId IN (SELECT peopleId FROM people GROUP BY peopleId HAVING COUNT(peopleId) > 1) 

#统计重复数据
SELECT COUNT(*) AS repetitions, last_name, first_name FROM person_tbl GROUP BY last_name, first_name HAVING repetitions > 1;

/*请执行以下操作：
确定哪一列包含的值可能会重复。
在列选择列表使用COUNT(*)列出的那些列。
在GROUP BY子句中列出的列。
HAVING子句设置重复数大于1。*/
#过滤重复数据 distinct
SELECT  DISTINCT list1 ,list2   FROM tableName ORDER BY last_name;
SELECT last_name, first_name FROM person_tbl GROUP BY (last_name, first_name);

#删除重复数据
CREATE TABLE tmp AS SELECT last_name, first_name, sex FROM person_tbl GROUP BY (last_name, first_name);
DROP TABLE person_tbl;
ALTER TABLE tmp RENAME TO person_tbl;

ALTER IGNORE TABLE person_tbl ADD PRIMARY KEY (last_name, first_name);

#导出SQL
SELECT...INTO OUTFILE

#以下实例中我们将数据表 tutorials_tbl 数据导出到 /tmp/tutorials.txt 文件中:
SELECT * FROM `z_staff_info_copy1` INTO OUTFILE "C:\Users\moqikaka\Desktop\SNACKS\a12\13.txt";


# DATE_ADD
#(date,INTERVAL expr type) date 参数是合法的日期表达式。expr 参数是您希望添加的时间间隔。 type 类型
 SELECT Id,DATE_ADD(CrDate,INTERVAL 30 MINUTE) AS OrderPayDate FROM `z_staff_info_copy1`

#DATE_SUB() 函数从日期减去指定的时间间隔
#DATE_SUB(date,INTERVAL expr type)
SELECT Id  ,DATE_SUB(CrDate,INTERVAL 30 DAY) AS `data` FROM  `z_staff_info_copy1` WHERE Id<1004

#DATEDIFF() 函数返回两个日期之间的天数。
#DATEDIFF(date1,date2)
ALTER TABLE  z_staff_info_copy1 ADD  COLUMN `Date` DATETIME NOT NULL COMMENT '时间' 
UPDATE    z_staff_info_copy1  SET `date`='2019-12-27 17:28:02'
SELECT DATEDIFF('2008-11-29','2008-11-30') AS DiffDate
SELECT DATEDIFF(`Date`,CrDate) AS DiffDate FROM  `z_staff_info_copy1` 

#DATE_FORMAT() 函数用于以不同的格式显示日期/时间数据
# DATE_FORMAT(date,format)
SELECT  DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p') AS list1,
DATE_FORMAT(NOW(),'%m-%d-%Y') AS list2,
DATE_FORMAT(NOW(),'%d %b %y') AS list3,
DATE_FORMAT(NOW(),'%d %b %Y %T:%f') AS list4

#NOW() 返回当前的日期和时间。
 SELECT NOW(),CURDATE(),CURTIME()
CREATE TABLE Orders
 (
 OrderId INT NOT NULL,
 ProductName VARCHAR(50) NOT NULL,
 OrderDate DATETIME NOT NULL DEFAULT CURDATE(),#mysql5.5不支持
 PRIMARY KEY (OrderId),
 )
 
 #DATE() 函数提取日期或日期/时间表达式的日期部分。
 #DATE(date)
SELECT  DATE(crdate) AS outdata FROM z_staff_info_copy1
 
 #EXTRACT() 函数用于返回日期/时间的单独部分，比如年、月、日、小时、分钟等等
 # EXTRACT(unit FROM date)
 SELECT EXTRACT(YEAR FROM OrderDate) AS OrderYear,
 EXTRACT(MONTH FROM OrderDate) AS OrderMonth,
 EXTRACT(DAY FROM OrderDate) AS OrderDay,
 FROM Orders
 WHERE OrderId=1 
 
 #CONCAT(str1,str2,…)  
 #返回结果为连接参数产生的字符串。如有任何一个参数为NULL ，则返回值为 NULL。
 #如果所有参数均为非二进制字符串，则结果为非二进制字符串。 
 #如果自变量中含有任一二进制字符串，则结果为一个二进制字符串。
 #一个数字参数被转化为与之相等的二进制字符串格式；若要避免这种情况，可使用显式类型 cast, 
SELECT CONCAT(CAST(int_col AS CHAR), char_col)
#concat连接多个字符串
SELECT CONCAT('10');
SELECT CONCAT('11','22','33');
#连接字符时只要一个为null就为null
SELECT CONCAT('11','22',NULL);
#concat_ws 函数
CONCAT_WS(SEPARATOR,str1,str2,...)
#CONCAT_WS() 代表 CONCAT With Separator ，是CONCAT()的特殊形式。第一个参数是其它参数的分隔符。分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串，也可以是其它参数。
#,号分割
CONCAT_WS(',','11','22','33')
#concat_ws函数在执行的时候,不会因为NULL值而返回NULL
CONCAT_WS(';','11','22',NULL)
#得到 11;22
#group_concat 函数
#group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符']),,,逗号分隔(默认)
SELECT id,GROUP_CONCAT(`name`) FROM tablt_name GROUP BY id;#以id分组，把name字段的值打印在一行
SELECT id,GROUP_CONCAT(DISTINCT ·name·) FROM tablt_name GROUP BY id;  #以id分组，把不同的name字段的值打印在一行
SELECT id,GROUP_CONCAT(`name` ORDER BY `name` DESC) FROM tablt_name GROUP BY id;

#repeat()函数
SELECT REPEAT('ab',2);#用来复制字符串,如下'ab'表示要复制的字符串，2表示复制的份数
#mysql向表中某字段后追加一段字符串:
UPDATE table_name SET FIELD=CONCAT(`field`,'',str)
#mysql 向表中某字段前加字符串
UPDATE table_name SET FIELD=CONCAT('str',`field`)

#subString 字符串截取函数
left（str, length） #说明：left（被截取字段，截取长度） 
SELECT LEFT(content,200) AS abstract FROM my_content_t 

#right（str, length） #说明：right（被截取字段，截取长度） 
#截取字符串 
substring（str, pos） #说明：substring（被截取字段，从第几位开始截取） 
substring（str, pos, length） #substring（被截取字段，从第几位开始截取，截取长度） 

substring_index（str,delim,count） #说明：substring_index（被截取字段，关键字，关键字出现的次数） 


#所有的数学函数在发生错误的情况下，均返回 NULL
ABS(X)返回 X 的绝对值：
SIGN(X)以 -1、0 或 1 方式返回参数的符号，它取决于参数 X 是负数、0 或正数。
MOD(N,M)% 取模 (就如 C 中的 % 操作符)。返回 N 被 M 除后的余数：
FLOOR(X)返回不大于 X 的最大整数值：
CEILING(X)返回不小于 X 的最小整数：
ROUND(X,D)将参数 X 四舍五入到最近的整数，然后返回。两个参数的形式是将一个数字四舍五入到 D 个小数后返回。
DIV整除。类似于 FLOOR()，但是它可安全地用于 BIGINT 值。

#MySQL数据库查询带有某个字段的所有表名：
#（1）精确查询语句如下：
SELECT * FROM information_schema.columns WHERE column_name='JewelryModelID';
#（2）模糊匹配查询

SELECT * FROM information_schema.columns WHERE column_name LIKE '%column_name%';

#查询数据库里面的所用表名
SELECT table_name FROM information_schema.tables WHERE table_schema='z_testocean'
#先选中要查的数据库，使用show tables 查看数据库里面的数据表
SHOW TABLES;
# 显示表结构 describe 可简写
DESCRIBE jewelry_get_log;
DESC jewelry_get_log;
#用文本方式将数据装入数据库，如存在 “D:/mysql.txt”
LOAD DATA LOCAL INFILE “D:/mysql.txt” INTO TABLE jewelry_get_log;
#导入.sql文件命令（例如D:/mysql.sql），再数据库操作平台上
USE DATABASE;
source d:/mysql.sql;
#使用SHOW语句找出在服务器上当前存在什么数据库
SHOW DATABASES;

 

 

 

#数据表重命名
ALTER TABLE a_text_student RENAME a_text_2

 /*不加条件的话，修改整列数据*/   
UPDATE a_text_2 SET getUpdate='2019/06/24'             
SELECT *FROM a_text_2

#创建数据表
CREATE TABLE a_text (
Id INT AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR (128),
born DATETIME 
);
SELECT *FROM a_text

#查询表结构
DESCRIBE a_text

INSERT INTO a_text VALUES(1,'冉小雅','1994/02/05'),(2,'冉光','2019/02/00'),(3,'冉小雅','1995/02/05'),(4,'冉同','1994/02/05');
SELECT *FROM a_text

/*独特的distinct*/
SELECT DISTINCT `name` FROM a_text

#使用concat连接 重命名
SELECT CONCAT(Id,'++',`name`,'--',add_list) 'Id和name的组合值' FROM a_text;
SELECT CONCAT ('学号',Id,':' ,'姓名' ,`name`)FROM a_text/*concat 组合*/

#多条select 联合查询
SELECT country FROM Websites 
UNION
SELECT country FROM apps
ORDER BY country;

/*union 联合查询两个表的所有Id数据，不包括重复的值*/
SELECT Id FROM a_text UNION  SELECT  Id FROM a_text_2 ORDER BY Id;  

/*union 联合查询两个表的所有Id数据，重复的值也罗列出来*/
SELECT Id FROM a_text UNION ALL SELECT  Id FROM a_text_2 ORDER BY Id;
SELECT Id FROM a_text_2 UNION ALL SELECT  Id FROM a_text ORDER BY  Id DESC 
SELECT Id description FROM a_text_2 WHERE Id<=3 UNION  ALL SELECT Id `name` FROM a_text WHERE Id<=3

/*外键约束标志，来禁用外键约束.外键一般是两张表，主表不用外键，重表才用外键*/
SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
--  Table structure for `employee_tbl`
-- ----------------------------
DROP TABLE IF EXISTS `a_employee_tbl`;
CREATE TABLE `a_employee_tbl` (
  `id` INT(11) NOT NULL,
  `name` CHAR(10) NOT NULL DEFAULT '',
  `date` DATETIME NOT NULL,
  `singin` TINYINT(4) NOT NULL DEFAULT '0' COMMENT '登录次数',
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;


#事务，一般提交commit 或rollback
BEGIN;
INSERT INTO `a_employee_tbl` VALUES ('1', '小明', '2016-04-22 15:25:33', '1'), ('2', '小王', '2016-04-20 15:25:47', '3'), ('3', '小丽', '2016-04-19 15:26:02', '2'), ('4', '小王', '2016-04-07 15:26:14', '4'), ('5', '小明', '2016-04-11 15:26:40', '4'), ('6', '小明', '2016-04-04 15:26:54', '2');
END
COMMIT;

#启用外键约束
SET FOREIGN_KEY_CHECKS = 1;

SELECT NAME, COUNT(*) FROM   a_employee_tbl GROUP BY NAME;
SELECT NAME, SUM(singin) AS singin_count FROM  a_employee_tbl GROUP BY NAME WITH ROLLUP;
SELECT COALESCE(NAME, '总数'), SUM(singin) AS singin_count FROM  a_employee_tbl GROUP BY NAME WITH ROLLUP;/*COALESCE(a,b,c),有a则选择a,没有选择b...都没有返回空*/


#SELECT, UPDATE 和 DELETE 语句中使用 Mysql 的 JOIN 来联合多表查询。

/* alter命令*/
USE `newdzz_model_develop_ocean`;
DROP TABLE  a_employee_tbl;
CREATE TABLE testalter_tbl
     (
     i INT,
    c CHAR(1)
     );
 
 /*横向显示表单*/   
SHOW COLUMNS FROM testalter_tbl;
SELECT *FROM testalter_tbl;
ALTER  TABLE  testalter_tbl DROP i ;/*删除表单的i列*/
/*如果数据表中只剩余一个字段则无法使用DROP来删除字段。*/
ALTER TABLE testalter_tbl ADD (i INT, j INT);/*ADD 添加多列， FIRST和AFTER会控制新增位置*/
SHOW COLUMNS FROM testalter_tbl;

ALTER TABLE testalter_tbl MODIFY c CHAR(10);/*MODIFY 修改字段类型*/
ALTER TABLE testalter_tbl RENAME  tbl;/*rename 修改表名*/
ALTER TABLE  a_text RENAME a_text_1;
ALTER TABLE  a_text_1 RENAME TO a_text;
    
    
-- 使用 CHANGE 子句, 语法有很大的不同。 在 CHANGE 关键字之后，紧跟着的是你要修改的字段名，然后指定新字段名及类型。--
ALTER TABLE testalter_tbl CHANGE i j BIGINT;/*CHANGE 修改字段名*/
ALTER TABLE testalter_tbl CHANGE j j INT;

ALTER TABLE testalter_tbl  MODIFY j BIGINT NOT NULL DEFAULT 100;/*modify修改j字段类型，并设置默认值 default(不到场，不履行)*/

-- 如果你不设置默认值，MySQL会自动设置该字段默认为 NULL。 --

ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;/*修改字段默认值，关键字alter 和set */
SHOW COLUMNS FROM testalter_tbl;

ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;/*删除字段默认值，关键字alter 和drop */


ALTER TABLE testalter_tbl ENGINE = MYISAM;/*修改存储引擎：修改为myisam 。alter table tableName engine=myisam;*/
SHOW TABLE STATUS   LIKE  'a_text'\G

SHOW TABLE STATUS LIKE  'a_text'\G

ALTER TABLE tableName DROP FOREIGN KEY keyName;/*删除外键约束：keyName是外键别名*/

ALTER TABLE tableName MODIFY name1 type1 FIRST|AFTER name2;/*修改字段的相对位置：这里name1为想要修改的字段，type1为该字段原来类型，first和after二选一，这应该显而易见，first放在第一位，after放在name2字段后面*/

 -- 索引--
 -- 创建表的时候直接指定 --
CREATE TABLE a_mytable(  
ID INT NOT NULL DEFAULT 10,   
username VARCHAR(16) NOT NULL DEFAULT '小王',  
INDEX [indexName] (username(LENGTH))  
); 
 CREATE INDEX indexName ON a_mytable(username(LENGTH)); /*CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。*/
 ALTER TABLE a_mytable ADD INDEX indexName(columnName);/*修改表结构(添加索引)*/
 DROP INDEX [indexName] ON a_mytable;  /*删除索引的语法*/

-- 复制表--
-- InnoDB 提供了事务控制能力功能

-- 方法一： 创建一个和原表数据类型一样的附表。使用insert into 依次插入数据

INSERT INTO clone_tbl (runoob_id,runoob_title,runoob_author,submission_date)SELECT runoob_id,runoob_title,runoob_author,submission_date FROM runoob_tbl;--设置附表为InNoDB形式
-- 方法二 ：
CREATE TABLE targetTable LIKE sourceTable;
INSERT INTO targetTable SELECT * FROM sourceTable;

-- 方法二 之复制部分字段：
CREATE TABLE newadmin AS
(
    SELECT username, PASSWORD FROM admin
)
-- 可以将新建的表的字段改名:
CREATE TABLE newadmin AS
(  
    SELECT id, username AS uname, PASSWORD AS pass FROM admin
)

-- 可以拷贝一部分数据:
CREATE TABLE newadmin AS
(
    SELECT * FROM admin WHERE LEFT(username,1) = 's'
)

-- 可以在创建表的同时定义表中的字段信息:
CREATE TABLE newadmin
(
    id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY
)
AS
(
    SELECT * FROM admin
)  

-- 获取数据库元数据-- 
SELECT USER( ) -- 当前用户名
SELECT VERSION( ) -- 获取服务器版本信息
SELECT DATABASE( ) -- 当前数据库名 (或者返回空)
SHOW STATUS  -- 当前服务器状态
SHOW VARIABLES  -- 当前服务器状态值

-- 导出数据
SELECT * FROM runoob_tbl INTO OUTFILE '/tmp/runoob.txt';