mySql常用操作

一、SQL语句分类

1、DDL语句（数据库定义语言）： 数据库、表、视图、索引、存储过程，例如：CREATE、DROP、ALTER 

2、DML语句（数据库操纵语言）： 插入数据INSERT、删除数据DELETE、更新数据UPDATE、查询数据SELECT 

3、DCL语句（数据库控制语言）： 控制用户的访问权限GRANT、REVOKE 

二、操作库

mysql自带的库：

information_schema： 虚拟库，不占用磁盘空间，存储的是数据库启动后的一些参数，如用户表信息、列信息、权限信息、字符信息等

performance_schema： MySQL 5.5开始新增一个数据库：主要用于收集数据库服务器性能参数，记录处理查询请求时发生的各种事件、锁等现象

mysql： 授权库，主要存储系统用户的权限信息

test： MySQL数据库系统自动创建的测试数据库

连接数据库，查看已有的数据库，show databases;

 

创建库

指定编码：create database zss charset utf8; 

使用数据库：use zss;

 

查询库

show create database zss;

show databases;  展示所有的数据库

查看库位置：find / -name zss

查看当前所在的库：select database(); 

 

改库

修改编码: alter database zss charset gbk;

 

删库

drop database zss;

 

三、操作表及列

切换数据库:use zss;

查看当前所在数据库（文件夹）：select database();

增加表

create table 表名(

    字段名1 类型[(宽度) 约束条件],

    字段名2 类型[(宽度) 约束条件],

    字段名3 类型[(宽度) 约束条件]

);

create table test(id int, name varchar(255));

增加后，多了一个test.frm文件，frm是表结构

 

查询表

show create table test \G;                  \G表示按行显示表的详细结构

show tables;

desc test; # 等价于describe test;

 

 插入数据：insert test(id,name) values(1,'tom'),(2,'jack'),(3,'tony');

 

复制表

复制表结构＋记录：create table test2 select * from test;

只复制表结构：create table test3 select * from test where 1=2;

             或者：create table test4 like test;

 

改表

ALTER TABLE 表名

    # 修改表名

      RENAME 新表名; 

    # 增加字段

      ADD 字段名  数据类型 [完整性约束条件…];

      ADD 字段名  数据类型 [完整性约束条件…]  FIRST; # 添加到第一个字段

      ADD 字段名  数据类型 [完整性约束条件…]  AFTER 字段名;  # 添加到某个字段之后

    # 删除字典

      DROP 字段名;

    # 修改字段

      MODIFY  字段名 数据类型 [完整性约束条件…];

      CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];

      CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];

说明：

MODIFY，可以改字段属性

CHANGE，可以改字段名、字段属性　

例如：

alter table test modify name varchar(256);

desc test;

alter table test change name NAME varchar(257);

desc test;

删除表

drop table test;

四、操作记录数据

 增数据

全字段插入：INSERT INTO 表名(字段1,字段2,字段3…字段n) VALUES(值1,值2,值3…值n);  


多条数据逗号分隔INSERT INTO 表名 VALUES(值1,值2,值3…值n),(值1,值2,值3…值n),(值1,值2,值3…值n);

指定字段插入：INSERT INTO 表名(字段1,字段2,字段3…) VALUES (值1,值2,值3…);

例如：

create table test(id int, name varchar(255));

insert test(id,name) values(1,'tom'),(2,'jack'),(3,'tony');

 

删数据

delete from test where id=3;

delete，用于删除数据，自增长字段的值未重置

truncate，用于清空表，自增长字段的值也被重置

 

改数据

UPDATE 表名 SET 字段1=值1 WHERE 约束条件;

update test set name='meimei' where id=2;

 

查数据

select * from test;

如果在其它库查test，表前必须加库作为前缀。select * from zss.test; 

 

五、数据类型

 数字类型

参考：https://www.runoob.com/mysql/mysql-data-types.html

 

 

日期类型

 

 例如：create table student(

    id int,

    name char(6),   # 最大存储6个字符

    born_year year,  # 年

    birth_date date,  # 年月日

    class_time time,  # 时分秒

    reg_time datetime  # 年月日时分秒

);

插入数据

insert into student values(1,'jack',now(),now(),now(),now());

insert into student values(2,'tom',"2022","2022-5-1","00:00:00","2022-5-1 00:00:00");

now()是mysql提供的函数，当前时间

 

字符类型

 

 # 宽度指的是字符的个数

create table test(name char(5));

create table test(name varchar(5));

char：按指定长度存，存取速度快，但是当存的数据的长度小于字段定义的长度时浪费空间

varchar：存数据更精简，更加节省空间（是在存的数据的长度小于字段定义的长度时），缺点，存取速度慢，要先存头，再存数据；先取头，再取数据；

现如今，存储空间已经不是限制了，要追求存取速度，大部分用char，与查询无关的用varchar

建表的时候，定长的数据往前放，变长的往后放，而且，一张表中，不要char和varchar混用

 

