文章目录

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