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>