MYSQL 常用语法

1. 查询数据库列表 show databases;
2. 创建数据库 create database test001;
3. 使用数据库 use test001;
4. 删除数据库 drop database test001;
5. 使用数据库 use test001;
6. 创建表 create table emp(ename varchar(10), hiredate date, sal decimal(10,2),deptno int(2));
7. 查看表定义 desc emp;
8. 查看表结构
show create table emp \G;
9. 删除表 drop table emp;
10. 修改表类型
alter table emp modify ename varchar(20);
11. 增加表字段
alter table emp add column age int(3);
12. 删除表字段
alter table emp drop column age;
13. 修改表字段名
alter table emp change column age age1 int(4);
备注: 这里要关注 modify 和 change 的区别，change 可以修改列的名称，modify 不能
14. 新增字段排序顺序
alter table emp add birth date after ename;
15. 修改字段排序
alter table emp modify age int(3) first;
16. 更改表名
alter table emp rename emp1;
17. 表新增记录
insert into emp(ename, hiredate, sal, deptno) values ('lgh','2022-03-04','2000',1);
insert into emp values('lgh','2022-03-04','2000',2);
insert into emp(ename. hiredate) values ('lgh','1999-02-14');
insert into emp(ename,hiredate) values('lgh','1999-02-14'),('lgh','1999-02-14');
18. 表更新记录
update emp set sal = 4000 where ename = 'lgh';
update emp1,emp2 set emp1.sal = 4000,emp2.sal = 2000 where ename = 'lgh';
19. 表删除记录
delete from emp where ename = 'lgh';
delete a,b from emp1 a,emp2 b where a.ename = 'lgh' and b.ename = 'lgh';
备注: 不管是单表还是多表，不加条件就是删除表的所有记录
20. 查询记录
select * from emp;
select ename, hiredate from emp;
21. 查询不重复的记录
select distinct * from emp;
select distinct age from emp; 只能输出age不可重复的数据
22. 条件查询
select * from emp where enamel = 'zhangsan';
select * from emp where sal > 3000;
23. 查询排序
select * from emp order by sal; 默认升序 ASC ,降序 desc
select * from emp order by sal desc; 降序 从大到小
select * from emp order by sal; 升序 从小到大
24. 查询前几条数据
select * from emp limit 1;
select * from emp limit 1,3; 从索引第1条数据向后展示3条
25. 聚合统计总记录数
select count(1) from emp;
26. 根据某字段统计总记录数
select count(1) from emp group by age;
select age,count(1) from emp group by age;
27. having 筛选
select age, count(1) from emp group by age having age > 1;
备注: having 和 where 的区别， having 是和 group by 组合使用的，也就是说having是聚合后的筛选，
而where 是聚合前的筛选，尽可能的先用where 聚合前筛选，然后再根据having聚合后筛选
28. 统计求和、最大、最小的数据
select sum(age),max(age),min(age) from emp;
29. 表连接: 内连接 和 外连接
内连接和外连接的区别： 内连接仅选出两张表互相匹配的记录，外连接会选出其他不匹配的记录
内连接：
select ename,deptname from emp,dept where emp.deptno = dept.deptno;
外连接：左外连接和右外连接
左连接:
select ename.deptname from emp left join dept on emp.deptno = dept.deptno;
右连接：
select ename,deptname from emp right join dept on emp.deptno = dept.deptno;
30. 子查询
子查询的关键字包括: in、not in、=、!=、exists、not exists
select * from emp where deptno in (select deptno from dept);

支持的数据类型
1. 整数类型: TINYINT、SAMLLINT、MEDIUMINT、INT、INTEGER、BIGINT、FLOAT、DOUBLE、DECIMAL()、BIT
int 默认是 int(11),

2. 整数类型有一个属性: AUTO_INCREMENT, 对于想使用 AUTO_INCREMENT 的列必须定义为 NOT_NULL,并定义为 PRIMARY KEY 或者UNIQUE
3.日期类型:
DATE: 年月日
DATETIME: 年月日时分秒
TIME: 时分秒
DATESTAMP: 系统日期 时分秒，只有一个字段，再加同样类型的字段，默认为 0000-00-00 00:00:00
4.字符串类型
CHAR()
VARCHAR()
TEXT 0~65535 个字节
CHAR 和 VARCHAR 的区别:
在与存储方式不同，CHAR 列的长度固定为创建表时声明的长度，值长度不够时用空格代替；
VARCHAR 列的值为可变的字符串
在检索的时候，CHAR 列删除尾部的空格

常用函数
CONCAT() 连接一个字符串
INSERT(str, x, y, instr) 将字符串 str 从第 x 位置开始，y个字符长的子串替换成 instr
LOWER(str) 将字符串 str 中的字符变成小写
UPPER(str) 将字符串 str 中的字符变成大写
LEFT(str,x) 返回字符串str最左边的x个字符
RIGHT(str,x) 返回字符串 str 最右边的 x个字符

存储引擎
1.MYISAM 和 INNODB 区别
MYISAM: 有存储限制，INNODB 64TB
MYISAM 不支持事务，INNODB 支持事务
MYISAM 表锁，INNODB 行锁
MYISAM 不支持集群索引、数据缓存和外键
INNODB 支持集群索引、数据缓存和外键
2. 如何选用存储引擎
5.1 之前是默认的mysql的存储引擎，如果应用以读操作和插入操作为主，只有少量的更新和删除操作。并且对事物的完整性、
并发性要求不是很高，选择MYISAM是非常合适的
5.1 之后是默认的mysql 的存储引擎，用于事务处理的应用程序，支持外键，如果应用对事务的完整性有比较高的要求，在并发
条件下要求数据的一致性，数据操作出了插入和查询以外，还包括很多的更新、删除操作。那么选用INNODB比较合适

索引
MyISAM 和 INNODB 存储引擎的表默认创建的都是BTREE索引
创建前缀索引
create index cityname on city(cityname(10));
删除索引
drop index cityname on city;

2。设计索引的原则
1. 搜索的索引列，最适合索引的列是where 子句中的列或连接子句中的列
2.使用唯一索引，考虑某列中的值得分布。索引列的基数越大索引效果越好，比如日期列具有不同值很容易区分各行；而记录性别的列
只有M 和 F，索引用处不大，因为不管搜索哪个值都会得约一半的结果
3.使用短索引，对字符串索引，指定一个前缀长度，能够节省大量的索引空间，查询更快；因为较小的索引涉及磁盘io较少，较短的值
比较起来更快，较短的键值，索引高速缓存中的块能容纳更多的键值
4.利用最左前缀
5.不过度使用索引，每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能，在修改表的内容时，索引必须进行更新，有时可能需要重构