常用sql语句整理

在mysql中，常用sql语句为：

修改密码：
alter user 'root'@'localhost' identified with mysql_native_password BY '新密码';
alter user 'root'@'localhost' identified with mysql_native_password BY '123456';

DDL（Data Definition Language）：数据定义语言，用来定义数据库对象：库、表、列等。

DML（Data Manipulation Language）：数据操作语言，用来定义数据库记录（数据）增删改。

DCL（Data Control Language）：数据控制语言，用来定义访问权限和安全级别。

DQL（Data Query Language）：数据查询语言，用来查询记录（数据）查询。

补充：sql语句不区分大小写

DDL

DDL操作数据库

  1. 创建 CREATE DATABASE用于创建数库
#create database 数据库名
CREATE DATABASE mydb1;

#create database 数据库名 character set 编码方式
CREATE DATABASE mydb2 character SET GBK;

#create database 数据库名 set 编码方式 collate 排序规则
CREATE DATABASE mydb3 character SET GBK COLLATE gbk_chinese_ci;
  1. 查看数据库
#查看数据库服务器中所有数据库
show databases;

#show CREATE database 数据库名字;
#查看前面创建的mydb2数据库的定义信息 
show CREATE database mydb2;
  1. 修改数据库
#alter database 数据库名 character set 编码方式
#查看服务器中的数据库，并把mydb2的字符集修改为utf8;
ALTER DATABASE mydb2 character SET utf8;

4.删除数据库

#drop database 数据库名字；
#删除mydb3
drop database mydb3;
drop database mydb2;

5.其他

#查看当前使用的数据库
select database();

#切换数据库
#use 数据库名；
use mydb2;

DDL操作表

  1. 创建
#create table 表名(列名1 数据类型 [约束]……列名n 数据类型 [约束]);
#说明：表名，列名是自定义，多列之间使用逗号间隔，最后一列的逗号不能写[约束] 表示可有可无

#例：
create table student(
sno int primary key ,#学号，主键
sname varchar(5)#学生姓名
);
  1. 查看表信息
#查看当前数据库所有表
show tabels;

#查看表格的创建细节  show create table 表名;
SHOW CREATE TABLE student;

#查看表的字段信息
#desc 表名
desc student;
  1. 修改表
#修改列信息
#修改表名    alter table 旧表名 rename 新表名;
alter table student rename stu;

#修改表的字符集   alter table 表名 character set 编码方式
ALTER TABLE stu CHARACTER SET gbk;

#增加1列   alter table 表名 add 新列名 数据类型 [约束];
alter table stu add phone varchar(12);

#修改列    alter table 表名 change 旧列名 新列名 新的数据类型
alter table stu change sno sid int primary key;

#删除列    alter table 表名 drop 列名
aleter table stu drop phone;
  1. 删除表
#drop table 表名;
drop table stu;

DML

DML是对表中的数据进行增、删、改的操作。不要与DDL混淆了。主要包括：INSERT 、UPDATE、 DELETE

注意：在mysql中，字符串类型和日期类型都要用单引号括起来; 空值：null

  1. 插入操作 insert
#insert into 表名(列名) values(数据值);
insert into stu(sid,sname) values(1,'张三');

/*
注意:
1.多列和多个列值之间使用逗号隔开 
2.列名要和列值一一对应
3.非数值的列值两侧需要加单引号,插入的日期和字符一样，都使用引号括起来。
4.如果插入空值，请使用null
*/

#添加数据的时候可以将列名省略->当给所有列添加数据的时候,此时列值的顺序按照数据表中列的顺序执行
insert into stu values(2,'李四');

#同时添加多行
insert into stu values(3,'王五'),(4，'赵六');
  1. 修改（更新）操作 update
#update 表名 set 列名1=列值1，列名2=列值2……where 条件;
#将赵六姓名改为陈六
update stu set sname='陈六' where sname='赵六';
  1. 删除操作 delete
#单表删除：delete from 表名 where 条件
#将学号为3的学生信息删除掉
delete from stu where sid=3;
#将表所有学生信息删除
truncate table stu;
/*
注意：
DELETE 删除表中的数据，表结构还在;删除后的数据可以找回
TRUNCATE table 表名; 是把表直接DROP掉，然后再创建一个同样的新表,删除的数据不能找回。执行速度比DELETE快。
*/

DCL

  1. 创建用户
#create user 用户名@指定ip identified by 密码;
create user test@localhost IDENTIFIED by 'test123';

#create user 用户名@客户端ip identified by 密码; 指定IP才能登陆
create user test@10.4.10.18 IDENTIFIED by 'test123';

