MySQL 语句学习总结

启动

service mysql start

修改密码

修改MySQL登录设置

vi /etc/my.cnf  在最后加一句  skip-grant-tables

重启MySQL

service mysqld restart

mysql>update mysql.user set authentication_string=password("新密码");
mysql>flush privileges;

范式

（1）第一范式（每列都保持原子性)
（2）第二范式（非主属性不部分依赖于候选码）
（3）第三范式（非主属性不传递依赖于候选码）
（4）BC范式（任何属性不部分依赖与候选码，不传递依赖与候选码）

创建表SQL

CREATE TABLE user (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
        `name` VARCHAR(10),
        `age` INT ,
        PRIMARY KEY (`id`)
)ENGINE = INNODB CHARACTER SET = utf8

更新表中数据SQL

update user set user.name = "lisheng" where id = 1;

查询表中数据的SQL

select user.name , user.age from user;

插入表中数据的SQL

insert into user (id ,name ,age) values (1,"lisheng",12);

连接

select user.name ,good.name from user join good on user.id = good.user_id;

删除表中数据的SQL

delete from user where id =1;

导出数据库

mysqldump -u dbuser -p dbname > dbname.sql   // 在/usr/local/mysql/bin目录执行

导入sql数据库

source /home/abc/abc.sql;  // 登录到数据库内执行

查看数据库表的编码格式

show create table table-name;

设置字段默认值

ALTER TABLE user ALTER  COLUMN name SET DEFAULT "" ;

修改表字段

ALTER TABLE address MODIFY COLUMN city CHAR(30);

增加一个表的一个字段

ALTER TABLE wechat_auth ADD user_id VARCHAR(255)

时间日期格式化

  DATE_FORMAT(t1.create_time,'%Y-%c-%d %h:%i:%s') 
```  

### 创建索引

```sql
CREATE INDEX index_name
ON table_name (column_name)

删除索引

drop INDEX index_name ON tbl_name

导入cvs文件

LOAD DATA INFILE '[FILE]'  
INTO TABLE [TABLE]；  
或者  
LOAD DATA INFILE '[FILE]'  
INTO TABLE [TABLE]  
FIELDS TERMINATED BY ','   
OPTIONALLY ENCLOSED BY '"'   
LINES TERMINATED BY '\n'；

导出cvs文件

SELECT * FROM [TABLE]  
INTO OUTFILE '[FILE]'；  
或者  
SELECT * FROM [TABLE]  
INTO OUTFILE '[FILE]'  
FIELDS TERMINATED BY ','   
OPTIONALLY ENCLOSED BY '"'   
LINES TERMINATED BY '\n'；

查询指定字段重复的记录

SELECT * FROM users GROUP BY mobile HAVING COUNT(mobile)>1 ORDER BY create_time DESC ;

当数据库中不存在该记录的时候插入

INSERT INTO `table` (value1, value2)
  SELECT
    'stuff for value1',
    'stuff for value2'
  FROM DUAL
  WHERE NOT EXISTS(SELECT *
                   FROM `table`
                   WHERE value1 = 'stuff for value1' AND value2 = 'stuff for value2')
  LIMIT 1;

例子

INSERT INTO `t_test` (id, name)
  SELECT
    1,
    'isheng'
  FROM DUAL
  WHERE NOT EXISTS(SELECT *
                   FROM `t_test`
                   WHERE id = 1 AND name = 'iisheng')
  LIMIT 1;

给表中两个字段添加唯一性约束

ALTER TABLE `table` ADD unique unique-name (column1,column2);

删除唯一性约束

ALTER TABLE 表名 DROP KEY 约束名;

inner join 和left join区别

left join (左连接) 返回包括左表中所有记录和右表中连接字段相等的记录

right join (右连接) 返回包括右表中所有记录和左表中连接字段相等的记录

inner join (等值连接) 只返回两个表中连接字段相等的记录

SQL 优化

