1、CMD打开执行命令：mysql -u root -p
输入数据库密码，进入数据库
PS C:Usersadmin> mysql -u root -p Enter password: **** Welcome to the MariaDB monitor. Commands end with ; or g. Your MariaDB connection id is 11 Server version: 10.4.7-MariaDB mariadb.org binary distribution Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
2、执行show databases; ,查看MariaDB的数据库结构。
MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | django | | information_schema | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.000 sec)
3、新建一个库，名字叫my。
MariaDB [test]> create database my;
Database changed
4、可以再次通过show databases查看新建的库
5、使用库：use my
6、新建一个表单：sql语句如下
MariaDB [my]> create table my.student( -> id int(4) primary key, -> name varchar(4) not null, -> age int(2) not null -> );
注：语句的结构如果错误，就会出现问题。错一点点都不行，反例如下：
MariaDB [my]> create table my.student( -> id int(4) primary key, -> name varchar(4) not null, -> age int(2) not null, #此处多了一个,导致了报错 -> ); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 5
7、查看表情况
MariaDB [my]> show tables; +--------------+ | Tables_in_my | +--------------+ | student | +--------------+ 1 row in set (0.000 sec)
8、查看表的字段信息
MariaDB [my]> desc student -> ; +-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | id | int(4) | NO | PRI | NULL | | | name | varchar(4) | NO | | NULL | | | age | int(2) | NO | | NULL | | +-------+------------+------+-----+---------+-------+ 3 rows in set (0.011 sec)
注：CMD内执行命令，如果结束了必须得带上分号 ;
9、 改变表的结构
eg1 新增字段address
MariaDB [my]> alter table student add address varchar(48); Query OK, 0 rows affected (0.007 sec) Records: 0 Duplicates: 0 Warnings: 0
eg2 新增字段gender
MariaDB [my]> alter table student add gender enum("boy","girl") after age; Query OK, 0 rows affected (0.008 sec) Records: 0 Duplicates: 0 Warnings: 0
语句必须要正确，不然一定会报错。如下：
MariaDB [my]> alert table student add address varchar(48); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'alert table student add address varchar(48)' at line 1
这里的错误是语句alter，这里打成了alert
10、表结构改变成功后查看结构
MariaDB [my]> desc student; +---------+--------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------------+------+-----+---------+-------+ | id | int(4) | NO | PRI | NULL | | | name | varchar(4) | NO | | NULL | | | age | int(2) | NO | | NULL | | | gender | enum('boy','girl') | YES | | NULL | | | address | varchar(48) | YES | | NULL | | +---------+--------------------+------+-----+---------+-------+ 5 rows in set (0.007 sec)
11、修改表字段
eg1 将gender修改为sex
MariaDB [my]> alter table student change gender sex enum("boy","girl") not null; Query OK, 0 rows affected (0.014 sec) Records: 0 Duplicates: 0 Warnings: 0
查看表结构信息
MariaDB [my]> desc student; +---------+--------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------------+------+-----+---------+-------+ | id | int(4) | NO | PRI | NULL | | | name | varchar(4) | NO | | NULL | | | age | int(2) | NO | | NULL | | | sex | enum('boy','girl') | NO | | NULL | | | address | varchar(48) | YES | | NULL | | +---------+--------------------+------+-----+---------+-------+ 5 rows in set (0.007 sec)