MySQL语句DDL,DML,DQL基础篇

MySQL语句DDL,DML,DQL基础篇

DDL语法

DML语法

DQL语法

单行注释
– 单行注释
/*
多行注释
*/
– sql是结构化查询语言
– MySQL是数据库的名称
– SQLyog是一款可视化软件工具

DDL语法

DDL数据(结构)定义语言,用于创建和修改数据库表结构的语言,也是属于sql语言
– 常用语句 create drop alter rename

– 创建数据库
CREATE DATABASE IF NOT EXISTS school_db CHARSET utf8

– 删除数据库
DROP DATABASE school_db

– 修改数据库
ALTER DATABASE school_db CHARSET gbk
– 注意数据库名一旦给定不能修改

– 创建表的语法

---

/*
主键约束:每个表中只能有一个,不能为空,不能重复
唯一约束:一个表中可以有多个,可以为空

*/

/*
create table t_student (
num int PRIMARY KEY comment ‘主键’,
name varchar(10) not null comment ‘姓名’,
sex char(1) default ‘男’ COMMENT ‘性别’,
birthady date,
grade int,
score double(4,1) check(score<=100 and score>=0),
mobile varchar(11) unique,
reg_time DateTIME
)
*/

---

– 创建表,先不添加任何约束,通过ddl语句修改表结构
CREATE TABLE t_student(
num INT ,
NAME VARCHAR(10),
sex CHAR(1),
birtyday DATE,
grade INT,
score DOUBLE(3,1),
mobile VARCHAR(11),
reg_time DATETIME
)

– 添加主键约束
ALTER TABLE t_student ADD PRIMARY KEY(num)

– 删除主键约束
ALTER TABLE t_student DROP PRIMARY KEY

– 主键的自动增长(只能是主键,数据类型必须是int类型)
ALTER TABLE t_student MODIFY num INT AUTO_INCREMENT

– 删除主键的自动增长
ALTER TABLE t_student MODIFY num INT

– 设置不能为空
ALTER TABLE t_student MODIFY NAME VARCHAR(10) NOT NULL

– 删除不能为空
ALTER TABLE t_student MODIFY NAME VARCHAR(10) NULL

– 添加唯一约束
ALTER TABLE t_student ADD CONSTRAINT mobile_unique UNIQUE(mobile)

– 删除唯一约束
ALTER TABLE t_student DROP INDEX mobile_unique

– 添加检查约束
ALTER TABLE t_student ADD CONSTRAINT score_check CHECK(score>=0 AND score<=100)

– 删除唯一约束
ALTER TABLE t_student DROP CHECK score_check

– 添加列
ALTER TABLE t_student ADD address VARCHAR(50) FIRST
ALTER TABLE t_student ADD address VARCHAR(50) AFTER mobile

– 删除列
ALTER TABLE t_student DROP address

– 修改列的类型
ALTER TABLE t_student MODIFY score INT

– 修改列的类型
ALTER TABLE t_student CHANGE mobile phone VARCHAR(11)

---

DROP TABLE t_student

DML语法

– DML 数据操作语言
– 常用语句 insert delete update (增,删,改)

CREATE TABLE t_student(
num INT ,
NAME VARCHAR(10),
sex CHAR(1),
birtyday DATE,
grade INT,
score DOUBLE(3,1),
mobile VARCHAR(11),
reg_time DATETIME
)
– 添加主键约束
ALTER TABLE t_student ADD PRIMARY KEY(num)
– 添加主键自动增长
ALTER TABLE t_student MODIFY num INT AUTO_INCREMENT
– 插入数据
/*
方式1: INSERT INTO 表名(列1,列2……,列n) VALUES(值1,值2……,值n);
方式2: INSERT INTO 表名 set 列名1=值1,…列名n=值n;
方式3: INSERT INTO 表名(列1,列2……,列n) VALUES(值1,值2……,值n),(值1,值2……,值n);
方式4:INSERT INTO 表名(列1,列2……,列n) 查询语句(查询的列数与插入列数匹配);

*/
– 方式1:INSERT INTO 表名(列1,列2……,列n) VALUES(值1,值2……,值n);
INSERT INTO t_student(NAME,sex,birtyday,grade,score,mobile,reg_time) VALUES(‘张三’,‘男’,NOW(),1,90,15769216735,CURDATE())
– 方式2:INSERT INTO 表名 set 列名1=值1,…列名n=值n;
INSERT INTO t_student SET NAME=‘李四’,sex=‘男’,birtyday=NOW(),grade=‘2’,mobile=‘163737484’,score=‘88’,reg_time=CURTIME()
– 方式2:INSERT INTO 表名(列1,列2……,列n) VALUES(值1,值2……,值n),(值1,值2……,值n)
INSERT INTO t_student(NAME,sex,birtyday,grade,score,mobile,reg_time)
VALUES(‘张三’,‘男’,NOW(),1,90,15769216735,CURDATE()),
(‘王涛’,‘男’,NOW(),1,90,15739216735,CURDATE()),
(‘吴琼’,‘女’,NOW(),1,90,15769216735,CURDATE())

