MySQL基础语句

MySQL基础语句

定义

SQL是结构化查询语言，用于操作关系型数据库

语法要求

SQL语句可以单行或多行书写，以分号结尾
可以用空格和缩进来增强语句的可读性
关键字不区分大小写，但是建议大写

数据库的基本介绍

DDL：数据定义语言，用来定义数据库对象：库、表、列等

DML：数据操作语言，用来定义数据库记录

DCL：数据控制语言，用来定义访问权限和安全级别

DQL：数据查询语言，用来查询记录

DDL

查看所有数据库：SHOW DATABASES;
　　切换数据库：USE 数据库名;
　　创建数据库：CREATE DATABASE [IF NOT EXISTS] 数据库名;　　
备注：IF NOT EXISTS 为可选内容，主要是用于避免因数据库已存在而报错
　　删除数据库：DROP DATABASE [IF EXISTS] 数据库名; 　　
备注：IF EXISTS 为可选内容，主要是用于避免因数据库不存在而报错
　　修改数据库编码：ALTER DATABASE 数据库名 CHARACTER SET utf8;
　　创建表：CREATE TABLE 表名(列名 列类型,列名 列类型,…);
　　查看当前数据库中所有表：SHOW TABLES;
　　查看指定表的创建语句：SHOW CREATE TABLE 表名;
　　查看表结构：DESC 表名;
　　删除表：DROP TABLE 表名;
　　修改表
　　　　添加列：ALTER TABLE 表名 ADD (列名 列类型);
　　　　修改列类型：ALTER TABLE 表名 MODIFY 列名 列类型;
　　　　修改列名：ALTER TABLE 表名 change 原列名 新列名 列类型;
　　　　删除列：ALTER TABLE 表名 DROP 列名;
　　　　修改表名称：ALTER TABLE 原表名 RENAME TO 新表名;

DML

插入数据：INSERT INTO 表名(列名1,列名2, …) VALUES(值1, 值2);　　
备注：没有制定列名时，表示按照表结构插入所有的值，所有字符串必须用单引号
修改数据：UPDATE 表名 SET 列名1=值1, … 列名n=值n [WHERE 条件];
删除数据:DELETE FROM 表名 [WHERE 条件];
或者TRUNCATE TABLE 表名;
注意：虽然都是删除数据，但是DELETE效率没有TRUNCATE效率高，但是DELETE删除的记录是可以回滚,
TRUNCATE其实是DDL语句，因为它先是DROP表，然后CREATE表

DCL

创建用户：CREATE USER 用户名@地址 IDENTIFIED BY ‘密码’;
　给用户授权：GRANT 权限1, … , 权限n ON 数据库.* TO 用户名;
　撤销授权：REVOKE权限1, … , 权限n ON 数据库.* FORM 用户名;
　查看用户权限：SHOW GRANTS FOR 用户名;
　删除用户：DROP USER 用户名;

DQL

　　

基础查询

　　

查询所有列：SELECT * FROM 表名;
　查询指定列：SELECT 列名1,列名2…列名n FROM 表名;

条件查询

在WHERE子句中可以使用的运算符及关键字
　=、!=、<>、<、<=、>、>=
　BETWEEN…AND…
　IN(集合)
　IS NULL
　AND
　OR
　NOT
　格式：SELECT * FROM 表名 WHERE 条件

模糊查询

SELECT * FROM 表名 WHERE 列名 LIKE 匹配条件
#_匹配任意字符，%匹配0~n个字符

字段控制查询

去除重复记录：

SELECT DISTINCT 列名 FROM 表名;去掉查询结果中完全相同的记录

列运算

SELECT 列1+列2 FROM 表名;列类型必须为数值类型 SELECT 列1+IFNULL(列2,需要转换成的数值) FROM 表名; 列记录为NULL时需要将NULL转换成数值，否则任何数值与NULL运算结果都为NULL

给列添加别名

SELECT 列 AS 别名 FROM 表名;主要是在进行列运算时，结果中列名为列1+列2的形式，不美观；AS可以省略

排序

升序ASC：

