MySQL的一些用法（一）

一、mysql在单表中的用法：

关系型数据库 ：建立在关系模型上的数据库系统。

实体关系图：（Entity-Relationship diagram ）这个图是用来分析实体与实体之间的关系的。

SQL语句：Structured Query Language结构化查询语言。SQL语句不依赖于任何平台，对所有的数据库是通用的。

SQL分类：①DDL （数据定义语言）                   数据定义语言 - Data Definition Language

                      用来定义数据库的对象，比如创建数据表(对数据库和数据表进行操作的)

                      create drop alter truncate (table)

                  ②DML  （数据操纵语言）                  数据处理语言 - Data Manipulation Language

                      在数据库表中更新，增加和删除记录（对数据表中的表数据进行增删改）

                      如 update， insert， delete

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

                      指用于设置用户权限和控制事务语句（事务）

                      如grant，revoke，if…else，while，begin transaction

                  ④DQL （数据查询语言）                    数据查询语言 – Data Query Language（对数据表中的表数据进行查询）

                      select

1、数据库的操作：

查看当前所有的数据库：show  databases；

查看建库语句和字符集：show create database 库名；

创建数据库：A.create database 数据库名；

                     B.create database 数据库名 character set 编码表名；

删除数据库：drop database 数据库名；

修改数据库的编码集：alter database 数据库名称 character set 字符集；

切换数据库：use 数据库名；

查看当前数据库名称：select database()；

2、数据库表结构的增删改查：

1)新增表：

create table 表名(

列名 数据类型，   

列名 数据类型，

……

列名 数据类型 (最后一个列不需要逗号)

)；

2)删除表：drop table 表名；

3)修改表：

①增加一列：alter table 表名 add 列名 类型(长度) 约束；

②修改列的类型：alter table 表名 modify 列名 类型(长度) 约束；

③删除一列：alter table 表名 drop 列名；

④修改列名：alter table 表名 change 旧列名 新列名 类型(长度) 约束；

⑤改表名：rename table 旧表名 to 新表名；

⑥改编码集：alter table 表名 character set 编码集；

4)查询表：

①查看所有的表：show tables；

②查看建表语句以及字符集：show create table 表名；

③查看表的列的信息：desc 表名；

3、数据库表内容的增删改查：

1)新增表数据：

①全写：insert into 表名 (列名，列名，列名......)  values (值，值，值......)；

②省略部分列：insert into 表名 (列名，列名)  values (值，值，值......)；

③省略所有列：insert into 表名 values（值，值，值，值）；

2)删除表数据：delete from 表名 [where条件语句]；

注意：如果没有where，删除表中的所有的记录。delete删除的是行

       truncate table 表名；先删除表,再创建表，就等于将数据全部删除了。

注：delete 和 truncate的区别是delete删除的数据在事务管理的情况下还能恢复，而truncate则不能恢复。

3)修改表数据：update 表名 set 列名=值,列名=值.... [ where条件语句 ]；

注意事项：

①、如果不加条件，将会修改某一列的所有值。②、一般修改数据时，都需要增加条件。 多个列使用逗号隔开。

4)查询表数据：

①查询所有信息：select * from 表名；

②查询指定列的信息：select 列名,列名...  from 表名；

③条件查询：select * from 表名 where 条件；

④运算符：

比较运算符：>  <  <=  >=  =  <>    大于、小于、大于/小于等于、等于、不等于

                     between...and...        in()          like        is  (not)   null

逻辑运算符：and、or、not

⑤过滤重复：select distinct 列名 from 表名 [where 条件]；

⑥别名及列运算：select 列名 as 别名,列名 as 别名,列名 as 别名.... from 表名 [where 条件]；

⑦order by 排序：select * from 表名 order by 列名 asc|desc ；

⑧关于null的问题：ifnull(列名，默认值)；

                              判断该列是否为null,如果为null，返回默认值，如果不为null，返回实际的值。

⑨SQL中的 聚合 / 聚集 函数：

