MySql基础知识总结

MySQL详细资料整理

1 . 前言介绍

   1.1 主流数据库的介绍

     1.1.1数据库：俗称数据的仓库。

     1.1.2数据结构：数据的组织方式。

     1.1.3数据库管理系统：方便管理数据的软件（或程序）

     1.1.4目前常用以下几种数据库：

        Oracle，甲骨文公司的产品。 当前最流行应用最广泛的数据库软件。和java语言兼容非常好。适合中大型，中大应用。

        SQL Server: 是微软公司的产品。window平台应用非常广泛。和c#，net平台兼容非常好。

        DB2： IBM公司的产品。IBM服务器--> UNIX -> DB2- > Websphere

        MySQL:开源组织的产品。甲骨文公司的产品。免费！！！和java语言兼容非常好！适合中小企业，中小应用

大型数据库：Oracle ，DB2，Infix

中小型数据库：SQL Server ,MySQL

微型数据库：Access , SQLLite数据库

   1.2  一个数据库中有哪些数据库对象？

    表:存放数据的容器

    视图：虚拟的表，数据来源于表

    存储过程：对数据库中表或者视图进行综合操作的命令的集合（主动操作）

    触发器：当对表做增删改操作时自动作出某些操作的使用（被动记录某些操作）

1.3  SQL（Structure Query Language）结构化查询语言

        1.3.1 DDL（Data Definition Language）  数据定义语言

包括：create(创建数据库对象),alter(修改数据库对象的结构)和drop(删除数据库对象)

（1）创建对象：

创建数据库： CREATE DATABASE bank;

创建表：

Create Table [if not exists] 表名(

列名1 数据类型 相关约束,

列名2 数据类型 相关约束,

....

列名n 数据类型 相关约束

);

（2）修改表

添加列：Altertable 表名 add 列名 【数据类型  相关约束】 ;

修改列：Altertable 表名 modify 列名 【数据类型  相关约束】;

删除列：Altertable 表名 drop 列名 【相关约束】;

删除表：Droptable 表名;

1.3.2  DML（Data ManipulationLanguage）  数据操纵语言

包括:Insert(插入数据),update(修改数据),delete(删除数据);

（1）插入数据：Insert into 表名[(字段列表)] values(值列表1)[,(值列表2),...(值列表n)];

（2）修改数据：Update 表名 set 字段名1=字段值1,字段名2=字段值2,.. [where 条件表达式]；

（3）删除数据：Delete from 表名 [where 条件表达式]; truncate table 表名：

★★★面试题★★★★ 使用delete和truncate table删除数据的区别： 相同点：都能删除表中的数据。 不同点： 1.使用delete from 删除数据可以加where条件，数据可以回滚，记录操作日志，速度慢，触发触发器 2.使用truncate table删除数据不可以加where条件，数据不可以回滚，不记录操作日志，速度快,不触发触发器。

 1.3.3  DQL（Data Query Language）  数据查询语言

包括Select(查询)

（1）格式如下：

Select 字段列表|*

from 表

[where 条件表达式

 group by 分组表达式

 having 筛选表达式

order by 分组表达式

limit 分页表达式];

1.3.3 DCL (DataControl Language) 数据控制语言

包括grant(授权),revoke(收回授权);

    （1）授权：

Grant 权限列表 on 数据库.数据库对象 to 用户名@’ip地址’ identified by 密码;

如：grant insert,select on hibernate.* to john@'%(代表任何主机都可以连接到当前数据库服务器)|192.168.1.150' identified by '123';

    （2）收回授权：

Revoke 权限列表 on 数据库.数据库对象 from 用户名@主机名;

如： revoke insert on hibernate.* from john@”%”;

