MySQL和SQL入门

1.1 MySQL基础

1.1.1 新增账户

1、新增账户

create user 'sampdb'@'localhost' identified by 'secret';

2、授权

grant all on sampdb.* to 'sampdb'@'localhost';

1.1.2客户端连接服务器

1、连接命令

mysql options

opitons可以用或者不用空格， 常见的命令:

mysql -h host_name -u user_name -p 

• -h host_name （替换形式：--host=host_name）

待连接的mysql服务器主机名。如果客户端就在服务器上，此项可以省略。

• -u user_name（替换形式：--user=user_name）

mysql用户名

• -p （替换形式：--password）

mysql会提示输入口令。也可以-p 后面加上口令（不允许空格），但是不太安全，不建议。

2、断开连接命令

quit

exit

\q

Unix系统下，ctrl+D

1.1.3 执行SQL语句

• “；”或者\g结尾。

• \G竖排显示结果，没行一个值。

• \c清除输入

• 语句不区分大小写，以下几条是等效的：

• 可以将语句放在文件里执行

比如，myscript.sql文件里放了一条语句：

select now(),user(),version();

1.1.4 创建数据库

1、创建数据库

create database db_name;

2、查询是否选用数据库

select database();

null表示没有选用数据

3、选用数据库

use db_name;

或者在连接mysql服务器的时候选用数据库（推荐）

mysql options db_name

1.1.5 创建数据表

1、建表命令

create table table_name(column_specs);

• table_name

表名

• column_specs

该表里的各个数据列以及各个索引（如果有点话）的定义

数据列定义：数据列名字  数据类型  其它属性。

2、查看表结构

describe table_name;

desc table_name;

explain table_name;

show couluns from table_name;

show fields from table_name;

还可以用通配符“%”过滤

另外：

show tables

列出当前默认数据库中的数据表。

show databases;

列出所连接的服务器上的数据库。

客户程序mysqlshow

• mysqlshow options

列出一份数据库清单。

• mysqlshow options db_name

列出指定数据库中的表

• mysqlshow options db_name table_name

列出表中的所有列的信息。

3、建立其它示例表

---

DROP TABLE IF EXISTS student;

CREATE TABLE student

(

  name       VARCHAR(20) NOT NULL,

  sex        ENUM('F','M') NOT NULL,

  student_id INT UNSIGNED NOT NULL AUTO_INCREMENT,

  PRIMARY KEY (student_id)

) ENGINE=InnoDB;

---

DROP TABLE IF EXISTS grade_event;

CREATE TABLE grade_event

(

  date     DATE NOT NULL,

  category ENUM('T','Q') NOT NULL,

  event_id INT UNSIGNED NOT NULL AUTO_INCREMENT,

  PRIMARY KEY (event_id)

) ENGINE=InnoDB;

---

DROP TABLE IF EXISTS score;

CREATE TABLE score

(

  student_id INT UNSIGNED NOT NULL,

  event_id   INT UNSIGNED NOT NULL,

  score      INT NOT NULL,

  PRIMARY KEY (event_id, student_id),

  INDEX (student_id),

  FOREIGN KEY (event_id) REFERENCES grade_event (event_id),

  FOREIGN KEY (student_id) REFERENCES student (student_id)

) ENGINE=InnoDB;

-- 

-- InnoDB储存引擎会为出现在外键定义里的数据列自动创建一个索引，但是一般不是我们想要的，所以我们手动指定一个

---

DROP TABLE IF EXISTS absence;

CREATE TABLE absence

(

  student_id INT UNSIGNED NOT NULL,

  date       DATE NOT NULL,

  PRIMARY KEY (student_id, date),

  FOREIGN KEY (student_id) REFERENCES student (student_id)

) ENGINE=InnoDB;

1.1.6 添加新的数据行

1、利用insert语句添加数据

（1）一次性列出全部数据列的值

insert into table_name values(value1, value2, ...);

或者

insert into table_name vallues(...),(...),...;

auto_increment指定的列插入null会自动生成一个序号。

批量插入比单条插入服务器执行效率高

（2）指定列赋值

insert into table_name(column_name1, column_name2, ...) values(value1, value2, ...);

或者

insert into table_name(column_name1, column_name2, ...) values(...),.(...),...;

（3）用 set col_name=value对数据进行赋值

insert into table_name set col_name1=value1, col_name2=value2, ...;

2、通过读取文件来添加数据

• 文件中存的是insert语句

（1）使用mysql客户端程序

mysql options db_name < file_name

2、进入mysql，source命令读取文件

source filename;

• 文件存储的是纯数值

3、进入mysql,用load data命令读取文件

load data local infile 'filename' into table table_name;

如果在mysql服务器上执行这条语句，可以如果省略了local。

4、使用mysqlimport客户端程序

mysqlimport options --local db_name table_name.filesuffix1.filesuffix1;

mysqlimport程序会生成load data语句，文件名第一个“.”号前面的名称为将要导入的表的名称。

1.1.7 查询信息

注意：

1、数据库和表名称在windows系统不区分大小写，在Unix系统上区分。

2、字符串的比较操作通常不区分大小写。

3、对于包含null值得数据行，如果按升序排序将会出现在开头，如果按降序排序将会出现在末尾。要想让升序时包含null的行排在末尾，得增加一个排序数据列来区分null值和非null值。

4、随机返回几条数据，可以联合使用order by rand()和limit n。

5、如果别名中有空格，用引号括起来。

6、count(数据列名称)，统计非空数据。

7、with rollup生成超级聚合。

1.1.8 删除或更新数据行

1.2 与客户程序mysql交互的技巧

1.2.1 简化连接过程

简化连接过程的三种方式：

（1）使用一个选项文件；（可以供mysql,mysqlshow,mysqlimport等客户端程序使用）

[client]

host=localhost

user=sampdb

password=secret

（2） 利用shell的历史命令功能

（3）利用shell别名或脚本

• shell别名

想要让别名每次登陆系统都生效，就得吧alias命令放在shell的某个shell启动文件下。

• 脚本

1.2.2减少输入查询命令式的打字动作

（1）在mysql查询命令窗口编辑

（2）复制+粘贴

（3）用mysql程序执行脚本（mysql options -t 数据库 < 文件名 或者  进入mysql用source）

-t是为了格式化查询结果与在命令窗口查询的格式一致，不加就是以制表格分隔。