1. 查询 seeker_job 去掉 seeker_job_suggest 关联
2. 优化模糊查询 改用单 like% reverse函数建立反向索引
3. 范围模糊查询 自增id优于uuid 使用count(1)也是自增id更好
4. 在适当的地方添加索引 （MySQL仅能对索引最左边的前缀进行有效的查找）
5. where语句后面的顺序 以及 建立 索引的顺序 （最左前缀匹配原则）
6. （尽量选择区分度高的列作为索引。）( = 和 in 可以乱序)
7. （尽量扩展索引，少去新建索引）
8. 不以通配符开头的sql语句 like “%aaa” ALL 全表扫描 like “aaaa%” range 索引
9. 尽量避免 null值判断 应该使用 0 默认值的 方法
10. 避免 select *
11. order by语句后面跟索引 排序的顺序 很重要 很重要
12. 提高GROUP BY 语句的效率, 可以通过将不需要的记录在GROUP BY 之前过滤掉
13. 能用DISTINCT的就不用GROUP BY

查看编码

show variables like '%char%';

查看表编码

show create table <表名>;

例：show create table user;

查看字段编码

show full columns from <表名>;

例： show full column from user;

修改数据库编码格式

alter database <数据库名> character set utf8mb4;
例：alter database db_user character set utf8mb4;

修改表编码

alter table <表名> character set utf8mb4;
例：alter table user character set utf8mb4;

修改字段编码

ALTER TABLE <表名> MODIFY COLUMN <字段名> <字段类型> CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
例：ALTER TABLE comment MODIFY COLUMN content VARCHAR(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

查询sql不走缓存

SELECT SQL_NO_CACHE * FROM users;

修改表字符集和校对集

ALTER  TABLE user
CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci;

修改数据库字符集和校对集

ALTER DATABASE mydata
CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci;

修改表字段相关信息

ALTER TABLE `job_auth_record` CHANGE `id` `id` VARCHAR(32) CHARACTER SET utf8mb4 NOT NULL COMMENT '主键id';

Mysql ASC 和 DESC 索引 怎么走的 ？？？

分组差查询数量 超级吊

SELECT
  company_id,
  count(*) AS counts
FROM seeker_job
WHERE company_id IN ("8a9c889c5dcbe37c015dcf16e284001f", "04be17537c5e4b299c135eeaadb5e131")
GROUP BY company_id;

修改校验集

ALTER TABLE insurance_insured CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

ALTER  TABLE insurance_insured CHARACTER SET utf8mb4;

ALTER TABLE `insurance_insured` CHANGE `name` `name`  varchar(64) COLLATE utf8mb4_general_ci DEFAULT NULL;

忽略某个索引

SELECT * FROM seeker_job IGNORE INDEX(idx_modify_time)

DISTINCT COUNT

SELECT  count(DISTINCT j1.job_id,j1.agent_company_id)
FROM job_agent j1 INNER JOIN job_agent j2 ON j1.job_id = j2.job_id AND j1.agent_company_id = j2.agent_company_id AND j1.id != j2.id ;

DISTINCT

SELECT  DISTINCT j1.job_id,j1.agent_company_id
FROM job_agent j1 INNER JOIN job_agent j2 ON j1.job_id = j2.job_id AND j1.agent_company_id = j2.agent_company_id AND j1.id != j2.id ;

查找重复字段记录

https://www.cnblogs.com/jiangxiaobo/p/6589541.html

修改表编码个字符集

ALTER  TABLE apply_stencil CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

ALTER TABLE apply_stencil MODIFY COLUMN id VARCHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

ALTER TABLE apply_stencil MODIFY COLUMN apply_id VARCHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

查询一个库里面有多少表

SELECT COUNT(*) TABLES, table_schema FROM information_schema.TABLES  WHERE table_schema = 'goabroad'

时间相关格式化函数

create_time时间格式
SELECT DATE_FORMAT(create_time,'%Y%u') weeks,COUNT(id) COUNT FROM role GROUP BY weeks;
SELECT DATE_FORMAT(create_time,'%Y%m%d') days,COUNT(id) COUNT FROM role GROUP BY days;
SELECT DATE_FORMAT(create_time,'%Y%m') months,COUNT(id) COUNT FROM role GROUP BY months


create_time时间戳格式
SELECT FROM_UNIXTIME(create_time,'%Y%u') weeks,COUNT(id) COUNT FROM role GROUP BY weeks;
SELECT FROM_UNIXTIME(create_time,'%Y%m%d') days,COUNT(id) COUNT FROM role GROUP BY days;
SELECT FROM_UNIXTIME(create_time,'%Y%m') months,COUNT(id) COUNT FROM role GROUP BY months

回收表

alter table name engine=innodb

left() 和 length()函数

UPDATE seeker_job SET country_id = left(country_id,6) WHERE length(country_id) > 6;

查看mysql版本号

SELECT version();