2 使用数据库

   2.1 mysql 常用命令

    （1）连接本机数据库： mysql –u名称（数据库名称） –p密码（数据库密码）  回车

    （2）连接远程数据库：mysql –h（host缩写） ip地址（主机ip） –u名称（数据库名称）–p密码（数据库密码）

    （3）数据库退出，使用 exit; quit

    （4）切换数据库：use  数据库名

    （5）更改用户密码：

    mysql>usemysql;

       mysql> UPDATE user SETpassword=PASSWORD("new password") WHERE               user='username';

       mysql> FLUSH PRIVILEGES;

       mysql> exit;

    （6）显示表：showtables;

    （7）表的详细描述：describe+ 表名称   ;   desc + 表名称

    （8）刷新数据库：flushprivileges 

2.2要连接到一个数据库，有以下几大要素：

数据库服务器ip地址，数据库用户名，数据库密码，Mysql端口号，数据库名称

3、SQL语句使用（重点）

   3.1  DDL （Data DefinitionLanguage）数据库定义语言

包括： create(创建数据库对象) alter(修改数据库对象的结构) drop(删除数据库对象)

    （1）查询所有数据库：   show databases;

    （2）选择使用的数据库：use + 数据库名称----→ 进入该数据库了。

    （3）删除数据库：      drop database  [if exists] 数据库名;

    （4）创建数据库： createdatabase  [if not exists] 数据库名;

    （5）删除表：drop table[ if exists] 表名称；

    （6）创建表：createtable  [if not exists] 表名称 (

                        列名1 数据类型相关约束,

                        列名2 数据类型相关约束,

                                ....

                        列名n 数据类型相关约束

                  );

    （7）如何判断一个表是主(父)表还是从(子)表?

答：如果一个表中有外键字段，则含有外键字段的表是从表或者是子表。 一个字段在当前表中是主键，在另外一个表中是外键，则当前这个表就是主表。

    （8） 注释的作用有哪些：

A: 解释代码的功能       B:屏蔽掉暂时不需要的代码。

    （9） 查看表中的数据：select* from 表名称； （*代表查看所有列）

    （10）给表添加字段：alter table  表名 add 新列名  新列的数据类型;

            如：ALTER TABLE  t_student ADD   qq VARCHAR(11);

    （11）修改表字段的数据类型：ALTER TABLE表名 MODIFY 列名 新数据类型;

            如：ALTER TABLE t_student MODIFYqq  char(11);

    （12）修改表字段的名称及数据类型:ALTER TABLE 表名 CHANGE 原列名 修改后的新列名 修改后新列的数据类型;

    如：ALTERTABLE t_student CHANGE qq msn varchar(11);

    （13）删除字段：ALTER TABLE表名 DROP 列名;

            如 :  ALTER TABLE t_student DROP msn;

    （14）修改表的名称:ALTER TABLE原表名 RENAME TO 新表名;

    如：ALTERTABLE t_student RENAME TO student;

   3.2 DML（Data Manipulation Language）数据库操纵语言

    包括：Insert(插入数据) update(修改数据)  delete(删除数据)

    3.2.1 语法结构：

    （1）insert(插入数据) insert into 表名[(字段列表)]values(值1，值2，值3);

表中插入数据的三种形式： 第一种：#只要字段名和字段值一一对应，字段的书写顺序不重要 insert into  表名称 (列名1，列名2) VALUES(值1，值2); 第二种：#字段列表可以省略，但必须要求值列表和字段列表一一对应 insert into  表名称  VALUES(值1，值2，值3); 第三种：#可以一次插入多条记录 insert into  表名称  VALUES ('c004','班级四'), ('c005','班级五'), ('c006','班级六');

    （2）update(修改数据) update 表名 set 字段1=值1,字段2=值2,.. [where 条件表达式]；

    （3）delete(删除数据) delete from 表名 [where 条件表达式];

3.3       DCL（Data Query Language）数据库查询语言

    包括：select（查询）等

    3.3.1语法结构：（【】内按顺序书写，不得颠倒位置）

        Select字段列表 | *

        from表名称

        【where 条件表达式

        groupby 分组表达式

         having 筛选表达式

        orderby 分组表达式

        limit  分页表达式 】

    （1）查询时去除重复记录：distinct

    （2）查询时添加常量列  用as

    如：select * , '中国' as country from student; //在后面增加一栏 country 内用全部是中国。

    （3）逻辑条件： and(与) or(或)

    （4）比较条件

