mysql-笔记

limit用法

       一、select * from tablename order by orderfield desc/asc limit position， counter；
       position 指示从哪里开始查询，如果是0则是从头开始，counter 表示查询的个数
       二、取前15条记录：
       select * from tablename order by orderfield desc/asc limit 0,15

       三、表示从3行以后开始(不包括第3行)，按照数据库顺序取10条数据，即检索得到4到13行的数据。

       select * from auth_permission limit 3,10;

       四、LIMIT n 等价于 LIMIT 0,n

left join

• LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行

一条sql语句中可以用多个left join,例如:

select e.last_name,e.first_name,d.dept_name from  employees as e 

left join dept_emp as de on e.emp_no=de.emp_no 

left join departments as d on de.dept_no=d.dept_no;

#解法二:
 select e.last_name,e.first_name,XB.dept_name from employees e left join 
 (select d1.dept_no,d1.dept_name,dept.emp_no from departments d1 
 inner join dept_emp dept on d1.dept_no=dept.dept_no ) AS XB
 on e.emp_no=XB.emp_no

不同写法结果相同,查询的结果可做为一个新表

create 建表

举例:

CREATE TABLE actor(
actor_id smallint(5) primary key,
first_name varchar(45) not null,
last_name varchar(45) not null,
last_update date not null);

题目:创建一个actor_name表，并且将actor表中的所有first_name以及last_name导入该表.

create table actor_name select first_name,last_name from actor

 

insert插入记录

方法1:INSERT INTO tablename (列名…) VALUES(列值);      (列名必须和列值的数一致)

方法2:INSERT INTO tablename SET column_name1 = value1, column_name2 = value2，…;  (一次只能插入一条记录)

eg:INSERT INTO users(id, name, age) VALUES(123, '姚明', 25);

eg2: INSERT INTO users VALUES(123, '姚明', 25);            //向表中所有字段赋值

一次性插入多条数据,INSERT语句中的VALUES后必须每一条记录的值放到一对(…)中，中间使用","分割

eg1:INSERT INTO users (name, age)  VALUES('姚明', 25), ('比尔.盖茨', 50), ('火星人', 600);

eg2:insert into actor values(1,'PENELOPE','GUINESS','2006-02-15 12:34:33'),(2,'NICK','WAHLBERG','2006-02-15 12:34:33')

 

ignore 忽略

有冲突的数据则忽略,不进行插入

insert IGNORE into actor values(3,'ED','CHASE','2006-02-15 12:34:33');

 

alter创建索引(也可用creste创建索引)

1. 添加主键

   1 2	ALTER TABLE tbl_name ADD PRIMARY KEY (col_list); // 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。

2. 添加唯一索引

   1 2	ALTER TABLE tbl_name(表名) ADD UNIQUE index_name索引名 (col_list字段名); // 这条语句创建索引的值必须是唯一的。

3. 添加普通索引

   1 2	ALTER TABLE tbl_name ADD INDEX index_name (col_list); // 添加普通索引，索引值可出现多次。

4. 添加全文索引

   1 2	ALTER TABLE tbl_name ADD FULLTEXT index_name (col_list); // 该语句指定了索引为 FULLTEXT ，用于全文索引。

删除索引：

DROP INDEX index_name ON tbl_name;

ALTER TABLE tbl_name DROP INDEX index_name；

ALTER TABLE tbl_name DROP PRIMARY KEY;

create

CREATE INDEX index_name ON table_name (column_name) 创建一个简单的索引。允许使用重复的值, column_name 规定需要索引的列。

CREATE UNIQUE INDEX index_name  ON table_name (column_name)  创建唯一索引，

如果希望以降序 索引某个列中的值，可以在列名称之后添加保留字 DESC        举例：CREATE INDEX PersonIndex ON Person (LastName DESC) 

强制索引 FORCE INDEX 

SELECT * FROM TABLE1 FORCE INDEX (FIELD1) …

select *from salaries force index (idx_emp_no)  where emp_no=10005   //针对salaries表emp_no字段创建索引idx_emp_no，查询emp_no为10005, 使用强制索引。

ALTER创建外键 

ALTER TABLE 从表   ADD CONSTRAINT 约束名 FOREIGN KEY (关联字段) references 主表(关联字段);

举例：在audit表上创建外键约束，其emp_no对应employees_test表的主键id。

alter table audit add CONSTRAINT  foreign key (emp_no) references employees_test(id)

创建视图create

格式：

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

题目：针对actor表创建视图actor_name_view，只包含first_name以及last_name两列，并对这两列重新命名，first_name为first_name_v，last_name修改为last_name_v

create view actor_name_view as select first_name AS first_name_v,last_name AS last_name_v from actor

删表记录-delete

MySQL中不允许在子查询的同时删除表数据

eg:删除emp_no重复的记录，只保留最小的id对应的记录。

DELETE FROM titles_test WHERE id NOT IN( SELECT * FROM(SELECT MIN(id) FROM titles_test GROUP BY emp_no) as a);

UPDATE 修改表记录（与replace连用）

将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005,其他数据保持不变，使用replace实现

 REPLACE(String,from_str,to_str) 即：将String中所有出现的from_str替换为to_str。

update titles_test set to_date= NULL,from_date = '2001-01-01'  where to_date='9999-01-01'

rename 修改表名

RENAME TABLE <旧表名> TO <新表名>;

concat函数

题目:将employees表的所有员工的last_name和first_name拼接起来作为Name，中间以一个空格区分

写法1:select concat_ws(' ',last_name,first_name)as Name from employees

写法2: select concat(last_name," ",first_name) as Name from employees

group_concat函数

group_concat(X,split)，X表示需要连接的列名，split表示连接的分隔符，默认为逗号；在这里group_concat()聚合函数需要结合group by一起来使用

举例：select dept_no,group_concat(emp_no)from dept_emp group by dept_no

substr函数：截取字符串

 substr(参数1，参数2，参数3)

参数1：列名/字符串

参数2：起始位置，如果为正数，就表示从正数的位置往下截取字符串（起始坐标从1开始），反之如果参数2为负数，那么 表示就从倒数第几个开始截取

参数3：表示截取字符的个数

OFFSET（偏移量）

OFFSET后的数字代表从第几条记录开始返回（默认第一条记录序号为0），也可理解为跳过多少条记录后开始返回。

题目：分页查询employees表，每5行一页，返回第2页的数据

SELECT * FROM employees LIMIT 5 OFFSET 5

EXISTS子查询

EXISTS 子查询找到的提交

NOT EXISTS 子查询中 找不到的提交

题目：使用含有关键字exists查找未分配具体部门的员工的所有信息。

select * from employees where not exists(select emp_no from dept_emp where dept_emp.emp_no= employees.emp_no)