mysql语句大全
mysql 与 mssql 语法十分相似,懂一门另一门也就差不多了,下面来总结 mysql 都有哪些语句。
1、登录 mysql
语法如下:mysql -u用户名 -p用户密码
例:
1)登录本机:mysql -uroot -p123456
2)远程登录:mysql -h192.186.210.69 -uroot -p123456 /*-h后是IP地址*/
2、启动和停止 mysql 服务
启动:net start mysql
停止:net stop mysql
3、创建与删除数据库
创建:CREATE DATABASE 数据库名
删除:drop database 数据库名
4、打开(使用)数据库
use 数据库名
5、创建表
create table tablename(column1 type1 [not null] [primary key],column2 type2 [not null],…)
例:create table employee(id int not null primary, ename varchar(30))
根据已有表创建新表:
1)create table newtable like oldtable (使用旧表创建新表)
2)create table newtable as select column1,column2,… from oldtable definition only
6、删除表
drop table tablename
7、查询
select column1,column2,… from tablename where 条件 order by field1,[field2,…] [ASC | DESC]
例:
select ename,age from employee where age > 25 order by age; -- 按年龄升序排列
select distinct ename,age from employee order by age desc; -- 按年龄降序排列
select ename,age from employee where ename like '%李%'; -- 查找包含“李”的记录
select max(age) from employee; -- 最大值
select min(age) from employee; -- 最小值
select count as totalRecord from employee; -- 统计总数
select sum(age) from employee where age > 25; -- 求和
select avg(age) from employee where age > 25; -- 求平均值
select ename from employee where ename in(select ename from users); -- 子查询
8、插入
insert into tablename(field1,field2,…)values(value1,value2,…)
例:insert into employee(id,ename,age) values(1,'李大海',26);
9、更新
update tablename set field1=value1,field2=value2,… where 条件
例:update employee set age=28 where ename='李大海';
10、删除
delete from tablename where 条件
例:delete from employee where id > 100
11、增加删除列
增加列:Alter table tablename add column columnname type [[after|before] column]
例:Alter table employee add column address varchar(50) before age;
Alter table employee add column address varchar(50) after age;
删除列:Alter table tablename drop column columnname
例:Alter table employee drop column address;
重命名列:Alter table tablename change oldcolumnname newcolumnname type
例:Alter table employee change address useraddr varchar(50);
修改列类型:Alter table tablename modify columnname type
或:Alter table tablename change columnname columnname type
例:Alter table employee modify address char(100);
Alter table employee change address address char(100);
12、重命名表
rename table oldtablename to newtablename
例:rename employee to user;
13、创建删除索引
创建:create [UNIQUE|FULLTEXT|SPATIAL] index indexname on tablename(column1,column2,…)
例:create unique index emp_ename on employee(ename);
删除:drop index indexname
例:drop index emp_ename;
14、添加删除主键
添加:Alter table tablename add primary key(column1,column2,…)
例:Alter table employee add primary key(ename,id);
删除:Alter table tablename drop primary key(column1,column2,…)
例:Alter table employee drop primary key(ename,id)
15、创建删除视图
创建:create view viewname as select statement
例:create view v-employee as select ename,age from employee;
删除:drop view viewname
例:drop view v-employee;