#create user 用户名@'%' identified by 密码 任意IP均可登陆
create user test@'%' IDENTIFIED by 'test123';
  1. 用户授权
#grant 权限1,权限2,........,权限n on 数据库名.* to 用户名@IP; 给指定用户授予指定指定数据库指定权限
#给test用户在数库mydb1的表stu授权查询、插入、更新、删除、创建权限
grant select,insert,update,delete,create on mydb1.stu to 'test'@'127.0.0.1';

#grant all on . to 用户名@IP 给指定用户授予所有数据库所有权限
grant all on *.* to 'test'@'127.0.0.1'

#用户权限查询
#show grants for 用户名@IP;
show grants for 'root'@'%';

#撤销用户权限
#revoke 权限1,权限2,........,权限n on 数据库名.* from 用户名@IP;
REVOKE SELECT ON *.* FROM 'test'@'%' ;

#删除用户
#drop user 用户名@IP;
drop user test@localhost;

DQL

数据库执行DQL语句不会对数据进行改变，而是让数据库发送结果集给客户端。查询返回的结果集是一张虚拟表。

查询关键字：SELECT

SELECT 列名 FROM 表名 [WHERE --> BROUP BY-->HAVING--> ORDER BY];

/*
注：having与where的区别:
1.having是在分组后对数据进行过滤,where是在分组前对数据进行过滤
2.having后面可以使用分组函数(统计函数)
3.where后面不可以使用分组函数。
4.where是对分组前记录的条件，如果某行记录没有满足where子句的条件，那么这行记录不会参加分组；而having是对分组后数据的约束。
*/

通配符

/*
_(下划线)：任意一个字符
%:任意0~n个字符
*/

聚合函数

/*
聚合函数是用来做纵向运算的函数：
COUNT(列名)：统计指定列不为NULL的记录行数；
MAX(列名)：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
MIN(列名)：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
SUM(列名)：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；
AVG(列名)：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；
*/

limit

/*
limit用来限定查询结果的起始行，以及总行数。
limit 开始下标,显示条数；//开始下标从0开始
limit 显示条数；//表示默认从0开始获取数据
*/

常见数据类型：

数值类型

MySQL支持所有标准SQL数值数据类型。

这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。

关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。

BIT数据类型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表。

作为SQL标准的扩展，MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。

下面的表显示了需要的每个整数类型的存储和范围：

类型

大小

范围（有符号）

范围（无符号）

用途

对应java中基本数据类型

TINYINT

1 byte

(-128，127)

(0，255)

小整数值

byte

SMALLINT

2 bytes

(-32 768，32 767)

(0，65 535)

大整数值

short

MEDIUMINT

3 bytes

(-8 388 608，8 388 607)

(0，16 777 215)

大整数值

/

INT或INTEGER

4 bytes

(-2 147 483 648，2 147 483 647)

(0，4 294 967 295)

大整数值

int

BIGINT

8 bytes

(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)

(0，18 446 744 073 709 551 615)

极大整数值

long

FLOAT

4 bytes

(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)

0，(1.175 494 351 E-38，3.402 823 466 E+38)

单精度 浮点数值

float

DOUBLE

8 bytes

(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)

0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)

双精度 浮点数值

double

DECIMAL

对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2

依赖于M和D的值

依赖于M和D的值

小数值

日期和时间类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。

TIMESTAMP类型有专有的自动更新特性，将在后面描述。

类型

大小 ( bytes)

范围

格式

用途

DATE

3

1000-01-01/9999-12-31

YYYY-MM-DD

日期值

TIME

3

‘-838:59:59’/‘838:59:59’

HH:MM:SS

时间值或持续时间

YEAR

1

1901/2155

YYYY

年份值

DATETIME

8

1000-01-01 00:00:00/9999-12-31 23:59:59

YYYY-MM-DD HH:MM:SS

混合日期和时间值

TIMESTAMP

4

1970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07

YYYYMMDD HHMMSS

混合日期和时间值，时间戳

字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。

类型

大小

用途

CHAR

0-255 bytes

定长字符串

VARCHAR

0-65535 bytes

变长字符串

TINYBLOB

0-255 bytes

不超过 255 个字符的二进制字符串

TINYTEXT

0-255 bytes

短文本字符串

BLOB

0-65 535 bytes

二进制形式的长文本数据

TEXT

0-65 535 bytes

长文本数据

MEDIUMBLOB

0-16 777 215 bytes

二进制形式的中等长度文本数据

MEDIUMTEXT

0-16 777 215 bytes

中等长度文本数据

LONGBLOB

0-4 294 967 295 bytes

二进制形式的极大文本数据

LONGTEXT

0-4 294 967 295 bytes

极大文本数据