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> grant select,insert,update,delete on kings.Tanks to 'peng'@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host ,password from mysql.user;
+------+-----------------------+-------------------------------------------+
| user | host | password |
+------+-----------------------+-------------------------------------------+
| root | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
| | localhost.localdomain | |
| root | 192.168.0.% | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| peng | % | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+------+-----------------------+-------------------------------------------+
7 rows in set (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql>