Sql语言中有以下比较条件：  

        大于：>

        小于:<

    大于等于: >=

    小于等于: <=

        等于:  =

     不等于: <>

    在XX和XX之间：between XX and  XXX  等价于大于等于XX，小于等于XXX 

    （5） In   -- 字段值 in (值1,值2,值3......) 

    （6）判空条件    is null 和  is not null 之时判断是否是null，跟空字符串无关

    （7）模糊查询 like    %匹配任意多个字符  _匹配任意一个字符

如： -- 查找姓葛的学生

select * from tbl_student where stuName like '葛%';

如： -- 查找名字里面带有葛的学生

select * from tbl_student where stuName like '%葛%';

如： -- 查找姓葛且名字是3个字的学生

select * from tbl_student where stuName like '葛__';

      3.3.2 记录查询

（1）常用的聚合函数如下：总和：sum ( )，平均:avg( )，最大：max( )，最小:min()，总个数：count(  )

思考：当查询一张表内有多少条记录时，使用哪种查询时最方便的？

#count(*):一般不建议大家使用*,效率低（如果有100000000条记录的话，估计出结果要很久了~） #count(字段):自动过滤掉null值的字段 #count(主键字段):建议大家使用主键字段   (使用此查询) ✔

 

    3.3.3排序

-order by

-- 语法 ：order by 字段 asc/desc

-- asc: 顺序，正序。数值：递增，字母：自然顺序（a-z）

-- desc: 倒序，反序。数值：递减，字母：自然反序(z-a)

      3.3.4分组（ group by ）

分组后筛选（重点）

-- 分组后的条件筛选 使用having关键字:对分组之后的结果再进行筛选。

where不能用于group by之后，它只能用于分组之前

      3.3.5分页查询(limit 起始行，查询几行)

- 分页查询 limit 参数1参数2

-- 参数1：查询的起始索引位置 

-- 参数2：每页查询的条数

公式：#显示第一页数据的公式：起始索引=(当前页-1)*每页显示记录数

      3.3.6 数据约束

（1）概念：对用户操作的表的数据进行约束。

（2）默认值：DEFAULT  ,可以对默认段插入null 或非null

（3）非空约束： NOT NULL

（4）唯一约束：UNIQUE 

    注意：

1）唯一的字段可以插入null 2)  唯一的字段可以插入多个null，唯一约束对Null没有约束

（1）主键(非空+唯一)

主键约束：primary key 对一个字段加上主键约束，控制字段的值不能为null，而且必须唯一，不能重复。

    注意：

主键字段，不能插入null，但是可以插入空字符串””，如果主键字段是int类型的，插入空字符串的时候，实际插入的是0值。但是因为有唯一约束，所以不能重复插入空字符串。

（2）外键约束（foreign key ）

外键约束：约束两张表的数据。

外键定义语法：  constraint 外键约束名称 foreign key(外键字段) references 主表名称(引用字段)

被约束的表叫做副表，外键设置在副表上面，外键引用字段所在的表叫做主表。

    注意：

1) 当有外键约束之后，添加数据的时候，先添加主表数据，再添加副表数据 2) 当有了外键约束，修改数据的时候，先改副表的数据，在修改主表的数据。 3) 当有了外键约束，删除数据的时候，也是先删除副表的数据，再删除主表的数据

（3）自增长 （auto_increment）

设置自增长之后的字段，可以不赋值，默认的自动

增长1

    注意：

1) 自增长只能用于int数据类型的字段 2) 自增长如果用于小数点类型的字段，当该字段不赋值的时候，仍然是按照整数在递增的。 3) 自增字段只能被设置在主键字段上 4）可以使用zerofill 来设置int类型的字段增长的时候的格式：使用0来填充剩下的位数。zerofill需要紧跟在int类型的后面

