SQL语句使用基础
SQL语句主要划分为三类:DDL、DML、DCL
-
DDL(Data Definition Languages)语句:数据定义语言
这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象。常用的语句关键字主要包括create、drop、alter等。 -
DML(Data Manipulation Languages)语句:数据操纵语句
用于添加、删除、更新、和查询数据库记录,并检查数据完整性。常用的语句 关键字主要包括insert、delete、update和select等。 -
DCL(Data Control Languages)语句:数据控制语句
用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。主要的语句关键字包括grant、revoke等。
DDL语句
连接到MySQL服务器
mysql -uroot -p密码
创建数据库test1
create database test1;
查看有哪些数据库
show databases;
安装MySQL时,系统自动创建了4个数据库
- information_schema:主要存储了系统中的一些数据库对象信息,比如用户表信息、列信息、权限信息、字符集信息、分区信息等。
- cluster:存储了系统的集群信息。
- mysql:存储了系统的用户权限信息。
- test:系统创建的测试数据库,任何用户都可以使用。
选择数据库
use test1;
查看该数据库下所有的数据表
show tables;
如果显示为“Empty set”,则表示操作结果集为空
删除数据库test1
drop database test1
删除数据库后,该数据库下所有的表数据都会被删除,所以,慎重。。。
创建表
示例为,创建表emp,表中包含字段ename(姓名)、hiredade(雇佣日期)、sal(薪水)和deptno(部门编号),字段类型为 varchar(10)、date、decimal(10,2)和 int(2)。
create table emp(ename varchar(10),hiredate date,sal decimal(10,2),deptno int(2));
查看表emp的定义
desc emp; 或 show create table emp \G;
第二种输出的信息更加全面,\G使记录能够按照字段竖向排列,更好的显示内容较长的记录。
修改表emp中字段ename的字段类型
alter table emp modify ename varchar(20);
增加表字段
字段age,字段类型int(3)
alter table emp add column age int(3);
修改字段名和字段类型
alter table emp change age agel int(4);
change可以修改字段名和字段类型,modify只能修改字段类型。但是,change需要输入两次字段名
改变字段排列顺序
新增字段birth放在ename后面
alter table emp add birth date after ename;
修改字段age,放在最前面
alter table emp modify age int(4) first;
删除字段
alter table emp drop column age;
更改表名
alter table emp rename emp1;
删除表
drop table emp1;
DML
插入记录 insert
insert into emp(ename,hiredate,sal,deptno) values('zzx1','2000-01-01','2000',1);
也可以不指定字段名称,但是后面值的顺序应该和字段的排列顺序一致
insert into emp values('lisa','2003-02-01','3000',2);
如果只对表中的某些字段插入值,则其他没写的字段自动设置为NULL
insert into emp(ename,sal) values('dony',1000);
insert语句还可以一次性插入多条记录,每条记录之间用逗号分隔
insert into detp values(5,'dept5'),(6,'dept6');
更新记录 update
update emp set sal=4000 where ename='lisa';
update可以同时更新多个表中的数据,多用在根据一个表的字段来动态的更新另外一个表的字段
update emp a,dept b set a.sal=a.sal*b.deptno,b.deptna=a.ename where a.deptno=b.deptno;
删除记录 delete
delete from emp where ename='dony';
delete可以一次删除多个表的数据。如果不加where,整个表的数据都会被删掉,所以,慎重。。。
delete a,b from emp a,dept b where a.deptno=b.deptno and a.deptno=3;
查询记录 select
1、第一种查询显示所有字段,第二种查询显示指定字段
select * from emp;
或者
select ename,hiredate,sal,deptno from emp;
2、去重查询用distinct关键字:
select distinct deptno from emp;
条件查询用where关键字,比较运算符有=,>,<,>=,<=,!=等,逻辑运算符有or、and等
select * from emp where deptno=1;
3、排序用关键字order by,desc是降序,asc是升序,不写默认是升序。order by后面可以跟多个不同的排序字段,且每个排序字段可以有不同的排序顺序。
select * from emp order by deptno,sal desc;
显示emp表中按照sal排序后的前三条记录:
select * from emp order by sal limit 3;
显示emp表中按照sal排序后从第二条开始的三条记录:
select * from emp order by sal limit 1,3;
4、聚合
对数据进行汇总操作,就用到了聚合操作,语法如下:
select *,fun_nqme from tablename where where_contition group by * with rollup having where_contition;
fun_name表示常用的聚合操作,比如求和sum,count记录数,max最大值,min最小值等
group by表示按照字段分类
with rollup可选,表示是否对分类聚合后的结果再进行汇总。
having是条件再次过滤,where是在聚合前先过滤,having是在聚合后再过滤,如果逻辑允许,尽量用where先进行过滤,这样结果集减小,将对聚合的效率大大提高
统计人数大于1的部门
select deptno,count(1) from emp group by deptno having count(1)>1;
统计所有员工工资的总和、最大值、最小值
select sum(sal) ,max(sal),min(sal) from emp;
5、表连接
表连接分为内连接与外连接,外连接又分为左连接与右连接。
select ename,deptname from emp,dept where emp.deptno=dept.deptno;
select ename,deptname from emp left join dept on emp.deptno=dept.deptno;
第一条为内连接,他只能显示出两个表内共有的信息。第二条为外连接中的左连接,他会显示左表emp中所有的ename,以及右表中对应的的deptname。
6、子查询
查询所需要的条件是另外一条搜索语句的结果,这就用到子查询,子查询用到的关键字有in、not in、=、!=、exist、not exist等
查询emp表中所有在dept表中存在deptno的数据 :
select * from emp where deptno in (select deptno from dept);
如果子查询数据量为1条,可以用=代替in:
select * from emp where deptno = (select deptno from dept limit 1);
在某些情况下,子查询可以转化为表连接:
select emp.* from emp,dept where emp.deptno=dept.deptno;
表连接在很多情况下用于优化子查询
7、记录联合
将两条查询的数据联合在一起,关键词为union和union all。union all将所有数据联合,而union会将两次查询的数据联合并去重。
select deptno from emp
union all
select deptno from dept;
DCL
DCL语句主要是DBA(数据库管理员)用来管理系统中对象的权限。
1.查询用户 Host,User,Password
select host,user,password from mysql.user;
2.创建用户
insert into mysql.user (host,user,password) values ('%','testuser',password'123456'));
或
create user "username"@"host" identified by "password";
3.创建一个用户z1,密码123,具有对sakila数据库中所有的表的select和insert权限:
grant select,insert on sakila.* to 'z1'@'localhost' identified by '123';
4.撤销z1的insert权限
revoke insert on sakila.* from 'z1'@localhost
5.用户分配权限
grant all on *.* to testuser@'%';
grant 权限 on 数据库.表 to 用户名@'登陆主机' identified by '密码';
对于MySQL用户权限,可以参考:https://www.cnblogs.com/yingsong/p/9718426.html
6.刷新系统权限表
flush privileges;
7.查看用户权限
select * from mysql.user where user='testuser' \G;
或者
show grants for 'testuser'@'%'
8.修改用户信息,密码,类似可以修改其他字段
update mysql.user set password=password('654321') where host='%' and user='testuser';
9.删除用户
delete from mysql.user where host='%' and user='testuser';