一、通过cmd链接到数据库
mysql -u root -p123456 #本地自建数据库
mysql -h ************ -uroot -p** #连接到服务器
或者
mysql -u root -p
弹出:
Enter password: ******
二、通过cmd创建与删除database
2.1create database [databasename]
create database mytest; #创建mytest ,如果mytest 已经存在,再次创建会报错
create database mytest;
ERROR 1007 (HY000): Can’t create database ‘mytest’; database exists
mysqladmin -u root -p drop mytest; #删除
加上IF NOT EXISTS就算数据库已经存在,把原来的覆盖掉了
mysql> create database if not exists mytest;
Query OK, 1 row affected, 1 warning (0.00 sec)
2.2、创建数据库,charset=utf8mb4;指定最新的字符集类型
create database my2 charset=utf8mb4;
三、查看所有的数据库
3.1数据库明细
show databases;
3.2查看数据库的格式
show create database my2;
3.3选择所要操作的数据库
use mytest;
Database changed
四、列表操作
4.1列表创建,如果某列表已经存在,则不会重新创建;
CREATE TABLE table_name (column_name column_type);
可以添加条件语句进行判断是否存在XXX列表
create table user2(
id int not null key auto_increment,
symbol char(10) not null,
name varchar(30) not null,
type tinyint(3) not null,
buy_date date not null,
sell_date date not null,
strategy int(10) not null,
hot varchar(100) not null,
hot_rel double not null,
result tinyint(3) not null,
return_rate decimal(10),
max_return_rate decimal(10),
status tinyint(3))engine = innodb charset = utf8;
对于一些较长的语句在命令提示符下可能容易输错, 因此我们可以通过任何文本编辑器将语句输入好后保存为 createtable.sql 的文件中, 通过命令提示符下的文件重定向执行执行该脚本。
打开命令提示符, 输入:
mysql -D samp_db -u root -p < createtable.sql
(提示: 1.如果连接远程主机请加上 -h 指令; 2. createtable.sql 文件若不在当前工作目录下需指定文件的完整路径。)
4.2查看mytest数据库有多少数据库表
mysql> SHOW TABLES;
+------------------+
| Tables_in_mytest |
+------------------+
| user2 |
+------------------+
1 row in set (0.00 sec)
4.3查看表user2的结构
mysql> desc user2;
+-----------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| symbol | char(10) | NO | | NULL | |
| name | varchar(30) | NO | | NULL | |
| type | tinyint(3) | NO | | NULL | |
| buy_date | date | NO | | NULL | |
| sell_date | date | NO | | NULL | |
| strategy | int(10) | NO | | NULL | |
| hot | varchar(100) | NO | | NULL | |
| hot_rel | double | NO | | NULL | |
| result | tinyint(3) | NO | | NULL | |
| return_rate | decimal(10,0) | YES | | NULL | |
| max_return_rate | decimal(10,0) | YES | | NULL | |
| status | tinyint(3) | YES | | NULL | |
+-----------------+---------------+------+-----+---------+----------------+
13 rows in set (0.01 sec)
4.4查看表的DDL
show create table user2;
4.5删除数据库表的一个字段,删除id字段
mysql> ALTER TABLE user2 DROP COLUMN id;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user2;
+-----------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------------+------+-----+---------+-------+
| symbol | char(10) | NO | | NULL | |
| name | varchar(30) | NO | | NULL | |
| type | tinyint(3) | NO | | NULL | |
| buy_date | date | NO | | NULL | |
| sell_date | date | NO | | NULL | |
| strategy | int(10) | NO | | NULL | |
| hot | varchar(100) | NO | | NULL | |
| hot_rel | double | NO | | NULL | |
| result | tinyint(3) | NO | | NULL | |
| return_rate | decimal(10,0) | YES | | NULL | |
| max_return_rate | decimal(10,0) | YES | | NULL | |
| status | tinyint(3) | YES | | NULL | |
+-----------------+---------------+------+-----+---------+-------+
12 rows in set (0.00 sec)
4.6删除整个user2表
mysql> drop table user2;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
Empty set (0.00 sec)
4.7给数据库表user2添加一个love字段
FIRST说明,把该字段设为数据库表的第一个字段
mysql> ALTER TABLE user2 ADD love VARCHAR(20) NOT NULL FIRST;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user2;
+-----------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------------+------+-----+---------+----------------+
| love | varchar(20) | NO | | NULL | |
| id | int(11) | NO | PRI | NULL | auto_increment |
| symbol | char(10) | NO | | NULL | |
| name | varchar(30) | NO | | NULL | |
| type | tinyint(3) | NO | | NULL | |
| buy_date | date | NO | | NULL | |
| sell_date | date | NO | | NULL | |
| strategy | int(10) | NO | | NULL | |
| hot | varchar(100) | NO | | NULL | |
| hot_rel | double | NO | | NULL | |
| result | tinyint(3) | NO | | NULL | |
| return_rate | decimal(10,0) | YES | | NULL | |
| max_return_rate | decimal(10,0) | YES | | NULL | |
| status | tinyint(3) | YES | | NULL | |
+-----------------+---------------+------+-----+---------+----------------+
14 rows in set (0.00 sec)
AFTER说明,把该字段放到id字段后面
mysql> ALTER TABLE user2 ADD hate VARCHAR(20) NOT NULL after id;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user2
-> ;
+-----------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------------+------+-----+---------+----------------+
| love | varchar(20) | NO | | NULL | |
| id | int(11) | NO | PRI | NULL | auto_increment |
| hate | varchar(20) | NO | | NULL | |
| symbol | char(10) | NO | | NULL | |
| name | varchar(30) | NO | | NULL | |
| type | tinyint(3) | NO | | NULL | |
| buy_date | date | NO | | NULL | |
| sell_date | date | NO | | NULL | |
| strategy | int(10) | NO | | NULL | |
| hot | varchar(100) | NO | | NULL | |
| hot_rel | double | NO | | NULL | |
| result | tinyint(3) | NO | | NULL | |
| return_rate | decimal(10,0) | YES | | NULL | |
| max_return_rate | decimal(10,0) | YES | | NULL | |
| status | tinyint(3) | YES | | NULL | |
+-----------------+---------------+------+-----+---------+----------------+
15 rows in set (0.00 sec)
4.8把数据库表user2中hate字段名字修改为mathhate,位置放在name后面。
mysql> ALTER TABLE user2 CHANGE hate mathhate TINYINT UNSIGNED NOT NULL AFTER name;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user2;
+-----------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------+----------------+
| love | varchar(20) | NO | | NULL | |
| id | int(11) | NO | PRI | NULL | auto_increment |
| symbol | char(10) | NO | | NULL | |
| name | varchar(30) | NO | | NULL | |
| mathhate | tinyint(3) unsigned | NO | | NULL | |
| type | tinyint(3) | NO | | NULL | |
| buy_date | date | NO | | NULL | |
| sell_date | date | NO | | NULL | |
| strategy | int(10) | NO | | NULL | |
| hot | varchar(100) | NO | | NULL | |
| hot_rel | double | NO | | NULL | |
| result | tinyint(3) | NO | | NULL | |
| return_rate | decimal(10,0) | YES | | NULL | |
| max_return_rate | decimal(10,0) | YES | | NULL | |
| status | tinyint(3) | YES | | NULL | |
+-----------------+---------------------+------+-----+---------+----------------+
15 rows in set (0.00 sec)
4.9在数据库表user2建好之后,修改唯一键
mysql> ALTER TABLE user2 ADD UNIQUE (id);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
4.10把id字段的唯一键性质取消,使用INDEX
mysql> ALTER TABLE user2 DROP INDEX id;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
4.11重命名表名
rename table user2 to usess;
alter table usess rename (as/to) user;