SQL 语句总结

2018-10-10 本文已影响0人末池桑

一、DDL（Data Definition Language）数据定义语言

* CREATE * ALTER * DROP * SHOW

1. 操纵数据库

a. 创建数据库:

Create database mydb1;

Create database mydb2 character set gbk;

Create database mydb3 character set gbk COLLATE gbk_chinese_ci;

b. 查询数据库：

Show databases; 查看当前数据库服务器中的所有数据库

Show create database mydb2; 查看前面创建的 mydb2 数据库的定义信息

c. 修改数据库：

alter database mydb2 character set utf8; 查看服务器中的数据库，并把 mydb2 的字符集修改为utf8;

Drop database mydb3; 删除数据库

2. 操作数据表

a. 创建表

create table表名(

                            字段1 字段类型,

                            字段2 字段类型,

                            ...

                            字段n 字段类型 // 这边没有句号和分号

                     );

* 常用数据类型：

int：整型

double：浮点型，例如 double(5,2) 表示最多5位，其中必须有2位小数，即最大值为999.99；

char：固定长度字符串类型； char(10) 'aaa ' 占10位

varchar：可变长度字符串类型；varchar(10) 'aaa' 占3位

text：字符串类型；

blob：字节类型；

date：日期类型，格式为：yyyy-MM-dd；

time：时间类型，格式为：hh:mm:ss

timestamp：时间戳类型 yyyy-MM-dd hh:mm:ss 会自动赋值

datetime:日期时间类型 yyyy-MM-dd hh:mm:ss

b. 查询表

SHOW TABLES;        当前数据库中的所有表

DESC employee;        查看表的字段信息

SHOW CREATE TABLE user;        查看表格的创建细节

c. 删除表

ALTER TABLE employee ADD image blob; 在上面员工表的基本上增加一个image列。

ALTER TABLE employee MODIFY job varchar(60); 修改 job 列，使其长度为60。

ALTER TABLE employee DROP image;        删除 image 列,一次只能删一列。

RENAME TABLE employee TO user;            表名改为user。

ALTER TABLE user CHARACTER SET gbk;        修改表的字符集为gbk

ALTER TABLE user CHANGE name username varchar(100);        列名 name 修改为username

DROP TABLE user ;                删除表、

二、DML（Data Manipulation Language）数据操作语言

DML是对表中的数据进行增、删、改的操作。不要与DDL混淆了。

1. 插入操作：insert

INSERT INTO 表名（列名1，列名2 ...）VALUES(列值1，列值2...);

小知识：在 MySQL 中，字符串类型 和 日期类型 都要用单引号括起来。

空值：Null

查看数据库编码的具体信息： Show variables like ‘character%’;

临时更改 客户端 和 服务器 结果集的编码：

Set character_set_client=gbk;

Set character_set_results=gbk;

2. 修改操作: update

UPDATE 表名 SET 列名1=列值1，列名2=列值2 。。。WHERE 列名 = 值

将所有员工薪水修改为 5000 元。

            UPDATE emp SET salary=5000

将姓名为’zhangsan’的员工薪水修改为 3000 元。

            UPDATE emp SET salary=3000 WHERE name=’ zhangsan’;

将姓名为’lisi’的员工薪水修改为 4000 元, job 改为 ccc。

             UPDATE emp SET salary=4000, job='ccc'WHERE name='lisi';

将 gender 为 male 的薪水在原有基础上增加 1000 元。

      UPDATE emp SET salary=salary+1000 WHERE gender='male';

3. 删除操作 DELETE:

删除表中名称为’zs’的记录。

            DELETE FROM emp WHERE name=‘zs’;

删除表中所有记录。

            DELETE FROM emp;

使用truncate删除表中记录。

            TRUNCATE TABLE emp; // truncate

DELETE 删除表中的数据，表结构还在; 删除后的数据可以找回

TRUNCATE (truncate) 删除是把表直接DROP掉，然后再创建一个同样的新表。

删除的数据不能找回。执行速度比DELETE快。

三、DQL（Data Query Language）数据查询语言

数据库执行 DQL 语句不会对数据进行改变，而是让 数据库 发送结果集给 客户端。

查询返回的结果集是一张虚拟表。

CREATE TABLE stu(

        sid INT PRIMARY KEY AUTO_INCREMENT,

        sname VARCHAR(50) NOT NULL,

        age int,

        gender varchar(50)

)ENGINE=INNODB, DEFAULT CHARSET=UTF8;

1. 条件查询

条件查询就是在查询时给出WHERE子句，在WHERE子句中可以使用如下运算符及关键字：

AND： SELECT * FROM stu WHEREgender='female' AND age<50;

OR： select *from stu where sid=8 or sname='chenEr';

IN： SELECT * FROM stu WHERE sid IN ('1','2','3');

NO IN ： SELECT * FROM stu WHERE sid NOT IN ('1','2','3');

IS NULL ： SELECT * FROM stu WHERE age IS NULL;

BETWEEN…AND： SELECT * FROM stu WHERE age BETWEEN 20 AND 40;

相当于： SELECT * FROM stu WHERE age>=20 AND age<=40;

= != <> < <= > >=

NOT ： SELECT * FROM stuWHERE NOT sname IS NULL;

SELECT * FROM stu WHERE sname IS NOT NULL;

2. 模糊查询

当想查询姓名中包含a字母的学生时就需要使用模糊查询了。模糊查询需要使用关键字 LIKE。

_ ：任意一个字母

SELECT * FROM stu WHERE sname LIKE '___Y_';

%：任意 0~n 个字母

SELECT * FROM stu WHERE sname LIKE '%a%';

3. 字段控制查询

去除重复记录： SELECT DISTINCT gender FROM stu;

两列值相加

给列添加别名

4. 排序

升序： SELECT *FROM stu ORDER BY age ASC;

降序： SELECT *FROM stu ORDER BY age DESC;

5.聚合函数 sum avg max min count

COUNT()：统计指定列不为 NULL 的记录行数；

MAX()：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；

MIN()：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；

SUM()：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；

AVG()：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；