首页数据库

# 连接数据库

连接数据库
/bin#mysql -u root -p
password:
增 create database 数据库名字;
结尾要加分号
查询数据库 show databaes;查询所有数据库
查询指定数据库show create database 名字; 
use 数据库名字 进入数据库
mysql注释符"#"   "/*123*/"  " --"

# 创建表

查看表结构 desc table 表名;
查看表 desc 表名；
查看当前数据库所有表 show tables;
创建表 create table if not exists GYusers (GYid int(100) auto_increment,
                          users varchar(30) not null,
                          password varchar(40) not null,
                          email varchar(40) not null,
                          primary KEY (GYid)
                         )engine=myisam default charset=utf8;
auto_increment(定义列为自增的属性，一般用于主键，数值会自动加1。)
not null不可为null
primary key (关键字用于定义列为主键。 您可以使用多列来定义主键，列间以逗号分隔。)
engine=(选择数据库引擎)常见的数据库引擎 myisam,InnoDB
charset=(设置编码)
if not exists(表示当相同的表名存在时，则不执行此创建语句，避免语句执行错误)
default(为该属性设置默认值)
通过命令提示符创建表
mysql> create table if not exists GYusers (
  -> GYid int not null auto_increment,
  -> users varchar(30) not null,
  -> password varchar(40) not null,
  -> email varchar(40) not null,
  -> PRIMARY KEY (GYid)
  -> )engine=myisam default charset=utf8;
Query OK, 0 rows affected (0.16 sec)
mysql>
# 修改表的操作


删除表
drop table 表名;
mysql>drop table gy_table;
修改表名
alter table 旧表名 rename 新表名；
mysql>alter table gy_table rename ar_table;
修改字段名
ALTER TABLE 表名 CHANGE 旧属性名 新属性名 新数据类型;
mysql>alter table nasa change qqn qq int(10);
修改字段的数据类型
alter table 表名 modify 属性名 数据类型 修改字段的数据类型
mysql>alter table nasa modify password int not null;
同时也可以修改可以为空不可以为空 加上not null表示不可以为空 只有null
表示可以为空 不加就代表不修改
增加字段名
mysql>alter table add nasa qq int(10) unsigned null;
unsigned(不允许有符号可以用做不能有负数)
ALTER TABLE 表名 ADD 属性名1 数据类型 [完整性约束条件] [FIRST | AFTER 属性名2];
在某属性名后增加
mysql> alter table users add qq int(10) not null  after  password;
在开头添加
mysql>alter table users add addres varchar(30) not null first;
删除字段
ALTER TABLE 表名 DROP 属性名;
mysql>alter table users drop password;
更改表的存储引擎
格式：ALTER TABLE 表名 ENGINE = 存储引擎名;
mysql> alter table users engine=InnoDB;
# 增删改查


