mysql 查询语句

实例

-- 字段

-- id int 
-- name varchar
-- sex  tinyint
-- age  tinyint
-- city varchar
-- money decimal

CREATE TABLE IF NOT EXISTS `info`(
	`id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	`name` VARCHAR(255),
	`sex` TINYINT NOT NULL DEFAULT 0,
	`age` TINYINT UNSIGNED NOT NULL DEFAULT 0,
	`city` VARCHAR(255) NOT NULL DEFAULT '北京',
	`money` DECIMAL(10,2) NOT NULL DEFAULT 0
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO info(id,name,sex,age,city) VALUES(NULL,'李旺儒','0','77','广州'),(NULL,'孔令峰','1','30','广州'),(NULL,'黄景坤','1','30','广州'),(NULL,'奇奇','0','90','深圳'),(NULL,'吴志勇','1','18','深圳'),(NULL,'狗哥','1','10','深圳'),(NULL,'','0','77','广州'),(NULL,'张三金','1','85','郑州'),(NULL,'张书领','1','43','郑州'),(NULL,'申坤奇','1','33','郑州'),(NULL,'王有卓','1','23','南宁'),(NULL,'唐金铭','1','24','南宁'),(NULL,'杨胜涛','1',27,'南宁'),(NULL,'叶泰峰','1','30','南宁'),(NULL,'李帅想','1','18','北京');
INSERT INTO info(id,name,sex,age,city) VALUES(NULL,'想帅帅',0,16,'北京'),(NULL,'李想帅',1,15,'北京'),(NULL,'李想',1,19,'北京');
-- =============================================================
-- 比较运算符 = <> >= <= != > <
SELECT id,name,sex,age,city FROM info WHERE age = 5;
SELECT id,name,sex,age,city FROM info WHERE age <> 5;
SELECT id,name,sex,age,city FROM info WHERE age >= 5;
SELECT id,name,sex,age,city FROM info WHERE age <= 5;
SELECT id,name,sex,age,city FROM info WHERE age != 5;
SELECT id,name,sex,age,city FROM info WHERE age < 5;
SELECT id,name,sex,age,city FROM info WHERE age > 5;

-- 指定范围 between and   not between and 
SELECT id,name,sex,age,city FROM info WHERE id BETWEEN 5 AND 10;
SELECT id,name,sex,age,city FROM info WHERE id NOT BETWEEN 5 AND 10;
SELECT id,name,sex,age,city FROM info WHERE age  BETWEEN 10 AND 30;

-- 指定集合操作  in  not in
SELECT id,name,sex,age,city FROM info WHERE id IN(6,12,14,9,5,10);
SELECT id,name,sex,age,city FROM info WHERE id NOT IN(6,12,14,9,5,10);

-- 多条件查询 AND OR
SELECT id,name,sex,age,city FROM info WHERE sex=1 AND age >40;
SELECT id,name,sex,age,city FROM info WHERE sex=0 OR age <20;

-- 查询我们有几个城市（去除重复） DISTINCT
SELECT DISTINCT city FROM info;
-- ====================================================
-- COUNT 统计行数
SELECT COUNT(id) FROM  info;

-- AVG 平均
SELECT AVG(age) FROM info;

-- SUM 求和
SELECT SUM(age) FROM info;

-- MIN 最小值
SELECT MIN(age) FROM info;

-- MAX 最大值
SELECT MAX(age) FROM info;

-- GROUP BY 分组
SELECT id,name,sex,age,city FROM info GROUP BY city;

-- GROUP_CONCAT() 归类函数 拼接内容

SELECT GROUP_CONCAT(name,',',age),city FROM info GROUP BY city;

-- COUNT
SELECT  city,COUNT(id) FROM info GROUP BY city;

SELECT city,COUNT(id) FROM info GROUP BY city HAVING COUNT(id)>=4;

-- SUM 
SELECT city,SUM(age) FROM info GROUP BY city;
SELECT city,SUM(age) FROM info GROUP BY city HAVING SUM(age)>150;

-- ORDER BY 排序
-- ASC  从小到大 
-- DESC 从大到小
SELECT id,name,sex,age,city FROM info  ORDER BY age ASC;
SELECT id,name,sex,age,city FROM info  ORDER BY age DESC;
SELECT id,name,sex,age,city FROM info ORDER BY age ASC,id DESC;

//SELECT id,name,sex,age,city FROM info WHERE age >20 ORDER BY age DESC;
//-- SELECT 字段列表 FROM 表名
//-- [WHERE 表达式]
//-- [GROUP BY 字段名 [HAVING 条件]]
//-- [ORDER BY 字段名  ASC|DESC]
//-- [LIMIT]


-- 每页显示多少条
-- 偏移量

-- limit  不指定初始值
SELECT id,name,sex,age,city FROM info LIMIT 5;

-- limit  指定初始值
SELECT id,name,sex,age,city FROM info LIMIT 2,5;

-- 为字段起别名 空格 as 

SELECT id '编号',name '姓名',sex '性别',age '年龄',city '城市' FROM info;

SELECT COUNT(id) as total FROM info;

-- 为表起别名
SELECT id,name,sex,age,city FROM info i;
SELECT id,name,sex,age,city FROM info as i;

-- 模糊查询
-- 匹配字符  like   not like
-- _代表1个字符  %代表 0个1个多个

SELECT id,name,sex,age,city FROM info WHERE name LIKE '想_';
SELECT id,name,sex,age,city FROM info WHERE name LIKE '想__';
SELECT id,name,sex,age,city FROM info WHERE name LIKE '_想';
SELECT id,name,sex,age,city FROM info WHERE name LIKE '__想';
SELECT id,name,sex,age,city FROM info WHERE name LIKE '_想_';

-- 以xxxx字符开头的
SELECT id,name,sex,age,city FROM info WHERE name LIKE '想%';

-- 以xxx字符结尾的内容
SELECT id,name,sex,age,city FROM info WHERE name LIKE '%想';

-- 所有带xxx字符串的都会被输出出来
SELECT id,name,sex,age,city FROM info WHERE name LIKE '%想%';

SELECT id,name,sex,age,city FROM info WHERE name NOT LIKE '%想%';

-- 小三表  smallsan
	CREATE TABLE IF NOT EXISTS `smallsan`(
		id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
		name VARCHAR(255),
		info_id INT UNSIGNED NOT NULL
	)ENGINE =MyISAM DEFAULT CHARSET=utf8; 
INSERT INTO smallsan VALUES(NULL,'波多老湿',8);
INSERT INTO smallsan VALUES(NULL,'麻老湿',8);
INSERT INTO smallsan VALUES(NULL,'小泽老湿',8);
INSERT INTO smallsan VALUES(NULL,'苍老湿',8);

INSERT INTO smallsan VALUES(NULL,'加藤鹰',4);
INSERT INTO smallsan VALUES(NULL,'大木老师',4);
INSERT INTO smallsan VALUES(NULL,'犀利哥',4);
INSERT INTO smallsan VALUES(NULL,'面筋哥',4);
INSERT INTO smallsan VALUES(NULL,'狗哥',4);

INSERT INTO smallsan VALUES(NULL,'凤姐',13);
INSERT INTO smallsan VALUES(NULL,'如花',13);
INSERT INTO smallsan VALUES(NULL,'芙蓉',13);
INSERT INTO smallsan VALUES(NULL,'石榴姐',13);
INSERT INTO smallsan VALUES(NULL,'包租婆',13);

-- 查询涛哥的所有信息和他的小三们
-- ERROR 1052 (23000): Column 'id' in field list is ambiguous
SELECT i.id iid,i.name iname,i.sex,i.age,i.city,s.id sid,s.name sname,s.info_id FROM info i,smallsan s WHERE i.id=s.info_id AND i.id=13;

-- 查询奇奇的城市和姓名以及他的小三们
SELECT i.city,i.name,s.name FROM info i , smallsan s WHERE i.id=s.info_id AND i.name='奇奇';

-- 先写出你要查询的表名  写一个where 条件  查看表中的关联字段
-- 在写要查询的字段和我们查询的字段别名

-- 查看三金的信息和他的小三们
SELECT i.id,i.name,i.sex,i.age,i.city,s.name,s.id FROM info i,smallsan s WHERE i.id=s.info_id AND i.name='张三金';

-- 不怎么用
SELECT id,name FROM info
UNION
SELECT id,name FROM smallsan;

SELECT id,name FROM info
UNION ALL
(SELECT id,name FROM smallsan);

INSERT INTO  info(name) VALUES(NULL);
-- 查询不出来我们的NULL
-- SELECT * FROM info WHERE name = NULL;
-- SELECT * FROM info WHERE name != NULL;
-- 想要查询值为null 必须使用 isnull 来进行查询
SELECT * FROM info WHERE name is NULL;
SELECT * FROM info WHERE name is NOT NULL;

-- 表引擎
-- MyISAM
-- InnoDB
-- MyISAM 与 InnoDB
-- MyISAM 读取速度快  但是不支持事务
-- InnoDB 读取速度稍慢 但是支持事务
-- UPDATE info SET money = 10000;

UPDATE info SET money=money-5000 WHERE id=2;
UPDATE info SET money=money+5000 WHERE id=1;

-- 开启事务命令
begin
-- 事务确定
commit
-- 事务回滚
rollback
-- 查看建表语句
SHOW CREATE TABLE info;
-- 修改表引擎
ALTER TABLE 表名 ENGINE =引擎名

-- 琦琦这个用户
GRANT SELECT  ON *.* TO 'qiqi'@'localhost' IDENTIFIED BY '250250';
--
GRANT SELECT ON  ss34.* TO 'gouge'@'localhost' IDENTIFIED BY '138138';

-- 查询所有用户
SELECT user,host FROM mysql.user;

-- 删除用户
DROP USER 'debao'@'localhost';

-- root 修改自己的密码
SET PASSWORD = PASSWORD('654321');
-- root 修改普通用户的密码
--SET PASSWORD FOR '⽤户名'@'主机名'=PASSWORD('新密码') 
SET PASSWORD FOR 'qiqi'@'localhost'=PASSWORD('383838');