mysql使用笔记(二)

时间:2021-05-26 18:57:26

一、启动和关闭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%';