MySQL基础操作大全（一）

本文所有自定义数据库库名均使用 database_name 代替
所有表名均使用 table_name 代替
所有列名均使用 column_name 代替
所有值均使用 value 代替

常用字段类型

• int
  • 整数类型
• float
  • float(n,m)
  • 浮点数类型，n指定共n位数值，m指定为m位小数
• char
  • char(n)
  • 定长字符串，该类型字符数量不能超过指定数量n，当少于指定字符数量时，用空格补齐至指定字符数量，读取时取出多余的空格
• varchar
  • varchar(n)
  • 变长字符串，该类型字符数量不能超过指定数量n，当少于指定字符数量时，只存储插入的字符数量

MySQL的安装与连接

• mysql的安装与运行（这里使用centos7的环境）

//安装mariadb-server
yum install mariadb-server
//检查是否安装
rpm -qa mariadb-server
//运行sql服务
systemctl start mariadb-server

• mysql数据库的连接

//mysql在刚安装完成未做用户名及密码修改时，用户名默认为root，密码为空
mysql -u root -p

库操作

• 查看所有数据库

show databases;

• 切换数据库

use database_name;

• 创建数据库

create database database_name charset=utf8; 
#通过charset指定字符集

• 查看数据库的创建信息

show create database database_name;

• 删除数据库

drop database database_name;

表操作

• 查看当前数据库中有哪些表

show tables;

• 创建表
  在创建表时，每个字段除一些基本数据类型（int，char…）外，还有一些其他的属性可以设置
  如：是否为空（默认允许为空），默认值（default 值，默认为NULL），还可以指定是否为自增auto_increment

create table table_name(
	column_name1 int not null auto_increment,  //字段存储数据为int类型，不可以为空，自增
	column_name1 int null default 0,  //字段存储数据为int类型，允许为空，默认为0
	column_name2 char(3),  //该字段储存数据为char(3)类型，其他属性皆为默认
	...
);

• 查看表的创建信息

show create table table_name;

• 查看表结构

desc table_name;

• 删除表

drop table table_name;

增删改查之查询（select）

• 查询表中数据

//查询表中全部的数据
select * from table_name;
//查询表中指定字段的数据
select column_name1, column_name2... from table_name;
//查询其他库中的表
select * from database_name.table_name;

• 分页查询
  当我们只需要查询表中的指定位置的数据时

select * from table_name order by column_name desc limit n;
select * from table_name limit n,m;

• 条件查询
  • where条件判断：
    • 比较运算符：大于 > ，小于 < ， 大于等于 >= ， 小于等于 <= ， 等于 = ，不等于 !=，查询的内容是否在集合中 in ( ) ，not in () ，返回的结果集是否存在数据 exists( ) ，not exists( )
    • 逻辑运算符：或 or （或者，只要有一个条件成立），与 and（并且，必须条件都成立）

//单条件查询
select * from table_name where column_name = value;
//多条件与运算条件查询
select * from table_name 
where column_name1 > value1 and column_name1 != value2;
//多条件或运算条件查询
select * from table_name 
where column_name1 > value1 or column_name1 < value2;
//in的用法
select * from table_name where column_name in (value1,value2...);
select * from table_name where column_name in (select column_name from table_name);
//exists用法，exists效率高于in，他只判段那目标集合中是否有返回数据存在，而不去判断之具体是什么，若有数据即为真，否则为假
select * from table_name where exists (select * from table_name);

• 模糊查询
  • 使用where column_name like ’ ’ 的格式进行模糊查询匹配
    • like模糊查询使用的通配符有：% 匹配除null外的值，_ 匹配一个字符
  • 使用 regexp 正则表达式来进行匹配，
    • 常用正则符号有：
    • ^xxx 以xxx开始的字符串；
    • xxx$ 以xxx结尾；
    • a|b 匹配a或者b；
    • . 匹配任意字符；
    • * 匹配前一个字符0或无数次；
    • + 匹配前一字符1次或无数次；
    • {n,m} 匹配前一字符n~m次；
    • ? 匹配前一个字符0或1次，非贪婪匹配，当只需要匹配到第一个匹配的字符串就停止时可用使用；
    • [a-z] 匹配a-z任意字符；
    • [^a] 匹配非a字符（^出现在 [] 中时表示取反，匹配非 [ ] 中的内容）。

