mysql使用笔记（二）

一、启动和关闭mysql服务

windows下 
启动 
（方法1）管理员权限进入cmd，执行 net start mysql 
（方法2）管理员权限进入cmd，执行 mysqld -uroot --console 
（方法3）在计算机——服务——mysql，通过图形界面的方式启动 
关闭 
（方法1）管理员权限进入cmd，执行 net stop mysql 
（方法2）管理员权限进入cmd，执行 mysqladmin -uroot shutdown 
（方法3）在计算机——服务——mysql，通过图形界面关闭

linux下 
启动 
（方法1）终端下执行 cd mysql可执行文件目录，比如cd /usr/bin，执行 
./mysqld_safe & 
（方法2）终端下执行 service mysql start （这种方法需要RPM方式安装的mysql才行）

关闭 
（方法1）终端下执行 mysqladmin -uroot shutdown 
（方法2）service stop mysql （这种方法需要RPM方式安装的mysql才行）

常见的异常 
    启动时候失败，提示 [ERROR] InnoDB: .\ibdata1 can't be opened in read-write mode。解决方案： 
（1）打开任务管理器，终止mysqld.exe 
（2）打开mysql安装目录的data文件夹，删除 ib_logfile0, ib_logfile1 
（3）重启mysql

二、 连接数据库

（方法1）通过mysql workbench图形界面的方式连接 
（方法2）在命令行下，执行 mysql -u 用户名 -p 密码 -h 主机ip，如 
mysql -u root -p,回车后输入密码，不写 -h 表示使用本机ip地址。

三、 SQL语句分类

    mysql支持标准sql语句，同时有自己的拓展语句。SQL语句可以分为三类： 
（1）DDL(Data Definition Language) 数据定义语句，定义了不同的数据段、数据库、表、列、索引等数据库对象。常用的语句关键字主要包含create、drop、alter等。 
（2）DML(Data Manipulation Language) 
数据操纵语句，用于添加、删除、更新和查询数据库记录，并检查数据完整性。常用的语句关键字主要包括insert、delete、update、select。 
（3）DCL(Data Control Language) 
数据控制语句，用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别，主要的语句关键字包含grant、revoke等。

    DML语句只是对数据库表内的数据进行操作，而DDL语句可以对表定义、结构进行修改。 
    在mysql命令行下，语句以;作为结束，每个语句之后跟;，然后回车，才会被执行。

四、 DDL语句操作

（4.1）创建数据库 
create database dbname; 
（4.2）显示当前数据库 
show databases; 
    在安装mysql时，会自动创建4个数据库： 
(a)information_schema: 主要保存了系统中的一些数据库对象信息，比如用户表信息、列信息、权限信息、字符集信息、分区信息。 
(b)cluster: 存储了系统的集群信息 
(c)mysql: 存储了系统的用户权限信息 
(d)test: 系统自动创建的测试数据库，任何用户都可以使用

(4.3)选择数据库 
use dbname; 
之后的操作，都是在选择的数据库下的操作。比如，show tables;，显示该数据库下的所有表。

(4.4)查看当前信息 
status,显示当前connection的信息，比如使用的数据库、用户、数据库的字符集等。

（4.5）删除数据库 
drop database dbname; 
在mysql中，drop语句的操作结果均显示'0 rows affected'

