一、MySQL数据库sql语句
1.1 sql 命令
database | 数据库 |
table | 表 |
row | 行 |
column | 列 |
user | 用户 |
select | 从数据表中获取数据 |
updata | 更新数据库中的数据 |
delete | 从数据库中删除数据 |
insert into | 向数据表插入数据 |
create database | 创建新数据库 |
alter database | 修改数据库 |
create table | 创建新表 |
alter table | 变更数据库 |
drop table | 删除表 |
1.2 sql 规范
在数据库系统中,sql语句不区分大小写;
sql语句可单行或多行书写,以‘;’结尾;
关键词不能跨多行或简写;
用空格和tab缩进来提高语句的可读性
二、管理MySQL数据库
2.1 查看数据库
查看当前服务器中的数据库
show databases; #注意以;结尾
查看数据库中包含的表
方法一:在库中查看表
use mysql; #use 数据库名
show tables; #查看表
方法二 :在库外查看表
show tables from mysql; #show tables from 数据库名
查看表的结构(字段)
use 数据库名;
describe [数据库名.]表名;
可缩写成:desc 表名;
查看MySQL的版本
mysql -V
2.2 数据类型
field | 字段名称 |
type | 数据类型 |
null | 是否允许为空 |
key | 主键 |
default | 默认值 |
extra | 扩展属性 |
id | 1 3 5 7 |
int | 整型,用于定义整数类型的数据 |
float | 单精度浮点4字节32位 准确表示到小数点后六位 |
double | 双精度浮点8字节64位 |
char | 固定长度的字符类型 用于定义字符类型数据 |
varchar | 可变长度的字符类型 |
text | 文本 |
image | 图片 |
decimal(5,2) | 5个有效长度数字,小数点后面有2位 指定长度数组 |
#Char如果存入数据的实际长度比指定长度要小,会补空格至指定长度,如果存入的数据的实际长度大于指定长度,低版本会被截取,高版本会报错
2.3 创建、删除数据库和表
创建新的数据库(create database)
#建立数据库
create database 数据库名;
#建立数据库并指定字符集utf8mb4
create database 数据库名 charset=utf8mb4;
删除指定的数据库(drop database)
drop database 数据库名;
创建新的表(create table)
use 数据库名;
create table 表名 (字段1 数据类型,字段2 数据类型[,...][,primary key (主键名)]);
#主键一般选择能代表唯一性的字段不允许取空值(NULL),一个表只能有一个主键。
例如:
use nj;
create table jiangning (id int NOT NULL,name varchar(10),area decimal(8,2),passwd char(48)DEFAULT' ',PRIMARY KEY (id));
#NOT NULL 不允许为空值
#DEFAULT'' 默认值为空#PRIMARY KEY :主键一般选择没有重复并且不为空值的字段
desc jiangning 描述
删除指定的数据表(drop table)
方法一:库内删除
use 数据库名;
drop table 数据表名;
方法二:库外删除
#如不用USE进入库中,则需加上数据库名
drop table 数据库名.数据表名;
2.4 管理表中的数据记录
向数据表中插入新的数据记录(insert into)
insert into 表名(字段1,字段2[,...]) values(字段1的值,字段2的值,...);
例如:
insert into students(id,name,age,passwd) values(1,'lisi',20,'112233' );
insert into students(id,name,age,passwd) values(2,'jiami',22,PASSWORD('123456'));
#PASSWORD('112233'):查询数据记录时,密码字串以加密形式显示;若不使用PASSWORD(),查询时以明文显示
select * from students;
提前新建数据表(注意长度设置)
use mysql;
create table students (id smallint unsigned primary key auto_increment, name varchar(10),age tinyint unsigned, passwd varchar(48));
此处添加数据为中文,注意字符集(默认为拉丁文)是否更改为utf8和utf8mb4
如果/etc/my.cnf没有更改,建立数据库时要指定字符集utf8mb4:
create database 数据库名 charset=utf8mb4;
查询数据表内数据记录(select)
select 字段名1,字段名2[,...] from 表名 [where 条件表达式];
基本操作:
1. #查询全部数据
select * from 数据表名;
例如:
select * from students;
2. #查询指定数据
例如:
select id,name from students where id=2;
更多操作:
3. #以列表方式竖向显示
select * from 数据表名\G;
例如:
select * from students\G;
4. #只显示头2行
select * from 数据表名 limit 2;
例如:
select * from students limit 2;
5. #显示第3行后的2行
select * from 数据表名 limit 3,2;
例如:
select * from students limit 3,2;
修改、更新数据表中的数据记录(update)
update 表名 set 字段名1=字段值1[,字段名2=字段值2] [where 条件表达式];
例如:
update students set age=19 where id=3;
update students set age=25,passwd='111111' where id=2; #同时修改age和passwd
在数据表中删除指定的数据记录
delete from 表名 [where 条件表达式];
例如:
delete from students where id=2;
!!!!注意id并不会顶上去!!!!
2.5 修改名和表结构(alter table)
修改表名 (rename)
alter table 旧表名 rename 新表名;
例如:
alter table students rename xuesheng;
扩展表结构,增加字段(add)
alter table 表名 add 字段;
例如:
alter table xuesheng add address varchar(50) default 'wait get';
#default 'wait get':表示此字段设置默认值 wait get;可与 NOT NULL 配合使用
修改字段(列)名,添加唯一键(change)
alter table 表名 change 旧列名 新列名 数据类型 [unique key];
例如:
alter table xuesheng change name user_name char(30) unique key;
#CHANGE可修改字段名、数据类型、约束等所有项。
验证:
insert into xuesheng values(6,lili,23,234567,NANKING);
失败,因为设置了唯一键,lili不能重复,改成lulu即可
insert into xuesheng values(6,lulu,23,234567,NANKING);
删除字段(drop)
alter table 表名 drop 字段名;
例如:
#删除表中passwd字段
alter table xuexiao drop passwd;
2.6 克隆表——将数据表的数据记录生成到新的表中
方法1:从现有的表中创建一个克隆表
create table 新表 like 旧表;
insert into 新表 select * from 旧表;
例如:
create table ky35 like ky36; #通过 LIKE 方法,复制 info 表结构生成 test01 表
insert into ky35 select * from ky36; #导入数据
方法2:创建表的时候同时导入
create table ky37 (select * from ky36);
2.7 清空表——删除表内数据
#删除类型
drop table table_name
1)属于DDL
2)不可回滚(无法恢复)
3)不可带where
4)表内容和结构删除
5)删除速度快
truncate table table_name
1)属于DDL
2)不可回滚
3)不可带where
4)表内容删除
5)删除速度快
delete from table_name
1)属于DML
2)可回滚(可恢复)
3)可带where
4)表结构在,表内容要看where执行的情况
5)册删除速度慢,需要逐行删除
不再需要一张表的时候,用drop
想删除部分数据行时候,用delete,并且带上where子句
保留表而删除所有数据的时候用truncate
#删除
速度
drop> truncate > delete
安全性
delete 最好
delete 删除
delete from ky36 where name=xxj;
delete from 表名 where (条件)=(选项)
delete from 表名;
例如:
delete from ky37;
truncate table 表名; 但表的格式没有删除,可以再加入
例如:
truncate table test1;
2.8 创建临时表
##添加临时表ky37
create temporary table tky37 (id int(4) primary key auto_increment,name varchar(10),age varchar(3));
show tables; ## 查看当前库中所有表
insert into ky37 values(1,'wzf',25); ##在临时表中添加数据
select * from ky37; ##查看当前表中所有数据
quit ##退出数据库
mysql -u root -p ##重新登录后进行查看
select * from test3; ##查看之前创建的临时表中所有数据,发现已经被自动销毁
select:查询语句
where:根据条件查询
#语法:select '字段' from 表名 where 条件
#示例:显示name和age 字段 并且要找到age小于20
select name,age from students where age < 20;and且、or或:根据多个条件查询
#语法:select 字段名 from 表名 where 条件1 (and|or) 条件2 (and|or)条件3;
#示例:显示name和age 并且要找到age大于20小于30
select name,age from students where age >20 and age <30;in:显示已知值的资料
#语法:select 字段名 from 表名 where 字段 in ('值1','值2'....);
#示例1:显示学号为1,2,3,4的学生记录
select * from students where StuID in (1,2,3,4);
#示例2:显示班级为1和3的学生记录
select * from students where ClassID in (1,3);
三、忘记root密码的解决办法
修改/etc/my.cnf 配置文件,免密登陆mysql
vim /etc/my.cnf
[mysqld]
skip-grant-tables #添加,使登录mysql不使用授权表systemctl restart mysqld
mysql #直接登录
然后使用SQL语句修改密码
UPDATE mysql.user SET AUTHENTICATION_STRING = PASSWORD('abc123') where user='root';FLUSH PRIVILEGES;
quit
mysql -u root -pabc123PS:最后再把/etc/my.cnf 配置文件里的skip-grant-tables 删除,并重启mysql服务
update mysql.user SET AUTHENTICATION_STRING = PASSWORD('abc123') where user='root'
第一步:修改配置文件,添加配置,使登录mysql不使用授权表
第二步:重启服务,登录测试
systemctl restart mysqld
mysql
第三步:使用update修改root密码,刷新数据库
update mysql.user set authentication_string = password('123456') where user='root';
flush privileges;
第四步:再次修改my.conf配置文件,注释掉之前添加的配置命令
vim /etc/my.cnf
#删除此行
skip-grant-tables
#重启服务
systemctl restart mysqld
四、数据库用户授权
4.1 授予权限
grant语句:专门用来设置数据库用户的访问权限
- 当指定的用户名不存在时,grant语句将会创建新的用户
- 当指定的用户名存在时,grant语句用于修改用户信息
格式:
grant 权限列表 on 数据库名.表名 to '用户名'@'来源地址' [identified by '密码'];
权限列表:用于列出授权使用的各种数据库操作,以逗号进行分隔,如“select,insert,update”。使用“all”表示所有权限,可授权执行任何操作。
数据库名.表名:用于指定授权操作的数据库和表的名称,其中可以使用通配符“”。例如,使用“kgc.”表示授权操作的对象为 kgc数据库中的所有表。
‘用户名@来源地址’:用于指定用户名称和允许访问的客户机地址,即谁能连接、能从哪里连接。来源地址可以是域名、IP 地址,还可以使用“%”通配符,表示某个区域或网段内的所有地址,如“%.accp.com”、“192.168.79.%”等。
identified by:用于设置用户连接数据库时所使用的密码字符串。在新建用户时,若省略“IDENTIFIED BY”部分, 则用户的密码将为空。
4.2 允许指定用户查询指定数据库表
示例:允许用户chen在本地查询ky35数据库中所有表的数据记录,但禁止查询其他数据库中的表的记录
#使用root用户登入数据库
mysql -u root- p123456
#授权*表示库下的所有表
grant select on test.* to 'zhuo'@'localhost' identified by '123456';
#退出数据库
quit
#使用zhuo用户登入
mysql -u chen -p123456
#查看所有库
show databases;
#切换库
use test;
#查看所有表
show tables;
4.3 允许用户在所有终端远程连接并拥有所有权限
示例2:允许用户chen在所有终端远程连接mysql,并拥有所有权限
格式:
grant all on *.* to '用户名'@'%' identified by '用户密码';
示例:
grant all on *.* to 'chen'@'%' identified by '123456';
4.4 查看权限
格式:
show grants for 用户名@来源地址;
示例:
show grants for 'chen'@'localhost';
4.5 撤销权限
#撤销所有权限格式:
revoke all on *.* from '用户名'@'来源地址';
#撤销test库权限格式:
revoke select on test.* from '用户名'@'来源地址';
#示例:撤销用户chen的所有权限
#查看用户chen的权限
show grants for 'chen'@'localhost';
#撤销test库权限格式:
revoke select on test.* from 'chen'@'localhost';
#撤销用户chen所有主机登录的所有权限
revoke all on *.* from 'chen'@'%';