MySQL常用DDL、DML、DCL言语收拾(附样例)51CTO博客 - 凯时娱乐

MySQL常用DDL、DML、DCL言语收拾(附样例)51CTO博客

2019-03-06 10:23:21 | 作者: 代秋 | 标签: 言语,查询,数据 | 浏览: 2937

在介绍这些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@%;


版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表凯时娱乐立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章