MySQL常用DDL、DML、DCL语言整理
DDL
----Data Definition Language 数据库定义语言 如 create procedure之类
创建数据库
CREATE DATABASE [IF NOT EXISTS] DBNAME [CHARACTER SET 'CHAR_NAME'] [COLLATE 'COLL_NAME']
修改:ALTER 删除:DROP
DML
----Data Manipulation Language 数据操纵语言
如insert,delete,update,select(插入、删除、修改、检索)插入修改数据
mysql>INSERT INTO students (Name,Gender,teacher) VALUE ('lujunyi','M','mage'),('wusong','M','zhuima'); mysql>INSERT INTO students SET Name='lujunyi',Gender='M',tearcher='zhuima';
更新数据
mysql>UPDATE tb_name SET column=value WHERE column=value; mysql>UPDATE students SET Course='mysql' WHERE Name='lujunyi';
替换数据:
和UPDATE使用方式一样,只要将UPDATE换成REPLACE即可
删除数据:
mysql>DELETE FROM tb_name WHERE conditions; mysql>DELETE FROM students WHERE Course='mysql';
清空表:
mysql>TRUNCATE tb_name
查询数据
单表查询:
mysql>SELECT [DISTINCT] column FROM tb_name WHERE CONDITION;
EXAMPLE:
#基本投影查询
mysql>SELECT Name,teacher FROM students WHERE Name='wusong';
#重复的结果只显示一次
mysql>SELECT DISTINCT Gender FROM students;
#组合条件,可以使用AND,OR,NOT,XOR组合多个条件
mysql>SELECT * FROM students WHERE Age>20 AND Gender='M';
#使用BETWEEN...AND...筛选出年龄介于20-25之间的数据
mysql>SELECT * FROM students WHERE Age BETWEEN 20 AND 25;
#查询Name以Y开头的的数据,%表示任意长度的任意字符,_表示任意单个字符
mysql>SELECT * FROM student WHERE Name LIKE 'Y%';
#使用正则表达式匹配查询,关键词为RLINK或者REGEXP
mysql> SELECT * FROM students WHERE Name RLINK '^[MNY].*$';
#使用IN关键词,将条件限定在一个列表中。用IS关键词,表示条件是否为空(IS NULL 或者 IS NOT NULL)
mysql>SELECT * FROM students WHERE Age IN (20,22,24);
#将查询的结果进行排序
mysql>SELECT * FROM students ORDER BY Name {ASC|DESC};
#查询结果别名显示
mysql>SELECT Name AS Stu_Name FROM students;
#LIMIT限定查询结果的条数,LIMIT 2,3表示偏移2条数据后,取3条数据
mysql>SELECT * FROM students LIMIT 2;
#求平均数:AVG(),最大值:MAX() 最小值MIN() 数量:COUNT() 求和:SUM()
mysql>SELECT AVG(age) FROM students;
#分组GROUP BY
mysql>SELECT Age, Gender FROM students GROUP BY Gender;
#别名:AS
mysql>SELECT COUNT(Age) AS Num,Age FROM students GROUP BY Age;
#过滤:HAVING
mysql>SELECT COUNT(Age) AS Num,Age FROM students GROUP BY Age HAVING Num>2;
多表查询:
#指定已哪个字段连接2张表
mysql>SELECT students.Name,courses.Cname FROM students,courses WHERE students.CID1 = courses.CID;
#连接时指定别名
mysql>SELECT students.Name,courses.Cname FROM students,courses WHERE students.CID1 = courses.CID;
#左外连接...LEFT JOIN...ON...
mysql>SELECT s.Name,c.Cname FROM students AS s LEFT JOIN courses AS c ON s.CID1=c.CID;
#右外连接...RIGHT JOIN...ON...
mysql>SELECT s.Name,c.Cname FROM students AS s RIGHT JOIN courses AS c ON s.CID1=c.CID;
子查询
#查询年龄大于平均年龄的数据
mysql>SELECT * FROM students WHERE Age > (SELECT AVG(Age) FROM students);
#在FROM中使用子查询
mysql>SELECT Name,Age FROM (SELECT * FROM students WHERE CID IN (2,3)) AS t WHERE Age>20;
#联合查询
mysql>(SELECT Name,Age FROM students) UNION (SELECT Tname,Age FROM tutors);
创建视图
CREATE VIEW VIEW_NAME AS SELECT....
DCL
----Data Control Language 数据库控制语言如grant,deny,revoke等,只有管理员才有这样的权限。
创建用户
mysql>CREATE USER 'USERNAME'@'HOST' IDENTIFIED BY 'PASSWORD'
删除用户
mysql>DROP USER 'USERNAME'@'HOSHOST支持通配符
_:任意单个字符
%:任意多个字符
授权
mysql>GRANT pri1,pri2...ON DB_NAME.TB_NAME TO 'USERNAME'@'HOST' [IDENTIFIED BY 'PASSWORD']
取消授权
mysql>REVOKE pri1,pri2...ON DB_NAME.TB_NAME FROM 'USERNAME'@'HOST';
查看授权
mysql>SHOW GRANTS FOR 'USERNAME'@'HOST';
EXAMPLE:
mysql>CREATE USER 'lujunyi'@'%' IDENTIFIED BY ''; mysql>SHOW GRANTS FOR 'lujunyi'@'%'; mysql>GRANT ALL PRIVILEGES ON testdb.* TO 'lujunyi'@'%';
最新文章
- Linux crontab定时器的使用
- java 读取数据库中表定义
- UITableView错误 ‘unable to dequeue a cell with identifier Cell'
- 五:分布式事务一致性协议paxos的应用场景
- 第三百三十一天 how can I 坚持
- 2015 年 Ruby 大盘点
- mybatis 自动生成xml文件配置
- 使用JS截取字符串函数详解
- html5 Canvas画图3:1px线条模糊问题
- 使用咪咕云做C站视频直链源
- 使用控制台调试WinForm窗体程序
- 腾讯云H5语音通信QoE优化
- jsp基础语言-jsp代码段
- Redis数据过期策略详解
- 2019.01.20 bzoj2388: 旅行规划(分块+凸包)
- 2018.10.29 bzoj3718: [PA2014]Parking(树状数组)
- 在 Unity 中基于 Oculus DK1 的开发
- Xuan.UWP.Framework(2)
- Object&;nbsp;c&;nbsp;基础知识
- 498B Name That Tune