MySQL命令记录2

查询

简单查询

查询语句

select 列名 from 表名 [where 条件];

select 100;					-- 简单查询
select 1+1;					-- 查表达式
select 100%98;
select now();				-- 查函数
select varsion();

select 100+100;			-- 查运算
select 100+'100';			-- '100' 转换为 100
select 100+'a';				-- 'a' 转换为 0
select null+100;			--一方有null 结果一定是null
select concat('i','love','mysql');				-- 拼接字符串

-- 查 所有 的信息
select uid,username,password from users;
select * from users;
-- 查 指定列 的信息
select username,password from users;

-- 别名 查询		列名 as '别名'
select username as '用户',password as '密码' from users;
-- as 可省略
select username '用户名' from users;
-- ''可省略  别名中有空格 必须使用
select username 用户名,password '密  码' from users;

去重

-- 去重 
-- distinct 列名
-- 如果distinct 后面有多列 必须多列完全一致时 才会去重
select distinct username from users;
select distinct username,password from users;

-- 查询时计算		uid 整列 都+100
select uid+100 'uid' from users;

条件查询

-- 条件查询 
-- select  列名 from 表名 where 条件;
select * from users where true;
select * from users where false;
select * from users where true and false;
select * from users where true or false;
select * from users where 1=1;
select * from users where 1-2;

-- 查询密码是123的用户 
SELECT * FROM users WHERE PASSWORD = '123';

-- 查询 uid 是 3 4 5 的用户
select * from users where uid >=3 and uid <=5;
select * from users where uid between 3 and 5;		-- 必须 
select * from users where uid =3 or uid = 4 or uid = 5;
select * from users where uid in(3,4,5);

-- 查询 uid 不是3的所用信息 
select * from users where uid !=3;
select * from users where uid <>3;
select * from users where uid not(uid=3);

模糊查询

列名 like

a% a开头

%a a结尾

%a% 包含a就可以

_ 一个"_"代表一个任意字符

-- 查询所有a开头的用户名 
select username from users where username like 'a%';
-- 查询用户名是5个字符的用户信息
select * from users where username like '_____';
-- 查询用户名第2个字符是a的用户的信息
select * from users where username like'_a%';

空值查询

-- 查询密码是null的用户信息
select * from users where password is null;
-- 查询密码不是null的用户信息
select * from users where password is not null;
select * from user where not(password is null);

排序

排序 都是对结果进行排序 所以写在sql的最后

order by 列名 asc/desc

asc:升序排列 不写默认升序

desc:降序排列

-- 查询所有用户 按照uid 降序排列 
select * from users order by uid desc;
-- 查询id为  3 5 7的用户信息 按照uid 降序排列 
select * from users where uid in (3,5,7) order by uid desc;

聚合函数

count(列名)

sum(列名)

avg(列名)

max(列名)

min(列名)

聚合函数忽略null值

-- 计算行数
SELECT COUNT(username) FROM users;
SELECT COUNT(PASSWORD) FROM users;
SELECT COUNT(*) FROM users;
-- 求和
SELECT SUM(uid) FROM users WHERE uid IN (3,5,7);
SELECT SUM(uid) FROM users WHERE uid IN(10,11);
-- 将 password 转换为 整数
ALTER TABLE users MODIFY PASSWORD INT ;
SELECT SUM(PASSWORD) FROM users WHERE uid IN(10,11,8);
-- 求平均值
SELECT AVG(PASSWORD) FROM users WHERE  uid IN(3,4,10);
-- 最大 password 和 最小 password
SELECT MAX(PASSWORD),MIN(PASSWORD) FROM users;

新建表并添加数据