Mysql--sql
索引:可以大幅提升数据查询速度。
--适合添加索引 主键自动建立主键索引(唯一索引) where字句中的列,频繁作为查询字段的列 表连接关联的列 排序用到的列 索引列的基数越大(即不重复的值越多),索引的效率就越高
--不适合添加索引 表中数据太少 频繁修改的字段 数据重复且分布平均的字段
--索引分类 单值索引:即一个索引只包含单个列,一个表可以有多个单列索引。 唯一索引:索引列的值必须唯一,但是允许有空值。 复合索引:即一个索引包含多个列。 全文索引:使用fulltext创建全文索引
创建索引:create [unique|fulltext] index 索引名 on 表名 (属性名[长度][asc|desc])。 删除索引:drop index 索引名 on 表名。 查看索引:show index from 表名。
批量插入:批量插入多条语句是在一个事务里,要么全部成功,要么全部失败!mysql对sql的长度有限制,默认是4M,所以批量插入也不能插入大批量数据。
insert into table_name(id,name,age) values (1,'John',22), (2,'Lucy',18), (3,'Jack',30) ;
mysql获取系统时间(注意字段类型是DATE还是TIMESTAMP)
current_timestamp : 2020-03-15 10:23:30
current_time : 10:23:30
current_date : 2020-03-15
now() : 2020-03-15 10:23:30
curdate() : 2020-03-15
curtime() : 10:23:30
mysql修改密码不生效?
--记得刷新权限表
flush privileges;
字符集设置
--查看MYSQL数据库服务器和数据库字符集 show variables like '%character%'; --查看数据库字符集 show create database 库名; --查看表字符集 show table status from 库名 like '表名'; --创建库的时候指定字符集 create database 库名 default character set=字符集; --修改库的字符集 alter database 库名 default character set 字符集; --创建表的时候指定字符集 create table 表名(属性)default character set = 字符集; --修改表的字符集 alter table 表名 convert to character set 字符集;
创建用户:
username:你将创建的用户名
host:指定该用户在哪个主机上可以登陆,如果是本地用户可用localhost,如果想让该用户可以从任意远程主机登陆,可以使用通配符%
password:该用户的登陆密码,密码可以为空,如果为空则该用户可以不需要密码登陆服务器
CREATE USER 'username'@'host' IDENTIFIED BY 'password'; --例子 CREATE USER 'dog'@'localhost' IDENTIFIED BY '123456'; CREATE USER 'pig'@'192.168.1.101_' IDENDIFIED BY '123456'; CREATE USER 'pig'@'%' IDENTIFIED BY '123456'; CREATE USER 'pig'@'%' IDENTIFIED BY ''; CREATE USER 'pig'@'%';
授权:
privileges:用户的操作权限,如SELECT,INSERT,UPDATE等,如果要授予所的权限则使用ALL
databasename:数据库名
tablename:表名,如果要授予该用户对所有数据库和表的相应操作权限则可用*表示,如*.*
GRANT privileges ON databasename.tablename TO 'username'@'host' --例子: GRANT SELECT, INSERT ON test.user TO 'pig'@'%'; GRANT ALL ON *.* TO 'pig'@'%'; GRANT ALL ON maindataplus.* TO 'pig'@'%'; --用以上命令授权的用户不能给其它用户授权,如果想让该用户可以授权,用以下命令: GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;
设置与更改用户密码
SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword'); --如果是当前登陆用户用: SET PASSWORD = PASSWORD("newpassword"); --例子: SET PASSWORD FOR 'pig'@'%' = PASSWORD("123456");
撤销用户权限
REVOKE privilege ON databasename.tablename FROM 'username'@'host'; --例子: REVOKE SELECT ON *.* FROM 'pig'@'%';
删除用户
DROP USER 'username'@'host';
窗口函数:
--每组TopN SELECT * from( SELECT t.*,ROW_NUMBER() OVER(PARTITION BY subject ORDER BY score desc) rn FROM zxportal.test t ) b WHERE rn<=3; WITH t1 as( SELECT t.*,ROW_NUMBER() OVER(PARTITION BY SUBJECT ORDER BY SCORE desc) rn FROM zxportal.test t ) SELECT * FROM t1 WHERE rn<=3; --列转行 SELECT name, sum(CASE WHEN SUBJECT='语文' THEN SCORE ELSE 0 END) AS 语文, sum(CASE WHEN SUBJECT='数学' THEN SCORE ELSE 0 END) AS 数学, sum(CASE WHEN SUBJECT='英语' THEN SCORE ELSE 0 END) AS 英语 FROM zxportal.test GROUP BY name; SELECT * FROM zxportal.test2; --连续N日登陆 WITH t1 as( SELECT DISTINCT name,login_time FROM zxportal.test2 ), t2 AS( SELECT t1.*,ROW_NUMBER() OVER(PARTITION BY name ORDER BY login_time) rn FROM t1 ), t3 as( SELECT t2.*,date_sub(LOGIN_TIME, INTERVAL rn DAY) AS tmp_time FROM t2 ) SELECT name,tmp_time,count(*) pv FROM t3 GROUP BY name,tmp_time HAVING count(*)>=2;