mysql数据库的基本操作代码

一.对数据库的操作（对库不能进行修改，只能删除操作）

show databases;//显示库

create database user;//创建user的库

drop database user;//删除user的库

use database;//使用库

use user;//使用user这个库

二.对数据表的操作

数据表就等于面向对象中的一个对象，表里面的字段就是对象的属性。

User(string username,string password,string nickname)

1.create table t_user(//创建表

            id int(20) primary key auto_increment,//创建id为主键（类型为INT（大小））,它是自动递增的（必须创建主键）

            username varchar(20),

            password varchar(40),

            nickname varchar(20)

);

2.drop table t_user;//删除表

3.show tables;//查询所有表

4.desc t_user;//查询t_user表的结构

5.alter table t_user change username name varchar(100);//修改字段的名称

6.alter table t_user add column age int(10) after username;//在username之后添加age

7.alter table t_user drop column age;//删除age字段

三.数据表中的内容操作

1.插入

insert into t_user (username,password,nickname) value ('zxy',"123","张学友");

2.修改

updata t_user set nickname='张学友' where id=2;//将id为2的nickname改成张学友

update t_user set nickname='张学友',username="zzxy" where id=2;//将id=2的nickname改为张学友，username改为zzxy

3.删除

delete from t_user where id=2;//删除表中id=2的所有内容（但是不会删除索引）

delete from t_user;//删除表中的所有内容（但是不会删除索引）

truncate t_user;//删除表中的内容包括索引

4.查询

select * from t_user;//查询表中的所有内容

select username,nickname from t_user;//查询表中其中几个字段（投影）

select id as '标示',username as '用户名' from t_user;//将投影改为中文名

insert into t_user (username,password,nickname,age) value ('z1xy',"123","张学友",33);

insert into t_user (username,password,nickname,age) value ('zxy2',"123","张趣味",33);

insert into t_user (username,password,nickname,age) value ('zx3y',"123","到时候",43);

insert into t_user (username,password,nickname,age) value ('zx2y',"123","犬瘟热",23);

insert into t_user (username,password,nickname,age) value ('z1x1y',"123","莱顿河",13);

insert into t_user (username,password,nickname,age) value ('zdxy',"123","刘德华",53);

insert into t_user (username,password,nickname,age) value ('szxy',"123","富士山",32);

insert into t_user (username,password,nickname,age) value ('dzxy',"123","佛山市",52);

insert into t_user (username,password,nickname,age) value ('dczxy',"123","海景房",53);

insert into t_user (username,password,nickname,age) value ('zfxy',"123","大赛",32);

insert into t_user (username,password,nickname,age) value ('ezdxy',"123","大大大",56);

insert into t_user (username,password,nickname,age) value ('ezxdsy',"123","有意义",12);

insert into t_user (username,password,nickname,age) value ('ezesxy',"123","张学友",23);

insert into t_user (username,password,nickname,age) value ('ezwxy',"123","张学友",25);

insert into t_user (username,password,nickname,age) value ('ezxy',"123","张学友",22);

四.基础查询（包含输出的结果）

mysql> select * from t_user where age>23;//查询年龄大于23

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

| id | username | password | nickname  | age  |

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

|  2 | z1xy    | 123      | 张学友    |  33 |

|  3 | zxy2    | 123      | 张趣味    |  33 |

|  4 | zx3y    | 123      | 到时候    |  43 |

|  7 | zdxy    | 123      | 刘德华    |  53 |

|  8 | szxy    | 123      | 富士山    |  32 |

|  9 | dzxy    | 123      | 佛山市    |  52 |

| 10 | dczxy    | 123      | 海景房    |  53 |

| 11 | zfxy    | 123      | 大赛      |  32 |

| 12 | ezdxy    | 123      | 大大大    |  56 |

| 15 | ezwxy    | 123      | 张学友    |  25 |

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

10 rows in set (0.00 sec)

mysql> select * from t_user where age>23 and username like 'z%';//查询uername为z开头的数据（%代表任意字符）

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

| id | username | password | nickname  | age  |

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

|  2 | z1xy    | 123      | 张学友    |  33 |

|  3 | zxy2    | 123      | 张趣味    |  33 |

|  4 | zx3y    | 123      | 到时候    |  43 |

|  7 | zdxy    | 123      | 刘德华    |  53 |

| 11 | zfxy    | 123      | 大赛      |  32 |

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

5 rows in set (0.00 sec)

mysql> select * from t_user where age>23 and username like '%z%';//查询username包含z的数据

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

| id | username | password | nickname  | age  |

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

|  2 | z1xy    | 123      | 张学友    |  33 |

|  3 | zxy2    | 123      | 张趣味    |  33 |

|  4 | zx3y    | 123      | 到时候    |  43 |

|  7 | zdxy    | 123      | 刘德华    |  53 |

|  8 | szxy    | 123      | 富士山    |  32 |

|  9 | dzxy    | 123      | 佛山市    |  52 |

| 10 | dczxy    | 123      | 海景房    |  53 |

| 11 | zfxy    | 123      | 大赛      |  32 |

| 12 | ezdxy    | 123      | 大大大    |  56 |

| 15 | ezwxy    | 123      | 张学友    |  25 |

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

10 rows in set (0.00 sec)

mysql> select count(*) from t_user where nickname like '张%';//查询nickname姓张有多少人

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

| count(*) |

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

|        6 |

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

1 row in set (0.04 sec)

mysql> select max(age) from t_user;//查询年龄最大的值

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

| max(age) |

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

|      56 |

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

1 row in set (0.00 sec)

mysql> select max(age) from t_user where nickname like '张%';//姓张的最大年龄

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

| max(age) |

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

|      33 |

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

1 row in set (0.00 sec)

mysql> select min(age) from t_user where nickname like '张%';//姓张的最小年龄

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

| min(age) |

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

|      22 |

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

1 row in set (0.00 sec)

mysql> select avg(age) from t_user where nickname like '张%';//姓张的平均年龄

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

| avg(age) |

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

|  27.2000 |

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

1 row in set (0.00 sec)

mysql> select * from t_user where age=(select min(age) from t_user);//查询年纪最小的用户

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

| id | username | password | nickname  | age  |

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

| 13 | ezxdsy  | 123      | 有意义    |  12 |

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

1 row in set (0.00 sec)

mysql> select * from t_user where age is NULL;//查询空值

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

| id | username | password | nickname  | age  |

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

|  1 | zxy      | 123      | 张学友    | NULL |

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

1 row in set (0.00 sec)