(4.6)创建表 
create table tbname( 
column1_name column1_constraints, 
column2_name column2_constraints, 
... 

    例如，创建一个emp表，表中包括 ename（姓名）、hiredate（雇佣日期）、sal（薪水）、deptno部门编号三个字段，字段类型分别为varchar(10), date, decimal(10,2), int(2) 
create table emp( 
ename varchar(10), 
hiredate date, 
sal decimal(10, 2), 
deptno int(2)) 


(4.6) 查看表定义 
desc tbname;,desc 可以查看表定义，但是输出信息不够全面，可以通过 show create table tbname \G;来查看更加详细的信息，比如存储引擎，字符集等。"\G"选项是使得记录能够按照字段竖向排列，以便更好的显示内容较长的记录。

(4.7)删除表 
drop table tbname;

(4.8)修改表 
    大多数情况下，表结构的修改都通过alter table语句，常用的操作包括： 
<1> 修改字段 
alter table tbname modify column_name column_definition [first|after col_name] 
比如 alter table emp modify ename varchar(20); #将enmae列类型修改为varchar(20) 
<2> 增加字段 
alter table tbname add column_name column_definition [first|after col_name] 
比如 alter table emp add age int(3) #在emp表中添加列age 
<3> 删除字段 
alter table tbname drop column_name 
比如 alter table emp drop age; 
<4> 字段改名 
alter table tbname change old_col_name new col_name [new_col_definition] [first|after col_name]#[]表示其包含的内容可有可无 
比如 alter table emp change age age1 int(4);#将age字段改名为age1，同时修改字段类型为int(4) 
    change和modify都可以修改表的定义，不同的是change后面需要写两次列明，但是change可以修改列名称，而modify不能。 
<5> 修改字段排列顺序 
上面的add/change/modify操作语句中都有 [first|after col_name]选项，用来修改字段在表中的位置，add新加的字段默认是加在表的最后位置，而change/modify默认都不会改变字段的位置。 
比如 alter table emp add birth date after ename; #将新增的birth字段加在ename之后 
alter table emp modify age int(3) first; #将字段age放在最前面 
<6> 更改表名 
alter table tbname rename new_name

五、 DML语句

(5.1)插入记录 
insert into tbname (field1, field2, ...fieldn) values (val1, val2, ...valn) 
也可以不用指定字段名称，但是values后面的顺序应该和表中字段顺序一致，含可控字段、非空但是含有默认值的字段、自增字段，可以不用在insert后的字段列表里面出现，values后面只写对应字段名称的value。 
    insert语句可以一次性插入多条记录，格式为： 

insert into tbname (field1, field2, ...fieldn) values 
(val11, val12, ... val1n), 
(val21, val22, ... val2n), 
.... 
(valk1, valk2, ... valkn)

(5.2)更新记录 
update tbname set col1 = val1, col2 = val2 .. [where condition] 
    在mysql中，update命令可以同时更新多个表中的数据，格式如下： 
update t1, t2, ...tn set t1.field1=expr1, ... tn.fieldn=exprn [where condition] 
比如 udpate emp a, dept b set a.sal=b.sal*b.deptno, b.deptname=a.ename where a.deptno=b.deptno;#emp表（记为a）和dept表（记为b），a中存放雇员信息，包括雇员薪水和其所在的部门号；b中存放部门信息，包括部门号和部门名称。对于给出a的一条雇员信息记录，通过其部门号deptno，从b中找到该deptno对应的deptname.....

(5.3)删除记录 
    如果记录不再需要，可以用delete命令删除 
delete from tbname [where condition] 
在mysql中可以一次删除多一个表中的数据，格式如下： 
delete t1,t2....tn from t1,t2...tn [where condition] 如果from后面的表名为别名，则delete后面也要用相应的别名，否则会提示语法错误。 
无论是单表还是多表，如果不加where条件，会将表的所有记录删除。

(5.4)查询记录 
select field1, field2,....fieldn from tbname [where condition] 
<1> 普通查询 
比如： 
select age, deptno from emp where sal > 1000 and deptno = 1; 
select * from emp; 
<2> 查询不重复的记录 
有时候需要将表中重复的记录去掉后显示出来，可以用distnict关键字来实现： 
select distinct deptno from emp; 
<3> 排序和限制 
如果需要返回按照某个字段排序后的结果集，可以使用order by实现： 
select * from tbname [where condition] order by field1 [desc|asc], 
field2 [desc|asc] ... fieldn[desc|asc] 
排序规则默认为asc（升序排序）

(5.4)聚合 
    对数据进行汇总操作，group by 
select [field1, field2 ... fieldn] fun_name from tbname [where where_condition] 
[group by field1, field2....fieldn] [with rollup] [having where_condition]

fun_name 表示要做的聚合操作，即聚合函数，常用的有sum，count，max，min，avg等 
group by关键字表示要进行分类聚合的字段，比如要按照部门分类统计员工数量，部门就应该写在group by后面 
with rollup 可选语法，表明是否对分类聚合后的结果进行再汇总 
having 关键字表示对分类后的结果再进行条件的过滤 
having和where的区别：having是对聚合后的结果进行条件的过滤，而where是在聚合前就对记录进行过滤，如果逻辑允许，尽可能使用where先过滤记录，这样减少结果集，提高效率。

比如： 
select count(1) from emp; 统计公司雇员人数 
select deptno, count(1) from emp group by deptno; 统计各个部门的人数 
select deptno, count(1) from emp group by deptno with rollup;统计各部门人数，最后进行汇总 
select deptno, count(1) from emp group by deptno having count(1) > 1;统计部门人数大于1的各个部门以及该部门的人数 
select max(val), min(val), sum(val) from emp; 统计所有的雇员中的最大、最小、总薪水值

(5.5)表连接 
    当需要同时显示多个表中的字段，需要用到表连接。表连接分为内连接和外连接，之间的主要区别是，内连接仅选出两张表中互相匹配的记录，外连接会选出其他不匹配的记录，常用内连接。 
比如： 
内连接select ename, deptname from emp, dept where emp.deptno = dept.deptno; 
外连接分为左连接和右连接，左连接是指包含所有的左边的表中的记录，即使有些记录在右边的表中没有与之相匹配的记录； 
右连接是指包含所有的右边表中的记录，即使有些左边表中没有与之匹配的记录。 
比如： 
select ename, deptname from emp left join dept on emp.deptno=dept.deptno;emp表左连接dept表，连接后的结果中包含emp表中的所有的ename，即使该ename对应的员工的deptno在dept表中不存在。

(5.6)子查询 
    某些情况下，当进行查询的时候，需要的条件就是另外一个select的结果，这个时候需要用到子查询。用于子查询的关键字主要包括 in, not in, =, !=, exists, not exists等。 
比如： 
select * from emp where deptno in (select deptno from dept);只查找deptno在dept表中存在的那些deptno的emp中的记录。 
    如果子查询记录数唯一，还可以用 = 代替 in。 
    某些情况下，子查询可以转化为表连接，例如： 
select * from emp where deptno in (select deptno from dept); 
转化为表连接之后，为 select emp.* from emp, dept where emp.deptno=dept.deptno;

(5.7)记录联合 
    如果需要将两个表的数据按照一定的查询条件查询出来以后，将结果合并到一起显示出来，此时需要用union和union all关键字来实现这样的功能： 
select * from t1 union|union all 
select * from t2 union|union all 
.... 
select * from tn; 

    union和union all的区别在于：union all是把结果集直接合并在一起，而union是将union all的结果进行一次distinct，去除重复记录后的结果。

    比如，将emp和dept表中的部门编号的集合显示出来： 
select deptno from emp union all select deptno from dept;这样将显示所有的在emp表和deptno表中出现的deptno，去掉重复：select deptno from emp union select deptno from dept;

六、DCL语句

    DCL语句主要是DBA用来管理系统中的对象使用权限时使用，一般的开发人员很少使用。 
    比如创建一个数据库用户zl，具有对sakila数据库中所有表的select/insert权限： 
mysql -u root -p 
grant select,insert on sakila.* to 'zl'@'localhost' identified by '123';此时，数据库sakila多了一个新的用户zl，密码为123，且具有select，insert权限。 
    由于权限变更，需要将zl的权限修改，收回insert，只能对数据进行select操作： 
mysql -u root -p 
revoke insert on sakila.* from 'zl'@'localhost';

七、 查询元数据信息

    msql 5.5之后，提供了新的数据库information_schema，用来记录mysql中的元数据信息。元数据指数据的数据，比如表名、列名、列类型、索引名等表的各种属性名称。该数据库是一个虚拟的数据库，物理上不存在相关的目录和文件，通过show tables显示的并不是实际存在的表，全部是视图。 
    一些比较常用的视图： 
schemata：提供了当前mysql示例中的所有数据库的信息，show databases的结果来自于该表； 
tables：提供了关于数据库中的表的信息（包括视图），详细表述了某个表属于哪个schema、表类型、表引擎、创建时间等信息。show tables from schemaname的结果来自该表； 
colunms：提供了表中的列信息，详细表述了某张表的所有列以及每个列的信息，show columns from schemaname.tablename的结果取自该表； 
statistics：该表提供了关于表索引的信息

    利用数据库元信息进行一些操作，比如删除数据库 test1 下所有前缀为tmp的表： 
select concat('drop table test1.', table_name, ';') from tables where table_schema='test1' and table_name like 'tmp%';