（4） 级联操作

问题： 当有了外键约束的时候，必须先修改或删除副表中的所有关联数据，才能修改或删除主表！但是，我们希望直接修改或删除主表数据，从而影响副表数据。可以使用级联操作实现！！！

级联修改：on update cascade

级联删除：on delete cascade

注意：级联操作必须在外键的基础上使用

      3.6.7高级查询

（1）表连接

A：内连接查询

两种查询形式：（以emp表为例）

第一种：表与表之间用“，”连接：select e.ename ,m.ename  from emp e ,  emp m  where e.mgr = m.empno;

第二种：表与表、条件之间 用“表名1 inner join 表名2 on 关联条件”：

    selecte.ename, m.ename from emp e  inner joinemp m on e.mgr = m.empno;

B：左【外】连接

语法：Left [ outer ]   join 表  on关联条件

使用左边表的数据去匹配右边表的数据，如果符合连接条件的结果则显示，如果不符合连接条件则显示null

C：右【外】连接

语法：right [outer] join 表 on  关联条件

使用右边表的数据去匹配左边表的数据，如果符合连接条件的结果则显示，如果不符合连接条件则显示null

D：自连接

表内相关的数据连接，如emp表中的雇员编号与领导编号。其中领导也是雇员。 e.mgr = m.empno

（2）子查询

如下格式：例：

SELECT stuName , age , gender , (SELECT cityName FROMt_city B WHERE B.id=A.cityId)

FROM t_student A;

（3）分页查询

格式： 起始索引=(当前页号-1)*每页显示记录数；

           结束索引=当前页号*每页显示记录数 ；

（4）视图 （重点部分）

1、概念：是一个封装了一些命令的集合体。本身不存放数据，数据来源于命令查询的表中。

2、创建视图格式： create view 视图名 as 查询命令；

3、代码演示：

#创建视图

create view  v_myview01 as SELECT ename,job,dname FROM t_emp e,t_dept d WHERE e.dept_no=d.dept_no; -- 把视图当做表使用即可，但需要注意的是：通过视图做增删改有条件的，做查询可以直接做 SELECT * FROM v_myview01; -- 创建视图，如果已经存在则直接删除后创建新的视图 CREATE OR REPLACE VIEW v_myview01 AS SELECT * FROM t_dept; -- 修改视图 ALTER VIEW v_myview01 AS SELECT emp_no,ename,job,dname FROM t_emp e,t_dept d WHERE e.dept_no=d.dept_no; -- 删除视图 DROP VIEW v_myview01; -- 通过视图修改关联表中的数据，有条件的：所有操作影响的字段必须来源于同一张表，否则操作失败 UPDATE v_myview01 SET dname='公共部',loc='湖南' WHERE dept_no=5; SELECT * FROM t_emp; -- error,因为本次修改的字段来源于两张表 UPDATE v_myview01 SET ename='龙儿', dname='市场部' WHERE emp_no=4; -- 通过视图删除关联表中的数据 DELETE FROM v_myview01 WHERE dept_no=6;

（5）存储过程

1  概念：带有业务逻辑的sql语句。里面有流程控制语句。如 if条件判断，while循环等等

2  存储过程的特点：

    ① 执行效率非常快，存储过程是在数据库的服务器端执行的。

    ② 移植性很差，不同数据库的存储过程是不能移植的。

3  存储过程语法(存储过程不能对存储内容进行修改，只能对名称进行修改。如果想要修改需要重新建立一个新的存储过程)

—    创建存储过程  create procedure 存储过程名字（参数）

     — 调用存储过程 call 存储过程名字 （参数）

     — 删除存储过程 drop procedure 存储过程名字

在定义存储过程的时候，参数定义如下：

    IN ：（实参传递给形参）表示输入参数，可以携带数据到存储过程中

    OUT : （形参反向传值给实参）表示输出参数，可以从存储过程中返回结果

    INOUT ：（实参可以传值给形参，同时形参也可以将值传给实参）表示输入输出参数，既可以输入功能，也可以输出功能   

