数据库的管理

1.数据库的对象

1.数据库系统  DBMS

2.数据库  DB

3.表  table

4.记录  record

5.字段  feild

1.数据库操作

删除库

mysql> drop database test;

Query OK, 0 rows affected (0.01 sec)

mysql> show databases;

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| sys |

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

4 rows in set (0.00 sec)

创建库

mysql> create datababse test charset utf8;

Query OK, 1 row affected, 1 warning (0.01 sec) mysql> show databases;

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| sys |

| test |

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

5 rows in set (0.00 sec)

查看库

mysql> show databases;

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| sys |

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

4 rows in set (0.01 sec)

2.表的操作

   1.选择库

mysql> use mysql;

Reading table information for completion of table and

column names

You can turn off this feature to get a quicker startup

with -A

Database changed

2.查看表

mysql> show tables;

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

| Tables_in_mysql |

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

| columns_priv |

| component |

| db |

| default_roles |

| engine_cost |

| func |

| general_log |

| global_grants |

| gtid_executed |

| help_category |

| help_keyword |

| help_relation |

| help_topic |

| innodb_index_stats |

| innodb_table_stats |

| ndb_binlog_index |

| password_history |

| plugin |

| procs_priv || proxies_priv |

| replication_asynchronous_connection_failover |

| replication_asynchronous_connection_failover_managed |

| replication_group_configuration_version |

| replication_group_member_actions |

| role_edges |

| server_cost |

| servers |

| slave_master_info |

| slave_relay_log_info |

| slave_worker_info |

| slow_log |

| tables_priv |

| time_zone |

| time_zone_leap_second |

| time_zone_name |

| time_zone_transition |

| time_zone_transition_type |

| user |

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

38 rows in set (0.01 sec)

3.创建表

mysql> CREATE TABLE `test`.`user` (

-> `id` INT NOT NULL AUTO_INCREMENT,

-> `username` VARCHAR(45) NOT NULL,

-> `password` VARCHAR(45) NOT NULL,

-> PRIMARY KEY (`id`),

-> UNIQUE INDEX `username_UNIQUE` (`username` ASC)

VISIBLE)

-> COMMENT = 'us';

mysql> show tables;

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

| Tables_in_test |

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

| user |

| user0 |

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

4.删除表

mysql> show tables;

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

| Tables_in_test |

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

| user |

| user0 |

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

2 rows in set (0.00 sec)

mysql> drop table user0;

Query OK, 0 rows affected (0.01 sec)

mysql> show tables;

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

| Tables_in_test |

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

| user |

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

1 row in set (0.01 sec)

新增列

ALTER TABLE `test`.`user`

ADD COLUMN `realname` VARCHAR(45) NULL AFTER `password`;

mysql> desc user;

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

-------+

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

|

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

-------+

| id | int | NO | PRI | NULL |

auto_increment |

| username | varchar(45) | NO | UNI | NULL |

| password | varchar(45) | NO | | NULL |

|

| realname | varchar(45) | YES | | NULL |

|

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

-------+

4 rows in set (0.01 sec)

修改列名

mysql> ALTER TABLE `test`.`user`

-> CHANGE COLUMN `realname` `zsxm` VARCHAR(45) NULL

DEFAULT NULL ;

Query OK, 0 rows affected (0.02 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc user;

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

-------+

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

|

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

-------+

| id | int | NO | PRI | NULL |

auto_increment |

| username | varchar(45) | NO | UNI | NULL |

|

| password | varchar(45) | NO | | NULL |

|

| zsxm | varchar(45) | YES | | NULL |

|

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

-------+

删除列

mysql> ALTER TABLE `test`.`user`

-> DROP COLUMN `zsxm`;

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc user;

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

-------+

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

|

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

-------+

| id | int | NO | PRI | NULL |

auto_increment |

| username | varchar(45) | NO | UNI | NULL |

|

| password | varchar(45) | NO | | NULL |

|

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

-------+

3 rows in set (0.00 sec)

         5.其他

3.记录的操作

  1. 新增

#insert into 表名 (字段名列表) values (字段值列表)

mysql> select * from user;

Empty set (0.00 sec)

mysql> insert into user (id,username,password) values

(3,'zhangsan','zhangsan');Query OK, 1 row affected (0.01 sec)

mysql> select * from user;

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

| id | username | password |

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

| 3 | zhangsan | zhangsan |

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

1 row in set (0.00 sec)

mysql> select * from user;

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

| id | username | password |

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

| 3 | zhangsan | zhangsan |

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

1 row in set (0.00 sec)

mysql> insert into

user(username,password)values('lisi','lisi');

Query OK, 1 row affected (0.00 sec)

mysql> select * from user;

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

| id | username | password |

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

| 3 | zhangsan | zhangsan |

| 4 | lisi | lisi |

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

2 rows in set (0.00 sec)

mysql> desc user;

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

----------+

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

|+----------+-------------+------+-----+---------+------

----------+

| id | int | NO | PRI | NULL |

auto_increment |

| username | varchar(45) | NO | UNI | NULL |

|

| password | varchar(45) | NO | | NULL |

|

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

----------+

3 rows in set (0.00 sec)

2. 删除

delte from user where 条件;

mysql> delete from user where username='zhangsan';

Query OK, 1 row affected (0.00 sec)

mysql> select * from user;

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

| id | username | password |

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

| 4 | lisi | lisi |

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

1 row in set (0.00 sec)

mysql> delete from user;

Query OK, 1 row affected (0.00 sec)mysql> select * from user;

Empty set (0.00 sec)

3. 修改

mysql>

mysql> insert into user values

(1,'zhangsan','zhangsan');

Query OK, 1 row affected (0.00 sec)

mysql> insert into user values (2,'lisi','lisi');

Query OK, 1 row affected (0.00 sec)

mysql> insert into user values (3,'wangwu','wangwu');

Query OK, 1 row affected (0.00 sec)

mysql> select * from user;

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

| id | username | password |

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

| 1 | zhangsan | zhangsan |

| 2 | lisi | lisi |

| 3 | wangwu | wangwu |

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

3 rows in set (0.00 sec)

mysql> update user set password=123 where

username='zhangsan';

Query OK, 1 row affected (0.01 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from user;

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

| id | username | password |

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

| 1 | zhangsan | 123 || 2 | lisi | lisi |

| 3 | wangwu | wangwu |

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

3 rows in set (0.00 sec)

4. 查询