Create DATABASE Golden;
CREATE TABLE Golden.users (
usersid int unsigned NOT NULL Auto_increment primary key,
UserName char () NOT NULL,
PassWord int unsigned NOT NULL
);
CREATE table golden.u like golden.users; --Duplicate table structure
INSERT INTO GOLDEN.U select * from Golden.users; --Copy table data
CREATE TABLE Golden.uu select * from GOLDEN.U; --Copy table structure and data
SELECT * from Golden.uu;
SELECT * from GOLDEN.U;
ALTER TABLE golden.users Change password pwd varchar (20); --Modify column names
ALTER TABLE golden.users change userName usersname varchar (20); --Modify column names
ALTER TABLE golden.users Modify column PassWord char (20); --Modifying column types
ALTER TABLE golden.users auto_increment = 2; --Modify the self-increment start value
ALTER TABLE golden.users add column age int; --Add a column
Insert into Golden.users (USERSID,USERSNAME,PWD) value (5, ' Wangwu ', ' 123 '); --Add data
Insert into Golden.users (usersname,pwd) value (' lb ', 123);
Delete from golden.users where usersid=5 and usersname = ' ww '; --Delete data
Update golden.users set age=10 where usersid=4; --Modify data
SELECT * from Golden.users; --Query
Create INDEX Index_usersid on golden.users (USERSID);--Creating indexes
Drop index userId on golden.users; --Delete Index
Show index from Golden.users; --Display Index
Show keys from Golden.users;
CREATE VIEW Golden.v_view as SELECT * from Golden.users; --Create a view
Create View Golden.v_view (usersid,usersname) as select Usersid,usersname from Golden.users;
Drop View Golden.v_view; --Delete View
Describe Golden.v_view; --Query view
Desc Golden.v_view;
Select count (age) from Golden.users; --Total queries
Select SUM (age) from Golden.users; --Summation
Select AVG (USERSID) from Golden.users; --Average
Select Max (USERSID) from Golden.users; --Maximum
Select min (usersid) from Golden.users; -Minimum
--Paged query Limit (m,n) (M refers to the beginning of the record index, starting from 0, indicating the first record, N for the beginning of m+1, Fetch N)
SELECT * from Golden.users ORDER by UserSID LIMIT 3, 2;
SELECT * from Golden.users WHERE UserSID between 2 and 6;
SELECT * from Golden.users WHERE usersid in (1, 4, 6);