4  输入参数的存储过程（ in ）

/*创建带有参数的存储过程*/ delimiter #  -- 声明存储过程的结束符 create  procedure  pro_empno(  in  pro_empno_pragm  int ) – 创建存储过程，声明形参 begin     SELECT * FROM EMP WHERE empno = pro_empno_pragm; end  # delimiter ;   ------------------ call  pro_empno(8888); -- 调用存储过程，传入实际参数 ------------------- drop  procedore  pro_empno; /*删除存储过程*/

5  输出参数的存储过程（out ）

drop  procedore  pro_emp_out; #删除存储过程 delimiter $ create  procedore  pro_emp_out ( out param_count int ) begin   #set param_count=5;   SELECT COUNT(1) INTO param_count FROM student; end $ delimiter ; -- 中间必须有空格，否则报错 ---------------------------- call  pro_emp_out(@param_max); -- 调用带输出参数的存储过程;@表示定义局部变量 select @param_max; -- 显示变量的值

      3.6.8 mysql变量

1、全局变量：mysql数据库内置的变量 （所有连接都起作用）

    （1）查看所有全局变量： show variables

    （2）查看某个全局变量： select @@变量名 （一个@表示局部变量，两个@表示 全局变量）

    （3）修改变量：set 变量名=新值

    （4）character_set_client: mysql服务器的接收数据的编码

    （5）character_set_results：mysql服务器输出数据的编码

2、会话变量: 只存在于当前客户端与数据库服务器端的一次连接当中。连接端口，会话变量丢失

    -- 定义会话变量: set @变量=值

-   - 查看会话变量： select @变量

 3、局部变量：在存储过程中使用的变量就叫局部变量。只要存储过程执行完毕，局部变量就丢失！！

    范例：使用创建存储过程计算1+2+3+…+100的值。

DELIMITER // CREATE PROCEDURE pro_sum(IN num INT,OUT result INT) BEGIN DECLARE i INT DEFAULT 1;#在mysql中如何定义局部变量并赋值 DECLARE psum INT DEFAULT 0;   WHILE i<=num DO SET psum=psum+i; SET i=i+1; END WHILE; SET result=psum; END // DELIMITER ;#在delimiter和;中间必须有空格，否则挂掉   #调用存储过程 CALL pro_sum(100,@result); SELECT @result;

3.6.9触发器

代码演示：

#切换数据库 USE test; DROP TABLE t_info; #创建信息表 CREATE TABLE t_info(          id INT PRIMARY KEY AUTO_INCREMENT,          content VARCHAR(200),          commentNum INT DEFAULT 0,          pubDate DATETIME ); #插入测试数据 INSERT INTO t_info(id,content,pubDate) VALUES(NULL,'读书有用吗？',NOW()); #查看数据 SELECT * FROM t_info;   DROP TABLE t_comment; #创建评论表 CREATE TABLE t_comment(          id INT PRIMARY KEY AUTO_INCREMENT,          content VARCHAR(20),          infoId INT,          pubDate DATETIME ); #创建触发器,魔表new:代表新插入的一条记录 CREATE TRIGGER trg_insert AFTER INSERT ON t_comment FOR EACH ROW UPDATE t_info SET commentNum=commentNum+1 WHERE id=new.infoId; #插入测试数据 INSERT INTO t_comment VALUES(NULL,'百无一用是书生',1,NOW()); INSERT INTO t_comment VALUES(NULL,'书中自有颜如玉',1,NOW()); #查看数据 SELECT * FROM t_comment; SELECT * FROM t_info;   DROP TABLE t_user; #创建用户表 CREATE TABLE t_user(          id INT PRIMARY KEY AUTO_INCREMENT,          userName VARCHAR(20) UNIQUE,          pwd VARCHAR(10) NOT NULL,          money DOUBLE,          pubDate DATETIME ); #插入测试数据 INSERT INTO t_user VALUES(NULL,'admin','123',10000,NOW()); INSERT INTO t_user VALUES(NULL,'hsj','123',20000,NOW()); #查看数据 SELECT * FROM t_user; #模拟转账 UPDATE t_user SET money=money+1000 WHERE userName='admin'; UPDATE t_user SET money=money-1000 WHERE userName='hsj';   DROP TABLE t_log; #创建日志表 CREATE TABLE t_log(          id INT PRIMARY KEY AUTO_INCREMENT,          content VARCHAR(200),          pubDate DATETIME ); #插看数据 SELECT * FROM t_log; #删除触发器 DROP TRIGGER trg_update; #创建修改触发器,当触发器做修改操作时有两个魔表，old,new CREATE TRIGGER trg_update AFTER UPDATE ON t_user FOR EACH ROW INSERT INTO t_log VALUES(NULL,CONCAT('transfer',(new.money-old.money)),NOW()); #进行测试触发器 UPDATE t_user SET money=money-1000 WHERE userName='admin'; SELECT * FROM t_user; #删除触发器 CREATE TRIGGER trg_delete AFTER DELETE ON t_user FOR EACH ROW INSERT INTO t_log VALUES(NULL,CONCAT('delete ',old.userName),NOW()); #测试删除触发器 DELETE FROM t_user WHERE id=1;

