SQL相关语句，以操作MySql为例 --- macOs

操作MySql相关命令以及一些基本的增删改SQL语句。

1、从Terminal终端连接MySql数据库系统，并输入数据库登陆密码：

/usr/local/mysql/bin/mysql -u root -p

2、查看已经存在的数据库：

show databases;

3、创建新的数据库，创建数据库的格式：CREATE DATABASE 数据库名;

create database test;

4、删除数据库，格式：DROP DATABASE 数据库名;

drop database test;

5、查询数据存储引擎类型，MySQL中查询存储引擎的类型命令：SHOW ENGINES;

show engines;

存储引擎就是指表的类型，数据库存储引擎决定了表在计算机的存储方式。

查询结果中，Engine参数指存储引擎名称；Support参数说明MySQL是否支持该类型引擎；Comment参数表示对该引擎的评论；Transaction参数表示是否支持事务处理；XA参数表示是否分布式交易处理的XA规范；Savepoints参数表示是否支持保存点，以方便事务的回滚操作；由上面我们看到InnoDB存储引擎是default的，也就是数据库默认的存储引擎，下面我们简单介绍一下InnoDB。InnoDB是MySQL的一种存储引擎，InnoDB给MySQL提供了事务、回滚、崩溃修复能力和多版本并发控制的事务安全。InnoDB是MySQL上第一个提供外键约束的表引擎，而且对事务处理的能力，也是其他存储引擎不能比拟的。不过这种引擎的缺点就是读写效率稍差，占用的数据空间相对比较大。

6、选择数据库，在创建表之前先选择数据库，格式：USE 数据库名;

use test;

7、查看当前使用的数据库，格式：SELECT DATABASES();

select database();

8、查看数据库使用端口，格式：SHOW  VARIABLES  LIKE 'PORT';

show variables  like 'port';

9、查看当前数据库大小，例如查看test数据库的大小，可以通过下面SQL查看；

use information_schema

select concat(round(sum(data_length)/(1024*1024),2) + round(sum(index_length)/(1024*1024),2),'MB') as 'DB Size' 
from tables where table_schema = 'test';

10、查看数据所占的空间大小，可以通过下面SQL查看；

select concat(round(sum(data_length)/(1024*1024),2),'MB') as 'DB Size'
from tables where table_schema='test';

11、查看索引所占的空间大小，可以通过下面SQL查看；

select concat(round(sum(index_length)/(1024*1024),2),'MB') as 'DB Size' 
from tables where table_schema='test';

12、查看数据库编码，可以通过下面SQL查看；

show variables like 'character%';

character_set_client      为客户端编码方式；character_set_connection  为建立连接使用的编码；character_set_database    为数据库的编码；character_set_results     为结果集的编码；character_set_server      为数据库服务器的编码；

13、查看数据库的所有用户信息，可以通过下面SQL查看；

