SQL基础

在这里插入图片描述

mysql dos常用命令

打开mysql服务

net start mysql

关闭mysql服务

net stop mysql

登陆mysql

mysql -u root -p

连接到远程主机上的mysql
假设远程主机ip为： 22.22.22.22 ,用户名为root ，密码为abcd123.则键入以下命令

mysql -h22.22.22.22 -u root -p abc123

退出mysql

exit

修改密码
修改root 密码

update user set password=password("sss") where user = "root";

打开数据库

use datatable

显示所有数据库

show databases;

显示数据库mysql里的所有表，先切入到数据库（如use datatables)，才能看到表

show tables;

显示表mysql数据库中的user表的列信息

describe user;

数据库创建

create  database db_name;

删除数据库

drop database db_name;删除时可先判断是否存在，drop database if exits db_name

建表

create table table_name(字段一 数据类型，字段二 数据类型）；

添加数据 insert into 表名[(字段一，字段二）]value (值1，值2）；
如果向表中的每一个字段都插入一个值，[]中括号里面的可以不用写

查询表里所有数据

select * from tbale;

更新指定数据

update table_name set 字段名 = '新值‘where id =1;

删除表中数据

delete from table_name;

一次创建多个数据库用户

create user username identified by 'password' ,username2 identified by 'password'

用户的权限控制
将某个库中的某个表的控制权赋予某个用户

grant all on db_name.table_name to user_name [indentified by 'password'];

表结构的修改
增加一个字段

alert table  table_name add column 字段名 字段类型 alter 某字段；

修改字段名称

alter table table_name change 旧字段名 新字段名 新字段类型；

改表的名字

alter table table_name rename to new_table_name;

一次性清空表中的所有数据

truncate table table_name;

备份数据库

mysqldump -h host -u root -p dbname >dbname_backup.sql

sql常用函数

“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 |
| 7 | stackoverflow | http://stackoverflow.com/ | 0 | IND |
+—-+—————+—————————+——-+———+
“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 |
+—–+———+——-+————+

有用的 Aggregate 函数：

AVG() - 返回平均值
COUNT() - 返回行数
FIRST() - 返回第一个记录的值
LAST() - 返回最后一个记录的值
MAX() - 返回最大值
MIN() - 返回最小值
SUM() - 返回总和

AVG() - 返回平均值
下面的 SQL 语句选择访问量高于平均访问量的 “site_id” 和 “count”：

SELECT site_id, count FROM access_log
WHERE count > (SELECT AVG(count) FROM access_log);

COUNT() - 返回行数

COUNT() 函数返回匹配指定条件的行数。适用于mysql，和Oracle
COUNT(DISTINCT column_name) 函数返回指定列的不同值的数目：

SELECT COUNT(DISTINCT column_name) FROM table_name;
MAX

下面的 SQL 语句从 “Websites” 表的 “alexa” 列获取最大值：

SELECT MAX(alexa) AS max_alexa FROM Websites;
MIN

下面的 SQL 语句从 “Websites” 表的 “alexa” 列获取最小值：

SELECT MIN(alexa) AS min_alexa FROM Websites;
SUM

下面的 SQL 语句查找 “access_log” 表的 “count” 字段的总数：

SELECT SUM(count) AS nums FROM access_log;
GROUP BY

GROUP BY 语句用于结合聚合函数，根据一个或多个列对结果集进行分组。

下面的 SQL 语句统计所有网站的访问的记录数：

SELECT Websites.name,COUNT(access_log.aid) AS nums FROM access_log
LEFT JOIN Websites
ON access_log.site_id=Websites.id
GROUP BY Websites.name;

HAVING 子句

在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用。
HAVING 子句可以让我们筛选分组后的各组数据。
下面的 SQL 语句统计所有网站的访问的记录数：

SELECT Websites.name,COUNT(access_log.aid) AS nums FROM access_log
LEFT JOIN Websites
ON access_log.site_id=Websites.id
GROUP BY Websites.name;

现在我们想要查找总访问量大于 200 的网站，并且 alexa 排名小于 200。
我们在 SQL 语句中增加一个普通的 WHERE 子句：

SELECT Websites.name, SUM(access_log.count) AS nums FROM Websites
INNER JOIN access_log
ON Websites.id=access_log.site_id
WHERE Websites.alexa < 200 
GROUP BY Websites.name
HAVING SUM(access_log.count) > 200;

UCASE() 函数

UCASE() 函数把字段的值转换为大写。

下面的 SQL 语句从 “Websites” 表中选取 “name” 和 “url” 列，并把 “name” 列的值转换为大写：

SELECT UCASE(name) AS site_title, url
FROM Websites;

LCASE() 函数

LCASE() 函数把字段的值转换为小写。

下面的 SQL 语句从 “Websites” 表中选取 “name” 和 “url” 列，并把 “name” 列的值转换为小写：

SELECT LCASE(name) AS site_title, url
FROM Websites;

MID() 函数

MID() 函数用于从文本字段中提取字符。

SELECT MID(column_name,start[,length]) FROM table_name;
下面的 SQL 语句从 “Websites” 表的 “name” 列中提取前 4 个字符：

SELECT MID(name,1,4) AS ShortTitle
FROM Websites;

LEN() 函数

LEN() 函数返回文本字段中值的长度。

SELECT name, LENGTH(url) as LengthOfURL
FROM Websites;

ROUND() 函数

ROUND() 函数用于把数值字段舍入为指定的小数位数。
SELECT ROUND(column_name,decimals) FROM table_name;
decimals 必需。规定要返回的小数位数。

ROUND(X,D)：返回参数X的四舍五入的有 D 位小数的一个数字。如果D为0，结果将没有小数点或小数部分。

mysql> select ROUND(1.298, 1);         -> 1.3 mysql> select ROUND(1.298, 0);         -> 1
FORMAT() 函数

FORMAT() 函数用于对字段的显示进行格式化。

SELECT FORMAT(column_name,format) FROM table_name;

column_name 必需,要格式化的字段。
format 必需,规定格式。

下面的 SQL 语句从 “Websites” 表中选取 name, url 以及格式化为 YYYY-MM-DD 的日期：

SELECT name, url, DATE_FORMAT(Now(),'%Y-%m-%d') AS date
FROM Websites;

参考
快速查看