用户

用户登录：

mysql –h hostname user_name –p
Mysql –h root –p

创建用户：

Create user ‘用户名’@’主机名’ identifiy by ‘密码
Create user ‘zeyu’ @’localhost’ identify by 123456

分配权限：

grant 权限 on数据库/表 TO ‘用户’@’主机名’[identified by ‘密码’]
*.* * all with资源限制
Grant all privilege on movies.* to zeyu@localhost;
Flush privilege 刷新权限立即生效

显示用户列表：

select user from mysql.user
Desc mysql.user//显示user全部信息

显示用户权限：

select user, select_priv from mysql.user//是否有select权限
Select user, db,select_priv from mysql.db;//用户在某数据库是否有select权
Show grants for zeyu@localhost;//显示zeyu的全部权限

吊销用户权限：

revoke update,delete on movies from zeyu@localhost;//吊销zeyu的更新和删除权限
all privilege 全部权限

设置密码：set password for zeyu@localhost = password(‘hello’);

删除用户： drop user zeyu@localhost;

数据库

创建，使用，删除数据库：

create database movies;
Show databases;
create database if not exists movies;//没有才创建，不报错
登录时加 –showwarnnings可显示错误
Use movies;//切换数据库
Drop movies；//删除数据库

创建数据表：

create database movies;

        Create table film(
            Film_name varchar(255),
            Film date 
        );

    Describe film;

添加数据栏:

   Alter table film add id INT（10）first;//在第一个位置
   Alter table film add file_context after film_name;//在film_name后面`
   Alter table film add PRIMERY KEY (id);//设置主键

修改数据栏/表：

        Alter table film change id film_id INT(10);//改数据栏
        Alter table film rename to movie;//改数据表
        Alter table movie drop film_content;//删除栏
        Drop table movie;//删除表

重新创建数据库/表：

       drop database movietalk;
       Create database movietalk charset=utf8;//创建并指定字符集
       Use moviestalk;
       Create table people(
            People_id INT(10) unsigned not null auto_inclument,
            People _name varchar(100),
            People_birth DATE,
            People_location varchar(100),
            Primary key(people_id)//设置主键
        ）default charset=utf8//设置默认字符集 

插入数据：

    insert into 数据表（栏1,栏2,栏3）value(值1，值2，值3)；
    Insert into people values(null,’丹泽尔’，‘1954-12-28’，‘美国’);
    Insert into people(people_name, people_location) values (‘赫本’，‘美国’)；

选择数据：

    select 栏1，栏2，栏3…from 数据表
    Select * from people;//*表示全部
    Select people_name,people_birth from people;
    Select * from people where people_location = ‘美国’；
    Select * from people order by people_birth desc//小到大 asc:大到小

更新数据：

          update 表 set 字段=‘值’where 字段 = 值；
          Update people set people_birth = ‘1976-06-05’ where people_id = 2;
          Delete from people where people_id = 6;

限制结果的数量与偏移：

            select * from where people_location=’美国’ limit 3;//数量限制为3
            Select * from people limit 3 offset 1;//1 为偏移量
            Select *from people limit 1， 3;//第一个参数为偏移量，第二个为数量

操作符：

   select *  from people where people_birth>’1960-01-01’
   select *  from people where people_birth>’1960-01-01’ and people_birth<’1970-01-01’;
   select *  from people where people_location in (‘美国’，’英国’)；
   select *  from people where people_location not in (‘美国’，’英国’)；
   select *  from people where people_name like (‘李%’)；//_一个 %一个或者多个关系

关联：

select user_name ,review_containt from user review where user. userid = review.reviewid; , = 交叉关联 or 内部关联
select user_name ,review_containt from user inner join review on user. userid = review.reviewid;
select user_name ,review_containt from user inner join review on user. userid = review.reviewid where userid = 1;
left join
select username,reviewcontent from user left join review on user.userid = review.userid;
right join

统计：

select count(reviewed) from review;
Select fim_id ,count(reviewed) from review group by filmid;
Select film_id , avg(review_rate) from review group by filmid;

三个表：

    select filmname, peoplename job from film,people,filmpeople where(filmpeople.fimid = fim.fimid and fimname.film_name…. and  fimname = ‘无间道’)
    Select sum(fimbox) as totalbox, peoplename from film,people,filmpeople where filmpeople.fimid =film.filmid and… and job = ‘导演’ group by peoplename order by totalbox desc;