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)
