mysql 基本操作

mysql_secure_installation

安装完mysql-server 会提示可以运行mysql_secure_installation。运行mysql_secure_installation会执行几个设置：
  a)为root用户设置密码
  b)删除匿名账号
  c)取消root用户远程登录
  d)删除test库和对test库的访问权限
  e)刷新授权表使修改生效

通过这几项的设置能够提高mysql库的安全。建议生产环境中mysql安装这完成后一定要运行一次mysql_secure_installation

 

Mysql详细教程 >>> http://www.runoob.com/mysql/mysql-tutorial.html

1.登录mysql

root@openstack001:/tmp# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 36
Server version: 5.5.53-0ubuntu0.14.04.1 (Ubuntu)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

2.show查询database

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| mysql_python       |
| performance_schema |
| text               |
+--------------------+
5 rows in set (0.00 sec)

mysql> 

3.use进入database,查看表清单

mysql> use text;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_text |
+----------------+
| department     |
| employee       |
| project        |
| table_1        |
| v_emp          |
+----------------+
5 rows in set (0.01 sec)

mysql> 

4.desc查看表结构

mysql> desc employee;
+--------+----------+------+-----+---------+-------+
| Field  | Type     | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| id     | int(10)  | NO   | PRI | NULL    |       |
| name   | char(20) | YES  |     | NULL    |       |
| age    | int(10)  | YES  |     | NULL    |       |
| salary | int(10)  | NO   |     | NULL    |       |
| phone  | int(12)  | NO   | UNI | NULL    |       |
| in_dpt | char(20) | NO   | MUL | NULL    |       |
+--------+----------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql> 

5.Create database创建数据库

mysql> create database hoho;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hoho               |
| mysql              |
| mysql_python       |
| performance_schema |
| text               |
+--------------------+
6 rows in set (0.00 sec)

mysql> 

6.Create table创建表

在数据库中新建一张表的语句格式为：
CREATE TABLE 表的名字
(
列名a 数据类型(数据长度),
列名b 数据类型(数据长度)，
列名c 数据类型(数据长度)
);

mysql> use hoho;
Database changed
mysql> CREATE TABLE employee (id int(10),name char(20),phone int(12));
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+----------------+
| Tables_in_hoho |
+----------------+
| employee       |
+----------------+
1 row in set (0.00 sec)

mysql> 

7.Insert Into插入数据

表创建好后还是一个空表，我们需要像表中写入记录，语法格式为：

INSERT INTO 表的名字(列名a,列名b,列名c) VALUES(值1,值2,值3);

mysql> INSERT INTO employee(id,name,phone) VALUES(01,'Tom',110110110);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee VALUES(02,'Jack',119119119);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee(id,name) VALUES(03,'Rose');
Query OK, 1 row affected (0.00 sec)

mysql> 

8.Select查询数据

mysql> select * from employee;
+------+------+-----------+
| id   | name | phone     |
+------+------+-----------+
|    1 | Tom  | 110110110 |
|    2 | Jack | 119119119 |
|    3 | Rose |      NULL |
+------+------+-----------+
3 rows in set (0.01 sec)

mysql>