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 identifier,SAVEPOINT允许在事务中创建一个保存点,一个事务中可以有多个SAVEPOINT。
5、RELEASE SAVEPOINT identifier删除一个事务的保存点,当没有指定的保存点时,执行该语句会抛出一个异常。
6、ROLLBACK TO identifier把事务回滚到标记点。
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 table_name;
- 唯一索引:索引列必须唯一,但允许有空值
创建索引
- 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;
- 全文索引
ALTER TABLE tableName ADD FULLTEXT indexName(aa)。
显示索引信息
SHOW INDEX FROM tableName;
MySQL临时表
临时表只在当前连接可见,当关闭连接时,MySQL会自动删除表并释放所有空间。
CREATE TEMPORARY TABLE tableName
(id INT,
name VARCHAR(16)
);
DROP TABLE tableName;
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
(12)TRIM(s)去掉字符串s开始和结尾处的空格
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运算符
- 算术运算符
- 比较运算符
- 逻辑运算符
- 位运算符