增
insert into users(username,password,qq,email)va
lues("admin","admin123",112233,"31166@qq.com");
增加两条
insert into users(username,password,qq,email)
values("root","root",1133333,"30099@qq.com"),("gyar","adminroot",878787,"admin@qwe.com");
删除
delete from users where id=1;
where条件 id=1
不添加where语句代表全部删除
更改
update users set password="admin321"where id=2;
更改多个
update users set password="admin321",qq="75757575"where id=2;
查询
查询所有
select * from users;
查询指定
select password,qq from users;
查询id=1的password,qq
select password,qq from users where id=3;
select password,qq from users where id>3;
select password,qq from users where id<3;
in查询
查询id=2,4,5
mysql>select * from users where id in(2,4,5);
+----+----------+-----------+----------+---------------+
| id | username | password  | qq       | email         |
+----+----------+-----------+----------+---------------+
|  2 | admin    | admin321  |  75757575 | 31166@qq.com  |
|  4 | gyar     | adminroot |   878787 | admin@qwe.com |
|  5 | adminer  | 443322114  | 21987321 | 123408@ad.com |
+----+----------+-----------+----------+---------------+
between查询
查询2到4
mysql>select password,qq from users where id between 2 and 4;
+-----------+---------+
| password  | qq      |
+-----------+---------+
| admin321  | 75757575 |
| root      | 1133333 |
| adminroot |  878787 |
+-----------+---------+
不查询1到3
mysql> select password,qq from users where id not between 1 and 3;
+-----------+----------+
| password  | qq       |
+-----------+----------+
| adminroot |   878787 |
| 443322114  | 21987321 |
+-----------+----------+
like查询
查询username字段里带m的
mysql>select * from users where username like "%m%";
+----+----------+----------+----------+---------------+
| id | username | password | qq       | email         |
+----+----------+----------+----------+---------------+
|  2 | admin    | admin321 |  75757575 | 31166@qq.com  |
|  5 | adminer  | 443322114 | 21987321 | 123408@ad.com |
+----+----------+----------+----------+---------------+
查询email字段里带we的
mysql> select * from users where email like "%we%";
+----+----------+-----------+--------+---------------+
| id | username | password  | qq     | email         |
+----+----------+-----------+--------+---------------+
|  4 | gyar     | adminroot | 878787 | admin@qwe.com |
+----+----------+-----------+--------+---------------+
下划线 _ 匹配一个字符 
select* from users where username like "admi_"
mysql> select * from users where username like "admi_";
+----+----------+----------+---------+--------------+
| id | username | password | qq      | email        |
+----+----------+----------+---------+--------------+
|  2 | admin    | admin321 | 75757575 | 31166@qq.com |
+----+----------+----------+---------+--------------+
充当下一个字符
满足多个条件 and
mysql> select * from users where id=3 and password="root";
+----+----------+----------+---------+--------------+
| id | username | password | qq      | email        |
+----+----------+----------+---------+--------------+
|  3 | root     | root     | 1133333 | 30099@qq.com |
+----+----------+----------+---------+--------------+
满足任意一个条件 or
mysql> select * from users where id=3 or password="roo"；
+----+----------+----------+---------+--------------+
| id | username | password | qq      | email        |
+----+----------+----------+---------+--------------+
|  3 | root     | root     | 1133333 | 30099@qq.com |
+----+----------+----------+---------+--------------+
1 row in set (0.00 sec)
聚合查询
聚合函数包括 COUNT()，SUM()，AVG()，MAX() 和 MIN()。其中，COUNT() 用来统计
录的条数；SUM() 用来计算字段值的总和；AVG() 用
来计算字段值的平均值；MAX() 用来查询字段的最大值；MIN() 用来查询字段的最小值。
count 返回行数
mysql> select count(*) from users;
+----------+
| count(*) |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)
mysql> select count(id) from users;
+-----------+
| count(id) |
+-----------+
|         4 |
+-----------+
1 row in set (0.00 sec)
sum返回某列的和
mysql> select sum(qq) from users;
+----------+
| sum(qq)  |
+----------+
| 26004269 |
+----------+
1 row in set (0.00 sec)
avg返回某列平均值
mysql> select avg(qq) from users;
+--------------+
| avg(qq)      |
+--------------+
| 6501067.2500 |
+--------------+
1 row in set (0.00 sec)
max返回最大值
mysql> select max(qq) from users;
+----------+
| max(qq)  |
+----------+
| 21987321 |
+----------+
1 row in set (0.00 sec)
MIN() 返回某列的最小值
mysql> select min(qq) from users;
+---------+
| min(qq) |
+---------+
|  878787 |
+---------+
1 row in set (0.00 sec)
 GROUP BY单独使用
单独使用 GROUP BY 关键字时，查询结果会只显示每个分组的第一条记录。
GROUP_CONCAT() 
GROUP BY 关键字可以和 GROUP_CONCAT() 函数一起使用。GROUP_CONCAT() 函数
会把每个分组的字段值都显示出来
mysql> SELECT `username`, GROUP_CONCAT(qq) FROM users group by username;
+----------+------------------+
| username | GROUP_CONCAT(qq) |
+----------+------------------+
| admin    | 75757575,2931023  |
| adminer  | 21987321         |
| gyar     | 878787           |
| root     | 1133333          |
+----------+------------------+
count和group by
mysql> select username,count(username) from users group by username;
+----------+-----------------+
| username | count(username) |
+----------+-----------------+
| admin    |               2 |
| root     |               1 |
| gyar     |               1 |
| adminer  |               1 |
+----------+-----------------+
WITH POLLUP 关键字用来在所有记录的最后加上一条记录，这条记录是上面所有记
录的总和，即统计记录数量。
mysql> select `username`, group_concat(qq) from users group by username WITH ROLLUP;
+----------+-----------------------------------------+
| username | group_concat(qq)                        |
+----------+-----------------------------------------+
| admin    | 75757575,2931023                         |
| adminer  | 21987321                                |
| gyar     | 878787                                  |
| root     | 1133333                                 |
| NULL     | 75757575,2931023,21987321,878787,1133333 |
+----------+-----------------------------------------+
limit 查询
限制查询数量
从1开始查询6个
mysql> select * from users limit 1,6;
+----+----------+-----------+----------+---------------+
| id | username | password  | qq       | email         |
+----+----------+-----------+----------+---------------+
|  3 | root     | root      |  1133333 | 30099@qq.com  |
|  4 | gyar     | adminroot |   878787 | admin@qwe.com |
|  5 | adminer  | 443322114  | 21987321 | 123408@ad.com |
|  6 | admin    | password  |  2931023 | admin123@.com |
+----+----------+-----------+----------+---------------+
4 rows in set (0.00 sec)
子查询
as(起一个别名)
mysql> select * ,(select version())as 数据库版本 from users;
+----+----------+-----------+----------+---------------+------------+
| id | username | password  | qq       | email         | 数据库版本 |
+----+----------+-----------+----------+---------------+------------+
|  2 | admin    | admin321  |  75757575 | 31166@qq.com  | 8.0.32     |
|  3 | root     | root      |  1133333 | 30099@qq.com  | 8.0.32     |
|  4 | gyar     | adminroot |   878787 | admin@qwe.com | 8.0.32     |
|  5 | adminer  | 443322114  | 21987321 | 123408@ad.com | 8.0.32     |
|  6 | admin    | password  |  2931023 | admin123@.com | 8.0.32     |
+----+----------+-----------+----------+---------------+------------+
where型子查询
（把内层查询结果当作外层查询的比较条件）	
mysql> select * from users where id in (select id from users where id<10);
+----+----------+-----------+----------+---------------+
| id | username | password  | qq       | email         |
+----+----------+-----------+----------+---------------+
|  2 | admin    | admin321  |  75757575 | 31166@qq.com  |
|  3 | root     | root      |  1133333 | 30099@qq.com  |
|  4 | gyar     | adminroot |   878787 | admin@qwe.com |
|  5 | adminer  | 443322114  | 21987321 | 123408@ad.com |
|  6 | admin    | password  |  2931023 | admin123@.com |
+----+----------+-----------+----------+---------------+
5 rows in set (0.00 sec)
from型子查询 
	(把内层的查询结果供外层再次查询)				
