MYSQL使用笔记

库

连接数据库

命令：

mysql -uroot -ptoot（-u用户名 -p密码）

例如：

创建数据库

代码：

create database 数据库名称;

例如：

查看数据库

命令：

show databases;

例如：

选择数据库

命令：

use 数据库名称;

例如

删除数据库

命令：

drop database 数据库名;

表

创建表

命令：

create table 表名(属性名，类型名，是否为空，默认值，注释）；

例如

这里创建了一个表seet1，其中有字段名

id,类型为int，不能为空，注释为ID
name，类型为char型长度为10，可以为空（因为没有设置not null)默认值为‘张三’注释为姓名

存储引擎为InnoDB，默认值，串类型为gbk；

显示表

简单显示表
命令：

desc 表名;

例如：

显示表的全部字段
命令：

show full columns from 表名;

例如

详细显示表的属性
命令：

show create table 表名;

例如

修改字段

在表创建后可以对表中的字段进行修改
命令：

alter table 表名 modify 字段名 (修改的信息);

例如：将表中的id修改为varchar(2)型，注释为学号

修改字段名称
命令

alter table 表名 change 字段名称 新名称 新的数据类型

重命名表
命令

alter table 旧表 rename  新表
或者
rename table 旧表 to 新表

增加字段

在表创建之后还想增加字段
命令：

alter table 表名 add （字段信息);

例如：增加一个sex的字段，enum(‘男’,‘女’) 默认值为 男，注释为性别

这里可以对表中的字段进行添加主键和绑定外键的操作

添加主键
命令：

alter table 表名 add primary key （字段名)

例如：将id设置为主键

添加外键
命令：

alter table 表名 add 字段名 foreign key 表名(字段名) references 表名(字段名)

当添加外键时会报该错误，原因是两者的类型不同，比如：varchar 与 int，或者 int无符号 与 int有符号，都不行，必须要一模一样才可以，修改完成后，就可以添加外键了。

修改的是表2中的ID属性，改为为了varchar，这样就与表一中的name的类型一样，这就可以进行添加外键的操作了。

删除字段

命令:

alter table 表名 drop 属性名

删除外键

首先要知道外键是什么
命令：

show create table 表名

删除外键：

alter table 表名 drop foreign key 外键名

报错是因为seet2中没有该外键名称，只有seet1中有该键，所以在seet1中将外键删除即可。

重命名外键名称

上面再创建外键时会自动为我们生成一个外键名称
要想自己创建名称，就要使用如下
命令：

constraint 约束名称 [primary key(属性名)|foreign key(属性名) references 表名(属性名)|unique key(属性名)|check 语句]

这里是要再创建表的时候才能对其进行重命名的语句

设置默认递增

在为创建表的时候在属性中加入auto_increment即可。
创表后
命令：

alter table 表名 auto_incremenr = 1000;

设置字段排序

命令：

alter table 表名 modify 字段1 字段1类型 [first] [after] 字段2

first 和 after为自选字段
first默认为第一个属性
after为将字段1放到字段2之后

更改表的属性

命令：

alter table 表名 engine=引擎名

删除表

命令：

drop table 表名

因为有外键不能进行删除，
所以我们要删除外键。
删除了外键就可以将表进行删除操作了。

重名名外键名称
命令：

constraint <重命名的名称> foreign key (属性名) reference 表名 (属性名)

这里的指令是在创建数据表时使用的

复制表

仅仅复制表1的结构到表2中
命令：

create table 表2 like 表1

例如：
复制book表的表结构到bb表中

复制表1的结构以及数据到表2中
命令：

create table 表2 as select * from 表1

插入数据

为所有字段插入数据

命令：

insert into 表名 values（值1，值2，……);

为表的指定字段插入数据

在表名后列出所有字段，用来更改传值顺序，或者选择插入
命令：

insert into 表名(字段1，字段2，……) values(值1，值2……);

同时插入多条记录

命令：

