命令行操作MYSQL
连接mysql:
mysql -u root -p
查看所有数据库:
mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
修改mysql root密码:
alter user 'root'@'localhost' identified by 'root';
查看所有数据库:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| nbtester |
| performance_schema |
| sonar |
| springdemo |
| sys |
+--------------------+
8 rows in set (0.00 sec)
连接数据库
mysql> use mydb;
查看当前数据库包含的表信息:
mysql> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| users |
+----------------+
1 row in set (0.00 sec)```
查询表中所有数据:
mysql> select * from users;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | ddcd | 18 |
+----+--------+------+
1 row in set (0.00 sec)
给表中增加字段:
alter table mydb.users add city varchar(20) default '';
获取表结构:
mysql> desc users;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(45) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| city | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
创建数据库:
命令:create database <数据库名>
例如:建立一个名为xhkdb的数据库
mysql> create database xhkdb;
查看当前使用的数据库:
mysql> select database();
建表:
命令:create table <表名> ( <字段名> <类型> [,..<字段名n> <类型n>]);
mysql> create table MyClass(
> id int(4) not null primary key auto_increment,
> name char(20) not null,
> sex int(4) not null default ’′,
> degree double(16,2));
> ```
删除表中数据
命令:delete from 表名 where 表达式
例如:删除表 MyClass中编号为 的记录
mysql> delete from MyClass where id=1;
修改表中数据:
update 表名 set 字段=新值,…where 条件
mysql> update MyClass set name=’Mary’where id=1;