MySQL Learning-basic commands (1)

This article mainly introduces MySQL to add new users, create databases for users, assign permissions to new users, and MySQL basic commands.

Reference Blog http://blog.csdn.net/u013216667/article/details/70158452

First, log in to MySQL

mysql -u root -p

Second, add new users

Allow local IP access to localhost, 127.0.0.1

# 第一种添加方式# username：jasper# password: 123456create user ‘jaser‘@‘localhost‘ identified by ‘123456‘; # 第二种添加方式# username：laravel_tx# password: 123456insert into mysql.user(Host,User,Password) values("localhost","laravel_tx",password("123456"));

Allow extranet IP access

# username：jasper# password: 123456create user ‘jaser‘@‘%‘ identified by ‘123456‘;# 修改用户密码update mysql.user set password=password(‘123456‘) where User="jasper" and Host="localhost";

Third, refresh the authorization

flush privileges;

Iv. creating a database for users

V. Assigning permissions to new users

Grant the user full permissions on the local server for the database

 grant all privileges on `laravel`.* to ‘jasper‘@‘localhost‘ identified by ‘123456‘;  

Specify partial permissions to users

 grant select on laravel.* to [email protected] identified by ‘123456‘;

Six, refresh Permissions

Vii. log out of root and login again

exit;

Eight, the new user login

mysql -u jasper -h 127.0.0.1 -p

Ix. Creating a data table

show databases;use laravel;CREATE TABLE IF NOT EXISTS tasks (  task_id bigint(20) NOT NULL AUTO_INCREMENT,  subject VARCHAR(45) DEFAULT NULL,  start_date DATE DEFAULT NULL,  end_date DATE DEFAULT NULL,  description VARCHAR(200) DEFAULT NULL,  PRIMARY KEY (task_id)) ENGINE=InnoDB  DEFAULT CHARSET=utf8  comment=‘任务信息表‘;

X. Inserting a piece of data

INSERT INTO tasks ( subject, start_date, end_date,description ) VALUES ( ‘laravel框架‘,‘2017-11-19‘, ‘2017-12-30‘,‘有插入权限的用户插入数据‘);

XI. Basic SQL statements

drop database dbname;//删除一个已经确定存在的数据库alter table 表名 ENGINE=存储引擎名;//修改表的存储引擎alter table 表名 drop 属性名;//删除字段alter table 旧表名 rename to 新表名;//修改表名alter table 表名 modify 属性名 数据类型;//修改字段数据类型alter table 表名 change 旧属性名 新属性名 新数据类型;//修改字段名alter table 表名 drop FOREING KEY 外键别名;//删除子表外键约束alter table example add phone VARCHAR(20);//增加无约束的字段alter table example add age INT(4) NOT NULL;//增加有约束的字段alter table example add num INT(8) PRIMARY KEY FIRST;//表的第一个位置增加字段alter table example add address VARCHAR(30) NOT NULL AFTER phone;//表的指定位置之后增加字段alter table example modify name VARCHAR(20) FIRST;//把字段修改到第一位alter table example modify num INT(8) ATER phone;//把字段修改到指定字段之后

MySQL Learning-basic commands (1)