insert into 表名(字段1，字段2，……) values(值1，值2……），(值1，值2……），(值1，值2……);

不同的值用逗号隔开

从目标表中插入数据

命令：

insert into 表名 [属性列表] select 列表名 from 表名;

replace插入语句

命令：

replace [into] 表名[属性列表] values(值1，值2，……);

采用该方法进行插入时，如果插入的记录的主键值或者唯一约束的字段值与已经有记录的值相同，则已有记录会被删除后在添加新的记录

修改数据

命令：

update 表名 set 属性1=值1，属性2=值2…… where 条件表达式;

删除数据

命令：

delete from 表名 [where 条件表达式];

truncate清空语句

命令：

truncate table 表名;

单表查询

select语法

命令：

select 字段名 from <表名或视图名>
[where 条件表达式]
[group by <列名 1>]
[having <条件表达式>]
[order by <列名2> [asc|desc]]
[limit 子句]

查询所有字段

命令：

select * from 表名;

指定字段查询

命令：

select 属性1,属性2…… from 表名

避免重复数据查询

命令：

select distinct * from 表名

这里先插入了和4相同姓名和性别的5，将name和sex设置为查询字段时，采用distinct查询就会只查询到一个

采用all查询时就会出现两个Jerry，查询中默认使用all

为表和字段取别名

命令：

select <字段属性1> as 别名1,<字段属性2> as 别名2…… from 表名1 别名1，表名2 别名2……;

from子句

用于指定数据源
可以使用数据源.*来获取到该数据中的所有列中的数据

select 表名.* from 表名;

可以在选择列的时候对列中的字段进行显示的操作

select (属性1表达式) from seet1

where子句

对记录进行筛选，表示关系运算中的选择运算

between and

查询数据在数值1和数值2之间的数据
命令：

select 属性1…… from 表名 where  属性名 between 数值1 and 数值2; 

[not] in

筛选数据在集合中的所有信息
命令：

select 属性…… from 表名 where 属性 in (元素1，元素2……);

is [not] null

筛选字段是否为null的所有信息
命令：

select 属性…… from 表名 where 属性 is not null

注意和=null的区别
=null是表示字段为null字符串的信息
is null表示该字段为空的信息，两者是不同的

like

匹配关键字
%：匹配多个字符
_：匹配单个字符
命令

select 属性…… from 表名 where 属性 like “a%”;

注意：

查询姓名以M开头中间有_且倒数第二个字符时n的用户信息

高级查询

SQL提供了统计函数进行数据统计

查询性别为男的id之和
命令

SELECT sum(id) from seet1 where sex='男'

分组查询

GROUP BY

可以将查询到的结果按照一个字段或多个字段进行分组，并可以对分组进行统计
语法：

select * from 表名  group by 分组标准 [having 表达式] [with rollup]

• 分组标准可以是一个字段，也可以是多个字段
• 查询结果是每一个分组显示一条数据
• having 表达式 和group by 搭配使用，且只能用于分组查询，注意和where之间的区别，筛选条件可使用统计函数，或子函数查询，可以使用别名
• with rollup 在所有的记录后面加上一条记录，对上面的记录进行求和
  

统计每个性别的用户的个数

查询每个班级中的男女个数分别为多少

SELECT class,sex,count(*) as "总人数" from seet1 GROUP BY sex,class

having

分组筛选
用于分组后的显示数据在进行筛选
统计人数大于3的班级总人数

with rollup

分组汇总
查询每个班级中的男女个数分别为多少，并输出总人数

排序查询结果

order by

命令

order by 属性名[asc|desc]……

例如统计所有用户信息，并以id降序排序

限制查询结果的数目

limit

语法：

limit {[offset,]row_count|row_count OFFSET offset}

例如：
查询从第二个开始的后面5条数据

多表查询

内连接查询

等值连接

命令：

select * from 表1 inner join 表2 on 表1.属性1=表2.属性1

这里的两个属性必须存在相同的元组，属性名不同可以，但是其中的值必须要相同

自然连接

select * from 表1 natural join 表2

得到的表相同字段只显示一列

不等值连接

命令：

select * from 表1 inner join 表2 on 表1.属性1!=表2.属性1

外连接查询

左外连接

命令：

select * from 表1 left join 表2 on 表1.属性=表2.属性

左连接就是右边属性不存在，而左边属性存在，则会将右边属性赋值为空值，然后和左边进行连接

右外连接

命令：

select * from 表1 right join 表2 on 表1.属性=表2.属性

子查询

使用in，not in，any，all，exist等关键字来进行嵌套查询

带IN的子查询

命令：

select * from 表1 where 属性1 in (select 属性1 from 表 where ……)

例如：
查询看过红楼梦的读者信息

带exists的子查询

命令：

select * from 表1 where exits (select * from 表2 where ……)

exist 后面的查询语句有返回值，则前面的select就进行查询
例如：
借阅了机械工业出版社出版的所有书的读者信息

带有any的子查询

命令：

select * from 表1 where 属性1 >[<|=] any(select 属性1 from ……)

例如:
查询年龄比任意一个女生大的男生信息

带有all的子查询

命令：

select * from 表1 where 属性1 >[<]all(select 属性1 from ……)

例如：
查询比所有年龄都大的读者信息

创建索引

命令：

create index 索引名称 on 表名(属性名[(长度)][asc|desc])

例如
在表book的bname的前5个字段加上名为book_index的索引，默认为倒序排序

也可以在创建表的时候添加索引
就想当时在表的属性上添加主键属性和外键属性一样
例如：

create table 表名(
……
index 索引名 (属性名2(长度),属性名2……)
);

查看索引

命令：

show index from 表名

删除索引

命令：

drop index 索引名称 on 表名

或者使用alter语法
命令：

alter table 表名 drop index 索引名

创建视图

命令：

create [algorithm={undefined | merge | temptable} 
view 视图名称 
as select * from 表名

例如
在book表上创建一个只含有rno和borrowtime属性的视图

删除视图

命令：

drop view 视图名称

查看视图定义

命令：

desc 视图名称
show create view 视图名称

修改视图定义

命令：

create or replace view 视图名称 as select ……
或者
alter view 视图名称 as select……

更新视图数据

命令：

create view 视图名称(属性列表) as select ……
或者
update view 视图名称 set 属性1=value1 where ……（这里要满足一定的条件)