MySql basic syntax (study notes)

MySQL syntax _ study notes prepared by myself

Select * from emp; # comment
#---------------------------
# ---- Use a command line to connect to MySql ---------

# Start the mysql server
Net start mysql

# Disable
Net stop mysql
 
# Enter
Mysql-h host address-u user name-p User Password

# Exit
Exit

#---------------------------
# ---- MySql user management ---------

# Change Password: first enter the bin directory of the mysql installation path under DOS, and then type the following command:
Mysqladmin-uroot-p123 password 456;

# Adding users
# Format: grant permission on database. * to username @ login host identified by 'Password'
/*
For example, you can add a user user1 with the password password1 so that the user can log on to the machine and have the permission to query, insert, modify, and delete all databases. First, use the root user to connect to mysql, and then type the following command:
Grant select, insert, update, delete on *. * to user1 @ localhost Identified by "password1 ";
If you want the user to log on to mysql on any machine, change localhost to "% ".
If you do not want user1 to have a password, you can run another command to remove the password.
Grant select, insert, update, delete on mydb. * to user1 @ localhost identified "";
*/

Grant all privileges on wpj1105. * to sunxiao @ localhost identified by '000000'; # all privileges Permissions

#----------------------------
# ----- MySql database operation basics -----

# Displaying Databases
Show databases;

# Determine whether the database wpj1105 exists. If yes, delete it first.
Drop database if exists wpj1105;

# Creating a database
Create database wpj1105;

# Deleting a database
Drop database wpj1105;

# Use this database
Use wpj1105;

# Display tables in the database
Show tables;

# Determine whether a table exists and delete it first
Drop table if exists student;

# Creating a table
Create table student (
Id int auto_increment primary key,
Name varchar (50 ),
Sex varchar (20 ),
Date varchar (50 ),
Content varchar (1, 100)
) Default charset = utf8;

# Deleting a table
Drop table student;

# View the table structure
Describe student; # It can be abbreviated as desc student;

# Insert data
Insert into student values (null, 'A', 'male', '2017-10-2 ','......');
Insert into student values (null, 'bb ', 'female', '2017-03-6 ','......');
Insert into student values (null, 'cc', 'male', '2017-08-8 ','......');
Insert into student values (null, 'dd', 'femal', '2017-12-8 ','......');
Insert into student values (null, 'ee ', 'female', '2017-09-6 ','......');
Insert into student values (null, 'ff ', 'null', '2017-09-6 ','......');
# Querying data in a table
Select * from student;
Select id, name from student;

# Modifying a piece of data
Update student set sex = 'male' where id = 4;

# Deleting data
Delete from student where id = 5;

# And
Select * from student where date> '2014-1-2 'and date <'2014-12-1 ';

# Or
Select * from student where date <'2014-11-2 'or date> '2014-12-1 ';

#
Select * from student where date between '2014-1-2 'and '2014-12-1 ';

# In queries data in a set
Select * from student where id in (1, 3, 5 );

# Sort asc ascending desc descending
Select * from student order by id asc;

# Grouping query # Aggregate functions
Select max (id), name, sex from student group by sex;

Select min (date) from student;

Select avg (id) as 'calculate average' from student;

Select count (*) from student; # Total number of statistical tables

Select count (sex) from student; # The total number of gender in the statistical table. If sex is null in one piece of data, no statistics will be collected ~

Select sum (id) from student;

# Query data from entry I to entry j (excluding entry I)
Select * from student limit 2, 5; # display 3-5 data records

# Consolidation exercise
Create table c (
Id int primary key auto_increment,
Name varchar (10) not null,
Sex varchar (50), # DEFAULT 'male ',
Age int unsigned, # It cannot be a negative value (if it is a negative value, the default value is 0)
Sno int unique # repeatable
);

Drop table c;
Desc c;

Insert into c (id, name, sex, age, sno) values (null, 'taoge', 'male );
Insert into c (id, name, sex, age, sno) values (null, 'A', 'male );
Insert into c (id, name, sex, age, sno) values (null, 'flout', 'male', 35, 3 );
...

Select * from c;

# Modifying data
Update c set age = 66 where id = 2;
Update c set name = 'huahua ', age = 21, sex = 'female' where id = 2
Delete from c where age = 21;

# Common query statements
Select name, age, id from c
Select * from c where age> 40 and age <60; # and
Select * from c where age <40 or age <60; # or
Select * from c where age between 40 and 60 #
Select * from c where age in (,); # in queries data in a specified set
Select * from c order by age desc; # order by (asc ascending des descending)

# Group query
Select name, max (age) from c group by sex; # query the maximum age by gender
# Aggregate functions
Select min (age) from c;
Select avg (age) as 'average age' from c;
Select count (*) from c; # Total number of data in the statistical table
Select sum (age) from c;

# Modifying the table name
# Format: alter table tbl_name rename to new_name
Alter table c rename to;
 
# Modifying table structure
Create table test
(
Id int not null auto_increment primary key, # Set the primary key
Name varchar (20) not null default 'noname', # Set the default value
Department_id int not null,
Position_id int not null,
Unique (department_id, position_id) # Set the unique value
);

# Modifying the table name
# Format: alter table tbl_name rename to new_name
Alter table test rename to test_rename;

# Add a field (column) to the table)
# Format: alter table tablename add columnname type;/alter table tablename add (columnname type );
Alter table test add columnname varchar (20 );

# Modifying the name of a field in a table
Alter table tablename change columnname newcolumnname type; # modify the field name of a table
Alter table test change name uname varchar (50 );

Select * from test;

# Add column test to table position
Alter table position add (test char (10 ));
# Test
Alter table position modify test char (20) not null;
# Modify the default value of column test in table position
Alter table position alter test set default 'system ';
# Remove the default value of test from table position
Alter table position alter test drop default;
# Remove column test from table position
Alter table position drop column test;
# Table depart_pos Delete primary keys
Alter table depart_pos drop primary key;
# Add a primary key for table depart_pos
Alter table depart_pos add primary key PK_depart_pos
(Department_id, position_id );

# Load data into a database table in text format (for example, D:/mysql.txt)
Load data local infile "D:/mysql.txt" into table MYTABLE;

# Import the. SQL FILE command (for example, D:/mysql. SQL)
Source d:/mysql. SQL; # Or/. d:/mysql. SQL;