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