mysql数据库中dml_MySQL数据库(DDL和DML基础)

本文介绍了MySQL数据库中的DDL(数据定义)和DML(数据操作)

– SQL语法

– DDL(数据定义语言)

– DML(数据操作语言)

– DCL(数据控制语言)

– 注意: SQL中大小写不敏感(大写和小写是一样的); 每条SQL语句必须以分号结束

– 一.DDL - 主要提供数据库和表的创建、删除和修改

– 0. 删除数据库: drop database 数据库名;

DROP DATABASE school; – 直接删除指定数据库

DROP DATABASE if EXISTS school; – 如果指定的数据库存在就删除数据库

– 1.创建数据库: create database 数据库名;

create database school; – 直接创建指定数据库

CREATE database if not EXISTS school; – 当指定数据库不存在的时候才创建数据库

create database if not EXISTS school default charset utf8; – 创建数据库的时候设置字符集编码方式为utf8,让数据库支持中文数据的存储

– 2.使用/切换数据库: use 数据库名;

use school;

– 3.新建表: create table if not exists 表名(字段名1 类型1, 字段2 类型2,…);

– 注意： a. 表名一般需要加前缀’t’或者’tb’ b.字段用来确定表中要存储哪些数据，字段名随便命名但是不能是关键字 c.数据类型必须是MySQL支持的数据类型

– 常用数据类型: int-整数, char(size)-定长字符串, varchar(size)-不定长字符串, text-字符串, bit-布尔, date-日期

CREATE TABLE if not EXISTS t_student(stuid int, stuname varchar(20), gender bit, birth date);

– 新建表并且添加约束: create table if not exists 表名(字段名1 类型1 约束1, 字段2 类型2 约束2,…);

– 常见约束: not null - 不为空, default - 设置默认值, unique - 值唯一, primary key - 主键约束

– 主键约束: 主键的值可以确定列表中唯一一条记录(通过一个主键值可以找到表中的唯一一条记录)

– 注意: auto_increment只针对主键有效,并且主键的类型是整型;

CREATE TABLE if not EXISTS t_student

(

stuid int not null auto_increment,

stuname varchar(20) not null,

gender bit DEFAULT 1,

birth date,

PRIMARY KEY(stuid) – 将字段stuid作为当前表的主键(设置主键可以间接约束这个字段的值是唯一的)

);

– 4.删除表: DROP TABLE if EXISTS 表名;

DROP TABLE if EXISTS t_student;

– 清空表中的数据: TRUNCATE TABLE 表名;

TRUNCATE TABLE t_student;

– 5.修改表

– 5.1 添加列: alter TABLE 表名 add COLUMN 字段名 字段类型 约束;

alter TABLE t_student add COLUMN score FLOAT(8,2) DEFAULT 0;

– 5.2 删除列: alter TABLE 表名 drop COLUMN 字段名;

alter TABLE t_student drop COLUMN gender;

– 二、DML(数据操作语言) - 主要针对数据库中数据的增、删、改、查

– 1.增(添加数据/记录)

– 1.1插入数据/记录: insert into 表名 values(值1, 值2, 值3,….) - 依次给指定表中的字段赋值

INSERT into t_student VALUES(100, ‘张三’, 0, ‘2019-9-23’);

– 1.2插入数据/记录: insert into 表名(字段名1,字段名2,…) values(值1, 值2,…) -以指定的顺序给指定的字段赋值

INSERT into t_student(stuname, birth) VALUES(“小花”, date(now())); – 一次插入一条记录

– 一次插入多条记录

INSERT into t_student(stuname, birth) VALUES

(“小花”, date(now())),

(‘小明’, ‘2018-9-8’),

(‘路飞’, ‘1999-12-16’),

(‘佐助’, ‘2000-10-12’);

– 值的问题: sql中是数字对应的值直接写，字符串需要使用引号引起来，bit类型的值只有0或者1, 时间可以用内容是满足时间格式字符串也可以是通过时间函数获取的值

– 时间函数: now() - 当前时间 date(now()) - 当前日期 year(now()) - 当前年 month(now()) - 当前月 ….

– 2.删(删除数据/记录)

– delete from 表名; - 删除指定表中所有记录

DELETE FROM t_student;

– delete from 表名 where 条件语句; - 删除满足条件的记录

– SQL中的条件语句: =(判断是否相等), <>(不等于,和python中的!=功能一样), >, =, <=

DELETE FROM t_student WHERE stuid=100; – 删除t_student表中stuid的值等于100的记录

DELETE FROM t_student WHERE stuname=’小花’; – 删除t_student表中stuname的值等于’小花’的记录

DELETE FROM t_student WHERE stuid<108; – 删除t_student表中stuid的值小于’小花’的记录

– 3.改(修改数据/记录)

– update 表名 set 字段1=新值1, 字段2=新值2,…; - 将指定表中所有行的指定列/字段的值赋值为新值

UPDATE t_student set birth=’1999-10-1’, gender=1;

– update 表名 set 字段1=新值1, 字段2=新值2,… where 条件语句; -将表中满足条件的行中指定字段的值赋值为新值

UPDATE t_student set gender=0 WHERE stuname=’小花’;

– 通配符%: 表示任意个数的任意字符(包括0个)

UPDATE t_student set birth=’2000-01-01’ WHERE stuname LIKE ‘小%’; – 修改stuname是以’小’开头的行对应的birth的值