SELECT * FROM 表名 ORDER BY 列名 ASC;　　ASC可以省略，但是不建议 降序DESC：SELECT * FROM 表名 ORDER BY 列名 DESC;　　DESC不可以省略

混合使用：

SELECT * FROM 表名 ORDER BY 列名1 DESC,列名2 ASC;　 　排序时先按照列1降序，若列1的值相同，则按照列2升序排列； DESC和ASC根据具体情况使用

聚合函数

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

分组查询

GROUP BY：

SELECT 列名, COUNT(*) FROM 表名 [WHERE 条件] GROUP BY 列名;

前后两个列名相同，表示对该列根据相同记录进行分组，
COUNT(*)表示分组后统计，也可以是其他操作。
有WHERE时，被过滤掉的记录不会参加分组

HAVING子句：

SELECT 列名, COUNT(*) FROM 表名 GROUP BY 列名 HAVING 条件;　
不同于WHERE，HAVING是对分组后的结果进行刷选

LIMIT：

SELECT * FROM 表名 LIMIT 起始行, 需要查询的行数;
限定查询结果的起始行和总行数，一般用于分页查询

数据库基本类型

#float(m,n)定义最大位数为m,精确到小数点后n位的浮点小数
#double(m,n)定义最大位数为m,精确到小数点后n位的双精度浮点小数
#n会占用m的位数，float（5,2）表示2位小数，最多5位，整数3位
#char(n)定义固定长度的字符串类型，n表示当前列能够容纳的最大位数，n的最大值是255字节
#varchar(n)定义可变长度的字符串类型，n表示当前列能够容纳的最大位数，n的最大值是65530字节
#char(n)如果放入的数据不到n个字节，会自动使用空格补齐到n个字节
#varchar(n)按照放入数据的时间长度存储
#char类型浪费存储空间
#varchar类型节省存储空间，查询效率相对低
#date 日期
#time 时间
#datetime 日期时间

基本增、删、改、查

-- 显示已经创建的数据库
SHOW DATABASES;
-- 选择数据库
USE emp;
-- 显示选中数据库的表
SHOW TABLES;
-- 创建表
CREATE TABLE `grade` (
  `grade_id` int(11) NOT NULL COMMENT '班级ID',
  `grade_name` varchar(10) NOT NULL COMMENT '班级名',
  `grade_director` varchar(20) NOT NULL COMMENT '班主任',
  PRIMARY KEY (`grade_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
-- 如果因为又外连接无法删除可以禁用外连接0：禁用，1：启用
SET FOREIGN_KEY_CHECKS=1;
-- 查看当前外键的状态
SELECT @@FOREIGN_KEY_CHECKS;
-- 删除表
DROP TABLE emp.grade;
-- 查询表中的数据
SELECT  * FROM grade;
-- 添加数据（添加单个数据）
INSERT INTO grade VALUES(1,"student1",20);
-- 添加数据（多个）
INSERT INTO grade VALUE(2,"student2",50),(4,"student4",80),(3,"student3",100),(5,"student5",150);
-- 修改数据
UPDATE grade SET grade.grade_director=50 WHERE grade.grade_id=1;
-- 查询数据
SELECT * FROM grade WHERE grade.grade_director=100;
-- 删除数据(只删除符合条件的数据)
DELETE FROM grade WHERE grade.grade_id=5;
-- 删除全部数据，但是保留表结构
DELETE FROM grade ;
-- 显示创建表的语句
SHOW CREATE TABLE grade;

数据库的基本查询

-- 如果userid=1 则输出张三
SELECT CASE userid WHEN '1'  THEN '张三' 
 WHEN '2' THEN '李四'
  WHEN '3' THEN '王五' ELSE '丽丽'  END as name,username FROM  USER;
-- 取右边三位
SELECT RIGHT('zhansahng',3);
-- 取左边三位
SELECT LEFT('zhansahng',3);
-- 顺序颠倒
SELECT REVERSE('ahkdkkaf');
-- 使用lpad(需要补齐的字段，补齐的长度，以什么补齐)从左边 补齐
SELECT LPAD(username,20,'1') AS demo FROM