DML(data manipulation language)

SQL2

1、DMLdata manipulation language)

  1. DML语句 数据库操纵语言: 插入数据INSERT、删除数据DELETE、更新数据UPDATE
  2. 插入数据INSERT
  • INSERT INTO 表名 VALUES活动板房 (值1,值2,值3…值n); --完整插入
  • INSERT INTO 表名(列名,列名) VALUES (值1,值2); ---部分插入
  1. 更新数据UPDAT阿泰宁E
  • UPDAT具备是什么意思E 表名 SET 列名=值 WHERE CONDITION磁盘清理; --- 语法
  • update t6 set name关闭qq空间申请='cc' where id=2;

  • update mysql活动主题.user set authentication_string=password("QianF磁盘分区eng@123456") where usIPer="root"; -- 更改数据库密码

  • flush privileg163网易免费邮箱es -- 更新数据库
  1. 删除数据DELETE
  • DELETE FROM 表名 WHERE CONDITION; --- 语法

  • delete from t6 where id=2;
  1. 更改表的结构
  • alter table t2 add xingbie enum('nan','nv'); --- 增加列

  • 增加列:alter table t2 drop xingbie ; --- 减少列

2、DQL语句 数据库查询语言

(1)目的

在MySQL管理软件中,可具备使用警犬追踪或者鉴别条件的以通过SQL语句中的DQL语言来实现数据的

(2)MySQL查询

  1. 准备环境
  • CREATE16 TABLE company.employee5(
    id int primary ke二进制转八进制y AUTO_INC关闭花呗REMENT not163邮箱登录入口 null,
    name varchar(30) not null,
    se是什么样的年纪太多忧愁是什么歌x enum('male','female') default 'male' not null,
    hire_date date not null,
    post varchar(50) not null,
    job_description varchar(100),
    salary double(15,2) not null,
    office int,
    dep_id int
    ); ---- 结构语句

  • desc employee5; --- 查看表结构

  • insert into c具备的反义词ompany.employee5(name,s活动房ex,hire_date,post,job_descripti阿塔兰忒on,salary,office,dep_id) values
    ('jack','male','20180202','instructor','teach',5000,5data01,100),
    ('tom','male','20180203','instructor','teach',5500,501,100),
    ('robin'魔临,'male','20180202','instructor','teach',8000,501,100),
    ('alice','female',大唐开局震惊了李世民'20180202','instructor','teach',7200,501,100),
    ('aofa','male','20180202','hr','hr是什么表情cc'牡蛎,600,502,101),
    ('harry','male'163.免费邮箱,'20180202','hr',NULL,6000,502,101),
    ('emma16','femal是什么意思日语e','201磁盘阵列80206','sale','sale信息管理与信息系统cc',20000,503,102),
    ('christine163.免费邮箱','female','20180205','sale','sa大唐开局震惊了李世民lecc',2200,503,102),
    ('zhuzhu','male','20180205','sale',N阿陶陶的三部小说是ULL,2200,503,102),
    ('gougou','m是什么样的年纪太多忧愁是什么歌ale','20180205计算器在线','sale','',2200,5大明流匪03,102); ----插入数据
  1. 简单查询
  • SELECT * FROM 表名; --- 查看所有列
  • SELECT 列1,列2,列3 FROM 表名; --- 查看部分列
  • SELECT name, salary, salary*14 FROM employee5; --- 查看年薪
  • SELECT name, salary, salary*14 as 年薪 FROM employee5; ---查看个性化的年薪
  1. 活动方案格式模板件查询
  • 单条件查询where

    SELECT具备造句 name,post FROM employee5 WHERE post='hr';

  • 多条件查询AND/OR

    SELECT name,salary FROM employee5 WHERE post='hr' AND salary>1000;

    SELECT name, salary FROM employee5 WHERE salary=6000 OR salary=8000

  • 关键字BETWEEN AND 在什么之间

    SELECT name,salary FROM em163.免费邮箱ployee5 WHERE salary BETWEEN 5000 AND 15000;

    SELECT name,具备造句sa具备和俱备lary FROM employee5 WHERE salary NOT BETWEEN 5000 AND 15000;

  • 关键字IN集合查询

    SELECT name, salary FROM employee5 WHERE salary IN (4000,5000,6000,9000) ;

    SELECT name, salary FROM employee WHERE sipoalary NO二进制转十进制计算T IN (4000,5000,6000,9000) ;是什么样的年纪太多忧愁是什么歌

  • 关键字IS NULL

    SELECT name,job_description FROM employee5 W163邮箱HERE job_descri计算器ption IS NULL139;

    SELECT name,job_des活动cription FROM employee5 WHERE job_description='';大唐开局震惊了李世民 --错误示范

  • 关键字LIKE模糊查询

    SELECT FR信息素OM employee5 WHERE name LIKE 'al%'; //通配符’%’代表多个任意字符,注意不是she活动ll的""星号。mysql使用"%" ---模糊查找‘姓a’

    SELECT * FROM employee5 WHERE name LIKE 'al___'; // 注意不是shell的“?”问号。mys磁盘清理ql使用下划线 -

  1. 查询排序
  • 升序排列

    SELECT * FROM 表名 ORDER BY 工资的列名 ASC;

  • 降序排列

    SELECT * FROM 表名 ORDER BY 工资的列名 DESC;

  • 查询前几位

    SELECT * FROM employee5 ORDER BY salary DESC LIMIT 5; //默认初始位置为0