-- 如果該數(shù)據(jù)庫不存在，創(chuàng)建該數(shù)據(jù)庫
create database if not exists 數(shù)據(jù)庫名;-- 設置指定數(shù)據(jù)庫的字符集為gbk/uft8
alter database 數(shù)據(jù)庫名 character set gbk/uft8;-- 如果該數(shù)據(jù)庫存在刪除該數(shù)據(jù)庫
drop database if exists 數(shù)據(jù)庫名 ; -- 查看所有數(shù)據(jù)庫
show databases; -- 使用數(shù)據(jù)庫
use 數(shù)據(jù)庫名; -- 在當前數(shù)據(jù)庫下查看所有表格
show tables; -- 查看其他數(shù)據(jù)庫的全部表格
show tables from 數(shù)據(jù)庫名; -- 查看當前數(shù)據(jù)庫名
select database(); -- 查看當前MySQl登陸的用戶
select user(); -- 查看當前數(shù)據(jù)庫的版本
select version(); -- 查看表結構
desc 表名; -- 查看數(shù)據(jù)庫的字符集
show variables like '%character%';
show variables like '%char%';
show variables like 'collation%';-- 查看數(shù)據(jù)庫的引擎
show engines;
-- 創(chuàng)建員工表，先在數(shù)據(jù)庫里面創(chuàng)建該表，為了后面做測試用
create table dept(department_id int primary key auto_increment, -- 部門編號dname varchar(14) , -- 部門名字location varchar(13) -- 地址
);create table employee(employee_id int primary key auto_increment, -- 員工編號c_name varchar(20)， -- 員工中文名e_name varchar(20)， -- 員工英文名hiredate date, -- 雇傭日期，入職日期salary int, -- 薪水comm int, -- 獎金job_id int, -- 所屬工種department_id int not null, -- 部門編號manager_id int -- 直接領導編號
);-- 表中插入數(shù)據(jù)
insert into dept values(10,'財務部','北京');
insert into dept values(20,'研發(fā)部','上海');
insert into dept values(30,'銷售部','廣州');
insert into dept values(40,'行政部','深圳');
insert into dept values(50,'人力資源','惠州');
-- 表中插入數(shù)據(jù)
insert into employee values(1,'劉一','liuyi','1980-12-17',7902,800,1,10,2);
insert into employee values(2,'陳二','chener','1981-02-20',7698,1600,3,30,3);
insert into employee values(3,'張三','zhangsan','1981-02-22',7698,1250,5,30,4);
insert into employee values(4,'李四','lisi','1981-04-02',7839,2975,2,20,5);
insert into employee values(5,'王五','wangwu','1981-09-28',7698,1250,1,40,0);
insert into employee values(6,'趙六','zhaoliu','1981-05-01',7839,2850,3,50,5);-- 更新數(shù)據(jù)：
update employee set c_name ="劉一一" where id=1;-- 刪除數(shù)據(jù)：
delete from employee where id=1;-- 查詢數(shù)據(jù)
select * from employee where id in(1,4);-- 修改表名
alter table 舊表名 rename to 新表名;-- 修改表注釋
alter table 表名 comment '系統(tǒng)信息表';-- 修改字段類型和注釋
alter table 表名 modify column 字段名 varchar(20) COMMENT '新的注釋';-- 設置字段允許為空
alter table 表名 modify column 字段名 varchar(255) null COMMENT '新注釋';-- 增加一個字段，設好數(shù)據(jù)類型，且不為空，添加注釋
alert table 表名 add 字段名 varchar(255) not null comment '新注釋'; -- 增加非空、自增主鍵
alter table 表名 add 字段名 int(5) not null auto_increment ,add primary key (aid); -- 修改字段名字(要重新指定該字段的類型)
alter table t_app change 原字段名 新字段名 varchar(20) not null;-- 刪除字段
alter table 表名 drop 字段名; -- 在某個字段后增加字段
alter table 表名 add column 新字段名 int not null default 0 after 字段名；-- 調整字段順序
alter table employee change num num int not null after departmen_id ; -- 表的刪除
drop table 表名 ;--復制表的結構，不復制表數(shù)據(jù)
create table 新表名 like 舊表名;--復制表的結構，同時也復制表數(shù)據(jù)
create table 新表名 select * from 舊表名;--只復制部分表結構和對應的數(shù)據(jù)，并且?guī)ШY選條件
create table user1 select id, name,salary from user where salary>3000;--僅復制部分字段
create table user2 select id, name from user;
表約束
NOT NULL： 非空約束
DEFAULT： 默認，用于保證該字段有默認值。
PRIMARY KEY： 主鍵約束
UNIQUE: 唯一約束
CHECK： 檢查約束
FOREIGN KEY：外鍵約束。
create table student (
id int,
name varchar(20) ,
gender char(1) ,
seat int,
age int,
class_id int, PRIMARY KEY(id), -- 主鍵
UNIQUE (seat), -- 唯一鍵
CHECK(gender="男" or gender="女"),-- 檢查
FOREIGN KEY (class_id) REFERENCES class (id) -- 外鍵
)
注釋：這些約束在你創(chuàng)建表和修改表的時候都可以使用。
表查詢
distinct(去重)
limit(分頁查詢)
offset(跳過多少條)
UNION 和 UNION ALL(聯(lián)合查詢)
like(模糊查詢)
where、between、in、or、and條件關鍵字
order by (asc升序、desc降序排序)
group by (分組查詢)
having 關鍵字
case(流程控制)
-- distinct(去重)
select distinct 字段名 from 表名;-- limit(初始記錄行的偏移量是 0(而不是 1),第一個參數(shù)指定第一個返回記錄行的偏移量，第二個參數(shù)指定返回記錄行的最大數(shù)目。)
select * from 表名 limit 5,10; -- 檢索記錄行6-15-- offset(跳過多少條)
selete * from employee limit 2 offset 1;
+----+----------+-------+--------------+------+
| 2 | lisi | 12000 | 40 | 90 |
| 3 | wangwu | 0 | 50 | 0 |
+----+----------+-------+--------------+------+
-- 注意：
-- 1.數(shù)據(jù)庫數(shù)據(jù)計算是從0開始的
-- 2.offset X是跳過X個數(shù)據(jù)，limit Y是選取Y個數(shù)據(jù)
-- 3.limit X,Y 中X表示跳過X個數(shù)據(jù)，讀取Y個數(shù)據(jù)--union 和union all（union all是直接連接，取到得是所有值，記錄可能有重復 union 是取唯一值，記錄沒有重復。）-- UNION 的語法如下：
[SQL 語句 1]
UNION
[SQL 語句 2]-- UNION ALL 的語法如下：
[SQL 語句 1]
UNION ALL
[SQL 語句 2]-- UNION全連接查詢，把部門表和員工表的所有數(shù)據(jù)都查出來，若有兩個表都有匹配數(shù)據(jù)的就顯示匹配數(shù)據(jù)，若其中有一個表在另一個表中沒有匹配數(shù)據(jù)的輸就顯示為null
select e.ename,d.dnameFROM employee e left JOIN dept dON e.department_id= d.department_id
UNION
select e.ename,d.dnameFROM employee e right JOIN dept dON e.department_id= d.department_id;
+--------+----------+
| ename | dname |
+--------+----------+
| 劉一 | 財務部 |
| 陳二 | 銷售部 |
| 張三 | NULL |
| 李四 | 研發(fā)部 |
| 王五 | 行政部 |
| 趙六 | 人力資源 |
+--------+----------+-- 模糊查詢：like，%標識匹配任意哥字符，_表示匹配一個字符
-- 查詢employee表里面名字含有豪字的員工的全部信息
select * form employee where c_name like '%豪%';-- 查詢員工名中第三個字母為a，第五個字母為b的員工信息;
select * from employee where c_name like '__達_法%';-- 當查詢的信息信息看里面還有_這樣的特殊字符;
select * from employee where c_name like '__%';-- 查詢工資在5000到6000之間的員工信息;
select * from employee where salary between 5000 and 6000;-- in、or、and關鍵字
select * from dept where location in ('北京','上海');
select * from dept where location ='北京' or location ='上海' ;
select * from employee where (department_id=30 or department_id=40) and salary >3000;-- 選擇工資不在3000到5000的員工的姓名和工資，按工資降序
select name, salary, department_id from employees where salary not between 3000 and 5000 order by salary desc;-- 查詢每個部門的員工個數(shù)
select count (*) , department_ id from employee group by department_ id;-- 給30號部門的增加500，40號部門增加1000，50號部門增加1500
select *, (case department_idwhen 30 thensalary+500when 40 thensalary+1000when 50 thensalary+1500else salaryend) '漲后工資'
fromemployee;-- 顯示員工的薪資等級
select *, (casewhen salary >=7900 then'高薪資'when salary >=7800 then'中等薪資'when salary >=7700 then'低薪資'else'太難了'end) '薪資等級'
fromemployee
order by salary desc;-- 語法格式
select 字段1,字段2,字段3 from 表名 [where 篩選條件] [group by 分組] [having 篩選條件1] [order by 排序列表]
mysql字符串函數(shù)
concat()：拼接字符串
substr()：截取字符串
instr()：獲取子串第一次出現(xiàn)的索引
lpad()：左邊以指定字符填充到指定長度
rpad()：右邊以指定字符填充到指定長度
upper()：轉換為大寫
lover()：轉換為小寫
replace()：替換函數(shù)
length()：獲取字節(jié)長度
trim()：去掉字符串前后空格
-- 將英文名全部轉換大寫和全部轉換為小寫，然后進行拼接。
select concat(upper(e_name) ,lower(e_name)) from employee;-- substr，注意:索引從1開始，截取從指定索引處后面所有字符
select substr("歡迎關注非科班的科班，帶你一起提升代碼內功',7) str ;-- 從指定索引截取指定長度的字符串substr(str,num1,num2)第二個參數(shù)時索引、第三個參數(shù)是指定的長度。
select substr('歡迎關注非科班的科班，帶你一起提升代碼內功',5,6) str;-- 姓名中首字符大寫，其他字符小寫然后用_拼接，顯示出來
SELECT CONCAT (UPPER (SUBSTR(last_ name,1,1)),'_' ,LOWER (SUBSTR(last_ name,2))) out_put
FROM employees;-- instr返回子串在指定字符串第一次出現(xiàn)的素引，如果找不到返回0
select instr('歡迎關注非科班的科班，帶你一起提升代碼內功', '科班') as str;-- length獲取字符串長度、trim()去掉字符串前后的空字符串
select length(trim(" 非科班的科班 ")) as str; -- lpad用指定的字符實現(xiàn)左填充指定長度
select lpad('非科班的科班',9,'*') as str;-- rpad用指定的字符實現(xiàn)右填充指定長度
select rpad('非科班的科班',9,'*') as str;-- replace 替換
select replace('非科班的科班', '科班','javaboy') as str;
-- select后面的子查詢
-- 查詢每個部門員工個數(shù)
SELECT d.*,(SELECT count(*)FROM employee bWHERE b.department_id = d.department_id) AS 員工個數(shù)
FROM dept d;-- 查詢員工號等于3的部門名稱
SELECT (SELECT a.dname FROM dept a, employee b WHERE a.department_id = b.department_id AND b.employee_id = 3) AS 部門名;-- from后面的子查詢
-- 查詢每個部門平均工資的工資等級
-- （1）先查詢每個部門平均工資
SELECTdepartment_id,avg(a.salary)
FROM employee a
GROUP BY a.department_id;-- （2）然后是查詢薪資等級表
SELECT *
FROM job_grades;-- （3）將上面2個結果連接查詢，篩選條件:平均工資 between lowest_sal and highest_sal;
SELECTt1.department_id,avg_salary AS '平均工資',t2.grade_level
FROM (SELECTdepartment_id,avg(a.salary) avg_salaryFROM employees aGROUP BY a.department_id) t1, job_grades t2
WHEREt1.avg_salary BETWEEN t2.lowest_sal AND t2.highest_sal;-- where和having后面的子查詢
-- 查詢誰的工資比javaboy的高？
-- （1）查詢lisi的工資
SELECT salary FROM employee WHERE e_name = 'lisi';-- （2）查詢員工信息，滿足salary>上面的結果
SELECT *
FROM employee a
WHERE a.salary > (SELECT salaryFROM employeeWHERE e_name = 'lisi');--having后的子查詢
--查詢最低工資大于40號部門最低工資的部門id和其最低工資
-- （1）查詢40號部門的最低工資
SELECT min(salary)
FROM employee
WHERE department_id = 40;--（2）查詢每個部門的最低工資
SELECTmin(salary),department_id
FROM employee
GROUP BY department_id;--（3）③在②的基礎上篩選，滿足min(salary)>①
SELECTmin(a.salary) minsalary,department_id
FROM employee a
GROUP BY a.department_id
HAVING min(a.salary) > (SELECT min(salary)FROM employeeWHERE department_id = 50);
