使用命令行方式操作Mysql
连接数据库
mysql -u账号 -p密码 -h主机地址 -P端口
mysql -uroot -pmysql
查看数据库版本
select version();
显示当前时间
select now();
查看所有数据库：
show databases;
创建数据库
create database 数据库名 charset=utf8;
注意：创建库的时候一定要指定编码utf8，utf8中间没有-，跟pyhton中写编码有点区别
mysql> create database mydjango charset=utf8;
Query OK, 1 row affected (0.01 sec)
mysql>
切换数据库：
use 数据库名
查看当前正在使用哪个数据库
select database();
删除数据库
drop database 数据库名;
mysql> drop database mydjango;
Query OK, 0 rows affected (0.03 sec)
mysql>
数据表操作
查看当前数据库中的表
show tables;
创建表
create table 表名(
id int unsigned auto_increment primary key not null,
name varchar(10) not null,
is_delete bit(1) not null default 0
);
建表主要是前面是字段，字段后面跟的是约束条件。
创建学生表
create table students(
id int auto_increment primary key not null,
name varchar(10) not null,
gender bit(1) default 0,
hometown varchar(40) default ""
)
comment 注释，在创建表的时候如果字段很多，防止忘记字段是存什么数据的，可以给字段添加注释
create table students(
id int auto_increment primary key not null comment '主键',
name varchar(10) not null comment '学生姓名',
gender bit(1) default 0 comment '性别',
hometown varchar(40) default "" comment '家乡地址'
)
查看创建表的sql语句
show create table 表名;
mysql> create table students(
-> id int auto_increment primary key not null,
-> name varchar(10) not null,
-> gender bit(1) default 0,
-> hometown varchar(40) default ""
-> );
Query OK, 0 rows affected (0.07 sec)
mysql> show create table students;
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| students | CREATE TABLE `students` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) NOT NULL,
`gender` bit(1) DEFAULT b'0',
`hometown` varchar(40) DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
mysql>
添加字段
alter table 表名 add 列名 类型;
给students添加一个生日字段
alter table students add birthday date;
mysql> show create table students;
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| students | CREATE TABLE `students` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) NOT NULL,
`gender` bit(1) DEFAULT b'0',
`hometown` varchar(40) DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
mysql> alter table students add birthday date;
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table students;
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| students | CREATE TABLE `students` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) NOT NULL,
`gender` bit(1) DEFAULT b'0',
`hometown` varchar(40) DEFAULT '',
`birthday` date DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
修改字段
第一种，不修改字段名只修改类型及约束
alter table 表名 modify 列名 类型及约束;
mysql> show create table students;
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| students | CREATE TABLE `students` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) NOT NULL,
`gender` bit(1) DEFAULT b'0',
`hometown` varchar(40) DEFAULT '',
`birthday` date DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql>
mysql>
mysql>
mysql> alter table students modify hometown varchar(40) default NUll;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table students;
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| students | CREATE TABLE `students` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) NOT NULL,
`gender` bit(1) DEFAULT b'0',
`hometown` varchar(40) DEFAULT NULL,
`birthday` date DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
第二种，需要修改字段名字
alter table 表名 change 原名 新名 类型及约束;
将class表的is_delete字段修改为delete
alter table class change is_delete delete bit(1) NOT NULL DEFAULT b'0';
删除字段
alter table 表名 drop 字段名字;
将students表中的gender字段删除
alter table students drop gender;
mysql> show create table students;
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| students | CREATE TABLE `students` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) NOT NULL,
`gender` bit(1) DEFAULT b'0',
`hometown` varchar(40) DEFAULT NULL,
`birthday` date DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> alter table students drop gender;
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table students;
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| students | CREATE TABLE `students` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) NOT NULL,
`hometown` varchar(40) DEFAULT NULL,
`birthday` date DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
删除表：
drop table 表名；
删除学生表
drop table students;
mysql> drop table students;
Query OK, 0 rows affected (0.02 sec)
mysql>