MySQL的使用

数据库的基本操作

显示已有数据库：show databases;

创建数据库：create database 数据库名称;

示例：create database 数据库名称 default charset utf8 collate utf8_general_ci;（红色标记是字符集设置）

使用数据库：use 数据库名称;

删除数据库：drop database 数据库名称;

 

数据表的基本操作

显示已有的表：show tables;

显示表结构：desc 表名;

创建表：create table 表名 （字段1 类型，字段2 类型(长度)。。。。。）

删除表：drop table 表名;

删除表内容：delete from 表名; 带有自增的会记住之前的位置

删除表内容：truncate table 表名; #带有自增，回到原始1

 

数据库与表之间的关系

test数据库下的tb1表：test.tb1

test数据库下的所有表：test.*

所有数据库下的所有表：*.*

除grant外的所有权限 all privileges

无访问权限   usage

 

用户管理

查看已存在的用户信息：进入mysql-->show databases;(显示存在的数据库)--->use mysql（使用mysql数据库）--->desc user;（查看user表结构）--->（举例）select Host,User from user;

• 创建用户：create user '用户名'@'IP地址' identified by '密码';

• 修改用户：rename user '用户名'@'IP地址' to '新用户名'@'IP地址';

• 删除用户：drop user '用户名'@'IP地址';

• 修改用户密码：set password for '用户名'@'IP地址' = Password('新密码');

 

权限管理

查看权限：show grants for '用户名'@'IP地址';

授权：grant 权限 on 数据库.表 to '用户'@'IP地址';

示例：grant select on test.map to wang@localhost;(授予数据库test中的表map查询权限)

1.首先查看权限（usage表示没有权限）

2.授予权限

3.再次查看权限，就会显示授予的权限

取消权限：revoke 权限 on 数据库.表 from '用户'@'IP地址';

示例：revoke select on test.map to wang@localhost;(取消数据库test中的表map查询权限)

 

表内容操作（增删改查）

增：

• 增加一条：insert into 表(列名，列名...) values （值，值...）

举例：在map表中，为字段address和id插入信息分别为'beijing',30;

insert into map(address,id) values('beijing',30);

• 增加多条：insert into 表(列名，列名...) values （值，值，值...）,（值，值，值...）...

举例：在map表中，为字段address和id插入两条信息分别为('beijing',30),('shanghai',100);

insert into map(address,id) values('beijing',30),('shanghai',100);

• 从其他表中查询信息然后插入：insert into 表(列名，列名...) select (列名，列名) from 表名;

举例：表1：tb1(nid int,name varchar(20),age int) [字段分别为nid,name,age]

          表2：tb2(nid int,caption varchar(20), gender varchar(20))  [字段为nid,caption,gender]（创建表tb2，并添加数据）

        将tb2中的数据插入到tb1里面

         insert into tb1 (name,age) select  caption,gender from tb2;

这里的gender是varchar类型，age是int类型，如果插入的字符串为纯数字的时候，会自动转换，但是如果不是纯数字，执行上面命令不会显示出来age，如上age全部为0，要么报错。

 

SQL中新增列或者说添加字段的方法：

alter table 表名 add 列名 数据类型  (tb1为表名，newcolumn为新列名)

eg：alter table tb1 add newcolumn varchar(20);

 

删：

delete from 表名;

举例：delete from tb1;

delete from 表名 where 字段名='数据';

举例：delete from tb1 where name='zhao';（删除表tb1中name='zhao'的消息）

 

改：

update 表名 set age=1;(修改全部的age为1)

update 表名 set age=8 where name='wang'  将name='wang'的age修改成8）;

 

查：

select 字段 from 表名;

select * from 表名 where nid>12;(查询nid>12的全部字段内容)

 

change和modify的使用

change的相关使用：

          更改列名：alter table 表名 change 旧列名 新列名 类型;

modify的相关使用：

        更改类型：alter table 表名 modify 列名 类型;

(在使用change的时候如果之前没有旧列名，需要将该列名写两次，修改类型的时候，如果使用change,如果里面有数据，不同类型之间不能相互转换，如果没有数据，可以修改为其他的类型)

(如果只修改类型的时候，可以使用modify，在写列名的时候只需要写一次)

rename修改表名

rename table 表名 to 新表名;

 

 

有关条件的表达

where的使用：

举例：select * from tb1 where age>10;(挑选tb1中age大于10的全部信息)

• 大于 >
• 小于 <
• 等于 =
• 不等于  != 或者 <>

in 的使用  （可以理解为or）

举例：select * from tb2 where nid in (2,5); (查找tb2表中的nid为2或者5的数据)

not in 的使用

举例：select * from tb2 where nid not  in (2,5); (查找tb2表中的nid不是2或者5的数据)

between and 的使用

举例：select * from tb2 where nid between 2 and 5;  （查找表tb2中nid在2到5之间的数据）

 

