MySQL基本操作

一.MySQL数据库基本操作

1.SQL分类

数据库：database

表：table，行：row 列：column

索引：index

视图：view

存储过程：procedure

存储函数：function

触发器：trigger

事件调度器：event scheduler，任务计划

用户：user

权限：privilege

2.SQL语言规范

1. 在数据库系统中，SQL 语句不区分大小写，建议用大写
2. SQL语句可单行或多行书写，默认以 " ; " 结尾
3. 关键词不能跨多行或简写
4. 用空格和TAB 缩进来提高语句的可读性
5. 子句通常位于独立行，便于编辑，提高可读性

3.数据库对象和命名

数据库的组件(对象)：

数据库、表、索引、视图、用户、存储过程、函数、触发器、事件调度器等

命名规则：

必须以字母开头，后续可以包括字母,数字和三个特殊字符（# _ $）

不要使用MySQL的保留字,如tabble select show databases

4.SQL语句分类

SQL语句用于维护管理数据库，包括数据查询、数据更新、访问控制、对象管理等功能。

SQL语句分类：

语句	代表的意思
DDL	数据定义语言，用于创建数据库对象，如库、表、索引等
DML	数据操纵语言，用于对表中的数据进行管理
DQL	数据查询语言，用于从数据表中查找符合条件的数据记录
DCL	数据控制语言，用于设置或者更改数据库用户或角色权限

5.查看帮助信息

#help后面跟上具体命令可以查看帮助
例如：mysql> help create

6.查看、修改字符集

1. #查看支持字符集
show charset;
默认拉丁文字，需要修改为
 
utf8     | UTF-8 Unicode   
#阉割版的
 
utf8mb4  | UTF-8 Unicode 
#真实的版本
 
2. #修改字符集
vim /etc/my.cnf
[mysqld]
character-set-server=utf8mb4

二.管理MySQL数据库

1.查看数据库结构

1.1?查看当前服务器中的数据库

show databases;					#大小写不区分，分号“;”表示结束

1.2 查看数据库中包含的表

方法一：在库中查看表
use mysql;             #use 数据库名
show tables;           #查看表
 
法二 ：在库外查看表
show tables from mysql;    #show tables from 数据库名

1.3?查看表的结构（字段）

use 数据库名;
describe [数据库名.]表名;
可缩写成：desc 表名;

1.4 查看mysql版本

mysql -V

2.数据类型

2.1 常用数据类型

类型	说明
int	整型，用于定义整数类型的数据
fload	单精度浮点4字节32位，准确表示到小数点后六位
double	双精度浮点8字节64位
char	固定长度的字符类型，用于定义字符类型数据。
varchar	可变长度的字符类型
text	文本
image	图片
decimal(5,2)	5个有效长度数字，小数点后面有2位。指定长度数组

char

char的长度是不可变。char如果存入数据的实际长度比指定长度小，会补空格至指定长度，如果存入的数据的实际长度大于指定长度，低版本会被截取，高版本会报错。

varchar

varchar长度是可变的，默认会加一个隐藏的结束符，因此结束符会多算一个字节。

选择正确的数据类型对于获得高性能至关重要，三大原则：

1. 更小的通常更好，尽量使用可正确存储数据的最小数据类型

2. 简单就好，简单数据类型的操作通常需要更少的CPU周期

3. 尽量避免NULL，包含为NULL的列，对MySQL更难优化

2.2?整数型

tinyint(m) 1个字节 范围(-128~127)

smallint(m) 2个字节 范围(-32768~32767)

mediumint(m) 3个字节 范围(-8388608~8388607)

int(m) 4个字节 范围(-2147483648~2147483647)

bigint(m) 8个字节 范围(+-9.22*10的18次方)

上述数据类型，如果加修饰符unsigned后，则最大值翻倍

如：tinyint unsigned的取值范围为(0~255)

2.3?浮点型 (float 和 double) ，近似值

float(m,d) 单精度浮点型 8位精度(4字节) m总个数，d小数位, 注意: 小数点不占用总个数

double(m,d) 双精度浮点型16位精度(8字节) m总个数，d小数位, 注意: 小数点不占用总个数

设一个字段定义为float(6,3)，如果插入一个数123.45678,实际数据库里存的是123.457，但总个数还以实际为准，即6位

2.4?定点数

在数据库中存放的是精确值,存为十进制

格式 decimal(m,d) 表示最多 m 位数字,其中 d 个小数,小数点不算在长度内

比如: DECIMAL(6,2) 总共能存6位数字，末尾2位是小数，字段最大值 9999.99 （小数点不算在长度内）

参数m<65 是总个数，d<30且 d<m 是小数位

MySQL5.0和更高版本将数字打包保存到一个二进制字符串中（每4个字节存9个数字）。

例如: decimal(18,9)小数点两边将各存储9个数字，一共使用9个字节：其中，小数点前的9个数字用4个字节，小数点后的9个数字用4个字节，小数点本身占1个字节

浮点类型在存储同样范围的值时，通常比decimal使用更少的空间。float使用4个字节存储。double占用8个字节

2.5?字符串

char(n) 固定长度，最多255个字符,注意不是字节

varchar(n) 可变长度，最多65535个字符

tinytext 可变长度，最多255个字符

text 可变长度，最多65535个字符

mediumtext 可变长度，最多2的24次方-1个字符

