mysql学习笔记-初识mysql

mysql是开源免费的数据库，适合中小型企业应用。能提供高效低成本支持，主要功能有ACID兼容，支持大多数ANSI SQL，联机备份，复制，安全套阶层等，可移植性好，易用。

今天学习了mysql的一些基本语句：

设置root密码

$mysql
mysql>-u root set passward=password('yourcode');

登录：

$mysql -u root -p mysql
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 49
Server version: 5.5.24-0ubuntu0.12.04.1 (Ubuntu)

登录成功

创建第一个数据库：

mysql> create database firstdb;
Query OK, 1 row affected (0.03 sec)

创建一个用户，并把firstdb的完全权限赋给这个用户

mysql> grant all on firstdb.* to victor@localhost
-> identified by 'ethenlong';
Query OK, 0 rows affected (0.00 sec)

然后用新建的用户名（如victor）登录，并将新建的数据库链接到这个用户名

mysql -u victor -pethenlong firstdb
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 51

切换数据库

mysql> use firstdb
Database changed

数据库已经建立完成，现在要向数据库中添加数据表格：

mysql> create table roommate(
-> school_num int,
-> surname varchar(40),
-> firstname varchar(30),
-> commission tinyint
-> );

注解：创建一个roommate表格，有学号/姓/名等关键字，其后接的参数是指数据类型，mysql支持多行命令，但是每个命令一定要用；结尾。

显示现有数据库中有的表格：

mysql> show tables
-> ;
+-------------------+
| Tables_in_firstdb |
+-------------------+
| sale_rep |
+-------------------+

显示表格结构：

mysql> describe sale_rep
-> ;
+-----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| employee_number | int(11) | YES | | NULL | |
| surname | varchar(40) | YES | | NULL | |
| firstname | varchar(30) | YES | | NULL | |
| commission | tinyint(4) | YES | | NULL | |
+-----------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

在表格中插入记录：

mysql> insert into sale_rep (employee_number,surname,firstname,commission)
-> values(1,'victor','xie',10);
Query OK, 1 row affected (0.05 sec)

或

mysql> insert into sale_rep values(3,'yongle','wang',14);
Query OK, 1 row affected (0.04 sec)

或同时插入多条记录：

mysql> insert into sale_rep values(4,'teng','wu',17),(5,'cun','lai',18);
Query OK, 2 rows affected (0.05 sec)
Records: 2 Duplicates: 0 Warnings: 0

信息检索select

mysql> select commission from sale_rep where surname='victor';
+------------+
| commission |
+------------+
| 10 |
+------------+

或者多个关键字：

mysql> select commission,employee_number from sale_rep where surname='victor';
+------------+-----------------+
| commission | employee_number |
+------------+-----------------+
| 10 | 1 |
+------------+-----------------+

或者返回整行：

mysql> select * from sale_rep where surname='victor';
+-----------------+---------+-----------+------------+
| employee_number | surname | firstname | commission |
+-----------------+---------+-----------+------------+
| 1 | victor | xie | 10 |
+-----------------+---------+-----------+------------+

或者按照多个条件检索：

mysql> select * from sale_rep where surname='victor' or commission>11;
+-----------------+---------+-----------+------------+
| employee_number | surname | firstname | commission |
+-----------------+---------+-----------+------------+
| 1 | victor | xie | 10 |
| 3 | yongle | wang | 14 |
| 4 | teng | wu | 17 |
| 5 | cun | lai | 18 |
+-----------------+---------+-----------+------------+

注解：在使用条件检索的时候，注意and 和or关系的使用。

模糊查询，当记不清准确关键字的时候可以使用like：

mysql> select * from sale_rep where surname like 'vic%';
+-----------------+---------+-----------+------------+
| employee_number | surname | firstname | commission |
+-----------------+---------+-----------+------------+
| 1 | victor | xie | 10 |
+-----------------+---------+-----------+------------+

或：

mysql> select * from sale_rep where surname like '%c%';
+-----------------+---------+-----------+------------+
| employee_number | surname | firstname | commission |
+-----------------+---------+-----------+------------+
| 1 | victor | xie | 10 |
| 5 | cun | lai | 18 |
+-----------------+---------+-----------+------------+

或者分类查询，按照某一个关键字显示，使用order by

mysql> select * from sale_rep order by surname;
+-----------------+---------+-----------+------------+
| employee_number | surname | firstname | commission |
+-----------------+---------+-----------+------------+
| 5 | cun | lai | 18 |
| 2 | erlong | sun | 11 |
| 4 | teng | wu | 17 |
| 1 | victor | xie | 10 |
| 3 | yongle | wang | 14 |
| 2 | yuan | sun | 11 |
+-----------------+---------+-----------+------------+

或者保持分类查询多级优先排序，可加多个关键字：

mysql> select * from sale_rep order by surname,commission;
+-----------------+---------+-----------+------------+
| employee_number | surname | firstname | commission |
+-----------------+---------+-----------+------------+
| 5 | cun | lai | 18 |
| 2 | erlong | sun | 11 |
| 4 | teng | wu | 17 |
| 1 | victor | xie | 10 |
| 3 | yongle | wang | 14 |
| 2 | yuan | sun | 11 |
+-----------------+---------+-----------+------------+

或者采用降续查询时，可在其后加关键字desc

mysql> select * from sale_rep order by commission desc;

使用max（）返回最大值：

mysql> select max(commission) from sale_rep;
+-----------------+
| max(commission) |
+-----------------+
| 18 |
+-----------------+

还有avg（）,min(),sum()等返回平均数，最小值，总数等。

数学计算，如

mysql> select surname,firstname,commission+1 from sale_rep;
+---------+-----------+--------------+
| surname | firstname | commission+1 |
+---------+-----------+--------------+
| victor | xie | 11 |
| erlong | sun | 12 |
| yuan | sun | 12 |
| yongle | wang | 15 |
| teng | wu | 18 |
| cun | lai | 19 |
+---------+-----------+--------------+

删除某条记录：

mysql> delete from sale_rep where commission=18;

更新某条记录：

mysql> update sale_rep set commission = 19 where employee_number=1;

insert/select/update/delete组成了处理数据库的四个标准语句，是（DATA MANIPULATION LANGUAGE DML）的一部分。

初学，高手莫喷啊！！！
转自本人博客：http://www.shallong.me/?p=43