1. 数据库的操作
1. 打开数据库
-uroot:root为账号 -p123456：123456为密码 -h127.0.0.1：为地址
mysql -uroot -p123456 -h127.0.0.1
2. 退出数据库
exit
\q
quit
3. 注释符
#
/** */
--
4. 数据库的增删改查
增： create database mydata;
删： drop database mydata;
改： alter database mydata charset utf8;
查： show databases;
5. 查询表结构
show databases; #显示有什么数据库
use mydb; #使用mydb数据库
desc users; # 查询表结构
6. 查询
select password from users;
2. 表的操作
2.1创建表
create table users(id int(7) UNSIGNED AUTO_INCREMENT,username varchar(30) not null,password varchar(30) not null,email varchar(40), PRIMARY KEY(id))ENGINE=InnoDB default charset=utf8;
2.2修改表
- 修改表名
alter table users rename newusers;
- 修改表字段
alter table users modify username char(30) not null;
alter table users add qq int(10) unsigned not null;
alter table users add address varchar(100) not null after email;
alter table users add address varchar(100) not null first;
- 删除表字段
alter table users drop address;
- 修改表的存储引擎
常见的数据库存储引擎
InnoDB myisam
alter table users engine=myisam;
3.对表内容的操作
增：
insert into users(username,password,email,qq)values('xiaoming','123456','[email protected]','1234');
insert into users(username,password,email,qq)values('admin','123456','[email protected]','1234'),('admin1','123456','[email protected]','123456');
查
select * from users;
select username,password from users;
select username,password from users where id=3;
删除
delete from users where id=1;
改：
update users set password='qwert' where id=2;
update users set password='qwert',email='[email protected]' where id=2;
4.数据库查询
- 查询所有字段
select * from users;
- 条件查询
select * from users where id>1;
select * from users where id in (2,4);
select * from users where id between 1 and 10; #查询id1-10
select * from users where id not between 1 and 10;
select distinct username,password from users; #关键字查询
- 修改别名
select username as name,password as pwd from users;
- 模糊查询
select * from users where username like "%m%";
select * from users where username like "m%";
select * from users where username like "%m";
select * from users where username like "m_";#下划线表示一个字符
- and、or查询:and 优先级高于or
select true or false; #1
select true adn false; #0
select * from users where username='xiaohong' and password="qwert";
select * from users where id>20 and username='xiaohong' or password="qwert";
- count 返回行数
select count(*) from users;
- sum()求和
select sum(id) from users;
- avg:平均值、max:最大值、min:最小值
select avg(id) from users;
select max(id) from users;
select min(id) from users;
- group分组
需设置my.ini sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
select * from users group by username;
- LIMIT限制查询次数
select * from users limit 2,10; #从2开始取10个
- 子查询
select * ,(select version()) as version from users; #查询的时候查询版本信息。
select * ,(select user()) as user from users; #查询该数据库的用户信息
select * ,(select database()) as dat from users; #查询当前的数据库为什么
where型子查询
select * from users where id in (select id from users where username="admin");
from型，把内层的查询结果供外层再次查询
agev_a 为查询表的别名
select * from (select username,age from users) as agev_a where age>20;
exists型
如果后面存在，则执行前面的语句，否则为空
select * from users where exists (select * from users where id>3);
- 联合查询
select id,password,username from users union select * from news;