longtext 可变长度，最多2的32次方-1个字符

BINARY(M) 固定长度，可存二进制或字符，长度为0-M字节

VARBINARY(M) 可变长度，可存二进制或字符，允许长度为0-M字节

内建类型：ENUM枚举, SET集合

3.创建、删除数据库和表

3.1?创建新的数据库（create database）

#建立数据库 
create database 数据库名;

3.2 删除指定的数据库（drop database）

drop database 数据库名;

3.3 创建新的表 （create table）

use 数据库名;
create table 表名 (字段1 数据类型,字段2 数据类型[,...][,primary key (主键名)]);
#主键一般选择能代表唯一性的字段不允许取空值（NULL），一个表只能有一个主键。
 
例如：
use kgc;
create table students (id smallint unsigned primary key auto_increment, name varchar(10), age tinyint unsigned,gender enum('M','F') default 'M' );
 
#unsigned：取消负数
primary  key：主键
auto_increment： 自增长
enum('M','F')：多选 
default 'M'：默认值为 M

3.4 删除指定的数据表（drop table）

方法一：库内删除
use 数据库名；
drop table 数据表名;
 
方法二：库外删除
#如不用USE进入库中，则需加上数据库名
drop table 数据库名.数据表名;			

4.管理表中的数据记录

4.1?向数据表中插入新的数据记录（insert into）

insert into 表名(字段1,字段2[,...]) values(字段1的值,字段2的值,...);
 
例如：
insert into students(id,name,age,passwd) values(1,'lisi',20,'112233' );
insert into students(id,name,age,passwd) values(2,'jiami',22,PASSWORD('123456'));
#PASSWORD('112233')：查询数据记录时，密码字串以加密形式显示；若不使用PASSWORD()，查询时以明文显示
select * from students;
 
提前新建数据表（注意长度设置）
use mysql;
create table students (id smallint unsigned primary key auto_increment, name varchar(10),age tinyint unsigned, passwd varchar(48));





insert into students(id,name,age,passwd) values(3,'liwu',21,'115643' );
insert into students(id,name,age,passwd) values(4,'wanger',25,'133643' );
insert into students(id,name,age,passwd) values(5,'zhangsan',27,'166643' );

此处添加数据为中文，注意字符集（默认为拉丁文）是否更改为utf8和utf8mb4

如果/etc/my.cnf没有更改，建立数据库时要指定字符集utf8mb4：
create database 数据库名 charset=utf8mb4;

4.2 查询数据表内数据记录（select）

select 字段名1,字段名2[,...] from 表名 [where 条件表达式];
 
基本操作：
1. #查询全部数据
select * from 数据表名;
例如：
select * from students;
 
2. #查询指定数据
例如：
select id,name from students where id=2;

更多操作：
3. #以列表方式竖向显示
select * from 数据表名\G;			
例如：
select * from students\G;
 
4. #只显示头2行
select * from 数据表名 limit 2;
例如：	
select * from students limit 2;
 
5. #显示第3行后的2行
select * from 数据表名 limit 3,2;
例如：
select * from students limit 3,2;

4.3?修改、更新数据表中的数据记录（update）

update 表名 set 字段名1=字段值1[,字段名2=字段值2] [where 条件表达式];
 
例如：
update students set age=19 where id=4;
update students set age=20,passwd='111111' where id=2;    #同时修改age和passwd
 

4.4?在数据表中删除指定的数据记录

delete from 表名 [where 条件表达式];
 
例如：
delete from students where id=2;
 
！！！！注意id并不会顶上去！！！！

5.修改表名和表结构（alter table）

5.1?修改表名（rename）

alter table 旧表名 rename 新表名;
 
例如：
alter table xuesheng rename students;

5.2 扩展表结构，增加字段（add）

alter table 表名 add 字段;
 
例如：
alter table students add address varchar(50) default 'wait get';
#default 'wait get'：表示此字段设置默认值 wait get；可与 NOT NULL 配合使用

5.3 修改字段（列）名，添加唯一键（change）

alter table 表名 change 旧列名 新列名 数据类型 [unique key];
 
例如：
alter table students change name user_name char(30) unique key;
#CHANGE可修改字段名、数据类型、约束等所有项。
验证：
insert into students values(6,'lili',23,234567,'NANKING');
insert into students values(7,'lili',23,234567,'NANJING');
失败，因为设置了唯一键，lili不能重复，改成lulu即可
insert into students values(7,'lulu',23,234567,'NANJING');

5.4 删除字段（drop）

alter table 表名 drop 字段名;
 
例如：
#删除表中passwd字段
alter table students drop passwd;

6.查看、修改字符集（show）

6.1 查看MySQL支持的所有字符集

show charset;

6.2 查看数据库字符集

show create database 数据库名\G
 
例如：
#查看mysql数据库中mysql库的字符集
show create database mysql\G

6.3 查看数据表的字符集

show table status from 库名 like '表名';
 
例如：
#查看kgc数据库中students表的字符集
show table status from kgc like 'students';

6.4 修改数据库字符集（character set）

#建立数据库并指定字符集utf8mb4
create database 数据库名 charset=utf8mb4;
 
#若数据库已建立，修改字符集
alter database 数据库名 character set 字符集;
例如：
alter database mysql character set utf8mb4;

alter database mysql character set latin1;