关于数据库的操作命令

<mark>登录数据库</mark>

mysql -uroot -p

<mark>显示当前时间</mark>

select now();

<mark>登出(退出)数据库</mark>

quit/ exit/ ctrl+d

<mark>查看所有数据库</mark>

show databases;

<mark>创建数据库</mark>

create database test1 charset=utf8;

<mark>使用数据库</mark>

use test1;

<mark>查看当前使用的数据库</mark>

select database();

<mark>删除数据库—慎重</mark>

drop database test1;

<mark>查看创库语句</mark>

show create database test1;
结果:

mysql> show create database test1;
+----------+----------------------------------------------------------------+
| Database | Create Database                                                |
+----------+----------------------------------------------------------------+
| test1    | CREATE DATABASE `test1` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)

<mark>删除数据库</mark>

drop database test1;

关于表的操作命令

<mark>查看当前数据库中的所有表</mark>

show tables;

<mark>创建表</mark>

mysql> create table students(
-> id int unsigned primary key auto_increment not null,
-> name varchar(20) not null,
-> age tinyint unsigned default 18,
-> height decimal(5,2),
-> gender enum(‘男’,‘女’)
-> );
字段名称 数据类型 可选的约束条件

<mark>修改表-添加birthday字段</mark>

alter table students add birthday datetime;
这里没有指定not null 就是可以为空的哦

<mark>修改表-修改字段类型</mark>

alter table students modify birthday date not null;
这里我们赶紧birthday的类型datetime太长了,所以修改成date,顺便改成非空的not null

<mark>修改表-修改字段名和字段类型</mark>

alter table students change birthday birth datetime not null;
这里我们加上了一个功能。就是修改字段的名字,把birthday改成birth,就紧跟着后面就行了
十分注意。这里的字段类型还有not null都是要跟着的,否则都会变哦

<mark>修改表-删除birth字段</mark>

alter table students drop birth;
结果:

`----------------------------------------------------------------------------------------------------------+ | students | CREATE TABLE `students` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(20) NOT NULL, `age` tinyint(3) unsigned DEFAULT '18', `height` decimal(5,2) DEFAULT NULL, `gender` enum('男','女') DEFAULT NULL, `birth` date NOT NULL, PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8   |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

`

<mark>查看创建表的语句</mark>

show create table students;

<mark>删除表</mark>

drop table students;

关于表的数据的操作命令

<mark>查询数据</mark>

1.查询所有列
select * from students;

2.查询指定列
select id, name from students;

<mark>添加数据</mark>

1.全列插入:值的顺序和表结构的字段的顺序完全一一对应
insert into students values(‘0’, ‘vicky’, default, default, ‘女’,‘1996-07-07’);

2.部分列插入:值的顺寻与给出的列的顺序对应
insert into students(name, age, birth) values(‘JJ’, 23, ‘1996-02-25’);

3.全列多行插入
insert into students values(‘0’, ‘chuliuliu’, 23,160,‘女’, ‘1997-01-06’), (‘0’, ‘wangzijian’, 24, 180, ‘男’, ‘1995-02-25’);

4.部分列多行插入
insert into students(name, birth) values(‘zhuliyun’, ‘1994-12-08’), (‘liuruixi’, ‘1995-02-17’), (‘chenxiaolu’, ‘1988-05-24’);

<mark>修改数据</mark>