SQL 基础语句

SQL 基础语句

DDL

• Data Definition Language 数据定义语言
• 创建 create
• 删除 drop
• 修改 alter
• 清空 truncate

show tables ; --查看所有表：    
drop database db1;  --删除数据库    
create database db1 default character set utf8; --创建数据库    
use database; --选择数据库
create table t1(id int(3),name varchar(20));-- 创建表
desc t1--查看表
insert into t1 values(1,'user1'); -- 插入数据
alter table t1 add(age int(3)); --添加表字段语句  
alter table t1 drop id; -- 删除表字段语句  
alter table t1 modify age varchar(2); -- 修改表字段类型格式  
alter table t1 change age p1age int(3); -- 修改表字段名称  
alter table t1 rname per; -- 修改表名
truncate table t1; -- 清空表结构  
drop table t1; -- 删除表结构

DML

• Data Manipulation Language 数据操纵语言
• insert 插入数据入表
• delete 删除数据
• update 修改数据

实例

use db1;

create table tb1(
id int,
name varchar(20),
birth date,
address varchar(50)
);

#插入数据
insert into tb1 (id,name,address)values(102,'rose','beijing')

# 删除表tb1中的所有数据。
delete from tb1;
# 删除表中tb1中的id为101的记录。
delete from tb1 where id=101;
# 删除表tb1中 id为102和地址为上海的的数据
select * from tb1;
delete from tb1 where id=102 and address='shanghai';

update tb1 set address='english',name='micheal' where id=102;
# 将生日为 null 的记录的 name 改为 'general'
update tb1 set name='general' where birth is null;
# 将 id 为 101 的 birth 改为'2000-8-8';
update tb1 set birth = '2000-8-8' where id = 101;

DQL

数据查询语言

select子句  from子句     select colName[,colName.......]  from tablname;
select 列名1 as "要起的名" [, 列名2 as "要起的名" ,... ]  from tablname;

实例

#创建信息表
create table tb2 (
id int(5),
name VARCHAR(10),
job VARCHAR(9),
mgr int(4),
hiredate DATE,
sal int(7),
comm int(7),
deptid int(2)
);


#添加数据
insert into tb2 (id,name,job,mgr,hiredate,sal,comm,deptid) values
(7369 ,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20),
(7499 ,'qfedu','SALESMAN' , 7698 , '1981-2-10' ,  1600 , 300 , 30),
(7521 ,'WARD', 'SALESMAN' , 7698,  '1981-2-22' ,  1250 , 500 , 30),
(7566 ,'JONES','MANAGER' ,  7839,  '1981-4-2' ,  2975 , NULL , 20),
(7654 ,'MARTIN','SALESMAN' , 7698,  '1981-9-28',   1250 , 1400 , 30),
(7698 ,'BLAKE','MANAGER' ,  7839 , '1981-5-1' ,   2850 , NULL , 30),
(7782 ,'CLARK','MANAGER' ,  7839 , '1981-6-9' ,   2450 , NULL , 10),
(7788 ,'SCOTT','ANALYST' ,  7566,  '1987-4-19',   3000 , NULL , 20),
(7839 ,'KING','PRESIDENT' ,NULL,  '1981-11-17',  5000 , NULL , 10),
(7844 ,'TURNER','SALESMAN' , 7698,  '1981-9-8',    1500 , 0   ,  30),
(7876 ,'ADAMS','CLERK' ,   7788 , '1987-5-23',   1100,  NULL , 20),
(7900 ,'JAMES','CLERK' ,    7698 , '1981-12-3',   950 ,  NULL , 30),
(7902 ,'FORD','ANALYST' ,  7566 , '1981-12-3' ,  3000 , NULL , 20),
(7934 ,'MILLER','CLERK',     7782 , '1982-1-23',   1300 , NULL , 10),
(8002 ,'IRONMAN','MANAGER',   7839 , '1981-6-9',    1600, NULL , 10),
(8003 ,'SUPERMAN','MANAGER',   7839 , '1981-6-9',    1600 , NULL , NULL);


#查询 员工姓名 职位 入职时间 所在部门
select name, job, hiredate, deptid
from tb2 limit 5;

SMITH,CLERK,1980-12-17,20
qfedu,SALESMAN,1981-02-10,30
WARD,SALESMAN,1981-02-22,30
JONES,MANAGER,1981-04-02,20
MARTIN,SALESMAN,1981-09-28,30


#查询 姓名 职位 分别起别名
select name as "姓名", job as "职位"
from tb2

# 查询员工表中部门号为 10 和 20 的员工的编号，姓名，职位，工资
select id, name, job, sal
from tb2
where deptid = 10
   or deptid = 20;
   
# 查询员工表中部门号不是 10 和 20 的员工的所有信息。
select *
from tb2
where deptid <> 10
  and deptid <> 20;
  
