实例
-- 字段
-- 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');运行实例 »
点击 "运行实例" 按钮查看在线实例