注意：A、不会将null值参与计算；B、不能嵌套使用。

count 统计:select count(*) | count(列名) from 表名；

sum 求和:select sum(列名) from 表名；

avg 平均值:select avg(列名) from 表名；

max/min 最大值/最小值：select max(列名),min(列名) from 表名；

⑩group by分组查询：select … from … group by 列名；

注意：select中内容只能是被分组的列和聚合函数；分组之后，需要过滤，只能用having。

执行顺序：select(5) ... from(1) ... where(2) 条件1 ... group by(3) ... having(4) 条件2  order by(6)

4、数据库的备份和恢复：

备份：mysqldump -u 用户名 -p 数据库名 > 磁盘SQL文件路径

           由于mysqldump命令不是sql命令，需要在dos窗口下使用

恢复：A、创建数据库，导入数据

           B、创建数据库，在dos命令行中输入：

           mysql -u 用户名 -p 导入库名 < 硬盘SQL文件绝对路径

注意：在cmd下使用，不是登录mysql后使用，和备份比较类似，只不过mysql后面不带dump，并且箭头是<,指向需要导入数据的新的数据库。这种恢复的方式，也需要数据库提前存在。

二、mysql在多表中的用法：

1、多表设计：

①一对多：在一对多的表关系，应该在多方添加一方的主键作为外键。

外键约束：

添加外键需要注意的问题：

1. 如果从表要去添加一个外键约束。要求主表被引用的字段是主键或者唯一的。通常使用主键。
2. 如果要删除主表中的数据。要求在从表中这个数据，要没有被引用，才可以去删除。
3. 如果要向从表中去添加数据。要求在主表中，要有对应的数据。才可以去添加。
4. 如果要删除表。要先删除从表。然后去删除主表。
5. 新建表的时候。需要先去创建主表，然后去创建从表。

作用：保持数据的完整性，和有效性。

A、建表之后添加外键约束：alter table 从表名称 add foreign key (外键列的名称) references 主表名称(主键)；

B、建表的时候添加外键约束：在从表中后写：外键列的名称       数据类型

                                                                          foreign key (外键列的名称) references 主表名称(主键)

②多对多：在多对多的表设计中，应该添加一张中间表来维护两张表的关系。中间表添加两张表的主键作为自己的外键。

③一对一：在任意一方增加一列，引入另一方的主键作为自己的外键。

2、多表查询：

①笛卡尔积：select * from a,b；

                     两张表直接查询，数据是两张表记录的结合。

缺点：里面有很多错误的数据，需要过滤。

②内连接查询：通过两张表查找其对应的记录。

隐式内连接：select * from a,b where a.列名 = b.列名；

显示内连接：select * from a inner join b on a.列名 = b.列名；

③外连接查询：

左外连接：select * from a left outer(可省略) join b on a.列名 = b.列名；

右外连接：select * from  a  right  outer(可省略)  join  b  on  a.列名 = b.列名；

全外连接：select * from a full outer join b on a.列名 = b.列名；             注：mysql 不支持全外连接！可用union把左外连接和右外连接连接起来:相当于全外连接。

④关联子查询：将一个查询作为另一个查询的一部分。

in的用法：

in (20，30)：表示条件是20或者30，类似于添加条件select * from student where age = 20 or age = 30；

注意 : 不可以使用 id = …; 语句, 因为 id = 之后只能存放一个值, in 可以表示多个值取其中一个。

all的用法：表示所有，和union一起使用。左连接和右连接查询结果的合集。不去重！

any和some的用法：

A、any：表示任何一个。注意：any的后面需要跟语句，不能直接放值。

B、some：表示任何一个和any的作用相同，是 SQL-92标准的ANY的等效物。

as的用法：不仅可以用来做列的别名，还可以将查询结果通过as作为一张表来使用。临时表！

⑤分页查询：select * from 表名 LIMIT [offset], row_count；

limit的作用：限制查询结果返回的数量。

注意 : 记录是从 0 开始编号的。