目录
摘要：本文档详细介绍了生产环境中常用的mysql语法以及注意事项
1、sql语法分类
SQL语言：Structured Query Language：结构化查询语言
DDL：数据定义语言，如CREATE、ALTER、DROP
DML：数据操作语言，如SELECT、UPDATE、INSERT、DELETE
DCL：数据库控制语言，如grant,deny,revoke2、mysql语法规范以及信息查询
2.1语法规范
常用mysql的关键字我们需要大写，库名、表名、字段名称等使用小写
数据库名称、表名称、字段名称不要使用mysql的保留字，如果必须要使用，需要用反引号``将其括起来2.2信息查询语法
SELECT USER() #得到登陆的用户
SELECT VERSION() #得到MySQL的版本信息
SELECT NOW() #得到当前的日期时间
SELECT DATABASE() #得到当前打开的数据库3、mysql用户创建以及赋权限语法
grant all privileges on bigdata.* to wsktest@'%' identified by '你的密码';
-- 将bigdata数据库所有对象的所有权限付给新建的用户wsktest，该用户可从任意
--一台远程机器登录访问(%,表示所有)
flush privileges; 4、mysql数据库操作语法
4.1创建数据库
CREATE {DATABASE|SCHEMA} db_name;
CREATE DATABASE [IF NOT EXISTS] db_name;
#检测数据库名称是否存在，不存在则创建
#在创建数据库的同时指定编码方式
CREATE DATABASE [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset;
注意
- 数据库名称最好有意义
- 名称不要包含特殊字符或者是MySQL关键字4.2数据库其它常用操作语法
#查看当前服务器下全部数据库
SHOW DATABASES|SCHEMAS;
#查看指定数据库的详细信息
SHOW CREATE DATABASE db_name;
#修改指定数据库的编码方式
ALTER DATABASE db_name [DEFAULT] CHARACTER SET [=] charset;
#打开指定数据库
USE db_name;
#得到当前打开的数据库
SELECT DATABASE()|SCHEMA();
#删除指定的数据库
DROP DATABASE db_name;
#如果数据库存在则删除
DROP DATABASE [IF EXISTS] db_name;
5、msyql常用数据类型
CHAR效率高于VARCHAR,CHAR相当于拿空间换时间，VARCHAR拿时间换空间
CHAR默认存储数据的时候，后面会用空格填充到指定长度；而在检索的时候会去掉后面空格；VARCHAR在保存的时候不进行填充，尾部的空格会留下
TEXT列不能有默认值,检索的时候不存在大小写转换
6、msyql表的操作
6.1创建表
CREATE TABLE [IF NOT EXISTS] tbl_name(
字段名称 字段类型 [完整性约束条件],
字段名称 字段类型 [完整性约束条件],
...
)ENGINE=存储引擎 CHARSET=编码方式;6.2约束
UNSIGNED 无符号，没有负数，从0开始
ZEROFILL 零填充，当数据的显示长度不够的时候可以使用前补0的效果填充至指定长度,字段会自动添加UNSIGNEDNOT NULL 非空约束，也就是插入值的时候这个字段必须要给值,值不能为空
DEFAULT 默认值，如果插入记录的时候没有给字段赋值，则使用默认值
PRIMARY KEY 主键，标识记录的唯一性，值不能重复，一个表只能有一个主键，自动禁止为空
AUTO_INCREMENT 自动增长，只能用于数值列，而且配合索引使用,默认起始值从1开始，每次增长1
UNIQUE KEY 唯一性，一个表中可以有多个字段是唯一索引，同样的值不能重复，但是NULL值除外
FOREIGN KEY 外键约束6.3查看数据库表的详细信息
SHOW CREATE TABLE tbl_name;
5.4查看表结构
DESC tbl_name;
DESCRIBE tbl_name;
SHOW COLUMNS FROM tbl_name;6.4删除表
DROP TABLE [IF EXISTS] tbl_name;6.5表结构的相关操作
添加字段
ALTER TABLE tbl_name
ADD 字段名称 字段属性 [完整性约束条件] [FIRST|AFTER 字段名称]
删除字段
ALTER TABLE tbl_name
DROP 字段名称
添加默认值
ALTER TABLE tbl_name
ALTER 字段名称 SET DEFAULT 默认值;
删除默认值
ALTER TABLE tbl_name
ALTER 字段名称 DROP DEFAULT
修改字段类型、字段属性
ALTER TABLE tbl_name
MODIFY 字段名称 字段类型 [字段属性] [FIRST | AFTER 字段名称]
修改字段名称、字段类型、字段属性
ALTER TABLE tbl_name
CHANGE 原字段名称 新字段名称 字段类型 字段属性 [FIRST | AFTER 字段名称]
添加主键
ALTER TABLE tbl_name
ADD PRIMARY KEY(字段名称)
删除主键
ALTER TABLE tbl_name
DROP PRIMARY KEY;
添加唯一
ALTER TABLE tbl_name
ADD UNIQUE KEY|INDEX [index_name] (字段名称)
删除唯一
ALTER TABLE tbl_name
DROP index_name;
修改数据表名称
ALTER TABLE tbl_name
RENAME [TO|AS] new_tbl_name
RENAME TABLE tbl_name TO new_tbl_name;
修改AUTO_INCREMENT的值
ALTER TABLE tbl_name AUTO_INCREMENT=值7、msyql数据的操作
7.1增
INSERT [INTO] tbl_name[(col_name,...)] {VALUE|VALUES}(VALUES...);
不指定字段名称
INSERT tbl_name VALUE(value...)
需要按照建表时的字段顺序给每一个字段赋值
列出指定字段
INSERT tbl_name(字段名称,...) VALUES(值,...)
INSERT ... SET的形式
INSERT tbl_name SET 字段名称=值,...;
INSERT ... SELECT
INSERT tbl_name[(字段名称...)] SELECT 字段名称,... FROM tbl_name [WHERE 条件]
一次添加多条记录
INSERT tbl_name[(字段名称,...)] VALUES(值,...),
(值,....),
(值,...)7.2改
UPDATE tbl_name SET 字段名称=值,字段名称=值 [WHERE 条件]
如果不添加条件，整个表中的记录都会被更新
7.3删
DELETE FROM tbl_name [WHERE 条件]
如果不添加条件，表中所有记录都会被删除
DELETE 清空数据表的时候不会重置AUTO_INCREMENT的值，可以通过ALTER 语句将其重置为1
彻底清空数据表
TRUNCATE [TABLE] tbl_name;
清除表中所有记录，会重置AUTO_INCREMENT的值7.4查询
语法
SELECT select_expr,... FROM tbl_name
[WHERE 条件]
[GROUP BY {col_name|position} HAVING 二次筛选]
[ORDER BY {col_name|position|expr} [ASC|DESC]]
[LIMIT 限制结果集的显示条数]基础操作查询表中所有记录
SELECT * FROM tbl_name;
*所有字段
指定字段的信息
SELECT 字段名称,... FROM tbl_name
库名.表名
SELECT 字段名称,... FROM db_name.tbl_name;
给字段起别名
SELECT 字段名称 [AS] 别名名称,... FROM db_name.tbl_name;
给数据表起别名
SELECT 字段名称 ,... FROM tbl_name [AS] 别名;
表名.字段名的
SELECT tbl_name.col_name,... FROM tbl_name;where条件
比较运算符
> >= < <= != <> <=>
<=> 和 =的区别? <=>可以检测NULL值
IS [NOT] NULL
检测值是否为NULL或者NOT NULL
指定范围 [NOT] BETWEEN ... AND
指定集合 [NOT] IN(值,...)
逻辑运算符
AND 逻辑与
OR 逻辑或
匹配字符
[NOT] LIKE
% 任意长度的字符串
_ 任意一个字符GROUP BY 分组
把值相同放到一个组中，最终查询出的结果只会显示组中一条记录
分组配合GROUP_CONCAT()查看组中某个字段的详细信息
配合聚合函数使用
COUNT()
统计记录总数
如果写的是COUNT(字段名称)，字段中的值为NULL，不统计进来
写COUNT(*)会统计NULL值
SUM() 求和
MAX() 求最大值
MIN() 求最小值
AVG() 求平均值
配合WITH ROLLUP关键使用,会在记录末尾添加一条记录，是上面所有记录的总和
HAVING子句对分组结果进行二次筛选ORDER BY 排序
ORDER BY 字段名称 ASC|DESC
LIMIT 限制结果集显示条数
LIMIT 值
显示结果集的前几条记录
LIMIT offset,row_count
从offset开始，显示几条记录,offset从0开始多表查询
join连接
- 内连接：查询两个表中符合连接条件的记录
SELECT 字段名称,... FROM tbl_name1
INNER JOIN tbl_name2
ON 连接条件
- 左连接：先显示左表中的全部记录，再去右表中查询复合条件的记录，不符合的以NULL代替
SELECT 字段名称,... FROM tbl_name1
LEFT [OUTER] JOIN tbl_name2
ON 条件;
- 右连接：先显示右表中的全部记录，再去左表中查询复合条件的记录，不符合的以NULL代替
SELECT 字段名称,... FROM tbl_name1
RIGHT [OUTER] JOIN tbl_name2
ON 条件;子查询
SELECT 字段名称 FROM tbl_name WHERE col_name=(SELECT col_name FROM tbl_name)
- 内层语句查询的结果可以做为外层语句查询的条件联合查询
SELECT 字段名称,... FROM tbl_name1
UNION
SELECT 字段名称... FROM tbl_name2;
SELECT 字段名称,... FROM tbl_name1
UNION ALL
SELECT 字段名称... FROM tbl_name2;
UNION ALL 是简单的合并，UNION会去掉表中重复记录
8、msyql经典面试案列
8.1生产中表创建规范。重要！！！
create table rzdata(
id int AUTO_INCREMENT primary key,
num long,
name varchar(100),
age int,
createtime timestamp default current_timestamp,
createuser varchar(100),
updatetime timestamp default current_timestamp on update current_timestamp,
updateuser varchar(100)
)
注意：
1）生产上所有的表强制要求第一个字段是自增主键
2）后四个字段也是生产表常有的字段
3）修改别人创建的表时，注意字段命名规则，要统一
4）坚决不能出现汉语拼音以及中文字符为表字段8.2求每个部门的薪水总和最高topN的工种？ 重要！！！
create view sal
as
select
deptno,job,sum(sal+ifnull(comm,0)) as sal
from emp group by deptno,job;
--- 创建视图，目的将复杂的语法抽象出来，comm是奖金，里面可能有null值
select
a.*
from sal a
where
(
select count(*) from sal b where a.deptno = b.deptno and a.sal <b.sal
) <=1
order by a.deptno asc, a.sal desc;
-- 查询top2薪水的工种，并将结果按部门升序，薪水降序的顺序排序
扫一扫
773
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