3.6.10 mysql权限

（1）password:md5 单向加密函数，不能解密

   如：selectpassword("123456");   加密码是： *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9

（2） 修改用户密码

第一步：UPDATE USER SET PASSWORD=PASSWORD('root') WHEREUSER='root';

第二步：FLUSH PRIVILEGES;

3.6.11数据库备份与还原（重点）

A:数据库备份（不需要登陆）：

--cmd命令窗口：

    mysqldump -u+用户名  -p+密码   数据库名称 >d:\XXXX.sql（存放路径以及文件名）

B：数据库还原(不需要登陆)：数据库还原的时候一定要指定数据库

   1. 首先新建空的数据库：登陆MySQL,建立数据库。

create database 数据库名；

   2. 在cmd命令窗口运行如下代码：

mysql -u+用户名  -p+密码  数据库名<d:\mysql\bank.sql （存放路径以及文件名）

3.4       DCL （Data Control Language）数据控制语言

    包括：grant(授权) 、 revoke(收回授权)

1、grant(授权)     语法结构：

Grant 权限列表  on  数据库.数据库对象 to 用户名@’ip地址’ identified by 密码;

实例：创建用户hsj,密码为123的账户，并指定拥有insert,select权限。

grant insert,select on bank.t_user to hsj@'%' identified by '123' ; //其中% 表示所有。

2、 revoke(收回授权) 语法结构：

 Revoke 权限列表 on 数据库.数据库对象 from 用户名@主机名;

实例：收回bank.t_user表上的查询权限

revoke select on bank.t_user from hsj@'%';

3、相关练习如下：

打开命令行窗口输入如下内容登录：

Mysql -u+用户名-p+密码 ： 如：Mysql -uroot -proot

① .创建用户hsj,密码为123的账户，并指定拥有insert,select权限。

grant insert,select on bank.t_user to hsj@'%'identified by '123';

②.重新启动命令行使用上面的账户登录并查询数据。

打开新的命令行窗口输入如下内容登录：

mysql -uhsj -p123

select * from bank.t_user;

③.收回再bank.t_user表上的查询权限

revoke select on bank.t_user from hsj@'%';

④.继续执行查询操作

select * from bank.t_user;

报如下错误：

ERROR 1142 (42000): SELECT command denied to user'hsj'@'localhost' for table 't

_user'

说明当前账户已经没有查询数据的权限了。

但可以插入数据。

insert into bank.t_user values(null,'msn','123','佳平',20,'男','武汉');

可以成功运行。

⑤.如果想查询数据，必须先再次通过之前的窗口授予查询权限：

grant select on bank.t_user to hsj@'%' identified by'123';

⑥.再次执行查询即可查到数据：

select * from bank.t_user;