[大数据]mysql数据库操作

显示数据位数32还是64：

mysql -V / mysql --version

登录数据库：

mysql -u root -p

输入登录密码：

创建数据库：

create DATABASE RUNOOB;

例子：

mysql> create database tmysql;

Query OK, 1 row affected (0.02 sec)

显示所有数据库：

show databases;

例子：

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| runoob |

| sakila |

| sys |

| tmysql |

| world |

删除数据库：

drop database RUNOOB;

例子：

mysql> drop database tmysql;

Query OK, 1 row affected (0.09 sec)

使用数据库：

use RUNOOB;

例子：

mysql> use tmysql;

Database changed

显示当前使用数据；

select database();

创建表

mysql> create table student(

-> stuid INT NOT NULL,

-> stuname VARCHAR(20) NOT NULL,

-> stusex VARCHAR(4),

-> PRIMARY KEY(stuid)) ENGINE=InnoDB DEFAULT CHARSET = utf8;

Query OK, 0 rows affected, 1 warning (0.06 sec)

显示所有表

show tables;

例如：

mysql> show tables;

+------------------+

| Tables_in_tmysql |

+------------------+

| student |

+------------------+

1 row in set (0.00 sec)

显示表的详情：

mysql> desc student;

+---------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+---------+-------------+------+-----+---------+-------+

| stuid | int | NO | PRI | NULL | |

| stuname | varchar(20) | NO | | NULL | |

| stusex | varchar(4) | YES | | NULL | |

+---------+-------------+------+-----+---------+-------+

显示表的创建sql语句：

mysql> show create table student;

+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table |

+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| student | CREATE TABLE `student` (

`stuid` int NOT NULL,

`stuname` varchar(20) NOT NULL,

`stusex` varchar(4) DEFAULT NULL,

PRIMARY KEY (`stuid`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 |

+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

修改表名：

mysql> alter table student rename student1;

Query OK, 0 rows affected (0.02 sec)

mysql> show tables;

+------------------+

| Tables_in_tmysql |

+------------------+

| student1 |

+------------------+

1 row in set (0.02 sec)

更改表的属性：

mysql> alter table student modify stusex varchar(6);

Query OK, 0 rows affected (0.03 sec)

Records: 0 Duplicates: 0 Warnings: 0

修改表的属性名：

mysql> alter table student change stuid stuId int;

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc student;

+---------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+---------+-------------+------+-----+---------+-------+

| stuId | int | NO | PRI | NULL | |

| stuname | varchar(20) | NO | | NULL | |

| stusex | varchar(6) | YES | | NULL | |

+---------+-------------+------+-----+---------+-------+

添加表的属性：

mysql> alter table student add studept varchar(10);

Query OK, 0 rows affected (0.02 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc student;

+---------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+---------+-------------+------+-----+---------+-------+

| stuId | int | NO | PRI | NULL | |

| stuname | varchar(20) | NO | | NULL | |

| stusex | varchar(6) | YES | | NULL | |

| studept | varchar(10) | YES | | NULL | |

+---------+-------------+------+-----+---------+-------+

删除表的属性：

mysql> desc student;

+---------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+---------+-------------+------+-----+---------+-------+

| stuid | int | NO | PRI | NULL | |

| stuname | varchar(20) | NO | | NULL | |

| stusex | varchar(6) | YES | | NULL | |

| studept | varchar(10) | YES | | NULL | |

+---------+-------------+------+-----+---------+-------+

4 rows in set (0.02 sec)

mysql> alter table student drop studept;

Query OK, 0 rows affected (0.10 sec)

Records: 0 Duplicates: 0 Warnings: 0

删除表：

drop table student;

例子：

mysql> drop table student;

Query OK, 0 rows affected (0.03 sec)

插入数据:

mysql> INSERT INTO stu

-> (stuid ,stdname)

-> VALUES

-> (20213,'小王');

Query OK, 1 row affected (0.00 sec)

mysql> insert into stu (stuid, stdname) values (20212,'小刘');

Query OK, 1 row affected (0.01 sec)

查询表：

mysql> SELECT * FROM stu;

+-------+---------+

| stuid | stdname |

+-------+---------+

| 20211 | 小明 |

| 20212 | 小刘 |

| 20213 | 小王 |

+-------+---------+

3 rows in set (0.00 sec)

mysql> select * from stu where stuid = 20211;

+-------+---------+

| stuid | stdname |

+-------+---------+

| 20211 | 小明 |

+-------+---------+

1 row in set (0.00 sec)

mysql> select * from stu where stuid between 20211 and 20213;

+-------+---------+

| stuid | stdname |

+-------+---------+

| 20211 | 小明 |

| 20212 | 小刘 |

| 20213 | 小王 |

+-------+---------+

3 rows in set (0.01 sec)

修改数据表项：

mysql> update stu set stdname = '小赵' where stuid = 20211;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from stu;

+-------+---------+

| stuid | stdname |

+-------+---------+

| 20211 | 小赵 |

| 20212 | 小刘 |

| 20213 | 小王 |

+-------+---------+

3 rows in set (0.00 sec)

删除数据表项：

mysql> delete from stu where stuid = 20211;

Query OK, 1 row affected (0.01 sec)

mysql> select * from stu;

+-------+---------+

| stuid | stdname |

+-------+---------+

| 20212 | 小刘 |

| 20213 | 小王 |

+-------+---------+

2 rows in set (0.00 sec)