mysql第二部分
常用增删改查
//UNSIGNED AUTO_INCREMENT 设置自增 INT设置字段类型为int
//NOT NULL 值不为空 VARCHAR(100)字段类型为varchar长度为100
//定义主键 runoob_id
//设置数据库引擎为 InnoDB 字符集为utf8
CREATE TABLE IF NOT EXISTS `runoob_tbl`(
`runoob_id` INT UNSIGNED AUTO_INCREMENT,
`runoob_title` VARCHAR(100) NOT NULL,
`runoob_author` VARCHAR(40) NOT NULL,
`submission_date` DATE,
PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
desc runoob_tb1; //查看表结构
DROP TABLE runoob_tbl; //删除表
// 插入数据
INSERT into runoob_tbl(runoob_title, runoob_author, submission_date) VALUES("学习 PHP", "菜鸟教程", NOW());
SELECT * from runoob_tbl; --查询runoob_tbl所有数据
// where指定条件 runoob_title='学习 python'的数据
SELECT runoob_author,submission_date from runoob_tbl WHERE runoob_title='学习 python';
// 模糊查询 like 约等于 '%golang%' 其中%为通配符与linux中的*类似
SELECT * from runoob_tbl WHERE runoob_title LIKE '%golang%';
UPDATE runoob_tbl SET runoob_author='younglinuxer' WHERE runoob_title="学习 python"; // 更新一条数据
DELETE FROM runoob_tbl WHERE runoob_title="学习 golang"; // 删除数据
用户与权限管理
grant 权限 on 数据库对象 to 用户
grant all privileges on blog.* to younglinuxer@'%' identified by '123456'; //授权所有权限 给blog库(及下面所有表) 用户为younglinux(连接地址为% 任何地址) 密码为123456
grant all privileges on blog.* to younglinuxer@'192.168.123.%' identified by '123456';
grant select, insert, update, delete on blog.orders to dba@localhost;
查询
tables说明
本文使用数据参考 http://www.runoob.com/sql/sql-tutorial.html
大部分内容只为转发 作为学习笔记 内容如下
mysql> select * from websites;
+----+--------------+---------------------------+-------+---------+
| id | name | url | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1 | Google | https://www.google.cm/ | 1 | USA |
| 2 | 淘宝 | https://www.taobao.com/ | 13 | CN |
| 3 | 菜鸟教程 | http://www.runoob.com/ | 4689 | CN |
| 4 | 微博 | http://weibo.com/ | 20 | CN |
| 5 | Facebook | https://www.facebook.com/ | 3 | USA |
+----+--------------+---------------------------+-------+---------+
5 rows in set (0.00 sec)
mysql> select * from apps;
+----+------------+-------------------------+---------+
| id | app_name | url | country |
+----+------------+-------------------------+---------+
| 1 | QQ APP | http://im.qq.com/ | CN |
| 2 | 微博 APP | http://weibo.com/ | CN |
| 3 | 淘宝 APP | https://www.taobao.com/ | CN |
+----+------------+-------------------------+---------+
3 rows in set (0.00 sec)
mysql> select * from access_log;
+-----+---------+-------+------------+
| aid | site_id | count | date |
+-----+---------+-------+------------+
| 1 | 1 | 45 | 2016-05-10 |
| 2 | 3 | 100 | 2016-05-13 |
| 3 | 1 | 230 | 2016-05-14 |
| 4 | 2 | 10 | 2016-05-14 |
| 5 | 5 | 205 | 2016-05-14 |
| 6 | 4 | 13 | 2016-05-15 |
| 7 | 3 | 220 | 2016-05-15 |
| 8 | 5 | 545 | 2016-05-16 |
| 9 | 3 | 201 | 2016-05-17 |
+-----+---------+-------+------------+
9 rows in set (0.00 sec)
DISTINCT 去重
//DISTINCT 去除重复的条数 因为country只包含两个国家
mysql> select DISTINCT country from websites;
+---------+
| country |
+---------+
| USA |
| CN |
+---------+
2 rows in set (0.00 sec)ORDER BY排序
// 查询结果 按alexa结果排序
mysql> select * from websites order by alexa;
+----+---------------+---------------------------+-------+---------+
| id | name | url | alexa | country |
+----+---------------+---------------------------+-------+---------+
| 6 | stackoverflow | https://stackoverflow.com | 0 | IND |
| 1 | Google | https://www.google.cm/ | 1 | USA |
| 5 | Facebook | https://www.facebook.com/ | 3 | USA |
| 2 | 淘宝 | https://www.taobao.com/ | 13 | CN |
| 4 | 微博 | http://weibo.com/ | 20 | CN |
| 3 | 菜鸟教程 | http://www.runoob.com/ | 4689 | CN |
+----+---------------+---------------------------+-------+---------+
6 rows in set (0.00 sec)
// desc 倒序排列
mysql> select * from websites order by alexa desc;
+----+---------------+---------------------------+-------+---------+
| id | name | url | alexa | country |
+----+---------------+---------------------------+-------+---------+
| 3 | 菜鸟教程 | http://www.runoob.com/ | 4689 | CN |
| 4 | 微博 | http://weibo.com/ | 20 | CN |
| 2 | 淘宝 | https://www.taobao.com/ | 13 | CN |
| 5 | Facebook | https://www.facebook.com/ | 3 | USA |
| 1 | Google | https://www.google.cm/ | 1 | USA |
| 6 | stackoverflow | https://stackoverflow.com | 0 | IND |
+----+---------------+---------------------------+-------+---------+
6 rows in set (0.00 sec)
and(和)/or(或者) 指定条件 in / BETWEEN
//and 同时满足两个条件
mysql> select * from websites where country='CN' and alexa >1000;
+----+--------------+------------------------+-------+---------+
| id | name | url | alexa | country |
+----+--------------+------------------------+-------+---------+
| 3 | 菜鸟教程 | http://www.runoob.com/ | 4689 | CN |
+----+--------------+------------------------+-------+---------+
1 row in set (0.00 sec)
// or 满足其中一个条件
mysql> select * from websites where country='USA' or alexa >1000;
+----+--------------+---------------------------+-------+---------+
| id | name | url | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1 | Google | https://www.google.cm/ | 1 | USA |
| 3 | 菜鸟教程 | http://www.runoob.com/ | 4689 | CN |
| 5 | Facebook | https://www.facebook.com/ | 3 | USA |
+----+--------------+---------------------------+-------+---------+
3 rows in set (0.00 sec)
// in 取同一个字段不同的值,注意和and不同 and是取不同条件 in只是取同一字段的不同的值
mysql> select * from websites where name in ('Google','Facebook');
+----+----------+---------------------------+-------+---------+
| id | name | url | alexa | country |
+----+----------+---------------------------+-------+---------+
| 1 | Google | https://www.google.cm/ | 1 | USA |
| 5 | Facebook | https://www.facebook.com/ | 3 | USA |
+----+----------+---------------------------+-------+---------+
2 rows in set (0.00 sec)
//BETWEEN 取介于两个值(数字,文本,日期)之间的范围值
//取字母在A-G之前(不包含G) 同理取数字between 10 AND 200;
mysql> select * from websites where name between 'A' AND 'G';
+----+----------+---------------------------+-------+---------+
| id | name | url | alexa | country |
+----+----------+---------------------------+-------+---------+
| 5 | Facebook | https://www.facebook.com/ | 3 | USA |
+----+----------+---------------------------+-------+---------+
1 row in set (0.00 sec)
// between和in 混合使用
mysql> select * from websites where name between 'A' AND 'o' and alexa not in (2,3);
+----+--------+------------------------+-------+---------+
| id | name | url | alexa | country |
+----+--------+------------------------+-------+---------+
| 1 | Google | https://www.google.cm/ | 1 | USA |
+----+--------+------------------------+-------+---------+
1 row in set (0.00 sec)
REGEXP 使用正则
// REGEXP后使用正则匹配 '^菜' 匹配菜开头的条目
mysql> select * from websites where name REGEXP '^菜';
+----+--------------+------------------------+-------+---------+
| id | name | url | alexa | country |
+----+--------------+------------------------+-------+---------+
| 3 | 菜鸟教程 | http://www.runoob.com/ | 4689 | CN |
+----+--------------+------------------------+-------+---------+
1 row in set (0.00 sec)JOIN 连接查询
// JOIN 子句用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段 本文中 websites.id 与access_log.site_id 两个字段是关联关系
// INNER JOIN：如果表中有至少一个匹配，则返回行(只查询出能匹配出的行)
mysql> select websites.name,access_log.count,access_log.date from websites inner join access_log on websites.id=access_log.site_id order by access_log.count;
+--------------+-------+------------+
| name | count | date |
+--------------+-------+------------+
| 淘宝 | 10 | 2016-05-14 |
| 微博 | 13 | 2016-05-15 |
| Google | 45 | 2016-05-10 |
| 菜鸟教程 | 100 | 2016-05-13 |
| 菜鸟教程 | 201 | 2016-05-17 |
| Facebook | 205 | 2016-05-14 |
| 菜鸟教程 | 220 | 2016-05-15 |
| Google | 230 | 2016-05-14 |
| Facebook | 545 | 2016-05-16 |
+--------------+-------+------------+
9 rows in set (0.00 sec)
// LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行
INSERT INTO websites(id,name,url,alexa,country) VALUES(NULL,'stackoverflow','https://stackoverflow.com','0','IND')
// 在websites中插入一条数据与access_log中无关联
// left join 从左边取出所有值即使右边null为空
mysql> select websites.name,access_log.count,access_log.date from websites left join access_log on websites.id=access_log.site_id order by access_log.count desc;
+---------------+-------+------------+
| name | count | date |
+---------------+-------+------------+
| Facebook | 545 | 2016-05-16 |
| Google | 230 | 2016-05-14 |
| 菜鸟教程 | 220 | 2016-05-15 |
| Facebook | 205 | 2016-05-14 |
| 菜鸟教程 | 201 | 2016-05-17 |
| 菜鸟教程 | 100 | 2016-05-13 |
| Google | 45 | 2016-05-10 |
| 微博 | 13 | 2016-05-15 |
| 淘宝 | 10 | 2016-05-14 |
| stackoverflow | NULL | NULL |
+---------------+-------+------------+
10 rows in set (0.00 sec)
// RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行
// FULL JOIN：只要其中一个表中存在匹配，则返回行函数
AVG() 求平均值
// 取websites表中alexa的平均值 并设置别名 alexaavg
mysql> select avg(alexa) as alexaavg from websites;
+----------+
| alexaavg |
+----------+
| 787.6667 |
+----------+
1 row in set (0.00 sec)
//查找大于平均值的数据 注意后面使用(sql)包含起来
mysql> select * from websites where alexa > (select avg(alexa) from websites);
+----+--------------+------------------------+-------+---------+
| id | name | url | alexa | country |
+----+--------------+------------------------+-------+---------+
| 3 | 菜鸟教程 | http://www.runoob.com/ | 4689 | CN |
+----+--------------+------------------------+-------+---------+
1 row in set (0.00 sec)COUNT()统计
//统计查询出的数据 SELECT COUNT(column_name) FROM table_name;
mysql> select count(*) from websites where alexa > (select avg(alexa) from websites);
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)GROUP BY
//说实话 我也没搞懂 续更NOW() 当前时间
// 插入数据使用now函数 查询时间为当前时间
INSERT INTO access_log(aid,site_id,count,date) VALUES(10,1,100,NOW());
mysql> select * from access_log order by aid desc limit 1;
+-----+---------+-------+------------+
| aid | site_id | count | date |
+-----+---------+-------+------------+
| 10 | 1 | 100 | 2019-01-02 |
+-----+---------+-------+------------+
1 row in set (0.00 sec)
参考文档
sql教程: http://www.runoob.com/sql/sql-tutorial.html