Mysql常用命令

一、数据库

1、创建数据库

CREATE DATABASE 数据库名;

• 创建数据库demo

create database demo

2、删除数据库

drop database 数据库名；

• 删除数据库demo

drop database demo

3、查看数据库字符集

show VARIABLES like 'character%';

4、修改数据库字符集

alter database 数据库名 character set utf8;

二、数据表

1、创建数据表

CREATE TABLE table_name (column_name column_type);

创建表user

CREATE TABLE IF NOT EXISTS user (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int UNIQUE NOT NULL COMMENT '用户ID',
    nick_name varchar(64) COMMENT '昵称',
    achievement int DEFAULT 0 COMMENT '成就值',
    `level` int COMMENT '用户等级',
    job varchar(32) COMMENT '职业方向',
    register_time datetime DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2、删除数据表

DROP TABLE table_name ;

删除表user

drop table user

3、修改数据表

ALTER TABLE table_name [修改选项]

3.1、添加字段

ADD COLUMN [字段][类型]

添加字段id，类型为int

ALTER TABLE teacher ADD id INT

3.2、删除字段

DROP COLUMN [字段]

删除id字段

ALTER TABLE teacher DROP id 

3.3、修改字段

MODIFY COLUMN [字段][类型]

修改字段id的类型为VARCHAR(10)

ALTER TABLE teacher MODIFY id VARCHAR(10);

3.4、修改字段及名称

CHANGE COLUMN [旧字段][新字段][新类型]

修改id的字段名为t_id，并且类型为int

ALTER TABLE teacher CHANGE id t_id INT;

3.5、修改字段默认值或删除默认值

ALERT COLUMN [字段]{SET DEFAULT <默认值> | DROP DEFAULT}
修改字段id的默认值为1000

ALTER TABLE teacher ALTER id SET DEFAULT 1000;

删除字段id的默认值

ALTER TABLE testalter_tbl ALTER id DROP DEFAULT;

3.6、修改表名

RENAME TO [新表名]

修改表名teacher为teacher_info

ALERT TABLE teacher RENAME TO teacher_info

3.7、修改字符集及校对规则名

CHARACTER SET [字符集名称] COLLATE [校对规则名]

CHARACTER SET utf8 COLLATE utf8_general_ci;

三、数据

1、插入数据

INSERT INTO table_name ( field1, field2,…fieldN )
VALUES ( value1, value2,…valueN );

向user表插入数据

INSERT INTO user 
(user_title, user_author, submission_date)
VALUES ("好好学习", "lyp", NOW());

2、查询数据

SELECT column_name,column_name FROM table_name

读取user表

select * from user;

3、修改数据

UPDATE table_name SET field1=new-value1, field2=new-value2[WHERE Clause]

修改user_id为3的user_title值

UPDATE user SET user_title='学习JAVA' WHERE user_id=3;

4、删除数据

DELETE FROM table_name [WHERE Clause]

删除user_id为3的数据

DELETE FROM user WHERE user_id=3;

5、where子句

读取 user 表中 user_author 字段值为 lyp的所有记录：

SELECT * from user WHERE user_author='lyp';

6、like子句

查询user表中user_author字段以 lyp结尾的所有记录;

SELECT * from user WHERE user_author LIKE '%lyp';

7、order by排序

根据提交时间升序（默认）

SELECT * from user ORDER BY submission_date ASC;

根据提交时间降序

SELECT * from user ORDER BY submission_date DESC;

8、group by分组

将数据表按名字进行分组，并统计每个人有多少条记录：

SELECT name, COUNT(*) FROM   employee GROUP BY name;

9、join 连接使用

• INNER JOIN（内连接或等值连接）：获取两个表中字段匹配关系的记录。

• LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。

• RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

内连接

SELECT a.user_id, a.user_author, b.user_count
 FROM user a INNER JOIN tcount b ON a.user_author = b.user_author;

左连接

 SELECT a.user_id, a.user_author, b.user_count 
FROM user a LEFT JOIN tcount b ON a.user_author = b.user_author;

右连接

 SELECT a.user_id, a.user_author, b.user_count 
FROM user a RIGHT JOIN tcount b ON a.user_author = b.user_author;

10、null值处理

查找数据表中name 列为 NULL的数据

SELECT * FROM user WHERE name IS NULL;

查找数据表中name 列为 不为NULL的数据

SELECT * FROM user WHERE name IS NOT NULL;

四、索引

1、单例索引(主键索引、唯一索引、普通索引)

CREATE TABLE projectfile (
	id INT AUTO_INCREMENT COMMENT '附件id',
	fileuploadercode VARCHAR(128) COMMENT '附件上传者code',
	projectid INT COMMENT '项目id;此列受project表中的id列约束',
	filename VARCHAR (512) COMMENT '附件名',
	fileurl VARCHAR (512) COMMENT '附件下载地址',
	filesize BIGINT COMMENT '附件大小，单位Byte',
	-- 主键本身也是一种索引（注:也可以在上面的创建字段时使该字段主键自增）
        PRIMARY KEY (id),
	-- 主外键约束（注:project表中的id字段约束了此表中的projectid字段）
	FOREIGN KEY (projectid) REFERENCES project (id),
	-- 给projectid字段创建了唯一索引(注:也可以在上面的创建字段时使用unique来创建唯一索引)
	UNIQUE INDEX (projectid),
	-- 给fileuploadercode字段创建普通索引
	INDEX (fileuploadercode)
	-- 指定使用INNODB存储引擎(该引擎支持事务)、utf8字符编码
) ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT '项目附件表';

五、联合查询

六、分页查询

1、limit用法

检索记录行 6-15

SELECT * FROM table LIMIT 5,10;

检索记录行 96-last.

 SELECT * FROM table LIMIT 95,-1;

检索前 5 个记录行

 SELECT * FROM table LIMIT 5; 

LIMIT n 等价于 LIMIT 0,n

2、最基本的分页查询

SELECT … FROM … WHERE … ORDER BY … LIMIT …

SELECT * FROM articles WHERE category_id = 123 ORDER BY id LIMIT 50, 10  

3、子查询的分页方式

子查询分页 (提高分页效率)

SELECT * FROM articles WHERE  id >=   
 (SELECT id FROM articles  WHERE category_id = 123 ORDER BY id LIMIT 10000, 1) LIMIT 10  

4、JOIN分页方式

SELECT * FROM `content` AS t1 
JOIN (SELECT id FROM `content` ORDER BY id desc LIMIT ".($page-1)*$pagesize.", 1) AS t2 
WHERE t1.id <= t2.id ORDER BY t1.id desc LIMIT $pagesize; 

七、各种场景应用

1、一个字段存在多个ID值的查询

FIND_IN_SET(str,strList)

• str 要查询的字符串
• strList 字段名，参数以“,”分隔，如(1,2,6,8)
  查询字段(strList)中包含的结果，返回结果null或记录。

SELECT id,name,alert_id,create_time,update_time FROM t_alert_notice_template where FIND_IN_SET('cc22f2cc-7a48-4583-a446-f120fd7a8985',alert_id);

2、按时间分布查询记录数

• 按日查看：从当日往前统计30天

SELECT timeList.time, COUNT(t_alert_record.id) AS count
FROM (SELECT @date := DATE_ADD(@date, INTERVAL + 1 DAY) time FROM (SELECT @date := DATE_ADD(CURDATE(), INTERVAL - 30 DAY ) FROM t_num LIMIT 30) time) AS timeList
         LEFT JOIN t_alert_record ON DAY(t_alert_record.create_time) = DAY(timeList.time)  AND t_alert_record.status != '-1'
GROUP BY timeList.time;

• 按周查看：从当周往前统计12周
  

• 按月查看：从当月往前统计12月

SELECT timeList.time, COUNT(t_alert_record.id) AS count
FROM (SELECT @date := DATE_ADD(@date, INTERVAL + 1 MONTH) time FROM (SELECT @date := DATE_ADD(CURDATE(), INTERVAL - 12 MONTH) FROM t_num LIMIT 12) time) AS timeList
LEFT JOIN t_alert_record ON MONTH(t_alert_record.create_time) = MONTH(timeList.time) AND t_alert_record.status != '-1'
GROUP BY timeList.time;

• 按年查看：从当年往前统计三年

SELECT timeList.time, COUNT(t_alert_record.id) AS count
FROM (SELECT @date := DATE_ADD(@date, INTERVAL + 1 YEAR) time FROM (SELECT @date := DATE_ADD(CURDATE(), INTERVAL - 3 YEAR) FROM t_num LIMIT 3) time) AS timeList
LEFT JOIN t_alert_record ON YEAR(t_alert_record.create_time) = YEAR(timeList.time) AND t_alert_record.status != '-1'
GROUP BY timeList.time;