mysql学习笔记-基本语法-表结构创建

一、数据库连接

• 数据库的连接

命令行工具 mysql [-h 127.0.0.1][-p3306] -uroot -p xxxx

二、DLL语法

C:/Users>mysql -uroot -p xxxx

mysql> # 查询所有数据库
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| itcast             |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| world              |
+--------------------+
7 rows in set (0.00 sec)

mysql> # 查询当前数据库
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| NULL       |
+------------+
1 row in set (0.00 sec)

mysql> # 创建数据库
mysql> CREATE DATABASE if not exists test default charset utf8;
Query OK, 1 row affected, 1 warning (0.02 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| itcast             |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| test               |
| world              |
+--------------------+
8 rows in set (0.00 sec)

mysql> # 删除数据库,if exists 如果存在
mysql> drop database if exists test;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| itcast             |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| world              |
+--------------------+
7 rows in set (0.00 sec)

mysql> # 切换使用数据库use
mysql> use itcast;
Database changed
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| itcast     |
+------------+
1 row in set (0.00 sec)

# DDL 表创建

CREATE TABLE tb_uset(
	id int comment '编号',
	name varchar(50) comment '姓名',
	age int comment'年龄',
	gender varchar(1) comment '性别'
)comment '用户表';

mysql> # 查询当前数据库所有表
mysql> SHOW TABLES;
+------------------+
| Tables_in_itcast |
+------------------+
| tb_uset          |
+------------------+
1 row in set (0.00 sec)

mysql> # 查询表结构
mysql> DESC tb_uset;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int         | YES  |     | NULL    |       |
| name   | varchar(50) | YES  |     | NULL    |       |
| age    | int         | YES  |     | NULL    |       |
| gender | varchar(1)  | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> # 查询指定表的建表语句
mysql> SHOW CREATE TABLE tb_uset;
+---------+------------------------------------------------------+
| Table   | Create Table      |
+---------+------------------------------------------------------+
| tb_uset | CREATE TABLE `tb_uset` (
  `id` int DEFAULT NULL COMMENT '编号',
  `name` varchar(50) DEFAULT NULL COMMENT '姓名',
  `age` int DEFAULT NULL COMMENT '年龄',
  `gender` varchar(1) DEFAULT NULL COMMENT '性别'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户表' |
+---------+------------------------------------------------------+
1 row in set (0.00 sec)