评论

收藏

[MySQL] MySQL常用DDL、DML、DCL语言整理(附样例)

数据库 数据库 发布于:2021-07-04 11:47 | 阅读数:367 | 评论:0

  
  在介绍这些SQL语言之前,先罗列一下mysql的常用数据类型数据类型修饰,供查询参考
  后面的带数字表示此类型的字段长度
  

  数值型:
  TINYINT 1 ,SMALLINT 2,MEDIUMINT 3 ,INT 4,BIGINT 8,DECIMAL,FLOAT 4,DOUBLE 8,BIT
  
  字符串型
  CHAR,VARCHAR,BINARY,VBINARY,TINYBLOB,BLOB,MEDIUMBLOB,LONGBLOG,TINYTEXT,TEXT,MEDIUMTEXT,LONGTEXT,EMUM,SET
  日期时间型
  date,time,datetime,timestamp
  数据限定修饰:
  NOT NULL,NULL,DEFAULT,AUTO_INCREMENT,UNSIGNED,PRIMARY KEY,UNIQUE KEY,FOREIGN KEY
  CHARACTER SET #ps:SHOW CHARACTER SET 显示当前数据库所支持的所有字符集
  COLLATION #ps:SHOW COLLATION 显示所支持的所有排序规则
  

  以下是SQL的每种DDL,DCL,DML语言
  

  
  DDL
----Data Definition Language 数据库定义语言 如 create procedure之类
  创建数据库
CREATE DATABASE [IF NOT EXISTS] DBNAME [CHARACTER SET 'CHAR_NAME'] [COLLATE 'COLL_NAME']
  修改:ALTER 删除:DROP
  创建一张新表
  
CRTATE TABLE [IF NOT EXISTS] TBNAME(col_name col_definition,...)
  EXAMPLE:
mysql>CREATE TABLE students(Id INT PRIMARY KEY NOT NULL AUTO_INCREMENT UNSIGNED,Name CHAR(20) UNIQUE KEY NOT NULL,Age TINYINT UNSIGNED INDEX,Gender CHAR(1) NOT NULL) [ENGINE={MyISAM | InnoDB }];
  

  也可以这样写(区别在于单独定义主键,唯一键和索引):
  
  
mysql>CREATE TABLE students(Id INT NOT NULL AUTO_INCREMENT UNSIGNED,Name CHAR(20) NOT NULL,Age TINYINT UNSIGNED,Gender CHAR(1) NOT NULL,PRIMARY KEY(id),UNIQUE KEY(name),INDEX(age))
  

  查询出一张表的数据后创建新表(字段定义会丢失,数据会保留)
  
CREATE TABLE TBNAME SELECT...
  EXAMPLE:
  
mysql>CREATE TABLE test SELECT * FROM students WHERE Id>5;
  

  以一张表的格式定义,创建一张新的空表
  
CREATE TABLE TBNAME1 LIKE TBNAME2
  修改表:
  ALTER TABLE tb_name
                      MODIFY #修改字段定义
                      CHANGE #可以修改字段名和字段定义
                      ADD
                      DROP
  
  

  EXAMPLE:
  给表添加字段
  
  
mysql>ALTER TABLE students ADD (course VARCHAR(100),teacher CHAR(20));
  添加惟一键
  
mysql>ALTER TABLE students ADD UNIQUE KEY Name;
  修改字段:
  修改course字段为Course字段,并放在Name字段之后(修改字段需要带上新的字段的定义)ps:MODIFY只能修改字段定义
  
  
mysql>ALTER TABLE students CHANGE course Course VARCHAR(100) [AFTER Name];
  重命名表名
  
  
mysql>ALTER TABLE students RENAME TO stu;
mysql>RENAME TABLE stu TO students;
  添加一个外键约束
  
ALTER TABLE students ADD FOREIGN KEY foreign _cid (CID) REFERENCES course (CID);
  创建索引
CREATE INDEX index_name ON TABLE (col_name[(length)] [ASC|DESC]) [USING {BTREE|HASH}];
  删除索引
  
DROP INDEX index_name ON TBNAME;
   查看表状态:SHOW STATUS LIKE 'TBNAME';
   查看表的索引:SHOW INDEXES FROM TBNAME;
  

  DML
----Data Manipulation Language 数据操纵语言如insert,delete,update,select(插入、删除、修改、检索)插入修改数据
   #如果每个字段都有值,不需要写字段名称,每组值用,隔开
  mysql>INSERT INTO tb_name (col1,col2) VALUES ('STRING',NUM),('STRING',NUM);
  mysql>INSERT INTO tb_name SET  col1='string',col2='string';
  mysql>INSERT INTO tb_name (col1,col2,col3) SELECT...;
  EXAMPLE:
  
  
  
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 '123456';
mysql>SHOW GRANTS FOR 'lujunyi'@'%';
mysql>GRANT ALL PRIVILEGES ON testdb.* TO 'lujunyi'@'%';
  


  
关注下面的标签,发现更多相似文章