数据库常见的苏索引:
普通索引:没有什么限制
唯一索引:索引列的值必须唯一
主键索引:是一种特殊的唯一索引,一个表只能有一个主键,不允许有空值
组合索引
全文索引
#从第二行开始的最多五条数据
select * from ****
where age>25
order by Salary desc
limit 2,5
id = 2
查找平均工资位于前5-10名的部门(用到avg group by order by limit)
SELECT device_id as user_infors_example FROM user_profile limit 0,2
select distinct university from user_profile
SELECT device_id , university from user_profile where university = '北京大学'
select device_id ,age,university from user_profile where university like '北京%'
select gpa from user_profile where university = '复旦大学' order by gpa desc limit 0,1
SELECT
COUNT(gender) as male_num,
ROUND(AVG(gpa),1) as avg_gpa
FROM user_profile
WHERE gender = 'male'
select university ,avg(question_cnt) as avg_question_cnt ,avg(answer_cnt) as avg_answer_cnt
from user_profile
group by university
having avg_question_cnt < 5 or avg_answer_cnt < 20
#having 句用于对分组后的结果再进行过滤,
# 结果不去重 union all 结果去重 union 或 直接写在一起 where university = '山东大学' and where gender = 'male'
select device_id,gender,age,gpa
from user_profile
where university = '山东大学'
union all
select device_id,gender,age,gpa
from user_profile
where gender = 'male'
---
select device_id,gender,age,gpa
from user_profile
where university = '山东大学'
union
select device_id,gender,age,gpa
from user_profile
where gender = 'male'
1217
show create table users;
show databases;
use databases_name;(数据库名字)
show tables;
desc tables_name;展示表的结构
------------
mysql:
1. 安装
bilibili 安装视频:(注意没事分号 不是双点号)
download -- MySQL Community (GPL) Downloads » -- MySQL Installer for Windows
123456/123456 mysql 密码 server name:MySQL81
2.使用
dos 环境下的指令使用:区分管理工具下的使用 如navicat...
查看板 mysql -v
开启服务:开启服务:net start mysql81 (后面是服务器的名字)
进入不需密码::mysql -u root -p 未初始 不需密码 有报错:
Access denied for user 'root'@'localhost' (using password: Y
use mysql;
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456':
use mysql;
se changed
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
重新登录:mysql -u root -p
接下来 进入数据库 mysql -u root -p
设置password:123456
()初始的时候 是不需要密码的 : 启动: mysql -u root -p 密码:空密码直接回车登陆
create database test;
use teat;
create table info(name varchar(10),
age int(5));
插入数据 显示i
insert into info(name,age)value('张三','23');
select * from info;
create database test;
use test;
create table test_table;
create database db1;
use db1;
create table use_info;
show databases;(注意结尾 ; 和 s),使用其他table时 注意切换database 即 use或 先创建 database;
show tables;