mysql-常用基础命令

dml语句

1.插入记录
insert into tablename (fielde1..2...3) values (value1..2...3);

2.更新记录
update table set fied1=value1 [where condition]

3.删除记录
delete from tablename [where]

4.查询记录
select * from tablename [where]
select count(*) from tablename 查看多少条数据
(1)不重复查询
select distinct * from tablename;

(2)条件查询
select * from tablename where  字段[<,>,=,<=,>=,!= | or , and ]

(3)排序和限制
desc 降序
asc 升序
select * from tablename [where ] order by  字段 desc|asc

(4)子查询
in
not in
=
!=
exists
not exists
select * from tablename where 字段 = (select * from tablename)

(5)记录联合
union    去除重复在合并
union all 结果直接合并

ddl语句

1.创建数据库
create database databasename;

2.查看有哪些库
show databases;

3.进入数据库
use databasesname;

4.创建表
create table tablename (字段名 类型());

5.查看有哪些表
show tables;

6.查看表结构
desc tablename;

7.删除数据库
drop database dbname;

8.删除表
drop table tablename;

9.修改表结构
alter table tablename modify 字段名  修改的值

10.增加表字段
alter table tablename add column 字段名 type;

11.删除字段
alter table tablename drop column 字段名;

12字段名
alter table tablename change oldtablename newtablename ;

13.更改表名
alter table tablename rename new_tablename;

14.修改字段排序
alter table tablename modfiy

dcl语句

grant * on databasename.tablename to username@% identified by 'pass'

例如：
grant all privileges on *.* to 178common@'%' identified by 'common';
grant select,insert,update,delete on *.* to 224test@'%' identified by '224test';