MySQL(一)

第三章  基本的select 语句                    //  尚硅谷笔记

1.SQL 的分类

DDL :      定义

              Create    \      alter       \             drop       \      rename         \             truncate

DML:      操作

              Insert     \      delecte   \             update \        select

DCL :      控制

              Commit         \      rollback         \      save point \    grant      \        revoke

2 .sql 规范

3. 注释

4.导入现有的数据表 表的数据
	方式一	soure 文件的全路径名
		Ef: soure d:\我的文件.sql
	方式二	基于具体的图形化界面的工具可以导入数据
	比如： SQL 有中选择 “工具“ ----”执行sql 脚本“----”选中xxx.sql即可“
	
5 最基本的select 语句
	Select 字段1 字段2	… from  表名
	Select 1+1  ,  1*3;
	
From dual;	# dual: 伪类

6  *： 表中的所有字段（或列）	  
Select * from 表名


show databases ;
use ckgl;
show table status ;
show tables;


# 6 列的别名
# as 全程: alias(别名)
select * from employee;
select * from supplier;
select * from employee;

use xskc;
show table status ;
select * from s;
select * from sc;

select sno , sname as snoes,sname from s;
select * from s;

# 7 去除重复行
# 查询员工表中一共有那些部门
select * from s;
select * from sc;
select distinct sno from sc;

# 错误  没有实际意义
select distinct sno,grade from sc;

# 空值参与运算
# 空值：null
# null不等同于 0 ，‘’ ， ‘null’

#3 空值参与运算
use ckgl;
select * from `employee`;
select enum , salary '月工/资' from employee;

#9 着重号
select * from `employee`;
select enum as '姓名', wnum as w, salary as '月工资'
from employee;

# 10 查询常数
select '吉林师范大学' ,enum from employee;

# 11 显示表结构
describe employee;
desc employee;

insert into employee value ('小明','07',15000);

# create table departments(file char(10),type char,null bool,key)

# 12 过滤数据

select * from employee;
select * from employee where salary>4000 and wnum=05;

# 12 过滤数据

select * from employee;
select * from employee where salary>4000 and wnum=05;

# 练习
# 1 查询.....
select enum,salary*12 from employee;

# 2. 去除重复的wnum以后的数据
select distinct wnum from employee;
select distinct wnum from employee where salary>5000;

# 3 查询enum号为 z6 的员工的salary

select enum,salary from employee where enum=11;

# 4 显示表 employee 的结构,并查询其中的全部数据

describe employee;
select * from employee;

# 第四章 运算符

# 1 算数运算符
#     +     -   *     / div   % mod

# 整形  浮点型
select 100,100+0,100-0,100+50,100-35.5,100/1.0,100/2,100/3 from dual;
# 隐式转换
select 100 + '10' from dual;
select 100+'a' from dual; # 此时a==0
select 100+null from dual;
show table status ;

# 取模运算:  %  mod
select 12%3,12%5
from dual;

# 比较运算符
# --------------
# =       <=>     <>(!=)      <   <=      >   >=

select 1=2,1!=2,1='a',0='a','a'='a','a'='b'
from dual;
# 两边都是字符串的话，则按照ansi码

select * from employee;
select enum,salary from employee where salary>=5550;

# <=> 安全等于

select 1 <=> 1,1<=>NULL,NULL<=>NULL from dual;

alter table employee add commission_pct int after salary;
select salary ,commission_pct from employee where commission_pct<=>NuLL and salary<5000;

# 2.2
# is null     is not null     isnull

select * from employee;
select salary ,commission_pct from employee where commission_pct is not NuLL and salary<5000;

# least()     greatest()
select least('g','b','t','m'),greatest('g','b','t','m') from dual;

select least(first_name, last_name),least(length(first_name, length(last_name()))) from dual;

# between...and

# 查询工资在6000 到 8000 的员工信息
select * from employee;
select employee.enum,employee.salary from employee
where salary between 5000 and 8000;
# where salary>=5000 && salary<=8000;
# where salary>=5000 and salary<=8000;


# in (set)      not in (set)

# 练习 查询部门为10 20 30 部门的员工
select * from employee where wnum=01 or wnum=05 or wnum=03 ;
select * from employee where wnum in (01,02,03);
select * from employee where wnum not in (01,02,03);


# like   模糊查询
#     %   : 代表不确定个数的字符
select * from employee where enum like '%i%';

#   查询enum 中 包含字符‘a’ 且 包含字符‘e’的信息
select enum from employee where enum like '%a%' and enum like '%e%';

#   查询enum 中 包含字符‘a’ 或 包含字符‘e’的信息
select enum from employee where enum like '%a%' or enum like '%e%';

select * from employee where enum like '%a%e%' or enum like '%e%a%';

#     查询 enum 中包含字符‘a’ 且 包含字符‘e’
select * from employee where enum like '%a%' and enum like '%e%';

select * from employee;
show table status ;

#     以字符 ‘k’ 开头
select * from employee where enum like "k%";

#     以字符 ‘i’ 在第三个位置
# _ : 代表一个不确定的字符
select * from employee where enum like '__a%';

# 查询第二字符值 _ 且 第3个字符是a

select * from employee where enum like '_\_a%';

select * from employee where enum like '_\_%';

# 或者 (了解)
# select * from employee where enum like '_$_a' escape '$';


select * from employee where enum like '%z%';

#     regexp  \  rlike : 正则表达式

select 'asdajssjlsj' regexp 'sasia' ,'hdkahuhkbjagsudg' regexp 'hk'  from dual;

#         .       []
select  'asguigussj' regexp 'gu.gu' , 'hahsdjnijsdalssdionhdia' regexp '[ah]';

# 逻辑运算符
# or || and  &&   not    !   xor

select * from employee where enum like '%s%' xor employee.salary>5000;

# 4 位运算符

# 4 位运算符

# &   |   ^   ~   >>  <<

select 12 & 5 ,12 | 5 , 12 ^ 5  from dual;