– 方式4:插入一个新表 INSERT INTO 新表名(列1,列2……,列n) 查询语句(查询的列数与插入列数匹配);
– 表名后不描述列名,表示向表中所有的列插入数据 值的数量需要与列的数量相匹配
CREATE TABLE t_stu(
num INT ,
NAME VARCHAR(10),
sex CHAR(1),
birtyday DATE,
grade INT,
score DOUBLE(3,1),
mobile VARCHAR(11),
reg_time DATETIME
)
INSERT INTO t_stu SELECT * FROM t_student

---

– 修改表数据 update
– 修改所有的行
– 语法:update 表名 set 列名=‘修改后的值’
UPDATE t_stu SET sex=‘n’

– 添加条件修改表数据,只修改条件所对应的行
– 语法:update 表名 set 列名=‘修改后的值’ where 条件
UPDATE t_stu SET sex=‘男’ WHERE grade=‘1’

– 删除表数据,删除是条件所对应的行
– 语法:delete from 表名 where 条件
DELETE FROM t_stu WHERE sex=‘n’

– 清空整张表
– 语法:truncate table 表名
TRUNCATE TABLE t_stu

---

DQL语法

– DQL 数据查询语言,可以从一个表中查询数据，也可以从多个表中查询数据。
– select 查询的结果 from 表 where 条件 [分组][分组后条件] 排序 数量限制
– 查询操作不会改变表中的数据.类似在一个操作完就销毁的备份表中操作

---

– 查询结果处理：
/*
查询常量值 SELECT 100;
查询表达式：select 100*98;
查询函数：select 函数; / 例如version()
特定列查询:select column1,column2 from table
全部列查询: select * from table
排除重复行: select distinct column1,column2 from table
算数运算符:+ - * /

*/

– 查询结果是常亮/查询常量值
SELECT 90 FROMt_student
– good是给查询结果表起的别名
SELECT score=90 good FROM t_student
SELECT 10*10
SELECT grade+score FROM t_student

– 函数
SELECT NOW(),VERSION()

– 查询表数据
– 特定列查询
SELECT NAME ,sex FROMt_student
– 查询整张表
SELECT * FROM t_student

– 排除重复行(重复:指的是多行数据所有列相同)(针对查询出来的结果去重)
SELECT DISTINCT sex,score FROM t_student
SELECT DISTINCT score FROMt_student

– 算数运算符:+ - * /(+没有连接功能)
SELECT score+grade FROM t_student
SELECT NAME+sex FROM t_student
SELECT score/2 FROM t_student
SELECT score-10 FROM t_student

---

/*
查询结果处理：
函数:类似于java中的方法，将一组逻辑语句事先在数据库中定义好,可以直接调用
好处：
隐藏了实现细节
提高代码的重用性
调用：select 函数名(实参列表) [from 表];
分类：
单行函数：如concat、length、ifnull等
分组函数：做统计使用，又称为统计函数、聚合函数、组函数

*/

– 单行字符函数
/*
字符函数
length()：获取参数值的字节个数
char_length()获取参数值的字符个数
concat(str1,str2,…)：拼接字符串
upper()/lower()：将字符串变成大写/小写
substring(str,pos,length)：截取字符串 索引位置从1开始
instr(str,指定字符)：返回子串第一次出现的索引，如果找不到返回0
trim(str)：去掉字符串前后的空格或子串,trim(指定子串 from 字符串)
lpad(str,length,填充字符)：用指定的字符实现左填充将str填充为指定长度
rpad(str,length,填充字符)：用指定的字符实现右填充将str填充为指定长度
replace(str,old,new)：替换，替换所有的子串
*/

– length(列名),返回字符字节数量长度(以字节为单位),只能有一个字符串/列
SELECT LENGTH(NAME) FROM t_student
SELECT LENGTH(‘好烦好烦好烦’)

– char_length(列名)返回字符数量长度(以字符为单位),只能有一个字符串/列
SELECT CHAR_LENGTH(NAME) FROM t_student
SELECT CHAR_LENGTH(‘好烦好烦好烦’)

– concat(列名1,列名2,)连接字符串/列
SELECT CONCAT(NAME,score) FROM t_student
SELECT CONCAT(‘aa’,‘bbb’)

– upper()/lower()：将字符串变成大写/小写
SELECT UPPER(‘aaa’)
INSERT INTO t_stu SET NAME=’ ooDDDD ’
SELECT UPPER(NAME) FROM t_stu
SELECT LOWER(‘AAA’)
SELECT LOWER(NAME) FROM t_stu

