#显示当前所有的数据库
show databases
#支持模糊查询、_表示占位符，加反斜杠转义
show databases like "db%";
show databases like "db/_%";
#显示当前所有的数据库
use dbName;
#显示当前数据库所有表、支持模糊查询
show tables like "t%"
#描述表的结构
desc\describe tableName;
#描述表的结构
show columns from t_user;
#修改数据库编码形式为gbk
alter database dbName default charset gbk;
#显示创建表的信息
show create database dbName;
#创建一个表
create table t_hello(
id int primary key auto_increment,（主键，自增）
gender varchar(20) not null
);
#显示表格信息 \G在cmd窗口旋转90度显示 默认为\g
show create table tname[\G];
#两种表格的重命名的方法
rename table t_hello to t_new;
alter table t_hello rename to new t_new;
#给表格创建一个名字字段
alter table t_new add name varchar(50) not null ;
#在表的第一行加入test字段
alter table t_new change name test varchar(30) default 'hello' first;
#在id后面加入test字段
alter table t_new modify test varchar(80) default 'hehe' after id;
#删除test字段
alter table t_new drop test;
#创建表
CREATE TABLE employee (
id int PRIMARY KEY auto_increment,
name VARCHAR(50) not NULL,
gender CHAR(10) DEFAULT "男",
tel varchar(20) DEFAULT '110',
address varchar(255),
qq varchar(30),
age int DEFAULT 18,
deptId int
)
插入数据
INSERT INTO employee(id, name, gender, tel, address, qq, age, deptId) VALUES (DEFAULT, '张三', '男', '110', '郑州', '110', '18', '1');
INSERT INTO employee(id, name, gender, tel, address, qq, age, deptId) VALUES (DEFAULT, '李四', '女', '110', '郑州', '120', '18', '2');
INSERT INTO employee(id, name, gender, tel, address, qq, age, deptId) VALUES (DEFAULT, '王五', '男', '130', '杭州', '130', '20', '3');
INSERT INTO employee(id, name, gender, tel, address, qq, age, deptId) VALUES (DEFAULT, '赵六', '女', '140', '广州', '140', '23', '4');
INSERT INTO employee(id, name, gender, tel, address, qq, age, deptId) VALUES (DEFAULT, '钱七', '女', '15000', '兰州', '150', '33', '5');
INSERT INTO employee(id, name, gender, tel, address, qq, age, deptId) VALUES (DEFAULT, '王八', '女', '16', '徐州', '180', '38', '2');
INSERT INTO employee(id, name, gender, tel, address, qq, age, deptId) VALUES (DEFAULT, '老牟', '男', '110120', '兰州', '110', '33', '2');
INSERT INTO employee(id, name, gender, tel, address, qq, age, deptId) VALUES (DEFAULT, '老李', '男', '200000', '郑州', '205845', '38', '3');
INSERT INTO employee(id, name, gender, tel, address, qq, age, deptId) VALUES (DEFAULT, '老增', '女', '2356', '长沙', '256789', '29', '3');
INSERT INTO employee(id, name, gender, tel, address, qq, age, deptId) VALUES (DEFAULT, '张三', '男', '110', '郑州', '110', '18', '4');
打印所有信息
SELECT * FROM employee
create table department(
id int PRIMARY KEY auto_increment,
name varchar(50) not null UNIQUE,
describle text
)
INSERT INTO department (id, name, describle) VALUES ('1', '总经理班', '管人的');
INSERT INTO department (id, name, describle) VALUES ('2', '研发部', '开发软件的');
INSERT INTO department (id, name, describle) VALUES ('3', '人事部', '人力资源管理');
INSERT INTO department (id, name, describle) VALUES ('4', '财务部', '发钱的');
INSERT INTO department (id, name, describle) VALUES ('5', '市场部', '调研的');
SELECT * FROM department
#交叉查询,产生笛卡尔积现象
SELECT *
FROM employee,department
select * from employee cross join dept;
内连接（两种方式）
SELECT *FROM employee,department WHERE employee.deptid = department.id
SELECT e.*,t.name as deptname, t.describle deptdesc from employee as e inner join department t on (e.deptid = t.id)
外连接（outer join）
左外连接：
select * from employee left [outer] join department on(employee.deptid = department.id);
右外连接：
select * from employeeright [outer] join department on(employee.deptid = department.id);
#自然连接（nature join）
#自连接 #外键在约束自身的某个字段（如主键）
#查询id=3的部门名称
SELECT name
from department
WHERE id = (SELECT deptId from employee where id=3);
SELECT d.name
from employee e ,department d
WHERE e.deptid=d.id and e.id = 3
SELECT d.name
from employee e INNER JOIN department d on(e.deptid=d.id)
where e.id = 3;
#创建表的同时创建外键：
create table tName([constraint fk_xx_xx] foreign key(deptid) references dept(id) [on delete cascade|restrict| set null on update restrict])
#为已经存在的表创建外键
alter table add tName [constraint fk_xx_xx] foreign key(deptid) references dept(id) [on delete cascade|restrict| set null on update restrict]
#删除外键
alter table drop 外键名称;
# mysql中的函数：
#获取当前时间
select now ();
#获取当前所在库
Select database();
#获取mysql的版本
SELECT VERSION();
#获取employee的名字的编码长度，其中在utf-8中中文占三位，jbk占两位
SELECT LENGTH(name),name from employee;
#获取字符串的长度
SELECT CHAR_LENGTH("五毛钱")
#去空格（只能去掉两端的空格）
SELECT TRIM(" gbngj ")
#截取长度 （第一个表示索引，索引从一开始，第二个表示截取的长度 与javascript中的substr类似）
SELECT SUBSTR("sdyugbsuyfhvuduviu",2,8);
SELECT SUBSTRING("sdyugbsuyfhvuduviu",2,8);
#显示字符的ASCII码
SELECT ASCII("a")
#将字符串全部大写或者小写
SELECT UPPER("hfvjhH"),LOWER("FBJFfkdj")
#获取当前时间的年
SELECT year(NOW())
#获取当前的年月日
SELECT curdate();
#获取当前时间的时分秒
SELECT CURTIME();
#获取当前的时间
SELECT NOW()
#日期格式成字符串
SELECT DATE_FORMAT(NOW(),"%Y年%m月%日 %H：%i：%s");
#创建视图
CREATE VIEW v_all_emp as SELECT * FROM employee
#两个表通过外键连接创建视图
CREATE VIEW v_all as SELECT employee.*,department.name as deptname, department.describle deptdesc from employee,department where employee.deptId=department.id
#把表中部分内容展示出来（把address,age 显示出来）
create view v_give_you as SELECT address,age from employee;
#查询视图
SELECT * from v_all
#删除视图
drop view v_all
#在创建表的时候创建索引：
create table t_today(
id int primary key auto_increment,
name varchar(255) not null,
age int not null,
index my_index(name(255))
)
#alter 修改表，添加索引
alter table employee add index indexName(name(255));
#create关键字创建索引
create index today_index on employee(fieldn(length));
#显示当前表中的索引
show index from employee
# 为表添加索引
create index i_name on employee(name(50),age);
# 删除索引
drop index today_index on employee;
网友评论