Mysql基础查询语句大全

1:查看表结构

//show columns from 数据表名 from 数据库名;
show columns from notice from manage

2:查看表的某一列结构

//DESCRIBE 表名 列名
DESCRIBE notice type   //注:DESCRIBE 也可写成DESC

3:查询某字母开头的库

show databases like "my%"

4查询某字母开头的表

show tables like "p%"

5:添加列并且修改列

ALTER TABLE user add age VARCHAR(50) NOT NULL,MODIFY id VARCHAR(40)

6:修改列的字段名

//          manage.user -> 库.表名       age address -> 旧列名   新列名
ALTER TABLE manage.user CHANGE COLUMN age address VARCHAR(64)NULL DEFAULT NULL;

7:删除列

ALTER TABLE user DROP address;

8:修改表名

//只能修改一个表
ALTER TABLE school RENAME AS newschool
//使用 ","连接可以同时修改多个表
RENAME TABLE school to newschool,pay to newpay

9:复制表结构

CREATE TABLE newuser LIKE user

10:复制表结构及数据

CREATE TABLE newuser AS SELECT * FROM user

11:删除表

//添加 "IF EXISTS" 可以避免要删除的表不存在时出现错误信息
DROP TABLE IF EXISTS newuser

12:插入查询结果

//插入一行新数据到school且id值等于user表里的id=111的值
INSERT INTO school(id) SELECT id FROM `user` where id = '111'

13:修改某列的值

UPDATE `user` set age = 100 where id = '111'

14:查询多表多列

select school.id,user.id from school,user;

15:查询包含在传入集合里符合的值

//查询所有userId = 123 / 456的数据
SELECT * FROM `user` WHERE userId IN('123','456')

//查询所有userId != 123 / 456的数据
SELECT * FROM `user` WHERE userId NOT IN('123','456')

16:BETWEEN 范围查询

//年龄在1 - 4 之间
select * from user where age BETWEEN 1 and 4

//年龄不在1 - 4 之间
select * from user where age NOT BETWEEN 1 and 4

17:LIKE匹配查询

// "%" 代表一位或多位数
select * from user where id LIKE "124%"

// "_" 代表一位数
select * from user where id LIKE "1_4"

18:NULL字段查询

select * from user where `password` IS NULL

19:AND多条件查询

select * from user where `password` IS NOT NULL AND account = '1'

20:DISTINCT 去重查询

//去除user表里age重复的记录
select DISTINCT age FROM `user`

21:ASC/DESC升序降序查询

//按age升序
select * FROM `user` ORDER BY age ASC

//按age降序
select * FROM `user` ORDER BY age DESC

22:GROUP BY 分组查询

//依据sex分组查询年龄最大的名字并显示MAX(age)列
select name, MAX(age) FROM `user` GROUP BY sex

23:LIMIT限制结果数量

//按UserId倒序只查3条
select * FROM `user` ORDER BY userId DESC LIMIT 3;

24:COUNT查询总记录数

select COUNT(*) FROM `user`

25:SUM查询某列取值总和

//查询age总和
select SUM(age) FROM `user`

26:AVG查询某列平均值

select AVG(age) FROM `user`

27:MAX某列最大值

select MIN(age) FROM `user`

28:MIN某列最小值

select MIN(age) FROM `user`

29:内连接查询两表不同字段

//查询notice和park表里Id相等的title和parktime字段
select title,parktime from notice,park where notice.id = park.id

30:左外连接查询

//查询结果显示notice的所有数据(显示总记录数以notice为主),park只显示符合的数据,其余为NULL,
select title,content,parktime from notice LEFT JOIN park on notice.id = park.id

31:右外连接查询

//查询结果显示park 的所有数据(显示总记录数以park 为主),notice 只显示符合的数据,其余为NULL,
select notice.id,title,content,parktime from notice RIGHT JOIN park on notice.id = park.id

32:多条件查询

select title,park.userid,parktime from notice,park where notice.id = park.id and parktime < 10;

33:IN子查询

//park表里的id包含notice的id才执行此条查询
select * from notice WHERE id in(select id from park)

34:带运算符的子查询

select notice.id,title from notice where notice.id >= (SELECT id from park where parktime = 6)

35:EXISTS子查询

//如果park表存在parktime = 20的数据就查询notice表,否则不
select * from notice where EXISTS(select * from park WHERE parktime = 20)
//多条件符合查询
select * from notice where num < 5 AND EXISTS(select * from park WHERE parktime = 6)

36:ANY查询

//如果ANY前面的条件满足后面的子查询结果的任何一个外层查询就执行
SELECT title,content from notice where num < ANY(SELECT parktime FROM park WHERE parktime = 6)

37:ALL查询

//只有满足ALL后面子查询所有结果才会执行外层查询
//释义:如果notice表的num大于park表parktime最大值(所有结果即最大值)就执行外层查询
SELECT title,content,num from notice where num > ALL(SELECT parktime FROM park)

38:UNION查询

//将notice表的num列和park表的parktime列结果去重合并
select num from notice UNION select parktime from park

39:UNION ALL查询

//只合并结果不去重
select num from notice UNION ALL select parktime from park

40:为表取别名

//在表名长时使用.nt代替notice执行查询
SELECT * FROM notice nt WHERE nt.num = 5

41:字段取别名

SELECT parktime AS pk from park;