mysql数据库的基本命令
一、数据库及其表管理
数据库管理
显示数据库集合:
>SHOW DATABASES;
创建数据库:
>CREATE DATABASE DATABASENAME(数据库名);
删除数据库:
>DROP DATABASE DATABASENAME(数据库名);
选中数据库(为了对数据库中的表单进行操作):
>USE DAYABASENAME;
修改数据库名:
方法一:可以进入到myslq安装时的数据目录,运用linux命令“mv”将数据库名重新命名。
数据库表单
数据类型:
对于MYSQL来说:主要的数据类型大致可以分为三类,Text(文本)、Number(数字)和 Date/Time(日期/时间)。
Text类型:
Number类型:
Date类型:
表单管理
`创建数据库表:`
>CREATE TABLE table_name(column1 datatype,column2 datatype, .....columnN datatype);
例:
>CREATE TABLE person( id int,name varchar(16),phone char(11));
复杂:
>create table person(id,int NOT NULL,uName,varchar(16) NOT NULL,phone,char(11) NUll);
其中:NULL 则指定该字段的值可以为空,NOT NULL 指定该字段不可以为空。这里的空并不是空字符串,空格字符串,而是未对该字段赋值就判定为空。
`为表新增列:`
>ALTER TABLE [tableName] ADD [columName] [列数据类型];
例:为 person 表新增一列 email,varchar 类型。
>ALTER TABLE person add email varchar(24);
'修改表列属性:列属性包括,列名、列数据类型。'
'修改列名'
>alter table 表名 change column 旧列名 新列名 新列名格式;
例:
>ALTER TABLE person change column uName userName varchar(123);
'修改列数据类型'
MySQL
>alter table 表名 modify column 列名 列类型
例:
>alter table person modify column userName varchar(16);
SQLServer
>alter table 表名 alter column 列名 列类型;
修改数据类型这块,各个应用数据库实现语法稍有差异,但仅限于关键字不同,格式上大体相似。
`删除列:`
>alter table 表名 drop 列名;
例:删除 person 表列 email
>ALTER TABLE person DROP email;
`删除行:`
> delete from student where sex='F';
[test_DATA]> delete from test_DATA.example where id='2';
`表名修改:`
>ALTER TABLE 表名 RENAME *想要修改成的表名*;
>RENAME TABLE 表名 TO 想要修改成的表名;
`删除数据库表:`
>drop table 表名;
注意,这里的表删除是连同表结构加表数据全部删除,谨慎使用。
二、数据库用户管理
创建用户
创建用户
>create user 用户名@"ip地址" "identified" by 密码;
例:
>create user tom@"192.168.101" identified by "123";
例:创建除本机以外其他ip第能登入
>create user 用户名@"%" "identified" by 密码;
用户授权
用户授权
'给予权限:'
> GRANT ALL on *.* to toms@"%" identified by "123";
all:增删改查所有权限(select,updata,delete,insert,create,drop ...);
all的地方可以修改对于的方式updata,select等待select后面可以跟字段表示只能查看某个字段;
on后面库名.表明,*表示所有;
to后面跟账号和账号ip;
如果创建账号时候没有创建密码可以在授权这里加上密码;
'收回权限:'
>REVOKE [all privileges]/某些权限 on 库名.表名 from user@"host";
all privileges:表示所有权限
删除用户
>drop user “user”@"host"
>drop mysql.user ''@localhost;
>drop mysql.user 'root'@centos7.localhost;
刷新权限表:
刷新权限表 一些时候权限信息可能会有所延迟 可以执行该语句立即刷新权限信息
>flush privileges;
更改用户名
`# mysql -u root -p `
Enter password:***
mysql> use mysql; 选择数据库
Database changed
mysql> update user set user="新用户名" where user="root"; 将用户名为root的改为新用户名
mysql> flush privileges; 刷新权限
mysql> exit
更改用户密码
有三种方式:
1.linux命令行执行mysqladmin客户端程序
2.mysql命令行set password命令
3.mysql命令行update命令 (推荐使用,避免多用户问题)
`第一种:mysqladmin方式`
$ mysqladmin -uuser_name -p'oldPasswd' password 'newPasswd'
其中user_name即为用户名,oldPasswd即为旧密码,newPasswd即为新密码;
示例:
$ mysqladmin -uroot -p'1qaz!QAZ' password 123456
`第二种:set password方式`
需要先通过mysql -uroot -p进入mysql命令行
格式
mysql> set password for user_name@localhost=password('newPasswd');
Query OK, 0 rows affected (0.94 sec)
其中user_name即为用户名,newPasswd即为新密码;
示例
mysql> set password for root@localhost = password('1qw2!QW@');
`第三种:update命令`
需要先通过mysql -uroot -p进入mysql命令行
格式
mysql> update mysql.user set password=password('newPasswd') where user='userName';
或:
mysql>update users set passwd=md5("新密码") where user='userName';;(2)
mysql> flush privileges;
其中user_name即为用户名,newPasswd即为新密码;
示例
mysql> update mysql.user set password=password('1qaz!QAZ') where user='root';
Query OK, 0 rows affected (0.94 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.94 sec)
Q & A 其他问题
问题:修改密码后服务端生效,但是navicat还能使用旧密码连接mysql?
其实mysql数据库中,我们所说的用户不仅仅只是'root'
,更多的是指root@localhost
和root@%
这种带有userName'@'hostName'
的才是具体的用户。
1)当我们使用:
mysql> set password for root@localhost=password('newPasswd');
可能只是改了root@localhost
这个用户,有可能还有root@%
用户。
2)查看user表:
mysql> select host, user, password from mysql.user where user='root';
在这里插入图片描述
从user表中,我们可以看到root
对应到多个host
,使用之前的set语句可能我们只是更改了其中一个用户,我们可以通过update
命令更新mysql.user
表,全局更新所有root@hostName
的密码;
3)所以,我们应该使用:
mysql> update mysql.user set password=password('newPasswd') where user='root';
Query OK, 1 row affected (0.11 sec)
Rows matched: 5 Changed: 1 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.94 sec)
这样可以全局改变所有带有root@的用户密码,再通过navicat使用旧密码连接数据库时,远程访问拒绝。
查看Mysql历史命令
有时,我们在mysql中运行一些命令,我们按键盘向上方向键会一一列出来。
如何查看命令历史呢,就像在linux下的shell中查看shell命令历史。
mysql 有这样一个机制,就是,在用户的主目录下会生成一个.mysql_history 的文件,这个文件会记录用户登录mysql后,在mysql中敲过的每条命令。
cat ~/.mysql_history 即可以看到以前的命令历史。
当正常输入用户密码时无法登陆?
可能性1:
可能和user表的plugin 字段为空有关。
1、首先将my.ini或my.cnf中加入在[mysqld]节点的配置上添加skip-grant-tables参数。
主要作用是:跳过表中的验证,可以无密码登陆,登陆后不能进行grant操作。
2、登录之后查询plugin字段值:
mysql> select plugin from user where user = ‘root’;
执行结果plugin字段为空。
3、更新plugin字段为mysql默认值:
mysql> update user set plugin=‘mysql_native_password’;
查询更新结果:
4、更新成功,继续执行更新密码操作:
mysql> update user set password=password(‘123456’) where user=‘root’ and host=‘localhost’;
5、刷新权限:
mysql> flush privileges;
6、将my.ini中的skip-grant-tables注释掉或者删掉
重启mysql,密码更新成功,亲测有效!
链接:https://www.cnblogs.com/sunshenggang/p/9400045.html
链接:https://blog.csdn.net/ynnmnm/article/details/45154725?utm_medium=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-3.nonecase&depth_1-utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-3.nonecase
链接:https://www.cnblogs.com/xing-ge/p/12815573.html
数据库命令练习:
一、
数据库和数据表单
1.创建数据库test_data,并显示数据库
>create database test_data;
>show databases;
2.将test_data数据库重命名为test_DATA;
#cd /data/mysqldb/
#mv test_data test_DATA
#mysqld -uroot -p
mariadb
>show databases;
3.在数据库test_DAT库中新建表,名为example(id int,name varchar(6),password char(16))
[(none)]>use test_DATA;
[test_DATA]> create TABLE example(id int,name varchar(6),password char(16));
[test_DATA]>show tables;
4.查看example表单的描述,并在example表中插入3条数据"1,zhangsan,password1","2,lisi,password2","wangwu,3,password3"
[test_DATA]>describe example;
[test_DATA]> insert into example(id,name,password) values('1','zhangsan','password1');
ERROR 1406 (22001): Data too long for column 'name' at row 1(报错,字符过长)
[test_DATA]> insert into example(id,name,password) values('1','zhang','password1');
[test_DATA]> insert into example(id,name,password) values('2','lisi','password2');
[test_DATA]> insert into example(id,name,password) values('3','wangwu','password3');
5.查看example表单的数据表单数据内容,查询example表中id>=2的数据;
[test_DATA]> select * from test_DATA.example;
或 [test_DATA]> select id,name,password from test_DATA.example;
[test_DATA]>select * from example where id>=2;
6.更改example表的数据类型,将name列的数据类型改为varchar(12),再次查看example表单描述是否已改?
[test_DATA]>alter table example modify name varchar(12);
[test_DATA]>describe example;
7.更新example表中的数据,将name列的zhang改为"zhangsan";
[test_DATA]>update example set name="zhangsan" where id="1";
[test_DATA]> select * from example;
8.删除example表单中的"password"列。
[test_DATA]> alter table example drop column password;
或:
[test_DATA]> alter table example drop password;
9.删除test_DATA.example表中id=2的行。
[test_DATA]> delete from example where id='2';
10.将example表单重命名为:example_bak;
[test_DATA]> alter table example rename example_bak
11.删除example数据表单;
[(none)]>drop table test_DATA.example;
12.删除test_DATA数据库;
[(none)]> drop database test_DATA;
用户管理:
1.创建用户test,密码为"test";
[test_DATA]> create user test@"localhost" identified by "test";
2.授予用户增,删,改,查的权限,对test_DATA.example表。
[test_DATA]> grant insert,drop,update,select on test_DATA.example to test@localhost;
3.收回test用户对于test_DATA表的增删改查权限,然后赋予全部权限;
[test_DATA]> revoke insert,drop,update,select on test_DATA.example from test@"localhost";
[test_DATA]> grant all privileges on test_DATA.example to test@"localhost";
4.收回收回test用户对于test_DATA表的所有权限,然后赋予全部权限给test_DATA库;
[(none)]> revoke all privileges on test_DATA.example from test@"localhost";
[(none)]> grant all privileges on test_DATA.* to test@"localhost";
5.删除test用户,并查看验证。
[(none)]> drop user test@"localhost";
[(none)]> select host,user from mysql.user;
6.创建用户test,密码为"test";然后修改test用户密码为"qqq"。
方法一:
[(none)]> update mysql.user set password=password("qqq") where user="tom";
[(none)]>flush privileges;
7.修改mysql数据库的root密码
方法一:
#mysqladmin -u root -p password abcdef(新密码) //只能改root的
请输入旧密码:
方法二:
[(none)]> update mysql.user set password=password("qqq") where user="root";
[(none)]>flush privileges;
权限:insert,drop,update,select,alter,delete,rename
二、
1. 创建数据库,要求是
a) 数据库名:cmsdb
b) 数据库字符集:GBK
c) 数据库引擎:InnoDB
mysql> create database cmsdb character set GBK;
mysql> set global engines=InnoDB;
2. 创建数据表,要求是
a) 数据表名:tb_emp
b) 字段为:id 主键、name 字符串长度25且不能为空、sex 字符串长度为6且不能为空、age 整数类型长度为4
mysql> use cmsdb;
mysql> create table tb_emp (id primary key,name varchar(25) not null,sex char(6) not null,age int(4));
3. 插入4条数据,要求是
a) id=1,name=zhangsan,sex=男,age=24
b) id=2,name=lisi,sex=女,age=18
c) id=3,name=wangwu,sex=女,age=16
d) id=4,name=zhaoliu,sex=男,age=18
mysql> insert into tb_emp (id,name,sex,age) values(1,’zhangsan’,’女’,24);
…后面改相应数据即可
4. 查询数据,要求是
a) 在cmsdb.tb_emp中查找年龄大于20的数据
b) 在cmsdb.tb_emp中查找性别为女的数据
c) 在cmsdb.tb_emp中查找age=18且sex为女
d) 查看tb_emp数据表的表结构
e) 查看cmsdb数据库的存储引擎
mysql> select * from cmsdb.tb_emp where age>20;
mysql> select * from cmsdb.tb_emp where sex=’女’;
mysql> select * from cmsdb.tb_emp where age=18 and sex=’女’;
mysql> dec tb_emp;
mysql> show engines like ‘cmsdb’;
5. 修改数据,要求是
a) 将id=1的那条数据中age修改为28
b) 将cmsdb.tb_emp中sex字段修改为gender,字符串长度修改为30
c) 将tb_emp数据表名修改为tb_emply
d) 在tb_emply数据表中添加一个字段etytime 数据类型为date
e) 将tb_emply的字符集修改为gbk2312
f) 将cmsdb数据库的字符集修改为utf8
mysql> update tb_emp set age=28 where id=1;
mysql> alter table tb_emp change sex gender varchar(30);
mysql> alter table tb_emp rename to tb_emply;
mysql> alter table tb_emply add etytime date;
mysql> alter table tb_emply character set gbk2312;
mysql> alter database cmsdb character set utf8;
6. 删除数据,要求是
a) 将cmsdb.tb_emply中age字段删除
b) 将tb_emply数据表删除
c) 将cmsdb数据库删除
mysql> alter table tb_emply drop age;
mysql> drop table tb_emply;
mysql> drop database cmsdb;
7. 创建用户,要求是
a) 使用create创建用户,用户名:cradmin,密码:123123
b) 使用grant创建用户,用户名:gradmin,密码:123123
mysql> create user “cradmin”@”localhost”identified by ‘123123’;
mysql> grant all on *.* to “gradmin”@”localhost”identified by ‘123123’;
8. 修改用户,要求是
a) 将cradmin用户名修改为cradmins
b) 将cradmins用户的密码修改为321321
mysql> rename user cradmin to cradmins;
mysq> set password for ‘cradmins’@’localhost’= password(‘321321’);
9. 查看用户权限,要求是
a) 查看cradmins权限范围
mysql> show grants for ‘cradmins’@’localhost’;
10. 管理用户授权,要求是
a) 赋予gradmin对所有库表的select、drop、alter权限
b) 移除gradmin对所有库表的drop权限
mysql> grant select,drop,alter on *.* to gradmin@localhost identified by ‘123123’;
mysql> revoke drop on *.* from gradmin