服务器连接数据库
登录数据库:MYSQL -h host-name -u user -p password
-h:当连接mysql服务器不在同台主机时,填写主机名或IP,连接本机可以省略-h
-u:登录mysql的用户名
-p:登录mysql密码
连接服务器中本地数据库并进入stus库
[root@io ~]# mysql -uroot -proot123 stus;
mysql: [Warning] Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5344
Server version: 5.7.21-log MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
常用命令
进入数据库:use stus;
mysql> use stus;
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
mysql>
显示所有数据库:show databases;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| from_info |
| students |
| stus |
| mysql |
| sys |
| test_info |
+--------------------+
7 rows in set (0.01 sec)
mysql>
显示默认数据库中所有表:show tables;
mysql> show tables;
+------------------------------+
| Tables_in_flyos |
+------------------------------+
| asssets |
| name_log_his |
| assets_info |
| student_info |
| update |
| update_his |
+------------------------------+
76 rows in set (0.00 sec)
mysql>
退出mysql服务器:;q或quit;
mysql> quit
Bye
[root@flyos ~]#
查看表结构 :desc 表名;
mysql> desc stus;
+-------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| id_no | varchar(20) | YES | | | | |
| name | varchar(50) | NO | | NULL | |
|create_time | bigint(20) | YES | | NULL | |
| update_time | bigint(20) | YES | | NULL | |
| update_ping | varcha(4) | YES | | 0 | |
+-------------+---------------+------+-----+---------+----------------+
11 rows in set (0.01 sec)
添加字段:alter table 表名 add 增加字段名 增加字段类型(varchar(50))not null;
mysql> alter table stus add sex varchar(50)not null;
修改字段类型:alter table 表名 modify column 字段 char(50)(字段类型);
mysql> alter table stus modify column name char(50);
修改字段名:alter table 表名 change column 修改前 修改后 varchar(30) null default null;
mysql> alter table stus change column name name_id varchar(30) null default null;
删除字段:alter table 表名 drop 字段;
mysql> alter table stus drop name;
修改表名:alter table 原表名rename as新表名;
mysql> alter table stusc as stuse;
删除表:drop table 表名;
mysql> drop table stus;
插入一条完整数据:insert into 表名 values (‘xx’,’xxx’,’as’,’ad’);
mysql> insert into stus values (‘12’,’zhangsan’,’as’,’ad’);
插入部分数据:insert into 表名(字段1,字段2,字段3) values(‘xx’,’xxx’,’as’,’ad’);
mysql> insert into stus(id,name) values(‘2’,’zhangliu’);
插入多条数据:insert into 表名(字段1,字段2,字段3) values(‘xx’,’xxx’,’as’,’ad’),(‘xx’,’xxx’,’as’,’ad’);
mysql> insert into stus(id,name) values(‘2’,’zhangliu’),(‘3’,’zhasi’);
用insert…set插入数据:insert into 表名 set 字段1=’xx’,字段2=’xxx’;
mysql> insert into stus set id=’15’,name=’lisi’;
查看数据:select * from 表名;
mysql> select * from stus;
+------+----------+
| id | name |
+------+----------+
| 1623 | lisi |
+------+----------+
4 rows in set (0.01 sec)
修改字段数据:update 表名set 字段修改后=’xx’ where 字段修改前=’xxx’;
mysql> update stus set name=’yangming’ where name=’lisi’;
+------+----------+
| id | name |
+------+----------+
| 1623 | yangming |
+------+----------+
4 rows in set (0.01 sec)
修改全部数据(字段内容都一样):update 表名 set 字段=’xx’;
mysql> update stus set name=’yangming’;
+------+----------+
| id | name |
+------+----------+
| 1623 | yangming |
+------+----------+
4 rows in set (0.01 sec)
删除表中部分数据:delete from 表名where 字段名=’xx’;
mysql> delete from stus where name=’yangming’;
+------+----------+
| id | name |
+------+----------+
4 rows in set (0.01 sec)
删除全部数据:delete from 表名;
mysql> delete from stus;
读完本文有收获吗?请转发分享更多人
软件测试分享圈