mysql基本语句（创建删除数据库表、插入数据，创建删除用户）

本文主要是介绍mysql基本语句（创建删除数据库表、插入数据，创建删除用户），对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

1、创建、查看数据库

mysql> create database if not exists kings default charset utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.01 sec)

mysql>

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |（存储mysql的内置信息）
| kings              |
| lol                |
| lol2               |
| mysql              |（存储mysql的用户信息，授权等）
| performance_schema |
| test               |（mysql存储性能相关的数据）
+--------------------+
7 rows in set (0.00 sec)

mysql>

mysql> show create database kings\G（查看创建数据库的SQL语句，-G是结果以列表展示）
*************************** 1. row ***************************
       Database: kings
Create Database: CREATE DATABASE `kings` /*!40100 DEFAULT CHARACTER SET utf8 */
1 row in set (0.00 sec)

mysql>

mysql> show variables like 'char%';（查看类似char字符的变量）
+--------------------------+------------------------------------------------------------------+
| Variable_name            | Value                                                            |
+--------------------------+------------------------------------------------------------------+
| character_set_client     | utf8                                                             |
| character_set_connection | utf8                                                             |
| character_set_database   | latin1                                                           |
| character_set_filesystem | binary                                                           |
| character_set_results    | utf8                                                             |
| character_set_server     | latin1                                                           |
| character_set_system     | utf8                                                             |
| character_sets_dir       | /application/mysql-5.6.40-linux-glibc2.12-x86_64/share/charsets/ |
+--------------------------+------------------------------------------------------------------+
8 rows in set (0.00 sec)

mysql>

mysql> show databases like 'k%';
+---------------+
| Database (k%) |
+---------------+
| kings         |
+---------------+
1 row in set (0.00 sec)

mysql>

2、创建、查看数据表

语法：

create table tb_name(字段名  字段数据类型 ，字段名2  字段数据类型……)数据表的额外参数

mysql> use kings;
Database changed
mysql> create table if not exists `Tanks` (

id int ,

name varchar(100) not null,

skill varchar(255) not null,

price int not null,

primary key(id)

)engine=innodb default charset=utf8;

Query OK, 0 rows affected (0.04 sec)

mysql>

mysql> show tables;（查看数据表）
+-----------------+
| Tables_in_kings |
+-----------------+
| Tanks           |
+-----------------+
1 row in set (0.00 sec)

mysql>

mysql> show create table Tanks\G;（查看创建数据表的详细信息）
*************************** 1. row ***************************
       Table: Tanks
Create Table: CREATE TABLE `Tanks` (
  `id` int(11) NOT NULL DEFAULT '0',
  `name` varchar(100) NOT NULL,
  `skill` varchar(255) NOT NULL,
  `price` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR:
No query specified

mysql>

mysql> desc Tanks;（查看  表结构）
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | NO   | PRI | 0       |       |
| name  | varchar(100) | NO   |     | NULL    |       |
| skill | varchar(255) | NO   |     | NULL    |       |
| price | int(11)      | NO   |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql>

3、插入表数据

mysql> insert into Tanks(id,name,skill,price) values(1,'程咬金','大招回血，血量越低，伤害越高','8888'),(2,'大鱼庄周','免疫队友所有控制','2888');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>

mysql> select * from Tanks;
+----+--------------+--------------------------------------------+-------+
| id | name         | skill                                      | price |
+----+--------------+--------------------------------------------+-------+
|  1 | 程咬金       | 大招回血，血量越低，伤害越高               |  8888 |
|  2 | 大鱼庄周     | 免疫队友所有控制                           |  2888 |
+----+--------------+--------------------------------------------+-------+
2 rows in set (0.00 sec)

mysql>

4、删除数据库和数据表

mysql> drop database lol;
Query OK, 0 rows affected (0.01 sec)

mysql> drop table kings.Tanks;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
Empty set (0.00 sec)

mysql>

5，创建用户

mysql> create user test@'127.0.0.1' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

mysql>

mysql> drop user test@'127.0.0.1  ';（删除用户）
Query OK, 0 rows affected (0.00 sec)

mysql>

这篇关于mysql基本语句（创建删除数据库表、插入数据，创建删除用户）的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！