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
![](https://i-blog.csdnimg.cn/direct/931e1daec12548cca7cc797aef54e8bd.png)
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)
![](https://i-blog.csdnimg.cn/direct/e417b1dd65aa41e8b25ddc528764c760.png)
3.创建表
![](https://i-blog.csdnimg.cn/direct/f696a967dff74dfe82e35771b8d26b1b.png)
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. 查询