数据库的增删改查

显示当前所有的数据库：show databases;

mysql> show databases;执行结果：+--------------------+| Database           |+--------------------+| information_schema || db_001             || mysql              || performance_schema || sakila             || sys                || world              |+--------------------+7 rows in set (0.00 sec)

创建数据库：create database 库名 default character set utf8;

mysql> create database db_123 default character set utf8;执行结果：Query OK, 1 row affected (0.00 sec)

显示创建数据库的操作：show create database 库名;

mysql> show create database db_123;执行结果：+----------+-----------------------------------------------------------------+| Database | Create Database                                                 |+----------+-----------------------------------------------------------------+| db_123   | CREATE DATABASE `db_123` /*!40100 DEFAULT CHARACTER SET utf8 */ |+----------+-----------------------------------------------------------------+1 row in set (0.00 sec)

使用数据库：use 库名;

mysql> use db_123;执行结果：Database changed

创建表：create table 表名(字段名1 数据类型(长度),字段名2 数据类型(长度));

mysql> create table userinfo(id int(20),name varchar(20));执行结果：Query OK, 0 rows affected (0.02 sec)

显示当前所有表：show tables;

mysql> show tables;执行结果：+------------------+| Tables_in_db_123 |+------------------+| userinfo         |+------------------+1 row in set (0.00 sec)

查看表的描述：describe 表名;

mysql> describe userinfo;执行结果：+-------+-------------+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id    | int(20)     | YES  |     | NULL    |       || name  | varchar(20) | YES  |     | NULL    |       |+-------+-------------+------+-----+---------+-------+2 rows in set (0.00 sec)

表中插入数据：insert into 表名(字段1,字段2)values(值1,值2);

mysql> insert into userinfo(id,name)value(1,"路飞");执行结果：Query OK, 1 row affected (0.00 sec)

表中插入多条数据：insert into 表名(字段1,字段2)values(值1,值2),(值1,值2);

mysql> insert into userinfo(id,name)values(2,"索隆"),(3,"娜美");执行结果：Query OK, 2 rows affected (0.01 sec)Records: 2  Duplicates: 0  Warnings: 0

查看表的所有数据：select * from 表名;

mysql> select * from userinfo;执行结果：+------+------+| id   | name |+------+------+|    1 | 路飞 ||    2 | 索隆 ||    3 | 娜美 |+------+------+3 rows in set (0.00 sec)

查看表的单列数据：select 字段名1,字段名2 from 表名;

mysql> select name from userinfo;执行结果：+------+| name |+------+| 路飞 || 索隆 || 娜美 |+------+3 rows in set (0.00 sec)

更新表信息：update 表名 set 字段=值 where 字段=值；

mysql> update userinfo set name = "罗宾" where id = 3;执行结果：Query OK, 1 row affected (0.01 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from userinfo;执行结果：+------+------+| id   | name |+------+------+|    1 | 路飞 ||    2 | 索隆 ||    3 | 罗宾 |+------+------+3 rows in set (0.00 sec)

增加表的字段：alter table 表名 add 字段名字段类型(长度);

mysql> alter table userinfo add sex varchar(10);执行结果：Query OK, 0 rows affected (0.06 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> select * from userinfo;执行结果：+------+------+------+| id   | name | sex  |+------+------+------+|    1 | 路飞 | NULL ||    2 | 索隆 | NULL ||    3 | 罗宾 | NULL |+------+------+------+3 rows in set (0.00 sec)

修改表的字段：alter table 表名 change 旧字段名新字段名字段类型(长度);

mysql> alter table userinfo change sex age int(10);执行结果：Query OK, 3 rows affected (0.04 sec)Records: 3  Duplicates: 0  Warnings: 0mysql> select * from userinfo;执行结果：+------+------+------+| id   | name | age  |+------+------+------+|    1 | 路飞 | NULL ||    2 | 索隆 | NULL ||    3 | 罗宾 | NULL |+------+------+------+3 rows in set (0.00 sec)

删除表的字段：alter table 表名 drop 字段名;

mysql> alter table userinfo drop age;执行结果：Query OK, 0 rows affected (0.05 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> select * from userinfo;执行结果：+------+------+| id   | name |+------+------+|    1 | 路飞 ||    2 | 索隆 ||    3 | 罗宾 |+------+------+3 rows in set (0.00 sec)

删除表：drop table 表名;

mysql> drop table userinfo;执行结果：Query OK, 0 rows affected (0.01 sec)mysql> show tables;执行结果：Empty set (0.00 sec)

删除数据库：drop database 库名;

mysql> drop database db_123;执行结果：Query OK, 0 rows affected (0.00 sec)mysql> show databases;执行结果：+--------------------+| Database           |+--------------------+| information_schema || db_001             || mysql              || performance_schema || sakila             || sys                || world              |+--------------------+7 rows in set (0.00 sec)