当前位置:Gxlcms > 数据库问题 > MySQL基础使用

MySQL基础使用

时间:2021-07-01 10:21:17 帮助过:27人阅读

数据库

其实我们常常说的数据库,应该叫数据库系统。

表和库

数据表:用来保存数据的表格

数据库:用来统一管理数据表的容器

启动mysql 关闭mysql

service mysqld start(启动)

service mysqld stop(关闭)

进入mysql

mysql -uroot -p

修改数据库管理员密码

/usr/bin/mysqladmin -u root password '新密码'

库的操作

查看库

show databases;

创建一个数据库

create database 您要创建的数据库名称;
//创建数据库并指定编码和校对集
create database `数据库名称` character set utf8 collate utf8_general_ci;
create database `数据库名称` character set utf8mb4 collate utf8mb4_general_ci;

修改已存在的数据库编码

alter database 数据库名 charset=新的编码

删除数据库

drop database 要删除的数据库名称;

切换当前数据库

use 数据库名称

数据表的操作

查看某个数据库里面的数据表的列表

show tables;

创建数据表

create table 表名(

    字段名1 类型(长度),
    字段名2 类型(长度),
    字段名3 类型(长度),
    ……
)

比如:

CREATE TABLE `x2` (
  `id` smallint(5),
  `name` varchar(4),
  `sex` tinyint(1),
  `mobile` char(11),
  `address` varchar(100),
  `add_time` datetime
)

创建数据表的时候指定数据表的编码

