mysql 数据库必备命令操作

show databases； 查看所有的数据库；

create database jfedu； 创建名为jfedu数据库；

use nihao； 进入jfedu数据库；

show tables； 查看数据库里有多少张表；

create table t1 (id varchar(20),name varchar(20))； 创建名为t1表，并创建两个字段，id、name，varchar表示设置数据长度，用字符来定义长度单位，其中1汉字=2字符=2Bytes；

insert into t1 values ("1","jfedu")； 向表中插入数据；

select * from t1； 查看t1表数据内容；

Select * from t1 where id=1 and age =’jfedu’； id、age多个条件查询；

desc t1； 查看t1表字段内容；

alter table t1 modify column name varchar(20)； 修改name字段的长度；

update t1 set name='jfedu.net' where id=1； 修改name字段的内

创建表格

create table t1(id char(20),name char(20),age char(20),job varchar(30));创建表格

show tables;查看表

select id,name,age,job from t1;

select * from t1; 查看信息

insert into t1 values (001,'zhangsan',26,'IT');

insert into t1 values (002,'lisi',27,'IT'); 插入信息

MariaDB [nihao]> select * from t1 where name like "%wang%"; 查找带有wang的

MariaDB [nihao]> select * from t1 where name like "%wang%" and age=28;

、

删除 MariaDB [nihao]> delete from t1 where name='zhangsan';

MariaDB [nihao]> descrip t1;

MariaDB [nihao]> show create table t1; 查看建表格式

MariaDB [nihao]> show create table t1;+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| t1 | CREATE TABLE t1 ( id char(20) DEFAULT NULL, name char(20) DEFAULT NULL, age char(20) DEFAULT NULL, job varchar(30) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

修改信息 update t1 set name='zhangming' where id=1

update t1 set age=21,job='jr' where name='zhangming' ;

设置数据库远程权限 MariaDB [nihao]> grant all on . to test@192.168.91.129 identified by "123456";Query OK, 0 rows affected (0.00 sec)

修改密码;MariaDB [(none)]> use mysql

MariaDB [mysql]> descrip user;

MariaDB [mysql]> select host,user,password from user;+-----------------------+------+-------------------------------------------+| host | user | password |+-----------------------+------+-------------------------------------------+| localhost | root | || localhost.localdomain | root | || 127.0.0.1 | root | || ::1 | root | || localhost | | || localhost.localdomain | | || 192.168.91.129 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 || 192.168.91.128 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |

MariaDB [mysql]> delete from user where host="192.168.91.128" and user="root";Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> update user set password=password("654321") where user='root'and host='192.168.91.129