create datebase wzsheng; #创建wzsheng数据库
drop database DB; #删除用户的数据库
use wzsheng; #进入wzsheng数据库
show tables; #显示表
select * from wu; #显示wu表所有列
create user wu@localhost identified by "cnhope"; #创建wu用户密码为cnhope
rename user wu to zhi; #将用户wu改为zhi名
drop user zhi; #删除zhi用户
grant select,insert,update,delete on wzsheng.* to zhi@localhost; #用户授权
revoke select on wzsheng.* from zhi@localhost; #回收用户权限
show grants for zhi; #查看zhi用户权限
flush privileges; #刷新权限
破解/设置密码
#修改用户当前密码
set password=password('cnhope');
#修改root用户密码
update mysql.user set Password=password('123456') where user='root';
#修改指定用户密码
update mysql.user set password=password('新密码') where user="test" and host="localhost";
#破解mysql密码
vim /etc/my.cnf
[mysqld]
skip-grant-tables
service mysql restart
#mysql
use mysql;
update user set password=password(‘cnhope’) where user=’root’;
exit
service mysql restart
# distinct 返回唯一值
select distinct wu_id from products;
# limit限制显示数量
select name from wu limit 5 #显示name信息不多与5行
select name from wu limit 5,5 #在表中name列第5行起(就从6行开始) 显示5条信息
#order by显示顺序
select name from wu order by name #从wu表中显示name并且name中按字母顺序排序显示
select id,name from wu order by id,name #先按id排序( 从小到大)当遇到相同则按 name开头字母顺序排序
#desc 降序
select id,name from wu order by id desc,name; #id按降序排序
MariaDB [wzs]> select prod_price from products order by prod_price desc limit 1; #显示最大的数
# where指定搜索条件
#select 目标 from 表 where 条件
select id from product where name = "RT-AC68U";
#order by 使用在where 后面
#在表wu中显示满足id为2 或 3并且按name顺序 显示name和price数据
select name , price from wu where id = 2 or id =3 order by name;
#WHERE子句操作符
![这里写图片描述](https://img-blog.csdn.net/20180816222006367?watermark/2/text/aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1VQTkVX/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70)
select id, price form wu where id=3 and price <=10; #显示要满足ID=3 和price=10的数据
select id, price form wu where id=3 or id=10; #选择id是3或者10
#数据过滤
#and 优先集比or高 所以 or 需要加 ()
MariaDB [wzs]> select * from products where (vend_id=1002 or vend_id=1003) and prod_price <=10;
#in 指定条件范围
select id,name from wu where id in(1002,1003) ;
#not 否定 条件
select id,name from wu WHERE id NOT IN (1002,1003) ORDER BY name; #不显示ID=1002和1003
select prod_name,prod_price from products where prod_price between 5 and 10;
#检查是否存在空值
#显示prod_price的null的行
select prod_name from products where prod_price is null;
#通配符使用like
#% 可以匹配0个或多个字符,但是单独 % 不能匹配
SELECT id,name FROM wu WHERE name like zhi%; # 搜索zhi开头的name数据
%zhi% s%e
_下滑线只能匹配单个字符
#正则表达式 regexp
like(匹配整个列, 如果被匹配的文本在列值
中出现, LIKE将不会找到它)和regexp(在列值内进行匹配) 区别
#like 只显示 +通配符的数据 若直接+确切数据则不显示
而 REGEXP 都行
显示含1000的name数据
SELECT name FROM wu WHERE name LIKE/REGEXP '1000' 只有REGEXP语句才有显示数据
#区分大小写 binary
WHRER name REGEXP BINARY 'Jet .00';
#OR 或 |
SELECT name FROM wu WHERE name ERGEXP '100|200' ORDER BY name 显示含100或200 name 数据
#[123]zhi 是OR 的另一种形式
查询 1zhi 或 2zhi 或 3zhi
显示1到5的Ton
select prod_name from products where prod_name regexp '[1-5] Ton' order by prod_name;
分组数据group by
以book分组,统计每种book的数量
select count(number) num from wu group by book;
会议book种类分组,再对price分组,再对date分组
select count(number) num from wu group by book,price,date;
#having限定分组查询
作用相当于where
显示avg(money) > 500
select book,avg(money) average from wu group by book having avg(money) > 500;
like/ regexplike(匹配整个列, 如果被匹配的文本在列值中出现, LIKE将不会找到它)和regexp(在列值内进行匹配) 区别like 只显示 +通配符的数据 若直接+确切数据则不显示而 REGEXP都行
% 可以匹配0个或多个字符,但是单独 % 不能匹配
SELECT id,name FROM wu WHERE name like zhi%; 搜索zhi开头的name数据
%zhi% s%e
_下滑线只能匹配单个字符
显示含1000的name数据
SELECT name FROM wu WHERE name LIKE/REGEXP '1000' 只有REGEXP语句才有显示数据
区分大小写 binary
WHRER name REGEXP BINARY 'Jet .00';
格式\\( \\)
SELECT name FROM wu WHERE name REGEXP '\\([0-9] wu?\\)' ORDER BY name
可显示 1 wus 2wu 等
concat()设置显示格式数据查询
以指定格式显示name和book并命名为information字段
select concat(name,' the book is:', book) information from wu;
as 修改字段名
将字段name显示为ha
select name as ha from wu;
运算符
+ - × /
SELECT id ,number, price,number*price AS allnumber FROM wu WHERE order_num = 2005
显示为
其中allnumber 为 number 和 price 乘积
[1-9] 1到9 数据
[a-z] a到z 字母
\\f 换页
\\n 换行
\\r 回车
\\t 制表
\\v 纵向制表
*0个或多个
+ 一个或多个 = {1,}
? 0个或多个 = {0,1}
{n} 指定数目匹配
{n,} 不少于指定数目匹配
{n,m} 匹配n到m范围 m不超过255
定位符
^ 文本的开始 和 否定
$ 文本的结尾
[[:<:]] 词的开始
[[:>:]] 词的结尾
使用数据处理函数
// Upper 将文本转换文大写
MariaDB [wzsheng]> select vend_name ,Upper(vend_name)
-> as vend_name_upcase
-> from vendors
-> order by vend_name;
// soundex() 匹配所有发音类似于Y.Lie的联系名:
MariaDB [wzsheng]> select cust_name,cust_contact
-> from customers
-> where soundex(cust_contact) = soundex('Y Lie');
// 日期date(order_date) 时间 time()
MariaDB [wzsheng]> select cust_id,order_num
-> from orders
-> where date(order_date) = '2005-09-01';
汇总数据
//AVG()
MariaDB [wzsheng]> select avg(prod_price)
-> as avg_price
-> from products;
//count()
MariaDB [wzsheng]> select count(*)
-> as num_cust
-> from customers;
//电子邮件客户数量
MariaDB [wzs]> select count(cust_email) from customers;
//COUNT(*)对表中行的数目进行计数,null空值还是not null
//COUNT(column)对特定列中具有值的行进行计数,忽略空值
MariaDB [wzsheng]> select count(*) as mun_items,
-> min(prod_price) as price_min,
-> max(prod_price) as price_max,
-> avg(prod_price) as price_avg
-> from products;
//sum
MariaDB [wzs]> select sum(quantity*item_price) as total_price
-> from orderitems
-> where order_num=20005;
//分组数据联合
//GROUP BY 必须出现在where子句之后 order by 之前
//where 显示vend_id = 1003的信息
MariaDB [wzsheng]> select count(*) as num_prods
-> from products
-> where vend_id = 1003 ;
//创建分组
//group by显示一组vend_id
//group by句必须出现在WHERE子句之后, ORDER BY子句之前
MariaDB [wzsheng]> select vend_id,count(*) asnum_prods
-> from products
-> group by vend_id;
//在尾部+ WITH ROLLUP, 可以得到每个分组以及每个分组汇总级别(针对每个分组)的值
//过滤分组
//having 过滤分组 WHERE过滤行( WHERE没有分组的概念) 且having可完全取代where
MariaDB [wzsheng]> select cust_id, count(*) as orders
-> from orders
-> group by cust_id
-> having count(*) >=2;
此处不能用where,该过滤是基于分组聚集值而不是特定行值。
//having(分组后过滤) WHERE(分组前过滤)
MariaDB [wzsheng]> select vend_id,count(*) as num_prods
-> from products
-> where prod_price >= 10 在分组前过滤掉price>=10
-> group by vend_id
-> having count(*) >= 2;
MariaDB [wzsheng]> select vend_id,count(*) as num_prods
-> from products
-> group by vend_id
-> having count(*) >= 2;
//分组和排序
//group by 和order by 联合使用
MariaDB [wzsheng]> select order_num,sum(quantity*item_price) as ordertotal
-> from orderitems
-> group by order_num
-> having sum(quantity*item_price) >=50
-> order by ordertotal;