MySQL基础1--基础操作

一. 选择存储引擎
a. MyISAM存储引擎：不支持事物、不支持外键、访问速度比较快，适用于对事务完整性没有要求并以访问为主的应用。
b. InnoDB存储引擎：在事务上具有优势，支持具有提交、回滚和崩溃恢复能力的事务安装，比MyISAM占用更多的磁盘空间。适用于需要进行频繁更新、删除操作，同时还对事务完整性要求比较高，需要实现并发控制的应用。
c. MEMORY存储引擎：使用内存存储数据，数据访问速度快，但是安全没有保障，适用于涉及数据比较小，需要进行快速访问的应用。
d. InnoDB和MyISAM支持btree类型索引、MEMORY支持hash索引。
二. 数据库中简单操作
1. 创建数据库 create database database_name（删除使用drop）
2. 如果要创建表，需要选择数据库：use database_name(删除使用drop)
3. 创建表：create table table_name(属性名 数据类型，属性名 数据类型，…属性名 数据类型)
4. 插入表中数据:insert into table_name values(相应表的格式);
5. 查看表定义：首先选用数据库(use database_name),然后执行describe table_name;或者执行show create table table_name;
6. 修改表：a.修改表名：alter table old_table_name rename new_table_name; b.表的最后一个位置增加字段：alter table table_name add 属性名 属性类型；c.表的第一个位置增加字段：alter table table_name add 属性名 属性类型 first;d.在表的指定位置增加字段: alter table table_name add 属性名 属性类型 after 属性名。
7. 修改字段：a.修改字段的数据类型：alter table table_name modify 属性名 数据类型;b.
修改字段名字：alter table table_name change 旧属性名 新属性名 旧数据类型；c.同时修改字段的名字和属性:alter table table_name change 旧属性名 新属性名 新数据类型;d.修改字段的顺序：alter table table_name modify 属性名1 数据类型 first(after 属性名2)，前者指的是将属性名1放在第一个，后者是放在指定位置后面。
8. 操作表的约束：create table table_name(属性名 数据类型 not null,dname varchar(20) default ‘cjgong’, loc varchar(40),constraint uk_dname unique(dname), constraint pk_dname_deptno primary key(deptno,dname)).外键：constraint 外键约束名 foreign key(属性名1) references 表名(属性名2)—这里的表名指其它的表，如果用户插入的记录中，该字段没有参考父表中属性名2的值，则会报错。自动增加约束：deptno int primary key auto_increment;
三. 创建索引适合与不适合的情况以及使用索引的利弊
适合的情况：
a. 经常被查询的字段，即在where子句中出现的字段。
b. 在分组的字段，即在group by子句中出现的字段
c. 存在依赖关系的子表和父表之间的联合查询，即主键或外键字段。
d. 设置唯一完整性约束的字段。

不适合创建索引：
a. 在查询中很少被使用的字段
b. 拥有许多重复值的字段。