//like 模糊查询,匹配包含value的列
select * from table_name 
where column_name like '%value%';
//regexp 正则模糊查询，匹配以value1开始，以value2结尾的列
select * from table_name 
where column_name regexp '^value1.*value2$';

• 常用连接查询
  • LEFT JOIN
  • RIGHT JOIN
  • INNER JOIN
• 如图为family表及job表数据：
  

//left join 左连接查询,只显示左表中所有符合条件的行，若右表不存在对应内容，则显示对应行内容为NULL
select * 
from table_name1 as t1 
     left join table_name2 as t2 on t1.column_name=t2.column_name;

左连接查询结果（绿框中的为右表job表的查询结果）：

//right join 左连接查询,只显示右表中所有符合条件的行，若左表不存在对应内容，则显示对应行数据为NULL
select * 
from table_name1 as t1 
     right join table_name2 as t2 on t1.column_name=t2.column_name;

右连接查询结果（绿框中的为左表family表的查询结果）：

// inner join 内连接查询,只显示左右表中均符合条件的行
select * 
from table_name1 as t1 
     inner join table_name2 as t2 on t1.column_name=t2.column_name;

内连接查询结果：

增删改查之插入数据（insert）

• 方式一：一次只插入一条数据

insert into table_name(column_name1,column_name2...) 
value (value1,value2...)

• 方式二：一次可以插入一条或多条数据

insert into table_name(column_name1,column_name2...) 
values (value1,value2...),(value1,value2...)...

增删改查之修改数据（update）

修改table_name表，column_name字段值为value的行column_name1=value1,column_name2=value2

update table_name 
set column_name1=value1,column_name2=value2 where cloumn_name=value

增删改查之删除数据（delete）

删除数据需谨慎，一定要想好where条件（需要删除哪些行）

delete from table_name where column_name=value

字段与表的修改操作

• 修改表名

alter table table_name rename new_table_name;

• 修改字段类型

alter table table_name modify column_name new_type;

• 修改字段名称及类型

alter table table_name change column_name new_column_name new_type;

• 添加字段

//默认添加字段在最末尾一列
alter table table_name add column_name type;
// after 指定已有字段，将新字段添加在该字段之后
alter table table_name add column_name type after column_name1;
// 在最后指定first，添加字段在第一列
alter table table_name add column_name type first;

• 删除字段

alter table table_name drop column column_name;

常用函数

1. 查看当前数据版本

select version();

2. 查看当前所在数据库

select database();

3. 查看当前数据文件位置

select @@datadir;

4. 查看当前数据库用户

select user();

5. 查询字符串字节长度，输出结果为3，若是中文utf8编码一个汉字占3个字节

select length('abc');

6. 获取sql加密后的文本

select password('password')

7. 查询字符串abc，查询起始位置为第二个字符，只查询一个字符，输出结果为b

select substr('abc', 2, 1);
select substring('abc', 2, 1);

8. 查询字符的ascii码，输出结果为97

select ascii('a');

9. 查询ascii码对应的字符，输出结果为a

select char(97);

10. 连接查询结果，作为一个字段输出，输出结果为abc

select concat('a','b','c');
select count(column_name),group_concat(coumn_name) from table_name group by column_name;

11. 统计查询到的记录数

select count(*) from table_name;

12. 求最大值，最小值

select max(column_name) from table_name;
select min(column_name) from table_name;

13. 求和，求平均值

select sum(column_name) from table_name;
select avg(column_name) from table_name;

MySQL系统库表

• 重要的库表
  • information_schema
    • schemata 存放了所有数据库的库信息
      • schema_name 数据库名字段
    • tables 存放了所有数据库中的表信息
      • table_schema 表所在的数据库名称
      • table_name 表名
    • columns 存放了数据库中所有的列及其对应库表信息
      • table_schema 数据库名称
      • table_name 表名
      • column_name 列名
  • mysql
    • user mysql数据库的用户表
      • host 允许登录的主机名
      • user 用户名
      • password 用户密码