数据库的登录操作:
mysql -u root -p
-u 那个用户
root 用户
-p 密码
####数据库的操作:
1. 数据库的查看:
show databases;
2. 数据库的切换:
use database;
3. 创建数据库:
create database 数据库名称;
create database test01;
4. 删除数据库:
drop database 数据库名称;
drop database test01;
5.查看当前数据的名称:
select database();
####数据库中表的操作:
1. 创建表:
create table 表名(
字段1 数据类型1(数据限制),
字段2 数据类型2(数据限制)
);
create table stu(
name varchar2(20) not null,
age int
);
- 查看表详情:
desc 表;
desc stu;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(10) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
####修改表的操作:
1. 添加字段:
alter table stu add 字段名称 数据类型;
alter table stu add id int;
2. 修改字段:
alter table stu change 旧字段 新字段 数据类型;
alter table stu change id stu_id int;
3. 删除字段:
alter table stu drop 字段;
alter table stu drop id;
4. 修改字段数据类型:
alter table stu modify 字段 varchar(20);
####数据的操作:
1. 插入数据:
insert into 表名(字段1,字段2…) values(值1,值2);
insert into stu (name,age) values(‘aa’,14);
2. 修改数据:
update 表名 set 字段1 = 值1,字段2 = 值2 where(条件);
update stu set name = '马云',age = '48' where name = 'aa';
3. 删除数据:
delete from 表名 where(条件);
delete from stu where name = 'bb';
####用户权限的操作:
1. 创建用户:
create user 用户名@localhost identified by '密码';
create user test@localhost identified by 'test';
2.用户授权:
grant 权限1,权限2 ... 数据库.* to 用户名;
grant select,insert on test01. to test@localhost;
mysql> show databases; 没有授权
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
给授权:
root 用户给授权:
grant select,insert on test01.* to test@localhost;
test 用户再次查看的效果:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| test01 |
+--------------------+
3. 查看用户的权限:
show grants for 用户名;
show grants for test@localhost;
```
| Grants for test@localhost |
+------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'localhost' |
| GRANT SELECT, INSERT, DELETE ON `test01`.* TO 'test'@'localhost' |
+--------------------------------------------------------
```
- 删除用户:
drop user test;
drop user test@localhost; - 查看有哪些用户:
进入到mysql 数据库:
use mysql;
查询用户表;
select host,user from user;