mysql基本用法

1.安装

2.登录

• 启动数据库

  [root@localhost ~]# service mysqld start
  [root@localhost ~]# /etc/init.d/mysqld start

  
  
  
• 关闭数据库

  [root@localhost ~]# service mysqld stop

• 增加mysql自启动

  [root@localhost ~]# chkconfig --list mysqld
  mysqld         	0:关闭	1:关闭	2:关闭	3:关闭	4:关闭	5:关闭	6:关闭
  [root@localhost ~]# chkconfig --add mysqld
  [root@localhost ~]# chkconfig --list mysqld
  mysqld         	0:关闭	1:关闭	2:关闭	3:关闭	4:关闭	5:关闭	6:关闭
  [root@localhost ~]# chkconfig --level 345 mysqld on
  [root@localhost ~]# chkconfig --list mysqld
  mysqld         	0:关闭	1:关闭	2:关闭	3:启用	4:启用	5:启用	6:关闭

  
  
• 修改密码

  [root@localhost ~]# vim /etc/my.cnf 
  [root@localhost ~]# service mysqld start
  root@localhost ~]# /usr/bin/mysqladmin -u root password beyond

  
  
• 查看数据库

  mysql> show databases;
  +--------------------+
  | Database           |
  +--------------------+
  | information_schema |
  | test               |
  +--------------------+
  2 rows in set (0.00 sec)

  
  
• 使用数据库

  mysql> use test;
  Database changed

• 创建一个简单表结构

  mysql> create table users
      -> (
      -> id int,
      -> name char(10),
      -> year date
      -> );
  Query OK, 0 rows affected (0.08 sec)

• 显示表结构

  mysql> describe users;
  +-------+----------+------+-----+---------+-------+
  | Field | Type     | Null | Key | Default | Extra |
  +-------+----------+------+-----+---------+-------+
  | id    | int(11)  | YES  |     | NULL    |       |
  | name  | char(10) | YES  |     | NULL    |       |
  | year  | date     | YES  |     | NULL    |       |
  +-------+----------+------+-----+---------+-------+
  3 rows in set (0.00 sec)

  
  
• 查看现在的数据库中存在什么表

  mysql> use test;
  Database changed
  mysql> show tables;
  
  +----------------+
  | Tables_in_test |
  +----------------+
  | users          |
  +----------------+
  1 row in set (0.00 sec)

  
  

• 添加记录，依次添加

  mysql> insert into users values(1, 'wm', '2015-4-20');

  
  
• 查询记录

  mysql> select * from users;
  +------+------+------------+
  | id   | name | year       |
  +------+------+------------+
  |    1 | wm   | 2015-04-20 |
  |    1 | wm   | 2015-04-21 |
  |    1 | wm   | 2015-04-22 |
  |    2 | wm1  | 2015-04-20 |
  |    2 | wm1  | 2015-04-21 |
  |    2 | wm1  | 2015-04-22 |
  |    3 | wm3  | 2015-04-20 |
  |    3 | wm3  | 2015-04-21 |
  |    3 | wm3  | 2015-04-22 |
  |    3 | wm3  | 2015-04-23 |
  +------+------+------------+
  10 rows in set (0.00 sec)

  
  
• 查询用户最后一次登录的记录

  mysql> select t.id, t.name, t.year from users t inner join (
      -> select id, max(year) as year1
      -> from users
      -> group by id
      -> ) tm on t.id = tm.id and t.year = tm.year1;
  +------+------+------------+
  | id   | name | year       |
  +------+------+------------+
  |    1 | wm   | 2015-04-22 |
  |    2 | wm1  | 2015-04-22 |
  |    3 | wm3  | 2015-04-23 |
  +------+------+------------+
  3 rows in set (0.00 sec)

  
  
• 使用substring_index和group_concat函数实现查询记录

  mysql> select id, name,substring_index(group_concat(year order by year desc), ",",1) as atime from users group by id;
  +------+------+------------+
  | id   | name | atime      |
  +------+------+------------+
  |    1 | wm   | 2015-04-22 |
  |    2 | wm1  | 2015-04-22 |
  |    3 | wm3  | 2015-04-23 |
  +------+------+------------+
  3 rows in set (0.00 sec)

  
  
• 上面实例查询并删除其他记录

  mysql> delete from users where (id, name, year) not in (select * from (select id, name,substring_index(group_concat(year order by year desc), ",",1) as year from users group by id) A);
  Query OK, 9 rows affected (0.01 sec)
  
  mysql> select * from users;
  +------+------+------------+
  | id   | name | year       |
  +------+------+------------+
  |    1 | wm   | 2015-04-22 |
  |    2 | wm2  | 2015-04-23 |
  |    3 | wm3  | 2015-04-24 |
  +------+------+------------+
  3 rows in set (0.00 sec)

  
  
• 另一种上例方式删除去重记录只保留最近一条

  mysql> delete A from users A where not exists( select 1 from (select id, name,substring_index(group_concat(year order by year desc), ",",1) as year from users group by id) B  where B.id=A.id and B.name=A.name and B.year=A.year);