UPDATE t_student set birth=’2111-01-01’ WHERE stuname LIKE ‘%小%’;

– 统配符_: 表示一个任意字符

UPDATE t_student set birth=’2444-01-01’ WHERE stuname LIKE ‘小_’; – 修改stuname只有两个字符，并且第一个字符是‘小’对应的行的birth的值

– 注意: 通配符只针对字符串有效!

– 4.查(获取数据)

– 4.1直接查询

– select * from 表名; - 获取指定表中所有行和所有的列(所有数据)

SELECT * FROM t_student;

– select 字段名1,字段名2,… from 表名; - 获取指定表中所有行指定的列

SELECT stuname,stuid FROM t_student;

– select * from 表名 where 条件; - 获取指定表中所有满足条件的行所有列的数据

SELECT * FROM t_student WHERE stuid>115;

– 4.2列重命名

– select 字段1 as 新字段1, 字段2 as 新字段2,… from 表名;

– 注意： 这儿的as可以省略

SELECT stuid as ‘学号’, stuname, gender as ‘性别’ FROM t_student; – 对查询结果中的stuid和gender字段进行重命名

– 4.3对查询结果重新赋值(一般针对布尔数据)

– select if(字段名,值1,值2) from 表名; -查询指定字段，并且判断字段对应的值是0还是1，如果是1结果为值1，否则为值2

– 注意: 这儿的if的用法是MySQL专有的

– MySQL写法: if(字段, 新值1, 新值2)

SELECT stuname,if(gender,’男’,’女’) as ‘性别’ FROM t_student;

– 通用写法: case 字段 when 值 then 新值1 else 新值2 end

SELECT case gender WHEN 1 THEN ‘男’ ELSE ‘女’ END as ‘性别’ FROM t_student;

– 4.4对列进行合并

– select concat(字段1,字段2,…) from 表名;

SELECT CONCAT(stuname,stuid) as ‘name_id’ FROM t_student;

SELECT CONCAT(stuname,’:’,stuid) as ‘name_id’ FROM t_student;

– 注意: 数字和字符串数据可以合并，bit类型的数据不可以合并

– SELECT CONCAT(stuname,’:’,gender) as ‘name_id’ FROM t_student;

– 4.5模糊查询 - 查询的时候时候通过like条件来指定查询对象

– sql中支持逻辑运算符and(逻辑与运算)和or(逻辑或运算)，not(逻辑非)

SELECT * FROM t_student WHERE stuname like ‘%飞%’ or not stuid < 110;

– 4.6排序(先按之前的任何语法进行查询在排序)

– select * from 表名 order by 字段; - 对查询结果按照指定字段的值进行升序排序

– select * from 表名 order by 字段 asc; - 对查询结果按照指定字段的值进行升序排序

– select * from 表名 order by 字段 desc; - 对查询结果按照指定字段的值进行降序排序

SELECT * FROM t_student ORDER BY gender; – 按性别升序排序

SELECT * FROM t_student ORDER BY stuid ASC; – 按学号升序排序

SELECT * FROM t_student ORDER BY stuid DESC; – 按学号降序排序

– 排序的时候可以通过在order by的后边加多个字段，进行联排。排序的时候前面的字段的优先级高些

SELECT * FROM t_student ORDER BY gender ASC, stuid DESC; – 先按性别进行升序排序，然后再按学号降序排序。

– 4.7限制

– select * from 表名 limit N; – 获取查询结果的前N条记录

SELECT * FROM t_student LIMIT 3; – 获取查询结果的前3条记录

– select * from 表名 limit M offset N; – 对查询结果跳过前N条数据，取M条数据出来

SELECT * FROM t_student LIMIT 3 offset 4; – 跳过前4数据，然后取3条数据

– =================================sql基础补充===============================

– 1.条件语句的写法

– 在SQL中可以通过 where 条件语句来对操作对象进行筛选 - 筛选

– a. 比较运算符： =, <>, >, =, <=

– 注意: 判断一个字段的值是否为空不用使用=和<>, 而是使用is null 和 is not null

SELECT addr FROM t_student;

SELECT stuname FROM t_student WHERE addr is NULL; – 判断是否为NULL(空)

SELECT stuname FROM t_student WHERE addr=’’; – 判断是否是空串

– b. 逻辑运算符: and, or, not

– c. where 字段名 between 值1 and 值2 – 筛选指定的字段的值在值1和值2之间

SELECT stuname,birth FROM t_student WHERE birth BETWEEN ‘1990-1-1’ AND ‘1999-12-31’;

– d. where 字段名 in 集合 – 筛选出字段值是集合中的元素;(集合是使用括号括起来里面多个值)

SELECT * FROM t_student WHERE stuname in (‘小花’, ‘小明’, ‘路飞’);

– e. like操作

– 2.数据类型

– varchar(size): 不定长字符串，size决定的是最大长度

– char(size): 定长字符

– text: 不限长度(最大是255个字符)

– int/tinyint(-128~127)

– FLOAT(size,d)/DOUBLE(size,d) - 这儿的size和d的值都有约束效果

– bit: 只有0和1两个值

– date/datetime/time: 值可以是时间函数的结果，也可以时间字符串；计算或者是比较的时候内部是按时间处理的

UPDATE t_student SET tel=’1367819230’;

ALTER TABLE t_student ADD COLUMN intro VARCHAR(10);

SELECT * FROM t_student;

– 3.去重

SELECT DISTINCT addr FROM t_student;