mysql基础篇

查看数据库版本

#登录到mysql服务端
select version();
#没有登录到mysql服务端
mysql --version
mysql --V

查看当前所有数据库

show databases;

打开指定的库

use 库名;

查看当前库的所有表

show tables;

查看其它库的所有表

show tables from 库名;

创建表

create table 表名(
    id int(4),
    name varchar(10),
);

查看表结构

desc 表名;

查询

基础查询

#查询全部数据
SELECT * FROM students;
#查询指定字段
SELECT stu_id,first_name FROM students;
#别名
SELECT stu_id,first_name,last_name,sex AS '性别' FROM students;
#拼接
SELECT CONCAT(first_name,last_name) AS '姓名' FROM students;
#逗号拼接和判断是否为null
SELECT CONCAT('first_name',',','last_name',',','sex',',',IFNULL(hobby,'football')) FROM students;
#年龄去重
SELECT DISTINCT age FROM students;

条件查询

#>   <   >=   <=   =   <>   and   or
#查询年龄在18到23之间的学生id和姓名
SELECT id,last_name FROM students WHERE age>=18 AND age<=23;
#查询年龄不是在18和23之间，或者成绩大于70的学生信息
SELECT * FROM students WHERE age<18 OR age>23 OR grade>70;
SELECT * FROM students WHERE NOT(age>=18 AND age<=23) OR grade>70;

#like      between and      in      is null
#1.like
#查询名字中包含张的学生信息
SELECT * FROM students WHERE CONCAT(first_name,last_name) AS 'stu_name' LIKE '%张%';
#查询爱好中第一个字符为b第五个字符为e的学生id和成绩
SELECT id,grade FROM students WHERE  hobby LIKE 'b___e%';
#查询姓名中第二个字符为_的学生信息 转义
SELECT * FROM students WHERE last_name LIKE '_$_%' ESCAPE '$';
#2.between and
#查询学生id在20到30之间的学生信息
SELECT * FROM students WHERE id>=20 AND id<=30;
SELECT * FROM students WHERE id BETWEEN 20 AND 30;
#3.in
#查询爱好是football,basketball,sing的学生姓名和爱好
SELECT last_name,hobby FROM students WHERE hobby='football' OR hobby='basketball' OR hobby='sing';
SELECT last_name,hobby FROM students WHERE hobby IN('football','basketball','sing');
#4.is null
#查询没有爱好的学生信息
SELECT * FROM students WHERE hobby IS NULL;
#查询有爱好的学生信息
SELECT * FROM students WHERE hobby IS NOT NULL;
#查询学号为30的学生姓名，成绩和奖学金
SELECT last_name AS '姓名',grade AS '成绩',1000*(1+IFNULL(commission_pct,0)) AS '奖学金' FROM students;