– substring(字符串/列,开始的位置,截取的长度)：截取字符串 索引位置从1开始
SELECT SUBSTRING(‘asdsdfgg’,2,5)
SELECT SUBSTRING(NAME,1,3) FROM t_student

– instr(字符串/列,指定字符)：返回字符第一次出现的索引，如果找不到返回0 索引位置从1开始
SELECT INSTR(‘asdsads’,‘s’)

– trim 去掉前后空格 或者 去掉前后指定字符
SELECT CHAR_LENGTH(TRIM(NAME)) FROM t_stu;
SELECT TRIM(NAME) FROM t_stu;
SELECT TRIM(‘D’ FROM NAME) FROM t_stu;

– lpad rpad 左天充,右填充
SELECT LPAD(NAME,7,‘a’) FROM t_student
SELECT RPAD(NAME,7,‘a’) FROM t_student

– replace(str,old,new)：替换，替换所有的子串
SELECT REPLACE(NAME,‘o’,‘A’) FROM t_stu

– 单行逻辑处理
/*
case when 条件 then 结果1 else 结果2 end; 可以有多个when
ifnull(被检测值,默认值)函数检测是否为null，如果为null，则返回指定的值，否则返回原本的值
if函数：if else的效果 if(条件,结果1,结果2)
*/
SELECT
CASE WHEN score>=90 THEN ‘优秀’ ELSE ‘良好’ END 评价 FROM t_student

SELECT
num,
NAME,
(CASE WHEN score>=90 THEN ‘优秀’
WHEN score>=80 AND score<=89 THEN ‘良好’
WHEN score>=70 AND score<80 THEN ‘中等’
ELSE ‘不好’
END) GDK – 定义别名
FROM t_student

– ifnull(判断的列,指定的默认值)
SELECT IFNULL(phone,‘暂未登记手机号’)phone FROM t_student
SELECT IFNULL(phone,NAME)phone FROM t_student

– if(条件,结果1,结果2)
SELECT IF(score>=60,‘及格’,‘不及格’)score FROM t_student

– 数学函数
SELECT ROUND(5.4); – 四舍五入
SELECT CEIL(5.4); – 向上取整
SELECT FLOOR(5.9); – 向下取整
SELECT TRUNCATE(5.4363,2);-- 指定保留的小数位数,直接截断

– mod(被除数,除数)：取余，被除数为正，则为正；被除数为负，则为负
SELECT MOD(10,-3);
SELECT MOD(10,-3);

SELECT RAND(); – 返回0-1之间的随机数

/*
日期函数
now()：返回当前系统日期+时间
curdate()：返回当前系统日期，不包含时间
curtime()：返回当前时间，不包含日期
可以获取指定的部分，年、月、日、小时、分钟、秒 YEAR(日期),MONTH(日期),DAY(日期) ,HOUR(日期) ,MINUTE(日期) SECOND(日期)
str_to_date：将日期格式的字符转换成指定格式的日期
SELECT STR_TO_DATE(‘1998-3-2’, ‘%Y-%m-%d’);
date_format：将日期转换成字符串
SELECT DATE_FORMAT(NOW(), ‘%y年%m月%d日)’) AS output
datediff：返回两个日期相差的天数

*/

– 日期处理函数
SELECT NOW();
SELECT CURDATE();
SELECT CURTIME();

– 日期格式化
SELECT YEAR(reg_time) FROM t_student;

SELECT MONTH(reg_time) FROM t_student;

– 字符串 转为 日期
SELECT STR_TO_DATE(‘2000-10-1’,"%Y-%m-%d");

– 日期格式化为指定格式的字符串
SELECT DATE_FORMAT(reg_time,’%Y-%m-%d’)m FROM t_student

SELECT COUNT(*),DATE_FORMAT(reg_time,’%Y-%m-%d’)m FROM t_student GROUP BY m

– 计算两个日期之间差
SELECT DATEDIFF(NOW(),reg_time) FROM t_student

– 组函数,聚合函数,统计函数
/*
分类：sum 求和、avg 平均值、max 最大值、min 最小值、count 计数（非空）
1.sum，avg一般用于处理数值型max，min，count可以处理任何类型
2.以上分组函数都忽略null值
3.可以和distinct搭配实现去重的运算
4.count函数的一般使用count（*）用作统计行数
*/

– 求总和
SELECT SUM(score) FROM t_student
SELECT SUM(DISTINCT score) FROM t_student

– 求平均值
SELECT SUM(score),AVG(DISTINCT score) FROM t_student

– 最大值
SELECT MAX(score) FROM t_student

– 最小值
SELECT MIN(score) FROM t_student

– 统计数量
SELECT COUNT(*) FROM t_student
SELECT COUNT(num) FROM t_student