CREATE TABLE `x5` (
  `id` smallint(5) DEFAULT NULL,
  `name` varchar(4) DEFAULT NULL,
  `sex` tinyint(1) DEFAULT NULL,
  `mobile` char(11) DEFAULT NULL,
  `address` varchar(100) DEFAULT NULL,
  `add_time` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

修改数据表

增加列(字段)

alter table 数据表名 add 列名以及类型、约束等信息
//一次增加多列
alter table 数据表名 add (列名1以及类型、约束等信息,列名2以及类型、约束等信息)

修改列(字段)

//列名必须是已经存在的
alter table 数据表名 modify 列名以及类型、约束等信息
//旧列名必须存在,新列名可以和旧列名一样,但不能和其它列名相同
alter table 数据表名 change 旧列名称 新列名以及类型、约束等信息

删除列

alter table 数据表名称 drop 列名

修改数据表的编码

alter table 数据表名 charset=新的编码

字段类型

字段类型 意义 表示范围
tinyint 微小整型 -128-127 ; 0-255
smallint 小整型 -32768-32767 ; 0-65535
mediumint 中等整型 -8388608-8388607 ;0-16777215
int 整型 -2147493648-2147493647;0-4294967295
bigint 大整型 -9223372036854775808-9223372036854775807;0-18446744073709551615
float 单精度浮点型
double 双精度浮点型
char 字符型
varchar 字符型
text 文本型 支持65535个字符。要求长度+2字节的存储
mediumtext 中等文本型 支持16777215个字符。需要长度+3字节的存储
longtext 长文本型 支持4294967295个字符。需要长度+4字节的存储
datetime 日期类型
timestamp 时间戳日期

字段约束

名称 含义 备注
not null 限制字段值不能为null
default 设置默认值
primary key 设置主键 也可以在最后使用 key(字段名)的形式设置主键
auto_increment 设置自动增长 只能设置主键字段,且类型为整数型
unsigned 设置字段为无符号类型
comment 注释

查看建表语句

show create table 数据表名;

删除数据表

drop table 表名;

数据操作

插入数据

insert into 表名 (字段名1,字段名2,……) values (字段1的值,字段2的值,……),(字段1的值,字段2的值,……),(字段1的值,字段2的值,……);
//从一个表中选择数据插入到另一个表中
insert into user (name,sex,age,mobile,address,native,card_id,number,room,xxx) select name,sex,age,mobile,address,native,card_id,number,room,xxx from student;

查询数据

select * from 表名;
查询用户
select * from mysql.user \G;
查看user表结构 需要具体的项可结合表结构来查询
desc mysql.user;
查看MYSQL数据库中所有用户
SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;
查询特定字段数据
select number,name,age,address,sex from student where native='湖北';
多条件查询
select number,name,age,address,sex from student where native='湖北' and sex=1;
select number,name,age,address,sex from student where native='湖北' && sex=1;


select number,name,age,address,sex from student where native='湖北' or native='湖南';
select number,name,age,address,sex from student where native='湖北' || native='湖南';
select * from student where native in ('湖南','湖北');
where 子句

可以使用各种运算符:

比较运算符: >、 <、 >=、 <=、 !=、 =、 <>

逻辑运算符:and(&&)、or(||)

小括号可以改变各个条件的运算顺序。

字段名 in (条件值)字段名 not in (条件值)

字段名 between A值 and B值 (包含边界) / 字段名 not between A值 and B值(不包含边界)

字段名 like ‘%字符%‘字段名 not like ‘%字符%‘ %可有可无,可在前,可在后。

字段名 is null字段名 is not null 针对值为null的查询处理。

order 子句

可以对查询结果集根据某些字段进行正序(asc)或倒序(desc)排序。可以同时根据多个字段排序,优先以前面的排序,当前面的排序值一样的时候,以紧跟其后的排序规则进行排序,以此类推。

limit 子句

该子句后面可以跟两参数,第一个表示向后偏移量;第二个表示要查询的数量。

当偏移量为0的时候,可以省略。

where、order、limit子句如果两两同时出现,那么顺序应该是:where最前,order次之,limit最后。

group 子句

分组查询,先根据某个分组字段进行分组,然后在组内分别计算。

having 子句

当having不和group子句一起使用的时候,其外在表现和where子句一样。

当having和group同时出现的时候,having要放到group后面。表示,先分组计算处理,然后从分组后的结果中筛选符合条件的数据。

聚合函数

sum() 求和

avg() 求平均数

max() 求最大值

min() 求最小值

count() 求数量

大多数聚合函数的值,可以使用其他语句得到相同值。

其他常用函数
abs() 求绝对值

ceil() 进一法取整

floor() 退一法取整

round(n, m)  对n四舍五入,m表示小数位,默认是0位

mod(a, b) 求a/b的余数(取模)

pi() 获取π的值

rand(n) 获取一个0-1之间的随机数,如果n存在,多次获取,结果的值不变。

sqrt(n) 返回n的平方根

ascii() 获取一个字符在ascii表中的十进制数字值

concat(str1,str2,……,strn); 将多个字符串拼成一个字符串
concat_ws(a, str1,str2,……,strn)  将多个字符串拼成一个字符串,将str1-strn使用a字符拼分割拼接成一个长字符串。
//select concat_ws('-',name,age,mobile,native) from student;

insert(a,m,n,b)  将a字符(字段的值)从第m位开始,选取n个,被替换成b。
//select insert(card_id,7,8,'********') from student;

find_in_set(a, b) 在b列表中寻找a(b需要时使用逗号分割的,比如:'数学,英语,物理' ),返回的是a在b中的位置,为0时表示不存在。
//select find_in_set('数学', subject),id,name from student;

group_concat(字段名) 将一组内的某个字段的所有值使用逗号连接成一个字符串。

ucase(str) / upper(str) 将 str全部转成大写
lcase(str) / lower(str)将 str全部转成小写

length(str) 计算字符str的长度,其中每个汉字占用3位。

left(str,n) 从字符str的左侧开始,选取n位作为结果。
right(str,n) 从字符str的右侧开始,选取n位作为结果。

trim(str) 清除str中开头和结尾的空格部分
ltrim(str) 清除str中开头空格部分
rtrim(str) 清除str中结尾的空格部分

replace(str,old, new) 将str中的old字符(串)替换成new字符(串)

reverse(str) 将str颠倒顺序


curdate() 当前日期
curtime() 当前时间
unix_timestamp() 获取时间戳
now() 获取当前时间和日期


md5() 计算哈希值,使用的是md5算法
sha1() 计算hash值,使用的是sha1算法


if(a,b,c) 如果a为真,则返回b,否则返回c,经测试:a为字符(串)的时候,一直返回c。

ifnull(a,b) 如果a为null,则返回b,否则返回a。

删除数据

delete from 数据表名称;
delete from 数据表名称 where 条件;

修改数据

update 数据表名 set 字段名=新的值 where 条件;
update 数据表名 set 字段名1=新的值,字段名2=新的值 where 条件;

用户管理

创建用户

//创建完默认情况下不能本地登录,只能远程登录
create user 用户名 identified by 密码

//创建用户并只能可以通过某个地址的客户端登录
create user 用户名@主机地址 identified by 密码

//使用授权命令授权的时候创建新用户并授权
grant all privileges on xxxx.* to dddd@'%' identified by '654321';

授权

//使用授权命令授权的时候创建新用户并授权
grant 权限类型 on 数据库.数据表 to 用户@主机地址 identified by 密码;

//给已经存在的用户授权
grant 权限类型 on 数据库.数据表 to 已存在的用户名;

//给用aaa在xxxx数据库中的所有表都授有select权限
grant select on xxxx.* to aaa;
//对于有些权限的设置 需要加上host地址,这个地址一般和该用户在mysql.user表中的host字段的值相同
grant select on xxxx.* to aaa@'host地址';

//给用aaa在xxxx数据库中的所有表都授有update和delete权限
grant update,delete on xxxx.* to aaa;

常用的权限类型:

权限类型 说明
all privileges 所有权限
all 所有权限
select 读取权限
delete 删除权限
update 更新权限
create 创建权限
drop 删除数据库、数据表权限

主机地址

主机类型 含义
localhost 只允许该用户在本地登录,不能远程登录
% 允许在除本机之外的任何一台机器远程登录
192.168.52.32 具体的IP表示只允许该用户从特定IP登录

撤销权限

基本格式:

revoke 权限类型 on 数据库.数据表 from 用户名;

例子:

//撤销某一类权限
revoke update on xxxx.* from aaa;

//一次性多个权限
revoke select,insert,delete on xxxx.* from aaa;

//一次性撤销所有权限
revoke all  on xxxx.* from aaa;
revoke all privileges on xxxx.* from aaa;

刷新权限

flush privileges;

注意:权限更新后,如果之前已经连接到服务端的用户,并不会马上生效新权限更改。只有退出后重新登录才生效。

密码修改

方法1:

mysqladmin -u 用户名 password 新密码 -p

例:

mysqladmin -u root password '654321' -p

方法2:

使用grant授权的时候给已存在用户进行密码修改

//给qqq用户在xxxx库中所有表的所有权限。
//如果qqq用户不存在,那么会创建一个新用户qqq,并且设置密码为111111。
//如果qqq用户已经存在且原密码不是111111,那么会将密码修改为111111。
grant all on xxxx.* to qqq@'localhost' identified by '111111';

方法3:

//给当前登录用户修改密码
set password=password('222222');

//权限比较高的用户为其他用户或者自己修改密码
set password for aaa@'%'=password('333333');

方法4:

直接修改mysql库里面的user表里面的数据

//将用户bbb的密码修改为111111.注意一定要使用password()函数处理
update user set Password=password('111111') where User='bbb';

//该方法修改完密码以后需要刷新一下
flush privileges;

附加1 mysql权限类型

权限 含义
usage 连接(登陆)权限,建立一个用户,就会自动授予其usage权限(默认授予)。该权限只能用于数据库登陆,不能执行任何操作;且usage权限不能被回收,也即REVOKE用户并不能删除用户。
file 拥有file权限才可以执行 ?select ..into outfile和load data infile…操作,但是不要把file, process, ?super权限授予管理员以外的账号,这样存在严重的安全隐患。
super 这个权限允许用户终止任何查询;修改全局变量的SET语句;使用CHANGE ?MASTER,PURGE MASTER LOGS。
select 必须有select的权限,才可以使用select ?table
insert 必须有insert的权限,才可以使用insert ?into ….. values….
update 必须有update的权限,才可以使用update ?table
delete 必须有delete的权限,才可以使用delete ?from ….where….(删除表中的记录)
alter 必须有alter的权限,才可以使用alter ?table
alter routine 必须具有alter ?routine的权限,才可以使用{alter | drop} {procedure|function}
create 必须有create的权限,才可以使用create ?table
drop 必须有drop的权限,才可以删除库、表、索引、视图等
create routine 必须具有create ?routine的权限,才可以使用{create | alter
create temporary tables (注意这里是tables,不是table) 必须有create ?temporary tables的权限,才可以使用create temporary tables.
create view 必须有create ?view的权限,才可以使用create view
create user 要使用CREATE ?USER,必须拥有mysql数据库的全局CREATE USER权限,或拥有INSERT权限。
show database 通过show ?database只能看到你拥有的某些权限的数据库,除非你拥有全局SHOW DATABASES权限。对于root@localhost用户来说,没有对mysql数据库的权限,所以以此身份登陆查询时,无法看到mysql数据库:
show view 必须拥有show ?view权限,才能执行show create view
index 必须拥有index权限,才能执行[create ?| drop] index
excute 执行存在的Functions,Procedures
event event的使用频率较低建议使用root用户进行创建和维护。要使event起作用,MySQL的常量GLOBAL event_scheduler必须为on或者是1
lock tables 必须拥有lock ?tables权限,才可以使用lock tables
references 有了REFERENCES权限,用户就可以将其它表的一个字段作为某一个表的外键约束。
reload 必须拥有reload权限,才可以执行flush ?[tables | logs | privileges]
replication client 拥有此权限可以查询master ?server、slave server状态。
replication slave 拥有此权限可以查看从服务器,从主服务器读取二进制日志。
Shutdown 关闭mysql权限
grant option 拥有grant ?option,就可以将自己拥有的权限授予其他用户(仅限于自己已经拥有的权限)
process 通过这个权限,用户可以执行SHOW ?PROCESSLIST和KILL命令。默认情况下,每个用户都可以执行SHOW PROCESSLIST命令,但是只能查询本用户的进程。
all privileges 所有权限。with ?grant option 可以连带授权

导入和导出

导出

导出某个库的所有表结构和数据

基本结构:

mysqldump -u 用户名 -p 数据库名 > 导出的文件路径

例子:

mysqldump -u root -p xxxx > ./xxxx.sql

导出某个库的所有表结构

基本结构:

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

例子:

mysqldump -u root -p -d xxxx > ./xxxx.sql

导出某个库的某个表的结构和数据

基本结构:

mysqldump -u 用户名 -p 数据库名 数据表名 > 导出的文件路径

例子:

mysqldump -u root -p xxxx user > ./xxxx.sql

导出某个库的某个表的结构

基本结构:

mysqldump -u 用户名 -p -d 数据库名 数据表名 > 导出的文件路径

例子:

mysqldump -u root -p -d xxxx user > ./xxxx.sql

导出所有的数据库结构和数据

mysqldump -uroot -p --all-databases > aaa.sql

导出所有的数据库结构

mysqldump -uroot -p -d --all-databases > aaa.sql

更多请参考:https://www.cnblogs.com/emanlee/p/5410177.html

导入

方法1:

在mysql命令行:

source 备份文件路径

//例
source /root/123.sql

方法2:

 mysql -u 用户名 -p 数据库名 < 数据文件路径  
 
//例
mysql -u username -p test_db < test_db.sql  

杂项

起别名:as

MySQL基础使用

标签:sql   客户   for   www   平方根   now()   html   索引   float   

人气教程排行