利：数据库对象索引的出现可以提高数据库管理系统的查找速度，而且还可以保证字段的唯一性，从而实现数据库表的完整性。
弊：过多的创建索引会占据许多的磁盘空间，降低表的更新速度，影响库的性能。
四. 操作索引的操作
1. 创建表时创建普通索引:create table t_dept(deptno int,dname varchar(20),loc varchar(40),index index_deptno(deptno(长度可缺省)asc|desc可缺省))
2. 在已经存在的表上创建普通索引：create index 索引名 on 表名(属性名【(长度)】【asc|desc】)。create index dept_index on tab_dept(dept(15)asc);
3. alter table 创建索引名：alter table table_name add index|key 索引名(属性名【(长度)】【asc|desc】)
4. 创建唯一索引：在普通创建索引语句index前面加unique：如create unique index dept_index on tab_dept(dept(15)asc);使用alter table创建也是同样的规则。
5. 创建和查看全文索引：主要数据类型为char、varchar和text，创建全文索引时类似于创建普通索引，在普通索引上index前加fulltext即可。
6. 创建多列索引：类似于普通索引创建，只是在包含属性名的括号里有多个属性名。
例如：create table t_dept(deptno int,dname varchar(20),loc varchar(40),index index_deptno(deptno(长度可缺省)asc|desc可缺省，dename(长度可缺省)asc|desc可缺省)
7. 删除索引：drop index index_name on table_name;

五. 视图的操作
1. 视图的特点：
a. 视图的列可以来自不同的表，是表的抽象和在逻辑意义上建立的新关系。
b. 视图是由基本表(实表)产生的表(虚表)。
c. 视图的建立和删除不影响基本表。
d. 对视图内容的更新(增删改)直接影响基本表。
e. 当视图来自多个基本表时，不允许添加和删除数据。
2. 创建视图：create view view_name as 查询语句。例如：create view view_1 as select id,name from t_product; select * from view_1;(将视图当作表来执行查询操作)
3. 创建各种视图：
a. 常量视图:create view view_test1 as select 3.15;
b. 封装使用聚合函数(sum、min、max、count等)查询语句的视图：create view view_1 as select count(name) from table_name;
c. 封装了实现排序功能查询语句的视图：create view view_1 as select name from tab_dept order by id(属性名) desc(可以是asc)。通过某一属性参数进行排序查找另一个 属性的值，可以使用该语句进行建立视图。
d. 封装了实现表内连接查询语句的视图：create view view_1 as select s.d from tab_dept as s,t1 as t where s.d=t.deptno and t.deptno=2;其中连接了tab_dept和t1两个 表。Where后面表示满足的条件。
e. 封装了外连接(left join和right join)查询语句的视图：create view view_1 as select s.name from t_student as s left join t_group as g on s.group_id=g.id where g.id=2;
f. 封装了实现子查询相关查询语句的视图：create view view_1 as select s.name from t_student as s where s.group_id in(select id from t_group);
g. 封装了实现记录联合(union和union all)查询语句的视图：create view view_1 as select id,name from t_student union all select id,name from t_group;如果有两个表分 别记录的是一个年级两个班的成绩，现在要进行统计两个班整体的成绩排名情况，就可以使用记录联合。

4. 查看视图：1.show table status from view like view_name; 2. show create view view_name;3. use information_schema;select * from views where table_name='view_2';
5. 删除视图：drop view view_name(,view_name1,view_name2…);例如：drop view view_1,view_2;
6. 修改视图：1.create or repalace view view_name as 查询语句；可以用该语句进行修改视图。2.alter view view_name as 查询语句(修改后的查询语句).例：create or replace view view_1 as select name from t_dept; alter view view_1 as select name from t_dept;
7. 利用视图操作基本表数据，根据视图的特点五，对视图进行增删改可以，基本表也会更改：1.添加数据：insert into view_1(id,name) values(1,’Tom’);2.删除数据：delete from view_1 where name=’Tom’；3更新数据：update view_1 set id=2 where name=’Tom’.
六. 触发器的操作
1. 创建一条执行语句的触发器：create trigger trigger_name before|after trigger_event(增删改三个动作之一) on table_name for each row trigger_stmt(执行语句); 例：create trigger tri_diarytime before insert on t_dept for each row insert into t_diary values(null,’t_dept’,now()); insert into t_dept values(1,’x’,’xx’);这里指的是在t_dept插入数据之前先在t_diary中插入数据。
2. 创建多条执行语句的触发器：create trigger trigger_name before|after trigger_event(增删改三个动作之一) on table_name for each row begin trigger_stmt(执行语句) end.其中执行语句可以是多条，每条之间用分号隔开，结尾采用其它符号表示结束，delimiter &&,将结束符号设置成了&&。
3. 查看触发器：1.show triggers;2.use information_schema;select * from triggers where trigger_name=’xx’;
4. 删除触发器：drop trigger trigger_name;
七. 数据的操作
1. 插入数据：1.insert into table_name(field1,field2,field3,…fieldn) values(value1…..valuen);这个语句是标准语法，无论是完整数据记录还是局部数据记录都可使用。2. insert into table_name values(value1…..valuen);该语句只能用于插入完整数据记录。3.插入多条数据：insert into table_name(field1,field2,field3,…fieldn) values(value11…..valuen1), (value12…..valuen2)…., (value1m…..valuenm);4.插入查询结果：insert into table_name1(field1,field2,field3,…fieldn) select field1,field2,field3,…fieldn from table_name2 where…;select后是查询的结果，是数据，该语句是将查询得到的数据插入table_name1.
2. 更新记录：update table_name set field1=value1,field2=value2…. ,where condition;
3. 删除记录：delete from table_name where….;
八. 单表数据记录查询
1. 带in关键字的集合查询：select field1 field2…..field3 from table_name where field in (value1,value2,….valuen);若在in前面加个not表示不再集合内的数据查询。若将not加在field前面则构成非逻辑，运行时间更快。Not in集合中若存在null值，查询结果为空。
2. 带like关键字的模糊查询：select field1 field2…..field3 from table_name where field like value;此处的value需要进行模糊化，使用通配符’-’和’%’；‘-’能匹配单个字符，‘%’能匹配任意长度的字符串。例如：select ename from t_dept where ename like ‘A%’;该语句表示查出ename中以A开头的字段。select ename from t_dept where not ename like ‘%A%’;该语句表示查询不含A的字段。
3. 排序记录查询：可以单字段查询，可以按照多字段查询。限制数据记录查询数量：select field1 field2…fieldn from table_name where condition limit offset_start,row_count;其中offset_start表示起始偏移量，可缺省，缺省默认从第一条记录开始，row_count表示显示的行数。如select * from tab_dept order by dept desc limit 2,1;该语句是求tab_dept表中dept值第三大的全部信息。
4. 统计函数：select function(field) ((as)newname) from table_name where condition;其中function有count()函数：统计记录数目；avg()函数：统计计算字段值的平均值； sum()函数；max()函数；min()函数。除了count()有点特殊，count（*）表示包含null值，count(field)不包含null值。而其他函数只有后者。注意：如果没有任何数据记录 count()返回0，而其他函数返回null。例如：select count(deptno) number from t_dept;

5. 分组查询记录:1.group by语句：select deptno,group_concat(ename) enames,count(ename) number from t_employee group by deptno;goup_concat(field)函数是显示所有相应的field。 Group by后面跟多个字段，便可实现多个字段分组。2.having子句：having子句是前面查询结束后再通过having后面的条件进行限制，having与where的区别：where后面的条件先执行然后选择，而having则是等前面符合条件的都选择好，然后做最后的限制。

九.多表数据记录查询
1.内连接查询：只包含两表匹配的结果(INNER JOIN….ON)
Select e.filed1…e.fieldn,d.filed1…,d.fieldn from t_e e inner join t_d d on e.fieldx=d.fieldy;
a.内连接分为自然连接、等值连接和不等连接
b.执行自然连接时，会自动判断两表相同名称的字段，然后进行数据值的匹配，然后去掉重复的字段
c.执行等值连接时，需要用符号=指定匹配条件，在新关系中不会去掉重复字段
select salaries.emp_no,salaries.salary,salaries.from_date,salaries.to_date,dept_manager.dept_no
from salaries inner join dept_manager on dept_manager.emp_no = salaries.emp_no
and dept_manager.to_date = '9999-01-01' and salaries.to_date = '9999-01-01';以上是进行等值连接，通过等值连接提取salaries表和dept_manager表的相关信息，通 过两个表都有的emp_no进行将表等值连接。
d.不等连接：即与等值连接差不多，只不过用!=进行匹配，比如a表有个字段num，b组有个字段num，则进行不等连接，逐一遍历a表，同时遍历b表，若两表的num值不 同，则保存记录，否则，往下遍历a，循环操作。最终结果，每条记录两个num不相等。在新关系中不会去掉重复字段。

2.外连接查询：除了包含匹配的还包含不匹配的
a.外连接分为左外连接、右外连接和全外连接
b.左外连接除了选择相匹配的数据记录，还包含关联左边表中不匹配的数据记录，即包含左边表中没用到的记录。
select e.last_name,e.first_name,d.dept_no from employees e left join dept_emp d on e.emp_no=d.emp_no;
employees表中拥有所有的员工信息，但是dept_emp并没有包含所有员工，说明employees表中有的员工并未分部门，所以使用左外连接能够显示所有员工信息包括没分 配部门的员工。
c.右外连接则是还包含右边表没用到的记录。
d.全外连接结果则是左外连接和右外连接的并集。

3.合并查询数据记录：
select field1 field2 ….fieldn from tablename1 union|union all select field1 field2 ….fieldn from tablename2 union|union all select field1 field2 ….fieldn from tablename3:其中使用union合并之后去掉了重复数据记录，使新关系里没有重复的数据记录。union all直接合并，不去重复记录。
4.子查询：直接使用where等语句进行条件限制，不使用连接的查询。
In语句：Select * from t_employee where deptno (not)in (select deptno from t_dept);显示表employee中deptno（不在）在表t_dept中的员工信息。
Any语句：select ename,sal from t_employee where sal>any(select sal from t_employee where job=’manager’);显示工资不低于manager工资的雇员的姓名和工资。 （大于最小的）
All语句：select ename,sal from t_employee where sal>all(select sal from t_employee where job=’manager’);显示工资大于manager工资最大值的雇员的姓名和工资。 （大于最大的）
Exists语句：select * from t_dept c where exists(select * from t_employee where deptno=c.deptno);
子查询中构建临时表：select d.deptno,d.name,d.loc,number,average from t_dept d,(select deptno dno,count(empno) number,avg(sal) average from t_employee group by deptno desc) employee where d.deptno=employee.dno; 红色区域是根据题目需要通过t_employee构建的临时表。
注意：子查询的执行效率比连接查询的执行效率高。

十.MySQL运算符
运算符分为：算数运算符、比较运算符、逻辑运算符和位运算符
1. 比较运算符：a.’=’和’<=>’都表示判断是否相等的意思，前者不可以操作null而后者可以，与之对应的是’!=’和’<>’表示判断是否不相等。
2. 逻辑运算符：1.and：若有一个为0，返回0,；若有一个为null且没0，返回null；若所有操作数都不为0和null时，返回1；2.or：含有null则返回null；若不含null时，只要有一个不为0，返回1，否则返回0. 3.！：含有null返回null，如果是0返回1，否则返回0； 4.xor：如果有null，返回null。
十一.MySQL常用函数
1.合并字符串函数concat()和concat_ws()：a.对于concat(),如果传入的参数有一个是null，则结果为null；b.concat_ws(sep,s1,s2…sn)：sep是分隔符。比如 concat_ws(‘-’,’a’,’b’,’c’)的结果是a-b-c;当分隔符为null时，结果为null，若传入参数有null，则返回结果自动忽略null。比如select concat_ws('-',null,'a','b') 合并后字符;结果如下图：

3. 比较字符串大小函数：strcmp(str1,str2)，比较子串str1和子串str2的大小，前者大为1，相等为0，否则为-1；
4. 获取字符串长度函数length()函数和字符数函数char_length():a.length(str)表示str字符串含有的字节数，比如length(‘数据库’)的结果是6；b.char_length(str)表示字符串的字符数，
char_length(‘数据库’)结果为3.如：select '数据库' 中文字符串,length('数据库') 字符串长度,char_length('数据库') 字符串字符数;结果如下图：

5. 查找字符串：a.find_in_set(‘a’,’a,b,c’)结果是1，该函数表示第二个参数中与第一个参数中匹配的位置，第二个参数包含若干个用逗号隔开的字符串。如果返回值为0，说明无法匹配。

b.field(str,str1,str2…)返回第一个与字符串str匹配的字符串的位置，即str在后面传入参数中第一次出现的位置。 C.返回字符串相匹配的开始位置：locate(‘sql’,’mysql’)返回3，position(‘sql’ in ‘mysql’)返回3，instr(‘mysql’,’sql’)返回3。若返回0，则表示不匹配; d.elt(n,str1,str2…)返回第n个字符串； e.make_set(num,str1,str2,str3….)函数：通过num的二进制形式对后面字符串进行筛选，返回1对应的字符串,例如：select bin(5) 二进制数,make_set(5,'a','b','c','d','e') 选取后的字符串;结果如下：

6. 截取字符串函数：a.从左边或者右边截取字符串：left(str,x):从str左边截取x个字符，right(str,x)：从右边截取x个字符; b.截取指定位置和长度子字符串:substring(str,num,len)和mid(str,num,len)；
7. 去除字符串的首尾空格：ltrim(str):去除字符串首部的空格，rtrim(str)：去除字符串尾部的空格，trim(str)：去除字符串首尾的空格。
8. 替换字符串：a.insert(str,pos,len,newstr):将str字符串pos位置开始长度为len的部分替换成字符串newstr；b.replace(str,substr,newstr):将字符串str子字符串substr替换成newstr。

9. 数值函数：select 4.1315 原来的数,ceil(4.1315) 向上取整,floor(4.1315) 向下取整,round(4.1315,3) 四舍五入保留3位小数,truncate(4.1315,3) 截断保留3位小数;结果如下：

10. 使用日期和时间函数：select concat_ws(' ',curdate(),curtime()) curtime,now() now,week(now()) week,year(now()) year,hour(now()) hour,minute(now()) minute,monthname(now()) month,unix_timestamp(now()) unixtimestamp；结果如下：