MySQL数据类型
一、数值类型
注:DECIMAL中M+2为字节数。???
二、日期和时间类型
三、字符串类型
MySQL语句
1、创建数据库:create database 数据库名;
2、删除数据库:drop database 数据库名;
3、创建表
create table tableName(column_name column_type);
4、删除数据表
Drop table tableName;
5、插入数据
Insert into tableName (field1,field2,…fieldN)values(value1,value2,…valueN)
6、Where子句
操作符 = <> != > < >= <=
Where子句中字符串比较,不区分大小写。如果要区分大小写,可以用关键字BINARY。
Select * from customer where BINARY cust_name =‘Marry’
7、update更新
Update tableName set field1=value1,field2=value2。
[Where clause]
8、delete删除
Delete from tableName [where clause]
9、like子句
Select * from tableName where field1 like ‘%hello%’
Update tableName set field2=value2 Where field1 like ‘%hello%’
Delete tableName where field1 like ‘%hello%’
10、union操作符
Select field1,field2,…fieldN
From tableName1
[where conditions]
Union[ALL | DISTINCT]
Select field1,field2,…fieldN
From tableName2
[where conditions];
11、order by子句
12、group by子句
Select field1,function(field1)
From tableName
Group by field1;
使用with rollup可以实现在分组统计数据基础上再进行相同的统计(SUM,AVG,COUNT…)。
13、连接
(1)Inner join(内连接):获取两个表中字段匹配关系的记录。
(2)Left join(左连接):获取左表所有记录,即使右表没有对应匹配的记录。
(3)Right join(右连接):获取右表所有记录,即使左表没有对应匹配的记录。
- Mysql null值处理:is null
15、正则表达式
select * from staff where username REGEXP '^a'
\r是回车 \n是换行
16、ALTER
当我们需要修改数据库表名或者修改数据库表字段时,就需要使用到MySQL ALTER命令。
(1)删除,添加或修改表字段
ALTER TABLE tableName DROP aa;
如果数据表中只剩余一个字段则无法使用DROP来删除字段。
ALTER TABLE tableName ADD aa INT;
ALTER TABLE tableName ADD aa INT FIRST;
ALTER TABLE tableName ADD aa INT AFTER bb;
(2)修改字段类型及名称
ALTER TABLE tableName MODIFY aa CHAR(10);
ALTER TABLE tableName CHANGE aa bb BIGINT;
(3)ALTER TABLE对Null值和默认值的影响
ALTER TABLE tableName aa BIGINT NOT NULL DEFAULT 100;
ALTER TABLE tableName ALTER aa SET DEFAULT 1000;
ALTER TABLE tableName ALTER aa DROP DEFAULT;
(4)修改表名
ALTER TABLE tableName RENAME TO table_name2;
(5)删除外键约束
ALTER TABLE tableName DROP FOREIGN KEY keyname;
(6)修改存储引擎
ALTER TABLE tableName ENGINE=MYISAM。
(7)修改字段相对位置 aa为需要修改的字段名
ALTER TABLE tableName MODIFY aa type1 FIRST|AFTER bb;
Mysql事务
1、在mysql中只有使用了Innodb数据库或表才支持事务。
2、事务处理可以用来维护数据库的完整性,保证成批的SQL语句要么全部执行要么全部不执行。
3、事务用来管理insert,update,delete语句。
ACID:
- 原子性(Atomicty):一个事务中的所有操作,要么全部完成,要么全部不完成,不会结束在中间某个环节。事务在执行过程中发生错误,会被回滚(Rollback)到事务开始前的状态,就像这个事务从来没有执行过一样。
- 一致性(Consistency):在事务开始之前和事务结束之后,数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则,这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
- 隔离性(Isolation):数据库允许多个并发事务同时对其数据进行读写和修改,隔离性可以防止多个事务并发执行时由于交叉执行而导致数据不一致。事务隔离分为不同级别,包括未提交(Read uncommitted)、读提交(read committed)、可重复读(repeatable read)和串行化(Serializable)。
- 持久性(Durability):事务处理结束后,对数据的修改就是永久的,即便系统故障也不会丢失。
事务控制语句:
1、BEGIN或START TRANSACTION显式地开启一个事务。
2、COMMIT也可以使用COMMIT WORK,COMMIT会提交事务,并使已对数据库进行的所有修改成为永久性的。
3、ROLLBACK也可以使用ROLLBACK WORK,不过二者是等价的。回滚会结束用户的事务,并撤销正在进行的所有未提交的修改。
4、SAVEPOINT允许在事务中创建一个保存点,一个事务中可以有多个SAVEPOINT。
5、RELEASE SAVEPOINT删除一个事务的保存点,当没有指定的保存点时,执行该语句会抛出一个异常。
6、ROLLBACK TO把事务回滚到标记点。
7、SET TRANSACTION用来设置事务的隔离级别。
8、直接用SET来改变MySQL的自动提交模式:
SET AUTOCOMMIT=0禁止自动提交;
SET AUTOCOMMIT=1开启自动提交。
死锁事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
Mysql索引
- 普通索引
创建索引
- CREATE INDEX indexName on tableName(aa(length));
如果是CHAR,VARCHAR类型,length可以小于字段实际长度;如果是BLOB和TEXT类型,必须指定length。
- ALTER TABLE tableName ADD INDEX indexName(aa)
- CREATE TABLE tableName
(id INT NOT NULL,
name VARCHAR(16) NOT NULL,
INDEX [indexName] (name(length)
);
删除索引
DROP INDEX [indexName] ON tableName;
- 唯一索引:索引列必须唯一,但允许有空值
创建索引
- CREATE UNIQUE INDEX indexName ON tablename(aa(length))
- ALTER TABLE tablename ADD UNIQUE [indexName](aa(length))
- CREATE TABLE tableName
(id INT NOT NULL,
name VARCHAR(16) NOT NULL,
UNIQUE [indexName](name(length))
);
- 主键索引
ALTER TABLE tableName ADD PRIMARY KEY(aa)。
ALTER TABLE tableName DROP PRIMARY KEY;
- 全文索引
(1)ALTER TABLE tableName ADD FULLTEXT indexName(aa)。
(2)CREATE TABLE tableName
(id INT,
name VARCHAR(20),
FULLTEXT(name));
Innodb引擎下,临时表不能加全文索引。
显示索引信息
SHOW INDEX FROM tableName;
强制使用索引
SELECT * FROM table_name force index(index_name) WHERE condition
MySQL临时表
临时表只在当前连接可见,当关闭连接时,MySQL会自动删除表并释放所有空间。
CREATE TEMPORARY TABLE tableName
(id INT,
name VARCHAR(16)
);
DROP TABLE tableName;
Show tables不会列出临时表,information_schema中不会列出临时表,show create table 可以查看临时表
临时表分为2种,一种是内存临时表,一种是磁盘临时表。
磁盘临时表:
MySQL复制表
一、
- 使用SHOW CREATE TABLE获取创建数据表语句,该语句包含原数据表的结构,索引等。
- 复制SQL语句,修改数据表名。
- 复制表的内容,INSERT INTO … SELECT语句。
二、只复制表结构
CREATE TABLE 新表 SELECT * FROM 旧表 WHERE 1=2
或者
CREATE TABLE 新表 LIKE 旧表
三、复制表结构及数据
CREATE TABLE 新表 SELECT * FROM 旧表。
MySQL序列
创建序列
CREATE TABLE tableName
(id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(10)
);
重置序列
如果删除了数据表中的多条记录,并希望对剩下的AUTO_INCREMENT列进行重新排列,那么你可以通过删除自增列,然后重新添加来实现。
ALTER TABLE tableName DROP id;
ALTER TABLE tableName ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST;
设置序列的开始值
一般情况开始值为1
(1)CREATE TABLE tableName
(id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(10)
)ENGINE=innodb AUTO_INCREMENT=100 CHARSET=UTF8;
(2)ALTER TABLE tableName AUTO_INCREMENT=100;
MySQL处理重复数据
- 防止表中出现重复数据
- 添加主键PRIMARY KEY
(1)IGNORE 如果数据表中存在数据,则跳过这条数据;如果不存在数据,则插入。
INSERT IGNORE INTO tableName(aa) VALUES (1),(2),(3);
(2)REPLACE 如果数据表中存在数据,则先删除掉,再插入;如果不存在数据,则插入。
INSERT REPLACE INTO tableName(aa) VALUES (1),(2),(3);
-
- 添加UNIQUE索引
- 统计重复数据
SELECT COUNT(*) as repetitions
FROM tableName
GROUP BY id
HAVING repetitions > 1;
- 过滤重复数据
- SELECT DISTINCT id FROM tableName;
- SELECT id FROM tableName GROUP BY id;
MySQL内置函数
1、MySQL字符串函数
(1)CHAR_LENGTH(s)返回字符串s的字符数
SELECT CHAR_LENGTH(“RUNOOB”) AS LengthOfString;
(2)CONCAT(s1,s2,…sn)字符串s1,s2,等多个字符串合并为一个字符串
SELECT CONCAT(“aaa”,“bbb”,“ccc”);
- FIND_IN_SET(s1,s2)返回在字符串s2中与s1匹配的字符串的位置
SELECT FIND_IN_SET(“c”,“a,b,c,d”);
- FORMAT(x,n)将x保留n位小数,最后一位四舍五入
SELECT FORMAT(25000.5642,2)-----25000.56
- INSERT(s1,x,len,s2)字符串s2替换s1的x位置开始len长度的字符串
SELECT INSERT(“google.com”,1,6,“runnob”);---runnob.com
- LOCATE(s1,s)从字符串s中获取s1的开始位置
SELECT LOCATE(“st”,“myteststring”)----5
- LCASE(s)将字符串s的所有字母变成小写字母
SELECT LCASE(“RUNOOB”) ----runoob
- LEFT(s,n)返回字符串s的前n个字符
SELECT LEFT(“runoob”,2)----ru
- LOWER(s)将字符串s的所有字母变成小写字母
SELECT LOWER(“RUNOOB”)---runoob
- SUBSTRING(s,start,length)从字符串s的start位置截取长度为 length的子字符串
SELECT SUBSTRING(“RUNOOB”,2,3)---UNO
- TRIM(s)去掉字符串s开始和结尾处的空格
- SUBSTRING_INDEX(s,delim,count)delim分隔符。如果count>0,则从左取第count分隔符左边的内容,如果count<0,则从右取第count分隔符右边的内容。
s=www.hello.com
SUBSTRING_INDEX(s,’.’,2)---www.hello
2、Mysql数字函数
(1)ABS(s)返回x的绝对值
(2)AVG(s)返回一个字段的平均值
(3)COUNT(s)返回查询记录的总数,s为字段名或*
(4)MAX(s)返回字段的最大值
(5)MIN(s)返回字段的最小值
(6)SUM(s)返回字段的总和
(7)FLOOR(x)小于或者等于x的最大整数
3、MySQL日期函数
(1)ADDDATE(d,n)计算起始日期d加上n天的日期
SELECT ADDDATE(“2017-06-15”,INTERVAL 10 DAY);---2017-06-25
(2)CURRENT_DATE()返回会话时区中的当前日期
SELECT CURRENT_DATE()---2019-05-23
- CURRENT_TIME()返回会话时区中的当前时分秒
SELECT CURRENT_TIME() 19:59:02
- CURRENT_TIMESTAMP()返回会话时区中的当前时间
暂且认为current_timestamp()和now()是一样的
SELECT CURRENT_TIMESTAMP() ---2019-05-23 19:59:02
- DATE()从日期或日期时间表达式中提取日期值
SELECT DATE(“2017-06-15”) --- 2017-06-15
- DATEDIFF(d1,d2)计算d1和d2之间间隔的天数
SELECT DATEDIFF(“2001-02-02”,“2001-01-01”)—32
- DATE_FORMAT(d,f)按表达式f的要求显示日期d
SELECT DATE_FORMAT(“2019-05-23 12:05:23”,“%Y-%m-%d”)
---2019-05-23
- DATE_SUB(date,INTERVAL exprtype)函数从日期减去指定的时间间隔
SELECT DATE_SUB(“2019-05-23”,INTERVAL 2 DAY)
- DAY(d)返回日期值d的日期部分
HOUR(time)
MINUTE(time)
SECOND(time)
TIME(time)
MONTH(d)
SELECT DAY(“2019-05-12”) ---12
(11)DAYNAME(d)返回日期d是星期几
SELECT DAYNAME(“2011-11-11 12:49:23”)---FRIDAY
(12)DAYOFWEEK(d)返回d是星期几
1是星期日 2是星期一
SELECT DAYOFWEEK(“2011-11-11 12:49:23”)---6
WEEKDAY(d)返回d是星期几
0是星期一 1是星期2
SELECT WEEKDAY(“2011-11-11”)---4
(13)SYSDATE()返回服务器时间
4、MySQL高级函数
(1)CASE expression
WHEN condition1 THEN
result1
WHEN condition2 THEN
result2
…
WHEN conditionN THEN
resultN
ELSE result
END
当condition1成立,则返回result1,当condition2成立,则返回result2,若都没有成立,则返回resultN。当有一个成立,后面的不执行。
(2)CAST(x AS type)转换数据类型
SELECT CAST(“2017-08-12”AS DATE);---2017-08-12
(3)COALESCE(expr1,expr2…exprn)返回参数中的第一个非空表达式(从左向右)
SELECT COALESCE(NULL,NULL,“huawei”)---huawei
(4)CONNECTION_ID()返回服务器的连接数
(5)CURRENT_USER()返回当前用户
(6)DATABASE()返回当前数据库名
(7)IF(expr,v1,v2)如果表达式expr成立,返回v1,否则返回v2。
(8)IFNULL(v1,v2)如果v1值不为null,返回v1,否则返回v2
(9)SESSION_USER()返回当前用户
(10)VERSION()返回数据库版本号
MySQL运算符
- 算术运算符
- 比较运算符
- 逻辑运算符
- 位运算符
报错解决
- data truncated for column
调整字段长度
查询
show global status like 'open%tables%';
show variables like '%table_open_cache%';
修改
set global table_open_cache=3000;
set global table_definition_cache=3000;
创建视图
create or replace view venue_param as select * from venue.venue_param
为变量赋值
select a,b into @a,@b
查看数据库最大连接数:
show variables like 'max_connections';
查看当前访问mysql的线程:
show processlist
select * from information_schema.processlist两者相等
显示用户正在运行的线程,需要注意的是,除了root用户能看到所有正在运行的线程外,其他用户都只能看到自己正在运行的线程,看不到其他用户正在运行的线程,除非单独给这个用户赋予process权限。
ID:就是这个线程的唯一标识,当发现某个线程有问题时,可以通过kill命令,加上这个ID值将这个线程杀掉。
USER:启动这个线程的用户。
HOST:记录了发送请求的客户端的IP和端口号。通过这些信息在排查问题的时候,我们可以定位到哪个客户端的哪个进程发送的请求。
DB:当前执行的命令是在哪一个数据库上。如果没有指定数据库,则该值为null。
COMMAND:是指此刻该线程正在执行的的命令。
Binlog Dump:主节点正在将二进制日志,同步到从节点。
Change User:正在执行一个change-user的操作。
Close Stmt:正在关闭一个prepare statement对象。
Connect Out:一个从节点正在连接主节点。
Create DB:正在执行一个create-database的操作。
Daemon:服务器内部线程,而不是来自客户端的链接。
Debug:线程正在生成调试信息。
Delayed Insert:该线程是一个延迟插入的处理程序。
Drop DB:该线程正在执行一个drop-database的操作。
Execute:正在执行一个prepared statement。
Fetch:正在从Prepared statement中获取执行结果。
Field List:正在获取表的列信息。
Init DB:该线程正在选取一个默认的数据库。
Kill:正在执行kill语句,杀死指定线程。
Long Data:正在从Prepared Statement中检索long data。
Ping:正在处理server-ping的请求。
Prepare:该线程正在准备一个Prepared statement
Processlist:该线程正在生成服务器线程相关信息。
Query:该线程正在执行一个语句。
Quit:该线程正在退出。
Refresh:该线程正在刷表,日志或者缓存;或者在重置状态变量,或者在复制服务器信息。
Register Slave:正在注册从节点。
Reset Stmt:正在重置prepared statement
Set Option:正在设置或重置客户端的statement-execution选项。
Shutdown:正在关闭服务器。
Sleep:正在等待客户端向它发送执行语句。
Statistics:该线程正在生成server-status信息。
Table Dump:线程正在把表的内容发送到从属服务器。
Time:没用过。
TIME:表示该线程处于当前状态的时间。
STATE:线程的状态,和COMMAND对应。
INFO:一般记录的是线程执行的语句。默认只显示前100个字符,要看到全部信息,需要使用show full processlist。
1、按客户端ip分组,看哪个客户端的链接数最多
select
client_ip,
count(client_ip) as
client_num
from
(
select
substring_index(host,
':'
,1)
as
client_ip
from
information_schema.processlist)
as
connect_info
group
by
client_ip
order
by
client_num
desc;
查看正在执行的线程,并按time倒排序,看看有没有执行时间特别长的线程
select * from information_schema.processlist where command !=’Sleep’order by TIME desc;
- 找出所有执行时间超过5分钟的线程,拼凑出kill语句,方便后面查杀
Select * from processlist where command !=’Sleep’and TIME >300 order by time desc;