526互联

数据库的基本操作

发布时间 2023-09-03 22:59:58作者: QxrwQ

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修改表

  1. 修改表名
alter table users rename newusers;
  1. 修改表字段
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;
  1. 删除表字段
alter table users drop address;
  1. 修改表的存储引擎
    常见的数据库存储引擎
    InnoDB myisam
alter table users engine=myisam;

3.对表内容的操作

增：

insert into users(username,password,email,qq)values('xiaoming','123456','qq@qq.com','1234');
insert into users(username,password,email,qq)values('admin','123456','qq@qq.com','1234'),('admin1','123456','qq@qq.com','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='root@qq.com' where id=2;

4.数据库查询

  1. 查询所有字段
select * from users;
  1. 条件查询
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; #关键字查询
  1. 修改别名
 select  username as name,password as pwd from users;
  1. 模糊查询
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_";#下划线表示一个字符
  1. 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";
  1. count 返回行数
select count(*) from users;
  1. sum()求和
select sum(id) from users;
  1. avg:平均值、max:最大值、min:最小值
select avg(id) from users;
select max(id) from users;
select min(id) from users;
  1. 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;
  1. LIMIT限制查询次数
select * from users limit 2,10;  #从2开始取10个
  1. 子查询
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);
  1. 联合查询
select id,password,username from users union select * from news;