#表约束 和 创建表
1.非空约束 NOT NULL
2.默认值约束 DEFAULT '男'
3.唯一约束 UNIQUE
4.主键约束 PRIMARY KEY
#例
CREATE TABLE table_a (
#id char(19) PRIMARY KEY NOT NULL DEFAULT '默认值' COMMENT '主键非空char类型 一张表只能有一个主键',
t_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键自增',
NAME VARCHAR(20) NOT NULL DEFAULT '' COMMENT '角色名称',
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='指定数据库引擎和编码 和添加表描述';
#增
INSERT INTO table_name ( field1, field2,...fieldN ) VALUES ( value1, value2,...valueN );
INSERT INTO table_name ( field1, field2,...fieldN ) VALUES ( value1, value2,...valueN ),( value1, value2,...valueN ),( value1, value2,...valueN );
#删
DELETE FROM 表 WHERE 条件; //删除数据带条件指定数据,否则删除全表数据
#联表删除,慎用 找好关系条件再尝试
DELETE a,b FROM table_a AS a LEFT JOIN table_b AS b ON a.id = b.id WHERE a.name='xxx'
#改
UPDATE 表 SET 字段=值 WHERE 条件; //带条件修改指定数据,否则修改全表
#查 往下翻有其他查询
SELECT * FROM table_name
#模糊查 % 或者 _ || LIKE '%xxx%' || %在前不会走索引查询
SELECT * FROM table_name WHERE t_name LIKE '%xxx%'
#联查
LEFT JOIN :左连接,返回左表中所有的记录以及右表中符合条件的记录
RIGHT JOIN :右连接,返回右表中所有的记录以及左表中符合条件的记录
FULL JOIN :输出所有表中的所有信息
INNER JOIN :搜索出两个表中相同的记录,然后输出
#例子
SELECT a.*,COUNT(b.列段) FROM table_a a
LEFT JOIN table_b b ON a.id = b.id
LEFT JOIN table_c c ON b.id = c.id
WHERE a.name='xxx'
GROUP BY a.列段 HAVING AVG(b.列段) = 'xxx' #HAVING语句通常与GROUP BY语句联合使用,用来过滤由GROUP BY语句返回的记录集。HAVING语句的存在弥补了WHERE关键字不能与聚合函数联合使用的不足。
LIMIT 100,10 #从第一百行数据开始,每页显示10条
#聚合函数(常用于GROUP BY从句的SELECT查询中)
AVG(col)返回指定列的平均值
COUNT(col)返回指定列中非NULL值的个数
MIN(col)返回指定列的最小值
MAX(col)返回指定列的最大值
SUM(col)返回指定列的所有值之和
GROUP_CONCAT(col) 返回由属于一组的列值连接组合而成的结果
#数学函数
ABS(X) 返回x的绝对值
BIN(X) 返回x的二进制(OCT返回八进制,HEX返回十六进制)
CEILING(X) 返回大于x的最小整数值
EXP(X) 返回值e(自然对数的底)的x次方
FLOOR(X) 返回小于x的最大整数值
GREATEST(x1,x2,...,xn)返回集合中最大的值
LEAST(x1,x2,...,xn) 返回集合中最小的值
LN(X) 返回x的自然对数
LOG(X,Y)返回x的以y为底的对数
MOD(X,Y) 返回x/y的模(余数)
PI()返回pi的值(圆周率)
RAND()返回0到1内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值。
ROUND(X,Y)返回参数x的四舍五入的有y位小数的值
SIGN(X) 返回代表数字x的符号的值
SQRT(X) 返回一个数的平方根
TRUNCATE(X,Y) 返回数字x截短为y位小数的结果
#字符串函数
ASCII(CHAR)返回字符的ASCII码值
BIT_LENGTH(str)返回字符串的比特长度
CONCAT(s1,s2...,sn)将s1,s2...,sn连接成字符串
CONCAT_WS(sep,s1,s2...,sn)将s1,s2...,sn连接成字符串,并用sep字符间隔
INSERT(str,X,Y,INSTR) 将字符串str从第x位置开始,y个字符长的子串替换为字符串instr,返回结果
FIND_IN_SET(str,LIST)分析逗号分隔的list列表,如果发现str,返回str在list中的位置
LCASE(str)或LOWER(str) 返回将字符串str中所有字符改变为小写后的结果
LEFT(str,X)返回字符串str中最左边的x个字符
LENGTH(s)返回字符串str中的字符数
LTRIM(str) 从字符串str中切掉开头的空格
POSITION(SUBSTR,str) 返回子串substr在字符串str中第一次出现的位置
QUOTE(str) 用反斜杠转义str中的单引号
REPEAT(str,srchstr,rplcstr)返回字符串str重复x次的结果
REVERSE(str) 返回颠倒字符串str的结果
RIGHT(str,X) 返回字符串str中最右边的x个字符
RTRIM(str) 返回字符串str尾部的空格
STRCMP(s1,s2)比较字符串s1和s2
TRIM(str)去除字符串首部和尾部的所有空格
UCASE(str)或UPPER(str) 返回将字符串str中所有字符转变为大写后的结果
#加密函数
AES_ENCRYPT(str,KEY) 返回用密钥key对字符串str利用高级加密标准算法加密后的结果,调用AES_ENCRYPT的结果是一个二进制字符串,以BLOB类型存储
AES_DECRYPT(str,KEY) 返回用密钥key对字符串str利用高级加密标准算法解密后的结果
DECODE(str,KEY) 使用key作为密钥解密加密字符串str
ENCRYPT(str,salt) 使用UNIXcrypt()函数,用关键词salt(一个可以惟一确定口令的字符串,就像钥匙一样)加密字符串str
ENCODE(str,KEY) 使用key作为密钥加密字符串str,调用ENCODE()的结果是一个二进制字符串,它以BLOB类型存储
MD5() 计算字符串str的MD5校验和
PASSWORD(str) 返回字符串str的加密版本,这个加密过程是不可逆转的,和UNIX密码加密过程使用不同的算法。
SHA() 计算字符串str的安全散列算法(SHA)校验和
示例:
SELECT ENCRYPT('root','salt');
SELECT ENCODE('xufeng','key');
SELECT DECODE(ENCODE('xufeng','key'),'key');#加解密放在一起
SELECT AES_ENCRYPT('root','key');
SELECT AES_DECRYPT(AES_ENCRYPT('root','key'),'key');
SELECT MD5('123456');
SELECT SHA('123456');
#类型转化函数
为了进行数据类型转化,MySQL提供了CAST()函数,它可以把一个值转化为指定的数据类型。类型有:BINARY,CHAR,DATE,TIME,DATETIME,SIGNED,UNSIGNED
示例:
SELECT CAST(NOW() AS SIGNED INTEGER),CURDATE()+0;
SELECT 'f'=BINARY 'F','f'=CAST('F' AS BINARY);
#数据去重
SELECT DISTINCT 列…. FROM 表名;
#拼接结果
SELECT CONCAT(列1,列2) FROM 表名;
#基本查询
SELECT 列 AS 别名 FROM 表名
SELECT 列 别名 FROM 表名
#范围查询
WHERE 列 BETWEEN 条件1 AND 条件2; //列在这个区间的值 # 可以用来 集合查询( 判断列的值是否在指定的集合中 )
WHERE 列 NOT BETWEEN 条件1 AND 条件2; //不在这个区间
WHERE !( 列 BETWEEN 条件1 AND 条件2 ); //同样表示不在这个区间
#排序 升序:asc 降序:desc 注意:不能使用中文的别名排序
WHERE 条件 ORDER BY 列 [ASC/DESC]
#分页分页查询( beginIndex:表示从第多少条数据开始 pageSize:表示每页显示的数据条数 ) beginIndex公式:(当前页数-1)*pageSize
WHERE [条件] LIMIT beginIndex,pageSize;
#分组函数( 注意:如果要对分组后的数据进行筛选,那么必须使用having关键字,条件写在having后 )
SELECT 聚集函数 FROM 表名 WHERE [条件] GROUP BY 列 HAVING 分组后的条件
#语句的执行顺序:FROM—> WHERE—>group by---->Having—>SELECT-->ORDER BY
#having用来过滤由GROUP BY语句返回的记录集。HAVING语句的存在弥补了WHERE关键字不能与聚合函数联合使用的不足。
#Where和having的区别:
WHERE: 先过滤已有的数据(数据是已经存在的),在进行分组,在聚集计算 #语句的执行顺序:from—>where—>SELECT
HAVING:先分组,在对每组进行计算,根据得到结果在过滤(分组把数据算出之后,在过滤)
#数据索引
索引的作用相当于图书的目录,可以根据目录中的页码快速找到所需的内容。
什么列适合建索引??
1.表的主键、外键必须有索引;
2.数据量超过30000的表应该有索引;
3.经常与其他表进行连接的表,在连接字段上应该建立索引;
4.经常出现在Where子句中的字段,特别是大表的字段,应该建立索引;
5.索引应该建在选择性高的字段上;
6.索引应该建在小字段上,对于大的文本字段甚至超长字段,不要建索引;
sql语句创建和删除索引:
创建索引:
CREATE INDEX 索引名称 ON 表名 (列名)
删除索引:
方式一:
DROP INDEX 索引名 ON 表名
方式二:
ALTER TABLE 表名 DROP INDEX 索引名
mysql常用,菜鸟看这一篇就够了
最新推荐文章于 2022-08-02 10:41:27 发布