有关通配符的使用

%的使用（匹配0到多个字符）

举例：select * from tb2 where caption like 'wang%';

（查找表tb2中caption是以wang开头的数据【包括wang】）

 

_的使用(只能匹配一个字符)

举例：select * from tb2 where caption like 'wang_';

（查找表tb2中caption是以wang开头，并且后面只有一个字符的数据信息）

 

有关限制的表达方式

limit的使用：

select * from tb2 limit 5;(只取数据的前五行)

 select * from tb2 limit 4,2; （从第五行开始取两行）（因为数据是从第0行开始取，所以4表示第五行）

select * from tb2 limit 2 offset 4;（ 从第五行开始取两行）（同上一个含义，表达方式不同）

 

有关排序的表达

order by 列名；

 select * from tb2 order by caption (asc);【asc当表示升序的时候可以省略】【desc表示降序】

【将表tb2中的信息按照caption列升序的方式展示出来】

select * from tb2 order by caption asc,gender asc;  （将表tb2中的数据按照caption列升序的方式排列，如果数据相同，就按照列gender升序的方式排序）

 

分组的表达【此部分有待改进】

group by 字段名【使用group by 如果后面想要添加条件，不能使用where，需要使用having】

举例：select * from tb2 group by nid; 【使用group by 进行分组的时候，只显示一个nid=8的数据】

select count(nid),nid,caption,gender from tb2 group by nid; [count表示统计数据]

• max(字段名) 【字段中最大的】
• min(字段名) 【字段中最小的】
• sum(字段名) 【字段的总和】
• avg(字段名) 【字段的平均值】

as 新名字【重命名】

 

主键和外键

MySQL中的主键和外键是为了维护关系数据库的完整性

主键是能确定一条记录的唯一标识，外键是用于与另一张表进行关联。

举例：一张包含个人信息的表A，字段分别为身份证号码，姓名，年龄，性别。因为姓名，年龄，性别都可能重复，只有身份证号码是每个人特有的，因此在这张表中，如果设置主键，就是身份证号码。另一张表B中也包含身份证号码，此时A表中的该字段就是表B的外键。

一张表只能有一个主键（可以所列组成），唯一不重复，不能为空，一般情况下设置为自增列

 

表的修改

添加列：alter table 表名 add 列名 类型

删除列：alter table 表名 drop column 列名

修改列的类型：alter table 表名 modify column 列名 类型；

修改列的名称：alter table 表名 modify column 原列名 新列名 类型；

添加主键：alter table 表名 add primary key (列名);

删除主键：alter table 表名 drop primary key;

添加外键：alter table 从表 add constraint 外键名称（比如：FK_从表_主表）foreign key 从表（外键字段）references 主表(主键字段);

删除外键：alter table 表名 drop foreign key 外键名称;

修改默认值：alter table 表名 alter 列名 set default 100;(当创建表的时候，如果表中的某一列有字段名但是没有填写信息，为该字段添加默认值，之后就不用填写信息，直接显示默认值，这里是修改默认值)

删除默认值：alter table 表名 alter 列名 drop default;

 

连表操作

在上边有说到主键和外键，当操作的信息需要用到多个表的时候，需要使用连表操作，主键和外键派上用场

表A：字段：nid(主键) ，num，age，message

表B：字段：id(主键)，a_nid(外键)，name，num 

举例：联合两个表，查看A表中的num列信息，以及B表中的name,num列信息

select A.num,B.num,B,name from A,B where A.nid = B.a_nid;

 

    无对应关系则不显示

    select A.num, A.name, B.name

    from A,B(只有这些的时候可以是笛卡尔积)

    where A.nid = B.nid

举例：联合tb1和tb2两个表，当没有where的时候，会将信息全部显示，tb1中的数据有四条，会将这四条信息分别和tb2中的10条数据结合，就会形成4*10=40条数据

当使用where的时候，因为tb1中的nid全部等于5，tb2中nid的数据中也有等于5的，所以就会只结合4条数据

 

    无对应关系则不显示【使用inner join,left join,right join连接的时候需要使用on】

    select A.num, A.name, B.name

    from A inner join B

    on A.nid = B.nid（如果结果中有null的被过滤掉）

 

    A表所有显示，如果B中无对应关系，则值为null

    select A.num, A.name, B.name

    from A left join B

    on A.nid = B.nid

 

    B表所有显示，如果A中无对应关系，则值为null

    select A.num, A.name, B.name

    from A right join B

    on A.nid = B.nid

 

组合

    组合，自动处理重合

    select nickname

    from A

    union (查询A表中的nickname列信息，以及B表中的name列信息，重合信息不显示)

    select name

    from B

 

    组合，不处理重合

    select nickname

    from A

    union all (查询A表中的nickname列信息，以及B表中的name列信息，显示全部信息)

    select name

    from B