select distinct concat('user: ''',user,'''@''',host,''';') as query from mysql.user;

14、查看某个具体用户的权限，可以通过下面SQL查看；

flush privileges; //权限刷新
show grants for 'root'@'localhost';

15、查看数据库的最大连接数，可以通过下面SQL查看；

show variables like '%max_connections%';

16、查看数据库当前连接数、并发数，可以通过下面SQL查看；

show status like 'Threads%';

17、查看数据文件存放路径，可以通过下面SQL查看；

show variables like '%datadir%';

18、下面是关于表的创建、修改和删除。

1)、表的创建，  创建表的形式：      

      CREATE TABLE 表名 (
           属性名 数据类型 [完整约束条件],
           属性名 数据类型 [完整约束条件],
           ...
           ...
           属性名 数据类型 [完整约束条件]
          );

use test;

create table user(
     id int,
     name varchar(20)
     );

2)、查看数据库的表信息，可以通过下面SQL查看；

show tables;

3)、创建表的时候涉及到一个完整性约束条件，下面就列出一个完整性约束条件表：

约束条件	说明
PRIMARY KEY	标识该属性为该表的主键，可以唯一的标识对应的元组
FOREIGN KEY	标识该属性为该表的外键，是与之联系某表的主键
NOT NULL	标识该属性不能为空
UNIQUE	标识该属性的值是唯一的
AUTO_INCREMENT	标识该属性的值是自动增加，这是MySQL的SQL语句的特色
DEFAULT	为该属性设置默认值

4)、完整性约束条件的应用—设置表的主键，单字段主键格式：属性名 数据类型 PRIMARY KEY；

create table user(
    id int primary key,
    name varchar(20)
    );
describe user;//查看表基本结构语句

5)、完整性约束条件的应用—设置表的主键，多字段主键格式：PRIMARY KEY(属性名1,属性名2....属性名n)；

create table user( id int, user_id int, name varchar(20), primary key(id,user_id) );
describe user;//查看表基本结构语句

6)、完整性约束条件的应用—设置表的外键，格式：CONSTRAINT 外键别名 FOREIGN KEY(属性1,属性2,....属性n) REFERENCES 表名(属性1',属性2',...属性n')；

create table user( id int primary key, user_id int, name varchar(20), constraint userid foreign key(user_id) references student(id) );

7)、完整性约束条件的应用

—设置表的非空约束，简单的说就是不让这个属性的值为空，不填的话就会报错

格式：属性名 数据类型 NOT NULL

 

—设置表的唯一性约束，就是这个属性的值是不能重复的

格式：属性名 数据类型 UNIQUE

 

—设置表的属性值自动增加，AUTO_INCREMENT约束的字段可以是任何整数类型（TINYINT、SMALLINT、INT和BIGINT），在默认的情况下，该字段的值是从1开始自增

格式：属性名 数据类型 AUTO_INCREMENT

—设置表的属性的默认值

格式：属性名 数据类型 DEFAULT 默认值

create table user( id int primary key auto_increment, user_id int unique, name varchar(20) not null, sex varchar(10) default 'male' );

8)、查看表基本结构语句DESCRIBE、查看表详细结构语句SHOW CREATE TABLE

desc/describe user; //查看表基本结构
show create table user; //查看表详细结构

9)、修改表名，格式：ALTER TABLE 旧表名 RENAME 新表名;

alter table user rename people;

10)、修改字段的数据类型，格式：ALTER TABLE 表名 MODIFY 属性名 数据类型;

alter table people modify name varchar(30);

11)、修改字段名，格式：ALTER TABLE 表名 CHANGE 旧属性名 新属性名 新数据类型;

alter table people change name peopel_name int;

修改的字段名的同时也修改了数据类型了

12)、增加字段，格式：ALTER TABLE 表名 ADD 属性名1 数据类型 [完整性约束条件] [FIRST | AFTER 属性名2];

alter table people add name varchar(20) not null after sex;

其中，“属性名1”参数指需要增加的字段的名称；“FIRST”参数是可选参数，其作用是将新增字段设置为表的第一个字段；“AFTER”参数也是可选的参数，其作用是将新增字段添加到“属性名2”后面；“属性名2”当然就是指表中已经有的字段

13)、删除字段，格式：ALTER TABLE 表名 DROP 属性名;

alter table people drop peopel_name;

14)、更改表的存储引擎，格式：ALTER TABLE 表名 ENGINE = 存储引擎名;

show create table people;//先查看一下当前表的存储引擎

alter table people engine = MYISAM;

15)、增加表的外键约束(创建表之后)，格式：Alter table 表名 add [constraint 外键名字] foreign key [外键字段] references 父表(主键字段);

alter table people change name name_id int;//先把字段name改成name_id,包括字段的类型改成int

alter table people add constraint NAME_ID foreign key(name_id) references student(id);
//标识该属性name_id为该表的外键，是与之联系student表的主键

16)、删除表的外键约束，格式：ALTER TABLE 表名 DROP FOREIGN KEY 外键别名;

alter table people drop foreign key NAMEID;

17)、删除表，格式：DROP TABLE 表名;

drop table people;

删除没有被关联的普通表：直接上面的SQL语句就行了

删除被其他表关联的父表：

方法一：先删除子表，在删除父表

方法二：删除父表的外键约束（上面有介绍），再删该表

     本博客所记录的文章，部分是从网络收集的，有一些因为经过多次转载，所以出处已经不知，若是侵权，请通知我，我及时修改。本博客主要是用来记录我对所写文章的理解，若有错误，请大家指点，相互学习！