mysql的基本使用

登入mysql：

$ mysql -u dbuser1 -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.6.26 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, 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.

登入之后，首先要设置客户端字符集跟操作系统一致

查看系统字符集：

$ locale 
LANG=en_US.UTF-8
LC_CTYPE="en_US.UTF-8"
LC_NUMERIC="en_US.UTF-8"
LC_TIME="en_US.UTF-8"
LC_COLLATE="en_US.UTF-8"
LC_MONETARY="en_US.UTF-8"
LC_MESSAGES="en_US.UTF-8"
LC_PAPER="en_US.UTF-8"
LC_NAME="en_US.UTF-8"
LC_ADDRESS="en_US.UTF-8"
LC_TELEPHONE="en_US.UTF-8"
LC_MEASUREMENT="en_US.UTF-8"
LC_IDENTIFICATION="en_US.UTF-8"
LC_ALL=

设置客户端字符集：

mysql> set NAMES utf8;
Query OK, 0 rows affected (0.01 sec)

重设密码：

mysql> set password=password('1234');

创建 数据库：

注意创建的数据库字符集要跟操作系统的字符集一致

例：

创建一个名称为db1的数据库，字符集为utf-8

mysql> CREATE DATABASE db1 CHARACTER SET utf8;
Query OK, 1 row affected (0.10 sec)

使用数据库

例：

使用名称为db1的数据库

mysql> USE db1;
Database changed

查看已有所有数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.04 sec)

创建新用户：

注意不要以root用户使用数据库，不安全。

例：

创建一个名称为dbuser1,对数据库db1有所有操作权限的用户，密码为19940308

mysql> use mysql;
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> GRANT ALL ON db1.* TO dbuser1 IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.10 sec)

grant all privileges on *.* to cyd@localhost identified by '123456';

创建表：

例：
创建一张名称为table1的表，表中有name1列（定长），name2列（可变长），age列（int）

mysql> CREATE TABLE table1 (name1 char(100), name2 varchar(100), age int);

<pre code_snippet_id="1641147" snippet_file_name="blog_20160410_9_5532078" name="code" class="sql">mysql> <span style="font-family: Arial, Helvetica, sans-serif;">create table user(userId int primary key not null auto_increment, userName varchar(50) not null, password varchar(20) not null);</span>

Query OK, 0 rows affected (0.33 sec)

查看本数据库中所有表：

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

查看表的结构：

mysql> desc table1;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name1 | char(100)    | YES  |     | NULL    |       |
| name2 | varchar(100) | YES  |     | NULL    |       |
| age   | int(11)      | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.07 sec)

插入数据：

mysql> INSERT INTO table1 ( name1, name2, age) VALUES ('张三','李四', 20);
Query OK, 1 row affected (0.06 sec)

查看表内容：

mysql> SELECT * FROM table1;
+--------+--------+------+
| name1  | name2  | age  |
+--------+--------+------+
| 张三   | 李四   |   20 |
+--------+--------+------+
1 row in set (0.00 sec)

批量执行sql语句

首先创建一个后缀名为.sql的文件（保证字符集一致）

进入数据库中后

mysql> source my.sql;

查看当前用户

mysql> select user();

mysql> select current_user();

添加远程连接权限

mysql> use mysql;

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'IDENTIFIED BY 'password' WITH GRANT OPTION;

mysql> FLUSH PRIVILEGES; 

当MySQL 连接服务器时发生”Can not connect to mysql error 10061”错误，我们检查/etc/mysql/my.conf文件中的bind-address选项是否设置为服务器的IP，默认为127.0.0.1。换成本机IP。

创建主键

create table test ( name varchar(19), id number, value varchar(10), primary key (id,name) ) 

创建普通索引

如果是CHAR,VARCHAR类型，length可以小于字段实际长度;如果是  BLOB 和 TEXT 类型，必须指定length，下同。

CREATE INDEX indexName ON  tableName(tableColumns(length)); //创建索引

ALTER tableName ADD INDEX [indexName] ON (tableColumns(length))   //修改表结构

CREATE TABLE tableName ( [...], INDEX [indexName] (tableColumns(length)) ;  //创建表的时候直接指定

创建唯一索引

CREATE UNIQUE INDEX indexName ON tableName(tableColumns(length))

ALTER tableName ADD UNIQUE [indexName] ON (tableColumns(length))

CREATE TABLE tableName ( [...], UNIQUE [indexName] (tableColumns(length));

创建联合索引

CREATE INDEX idxtitle ON title (title,name)

ALTER TABLE `tasks`ADD INDEX `testabc` (`title`, `created`) ;

CREATE TABLE tableName ( [...], INDEX [indexName] (title,name);