MySQL Note 3

--Building a library
Create DATABASE Dsdb DEFAULT CHARACTER set UTF8 collate utf8_general_ci;
/*
Deleting a database
Drop database name
*/
Drop DATABASE Dsdb;

--Show Database
Show DATABASES;

--Switch database
Use database name;
Use Dsdb;

--Build a table
CREATE TABLE table name (field name data type [Length] property [non-null default primary key note ...]) Charset=utf8,engine=innodb;

CREATE TABLE User_info (
user_id Int (one) NOT null primary key auto_increment,
User_name VARCHAR () DEFAULT null,
User_sex char (2),
User_age Int (3)
) Charset=utf8,engine=innodb;

--Delete Table
drop table name;
drop table User_info;

--Copy table
CREATE table new Table select * from old table; Cannot copy key
CREATE TABLE User_info_back select * from User_info;

CREATE table new table like old table;
CREATE table user_info_back2 like User_info;
--Modification
ALTER TABLE name add field name data Type property;--Add Field
ALTER TABLE User_info add user_address varchar (.) NOT NULL;

ALTER TABLE name change old field name New name data Type property;--Modify field
ALTER TABLE User_info Change user_address my_address char (+) null;

--Delete
ALTER TABLE name drop field name
ALTER TABLE user_info drop my_address;

--Add primary key
ALTER TABLE name Add primary key (field name);
ALTER TABLE User_info_back add primary key (USER_ID);

--Modify the name
ALTER TABLE name rename to new name;
ALTER TABLE User_info_back Rename to user_new_table;

Data manipulation:
Insert data:
Insert into table name ([field name]) value ([List value])
Insert into table name set field name = value, field name = value ... Alter
--Copy table
Insert into table name 1 SELECT * from table Name 2;
Use YYGDB;

Show variables like ' autocommit ';
--The value 0 and off are the same, of course, 1 means on.

SET autocommit = 0;
Insert into UserInfo (usename,usesex,useage)
VALUES
(' Thomas ', ' Male ', 6),
(' Thomas 1 ', ' Male ', 6),
(' Thomas 2 ', ' Male ', 6),
(' Thomas 2 ', ' Male ', 6),
(' Thomas 2 ', ' Male ', 6),
(' Thomas 2 ', ' Male ', 6),
(' Thomas 3 ', ' Male ', 6);
ROLLBACK;

SELECT * FROM UserInfo
INSERT INTO UserInfo set usename= "James", usesex= ' male ', useage=20;
CREATE TABLE userinfo_new select * from UserInfo;

--Modification
Update table name SET field name = value, field name = value, field name = value .... where conditional expression;

Update userinfo set usesex= ' car ', useage= ' where id<24 and useage<18;

--Remove several ways to differentiate?
Delete from table name where conditional expression;

Delete from userinfo where usename= ' Thomas 2 ';//delete data does not release tablespace

TRUNCATE table name;//delete data release table space

TRUNCATE TABLE userinfo;

DROP TABLE IF EXISTS order_table;

--Query
select * [Field name] from table name where conditional expression;

--Take partial data (paged query) at query limit
select * [Field name] from table name limit data bar number;

SELECT * from UserInfo ORDER by ID DESC limit 2, 3;

SELECT * from UserInfo limit (current page-1) * Number of bars per page, number of pages per page;

SELECT * from UserInfo limit 8, 4;

CREATE TABLE Employee (
ID SMALLINT (4) NOT NULL auto_increment,
employee_id INT (4) is not NULL,
first_name varchar (+) NOT NULL,
last_name varchar (35),
Email VARCHAR (NOT null),
Salary Decimal (8,2) is not NULL,
Primary KEY (ID)
);

INSERT INTO employee VALUES (' Bob ', ' connwer ', ' 245532fdf ', 453.90),
(2,2, ' bob2 ', ' Connwehr ', ' 245532fdf ', 453.90),
(3,3, ' bob3 ', ' conndfwer ', ' 245532fdf ', 453.90),
(bis, ' bob4 ', ' connwbfder ', ' 245532fdf ', 453.90);

Create a View virtual table (to protect data information from being viewed casually, such as wages)
CREATE View Employee_contact_info_view as
Select First_Name, Last_name,email from Employee order by last_name ASC;

SELECT * from Employee_contact_info_view;
Show tables;

Show CREATE View Employee_contact_info_view;
Views can be used in conjunction with all clauses and functions

Create a new view and re-name the field
Create View Employee_view (firstname,lastname,emailaddress) as
Select First_name,last_name,email from Employee ORDER by last_name ASC;
Alter View Employee_contact_info_view (FIRST_NAME,LAST_NAME,EMPLOYEE_ID) as
Select first_name,last_name,employee_id from Employee ORDER by last_name ASC;
Update Employee_contact_info_view set last_name= ' Hahah ' where employee_id=1;

SELECT * from Information_schema.views;

Drop View Employee_view;

MySQL Note 3