DML ---数据操作语言(Data Manipulation Language)( insert delete update 插入 删除 更新)
DQL---- 数据查询语言(Data Query Language)(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:
1
2
|
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' ; |
1
2
|
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>SELECT [DISTINCT] column FROM tb_name WHERE CONDITION; //DISTINCT在count计算不重复的记录的时候能用到 //比如SELECT COUNT( DISTINCT player_id ) FROM task; //就是计算talbebname表中id不同的记录有多少条 |
#重复的结果只显示一次
1
|
mysql> SELECT DISTINCT Gender FROM students; |
#组合条件,可以使用AND,OR,NOT,XOR组合多个条件
1
|
mysql> SELECT * FROM students WHERE Age>20 AND Gender= 'M' ; |
#使用BETWEEN...AND...筛选出年龄介于20-25之间的数据
1
|
mysql> SELECT * FROM students WHERE Age BETWEEN 20 AND 25; |
#查询Name以Y开头的的数据,%表示任意长度的任意字符,_表示任意单个字符
1
|
mysql> SELECT * FROM student WHERE Name LIKE 'Y%' ; |
使用IN关键词,将条件限定在一个列表中。用IS关键词,表示条件是否为空(IS NULL 或者 IS NOT NULL)
1
|
mysql> SELECT * FROM students WHERE Age IN (20,22,24); |
#将查询的结果进行排序
1
|
mysql> SELECT * FROM students ORDER BY Name { ASC | DESC }; |
#查询结果别名显示
1
|
mysql> SELECT Name AS Stu_Name FROM students; |
#LIMIT限定查询结果的条数,LIMIT 2,3表示偏移2条数据后,取3条数据
1
|
mysql> SELECT * FROM students LIMIT 2; |
#求平均数:AVG(),最大值:MAX() 最小值MIN() 数量:COUNT() 求和:SUM()
1
|
mysql> SELECT AVG (age) FROM students; |
#分组GROUP BY
1
|
mysql> SELECT Age, Gender FROM students GROUP BY Gender; |
#过滤:HAVING
1
|
mysql> SELECT COUNT (Age) AS Num,Age FROM students GROUP BY Age HAVING Num>2; |
多表查询:
#指定已哪个字段连接2张表
1
|
mysql> SELECT students. Name ,courses.Cname FROM students,courses WHERE students.CID1
= courses.CID; |
#左外连接...LEFT JION...ON...
1
|
mysql> SELECT s. Name ,c.Cname FROM students AS s LEFT JION
courses AS c ON s.CID1=c.CID; |
#右外连接...RIGHT JION...ON...
1
|
mysql> SELECT s. Name ,c.Cname FROM students AS s RIGHT JION
courses AS c ON s.CID1=c.CID; |
子查询
#查询年龄大于平均年龄的数据
1
|
mysql> SELECT * FROM students WHERE Age >
( SELECT AVG (Age) FROM students); |
#在FROM中使用子查询
1
|
mysql> SELECT Name ,Age FROM ( SELECT * FROM students WHERE CID IN (2,3)) AS t WHERE Age>20; |
#联合查询
1
|
mysql>( SELECT Name ,Age FROM students) UNION ( SELECT Tname,Age FROM tutors); |