# 查询员工姓名中有a和s的员工信息。
select name
from tb2
where name like '%a%'
  and name like
      '%s%';
select name
from tb2
where name like '%a%s%'
   or name like
      '%s%a%';
      
      

DCL

DataControlLanguage，数据控制语言，用于创建用户，给用户授权，撤销权限，删除用户。

#创建用户
create user username@ip identified by newPwd;

create user 'mysql'@'192.168.1.128' identified by 'Rkun18.mysql';

#显示用户权限
show grants for username@ip;

mysql> show grants for mysql@192.168.1.128;
+-----------------------------------------------+
| Grants for mysql@192.168.1.128                |
+-----------------------------------------------+
| GRANT USAGE ON *.* TO 'mysql'@'192.168.1.128' |
+-----------------------------------------------+

授权

• DML权限：insert，delete，update
• DQL权限：select
• DDL权限：create，alter，drop…

grant 权限1，权限2... on 数据库名.* to username@ip;

grant select,drop,insert on db1.* to 'mysql'@'192.168.1.128';

mysql> show grants for mysql@192.168.1.128;
+------------------------------------------------------------------+
| Grants for mysql@192.168.1.128                                   |
+------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'mysql'@'192.168.1.128'                    |
| GRANT SELECT, INSERT, DROP ON `db1`.* TO 'mysql'@'192.168.1.128' |
+------------------------------------------------------------------+



#撤销权限
revoke 权限1，权限2..on 数据库名.* from username@ip;


mysql> revoke drop on db1.* from mysql@192.168.1.128;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for mysql@192.168.1.128;
+------------------------------------------------------------+
| Grants for mysql@192.168.1.128                             |
+------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'mysql'@'192.168.1.128'              |
| GRANT SELECT, INSERT ON `db1`.* TO 'mysql'@'192.168.1.128' |
+------------------------------------------------------------+


#删除用户 不同版本mysql可能格式上不相同 注意
drop user 'username'@'192.168.1.128';
#查看现有用户
use mysql;
select Host,User from user;

%,root
192.168.1.128,mysql
localhost,mysql.session
localhost,mysql.sys
localhost,rkun18
localhost,root

drop user 'mysql'@'192.168.1.128';

mysql> select Host,User from user;
+-----------+---------------+
| Host      | User          |
+-----------+---------------+
| %         | root          |
| localhost | mysql.session |
| localhost | mysql.sys     |
| localhost | rkun18        |
| localhost | root          |
+-----------+---------------+

#刷新权限
flush privileges;

MySQL数据导入 导出

导出数据库

mysqldump -h IP -u 用户名 -p -d 数据库名 > 导出的文件名


#导出所有数据

[root@mysql ~]#  mysqldump -uroot -pRkun18.mysql --all-databases > /data/mysql.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@mysql ~]# ll /data/mysql.sql 
-rw-r--r--. 1 root root 891320 6月  12 17:35 /data/mysql.sql

#导出数据库信息
mysqldump -uroot -pRkun18.mysql --databases db1 > 
/data/db1.sql


#导出某张表
mysqldump -uroot -pRkun18.mysql mysql user >/data/user.sql

导入数据库

#远程登录并进入数据库
mysql -h192.168.1.128 -uroot -pRkun18.mysql

-h:表示host地址，本地直接使用localhost，远程需要使用ip地址
-u:表示user用户
-p:表示password密码

#先把db1里的tb2表导出
[root@mysql data]# mysqldump -uroot -pRkun18.mysql db1 tb2 > /data/tb2.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@mysql data]# ll
总用量 4
-rw-r--r--. 1 root root 2873 6月  12 18:00 tb2.sql


#删除tb2
use db1;
drop table tb2;
#查看表 只有tb1
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| tb1           |
+---------------+

#导入数据
mysql> source /data/tb2.sql

mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| tb1           |
| tb2           |
+---------------+

#查看数据
mysql> select * from tb2 limit 5;
+------+--------+----------+------+------------+------+------+--------+
| id   | name   | job      | mgr  | hiredate   | sal  | comm | deptid |
+------+--------+----------+------+------------+------+------+--------+
| 7369 | SMITH  | CLERK    | 7902 | 1980-12-17 |  800 | NULL |     20 |
| 7499 | qfedu  | SALESMAN | 7698 | 1981-02-10 | 1600 |  300 |     30 |
| 7521 | WARD   | SALESMAN | 7698 | 1981-02-22 | 1250 |  500 |     30 |
| 7566 | JONES  | MANAGER  | 7839 | 1981-04-02 | 2975 | NULL |     20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 |     30 |
+------+--------+----------+------+------------+------+------+--------+

#再次删除tb2 退出mysql

#不登陆导入数据库
 mysql -uroot -pRkun18.mysql db1 < /data/tb2.sql