枚举及集合类型

enum 单选，只能在给定的范围内选一个值，如性别

sex enum('male','female','other'),

set 多选，在给定的范围内可以选择一个或一个以上的值（爱好1,爱好2,爱好3...）

hobbies set('play','music','read','run')

drop table test;

create table test(

  id int,

  name char(16),

  sex enum('male','female','other'),

  hobbies set('play','music','read','run')

);

插入数据：

insert into test values(1,'jack','male','music,read');  # 集合多个值用逗号分隔 

 

六、存储引擎

 分类：

InnoDB 存储引擎

MyISAM 存储引擎

NDB 存储引擎

Memory 存储引擎

Infobright 存储引擎

NTSE 存储引擎

BLACKHOLE

 

七、约束

 作用：保证数据的完整性和一致性

分类：PRIMARY KEY (PK)，标识该字段为该表的主键，可以唯一的标识记录

FOREIGN KEY (FK)，标识该字段为该表的外键

NOT NULL，标识该字段不能为空

UNIQUE KEY (UK)，标识该字段的值是唯一的

AUTO_INCREMENT， 标识该字段的值自动增长（整数类型，而且为主键）

DEFAULT， 为该字段设置默认值

 primary key

#方法一：在某一个字段后用primary key

create table test(

  id int primary key,

  name char(255)

);

#方法二：not null+unique

创建表时未指定主键，会找不为空且唯一的字段作为主键

create table test(

  id int not null unique,

  name char(255)

);　

#方法三：在所有字段后单独定义primary key

create table test(

  id int,

  name varchar(255),

  constraint pk_name primary key(id)

);

 

foreign key

建立表之间的关系

 create table student(

    id int primary key,

    name char(255),

    age int

);

 

# 关联的表

create table class(

    id int primary key,

    name char(255),

    stu_id int,

    foreign key(stu_id) references student(id)

    on delete cascade   # 删除同步

    on update cascade  # 修改同步

);

 

not null 与default

create table test(

    id int,

    name char(255),

    sex enum('male','female') not null default 'male'

);

 

 unique key

方式一、

create table test(

    id int unique,

    name char(255) unique

);

方式二、

create table test(

    id int,

    name char(255),

    unique(id),

    unique(name)

);　

联合唯一

create table test(

    id int,

    name char(255),

    unique(id,name)

);

 

复合主键

create table test(

  id int,

  name char(255),

  primary key(id, name)

);

auto_increment

约束字段为自动增长，增长字段必须设置为primary key，unique key

create table test(

    id int primary key auto_increment,

    name char(255)

);

八、表和表之间的关系

一对一

一对多

多对多

select distinct 字段1,字段2,字段3  # 要查询的字段或者分组字段聚合函数

from 库.表 # 从哪个表查，如果当前所在的库不是这个表所在的库，表的前面需要加上库名

where  # 约束条件

group by  # 分组

having  # 过滤

order by  # 排序

limit  # 限制条数

 

 如果是多表，加个join及连接条件就可以了，很简单。

说明：where

where是分组之前过滤，后面是普通条件

1.比较运算符：><>= <= <> !=

2.逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not

3.between 10 and 100 值在10到100之间

4.in(80,90) 值是80或90

5.like 'qzcsbj%'，除了%还可以_，%表示任意多字符，_表示一个字符

 having

having是分组之后过滤，后面是聚合条件

聚合函数（以组为单位进行统计）
max，最大
min，最小
avg，平均
sum，和
count，数量

order by 

默认升序，asc

降序，desc

也可以先按某个字段升序，再按某个字段降序，例如：select * from test order by id asc, name desc;

limit

limit n，默认初始位置为0，从1开始取，取n条，如果不足n条记录，那么有多少条就取多少条

limit m,n，表示位置m，从m+1开始取，取n条记录，如果不足n条记录，那么有多少条就取多少条

 

执行顺序

5 select <br>6 distinct

1 from 库.表

2 where 

3 group by 

4 having 

7 order by

8 limit

九、多表查询

 内连接：只取两张表的共同部分，join on

左外连接：显示左表全部记录，在内连接的基础上增加左边有右边没有的结果，left join on

右外连接：显示右表全部记录，在内连接的基础上增加右边有左边没有的结果，right join on

全外连接：

显示左右两个表全部记录，在内连接的基础上增加左边有右边没有的和右边有左边没有的结果，

union，其与union all的区别是，union会去掉相同的纪录，另外，mysql不支持full join on

 

十、内置函数

 数值函数

字符串函数

日期时间函数

流程控制函数

系统信息函数

十一、账号权限管理

创建新用户：CREATE USER 'test'@'localhost' IDENTIFIED BY '123456';

新用户授权：GRANT ALL PRIVILEGES ON *.* TO 'test'@'%'IDENTIFIED BY '123456' WITH GRANT OPTION;

刷新授权：flush privileges;