面试题汇总
mySQL
1.创建数据库
create database xxx;
2.删除数据库
drop database xxx;
3.选择数据库
use xxx数据库;
4.创建数据表
create table xxx表名 if not exists 'xxx表名' (
'id' int(10) unsigned auto_increment,
'account' vachr(50) not null,
'password' varchar(50) not null,
primary key('id')
)engine xxx数据库 default charset=utf8;
5.删除数据表;
drop table xxx数据表;
6.插入数据;
insert into table xxx数据表('id','account','password') values('15','root','root');
7.查询数据;
select id,account,password from user where account='root';
8.where条件子句;
select * from user where account='root';
执行顺序:
select<where<group by<having<order by
9.更新数据
update xxx数据表 set password = '123456' where account = 'root';
10.删除数据;
delete from xxx数据表 where account='root';
11.like子句
select * from user where account like '%t';
12.union连接多个查询
select account from user
union
select account from goods
order by account;
13.排序(升序asc,降序desc,)
select * from user order by u_id asc;
select * from user order by u_id desc;
14.order by 语句根据一个或者多个列对结果集合进行分组;
分组中可以调用count(),sum(),avg()等函数
select u_name,count(*),MAX(u_id) from user order by u_name;
15.连接的使用(内连接inner join,左连接left join 右连接right join)
内连接
SELECT
a.runoob_id, a.runoob_author, b.runoob_count
FROM
runoob_tbl a INNER
JOIN
tcount_tbl b
ON
a.runoob_author = b.runoob_author;
左连接(获取左边表中全部数据,右边表没有数据用null表示):
SELECT
a.runoob_id, a.runoob_author, b.runoob_count
FROM
runoob_tbl a
LEFT JOIN
tcount_tbl b
ON
a.runoob_author = b.runoob_author;
右连接(获取右边表中全部数据,左边表没有数据用null表示):
SELECT
a.runoob_id, a.runoob_author, b.runoob_count
FROM
runoob_tbl a
RIGHT JOIN
tcount_tbl b
ON
a.runoob_author = b.runoob_author;