mysql> select * from(select username,password from users) as ctt where username="admin";
+----------+----------+
| username | password |
+----------+----------+
| admin    | admin321 |
| admin    | password |
+----------+----------+
2 rows in set (0.00 sec)
mysql> select * from(select id,username,password from users) as ctt where id>3;
+----+----------+-----------+
| id | username | password  |
+----+----------+-----------+
|  4 | gyar     | adminroot |
|  5 | adminer  | 443322114  |
|  6 | admin    | password  |
+----+----------+-----------+
mysql> select * from(select id,username,password from users) as ctt where username="admin";
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  2 | admin    | admin321 |
|  6 | admin    | password |
+----+----------+----------+ 
exists型子查询
	（把外层查询结果拿到内层，看内层的查询是否成立）
mysql> select * from users where exists(select * from users where id>3);
+----+----------+-----------+----------+---------------+
| id | username | password  | qq       | email         |
+----+----------+-----------+----------+---------------+
|  2 | admin    | admin321  |  75757575 | 31166@qq.com  |
|  3 | root     | root      |  1133333 | 30099@qq.com  |
|  4 | gyar     | adminroot |   878787 | admin@qwe.com |
|  5 | adminer  | 443322114  | 21987321 | 123408@ad.com |
|  6 | admin    | password  |  2931023 | admin123@.com |
+----+----------+-----------+----------+---------------+
union联合查询
注意两边的列数要一样不然会报错，可以用数字子母等占位
mysql> select * from users union select 1,2,3,4,5 from nasa;
+----+----------+-----------+----------+---------------+
| id | username | password  | qq       | email         |
+----+----------+-----------+----------+---------------+
|  2 | admin    | admin321  |  75757575 | 31166@qq.com  |
|  3 | root     | root      |  1133333 | 30099@qq.com  |
|  4 | gyar     | adminroot |   878787 | admin@qwe.com |
|  5 | adminer  | 443322114  | 21987321 | 123408@ad.com |
|  6 | admin    | password  |  2931023 | admin123@.com |
+----+----------+-----------+----------+---------------+
5 rows in set (0.00 sec)
当然也是可以这样
mysql> select id,username,password from users union select naid,password,(select version()) from nasa;
+----+-------------+-----------+
| id | username    | password  |
+----+-------------+-----------+
|  2 | admin       | admin321  |
|  3 | root        | root      |
|  4 | gyar        | adminroot |
|  5 | adminer     | 443322114  |
|  6 | admin       | password  |
|  1 | admin123    | 8.0.32    |
|  2 | ad65123     | 8.0.32    |
|  3 | ad6rewt5123 | 8.0.32    |
|  4 | wet34t5123  | 8.0.32    |
+----+-------------+-----------+
mysql> select id,username,password from users union select naid,password,(select version()) from nasa limit 3,10;
+----+-------------+----------+
| id | username    | password |
+----+-------------+----------+
|  5 | adminer     | 443322114 |
|  6 | admin       | password |
|  1 | admin123    | 8.0.32   |
|  2 | ad65123     | 8.0.32   |
|  3 | ad6rewt5123 | 8.0.32   |
|  4 | wet34t5123  | 8.0.32   |
+----+-------------+----------+
6 rows in set (0.00 sec)