mysql 重新学习一 （增删查改）

0. mysql的开关：

/etc/init.d/mysql   stop

/etc/init.d/mysql   start

/etc/init.d/mysql   restart

#service  mysql   stop

#service  mysql   start

#service  mysql  restart

service  mysqld   stop

service  mysqld  start

service  mysqld  restart

root@cy-VirtualBox:~# /etc/init.d/mysql stop

 * Stopping MySQL database server mysqld                                 [ OK ] 

root@cy-VirtualBox:~# /etc/init.d/mysql start

 * Starting MySQL database server mysqld                                 [ OK ] 

root@cy-VirtualBox:~# service mysql restart

mysql stop/waiting

mysql start/running, process 7277

root@cy-VirtualBox:~# service mysql stop

mysql stop/waiting

root@cy-VirtualBox:~# service mysql start

mysql start/running, process 7441

root@cy-VirtualBox:~# /etc/init.d/mysql restart

 * Stopping MySQL database server mysqld                                 [ OK ] 

 * Starting MySQL database server mysqld                                 [ OK ] 

 * Checking for tables which need an upgrade, are corrupt or were 

not closed cleanly.

root@cy-VirtualBox:~# 

查看mysql状态：

root@cy-VirtualBox:~# /etc/init.d/mysql status

 * MySQL is stopped.

正常状态：

root@cy-VirtualBox:~# /etc/init.d/mysql status

 * /usr/bin/mysqladmin  Ver 8.42 Distrib 5.5.43, for debian-linux-gnu on i686

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.

Server version 5.5.43-0ubuntu0.14.04.1

Protocol version 10

Connection Localhost via UNIX socket

UNIX socket /var/run/mysqld/mysqld.sock

Uptime: 1 min 34 sec

Threads: 1  Questions: 114  Slow queries: 0  Opens: 408  Flush tables: 1  Open tables: 400  Queries per second avg: 1.212

root@cy-VirtualBox:~# 

1. 进入数据库：

mysql -h localhost -u root -p

root@cy-VirtualBox:~# mysql -h localhost -u root -p

Enter password: 

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 43

Server version: 5.5.43-0ubuntu0.14.04.1 (Ubuntu)

2.显示数据库：

show  databases;

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| GoodByeDealer      |

| iWebShop           |

| invo               |

| laravel5           |

| mysql              |

| performance_schema |

| prestashop         |

| test               |

| tutorial           |

+--------------------+

10 rows in set (0.00 sec)

mysql> 

3.创建数据库；

create  database  chuanyu;

mysql> create database chuanyu;

Query OK, 1 row affected (0.08 sec)

mysql> 

删除数据库：

drop  database  test;

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| GoodByeDealer      |

| chuanyu            |

| iWebShop           |

| invo               |

| laravel5           |

| mysql              |

| performance_schema |

| prestashop         |

| test               |

| tutorial           |

+--------------------+

11 rows in set (0.00 sec)

mysql> 

4.使用创建的database；

use  chuanyu;

mysql> use chuanyu;

Database changed

mysql> 

5.查看database下的表结构

 show  tables;

mysql> show tables;

Empty set (0.00 sec)

mysql> 

6.创建一个user

create table user(

id int,

name varchar(20),

passwor varchar(20)

);

mysql> create table user

    -> (

    -> id int,

    -> name varchar(20),

    -> password varchar(20)

    -> );

Query OK, 0 rows affected (0.06 sec)

mysql> 

修改表名：

ALTER   TABLE  test  RENAME  test2;

查看表属性字段：

DESCRIBE  test;

增加字段：

ALTER  TABLE  test  ADD  content  varchar(50);

删除字段：

ALTER  TABLE  test  DROP  content;

删除表：

DROP  TABLE  test;

7.插入数据：

mysql> insert into user values(1,'chuanyu','123456');

Query OK, 1 row affected (0.05 sec)

mysql> insert into user values(2,'cy','123456');

Query OK, 1 row affected (0.05 sec)

mysql> insert into user values(3,'管理员','123456');

Query OK, 1 row affected, 1 warning (0.06 sec)

mysql> 

查看插入的数据：

mysql> show tables;

+-------------------+

| Tables_in_chuanyu |

+-------------------+

| user              |

+-------------------+

1 row in set (0.00 sec)

mysql> select * from user;

+------+---------+----------+

| id   | name    | password |

+------+---------+----------+

|    1 | chuanyu | 123456   |

|    2 | cy      | 123456   |

|    3 | ???     | 123456   |

+------+---------+----------+

3 rows in set (0.00 sec)

mysql> 

发现无法显示中文.

8.删除一条信息：

delete  from user where id = 3;

mysql> delete from user where id=3;

Query OK, 1 row affected (0.09 sec)

mysql> select * from user;

+------+---------+----------+

| id   | name    | password |

+------+---------+----------+

|    1 | chuanyu | 123456   |

|    2 | cy      | 123456   |

+------+---------+----------+

2 rows in set (0.00 sec)

mysql> 

9.更新一条数据：

update user  set  name ='admin'  where id = 3;

mysql> update user set name= admin where id = 3;

ERROR 1054 (42S22): Unknown column 'admin' in 'field list'

mysql> update user set name= 'admin' where id = 3;

Query OK, 1 row affected (0.06 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user;

+------+---------+----------+

| id   | name    | password |

+------+---------+----------+

|    1 | chuanyu | 123456   |

|    2 | cy      | 123456   |

|    3 | admin   | 123456   |

+------+---------+----------+

3 rows in set (0.00 sec)

mysql> 

更新多条数据：

update user set name='test',password='123' where id=3;

mysql> update user set name='test',password='123' where id=3;

Query OK, 1 row affected (0.05 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> 

mysql> select * from user;

+------+---------+----------+

| id   | name    | password |

+------+---------+----------+

|    1 | chuanyu | 123456   |

|    2 | cy      | 123456   |

|    3 | test    | 123      |

+------+---------+----------+

3 rows in set (0.00 sec)

10.查询所有用户的信息：

select  id, name, password  from user;

或  select  * from user;

mysql> select * from user;

+------+---------+----------+

| id   | name    | password |

+------+---------+----------+

|    1 | chuanyu | 123456   |

|    2 | cy      | 123456   |

|    3 | admin   | 123456   |

+------+---------+----------+

3 rows in set (0.00 sec)

mysql> select id,name,password from user;

+------+---------+----------+

| id   | name    | password |

+------+---------+----------+

|    1 | chuanyu | 123456   |

|    2 | cy      | 123456   |

|    3 | admin   | 123456   |

+------+---------+----------+

3 rows in set (0.00 sec)

mysql>