MySQL常用语句:
一:数据库操作:
增:create database 数据库名;
删:drop database 数据库名;
改:rename database 数据库名 to 新数据库名;(尝试不对)
查:show databases;
用户操作:
增:create user ‘用户名’@‘用户IP’ identified by ‘password’;
用户名 – 你将创建的用户名
用户ip – 指定该用户在哪个主机上可以登陆,如果是本地用户可用localhost, 如果想让该用户可以从任意远程主机登陆,可以使用通配符%
password – 该用户的登陆密码,密码可以为空,如果为空则该用户可以不需要密码登
删:
删除用户:drop user ‘user’@‘host’;
改:
修改用户信息:
用户信息存储在mysql数据库中user表中,修改表中user字段即可
update mysql.user set user=‘newname’,host=‘newhostname’ where user=‘oldname’;修改新用户名和host
修改用户密码:
set password for ‘user’@‘host’ = password(‘123ABCdef*’);
修改当前登录用户密码:
set password = password(‘123ABCdef*’);
用户权限:
grant privileges on 数据库名.表名 to ‘用户名’@‘用户IP’;
privileges – 用户的操作权限,如SELECT , INSERT , UPDATE 等(详细列表见该文最后面).如果要授予所有的权限则使用ALL
数据库名 – 数据库名
表名- 如果要授予该用户对所有数据库和表的相应操作权限则可用* 表示, 如*.*
grant insert,update,select on 数据库名.表名 to ‘user’@‘host’;
grant all (privileges) on *.* to ‘user’@‘host’;
额外参数:with grant option 此用户可以给其他用户授权
额外参数:identified by ‘访问密码’ 给用户操作的授权密码
回收权限:
revoke privileges on 数据库名.表名 from ‘用户名’@‘用户IP’;
revoke insert,update,select on 数据库名.表名 from ‘user’@‘host’;
revoke all (privileges) on *.* from ‘user’@‘host’;
额外参数:cascade 回收此用户给其他人的权限
revoke all (privileges) on *.* from ‘user’@‘host’ cascade;
假如你在给用户’javacui’@‘%’授权时是这样的:GRANT SELECT ON test.user TO ‘javacui’@’%’; 则在使用 REVOKE SELECT ON *.* FROM ‘javacui’@'%’;并不能撤销该用户对test数据库中user表的SELECT 权限
同理,如果授权使用的是GRANT SELECT ON *.* TO ‘javacui’@‘%’;则 REVOKE SELECT ON test.user FROM ‘javacui’@’%’;也不能撤销该用户对test数据库中user表的 Select 权限
查看具体用户权限:SHOW GRANTS FOR ‘javacui’@'%’;
查:所有用户及密码(加密过的)存储在数据库mysql中user表里
使用select * from mysql.user;查看所有信息
使用select user from mysql.user;查看所有用户
使用select
flash privileges;刷新数据库
二:表操作:
增:create table 表名(
字段1 数据类型 约束
字段2 数据类型 约束
primary key xxx
);
eg:create table t1(
id int not null primary key,
name char(10) not null
);
删:
drop table 表名;
改:
①添加属性
alter table 表名 add
eg:alter table t1 add(score int not null,name char(20) not null);
②删除属性
alter table 表名 drop 字段名
eg:alter table t1 drop score,name;
③修改属性
alter table 表名 modify或change 字段属性及约束
eg:alter table t1 modify或者change score int not null;
查:
show tables;
三:数据操作:
增:insert into
eg:insert into person(id,name,age.phone,address) values(1,‘zhangsan’,22,8630111,‘shanghai’);
删:delete from
eg:delete from person where id=1
改:update…set…
eg:update person set address=‘zhejiang’ where id=1;
查:select 字段 from 表名;
select all|distinct 目标表达式
from 表名1,表名2
where 条件表达式
group by 列名1 having 条件表达式
order by 列名2 asc|desc
limit number
eg:select * from ti;
select sum(number) from product (as) prod
select distinct typeid from product=select typeid from product group by typied
select id,name,(price+1)*50 (as) newprice from product
解释:as:起一个新名字,可省略
where子句运算符
运算符 | 符号 |
---|---|
集合成员 | IN或NOT IN(等价or) |
字符串匹配 | LIKE或NOT LIKE(%多字符,_单字符,转义字符\) |
空值 | IS NULL或NOT NULL |
算数运算符 | =、>、<、>=、<=、!=或<> |
逻辑运算 | AND与、OR或、NOT非 |
其他运算符 | BETWEEN…AND…或>=AND<= |
连接查询:涉及两个以上的表
分类 | 子类 | 示例 |
---|---|---|
内连接 | 等值连接 | select * from stu s,class c where s.classno=c.classno; (sql92标准) select s.sname,c.cname from stu s join class c on s.classno=c.classno;(sql95标准) |
内连接 | 非等值连接 | select s.sname,c.cname from stu (as) s join class (as) c on s.stuno between 1 and 3; |
内连接 | 自连接 | select a.sname as ‘学生名’,b.sname as ‘班长名’ from stu a inner join stu b on a.monitor=b.stuno; |
内连接:两张表的交集,且A表和B表都必须有数据
分类 | 子类 | 示例 |
---|---|---|
外连接 | 左外连接 | select * from stu a left join class b on a.classno=b.classno; |
外连接 | 右外连接 | select * from stu a right join class b on a.classno=b.classno; |
左外连接:左表+右表满足
右外连接:右表+左表满足
分类 | 示例 |
---|---|
全连接 | select * from stu full join class on stu.calssno=class.classno;//oracle select *from stu left join class on stu.classno=class.classno union select * from stu right join class on stu.classno=class.classno;//mysql |
全连接:两张表的并集
聚集函数:
ID | 聚集函数 | 说明 | 限制 |
---|---|---|---|
1 | AVG() | 平均值 | 数字列 |
2 | COUNT() | 统计 | 数字和字符列 |
3 | SUM() | 求和 | 数字列 |
4 | MAX() | 最大值 | 数字、字符和日期时间列 |
5 | MIN() | 最小值 | 数字、字符和日期时间列 |
count(*):统计所有记录数(null也统计)
count(列名):统计某列值的个数(重复的也统计,null不统计)
count(distinct 列名):统计某列值的个数(重复的不统计,null不统计)
子查询(嵌套查询,查询中的查询)
select * from company
where id (not) in (
select id from company
where ralary>5000 );
ID | 类型 | 示例 |
---|---|---|
1 | select | |
2 | insert | insert into company_bkp select * from company where id in (select id from company); |
3 | update | update company set salary = salary*0.5 where age in (select age from company_bkp where age >=27); |
4 | delete | delete from company where age in (select age from company_bkp where age>=27); |
查询的重要谓词:
like:
字符串部分查询:如:
where name like ‘王%’
where name like ‘王_’
where name like ‘\%_’
bewteen…and…:
范围查找:如:
where age bewteen 20 and 30
where age>=20 and age<=30
in:or的升级版:如:
where status in(0,1,2)
where status=0 or status=1 or status=2
all和any:
所有值和某一个值:如:
select * from student where 班级=‘1班’ and age>all(select age from student where 班级=‘2班’;
select * from student where 班级=‘1班’ and age>(select max(age) from student where 班级=‘2班’;
分组查询:group by
eg:统计年龄再20岁以上且不少于2人的班级及其学生人数,并按人数从大到小排序;
select 班级,count(*) as 人数 from where 年龄>20 group by 班级 having 人数>=2 order by 人数 desc;
注意:having用于分组后的过滤筛选,where用于行条件过滤筛选
having一般配合group by和聚合函数一起出现
where条件子句中不能使用聚合函数,而having子句可以
优先级:from>where>group by>having>select>group by>limit